創(chuàng)建高性能索引

索引優(yōu)化應(yīng)該是對(duì)查詢性能優(yōu)化的最有效的手段了。索引可以輕易的將查詢性能提升幾個(gè)數(shù)量級(jí)。

索引基礎(chǔ)

SELECT first_name FROM 表 WHERE id=5;

對(duì)于上面這個(gè)查詢，如果id列上有索引。則MySQL將使用該索引找到id=5的行，也就是說(shuō)，mysql現(xiàn)在索引上按值查找，然后返回所有包含該值的數(shù)據(jù)行。

索引可以包含一個(gè)或多個(gè)列的值。如果索引包含多個(gè)列，那么列的順序也十分重要，因?yàn)镸ySQL只能高效的使用索引的最左前綴列。

索引的類型

在MySQL中，索引是在存儲(chǔ)引擎層而不是服務(wù)器層實(shí)現(xiàn)的。

下面介紹mysql支持的索引類型：

B-Tree索引

當(dāng)我們?cè)谡務(wù)撍饕臅r(shí)候，如果沒(méi)有特別的指明類型，那么多半都是B-Tree索引，他使用B樹數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)數(shù)據(jù)。當(dāng)然更多的使用的是B+樹的數(shù)據(jù)結(jié)構(gòu)，你比如說(shuō)Innodb。

B樹索引為什么能夠加快數(shù)據(jù)的訪問(wèn)速度呢，這就打到我的甜點(diǎn)位了，因?yàn)榇鎯?chǔ)引擎不需要進(jìn)行全表掃描了呀，取而代之的是從索引的根節(jié)點(diǎn)開始進(jìn)行搜索，并且每個(gè)子節(jié)點(diǎn)都有子節(jié)點(diǎn)頁(yè)的上限和下限，最終引擎要么就是找到，要么就是找不到。

還有一個(gè)點(diǎn)B+樹是所有節(jié)點(diǎn)信息都在葉子節(jié)點(diǎn)保存著呢

請(qǐng)注意：索引對(duì)多個(gè)值進(jìn)行排序的依據(jù)是你在建表語(yǔ)句中定義索引時(shí)列的順序。

你比如說(shuō)如下的數(shù)據(jù)表：

CREATE TABLE People(
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob date  not null,
	gender enum('m','f') not null,
	key(last_name,frist_name,dob)
);

咱就是說(shuō)，這個(gè)key，我又想提幾句

MySQL中有四種Key: Primary Key, Unique Key, Key 和 Foreign Key。

除了Foreign Key最好理解外，其他的都要區(qū)分一下。

剩下的三種都要在原表上建立索引。

Primary Key和Unique Key之間的區(qū)別網(wǎng)上說(shuō)的最多。Primary Key的提出就是為了唯一標(biāo)示表中的字段，就像我們的身份證號(hào)一樣。此外，所有字段都必須是not null的Unique Key則是為了保證表中有些字段是唯一的。比如有些單位領(lǐng)導(dǎo)叫“張三”，所以下面招人的時(shí)候是決不可招一個(gè)有同樣名字的。

至于Key嗎，網(wǎng)上說(shuō)的比較少。其實(shí)某個(gè)字段標(biāo)記為Key，是不能保證這個(gè)字段的值在表中是唯一出現(xiàn)的。它的目的就是建立索引。

之前所述的索引對(duì)下面的類型的查詢都是有效的：

• 全值匹配
  • 全值匹配指的是和索引中的所有列進(jìn)行匹配，例如你要查找一個(gè)姓名，出生日期的信息，就可以完全走索引。
• 匹配最左前綴、
  • 就是查找last_name 的信息可以走索引。
• 匹配列前綴
  • ? 也可以值匹配某一列的值的開頭部分。比如說(shuō)查找所有以J開頭的last_name的人，就是可以使用索引的第一列。
• 精確匹配某一列并范圍匹配另外一列
  • ? 就是這個(gè)索引我們可以第一列l(wèi)ast_name 全匹配，但是first_name進(jìn)行一個(gè)范圍匹配，這樣的操作
• 只訪問(wèn)索引的查詢
  • B樹通常是支持只訪問(wèn)索引的查詢，即查詢只需要訪問(wèn)索引，而無(wú)須訪問(wèn)數(shù)據(jù)行。

