MySQL5.1性能優(yōu)化方案

1、 11/11MySQL5.1性能優(yōu)化方案 MySQL5.1性能優(yōu)化方案 1.平臺數(shù)據(jù)庫 1.1. 操作系統(tǒng) Red Hat Enterprise Linux Server release 5.4 (Tikanga) ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped 32位Linux服務(wù)器，單獨作為MySQL服務(wù)器使用。 1.2. MySQL 系統(tǒng)使用的

2、是MySQL5.1，最新的MySQL5.5較之老版本有了大幅改進。主要體現(xiàn)在以下幾個方面： 1）默認存儲引擎更改為InnoDB InnoDB作為成熟、高效的事務(wù)引擎，目前已經(jīng)廣泛使用，但MySQL5.1之前的版本默認引擎均為MyISAM，此次MySQL5.5終于將默認數(shù)據(jù)庫存儲引擎改為InnoDB，并且引進了Innodb plugin 1.0.7。此次更新對數(shù)據(jù)庫的好處是顯而易見的：InnoDB的數(shù)據(jù)恢復(fù)時間從過去的一個甚至幾個小時，縮短到幾分鐘(InnoDB plugin 1.0.7，InnoDB plugin 1.1，恢復(fù)時采用紅-黑樹)。InnoDB Plugin 支持數(shù)據(jù)壓縮存儲，節(jié)約

3、存儲，提高內(nèi)存命中率，并且支持adaptive flush checkpoint, 可以在某些場合避免數(shù)據(jù)庫出現(xiàn)突發(fā)性能瓶頸。 Multi Rollback Segments：原來InnoDB只有一個Segment，同時只支持1023的并發(fā)?，F(xiàn)已擴充到128個Segments，從而解決了高并發(fā)的限制。 2）多核性能提升 Metadata Locking (MDL) Framework替換LOCK_open mutex (lock)，使得MySQL5.1及過去版本在多核心處理器上的性能瓶頸得到解決。 3）制功能(Replication)加強 過去的異步復(fù)制方式意味著極端情況下的數(shù)據(jù)風(fēng)險，MySQ

4、L5.5將首次支持半同步(semi-sync replication)在MySQL的高可用方案中將產(chǎn)生更多更加可靠的方案。4）增強表分區(qū)功能 MySQL 5.5的分區(qū)更易于使用的增強功能，以及TRUNCATE PARTITION命令都可以為管理和維護數(shù)據(jù)庫節(jié)省大量的時間，并且具有更加靈活高效的分區(qū)方式。 1.3. CPU 系統(tǒng)所用CPU是單個4核CPU。對于CPU密集的負載，MySQL通常從更快的CPU中獲益，而不是更多CPU。MySQL5.1的架構(gòu)對多CPU的擴展性不好，并且MySQL不能在多個CPU上并行地運行某個查詢，因此在對于單個CPU進行密集的查詢時，CPU速度限制了響應(yīng)時間。為了實

5、現(xiàn)低延遲，即快速響應(yīng)時間，需要快速的CPU，因為單個查詢只能使用一個CPU。值得注意的是，MySQL5.5在多核心處理器上的性能有了很大的提升。另外，MySQL在64位架構(gòu)上工作得更好，比32位架構(gòu)更能有效地使用大量內(nèi)存。 盡管本系統(tǒng)使用的是32位操作系統(tǒng)，CPU運行在32位模式下，但它仍支持64位計算。（cat /proc/cpuinfo | grep flags | grep lm | wc -l） 1.4. 磁盤空間 系統(tǒng)的磁盤空間目前沒有壓力。 1.5. 內(nèi)存 內(nèi)存總大小為4G，只供操作系統(tǒng)和數(shù)據(jù)庫使用。 1.6. 數(shù)據(jù)庫的表和文件 數(shù)據(jù)庫addb共有339張表：其中InnoDB表30

6、3張，MyISAM表34張，MEMORY表2張。 InnoDB數(shù)據(jù)文件ibdata1大小為30138MB，一周后ibdata1大小為30234MB，MyISAM數(shù)據(jù)文件（包括表結(jié)構(gòu)、索引及數(shù)據(jù)）總大小約為1642MB，一周后約為1639MB?？梢钥闯觯瑪?shù)據(jù)庫的數(shù)據(jù)量較穩(wěn)定，InnoDB數(shù)據(jù)文件增加了約106MB，總大小一周內(nèi)沒有大的變化。MyISAM表中，值得注意的是表terminalalarm_bak，該表總大小約為1623MB，占整個MyISAM表總大小比重近99%。 二進制日志單個文件大小為1GB，二進制日志文件總大小接近20GB。 1.7. 數(shù)據(jù)分布情況 服務(wù)器某時間點非精確值： 數(shù)據(jù)

