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

索引(在MySQL中也叫做“鍵(key)”) 是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。

索引對于良好的性能非常關(guān)鍵。尤其是當(dāng)表中的數(shù)據(jù)量越來越大時，索引對性能的影響愈發(fā)重要。

在數(shù)據(jù)量較小且負(fù)載較低時，不恰當(dāng)?shù)乃饕龑π阅艿挠绊懣赡苓€不明顯，但當(dāng)數(shù)據(jù)量逐漸增大時，性能則會急劇下降。

索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化最有效的手段了。索引能夠輕易將查詢性能提高幾個數(shù)量級，“最優(yōu)”的索引有時比一個“好的”索引性能要好兩個數(shù)量級。

創(chuàng)建一個真正“最優(yōu)”的索引經(jīng)常需要重寫查詢。

5.1 索引基礎(chǔ)

要理解MySQL中索引是如何工作的,最簡單的方法就是去看看一本書的“索引”部分:

如果想在一本書中找到某個特定主題，一 般會先看書的‘索引”， 找到對應(yīng)的頁碼。

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

假如要運行下面的查詢:

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

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

索引可以包含一個或多個列的值。如果索引包含多個列，那么列的順序也十分重要，因為MySQL只能高效地使用索引的最左前綴列。

最左原則

創(chuàng)建一個包含兩個列的索引，和創(chuàng)建兩個只包含一列的索引是大不相同的，下面將詳細(xì)介紹。

如果使用的是ORM，是否還需要關(guān)心索引?
是的，仍然需要理解索引，即使是使用對象關(guān)系映射(ORM)工具。

ORM工具能夠生產(chǎn)符合邏輯的、合法的查詢(多數(shù)時候)，除非只是生成非?；镜牟樵?例如僅是根據(jù)主鍵查詢)，否則它很難生成適合索引的查詢。

無論是多么復(fù)雜的ORM工具，在精妙和復(fù)雜的索引面前都是“浮云”。

5.1.1 索引的類型

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

B-Tree索引

當(dāng)人們談?wù)撍饕臅r候，如果沒有特別指明類型，那多半說的是B-Tree索引，它使用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲數(shù)據(jù)。

大多數(shù)MySQL引擎都支持這種索引。

我們使用術(shù)語“B-Tree”,是因為MySQL在CREATE TABLE和其他語句中也使用該關(guān)鍵字。

不過，底層的存儲引擎也可能使用不同的存儲結(jié)構(gòu)，例如，NDB集群存儲引擎內(nèi)部實際上使用了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通常意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同。

圖5-1展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。MyISAM使用的結(jié)構(gòu)有所不同，但基本思想是類似的。

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

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

B-Tree對索引列是順序組織存儲的，所以很適合查找范圍數(shù)據(jù)。

例如，在一個基于文本域的索引樹上，按字母順序傳遞連續(xù)的值進(jìn)行查找是非常合適的，所以像“找出所有以I到K開頭的名字”這樣的查找效率會非常高。

假設(shè)有如下數(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, first_name, dob)
);

對于表中的每一行數(shù)據(jù)，索引中包含了last_name、 first_name 和dob列的值，圖5-2顯示了該索引是如何組織數(shù)據(jù)的存儲的。

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

可以使用B-Tree索引的查詢類型。B-Tree 索引適用于全鍵值、鍵值范圍或鍵前綴查找。

其中鍵前綴查找只適用于根據(jù)最左前綴的查找生。前面所述的索引對如下類型的查詢有效。

1. 全值匹配

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

2. 匹配最左前綴

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

3. 匹配列前綴

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

4. 匹配范圍值

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

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

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

6. 只訪問索引的查詢

B-Tree通?？梢灾С帧爸辉L問索引的查詢”，即查詢只需要訪問索引，而無須訪問數(shù)據(jù)行。也叫做 覆蓋索引

這種訪問不需要回表，因此特別快

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

1. 如果不是按照索引的最左列開始查找，則無法使用索引。

例如上面例子中的索引無法用于查找名字為Bill的人，也無法查找某個特定生日的人，因為這兩列都不是最左數(shù)據(jù)列。類似地，也無法查找姓氏以某個字母結(jié)尾的人。

2. 不能跳過索引中的列。

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

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

例如有查詢

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

這個查詢只能使用索引的前兩列，因為這里L(fēng)IKE是一個范圍條件(但是服務(wù)器可以把其余列用于其他目的)。如果范圍查詢列值的數(shù)量有限，那么可以通過使用多個等于條件來代替范圍條件。

索引下推(Index Condition Pushdown，簡稱ICP)，是MySQL5.6版本的新特性，它能減少回表查詢次數(shù)，提高查詢效率。

用于聯(lián)合索引,在有列需要范圍索引時，讓其后的列依然可以使用索引。也就是不會有這個問題了。

Mysql：好好的索引，為什么要下推？

索引列的順序十分重要:這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求。

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

哈希索引

