索引(在MySQL中也叫做“鍵（key)”)是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。這是索引的基本功能，除此之外，本章還將討論索引其他一些方面有用的屬性。

索引對于良好的性能非常關(guān)鍵。尤其是當(dāng)表中的數(shù)據(jù)量越來越大時(shí)，索引對性能的影響愈發(fā)重要。在數(shù)據(jù)量較小且負(fù)載較低時(shí)，不恰當(dāng)?shù)乃饕龑π阅艿挠绊懣赡苓€不明顯，但當(dāng)數(shù)據(jù)量逐漸增大時(shí)，性能則會急劇下降。

不過，索引卻經(jīng)常被忽略，有時(shí)候甚至被誤解，所以在實(shí)際案例中經(jīng)常會遇到由糟糕索引導(dǎo)致的問題。這也是我們把索引優(yōu)化放在了靠前的章節(jié)，甚至比查詢優(yōu)化還靠前的原因。

索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化最有效的手段了。索引能夠輕易將查詢性能提高幾個(gè)數(shù)量級，“最優(yōu)”的索引有時(shí)比一個(gè)“好的”索引性能要好兩個(gè)數(shù)量級。創(chuàng)建一個(gè)真正“最優(yōu)”的索引經(jīng)常需要重寫查詢，所以，本章和下一章的關(guān)系非常緊密。

5.1 索引基礎(chǔ)

要理解MySQL中索引是如何工作的,最簡單的方法就是去看看一本書的“索引”部分:如果想在一本書中找到某個(gè)特定主題，一般會先看書的“索引”，找到對應(yīng)的頁碼。

在MySQL中，存儲引擎用類似的方法使用索引，其先在索引中找到對應(yīng)值，然后根據(jù)匹配的索引記錄找到對應(yīng)的數(shù)據(jù)行。假如要運(yùn)行下面的查詢:

mysql> SELECT first_name FROH sakila.actor wHERE actor_id = 5;

如果在actor_id列上建有索引，則 MySQL將使用該索引找到actor_id為5的行，也就是說，MySQL先在索引上按值進(jìn)行查找，然后返回所有包含該值的數(shù)據(jù)行。

索引可以包含一個(gè)或多個(gè)列的值。如果索引包含多個(gè)列，那么列的順序也十分重要，因?yàn)镸ySQL 只能高效地使用索引的最左前綴列。創(chuàng)建一個(gè)包含兩個(gè)列的索引，和創(chuàng)建兩個(gè)只包含一列的索引是大不相同的，下面將詳細(xì)介紹。

5.1.1 索引的類型

索引有很多種類型，可以為不同的場景提供更好的性能。在MySQL中，索引是在存儲引擎層而不是服務(wù)器層實(shí)現(xiàn)的。所以，并沒有統(tǒng)一的索引標(biāo)準(zhǔn):不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引。即使多個(gè)存儲引擎支持同一種類型的索引，其底層的實(shí)現(xiàn)也可能不同。

下面我們先來看看MySQL支持的索引類型，以及它們的優(yōu)點(diǎn)和缺點(diǎn)。

B-Tree索引

當(dāng)人們談?wù)撍饕臅r(shí)候，如果沒有特別指明類型，那多半說的是B-Tree索引，它使用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲數(shù)據(jù)生2。大多數(shù)MySQL引擎都支持這種索引。Archive引擎是一個(gè)例外:5.1之前Archive不支持任何索引，直到5.1才開始支持單個(gè)自增列(AUTO_INCREMENT）的索引。

我們使用術(shù)語“B-Tree”,是因?yàn)镸ySQL在 CREATE TABLE和其他語句中也使用該關(guān)鍵字。不過，底層的存儲引擎也可能使用不同的存儲結(jié)構(gòu)，例如，NDB集群存儲引擎內(nèi)部實(shí)際上使用了T-Tree結(jié)構(gòu)存儲這種索引，即使其名字是BTREE ﹔InnoDB則使用的是B+Tree,各種數(shù)據(jù)結(jié)構(gòu)和算法的變種不在本書的討論范圍之內(nèi)。

存儲引擎以不同的方式使用B-Tree索引，性能也各有不同，各有優(yōu)劣。例如，MyISAM使用前綴壓縮技術(shù)使得索引更小，但I(xiàn)nnoDB則按照原數(shù)據(jù)格式進(jìn)行存儲。再如MyISAM索引通過數(shù)據(jù)的物理位置引用被索引的行，而InnoDB則根據(jù)主鍵引用被索引的行。

B-Tree通常意味著所有的值都是按順序存儲的，并且每一個(gè)葉子頁到根的距離相同。圖5-1展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。MyISAM使用的結(jié)構(gòu)有所不同，但基本思想是類似的。

B-Tree索引能夠加快訪問數(shù)據(jù)的速度，因?yàn)榇鎯σ娌辉傩枰M(jìn)行全表掃描來獲取需要的數(shù)據(jù)，取而代之的是從索引的根節(jié)點(diǎn)（圖示并未畫出）開始進(jìn)行搜索。根節(jié)點(diǎn)的槽中存放了指向子節(jié)點(diǎn)的指針，存儲引擎根據(jù)這些指針向下層查找。通過比較節(jié)點(diǎn)頁的值和要查找的值可以找到合適的指針進(jìn)入下層子節(jié)點(diǎn)，這些指針實(shí)際上定義了子節(jié)點(diǎn)頁中值的上限和下限。最終存儲引擎要么是找到對應(yīng)的值，要么該記錄不存在。

葉子節(jié)點(diǎn)比較特別，它們的指針指向的是被索引的數(shù)據(jù)，而不是其他的節(jié)點(diǎn)頁（不同引擎的“指針”類型不同)。圖5-1中僅繪制了一個(gè)節(jié)點(diǎn)和其對應(yīng)的葉子節(jié)點(diǎn)，其實(shí)在根節(jié)點(diǎn)和葉子節(jié)點(diǎn)之間可能有很多層節(jié)點(diǎn)頁。樹的深度和表的大小直接相關(guān)。

B-Tree對索引列是順序組織存儲的，所以很適合查找范圍數(shù)據(jù)。例如，在一個(gè)基于文本域的索引樹上，按字母順序傳遞連續(xù)的值進(jìn)行查找是非常合適的，所以像“找出所有以I到K開頭的名字”這樣的查找效率會非常高。

請注意，索引對多個(gè)值進(jìn)行排序的依據(jù)是CREATE TABLE語句中定義索引時(shí)列的順序?？匆幌伦詈髢蓚€(gè)條目，兩個(gè)人的姓和名都一樣，則根據(jù)他們的出生日期來排列順序。

可以使用B-Tree索引的查詢類型。B-Tree索引適用于全鍵值、鍵值范圍或鍵前綴查找。其中鍵前綴查找只適用于根據(jù)最左前綴的查找生3。前面所述的索引對如下類型的查詢有效。

全值匹配

全值匹配指的是和索引中的所有列進(jìn)行匹配，例如前面提到的索引可用于查找姓名為Cuba Allen、出生于1960-01-01的人。

匹配最左前綴

前面提到的索引可用于查找所有姓為Allen的人，即只使用索引的第一列。

匹配列前綴

也可以只匹配某一列的值的開頭部分。例如前面提到的索引可用于查找所有以J開頭的姓的人。這里也只使用了索引的第一列。

匹配范圍值

例如前面提到的索引可用于查找姓在Allen和Barrymore之間的人。這里也只使用了索引的第一列。

精確匹配某一列并范圍匹配另外一列

前面提到的索引也可用于查找所有姓為Allen，并且名字是字母K開頭（比如Kim、Karl等)的人。即第一列l(wèi)ast_name全匹配，第二列first_name范圍匹配。

只訪問索引的查詢

B-Tree通?？梢灾С帧爸辉L問索引的查詢”，即查詢只需要訪問索引，而無須訪問數(shù)據(jù)行。后面我們將單獨(dú)討論這種“覆蓋索引”的優(yōu)化。

因?yàn)樗饕龢渲械墓?jié)點(diǎn)是有序的，所以除了按值查找之外，索引還可以用于查詢中的ORDER BY操作（按順序查找)。一般來說，如果B-Tree可以按照某種方式查找到值，那么也可以按照這種方式用于排序。所以，如果ORDER BY子句滿足前面列出的幾種查詢類型，則這個(gè)索引也可以滿足對應(yīng)的排序需求。

下面是一些關(guān)于B-Tree索引的限制:

· 如果不是按照索引的最左列開始查找，則無法使用索引。例如上面例子中的索引無法用于查找名字為Bill的人，也無法查找某個(gè)特定生日的人，因?yàn)檫@兩列都不是最左數(shù)據(jù)列。類似地，也無法查找姓氏以某個(gè)字母結(jié)尾的人。

· 不能跳過索引中的列。也就是說，前面所述的索引無法用于查找姓為Smith并且在某個(gè)特定日期出生的人。如果不指定名(first_name)，則 MySQL只能使用索引的第一列。

· 如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找。例如有查詢wHERE last_name= 'Smith' AND first_name LIKE '%’AND dob = '1976-12-23'，這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里L(fēng)IKE是一個(gè)范圍條件（但是服務(wù)器可以把其余列用于其他目的)。如果范圍查詢列值的數(shù)量有限，那么可以通過使用多個(gè)等于條件來代替范圍條件。在本章的索引案例學(xué)習(xí)部分，我們將演示一個(gè)詳細(xì)的案例。

到這里讀者應(yīng)該可以明白，前面提到的索引列的順序是多么的重要:這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求。

也有些限制并不是B-Tree本身導(dǎo)致的，而是 MySQL優(yōu)化器和存儲引擎使用索引的方式導(dǎo)致的,這部分限制在未來的版本中可能就不再是限制了。

哈希索引

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

在MySQL 中，只有Memory引擎顯式支持哈希索引。這也是Memory引擎表的默認(rèn)索引類型，Memory引擎同時(shí)也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，這在數(shù)據(jù)庫世界里面是比較與眾不同的。如果多個(gè)列的哈希值相同，索引會以鏈表的方式存放多個(gè)記錄指針到同一個(gè)哈希條目中。

MySQL先計(jì)算'Peter'的哈希值，并使用該值尋找對應(yīng)的記錄指針。因?yàn)閒( 'Peter' )=8784，所以MySQL在索引中查找8784，可以找到指向第3行的指針，最后一步是比較第三行的值是否為'Peter'，以確保就是要查找的行。

因?yàn)樗饕陨碇恍璐鎯?yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，這也讓哈希索引查找的速度非?？臁Ｈ欢?#xff0c;哈希索引也有它的限制:

·哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行。不過，訪問內(nèi)存中的行的速度很快，所以大部分情況下這一點(diǎn)對性能的影響并不明顯。

·哈希索引數(shù)據(jù)并不是按照索引值順序存儲的，所以也就無法用于排序。

·哈希索引也不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部內(nèi)容來計(jì)算哈希值的。例如,在數(shù)據(jù)列(A,B)上建立哈希索引,如果查詢只有數(shù)據(jù)列A,則無法使用該索引。

·哈希索引只支持等值比較查詢，包括=、IN()、<=>(注意◇>和<=>是不同的操作)。也不支持任何范圍查詢，例如 WHERE price > 100。

·訪問哈希索引的數(shù)據(jù)非?？?#xff0c;除非有很多哈希沖突（不同的索引列值卻有相同的哈希值)。當(dāng)出現(xiàn)哈希沖突的時(shí)候，存儲引擎必須遍歷鏈表中所有的行指針，逐行進(jìn)行比較，直到找到所有符合條件的行。

·如果哈希沖突很多的話，一些索引維護(hù)操作的代價(jià)也會很高。例如，如果在某個(gè)選擇性很低（哈希沖突很多）的列上建立哈希索引，那么當(dāng)從表中刪除一行時(shí)，存儲引擎需要遍歷對應(yīng)哈希值的鏈表中的每一行，找到并刪除對應(yīng)行的引用，沖突越多，代價(jià)越大。

因?yàn)檫@些限制，哈希索引只適用于某些特定的場合。而一旦適合哈希索引，則它帶來的性能提升將非常顯著。舉個(gè)例子，在數(shù)據(jù)倉庫應(yīng)用中有一種經(jīng)典的“星型”schema，需要關(guān)聯(lián)很多查找表，哈希索引就非常適合查找表的需求。

.

除了Memory引擎外，NDB集群引擎也支持唯一哈希索引，且在NDB集群引擎中作用非常特殊，但這不屬于本書的范圍。

