1. 摘要

MySQL用來(lái)加快查詢的技術(shù)很多，其中最重要的是索引。通常索引能夠快速提高查詢速度。如果不適用索引，MYSQL必須從第一條記錄開始然后讀完整個(gè)表直到找出相關(guān)的行。表越大，花費(fèi)的時(shí)間越多。但也不全是這樣。本文討論索引是什么以及如何使用索引來(lái)改善性能，以及索引可能降低性能的情況。

2.MySQL索引原理

索引目的

索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒(méi)有索引，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺(jué)得如果沒(méi)有索引，這個(gè)事情根本無(wú)法完成？

索引原理

除了詞典，生活中隨處可見(jiàn)索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過(guò)不斷的縮小想要獲得數(shù)據(jù)的范圍來(lái)篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件，也就是我們總是通過(guò)同一種查找方式來(lái)鎖定數(shù)據(jù)。

數(shù)據(jù)庫(kù)也是一樣，但顯然要復(fù)雜許多，因?yàn)椴粌H面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫(kù)應(yīng)該選擇怎么樣的方式來(lái)應(yīng)對(duì)所有的問(wèn)題呢？我們回想字典的例子，能不能把數(shù)據(jù)分成段，然后分段查詢呢？最簡(jiǎn)單的如果1000條數(shù)據(jù)，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條數(shù)據(jù)，只要找第三段就可以了，一下子去除了90%的無(wú)效數(shù)據(jù)。但如果是1千萬(wàn)的記錄呢，分成幾段比較好？稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹，其平均復(fù)雜度是lgN，具有不錯(cuò)的查詢性能。但這里我們忽略了一個(gè)關(guān)鍵的問(wèn)題，復(fù)雜度模型是基于每次相同的操作成本來(lái)考慮的，數(shù)據(jù)庫(kù)實(shí)現(xiàn)比較復(fù)雜，數(shù)據(jù)保存在磁盤上，而為了提高性能，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來(lái)計(jì)算，因?yàn)槲覀冎涝L問(wèn)磁盤的成本大概是訪問(wèn)內(nèi)存的十萬(wàn)倍左右，所以簡(jiǎn)單的搜索樹難以滿足復(fù)雜的應(yīng)用場(chǎng)景。

磁盤IO與預(yù)讀

前面提到了訪問(wèn)磁盤，那么這里先簡(jiǎn)單介紹一下磁盤IO和預(yù)讀，磁盤讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動(dòng)，每次讀取數(shù)據(jù)花費(fèi)的時(shí)間可以分為尋道時(shí)間、旋轉(zhuǎn)延遲、傳輸時(shí)間三個(gè)部分，尋道時(shí)間指的是磁臂移動(dòng)到指定磁道所需要的時(shí)間，主流磁盤一般在5ms以下；旋轉(zhuǎn)延遲就是我們經(jīng)常聽(tīng)說(shuō)的磁盤轉(zhuǎn)速，比如一個(gè)磁盤7200轉(zhuǎn)，表示每分鐘能轉(zhuǎn)7200次，也就是說(shuō)1秒鐘能轉(zhuǎn)120次，旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms；傳輸時(shí)間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時(shí)間，一般在零點(diǎn)幾毫秒，相對(duì)于前兩個(gè)時(shí)間可以忽略不計(jì)。那么訪問(wèn)一次磁盤的時(shí)間，即一次磁盤IO的時(shí)間約等于5+4.17 = 9ms左右，聽(tīng)起來(lái)還挺不錯(cuò)的，但要知道一臺(tái)500 -MIPS的機(jī)器每秒可以執(zhí)行5億條指令，因?yàn)橹噶钜揽康氖请姷男再|(zhì)，換句話說(shuō)執(zhí)行一次IO的時(shí)間可以執(zhí)行40萬(wàn)條指令，數(shù)據(jù)庫(kù)動(dòng)輒十萬(wàn)百萬(wàn)乃至千萬(wàn)級(jí)數(shù)據(jù)，每次9毫秒的時(shí)間，顯然是個(gè)災(zāi)難。下圖是計(jì)算機(jī)硬件延遲的對(duì)比圖，供大家參考：

various-system-software-hardware-latencies

various-system-software-hardware-latencies

考慮到磁盤IO是非常高昂的操作，計(jì)算機(jī)操作系統(tǒng)做了一些優(yōu)化，當(dāng)一次IO時(shí)，不光把當(dāng)前磁盤地址的數(shù)據(jù)，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)，因?yàn)榫植款A(yù)讀性原理告訴我們，當(dāng)計(jì)算機(jī)訪問(wèn)一個(gè)地址的數(shù)據(jù)的時(shí)候，與其相鄰的數(shù)據(jù)也會(huì)很快被訪問(wèn)到。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(yè)(page)。具體一頁(yè)有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k，也就是我們讀取一頁(yè)內(nèi)的數(shù)據(jù)時(shí)候，實(shí)際上才發(fā)生了一次IO，這個(gè)理論對(duì)于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)非常有幫助。

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