哈希索引(hashindex)基于哈希表實現(xiàn)，只有精確匹配索引所有列的查詢才有效。

對于每一行數(shù)據(jù)，存儲引擎都會對所有的索引列計算-一個哈希碼(hash code),哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣。哈希索引將所有的哈希碼存儲在索引中，同時在哈希表中保存指向每個數(shù)據(jù)行的指針。

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

下面來看一個例子。假設(shè)有如下表：

CREATE TABLE testhash (
	fname VARCHAR(50) NOT NULL,
	lname VARCHAR(50) NOT NULL,
	KEY USING HASH(fname)
) ENGINE=MEMORY;

表中有如下數(shù)據(jù)

假設(shè)索引使用假想的哈希函數(shù)f()，它返回下面的值(都是示例數(shù)據(jù)，非真實數(shù)據(jù)) :

索引結(jié)構(gòu)如下：

注意每個槽的編號是順序的，但是數(shù)據(jù)行不是?，F(xiàn)在，來看如下查詢:

mysql> SELECT lname FROM testhash WHERE fname='Peter';

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

因為索引自身只需存儲對應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，這也讓哈希索引查找的速度非?？?。

然而，哈希索引也有它的限制:

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

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

• 哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的 全部內(nèi)容 來計算哈希值的。例如,在數(shù)據(jù)列(A,B) 上建立哈希索引,如果查詢只有數(shù)據(jù)列A,則無法使用該索引。

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

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

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

什么是Hash沖突？如何解決Hash沖突?

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

星型模型與雪花模型（Star Schema and Snowflake Schema）

InnoDB引擎有一個特殊的功能叫做 “自適應(yīng)哈希索引(adaptive hash index)” 。

當(dāng)InnoDB注意到某些索引值被使用得非常頻繁時，它會在內(nèi)存中基于B-Tree索引之上再創(chuàng)建一個哈希索引，這樣就讓B-Tree索引也具有哈希索引的一些優(yōu)點，比如快速的哈希查找。

這是一個完全自動的、內(nèi)部的行為，用戶無法控制或者配置，不過如果有必要，完全可以關(guān)閉該功能。

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

思路很簡單:

• 在B-Tree基礎(chǔ)上創(chuàng)建一個偽哈希索引。

這和真正的哈希索引不是一回事，因為還是使用B-Tree進(jìn)行查找，但是它使用哈希值而不是鍵本身進(jìn)行索引查找。你需要做的就是在查詢的WHERE子句中手動指定使用哈希函數(shù)。

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

mysql> SELECT id FROM url WHERE url="http://ww.mysql.com";

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

mysql> SELECT id FROM url WHERE url="http://www.mysql.com"
->AND url_ crc=CRC32("http://www.mysq1.com");

這樣做的性能會非常高，因為MySQL優(yōu)化器會使用這個選擇性很高而體積很小的基于url_ crc列的索引來完成查找(在上面的案例中，索引值為1560514994)。即使有多個記錄有相同的索引值，查找仍然很快，只需要根據(jù)哈希值做快速的整數(shù)比較就能找到索引條目，然后一一比較返回對應(yīng)的行。

另外一種方式就是對完整的URL字符串做索引, 那樣會非常慢。
這樣實現(xiàn)的缺陷是需要維護(hù)哈希值?？梢允謩泳S護(hù)，也可以使用觸發(fā)器實現(xiàn)。下面的案例演示了觸發(fā)器如何在插入和更新時維護(hù)url_crc列。首先創(chuàng)建如下表:

