今天來盤點一下關(guān)于MySQL索引常見的知識點：

圖片

本文主要是針對InnoDB存儲引擎進(jìn)行講解。

索引分類

索引的分類可以從不同的維度進(jìn)行分類

1、按使用的數(shù)據(jù)結(jié)構(gòu)劃分

• B+樹索引
• Hash索引
• ...

2、按實際的物理存儲數(shù)據(jù)構(gòu)劃分

• 聚簇索引
• 非聚簇索引（二級索引）

聚簇索引和非聚簇索引后面會著重說。

3、按索引特性劃分

• 主鍵索引
• 唯一索引
• 普通索引
• 全文索引
• ...

4、按字段個數(shù)劃分

• 單列索引
• 聯(lián)合索引

索引數(shù)據(jù)結(jié)構(gòu)

準(zhǔn)備

為了接下來文章更好地講解，這里我準(zhǔn)備了一張user表，接下來整篇文章的示例會以這張表來講解

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Hash索引

Hash索引其實用的不多，最主要是因為最常見的存儲引擎InnoDB不支持顯示地創(chuàng)建Hash索引，只支持自適應(yīng)Hash索引。

雖然可以使用sql語句在InnoDB顯示聲明Hash索引，但是其實是不生效的

圖片

對name字段建立Hash索引，但是通過show index from 表名就會發(fā)現(xiàn)實際還是B+樹

圖片

在存儲引擎中，Memory引擎支持Hash索引。

Hash索引其實有點像Java中的HashMap底層的數(shù)據(jù)結(jié)構(gòu)，他也有很多的槽，存的也是鍵值對，鍵值為索引列，值為數(shù)據(jù)的這條數(shù)據(jù)的行指針，通過行指針就可以找到數(shù)據(jù)

假設(shè)現(xiàn)在user表用Memory存儲引擎，對name字段建立Hash索引，表中插入三條數(shù)據(jù)

圖片

Hash索引會對索引列name的值進(jìn)行Hash計算，然后找到對應(yīng)的槽下面，如下圖所示：

圖片

當(dāng)遇到name字段的Hash值相同時，也就是Hash沖突，就會形成一個鏈表，比如有name=張三有兩條數(shù)據(jù)，就會形成一個鏈表。

之后如果要查name=李四的數(shù)據(jù)，只需要對李四進(jìn)行Hash計算，找到對應(yīng)的槽，遍歷鏈表，取出name=李四對應(yīng)的行指針，然后根據(jù)行指針去查找對應(yīng)的數(shù)據(jù)。

Hash索引優(yōu)缺點

• hash索引只能用于等值比較，所以查詢效率非常高
• 不支持范圍查詢，也不支持排序，因為索引列的分布是無序的

B+樹

B+樹是mysql索引中用的最多的數(shù)據(jù)結(jié)構(gòu)，這里先不介紹，下一節(jié)會著重介紹。

除了Hash和B+樹之外，還有全文索引等其它索引，這里就不討論了

聚簇索引

數(shù)據(jù)頁數(shù)據(jù)存儲

我們知道，我們插入表的數(shù)據(jù)其實最終都要持久化到磁盤上，InnoDB為了方便管理這些數(shù)據(jù)，提出了頁的概念，它會將數(shù)據(jù)劃分到多個頁中，每個頁大小默認(rèn)是16KB，這個頁我們可以稱為數(shù)據(jù)頁。

當(dāng)我們插入一條數(shù)據(jù)的時候，數(shù)據(jù)都會存在數(shù)據(jù)頁中，如下圖所示

圖片

當(dāng)數(shù)據(jù)不斷地插入數(shù)據(jù)頁中，數(shù)據(jù)會根據(jù)主鍵（沒有的話會自動生成）的大小進(jìn)行排序，形成一個單向鏈表

圖片

數(shù)據(jù)頁中除了會存儲我們插入的數(shù)據(jù)之外，還會有一部分空間用來存儲額外的信息，額外的信息類型比較多，后面遇到一個說一個。

單個數(shù)據(jù)頁的數(shù)據(jù)查找

既然數(shù)據(jù)會存在數(shù)據(jù)頁中，那么該如何從數(shù)據(jù)頁中去查數(shù)據(jù)呢？

假設(shè)現(xiàn)在需要在數(shù)據(jù)頁中定位到id=2的這條記錄的數(shù)據(jù)，如何快速定位？