前面講了生活中索引的例子，索引的基本原理，數(shù)據(jù)庫(kù)的復(fù)雜性，又講了操作系統(tǒng)的相關(guān)知識(shí)，目的就是讓大家了解，任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的，一定會(huì)有它的背景和使用場(chǎng)景，我們現(xiàn)在總結(jié)一下，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么，其實(shí)很簡(jiǎn)單，那就是：每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級(jí)，最好是常數(shù)數(shù)量級(jí)。那么我們就想到如果一個(gè)高度可控的多路搜索樹是否能滿足需求呢？就這樣，b+樹應(yīng)運(yùn)而生。

詳解b+樹

b+樹

b+樹

如上圖，是一顆b+樹，關(guān)于b+樹的定義可以參見(jiàn)B+樹，這里只說(shuō)一些重點(diǎn)，淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。

b+樹的查找過(guò)程

如圖所示，如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)，通過(guò)磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過(guò)指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。真實(shí)的情況是，3層的b+樹可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒(méi)有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬(wàn)次的IO，顯然成本非常非常高。

b+樹性質(zhì)

1.通過(guò)上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個(gè)磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m，則有h=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 數(shù)據(jù)項(xiàng)的大小，磁盤塊的大小也就是一個(gè)數(shù)據(jù)頁(yè)的大小，是固定的，如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量越多，樹的高度越低。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn)，一旦放到內(nèi)層節(jié)點(diǎn)，磁盤塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降，導(dǎo)致樹增高。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表。

2.當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+數(shù)是按照從左到右的順序來(lái)建立搜索樹的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來(lái)檢索的時(shí)候，b+樹會(huì)優(yōu)先比較name來(lái)確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒(méi)有name的數(shù)據(jù)來(lái)的時(shí)候，b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來(lái)搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來(lái)檢索時(shí)，b+樹可以用name來(lái)指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。

3. 索引分類和操作

索引的存儲(chǔ)分類

索引是在MYSQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的，而不是在服務(wù)層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同，也不是所有的存儲(chǔ)引擎都支持所有的索引類型。MYSQL目前提供了一下4種索引。

• B-Tree 索引：最常見(jiàn)的索引類型，大部分引擎都支持B樹索引。
• HASH 索引：只有Memory引擎支持，使用場(chǎng)景簡(jiǎn)單。
• R-Tree 索引(空間索引)：空間索引是MyISAM的一種特殊索引類型，主要用于地理空間數(shù)據(jù)類型。
• Full-text (全文索引)：全文索引也是MyISAM的一種特殊索引類型，主要用于全文索引，InnoDB從MYSQL5.6版本提供對(duì)全文索引的支持。

Mysql目前不支持函數(shù)索引，但是能對(duì)列的前面某一部分進(jìn)行索引，例如標(biāo)題title字段，可以只取title的前10個(gè)字符進(jìn)行索引，這個(gè)特性可以大大縮小索引文件的大小，但前綴索引也有缺點(diǎn)，在排序Order By和分組Group By 操作的時(shí)候無(wú)法使用。用戶在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候也可以對(duì)文本列根據(jù)此特性進(jìn)行靈活設(shè)計(jì)。
語(yǔ)法：create index idx_title on film (title(10))

MyISAM、InnoDB引擎、Memory三個(gè)常用引擎類型比較

B-TREE索引類型

普通索引
這是最基本的索引類型，而且它沒(méi)有唯一性之類的限制。普通索引可以通過(guò)以下幾種方式創(chuàng)建：
（1）創(chuàng)建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...);
（2）修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
（3）創(chuàng)建表時(shí)指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

UNIQUE索引
表示唯一的，不允許重復(fù)的索引，如果該字段信息保證不會(huì)重復(fù)例如身份證號(hào)用作索引時(shí)，可設(shè)置為unique：
（1）創(chuàng)建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
（3）創(chuàng)建表時(shí)指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );

主鍵：PRIMARY KEY索引
主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。
（1）主鍵一般在創(chuàng)建表的時(shí)候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。
（2）但是，我們也可以通過(guò)修改表的方式加入主鍵：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
每個(gè)表只能有一個(gè)主鍵。 （主鍵相當(dāng)于聚合索引，是查找最快的索引）
注：不能用CREATE INDEX語(yǔ)句創(chuàng)建PRIMARY KEY索引

刪除索引

可利用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)刪除索引。類似于CREATE INDEX語(yǔ)句，DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理，語(yǔ)法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前兩條語(yǔ)句是等價(jià)的，刪除掉table_name中的索引index_name。
第3條語(yǔ)句只在刪除PRIMARY KEY索引時(shí)使用，因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引，因此不需要指定索引名。如果沒(méi)有創(chuàng)建PRIMARY KEY索引，但表具有一個(gè)或多個(gè)UNIQUE索引，則MySQL將刪除第一個(gè)UNIQUE索引。

