數(shù)據(jù)庫(kù)優(yōu)化可以說(shuō)是后臺(tái)開發(fā)中永恒的話題，數(shù)據(jù)庫(kù)的性能通常是整個(gè)服務(wù)吞吐量的瓶頸之所在。

1. 索引概述

InnoDB中的表都是按照主鍵順序組織存放的，這種組織方式稱之為索引組織表，對(duì)比于MyISAM的表組織方式。在InnoDB中每張表都必須有一個(gè)主鍵，如果在創(chuàng)建表的時(shí)候沒有顯式定義主鍵，則InnoDB首先會(huì)判斷表中是否有非空的唯一索引，如果有則將該列作為主鍵；否則InnoDB會(huì)自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針作為主鍵。除主鍵之外，InnoDB還可以有輔助索引，而輔助索引頁(yè)中僅僅存放鍵值和指向數(shù)據(jù)頁(yè)的偏移量，而不像主鍵數(shù)據(jù)頁(yè)存儲(chǔ)的是一個(gè)完整的行記錄。

InnoDB存儲(chǔ)引擎中，所有的數(shù)據(jù)都被邏輯地存放在一個(gè)表空間中，表空間又被分為段(Segment)、區(qū)(Extent)、頁(yè)(Page)組成，其中段由存儲(chǔ)引擎自動(dòng)管理，區(qū)的大小固定為1M，然后默認(rèn)情況下頁(yè)的大小為16KB，也就是一個(gè)區(qū)總共有64個(gè)連續(xù)的頁(yè)組成。不過(guò)在MySQL5.6開始，頁(yè)的大小可以設(shè)置為4K、8K了，設(shè)置成4K除了可以提高磁盤的利用率之外，對(duì)于現(xiàn)代SSD硬盤將更加合適，不過(guò)這中更新比較的麻煩，需要將輸入導(dǎo)出后再重新導(dǎo)入，一般的備份恢復(fù)工具都是原樣復(fù)制數(shù)據(jù)，沒有辦法支持變更頁(yè)大小。

默認(rèn)的B+樹索引其查找次數(shù)(效率)取決于B+樹的高度，生產(chǎn)環(huán)境下一般樹高為3~4層，即查詢一條記錄需要經(jīng)過(guò)3~4個(gè)索引頁(yè)，而且B+樹索引并不能找到一個(gè)給定鍵值的具體行，其只能根據(jù)鍵和索引找到數(shù)據(jù)行所在的頁(yè)，然后數(shù)據(jù)庫(kù)把對(duì)應(yīng)的頁(yè)讀取到內(nèi)存，再在內(nèi)存中執(zhí)行查找，并最后得到需要查詢的數(shù)據(jù)。InnoDB還會(huì)監(jiān)控對(duì)表上各索引頁(yè)的查詢操作，如果觀察到通過(guò)建立hash索引可以帶來(lái)速度提升，則會(huì)根據(jù)訪問(wèn)頻率和訪問(wèn)模式自動(dòng)為部分熱點(diǎn)頁(yè)建立hash索引，這個(gè)過(guò)程稱之為自適應(yīng)哈希索引，而且該過(guò)程是人為無(wú)法干預(yù)、存儲(chǔ)引擎自動(dòng)實(shí)現(xiàn)的。

使用索引的一大禁忌是不要在引用索引列的時(shí)候使用函數(shù)，比如max(id)、id+3>5等，或者隱式的數(shù)據(jù)類型轉(zhuǎn)換操作，這樣會(huì)導(dǎo)致索引失效導(dǎo)致全掃描。

2. 在線修改數(shù)據(jù)表