CREATE TABLE pseudohash (
id int unsigned NOT NULL auto_ increment,
url varchar(255) NOT NULL,
url_ _CrC int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY(id)

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

DELIMITER 

CREATE TRIGGER pseudohash crc_ins BEFORE 
INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crC=crC32(NEW.ur1);
END;

CREATE TRIGGER pseudohash CrC_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url _crC=crC32(NEW.ur1);
END;

DELIMITER ;

如果采用這種方式，記住不要使用SHA1()和MD5()作為哈希函數(shù)。因為這兩個函數(shù)計算出來的哈希值是非常長的字符串，會浪費大量空間，比較時也會更慢。

SHA1() 和MD5()是強(qiáng)加密函數(shù)，設(shè)計目標(biāo)是最大限度消除沖突，但這里并不需要這樣高的要求。簡單哈希函數(shù)的沖突在一個可以接受的范圍，同時又能夠提供更好的性能。

如果數(shù)據(jù)表非常大，CRC32()會出現(xiàn)大量的哈希沖突，則可以考慮自己實現(xiàn)一個簡單的64位哈希函數(shù)。這個自定義函數(shù)要返回整數(shù),而不是字符串。一個簡單的辦法可以使用MD5()函數(shù)返回值的一部分來作為自定義哈希函數(shù)。

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

MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲。和B-Tree索引不同，這類索引無須前綴查詢?？臻g索引會從所有維度來索引數(shù)據(jù)。

查詢時，可以有效地使用任意維度來組合查詢。必須使用MySQL的GIS相關(guān)函數(shù)如MBRCONTAINS()等來維護(hù)數(shù)據(jù)。MySQL的GIS支持并不完善，所以大部分人都不會使用這個特性。開源關(guān)系數(shù)據(jù)庫系統(tǒng)中對GIS的解決方案做得比較好的是PostgreSQL的PostGIS

全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。

全文搜索和其他幾類索引的匹配方式完全不一樣。它有許多需要注意的細(xì)節(jié)，如停用詞、詞干和復(fù)數(shù)、布爾搜索等。

全文索引更類似于搜索引擎做的事情，而不是簡單的WHERE條件匹配。

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

其他索引類別

還有很多第三方的存儲引擎使用不同類型的數(shù)據(jù)結(jié)構(gòu)來存儲索引。

例如TokuDB使用 分形樹索引(fractal tree index) ,這是一類較新開發(fā)的數(shù)據(jù)結(jié)構(gòu)，既有B-Tree的很多優(yōu)點，也避免了B-Tree的一些缺點。多數(shù)情況下，針對InnoDB的討論也都適用于TokuDB。

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

還有Elastic Search 使用的倒排索引

5.2 索引的優(yōu)點

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

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

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

Lahdenmaki和Leach介紹了如何評價一個索引是否適合某個查詢的 “三星系統(tǒng)”(three-star system) :

1. 索引將相關(guān)的記錄放到一起則獲得一星;
2. 如果索引中的數(shù)據(jù)順序和查找中的排列順序一致則獲得二星;
3. 如果索引中的列包含了查詢中需要的全部列則獲得“三星”。

索引是最好的解決方案嗎?

索引并不總是最好的工具。

總的來說，只有當(dāng)索引幫助存儲引擎快速查找到記錄帶來的好處大于其帶來的額外工作時，索引才是有效的。

• 對于非常小的表，大部分情況下簡單的全表掃描更高效。

• 對于中到大型的表，索引就非常有效。

• 但對于特大型的表，建立和使用索引的代價將隨之增長。這種情況下，則需要一種技術(shù)可以直接區(qū)分出查詢需要的一組數(shù)據(jù)，而不是一條記錄一條記錄地匹配。例如可以使用分區(qū)技術(shù)。

• 如果表的數(shù)量特別多，可以建立一個元數(shù)據(jù)信息表，用來查詢需要用到的某些特性。例如執(zhí)行那些需要聚合多個應(yīng)用分布在多個表的數(shù)據(jù)的查詢，則需要記錄。哪個用戶的信息存儲在哪個表中”的元數(shù)據(jù)，這樣在查詢時就可以直接忽略那些不包含指定用戶信息的表。對于大型系統(tǒng)，這是一個常用的技巧。

5.3 高性能的索引策略

正確地創(chuàng)建和使用索引是實現(xiàn)高性能查詢的基礎(chǔ)。

高效地選擇和使用索引有很多種方式，其中有些是針對特殊案例的優(yōu)化方法，有些則是針對特定行為的優(yōu)化。

5.3.1 獨立的列

我們通常會看到一些查詢不當(dāng)?shù)厥褂盟饕?，或者使得MySQL無法使用已有的索引。

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

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

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

憑肉眼很容易看出WHERE中的表達(dá)式其實等價于actor_id=4，但是MySQL無法自動解析這個方程式。

這完全是用戶行為。我們應(yīng)該養(yǎng)成簡化WHERE條件的習(xí)慣，始終將索引列單獨放在比較符號的一側(cè) 。

下面是另一個常見的錯誤:

mysql> SELECT ... WHERE TO_DAYS(CURRENT_ DATE) - TO_ DAYS(date_col) <= 10;

5.3.2 前綴索引和索引選擇性

有時候需要索引很長的字符列，這會讓索引變得大且慢。

一個策略是前面提到過的模擬哈希索引。通常可以索引開始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。

但這樣也會降低索引的選擇性。索引的選擇性是指，不重復(fù)的索引值(也稱為基數(shù)，cardinality)和數(shù)據(jù)表的記錄總數(shù)(#T) 的比值，范圍從1/#T到1之間。

索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓MySQL在查找時過濾掉更多的行。

唯一索引的選擇性,這是最好的索引選擇性，性能也是最好的。

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

訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長(以便節(jié)約空間)。

前綴應(yīng)該足夠長，以使得前綴索引的選擇性接近于索引整個列。換句話說，前綴的“基數(shù)”應(yīng)該接近于完整列的“基數(shù)”。

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

CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);

INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;

-- Repeat the next statement five times:

INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;

-- Now randomize the distribution (inefficiently but conveniently):

UPDATE sakila.city_demo
SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);

現(xiàn)在我們有了示例數(shù)據(jù)集。

首先，我們找到最常見的城市列表:

mysql> SELECT COUNT(*) AS cnt,city
-> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

mysql> SELECT COUNT(*) AS cnt,city
-> FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10; 