因?yàn)檠?，這個(gè)索引樹中的節(jié)點(diǎn)是有序的，所以處理按值查找外，索引還可以用于查詢中的order by 操作，一般來(lái)說(shuō)，

如果B樹可以按照某種方式查找到值，那么也可以按照這種方式用于排序，所以，如果orderby 子句滿足我們剛剛上面提到的查詢類型，那么這個(gè)索引也可以滿足對(duì)應(yīng)的排序需求。

下面呢，我就要你介紹一些B樹索引的限制了：

• 如果不是按照索引的最左列開始查找，則無(wú)法使用索引。（比如姓氏以某個(gè)字母結(jié)尾的這種也是無(wú)法使用索引的）

• 不能跳過(guò)索引的列，就像上面的例子，你不能跳過(guò)first_name 去查找last_name 和dob

• 如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無(wú)法使用索引優(yōu)化查找。

WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'

這個(gè)查詢就只能使用索引的前兩列，因?yàn)閘ike是一個(gè)范圍條件。

所以，如果范圍查詢列值的數(shù)量有限，那么就可以通過(guò)使用多個(gè)等于條件來(lái)代替范圍條件。

哈希索引

哈希索引基于哈希表實(shí)現(xiàn)，只有精確匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼。哈希碼是一個(gè)比較小的值，并且不同鍵值的行計(jì)算出來(lái)的哈希碼也不一樣，哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)哈希表中保存指向每個(gè)數(shù)據(jù)行的指針。

在MySQL中只有memory引擎顯示支持哈希索引。這也是memory引擎表的默認(rèn)索引類型。

select name FROM 表  WHERE name=‘peter';

哈希索引查詢過(guò)程：

MySQL先計(jì)算’peter’的哈希值,并使用該值尋找對(duì)應(yīng)的記錄指針。f（peter）=8784 。所以mysql在索引中查找8784。就可以找到一個(gè)指向表的第三行的指針，最后一步，比較第三行的值是不是peter，以確保就是要查找的行。

因?yàn)樗饕恍璐鎯?chǔ)對(duì)應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，這也讓哈希索引查找的速度十分快。

但是

哈希索引，也是有他自己的限制。

• 哈希索引只包含哈希值和行指針，而不存儲(chǔ)字段值，所以不能使用索引中的值來(lái)避免讀取行。
• 哈希索引的數(shù)據(jù)并不是按照哦索引值順序存儲(chǔ)的，所以也就無(wú)法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來(lái)計(jì)算哈希值。
• 哈希索引只支持等值比較查詢，不支持任何范圍查詢。
• 訪問(wèn)哈希索引的數(shù)據(jù)非?？欤怯泻芏喙_突。當(dāng)哈希沖突出現(xiàn)的時(shí)候，存儲(chǔ)引擎必須遍歷鏈表中的所有行指針，逐行進(jìn)行比較。直到找到所有符合條件的行。
• 如果哈希沖突特別多的時(shí)候，一些索引維護(hù)的操作的代價(jià)也會(huì)特別高。

創(chuàng)建自定義哈希索引

如果存儲(chǔ)引擎不支持哈希索引，則可以模擬像InnoDB一樣創(chuàng)建哈希索引，這樣同樣可以使用一些哈希索引的便利。

思路：

在b樹的基礎(chǔ)上創(chuàng)建一個(gè)偽哈希索引，這和真正的哈希索引不是一回事，因?yàn)檫€是使用b樹進(jìn)行查找，但是他使用的是哈希值不是鍵本身進(jìn)行索引查找，你需要做的是在查詢的WHERE子句中手動(dòng)指定使用哈希函數(shù)。

比如：

我們下面這個(gè)查詢url的語(yǔ)句：

select id FROM url where url='www.baidu.com';

如果我們刪除原來(lái)url列上的索引，而新增一個(gè)url_crc列，使用crc32做哈希，就如下面:

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

你這樣做，他的性能就很高，有多高，有三四層樓那么高。

因?yàn)镸ySQL優(yōu)化器會(huì)使用這個(gè)選擇性很高而體積很小的基于url_crc列的索引來(lái)完成查找。即使多個(gè)記錄有相同的索引值，查找依舊很快，只需根據(jù)哈希值做快速的整數(shù)比較就能找到索引條目，然后一一比較返回的對(duì)應(yīng)行。

