Mysql_索引優(yōu)化_6

前綴壓縮索引

????

????對(duì)于前綴壓縮索引的使用，是在MyISAM中使用，為了讓更多的索引可以放入內(nèi)存中，在某些情況下可以非常明顯地提高性能。默認(rèn)情況下只會(huì)對(duì)字符串進(jìn)行壓縮，可以通過參數(shù)設(shè)置對(duì)整數(shù)進(jìn)行壓縮。

????

MyISAM的壓縮處理

????先保存索引塊的第一個(gè)值，然后將其他值和第一個(gè)值進(jìn)行比較，得到相同前綴的字節(jié)數(shù)和剩余的不同后綴部分，然后將這部分存儲(chǔ)起來，如果看官有看雷記之前的更新會(huì)發(fā)現(xiàn)，這個(gè)處理與MyISAM對(duì)行指針采用的前綴壓縮相同。

????對(duì)于壓縮要說明的一點(diǎn)是，它可以帶來更多的空間節(jié)約，但是對(duì)應(yīng)的代價(jià)則是某些操作會(huì)變慢，這源于每個(gè)值得壓縮前綴都依賴于嵌滿的值，因此也注定MyISAM在查找時(shí)無法在索引塊里使用二分查找，只能從頭開始掃描，如果是倒序掃描的話，所有查找某一行得操作平均下來都需要掃描半個(gè)索引塊，在CPU密集型應(yīng)用中，進(jìn)行得掃描是隨機(jī)查早，壓縮索引使MyISAM在索引查找上慢好幾倍，類比到壓縮索引的倒序掃描就更慢，另外也是由于壓縮索引可能只需要很小的磁盤空間，在IO密集型應(yīng)用中對(duì)于一些查詢帶來得收益會(huì)比成本高很多，再創(chuàng)建表的CREATE TABLE語句中以PACK_KEYS參數(shù)進(jìn)行索引壓縮方式的控制。

關(guān)于CPU密集型、IO密集型

????CPU密集型，為計(jì)算密集型，一般指系統(tǒng)的硬盤、內(nèi)存性能相對(duì)CPU要好很多，系統(tǒng)運(yùn)作大多數(shù)狀況是CPU loading100%，CPU要讀寫IO，IO在很短的時(shí)間完成，因?yàn)镃PU的輪片機(jī)制要處理很多運(yùn)算，所以致使CPU 的loading非常高。

????IO密集型，一般指系統(tǒng)的CPU性能相對(duì)硬盤、內(nèi)存要好很多，系統(tǒng)的運(yùn)作大部分情況下CPU在等IO的讀寫，CPU的使用率不高，簡(jiǎn)單來說便是大量的輸入輸出，如讀、寫文件，傳輸文件，網(wǎng)絡(luò)請(qǐng)求此類。

????對(duì)于CPU密集型應(yīng)用我們會(huì)考慮其處理場(chǎng)景是否真的有必要進(jìn)行多線程處理，因?yàn)榇嬖诰€程切換帶來的上下文切換，會(huì)出現(xiàn)時(shí)間、資源消耗，一般不建議開啟多線程，對(duì)于這一問題的根本原因，我們?cè)诖讼嚷褚粋€(gè)伏筆，待我們進(jìn)行多線程并發(fā)優(yōu)化時(shí)再細(xì)作分析。對(duì)于線程間上下文切換的細(xì)節(jié)，各位看官可以到雷記個(gè)人博客關(guān)于多線程并發(fā)的系列帖中了解。

關(guān)于冗余、未使用索引的一些坑

????mysql允許在相同的列上創(chuàng)建多個(gè)索引的初衷，雷記認(rèn)為是Mysql為多字段索引創(chuàng)建放開的限制，但是很不幸地是我們?cè)趯?shí)際中因?yàn)槟X洞太大，搞了很多在相同列上相同順序、相同類型地索引，mysql對(duì)于這些重復(fù)地索引進(jìn)行單獨(dú)維護(hù)，優(yōu)化器在優(yōu)化查詢的時(shí)候會(huì)對(duì)這些索引逐個(gè)考慮，所以勢(shì)必會(huì)影響性能，所以建議發(fā)現(xiàn)這種問題地索引要立即索引，下面舉一個(gè)最為常見也最為普遍地問題：