注意到，上面每個值都出現(xiàn)了45 ~ 65次?，F(xiàn)在查找到最頻繁出現(xiàn)的城市前綴，先從3個前綴字母開始:

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref
-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

每個前綴都比原來的城市出現(xiàn)的次數(shù)更多，因此唯一前綴比唯一城市要少得多。

然后我們增加前綴長度，直到這個前綴的選擇性接近完整列的選擇性。經(jīng)過實驗后發(fā)現(xiàn)前綴長度為7時比較合適:

mysql> SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref
-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

計算合適的前綴長度的另外一個辦法就是計算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性。

下面顯示如何計算完整列的選擇性:

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;

通常來說(盡管也有例外情況)，這個例子中如果前綴的選擇性能夠接近0.031，基本上就可用了?？梢栽谝粋€查詢中針對不同前綴長度進(jìn)行計算，這對于大表非常有用。

下面給出了如何在同一個查詢中計算不同前綴長度的選擇性:

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
->COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
->COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
->COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS se16,
->COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7,
-> FROM sakila.city demo;

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

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

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

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

一個常見的場景是針對很長的十六進(jìn)制唯一ID使用前綴索引。

在前面的章節(jié)中已經(jīng)討論了很多有效的技術(shù)來存儲這類ID信息，但如果使用的是打包過的解決方案，因而無法修改存儲結(jié)構(gòu)，那該怎么辦?

• 例如使用vBulletin或者其他基于MySQL的應(yīng)用在存儲網(wǎng)站的會話(SESSION)時，需要在一個很長的十六進(jìn)制字符串上創(chuàng)建索引。
• 此時如果采用長度為8的前綴索引通常能顯著地提升性能，并且這種方法對上層應(yīng)用完全透明。

有時候后綴索引(suffix index)也有用途(例如，找到某個域名的所有電子郵件地址)。MySQL原生并不支持反向索引，但是可以把字符串反轉(zhuǎn)后存儲，并基于此建立前綴索引?？梢酝ㄟ^觸發(fā)器來維護(hù)這種索引。

5.3.3 多列索引

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

為每個列創(chuàng)建獨立的索引，從SHOW CREATE TABLE中很容易看到這種情況:

CREATE TABLE t (
	C1 INT,
	C2 INT,
	C3 INT,
	KEY(c1),
	KEY(c2),
	KEY(c3)
);

這種索引策略，一般是由于人們聽到一些專家諸如“把WHERE條件里面的列都建上索引”,這樣模糊的建議導(dǎo)致的。

實際上這個建議是非常錯誤的。這樣一來最好的情況下也只能是“一星”索引，其性能比起真正最優(yōu)的索引可能差幾個數(shù)量級。

有時如果無法設(shè)計一個“三星”索引，那么不如忽略掉WHERE子句，集中精力優(yōu)化索引列的順序，或者創(chuàng)建一個全覆蓋索引。

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

• MySQL5.0和更新版本引入了一種叫 “索引合并”(index merge) 的策略，一定程度上可以使用表上的多個單列索引來定位指定的行。

• 更早版本的MySQL只能使用其中某一個單列索引，然而這種情況下沒有哪一個獨立的單列索引是非常有效的。

例如，表film_actor在字段film_id和actor_id. 上各有一個單列索引。但對于下面這個查詢WHERE條件，這兩個單列索引都不是好的選擇:

mysql> SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 10 or film_id = 1;

在老的MySQL版本中, MySQL對這個查詢會使用全表掃描。除非改寫成如下的兩個查詢UNION的方式:

mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
-> UNION ALL
-> SELECT film_id, actor_id FROM sakila.fi1m_actor WHERE film_id = 1
->AND actor_id < 1;

但在MySQL5.0和更新的版本中，查詢能夠同時使用這兩個單列索引進(jìn)行掃描，并將結(jié)果進(jìn)行合并。

這種算法有三個變種:

• OR條件的聯(lián)合(union)
• AND條件的相交(intersection)
• 組合前兩種情況的聯(lián)合及相交

下面的查詢就是使用了兩個索引掃描的聯(lián)合，通過EXPLAIN中的Extra列可以看到這點:

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

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

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

這樣做不但會消耗更多的CPU和內(nèi)存資源，還可能會影響查詢的并發(fā)性，但如果是單獨運行這樣的查詢則往往會忽略對并發(fā)性的影響。通常來說，還不如像在MySQL4.1或者更早的時代一樣，將查詢改寫成UNION的方式往往更好。

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

5.3.4 選擇合適的索引列順序

我們遇到的最容易引起困惑的問題就是索引列的順序。

正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要

書中的內(nèi)容適用于B-Tree索引
哈?；蛘咂渌愋偷乃饕⒉粫馚-Tree索引一樣按順序存儲數(shù)據(jù)

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

在Lahdenmaki和Leach的“三星索引”系統(tǒng)中，列順序也決定了一個索引是否能夠成為一個真正的“三星索引”。

