1.5萬字+30張圖盤點(diǎn)索引常見的11個(gè)知識(shí)點(diǎn)
今天來盤點(diǎn)一下關(guān)于MySQL索引常見的知識(shí)點(diǎn)
本來這篇文章我前兩個(gè)星期就打算寫了，提綱都列好了，但是后面我去追《漫長(zhǎng)的季節(jié)》這部劇去了，這就花了一個(gè)周末的時(shí)間，再加上后面一些其它的事，導(dǎo)致沒來得及寫
不過不要緊，好飯不怕晚，雖遲但到，走起，開干！
對(duì)了，本文主要是針對(duì)InnoDB存儲(chǔ)引擎進(jìn)行講解。
索引分類
索引的分類可以從不同的維度進(jìn)行分類
1、按使用的數(shù)據(jù)結(jié)構(gòu)劃分
B+樹索引
Hash索引
...
2、按實(shí)際的物理存儲(chǔ)數(shù)據(jù)構(gòu)劃分
聚簇索引
非聚簇索引（二級(jí)索引）
聚簇索引和非聚簇索引后面會(huì)著重說。
3、按索引特性劃分
主鍵索引
唯一索引
普通索引
全文索引
...
4、按字段個(gè)數(shù)劃分
單列索引
聯(lián)合索引
索引數(shù)據(jù)結(jié)構(gòu)
準(zhǔn)備
為了接下來文章更好地講解，這里我準(zhǔn)備了一張user表，接下來整篇文章的示例會(huì)以這張表來講解
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索引其實(shí)用的不多，最主要是因?yàn)樽畛Ｒ姷拇鎯?chǔ)引擎InnoDB不支持顯示地創(chuàng)建Hash索引，只支持自適應(yīng)Hash索引。
雖然可以使用sql語句在InnoDB顯示聲明Hash索引，但是其實(shí)是不生效的
對(duì)name字段建立Hash索引，但是通過show index from 表名就會(huì)發(fā)現(xiàn)實(shí)際還是B+樹
在存儲(chǔ)引擎中，Memory引擎支持Hash索引
Hash索引其實(shí)有點(diǎn)像Java中的HashMap底層的數(shù)據(jù)結(jié)構(gòu)，他也有很多的槽，存的也是鍵值對(duì)，鍵值為索引列，值為數(shù)據(jù)的這條數(shù)據(jù)的行指針，通過行指針就可以找到數(shù)據(jù)
假設(shè)現(xiàn)在user表用Memory存儲(chǔ)引擎，對(duì)name字段建立Hash索引，表中插入三條數(shù)據(jù)
Hash索引會(huì)對(duì)索引列name的值進(jìn)行Hash計(jì)算，然后找到對(duì)應(yīng)的槽下面，如下圖所示
當(dāng)遇到name字段的Hash值相同時(shí)，也就是Hash沖突，就會(huì)形成一個(gè)鏈表，比如有name=張三有兩條數(shù)據(jù)，就會(huì)形成一個(gè)鏈表。
之后如果要查name=李四的數(shù)據(jù)，只需要對(duì)李四進(jìn)行Hash計(jì)算，找到對(duì)應(yīng)的槽，遍歷鏈表，取出name=李四對(duì)應(yīng)的行指針，然后根據(jù)行指針去查找對(duì)應(yīng)的數(shù)據(jù)。
Hash索引優(yōu)缺點(diǎn)
hash索引只能用于等值比較，所以查詢效率非常高
不支持范圍查詢，也不支持排序，因?yàn)樗饕械姆植际菬o序的
B+樹
B+樹是mysql索引中用的最多的數(shù)據(jù)結(jié)構(gòu)，這里先不介紹，下一節(jié)會(huì)著重介紹。
除了Hash和B+樹之外，還有全文索引等其它索引，這里就不討論了
聚簇索引
數(shù)據(jù)頁數(shù)據(jù)存儲(chǔ)
我們知道，我們插入表的數(shù)據(jù)其實(shí)最終都要持久化到磁盤上，InnoDB為了方便管理這些數(shù)據(jù)，提出了頁的概念，它會(huì)將數(shù)據(jù)劃分到多個(gè)頁中，每個(gè)頁大小默認(rèn)是16KB，這個(gè)頁我們可以稱為數(shù)據(jù)頁。
當(dāng)我們插入一條數(shù)據(jù)的時(shí)候，數(shù)據(jù)都會(huì)存在數(shù)據(jù)頁中，如下圖所示
當(dāng)數(shù)據(jù)不斷地插入數(shù)據(jù)頁中，數(shù)據(jù)會(huì)根據(jù)主鍵（沒有的話會(huì)自動(dòng)生成）的大小進(jìn)行排序，形成一個(gè)單向鏈表
數(shù)據(jù)頁中除了會(huì)存儲(chǔ)我們插入的數(shù)據(jù)之外，還會(huì)有一部分空間用來存儲(chǔ)額外的信息，額外的信息類型比較多，后面遇到一個(gè)說一個(gè)
單個(gè)數(shù)據(jù)頁的數(shù)據(jù)查找
既然數(shù)據(jù)會(huì)存在數(shù)據(jù)頁中，那么該如何從數(shù)據(jù)頁中去查數(shù)據(jù)呢？
假設(shè)現(xiàn)在需要在數(shù)據(jù)頁中定位到id=2的這條記錄的數(shù)據(jù)，如何快速定位？
有一種笨辦法就是從頭開始順著鏈表遍歷就行了，判斷id是不是等于2，如果等于2就取出數(shù)據(jù)就行了。
雖然這種方法可行，但是如果一個(gè)數(shù)據(jù)頁存儲(chǔ)的數(shù)據(jù)多，幾十或者是幾百條數(shù)據(jù)，每次都這么遍歷，不是太麻煩了
所以mysql想了一個(gè)好辦法，那就是給這些數(shù)據(jù)分組
假設(shè)數(shù)據(jù)頁中存了12條數(shù)據(jù)，那么整個(gè)分組大致如下圖所示
為了方便了，我這里只標(biāo)出了id值，省略了其它字段的值
這里我假設(shè)每4條數(shù)據(jù)算一個(gè)組，圖上就有3個(gè)組，組分好之后，mysql會(huì)取出每組中最大的id值，就是圖中的4、8、12，放在一起，在數(shù)據(jù)頁中找個(gè)位置存起來，這就是前面提到的數(shù)據(jù)頁存儲(chǔ)的額外信息之一，被稱為頁目錄
假設(shè)此時(shí)要查詢id=6的數(shù)據(jù)之后，此時(shí)只需要從頁目錄中根據(jù)二分查找，發(fā)現(xiàn)在4-8之間，由于4和8是他們所在分組的最大的id，那么id=6肯定在8那個(gè)分組中，之后就會(huì)到id=8的那個(gè)分組中，遍歷每個(gè)數(shù)據(jù)，判斷id是不是等于6即可。
由于mysql規(guī)定每個(gè)組的數(shù)據(jù)條數(shù)大概為4~8條，所以肯定比遍歷整個(gè)數(shù)據(jù)頁的數(shù)據(jù)快的多
上面分組的情況實(shí)際上我做了一點(diǎn)簡(jiǎn)化，但是不耽誤理解
多個(gè)數(shù)據(jù)頁中的數(shù)據(jù)查找
當(dāng)我們不斷的往表中插入數(shù)據(jù)的時(shí)候，數(shù)據(jù)占用空間就會(huì)不斷變大，但是一個(gè)數(shù)據(jù)頁的大小是一定的，當(dāng)一個(gè)數(shù)據(jù)頁存不下數(shù)據(jù)的時(shí)候，就會(huì)重新創(chuàng)建一個(gè)數(shù)據(jù)頁來存儲(chǔ)數(shù)據(jù)
mysql為了區(qū)分每個(gè)頁，會(huì)為每個(gè)數(shù)據(jù)頁分配一個(gè)頁號(hào)，存在額外信息的存儲(chǔ)空間中，同時(shí)額外信息還會(huì)存儲(chǔ)當(dāng)前數(shù)據(jù)頁的前一個(gè)和后一個(gè)數(shù)據(jù)頁的位置，從而形成數(shù)據(jù)頁之間的雙向鏈表
數(shù)據(jù)頁2的頁號(hào)就是2，數(shù)據(jù)頁3的頁號(hào)就是3，這里我為了方便理解，就直接寫數(shù)據(jù)頁幾。
并且mysql規(guī)定，前一個(gè)數(shù)據(jù)頁的存儲(chǔ)數(shù)據(jù)id的最大值要小于后一個(gè)數(shù)據(jù)頁的存儲(chǔ)數(shù)據(jù)id的最小值，這樣就實(shí)現(xiàn)了數(shù)據(jù)在所有數(shù)據(jù)頁中按照id的大小排序。
現(xiàn)在，如果有多個(gè)數(shù)據(jù)頁，當(dāng)我們需要查找id=5的數(shù)據(jù)，怎么辦呢？
當(dāng)然還是可以用上面的笨辦法，那就是從第一個(gè)數(shù)據(jù)頁開始遍歷，然后遍歷每個(gè)數(shù)據(jù)頁中的數(shù)據(jù)，最終也可以找到id=5的數(shù)據(jù)。
但是你仔細(xì)想想，這個(gè)笨辦法就相當(dāng)于全表掃描了呀，這肯定是不行的。
那么怎么優(yōu)化呢？
mysql優(yōu)化的思路其實(shí)跟前面單數(shù)據(jù)頁查找數(shù)據(jù)的優(yōu)化思路差不多
它會(huì)將每個(gè)數(shù)據(jù)頁中最小的id拿出來，單獨(dú)放到另一個(gè)數(shù)據(jù)頁中，這個(gè)數(shù)據(jù)頁不存儲(chǔ)我們實(shí)際插入的數(shù)據(jù)，只存儲(chǔ)最小的id和這個(gè)id所在數(shù)據(jù)頁的頁號(hào)，如圖所示
為了圖更加飽滿，我加了一個(gè)存放數(shù)據(jù)的數(shù)據(jù)頁4
此時(shí)數(shù)據(jù)頁5就是抽取出來的，存放了下面三個(gè)存放數(shù)據(jù)的數(shù)據(jù)頁的最小的id和對(duì)應(yīng)的數(shù)據(jù)頁號(hào)
如果此時(shí)查找id=5的數(shù)據(jù)就很方便了，大致分為以下幾個(gè)步驟：
從數(shù)據(jù)頁5直接根據(jù)二分查找，發(fā)現(xiàn)在4-7之間
由于4和7是所在數(shù)據(jù)頁最小的id，那么此時(shí)id=5的數(shù)據(jù)必在id=4的數(shù)據(jù)頁上(因?yàn)閕d=7的數(shù)據(jù)頁最小的id就是7)，
接下來就到id=4對(duì)應(yīng)的數(shù)據(jù)頁2的頁號(hào)找到數(shù)據(jù)頁2
之后再根據(jù)前面提到的根據(jù)數(shù)據(jù)的主鍵id從單個(gè)數(shù)據(jù)頁查找的流程查找數(shù)據(jù)
這樣就實(shí)現(xiàn)了根據(jù)主鍵id到在多個(gè)數(shù)據(jù)頁之間查找數(shù)據(jù)
聚簇索引
隨著數(shù)據(jù)量不斷增多，存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)頁不斷變多，數(shù)據(jù)頁5的數(shù)據(jù)就會(huì)越來越多，但是每個(gè)數(shù)據(jù)頁默認(rèn)就16k，所以數(shù)據(jù)頁5也會(huì)分裂出多個(gè)數(shù)據(jù)頁的情況，如下圖
數(shù)據(jù)頁10的作用就跟數(shù)據(jù)頁5是一樣的
此時(shí)如還要查找id=5的數(shù)據(jù)，那么應(yīng)該去數(shù)據(jù)頁5進(jìn)行二分查找呢還是去數(shù)據(jù)頁10進(jìn)行二分查找呢？
笨辦法就是遍歷，但是真沒必要，mysql會(huì)去抽取數(shù)據(jù)頁5和數(shù)據(jù)頁10存儲(chǔ)的最小的數(shù)據(jù)的id和對(duì)應(yīng)的數(shù)據(jù)頁號(hào)，單獨(dú)拎出來放到一個(gè)數(shù)據(jù)頁中，如下圖
數(shù)據(jù)頁11就是新抽取的數(shù)據(jù)頁，存儲(chǔ)了id=1和對(duì)應(yīng)的數(shù)據(jù)頁5的頁號(hào)以及數(shù)id=10和對(duì)應(yīng)的數(shù)據(jù)頁10的頁號(hào)
而這就是B+樹。
一般來說，mysql數(shù)據(jù)庫(kù)的B+樹一般三層就可以放下幾千萬條數(shù)據(jù)
此時(shí)查找id=5的數(shù)據(jù)，大致分為以下幾個(gè)步驟：
從數(shù)據(jù)頁11根據(jù)二分查找定位到id=5對(duì)應(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é)點(diǎn)存儲(chǔ)實(shí)際插入的數(shù)據(jù)的B+樹就被稱為聚簇索引，非葉子節(jié)點(diǎn)存儲(chǔ)的就是記錄的id和對(duì)應(yīng)的數(shù)據(jù)頁號(hào)。
所以對(duì)于InnoDB存儲(chǔ)引擎來說，數(shù)據(jù)本身就存儲(chǔ)在一顆B+樹中。
二級(jí)索引
二級(jí)索引也被稱為非聚簇索引，本身也就是一顆B+樹，一個(gè)二級(jí)索引對(duì)應(yīng)一顆B+樹，但是二級(jí)索引B+樹存儲(chǔ)的數(shù)據(jù)跟聚簇索引不一樣。
聚簇索引前面也說了，葉子節(jié)點(diǎn)存的就是我們插入到數(shù)據(jù)庫(kù)的數(shù)據(jù)，非葉子節(jié)點(diǎn)存的就是數(shù)據(jù)的主鍵id和對(duì)應(yīng)的數(shù)據(jù)頁號(hào)。
而二級(jí)索引葉子節(jié)點(diǎn)存的是索引列的數(shù)據(jù)和對(duì)應(yīng)的主鍵id，非葉子節(jié)點(diǎn)除了索引列的數(shù)據(jù)和id之外，還會(huì)存數(shù)據(jù)頁的頁號(hào)。
前面提到的數(shù)據(jù)頁，其實(shí)真正是叫索引頁，因?yàn)槿~子節(jié)點(diǎn)存的是實(shí)際表的數(shù)據(jù)，所以我就叫數(shù)據(jù)頁了，接下來因?yàn)檎嬲v到索引了，所以我就將二級(jí)索引的頁稱為索引頁，你知道是同一個(gè)，但是存儲(chǔ)的數(shù)據(jù)不一樣就可以了。
單列索引
假設(shè)，我們現(xiàn)在對(duì)name字段加了一個(gè)普通非唯一索引，那么name就是索引列，同時(shí)name這個(gè)索引也就是單列索引
此時(shí)如果往表中插入三條數(shù)據(jù)，那么name索引的葉子節(jié)點(diǎn)存的數(shù)據(jù)就如下圖所示
mysql會(huì)根據(jù)name字段的值進(jìn)行排序，這里我假設(shè)張三排在李四前面，當(dāng)索引列的值相同時(shí)，就會(huì)根據(jù)id排序，所以索引實(shí)際上已經(jīng)根據(jù)索引列的值排好序了。
這里肯定有小伙伴疑問，name字段存儲(chǔ)的中文也可以排序么？
答案是可以的，并且mysql支持很多種排序規(guī)則，我們?cè)诮〝?shù)據(jù)庫(kù)或者是建表的時(shí)候等都可以指定排序規(guī)則，并且后面文章涉及到的字符串排序都是我隨便排的，實(shí)際情況可能不一樣。
對(duì)于單個(gè)索引列數(shù)據(jù)查找也是跟前面說的聚簇索引一樣，也會(huì)對(duì)數(shù)據(jù)分組，之后可以根據(jù)二分查找在單個(gè)索引列來查找數(shù)據(jù)。
當(dāng)數(shù)據(jù)不斷增多，一個(gè)索引頁存儲(chǔ)不下數(shù)據(jù)的時(shí)候，也會(huì)用多個(gè)索引頁來存儲(chǔ)，并且索引頁直接也會(huì)形成雙向鏈表
當(dāng)索引頁不斷增多是，為了方便在不同索引頁中查找數(shù)據(jù)，也就會(huì)抽取一個(gè)索引頁，除了存頁中id，同時(shí)也會(huì)存儲(chǔ)這個(gè)id對(duì)應(yīng)的索引列的值
當(dāng)數(shù)據(jù)越來越多越來越多，還會(huì)抽取，也會(huì)形成三層的一個(gè)B+樹，這里我就不畫了。
聯(lián)合索引
除了單列索引，聯(lián)合索引其實(shí)也是一樣的，只不過索引頁存的數(shù)據(jù)就多了一些索引列
比如，在name和age上建立一個(gè)聯(lián)合索引，此時(shí)單個(gè)索引頁就如圖所示
先以name排序，name相同時(shí)再以age排序，如果再有其它列，依次類推，最后再以id排序。
相比于只有name一個(gè)字段的索引來說，索引頁就多存了一個(gè)索引列。
最后形成的B+樹簡(jiǎn)化為如下圖
小結(jié)
其實(shí)從上面的分析可以看出，聚簇索引和非聚簇索引主要區(qū)別有以下幾點(diǎn)
聚簇索引的葉子節(jié)點(diǎn)存的是所有列的值，非聚簇索引的葉子節(jié)點(diǎn)只存了索引列的值和主鍵id
聚簇索引的數(shù)據(jù)是按照id排序，非聚簇索引的數(shù)據(jù)是按照索引列排序
聚簇索引的非葉子節(jié)點(diǎn)存的是主鍵id和頁號(hào)，非聚簇索引的非葉子節(jié)點(diǎn)存的是索引列、主鍵id、頁號(hào)
由于后面這個(gè)索引樹會(huì)經(jīng)常用到，為了你方便比較，所以我根據(jù)上面索引樹的數(shù)據(jù)在表中插入了對(duì)應(yīng)的數(shù)據(jù)，sql在文末
實(shí)際情況下索引B+樹可能并不是按照我圖中畫出來的那樣排序，但不耽誤理解。
回表
講完二級(jí)索引，接下來講一講如何使用二級(jí)索引查找數(shù)據(jù)。
這里假設(shè)對(duì)name字段創(chuàng)建了一個(gè)索引，并且表里就存了上面示例中的幾條數(shù)據(jù)，這里我再把圖拿過來
那么對(duì)于下面這條sql應(yīng)該如何執(zhí)行？
select?*?from?`user`?where?name?=?'趙六';
由于查詢條件是name = '趙六'，所以會(huì)走name索引
整個(gè)過程大致分為以下幾個(gè)步驟：
從最上面那層索引頁開始二分查找，我們圖中就是索引頁113，如果索引頁113上面還有一層，就從上面一層二分查找
在索引頁113查找到
趙六在王五和劉七之間，之后到王五對(duì)應(yīng)的索引頁111上去查找趙六在索引頁111找到
趙六的第一條記錄，也就是id=4的那條由于是
select *，還要查其它字段，此時(shí)就會(huì)根據(jù)id=4到聚簇索引中查找其它字段數(shù)據(jù)，這個(gè)查找過程前面說了很多次了，這個(gè)根據(jù)id=4到聚簇索引中查找數(shù)據(jù)的過程就被稱為回表由于是非唯一索引，所以
趙六這個(gè)值可能會(huì)有重復(fù)，所以接著就會(huì)在索引頁111順著鏈表繼續(xù)遍歷，如果name還是趙六，那么還會(huì)根據(jù)id值進(jìn)行回表，如此重復(fù)，一直這么遍歷，直至name不再等于趙六為止，對(duì)于圖示，其實(shí)就是兩條數(shù)據(jù)
從上面的二級(jí)索引的查找數(shù)據(jù)過程分析，就明白了回表的意思，就是先從二級(jí)索引根據(jù)查詢條件字段值查找對(duì)應(yīng)的主鍵id，之后根據(jù)id再到聚簇索引查找其它字段的值。
覆蓋索引
上一節(jié)說當(dāng)執(zhí)行select * from user where name = '趙六';這條sql的時(shí)候，會(huì)先從索引頁中查出來name = '趙六';對(duì)應(yīng)的主鍵id，之后再回表，到聚簇索引中查詢其它字段的值。
那么當(dāng)執(zhí)行下面這條sql，又會(huì)怎樣呢？
select?id?from?`user`?where?name?=?'趙六';
這次查詢字段從select *變成select id，查詢條件不變，所以也會(huì)走name索引
所以還是跟前面一樣了，先從索引頁中查出來name = '趙六';對(duì)應(yīng)的主鍵id之后，驚訝的發(fā)現(xiàn)，sql中需要查詢字段的id值已經(jīng)查到了，那次此時(shí)壓根就不需要回表了，已經(jīng)查到id了，還回什么表。
而這種需要查詢的字段都在索引列中的情況就被稱為覆蓋索引，索引列覆蓋了查詢字段的意思。
當(dāng)使用覆蓋索引時(shí)會(huì)減少回表的次數(shù)，這樣查詢速度更快，性能更高。
所以，在日常開發(fā)中，盡量不要select * ，需要什么查什么，如果出現(xiàn)覆蓋索引的情況，查詢會(huì)快很多。
索引下推
假設(shè)現(xiàn)在對(duì)表建立了一個(gè)name和age的聯(lián)合索引，為了方便理解，我把前面的圖再拿過來
接下來要執(zhí)行如下的sql
select?*?from?`user`?where?name?>?'王五'?and?age?>?22;
在MySQL5.6(不包括5.6)之前，整個(gè)sql大致執(zhí)行步驟如下：
先根據(jù)二分查找，定位到
name > '王五'的第一條數(shù)據(jù)，也就是id=4的那個(gè)趙六之后就會(huì)根據(jù)id=4進(jìn)行回表操作，到聚簇索引中查找id=4其它字段的數(shù)據(jù)，然后判斷數(shù)據(jù)中的age是否大于22，是的話就說明是我們需要查找的數(shù)據(jù)，否則就不是
之后順著鏈表，繼續(xù)遍歷，然后找到一條記錄就回一次表，然后判斷age，如此反復(fù)下去，直至結(jié)束
所以對(duì)于圖上所示，整個(gè)搜索過程會(huì)經(jīng)歷5次回表操作，兩個(gè)趙六，兩個(gè)劉七，一個(gè)王九，最后符合條件的也就是id=6的趙六那條數(shù)據(jù)，其余age不符和。
雖然這么執(zhí)行沒什么問題，但是不知有沒有發(fā)現(xiàn)其實(shí)沒必要進(jìn)行那么多次回表，因?yàn)楣鈴纳厦娴乃饕龍D示就可以看出，符合name > '王五' and age > 22的數(shù)據(jù)就id=6的趙六那條數(shù)據(jù)
所以在MySQL5.6之后，對(duì)上面的age > 22判斷邏輯進(jìn)行了優(yōu)化
前面還是一樣，定位查找到id=4的那個(gè)趙六，之后就不回表來判斷age了，因?yàn)樗饕杏衋ge的值了，那么直接根據(jù)索引中age判斷是否大于22，如果大于的話，再回表查詢剩余的字段數(shù)據(jù)（因?yàn)槭?code>select *），然后再順序鏈表遍歷，直至結(jié)束
所以這樣優(yōu)化之后，回表次數(shù)就成1了，相比于前面的5次，大大減少了回表的次數(shù)。
而這個(gè)優(yōu)化，就被稱為索引下推，就是為了減少回表的次數(shù)。
之所以這個(gè)優(yōu)化叫索引下推，其實(shí)是跟判斷
age > 22邏輯執(zhí)行的地方有關(guān)，這里就不過多贅述了。
索引合并
索引合并（index merge）是從MySQL5.1開始引入的索引優(yōu)化機(jī)制，在之前的MySQL版本中，一條sql多個(gè)查詢條件只能使用一個(gè)索引，但是引入了索引合并機(jī)制之后，MySQL在某些特殊的情況下會(huì)掃描多個(gè)索引，然后將掃描結(jié)果進(jìn)行合并
結(jié)果合并會(huì)為下面三種情況：
取交集（intersect）
取并集（union）
排序后取并集（sort-union）
為了不耽誤演示，刪除之前所有的索引，然后為name和age各自分別創(chuàng)建一個(gè)二級(jí)索引idx_name和idx_age
取交集（intersect）
當(dāng)執(zhí)行下面這條sql就會(huì)出現(xiàn)取交集的情況
select?*?from?`user`?where?name?=?'趙六'?and?age=?22;
查看執(zhí)行計(jì)劃
type是index_merge，并且possible_key和key都是idx_name和idx_age，說明使用了索引合并，并且Extra有Using intersect(idx_age,idx_name)，intersect就是交集的意思。
整個(gè)過程大致是這樣的，分別根據(jù)idx_name和idx_age取出對(duì)應(yīng)的主鍵id，之后將主鍵id取交集，那么這部分交集的id一定同時(shí)滿足查詢name = '趙六' and age= 22的查詢條件（仔細(xì)想想），之后再根據(jù)交集的id回表
不過要想使用取交集的聯(lián)合索引，需要滿足各自索引查出來的主鍵id是排好序的，這是為了方便可以快速的取交集
比如下面這條sql就無法使用聯(lián)合索引
select?*?from?`user`?where?name?=?'趙六'?and?age?>?22;
只能用name這個(gè)索引，因?yàn)?code>age > 22查出來的id是無序的，前面在講索引的時(shí)候有說過索引列的排序規(guī)則
由此可以看出，使用聯(lián)合索引條件還是比較苛刻的。
取并集（union）
取并集就是將前面例子中的and換成or
select?*?from?`user`?where?name?=?'趙六'?or?age?=?22;
前面執(zhí)行的情況都一樣，根據(jù)條件到各自的索引上去查，之后對(duì)查詢的id取并集去重，之后再回表
同樣地，取并集也要求各自索引查出來的主鍵id是排好序的，如果查詢條件換成age > 22時(shí)就無法使用取并集的索引合并
select?*?from?`user`?where?name?=?'趙六'?or?age?>?22;
排序后取并集（sort-union）
雖然取并集要求各自索引查出來的主鍵id是排好序的，但是如果遇到?jīng)]排好序的情況，mysql會(huì)自動(dòng)對(duì)這種情況進(jìn)行優(yōu)化，會(huì)先對(duì)主鍵id排序，然后再取并集，這種情況就叫 排序后取并集（sort-union）。
比如上面提到的無法直接取并集的sql就符合排序后取并集（sort-union）這種情況
select?*?from?`user`?where?name?=?'趙六'?or?age?>?22;
mysql如何選擇索引
在日常生產(chǎn)中，一個(gè)表可能會(huì)存在多個(gè)索引，那么mysql在執(zhí)行sql的時(shí)候是如何去判斷該走哪個(gè)索引，或者是全表掃描呢？
mysql在選擇索引的時(shí)候會(huì)根據(jù)索引的使用成本來判斷
一條sql執(zhí)行的成本大致分為兩塊
IO成本，因?yàn)檫@些頁都是在磁盤的，要想去判斷首先得加載到內(nèi)存，MySQL規(guī)定加載一個(gè)頁的成本為1.0
CPU成本，除了IO成本之外，還有條件判斷的成本，也就是CPU成本。比如前面舉的例子，你得判斷加載的數(shù)據(jù)
name = '趙六'符不符合條件，MySQL規(guī)定每判斷一條數(shù)據(jù)花費(fèi)的成本為0.2
全表掃描成本計(jì)算
對(duì)于全表掃描來說，成本計(jì)算大致如下
mysql會(huì)對(duì)表進(jìn)行數(shù)據(jù)統(tǒng)計(jì)，這個(gè)統(tǒng)計(jì)是大概，不是特別準(zhǔn)，通過show table status like '表名'可以查看統(tǒng)計(jì)數(shù)據(jù)
比如這個(gè)表大致有多少條數(shù)據(jù)rows，以及聚簇索引所占的字節(jié)數(shù)data_length，由于默認(rèn)是16kb，所以就可以計(jì)算出(data_length/1024/16)大概有多少個(gè)數(shù)據(jù)頁。
所以全表掃描的成本就這么計(jì)算了
rows * 0.2 + data_length/1024/16 * 1.0
二級(jí)索引+回表成本計(jì)算
二級(jí)索引+回表成本計(jì)算比較復(fù)雜，他的成本數(shù)據(jù)依賴兩部分掃描區(qū)間個(gè)數(shù)和回表次數(shù)
為了方便描述掃描區(qū)間，這里我再把上面的圖拿上來
select?*?from?`user`?where?name?=?'趙六';
對(duì)著圖看！
查詢條件name = '趙六'就會(huì)產(chǎn)生一個(gè)掃描區(qū)間，從id=4的趙六掃描到id=6的趙六
又比如假設(shè)查詢條件為name > '趙六'，此時(shí)就會(huì)產(chǎn)生一個(gè)從id=7的劉七開始直到數(shù)據(jù)結(jié)束（id=9的王九）的掃描區(qū)間
又比如假設(shè)查詢條件為name < '李四' and name > '趙六'，此時(shí)就會(huì)產(chǎn)生兩個(gè)掃描區(qū)間，從id=2的張三到id=3的張三算一個(gè)，從id=7的劉七開始直到數(shù)據(jù)結(jié)束算另一個(gè)
所以掃描區(qū)間的意思就是符合查詢條件的記錄區(qū)間
二級(jí)索引計(jì)算成本的時(shí)候，mysq規(guī)定讀取一個(gè)區(qū)間的成本跟讀取一個(gè)頁的IO成本是一樣的，都是1.0
區(qū)間有了之后，就會(huì)根據(jù)統(tǒng)計(jì)數(shù)據(jù)估計(jì)在這些區(qū)間大致有多少條數(shù)據(jù)，因?yàn)橐x寫這些數(shù)據(jù)，那么讀取成本大致就是 條數(shù) * 0.2
所以走二級(jí)索引的成本就是?區(qū)間個(gè)數(shù) * 1.0 + 條數(shù) * 0.2
之后這些數(shù)據(jù)需要回表（如果需要的話），mysql規(guī)定每次回表也跟讀取一個(gè)頁的IO成本是一樣，也是1.0
回表的時(shí)候需要對(duì)從聚簇索引查出來的數(shù)據(jù)進(jìn)行剩余查詢條件的判斷，就是CPU成本，大致為 條數(shù) * 0.2
所以回表的成本大致為?條數(shù) * 1.0 + 條數(shù) * 0.2
所以二級(jí)索引+回表的大致成本為?區(qū)間個(gè)數(shù) * 1.0 + 條數(shù) * 0.2 + 條數(shù) * 1.0 + 條數(shù) * 0.2
當(dāng)索引的成本和全表掃描的成本都計(jì)算完成之后，mysql會(huì)選擇成本最低的索引來執(zhí)行
mysql對(duì)上述成本計(jì)算結(jié)果還會(huì)微調(diào)，但是微調(diào)的值特別小，所以這里我就省略了，并且這里也只是大致介紹了成本計(jì)算的規(guī)則，實(shí)際情況會(huì)更復(fù)雜，比如連表查詢等等，有感興趣的小伙伴查閱相關(guān)的資料
小結(jié)
總的來說，這一節(jié)主要是讓你明白一件事，mysql在選擇索引的時(shí)候，會(huì)根據(jù)統(tǒng)計(jì)數(shù)據(jù)和成本計(jì)算的規(guī)則來計(jì)算使用每個(gè)索引的成本，然后選擇使用最低成本的索引來執(zhí)行查詢
索引失效
在日常開發(fā)中，肯定或多或少都遇到過索引失效的問題，這里我總結(jié)一下幾種常見的索引失效的場(chǎng)景
為了方便解釋，這里我再把圖拿過來
不符和最左前綴匹配原則
當(dāng)不符和最左前綴匹配原則的時(shí)候會(huì)導(dǎo)致索引失效
比如like以%開頭，索引失效或者是聯(lián)合索引沒走第一個(gè)索引列。
比如name和age的聯(lián)合索引，當(dāng)執(zhí)行select * from user where name > '王五' and age > 22;時(shí)，那么如果要走索引的話，此時(shí)就需要掃描整個(gè)索引，因?yàn)樗饕惺窍纫詎ame字段排序，再以age字段排序的，對(duì)于age來說，在整個(gè)索引中來說是無序的，從圖中也可以看出 18、23...9，無序，所以無法根據(jù)二分查找定位到age > 22是從哪個(gè)索引頁開始的，
所以走索引的話要掃描整個(gè)索引，一個(gè)一個(gè)判斷，最后還要回表，這就很耗性能，不如直接掃描聚簇索引，也就是全表掃描來的痛快。
索引列進(jìn)行了計(jì)算
當(dāng)對(duì)索引進(jìn)行表達(dá)式計(jì)算或者使用函數(shù)時(shí)也會(huì)導(dǎo)致索引失效
這個(gè)主要是因?yàn)樗饕斜４娴氖撬饕侄问窃贾?，從上面畫的圖可以看出來，當(dāng)經(jīng)過函數(shù)計(jì)算后的值，也就沒辦法走索引了
隱式轉(zhuǎn)換
當(dāng)索引列發(fā)生了隱式轉(zhuǎn)換可能會(huì)導(dǎo)致索引失效
舉個(gè)例子，mysql規(guī)定，當(dāng)字符串跟數(shù)字比較時(shí)，會(huì)把字符串先轉(zhuǎn)成數(shù)字再比較，至于字符串怎么轉(zhuǎn)成數(shù)字，mysql有自己的規(guī)則
比如說，當(dāng)我執(zhí)行了下面這條sql時(shí)就會(huì)發(fā)生隱式轉(zhuǎn)換
select?*?from?`user`?where?name?=?9527;
name字段是個(gè)varchar類型，9527，沒加引號(hào)，是數(shù)字，mysql根據(jù)規(guī)則會(huì)把name字段的值先轉(zhuǎn)換成數(shù)字，再與9527比較，此時(shí)由于name字段發(fā)生了轉(zhuǎn)換，所以索引失效了
ALL說明沒走索引，失效了。
但是假設(shè)現(xiàn)在對(duì)age創(chuàng)建一個(gè)索引，執(zhí)行下面這條sql
select?*?from?`user`?where?age?=?'22';
此時(shí)age索引就不會(huì)失效，主要是因?yàn)榍懊嬲f的那句話：
當(dāng)字符串跟數(shù)字比較時(shí)，會(huì)把字符串先轉(zhuǎn)成數(shù)字再比較
于是'22'會(huì)被隱式轉(zhuǎn)成數(shù)字，之后再跟age比較，此時(shí)age字段并沒有發(fā)生隱式轉(zhuǎn)換，所以不會(huì)失效。
所以說，隱式轉(zhuǎn)換可能會(huì)導(dǎo)致索引失效。
mysql統(tǒng)計(jì)數(shù)據(jù)誤差較大
mysql統(tǒng)計(jì)數(shù)據(jù)誤差較大也可能會(huì)導(dǎo)致索引失效，因?yàn)榍懊嬉舱f了，mysql會(huì)根據(jù)統(tǒng)計(jì)數(shù)據(jù)來計(jì)算使用索引的成本，這樣一旦統(tǒng)計(jì)數(shù)據(jù)誤差較大，那么計(jì)算出來的成本誤差就大，就可能出現(xiàn)實(shí)際走索引的成本小但是計(jì)算出來的是走索引的成本大，導(dǎo)致索引失效
當(dāng)出現(xiàn)這種情況時(shí)，可以執(zhí)行analyze table 表名這條sql，mysql就會(huì)重新統(tǒng)計(jì)這些數(shù)據(jù)，索引就可以重新生效了
索引建立原則
單個(gè)表索引數(shù)量不宜過多
從上面分析我們知道，每個(gè)索引都對(duì)應(yīng)一顆B+樹，并且葉子節(jié)點(diǎn)存儲(chǔ)了索引列全量的數(shù)據(jù)，一旦索引數(shù)量多，那么就會(huì)占有大量磁盤空間
同時(shí)前面也提到，在查詢之前會(huì)對(duì)索引成本進(jìn)行計(jì)算，一旦索引多，計(jì)算的次數(shù)就多，也可能會(huì)浪費(fèi)性能
經(jīng)常出現(xiàn)在where后的字段應(yīng)該建立索引
這個(gè)就不用說了，索引就是為了加快速度，如果沒有合適索引，就會(huì)全表掃描，對(duì)于InnoDB來說，全表掃描就是從聚簇索引的第一個(gè)葉子節(jié)點(diǎn)開始，順著鏈表一個(gè)一個(gè)判斷數(shù)據(jù)服不服合查詢條件
order by、group by后字段可建立索引
比如下面這條sql
select?*?from?`user`?where?name?=?'趙六'?order?by?age?asc;
查詢name = '趙六'并且根據(jù)age排序，name和age聯(lián)合索引
你可能記不清索樹了，我把那個(gè)索引樹拿過來
此時(shí)對(duì)著索引樹你可以發(fā)現(xiàn)，當(dāng)name = '趙六'時(shí)，age已經(jīng)排好序了（前面介紹索引的說了排序規(guī)則），所以就可以使用age索引列進(jìn)行排序。
頻繁更新的字段不宜建索引
因?yàn)樗饕枰ＷC按照索引列的值進(jìn)行排序，所以一旦索引字段數(shù)據(jù)頻繁更新，那么為了保證索引的順序，就得頻繁挪動(dòng)索引列在索引頁中的位置
比如name和age聯(lián)合索引
此時(shí)把id=9這條數(shù)據(jù)的name從王九改成趙六，那么此時(shí)就把這條更改后的數(shù)據(jù)在索引頁上移到王五和id=4的趙六之間，因?yàn)閚ame相同時(shí)，得保證順序性，同時(shí)要按照age排序，id=9的age為9，最小，那么排在最前。
所以頻繁更新的字段建索引就會(huì)增加維護(hù)索引的成本。
選擇區(qū)分度高的字段做索引
這個(gè)是因?yàn)椋绻麉^(qū)分度低，那么索引效果不好。
舉個(gè)例子，假設(shè)現(xiàn)在有個(gè)性別字段sex，非男即女，如果對(duì)sex建索引，假設(shè)男排在女之前，那么索引頁的數(shù)據(jù)排列大致如下：
這里我畫了6條數(shù)據(jù)，假設(shè)有10w條數(shù)據(jù)那么也是這繼續(xù)排，男在前，女子在后。
此時(shí)如果走sex索引，查詢sex=男的數(shù)據(jù)，假設(shè)男女?dāng)?shù)據(jù)對(duì)半，那么就掃描的記錄就有5w，之后如果要回表，那么根據(jù)成本計(jì)算規(guī)則發(fā)現(xiàn)成本是巨大的，那么此時(shí)還不如直接全表掃描來的痛快。
所以要選擇區(qū)分度高的字段做索引
總結(jié)
到這，本文就結(jié)束了，這里回顧一下本文講的內(nèi)容
首先主要是講了聚簇索引和非聚簇索引，隨后講了MySQL對(duì)于一些常見查詢的優(yōu)化，比如覆蓋索引，索引下推，都是為了減少回表次數(shù)，從而減少帶來的性能消耗，再后面就提到MySQL是如何選擇索引的，最后介紹了索引失效的場(chǎng)景和索引建立的原則。
最后希望本文對(duì)你有所幫助！
最后的最后，表數(shù)據(jù)sql如下
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(1,?'李四',?20,?'杭州');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(2,?'張三',?18,?'北京');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(3,?'張三',?23,?'上海');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(4,?'趙六',?22,?'杭州');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(5,?'王五',?19,?'北京');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(6,?'趙六',?24,?'上海');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(7,?'劉七',?20,?'上海');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(8,?'劉七',?22,?'上海');
INSERT?INTO?`user`?(`id`,?`name`,?`age`,?`city`)?VALUES?(9,?'王九',?9,?'杭州');
更多好文章
Java高并發(fā)系列（共34篇）
MySql高手系列（共27篇）
Maven高手系列（共10篇）
Mybatis系列（共12篇）
聊聊db和緩存一致性常見的實(shí)現(xiàn)方式
接口冪等性這么重要，它是什么？怎么實(shí)現(xiàn)？
泛型，有點(diǎn)難度，會(huì)讓很多人懵逼，那是因?yàn)槟銢]有看這篇文章！
轉(zhuǎn)載于：https://mp.weixin.qq.com/s/xwRT2vti_blFd3TOSXCYGQ
