一、索引基礎(chǔ)

Mysql索引可以包含一個(gè)或多個(gè)列的值，如果索引包含多個(gè)列，那么列的順序很重要，因?yàn)镸ysql只能高效的使用索引的最左前綴原則
無(wú)論是多么復(fù)雜的ORM工具，在精妙和復(fù)雜的索引面前都是“浮云”

索引的區(qū)別

1. B樹(shù)索引
   MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ)。MyISAM索引通過(guò)數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行
   B樹(shù)特點(diǎn)：所有值都是按照順序存儲(chǔ)的，并且每一個(gè)葉子頁(yè)到根的距離相同

B+樹(shù)

根節(jié)點(diǎn)的槽中存放了指向子節(jié)點(diǎn)的指針，通過(guò)比較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下層子節(jié)點(diǎn)。這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的上線和下線。最終存儲(chǔ)引擎要么找到對(duì)應(yīng)的值，要么記錄不存在。葉子節(jié)點(diǎn)的指針指向的是被索引的數(shù)據(jù)。

B樹(shù)對(duì)索引列是順序組織存儲(chǔ)的，所以很適合查找范圍數(shù)據(jù)，例如：找出所有以I到K開(kāi)頭的名字，這樣的查找效率會(huì)非常高。

B樹(shù)索引適用范圍：

• 全職匹配：是指和索引中的所有列進(jìn)行匹配
• 匹配最左前綴：即只使用索引的第一列
• 匹配列前綴：也可以只匹配某一列的值的開(kāi)頭部分
• 匹配范圍值：例如查找姓Allen和B之間的人
• 精確匹配某一列并范圍匹配另外一列
• 只訪問(wèn)索引的查詢：查詢只訪問(wèn)索引，而無(wú)須訪問(wèn)數(shù)據(jù)行。（覆蓋索引）

B樹(shù)索引的限制：

• 所有的查詢都必須以最左列為原則，否則索引失效
• 必須都包含，不能跳過(guò)索引中的某一列，否則只能匹配最左列
• 如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無(wú)法使用索引優(yōu)化查找

2. 哈希索引
   基于hash表實(shí)現(xiàn)，只有精確匹配索引所有列的查詢才有效。哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)在哈希表中保存指向每個(gè)數(shù)據(jù)行的指針。

在這里插入圖片描述

哈希索引的限制：

• 哈希索引只包含哈希值和行指針，而不存儲(chǔ)字段值，所以不能使用索引中的值來(lái)避免讀取行，不過(guò)訪問(wèn)內(nèi)存中的行速度很快，大部分情況下這一點(diǎn)對(duì)性能影響不大
• 哈希索引是無(wú)序的
• 哈希索引不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容計(jì)算哈希值的。
• 哈希索引只支持等值比較查詢，不支持范圍查詢
• 哈希索引不能建立在選擇性很低的列上，hash沖突很多代價(jià)越大。

例子：B樹(shù)上自建hash索引高查詢效率

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

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

三、高性能的索引策略

1. 索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)，簡(jiǎn)化where條件的習(xí)慣，始終將索引列單獨(dú)放在比較符號(hào)的一側(cè)
2. 前綴索引和索引選擇性
   例如：索引很長(zhǎng)的字符列，一個(gè)策略是模擬hash索引，一個(gè)是找到最適合的索引選擇性。索引選擇性=不重復(fù)的索引值/記錄總數(shù)。
   索引的選擇性越高則查詢效率越高，因?yàn)檫x擇性高的索引可以讓Mysql在查找時(shí)過(guò)濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
   訣竅：選擇足夠長(zhǎng)的前綴以保證較高的選擇性，同時(shí)又不能太長(zhǎng)
   計(jì)算合適的前綴長(zhǎng)度的另外一個(gè)辦法就是計(jì)算完整列的選擇性，并使用前綴的選擇性接近于完整列的選擇性