InnoDB引擎有一個(gè)特殊的功能叫做“自適應(yīng)哈希索引 (adaptive hash index)”。當(dāng)InnoDB注意到某些索引值被使用得非常頻繁時(shí)，它會在內(nèi)存中基于B-Tree索引之上再創(chuàng)建一個(gè)哈希索引，這樣就讓B-Tree索引也具有哈希索引的一些優(yōu)點(diǎn)，比如快速的哈希查找。這是一個(gè)完全自動的、內(nèi)部的行為，用戶無法控制或者配置，不過如果有必要，完全可以關(guān)閉該功能。

創(chuàng)建自定義哈希索引。如果存儲引擎不支持哈希索引，則可以模擬像InnoDB一樣創(chuàng)建哈希索引，這可以享受一些哈希索引的便利，例如只需要很小的索引就可以為超長的鍵創(chuàng)建索引。

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

下面是一個(gè)實(shí)例，例如需要存儲大量的URL，并需要根據(jù)URL進(jìn)行搜索查找。如果使用B-Tree來存儲URL，存儲的內(nèi)容就會很大，因?yàn)閁RL本身都很長。正常情況下會有如下查詢:

mysql> SELECT id FROM ur1 NHERE url="http://nnw.mysq1.com";

若刪除原來URL列上的索引，而新增一個(gè)被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查詢:

mysql>SELECT id FROM ur1 NHERE ur1="http://vwrw.mysql.com"

->AND url_crc=CRC32( "http://winw.mysql.com");

這樣做的性能會非常高，因?yàn)镸ySQL優(yōu)化器會使用這個(gè)選擇性很高而體積很小的基于url_crc列的索引來完成查找（在上面的案例中，索引值為1560514994)。即使有多個(gè)記錄有相同的索引值，查找仍然很快，只需要根據(jù)哈希值做快速的整數(shù)比較就能找到索引條目，然后一一比較返回對應(yīng)的行。另外一種方式就是對完整的URL字符串做索引，那樣會非常慢。

這樣實(shí)現(xiàn)的缺陷是需要維護(hù)哈希值。可以手動維護(hù)，也可以使用觸發(fā)器實(shí)現(xiàn)。下面的案例演示了觸發(fā)器如何在插入和更新時(shí)維護(hù)url_crc列。首先創(chuàng)建如下表:

然后創(chuàng)建觸發(fā)器。先臨時(shí)修改一下語句分隔符，這樣就可以在觸發(fā)器定義中使用分號:

剩下的工作就是驗(yàn)證一下觸發(fā)器如何維護(hù)哈希索引:

如果采用這種方式，記住不要使用SHA1()和 MD5()作為哈希函數(shù)。因?yàn)檫@兩個(gè)函數(shù)計(jì)算出來的哈希值是非常長的字符串，會浪費(fèi)大量空間，比較時(shí)也會更慢。SHA1()和MD5()是強(qiáng)加密函數(shù)，設(shè)計(jì)目標(biāo)是最大限度消除沖突，但這里并不需要這樣高的要求。簡單哈希函數(shù)的沖突在一個(gè)可以接受的范圍，同時(shí)又能夠提供更好的性能。

如果數(shù)據(jù)表非常大，CRC32()會出現(xiàn)大量的哈希沖突，則可以考慮自己實(shí)現(xiàn)一個(gè)簡單的64位哈希函數(shù)。這個(gè)自定義函數(shù)要返回整數(shù)，而不是字符串。一個(gè)簡單的辦法可以使用MD5()函數(shù)返回值的一部分來作為自定義哈希函數(shù)。這可能比自己寫一個(gè)哈希算法的性能要差（參考第7章)，不過這樣實(shí)現(xiàn)最簡單:

因?yàn)樗^的“生日悖論”注5，出現(xiàn)哈希沖突的概率的增長速度可能比想象的要快得多。CRC32()返回的是32位的整數(shù)，當(dāng)索引有93 000條記錄時(shí)出現(xiàn)沖突的概率是1%。例如我們將/usr/share/dict/words中的詞導(dǎo)入數(shù)據(jù)表并進(jìn)行CRC32()計(jì)算,最后會有98569行。這就已經(jīng)出現(xiàn)一次哈希沖突了，沖突讓下面的查詢返回了多條記錄:

要避免沖突問題，必須在WHERE條件中帶入哈希值和對應(yīng)列值。如果不是想查詢具體值，例如只是統(tǒng)計(jì)記錄數(shù)（不精確的)，則可以不帶入列值，直接使用CRC32()的哈希值查詢即可。還可以使用如FNV64()函數(shù)作為哈希函數(shù)，這是移植自Percona Server的函數(shù)，可以以插件的方式在任何MySQL版本中使用，哈希值為64位，速度快，且沖突比CRC32()要少很多。

空間數(shù)據(jù)索引(R-Tree)

MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲。和B-Tree索引不同，這類索引無須前綴查詢?？臻g索引會從所有維度來索引數(shù)據(jù)。查詢時(shí)，可以有效地使用任意維度來組合查詢。必須使用MySQL 的GIS相關(guān)函數(shù)如MBRCONTAINS()等來維護(hù)數(shù)據(jù)。MySQL的GIS支持并不完善，所以大部分人都不會使用這個(gè)特性。開源關(guān)系數(shù)據(jù)庫系統(tǒng)中對GIS的解決方案做得比較好的是PostgresQL的 PostGIS。

全文索引

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

在相同的列上同時(shí)創(chuàng)建全文索引和基于值的B-Tree索引不會有沖突，全文索引適用于MATCH AGAINST操作，而不是普通的 WHERE條件操作。

我們將在第7章討論更多的全文索引的細(xì)節(jié)。

其他索引類別

還有很多第三方的存儲引擎使用不同類型的數(shù)據(jù)結(jié)構(gòu)來存儲索引。例如TokuDB使用分形樹索引 (fractal tree index)，這是一類較新開發(fā)的數(shù)據(jù)結(jié)構(gòu)，既有B-Tree的很多優(yōu)點(diǎn)，也避免了B-Tree的一些缺點(diǎn)。如果通讀完本章，可以看到很多關(guān)于InnoDB的主題，包括聚簇索引、覆蓋索引等。多數(shù)情況下，針對InnoDB的討論也都適用于TokuDB。

ScaleDB使用Patricia tries (這個(gè)詞不是拼寫錯(cuò)誤)，其他一些存儲引擎技術(shù)如InfiniDB和Infobright則使用了一些特殊的數(shù)據(jù)結(jié)構(gòu)來優(yōu)化某些特殊的查詢。

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

索引可以讓服務(wù)器快速地定位到表的指定位置。但是這并不是索引的唯一作用，到目前為止可以看到，根據(jù)創(chuàng)建索引的數(shù)據(jù)結(jié)構(gòu)不同，索引也有一些其他的附加作用。

最常見的B-Tree索引，按照順序存儲數(shù)據(jù)，所以MySQL可以用來做ORDER BY和GROUPBY操作。因?yàn)閿?shù)據(jù)是有序的，所以B-Tree也就會將相關(guān)的列值都存儲在一起。最后，因?yàn)樗饕写鎯α藢?shí)際的列值，所以某些查詢只使用索引就能夠完成全部查詢。據(jù)此特性，總結(jié)下來索引有如下三個(gè)優(yōu)點(diǎn):

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

“索引”這個(gè)主題完全值得單獨(dú)寫一本書，如果想深入理解這部分內(nèi)容，強(qiáng)烈建議閱讀由Tapio Lahdenmaki和Mike Leach編寫的Relational Database Index Design and theOptimizers (Wiley出版社）一書，該書詳細(xì)介紹了如何計(jì)算索引的成本和作用、如何評估查詢速度、如何分析索引維護(hù)的代價(jià)和其帶來的好處等。

Lahdenmaki和Leach在書中介紹了如何評價(jià)一個(gè)索引是否適合某個(gè)查詢的“三星系統(tǒng)”(three-star system):索引將相關(guān)的記錄放到一起則獲得一星﹔如果索引中的數(shù)據(jù)順序和查找中的排列順序一致則獲得二星﹔如果索引中的列包含了查詢中需要的全部列則獲得“三星”。后面我們將會介紹這些原則。

5.3 高性能的索引策略

正確地創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢的基礎(chǔ)。前面已經(jīng)介紹了各種類型的索引及其對應(yīng)的優(yōu)缺點(diǎn)。現(xiàn)在我們一起來看看如何真正地發(fā)揮這些索引的優(yōu)勢。

高效地選擇和使用索引有很多種方式，其中有些是針對特殊案例的優(yōu)化方法，有些則是針對特定行為的優(yōu)化。使用哪個(gè)索引，以及如何評估選擇不同索引的性能影響的技巧，則需要持續(xù)不斷地學(xué)習(xí)。接下來的幾個(gè)小節(jié)將幫助讀者理解如何高效地使用索引。

5.3.1 獨(dú)立的列

我們通常會看到一些查詢不當(dāng)?shù)厥褂盟饕?#xff0c;或者使得MySQL無法使用已有的索引。如果查詢中的列不是獨(dú)立的，則MySQL就不會使用索引?！蔼?dú)立的列”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。

例如，下面這個(gè)查詢無法使用actor_id列的索引:

mysql>SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

憑肉眼很容易看出 MHERE中的表達(dá)式其實(shí)等價(jià)于actor_id = 4，但是MySQL無法自動解析這個(gè)方程式。這完全是用戶行為。我們應(yīng)該養(yǎng)成簡化WHERE條件的習(xí)慣，始終將索引列單獨(dú)放在比較符號的一側(cè)。

下面是另一個(gè)常見的錯(cuò)誤:

mysql> SELECT ... MHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

5.3.2 前綴索引和索引選擇性

有時(shí)候需要索引很長的字符列，這會讓索引變得大且慢。一個(gè)策略是前面提到過的模擬哈希索引。但有時(shí)候這樣做還不夠，還可以做些什么呢?

通?？梢运饕_始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。索引的選擇性是指，不重復(fù)的索引值（也稱為基數(shù)，cardinality)和數(shù)據(jù)表的記錄總數(shù)（#T)的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

一般情況下某個(gè)列前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB、TEXT或者很長的VARCHAR類型的列，必須使用前綴索引，因?yàn)镸ySQL不允許索引這些列的完整長度。

訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時(shí)又不能太長（以便節(jié)約空間)。前綴應(yīng)該足夠長，以使得前綴索引的選擇性接近于索引整個(gè)列。換句話說，前綴的“基數(shù)”應(yīng)該接近于完整列的“基數(shù)”。

為了決定前綴的合適長度，需要找到最常見的值的列表，然后和最常見的前綴列表進(jìn)行比較。在示例數(shù)據(jù)庫Sakila中并沒有合適的例子,所以我們從表city中生成一個(gè)示例表，這樣就有足夠的數(shù)據(jù)進(jìn)行演示:

現(xiàn)在我們有了示例數(shù)據(jù)集。數(shù)據(jù)分布當(dāng)然不是真實(shí)的分布﹔因?yàn)槲覀兪褂昧薘AND()，所以你的結(jié)果會與此不同，但對這個(gè)練習(xí)來說這并不重要。首先，我們找到最常見的城市列表:

每個(gè)前綴都比原來的城市出現(xiàn)的次數(shù)更多，因此唯一前綴比唯一城市要少得多。然后我們增加前綴長度，直到這個(gè)前綴的選擇性接近完整列的選擇性。經(jīng)過實(shí)驗(yàn)后發(fā)現(xiàn)前綴長度為7時(shí)比較合適:

計(jì)算合適的前綴長度的另外一個(gè)辦法就是計(jì)算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性。下面顯示如何計(jì)算完整列的選擇性:

通常來說（盡管也有例外情況)，這個(gè)例子中如果前綴的選擇性能夠接近0.031，基本上就可用了。可以在一個(gè)查詢中針對不同前綴長度進(jìn)行計(jì)算，這對于大表非常有用。下面給出了如何在同一個(gè)查詢中計(jì)算不同前綴長度的選擇性:

查詢顯示當(dāng)前綴長度到達(dá)7的時(shí)候，再增加前綴長度，選擇性提升的幅度已經(jīng)很小了。

只看平均選擇性是不夠的，也有例外的情況，需要考慮最壞情況下的選擇性。平均選擇性會讓你認(rèn)為前綴長度為4或者5的索引已經(jīng)足夠了，但如果數(shù)據(jù)分布很不均勻，可能就會有陷阱。如果觀察前綴為4的最常出現(xiàn)城市的次數(shù)，可以看到明顯不均勻:

如果前綴是4個(gè)字節(jié)，則最常出現(xiàn)的前綴的出現(xiàn)次數(shù)比最常出現(xiàn)的城市的出現(xiàn)次數(shù)要大很多。即這些值的選擇性比平均選擇性要低。如果有比這個(gè)隨機(jī)生成的示例更真實(shí)的數(shù)據(jù)，就更有可能看到這種現(xiàn)象。例如在真實(shí)的城市名上建一個(gè)長度為4的前綴索引，對于以“San”和“New”開頭的城市的選擇性就會非常糟糕，因?yàn)楹芏喑鞘卸家赃@兩個(gè)詞開頭。

在上面的示例中，已經(jīng)找到了合適的前綴長度，下面演示一下如何創(chuàng)建前綴索引:

mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));

前綴索引是一種能使索引更小、更快的有效辦法，但另一方面也有其缺點(diǎn):MySQL無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描。

一個(gè)常見的場景是針對很長的十六進(jìn)制唯一ID使用前綴索引。在前面的章節(jié)中已經(jīng)討論了很多有效的技術(shù)來存儲這類ID信息，但如果使用的是打包過的解決方案，因而無法修改存儲結(jié)構(gòu)，那該怎么辦?例如使用vBulletin或者其他基于MySQL的應(yīng)用在存儲網(wǎng)站的會話(SESSION)時(shí)，需要在一個(gè)很長的十六進(jìn)制字符串上創(chuàng)建索引。此時(shí)如果采用長度為8的前綴索引通常能顯著地提升性能，并且這種方法對上層應(yīng)用完全透明。

5.3.3 多列索引

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

我們會在5.3.4節(jié)中單獨(dú)討論索引列的順序問題。先來看第一個(gè)問題，為每個(gè)列創(chuàng)建獨(dú)立的索引，從 SHOw CREATE TABLE中很容易看到這種情況:

這種索引策略，一般是由于人們聽到一些專家諸如“把 wHERE條件里面的列都建上索引”這樣模糊的建議導(dǎo)致的。實(shí)際上這個(gè)建議是非常錯(cuò)誤的。這樣一來最好的情況下也只能是“一星”索引，其性能比起真正最優(yōu)的索引可能差幾個(gè)數(shù)量級。有時(shí)如果無法設(shè)計(jì)一個(gè)“三星”索引，那么不如忽略掉WHERE子句，集中精力優(yōu)化索引列的順序，或者創(chuàng)建一個(gè)全覆蓋索引。

在多個(gè)列上建立獨(dú)立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。MySQL5.0和更新版本引入了一種叫“索引合并”(index merge)的策略，一定程度上可以使用表上的多個(gè)單列索引來定位指定的行。更早版本的MySQL 只能使用其中某一個(gè)單列索引，然而這種情況下沒有哪一個(gè)獨(dú)立的單列索引是非常有效的。例如，表film_actor在字段film_id和 actor_id上各有一個(gè)單列索引。但對于下面這個(gè)查詢wHERE條件，這兩個(gè)單列索引都不是好的選擇:

但在MySQL 5.0和更新的版本中，查詢能夠同時(shí)使用這兩個(gè)單列索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并。這種算法有三個(gè)變種:OR條件的聯(lián)合(union) ,AND條件的相交( intersection)，組合前兩種情況的聯(lián)合及相交。下面的查詢就是使用了兩個(gè)索引掃描的聯(lián)合，通過EXPLAIN中的Extra列可以看到這點(diǎn):

MySQL會使用這類技術(shù)優(yōu)化復(fù)雜查詢，所以在某些語句的Extra列中還可以看到嵌套操作。

索引合并策略有時(shí)候是一種優(yōu)化的結(jié)果，但實(shí)際上更多時(shí)候說明了表上的索引建得很糟糕:

·當(dāng)出現(xiàn)服務(wù)器對多個(gè)索引做相交操作時(shí)（通常有多個(gè) AND條件)，通常意味著需要一個(gè)包含所有相關(guān)列的多列索引，而不是多個(gè)獨(dú)立的單列索引。

·當(dāng)服務(wù)器需要對多個(gè)索引做聯(lián)合操作時(shí)（通常有多個(gè)0R條件)，通常需要耗費(fèi)大量CPU和內(nèi)存資源在算法的緩存、排序和合并操作上。特別是當(dāng)其中有些索引的選擇性不高，需要合并掃描返回的大量數(shù)據(jù)的時(shí)候。

·更重要的是，優(yōu)化器不會把這些計(jì)算到“查詢成本”(cost)中，優(yōu)化器只關(guān)心隨機(jī)頁面讀取。這會使得查詢的成本被“低估”,導(dǎo)致該執(zhí)行計(jì)劃還不如直接走全表掃描。這樣做不但會消耗更多的CPU和內(nèi)存資源，還可能會影響查詢的并發(fā)性，但如果是單獨(dú)運(yùn)行這樣的查詢則往往會忽略對并發(fā)性的影響。通常來說，還不如像在MySQL4.1或者更早的時(shí)代一樣，將查詢改寫成UNION的方式往往更好。

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

5.3.4 選擇合適的索引列順序

我們遇到的最容易引起困惑的問題就是索引列的順序。正確的順序依賴于使用該索引的查詢，并且同時(shí)需要考慮如何更好地滿足排序和分組的需要（順便說明，本節(jié)內(nèi)容適用于B-Tree索引﹔哈?；蛘咂渌愋偷乃饕⒉粫馚-Tree索引一樣按順序存儲數(shù)據(jù))。

在一個(gè)多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列，等等。所以，索引可以按照升序或者降序進(jìn)行掃描，以滿足精確符合列順序的ORDER BY、GROUP BY和 DISTINCT等子句的查詢需求。

所以多列索引的列順序至關(guān)重要。在Lahdenmaki和 Leach的“三星索引”系統(tǒng)中，列順序也決定了一個(gè)索引是否能夠成為一個(gè)真正的“三星索引”(關(guān)于三星索引可以參考本章前面的5.2節(jié))。在本章的后續(xù)部分我們將通過大量的例子來說明這一點(diǎn)。

對于如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則:將選擇性最高的列放到索引最前列。這個(gè)建議有用嗎﹖在某些場景可能有幫助，但通常不如避免隨機(jī)IO和排序那么重要，考慮問題需要更全面(場景不同則選擇不同，沒有一個(gè)放之四海皆準(zhǔn)的法則。這里只是說明，這個(gè)經(jīng)驗(yàn)法則可能沒有你想象的重要)。

當(dāng)不需要考慮排序和分組時(shí)，將選擇性最高的列放在前面通常是很好的。這時(shí)候索引的作用只是用于優(yōu)化WHERE條件的查找。在這種情況下，這樣設(shè)計(jì)的索引確實(shí)能夠最快地過濾出需要的行，對于在WHERE子句中只使用了索引部分前綴列的查詢來說選擇性也更高。然而，性能不只是依賴于所有索引列的選擇性（整體基數(shù))，也和查詢條件的具體值有關(guān)，也就是和值的分布有關(guān)。這和前面介紹的選擇前綴的長度需要考慮的地方一樣?？赡苄枰鶕?jù)那些運(yùn)行頻率最高的查詢來調(diào)整索引列的順序，讓這種情況下索引的選擇性最高。

以下面的查詢?yōu)槔?

是應(yīng)該創(chuàng)建一個(gè)(staff_id, customer_id)索引還是應(yīng)該顛倒一下順序﹖可以跑一些查詢來確定在這個(gè)表中值的分布情況，并確定哪個(gè)列的選擇性更高。先用下面的查詢預(yù)測一下，看看各個(gè) WHERE 條件的分支對應(yīng)的數(shù)據(jù)基數(shù)有多大:

這樣做有一個(gè)地方需要注意，查詢的結(jié)果非常依賴于選定的具體值。如果按上述辦法優(yōu)化，可能對其他一些條件值的查詢不公平，服務(wù)器的整體性能可能變得更糟，或者其他某些查詢的運(yùn)行變得不如預(yù)期。

如果是從諸如pt-query-digest這樣的工具的報(bào)告中提取“最差”查詢，那么再按上述辦法選定的索引順序往往是非常高效的。如果沒有類似的具體查詢來運(yùn)行，那么最好還是按經(jīng)驗(yàn)法則來做，因?yàn)榻?jīng)驗(yàn)法則考慮的是全局基數(shù)和選擇性，而不是某個(gè)具體查詢:

當(dāng)使用前綴索引的時(shí)候，在某些條件值的基數(shù)比正常值高的時(shí)候，問題就來了。例如，在某些應(yīng)用程序中，對于沒有登錄的用戶，都將其用戶名記錄為“guset”，在記錄用戶行為的會話(session）表和其他記錄用戶活動的表中“guest”就成為了一個(gè)特殊用戶ID。一旦查詢涉及這個(gè)用戶，那么和對于正常用戶的查詢就大不同了，因?yàn)橥ǔＳ泻芏鄷挾际菦]有登錄的。系統(tǒng)賬號也會導(dǎo)致類似的問題。一個(gè)應(yīng)用通常都有一個(gè)特殊的管理員賬號，和普通賬號不同，它并不是一個(gè)具體的用戶，系統(tǒng)中所有的其他用戶都是這個(gè)用戶的好友，所以系統(tǒng)往往通過它向網(wǎng)站的所有用戶發(fā)送狀態(tài)通知和其他消息。這個(gè)賬號的巨大的好友列表很容易導(dǎo)致網(wǎng)站出現(xiàn)服務(wù)器性能問題。

這實(shí)際上是一個(gè)非常典型的問題。任何的異常用戶，不僅僅是那些用于管理應(yīng)用的設(shè)計(jì)糟糕的賬號會有同樣的問題﹔那些擁有大量好友、圖片、狀態(tài)、收藏的用戶，也會有前面提到的系統(tǒng)賬號同樣的問題。

下面是一個(gè)我們遇到過的真實(shí)案例，在一個(gè)用戶分享購買商品和購買經(jīng)驗(yàn)的論壇上，這個(gè)特殊表上的查詢運(yùn)行得非常慢:

這個(gè)查詢看似沒有建立合適的索引，所以客戶咨詢我們是否可以優(yōu)化。EXPLAIN的結(jié)果如下:

MySQL 為這個(gè)查詢選擇了索引(groupId,userId)，如果不考慮列的基數(shù)，這看起來是一個(gè)非常合理的選擇。但如果考慮一下user ID和group ID條件匹配的行數(shù)，可能就會有不同的想法了:

從上面的結(jié)果來看符合組(groupId）條件幾乎滿足表中的所有行，符合用戶(userId)條件的有130萬條記錄——也就是說索引基本上沒什么用。因?yàn)檫@些數(shù)據(jù)是從其他應(yīng)用中遷移過來的，遷移的時(shí)候把所有的消息都賦予了管理員組的用戶。這個(gè)案例的解決辦法是修改應(yīng)用程序代碼，區(qū)分這類特殊用戶和組，禁止針對這類用戶和組執(zhí)行這個(gè)查詢。

從這個(gè)小案例可以看到經(jīng)驗(yàn)法則和推論在多數(shù)情況是有用的，但要注意不要假設(shè)平均情況下的性能也能代表特殊情況下的性能，特殊情況可能會摧毀整個(gè)應(yīng)用的性能。

最后，盡管關(guān)于選擇性和基數(shù)的經(jīng)驗(yàn)法則值得去研究和分析，但一定要記住別忘了WHERE子句中的排序、分組和范圍條件等其他因素，這些因素可能對查詢的性能造成非常大的影響。.

5.3.5聚簇索引

聚簇索引些'并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲方式。具體的細(xì)節(jié)依賴于其實(shí)現(xiàn)方式，但I(xiàn)nnoDB的聚簇索引實(shí)際上在同一個(gè)結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。

當(dāng)表有聚簇索引時(shí),它的數(shù)據(jù)行實(shí)際上存放在索引的葉子頁(leaf page)中。術(shù)語“聚簇”表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。因?yàn)闊o法同時(shí)把數(shù)據(jù)行存放在兩個(gè)不同的地方,所以一個(gè)表只能有一個(gè)聚簇索引(不過,覆蓋索引可以模擬多個(gè)聚簇索引的情況，本章后面將詳細(xì)介紹)。

因?yàn)槭谴鎯σ尕?fù)責(zé)實(shí)現(xiàn)索引，因此不是所有的存儲引擎都支持聚簇索引。本節(jié)我們主要關(guān)注InnoDB，但是這里討論的原理對于任何支持聚簇索引的存儲引擎都是適用的。

圖5-3展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部數(shù)據(jù)，但是節(jié)點(diǎn)頁只包含了索引列。在這個(gè)案例中，索引列包含的是整數(shù)值。

一些數(shù)據(jù)庫服務(wù)器允許選擇哪個(gè)索引作為聚簇索引，但直到本書寫作之際，還沒有任何一個(gè)MySQL內(nèi)建的存儲引擎支持這一點(diǎn)。InnoDB將通過主鍵聚集數(shù)據(jù)，這也就是說圖5-3中的“被索引的列”就是主鍵列。

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