7、量范圍表數(shù)量（總共339張，其中分區(qū)表2張）1000萬 100% （理想值 85%）。MySQL服務(wù)器實際上允許max_connections+1個客戶端進行連接。額外的連接保留給具有SUPER權(quán)限的賬戶。通過為系統(tǒng)管理員而不是普通用戶授予SUPER權(quán)限（普通用戶不應(yīng)具有該權(quán)限），系統(tǒng)管理員能夠連接到服務(wù)器來診斷問題，即使已連接的無特權(quán)客戶端數(shù)已達到最大值也同樣。 本系統(tǒng)中設(shè)置的最大連接數(shù)是600，而響應(yīng)的連接數(shù)是601，應(yīng)適當增加Max_connections變量的值。 Open_files和Open_files_limit 如果Open_files的值與Open_files_limit的值

8、較為接近，那就應(yīng)該增加Open_files_limit。 max_connections 和table_open_cache 與open_files_limit 的關(guān)系： max_1 = 10 + max_connections + table_cache * 2，該值為1122； max_2 = max_connections * 5，該值為3000； max_3 = max_os_open_files，該值為1024，表示操作系統(tǒng)單個進程最大允許打開文件句柄（文件描述符）。 open_files_limit 取三個值中的最大值，設(shè)置3000較合理，不需要調(diào)整。Select_full_joi

9、n 全聯(lián)接是無索引聯(lián)接，它真正影響性能，最好能避免全聯(lián)接，即使是每分鐘一次也較多，如果聯(lián)接沒有索引，則最好能優(yōu)化查詢和索引。 select_full_range_join 如果select_full_range_join的值過高，就說明運行了許多使用了范圍查詢聯(lián)接表，有時大的范圍查詢也會比較慢，可以從中進行優(yōu)化。 Select_range_check Select_range_check變量記錄了在聯(lián)接時，對每一行數(shù)據(jù)重新檢查索引的查詢計劃的數(shù)量，它的性能開銷很大，如果該值較高或正在增加，則說明一些查詢沒有找到好索引。 上述變量目前正常，如果發(fā)生明顯變化，則結(jié)合慢查詢?nèi)罩靖櫲?lián)接性能 較差的

10、查詢。 Slow_launch_threads 該變量如果較大則說明某些因素正在延遲聯(lián)接的新線程，服務(wù)器存在一些問題。它通常表示系統(tǒng)過載，導(dǎo)致操作系統(tǒng)不能給新創(chuàng)建的線程分配時間片。Table_locks_waited Table_locks_waited變量顯示了有多少表被鎖住了并且導(dǎo)致服務(wù)器級的鎖等待，InnoDB的行級鎖不會使該變量增加。如果該值較高并且正在增加，則說明存在嚴重的并發(fā)瓶頸，這時應(yīng)該考慮使用InnoDB或另外使用行級鎖的存儲引擎，或者手動對大表進行分區(qū)，并優(yōu)化查詢，啟用并發(fā)插入或?qū)︽i設(shè)置進行優(yōu)化。 本系統(tǒng)該變量在半小時內(nèi)變化幅度不超過3，不必調(diào)整。 2.MySQL優(yōu)化策略 2

11、.1. 索引策略 索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，它對于高性能非常關(guān)鍵，因此建立索引是現(xiàn)實中性能問題的首要原因。索引在數(shù)據(jù)越大的時候越重要。規(guī)模小，負載輕的數(shù)據(jù)庫即使沒有索引，也能有好的性能，但是當數(shù)據(jù)增加的時候，性能就會下降。 MySQL有多種類型類型的索引，它們各有自己的性能特點。索引是在存儲引擎層實現(xiàn)的，而不是在服務(wù)器層，因此它們并不是標準化的，每個引擎的索引工作方式略有不同，并不是所有的引擎都支持所有類型的索引。即使多個引擎支持同樣的索引，他們的實現(xiàn)方式也可能有所不同。MySQL索引類型的各自特點可以查閱相關(guān)資料去進一步了解。 即使已經(jīng)了解了關(guān)于索引的知識，但也許還不知道如