select count(distinct city)/count(*) from sakila.city_demo;//0.0312

如果前綴的選擇性能接近0.031基本上就可以用了

select count(distinct left(city,3))/count(*) as sel3,//0.0239
count(distinct left(city,4))/count(*) as sel4,//0.0239
count(distinct left(city,5))/count(*) as sel5,//0.0305
count(distinct left(city,6))/count(*) as sel6,//0.0309
count(distinct left(city,7))/count(*) as sel7 from sakila.city_demo//0.0310

前綴索引的缺點(diǎn)：Mysql無(wú)法使用前綴索引做ORDER BY和GROUP BY，也無(wú)法使用前綴索引做覆蓋掃描。

有時(shí)候后綴索引也有用處，例如找到某個(gè)域名的所有電子郵件地址。MYSQL原生并不支持反向索引，但是可以把字符串反轉(zhuǎn)后存儲(chǔ)，并基于此建立前綴索引。

3. 聚簇索引（重點(diǎn)）
   不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。這里首先對(duì)比B樹(shù)和B+樹(shù)的區(qū)別：
   B樹(shù)的所有節(jié)點(diǎn)既存放所有節(jié)點(diǎn)的鍵key也存放數(shù)據(jù)data；而B(niǎo)+樹(shù)只有葉子節(jié)點(diǎn)存放key和data，其他內(nèi)節(jié)點(diǎn)只存放key
   B樹(shù)的葉子節(jié)點(diǎn)都是獨(dú)立的；B+樹(shù)的葉子節(jié)點(diǎn)有一條引用鏈指向與它相鄰的葉子節(jié)點(diǎn)
   B樹(shù)的檢索的過(guò)程相當(dāng)于對(duì)范圍內(nèi)的每個(gè)節(jié)點(diǎn)的關(guān)鍵字做二分查找，可能還沒(méi)有到達(dá)葉子節(jié)點(diǎn)檢索就結(jié)束了；而B(niǎo)+樹(shù)的檢索效率就很穩(wěn)定了，任何查找都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)的過(guò)程，葉子節(jié)點(diǎn)的順序檢索很明顯。

InnoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B樹(shù)索引和數(shù)據(jù)行，當(dāng)表中存在聚簇索引時(shí)，它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)（leaf page）中，聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起。InnoDB通過(guò)主鍵聚集數(shù)據(jù)，這也就是說(shuō)圖中被索引地列就是主鍵列。如果沒(méi)有定義主鍵，InnoDB會(huì)選擇一個(gè)唯一的非空索引代替。如果沒(méi)有這樣的索引，InnoDB會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引。

聚集數(shù)據(jù)的優(yōu)點(diǎn)：

• 可以把相關(guān)數(shù)據(jù)保存在一起。例如實(shí)現(xiàn)電子郵箱時(shí)，可以根據(jù)用戶ID來(lái)聚集數(shù)據(jù)，這樣只需要從磁盤(pán)讀取少數(shù)的數(shù)據(jù)頁(yè)就能獲取某個(gè)用戶的全部郵件。如果沒(méi)有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤(pán)I/O
• 數(shù)據(jù)訪問(wèn)更快。聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B樹(shù)上，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快。
• 使用覆蓋索引掃描的查詢可以直接用頁(yè)節(jié)點(diǎn)的主鍵值

聚簇索引的缺點(diǎn)：