聚簇主鍵可能對性能有幫助，但也可能導(dǎo)致嚴(yán)重的性能問題。所以需要仔細(xì)地考慮聚簇索引，尤其是將表的存儲引擎從InnoDB改成其他引擎的時(shí)候（反過來也一樣)。

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

·可以把相關(guān)數(shù)據(jù)保存在一起。例如實(shí)現(xiàn)電子郵箱時(shí)，可以根據(jù)用戶ID來聚集數(shù)據(jù)，這樣只需要從磁盤讀取少數(shù)的數(shù)據(jù)頁就能獲取某個(gè)用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤I/O。

·數(shù)據(jù)訪問更快。聚簇索引將索引和數(shù)據(jù)保存在同一個(gè)B-Tree中，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快。

·使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點(diǎn)中的主鍵值。

如果在設(shè)計(jì)表和查詢時(shí)能充分利用上面的優(yōu)點(diǎn)，那就能極大地提升性能。同時(shí)，聚簇索引也有一些缺點(diǎn):

·聚簇?cái)?shù)據(jù)最大限度地提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優(yōu)勢了。

·插入速度嚴(yán)重依賴于插入順序。按照主鍵的順序插入是加載數(shù)據(jù)到InnoDB表中速度最快的方式。但如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表。

·更新聚簇索引列的代價(jià)很高，因?yàn)闀?qiáng)制InnoDB將每個(gè)被更新的行移動到新的位置。

·基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行的時(shí)候，可能面臨“頁分裂(page split)”的問題。當(dāng)行的主鍵值要求必須將這一行插入到某個(gè)已滿的頁中時(shí)，存儲引擎會將該頁分裂成兩個(gè)頁面來容納該行，這就是一次頁分裂操作。頁分裂會導(dǎo)致表占用更多的磁盤空間。

·聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時(shí)候。

·二級索引(非聚簇索引）可能比想象的要更大，因?yàn)樵诙壦饕娜~子節(jié)點(diǎn)包含了引用行的主鍵列。

·二級索引訪問需要兩次索引查找,而不是一次。

最后一點(diǎn)可能讓人有些疑惑，為什么二級索引需要兩次索引查找?答案在于二級索引中保存的“行指針”的實(shí)質(zhì)。要記住，二級索引葉子節(jié)點(diǎn)保存的不是指向行的物理位置的指針，而是行的主鍵值。

這意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子節(jié)點(diǎn)獲得對應(yīng)的主鍵值，然后根據(jù)這個(gè)值去聚簇索引中查找到對應(yīng)的行。這里做了重復(fù)的工作:兩次B-Tree查找而不是一次性9。對于InnoDB，自適應(yīng)哈希索引能夠減少這樣的重復(fù)工作。

InnoDB和 MyISAM的數(shù)據(jù)分布對比

聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，以及對應(yīng)的主鍵索引和二級索引的數(shù)據(jù)分布也有區(qū)別，通常會讓人感到困擾和意外。來看看InnoDB和 MyISAM是如何存儲下面這個(gè)表的:

假設(shè)該表的主鍵取值為1 ~10 000，按照隨機(jī)順序插入并使用OPTIAIZE TABLE命令做了優(yōu)化。換句話說，數(shù)據(jù)在磁盤上的存儲方式已經(jīng)最優(yōu)，但行的順序是隨機(jī)的。列col2的值是從1~ 100之間隨機(jī)賦值，所以有很多重復(fù)的值。

MyISAM的數(shù)據(jù)分布。MyISAM的數(shù)據(jù)分布非常簡單，所以先介紹它。MyISAM按照數(shù)據(jù)插入的順序存儲在磁盤上，如圖5-4所示。

在行的旁邊顯示了行號，從О開始遞增。因?yàn)樾惺嵌ㄩL的，所以MyISAM可以從表的開頭跳過所需的字節(jié)找到需要的行(MyISAM并不總是使用圖5-4中的“行號”，而是根據(jù)定長還是變長的行使用不同策略)。

這種分布方式很容易創(chuàng)建索引。下面顯示的一系列圖，隱藏了頁的物理細(xì)節(jié)，只顯示索引中的“節(jié)點(diǎn)”，索引中的每個(gè)葉子節(jié)點(diǎn)包含“行號”。圖5-5顯示了表的主鍵。

這里忽略了一些細(xì)節(jié)，例如前一個(gè)B-Tree節(jié)點(diǎn)有多少個(gè)內(nèi)部節(jié)點(diǎn)，不過這并不影響對非聚簇存儲引擎的基本數(shù)據(jù)分布的理解。

那col2列上的索引又會如何呢?有什么特殊的嗎?回答是否定的:它和其他索引沒有什么區(qū)別。圖5-6顯示了col2列上的索引。

事實(shí)上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個(gè)名

InnoDB 的數(shù)據(jù)分布。因?yàn)镮nnoDB支持聚簇索引，所以使用非常不同的方式存儲同樣的數(shù)據(jù)。InnoDB以如圖5-7所示的方式存儲數(shù)據(jù)。

第一眼看上去，感覺該圖和前面的圖5-5沒有什么不同，但再仔細(xì)看細(xì)節(jié)，會注意到該圖顯示了整個(gè)表，而不是只有索引。因?yàn)樵贗nnoDB中，聚簇索引“就是”表，所以不像MyISAM那樣需要獨(dú)立的行存儲。

聚簇索引的每一個(gè)葉子節(jié)點(diǎn)都包含了主鍵值、事務(wù)ID、用于事務(wù)和MvCCt10的回滾指針以及所有的剩余列（在這個(gè)例子中是col2)。如果主鍵是一個(gè)列前綴索引，InnoDB也會包含完整的主鍵列和剩下的其他列。

還有一點(diǎn)和MyISAM的不同是，InnoDB的二級索引和聚簇索引很不相同。InnoDB二級索引的葉子節(jié)點(diǎn)中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。這樣的策略減少了當(dāng)出現(xiàn)行移動或者數(shù)據(jù)頁分裂時(shí)二級索引的維護(hù)工作。使用主鍵值當(dāng)作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB在移動行時(shí)無須更新二級索引中的這個(gè)“指針”。

圖5-8顯示了示例表的col2索引。每一個(gè)葉子節(jié)點(diǎn)都包含了索引列（這里是col2)，緊接著是主鍵值(col1)。

圖5-8展示了B-Tree的葉子節(jié)點(diǎn)結(jié)構(gòu)，但我們故意省略了非葉子節(jié)點(diǎn)這樣的細(xì)節(jié)。InnoDB的非葉子節(jié)點(diǎn)包含了索引列和一個(gè)指向下級節(jié)點(diǎn)的指針（下一級節(jié)點(diǎn)可以是非

如果還沒有理解聚簇索引和非聚簇索引有什么區(qū)別、為何有這些區(qū)別及這些區(qū)別的重要性，也不用擔(dān)心。隨著學(xué)習(xí)的深入，尤其是學(xué)完本章剩下的部分以及下一章以后，這些問題就會變得越發(fā)清楚。這些概念有些復(fù)雜，需要一些時(shí)間才能完全理解。

在InnoDB表中按主鍵順序插入行

如果正在使用InnoDB表并且沒有什么數(shù)據(jù)需要聚集，那么可以定義一個(gè)代理鍵(surrogate key)作為主鍵，這種主鍵的數(shù)據(jù)應(yīng)該和應(yīng)用無關(guān)，最簡單的方法是使用AUTO_INCREMENT自增列。這樣可以保證數(shù)據(jù)行是按順序?qū)懭?#xff0c;對于根據(jù)主鍵做關(guān)聯(lián)操作的性能也會更好。

最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是對于I/O密集型的應(yīng)用。例如，從性能的角度考慮，使用UUID來作為聚簇索引則會很糟糕:它使得聚簇索引的插入變得完全隨機(jī)，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。

為了演示這一點(diǎn)，我們做如下兩個(gè)基準(zhǔn)測試。第一個(gè)使用整數(shù)ID插入userinfo表:

第二個(gè)例子是userinfo_uuid表。除了主鍵改為UUID，其余和前面的userinfo表完全相同。

我們測試了這兩個(gè)表的設(shè)計(jì)。首先，我們在一個(gè)有足夠內(nèi)存容納索引的服務(wù)器上向這兩個(gè)表各插入100萬條記錄。然后向這兩個(gè)表繼續(xù)插入300萬條記錄，使索引的大小超過服務(wù)器的內(nèi)存容量。表5-1對測試結(jié)果做了比較。

注意到向UUID主鍵插入行不僅花費(fèi)的時(shí)間更長，而且索引占用的空間也更大。這一方面是由于主鍵字段更長﹔另一方面毫無疑問是由于頁分裂和碎片導(dǎo)致的。

為了明白為什么會這樣，來看看往第一個(gè)表中插入數(shù)據(jù)時(shí)，索引發(fā)生了什么變化。圖5-10顯示了插滿一個(gè)頁面后繼續(xù)插入相鄰的下一個(gè)頁面的場景。

如圖5-10所示，因?yàn)橹麈I的值是順序的，所以InnoDB把每一條記錄都存儲在上一條記錄的后面。當(dāng)達(dá)到頁的最大填充因子時(shí)(InnoDB默認(rèn)的最大填充因子是頁大小的15/16，留出部分空間用于以后修改)，下一條記錄就會寫入新的頁中。一旦數(shù)據(jù)按照這種順序的方式加載,主鍵頁就會近似于被順序的記錄填滿,這也正是所期望的結(jié)果(然而，二級索引頁可能是不一樣的)。

對比一下向第二個(gè)使用了UUID聚簇索引的表插入數(shù)據(jù)，看看有什么不同，圖5-11顯示了結(jié)果。

因?yàn)樾滦械闹麈I值不一定比之前插入的大，所以InnoDB無法簡單地總是把新行插入到索引的最后，而是需要為新的行尋找合適的位置--—通常是已有數(shù)據(jù)的中間位置——并且分配空間。這會增加很多的額外工作，并導(dǎo)致數(shù)據(jù)分布不夠優(yōu)化。下面是總結(jié)的一些缺點(diǎn):

·寫入的目標(biāo)頁可能已經(jīng)刷到磁盤上并從緩存中移除，或者是還沒有被加載到緩存中，InnoDB在插入之前不得不先找到并從磁盤讀取目標(biāo)頁到內(nèi)存中。這將導(dǎo)致大量的隨機(jī)I/O。

·因?yàn)閷懭胧莵y序的，InnoDB不得不頻繁地做頁分裂操作，以便為新的行分配空間。頁分裂會導(dǎo)致移動大量數(shù)據(jù)，一次插入最少需要修改三個(gè)頁而不是一個(gè)頁。

·由于頻繁的頁分裂，頁會變得稀疏并被不規(guī)則地填充，所以最終數(shù)據(jù)會有碎片。

在把這些隨機(jī)值載入到聚簇索引以后，也許需要做一次OPTIMIZE TABLE來重建表并優(yōu)化頁的填充。

從這個(gè)案例可以看出，使用InnoDB時(shí)應(yīng)該盡可能地按主鍵順序插入數(shù)據(jù)，并且盡可能地使用單調(diào)增加的聚簇鍵的值來插入新行。

5.3.6 覆蓋索引

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

覆蓋索引是非常有用的工具，能夠極大地提高性能?？紤]一下如果查詢只需要掃描索引而無須回表，會帶來多少好處:

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

·因?yàn)樗饕前凑樟兄淀樞虼鎯Φ?#xff08;至少在單個(gè)頁內(nèi)是如此)，所以對于I/O密集型的范圍查詢會比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的I/O要少得多。對于某些存儲引擎，例如MyISAM和Percona XtraDB，甚至可以通過OPTIMIZE命令使得索引完全順序排列，這讓簡單的范圍查詢能使用完全順序的索引訪問。

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

·由于InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節(jié)點(diǎn)中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

·在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多。

不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL 只能使用B-Tree索引做覆蓋索引。另外，不同的存儲引擎實(shí)現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引(在寫作本書時(shí)，Memory存儲引擎就不支持覆蓋索引)。

當(dāng)發(fā)起一個(gè)被索引覆蓋的查詢（也叫做索引覆蓋查詢）時(shí)，在EXPLAIN的Extra列可以看到“Using index”的信息生12。例如，表sakila.inventory有一個(gè)多列索引(store_id,film_id)。MySQL 如果只需訪問這兩列，就可以使用這個(gè)索引做覆蓋索引，如下所示:

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