12、何從實際的表開始。雖然通常情況下是檢查系統(tǒng)中最常運行的查詢，但往往性能瓶頸可能就出現(xiàn)在不那么經(jīng)常進行的插入或更新操作，要避免在不知道什么查詢會使用索引之前就創(chuàng)建它這種常見錯誤，并且要考慮是否所有的索引能形成一個優(yōu)化的配置。 有時只從查詢就可以知道需要什么索引，只要把它們加上就可以了。但是有時各種類型的查詢，卻不能找到適合他們的完美索引，這種情況就需要一些折中。為了找到最佳平衡，應(yīng)該進行測試和剖析。 第一個要檢查的就是響應(yīng)時間，要考慮為任何耗時很長的查詢創(chuàng)建索引，然后檢查導(dǎo)致最大負載的查詢，并且添加索引予以支持。如果系統(tǒng)正好碰到了內(nèi)存、CPU或磁盤瓶頸，也要把他們考慮進去。例如，如果運行了很長的

13、聚合查詢以生成匯總，那么磁盤會因為使用了支持GROUP BY查詢的索引而得益。 通常情況下，都要試著擴展索引，而不是新增索引，維護一個多列索引要比維護多個單列索引容易，但不能過多否則就會有麻煩，使用太多這種列表也會造成優(yōu)化器要計算的組合激增，并最終降低查詢速度。如果不知道查詢的分布，就盡可能使索引變得更有選擇性，這樣更有好處。即使已經(jīng)創(chuàng)建了正確的索引，還需要維護表和索引以確保它們能很好的工作。 2.2. 查詢性能優(yōu)化 2.2.1優(yōu)化數(shù)據(jù)訪問 查詢性能低下的最基本原因就是訪問了太多數(shù)據(jù)。一些查詢不可避免地要篩選大量數(shù)據(jù)，大部分性能欠佳的查詢都可以用減少數(shù)據(jù)訪問的方式進行修改。在分析性能較低的查詢

14、的時候，查明應(yīng)用程序是否正在獲取超過需要的數(shù)據(jù)，即訪問了過多的行或列，以及MySQL服務(wù)器是否分析了超過需要的行。 一些查詢先向服務(wù)器請求不需要的數(shù)據(jù)，然后再丟掉它們。這給服務(wù)器造成了額外的負擔(dān)，增加了網(wǎng)絡(luò)開銷，消耗了內(nèi)存和CPU資源。 2.2.2重構(gòu)查詢 當優(yōu)化有問題的查詢時，并不意味著一定要從MySQL得到完全一樣的結(jié)果，偶爾可以用完全等價的方式得到更好的性能。如果不同的查詢能提供更高的效率，盡管得到的結(jié)果不同，也可以考慮重寫查詢。也許最終應(yīng)用程序的代碼也會和查詢一起被改寫。 MySQL Server 客戶端 查詢 緩存 解析器 解 析 樹 預(yù)處理器 查詢 優(yōu)化器 查詢 執(zhí)行計劃 查詢執(zhí)行

15、引擎 解 析 樹 API調(diào)用 存儲引擎 etc. MyISAM InnoDB SQL 結(jié)果 結(jié)果 數(shù)據(jù) 查詢的執(zhí)行路徑 2.2.3復(fù)雜查詢 一個重要的查詢設(shè)計問題就是是否可以把一個復(fù)雜查詢分解成多個簡單的查詢。大多數(shù)時候，都強調(diào)用用盡可能少的查詢做盡可能多的事情。這種方式的積極意義在于它可以減少查詢解析和優(yōu)化的步驟，并且代碼量較少。但是對于數(shù)據(jù)量較大的查詢來說，通過分解查詢可以得到更高的效率，同時也使程序更易于維護和擴展?？梢允褂梅种畏?，讓查詢在本質(zhì)上不變，但是每次只執(zhí)行一小部分，以減少受影響的行數(shù)，清理陳舊數(shù)據(jù)也是一種很好的方式。 想得到高性能，最佳的方式就是了解MySQL如何優(yōu)化和執(zhí)行查詢

16、，一旦理解了背后的機制，那么很多優(yōu)化工作就簡化為純粹的推理，并且也可以理解查詢優(yōu)化過程中的邏輯性。 2.2.4分解聯(lián)接 在較復(fù)雜實際應(yīng)用中，可以采用“分解聯(lián)接”把一個多表聯(lián)接分解成多個單個查詢，然后在應(yīng)用程序端實現(xiàn)聯(lián)接操作，盡管從代碼初看上去比較浪費，其實 也只是增加了查詢的數(shù)量而已，但是這種分解方式有很好的性能優(yōu)勢，查詢本身會更高效，能使緩存的效率更高，如果一個表經(jīng)常改變，那么分解聯(lián)接就可以減少緩存失效的次數(shù)；同時對于MyISAM表來說，每個表一個查詢可以更有效地利用表鎖，因為查詢會鎖住單個表較短時間，而不是把多個表長時間鎖住，有時在應(yīng)用程序端進行聯(lián)接可以更方便擴展數(shù)據(jù)庫。 2.2.5查詢緩