如果從表中刪除了某列，則索引會(huì)受到影響。對(duì)于多列組合的索引，如果刪除其中的某列，則該列也會(huì)從索引中刪除。如果刪除組成索引的所有列，則整個(gè)索引將被刪除。

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

Table：表的名稱
Non_unique：如果索引不能包括重復(fù)詞，則為0。如果可以，則為1
Key_name：索引的名稱
Seq_in_index：索引中的列序列號(hào)，從1開始
Column_name：列名稱
Collation：列以什么方式存儲(chǔ)在索引中。在MySQL中，有值‘A’（升序）或NULL（無(wú)分類）。
Cardinality：索引中唯一值的數(shù)目的估計(jì)值。通過(guò)運(yùn)行ANALYZE TABLE或myisamchk -a可以更新?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來(lái)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的?；鶖?shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL使用該索引的機(jī)會(huì)就越大。
Sub_part：如果列只是被部分地編入索引，則為被編入索引的字符的數(shù)目。如果整列被編入索引，則為NULL。
Packed：指示關(guān)鍵字如何被壓縮。如果沒(méi)有被壓縮，則為NULL。
Null：如果列含有NULL，則含有YES。如果沒(méi)有，則該列含有NO。
Index_type：用過(guò)的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment：更多評(píng)注。

聯(lián)合索引

聯(lián)合索引的定義為（MySQL）：

ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);

聯(lián)合索引的優(yōu)點(diǎn)：
若多個(gè)一條SQL，需要多個(gè)用到兩個(gè)條件

SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX';

當(dāng)索引在檢索 password字段的時(shí)候，數(shù)據(jù)量大大縮小，索引的命中率減小，增大了索引的效率。

符合索引的索引體積比單獨(dú)索引的體積要小，而且只是一個(gè)索引樹，相比單獨(dú)列的索引要更加的節(jié)省時(shí)間復(fù)雜度和空間復(fù)雜度。

例如，有1000W條數(shù)據(jù)的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W*10%=100w 條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合b=2 and c= 3的數(shù)據(jù)，然后再排序，再分頁(yè)。
如果是復(fù)合索引，通過(guò)索引篩選出1000w *10% *10% *10%=1w，然后再排序、分頁(yè)，哪個(gè)更高效，一眼便知。

聯(lián)合索引命中的本質(zhì)（最左匹配原則）
在Mysql建立多列索引（聯(lián)合索引）有最左前綴的原則，即最左優(yōu)先。
如果我們建立了一個(gè)2列的聯(lián)合索引(col1,col2),實(shí)際上已經(jīng)建立了兩個(gè)聯(lián)合索引(col1)、(col1,col2）;
如果有一個(gè)3列索引(col1,col2,col3)，實(shí)際上已經(jīng)建立了三個(gè)聯(lián)合索引(col1)、(col1,col2)、(col1,col2,col3)。

最左匹配舉例

創(chuàng)建聯(lián)合索引時(shí)列的選擇原則
經(jīng)常用的列優(yōu)先（最左匹配原則）
離散度高的列優(yōu)先（離散度高原則）
寬度小的列優(yōu)先（最少空間原則）

索引選擇注意事項(xiàng)

既然索引可以加快查詢速度，那么是不是只要是查詢語(yǔ)句需要，就建上索引？答案是否定的。因?yàn)樗饕m然加快了查詢速度，但索引也是有代價(jià)的：索引文件本身要消耗存儲(chǔ)空間，同時(shí)索引會(huì)加重插入、刪除和修改記錄時(shí)的負(fù)擔(dān)，另外，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引，因此索引并不是越多越好。

一般兩種情況下不建議建索引：

表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒(méi)必要建索引，讓查詢做全表掃描就好了;

至于多少條記錄才算多，這個(gè)個(gè)人有個(gè)人的看法，我個(gè)人的經(jīng)驗(yàn)是以2000作為分界線，記錄數(shù)不超過(guò) 2000可以考慮不建索引，超過(guò)2000條可以酌情考慮索引。

索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大，這是由B+Tree的性質(zhì)決定的。例如，上文用到的employees.titles表，如果title字段經(jīng)常被單獨(dú)查詢，是否需要建索引，我們看一下它的選擇性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的選擇性不足0.0001（精確值為0.00001579），所以實(shí)在沒(méi)有什么必要為其單獨(dú)建索引。

MySQL只對(duì)一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些時(shí)候的like(不以通配符%或_開頭的情形)。

不要過(guò)度索引，只保持所需的索引。每個(gè)額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。 在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)可能需要重構(gòu)，因此，索引越多，所花的時(shí)間越長(zhǎng)。

4. 慢查詢優(yōu)化

4.1 MySQL Explain詳解