• 插入速度嚴(yán)重依賴插入順序
• 更新聚簇索引列的代價(jià)很高，因?yàn)闀?huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置
• 基于聚簇索引的表再插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨“頁(yè)分裂”問(wèn)題（頁(yè)分裂：當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁(yè)中時(shí)，存儲(chǔ)引擎會(huì)將該頁(yè)分裂成兩個(gè)頁(yè)面來(lái)容納行，也分裂會(huì)導(dǎo)致表占用更多的磁盤(pán)空間）
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí)候。
• 二級(jí)索引（非聚簇索引）可能比想象的要更大，因?yàn)樵诙?jí)索引中的葉子節(jié)點(diǎn)包含了引用行的主鍵列。（為什么索引需要兩次索引查找？答案是保存的行指針的實(shí)質(zhì)。要記住，二級(jí)索引葉子節(jié)點(diǎn)保存的不是行的物理位置的指針，而是行的主鍵值。這就意味著通過(guò)二級(jí)索引查找行，存儲(chǔ)引擎需要找到二級(jí)索引的葉子節(jié)點(diǎn)獲得對(duì)應(yīng)的主鍵值，然后根據(jù)這個(gè)值去聚簇索引中找到對(duì)應(yīng)的行。兩次B樹(shù)查找而不是一次。對(duì)于InnoDB自適應(yīng)哈希索引能夠減少這樣的重復(fù)工作。

InnoDB和MyISAM的數(shù)據(jù)分布對(duì)比
MyISAM數(shù)據(jù)分布非常簡(jiǎn)單，按照數(shù)據(jù)插入的順序存儲(chǔ)在磁盤(pán)上，在行的旁邊顯示了行號(hào)，從0開(kāi)始遞增。因?yàn)樾惺嵌ㄩL(zhǎng)的，所以MyISAM可以從表的開(kāi)頭跳過(guò)所需要的字節(jié)找到需要的行

數(shù)據(jù)分布

在這里插入圖片描述

• 覆蓋索引（重點(diǎn)）
  通常大家都會(huì)根據(jù)查詢的where條件來(lái)創(chuàng)建合適的索引，不過(guò)這只是一個(gè)方面。優(yōu)秀的索引應(yīng)該考慮到整個(gè)查詢，而不單是where條件部分。索引確實(shí)是一種查找數(shù)據(jù)的高效方式，但是MySQL也可以使用索引來(lái)直接獲取列數(shù)據(jù)，這樣就不再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么就沒(méi)有必要回表查詢了。如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要查詢的字段的值，我們就稱為“覆蓋索引”。覆蓋索引的好處：
• 索引條目通常小于數(shù)據(jù)行大小，所以如果只需要讀取索引，那MySQL就會(huì)極大地減少數(shù)據(jù)訪問(wèn)量。這對(duì)緩存的負(fù)載非常重要，因?yàn)檫@種情況下響應(yīng)時(shí)間大部分花費(fèi)在數(shù)據(jù)拷貝上。覆蓋索引對(duì)于I/O密集型的應(yīng)用也有幫助，因?yàn)樗饕葦?shù)據(jù)更小，更容易全部放入內(nèi)存。
• 因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的（至少在單個(gè)頁(yè)內(nèi)是如此），所以對(duì)于I/O密集型的范圍查詢會(huì)比隨機(jī)從磁盤(pán)讀取一行數(shù)據(jù)的I/O要少得多
• 一些存儲(chǔ)引擎如MyISAM在內(nèi)存中只緩存索引，數(shù)據(jù)則依賴于操作系統(tǒng)來(lái)緩存，因此要訪問(wèn)數(shù)據(jù)需要一次系統(tǒng)調(diào)用。這可能會(huì)導(dǎo)致嚴(yán)重的性能問(wèn)題，尤其是那些系統(tǒng)調(diào)用占了數(shù)據(jù)訪問(wèn)的最大開(kāi)銷的場(chǎng)景。
• 由于InnoDB的聚簇索引，覆蓋索引對(duì)于InnoDB表特別有用。InnoDB的二級(jí)索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級(jí)索引主鍵能夠覆蓋查詢，則可以避免對(duì)主鍵索引的二次查詢。

覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引、空間索引和全文索引等都不存儲(chǔ)索引列的值，所以MySQL只能使用B樹(shù)索引做覆蓋索引。
當(dāng)發(fā)起一個(gè)被索引覆蓋的查詢時(shí)，在EXPLAIN的Extra列可以看到Using index的信息。例如，表sakila.inventry有一個(gè)多列索引(store_id,film_id)。MySQL如果只需訪問(wèn)這兩列，就可以使用這個(gè)索引做覆蓋索引。