對于如何選擇索引的列順序有一個經(jīng)驗法則:

• 將選擇性最高的列放到索引最前列。

雖然該法則有用，但通常不如 避免隨機(jī)IO和排序 那么重要，考慮問題需要更全面

場景不同則選擇不同，沒有一個放之四海皆準(zhǔn)的法則。這里只是說明，這個經(jīng)驗法則可能沒有你想象的重要。

當(dāng)不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。

這時候索引的作用只是用于優(yōu)化WHERE條件的查找。在這種情況下，這樣設(shè)計的索引確實能夠最快地過濾出需要的行，對于在WHERE子句中只使用了索引部分前綴列的查詢來說選擇性也更高。

然而，性能不只是依賴于所有索引列的選擇性(整體基數(shù))，也和查詢條件的具體值有關(guān)，也就是和值的分布有關(guān)。這和前面介紹的選擇前綴的長度需要考慮的地方一樣。

可能需要根據(jù)那些運行頻率最高的查詢來調(diào)整索引列的順序，讓這種情況下索引的選擇性最高。以下面的查詢?yōu)槔?

SELECT * FROM payment WHERE staff.id = 2 AND customer._id = 584; 

是應(yīng)該創(chuàng)建一個(staff_id, customer_ id) 索引還是應(yīng)該顛倒一下順序?

可以跑一些查詢來確定在這個表中值的分布情況，并確定哪個列的選擇性更高。

先用下面的查詢預(yù)測一下，看看各個WHERE條件的分支對應(yīng)的數(shù)據(jù)基數(shù)有多大:

MySQl中的\g和\G

根據(jù)前面的經(jīng)驗法則，應(yīng)該將索引列customer_id放到前面，因為對應(yīng)條件值的customer_ id數(shù)量更小。

我們再來看看對于這個customer_ id 的條件值，對應(yīng)的staff_id列的選擇性如何:

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

如果是從諸如pt-query-digest這樣的工具的報告中提取“最差”查詢，那么再按上述辦法選定的索引順序往往是非常高效的。

如果沒有類似的具體查詢來運行，那么最好還是按經(jīng)驗法則來做，因為經(jīng)驗法則考慮的是全局基數(shù)和選擇性，而不是某個具體查詢:

customer_id的選擇性更高，所以答案是將其作為索引列的第一列:

當(dāng)使用前綴索引的時候，在某些條件值的基數(shù)比正常值高的時候，問題就來了。

mysql> ALTER TABLE payment ADD KEY(customer_id, staff_id);

例如，在某些應(yīng)用程序中，對于沒有登錄的用戶，都將其用戶名記錄為“guset”， 在記錄用戶行為的 會話(session) 表和其他記錄用戶活動的表中“guest" 就成為了一個特殊用戶ID。一旦查詢涉及這個用戶，那么和對于正常用戶的查詢就大不同了，因為通常有很多會話都是沒有登錄的。

系統(tǒng)賬號也會導(dǎo)致類似的問題。一個應(yīng)用通常都有一個特殊的管理員賬號，和普通賬號不同，它并不是一個具體的用戶，系統(tǒng)中所有的其他用戶都是這個用戶的好友，所以系統(tǒng)往往通過它向網(wǎng)站的所有用戶發(fā)送狀態(tài)通知和其他消息。這個賬號的巨大的好友列表很容易導(dǎo)致網(wǎng)站出現(xiàn)服務(wù)器性能問題。

這實際上是一個非常典型的問題。任何的異常用戶，不僅僅是那些用于管理應(yīng)用的設(shè)計糟糕的賬號會有同樣的問題。

那些擁有大量好友、圖片、狀態(tài)、收藏的用戶，也會有前下面是一個真實案例，在一個用戶分享購買商品和購買經(jīng)驗的論壇上，這個特殊表上的查詢運行得非常慢:

mysq1> SELECT COUNT(DISTINCT thread_Id) AS COUNT_VALUE
-> FROM Message
-> WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
-> ORDER BY priority DESC, modifiedDate DESC

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

MySQL為這個查詢選擇了索引(groupId, userId)，如果不考慮列的基數(shù)，這看起來是一個非常合理的選擇。

但如果考慮一下 user ID和group ID條件匹配的行數(shù)，可能就會有不同的想法了:

從上面的結(jié)果來看符合組(groupId) 條件幾乎滿足表中的所有行，符合用戶(userId)條件的有130萬條記錄一也就是說索引基本上沒什么用。

因為這些數(shù)據(jù)是從其他應(yīng)用中遷移過來的，遷移的時候把所有的消息都賦予了管理員組的用戶。這個案例的解決辦法是修改應(yīng)用程序代碼，區(qū)分這類特殊用戶和組，禁止針對這類用戶和組執(zhí)行這個查詢。

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

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

5.3.5 聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。

具體的細(xì)節(jié)依賴于其實現(xiàn)方式，但I(xiàn)nnoDB的聚簇索引實際上在同一個結(jié)構(gòu)中保存了B-Tree 索引和數(shù)據(jù)行。