在MySQL 5.5之前修改表結(jié)構(gòu)、或者創(chuàng)建新索引的時(shí)候，需要經(jīng)過(guò)：先鎖定原始表，創(chuàng)建一張新的臨時(shí)表(臨時(shí)使用tmpdir路徑，確保有足夠空間可用)，然后把原表中的數(shù)據(jù)導(dǎo)入到新的臨時(shí)表中，接著刪除原表，最后再把臨時(shí)表重新命名為原來(lái)的表名。所以修改表結(jié)構(gòu)需要注意，將對(duì)同一個(gè)表的ALTER TABLE多個(gè)操作合并到一條語(yǔ)句中，減少上述重復(fù)的步驟。同時(shí)，針對(duì)修改列名、修改數(shù)值類型的表示長(zhǎng)度INT(3)->INT(10)、修改數(shù)據(jù)表注釋、向ENUM增加新的類型、修改數(shù)據(jù)表名這些操作不需要將數(shù)據(jù)表中的所有記錄都復(fù)制到臨時(shí)表。

新版MySQL支持Fast Index Creation，具體說(shuō)來(lái)就是對(duì)于新輔助索引的創(chuàng)建，InnoDB會(huì)對(duì)要?jiǎng)?chuàng)建索引的表上一個(gè)S鎖，使該表以只讀的可用性提供服務(wù)，由于不需要重新創(chuàng)建表、拷貝數(shù)據(jù)，因而輔助索引的創(chuàng)建速度也快很多；刪除索引的時(shí)候InnoDB只需更新內(nèi)部試圖標(biāo)記輔助索引的空間為可用，同時(shí)刪除MySQL數(shù)據(jù)庫(kù)內(nèi)部試圖上對(duì)應(yīng)表的索引定義即可。

MySQL 5.6的版本支持Online DDL，允許在輔助索引創(chuàng)建的同時(shí)，還允許對(duì)表同時(shí)執(zhí)行諸如INSERT、UPDATE、DELETE等DML操作而不會(huì)被阻塞，其原理是在執(zhí)行索引創(chuàng)建或者刪除操作的時(shí)候，將INSERT、UPDATE、DELETE這類的操作日志先記錄到一個(gè)叫做“在線修改日志”的內(nèi)存空間中，當(dāng)索引完成后再重新應(yīng)用這些更新到表上，以此達(dá)到數(shù)據(jù)的一致性。不過(guò)“在線修改日志“只存留在內(nèi)存中，默認(rèn)大小是128MB，如果修改表結(jié)構(gòu)時(shí)候DML操作太多，會(huì)導(dǎo)致該空間不夠用而撤銷修改。

3. 創(chuàng)建索引

創(chuàng)建索引的時(shí)候講求一個(gè)Cardinality指標(biāo)，該值表示索引中唯一值的估計(jì)數(shù)目，理想情況下該值除以表行數(shù)應(yīng)該盡可能接近1，否則表示該列選擇性太低而應(yīng)該考慮刪除該索引。 對(duì)Cardinality的統(tǒng)計(jì)是使用采樣方式進(jìn)行估算的，當(dāng)表的修改數(shù)目超過(guò)總記錄的1/16、或者修改總次數(shù)超過(guò)20億次，則會(huì)隨機(jī)選擇8個(gè)數(shù)據(jù)頁(yè)重新統(tǒng)計(jì)該值，不過(guò)通過(guò)ANALYZE TABLE命令可以強(qiáng)制讓數(shù)據(jù)庫(kù)重新收集相關(guān)的統(tǒng)計(jì)信息。

實(shí)踐中OLTP和OLAP對(duì)索引的要求是有差異的，在OLTP應(yīng)用中查詢操作通常只從數(shù)據(jù)庫(kù)返回很小部分?jǐn)?shù)據(jù)集，此時(shí)根據(jù)查詢條件選擇高區(qū)分度的列來(lái)創(chuàng)建索引是很有意義的；對(duì)于OLAP應(yīng)用通常都需要返回大批量的數(shù)據(jù)，很多情況下建立索引意義不是很大，因?yàn)榇罅繑?shù)據(jù)返回的話往往全表順序掃描效率更高，不過(guò)OLAP中對(duì)時(shí)間創(chuàng)建索引是很常見的操作。

