一、索引基礎(chǔ)

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

索引的區(qū)別

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

B+樹

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

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

B樹索引適用范圍：

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

B樹索引的限制：

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

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

在這里插入圖片描述

哈希索引的限制：

• 哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行，不過訪問內(nèi)存中的行速度很快，大部分情況下這一點(diǎn)對性能影響不大
• 哈希索引是無序的
• 哈希索引不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內(nèi)容計算哈希值的。
• 哈希索引只支持等值比較查詢，不支持范圍查詢
• 哈希索引不能建立在選擇性很低的列上，hash沖突很多代價越大。

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

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

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

三、高性能的索引策略

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

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無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描。

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

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

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

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

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

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

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

InnoDB和MyISAM的數(shù)據(jù)分布對比
MyISAM數(shù)據(jù)分布非常簡單，按照數(shù)據(jù)插入的順序存儲在磁盤上，在行的旁邊顯示了行號，從0開始遞增。因為行是定長的，所以MyISAM可以從表的開頭跳過所需要的字節(jié)找到需要的行

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

在這里插入圖片描述

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

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

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條件中的字段，但不是整個查詢涉及的字段。如果條件為假，Mysql總是會回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會被過濾掉。

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

索引無法覆蓋該查詢，原因：

• 沒有任何索引能夠覆蓋這個查詢。因為查詢從表中選擇了所有的列，而沒有任何索引覆蓋了所有的列。不過，理論上MySQL還有一個捷徑可以利用：WHERE條件中的列有索引可以覆蓋的，因此MySQL可以使用該索引找到對應(yīng)的actor并檢查title是否匹配，過濾之后再讀取需要的數(shù)據(jù)行。
• MySQL不能在索引中執(zhí)行LIKE操作，MySQL5.5和更早的版本中只允許在索引中做簡單比較操作。MySQL能在索引中做最左前綴匹配原則的LIKE查詢，因為該操作可以轉(zhuǎn)換為簡單的比較操作，但是如果是通配符開頭的LIKE查詢，存儲引擎就無法做匹配。這種情況下，MySQL服務(wù)器只能提取數(shù)據(jù)行的值而不是索引值來比較。

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

select * from products 
//先用子查詢查詢到滿足主查詢條件的相關(guān)的prod_id,再根據(jù)join查詢主查詢相關(guān)語句，真的非常巧妙。使用了部分覆蓋索引
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值在外層查詢匹配獲取需要的所有列值。雖然無法使用覆蓋索引覆蓋整個查詢，但總算比完全無法利用索引覆蓋的好。

優(yōu)化結(jié)果：這樣的優(yōu)化效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個products表有100w行，我們來看一下上面兩個查詢在三個不同的數(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

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