create table rookie（

? ? id int not null primary key，

? ? a int not null，

? ? b int not null，

? ? unique（id），

? ? index（id）

）engine=Innodb；

????操作者的本意為創(chuàng)建一個(gè)rookie表，主鍵為id，先加上一個(gè)唯一限制，然后再加上索引以供查詢，實(shí)際上mysql的唯一限制和主鍵限制都是通過索引實(shí)現(xiàn)，所以這個(gè)寫法實(shí)際上在相同的id列上創(chuàng)建了三個(gè)重復(fù)的索引，這種場(chǎng)景只有一個(gè)理由可以解釋，那便是需要不同類型的索引來滿足不同的查詢需求，否則毫無意義。

????對(duì)于冗余索引，通常發(fā)生在為表添加新索引的時(shí)刻，如新增一個(gè)索引(A,B)，并非擴(kuò)展以后索引(A)，或者將索引擴(kuò)展為(A,id)，id作為主鍵，在innodb引擎情況時(shí)主鍵列已經(jīng)包含在二級(jí)索引中，以上均為冗余索引。正常情況下，雷記建議盡量擴(kuò)展已有索引而不是創(chuàng)建新的索引，這便存在一個(gè)關(guān)于取舍的博弈，在擴(kuò)展已有索引會(huì)導(dǎo)致索引變得太大，進(jìn)而影響使用其他地方使用這個(gè)索引的查詢性能減慢問題，所以需要操作前充分調(diào)研一番是有必要的，但是這并不是總是很現(xiàn)實(shí)，因此我們需要冗余索引。

????有過數(shù)據(jù)經(jīng)驗(yàn)的人會(huì)發(fā)現(xiàn)，表中的索引越多數(shù)據(jù)的插入速度就會(huì)越慢，新增索引會(huì)導(dǎo)致insert、update、delete的速度下降，這種現(xiàn)象在新增索引達(dá)到內(nèi)存瓶頸時(shí)尤其明顯，當(dāng)我們被性能問題困擾至臨界點(diǎn)時(shí)便會(huì)要找出這些冗余索引，然后刪掉它們，對(duì)此借助外部工具比自己分析寫查詢?cè)L問information_schema表要來的實(shí)惠更多，這里雷記推薦common_schema或pt_duplicate_key_checker來分析表結(jié)構(gòu)找出冗余和重復(fù)索引，在刪除時(shí)要注意上一次更新提到的索引掃描-數(shù)列常量化是否因?yàn)楫?dāng)前存儲(chǔ)引擎索引存儲(chǔ)結(jié)構(gòu)會(huì)發(fā)生失效的問題，可以了解一下pt_upgrade工具的使用，來檢測(cè)執(zhí)行計(jì)劃中的索引變更現(xiàn)象。

關(guān)于索引與表的維護(hù)

????前文書里我們對(duì)合適索引的選擇與使用進(jìn)行了分析，現(xiàn)在我們來了解一下更為關(guān)鍵的部分-維護(hù)工作，大致可以分為三個(gè)階段：

找到并修復(fù)損壞的表

????表損壞一般是由于硬件問題，mysql本身缺陷或者操作系統(tǒng)非正常運(yùn)行導(dǎo)致，索引也會(huì)被破壞，損壞的索引會(huì)查詢返回錯(cuò)誤結(jié)果、主鍵沖突等異常問題，甚至更惡劣地?cái)?shù)據(jù)庫崩潰，在遭遇到古怪問題時(shí)，可以嘗試check table檢查是否發(fā)生表損壞，在發(fā)生問題時(shí)盡量?jī)?yōu)先考慮數(shù)據(jù)backup，然后再嘗試?yán)胊lter等命令恢復(fù)表結(jié)構(gòu)，這樣可以避免數(shù)據(jù)的全丟失，至少給自己一次改過地機(jī)會(huì)，這種滋味說實(shí)話挺苦澀地。

????