我們常常用到explain這個(gè)命令來(lái)查看一個(gè)這些SQL語(yǔ)句的執(zhí)行計(jì)劃，查看該SQL語(yǔ)句有沒(méi)有使用上了索引，有沒(méi)有做全表掃描，這都可以通過(guò)explain命令來(lái)查看。所以我們深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問(wèn)策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語(yǔ)句時(shí)哪種策略預(yù)計(jì)會(huì)被優(yōu)化器采用。

-- 實(shí)際SQL，查找用戶名為Jefabc的員工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

expain出來(lái)的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：
id:選擇標(biāo)識(shí)符
select_type:表示查詢的類型。
table:輸出結(jié)果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時(shí)，可能使用的索引
key:表示實(shí)際使用的索引
key_len:索引字段的長(zhǎng)度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表?xiàng)l件過(guò)濾的行百分比
Extra:執(zhí)行情況的描述和說(shuō)明

id

SELECT識(shí)別符。這是SELECT的查詢序列號(hào)

我的理解是SQL執(zhí)行的順序的標(biāo)識(shí)，SQL從大到小的執(zhí)行

1. id相同時(shí)，執(zhí)行順序由上至下

2. 如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

3. id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

-- 查看在研發(fā)部并且名字以Jef開頭的員工，經(jīng)典查詢
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研發(fā)部';

select_type

示查詢中每個(gè)select子句的類型：

(1) SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)
(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)
(4) DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢)
(5) UNION RESULT(UNION的結(jié)果，union語(yǔ)句中第二個(gè)select開始后面所有select)
(6) SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)
(7) DEPENDENT SUBQUERY(子查詢中的第一個(gè)SELECT，依賴于外部查詢)
(8) DERIVED(派生表的SELECT, FROM子句的子查詢)
(9) UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行)

table

顯示這一步所訪問(wèn)數(shù)據(jù)庫(kù)中表名稱（顯示這一行的數(shù)據(jù)是關(guān)于哪張表的），有時(shí)不是真實(shí)的表名字，可能是簡(jiǎn)稱，例如上面的e，d，也可能是第幾步執(zhí)行的結(jié)果的簡(jiǎn)稱

type

對(duì)表訪問(wèn)方式，表示MySQL在表中找到所需行的方式，又稱“訪問(wèn)類型”。

常用的類型有：ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）

• ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行
• index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹
• range:只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行
• ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
• eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件
• const、system: 當(dāng)MySQL對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問(wèn)。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量，system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system
• NULL: MySQL在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引，例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。

possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒(méi)有任何索引顯示 null）。

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。
如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢

Key

key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中。

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度（key_len顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的）。

不損失精確性的情況下，長(zhǎng)度越短越好 。

ref

列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

rows

估算出結(jié)果集行數(shù)，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)

Extra

該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：

• Using where:不用讀取表中所有信息，僅通過(guò)索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾

• Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢，常見(jiàn) group by ; order by

• Using filesort：當(dāng)Query中包含 order by 操作，而且無(wú)法利用索引完成的排序操作稱為“文件排序”

-- 測(cè)試Extra的filesort
explain select * from emp order by name;

• Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒(méi)有使用索引，并且需要連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果。如果出現(xiàn)了這個(gè)值，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來(lái)改進(jìn)能。

• Impossible where：這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒(méi)有符合條件的行（通過(guò)收集統(tǒng)計(jì)信息不可能存在結(jié)果）。

• Select tables optimized away：這個(gè)值意味著僅通過(guò)使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

• No tables used：Query語(yǔ)句中使用from dual 或不含任何from子句

4.2 慢查詢優(yōu)化案例

4.2.1 復(fù)雜的深分頁(yè)問(wèn)題優(yōu)化

背景

有一個(gè)article表，用于存儲(chǔ)文章的基本信息的，有文章id，作者id等一些屬性，有一個(gè)content表，主要用于存儲(chǔ)文章的內(nèi)容，主鍵是article_id，需求需要將一些滿足條件的作者發(fā)布的文章導(dǎo)入到另外一個(gè)庫(kù)，所以我同事就在項(xiàng)目中先查詢出了符合條件的作者id，然后開啟了多個(gè)線程，每個(gè)線程每次取一個(gè)作者id，執(zhí)行查詢和導(dǎo)入工作。

查詢出作者id是1111，名下的所有文章信息，文章內(nèi)容相關(guān)的信息的SQL如下：

SELECT
    a.*, c.*
FROM
    article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
    a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100

因?yàn)椴樵兊倪@個(gè)數(shù)據(jù)庫(kù)是機(jī)械硬盤的，在offset查詢到20萬(wàn)時(shí)，查詢時(shí)間已經(jīng)特別長(zhǎng)了，運(yùn)維同事那邊直接收到報(bào)警，說(shuō)這個(gè)庫(kù)已經(jīng)IO阻塞了，已經(jīng)多次進(jìn)行主從切換了，我們就去navicat里面試著執(zhí)行了一下這個(gè)語(yǔ)句，也是一直在等待， 然后對(duì)數(shù)據(jù)庫(kù)執(zhí)行show proceesslist 命令查看了一下，發(fā)現(xiàn)每個(gè)查詢都是處于Writing to net的狀態(tài)，沒(méi)辦法只能先把導(dǎo)入的項(xiàng)目暫時(shí)下線，然后執(zhí)行kill命令將當(dāng)前的查詢都?xì)⑺肋M(jìn)程(因?yàn)橹皇强蛻舳薙top的話，MySQL服務(wù)端會(huì)繼續(xù)查詢)。