當(dāng)表有聚簇索引時,它的數(shù)據(jù)行實際上存放在索引的 葉子頁(leaf page) 中。

術(shù)語**“聚簇”** 表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起生8。因為無法同時把數(shù)據(jù)行存放在兩個不同的地方,所以一個表 只能有一個聚簇索引

覆蓋索引可以模擬多個聚簇索引的情況。

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

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

一些數(shù)據(jù)庫服務(wù)器允許選擇哪個索引作為聚簇索引。

InnoDB 將通過主鍵聚集數(shù)據(jù)，這也就是說圖5-3中的“被索引的列”就是主鍵列。

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

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

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

• 可以把相關(guān)數(shù)據(jù)保存在一起。例如實現(xiàn)電子郵箱時，可以根據(jù)用戶ID來聚集數(shù)據(jù),這樣只需要從磁盤讀取少數(shù)的數(shù)據(jù)頁就能獲取某個用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導(dǎo)致一次 磁盤I/O。
• 數(shù)據(jù)訪問更快。聚簇索引將索弓|和數(shù)據(jù)保存在同一個B-Tree中，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快。
• 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值。

聚簇索引也有一些缺點:

• 聚簇數(shù)據(jù)最大限度地提高了I/O密集型應(yīng)用的性能，但如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問的順序就沒那么重要了，聚簇索引也就沒什么優(yōu)勢了。
• 插入速度嚴(yán)重依賴于插入順序。按照主鍵的順序插入是加載數(shù)據(jù)到InnoDB表中速度最快的方式。但如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用OPTIMIZE TABLE命令重新組織一下表。

OPTIMIZE TABLE

• 更新聚簇索引列的代價很高，因為會強(qiáng)制InnoDB將每個被更新的行移動到新的位置。
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行的時候，可能面臨“頁分裂(page split)” 的問題。當(dāng)行的主鍵值要求必須將這一行插人到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導(dǎo)致表占用更多的磁盤空間。
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時候。
• 二級索引 (非聚簇索引)可能比想象的要更大，因為在二級索引的葉子節(jié)點包含了引用行的主鍵列。
• 二級索引訪問需要兩次索引查找，而不是一次。(回表）

二級索引需要兩次索引查找
保存的“行指針”的實質(zhì)。要記住，二級索引葉子節(jié)點保存的不是指向行的物理位置的指針，而是行的主鍵值。
這意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子節(jié)點獲得對應(yīng)的主鍵值，然后根據(jù)這個值去聚簇索引中查找到對應(yīng)的行。
這里做了重復(fù)的工作:兩次B-Tree查找而不是一次。
對于InnoDB，自適應(yīng)哈希索引能夠減少這樣的重復(fù)工作。

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

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

CREATE TABLE layout_ test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(co12)
);

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

MyISAM的數(shù)據(jù)分布

MyISAM按照數(shù)據(jù)插入的順序存儲在磁盤上，如圖5-4所示。

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

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

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

圖5-6顯示了col2 列上的索引。

事實上，MyISAM中主鍵索引和其他索引在結(jié)構(gòu)上沒有什么不同。主鍵索引就是一個名為PRIMARY的唯一非空索引。

InnoDB的數(shù)據(jù)分布

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

InnoDB的聚簇索引包含了了整個表，而不是只有索引。

因為在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那樣需要獨立的行存儲。

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

• InnoDB的二級索弓|和聚簇索引很不相同。InnoDB 二級索引的葉子節(jié)點中存儲的不是“行指針”，而是主鍵值,并以此作為指向行的“指針”。

  • 這樣的策略減少了當(dāng)出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護(hù)工作。
  • 使用主鍵值當(dāng)作指針會讓二級索引占用更多的空間，換來的好處是，InnoDB在移動行時無須更新二級索引中的這個“指針”。

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

圖5-8展示了B-Tree的葉子節(jié)點結(jié)構(gòu)，但我們故意省略了非葉子節(jié)點這樣的細(xì)節(jié)。

InnoDB的非葉子節(jié)點包含了索引列和一個指向下級節(jié)點的指針(下一級節(jié)點可以是非葉子節(jié)點，也可以是葉子節(jié)點)。這對聚簇索引和二級索引都適用。

圖5-9是描述InnoDB和MyISAM如何存放表的抽象圖。從圖5-9中可以很容易看出InnoDB和MyISAM保存數(shù)據(jù)和索引的區(qū)別。

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

如果正在使用InnoDB表并且沒有什么數(shù)據(jù)需要聚集，那么可以定義一個 代理鍵(surrogate key) 作為主鍵，這種主鍵的數(shù)據(jù)應(yīng)該和應(yīng)用無關(guān)，最簡單的方法是使用AUTO_ INCREMENT 自增列。

這樣可以保證數(shù)據(jù)行是按順序?qū)懭?，對于根?jù)主鍵做關(guān)聯(lián)操作的性能也會更好。

