通常大家都會(huì)根據(jù)査詢的WHERE條件來(lái)創(chuàng)建合適的索引哀九，不過(guò)這只是索引優(yōu)化的一個(gè)方 面。設(shè)計(jì)優(yōu)秀的索引應(yīng)該考慮到整個(gè)查詢，而不單單是WHERE條件部分均践。索引確實(shí)是一 種查找數(shù)據(jù)的高效方式局装，但是MySQL也可以使用索引來(lái)直接獲取列的數(shù)據(jù)坛吁，這樣就不 再需要讀取數(shù)據(jù)行。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)铐尚，那么還有什么必要 再回表査詢呢拨脉？如果一個(gè)索引包含（或者說(shuō)覆蓋）所有需要査詢的字段的值，我們就稱＜3S 之為“覆蓋索引”宣增。

覆蓋索引是非常有用的工具玫膀，能夠極大地提高性能〉ⅲ考慮一下如果査詢只需要掃描索引 而無(wú)須回表帖旨，會(huì)帶來(lái)多少好處：

? 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，所以如果只需要讀取索引灵妨，那MySQL就會(huì)極大 地減少數(shù)據(jù)訪問(wèn)量解阅。這對(duì)緩存的負(fù)載非常重要，因?yàn)檫@種情況下響應(yīng)時(shí)間大部分花 費(fèi)在數(shù)據(jù)拷貝上泌霍。覆蓋索引對(duì)于I/O密集型的應(yīng)用也有幫助货抄，因?yàn)樗饕葦?shù)據(jù)更小， 更容易全部放入內(nèi)存中（這對(duì)于MylSAM尤其正確，因?yàn)镸ylSAM能壓縮索引以 變得更械锵ā）桨武。

? 因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的（至少在單個(gè)頁(yè)內(nèi)是如此），所以對(duì)于I/O密集型的 范圍査詢會(huì)比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的I/O要少得多锈津。對(duì)于某些存儲(chǔ)引擎呀酸，例 如MylSAM和Percona XtraDB,甚至可以通過(guò)OPTIMIZE命令使得索引完全順序排 列，這讓簡(jiǎn)單的范圍査詢能使用完全順序的索引訪問(wèn)琼梆。

? 一些存儲(chǔ)引擎如MylSAM在內(nèi)存中只緩存索引性誉，數(shù)據(jù)則依賴于操作系統(tǒng)來(lái)緩存，因 此要訪問(wèn)數(shù)據(jù)需要一次系統(tǒng)調(diào)用茎杂。這可能會(huì)導(dǎo)致嚴(yán)重的性能問(wèn)題错览，尤其是那些系統(tǒng) 調(diào)用占了數(shù)據(jù)訪問(wèn)中的最大開(kāi)銷的場(chǎng)景。

? 由于InnoDB的聚簇索弓［,覆蓋索引對(duì)InnoDB表特別有用煌往。InnoDB的二級(jí)索引在 葉子節(jié)點(diǎn)中保存了行的主鍵值倾哺，所以如果二級(jí)主鍵能夠覆蓋査詢，則可以避免對(duì)主 鍵索引的二次査詢刽脖。

在所有這些場(chǎng)景中羞海，在索引中滿足査詢的成本一般比査詢行要小得多。

不是所有類型的索引都可以成為覆蓋索引曲管。覆蓋索引必須要存儲(chǔ)索引列的值却邓，而哈希索 引、空間索引和全文索引等都不存儲(chǔ)索引列的值院水，所以MySQL只能使用B-Tree索引做 覆蓋索引腊徙。另外，不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引的方式也不同檬某，而且不是所有的引擎都 支持覆蓋索引（在寫(xiě)作本書(shū)時(shí)撬腾，Memory存儲(chǔ)引擎就不支持覆蓋索引）。

當(dāng)發(fā)起一個(gè)被索引覆蓋的査詢（也叫做索引覆蓋査詢）時(shí)恢恼，在EXPLAIN的Ext「a列可以 看到“Using index”的信息注"民傻。例如，表sakila.inventory有一個(gè)多列索引（store id, film_id）o MySQL如果只需訪問(wèn)這兩列厅瞎，就可以使用這個(gè)索引做覆蓋索引饰潜，如下所示：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