mysql>EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*********************************************************
id: 1
select_type:SIMPLE
table:inventory
type:index
possible_keys:NULL
key:idx_store_id_film_id
key_len:3
ref:NULL
rows:4673
Extra:Using index

例子：假設(shè)索引覆蓋了WHERE條件中的字段，但不是整個(gè)查詢涉及的字段。如果條件為假，Mysql總是會(huì)回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會(huì)被過(guò)濾掉。

select * from products where actor = 'SEAN CARRY' and title like '%APOLLO%'\G

索引無(wú)法覆蓋該查詢，原因：

• 沒(méi)有任何索引能夠覆蓋這個(gè)查詢。因?yàn)椴樵儚谋碇羞x擇了所有的列，而沒(méi)有任何索引覆蓋了所有的列。不過(guò)，理論上MySQL還有一個(gè)捷徑可以利用：WHERE條件中的列有索引可以覆蓋的，因此MySQL可以使用該索引找到對(duì)應(yīng)的actor并檢查title是否匹配，過(guò)濾之后再讀取需要的數(shù)據(jù)行。
• MySQL不能在索引中執(zhí)行LIKE操作，MySQL5.5和更早的版本中只允許在索引中做簡(jiǎn)單比較操作。MySQL能在索引中做最左前綴匹配原則的LIKE查詢，因?yàn)樵摬僮骺梢赞D(zhuǎn)換為簡(jiǎn)單的比較操作，但是如果是通配符開(kāi)頭的LIKE查詢，存儲(chǔ)引擎就無(wú)法做匹配。這種情況下，MySQL服務(wù)器只能提取數(shù)據(jù)行的值而不是索引值來(lái)比較。

優(yōu)化：擴(kuò)展索引覆蓋三個(gè)數(shù)據(jù)列（artist, title,prod_id)然后使用如下查詢

select * from products 
//先用子查詢查詢到滿足主查詢條件的相關(guān)的prod_id,再根據(jù)join查詢主查詢相關(guān)語(yǔ)句，真的非常巧妙。使用了部分覆蓋索引
JOIN(select prod_id from products where actor='SEAN CARRY' and title like '%APOLLO%')AS t1 ON 
(t1.prod_id = products.prod_id)

在查詢的第一階段MySQL可使用覆蓋索引，在FROM子句的子查詢中找到匹配的prod_id,然后根據(jù)這些prod_id值在外層查詢匹配獲取需要的所有列值。雖然無(wú)法使用覆蓋索引覆蓋整個(gè)查詢，但總算比完全無(wú)法利用索引覆蓋的好。

優(yōu)化結(jié)果：這樣的優(yōu)化效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個(gè)products表有100w行，我們來(lái)看一下上面兩個(gè)查詢?cè)谌齻€(gè)不同的數(shù)據(jù)集上的表現(xiàn)。
1.Sean 出演了30000部作品，其中20000部的標(biāo)題包含了Apollo
2.Sean 出演了30000部作品，其中40部的標(biāo)題包含了Apollo
3.Sean 出演了50部作品，其中10部的標(biāo)題包含了Apollo

| 數(shù)據(jù)集| 原查詢 |優(yōu)化后
|1|每秒5次|每秒5次
| 2 | 每秒7次|每秒35次
|3|每秒2400|每秒2000

示例一查詢返回了一個(gè)很大的結(jié)果集，因此看不到優(yōu)化效果。大部分時(shí)間都花在讀取和發(fā)送數(shù)據(jù)上了。
示例二查詢性能提高了五倍
示例三效果下降因?yàn)樗饕慕Y(jié)果集已經(jīng)很小，所以子查詢帶來(lái)的成本反而比從表中直接提取完整行更高。