最好避免隨機(jī)的(不連續(xù)且值的分布范圍非常大)聚簇索引，特別是對于I/O密集型的應(yīng)用。

例如，從性能的角度考慮，使用UUID來作為聚簇索引則會很糟糕:它使得聚簇索引的插入變得完全隨機(jī)，這是最壞的情況，使得數(shù)據(jù)沒有任何聚集特性。

順序的主鍵什么時候會造成更壞的結(jié)果?

對于高并發(fā)工作負(fù)載，在InnoDB中按主鍵順序插入可能會造成明顯的爭用。

主鍵的上界會成為 “熱點” 。

• 因為所有的插入都發(fā)生在這里，所以并發(fā)插入可能導(dǎo)致間隙鎖競爭。

• 另一個熱點可能是AUTO_ INCREMENT 鎖機(jī)制

如果遇到這個問題，則可能需要考慮重新設(shè)計表或者應(yīng)用，或者更改innodb_autoinc_lock_mode 配置。

自增主鍵不連續(xù)？了解下Mysql的innodb_autoinc_lock_mode

如果你的服務(wù)器版本還不支持innodb_autoinc_lock_mode 參數(shù)，可以升級到新版本的InnoDB，可能對這種場景會工作得更好。

5.3.6 覆蓋索引

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

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