*************************** ] row ***************************

IJTD>

id: select type: table: type: possible_keys: key: key_len: ref: rows: Extra:

1

SIMPLE inventory index

NULL idx_store_id_film_id

3 ~ ~

NULL

4673

Using index

索引覆蓋査詢還有很多陷阱可能會(huì)導(dǎo)致無(wú)法實(shí)現(xiàn)優(yōu)化。MySQL査詢優(yōu)化器會(huì)在執(zhí)行查 詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋和簸。假設(shè)索引覆蓋了 WHERE條件中的字段彭雾，但不是整 個(gè)査詢涉及的字段。如果條件為假（false）, MySQL 5.5和更早的版本也總是會(huì)回表獲 取數(shù)據(jù)行锁保，盡管并不需要這一行且最終會(huì)被過(guò)濾掉薯酝。

概念

如果索引包含所有滿足查詢需要的數(shù)據(jù)半沽，則該索引稱為覆蓋索引(Covering Index)，也就是平時(shí)所說(shuō)的不需要回表操作吴菠。

判斷標(biāo)準(zhǔn)

使用explain者填，可以通過(guò)輸出的extra列來(lái)判斷，對(duì)于一個(gè)索引覆蓋查詢做葵，顯示為using index占哟，MySQL查詢優(yōu)化器在執(zhí)行查詢前會(huì)決定是否有索引覆蓋查詢

注意

1、覆蓋索引也并不適用于任意的索引類型酿矢，索引必須存儲(chǔ)列的值

2榨乎、Hash 和full-text索引不存儲(chǔ)值，因此MySQL只能使用B-TREE

3瘫筐、并且不同的存儲(chǔ)引擎實(shí)現(xiàn)覆蓋索引都是不同的

4蜜暑、并不是所有的存儲(chǔ)引擎都支持它們

5、如果要使用覆蓋索引策肝，一定要注意SELECT 列表值取出需要的列肛捍，不可以是SELECT *，因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龝?huì)導(dǎo)致索引文件過(guò)大之众，查詢性能下降拙毫，不能為了利用覆蓋索引而這么做

InnoDB

1、覆蓋索引查詢時(shí)除了索引本身的包含的列酝枢，還可以使用其默認(rèn)的聚集索引列

2恬偷、這跟INNOB的索引結(jié)構(gòu)有關(guān)系悍手，主索引是B+樹(shù)索引存儲(chǔ)帘睦，也即我們所說(shuō)的數(shù)據(jù)行即索引，索引即數(shù)據(jù)

3坦康、對(duì)于INNODB的輔助索引竣付，它的葉子節(jié)點(diǎn)存儲(chǔ)的是索引值和指向主鍵索引的位置，然后需要通過(guò)主鍵在查詢表的字段值滞欠，所以輔助索引存儲(chǔ)了主鍵的值

4古胆、覆蓋索引也可以用上INNODB 默認(rèn)的聚集索引

5、innodb引擎的所有儲(chǔ)存了主鍵ID筛璧，事務(wù)ID逸绎，回滾指針，非主鍵ID夭谤，他的查詢就會(huì)是非主鍵ID也可覆蓋來(lái)取得主鍵ID

覆蓋索引是一種非常強(qiáng)大的工具棺牧，能大大提高查詢性能，只需要讀取索引而不用讀取數(shù)據(jù)有以下一些優(yōu)點(diǎn)

1朗儒、索引項(xiàng)通常比記錄要小颊乘，所以MySQL訪問(wèn)更少的數(shù)據(jù)

2参淹、索引都按值的大小順序存儲(chǔ)，相對(duì)于隨機(jī)訪問(wèn)記錄乏悄，需要更少的I/O

3浙值、大多數(shù)據(jù)引擎能更好的緩存索引，比如MyISAM只緩存索引