17、存 MySQL有一種稱為查詢緩存的緩存機制，它可以保留查詢返回給客戶端的完整結(jié)果。當緩存命中的時候，服務(wù)器馬上返回保存的結(jié)果，并跳過解析、優(yōu)化和執(zhí)行步驟。MySQL將查詢緩存完全存儲在內(nèi)存中，緩存不僅僅存儲了查詢結(jié)果，它在某種程度上像一個文件系統(tǒng)，它保持了自身的結(jié)構(gòu)，而這些結(jié)構(gòu)有助于它了解那塊內(nèi)存是空閑的、表和查詢之間的映射關(guān)系。 查詢緩存保留了查詢使用過的表，如果表發(fā)生了改變，那么緩存就失效了，這樣看上去不夠高效，某些表的改變不會導(dǎo)致查詢結(jié)果的改變。但是這種簡單方式的開銷比較小，而這對于繁忙的系統(tǒng)是很重要的。查詢緩存對應(yīng)用程序完全透明，程序不用知道MySQL是從緩存中返回結(jié)果還是通過實際計算

18、返回結(jié)果。兩種方式返回的結(jié)果是一樣的，即不會改變語義，不管緩存是打開的還是關(guān)閉的，服務(wù)器的行為都一樣。 緩存并不會自動地比非緩存高校。緩存也需要開銷，只有在節(jié)省的資源大于開銷的時候，緩存才是真正有效率的，這和服務(wù)器的負載有關(guān)。理論上，可以通過對比在緩存開啟和關(guān)閉時服務(wù)器需要做的工作來了解緩存是否有幫助，但實際上很難精確地計算或者預(yù)測查詢緩存的好處，有時必須考慮外部因素。例如，查詢緩存可以減少產(chǎn)生結(jié)果的時間，但它不會減少將結(jié)果發(fā)送到客戶端的時間，而這有可能是主要因素。 從緩存中受益最多的查詢可能是需要很多資源來產(chǎn)生結(jié)果，但是不需要很多空間來保存的類型，比如集合查詢。所以用于存儲、返回和失效的代價

19、都較小。當然也有其他類型的查詢值得緩存。 檢查是否從查詢緩存中受益的最簡單的辦法就是檢查緩存命中率，它是緩存提供的查詢結(jié)果的數(shù)量，而不是服務(wù)器執(zhí)行的數(shù)量。當服務(wù)器收到SELECT語 句的時候，Qcache_hits和Com_select這兩個變量會根據(jù)查詢緩存的情況進行遞增，查詢緩存命中率的計算公式是：Qcache_hits / ( Qcache_hits + Com_select )。命中率多少才好視情況而定，如果緩存命中率代表了開銷最大的查詢，那么即使是很低的命中率也有很大的好處。緩存可能會因為碎片、內(nèi)存不足或數(shù)據(jù)改變而失效。如果已經(jīng)給緩存分配了足夠的內(nèi)存，并且把query_cache_m

20、in_res_unit調(diào)整到了合適的值，那么大部分緩存失效應(yīng)該是由數(shù)據(jù)改變引起的?？梢酝ㄟ^檢查Com_update, Com_delete等的值知道有多少查詢修改了數(shù)據(jù)，也可以通過檢查Qcache_lowmen_prunes的值了解有多少查詢因為內(nèi)存不足而失效。 應(yīng)該監(jiān)視服務(wù)器實際使用的緩存數(shù)量，如果它沒有用到分配的內(nèi)存，就應(yīng)該把分配給它的內(nèi)存減少一點。如果由于內(nèi)存限制引起了緩存失效，那么就應(yīng)該多分配一些內(nèi)存，但不用太在意緩存的大小，它比有實際影響的稍大一點或小一點都沒有問題，只有在內(nèi)存有嚴重浪費或緩存失效太多的時候才需要去考慮它的大小，有時還應(yīng)該在服務(wù)器其他緩存和查詢緩存之間找到某種平衡。