然后我們開始分析這條命令執(zhí)行慢的原因：

是否是聯(lián)合索引的問(wèn)題

當(dāng)前是索引情況如下：

article表的主鍵是id，author_id是一個(gè)普通索引
content表的主鍵是article_id

所以認(rèn)為當(dāng)前是執(zhí)行流程是先去article表的普通索引author_id里面找到1111的所有文章id，然后根據(jù)這些文章id去article表的聚集索引中找到所有的文章，然后拿每個(gè)文章id去content表中找文章內(nèi)容等信息，然后判斷create_time是否滿足要求，進(jìn)行過(guò)濾，最終找到offset為20000后的100條數(shù)據(jù)。

所以我們就將article的author_id索引改成了聯(lián)合索引(author_id,create_time),這樣聯(lián)合索引(author_id,create_time)中的B+樹就是先安裝author_id排序，再按照create_time排序，這樣一開始在聯(lián)合(author_id,create_time)查詢出來(lái)的文章id就是滿足create_time < '2020-04-29 00:00:00'條件的，后面就不用進(jìn)行過(guò)濾了，就不會(huì)就是符合就不用對(duì)create_time過(guò)濾。

流程確實(shí)是這個(gè)流程，但是去查詢時(shí)，如果limit還是210000, 100時(shí)，還是查不出數(shù)據(jù)，幾分鐘都沒(méi)有數(shù)據(jù)，一直到navica提示超時(shí)，使用Explain看的話，確實(shí)命中索引了，如果將offset調(diào)小，調(diào)成6000, 100，勉強(qiáng)可以查出數(shù)據(jù)，但是需要46s，所以瓶頸不在這里。

真實(shí)原因如下：

先看關(guān)于深分頁(yè)的兩個(gè)查詢，id是主鍵，val是普通索引

直接查詢法

select * from test where val=4 limit 300000,5;

先查主鍵再join

select * from test a 
inner join
(select id from test where val=4 limit 300000,5) as b 
on a.id=b.id;

這兩個(gè)查詢的結(jié)果都是查詢出offset是30000后的5條數(shù)據(jù)，區(qū)別在于第一個(gè)查詢需要先去普通索引val中查詢出300005個(gè)id，然后去聚集索引下讀取300005個(gè)數(shù)據(jù)頁(yè)，然后拋棄前面的300000個(gè)結(jié)果，只返回最后5個(gè)結(jié)果，過(guò)程中會(huì)產(chǎn)生了大量的隨機(jī)I/O。第二個(gè)查詢一開始在普通索引val下就只會(huì)讀取后5個(gè)id，然后去聚集索引下讀取5個(gè)數(shù)據(jù)頁(yè)。

同理我們業(yè)務(wù)中那條查詢其實(shí)是更加復(fù)雜的情況，因?yàn)槲覀儤I(yè)務(wù)的那條SQL不僅會(huì)讀取article表中的210100條結(jié)果，而且會(huì)每條結(jié)果去content表中查詢文章相關(guān)內(nèi)容，而這張表有幾個(gè)TEXT類型的字段，我們使用show table status命令查看表相關(guān)的信息發(fā)現(xiàn)

發(fā)現(xiàn)兩個(gè)表的數(shù)據(jù)量都是200多萬(wàn)的量級(jí)，article表的行平均長(zhǎng)度是266，content表的平均長(zhǎng)度是16847，簡(jiǎn)單來(lái)說(shuō)是當(dāng) InnoDB 使用 Compact 或者 Redundant 格式存儲(chǔ)極長(zhǎng)的 VARCHAR 或者 BLOB 這類大對(duì)象時(shí)，我們并不會(huì)直接將所有的內(nèi)容都存放在數(shù)據(jù)頁(yè)節(jié)點(diǎn)中，而是將行數(shù)據(jù)中的前 768 個(gè)字節(jié)存儲(chǔ)在數(shù)據(jù)頁(yè)中，后面會(huì)通過(guò)偏移量指向溢出頁(yè)。

（詳細(xì)了解可以看看這篇文章深度好文帶你讀懂MySQL和InnoDB）

img

這樣再?gòu)腸ontent表里面查詢連續(xù)的100行數(shù)據(jù)時(shí)，讀取每行數(shù)據(jù)時(shí)，還需要去讀溢出頁(yè)的數(shù)據(jù)，這樣就需要大量隨機(jī)IO，因?yàn)闄C(jī)械硬盤的硬件特性，隨機(jī)IO會(huì)比順序IO慢很多。所以我們后來(lái)又進(jìn)行了測(cè)試，

