今天來盤點一下關于MySQL索引常見的知識點：

圖片

本文主要是針對InnoDB存儲引擎進行講解。

索引分類

索引的分類可以從不同的維度進行分類

1、按使用的數據結構劃分

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

2、按實際的物理存儲數據構劃分

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

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

3、按索引特性劃分

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

4、按字段個數劃分

• 單列索引
• 聯(liá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索引，只支持自適應Hash索引。

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

圖片

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

圖片

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

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

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

圖片

Hash索引會對索引列name的值進行Hash計算，然后找到對應的槽下面，如下圖所示：

圖片

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

之后如果要查name=李四的數據，只需要對李四進行Hash計算，找到對應的槽，遍歷鏈表，取出name=李四對應的行指針，然后根據行指針去查找對應的數據。

Hash索引優(yōu)缺點

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

B+樹

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

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

聚簇索引

數據頁數據存儲

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

當我們插入一條數據的時候，數據都會存在數據頁中，如下圖所示

圖片

當數據不斷地插入數據頁中，數據會根據主鍵（沒有的話會自動生成）的大小進行排序，形成一個單向鏈表

圖片

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

單個數據頁的數據查找

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

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

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

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

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

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

圖片

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

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

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

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

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

多個數據頁中的數據查找

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

圖片

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

圖片

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

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

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

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

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

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

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

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

圖片

為了圖更加飽滿，我加了一個存放數據的數據頁4。

此時數據頁5就是抽取出來的，存放了下面三個存放數據的數據頁的最小的id和對應的數據頁號。

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

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

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

聚簇索引

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

圖片

數據頁10的作用就跟數據頁5是一樣的。

此時如還要查找id=5的數據，那么應該去數據頁5進行二分查找呢還是去數據頁10進行二分查找呢？

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

圖片

數據頁11就是新抽取的數據頁，存儲了id=1和對應的數據頁5的頁號以及數id=10和對應的數據頁10的頁號。

而這就是B+樹。

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

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

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

這樣就能成功查找到數據了。

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

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

二級索引

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

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

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

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

單列索引

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

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

圖片

mysql會根據name字段的值進行排序，這里我假設張三排在李四前面，當索引列的值相同時，就會根據id排序，所以索引實際上已經根據索引列的值排好序了。

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

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

圖片

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

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

圖片

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

圖片

當數據越來越多越來越多，還會抽取，也會形成三層的一個B+樹，這里我就不畫了。

聯(lián)合索引

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

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

圖片

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

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

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

圖片

小結

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

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

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

圖片

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

回表

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

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

圖片

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

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

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

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

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

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

覆蓋索引

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

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

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

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

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

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

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

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

索引下推

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

圖片

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

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

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

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

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

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

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

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

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

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

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

索引合并

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

結果合并會為下面三種情況：

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

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

取交集（intersect）

當執(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就是交集的意思。

整個過程大致是這樣的，分別根據idx_name和idx_age取出對應的主鍵id，之后將主鍵id取交集，那么這部分交集的id一定同時滿足查詢name = '趙六' and age= 22的查詢條件（仔細想想），之后再根據交集的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í)行的情況都一樣，根據條件到各自的索引上去查，之后對查詢的id取并集去重，之后再回表

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

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

排序后取并集（sort-union）

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

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

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

mysql如何選擇索引

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

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

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

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

全表掃描成本計算

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

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

圖片

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

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

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

二級索引+回表成本計算

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

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

圖片

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

對著圖看！

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

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

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

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

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

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

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

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

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

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

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

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

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

小結

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

索引失效

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

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

圖片

不符和最左前綴匹配原則

當不符和最左前綴匹配原則的時候會導致索引失效。

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

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

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

索引列進行了計算

當對索引進行表達式計算或者使用函數時也會導致索引失效。

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

隱式轉換

當索引列發(fā)生了隱式轉換可能會導致索引失效。

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

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

select * from `user` where name = 9527;

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

圖片

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

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

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

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

當字符串跟數字比較時，會把字符串先轉成數字再比較。

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

所以說，隱式轉換可能會導致索引失效。

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

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

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

索引建立原則

單個表索引數量不宜過多

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

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

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

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

比如下面這條sql：

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

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

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

圖片

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

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

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

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

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

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

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

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

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

圖片

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

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

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