當(dāng)然這種實(shí)現(xiàn)的缺陷是需要維護(hù)哈希值?？梢允謩?dòng)維護(hù)，也可以使用觸發(fā)器實(shí)現(xiàn)。

如果使用這種方式，你也切記不要使用sha1（）和md5（）作為哈希函數(shù)因?yàn)檫@兩函數(shù)計(jì)算出來(lái)的哈希值是特別長(zhǎng)的字符串，會(huì)浪費(fèi)大量空間，比較時(shí)也會(huì)比較慢。

處理哈希沖突

就像上面那個(gè)哈希索引查詢，你是必須包含常量值的在WHERE子句中，不然哈希值沖突你就沒(méi)法搞了呀：

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

空間索引

myisam表支持空間索引，可以用作地理數(shù)據(jù)存儲(chǔ)。和b樹索引不同，這類索引無(wú)須前綴查詢?？臻g索引會(huì)從所有維度來(lái)索引數(shù)據(jù)。查詢時(shí)，可以有效的使用任意維度來(lái)組合查詢。必須使用MySQL的GIS相關(guān)函數(shù)如mbrcontains（）等來(lái)維護(hù)數(shù)據(jù)。

全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。他有許多需要注意的細(xì)節(jié)，如停用詞，詞干和復(fù)數(shù)，布爾搜索等。

其他索引類別

還有許多第三方存儲(chǔ)引擎使用不同類型的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)索引。例如TokuDB使用分形樹索引。這是一類較新開發(fā)的數(shù)據(jù)結(jié)構(gòu)，既有b樹的很多優(yōu)點(diǎn)，也避免了b樹的一些缺點(diǎn)。

索引的優(yōu)點(diǎn)

索引可以讓服務(wù)器快速的定位到表的指定位置，但是這并不是索引的唯一作用。

最常見的b樹索引，按照順序存儲(chǔ)數(shù)據(jù)，所以mysql可以用來(lái)做order by和group by 操作。因?yàn)閿?shù)據(jù)是有序的，所以b樹也會(huì)將相關(guān)的列值存儲(chǔ)在一起。最后 ，因?yàn)樗饕写鎯?chǔ)了實(shí)際的列值，所以某些查詢只使用索引就能完成全部查詢：

總而言之，言而總之，有著如下的優(yōu)點(diǎn)：

索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量索引可以幫助服務(wù)器避免排序和臨時(shí)表索引可以將隨機(jī)IO變?yōu)轫樞騃O。

思考一個(gè)問(wèn)題，索引時(shí)最好的解決方案嗎？

其實(shí)不見的，對(duì)于非常小的表，大多是掃描全表是更加高效的，對(duì)于中到大型表索引就很有效，對(duì)于特大型表，建立和使用索引的代價(jià)將隨之增長(zhǎng)，這種情況下就要使用分區(qū)技術(shù)了，這個(gè)我們后面的文章介紹。

高性能的索引策略

獨(dú)立的列

如果查詢的列不是獨(dú)立的，則MySQL就不會(huì)使用索引。獨(dú)立的列是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。

如：

select actor_id FROM sakila.actor where actor_id + 1 = 5;

其實(shí)屏肉眼你是可以判斷出這個(gè)actor_id其實(shí)就是=4，但是mysql是無(wú)法自動(dòng)解析這個(gè)方程式的。這完全是用戶行為，我們應(yīng)該養(yǎng)成習(xí)慣，始終將索引列單獨(dú)放在比較符號(hào)的一側(cè)

前綴索引和索引的選擇性

有時(shí)啊，需要的索引很長(zhǎng)的字符列，這樣呢會(huì)使索引變得大且慢。一個(gè)策略是前面提到過(guò)的模擬哈希索引。再者呢？

我們可以索引開始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。

但是

這樣會(huì)降低索引的選擇性：

索引的選擇性=不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值。

索引的選擇性越高則查詢的效率越高，因?yàn)檫x擇性高的會(huì)在查找時(shí)過(guò)濾掉更多的行。

當(dāng)然對(duì)于blob，text，varchar這種很長(zhǎng)類型的列，必須使用前綴索引，mysql不允許索引這些列的完整長(zhǎng)度。