只是從article表里面查詢limit 200000，100的數(shù)據(jù)，發(fā)現(xiàn)即便存在深分頁(yè)的問(wèn)題，查詢時(shí)間只是0.5s，因?yàn)閍rticle表的平均列長(zhǎng)度是266，所有數(shù)據(jù)都存在數(shù)據(jù)頁(yè)節(jié)點(diǎn)中，不存在頁(yè)溢出，所以都是順序IO，所以比較快。

//查詢時(shí)間0.51s
SELECT a.* FROM article a  
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
LIMIT 200100, 100

相反的，我們直接先找出100個(gè)article_id去content表里面查詢數(shù)據(jù)，發(fā)現(xiàn)比較慢，第一次查詢時(shí)需要3s左右(也就是這些id的文章內(nèi)容相關(guān)的信息都沒(méi)有過(guò)，沒(méi)有緩存的情況)，第二次查詢時(shí)因?yàn)檫@些溢出頁(yè)數(shù)據(jù)已經(jīng)加載到buffer pool，所以大概0.04s。

SELECT SQL_NO_CACHE c.* 
FROM article_content c 
WHERE c.article_id in(100個(gè)article_id)

解決方案

所以針對(duì)這個(gè)問(wèn)題的解決方案主要有兩種：

先查出主鍵id再inner join

非連續(xù)查詢的情況下，也就是我們?cè)诓榈?00頁(yè)的數(shù)據(jù)時(shí)，不一定查了第99頁(yè)，也就是允許跳頁(yè)查詢的情況，那么就是使用先查主鍵再join這種方法對(duì)我們的業(yè)務(wù)SQL進(jìn)行改寫成下面這樣，下查詢出210000, 100時(shí)主鍵id，作為臨時(shí)表temp_table，將article表與temp_table表進(jìn)行inner join，查詢出中文章相關(guān)的信息，并且去left Join content表查詢文章內(nèi)容相關(guān)的信息。 第一次查詢大概1.11s，后面每次查詢大概0.15s

SELECT
    a.*, c.*
FROM article a
INNER JOIN(
    SELECT  id FROM article a
    WHERE   a.author_id = 1111
    AND a.create_time < '2020-04-29 00:00:00'
    LIMIT 210000 ,
    100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id

優(yōu)化結(jié)果

優(yōu)化前，offset達(dá)到20萬(wàn)的量級(jí)時(shí)，查詢時(shí)間過(guò)長(zhǎng)，一直到超時(shí)。

優(yōu)化后，offset達(dá)到20萬(wàn)的量級(jí)時(shí)，查詢時(shí)間為1.11s。

利用范圍查詢條件來(lái)限制取出的數(shù)據(jù)

這種方法的大致思路如下，假設(shè)要查詢test_table中offset為10000的后100條數(shù)據(jù)，假設(shè)我們事先已知第10000條數(shù)據(jù)的id，值為min_id_value

select * from test_table where id > min_id_value order by id limit 0, 100，就是即利用條件id > min_id_value在掃描索引是跳過(guò)10000條記錄，然后取100條數(shù)據(jù)即可，這種處理方式的offset值便成為0了，但此種方式有限制，必須知道offset對(duì)應(yīng)id，然后作為min_id_value，增加id > min_id_value的條件來(lái)進(jìn)行過(guò)濾，如果是用于分頁(yè)查找的話，也就是必須知道上一頁(yè)的最大的id，所以只能一頁(yè)一頁(yè)得查，不能跳頁(yè)，但是因?yàn)槲覀兊臉I(yè)務(wù)需求就是每次100條數(shù)據(jù)，進(jìn)行分批導(dǎo)數(shù)據(jù)，所以我們這種場(chǎng)景是可以使用。針對(duì)這種方法，我們的業(yè)務(wù)SQL改寫如下：

//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id 
FROM article a 
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
//然后每次循環(huán)查找
while(min_id<max_id) {
        SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id  WHERE a.author_id = 1111  AND a.id > min_id LIMIT 100
        //這100條數(shù)據(jù)導(dǎo)入完畢后，將100條數(shù)據(jù)數(shù)據(jù)中最大的id賦值給min_id，以便導(dǎo)入下100條數(shù)據(jù)
}

優(yōu)化結(jié)果

優(yōu)化前，offset達(dá)到20萬(wàn)的量級(jí)時(shí)，查詢時(shí)間過(guò)長(zhǎng)，一直到超時(shí)。

優(yōu)化后，offset達(dá)到20萬(wàn)的量級(jí)時(shí)，由于知道第20萬(wàn)條數(shù)據(jù)的id，查詢時(shí)間為0.34s。

4.2.2 聯(lián)合索引問(wèn)題優(yōu)化

聯(lián)合索引其實(shí)有兩個(gè)作用：

1.充分利用where條件，縮小范圍

例如我們需要查詢以下語(yǔ)句：