有一種笨辦法就是從頭開始順著鏈表遍歷就行了，判斷id是不是等于2，如果等于2就取出數(shù)據(jù)就行了。

雖然這種方法可行，但是如果一個數(shù)據(jù)頁存儲的數(shù)據(jù)多，幾十或者是幾百條數(shù)據(jù)，每次都這么遍歷，不是太麻煩了

所以mysql想了一個好辦法，那就是給這些數(shù)據(jù)分組。

假設(shè)數(shù)據(jù)頁中存了12條數(shù)據(jù)，那么整個分組大致如下圖所示：

圖片

為了方便了，我這里只標(biāo)出了id值，省略了其它字段的值。

這里我假設(shè)每4條數(shù)據(jù)算一個組，圖上就有3個組，組分好之后，mysql會取出每組中最大的id值，就是圖中的4、8、12，放在一起，在數(shù)據(jù)頁中找個位置存起來，這就是前面提到的數(shù)據(jù)頁存儲的額外信息之一，被稱為頁目錄

假設(shè)此時要查詢id=6的數(shù)據(jù)之后，此時只需要從頁目錄中根據(jù)二分查找，發(fā)現(xiàn)在4-8之間，由于4和8是他們所在分組的最大的id，那么id=6肯定在8那個分組中，之后就會到id=8的那個分組中，遍歷每個數(shù)據(jù)，判斷id是不是等于6即可。

由于mysql規(guī)定每個組的數(shù)據(jù)條數(shù)大概為4~8條，所以肯定比遍歷整個數(shù)據(jù)頁的數(shù)據(jù)快的多。

上面分組的情況實際上我做了一點簡化，但是不耽誤理解。

多個數(shù)據(jù)頁中的數(shù)據(jù)查找

當(dāng)我們不斷的往表中插入數(shù)據(jù)的時候，數(shù)據(jù)占用空間就會不斷變大，但是一個數(shù)據(jù)頁的大小是一定的，當(dāng)一個數(shù)據(jù)頁存不下數(shù)據(jù)的時候，就會重新創(chuàng)建一個數(shù)據(jù)頁來存儲數(shù)據(jù)。

圖片

mysql為了區(qū)分每個頁，會為每個數(shù)據(jù)頁分配一個頁號，存在額外信息的存儲空間中，同時額外信息還會存儲當(dāng)前數(shù)據(jù)頁的前一個和后一個數(shù)據(jù)頁的位置，從而形成數(shù)據(jù)頁之間的雙向鏈表。

圖片

數(shù)據(jù)頁2的頁號就是2，數(shù)據(jù)頁3的頁號就是3，這里我為了方便理解，就直接寫數(shù)據(jù)頁幾。

并且mysql規(guī)定，前一個數(shù)據(jù)頁的存儲數(shù)據(jù)id的最大值要小于后一個數(shù)據(jù)頁的存儲數(shù)據(jù)id的最小值，這樣就實現(xiàn)了數(shù)據(jù)在所有數(shù)據(jù)頁中按照id的大小排序。

現(xiàn)在，如果有多個數(shù)據(jù)頁，當(dāng)我們需要查找id=5的數(shù)據(jù)，怎么辦呢？

當(dāng)然還是可以用上面的笨辦法，那就是從第一個數(shù)據(jù)頁開始遍歷，然后遍歷每個數(shù)據(jù)頁中的數(shù)據(jù)，最終也可以找到id=5的數(shù)據(jù)。

但是你仔細(xì)想想，這個笨辦法就相當(dāng)于全表掃描了呀，這肯定是不行的。

那么怎么優(yōu)化呢？

mysql優(yōu)化的思路其實跟前面單數(shù)據(jù)頁查找數(shù)據(jù)的優(yōu)化思路差不多。

它會將每個數(shù)據(jù)頁中最小的id拿出來，單獨放到另一個數(shù)據(jù)頁中，這個數(shù)據(jù)頁不存儲我們實際插入的數(shù)據(jù)，只存儲最小的id和這個id所在數(shù)據(jù)頁的頁號，如圖所示：

圖片

為了圖更加飽滿，我加了一個存放數(shù)據(jù)的數(shù)據(jù)頁4。

此時數(shù)據(jù)頁5就是抽取出來的，存放了下面三個存放數(shù)據(jù)的數(shù)據(jù)頁的最小的id和對應(yīng)的數(shù)據(jù)頁號。