多列索引

很多人對(duì)多列索引的理解不夠。一個(gè)常見的錯(cuò)誤就是，為每個(gè)列都創(chuàng)建獨(dú)立的索引，或者按照錯(cuò)誤錯(cuò)誤的順序創(chuàng)建多列索引。

你比如說(shuō)下面這個(gè)例子：

CREATE TABLE t(
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
);

像這樣在多個(gè)列上建立獨(dú)立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。

但是

MySQL5.0和更新的版本引入了一個(gè)叫“索引合并”的策略，一定程度上是可以使用表上的多個(gè)單列索引來(lái)定位指定的行。在此之前的版本的MySQL只能使用其中某個(gè)單列索引。

你再比如說(shuō)下面這個(gè)例子：

select film_id,actor_id from biao
where actor_id =1 OR film_id = 1;

在老版本的mysql中，你像上面這樣where里的OR語(yǔ)句的情況是不能使用兩個(gè)單列索引進(jìn)行掃描的，只會(huì)進(jìn)行全表掃描。除非你把or改成UNION的形式。

索引合并策略確確實(shí)實(shí)的解決了上面我們所描述的那種情況。但實(shí)際上更多時(shí)候還是說(shuō)明呀，表上的索引建的hin糟糕：

• 當(dāng)出現(xiàn)服務(wù)器對(duì)多個(gè)索引做相交操作時(shí)（通常有多個(gè)and條件）。通常其實(shí)意味著是需要一個(gè)包含所有相關(guān)列的多列索引，而不是多個(gè)獨(dú)立的單列索引。
• 當(dāng)服務(wù)器需要對(duì)多個(gè)索引做聯(lián)合操作時(shí)（通常有多個(gè)or操作），通常需要耗費(fèi)大量的CPU和內(nèi)存資源在算法的緩存，排序和合并操作上。特別是有些索引的選擇性不高，需要合并掃描返回的大量數(shù)據(jù)的時(shí)候
• 更重要的是，優(yōu)化器并不會(huì)把這些計(jì)算到“查詢成本”中，優(yōu)化器只關(guān)心隨機(jī)頁(yè)面讀取。這會(huì)使得查詢的成本被低估，導(dǎo)致該執(zhí)行計(jì)劃還不如直接走全表掃描。這樣子不但會(huì)消耗更多的CPU和內(nèi)存資源，還可能會(huì)影響到查詢的并發(fā)性。

所以：如果在explain中看到有索引合并，應(yīng)該好好去檢查一下查詢的表和結(jié)構(gòu)，看看是不是已經(jīng)最優(yōu)了。你可以通過(guò)參數(shù)optimizer switch 來(lái)關(guān)閉索引合并功能。也可以使用ignore index 提示優(yōu)化器忽略掉某些索引。

選擇合適的索引順序

我們遇到的最容易引起困惑的問(wèn)題就是索引列的順序。這個(gè)順序的正確性其實(shí)是依賴使用該索引的查詢是怎么樣的。并且我們還需要考慮如何更好的滿足排序和分組的需要。（順帶說(shuō)一嘴，本節(jié)內(nèi)容適用于b樹索引）

我們之前也有介紹，在一個(gè)多列b樹索引中，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列，索引可以按照升序或者是降序進(jìn)行掃描，以滿足查詢列里的orderby groupby 和distinct等子句的查詢需求。

多列索引的列順序是至關(guān)重要的。

那么，對(duì)于如何選擇索引的列順序呢？有這樣一個(gè)經(jīng)驗(yàn)法則：將選擇性最高的列放到索引的最前列。當(dāng)然了場(chǎng)景不同，選擇不同，沒(méi)有那種放任四海皆準(zhǔn)的法則。

當(dāng)不需要考慮排序和分組時(shí)，將選擇性最高的列放在前面通常是很好的。這時(shí)候索引的作用就是用于優(yōu)化where條件的查找。在這種情況下，這樣設(shè)計(jì)的索引確實(shí)能夠最快的過(guò)濾出需要的行，對(duì)于在where子句中只使用索引部分前綴列的查詢來(lái)說(shuō)選擇性也更高。當(dāng)然為了性能你還得考慮哪些列是運(yùn)行頻率比較高的，來(lái)調(diào)整索引列的順序,讓這種情況下的索引的選擇性最高。