SELECT * FROM test WHERE a = 1 AND b = 2

如果對(duì)字段a建立單列索引，對(duì)b建立單列索引，那么在查詢時(shí)，只能選擇走索引a，查詢所有a=1的主鍵id，然后進(jìn)行回表，在回表的過(guò)程中，在聚集索引中讀取每一行數(shù)據(jù)，然后過(guò)濾出b = 2結(jié)果集，或者走索引b，也是這樣的過(guò)程。
如果對(duì)a，b建立了聯(lián)合索引(a,b),那么在查詢時(shí)，直接在聯(lián)合索引中先查到a=1的節(jié)點(diǎn)，然后根據(jù)b=2繼續(xù)往下查，查出符合條件的結(jié)果集，進(jìn)行回表。

2.避免回表(此時(shí)也叫覆蓋索引)

這種情況就是假如我們只查詢某幾個(gè)常用字段，例如查詢a和b如下：

SELECT a,b FROM test WHERE a = 1 AND b = 2

對(duì)字段a建立單列索引，對(duì)b建立單列索引就需要像上面所說(shuō)的，查到符合條件的主鍵id集合后需要去聚集索引下回表查詢，但是如果我們要查詢的字段本身在聯(lián)合索引中就都包含了，那么就不用回表了。

3.減少需要回表的數(shù)據(jù)的行數(shù)

這種情況就是假如我們需要查詢a>1并且b=2的數(shù)據(jù)

SELECT * FROM test WHERE a > 1 AND b = 2

如果建立的是單列索引a，那么在查詢時(shí)會(huì)在單列索引a中把a(bǔ)>1的主鍵id全部查找出來(lái)然后進(jìn)行回表。
如果建立的是聯(lián)合索引(a,b),基于最左前綴匹配原則，因?yàn)閍的查詢條件是一個(gè)范圍查找(=或者in之外的查詢條件都是范圍查找)，這樣雖然在聯(lián)合索引中查詢時(shí)只能命中索引a的部分，b的部分命中不了，只能根據(jù)a>1進(jìn)行查詢，但是由于聯(lián)合索引中每個(gè)葉子節(jié)點(diǎn)包含b的信息，在查詢出所有a>1的主鍵id時(shí)，也會(huì)對(duì)b=2進(jìn)行篩選，這樣需要回表的主鍵id就只有a>1并且b=2這部分了，所以回表的數(shù)據(jù)量會(huì)變小。

我們業(yè)務(wù)中碰到的就是第3種情況，我們的業(yè)務(wù)SQL本來(lái)更加復(fù)雜，還會(huì)join其他表，但是由于優(yōu)化的瓶頸在于建立聯(lián)合索引，所以進(jìn)行了一些簡(jiǎn)化，下面是簡(jiǎn)化后的SQL：

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a
where
  a.create_time between '2020-03-29 03:00:00.003'
and '2020-04-29 03:00:00.003'
and a.status = 1

我們的需求其實(shí)就是從article表中查詢出最近一個(gè)月，status為1的文章，我們本來(lái)就是針對(duì)create_time建了單列索引，結(jié)果在慢查詢?nèi)罩局邪l(fā)現(xiàn)了這條語(yǔ)句，查詢時(shí)間需要0.91s左右，所以開始嘗試著進(jìn)行優(yōu)化。

為了便于測(cè)試，我們?cè)诒碇蟹謩e對(duì)create_time建立了單列索引create_time，對(duì)(create_time,status)建立聯(lián)合索引idx_createTime_status。

強(qiáng)制使用idx_createTime進(jìn)行查詢

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_createTime)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

強(qiáng)制使用idx_createTime_status進(jìn)行查詢（即使不強(qiáng)制也是會(huì)選擇這個(gè)索引）

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_createTime_status)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

優(yōu)化結(jié)果：

優(yōu)化前使用idx_createTime單列索引，查詢時(shí)間為0.91s

優(yōu)化前使用idx_createTime_status聯(lián)合索引，查詢時(shí)間為0.21s

EXPLAIN的結(jié)果如下：

原理分析

先介紹一下EXPLAIN中Extra列的各種取值的含義

Using filesort

當(dāng)Query 中包含 ORDER BY 操作，而且無(wú)法利用索引完成排序操作的時(shí)候，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來(lái)實(shí)現(xiàn)。數(shù)據(jù)較少時(shí)從內(nèi)存排序，否則從磁盤排序。Explain不會(huì)顯示的告訴客戶端用哪種排序。

Using index

僅使用索引樹中的信息從表中檢索列信息，而不需要進(jìn)行附加搜索來(lái)讀取實(shí)際行(使用二級(jí)覆蓋索引即可獲取數(shù)據(jù))。 當(dāng)查詢僅使用作為單個(gè)索引的一部分的列時(shí)，可以使用此策略。

Using temporary