????由于innodb的設(shè)計(jì)保證了其不容易被損壞，所以當(dāng)innodb引擎的表發(fā)生損壞時(shí)一定是出現(xiàn)了嚴(yán)重錯(cuò)誤，類似數(shù)據(jù)庫硬件地內(nèi)存、硬盤問題，DBA地錯(cuò)誤操作，不存在執(zhí)行查詢讓innodb表損壞，如果某條查詢導(dǎo)致innodb數(shù)據(jù)損壞，則是遇到了bug，這次執(zhí)行的查詢并非根本原因。

????

????如果真的不幸發(fā)生了數(shù)據(jù)損壞，不要妄圖簡(jiǎn)單的修復(fù)后繼續(xù)使用，而是要排查出真正地問題所在永久的把問題解決掉，環(huán)境地?cái)?shù)據(jù)有時(shí)并不會(huì)給我們提供太多地試錯(cuò)機(jī)會(huì)，關(guān)于innodb引擎下地恢復(fù)數(shù)據(jù)雷記后續(xù)會(huì)安排更新進(jìn)行分析。

? ?

維護(hù)準(zhǔn)確的索引統(tǒng)計(jì)信息

????

????mysql查詢優(yōu)化器通過records_in_range()和info()這兩個(gè)api了解存儲(chǔ)引擎內(nèi)索引值地分布信息，以決定如何使用索引。

????records_in_range：向存儲(chǔ)引擎?zhèn)魅脒吔缰但@取范圍內(nèi)的記錄條數(shù)；

????info:獲取返回各類型地?cái)?shù)據(jù)，包括索引基數(shù)；

????

????如果存儲(chǔ)引擎向優(yōu)化器提供的掃描行信息不準(zhǔn)確，優(yōu)化器會(huì)使用索引統(tǒng)計(jì)信息估算掃描行數(shù)，在表沒有統(tǒng)計(jì)信息或者統(tǒng)計(jì)信息不準(zhǔn)確時(shí)優(yōu)化器便會(huì)因此做出錯(cuò)誤的決定，對(duì)此可以使用ANALYZE TABLE來重新生成統(tǒng)計(jì)信息來避免這個(gè)問題；

減少索引和數(shù)據(jù)地碎片

????

????btree索引會(huì)出現(xiàn)碎片化問題，碎片化的索引以近似無序地方式存貯于磁盤上，由于btree地隨機(jī)磁盤訪問會(huì)導(dǎo)致查詢性能很低。

????表數(shù)據(jù)存儲(chǔ)的碎片化分為三種情況：

????????行碎片：數(shù)據(jù)行被存儲(chǔ)為多個(gè)地方地多個(gè)片段中；

????????行間碎片：邏輯上順序的頁，或者行在磁盤上不是順序存儲(chǔ)，十分影響全秒掃描地性能；

????????剩余空間碎片：數(shù)據(jù)頁中有大量的空余空間，會(huì)導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù)；

????對(duì)這類問題可以采用OPTIMIZE TABLE或?qū)С?導(dǎo)入地方式重新整理數(shù)據(jù)，對(duì)于MyISAM地情況可以通過排序算法重建索引消除碎片。對(duì)于不支持OPTIMIZE TABLE地存儲(chǔ)引擎，可以通過ALTER TABLE table ENGINE=engine;的方式重建表，對(duì)于開啟了expand_fast_index_creation參數(shù)的percona server可以消除表和索引的碎片化，對(duì)于標(biāo)準(zhǔn)版本的mysql只能消除表的碎片化，或者刪除所有索引，然后重建表可以模擬這個(gè)percona server的功能。

結(jié)語

????關(guān)于索引的優(yōu)化策略大致就是這樣，在mysql中和索引這般相似地精妙設(shè)計(jì)，可說是浩如煙海，還需多做研究。從之前地更新中可以發(fā)覺mysql的存儲(chǔ)引擎對(duì)于其行為操作有重要地決定作用，那么后續(xù)雷記找時(shí)間做一系關(guān)于mysql存儲(chǔ)引擎地分享，敬請(qǐng)期待。另外為了方便大家更好的交流問題和想法，雷記組建了一個(gè)交流群，有興趣加入的話，可以給公眾號(hào)留言。