你比如說(shuō)下面這個(gè)例子：

SELECT * FROM 表 
WHERE
staff_id = 2 AND customer_id = 584

那么你就要考慮是創(chuàng)建一個(gè)（staff_id,customer_id）索引，還是兩者調(diào)換一下順序呢？

這個(gè)我們就得去查詢一下表中值的分布情況，來(lái)確定哪個(gè)列的選擇性更高。

我們一查就發(fā)現(xiàn)：

staff_id有7992個(gè)數(shù)據(jù)，staff_id只有30個(gè)數(shù)據(jù)。

那么根據(jù)我們的經(jīng)驗(yàn)法則，我們應(yīng)該將索引列customer_id放到前面，因?yàn)閷?duì)應(yīng)條件值的customer_id數(shù)量更少。

這樣做其實(shí)你還要注意一個(gè)地方，因?yàn)檫@個(gè)查詢結(jié)果是十分依賴選定的具體值。如果按照上面的查詢結(jié)果進(jìn)行優(yōu)化，可能對(duì)其他的值不公平，進(jìn)而導(dǎo)致服務(wù)器整體的性能可能更糟。

但是一般情況下我們就用下面介紹的經(jīng)驗(yàn)法則就可以知道那個(gè)字段選擇性更高：

SELECT COUNT(DISTINCT staff_id)/count(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM 表;

這樣我們可以看出來(lái)customer_id的選擇性更高， 所以就將它作為索引的第一列

ALTER TABLE 表  payment ADD KEY (customer_id,staff_id);

聚簇索引

聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式。具體的細(xì)節(jié)依賴于其實(shí)現(xiàn)方式，比如說(shuō)innodb的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了b樹索引和數(shù)據(jù)行。

當(dāng)表中有聚簇索引時(shí)，他的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(yè)中。

如果沒(méi)有定義主鍵，innodb會(huì)選擇一個(gè)唯一的非空索引代替。如果沒(méi)有這樣的索引，innodb會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引。innodb只聚集在同一個(gè)頁(yè)面中的記錄。包含相鄰鍵值的頁(yè)面可能會(huì)相距甚遠(yuǎn)。

但是聚簇主鍵可能對(duì)性能有幫助，也可能導(dǎo)致嚴(yán)重的性能問(wèn)題

聚集的數(shù)據(jù)有一些重要的優(yōu)點(diǎn)：

• 可以把相關(guān)數(shù)據(jù)保存在一起。比如存儲(chǔ)電子郵箱，可以根據(jù)用戶ID來(lái)聚集數(shù)據(jù)，這樣只需要從磁盤讀取少數(shù)的數(shù)據(jù)頁(yè)就能獲取某個(gè)用戶的全部郵件。如果沒(méi)有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤IO。
• 數(shù)據(jù)訪問(wèn)更快。聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)b樹中，因此從聚簇索引中獲取數(shù)據(jù)你通常要比在非聚簇索引中查找要快
• 使用覆蓋索引掃描的查詢可以直接使用葉節(jié)點(diǎn)中的主鍵值。

聚簇索引的一些缺點(diǎn)：

• 聚簇?cái)?shù)據(jù)最大限度地提高了IO密集型應(yīng)用的性能，但是如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問(wèn)的數(shù)據(jù)就沒(méi)那么重要了，聚簇索引也就沒(méi)什么優(yōu)勢(shì)了。
• 插入速度嚴(yán)重依賴與插入順序。按照主鍵的順序插入是加載數(shù)據(jù)到innodb表中速度最快的方式。但是如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用optimize TABLE命令重新組織一下表。
• 更新聚簇索引列的代價(jià)很大，因?yàn)閕nnodb會(huì)強(qiáng)制將每個(gè)被更新的行移動(dòng)到新的位置。
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動(dòng)行的時(shí)候，可能面臨“葉分裂”的問(wèn)題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁(yè)中時(shí)，存儲(chǔ)引擎會(huì)將該頁(yè)分裂成兩個(gè)
• 頁(yè)面來(lái)容納該行，這就是一次頁(yè)分裂操作。頁(yè)分裂會(huì)導(dǎo)致表占用更多的磁盤空間。
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者頁(yè)分裂導(dǎo)致數(shù)據(jù)存儲(chǔ)不連續(xù)的時(shí)候。
• 二級(jí)索引（非聚簇索引）可能比想象的要更大，因?yàn)樵诙?jí)索引的葉子節(jié)點(diǎn)包含了引用行的主鍵列。