21、3.調(diào)整配置文件 MySQL的默認配置不適用于使用大量資源，具體的配置文件依賴于服務(wù)器的硬件、數(shù)據(jù)量、查詢類型、響應(yīng)時間、事務(wù)持久性和連續(xù)性等因素。不能一直期望改變配置文件而帶來巨大的性能替身，提升的具體大小取決于工作負載，通?？梢酝ㄟ^選擇適當?shù)呐渲脜?shù)得到兩到三倍的性能提升。在這之后，性能提升就是增量的，通過改變一兩個配置參數(shù)，可能會看到某些原本運行較慢的查詢快了一些，但是，服務(wù)器的性能通常不會提升一個數(shù)量級，為了達到更大的提升，通常需要檢查服務(wù)器架構(gòu)、查詢及應(yīng)用程序的架構(gòu)。 最好的方式每次只小幅度地改動一兩個設(shè)置，并且在每次改動之后都進行測試查看效果，也許增減一點就帶來了性能提升，但是再增

22、加一點就讓性能下降了，那么有可能是過多地請求了某種資源，也有可能是在服務(wù)器和操作系統(tǒng)或硬件之間造成了某種不匹配，sort_buffer_size有可能會受到CPU緩存的影響。一些變量也會依賴于其他變量，innodb_log_file_size的最佳大小就依賴于innodb_buffer_pool_size。了解這些需要經(jīng)驗，也需要對系統(tǒng)架構(gòu)有一定了解。 在對配置進行調(diào)優(yōu)之前，應(yīng)該對查詢和結(jié)構(gòu)進行調(diào)優(yōu)，進行一些最基本的優(yōu)化，比如添加索引。如果已經(jīng)對配置文件做了很多調(diào)整，再回過頭來更改查詢和架構(gòu)，那么就有可能要重新調(diào)整配置文件，調(diào)優(yōu)是一個漸進的過程，可以把調(diào)整配置文件看成一個兩步的過程：在安裝的時

23、候使用適當?shù)某跏贾担缓蠡诠ぷ髫撦d進行細節(jié)調(diào)整。 4.內(nèi)存使用調(diào)優(yōu) 配置MySQL正確地使用內(nèi)存對性能至關(guān)重要?？梢哉J為MySQL的內(nèi)存消耗有兩種范疇：可以控制的和不可控制的。不能控制MySQL使用多少內(nèi)存來運行服務(wù)器、解析查詢及管理內(nèi)部運行，但是可以控制它為特定工作使用多少內(nèi)存。在特定的系統(tǒng)上，MySQL可能使用的內(nèi)存有一個絕對的上線。起始點是服務(wù)器物理內(nèi)存的數(shù)量。MySQL只需要很少的內(nèi)存保持連接開啟。它也需要一定的基本內(nèi)存來執(zhí)行查詢，需要在MySQL工作負載處于頂峰的時候為它分配足夠的內(nèi)存，否則查詢就會因為內(nèi)存不足而變得很慢，甚至失敗。和為查詢指定內(nèi)存一樣，還應(yīng)當為操作系統(tǒng)保留足夠的內(nèi)

24、存。 5.MySQL I/O調(diào)優(yōu) 某些配置選項可以影響MySQL把數(shù)據(jù)同步到硬盤和進行恢復(fù)的方式，它們通常對性能有很大的影響，因為這其中牽涉了昂貴的I/O操作，同時也代表了性能和數(shù)據(jù)安全的折中。一般情況下，保證數(shù)據(jù)被立即而連續(xù)地寫入磁盤的代價是很高的。 MyISAM表通常在每次寫入之后就會把索引的改變刷寫到磁盤上。如果準備對一個表做很多改變，那么把它們組成一個批處理就會快很多。 系統(tǒng)絕大多數(shù)表使用的是InnoDB引擎，我們不僅可以控制它如何恢復(fù)，還可以控制它如何打開表及刷寫數(shù)據(jù)，它們影響了數(shù)據(jù)恢復(fù)和總體性能。InnoDB 的恢復(fù)過程是自動的并且在InnoDB啟動的時候總會運行，但人為可以影響它

