文章是學(xué)習(xí)了林曉斌老師在極客時(shí)間的《mysql實(shí)戰(zhàn)45講》后，根據(jù)自己的理解整理而成的。

覆蓋索引

在之前《mysql索引初識(shí)》這篇文章中提到過(guò)，mysql的innodb引擎通過(guò)搜索樹方式實(shí)現(xiàn)索引，索引類型分為主鍵索引和二級(jí)索引（非主鍵索引），主鍵索引樹中，葉子結(jié)點(diǎn)保存著主鍵即對(duì)應(yīng)行的全部數(shù)據(jù)；而二級(jí)索引樹中，葉子結(jié)點(diǎn)保存著索引值和主鍵值，當(dāng)使用二級(jí)索引進(jìn)行查詢時(shí)，需要進(jìn)行回表操作。假如我們現(xiàn)在有如下表結(jié)構(gòu)

CREATE TABLE `user_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) unsigned Not NULL,
  PRIMARY KEY (`id`),
  key (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

執(zhí)行語(yǔ)句(A) select id from user_table where username = 'lzs'時(shí)，因?yàn)閡sername索引樹的葉子結(jié)點(diǎn)上保存有username和id的值，所以通過(guò)username索引樹查找到id后，我們就已經(jīng)得到所需的數(shù)據(jù)了，這時(shí)候就不需要再去主鍵索引上繼續(xù)查找了。
執(zhí)行語(yǔ)句(B) select password from user_table where username = 'lzs'時(shí)，流程如下

1、username索引樹上找到username=lzs對(duì)應(yīng)的主鍵id
2、通過(guò)回表在主鍵索引樹上找到滿足條件的數(shù)據(jù)

由上面可知，當(dāng)sql語(yǔ)句的所求查詢字段（select列）和查詢條件字段（where子句）全都包含在一個(gè)索引中，可以直接使用索引查詢而不需要回表。這就是覆蓋索引，通過(guò)使用覆蓋索引，可以減少搜索樹的次數(shù)，是常用的性能優(yōu)化手段。
例如上面的語(yǔ)句B是一個(gè)高頻查詢的語(yǔ)句，我們可以建立(username,password)的聯(lián)合索引，這樣，查詢的時(shí)候就不需要再去回表操作了，可以提高查詢效率。當(dāng)然，添加索引是有維護(hù)代價(jià)的，所以添加時(shí)也要權(quán)衡一下。

聯(lián)合索引

mysql的b+樹索引遵循“最左前綴”原則，繼續(xù)以上面的例子來(lái)說(shuō)明，為了提高語(yǔ)句B的執(zhí)行速度，我們添加了一個(gè)聯(lián)合索引（username,password）,特別注意這個(gè)聯(lián)合索引的順序，如果我們顛倒下順序改成（password,username),這樣查詢能使用這個(gè)索引嗎？答案是不能的！這是最左前綴的第一層含義：聯(lián)合索引的多個(gè)字段中，只有當(dāng)查詢條件為聯(lián)合索引的一個(gè)字段時(shí)，查詢才能使用該索引。
現(xiàn)在，假設(shè)我們有一下三種查詢情景：
1、查出用戶名的第一個(gè)字是“張”開頭的人的密碼。即查詢條件子句為"where username like '張%'"
2、查處用戶名中含有“張”字的人的密碼。即查詢條件子句為"where username like '%張%'"
3、查出用戶名以“張”字結(jié)尾的人的密碼。即查詢條件子句為"where username like '%張'"

以上三種情況下，只有第1種能夠使用（username,password）聯(lián)合索引來(lái)加快查詢速度。這就是最左前綴的第二層含義：索引可以用于查詢條件字段為索引字段，根據(jù)字段值最左若干個(gè)字符進(jìn)行的模糊查詢。

維護(hù)索引需要代價(jià)，所以有時(shí)候我們可以利用“最左前綴”原則減少索引數(shù)量，上面的（username,password）索引，也可用于根據(jù)username查詢age的情況。當(dāng)然，使用這個(gè)索引去查詢age的時(shí)候是需要進(jìn)行回表的，當(dāng)這個(gè)需求（根據(jù)username查詢age）也是高頻請(qǐng)求時(shí)，我們可以創(chuàng)建（username,password,age）聯(lián)合索引，這樣，我們需要維護(hù)的索引數(shù)量不變。

創(chuàng)建索引時(shí)，我們也要考慮空間代價(jià)，使用較少的空間來(lái)創(chuàng)建索引
假設(shè)我們現(xiàn)在不需要通過(guò)username查詢password了，相反，經(jīng)常需要通過(guò)username查詢age或通過(guò)age查詢username,這時(shí)候，刪掉（username,password）索引后，我們需要?jiǎng)?chuàng)建新的索引，我們有兩種選擇
1、（username,age）聯(lián)合索引+age字段索引
2、（age,username）聯(lián)合索引+username單字段索引
一般來(lái)說(shuō)，username字段比age字段大的多，所以，我們應(yīng)選擇第一種，索引占用空間較小。

索引下推

對(duì)于user_table表，我們現(xiàn)在有（username,age）聯(lián)合索引
如果現(xiàn)在有一個(gè)需求，查出名稱中以“張”開頭且年齡小于等于10的用戶信息，語(yǔ)句C如下："select * from user_table where username like '張%' and age > 10".
語(yǔ)句C有兩種執(zhí)行可能：
1、根據(jù)（username,age）聯(lián)合索引查詢所有滿足名稱以“張”開頭的索引，然后回表查詢出相應(yīng)的全行數(shù)據(jù)，然后再篩選出滿足年齡小于等于10的用戶數(shù)據(jù)。過(guò)程如下圖。

圖片來(lái)自課程文章

2、根據(jù)（username,age）聯(lián)合索引查詢所有滿足名稱以“張”開頭的索引，然后直接再篩選出年齡小于等于10的索引，之后再回表查詢?nèi)袛?shù)據(jù)。過(guò)程如下圖。

圖片來(lái)自課程文章

SET optimizer_switch = 'index_condition_pushdown=off';

• 注意點(diǎn)：
  1、innodb引擎的表，索引下推只能用于二級(jí)索引。

  就像之前提到的，innodb的主鍵索引樹葉子結(jié)點(diǎn)上保存的是全行數(shù)據(jù)，所以這個(gè)時(shí)候索引下推并不會(huì)起到減少查詢?nèi)袛?shù)據(jù)的效果。

  2、索引下推一般可用于所求查詢字段（select列）不是/不全是聯(lián)合索引的字段，查詢條件為多條件查詢且查詢條件子句（where/order by）字段全是聯(lián)合索引。

  假設(shè)表t有聯(lián)合索引（a,b）,下面語(yǔ)句可以使用索引下推提高效率
  select * from t where a > 2 and b > 10;

我們一般使用explain來(lái)查看語(yǔ)句是否使用索引，近期會(huì)推出一篇專門介紹explain的文章～～～