來看看為什么會發(fā)生這樣的情況，以及如何重寫查詢以解決該問題。從下面的查詢開始:

這里索引無法覆蓋該查詢，有兩個(gè)原因:

·沒有任何索引能夠覆蓋這個(gè)查詢。因?yàn)椴樵儚谋碇羞x擇了所有的列，而沒有任何索引覆蓋了所有的列。不過，理論上 MySQL還有一個(gè)捷徑可以利用:wHERE條件中的列是有索引可以覆蓋的，因此MySQL可以使用該索引找到對應(yīng)的actor并檢查title是否匹配，過濾之后再讀取需要的數(shù)據(jù)行。

·MySQL不能在索引中執(zhí)行LIKE操作。這是底層存儲引擎API的限制，MySQL 5.5和更早的版本中只允許在索引中做簡單比較操作（例如等于、不等于以及大于)。MySQL能在索引中做最左前綴匹配的LIKE比較，因?yàn)樵摬僮骺梢赞D(zhuǎn)換為簡單的比較操作，但是如果是通配符開頭的LIKE查詢，存儲引擎就無法做比較匹配。這種情況下，MySQL服務(wù)器只能提取數(shù)據(jù)行的值而不是索引值來做比較。

也有辦法可以解決上面說的兩個(gè)問題，需要重寫查詢并巧妙地設(shè)計(jì)索引。先將索引擴(kuò)展至覆蓋三個(gè)數(shù)據(jù)列(artist, title, prod_id)，然后按如下方式重寫查詢:

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

這樣優(yōu)化的效果取決于WHERE條件匹配返回的行數(shù)。假設(shè)這個(gè)products表有100萬行，我們來看一下上面兩個(gè)查詢在三個(gè)不同的數(shù)據(jù)集上的表現(xiàn)，每個(gè)數(shù)據(jù)集都包含100萬行:

1．第一個(gè)數(shù)據(jù)集，Sean Carrey出演了30 000部作品，其中有20 000部的標(biāo)題中包含

了Apollo。

2．第二個(gè)數(shù)據(jù)集，Sean Carrey出演了30 000部作品，其中40部的標(biāo)題中包含了

Apollo。

3．第三個(gè)數(shù)據(jù)集，Sean Carrey出演了50部作品，其中10部的標(biāo)題中包含了Apollo。

使用上面的三種數(shù)據(jù)集來測試兩種不同的查詢，得到的結(jié)果如表5-2所示。

下面是對結(jié)果的分析:

·在示例1中，查詢返回了一個(gè)很大的結(jié)果集，因此看不到優(yōu)化的效果。大部分時(shí)間都花在讀取和發(fā)送數(shù)據(jù)上了。

·在示例2中，經(jīng)過索引過濾，尤其是第二個(gè)條件過濾后只返回了很少的結(jié)果集，優(yōu)化的效果非常明顯﹔在這個(gè)數(shù)據(jù)集上性能提高了5倍,優(yōu)化后的查詢的效率主要得益于只需要讀取40行完整數(shù)據(jù)行，而不是原查詢中需要的30 000行。

·在示例3中，顯示了子查詢效率反而下降的情況。因?yàn)樗饕^濾時(shí)符合第一個(gè)條件的結(jié)果集已經(jīng)很小，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

在大多數(shù)存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以更進(jìn)一步優(yōu)化InnoDB?；叵?-下，InnoDB的二級索引的葉子節(jié)點(diǎn)都包含了主鍵的值，這意味著InnoDB 的二級索引可以有效地利用這些“額外”的主鍵列來覆蓋查詢。

例如，sakila.actor使用InnoDB存儲引擎，并在last_name字段有二級索引，雖然該索引的列不包括主鍵actor_id，但也能夠用于對actor_id做覆蓋查詢:

5.3.7使用索引掃描來做排序

MySQL有兩種方式可以生成有序的結(jié)果:通過排序操作﹔或者按索引順序掃描施13﹔如果EXPLAIN出來的type列的值為“index”，則說明MySQL使用了索引掃描來做排序（不要和 Extra列的“Using index”搞混淆了)。

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

MySQL可以使用同一個(gè)索引既滿足排序，又用于查找行。因此，如果可能，設(shè)計(jì)索引時(shí)應(yīng)該盡可能地同時(shí)滿足這兩種任務(wù)，這樣是最好的。

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時(shí)，MySQL才能夠使用索引來對結(jié)果做排序生1。如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY子句引用的字段全部為第一個(gè)表時(shí)，才能使用索引做排序。ORDER BY子句和查找型查詢的限制是一樣的:需要滿足索引的最左前綴的要求﹔否則，MySQL都需要執(zhí)行排序操作，而無法利用索引排序。

有一種情況下ORDER BY子句可以不滿足索引的最左前綴的要求，就是前導(dǎo)列為常量的時(shí)候。如果 WHERE子句或者J0IN子句中對這些列指定了常量,就可以“彌補(bǔ)”索引的不足。

例如，Sakila示例數(shù)據(jù)庫的表rental在列（rental_date,inventory_id, customer_id)上有名為rental_date的索引。

MySQL可以使用rental_date索引為下面的查詢做排序，從EXPLAIN中可以看到?jīng)]有出現(xiàn)文件排序(filesort）操作:

即使ORDER BY子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因?yàn)樗饕牡谝涣斜恢付橐粋€(gè)常數(shù)。

還有更多可以使用索引做排序的查詢示例。下面這個(gè)查詢可以利用索引排序，是因?yàn)椴樵優(yōu)樗饕牡谝涣刑峁┝顺Ａ織l件，而使用第二列進(jìn)行排序，將兩列組合在一起，就形成了索引的最左前綴:

... WHERE rental_date - '2005-05-25’ORDER BY inventory_id DESc;

下面這個(gè)例子理論上是可以使用索引進(jìn)行關(guān)聯(lián)排序的，但由于優(yōu)化器在優(yōu)化時(shí)將film_actor表當(dāng)作關(guān)聯(lián)的第二張表，所以實(shí)際上無法使用索引:

使用索引做排序的一個(gè)最重要的用法是當(dāng)查詢同時(shí)有ORDER BY和LIMIT子句的時(shí)候。后面我們會具體介紹這些內(nèi)容。

5.3.8 壓縮（前綴壓縮）索引

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

MyISAM壓縮每個(gè)索引塊的方法是，先完全保存索引塊中的第一個(gè)值，然后將其他值和第一個(gè)值進(jìn)行比較得到相同前綴的字節(jié)數(shù)和剩余的不同后綴部分，把這部分存儲起來即可。例如，索引塊中的第一個(gè)值是“perform”，第二個(gè)值是“performance”，那么第二個(gè)值的前綴壓縮后存儲的是類似“7,ance”這樣的形式。MyISAM對行指針也采用類似的前綴壓縮方式。

壓縮塊使用更少的空間，代價(jià)是某些操作可能更慢。因?yàn)槊總€(gè)值的壓縮前綴都依賴前面的值，所以MyISAM查找時(shí)無法在索引塊使用二分查找而只能從頭開始掃描。正序的掃描速度還不錯(cuò)，但是如果是倒序掃描-例如ORDER BY DESC一--就不是很好了。所有在塊中查找某一行的操作平均都需要掃描半個(gè)索引塊。

測試表明，對于CPU密集型應(yīng)用，因?yàn)閽呙栊枰S機(jī)查找，壓縮索引使得MyISAM在索引查找上要慢好幾倍。壓縮索引的倒序掃描就更慢了。壓縮索引需要在CPU內(nèi)存資源與磁盤之間做權(quán)衡。壓縮索引可能只需要十分之一大小的磁盤空間，如果是I/О密集型應(yīng)用，對某些查詢帶來的好處會比成本多很多。

可以在CREATE TABLE語句中指定PACK_KEYS參數(shù)來控制索引壓縮的方式。

5.3.9冗余和重復(fù)索引

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

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

有時(shí)會在不經(jīng)意間創(chuàng)建了重復(fù)索引，例如下面的代碼:

一個(gè)經(jīng)驗(yàn)不足的用戶可能是想創(chuàng)建一個(gè)主鍵，先加上唯一限制，然后再加上索引以供查詢使用。事實(shí)上，MySQL的唯一限制和主鍵限制都是通過索引實(shí)現(xiàn)的，因此，上面的寫法實(shí)際上在相同的列上創(chuàng)建了三個(gè)重復(fù)的索引。通常并沒有理由這樣做，除非是在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求.

冗余索引和重復(fù)索引有一些不同。如果創(chuàng)建了索引(A B)，再創(chuàng)建索引(A)就是冗余索引，因?yàn)檫@只是前一個(gè)索引的前綴索引。因此索引(AB)也可以當(dāng)作索引(A)來使用（這種冗余只是對B-Tree索引來說的)。但是如果再創(chuàng)建索引(B，A)，則不是冗余索引，索引(B)也不是，因?yàn)锽不是索引(A，B)的最左前綴列。另外，其他不同類型的索引（例如哈希索引或者全文索引)也不會是B-Tree索引的冗余索引，而無論覆蓋的索引列是什么。

冗余索引通常發(fā)生在為表添加新索引的時(shí)候。例如，有人可能會增加一個(gè)新的索引(A B)而不是擴(kuò)展已有的索引(A)。還有一種情況是將一個(gè)索引擴(kuò)展為(AID)，其中ID是主鍵，對于InnoDB來說主鍵列已經(jīng)包含在二級索引中了，所以這也是冗余的。

大多數(shù)情況下都不需要冗余索引，應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引。但也有時(shí)候出于性能方面的考慮需要冗余索引，因?yàn)閿U(kuò)展已有的索引會導(dǎo)致其變得太大，從而影響其他使用該索引的查詢的性能。

例如，如果在整數(shù)列上有一個(gè)索引，現(xiàn)在需要額外增加一個(gè)很長的VARCHAR列來擴(kuò)展該索引，那性能可能會急劇下降。特別是有查詢把這個(gè)索引當(dāng)作覆蓋索引，或者這是MyISAM表并且有很多范圍查詢（由于MyISAM的前綴壓縮）的時(shí)候。

考慮一下前面“在InnoDB中按主鍵順序插入行”一節(jié)提到的userinfo表。這個(gè)表有1000 000行，對每個(gè)state_id值大概有20 000條記錄。在state_id列有一個(gè)索引對下面的查詢有用，假設(shè)查詢名為Q1 :

索引擴(kuò)展后，Q2運(yùn)行得更快了，但是Q1卻變慢了。如果我們想讓兩個(gè)查詢都變得更快，就需要兩個(gè)索引，盡管這樣一來原來的單列索引是冗余的了。表5-3顯示這兩個(gè)查詢在不同的索引策略下的詳細(xì)結(jié)果，分別使用MyISAM和InnoDB存儲引擎。注意到只有state_id_2索引時(shí)，InnoDB引擎上的查詢Q1的性能下降并不明顯，這是因?yàn)镮nnoDB沒有使用索引壓縮。

可以看到，表中的索引越多插入速度會越慢。一般來說，增加新索引將會導(dǎo)致INSERT、UPDATE、DELETE等操作的速度變慢，特別是當(dāng)新增索引后導(dǎo)致達(dá)到了內(nèi)存瓶頸的時(shí)候。

解決冗余索引和重復(fù)索引的方法很簡單，刪除這些索引就可以，但首先要做的是找出這樣的索引?？梢酝ㄟ^寫一些復(fù)雜的訪問INFORMATION_SCHEMA表的查詢來找，不過還有兩個(gè)更簡單的方法。可使用Shlomi Noach的common_schema中的一些視圖來定位，common_schema是一系列可以安裝到服務(wù)器上的常用的存儲和視圖（http:l/code.google.com/plcommon-schema/)。這比自己編寫查詢要快而且簡單。另外也可以使用PerconaToolkit中的pt-duplicate-key-checker，該工具通過分析表結(jié)構(gòu)來找出冗余和重復(fù)的索引。對于大型服務(wù)器來說，使用外部的工具可能更合適些﹔如果服務(wù)器上有大量的數(shù)據(jù)或者大量的表，查詢INFORMATION_SCHEM表可能會導(dǎo)致性能問題。

在決定哪些索引可以被刪除的時(shí)候要非常小心?；貞浺幌?#xff0c;在前面的InnoDB的示例表中，因?yàn)槎壦饕娜~子節(jié)點(diǎn)包含了主鍵值，所以在列(A)上的索引就相當(dāng)于在(A，ID)上的索引。如果有像WHERE A = 5 ORDER BY ID這樣的查詢，這個(gè)索引會很有作用。但如果將索引擴(kuò)展為(A B)，則實(shí)際上就變成了(A, B,ID)，那么上面查詢的ORDER BY子句就無法使用該索引做排序，而只能用文件排序了。所以，建議使用Percona 工具箱中的pt-upgrade工具來仔細(xì)檢查計(jì)劃中的索引變更。