25、。InnoDB有復(fù)雜的鏈式緩沖區(qū)和文件，使它可以改進性能并且保證ACID屬性。 對于普通使用，一些較重要的配置是InnoDB 日志文件大小、InnoDB 如何刷寫日志緩沖區(qū)，以及InnoDB 如何執(zhí)行I/O 。 文件系統(tǒng) 日志 文件 緩沖池緩沖池 I/O 線程 操 作 系 統(tǒng) 緩 存 循環(huán)順序?qū)懭?事 務(wù) 日 志 日志文件雙寫 緩沖數(shù)據(jù)、索引、撤銷日志等日志文件- - - - - -數(shù)據(jù) 文件數(shù)據(jù) 文件數(shù)據(jù)文件- - - - - -寫入 線程讀取線程日志線程 InnoDB 的緩沖區(qū)和文件 6. InnoDB 調(diào)優(yōu) InnoDB 是系統(tǒng)主要用到的存儲引擎，它直接影響著系統(tǒng)的事務(wù)控制、安全性、備份

26、恢復(fù)以及日常的業(yè)務(wù)查詢等。 6.1 InnoDB 事務(wù)日志 InnoDB 使用日志來減少提交事務(wù)的開銷，它不是在每次事務(wù)提交的時候就把緩沖池刷寫到磁盤上，而是記錄了事務(wù)。事務(wù)對數(shù)據(jù)和索引做出的改變通常會被映射到表空間的隨機位置，所以將這些改變寫道磁盤上就會引起隨機I/O ，隨機I/O 比順序I/O 開銷要高得多。InnoDB 使用自身的日志把隨機I/O 轉(zhuǎn)換為順序I/O ，一旦日志被記錄到磁盤上，事務(wù)就是持久的了，盡管這時候改變還沒有被寫道數(shù)據(jù)文件中。如果一旦發(fā)生意外比如斷電，InnoDB 可以回放日志并恢復(fù)提交了的事務(wù)。當然，InnoDB 最終要把改變寫到數(shù)據(jù)文件中，因為日志的大小是 固定的

27、。它以循環(huán)的方式寫日志，當記錄達到日志的底部，就會又從頂部開始，它不會覆蓋改變沒有被應(yīng)用到數(shù)據(jù)文件的記錄，因為這會消除提交的事務(wù)唯一持久性的記錄。 InnoDB使用后臺線程智能地把改變寫入到文件中。該線程可以把寫入集中在一起，然后以更高的順序?qū)懭氲姆绞綀?zhí)行。實際上，事務(wù)日志把隨機數(shù)據(jù)文件I/O轉(zhuǎn)換為順序日志文件和數(shù)據(jù)文件I/O。把刷寫工作后臺進行可以讓查詢完成得更迅速，并且在查詢負載很大的時候可以對I/O系統(tǒng)進行緩沖。 InnoDB用多個文件組成一個循環(huán)日志系統(tǒng)，通常不用改變默認的日志數(shù)量，只須改變每個日志文件的大小就可以了。為了改變?nèi)罩疚募拇笮。汝P(guān)閉MySQL，然后移走舊日志，再重新配置

28、大小，最后重新啟動MySQL就行了。要確保干凈地關(guān)閉MySQL,否則日志文件中就有可能還保留著需要被應(yīng)用到數(shù)據(jù)文件的記錄。重新啟動服務(wù)器的時候可以觀察錯誤日志，完成啟動后就可以刪除舊日志。 為了決定日志文件的理想大小，需要衡量通常數(shù)據(jù)改變的開銷和崩潰時恢復(fù)的時間。如果日志太小，InnoDB將會設(shè)置更多的檢查點，并且導(dǎo)致更多日志寫入。如果日志太大，InnoDB在恢復(fù)的時候可能就會做大量的工作，增加恢復(fù)的時間。另外，數(shù)據(jù)大小和訪問模式也會影響恢復(fù)時間。如果數(shù)據(jù)量很大，在緩沖池中也有許多不干凈的頁面，比如那些更改沒有被應(yīng)用到數(shù)據(jù)文件的頁面，并且他們在整個數(shù)據(jù)中均勻分布，那么從崩潰中恢復(fù)就會花很長時間

29、，InnoDB不得不掃描日志、檢查數(shù)據(jù)文件，并且按照需要把改動應(yīng)用到文件上，這意味著大量的讀寫，另一方面，如果更改是局部化的，比如只有很少比例的數(shù)據(jù)經(jīng)常被改動，恢復(fù)就會很快，即使數(shù)據(jù)和日志文件很大也是這樣?；謴?fù)時間也依賴于典型更改的大小，它和數(shù)據(jù)行的平均長度有關(guān)，較短的行可以在日志中存儲更多的改動，所以InnoDB在恢復(fù)的時候就會回放更多的改動。 在InnoDB改變數(shù)據(jù)的時候，它會把這次改動的記錄寫到日志緩沖里面。日志緩沖被保存在內(nèi)存中。緩沖寫滿或事務(wù)提交，不管哪種情況先發(fā)生，InnoDB 都會把緩沖區(qū)寫道磁盤上的日志文件中。如果有大型事務(wù)，就可以增加緩存文件來減少I/O動作。 日志緩沖區(qū)必須

