mysql性能優(yōu)化解決方案.doc

MySQL數(shù)據(jù)庫性能優(yōu)化Renhao 2011/11/301. 資源管理平臺數(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, stripped32位Linux服務器，單獨作為MySQL服務器使用。1.2. MySQL系統(tǒng)使用的是MySQL5.1，最新的MySQL5.5較之老版本有了大幅改進。主要體現(xiàn)在以下幾個方面：1） 默認存儲引擎更改為InnoDBInnoDB作為成熟、高效的事務引擎，目前已經(jīng)廣泛使用，但MySQL5.1之前的版本默認引擎均為MyISAM，此次MySQL5.5終于將默認數(shù)據(jù)庫存儲引擎改為InnoDB，并且引進了Innodb plugin 1.0.7。此次更新對數(shù)據(jù)庫的好處是顯而易見的：InnoDB的數(shù)據(jù)恢復時間從過去的一個甚至幾個小時，縮短到幾分鐘(InnoDB plugin 1.0.7，InnoDB plugin 1.1， 恢復時采用紅-黑樹)。InnoDB Plugin 支持數(shù)據(jù)壓縮存儲，節(jié)約存儲，提高內(nèi)存命中率，并且支持adaptive flush checkpoint, 可以在某些場合避免數(shù)據(jù)庫出現(xiàn)突發(fā)性能瓶頸。Multi Rollback Segments： 原來InnoDB只有一個Segment，同時只支持1023的并發(fā)。現(xiàn)已擴充到128個Segments，從而解決了高并發(fā)的限制。2） 多核性能提升Metadata Locking (MDL) Framework替換LOCK_open mutex (lock)，使得MySQL5.1及過去版本在多核心處理器上的性能瓶頸得到解決。3） 制功能(Replication)加強過去的異步復制方式意味著極端情況下的數(shù)據(jù)風險，MySQL5.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速度限制了響應時間。為了實現(xiàn)低延遲，即快速響應時間，需要快速的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表303張，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ù)分布情況服務器某時間點非精確值：數(shù)據(jù)量范圍表數(shù)量（總共339張，其中分區(qū)表2張）1000萬rows5000萬4張（MyISAM表1張）500萬rows1000萬6張100萬rows500萬5張50萬rows100萬4張10萬rows50萬12張（MyISAM表1張）5萬rows10萬9張（MyISAM表1張）1萬rows5萬23張（MyISAM表2張）1 rows1萬136張（MyISAM表9張，MEMORY表2張）rows=0（無數(shù)據(jù)）140張觀察系統(tǒng)中數(shù)據(jù)量很大且未進行表分區(qū)的InnoDB表l adrotateresultdetail_fail的數(shù)據(jù)量達到4千萬，createTime列是datatime類型，且有索引，意味著存在以該列為查詢條件或關(guān)聯(lián)條件查詢的需求，因此可以在該列上以自然月份進行表分區(qū)。l terminalalarm的數(shù)據(jù)量也突破千萬，AlarmTime列是datatime類型，且有索引，意味著存在以該列為查詢條件或關(guān)聯(lián)條件查詢的需求，因此可以在該列上以自然月份進行表分區(qū)。在事件ev_terminalalarm中會查詢該表，若進行表分區(qū)，也能一定程度上提高事件的執(zhí)行效率。l terminalalarminfo表僅自增列有索引，主要用于存儲數(shù)據(jù)，可不用分區(qū)。l Terminallogin表的loginTime列是datatime類型，且有索引，意味著存在以該列為查詢條件或關(guān)聯(lián)條件查詢的需求，因此可以在該列上以自然月份進行表分區(qū)。l adplayinfo_bak表存在多個以INT類型為索引的列，根據(jù)實際業(yè)務情況選擇查詢頻率高且能以范圍值來分區(qū)的整型列對該表進行分區(qū)。l adrotateresultdetail的createTime列是datatime類型，且有索引，意味著存在以該列為查詢條件或關(guān)聯(lián)條件查詢的需求，因此可以在該列上以自然月份進行表分區(qū)。l upfile_bak表僅自增列有索引，若存在查詢或者統(tǒng)計業(yè)務則可以createTime列進行分區(qū)，若該表沒有查詢方面業(yè)務可不必進行分區(qū)。除去配置參數(shù)等屬性表，對于數(shù)據(jù)量大且不斷遞增的業(yè)務數(shù)據(jù)表，最直接的辦法可以按照時間字段進行分區(qū)，或是根據(jù)查詢業(yè)務來選擇合適的列進行表分區(qū)和創(chuàng)建索引，這樣能夠有效提高存儲和查詢效率。1.8. 服務器配置參數(shù)記錄查詢：普通日志log、慢速日志log_slow_queriesMySQL有兩種查詢?nèi)罩荆浩胀ㄈ罩竞吐偃罩?，它們都會記錄查詢。普通日志記錄了服務器接收到的每一個查詢，也包含了沒有被執(zhí)行的查詢，比如因為錯誤而未被執(zhí)行的查詢，還有一些非查詢事件，比如連接和斷開連接，普通日志不包含執(zhí)行時間或其他只有在查詢結(jié)束之后才能得到的信息。相反，慢速日志只包含了已經(jīng)執(zhí)行過的查詢，如果是啟動狀態(tài)，它記錄了執(zhí)行時間超過了特定長度的查詢。兩種日志都有助于分析，但是慢速日志更有利找到性能較慢的查詢。一個相關(guān)配置是log_queries_not_using_indexes，它使服務器把沒有使用索引的查詢記錄到慢速查詢?nèi)罩局?，無論它們執(zhí)行速度有多快。盡管打開慢速日志相對于執(zhí)行慢速查詢來說，通常只增加了很少的時間，但是如果沒有使用索引的查詢非?？?，例如從小數(shù)據(jù)量表中查詢，這樣就會記錄它們可能導致服務器變慢，甚至還會使用大量的磁盤空間，慢速日志也許就會被那些快速高效的查詢?nèi)麧M。慢查詢?nèi)罩究梢杂脕碚业綀?zhí)行時間長的查詢，可以用于優(yōu)化。慢日志打開后，通過設(shè)置long_query_time來配置記錄查詢超過的指定時間，默認值為10秒，根據(jù)系統(tǒng)的負載和性能要求進行設(shè)置(SET GLOBAL long_query_time = )。檢查又長又慢的查詢?nèi)罩緯苈闊梢允褂肕ySQLdumpslow命令獲得日志中顯示的查詢摘要來處理慢查詢?nèi)罩?。系統(tǒng)兩種日志都沒有開啟，可以在需要的時候打開慢速日志來幫助分析性能較慢的查詢。具體實施參考MySQL手冊。需要注意的是查詢在日志中只出現(xiàn)一次并不意味著它是一個不好的查詢，也不意味將來也會慢，查詢時快是慢有多種原因：1） 表也許被鎖定，導致查詢處于等待狀態(tài)；2） 數(shù)據(jù)或索引也許沒有被緩存在內(nèi)存中；3） 或者正在進行批處理大量的數(shù)據(jù)，使得磁盤I/O變慢；4） 服務器可能同時在運行其他的查詢，影響了當前查詢的效率。因此，只能把慢速查詢?nèi)罩究闯烧{(diào)優(yōu)工作的一部分，可以用它來找到可疑的查詢，但需要對它們進行仔細地排查和分析。u 啟用系統(tǒng)慢速日志，分析查詢性能慢的時候可以觀察該日志信息。Qcache_hitsCom_selectQcache_inserts檢查是否從查詢緩存中受益的最直接辦法就是檢查緩存命中率。它是提供緩存提供的查詢結(jié)果的數(shù)量，而不是服務器執(zhí)行的數(shù)量。當服務器收到select語句的時候，Qcache_hits和Com_select這兩個變量會根據(jù)查詢緩存的情況進行遞增。查詢緩存命中率的計算公式：Qcache_hits/(Qcache_hits+Com_select)，根據(jù)公式計算得出查詢緩存命中率為7%。初看上去該命中率很低，但注意到com_select等于qcache_inserts + qcache_not_cache + 權(quán)限檢查錯誤的總和，即這個比率中包含了緩存失效的因素，而對于數(shù)據(jù)變更頻繁的系統(tǒng)來說，緩存是及其容易失效的，表的任何時刻的數(shù)據(jù)插入或更新都會使該表的緩存失效，所以本系統(tǒng)緩存的插入率很低，拋開失效的緩存因素，用如下公式計算緩存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)= 84.87%，該比值要好得多，意味著大部分的查詢都命中了緩存，換一種說法就是仍有一小部分查詢沒有被緩存。沒被緩存和緩存失效是兩個概念，分別計數(shù)，但都會引起com_select的值增加。命中率要多少才好，這視情況而定，因為對于每一個查詢，不執(zhí)行它所節(jié)約的資源遠大于緩存中保存結(jié)果以及讓查詢失效的開銷，如果緩存命中代表了開銷最大的查詢，那么即使很低的命中率也是有好處的。緩存可能會因為碎片、內(nèi)存不足或數(shù)據(jù)改變而失效。如果已經(jīng)給緩存分配了足夠的內(nèi)存，并且把Query_cache_min_res_unit調(diào)整到了合適的值，那么大部分緩存失效都應該是由數(shù)據(jù)改變而引起的。Com_update, Com_delete等的值知道有多少查詢修改了數(shù)據(jù)，也可以通過檢查Qcache_lowmen_prunes的值了解有多少查詢因為內(nèi)存不足而失效。u 接近85%的命中率可以滿足系統(tǒng)要求，如果該命中率持續(xù)降低則需要對系統(tǒng)進行性能分析并調(diào)整。系統(tǒng)表數(shù)據(jù)變更頻繁，查詢緩存的失效率較高，如果對變更頻繁大表的查詢頻率較高，則使用SQL_NO_CACHE 和SQL_CACHE來控制是否需要使用查詢緩存。Query_cache_size分配給查詢的總內(nèi)存必須是1024的倍數(shù)，系統(tǒng)設(shè)置為128MB。在服務器啟動的時候，MySQL會為查詢緩存一次性分配變量所定義數(shù)量的內(nèi)存。如果更新了變量，MySQL會立即刪除所有緩存的查詢，重新把緩存設(shè)置為定義的大小，并重新初始化緩存的內(nèi)存。Query_cache_type Query_cache_type設(shè)置在何場景下使用 Query Cache。系統(tǒng)的查詢緩存是開啟狀態(tài)。_cache_type可以設(shè)置為0(OFF)，1(ON)或者2(DEMOND)，分別表示完全不使用query cache，除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，只有顯示要求才使用query cache(使用sql_cache)。Query_cache_limit該選項限制了MySQL存儲的最大結(jié)果為2M，如果查詢的結(jié)果比這個值大，那么就不會被緩存。服務器在產(chǎn)生結(jié)果的同時進行緩存，它無法預先知道結(jié)果是否會超過這一限制。如果在緩存的過程中發(fā)現(xiàn)已經(jīng)超過了限制，MySQL會自動增加Qcache_not_cached的值，并且丟掉已經(jīng)緩存過的值。如果預先判斷會有這種情況，可以給查詢加上SQL_NO_CHACHE來避免這種開銷。u 以查詢某表（18列）中的5000條結(jié)果為例，結(jié)果集數(shù)據(jù)大小約為1.4M，該設(shè)置是能滿足要求的，保持該值即可。但如果查詢結(jié)果數(shù)據(jù)過萬的情況較多的話則應適當增加該值，最大不要超過4M。Qcache_free_memory如果緩存由大結(jié)果和小結(jié)果混合而成，那么就很難找到一個合適的大小，既能避免碎片，也能避免過多的內(nèi)存分配，但是緩存大結(jié)果沒有太大的益處，可以通過降低Query_cache_limit的值阻止緩存大結(jié)果，它有時有助于在碎片和在緩存中保存結(jié)果的開銷中得到平衡。Query_cache_min_res_unitQcache_free_blocksQcache_total_blocksQcache_lowmen_prunes可以通過檢查Qcache_free_blocks的值來觀察緩存中碎片的情況，它可以顯示緩存中有多少內(nèi)存塊處于空閑狀態(tài)。碎片最嚴重的情況就是在每兩個存儲了數(shù)據(jù)的塊之間都有一個比最小值稍小的可用塊，這樣每隔一個存儲塊就有一個自由塊，因此，如果Qcache_free_blocks大致等于Qcache_total_blocks/2，則說明碎片非常嚴重。Qcache_lowmem_prunes表示由于緩存內(nèi)存不足被清除出查詢緩存的條數(shù)，如果Qcache_lowmem_prunes的值正在增加，并且有大量的自由塊，就說明碎片導致查詢正被從緩存中永久刪除。查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數(shù)據(jù)量的話。使用FLUSH QUERY CACHE命令移除碎片，該命令會把所有的存儲塊向上移動，并把自由塊移到底部。當它運行的時候，會阻止訪問查詢緩存，這會鎖定整個服務器，但它通常會很快，除非緩存特別大。如果緩存沒有碎片，但是命中率卻不高，那么就應該給緩存分配較少的內(nèi)存，如果服務器找不到足夠大小的塊來存儲結(jié)果，就應該從緩存中清理掉一些查詢，可以使用RESET QUERY CACHE命令從緩存中移除查詢。當服務器清理查詢的時候，Qcache_lowmen_prunes值會增加，如果它的值增加得很快，可能有兩個原因：1）如果有很多自由塊，就可能是有碎片引起的；2）如果自由塊比較少，就可能表示工作負載使用的內(nèi)存大小超過了所分配的內(nèi)存，可以檢查Qcache_free_memory知道為使用的內(nèi)存數(shù)量。如果有很多自由塊，碎片很少，由于內(nèi)存不足引起的清理工作也很少，但命中率仍然不高，這說明工作負載也許不能從緩存中受益，一定有什么阻止了查詢使用緩存，很多update語句可能會是原因，另一個原因可能是查詢是不可緩存的。查詢緩存分配的最小塊的大小Query_cache_min_res_unit為4MB。當查詢進行的時候，MySQL把查詢結(jié)果保存在查詢緩存中，但如果要保存的結(jié)果比較大，超過query_cache_min_res_unit的值 ，這時候MySQL會一邊檢索結(jié)果，一邊保存結(jié)果，所以，有時候并不是把所有結(jié)果全部得到后再進行一次性保存，而是每次分配一塊query_cache_min_res_unit 大小的內(nèi)存空間保存結(jié)果集，使用完后，接著再分配一個這樣的塊，如果還不夠，接著再分配一個塊，依此類推，也就是說，有可能在一次查詢中，MySQL要進行多次內(nèi)存分配的操作。當一塊分配的內(nèi)存沒有完全使用時，MySQL會把這塊內(nèi)存截掉，把沒有使用的那部分歸還以重復利用，當連續(xù)操作后剩下的內(nèi)存大小不足以分配一個內(nèi)存單元時，內(nèi)存碎片便產(chǎn)生了。通常無法避免所有的碎片，但是仔細選擇Query_cache_min_res_unit可以避免在查詢緩存中造成大量的內(nèi)存浪費，關(guān)鍵在于每一個新塊和服務器已分配給存儲結(jié)果的塊的數(shù)量之間找到平衡，如果值過小，服務器將會浪費較少的內(nèi)存，但會更頻繁地分配塊，這對服務器意味著更多的工作。如果值過大，碎片將會很多，合適的折中是在浪費內(nèi)存和增加處理時間上取得平衡。u 空緩存百分比：Qcache_free_blocks / Qcache_total_blocks 16%，且系統(tǒng)Qcache_free_blocks值較高，有可能是出現(xiàn)碎片了，使用flush query cache整理查詢緩存并消除碎片，該命令不會從緩存中移除任何查詢。同時定期觀察內(nèi)存碎片情況。Key_buffer_sizeKey_readsKey_reads_requests鍵緩存讀命中率：100-（Key_reads*100）/ Key_reads_requests）= 99.975Key_read_requests和Key_reads是兩個計數(shù)器，Key_read_requests是從緩存讀取索引的請求次數(shù)，Key_reads是從磁盤讀取索引的請求次數(shù)。key_buffer_size指定MyISAM表索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。MyISAM鍵緩存默認只有一個緩沖區(qū)，MyISAM自身只緩存了索引，沒有數(shù)據(jù)，它讓操作系統(tǒng)緩存數(shù)據(jù)，它的值應該占到所有保留內(nèi)存的25%到50%，操作系統(tǒng)緩存用來保存從MYD文件中讀取出來的數(shù)據(jù)。該變量給鍵緩沖分配指定大小的空間，但是操作系統(tǒng)只有在實際用到這些空間的時候才會進行分配，也可以創(chuàng)建多個鍵緩存，如果對于一個非默認大小的鍵緩存設(shè)置為0，MySQL就會把每一個索引從特定的緩存移到默認的緩存中，并且在沒有對象使用特定的緩存時就將其刪掉，給一個不存在的緩存設(shè)置這個變量將會創(chuàng)建緩存，對一個已有的緩存設(shè)置非零值將會沖洗緩存，這是一個在線操作，它會阻止所有訪問該緩存的動作，直到緩存沖洗完成。另一個參考指標是單位時間內(nèi)Key_reads值的變化情況。u 系統(tǒng)使用MyISAM表查詢頻率較低，鍵緩存讀命中率在99%以上，表明鍵緩存能滿足系統(tǒng)的性能要求。Key_blocks_unusedKey_blocks_used鍵緩存使用率= Key_blocks_used/ (Key_blocks_used+ Key_blocks_unused)=37%u 盡管鍵緩存使用率較低，說明key_buffer_size設(shè)置較高，MySQL沒有將其使用完，基于鍵緩存各方面都能滿足系統(tǒng)要求且內(nèi)存夠用，不必調(diào)整。table_cache_size/table_open_cache (5.1.2之后叫做table_open_cache)Open_tablesOpened_tablesOpen_tables表示當前打開的表緩存數(shù)，如果執(zhí)行flush tables操作，則此系統(tǒng)會關(guān)閉一些當前沒有使用的表緩存而使得此狀態(tài)值減?。籵pend_tables表示曾經(jīng)打開的表緩存數(shù)，會一直進行累加，如果執(zhí)行flush tables操作，值不會減小。應該將Open_tables的值和table_cache進行對照。如果每秒有太多Opened_tables，那么說明table_cache還不夠大，表緩存沒有被完全利用上時，顯式的臨時表也能導致Opened_tables增加。table_cache指定表高速緩存的大小。設(shè)置該變量不會立即生效，要等到下一個線程打開表的時候才會生效，當它生效的時候，MySQL會檢查變量的值，如果值大于緩存表中的數(shù)量，線程就可以把新打開的表插入到緩存中，這樣可以更快地訪問表內(nèi)容。如果值小于緩存表中的數(shù)量，MySQL就會從緩存中刪除掉沒有使用的表。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果發(fā)現(xiàn)open_tables等于table_cache，并且opened_tables在不斷增長，那么就需要增加table_cache的值了。u Open_tables值與table_cache相等，且觀察到Opened_tables較大，應適當增加table_cache，可將其設(shè)置為512。thread_cache_sizethread_cache_size是緩存的同時操作的線程數(shù)。線程緩存保存了和當前連接無關(guān)的線程，這些線程可以供新連接使用。當一個新連接被創(chuàng)建出來并且緩存中有一個線程的時候，MySQL會把這個線程從緩存中刪除，并且把它賦給連接。連接關(guān)閉時，MySQL會回收線程，把它放回到緩存中。如果緩存中沒空間了，MySQL就會銷毀該線程。只要緩存中有自由的線程，MySQL就能很快地響應連接請求，因為它不需要為每個連接都創(chuàng)建新的線程。設(shè)置該變量不會立即生效，需要等到下一次線程關(guān)閉的時候，MySQL會檢查緩存中是否有空間存儲線程。如果是，他會把線程緩存起來，供另外一個連接使用，如果不是，它會直接結(jié)束線程，這種情況下，緩存中線程的數(shù)量，以及線程緩存使用的內(nèi)存數(shù)量不會立即下降。只有當新連接為了使用線程而把它從緩存中移走的時候才會看到下降。MySQL只有在連接關(guān)閉的時候才會把線程加入緩存，也只有在創(chuàng)建新連接的時候才從緩存中移除線程。Connectionsthread_connectedthreads_createdConnections變量表示連接意圖的數(shù)量，而不是當前接連的數(shù)量（threads_connected），如果它的值快速增加，比如每秒幾百，就應該檢查連接以及操作系統(tǒng)的網(wǎng)絡(luò)設(shè)置。本系統(tǒng)中該值正常。thread_cache_size定義了MySQL能在緩存中保存的線程數(shù)量，可以通過觀察threads_created變量的值，以確定線程緩存是否足夠大。如果Threads_created的值較大或正在增加，可以嘗試增加thread_cache_size的值，通過檢查Threads_created知道有多少緩存已經(jīng)在緩存中了。如果每秒創(chuàng)建的線程數(shù)量少于10個，緩存的大小就是足夠的。另外，可以觀察thread_connected值的變化來設(shè)置線程緩存，本系統(tǒng)中它的值保持在100以下。大多數(shù)情況，非常大的線程緩存是沒有必要的，通常需要把線程緩存保持足夠大以使threads_created不會經(jīng)常增加，但是如果它的值非常大，本系統(tǒng)已超過一萬就屬于非常大了，那么就應該把它設(shè)置得小一點，因為操作系統(tǒng)不能很好地處理太多的線程，即使它們處于睡眠狀態(tài)也不行。通常情況，據(jù)物理內(nèi)存設(shè)置規(guī)則如下：1G內(nèi)存設(shè)為8，2G內(nèi)存設(shè)為16，3G內(nèi)存設(shè)為32，4G或4G以上設(shè)為64。u 本系統(tǒng)內(nèi)存為4G，且thread_connected 的增幅并不大，thread_cache_size設(shè)置為64，不需要更改。read_buffer_sizeread_buffer_size是MySQL讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySQL會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。MySQL只有在查詢需要的時候才會為該緩存分配內(nèi)存，并且是一次性把指定的大小分配給該緩存。read_rnd_buffer_sizeread_rnd_buffer_size是MySQL的隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設(shè)置該值，以避免內(nèi)存開銷過大。MySQL只有在查詢需要的時候才會為該緩存分配內(nèi)存，并且只會分配所需的內(nèi)存。sort_buffer_sizesort_buffer_size是MySQL執(zhí)行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變量的大小。MySQL只有在查詢需要排序的時候才會為該緩沖區(qū)分配內(nèi)存，只要發(fā)生了排序，MySQL會立即分配變量定義的所有內(nèi)存，不管是否需要這么大的空間。如果它的值大于排序需要的空間，那么就意味著浪費。sort_buffer_size有可能會受CPU緩存的影響。Sort_merge_passesSort_merge_passes的值較大說明應該增加sort_buffer_size，也許僅僅是為某些查詢，最好的辦法就是優(yōu)化排序性能較慢的查詢。u sort_buffer_size為3M，能夠滿足系統(tǒng)的查詢要求。對于排序沒有額外要求的情況下不需要調(diào)整。innodb_log_file_sizeInnodb_log_files_in_groupInnoDB日志文件總體大小由innodb_log_file_size和innodb_log_files_in_group控制，并且它們對寫入的性能影響較大。這兩個文件默認大小都較小，對于高性能的負載，這個大小是不夠的，日志文件總大小的上限是4GB，但是即使是寫入負載極高的查詢也只需要幾百兆，比如總共256MB。innodb_log_buffer_sizeInnoDB 在寫事務日志的時候，為了提高性能，也是先將信息寫入 Innodb_log _buffer中，當滿足innodb_flush_log_at_trx_commit參數(shù)所設(shè)置的相應條件(或者日志緩沖區(qū)寫滿)之后，才會將日志寫到文件中。可以通過 innodb_log_buffer_size 參數(shù)設(shè)置其可以使用的最大內(nèi)存空間?？刂凭彌_大小的變量是innodb_log_buffer_size。不需要把緩沖區(qū)變得很大。推薦值是1MB到8MB。除非要寫入大量的巨型BLOB記錄，否則這個大小就足夠了，日志相對InnoDB的正常數(shù)據(jù)要緊湊得多。它們不是基于頁面的，所以它們不會在存儲數(shù)據(jù)的時候浪費整個頁面。innodb_os_log_written可以通過show innodb status命令的log部分檢測InnoDB向日志文件寫入了多少數(shù)據(jù)，一個好的辦法就是觀察10秒到100秒時間間隔內(nèi)的數(shù)據(jù)，并且注意最大值，可以使用這個值來判斷日志緩沖大小是否合適。例如，如果最大數(shù)據(jù)是每秒寫入100KB，那么1MB的日志緩存可能就足夠了。也可以使用這個指標來決定日志文件的合適大小。如果最大值是每秒100KB，256MB日志文件就已足夠了。innodb_flush_log_at_trx_commit如果比起持久性而更在意性能，可以通過設(shè)置innodb_flush_log_at_trx_commit的值來控制日志緩存被刷寫到什么地方及刷寫的頻率。該參數(shù)可以設(shè)置為0，1，2，解釋如下：0：log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log file中，且同時會進行文件系統(tǒng)到磁盤的同步操作，但是每個事務的提交并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作；1：在每次事務提交的時候?qū)og buffer 中的數(shù)據(jù)都會寫入到log file，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步；2：事務提交會觸發(fā)log buffer 到log file的刷新，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會有一次文件系統(tǒng)到磁盤同步操作。上午10點抽樣查看半小時的日志數(shù)據(jù)情況，每分鐘InnoDB向日志文件寫入了多少數(shù)據(jù)，抽取其中數(shù)據(jù)較大的10條信息分析查看：u InnoDB平均每分鐘向日志文件寫入約9.2MB，而本系統(tǒng)大部分情況下每分鐘寫入約5MB6MB，每秒寫入約100KB，本系統(tǒng)innodb_log_file_size設(shè)置為64MB，可以將該值設(shè)置為256MB，使其足夠大滿足性能要求，日志文件越大，越節(jié)省IO，但是會增長恢復時間。該抽樣可能不是系統(tǒng)最高峰值，在系統(tǒng)負載最大時分析得出的結(jié)果更加準確。innodb_max_dirty_pages_pctinnodb_max_dirty_pages_pct不是用來設(shè)置用于緩存某種數(shù)據(jù)的內(nèi)存大小的一個參數(shù)，而是用來控制在 InnoDB緩沖池中可以不用寫入數(shù)據(jù)文件中的臟數(shù)據(jù)頁的比例（本系統(tǒng)為默認值：90%），即已經(jīng)被修改但還沒有從內(nèi)存中寫入到數(shù)據(jù)文件的臟數(shù)據(jù)。這個比例值越大，從內(nèi)存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤I/O。u 保持默認值。innodb_file_per_tableinnodb_file_per_table選項使InnoDB為每一個表使用一個文件，它在數(shù)據(jù)庫目錄中以表名.ibd文件形式保存數(shù)據(jù)，這使得刪除表后回收數(shù)據(jù)變得比較容易，并且它對于把表分布到多個磁盤上也很有用處，但將數(shù)據(jù)放在多個文件中能導致浪費更多的存儲空間，因為它把單個InnoDB表空間的碎片都放在了ibd文件中，這對于小表尤其會成為一個問題，因為InnoDB的頁面大小是16KB，即使表只有1KB數(shù)據(jù)，它也需要至少16KB的磁盤空間。即使開啟了innodb_file_per_table選項，還需要為撤銷日志和其他系統(tǒng)數(shù)據(jù)定義表空間，而且不能簡單地通過拷貝文件來移動、備份或恢復表，且肯定不能在服務器之間拷貝數(shù)據(jù)。u 本系統(tǒng)數(shù)據(jù)小于1萬條表比重超過80%，不需要為每個表使用一個文件，保持默認值。concurrent_insert可以使用concurrent_insert變量配置MyISAM表的并發(fā)插入行為，它有下面的值：0，MyISAM不允許并發(fā)插入，每一次插入都會把表鎖?。?，默認值，只要表中沒有空缺，MyISAM就允許并發(fā)插入；2，它強制并發(fā)插入到表尾，即使表有空缺也不例外，如果沒有線程從表中讀取數(shù)據(jù)，MySQL就會把新數(shù)據(jù)插入到空缺中。使用了該值，表的碎片會增多，也就需要更經(jīng)常地對表進行優(yōu)化。u 保持默認值。delay_key_write對于MyISAM表，可以通過配置把一些操作延遲，然后合并到一起執(zhí)行，例如可以使用delay_key_write延遲寫入索引，但也會帶來一些矛盾：立即寫入索引，安全但代價較高，或者等待寫入并邪王在寫入前不要斷電，這樣更快，但斷電就會導致大規(guī)模的索引損壞。innodb_thread_concurrencyInnoDB控制并發(fā)最基本的方式是使用innodb_thread_concurrency變量，它限制了一次有多少線程，它限制了一次有多少線程能進入內(nèi)核，沒有辦法為所有的架構(gòu)和負載確定最佳的并發(fā)數(shù)量，但通常情況下可以這樣計算：u 并發(fā)=CPU數(shù)量磁盤數(shù)量2，本系統(tǒng)計算并發(fā)數(shù)為8。innodb_thread_sleep_delay如果InnoDB內(nèi)核中已經(jīng)有了允許數(shù)量的線程，那么線程就不能再進入內(nèi)核了，InnoDB采用了一種兩階段的過程來保證線程可以盡可能高效地進入內(nèi)核，這種策略減少了操作系統(tǒng)引起的開銷。線程首先睡眠innodb_thread_sleep_delay所規(guī)定的微秒數(shù)，然后再進行嘗試，如果還是不能進入，它就會進入一個等待線程的隊列中并且把控制權(quán)交給操作系統(tǒng)。第一階段默認的睡眠時間是10000微秒，當有很多線程都處于正在等待進入隊列這一狀態(tài)時，改變這個值有助于提高系統(tǒng)并發(fā)，而默認值在有大量小查詢的時候會太大了，因為它給查詢增加了10毫秒延時。u 保持默認值，當并發(fā)使用大查詢時才有必要調(diào)整該值。innodb_commit_concurrencyInnoDB在提交階段還有另外一種形式的并發(fā)瓶頸，就是刷寫操作造成的密集I/O操作。innodb_commit_concurrency變量決定了某一時刻有多少線程能進行提交。當系統(tǒng)有大量線程狀況不佳時，可以嘗試將該變量增加。u 保持默認值，即不限制并發(fā)提交線程數(shù)。max_length_for_sort_datamax_sort_lengthMySQL有兩種文件排序算法，如果需要進行排序的列的總大小超過了max_length_for_sort_data定義的字節(jié)，MySQL就會使用雙路排序，反之就會選擇單路排序，雙路排序需要兩次訪問數(shù)據(jù)，尤其是第二次讀取操作會導致大量的隨機I/O操作。如果將并不需要的Columns也取出來，就會極大地浪費排序過程所需要的內(nèi)存，為了盡可能地提高排序性能，盡量使用第二種排序算法，所以在查詢中僅取出需要的列是非常有必要的。u 對于本系統(tǒng)，默認值足夠大，能滿足性能要求。Aborted_clients如果Aborted_clients變量隨時間增加，那么就要確定是否正常地關(guān)閉了連接。如果不是，就要檢查網(wǎng)絡(luò)性能，并且檢查max_allowed_packet變量，超多了max_allowed_packet的查詢會被強制地中斷。Aborted_connectsAborted_connects變量的值應接近于0，否則就可能有網(wǎng)絡(luò)問題，有幾個被中斷的連接是正常的。例如，試著從錯誤的主機連接、使用了錯誤的用戶名和密碼，或者定義了無效的數(shù)據(jù)庫，都會發(fā)生這樣的情況。u 觀察本系統(tǒng)10分鐘內(nèi)的Aborted_clients變化，一直保持為0，說明沒有連接方面的異常情況，可以定期觀察該變量分析連接問題。binlog_cache_sizeBinlog_cache_disk_useBinlog_cache_use當使用事務的表存儲引擎InnoDB時，所有未提交的二進制日志會被記錄到一個緩存中，等該事務提交時直接將緩沖中的二進制日志寫入二進制日志文件，而該緩沖的大小由binlog_cache_size決定，默認大小為32KB。此外，binlog_cache_size是基于會話的，當一個線程開始一個事務時，MySQL會自動分配一個大小為binlog_cache_size的緩存，因此該值不能設(shè)置過大。當一個事務的記錄大于設(shè)定的binlog_cache_size時，MySQL會把緩沖中的日志寫入一個臨時文件中，因此該值又不能設(shè)得太小。通過查看binlog_cache_use、binlog_cache_disk_use的狀態(tài),可以判斷當前binlog_cache_size的設(shè)置是否合適。Binlog_cache_use記錄了使用緩沖寫二進制日志的次數(shù)，binlog_cache_disk_use記錄了使用臨時文件寫二進制日志的次數(shù)。如果binlog_cache_disk_use與Binlog_cache_use之間的比值很大，就應該增加binlog_cache_size的值，只要保證大部分的事務都在二進制日志緩存里就可以了。u binlog_cache_disk_use/Binlog_cache_use比值非常小，說明本系統(tǒng)絕大部份事務都能下入在二進制日志緩存。Created_tmp_disk_tablesCreated_tmp_tables每次創(chuàng)建臨時表時，Created_tmp_tables增加，如果是在磁盤上創(chuàng)建臨時表，則Created_tmp_disk_tables也會增加，通?？梢酝ㄟ^Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables)來判斷基于內(nèi)存的臨時表利用率。如果Created_tmp_disk_tables太大，則需要檢查并優(yōu)化查詢語句，或有可能是tmp_table_size和max_heap_table_size不夠大。Created_tmp_disk_tables / Created_tmp_tables 100% （理想值 85%）。MySQL服務器實際上允許max_connections+1個客戶端進行連接。額外的連接保留給具有SUPER權(quán)限的賬戶。通過為系統(tǒng)管理員而不是普通用戶授予SUPER權(quán)限（普通用戶不應具有該權(quán)限），系統(tǒng)管理員能夠連接到服務器來診斷問題，即使已連接的無特權(quán)客戶端數(shù)已達到最大值也同樣。u 本系統(tǒng)中設(shè)置的最大連接數(shù)是600，而響應的連接數(shù)是601，應適當增加Max_connections變量的值。Open_files和Open_files_limit如果Open_files的值與Open_files_limit的值較為接近，那就應該增加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)單個進程最大允許打開文件句柄（文件描述符）。u open_files_limit 取三個值中的最大值 ，設(shè)置3000較合理，不需要調(diào)整。Select_full_join全聯(lián)接是無索引聯(lián)接，它真正影響性能，最好能避免全聯(lián)接，即使是每分鐘一次也較多，如果聯(lián)接沒有索引，則最好能優(yōu)化查詢和索引。select_full_range_join如果select_full_range_join的值過高，就說明運行了許多使用了范圍查詢聯(lián)接表，有時大的范圍查詢也會比較慢，可以從中進行優(yōu)化。Select_range_checkSelect_range_check變量記錄了在聯(lián)接時，對每一行數(shù)據(jù)重新檢查索引的查詢計劃的數(shù)量，它的性能開銷很大，如果該值較高或正在增加，則說明一些查詢沒有找到好索引。u 上述變量目前正常，如果發(fā)生明顯變化，則結(jié)合慢查詢?nèi)罩靖櫲?lián)接性能較差的查詢。Slow_launch_threads該變量如果較大則說明某些因素正在延遲聯(lián)接的新線程，服務器存在一些問題。它通常表示系統(tǒng)過載，導致操作系統(tǒng)不能給新創(chuàng)建的線程分配時間片。Table_locks_waitedTable_locks_waited變量顯示了有多少表被鎖住了并且導致服務器級的鎖等待，InnoDB的行級鎖不會使該變量增加。如果該值較高并且正在增加，則說明存在嚴重的并發(fā)瓶頸，這時應該考慮使用InnoDB或另外使用行級鎖的存儲引擎，或者手動對大表進行分區(qū)，并優(yōu)化查詢，啟用并發(fā)插入或?qū)︽i設(shè)置進行優(yōu)化。u 本系統(tǒng)該變量在半小時內(nèi)變化幅度不超過3，不必調(diào)整。2. MySQL優(yōu)化策略2.1. 索引策略索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，它對于高性能非常關(guān)鍵，因此建立索引是現(xiàn)實中性能問題的首要原因。索引在數(shù)據(jù)越大的時候越重要。規(guī)模小，負載輕的數(shù)據(jù)庫即使沒有索引，也能有好的性能，但是當數(shù)據(jù)增加的時候，性能就會下降。MySQL有多種類型類型的索引，它們各有自己的性能特點。索引是在存儲引擎層實現(xiàn)的，而不是在服務器層，因此它們并不是標準化的，每個引擎的索引工作方式略有不同，并不是所有的引擎都支持所有類型的索引。即使多個引擎支持同樣的索引，他們的實現(xiàn)方式也可能有所不同。MySQL索引類型的各自特點可以查閱相關(guān)資料去進一步了解。即使已經(jīng)了解了關(guān)于索引的知識，但也許還不知道如何從實際的表開始。雖然通常情況下是檢查系統(tǒng)中最常運行的查詢，但往往性能瓶頸可能就出現(xiàn)在不那么經(jīng)常進行的插入或更新操作，要避免在不知道什么查詢會使用索引之前就創(chuàng)建它這種常見錯誤，并且要考慮是否所有的索引能形成一個優(yōu)化的配置。有時只從查詢就可以知道需要什么索引，只要把它們加上就可以了。但是有時各種類型的查詢，卻不能找