如果此時查找id=5的數(shù)據(jù)就很方便了，大致分為以下幾個步驟：

• 從數(shù)據(jù)頁5直接根據(jù)二分查找，發(fā)現(xiàn)在4-7之間。
• 由于4和7是所在數(shù)據(jù)頁最小的id，那么此時id=5的數(shù)據(jù)必在id=4的數(shù)據(jù)頁上(因為id=7的數(shù)據(jù)頁最小的id就是7)。
• 接下來就到id=4對應(yīng)的數(shù)據(jù)頁2的頁號找到數(shù)據(jù)頁2。
• 之后再根據(jù)前面提到的根據(jù)數(shù)據(jù)的主鍵id從單個數(shù)據(jù)頁查找的流程查找數(shù)據(jù)。

這樣就實現(xiàn)了根據(jù)主鍵id到在多個數(shù)據(jù)頁之間查找數(shù)據(jù)。

聚簇索引

隨著數(shù)據(jù)量不斷增多，存儲數(shù)據(jù)的數(shù)據(jù)頁不斷變多，數(shù)據(jù)頁5的數(shù)據(jù)就會越來越多，但是每個數(shù)據(jù)頁默認(rèn)就16k，所以數(shù)據(jù)頁5也會分裂出多個數(shù)據(jù)頁的情況，如下圖：

圖片

數(shù)據(jù)頁10的作用就跟數(shù)據(jù)頁5是一樣的。

此時如還要查找id=5的數(shù)據(jù)，那么應(yīng)該去數(shù)據(jù)頁5進(jìn)行二分查找呢還是去數(shù)據(jù)頁10進(jìn)行二分查找呢？

笨辦法就是遍歷，但是真沒必要，mysql會去抽取數(shù)據(jù)頁5和數(shù)據(jù)頁10存儲的最小的數(shù)據(jù)的id和對應(yīng)的數(shù)據(jù)頁號，單獨拎出來放到一個數(shù)據(jù)頁中，如下圖：

圖片

數(shù)據(jù)頁11就是新抽取的數(shù)據(jù)頁，存儲了id=1和對應(yīng)的數(shù)據(jù)頁5的頁號以及數(shù)id=10和對應(yīng)的數(shù)據(jù)頁10的頁號。

而這就是B+樹。

一般來說，mysql數(shù)據(jù)庫的B+樹一般三層就可以放下幾千萬條數(shù)據(jù)

此時查找id=5的數(shù)據(jù)，大致分為以下幾個步驟：

• 從數(shù)據(jù)頁11根據(jù)二分查找定位到id=5對應(yīng)數(shù)據(jù)頁5
• 再到數(shù)據(jù)頁5根據(jù)id=5二分查找定位到數(shù)據(jù)頁3
• 再到數(shù)據(jù)頁3根據(jù)id=5查找數(shù)據(jù)，具體的邏輯前面也提到很多次了

這樣就能成功查找到數(shù)據(jù)了。

而這種葉子節(jié)點存儲實際插入的數(shù)據(jù)的B+樹就被稱為聚簇索引，非葉子節(jié)點存儲的就是記錄的id和對應(yīng)的數(shù)據(jù)頁號。

所以對于InnoDB存儲引擎來說，數(shù)據(jù)本身就存儲在一顆B+樹中。

二級索引

二級索引也被稱為非聚簇索引，本身也就是一顆B+樹，一個二級索引對應(yīng)一顆B+樹，但是二級索引B+樹存儲的數(shù)據(jù)跟聚簇索引不一樣。

聚簇索引前面也說了，葉子節(jié)點存的就是我們插入到數(shù)據(jù)庫的數(shù)據(jù)，非葉子節(jié)點存的就是數(shù)據(jù)的主鍵id和對應(yīng)的數(shù)據(jù)頁號。

而二級索引葉子節(jié)點存的是索引列的數(shù)據(jù)和對應(yīng)的主鍵id，非葉子節(jié)點除了索引列的數(shù)據(jù)和id之外，還會存數(shù)據(jù)頁的頁號。

前面提到的數(shù)據(jù)頁，其實真正是叫索引頁，因為葉子節(jié)點存的是實際表的數(shù)據(jù)，所以我就叫數(shù)據(jù)頁了，接下來因為真正要講到索引了，所以我就將二級索引的頁稱為索引頁，你知道是同一個，但是存儲的數(shù)據(jù)不一樣就可以了。

單列索引