4檩小、覆蓋索引對(duì)于InnoDB表尤其有用开呐，因?yàn)镮nnoDB使用聚集索引組織數(shù)據(jù)，如果二級(jí)索引中包含查詢所需的數(shù)據(jù)规求，就不再需要在聚集索引中查找了

在sakila的inventory表中负蚊，有一個(gè)組合索引(store_id,film_id)，對(duì)于只需要訪問(wèn)這兩列的查 詢颓哮，MySQL就可以使用索引家妆，如下

表結(jié)構(gòu)

CREATE TABLE `inventory` (

? `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

? `film_id` smallint(5) unsigned NOT NULL,

? `store_id` tinyint(3) unsigned NOT NULL,

? `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

? PRIMARY KEY (`inventory_id`),

? KEY `idx_fk_film_id` (`film_id`),

? KEY `idx_store_id_film_id` (`store_id`,`film_id`),

? CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,

? CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |

查詢語(yǔ)句

```c

mysql>? EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

*************************** 1. row ***************************

? ? ? ? ? id: 1

? select_type: SIMPLE

? ? ? ? table: inventory

? ? ? ? type: index

possible_keys: NULL

? ? ? ? ? key: idx_store_id_film_id

? ? ? key_len: 3

? ? ? ? ? ref: NULL

? ? ? ? rows: 4581

? ? ? ? Extra: Using index

1 row in set (0.03 sec)

在大多數(shù)引擎中，只有當(dāng)查詢語(yǔ)句所訪問(wèn)的列是索引的一部分時(shí)冕茅，索引才會(huì)覆蓋伤极。但是，InnoDB不限于此姨伤，InnoDB的二級(jí)索引在葉子節(jié)點(diǎn)中存儲(chǔ)了 primary key的值哨坪。

因此，sakila.actor表使用InnoDB乍楚，而且對(duì)于是last_name上有索引当编，所以，索引能覆蓋那些訪問(wèn)actor_id的查 詢徒溪，如下

在大多數(shù)引擎中忿偷，只有當(dāng)查詢語(yǔ)句所訪問(wèn)的列是索引的一部分時(shí)，索引才會(huì)覆蓋臊泌。但是鲤桥，InnoDB不限于此，InnoDB的二級(jí)索引在葉子節(jié)點(diǎn)中存儲(chǔ)了 primary key的值渠概。

因此茶凳，sakila.actor表使用InnoDB，而且對(duì)于是last_name上有索引播揪，所以贮喧，索引能覆蓋那些訪問(wèn)actor_id的查 詢，如下

```c

```c

mysql> EXPLAIN SELECT actor_id, last_name? FROM sakila.actor WHERE last_name = 'HOPPER'\G

*************************** 1. row ***************************

? ? ? ? ? id: 1

? select_type: SIMPLE

? ? ? ? table: actor

? ? ? ? type: ref

possible_keys: idx_actor_last_name

? ? ? ? ? key: idx_actor_last_name

? ? ? key_len: 137

? ? ? ? ? ref: const

? ? ? ? rows: 2

? ? ? ? Extra: Using where; Using index

1 row in set (0.00 sec)

使用索引進(jìn)行排序

MySQL中猪狈，有兩種方式生成有序結(jié)果集：一是使用filesort箱沦，二是按索引順序掃描

利用索引進(jìn)行排序操作是非常快的罪裹，而且可以利用同一索引同時(shí)進(jìn) 行查找和排序操作饱普。當(dāng)索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時(shí)运挫，可以使用索引來(lái)排序，如果查詢是連接多個(gè)表套耕，僅當(dāng)ORDER BY中的所有列都是第一個(gè)表的列時(shí)才會(huì)使用索引谁帕，其它情況都會(huì)使用filesort

```c

CREATE TABLE `actor` (

? `actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

? `name` varchar(16) NOT NULL DEFAULT '',

? `password` varchar(16) NOT NULL DEFAULT '',

? PRIMARY KEY (`actor_id`),

? KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into actor(name,password) values ('cat01','1234567'),('cat02','1234567'),('ddddd','1234567'),('aaaaa','1234567');

1、explain select actor_id from actor order by actor_id \G

mysql> explain select actor_id from actor order by actor_id \G

*************************** 1. row ***************************

? ? ? ? ? id: 1

? select_type: SIMPLE

? ? ? ? table: actor

? ? ? ? type: index

possible_keys: NULL

? ? ? ? ? key: PRIMARY

? ? ? key_len: 4

? ? ? ? ? ref: NULL

? ? ? ? rows: 4

? ? ? ? Extra: Using index

1 row in set (0.00 sec)

2冯袍、explain select actor_id from actor order by password \G

mysql> explain select actor_id from actor order by password \G

*************************** 1. row ***************************

? ? ? ? ? id: 1

? select_type: SIMPLE

? ? ? ? table: actor

? ? ? ? type: ALL

possible_keys: NULL

? ? ? ? ? key: NULL

? ? ? key_len: NULL

? ? ? ? ? ref: NULL

? ? ? ? rows: 4

? ? ? ? Extra: Using filesort

1 row in set (0.00 sec)

3匈挖、explain select actor_id from actor order by name \G

mysql> explain select actor_id from actor order by name \G

*************************** 1. row ***************************

? ? ? ? ? id: 1

? select_type: SIMPLE

? ? ? ? table: actor

? ? ? ? type: index

possible_keys: NULL

? ? ? ? ? key: name

? ? ? key_len: 50

? ? ? ? ? ref: NULL

? ? ? ? rows: 4

? ? ? ? Extra: Using index

1 row in set (0.00 sec)

當(dāng)MySQL不能使用索引進(jìn)行排序時(shí)，就會(huì)利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對(duì)數(shù)據(jù)進(jìn)行排序康愤，如果內(nèi)存裝載不下儡循，它會(huì)將磁盤(pán)上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè)數(shù)據(jù)塊進(jìn)行排序征冷，然后將各個(gè)塊合并成有序的結(jié)果集（實(shí)際上就是外排序）

對(duì)于filesort择膝，MySQL有兩種排序算法

1、兩遍掃描算法(Two passes)

實(shí)現(xiàn)方式是先將需要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出检激，然后在設(shè)定的內(nèi)存（通過(guò)參數(shù)sort_buffer_size設(shè)定）中進(jìn)行排序肴捉，完成排序之后再次通過(guò)行指針信息取出所需的Columns

注：該算法是4.1之前采用的算法，它需要兩次訪問(wèn)數(shù)據(jù)叔收，尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作齿穗。另一方面，內(nèi)存開(kāi)銷較小

2饺律、 一次掃描算法(single pass)

該算法一次性將所需的Columns全部取出窃页，在內(nèi)存中排序后直接將結(jié)果輸出

注：從 MySQL 4.1 版本開(kāi)始使用該算法。它減少了I/O的次數(shù)复濒，效率較高脖卖，但是內(nèi)存開(kāi)銷也較大。如果我們將并不需要的Columns也取出來(lái)芝薇，就會(huì)極大地浪費(fèi)排序過(guò)程所需要 的內(nèi)存胚嘲。在 MySQL 4.1 之后的版本中作儿，可以通過(guò)設(shè)置 max_length_for_sort_data 參數(shù)來(lái)控制 MySQL 選擇第一種排序算法還是第二種洛二。當(dāng)取出的所有大字段總大小大于 max_length_for_sort_data 的設(shè)置時(shí)，MySQL 就會(huì)選擇使用第一種排序算法攻锰，反之晾嘶，則會(huì)選擇第二種。為了盡可能地提高排序性能娶吞，我們自然更希望使用第二種排序算法垒迂，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當(dāng)對(duì)連接操作進(jìn)行排序時(shí)妒蛇，如果ORDER BY僅僅引用第一個(gè)表的列机断，MySQL對(duì)該表進(jìn)行filesort操作楷拳，然后進(jìn)行連接處理，此時(shí)吏奸，EXPLAIN輸出“Using filesort”欢揖；否則，MySQL必須將查詢的結(jié)果集生成一個(gè)臨時(shí)表奋蔚，在連接完成之后進(jìn)行filesort操作她混，此時(shí)，EXPLAIN輸出 “Using temporary;Using filesort”