高性能的索引策略

獨(dú)立的列

以下兩個(gè)錯(cuò)誤的語句,需要始終將索引列單獨(dú)放在比較符號(hào)的一側(cè)

SELECT actor_id FROM dskils.actor WHERE actor.id + 1 = 5;

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

前綴索引和索引的選擇性

有時(shí)候需要索引很長的字符列,這會(huì)讓索引變得大且慢.一個(gè)策略是前面提到過的模擬哈希索引.但有時(shí)候還不夠

??通常可以索引開始的部分字符,這樣可以大大節(jié)約索引空間,提高索引效率.但是這樣也會(huì)降低索引的選擇性(cardinality).索引的選擇性越高查詢效率越高,唯一索引的選擇性最高為1.

??一般情況下,某個(gè)列前綴的選擇性也是足夠高的,足以滿足查詢性能.但是對(duì)于 BLOB,TEXT 或者很長的 VARCHAR 類型的列,必須使用前綴索引,因?yàn)镸ySQL 不允許索引這些列的完整長度.

??訣竅在于要選擇足夠長的前綴索引以保證較高的選擇性,同事又不能太長(節(jié)約空間).前綴應(yīng)該足夠長,以使得前綴索引的選擇性接近于索引的整個(gè)列.

計(jì)算合適的前綴長度的另外一個(gè)方法就是計(jì)算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性。下面展示如何計(jì)算完整列的選擇性：

多列索引

選擇合適的索引列順序

當(dāng)不需要考慮排序和分組時(shí),最好將選擇性最高的列放到索引的最前列.具體的可以測(cè)試得出.

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型,而是一種數(shù)據(jù)存儲(chǔ)方式.InnoDB 的聚簇索引實(shí)際上在同一結(jié)構(gòu)中保存了 B-Tree 索引和數(shù)據(jù)行.

當(dāng)表有聚簇索引時(shí),他的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁中.術(shù)語"聚簇"表示數(shù)據(jù)行和相鄰的兼職緊湊的存儲(chǔ)在一起.因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方,所以一個(gè)表只有一個(gè)聚簇索引(不過覆蓋索引可以模擬多個(gè)聚簇索引的情況).

聚集的數(shù)據(jù)有以下重要的優(yōu)點(diǎn):

• 可以把相關(guān)的數(shù)據(jù)保存在一起.
• 數(shù)據(jù)訪問更快.因?yàn)榫鄞厮饕龑⑺饕蛿?shù)據(jù)存放在用一個(gè) B-Tree 中.
• 使用覆蓋索引掃描的查詢可以直接使用節(jié)點(diǎn)中的主鍵值

也有以下缺點(diǎn):

• 聚簇索引可以提高 I/O 的性能.如果將數(shù)據(jù)全部放在內(nèi)存中將沒有使用聚簇索引的必要.
• 插入速度嚴(yán)重依賴插入順序.
• 更新聚簇索引的代價(jià)很高.
• 基于聚簇索引的表在插入新行,或者主鍵被更新需要移動(dòng)行的時(shí)候可能面臨葉分裂的問題.葉分裂會(huì)導(dǎo)致表占用更多的磁盤空間
• 聚簇索引可能導(dǎo)致全表掃描變慢
• 二級(jí)索引可能比想象的更大
• 二級(jí)索引訪問需要兩次索引查找,而不是一次

覆蓋索引

如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù),那么還有什么必要再回表查詢呢?如果一個(gè)索引包含(或者說覆蓋)所有需要查詢的字段的值,我們就稱為覆蓋索引.

覆蓋索引有以下優(yōu)點(diǎn):

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，所以如果只需要讀取索引，就極大的減少數(shù)據(jù)訪問量。這對(duì)MyISAM尤其正確，因?yàn)镸yISAM能壓縮索引以變得更小.
• 因?yàn)樗饕前凑枕樞虼鎯?chǔ)的(單頁)，所以對(duì)于I/O密集型的范圍查詢會(huì)比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的I/O要少很多
• 由于InnoDB的聚簇索引，覆蓋索引對(duì)InnoDB特別有用。InnoDB的二級(jí)索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級(jí)主鍵能夠覆蓋查詢，則可以避免對(duì)主鍵索引的二次查詢

覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引、和全文索引都不能存儲(chǔ)列的值，所以MySQL只能使用B-Tree索引做覆蓋索引.

當(dāng)發(fā)起一個(gè)索引覆蓋的查詢時(shí)，在EXPLAIN的Extra列可以看到Usingindex的信息,另外需要注意觸發(fā)覆蓋索引的條件