假設(shè)，我們現(xiàn)在對name字段加了一個普通非唯一索引，那么name就是索引列，同時name這個索引也就是單列索引。

此時如果往表中插入三條數(shù)據(jù)，那么name索引的葉子節(jié)點存的數(shù)據(jù)就如下圖所示：

圖片

mysql會根據(jù)name字段的值進(jìn)行排序，這里我假設(shè)張三排在李四前面，當(dāng)索引列的值相同時，就會根據(jù)id排序，所以索引實際上已經(jīng)根據(jù)索引列的值排好序了。

這里肯定有小伙伴疑問，name字段存儲的中文也可以排序么？

答案是可以的，并且mysql支持很多種排序規(guī)則，我們在建數(shù)據(jù)庫或者是建表的時候等都可以指定排序規(guī)則，并且后面文章涉及到的字符串排序都是我隨便排的，實際情況可能不一樣。

圖片

對于單個索引列數(shù)據(jù)查找也是跟前面說的聚簇索引一樣，也會對數(shù)據(jù)分組，之后可以根據(jù)二分查找在單個索引列來查找數(shù)據(jù)。

當(dāng)數(shù)據(jù)不斷增多，一個索引頁存儲不下數(shù)據(jù)的時候，也會用多個索引頁來存儲，并且索引頁直接也會形成雙向鏈表。

圖片

當(dāng)索引頁不斷增多是，為了方便在不同索引頁中查找數(shù)據(jù)，也就會抽取一個索引頁，除了存頁中id，同時也會存儲這個id對應(yīng)的索引列的值。

圖片

當(dāng)數(shù)據(jù)越來越多越來越多，還會抽取，也會形成三層的一個B+樹，這里我就不畫了。

聯(lián)合索引

除了單列索引，聯(lián)合索引其實也是一樣的，只不過索引頁存的數(shù)據(jù)就多了一些索引列

比如，在name和age上建立一個聯(lián)合索引，此時單個索引頁就如圖所示

圖片

先以name排序，name相同時再以age排序，如果再有其它列，依次類推，最后再以id排序。

相比于只有name一個字段的索引來說，索引頁就多存了一個索引列。

最后形成的B+樹簡化為如下圖：

圖片

小結(jié)

其實從上面的分析可以看出，聚簇索引和非聚簇索引主要區(qū)別有以下幾點

• 聚簇索引的葉子節(jié)點存的是所有列的值，非聚簇索引的葉子節(jié)點只存了索引列的值和主鍵id
• 聚簇索引的數(shù)據(jù)是按照id排序，非聚簇索引的數(shù)據(jù)是按照索引列排序
• 聚簇索引的非葉子節(jié)點存的是主鍵id和頁號，非聚簇索引的非葉子節(jié)點存的是索引列、主鍵id、頁號

由于后面這個索引樹會經(jīng)常用到，為了你方便比較，所以我根據(jù)上面索引樹的數(shù)據(jù)在表中插入了對應(yīng)的數(shù)據(jù)，sql在文末。

圖片

實際情況下索引B+樹可能并不是按照我圖中畫出來的那樣排序，但不耽誤理解。

回表

講完二級索引，接下來講一講如何使用二級索引查找數(shù)據(jù)。

這里假設(shè)對name字段創(chuàng)建了一個索引，并且表里就存了上面示例中的幾條數(shù)據(jù)，這里我再把圖拿過來。

圖片

那么對于下面這條sql應(yīng)該如何執(zhí)行？

select * from `user` where name = '趙六';

由于查詢條件是name = '趙六'，所以會走name索引

整個過程大致分為以下幾個步驟：

• 從最上面那層索引頁開始二分查找，我們圖中就是索引頁113，如果索引頁113上面還有一層，就從上面一層二分查找
• 在索引頁113查找到趙六在王五和劉七之間，之后到王五對應(yīng)的索引頁111上去查找趙六
• 在索引頁111找到趙六的第一條記錄，也就是id=4的那條
• 由于是select *，還要查其它字段，此時就會根據(jù)id=4到聚簇索引中查找其它字段數(shù)據(jù)，這個查找過程前面說了很多次了，這個根據(jù)id=4到聚簇索引中查找數(shù)據(jù)的過程就被稱為回表
• 由于是非唯一索引，所以趙六這個值可能會有重復(fù)，所以接著就會在索引頁111順著鏈表繼續(xù)遍歷，如果name還是趙六，那么還會根據(jù)id值進(jìn)行回表，如此重復(fù)，一直這么遍歷，直至name不再等于趙六為止，對于圖示，其實就是兩條數(shù)據(jù)