5.3.10未使用的索引

除了冗余索引和重復(fù)索引，可能還會有一些服務(wù)器永遠(yuǎn)不用的索引。這樣的索引完全是累贅，建議考慮刪除性18。有兩個(gè)工具可以幫助定位未使用的索引。最簡單有效的辦法是在Percona Server或者M(jìn)ariaDB中先打開userstates服務(wù)器變量（默認(rèn)是關(guān)閉的)，然后讓服務(wù)器正常運(yùn)行一段時(shí)間，再通過查詢INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每個(gè)索引的使用頻率。

另外，還可以使用Percona Toolkit中的pt-index-usage，該工具可以讀取查詢?nèi)罩?#xff0c;并對日志中的每條查詢進(jìn)行EXPLAIN操作，然后打印出關(guān)于索引和查詢的報(bào)告。這個(gè)工具不僅可以找出哪些索引是未使用的，還可以了解查詢的執(zhí)行計(jì)劃——例如在某些情況有些類似的查詢的執(zhí)行方式不一樣，這可以幫助你定位到那些偶爾服務(wù)質(zhì)量差的查詢，優(yōu)化它們以得到一致的性能表現(xiàn)。該工具也可以將結(jié)果寫入到MySQL 的表中，方便查詢結(jié)果。

5.3.11索引和鎖

索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那么就會鎖定更少的行，從兩個(gè)方面來看這對性能都有好處。首先，雖然InnoDB的行鎖效率很高，內(nèi)存使用也很少，但是鎖定行的時(shí)候仍然會帶來額外開銷﹔其次，鎖定超過需要的行會增加鎖爭用并減少并發(fā)性。

InnoDB只有在訪問行的時(shí)候才會對其加鎖，而索引能夠減少InnoDB訪問的行數(shù)，從而減少鎖的數(shù)量。但這只有當(dāng)InnoDB在存儲引擎層能夠過濾掉所有不需要的行時(shí)才有效。如果索引無法過濾掉無效的行，那么在InnoDB檢索到數(shù)據(jù)并返回給服務(wù)器層以后，MySQL服務(wù)器才能應(yīng)用WHERE子句些1。這時(shí)已經(jīng)無法避免鎖定行了:InnoDB已經(jīng)鎖住了這些行，到適當(dāng)?shù)臅r(shí)候才釋放。在 MySQL 5.1和更新的版本中，InnoDB可以在服務(wù)器端過濾掉行后就釋放鎖，但是在早期的MySQL版本中，InnoDB 只有在事務(wù)提交后才能釋放鎖。

通過下面的例子再次使用數(shù)據(jù)庫Sakila很好地解釋了這些情況:

這條查詢僅僅會返回2~4之間的行，但是實(shí)際上獲取了1~4之間的行的排他鎖。InnoDB會鎖住第1行，這是因?yàn)镸ySQL為該查詢選擇的執(zhí)行計(jì)劃是索引范圍掃描:

換句話說，底層存儲引擎的操作是“從索引的開頭開始獲取滿足條件actor_id <5的記錄”，服務(wù)器并沒有告訴InnoDB可以過濾第1行的 WHERE條件。注意到EXPLAIN的Extra列出現(xiàn)了“Using where”，這表示MySQL服務(wù)器將存儲引擎返回行以后再應(yīng)用WHERE過濾條件。

下面的第二個(gè)查詢就能證明第1行確實(shí)已經(jīng)被鎖定，盡管第一個(gè)查詢的結(jié)果中并沒有這個(gè)第1行。保持第一個(gè)連接打開，然后開啟第二個(gè)連接并執(zhí)行如下查詢:

這個(gè)查詢將會掛起，直到第一個(gè)事務(wù)釋放第1行的鎖。這個(gè)行為對于基于語句的復(fù)制(將在第10章討論）的正常運(yùn)行來說是必要的。

就像這個(gè)例子顯示的，即使使用了索引，InnoDB也可能鎖住一些不需要的數(shù)據(jù)。如果不能使用索引查找和鎖定行的話問題可能會更糟糕，MySQL會做全表掃描并鎖住所有的行，而不管是不是需要。

關(guān)于InnoDB、索引和鎖有一些很少有人知道的細(xì)節(jié) :InnoDB在二級索引上使用共享(讀）鎖，但訪問主鍵索引需要排他（寫）鎖。這消除了使用覆蓋索引的可能性，并且使得SELECT FOR UPDATE比 LOCK IN SHARE MODE或非鎖定查詢要慢很多。

5.4索引案例學(xué)習(xí)

理解索引最好的辦法是結(jié)合示例，所以這里準(zhǔn)備了一個(gè)索引的案例。

假設(shè)要設(shè)計(jì)一個(gè)在線約會網(wǎng)站，用戶信息表有很多列，包括國家、地區(qū)、城市、性別、眼睛顏色，等等。網(wǎng)站必須支持上面這些特征的各種組合來搜索用戶，還必須允許根據(jù)用戶的最后在線時(shí)間、其他會員對用戶的評分等對用戶進(jìn)行排序并對結(jié)果進(jìn)行限制。如何設(shè)計(jì)索引滿足上面的復(fù)雜需求呢﹖

出人意料的是第一件需要考慮的事情是需要使用索引來排序，還是先檢索數(shù)據(jù)再排序。使用索引排序會嚴(yán)格限制索引和查詢的設(shè)計(jì)。例如，如果希望使用索引做根據(jù)其他會員對用戶的評分的排序，則wHERE條件中的age BETWEEN 18 AND 25就無法使用索引。如果MySQL使用某個(gè)索引進(jìn)行范圍查詢，也就無法再使用另一個(gè)索引(或者是該索引的后續(xù)字段）進(jìn)行排序了。如果這是很常見的WHERE條件，那么我們當(dāng)然就會認(rèn)為很多查詢需要做排序操作（例如文件排序filesort)。

5.4.1支持多種過濾條件

現(xiàn)在需要看看哪些列擁有很多不同的取值，哪些列在WHERE子句中出現(xiàn)得最頻繁。在有更多不同值的列上創(chuàng)建索引的選擇性會更好。一般來說這樣做都是對的，因?yàn)榭梢宰孧ySQL更有效地過濾掉不需要的行。

country列的選擇性通常不高，但可能很多查詢都會用到。sex列的選擇性肯定很低，但也會在很多查詢中用到。所以考慮到使用的頻率，還是建議在創(chuàng)建不同組合索引的時(shí)候?qū)?sex, country)列作為前綴。

但根據(jù)傳統(tǒng)的經(jīng)驗(yàn)不是說不應(yīng)該在選擇性低的列上創(chuàng)建索引的嗎?那為什么這里要將兩個(gè)選擇性都很低的字段作為索引的前綴列﹖我們的腦子壞了?

我們的腦子當(dāng)然沒壞。這么做有兩個(gè)理由:第一點(diǎn)，如前所述幾乎所有的查詢都會用到sex列。前面曾提到，幾乎每一個(gè)查詢都會用到sex列，甚至?xí)丫W(wǎng)站設(shè)計(jì)成每次都只能按某一種性別搜索用戶。更重要的一點(diǎn)是，索引中加上這一列也沒有壞處，即使查詢沒有使用sex列也可以通過下面的“訣竅”繞過。

這個(gè)“訣竅”就是:如果某個(gè)查詢不限制性別，那么可以通過在查詢條件中新增ANDSEX IN( 'm', 'f')來讓 MySQL選擇該索引。這樣寫并不會過濾任何行，和沒有這個(gè)條件時(shí)返回的結(jié)果相同。但是必須加上這個(gè)列的條件，MySQL才能夠匹配索引的最左前綴。這個(gè)“訣竅”在這類場景中非常有效，但如果列有太多不同的值，就會讓IN()列表太長，這樣做就不行了。

這個(gè)案例顯示了一個(gè)基本原則:考慮表上所有的選項(xiàng)。當(dāng)設(shè)計(jì)索引時(shí)，不要只為現(xiàn)有的查詢考慮需要哪些索引，還需要考慮對查詢進(jìn)行優(yōu)化。如果發(fā)現(xiàn)某些查詢需要創(chuàng)建新索引，但是這個(gè)索引又會降低另一些查詢的效率，那么應(yīng)該想一下是否能優(yōu)化原來的查詢。應(yīng)該同時(shí)優(yōu)化查詢和索引以找到最佳的平衡，而不是閉門造車去設(shè)計(jì)最完美的索引。

接下來，需要考慮其他常見WHERE條件的組合，并需要了解哪些組合在沒有合適索引的情況下會很慢。(sex,country, age)上的索引就是一個(gè)很明顯的選擇，另外很有可能還需要(sex, country, region, age)和( sex, country, region, city, age)這樣的組合索引。

這樣就會需要大量的索引。如果想盡可能重用索引而不是建立大量的組合索引，可以使用前面提到的IN()的技巧來避免同時(shí)需要(sex, country, age)和(sex, country,region, age)的索引。如果沒有指定這個(gè)字段搜索，就需要定義一個(gè)全部國家列表，或者國家的全部地區(qū)列表，來確保索引前綴有同樣的約束（組合所有國家、地區(qū)、性別將會是一個(gè)非常大的條件)。

這些索引將滿足大部分最常見的搜索查詢，但是如何為一些生僻的搜索條件（比如has_pictures、eye_color、hair_color和education)來設(shè)計(jì)索引呢﹖這些列的選擇性高、使用也不頻繁，可以選擇忽略它們，讓 MySQL多掃描一些額外的行即可。另一-個(gè)可選的方法是在age列的前面加上這些列，在查詢時(shí)使用前面提到過的IN()技術(shù)來處理搜索時(shí)沒有指定這些列的場景。

你可能已經(jīng)注意到了，我們一直將age列放在索引的最后面。age列有什么特殊的地方嗎?為什么要放在索引的最后﹖我們總是盡可能讓MySQL使用更多的索引列，因?yàn)椴樵冎荒苁褂盟饕淖钭笄熬Y，直到遇到第一個(gè)范圍條件列。前面提到的列在WHERE子句中都是等于條件,但是age列則多半是范圍查詢（例如查找年齡在18～25歲之間的人)。

當(dāng)然，也可以使用IN()來代替范圍查詢，例如年齡條件改寫為IN(18，19，20，21,22，23，24，25),但不是所有的范圍查詢都可以轉(zhuǎn)換。這里描述的基本原則是，盡可能將需要做范圍查詢的列放到索引的后面，以便優(yōu)化器能使用盡可能多的索引列。

前面提到可以在索引中加入更多的列，并通過IN(〉的方式覆蓋那些不在MERE子句中的列。但這種技巧也不能濫用，否則可能會帶來麻煩。因?yàn)槊款~外增加一個(gè)IN()條件，優(yōu)化器需要做的組合都將以指數(shù)形式增加，最終可能會極大地降低查詢性能?？紤]下面的WHERE子句:

前面提到可以在索引中加入更多的列，并通過IN(〉的方式覆蓋那些不在MHERE子句中的列。但這種技巧也不能濫用，否則可能會帶來麻煩。因?yàn)槊款~外增加一個(gè)IN()條件，優(yōu)化器需要做的組合都將以指數(shù)形式增加，最終可能會極大地降低查詢性能。考慮下面的WHERE子句:

優(yōu)化器則會轉(zhuǎn)化成4×3×2.= 24種組合，執(zhí)行計(jì)劃需要檢查WHERE子句中所有的24種組合。對于MySQL來說，24種組合并不是很夸張，但如果組合數(shù)達(dá)到上千個(gè)則需要特別小心。老版本的MySQL在IN()組合條件過多的時(shí)候會有很多問題。查詢優(yōu)化可能需要花很多時(shí)間，并消耗大量的內(nèi)存。新版本的MySQL在組合數(shù)超過一定數(shù)量后就不再進(jìn)行執(zhí)行計(jì)劃評估了，這可能會導(dǎo)致MySQL不能很好地利用索引。

組合。對于MySQL來說，24種組合并不是很夸張，但如果組合數(shù)達(dá)到上千個(gè)則需要特別小心。老版本的MySQL在IN()組合條件過多的時(shí)候會有很多問題。查詢優(yōu)化可能需要花很多時(shí)間，并消耗大量的內(nèi)存。新版本的MySQL在組合數(shù)超過一定數(shù)量后就不再進(jìn)行執(zhí)行計(jì)劃評估了，這可能會導(dǎo)致MySQL不能很好地利用索引。

5.4.2 避免多個(gè)范圍條件