30、被刷寫到持久性存儲中，以保證提交了的事務(wù)能完全持久 化。重要的是知道把日志緩沖寫入日志文件和把日志刷寫到持久性存儲中的區(qū)別。在大多數(shù)操作系統(tǒng)中，把緩沖寫入日志只是簡單地數(shù)據(jù)從InnoDB的內(nèi)存緩沖區(qū)移到操作系統(tǒng)的緩存中，它也在內(nèi)存中，實際上不會把數(shù)據(jù)寫入持久性存儲中。相反的是，將日志刷寫到持久性存儲中意味著InnoDB要求操作系統(tǒng)把數(shù)據(jù)刷到緩存外部并且確保寫入到磁盤上，這會阻止I/O掉用，直到數(shù)據(jù)被完全寫入。 6.2InnoDB表空間 InnoDB把數(shù)據(jù)保存在表空間中。表空間實際上是跨越了磁盤上的一個或多個文件的虛擬文件系統(tǒng)。InnoDB出于很多考慮使用了表空間，而不僅僅是為了存儲表和索引，它

31、保留了自己的撤銷日志、插入緩存以及表空間的其他內(nèi)部結(jié)構(gòu)。可以使用innodb_data_file_path定義表空間文件，這些文件都在innodb_data_home_dir定義的目錄中。 管理表空間也許是一件費力的事情，尤其是它能自動增長，而同時又想收回空間?；厥湛臻g的唯一辦法就是將數(shù)據(jù)進行轉(zhuǎn)儲、關(guān)閉MySQL、刪除舊的文件、改變配置、重新啟動、讓InnoDB創(chuàng)建新文件并且恢復(fù)數(shù)據(jù)。InnoDB對空間的控制很嚴格，不能簡單地移除文件或改變大小。如果表空間崩潰了的話，InnoDB 就不會啟動，它在寫入負荷很重的情況下會增長的很大，但不能像在MyISAM 中那樣隨便移動數(shù)據(jù)。 7.MySQL并發(fā)

32、調(diào)優(yōu) 當MySQL在高并發(fā)條件下工作的時候，可能會遇到以前未曾遇到過的性能瓶頸。 7.1 MyISAM并發(fā)調(diào)優(yōu) 需要很仔細地控制同時進行的讀寫，以避免讀取到不連續(xù)的數(shù)據(jù)。MyISAM 在某些條件下允許并發(fā)插入和讀取，并且還可以認為調(diào)度某些操作，以盡可能少地阻止工作。MyISAM的刪除操作不會重新安排整個表，它只是把行標記為已經(jīng)刪除，并且在表中留下了一些空余標識，MyISAM在可能的情況下會優(yōu)先使 用這些空余，為插入復(fù)用空間。如果表是完整的，它就會把新的行拼接在表的最后。即使MyISAM有表級別的鎖，它也能在讀取的同時把行拼接到表尾，通過禁止讀取最后一行做到這一點，這避免了不連續(xù)的讀取。但當表中

33、間的數(shù)據(jù)改變的時候，要提供連續(xù)讀取就困難得多，它只有在到達表尾的時候才允許并發(fā)插入?？梢允褂胏oncurrent_insert變量配置MyISAM的并發(fā)插入行為。 7.2 InnoDB并發(fā)調(diào)優(yōu) InnoDB是為高并發(fā)設(shè)計的，它的結(jié)構(gòu)仍然基于有限內(nèi)存、單CPU和單磁盤系統(tǒng)（新發(fā)布的5.5版本針對InnoDB已有較大改善）。InnoDB某些方面的性能在高并發(fā)條件下下降得很快，并且唯一的解決辦法就是限制并發(fā)。 8.操作系統(tǒng)和硬件優(yōu)化 MySQL服務(wù)器中最弱的部分決定了其性能，它的操作系統(tǒng)和硬件通常也會成為限制因素。磁盤大小、可用內(nèi)存、CPU資源、網(wǎng)絡(luò)和連接它們的組件一起決定了系統(tǒng)的最終容量。通常情況