4. 覆蓋索引

表示直接從輔助索引中就可以得到需要的查詢記錄，而不需要再?gòu)木鄞厮饕�中查詢行記錄。使用覆蓋索引的好處是輔助索引不包含整行記錄，所以索引大小會(huì)遠(yuǎn)遠(yuǎn)小于聚簇索引，單個(gè)索引頁(yè)就可以存儲(chǔ)更多的索引項(xiàng)，那么訪問(wèn)索引本身的操作就可以減少順序IO操作了。有些情況，比如在MySQL中SELECT COUNT(*) FROM t;優(yōu)化器是可以選擇使用輔助索引來(lái)優(yōu)化查詢速度的，因?yàn)榭梢栽L問(wèn)更少的索引頁(yè)就可以統(tǒng)計(jì)到查詢結(jié)果了。

如果SELECT列不能使用覆蓋索引完成，那么除了在輔助索引上查到指定記錄后，還需要進(jìn)行一次書簽訪問(wèn)才能查找到整行中其他列的數(shù)據(jù)，并且此時(shí)的查找將是成本很高的隨機(jī)離散讀操作(相對(duì)于傳統(tǒng)機(jī)械磁盤)。

所以如果優(yōu)化器覺得需要返回的數(shù)據(jù)量很少，則優(yōu)化器還是可能會(huì)選擇使用輔助索引外加訪問(wèn)聚簇索引的方式來(lái)返回記錄的；但是當(dāng)訪問(wèn)數(shù)據(jù)量占整個(gè)表記錄中挺大一部分的時(shí)候(比如20%)，則優(yōu)化器可能會(huì)選擇全表掃描的方式來(lái)查找數(shù)據(jù)，因?yàn)槿�表順序讀的代價(jià)可能比大量隨機(jī)讀的效率要高。大部分時(shí)候優(yōu)化器都能做的不錯(cuò)，不過(guò)當(dāng)用戶有對(duì)索引的使用有足夠信心的時(shí)候還是可以影響優(yōu)化器執(zhí)行計(jì)劃的生成的，比如：可以使用USE INDEX的方式來(lái)提示優(yōu)化器使用某個(gè)索引，不過(guò)實(shí)際上優(yōu)化器還是會(huì)根據(jù)自己的判斷確定是否需要使用該索引；而通過(guò)FORCE INDEX則會(huì)強(qiáng)制選擇使用該索引；使用IGNORE INDEX會(huì)使優(yōu)化器不能使用指定的索引，這通常可以誘導(dǎo)觸發(fā)執(zhí)行全表掃描。

5. Multi-Range Read(MRR)優(yōu)化

為了防止非覆蓋索引取數(shù)據(jù)的時(shí)候造成的大量隨機(jī)I/O，MyISAM和InnoDB會(huì)將查詢到的輔助索引存放在一個(gè)緩存中，然后將他們通過(guò)主鍵進(jìn)行排序，并按排序后的主鍵進(jìn)行順序書簽查找。通過(guò)這種方式可以將低效隨機(jī)訪問(wèn)轉(zhuǎn)化為高效順序數(shù)據(jù)訪問(wèn)，而且同一數(shù)據(jù)塊確保只需要被訪問(wèn)一次，同時(shí)也減少緩沖池中頁(yè)被替換的次數(shù)，所以可以帶來(lái)查詢性能的極大提升。

MySQL5.6開始支持該項(xiàng)優(yōu)化，使用的時(shí)候需要SET optimizer_switch=’mrr=on|off’的方式打開。MRR特性可以用于range、ref、eq_ref類型的查詢操作，當(dāng)查詢使用到該特性的時(shí)候就可以在Extra看到Using MRR提示了，當(dāng)在有表連接的情況下，如果連接鍵是被驅(qū)動(dòng)表的主鍵的時(shí)候，也會(huì)先基于驅(qū)動(dòng)表的連接鍵進(jìn)行排序，按照這個(gè)順序就可以MRR按照被驅(qū)動(dòng)表的主鍵訪問(wèn)數(shù)據(jù)了。