最后一點(diǎn)，做出一個(gè)解釋，為什么二級(jí)索引需要兩次索引查找？因?yàn)槎?jí)索引葉子節(jié)點(diǎn)保存的不是指向行的物理位置的指針，而是行的主鍵值。這就意味著通過(guò)二級(jí)索引查找行，存儲(chǔ)引擎需要找到二級(jí)索引的葉子節(jié)點(diǎn)獲得對(duì)應(yīng)的主鍵值。然后根據(jù)這個(gè)值去聚簇索引中查找到對(duì)應(yīng)的行。

覆蓋索引

通常啊，我們大家都會(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ù)行。

咱就是說(shuō)，如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要回表查詢呢？

如果一個(gè)索引包含所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

如果咱們查詢只是需要掃描索引而無(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ì)IO密集型的應(yīng)用也有幫助，因?yàn)樗饕葦?shù)據(jù)更小，更容易全部放入內(nèi)存。
• 因?yàn)樗饕前凑樟兄淀樞虼鎯?chǔ)的，所以對(duì)于IO密集型的范圍查詢會(huì)比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO要少的多。
• 由于innodb使用聚簇索引，所以覆蓋索引對(duì)innodb是特別有用的，innodb的二級(jí)索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級(jí)主鍵能夠覆蓋查詢，則可以避免對(duì)主鍵索引的二次查詢。這個(gè)我們之前也有提到過(guò)。

當(dāng)然，我們要清楚一點(diǎn)，不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲(chǔ)索引列的值，而哈希索引，空間索引和全文索引等都不存儲(chǔ)索引列的值，所以mysql只能使用b樹索引做覆蓋索引。

但是

索引覆蓋查詢還是有很多陷阱可能導(dǎo)致無(wú)法實(shí)現(xiàn)優(yōu)化，MySQL查詢優(yōu)化器會(huì)在執(zhí)行查詢前判斷是否有一個(gè)索引能進(jìn)行覆蓋，假設(shè)索引覆蓋了where條件中的字段，但不是整個(gè)查詢涉及的字段。MySQL5.5或者更早的版本還是會(huì)回表獲取數(shù)據(jù)行，盡管這一行可能最終還是會(huì)被過(guò)濾掉。

我們舉個(gè)栗子：

SELECT * FROM products where actor = 'SEAM CARREY'
AND title like '%APOLLO%';

像上面這個(gè)查詢索引是無(wú)法覆蓋的，有兩個(gè)原因：

• 沒(méi)有任何索引能夠覆蓋這個(gè)查詢。因?yàn)椴樵兊慕Y(jié)果字段是包括了所有的列，沒(méi)有任何一個(gè)索引是可以覆蓋所有的列的。當(dāng)然，理論上MySQL還是有一個(gè)捷徑可以利用，就是where語(yǔ)句中的條件是有索引覆蓋的。
• MySQL不能在索引中執(zhí)行l(wèi)ike操作。這是底層存儲(chǔ)引擎API的限制。MySQL5.5和更早的版本只支持在索引中做簡(jiǎn)單比較操作（例如等于，不等于，大于）。mysql能在索引中做最左前綴匹配的like比較，因?yàn)樵摬僮骺梢赞D(zhuǎn)換為簡(jiǎn)單的比較操作，但是如果是通配符開頭的like查詢，存儲(chǔ)引擎就無(wú)法做比較匹配。

也有辦法是可以解決我上面說(shuō)的這兩個(gè)問(wèn)題的，那就是需要重寫查詢并巧妙地設(shè)計(jì)索引。首先將索引擴(kuò)展到覆蓋是三個(gè)數(shù)據(jù)列（actor，title，prod_id），然后按照如下的方式重寫查詢。

select * 
from products
join (
	select prod_id
	from products
	where actor = 'SEAM CARREY' AND title like '%APOLLO%';
) AS t1 ON (t1.prod_id=products.prod_id);

