就像是一本書的目錄一樣，Mysql提供了索引來提高我們對于數(shù)據(jù)的查詢速度袭蝗，而如何用好它唤殴，就需要了解它的一些規(guī)則和原理。

測試數(shù)據(jù)

索引的類型

索引有很多種類型到腥，可以為不同的場景提供更好的性能朵逝，索引是在存儲(chǔ)引擎實(shí)現(xiàn)的而不是服務(wù)層，所以即使是同一種索引類型在不同的引擎中的實(shí)現(xiàn)可能也會(huì)有差異乡范。

常見的索引類別:

1. B-Tree 索引配名，最常用的索引類型啤咽，現(xiàn)在默認(rèn)都是用這個(gè)
2. 哈希索引，通過對行數(shù)據(jù)進(jìn)行哈希處理的一種索引類型
3. 空間數(shù)據(jù)索引渠脉，用于地理位置的索引
4. 全文索引宇整，用于關(guān)鍵字搜索的索引

索引的優(yōu)點(diǎn)

1. 索引大大減少了Mysql服務(wù)器層需要掃描的的數(shù)據(jù)量
2. 索引可以幫助服務(wù)器避免排序和使用臨時(shí)表
3. 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O

高性能的索引策略

想要正確的創(chuàng)建和使用索引，我們需要先知道索引的一些高性能策略

獨(dú)立的列

"獨(dú)立的列"是指索引列不能是表達(dá)式的一部分或者函數(shù)的參數(shù)芋膘。

下圖為測試結(jié)果:

獨(dú)立的列

通過上圖鳞青，我們知道id為索引列，當(dāng)它是表達(dá)式的一部分時(shí)为朋，沒有使用到索引臂拓。但是當(dāng)它單獨(dú)在比較符號(hào)的一側(cè)是，使用了索引习寸。性能上也有明顯的差距胶惰。

前綴索引和索引選擇性

有時(shí)候我們需要索引很長的字符列，這會(huì)讓索引變得大而且慢霞溪，除了將字符列的數(shù)據(jù)進(jìn)行哈希之外孵滞，我們還可以使用前綴索引來進(jìn)行優(yōu)化。

前綴索引是指取整個(gè)字符列的前面部分?jǐn)?shù)據(jù)來作為索引威鹿，這樣可以減低索引的空間大小剃斧，從而提高的索引效率。但是取字符列的前面幾位是一個(gè)問題忽你，如果位數(shù)太少幼东，那么就會(huì)降低索引的選擇性。索引的選擇性是指:不重復(fù)的索引值（也稱為基數(shù),cardinality）和數(shù)據(jù)表的記錄總數(shù)的比值科雳，選擇性越高則查詢效率越高根蟹。當(dāng)基數(shù)/記錄總數(shù)=1時(shí)，該索引的選擇性最好糟秘，例如主鍵简逮。

在不同的場景下，因?yàn)榇娴牟煌臄?shù)據(jù)尿赚，所以需要選擇的位數(shù)也不同散庶，所以需要我們自己通過測試來選擇使用的位數(shù)應(yīng)該是多少，可以通過下面的sql語句來進(jìn)行計(jì)算凌净。

select count(distinct left(字符列,位數(shù)))/count(*) from 表名;

也可以通過下面的語句悲龟，在不同位數(shù)之間進(jìn)行比較，當(dāng)發(fā)現(xiàn)隨著位數(shù)的提升冰寻，但是選擇性沒有明顯增加時(shí)须教，這個(gè)位數(shù)可能就比較適合改字符列了。

select 
count(distinct left(字符列,1))/count(*),
count(distinct left(字符列,2))/count(*),
count(distinct left(字符列,3))/count(*),
count(distinct left(字符列,4))/count(*) 
from 表名;

下面我將根據(jù)《高性能Mysql》書中例子進(jìn)行演示

完整字符列的選擇性:

完整字符列的選擇性

不同位數(shù)字符列的選擇性:

不同位數(shù)字符列的選擇性

通過上面兩張圖的對比我們可以知道斩芭，當(dāng)位數(shù)到6的時(shí)候轻腺，選擇性的提升幅度已經(jīng)很微小了乐疆，所以這時(shí)候我們就可以在city字段上創(chuàng)建長度為6的前綴索引:

alter table city_demo add key(city(6));

但是我們不能通過前綴索引進(jìn)行order by，下圖并沒有使用到索引

前綴索引排序