從上面的二級索引的查找數(shù)據(jù)過程分析，就明白了回表的意思，就是先從二級索引根據(jù)查詢條件字段值查找對應(yīng)的主鍵id，之后根據(jù)id再到聚簇索引查找其它字段的值。

覆蓋索引

上一節(jié)說當(dāng)執(zhí)行select * from user where name = '趙六';這條sql的時候，會先從索引頁中查出來name = '趙六';對應(yīng)的主鍵id，之后再回表，到聚簇索引中查詢其它字段的值。

那么當(dāng)執(zhí)行下面這條sql，又會怎樣呢？

select id from `user` where name = '趙六';

這次查詢字段從select *變成select id，查詢條件不變，所以也會走name索引

所以還是跟前面一樣了，先從索引頁中查出來name = '趙六';對應(yīng)的主鍵id之后，驚訝的發(fā)現(xiàn)，sql中需要查詢字段的id值已經(jīng)查到了，那次此時壓根就不需要回表了，已經(jīng)查到id了，還回什么表。

而這種需要查詢的字段都在索引列中的情況就被稱為覆蓋索引，索引列覆蓋了查詢字段的意思。

當(dāng)使用覆蓋索引時會減少回表的次數(shù)，這樣查詢速度更快，性能更高。

所以，在日常開發(fā)中，盡量不要select * ，需要什么查什么，如果出現(xiàn)覆蓋索引的情況，查詢會快很多。

索引下推

假設(shè)現(xiàn)在對表建立了一個name和age的聯(lián)合索引，為了方便理解，我把前面的圖再拿過來

圖片

接下來要執(zhí)行如下的sql：

select * from `user` where name > '王五' and age > 22;

在MySQL5.6(不包括5.6)之前，整個sql大致執(zhí)行步驟如下：

• 先根據(jù)二分查找，定位到name > '王五'的第一條數(shù)據(jù)，也就是id=4的那個趙六
• 之后就會根據(jù)id=4進(jìn)行回表操作，到聚簇索引中查找id=4其它字段的數(shù)據(jù)，然后判斷數(shù)據(jù)中的age是否大于22，是的話就說明是我們需要查找的數(shù)據(jù)，否則就不是
• 之后順著鏈表，繼續(xù)遍歷，然后找到一條記錄就回一次表，然后判斷age，如此反復(fù)下去，直至結(jié)束

所以對于圖上所示，整個搜索過程會經(jīng)歷5次回表操作，兩個趙六，兩個劉七，一個王九，最后符合條件的也就是id=6的趙六那條數(shù)據(jù)，其余age不符和。

雖然這么執(zhí)行沒什么問題，但是不知有沒有發(fā)現(xiàn)其實沒必要進(jìn)行那么多次回表，因為光從上面的索引圖示就可以看出，符合name > '王五' and age > 22的數(shù)據(jù)就id=6的趙六那條數(shù)據(jù)

所以在MySQL5.6之后，對上面的age > 22判斷邏輯進(jìn)行了優(yōu)化

前面還是一樣，定位查找到id=4的那個趙六，之后就不回表來判斷age了，因為索引列有age的值了，那么直接根據(jù)索引中age判斷是否大于22，如果大于的話，再回表查詢剩余的字段數(shù)據(jù)（因為是select *），然后再順序鏈表遍歷，直至結(jié)束

所以這樣優(yōu)化之后，回表次數(shù)就成1了，相比于前面的5次，大大減少了回表的次數(shù)。

而這個優(yōu)化，就被稱為索引下推，就是為了減少回表的次數(shù)。

之所以這個優(yōu)化叫索引下推，其實是跟判斷age > 22邏輯執(zhí)行的地方有關(guān)，這里就不過多贅述了。

索引合并

索引合并（index merge）是從MySQL5.1開始引入的索引優(yōu)化機(jī)制，在之前的MySQL版本中，一條sql多個查詢條件只能使用一個索引，但是引入了索引合并機(jī)制之后，MySQL在某些特殊的情況下會掃描多個索引，然后將掃描結(jié)果進(jìn)行合并

結(jié)果合并會為下面三種情況：

• 取交集（intersect）
• 取并集（union）
• 排序后取并集（sort-union）