假設(shè)我們有一個(gè)last_online列并希望通過下面的查詢顯示在過去幾周上線過的用戶:

WHEREeye_color IN( 'brown ' , 'blue' , ' hazel')

AND hair_color IN('black', ' red' , 'blonde' , ' brown ')AND sex

IN('M' , 'F')

AND last_online > DATE_sUB(NOw()，INTERVAL 7 DAY)AND age

BETWEEN 18 AND 25

這個(gè)查詢有一個(gè)問題:它有兩個(gè)范圍條件，last_online列和 age列，MySQL可以使用last_online列索引或者age列索引，但無法同時(shí)使用它們。

如果條件中只有l(wèi)ast_online而沒有age，那么我們可能考慮在索引的后面加上last_online列。這里考慮如果我們無法把a(bǔ)ge字段轉(zhuǎn)換為一個(gè)IN()的列表，并且仍要求對于同時(shí)有l(wèi)ast_online和 age這兩個(gè)維度的范圍查詢的速度很快，那該怎么辦﹖答案是，很遺憾沒有一個(gè)直接的辦法能夠解決這個(gè)問題。但是我們能夠?qū)⑵渲械囊粋€(gè)范圍查詢轉(zhuǎn)換為一個(gè)簡單的等值比較。為了實(shí)現(xiàn)這一點(diǎn)，我們需要事先計(jì)算好一個(gè)active列，這個(gè)字段由定時(shí)任務(wù)來維護(hù)。當(dāng)用戶每次登錄時(shí)，將對應(yīng)值設(shè)置為1，并且將過去連續(xù)七天未曾登錄的用戶的值設(shè)置為0。.

這個(gè)方法可以讓MySQL使用（active, sex, country, age)索引。active列并不是完全精確的，但是對于這類查詢來說，對精度的要求也沒有那么高。如果需要精確數(shù)據(jù)，可以把last_online列放到MHERE子句，但不加入到索引中。這和本章前面通過計(jì)算URL哈希值來實(shí)現(xiàn)URL的快速查找類似。所以這個(gè)查詢條件沒法使用任何索引，但因?yàn)檫@個(gè)條件的過濾性不高，即使在索引中加入該列也沒有太大的幫助。換個(gè)角度來說，缺乏合適的索引對該查詢的影響也不明顯。

到目前為止，我們可以看到:如果用戶希望同時(shí)看到活躍和不活躍的用戶，可以在查詢中使用IN()列表。我們已經(jīng)加入了很多這樣的列表，但另外一個(gè)可選的方案就只能是為不同的組合列創(chuàng)建單獨(dú)的索引。至少需要建立如下的索引 :(active,sex, country,age)，(active, country, age)，(sex, country, age)和 (country, age)。這些索引對某個(gè)具體的查詢來說可能都是更優(yōu)化的，但是考慮到索引的維護(hù)和額外的空間占用的代價(jià)，這個(gè)可選方案就不是一個(gè)好策略了。

在這個(gè)案例中，優(yōu)化器的特性是影響索引策略的一個(gè)很重要的因素。如果未來版本的MySQL能夠?qū)崿F(xiàn)松散索引掃描，就能在一個(gè)索引上使用多個(gè)范圍條件，那也就不需要為上面考慮的這類查詢使用IN()列表了。

5.4.3優(yōu)化排序

在這個(gè)學(xué)習(xí)案例中，最后要介紹的是排序。使用文件排序?qū)π?shù)據(jù)集是很快的，但如果一個(gè)查詢匹配的結(jié)果有上百萬行的話會怎樣?例如如果WHERE子句只有sex列，如何排序?

對于那些選擇性非常低的列，可以增加一些特殊的索引來做排序。例如，可以創(chuàng)建（sex,rating)索引用于下面的查詢:

mysql> SELECT <cols> FROM profiles MHERE sex='M'ORDER BY rating LIAIT 10;

這個(gè)查詢同時(shí)使用了ORDER BY和LIMIT，如果沒有索引的話會很慢。

即使有索引，如果用戶界面上需要翻頁，并且翻頁翻到比較靠后時(shí)查詢也可能非常慢。下面這個(gè)查詢就通過ORDER BY和 LIMIT偏移量的組合翻頁到很后面的時(shí)候:

mysq1> SELECT <cols> FROM profiles wHERE sex='N’ORDER BY rating LIMIT 100000，10;

無論如何創(chuàng)建索引，這種查詢都是個(gè)嚴(yán)重的問題。因?yàn)殡S著偏移量的增加，MySQL需要花費(fèi)大量的時(shí)間來掃描需要丟棄的數(shù)據(jù)。反范式化、預(yù)先計(jì)算和緩存可能是解決這類查詢的僅有策略。一個(gè)更好的辦法是限制用戶能夠翻頁的數(shù)量，實(shí)際上這對用戶體驗(yàn)的影響不大，因?yàn)橛脩艉苌贂嬲诤跛阉鹘Y(jié)果的第10 000頁。

優(yōu)化這類索引的另一個(gè)比較好的策略是使用延遲關(guān)聯(lián)，通過使用覆蓋索引查詢返回需要的主鍵，再根據(jù)這些主鍵關(guān)聯(lián)原表獲得需要的行。這可以減少M(fèi)ySQL掃描那些需要丟棄的行數(shù)。下面這個(gè)查詢顯示了如何高效地使用(sex, rating）索引進(jìn)行排序和分頁:

5.5維護(hù)索引和表

即使用正確的類型創(chuàng)建了表并加上了合適的索引，工作也沒有結(jié)束﹔還需要維護(hù)表和索引來確保它們都正常工作。維護(hù)表有三個(gè)主要的目的:找到并修復(fù)損壞的表，維護(hù)準(zhǔn)確的索引統(tǒng)計(jì)信息，減少碎片。

5.5.1找到并修復(fù)損壞的表

表損壞(corruption）是很糟糕的事情。對于MyISAM存儲引擎，表損壞通常是系統(tǒng)崩潰導(dǎo)致的。其他的引擎也會由于硬件問題、MySQL本身的缺陷或者操作系統(tǒng)的問題導(dǎo)致索引損壞。

損壞的索引會導(dǎo)致查詢返回錯(cuò)誤的結(jié)果或者莫須有的主鍵沖突等問題，嚴(yán)重時(shí)甚至還會導(dǎo)致數(shù)據(jù)庫的崩潰。如果你遇到了古怪的問題——例如一些不應(yīng)該發(fā)生的錯(cuò)誤——-可以嘗試運(yùn)行CHECK TABLE來檢查是否發(fā)生了表損壞（注意有些存儲引擎不支持該命令﹔而有些引擎則支持以不同的選項(xiàng)來控制完全檢查表的方式)。CHECK TABLE通常能夠找出大多數(shù)的表和索引的錯(cuò)誤。

可以使用 REPAIR TABLE命令來修復(fù)損壞的表，但同樣不是所有的存儲引擎都支持該命令。如果存儲引擎不支持，也可通過一個(gè)不做任何操作(no-op）的 ALTER操作來重建表，例如修改表的存儲引擎為當(dāng)前的引擎。下面是一個(gè)針對InnoDB表的例子:

mysql> ALTER TABLE innodb_tb1 ENGINE=INNODB;

此外，也可以使用一些存儲引擎相關(guān)的離線工具，例如myisamchky或者將數(shù)據(jù)導(dǎo)出一份，然后再重新導(dǎo)入。不過，如果損壞的是系統(tǒng)區(qū)域，或者是表的“行數(shù)據(jù)”區(qū)域，而不是索引，那么上面的辦法就沒有用了。在這種情況下，可以從備份中恢復(fù)表，或者嘗試從損壞的數(shù)據(jù)文件中盡可能地恢復(fù)數(shù)據(jù)。

如果InnoDB引擎的表出現(xiàn)了損壞，那么一定是發(fā)生了嚴(yán)重的錯(cuò)誤，需要立刻調(diào)查一下原因。InnoDB一般不會出現(xiàn)損壞。InnoDB的設(shè)計(jì)保證了它并不容易被損壞。如果發(fā)生損壞，一般要么是數(shù)據(jù)庫的硬件問題例如內(nèi)存或者磁盤問題（有可能)，要么是由于數(shù)據(jù)庫管理員的錯(cuò)誤例如在MySQL 外部操作了數(shù)據(jù)文件（有可能)，抑或是InnoDB本身的缺陷（不太可能)。常見的類似錯(cuò)誤通常是由于嘗試使用rsync備份InnoDB導(dǎo)致的。不存在什么查詢能夠讓InnoDB表損壞，也不用擔(dān)心暗處有“陷阱”。如果某條查詢導(dǎo)致InnoDB 數(shù)據(jù)的損壞，那一定是遇到了bug，而不是查詢的問題。

如果遇到數(shù)據(jù)損壞，最重要的是找出是什么導(dǎo)致了損壞，而不只是簡單地修復(fù)，否則很有可能還會不斷地?fù)p壞。可以通過設(shè)置innodb_force_recovery參數(shù)進(jìn)入InnoDB的強(qiáng)制恢復(fù)模式來修復(fù)數(shù)據(jù)，更多細(xì)節(jié)可以參考MySQL手冊。另外，還可以使用開源的InnoDB數(shù)據(jù)恢復(fù)工具箱(InnoDB Data Recovery Toolkit)直接從 InnoDB數(shù)據(jù)文件恢復(fù)出數(shù)據(jù)（下載地址:http://www.percona.com/software/mysql-innodb-data-recovery-tools/)。

5.5.2更新索引統(tǒng)計(jì)信息

MySQL的查詢優(yōu)化器會通過兩個(gè)API來了解存儲引擎的索引值的分布信息，以決定如何使用索引。第一個(gè)API是 records_in_range()，通過向存儲引擎?zhèn)魅雰蓚€(gè)邊界值獲取在這個(gè)范圍大概有多少條記錄。對于某些存儲引擎，該接口返回精確值，例如MyISAM﹔但對于另一些存儲引擎則是一個(gè)估算值，例如. InnoDB。

第二個(gè)API是info()，該接口返回各種類型的數(shù)據(jù)，包括索引的基數(shù)（每個(gè)鍵值有多少條記錄)。

如果存儲引擎向優(yōu)化器提供的掃描行數(shù)信息是不準(zhǔn)確的數(shù)據(jù)，或者執(zhí)行計(jì)劃本身太復(fù)雜以致無法準(zhǔn)確地獲取各個(gè)階段匹配的行數(shù)，那么優(yōu)化器會使用索引統(tǒng)計(jì)信息來估算掃描行數(shù)。MySQL優(yōu)化器使用的是基于成本的模型，而衡量成本的主要指標(biāo)就是一個(gè)查詢需要掃描多少行。如果表沒有統(tǒng)計(jì)信息，或者統(tǒng)計(jì)信息不準(zhǔn)確，優(yōu)化器就很有可能做出

錯(cuò)誤的決定?？梢酝ㄟ^運(yùn)行ANALYZE TABLE來重新生成統(tǒng)計(jì)信息解決這個(gè)問題。

每種存儲引擎實(shí)現(xiàn)索引統(tǒng)計(jì)信息的方式不同，所以需要進(jìn)行ANALYZE TABLE的頻率也因不同的引擎而不同，每次運(yùn)行的成本也不同:

·Memory 引擎根本不存儲索引統(tǒng)計(jì)信息。

·MyISAM將索引統(tǒng)計(jì)信息存儲在磁盤中，ANALYZE TABLE需要進(jìn)行一次全索引掃描來計(jì)算索引基數(shù)。在整個(gè)過程中需要鎖表。

·直到MySQL 5.5版本，InnoDB也不在磁盤存儲索引統(tǒng)計(jì)信息，而是通過隨機(jī)的索引訪問進(jìn)行評估并將其存儲在內(nèi)存中。

可以使用SHOw INDEX FROM命令來查看索引的基數(shù)（Cardinality)。例如:

這個(gè)命令輸出了很多關(guān)于索引的信息，在 MysQL手冊中對上面每個(gè)字段的含義都有詳細(xì)的解釋。這里需要特別提及的是索引列的基數(shù)(Cardinality)，其顯示了存儲引擎估算索引列有多少個(gè)不同的取值。在MySQL 5.0和更新的版本中，還可以通過INFORMATION_SCHEMA.STATISTICS表很方便地查詢到這些信息。例如基于工NFORMATION_SCHEMA的表，可以編寫一個(gè)查詢給出當(dāng)前選擇性比較低的索引。需要注意的是，如果服務(wù)器上的庫表非常多，則從這里獲取元數(shù)據(jù)的速度可能會非常慢，而且會給MySQL帶來額外的壓力。