要解決查詢，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)保存結(jié)果。 如果查詢包含不同列的GROUP BY和ORDER BY子句，則通常會(huì)發(fā)生這種情況。官方解釋：”為了解決查詢，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)。很明顯就是通過(guò)where條件一次性檢索出來(lái)的結(jié)果集太大了，內(nèi)存放不下了，只能通過(guò)加臨時(shí)表來(lái)輔助處理。

Using where

表示當(dāng)where過(guò)濾條件中的字段無(wú)索引時(shí)，MySQL Sever層接收到存儲(chǔ)引擎(例如innodb)的結(jié)果集后，根據(jù)where條件中的條件進(jìn)行過(guò)濾。

Using index condition

Using index condition 會(huì)先條件過(guò)濾索引，過(guò)濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過(guò)濾這些數(shù)據(jù)行；

我們的實(shí)際案例中，其實(shí)就是走單個(gè)索引idx_createTime時(shí)，只能從索引中查出 滿足a.create_time between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'條件的主鍵id，然后進(jìn)行回表，因?yàn)閕dx_createTime索引中沒(méi)有status的信息，只能回表后查出所有的主鍵id對(duì)應(yīng)的行。然后innodb將結(jié)果集返回給MySQL Sever，MySQL Sever根據(jù)status字段進(jìn)行過(guò)濾，篩選出status為1的字段，所以第一個(gè)查詢的Explain結(jié)果中的Extra才會(huì)顯示Using where。

filtered字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后，剩下多少滿足查詢的記錄數(shù)量的比例，這個(gè)是預(yù)估值，因?yàn)閟tatus取值是null，1，2，3，4，所以這里給的25%。

所以第二個(gè)查詢與第一個(gè)查詢的區(qū)別主要在于一開始去idx_createTime_status查到的結(jié)果集就是滿足status是1的id，所以去聚集索引下進(jìn)行回表查詢時(shí)，掃描的行數(shù)會(huì)少很多（大概是2.7萬(wàn)行與15萬(wàn)行的區(qū)別），之后innodb返回給MySQL Server的數(shù)據(jù)就是滿足條件status是1的結(jié)果集（2.7萬(wàn)行），不用再進(jìn)行篩選了，所以第二個(gè)查詢才會(huì)快這么多，時(shí)間是優(yōu)化前的23%。（兩種查詢方式的EXPLAIN預(yù)估掃描行數(shù)都是30萬(wàn)行左右是因?yàn)閕dx_createTime_status只命中了createTime，因?yàn)閏reateTime不是查單個(gè)值，查的是范圍）

//查詢結(jié)果行數(shù)是15萬(wàn)行左右
SELECT count(*) from article a 
where a.post_time 
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003'

//查詢結(jié)果行數(shù)是2萬(wàn)6行左右
SELECT count(*) from article a 
where a.post_time 
between '2020-03-22 03:00:00.003' and '2020-04-22 03:00:00.003' 
and a.audit_status = 1

發(fā)散思考：如果將聯(lián)合索引(createTime，status)改成(status，createTime)會(huì)怎么樣？

where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

根據(jù)最左匹配的原則，因?yàn)槲覀兊膚here查詢條件是這樣，如果是(createTime，status)那么索引就只能用到createTime，如果是(status，createTime)，因?yàn)閟tatus是查詢單個(gè)值，所以status，createTime都可以命中，在(status，createTime)索引中掃描行數(shù)會(huì)減少，但是由于(createTime，status)這個(gè)索引本身值包含createTime，status，id三個(gè)字段的信息，數(shù)據(jù)量比較小，而一個(gè)數(shù)據(jù)頁(yè)是16k，可以存儲(chǔ)1000個(gè)以上的索引數(shù)據(jù)節(jié)點(diǎn)，而且是查詢到createTime后，進(jìn)行的順序IO，所以讀取比較快，總得的查詢時(shí)間兩者基本是一致。下面是測(cè)試結(jié)果：

首先創(chuàng)建了(status，createTime)名叫idx_status_createTime，

SELECT
  a.id as article_id ,
  a.title as title ,
  a.author_id as author_id 
from
  article a  FORCE INDEX(idx_status_createTime)
where
  a.create_time between '2020-03-22 03:00:00.003'
and '2020-04-22 03:00:00.003'
and a.status = 1

查詢時(shí)間是0.21，跟第二種方式(createTime，status)索引的查詢時(shí)間基本一致。

Explain結(jié)果對(duì)比：

掃描行數(shù)確實(shí)會(huì)少一些，因?yàn)樵趇dx_status_createTime的索引中，一開始根據(jù)status = 1排除掉了status取值為其他值的情況。

5. 參考

（1）MySQL索引原理及慢查詢優(yōu)化 https://tech.meituan.com/2014/06/30/mysql-index.html
（2）MySQL Explain詳解 https://www.cnblogs.com/tufujie/p/9413852.html
（3）MySQL慢查詢優(yōu)化（線上案例調(diào)優(yōu)）https://www.cnblogs.com/notfound9/p/12928763.html