為了不耽誤演示，刪除之前所有的索引，然后為name和age各自分別創(chuàng)建一個二級索引idx_name和idx_age

取交集（intersect）

當(dāng)執(zhí)行下面這條sql就會出現(xiàn)取交集的情況：

select * from `user` where name = '趙六' and age= 22;

查看執(zhí)行計劃：

圖片

type是index_merge，并且possible_key和key都是idx_name和idx_age，說明使用了索引合并，并且Extra有Using intersect(idx_age,idx_name)，intersect就是交集的意思。

整個過程大致是這樣的，分別根據(jù)idx_name和idx_age取出對應(yīng)的主鍵id，之后將主鍵id取交集，那么這部分交集的id一定同時滿足查詢name = '趙六' and age= 22的查詢條件（仔細(xì)想想），之后再根據(jù)交集的id回表。

不過要想使用取交集的聯(lián)合索引，需要滿足各自索引查出來的主鍵id是排好序的，這是為了方便可以快速的取交集。

比如下面這條sql就無法使用聯(lián)合索引：

select * from `user` where name = '趙六' and age > 22;

圖片

只能用name這個索引，因為age > 22查出來的id是無序的，前面在講索引的時候有說過索引列的排序規(guī)則。

由此可以看出，使用聯(lián)合索引條件還是比較苛刻的。

取并集（union）

取并集就是將前面例子中的and換成or。

select * from `user` where name = '趙六' or age = 22;

前面執(zhí)行的情況都一樣，根據(jù)條件到各自的索引上去查，之后對查詢的id取并集去重，之后再回表

同樣地，取并集也要求各自索引查出來的主鍵id是排好序的，如果查詢條件換成age > 22時就無法使用取并集的索引合并

select * from `user` where name = '趙六' or age > 22;

排序后取并集（sort-union）

雖然取并集要求各自索引查出來的主鍵id是排好序的，但是如果遇到?jīng)]排好序的情況，mysql會自動對這種情況進(jìn)行優(yōu)化，會先對主鍵id排序，然后再取并集，這種情況就叫 排序后取并集（sort-union）。

比如上面提到的無法直接取并集的sql就符合排序后取并集（sort-union）這種情況

select * from `user` where name = '趙六' or age > 22;

mysql如何選擇索引

在日常生產(chǎn)中，一個表可能會存在多個索引，那么mysql在執(zhí)行sql的時候是如何去判斷該走哪個索引，或者是全表掃描呢？

mysql在選擇索引的時候會根據(jù)索引的使用成本來判斷

一條sql執(zhí)行的成本大致分為兩塊

• IO成本，因為這些頁都是在磁盤的，要想去判斷首先得加載到內(nèi)存，MySQL規(guī)定加載一個頁的成本為1.0
• CPU成本，除了IO成本之外，還有條件判斷的成本，也就是CPU成本。比如前面舉的例子，你得判斷加載的數(shù)據(jù)name = '趙六'符不符合條件，MySQL規(guī)定每判斷一條數(shù)據(jù)花費的成本為0.2

全表掃描成本計算

對于全表掃描來說，成本計算大致如下

mysql會對表進(jìn)行數(shù)據(jù)統(tǒng)計，這個統(tǒng)計是大概，不是特別準(zhǔn)，通過show table status like '表名'可以查看統(tǒng)計數(shù)據(jù)

圖片

比如這個表大致有多少條數(shù)據(jù)rows，以及聚簇索引所占的字節(jié)數(shù)data_length，由于默認(rèn)是16kb，所以就可以計算出(data_length/1024/16)大概有多少個數(shù)據(jù)頁。

所以全表掃描的成本就這么計算了。

rows * 0.2 + data_length/1024/16 * 1.0。

二級索引+回表成本計算

二級索引+回表成本計算比較復(fù)雜，他的成本數(shù)據(jù)依賴兩部分掃描區(qū)間個數(shù)和回表次數(shù)。

為了方便描述掃描區(qū)間，這里我再把上面的圖拿上來。

圖片

select * from `user` where name = '趙六';

對著圖看！

查詢條件name = '趙六'就會產(chǎn)生一個掃描區(qū)間，從id=4的趙六掃描到id=6的趙六。

又比如假設(shè)查詢條件為name > '趙六'，此時就會產(chǎn)生一個從id=7的劉七開始直到數(shù)據(jù)結(jié)束（id=9的王九）的掃描區(qū)間。