InnoDB的統(tǒng)計(jì)信息值得深入研究。InnoDB引擎通過抽樣的方式來計(jì)算統(tǒng)計(jì)信息，首先隨機(jī)地讀取少量的索引頁面，然后以此為樣本計(jì)算索引的統(tǒng)計(jì)信息。在老的

InnoDB版本中，樣本頁面數(shù)是8，新版本的InnoDB可以通過參數(shù)innodb_stats_sample_pages來設(shè)置樣本頁的數(shù)量。設(shè)置更大的值，理論上來說可以幫助生成更準(zhǔn)確的索引信息，特別是對于某些超大的數(shù)據(jù)表來說，但具體設(shè)置多大合適依賴于具體的環(huán)境。

InnoDB會在表首次打開，或者執(zhí)行ANALYZETABLE，抑或表的大小發(fā)生非常大的變化(大小變化超過十六分之一或者新插入了20億行都會觸發(fā)）的時(shí)候計(jì)算索引的統(tǒng)計(jì)信息。

InnoDB在打開某些INFORMATION_SCHEMA表，或者使用SHO TABLE STATUS和 SHOWINDEX，抑或在MySQL客戶端開啟自動補(bǔ)全功能的時(shí)候都會觸發(fā)索引統(tǒng)計(jì)信息的更新。如果服務(wù)器上有大量的數(shù)據(jù)，這可能就是個(gè)很嚴(yán)重的問題，尤其是當(dāng)I/O比較慢的時(shí)候?？蛻舳嘶蛘弑O(jiān)控程序觸發(fā)索引信息采樣更新時(shí)可能會導(dǎo)致大量的鎖，并給服務(wù)器帶來很多的額外壓力，這會讓用戶因?yàn)閱訒r(shí)間漫長而沮喪。只要SHOW INDEX查看索引統(tǒng)計(jì)信息，就一定會觸發(fā)統(tǒng)計(jì)信息的更新?？梢躁P(guān)閉innodb_stats_on_metadata參數(shù)來避免上面提到的問題。

如果使用Percona版本，使用的就是XtraDB引擎而不是原生的InnoDB引擎，那么可以通過innodb_stats_auto_update參數(shù)來禁止通過自動采樣的方式更新索引統(tǒng)計(jì)信息，這時(shí)需要手動執(zhí)行ANALYZE TABLE命令來更新統(tǒng)計(jì)信息。如果某些查詢執(zhí)行計(jì)劃很不穩(wěn)定的話，可以用該辦法固化查詢計(jì)劃。我們當(dāng)初引入這個(gè)參數(shù)也正是為了解決一些客戶的這種問題。

如果想要更穩(wěn)定的執(zhí)行計(jì)劃，并在系統(tǒng)重啟后更快地生成這些統(tǒng)計(jì)信息，那么可以使用系統(tǒng)表來持久化這些索引統(tǒng)計(jì)信息。甚至還可以在不同的機(jī)器間遷移索引統(tǒng)計(jì)信息，這樣新環(huán)境啟動時(shí)就無須再收集這些數(shù)據(jù)。在Percona 5.1版本和官方的5.6版本都已經(jīng)加入這個(gè)特性。在Percona版本中通過innodb_use_sys_stats_table參數(shù)可以啟用該特性，官方5.6版本則通過innodb_analyze_is_persistent參數(shù)控制。

一旦關(guān)閉索引統(tǒng)計(jì)信息的自動更新，那么就需要周期性地使用ANALYZE TABLE來手動更新。否則，索引統(tǒng)計(jì)信息就會永遠(yuǎn)不變。如果數(shù)據(jù)分布發(fā)生大的變化，可能會出現(xiàn)一些很糟糕的執(zhí)行計(jì)劃。

5.5.3 減少索引和數(shù)據(jù)的碎片

B-Tree索引可能會碎片化，這會降低查詢的效率。碎片化的索引可能會以很差或者無序的方式存儲在磁盤上。

根據(jù)設(shè)計(jì)，B-Tree需要隨機(jī)磁盤訪問才能定位到葉子頁，所以隨機(jī)訪問是不可避免的。然而，如果葉子頁在物理分布上是順序且緊密的，那么查詢的性能就會更好。否則，對于范圍查詢、索引覆蓋掃描等操作來說，速度可能會降低很多倍﹔對于索引覆蓋掃描這一點(diǎn)更加明顯。

表的數(shù)據(jù)存儲也可能碎片化。然而，數(shù)據(jù)存儲的碎片化比索引更加復(fù)雜。有三種類型的數(shù)據(jù)碎片。

行碎片(Row fragmentation)

這種碎片指的是數(shù)據(jù)行被存儲為多個(gè)地方的多個(gè)片段中。即使查詢只從索引中訪問一行記錄，行碎片也會導(dǎo)致性能下降。

行間碎片(Intra-row fragmentation)

行間碎片是指邏輯上順序的頁，或者行在磁盤上不是順序存儲的。行間碎片對諸如全表掃描和聚簇索引掃描之類的操作有很大的影響，因?yàn)檫@些操作原本能夠從磁盤上順序存儲的數(shù)據(jù)中獲益。

剩余空間碎片(Free space fragmentation)

剩余空間碎片是指數(shù)據(jù)頁中有大量的空余空間。這會導(dǎo)致服務(wù)器讀取大量不需要的數(shù)據(jù)，從而造成浪費(fèi)。

對于MyISAM 表，這三類碎片化都可能發(fā)生。但I(xiàn)nnoDB不會出現(xiàn)短小的行碎片﹔InnoDB會移動短小的行并重寫到一個(gè)片段中。

可以通過執(zhí)行OPTIMIZE TABLE或者導(dǎo)出再導(dǎo)入的方式來重新整理數(shù)據(jù)。這對多數(shù)存儲引擎都是有效的。對于一些存儲引擎如MyISAM，可以通過排序算法重建索引的方式來消除碎片。老版本的InnoDB沒有什么消除碎片化的方法。不過最新版本InnoDB新增了“在線”添加和刪除索引的功能，可以通過先刪除，然后再重新創(chuàng)建索引的方式來消除索引的碎片化。

對于那些不支持OPTIMIZETABLE的存儲引擎，可以通過一個(gè)不做任何操作(no-op)的ALTER TABLE操作來重建表。只需要將表的存儲引擎修改為當(dāng)前的引擎即可:

mysql> ALTER TABLE <table> ENGINE=<engine>;

對于開啟了expand_fast_index_creation參數(shù)的Percona Server，按這種方式重建表，則會同時(shí)消除表和索引的碎片化。但對于標(biāo)準(zhǔn)版本的MySQL則只會消除表（實(shí)際上是聚簇索引）的碎片化。可用先刪除所有索引，然后重建表，最后重新創(chuàng)建索引的方式模擬Percona Server的這個(gè)功能。

應(yīng)該通過一些實(shí)際測量而不是隨意假設(shè)來確定是否需要消除索引和表的碎片化。Percona的XtraBackup有個(gè)--stats參數(shù)以非備份的方式運(yùn)行，而只是打印索引和表的統(tǒng)計(jì)情況，包括頁中的數(shù)據(jù)量和空余空間。這可以用來確定數(shù)據(jù)的碎片化程度。另外也要考慮數(shù)據(jù)是否已經(jīng)達(dá)到穩(wěn)定狀態(tài)，如果你進(jìn)行碎片整理將數(shù)據(jù)壓縮到一起，可能反而會導(dǎo)致后續(xù)的更新操作觸發(fā)一系列的頁分裂和重組，這會對性能造成不良的影響（直到數(shù)據(jù)再次達(dá)到新的穩(wěn)定狀態(tài))。

5.6總結(jié)

通過本章可以看到，索引是一個(gè)非常復(fù)雜的話題!MySQL和存儲引擎訪問數(shù)據(jù)的方式，加上索引的特性，使得索引成為一個(gè)影響數(shù)據(jù)訪問的有力而靈活的工作（無論數(shù)據(jù)是在磁盤中還是在內(nèi)存中)。

在MySQL中，大多數(shù)情況下都會使用B-Tree索引。其他類型的索引大多只適用于特殊的目的。如果在合適的場景中使用索引，將大大提高查詢的響應(yīng)時(shí)間。本章將不再介紹更多這方面的內(nèi)容了，最后值得總的回顧一下這些特性以及如何使用B-Tree索引。

?

在選擇索引和編寫利用這些索引的查詢時(shí)，有如下三個(gè)原則始終需要記住:

1．單行訪問是很慢的。特別是在機(jī)械硬盤存儲中(SSD的隨機(jī)I/О要快很多，不過這一點(diǎn)仍然成立)。如果服務(wù)器從存儲中讀取一個(gè)數(shù)據(jù)塊只是為了獲取其中一行，那么就浪費(fèi)了很多工作。最好讀取的塊中能包含盡可能多所需要的行。使用索引可以創(chuàng)建位置引用以提升效率。

2．按順序訪問范圍數(shù)據(jù)是很快的,這有兩個(gè)原因。第一,順序I/O不需要多次磁盤尋道，所以比隨機(jī)I/О要快很多（特別是對機(jī)械硬盤)。第二，如果服務(wù)器能夠按需要順序讀取數(shù)據(jù)，那么就不再需要額外的排序操作，并且 GROUP BY查詢也無須再做排序和將行按組進(jìn)行聚合計(jì)算了。

3．索引覆蓋查詢是很快的。如果一個(gè)索引包含了查詢需要的所有列，那么存儲引擎就不需要再回表查找行。這避免了大量的單行訪問，而上面的第1點(diǎn)已經(jīng)寫明單行訪問是很慢的。

總的來說，編寫查詢語句時(shí)應(yīng)該盡可能選擇合適的索引以避免單行查找、盡可能地使用數(shù)據(jù)原生順序從而避免額外的排序操作，并盡可能使用索引覆蓋查詢。這與本章開頭提到的Lahdenmaki和Leach 的書中的“三星”評價(jià)系統(tǒng)是一致的。

如果表上的每一個(gè)查詢都能有一個(gè)完美的索引來滿足當(dāng)然是最好的。但不幸的是，要這么做有時(shí)可能需要創(chuàng)建大量的索引。還有一些時(shí)候?qū)δ承┎樵兪遣豢赡軇?chuàng)建一個(gè)達(dá)到“三星”的索引的（例如查詢要按照兩個(gè)列排序，其中一個(gè)列正序，另一個(gè)列倒序)。這時(shí)必須有所取舍以創(chuàng)建最合適的索引，或者尋求替代策略（例如反范式化，或者提前計(jì)算匯總表等)。

理解索引是如何工作的非常重要，應(yīng)該根據(jù)這些理解來創(chuàng)建最合適的索引，而不是根據(jù)一些諸如“在多列索引中將選擇性最高的列放在第一列”或“應(yīng)該為WHERE子句中出現(xiàn)的所有列創(chuàng)建索引”之類的經(jīng)驗(yàn)法則及其推論。

那如何判斷一個(gè)系統(tǒng)創(chuàng)建的索引是合理的呢?一般來說，我們建議按響應(yīng)時(shí)間來對查詢進(jìn)行分析。找出那些消耗最長時(shí)間的查詢或者那些給服務(wù)器帶來最大壓力的查詢（第3章中介紹了如何測量)，然后檢查這些查詢的schema、SQL和索引結(jié)構(gòu)，判斷是否有查詢掃描了太多的行，是否做了很多額外的排序或者使用了臨時(shí)表，是否使用隨機(jī)I/O訪問數(shù)據(jù)，或者是有太多回表查詢那些不在索引中的列的操作。

如果一個(gè)查詢無法從所有可能的索引中獲益，則應(yīng)該看看是否可以創(chuàng)建一個(gè)更合適的索引來提升性能。如果不行，也可以看看是否可以重寫該查詢，將其轉(zhuǎn)化成一個(gè)能夠高效利用現(xiàn)有索引或者新創(chuàng)建索引的查詢。這也是下一章要介紹的內(nèi)容。

如果根據(jù)第3章介紹的基于響應(yīng)時(shí)間的分析不能找出有問題的查詢呢?是否可能有我們沒有注意到的“很糟糕”的查詢，需要一個(gè)更好的索引來獲取更高的性能?一般來說，不可能。對于診斷時(shí)抓不到的查詢，那就不是問題。但是，這個(gè)查詢未來有可能會成為問題,因?yàn)閼?yīng)用程序,數(shù)據(jù)和負(fù)載都在變化。如果仍然想找到那些索引不是很合適的查詢，并在它們成為問題前進(jìn)行優(yōu)化，則可以使用pt-query-digest的查詢審查“review”功能，分析其EXPLAIN出來的執(zhí)行計(jì)劃。

分析其EXPLAIN出來的執(zhí)行計(jì)劃。