從上面的介紹看到MRR是一個(gè)思路簡(jiǎn)單但是卻很重要的優(yōu)化，但是在某些情況下使用也可能會(huì)有負(fù)面效應(yīng)。當(dāng)表的數(shù)據(jù)量很小，大部分?jǐn)?shù)據(jù)也都被緩存的時(shí)候，使用MRR不會(huì)帶來(lái)隨機(jī)訪問(wèn)的收益，反而會(huì)因?yàn)轭~外的排序操作增加資源消耗；當(dāng)限制只需要返回LIMIT n的時(shí)候，這種優(yōu)化會(huì)讀取排序很多不需要的索引，性能反而會(huì)降低；排序使用的內(nèi)存空間大小由mrr_buffer_size設(shè)定的，如果該內(nèi)存較小但是待排序的索引數(shù)量大的時(shí)候，就需要使用磁盤輔助進(jìn)行多塊排序歸并，這也會(huì)降低性能。

6. Index Condition Pushdown(ICP)優(yōu)化

老舊數(shù)據(jù)庫(kù)版本只有索引可用的限制條件才會(huì)被傳輸?shù)酱鎯?chǔ)引擎層，在新版本開啟ICP優(yōu)化的時(shí)候，針對(duì)選用索引涉及到的數(shù)據(jù)列條件就都會(huì)被傳輸?shù)酱鎯?chǔ)引擎層，所以在支持ICP特性后，存儲(chǔ)引擎在處理索引的同時(shí)就可以判斷是否可以通過(guò)下推的選擇條件對(duì)部分記錄直接進(jìn)行過(guò)濾操作了。所以在老版本的數(shù)據(jù)庫(kù)，都是存儲(chǔ)引擎對(duì)索引可以直接使用的條件進(jìn)行操作，然后再將這些數(shù)據(jù)傳遞給MySQL引擎，這樣就會(huì)涉及到大量數(shù)據(jù)條目的讀取、傳遞和篩選工作，這時(shí)候在Extra中肯定會(huì)看到Using where的提示，因?yàn)镸ySQL引擎對(duì)存儲(chǔ)引擎?zhèn)鬟f來(lái)的數(shù)據(jù)進(jìn)行了篩選加工；現(xiàn)在將索引涉及到的篩選條件下推放到了存儲(chǔ)引擎層，就大大減少了上面的操作任務(wù)。

該功能可以使用SET optimizer_switch=’index_condition_pushdown=on|off’的方式打開或者關(guān)閉。ICP優(yōu)化可以用于range、ref、req_ref、ref_or_null類型的查詢，當(dāng)查詢使用到該特性的時(shí)候可以在Extra看到Using index condition。

7. 索引合并

當(dāng)查詢WHERE中羅列有多個(gè)條件，他們都可以使用不同的索引進(jìn)行優(yōu)化查詢的時(shí)候，如果優(yōu)化器發(fā)現(xiàn)某一個(gè)索引返回的記錄相比其他索引顯著的要少，那么執(zhí)行計(jì)劃就會(huì)選用這個(gè)索引；而如果優(yōu)化器發(fā)現(xiàn)多個(gè)索引都不高效的時(shí)候，優(yōu)化器會(huì)將這些查詢條件分離，用各自的索引分別獨(dú)立執(zhí)行檢索，最后再將多個(gè)結(jié)果集合進(jìn)行合并后返回。當(dāng)然，這種情況優(yōu)化器也可能使用全表掃面的方式處理。

本文完！

參考

標(biāo)簽： Mysql ssd 數(shù)據(jù)庫(kù)

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。