又比如假設(shè)查詢條件為name < '李四' and name > '趙六'，此時就會產(chǎn)生兩個掃描區(qū)間，從id=2的張三到id=3的張三算一個，從id=7的劉七開始直到數(shù)據(jù)結(jié)束算另一個。

所以掃描區(qū)間的意思就是符合查詢條件的記錄區(qū)間

二級索引計算成本的時候，mysq規(guī)定讀取一個區(qū)間的成本跟讀取一個頁的IO成本是一樣的，都是1.0。

區(qū)間有了之后，就會根據(jù)統(tǒng)計數(shù)據(jù)估計在這些區(qū)間大致有多少條數(shù)據(jù)，因為要讀寫這些數(shù)據(jù)，那么讀取成本大致就是 條數(shù) * 0.2。

所以走二級索引的成本就是 區(qū)間個數(shù) * 1.0 + 條數(shù) * 0.2。

之后這些數(shù)據(jù)需要回表（如果需要的話），mysql規(guī)定每次回表也跟讀取一個頁的IO成本是一樣，也是1.0。

回表的時候需要對從聚簇索引查出來的數(shù)據(jù)進(jìn)行剩余查詢條件的判斷，就是CPU成本，大致為 條數(shù) * 0.2。

所以回表的成本大致為 條數(shù) * 1.0 + 條數(shù) * 0.2。

所以二級索引+回表的大致成本為 區(qū)間個數(shù) * 1.0 + 條數(shù) * 0.2 + 條數(shù) * 1.0 + 條數(shù) * 0.2。

當(dāng)索引的成本和全表掃描的成本都計算完成之后，mysql會選擇成本最低的索引來執(zhí)行

mysql對上述成本計算結(jié)果還會微調(diào)，但是微調(diào)的值特別小，所以這里我就省略了，并且這里也只是大致介紹了成本計算的規(guī)則，實際情況會更復(fù)雜，比如連表查詢等等，有感興趣的小伙伴查閱相關(guān)的資料。

小結(jié)

總的來說，這一節(jié)主要是讓你明白一件事，mysql在選擇索引的時候，會根據(jù)統(tǒng)計數(shù)據(jù)和成本計算的規(guī)則來計算使用每個索引的成本，然后選擇使用最低成本的索引來執(zhí)行查詢。

索引失效

在日常開發(fā)中，肯定或多或少都遇到過索引失效的問題，這里我總結(jié)一下幾種常見的索引失效的場景。

為了方便解釋，這里我再把圖拿過來。

圖片

不符和最左前綴匹配原則

當(dāng)不符和最左前綴匹配原則的時候會導(dǎo)致索引失效。

比如like以%開頭，索引失效或者是聯(lián)合索引沒走第一個索引列。

比如name和age的聯(lián)合索引，當(dāng)執(zhí)行select * from user where name > '王五' and age > 22;時，那么如果要走索引的話，此時就需要掃描整個索引，因為索引列是先以name字段排序，再以age字段排序的，對于age來說，在整個索引中來說是無序的，從圖中也可以看出 18、23...9，無序，所以無法根據(jù)二分查找定位到age > 22是從哪個索引頁開始的。

所以走索引的話要掃描整個索引，一個一個判斷，最后還要回表，這就很耗性能，不如直接掃描聚簇索引，也就是全表掃描來的痛快。

索引列進(jìn)行了計算

當(dāng)對索引進(jìn)行表達(dá)式計算或者使用函數(shù)時也會導(dǎo)致索引失效。

這個主要是因為索引中保存的是索引字段是原始值，從上面畫的圖可以看出來，當(dāng)經(jīng)過函數(shù)計算后的值，也就沒辦法走索引了。

隱式轉(zhuǎn)換

當(dāng)索引列發(fā)生了隱式轉(zhuǎn)換可能會導(dǎo)致索引失效。

舉個例子，mysql規(guī)定，當(dāng)字符串跟數(shù)字比較時，會把字符串先轉(zhuǎn)成數(shù)字再比較，至于字符串怎么轉(zhuǎn)成數(shù)字，mysql有自己的規(guī)則。

比如說，當(dāng)我執(zhí)行了下面這條sql時就會發(fā)生隱式轉(zhuǎn)換：

select * from `user` where name = 9527;