我們把這種方式叫做延遲關(guān)聯(lián)，因?yàn)檠舆t了對(duì)列的訪問(wèn)。在查詢的第一階段MySQL可以使用覆蓋索引，在from子句的子查詢中找到匹配的prod_id,然后根據(jù)這些prod_id 值在外層查詢匹配獲取需要的所有列值，雖然無(wú)法使用索引覆蓋整個(gè)查詢，但總算比完全無(wú)法利用索引覆蓋的好。

延時(shí)關(guān)聯(lián)，即通過(guò)使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)，尤其在大分頁(yè)查詢的場(chǎng)景下，可以提高查詢效率。

使用索引掃描來(lái)做排序

MySQL有兩種方式可以生成有序的結(jié)果：通過(guò)排序操作；或者按照索引順序掃描；如果explain出來(lái)的type列的值是index，則說(shuō)明mysql使用了索引掃描來(lái)做排序（不要和extra列的using index高混淆了）

掃描索引本身是很快的，因?yàn)橹恍枰獜囊粭l索引記錄移動(dòng)到緊挨著的下一條記錄。但是如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對(duì)應(yīng)的行。這基本上都是隨機(jī)IO，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序的全表掃描慢，尤其是在IO密集型的工作負(fù)載時(shí)。

只有當(dāng)索引的列順序和order by子句順序完全一致，并且所有列的排序方向都一樣時(shí)，MySQL才能用索引來(lái)對(duì)結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)order by子句引用的字段全為第一個(gè)表時(shí)，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求；否則MySQL都需要執(zhí)行排序操作，而無(wú)法利用索引排序。

當(dāng)然有一種情況下order by子句可以不滿足索引的最左前綴的要求，就是前導(dǎo)列為常量的時(shí)候。如果where子句或者join子句中對(duì)這些列指定了常量，就可以彌補(bǔ)索引的不足。

比如說(shuō)我們舉一個(gè)例子：

對(duì)于下面這樣一個(gè)表結(jié)構(gòu)：

create table 表名（
。。。。
key lizi （date，id1，id2），
。。。。
）

然后對(duì)于下面這個(gè)查詢語(yǔ)句：

select 內(nèi)容
from  表
where date  = 11
order by id1,id2;

這種查詢，MySQL就可以使用lizi這個(gè)索引為查詢排序。當(dāng)然，我們可以看到即使order by這個(gè)子句是不滿足最左前綴的要求的，但是沒(méi)關(guān)系呀，索引的第一列date的被指定為了一個(gè)常數(shù)呀。

你再比如像下面這樣：

where date = 常數(shù)
order by id1;

你像上面這個(gè)還是可以使用索引排序的，第一列常數(shù)，然后再索引的最左前綴嘛。

你再在比如說(shuō)下面這個(gè)：

where date >日期 order by  date，id1;

這個(gè)就也能用索引排序，order by使用的兩列就是索引的最左前綴。

都講了這么老半天的能用索引排序的，那么下面我就來(lái)說(shuō)說(shuō)這個(gè)不能使用索引排序的查詢。

• 使用兩種不同的排序方向，即使索引列都是正序排序的。

where date = 常數(shù) order by id1 desc ，id2  asc;

• order by 子句中引用了一個(gè)不在索引中的列

where  date  = 常數(shù) order by id1 ，id3;

• where和order by 中的列無(wú)法組合成索引的最左前綴

where date = 常數(shù) order by id2;

• 查詢的第一列不是常數(shù)，而是范圍，就也不能用最左前綴了。

where date > 常數(shù) order by id1，id2;

• id1這一列有多個(gè)等于條件，對(duì)于排序也是范圍查詢，不能使用索引排序

where date = 常數(shù) AND id1 IN （1,2）order by id2;

壓縮（前綴壓縮）索引

myisam使用前綴壓縮來(lái)減少索引的大小，從而讓更多的索引可以放入內(nèi)存中，這在某些情況下能極大的提高性能。默認(rèn)只壓縮字符串，但通過(guò)參數(shù)設(shè)置也可以對(duì)整數(shù)做壓縮。