避免為各個(gè)列建立單獨(dú)索引

在Mysql的最新版本中，Mysql能夠同時(shí)使用單獨(dú)列索引，并將結(jié)果進(jìn)行算法合并，也將這個(gè)稱為索引合并策略。這種算法有三種變種: OR條件的聯(lián)合(union)嚎花，AND條件的相交(intersection)，組合前面兩種情況的聯(lián)合及相交)谈火。

union

選擇合適的索引列順序

不同類型的索引蜘犁，它的存儲(chǔ)結(jié)構(gòu)是不一樣的，限制也不一樣止邮，所以也決定了我們在定義索引時(shí)这橙，需要選擇合適的索引列順序，才能真正的將索引的功能發(fā)揮出來导披，下面主要介紹B-Tree屈扎。

B-Tree限制

1. 如果不是按照索引的最左列開始查詢，則無法使用索引
2. 不能跳過索引的列
3. 如果查詢中有某個(gè)列的范圍查詢撩匕，則其右邊所有列都無法使用索引優(yōu)化查找鹰晨。
4. 只有當(dāng)索引的列順序和order by子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時(shí)止毕，才能使用索引來對結(jié)果進(jìn)行排序模蜡。

通過這些B-Tree的限制應(yīng)該能明白，定義索引列的順序決定了你將如何使用索引扁凛。而這些規(guī)則試用于所有涉及索引的操作忍疾，例如基本查找、where子句谨朝、order by子句等

表結(jié)構(gòu)和索引定義為:

索引定義

下面我們將主要使用rental_date這個(gè)索引進(jìn)行舉例:

例子1:

例子1

例子2:

例子2

例子3:

例子3

例子4:

例子4

例子5:

例子5

例子6:

例子6

這個(gè)例子中兩種排序字段的排序方向不一致猴凹，所以無法使用索引進(jìn)行排序。

例子7:

例子7

在這個(gè)例子中where子句使用了范圍查詢岭皂，所以導(dǎo)致了無法使用索引進(jìn)行排序

冗余和重復(fù)索引

Mysql允許在相同列上創(chuàng)建多個(gè)索引郊霎，通過上面的那些例子中就能發(fā)現(xiàn)一些字段存在于不同的索引中。如果一個(gè)索引是另一個(gè)索引的最左前綴爷绘，那么這個(gè)索引就冗余索引书劝，這樣的索引是沒有什么意義的。過多的索引會(huì)導(dǎo)致數(shù)據(jù)庫在修改表數(shù)據(jù)時(shí)變慢土至，并且增加資源的占用购对。通過上面的例子也可以發(fā)現(xiàn)，索引太多也會(huì)提升使用索引的復(fù)雜度陶因。

索引和鎖

下面有兩條語句

begin;
select actor_id from actor where actor_id < 5 and actor_id != 1 for update;

begin;
select actor_id from actor where actor_id=1 for update;

按照預(yù)期來說骡苞，上面那條語句應(yīng)該只會(huì)鎖2-4行的數(shù)據(jù)，那么下面那條語句應(yīng)該不會(huì)阻塞，但是在我當(dāng)前的版本解幽，還是出現(xiàn)了阻塞贴见。原因在actor_id < 5是一個(gè)索引范圍查詢， 導(dǎo)致InnoDB只會(huì)鎖定actor_id<5的行躲株，而不會(huì)執(zhí)行后面的條件片部，所以導(dǎo)致actor_id != 1是在服務(wù)層通過where進(jìn)行處理的。

例子8

通過上面的實(shí)例也能知道霜定，這條語句先用索引過濾出<5的數(shù)據(jù)档悠，然后在服務(wù)層用where進(jìn)行二次過濾，在索引層會(huì)將對應(yīng)的行鎖住望浩，導(dǎo)致第二條語句在執(zhí)行被阻塞辖所。

結(jié)尾

大部分人都知道當(dāng)Mysql查詢性能慢時(shí)，可以通過加索引的方式來提高性能曾雕。但是如何正確使用索引卻并沒有想象的那么簡單奴烙，需要考慮未來會(huì)如何使用，然后定義相應(yīng)的索引剖张，也需要對查詢語句進(jìn)行修改來保證正確能使用相應(yīng)的索引，也需要隨著業(yè)務(wù)的發(fā)展揩环，數(shù)據(jù)的變化搔弄，對索引進(jìn)行維護(hù)來保證索引的高性能。