name字段是個varchar類型，9527，沒加引號，是數(shù)字，mysql根據(jù)規(guī)則會把name字段的值先轉(zhuǎn)換成數(shù)字，再與9527比較，此時由于name字段發(fā)生了轉(zhuǎn)換，所以索引失效了。

圖片

ALL說明沒走索引，失效了。

但是假設(shè)現(xiàn)在對age創(chuàng)建一個索引，執(zhí)行下面這條sql。

select * from `user` where age = '22';

此時age索引就不會失效，主要是因為前面說的那句話：

當(dāng)字符串跟數(shù)字比較時，會把字符串先轉(zhuǎn)成數(shù)字再比較。

于是'22'會被隱式轉(zhuǎn)成數(shù)字，之后再跟age比較，此時age字段并沒有發(fā)生隱式轉(zhuǎn)換，所以不會失效。

所以說，隱式轉(zhuǎn)換可能會導(dǎo)致索引失效。

mysql統(tǒng)計數(shù)據(jù)誤差較大

mysql統(tǒng)計數(shù)據(jù)誤差較大也可能會導(dǎo)致索引失效，因為前面也說了，mysql會根據(jù)統(tǒng)計數(shù)據(jù)來計算使用索引的成本，這樣一旦統(tǒng)計數(shù)據(jù)誤差較大，那么計算出來的成本誤差就大，就可能出現(xiàn)實際走索引的成本小但是計算出來的是走索引的成本大，導(dǎo)致索引失效

當(dāng)出現(xiàn)這種情況時，可以執(zhí)行analyze table 表名這條sql，mysql就會重新統(tǒng)計這些數(shù)據(jù)，索引就可以重新生效了

索引建立原則

單個表索引數(shù)量不宜過多

• 從上面分析我們知道，每個索引都對應(yīng)一顆B+樹，并且葉子節(jié)點存儲了索引列全量的數(shù)據(jù)，一旦索引數(shù)量多，那么就會占有大量磁盤空間
• 同時前面也提到，在查詢之前會對索引成本進(jìn)行計算，一旦索引多，計算的次數(shù)就多，也可能會浪費性能

經(jīng)常出現(xiàn)在where后的字段應(yīng)該建立索引

這個就不用說了，索引就是為了加快速度，如果沒有合適索引，就會全表掃描，對于InnoDB來說，全表掃描就是從聚簇索引的第一個葉子節(jié)點開始，順著鏈表一個一個判斷數(shù)據(jù)服不服合查詢條件

order by、group by后字段可建立索引

比如下面這條sql：

select * from `user` where name = '趙六' order by age asc;

查詢name = '趙六'并且根據(jù)age排序，name和age聯(lián)合索引。

你可能記不清索樹了，我把那個索引樹拿過來。

圖片

此時對著索引樹你可以發(fā)現(xiàn)，當(dāng)name = '趙六'時，age已經(jīng)排好序了（前面介紹索引的說了排序規(guī)則），所以就可以使用age索引列進(jìn)行排序。

頻繁更新的字段不宜建索引

因為索引需要保證按照索引列的值進(jìn)行排序，所以一旦索引字段數(shù)據(jù)頻繁更新，那么為了保證索引的順序，就得頻繁挪動索引列在索引頁中的位置。

比如name和age聯(lián)合索引。

此時把id=9這條數(shù)據(jù)的name從王九改成趙六，那么此時就把這條更改后的數(shù)據(jù)在索引頁上移到王五和id=4的趙六之間，因為name相同時，得保證順序性，同時要按照age排序，id=9的age為9，最小，那么排在最前。

所以頻繁更新的字段建索引就會增加維護(hù)索引的成本。

選擇區(qū)分度高的字段做索引

這個是因為，如果區(qū)分度低，那么索引效果不好。

舉個例子，假設(shè)現(xiàn)在有個性別字段sex，非男即女，如果對sex建索引，假設(shè)男排在女之前，那么索引頁的數(shù)據(jù)排列大致如下：

圖片

這里我畫了6條數(shù)據(jù)，假設(shè)有10w條數(shù)據(jù)那么也是這繼續(xù)排，男在前，女子在后。

此時如果走sex索引，查詢sex=男的數(shù)據(jù)，假設(shè)男女?dāng)?shù)據(jù)對半，那么就掃描的記錄就有5w，之后如果要回表，那么根據(jù)成本計算規(guī)則發(fā)現(xiàn)成本是巨大的，那么此時還不如直接全表掃描來的痛快。

所以要選擇區(qū)分度高的字段做索引