myisam壓縮每個(gè)索引塊的方法是，先完全保存索引塊中的第一個(gè)值，然后將其他值和第一個(gè)值進(jìn)行比較得到相同前綴的字節(jié)數(shù)和剩余的不同后綴部分，把這部分存儲(chǔ)起來(lái)即可。

就是這樣，你比如說(shuō)：索引塊的第一個(gè)值是“perform”，第二個(gè)值是“performance”，那么第二個(gè)值的前綴壓縮后存儲(chǔ)的類似“7，ance”這樣的形式。myisam對(duì)行指針也采用類似的前綴壓縮方式。

壓縮塊使用更少的空間，代價(jià)是某些操作可能更慢。這你也是可以想象的嘛，因?yàn)榍熬Y壓縮了，你必然是不能二分查找，只能順序查找 了，對(duì)于order by desc 這種倒序的，那就更難受了。

我們可以在create table 語(yǔ)句中指定pack_keys參數(shù)來(lái)控制索引壓縮的方式。

冗余和重復(fù)索引

MySQL是允許在相同列上創(chuàng)建多個(gè)索引，無(wú)論你創(chuàng)建的時(shí)候是有意的還是無(wú)意的。MySQL需要單獨(dú)維護(hù)重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢的時(shí)候也需要進(jìn)行逐個(gè)考慮，這回影響性能。

重復(fù)索引，啥叫重復(fù)索引，就是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引。應(yīng)該避免這樣的創(chuàng)建，發(fā)現(xiàn)之后也應(yīng)該立即移除。

你比如說(shuō)下面這樣的例子：

create table test（
		ID int NOT null primary key,
		......
		unique(ID),
		INDEX(ID)
）

這上面，你創(chuàng)建了一個(gè)主鍵，先加上唯一限制，然后再加上索引以供查詢使用。事實(shí)上呀，MySQL的唯一限制和主鍵限制都是通過(guò)索引實(shí)現(xiàn)的 ，因此呀，上面這個(gè)寫法其實(shí)就是在相同的列上 創(chuàng)建了三個(gè)重復(fù)的索引。通常情況下是沒(méi)有必要這樣做的，除非呀是在同一列上創(chuàng)建不同類型的索引來(lái)滿足不同的查詢需求。

冗余索引和重復(fù)索引其實(shí)又有一些不同。如果創(chuàng)建了索引（A,B），再創(chuàng)建索引（A）那就是冗余索引了，因?yàn)檫@只是前一個(gè)索引的前綴索引。但是你要是創(chuàng)建的是（B,A）那可就不是冗余索引了。當(dāng)然不同的索引類型肯定也不會(huì)涉及到冗余索引的事情。

還有一種情況，將索引（A）擴(kuò)展為了索引（A，ID），其中ID是主鍵，這在innodb中來(lái)說(shuō)，主鍵列已經(jīng)包含到二級(jí)索引中去了，所以這也是冗余索引。

大多數(shù)情況下，我們不應(yīng)該是創(chuàng)建新索引，而是擴(kuò)展已有的索引。但你話也不能說(shuō)死，有時(shí)候出于性能的考慮，也會(huì)考慮冗余索引，因?yàn)槟銛U(kuò)展索引會(huì)導(dǎo)致其變得太大，從而影響其他使用該索引的查詢的性能。

未使用的索引

除了冗余索引，和重復(fù)索引，還有些索引呀，服務(wù)器可能這輩子都用不到，這種也要考慮刪除掉呀。

索引和鎖

索引可以讓查詢鎖定更少的行。

總結(jié)

在選擇索引和編寫利用索引的查詢時(shí)：有三個(gè)原則需要始終牢記。

• 單行訪問(wèn)是很慢的
• 按順序訪問(wèn)范圍數(shù)據(jù)是很快的
• 索引覆蓋查詢是很快的。
  都是通過(guò)索引實(shí)現(xiàn)的 ，因此呀，上面這個(gè)寫法其實(shí)就是在相同的列上 創(chuàng)建了三個(gè)重復(fù)的索引。通常情況下是沒(méi)有必要這樣做的，除非呀是在同一列上創(chuàng)建不同類型的索引來(lái)滿足不同的查詢需求。

到此這篇關(guān)于Mysql中創(chuàng)建高性能索引詳解的文章就介紹到這了,更多相關(guān)Mysql高性能索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！