使用索引來做排序

MySQL 有兩種方式可以生成有序的結(jié)果:通過排序操作;或者按索引順序掃描;如果 EXPLAIN 出來的 type 的值為 index, 則說明 MYSQLS使用了索引掃描來做排序.

• 排序操作: 將查找出來的結(jié)果使用排序算法進(jìn)行排序
• 按索引順序掃描:ORDER BY語句后跟著一個(gè)被索引的列，如此一來索引的順序就是索引對(duì)應(yīng)記錄的順序，這樣直接順著索引一直往下讀取記錄即可得到有序的結(jié)果。
• 隨機(jī)IO操作會(huì)大大拖慢執(zhí)行速度，導(dǎo)致按照索引掃描的執(zhí)行速度反而要比排序操作要慢。因此，在考慮使用按照索引掃描的方式去獲得有序結(jié)果，那么設(shè)計(jì)索引時(shí)必須要考慮索引覆蓋的情況

只有當(dāng)索引的列順序和 ORDER BY 字句的順序完全一致,并且所有列的排列方向(倒序或者正序)都一樣, MySQL 才能夠使用索引來對(duì)結(jié)果做排序.如果查詢需要關(guān)聯(lián)多張表時(shí),則只有當(dāng) ORDER BY 子句引用的字段全部為第一個(gè)表時(shí),才能使用索引做排序.ORDER BY子句和查找型查詢的限制是一樣的:需要滿足索引的最左前的要求;否則, MySQL 都需要執(zhí)行排序操作,從而無法利用索引排序.

有一種例外，ORDER BY后跟的字段可以不滿足最左前綴原則：當(dāng)前導(dǎo)量為常量的時(shí)候。這樣可以彌補(bǔ)索引的不足.

壓縮(前綴)索引

MyISAM 使用前綴壓縮來減少索引的大小,從而讓更多的索引可以放入內(nèi)存,默認(rèn)只壓縮字符串.

MyISAM 壓縮每一個(gè)索引塊的方法是先保存索引塊中的第一個(gè)值,然后將其他值和第一個(gè)值進(jìn)行比較得到相同前綴的字節(jié)數(shù)和剩余的不同后綴部分,把這部分存儲(chǔ)起來即可.

壓縮快可以使用更少的空間,代價(jià)是某些操作可能更慢.

冗余和重復(fù)索引

MySQL 允許在相同的列上創(chuàng)建多個(gè)索引,無論是有意的還是無意的.MySQL 需要單獨(dú)維護(hù)重復(fù)的索引,并且優(yōu)化器在優(yōu)化查詢的時(shí)候也需要逐個(gè)的進(jìn)行查詢考慮,這會(huì)影響性能.

重復(fù)索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引.

冗余索引通常發(fā)生在為表添加新索引的時(shí)候.例如有索引(A,B),又添加(A),或者(A,ID),因?yàn)?InnoDB 主鍵列已經(jīng)包含在二級(jí)索引中.

大多數(shù)時(shí)候都不需要冗余索引,但是如果擴(kuò)展索引會(huì)導(dǎo)致已有的索引變得太大從而影響查詢性能,可以新建索引.

未使用的索引

可以使用工具幫助定位未使用的索引.例如 Percona Toolkit中的 pt-index-usage,該公布工具不僅可以讀取查詢?nèi)罩静⑶覍?duì)日志中的每條查詢進(jìn)行 EXPALIN 操作.

索引和鎖

索引可以讓查詢鎖定更少的行.雖然 InnoDB 的行鎖效率很高,內(nèi)存使用很少,但是鎖定行的時(shí)候任然會(huì)帶來額外的開銷;其次鎖定超過需要的行會(huì)增加鎖爭用并減少并發(fā)性.

InnoDB 只有在訪問行的時(shí)候才會(huì)對(duì)其加鎖,而索引能減少 InnoDB 訪問的行數(shù),從而減少鎖的數(shù)量.但著只有當(dāng) InnoDB 在存儲(chǔ)引擎層能夠過濾掉所有不需要的行時(shí)才有效.否則數(shù)據(jù)傳輸?shù)椒?wù)器層以后, MySQL服務(wù)器才能應(yīng)用 WHERE 子句,這是已經(jīng)無法避免行鎖定了,只有到適當(dāng)?shù)臅r(shí)候才會(huì)釋放.

InnoDB 在二級(jí)索引上使用共享(讀)鎖,但訪問主鍵索引需要排他(寫)鎖.這消除了使用覆蓋索引的可能性,并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE或非鎖定查詢要慢得多.