• 索引條目通常遠(yuǎn)小于數(shù)據(jù)行大小，所以如果只需要讀取索引，那MySQL就會極大地減少數(shù)據(jù)訪問量。這對緩存的負(fù)載非常重要，因為這種情況下響應(yīng)時間大部分花費在數(shù)據(jù)拷貝上。覆蓋索引對于I/O密集型的應(yīng)用也有幫助，因為索引比數(shù)據(jù)更小，更容易全部放入內(nèi)存中(這對于MyISAM尤其正確，因為MyISAM能壓縮索引以變得更小)。
• 因為索引是按照列值順序存儲的(至少在單個頁內(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é)點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

不是所有類型的索引都可以成為覆蓋索引。

• 覆蓋索引必須要存儲索引列的值，而哈希索引、空間索引和全文索引等都不存儲索引列的值，所以MySQL只能使用B-Tree索引做覆蓋索引。

不同的存儲引擎實現(xiàn)覆蓋索引的方式也不同，而且不是所有的引擎都支持覆蓋索引(在寫作本書時，Memory 存儲引擎就不支持覆蓋索引)。

當(dāng)發(fā)起一個被索引覆蓋的查詢(也叫做索引覆蓋查詢)時，在EXPLAIN的Extra列可以看到“Using index”的信息。

索引覆蓋查詢還有很多陷阱可能會導(dǎo)致無法實現(xiàn)優(yōu)化

MySQL查詢優(yōu)化器會在執(zhí)行查詢前判斷是否有一個索引能進(jìn)行覆蓋。

假設(shè)索引覆蓋了WHERE條件中的字段，但不是整個查詢涉及的字段。

• 如果條件為假(false)， MySQL 5.5和更早的版本也總是會回表獲取數(shù)據(jù)行，盡管并不需要這一行且最終會被過濾掉。

請查看如下例子：

• 沒有任何索引能夠覆蓋這個查詢。因為查詢從表中選擇了所有的列，而沒有任何索引覆蓋了所有的列。

  • 不過，理論上MySQL還有一個捷徑可以利用:WHERE條件中的列是有索引可以覆蓋的，因此MySQL可以使用該索引找到對應(yīng)的actor并檢查title是否匹配，過濾之后再讀取需要的數(shù)據(jù)行。

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

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

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

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

1. 第一個數(shù)據(jù)集，Sean Carrey出演了30 000部作品，其中有20 000部的標(biāo)題中包含了Apollo。
2. 第二個數(shù)據(jù)集，Sean Carrey出演了30 000部作品，其中40部的標(biāo)題中包含了Apollo。
3. 第三個數(shù)據(jù)集，Sean Carrey出演了50部作品，其中10部的標(biāo)題中包含了Apollo。

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

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

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

• 在示例3中，顯示了子查詢效率反而下降的情況。因為索引過濾時符合第一個條件的結(jié)果集已經(jīng)很小，所以子查詢帶來的成本反而比從表中直接提取完整行更高。

在大多數(shù)存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。通過InnoDB的二級索引的葉子節(jié)點都包含了主鍵的值，這意味著InnoDB的二級索引可以有效地利用這些“額外”的主鍵列來覆蓋查詢。

5.3.7 使用索引掃描來做排序

MySQL有兩種方式可以生成有序的結(jié)果:

• 通過排序操作
• 按索引順序掃描

如果EXPLAIN出來的type列的值為“index”, 則說明MySQL使用了索引掃描來做排序(不要和Extra列的“Using index”搞混淆了)。

type：表示查詢訪問數(shù)據(jù)的方式，或者說MYSQL查找行的方式
extra: 其中的Using index 代表使用了覆蓋索引

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

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

只有當(dāng)索引的列順序和ORDER BY 子句的順序完全一致，并且所有列的排序方向(倒序或正序)都一樣時，MySQL才能夠使用索引來對結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY 子句引用的字段全部為第一個表時，才能使用索引做排序。

• ORDER BY 子句和查找型查詢的限制是一樣的:需要滿足索弓|的最左前綴的要求;否則，MySQL都需要執(zhí)行排序操作，而無法利用索引排序。

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

所謂前導(dǎo)列，就是在創(chuàng)建復(fù)合索引語句的第一列或者連續(xù)的多列

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

(rental_date, inventory_id, customer_id):

	CREATE TABLE rental(
	...
	PRIMARY KEY(rental_id),
	UNIQUE KEY rental_date(rental_date , inventory_id, customer_id),
	KEY idx_fk_inventory_id(inventory_id),
	KEY idx_fk_customer_id (customer_id),
	KEY idx_fk_staff_id(staff_id),
	...
);

即使ORDER BY子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因為索引的第一列被指定為一個常數(shù)。

5.3.8 壓縮（前綴壓縮）索引

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

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

例如，索引塊中的第一個值是“perform”, 第二個值是"performance", 那么第二個值的前綴壓縮后存儲的是類似“7,ance" 這樣的形式。MyISAM對行指針也采用類似的前綴壓縮方式。

壓縮塊使用更少的空間，代價是某些操作可能更慢。

因為每個值的壓縮前綴都依賴前面的值，所以MyISAM查找時無法在索引塊使用二分查找而只能從頭開始掃描。正序的掃描速度還不錯，但是如果是倒序掃描一例如 ORDER BY DESC 就不是很好了。所有在塊中查找某一行 的操作平均都需要掃描半個索引塊。

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

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

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

MySQL允許在相同列上創(chuàng)建多個索引，無論是有意的還是無意的。

MySQL需要單獨維護(hù)重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進(jìn)行考慮，這會影響性能。

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

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

CREATE TABLE test (
	ID INT NOT NULL PRIMARY KEY,
	A INT NOT NULL, 
	B INT NOT NULL,
	UNIQUE(ID),
	INDEX(ID)
) ENGINE=InnoDB;

MySQL的唯一限制和主鍵限制都是通過索引實現(xiàn)的，上面的寫法實際上在相同的列上創(chuàng)建了三個重復(fù)的索引。

通常并沒有理由這樣做，除非是在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求。

冗余索引和重復(fù)索引有一些不同

• 如果創(chuàng)建了索引(A, B),再創(chuàng)建索引(A)就是冗余索引,因為這只是前一個索引的前綴索引。

• 因此索引(A, B)也可以當(dāng)作索引(A)來使用(這種冗余只是對B-Tree索引來說的)。

• 但是如果再創(chuàng)建索引(B, A),則不是冗余索引，索引(B)也不是，因為B不是索引(A, B)的最左前綴列。

• 另外，其他不同類型的索引(例如哈希索引或者全文索引)也不會是B-Tree索引的冗余索引，而無論覆蓋的索引列是什么。

冗余索引通常發(fā)生在為表添加新索引的時候。

例如，有人可能會增加一個新的索引(A, B)而不是擴(kuò)展已有的索引(A)。還有一種情況是將-一個索引擴(kuò)展為(A, ID)，其中ID是主鍵,對于InnoDB來說主鍵列已經(jīng)包含在二級索引中了，所以這也是冗余的。

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

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

5.3.11 索引和鎖

索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那么就會鎖定更少的行，從兩個方面來看這對性能都有好處。

• 雖然InnoDB的行鎖效率很高，內(nèi)存使用也很少，但是鎖定行的時候仍然會帶來額外開銷
• 鎖定超過需要的行會增加鎖爭用并減少并發(fā)性

InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數(shù)，從而減少鎖的數(shù)量。但這只有當(dāng)InnoDB在存儲引擎層能夠過濾掉所有不需要的行時才有效。

如果索引無法過濾掉無效的行，那么在InnoDB檢索到數(shù)據(jù)并返回給服務(wù)器層以后，MySQL服務(wù)器才能應(yīng)用WHERE子句。這時已經(jīng)無法避免鎖定行了:InnoDB已經(jīng)鎖住了這些行，到適當(dāng)?shù)臅r候才釋放。

在MySQL 5.1和更新的版本中，InnoDB 可以在服務(wù)器端過濾掉行后就釋放鎖，但是在早期的MySQL版本中，InnoDB 只有在事務(wù)提交后才能釋放鎖。

（索引案例與維護(hù)修復(fù)略）

附錄

《高性能MySQL》
Baron Schwartz, Peter Zaitsev, Vadim Tkachenko 著
寧海元 周振興 彭立勛 翟衛(wèi)祥 劉輝 譯文章來源地址http://www.zghlxwxcb.cn/news/detail-465936.html

到了這里，關(guān)于《高性能MySQL》——創(chuàng)建高性能的索引（筆記）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！