覆蓋索引

在之前《mysql索引初識》這篇文章中提到過，mysql的innodb引擎通過搜索樹方式實現(xiàn)索引，索引類型分為主鍵索引和二級索引（非主鍵索引），主鍵索引樹中，葉子結(jié)點保存著主鍵即對應行的全部數(shù)據(jù)；而二級索引樹中，葉子結(jié)點保存著索引值和主鍵值，當使用二級索引進行查詢時，需要進行回表操作。假如我們現(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í)行語句(A) select id from user_table where username = 'lzs'時，因為username索引樹的葉子結(jié)點上保存有username和id的值，所以通過username索引樹查找到id后，我們就已經(jīng)得到所需的數(shù)據(jù)了，這時候就不需要再去主鍵索引上繼續(xù)查找了。
執(zhí)行語句(B) select password from user_table where username = 'lzs'時，流程如下

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

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

聯(lián)合索引

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

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

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

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

索引下推

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

圖片來自課程文章

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

圖片來自課程文章

明顯的，第二種方式需要回表查詢的全行數(shù)據(jù)比較少，這就是mysql的索引下推。mysql默認啟用索引下推，我們也可以通過修改系統(tǒng)變量optimizer_switch的index_condition_pushdown標志來控制

SET optimizer_switch = 'index_condition_pushdown=off';

• 注意點：
  1、innodb引擎的表，索引下推只能用于二級索引。

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

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

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