34、下，安裝更多內(nèi)存、重新配置磁盤或升級I/O 是較好的選擇。CPU飽和發(fā)生在MySQL使用的數(shù)據(jù)能被裝入內(nèi)存，或者能夠盡快根據(jù)需要從磁盤上讀取的時候，密集地在多表之間執(zhí)行無索引的連接操作都可能引發(fā)CPU飽和。I/O飽和通常發(fā)生在需要的數(shù)據(jù)比內(nèi)存多得多的時候。如果應(yīng)用程序分布在網(wǎng)絡(luò)上，查詢數(shù)據(jù)相當巨大，或者要求很低的延時，瓶頸就會轉(zhuǎn)移到網(wǎng)絡(luò)上。發(fā)現(xiàn)瓶頸通常不是顯而易見的事情。某個區(qū)域的弱點通常會給其他方面帶來壓力，問題可能就會表現(xiàn)在其他子系統(tǒng)上面。例如沒有足夠的內(nèi)存，MySQL就會刷寫數(shù)據(jù)，為需要的數(shù)據(jù)騰出空間，然后再馬上把數(shù)據(jù)重新讀回來，因此內(nèi)存不足就變線為I/O容量不夠，同樣地，達到飽和的內(nèi)存

35、問題也會表現(xiàn)為CPU問題。 擁有大量內(nèi)存的最大原因不是能夠在內(nèi)存中保存大量的數(shù)據(jù)，最終目的是可以避免磁盤I/O，訪問磁盤的速度比訪問內(nèi)存慢幾個數(shù)量級，關(guān)鍵就是在于平衡內(nèi)存大小、速度、開銷和其他因素，以得到好的性能。 數(shù)據(jù)庫服務(wù)器同時使用了隨機I/O和順序I/O，隨機I/O從緩存中得益是最 多的。磁盤中的順序操作都比隨機操作快，順序訪問內(nèi)存中的數(shù)據(jù)也比隨機訪問快，兩者可能會出現(xiàn)幾個數(shù)量級的差別。存儲引擎能更快地執(zhí)行順序讀取，隨機讀取通常意味著存儲引擎需要做索引操作，因此，對于隨機讀取I/O問題，添加內(nèi)存是最佳選擇。 9.大數(shù)據(jù)表的設(shè)計優(yōu)化 平臺的主要性能瓶頸主要是在大表查詢這塊，表寫入目前還沒有

36、形成壓力，以下主要闡述幾種設(shè)計思路，有的只會在某種特定業(yè)務(wù)中會用到，需要通過具體業(yè)務(wù)來對表設(shè)計進行具體分析。 9.1. 垂直切分 垂直切分主要是針對數(shù)據(jù)庫架構(gòu)而非表的設(shè)計而言的，在這里提出來是因為它會影響到大表結(jié)構(gòu)的設(shè)計。 系統(tǒng)的總體功能是由多個功能模塊所組成，而每一個功能模塊所需要的數(shù)據(jù)對應(yīng)到數(shù)據(jù)庫中就是一個或多個表。各個功能模塊相互之間的接口越統(tǒng)一，越少，系統(tǒng)的耦合度就越低，實現(xiàn)數(shù)據(jù)的垂直切分就越容易。若某一個功能模塊其整體數(shù)據(jù)量特別大或者因該功能并發(fā)讀寫特別頻繁而形成瓶頸，且與系統(tǒng)的其他功能模塊耦合度很低，則可以考慮將該功能模塊整體垂直切分，將其部署在單獨的主機上，分攤整體系統(tǒng)的壓力。 9.2. 水平切分 水平切分是針對表按照數(shù)據(jù)行來切分，即將表中的某些行切分到一個數(shù)據(jù)庫，而另外的某些行又切分到其他的數(shù)據(jù)庫中。切分必須按照某種特定的規(guī)則來進行，這樣才能夠較容易地判定各行數(shù)據(jù)被切分到那個數(shù)據(jù)庫中了?？筛鶕?jù)關(guān)鍵字段的取值（奇偶性，取模），時間字段的范圍等來進行水平切分，結(jié)合實際業(yè)務(wù)需要和數(shù)據(jù)量來定義切分的粒度。 如果某個功能存在一張大數(shù)據(jù)量表而影響性能，而整個功能模塊的大部分核心表都可以通過某個字段（如：userid）來進行關(guān)聯(lián)，那這個字段就是一個進行 水平切分的關(guān)鍵字段，這樣所有可以與該字段關(guān)聯(lián)的表都可以按照特定規(guī)則被水平切分到同一個數(shù)