《高效能MySQL》讀後感——高效能的索引策略

對於一條SQL，開發同學最先關心的啥？ 我覺得還不到這個SQL在資料庫的執行過程，而是這條SQL是否能儘快的返回結果，在SQL的生命週期裡，每一個環節都有足夠的最佳化空間，但是我們有沒有想過，SQL最佳化的本質是啥？終極目標又是啥？其實最佳化本質上就是減少SQL對資源的消耗和依賴，正如資料庫最佳化的終極目的是Do nothing in database一樣，SQL最佳化的終極目的是Consume no resource。

資料庫資源有兩個特性：

首先資源是有限的，大家都搶著用就會有瓶頸的，所以SQL的瓶頸可能是由資源緊張產生的。

其次資源是有代價的，並且代價各異，比如記憶體的時延100ns， SSD100us，SAS盤10ms，網路更高，那麼訪問CPU L1/L2/L3 cache的代價就比訪問記憶體的要低，訪問記憶體資源的代價要比訪問硬碟資源的代價，所以SQL的瓶頸也可能是訪問了代價比較高的資源導致的。

現代計算機體系下，機器上粗粒度的資源就那麼幾種，無非是CPU，記憶體，硬碟，和網路。那麼我們來看下SQL需要消耗哪些資源：

比較、排序、SQL解析、函式或邏輯運算需要用到CPU；

快取資料訪問，臨時資料存放需要用到記憶體；

冷資料讀取，大資料量的排序和關聯，資料寫入落盤，需要訪問硬碟；

SQL請求互動，結果集返回需要網路資源。

那麼SQL最佳化思路自然是減少SQL的解析，減少複雜的運算，減少資料處理的規模，減少對物理IO的依賴，減少伺服器和客戶端的網路互動， 本文的每一節都解決上面的一兩點，索引策略的組合最大化提升SQL最佳化效能：

獨立的列

： 減少SQL的解析

字首索引和索引選擇性

： 減少資料處理的規模，減少對物理IO的依賴

多列索引

：減少對物理IO的依賴

選擇和是的索引列順序

： 減少資料處理的規模，減少對物理IO的依賴

聚簇索引

： 減少資料處理的規模，減少對物理IO的依賴

覆蓋索引

： 減少對物理IO的依賴

使用索引掃描來做排序

： 減少複雜的運算

返回必要的列

： 減少對物理IO的依賴，減少伺服器和客戶端的網路互動

在學習MySQL索引之前，最好先學習

MySQL索引背後的資料結構及演算法原理

。

獨立的列

獨立的列是指索引列不能是表示式的一部分，也不能是函式的引數。

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

mysql

>

explain

select

actor_id

from

actor

where

actor_id

+

1

=

5

；

+——+——————-+————-+————-+————————-+——————————-+————-+————+————+——————————————+

|

id

|

select_type

|

table

|

type

|

possible_keys

|

key

|

key_len

|

ref

|

rows

|

Extra

|

+——+——————-+————-+————-+————————-+——————————-+————-+————+————+——————————————+

|

1

|

SIMPLE

|

actor

|

index

|

NULL

|

idx_actor_last_name

|

137

|

NULL

|

200

|

Using

where

；

Using

index

|

+——+——————-+————-+————-+————————-+——————————-+————-+————+————+——————————————+

憑肉眼容易看出where的表示式其實等價於actor_id=4，但是MySQL無法自動解析這個函式。所以應該簡化where條件：

始終將索引列單獨放在比較符號的一側

，使用索引的正確寫法如下，此時使用主鍵索引：

mysql

>

explain

select

actor_id

from

actor

where

actor_id

=

4

；

+——+——————-+————-+————-+————————-+————-+————-+————-+————+——————-+

|

id

|

select_type

|

table

|

type

|

possible_keys

|

key

|

key_len

|

ref

|

rows

|

Extra

|

+——+——————-+————-+————-+————————-+————-+————-+————-+————+——————-+

|

1

|

SIMPLE

|

actor

|

const

|

PRIMARY

|

PRIMARY

|

2

|

const

|

1

|

Using

index

|

+——+——————-+————-+————-+————————-+————-+————-+————-+————+——————-+

下面是另外一個常見的錯誤：

mysql>select 。。。 where to_days（current_date）-to_days（date_col） <=10；

字首索引和索引選擇性

有時候索引很長的字元列，讓索引變得大且慢。通常索引開始的部分字元，可以大大節約索引空間，從而提高索引效率。但這樣也會降低索引的選擇性。 索引的選擇性是指：不重複的索引值（也稱為基數，Cardinality）和資料表的記錄總數（#T）的比值，範圍是 1/#T ~ 1。索引的選擇性越高則查詢效率越高，因為選擇性高的索引讓MySQL在查詢時過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，效能也是最好的。

一般情況下某個列字首的選擇性如果足夠高，也是可以滿足查詢效能。對於BLOB、TEXT或者很長的VARCHAR型別的列，必須使用字首索引，因為MySQL不允許索引這些列的完整長度。如下所示，varchar（4000）型別的comment列最多隻能建字首長度為255的索引。

mysql

>

show

create

table

shop

；

+————-+————————————————————————————————————————————————————————————————-+

|

Table

|

Create

Table

|

+————-+————————————————————————————————————————————————————————————————-+

|

shop

|

CREATE

TABLE

`demo`

（

`id`

int

（

11

）

NOT

NULL

AUTO_INCREMENT

COMMENT

‘記錄ID’

，

`comment`

varchar

（

4000

）

DEFAULT

NULL

，

PRIMARY

KEY

（

`id`

），

KEY

`idx_comment`

（

`comment`

（

255

））

）

ENGINE

=

InnoDB

AUTO_INCREMENT

=

20001

DEFAULT

CHARSET

=

utf8

|

+————-+————————————————————————————————————————————————————————————————-+

訣竅在於選擇足夠長的字首以保證較高的選擇性，同時又不能太長（以便節約空間）。字首應該足夠長，以使得字首索引的選擇性接近於索引整個列。換句話說，字首的”基數“應該接近於完整列的”基數“。

為了決定字首的合適長度，需要找到最常見的值的列表，然後和最常見的字首列表進行比較。在示例資料Sakila沒有合適的例子，所以我們從表city生成一個示例表，生成足夠的資料用來演示：

mysql

>

CREATE

TABLE

city_demo

（

city

VARCHAR

（

50

）

NOT

NULL

）；

mysql

>

INSERT

INTO

city_demo

（

city

）

SELECT

city

from

city

；

Records

：

600

Duplicates

：

0

Warnings

：

0

重複執行下面的sql 五次：

mysql

>

insert

into

city_demo

（

city

）

select

city

from

city_demo

；

Records

：

600

Duplicates

：

0

Warnings

：

0

執行下面sql 隨機分佈資料：

mysql

>

update

city_demo

set

city

=

（

select

city

from

city

order

by

RAND

（）

limit

1

）；

Rows

matched

：

19200

Changed

：

19170

Warnings

：

0

示例資料集分佈是隨機生成的，與你的結果會有所不同，但是對於結論沒有影響。首先，我們找到最常見的城市列表：

mysql

>

select

count

（

*

）

as

cnt

，

city

from

city_demo

group

by

city

order

by

cnt

desc

limit

10

；

+——-+——————————-+

|

cnt

|

city

|

+——-+——————————-+

|

60

|

London

|

|

49

|

Skikda

|

|

48

|

Izumisano

|

|

47

|

Valle

de

Santiago

|

|

47

|

Tegal

|

|

46

|

Goinia

|

|

46

|

Tychy

|

|

46

|

Idfu

|

|

46

|

Clarksville

|

|

46

|

Paarl

|

+——-+——————————-+

注意到，上面每個值都出現了46-60次，現在查詢到最頻繁出現的城市字首，先從3個字首字母開始：

mysql> select count（*） as cnt，left（city，3） as pref from city_demo group by pref order by cnt desc limit 10；+——-+————+| cnt | pref |+——-+————+| 453 | San || 195 | Cha || 161 | Tan || 157 | Sou || 148 | Shi || 146 | Sal || 145 | al- || 140 | Man || 137 | Hal || 134 | Bat |+——-+————+

每個字首都比原來的城市出現的次數要多，因此唯一字首比唯一城市要少得多。然後我們增加字首長度，直到這個字首的選擇性接近完整列的選擇性。經過實驗發現字首長度為7時最為合適：

mysql> select count（*） as cnt，left（city，7） as pref from city_demo group by pref order by cnt desc limit 10；+——-+————-+| cnt | pref |+——-+————-+| 74 | Valle d || 70 | Santiag || 61 | San Fel || 60 | London || 49 | Skikda || 48 | Izumisa || 47 | Tegal || 46 | Tychy || 46 | Goinia || 46 | Idfu |+——-+————-+

計算合適的字首長度的另外一個方法就是計算完整列的選擇性，並使字首的選擇性接近於完整列的選擇性。下面展示計算完整列的選擇性：

mysql> select count（distinct city）/count（*） from city_demo；+————————————————-+| count（distinct city）/count（*） |+————————————————-+| 0。0312 |+————————————————-+

通常來說（儘管也有例外情況），這個例子中如果字首的選擇效能夠接近於0。031，基本上就可用了。可以在一個查詢中針對不同的字首長度進行計算，這對於大表非常有用。下面給出瞭如何在同一個查詢中計算不同字首長度的選擇性：

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 sel6， -> count（distinct left（city，7））/count（*） as sel7 -> from city_demo；+————+————+————+————+————+| sel3 | sel4 | sel5 | sel6 | sel7 |+————+————+————+————+————+| 0。0239 | 0。0293 | 0。0305 | 0。0309 | 0。0310 |+————+————+————+————+————+

查詢顯示當前綴長度達7的時候，再增加字首長度，選擇性提升的幅度已經很小，但是增加的字首索引佔用空間。

只看平均選擇性是不夠的，也有例外的情況，需要考慮最壞情況下的選擇性。平均選擇性會讓你認為字首長度為4或者5的索引已經足夠了，但如果資料分佈很不均勻，可能就會有陷阱。如果觀察字首為4的最常出現城市的次數，可以看到明顯不均勻：

mysql> select count（*） as cnt，left（city，4） as pref from city_demo group by pref order by cnt desc limit 5；+——-+————+| cnt | pref |+——-+————+| 198 | Sant || 186 | San || 124 | Sout || 106 | Toul || 102 | Chan |+——-+————+

如果字首是4個位元組，則最常出現的字首的出現次數比最常出現的城市的出現次數要大很多。即這些值的選擇性比平均選擇性要低。如果有比這個隨機生成的示例更真實的資料，就更有可能看到這種現象。例如在真實的城市名上建一個長度為4的字首索引，對於以“San”和“New”開頭的城市的選擇性就會非常糟糕，因為很多城市都以這兩個詞開頭。

在上面的示例中，已經找到了合適的字首長度，下面演示一下如何建立字首索引：

mysql>alter table city_demo add index idx_city（city（7））；

字首索引是一種能使索引更小更快的有效辦法，但另一方面也有其缺點：

MySQL無法使用字首索引做ORDER BY和GROUP BY，也無法使用字首索引做覆蓋掃描

。

有時候字尾索引（suffix index）也有用途（例如，找到某個域名的所有電子郵件地址）。MySQL原生並不支援反向索引，但是可以把字串反轉後儲存，並基於此建立字首索引。可以透過觸發器來維護這種索引。

多列索引

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

先來看第一個問題，為每個列建立獨立的索引，從show create table 中很容易看到這種情況：

create

talbe

t

（

c1

int

，

c2

int

，

c3

int

，

key

（

c1

），

key

（

c2

），

key

（

c3

）

）；

這種索引策略，一般是人們聽到一些專家諸如“把where條件裡面的列都建上索引”這樣模糊的建議導致的。實際上這個建議非常錯誤。這樣一來最好的情況下也只能是“一星”索引（關於三星索引可以參考拙作

《高效能MySQL》讀後感——B-Tree索引

的三星索引說明），其效能比起真正最優的索引可能差幾個數量級。有時如果無法設計一個“三星”索引，那麼不如忽略掉where子句，集中精力最佳化索引列的順序，或者建立一個全覆蓋索引。

在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查詢效能。MySQL 5。0和更新的版本引入了一種叫”索引合併”（index merge）策略，一定程度上可以使用表上的多個單列索引來定位指定的行。

索引合併策略有時候是一種最佳化的結果，但大多數時候說明表索引建得很糟糕：

當出現伺服器對多個索引做相交操作時（通常有多個AND條件），通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。

當伺服器需要對多個索引做聯合操作時（通常有多個OR條件），通常需要耗費大量CPU和記憶體資源在演算法的快取、排序和合並操作上。特別是當其中有些索引的選擇性不高，需要合併掃描返回的大量資料的時候。

更重要的是，最佳化器不會把這些計算到“查詢成本”（cost）中，最佳化器只關心隨機頁面讀取。這使得查詢的成本被“低估”，導致該執行計劃還不如直接走全表掃描。這樣做不但消耗更多的CPU和記憶體資源，還可能影響查詢的併發性，但如果是單獨執行這樣的查詢，則往往忽略對併發性的影響。

如果在explain中看到有索引合併，應該好好檢查一下查詢和表的結構，看是不是已經是最優的。也可以透過引數optimizer_switch來關閉索引合併功能。也可以使用ignore index提示讓最佳化器忽略掉某些索引。

選擇合適的索引列順序

我們遇到的最容易引起困惑的問題就是索引列的順序。正確的順序依賴於使用該索引的查詢，並且同時需要考慮如何更好地滿足排序和分組的需要（順便說明，本節內容適用於B-Tree索引；雜湊或者其他型別的索引並不會像B-Tree索引一樣按順序儲存資料）。

在一個多列B-Tree索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，等等。所以，索引可以按照升序或者降序進行掃描，以滿足精確符合列順序order by，group by和distinct等子句的查詢需求。

所以多列索引的列順序至關重要。

對於如何選擇索引的順序有一個經驗法則：

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

。這個建議有用嗎？在某些場景可能有幫助，但通常不如避免隨機IO和排序那麼重要，考慮問題需要更全面（場景不同則選擇不同，沒有一個放之四海皆準的法則。這裡只是說明，這個經驗法則可能沒有你想象的重要）。

當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是最好的。這時候索引的作用只是用於最佳化where條件的查詢。在這種情況下，這樣設計的索引確實能夠最快的過濾出需要的行，對於在where子句中使用了索引部分字首列的查詢來說選擇性也更高。然而，效能不只是依賴於所有索引列的選擇性（整體基數），和查詢條件的具體值也有關係，也就是和值的分佈有關。這和前面介紹的選擇字首的長度需要考慮的地方一樣。可能需要根據那些執行頻率最高的查詢來調整索引列的順序，讓這種情況下索引的選擇性最高。

以下面的查詢為例：

select * from payment where staff_id=2 and customer_id=584；

是應該建立一個（staff_id，customer_id）索引還是應該顛倒一下順序？可以跑一些查詢來確定在這個表中值的分佈情況，並確定哪個列的選擇性更高。先用下面的查詢預測一下，看看各個where條件的分支對應的資料基數有多大：

mysql

>

select

sum

（

staff_id

=

2

），

sum

（

customer_id

=

584

）

from

payment

\G

***************************

1。

row

***************************

sum

（

staff_id

=

2

）：

7992

sum

（

customer_id

=

584

）：

30

根據前面的經驗法則，應該將索引customer_id放到前面，因為對應條件值的customer_id數量更小。我們再來看看對於這個customer_id的條件值，對應的staff_id列的選擇性如何：

mysql

>

select

sum

（

staff_id

=

2

）

from

payment

where

customer_id

=

584

\G

***************************

1。

row

***************************

sum

（

staff_id

=

2

）：

17

這樣做有一個地方需要注意，查詢的結果非常依賴於特定的具體值。如果按上述辦法最佳化，可能對其他一些條件值的查詢不公平，伺服器的整體效能可能變得更糟，或者其他某些查詢的執行變得不如預期。

如果是從諸如pt-query-digest這樣的工具的報告中提取“最差”查詢，那麼再按上述辦法選定的索引順序往往是非常高效的。如果沒有類似的具體查詢來執行，那麼最好按經驗法則來做，因為經驗法則考慮的是全域性基數和選擇性，而不是某個具體查詢：

mysql

>

select

count

（

distinct

staff_id

）

/

count

（

*

）

as

staff_id_selectivity

，

->

count

（

distinct

customer_id

）

/

count

（

*

）

as

customer_id_selectivity

，

->

count

（

*

）

->

from

payment

\

G

***************************

1

。

row

***************************

staff_id_selectivity

：

0。0001

customer_id_selectivity

：

0。0373

count

（

*

）：

16049

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

mysql>alter table payment add index idx_cust_staff_id（customer_id，staff_id）；

當使用字首索引的時候，在某些條件值的基數比正常值高的時候，問題就來了。例如，在某些應用程式中，對於沒有登入的使用者，都將其使用者名稱記錄為”guest”，在記錄使用者行為的會話表和其他記錄使用者活動的表中”guest”就成為了一個特殊使用者ID。一旦查詢涉及這個使用者，那麼和對於正常使用者的查詢就大不同了，因為通常有很多會話都是沒有登入的。系統賬號也會導致類似的問題。一個應用通常都有一個特殊的管理員賬號，和普通賬號不同，它並不是一個具體的使用者，系統中所有的其他使用者都是這個使用者的好友，所以系統往往透過它向網站的所有使用者傳送狀態通知和其他訊息。這個賬號的巨大的好友列表很容易導致網站出現伺服器效能問題。

覆蓋索引

通常大家都會根據查詢的where條件來建立合適的索引，不過這只是索引最佳化的一個方面。設計優秀的索引應該考慮到整個查詢，而不單單是where條件部分。索引確實是一種查詢資料的高效方式，但是MySQL也可以使用索引來直接獲取列的資料，這樣就不再索引讀取資料行。如果索引的葉子節點中已經包含要查詢的資料，那麼還有什麼必要再回表查詢呢？如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為“覆蓋索引”。

覆蓋索引是非常有用的工具，能夠極大地提高效能。考慮一下如果查詢只需要掃描索引而無須回表，會帶來多少好處：

索引條目通常遠小於資料行大小，如果只讀取索引，那麼MySQL訪問更少資料量。

這對快取的負載非常重要，因為這種情況下響應時間大部分花費在資料複製上。覆蓋索引對於IO密集型的應用也有幫助，因為索引比資料還小，更容易全部放入記憶體中（這對於MyISAM尤其正確，因為MyISAM能壓縮索引以變得更小）。

索引按照列值順序儲存（至少在單個頁內是如此），對於IO密集型的範圍查詢會比隨機從磁碟讀取每一行資料的IO要少得多。

大多資料引擎能更好的快取索引。比如MyISAM在記憶體中只快取索引，資料則依賴於作業系統來快取，因此訪問資料多一次系統呼叫。

覆蓋索引對於InnoDB表特別有用，因為InnoDB使用聚集索引組織資料。InnoDB的二級索引在葉子節點中儲存行的主鍵值，如果二級索引能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。

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

對於索引覆蓋查詢（index-covered query），使用EXPLAIN時，在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一個組合索引（store_id，film_id），對於只需要訪問這兩列的查詢，MySQL就可以使用覆蓋索引，如下：

mysql> EXPLAIN SELECT store_id， film_id FROM inventory\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： inventory type： indexpossible_keys： NULL key： idx_store_id_film_id key_len： 3 ref： NULL rows： 5341 Extra： Using index

在大多數引擎中，只有當查詢語句所訪問的列是索引的一部分時，索引才會覆蓋。但是，InnoDB不限於此，InnoDB的二級索引在葉子節點中儲存了primary key的值。例如，sakila。actor表使用InnoDB，而且對於是last_name上有二級索引，所以索引能覆蓋那些訪問actor_id的查詢：

mysql> EXPLAIN SELECT actor_id， last_name FROM actor WHERE last_name = ‘HOPPER’\G*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： actor type： refpossible_keys： idx_actor_last_name key： idx_actor_last_name key_len： 137 ref： const rows： 2 Extra： Using where； Using index

使用索引掃描來做排序

MySQL有兩種方式生成有序的結果：

透過排序操作，Explain 的Extra 輸出“Using filesort”， MySQL使用檔案排序；

透過索引順序掃描，Explain的type列值為index，MySQL使用索引掃描排序（不要和Extra 列的“Using index”混淆）。

掃描索引本身很快，因為只需從一條記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，就要每掃描一條索引記錄得回表查詢一次對應的行。這基本上都是隨機IO，因此按索引順序讀取資料的速度通常比順序的全表掃描慢，尤其是在IO密集型。所以，設計索引時讓同一個索引既滿足排序，又用於查詢行，避免隨機IO。

當索引的列的順序和ORDER BY子句的順序完全一致，並且所有列的排序方向（倒序和正序）都一樣時，MySQL才能使用索引來對結果做排序。如果查詢需要關聯多張表，則只有ORDER BY子句引用的欄位全部為第一個表時，才能使用索引來做排序。ORDER BY子句和Where查詢的限制是一樣的：需要滿足索引的最左字首的要求。

當MySQL不能使用索引進行排序時，就會利用自己的排序演算法（快速排序演算法）在記憶體（sort buffer）中對資料進行排序，如果記憶體裝載不下，它會將磁碟上的資料進行分塊，再對各個資料塊進行排序，然後將各個塊合併成有序的結果集（實際上就是外排序，使用臨時表）。

對於filesort，MySQL有兩種排序演算法。

（1）兩次掃描演算法（Two passes）

實現方式是先將需要排序的欄位和可以直接定位到相關行資料的指標資訊取出，然後在設定的記憶體（透過引數sort_buffer_size設定）中進行排序，完成排序之後再次透過行指標資訊取出所需的Columns。

注：該演算法是4。1之前採用的演算法，它需要兩次訪問資料，尤其是第二次讀取操作會導致大量的隨機I/O操作。另一方面，記憶體開銷較小。

（2）一次掃描演算法（single pass）

該演算法一次性將所需的Columns全部取出，在記憶體中排序後直接將結果輸出。

注：從 MySQL 4。1 版本開始使用該演算法。它減少了I/O的次數，效率較高，但是記憶體開銷也較大。如果我們將並不需要的Columns也取出來，就會極大地浪費排序過程所需要的記憶體。在 MySQL 4。1 之後的版本中，可以透過設定 max_length_for_sort_data 引數來控制 MySQL 選擇第一種排序演算法還是第二種。當取出的所有大欄位總大小大於 max_length_for_sort_data 的設定時，MySQL 就會選擇使用第一種排序演算法，反之，則會選擇第二種。為了儘可能地提高排序效能，我們自然更希望使用第二種排序演算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當對連線操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然後進行連線處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連線完成之後進行filesort操作，此時，EXPLAIN輸出“Using temporary；Using filesort”。

當前導列為常量時，ORDER BY子句可以不滿足索引的最左字首要求。例如，Sakila資料庫的表rental在列

（rental_date，inventory_id，customer_id）

上有名為rental_date的索引，如下表所示。

CREATE

TABLE

`rental`

（

`rental_id`

int

（

11

）

NOT

NULL

AUTO_INCREMENT

，

`rental_date`

datetime

NOT

NULL

，

`inventory_id`

mediumint

（

8

）

unsigned

NOT

NULL

，

`customer_id`

smallint

（

5

）

unsigned

NOT

NULL

，

`return_date`

datetime

DEFAULT

NULL

，

`staff_id`

tinyint

（

3

）

unsigned

NOT

NULL

，

`last_update`

timestamp

NOT

NULL

DEFAULT

CURRENT_TIMESTAMP

ON

UPDATE

CURRENT_TIMESTAMP

，

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`

），

CONSTRAINT

`fk_rental_customer`

FOREIGN

KEY

（

`customer_id`

）

REFERENCES

`customer`

（

`customer_id`

）

ON

UPDATE

CASCADE

，

CONSTRAINT

`fk_rental_inventory`

FOREIGN

KEY

（

`inventory_id`

）

REFERENCES

`inventory`

（

`inventory_id`

）

ON

UPDATE

CASCADE

，

CONSTRAINT

`fk_rental_staff`

FOREIGN

KEY

（

`staff_id`

）

REFERENCES

`staff`

（

`staff_id`

）

ON

UPDATE

CASCADE

）

ENGINE

=

InnoDB

AUTO_INCREMENT

=

16050

DEFAULT

CHARSET

=

utf8

；

MySQL使用rental_date索引為下面的查詢排序，從EXPLAIN中看出沒有出現

filesort

：

mysql

>

EXPLAIN

SELECT

rental_id

，

staff_id

FROM

rental

WHERE

rental_date

=

‘2005-05-25’

ORDER

BY

inventory_id

，

customer_id\G

***************************

1。

row

***************************

id

：

1

select_type

：

SIMPLE

table

：

rental

type

：

ref

possible_keys

：

rental_date

key

：

rental_date

key_len

：

8

ref

：

const

rows

：

1

Extra

：

Using

where

即使ORDER BY 字句不滿足最左字首索引，也可以用於查詢排序，因為索引的第一列被指定為常數。

下面這個查詢可以利用索引排序，是因為查詢為索引的第一列提供了常量條件，用第二列進行排序，將兩列組合在一起，就形成了索引的最左字首：

。。。 where rental_date = ‘2005-05-25’ order by inventory_id desc；

下面這個查詢也沒問題，因為order by使用的就是索引的最左字首：

。。。 where rental_data > ‘2005-05-25’ order by rental_date，inventory_id；

下面一些不能使用索引做排序的查詢：

下面這個查詢使用兩種不同的排序方向：

。。。 where rental_date = ‘2005-05-25’ order by inventory_id desc，customer_id asc；

下面這個查詢的order by 子句中引用一個不在索引中的列（staff_id）：

。。。 where rental_date = ‘2005-05-25’ order by inventory_id，staff_id；

下面這個查詢的where 和 order by的列無法組合成索引的最左字首：

。。。 where rental_date = ‘2005-05-25’ order by customer_id；

下面這個查詢在索引列的第一列是範圍條件，所以MySQL無法使用索引的其餘列：

。。。 where rental_date > ‘2005-05-25’ order by customer_id；

這個查詢在inventory_id列上有多個等於條件。對於排序來說，這也是一種範圍查詢：

。。。 where rental_date = ‘2005-05-25’ and inventory_id in（1，2） order by customer_id；

壓縮（字首壓縮）索引

MyISAM使用字首壓縮來減少索引的大小，讓更多的索引可以放入記憶體中，這在某些情況下能極大地提高效能。預設只壓縮字串，但透過引數設定也可以對整數做壓縮。MyISAM壓縮每個索引塊的方法是，先完全儲存索引塊中的第一個值，然後將其他值和第一個值進行比較得到相同字首的位元組數和剩餘的不同字尾部分，把這部分儲存起來即可。例如：索引塊中的第一個值是“perform”，第二個值是“performance”，那麼第二個值的字首壓縮後儲存的是類似“7，ance”這樣的形式。MyISAM對行指標也採用類似的字首壓縮方式。

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

測試表明，對於CPU密集型應用，因為掃描需要隨機查詢，壓縮索引使得MyISAM在索引查詢上要慢好幾倍。壓縮索引的倒序掃描就更慢了。壓縮索引需要在CPU記憶體資源與磁碟之間做權衡。壓縮索引可能只需要十分之一大小的磁碟空間，如果是IO密集型應用，對某些查詢帶來的好處會比成本多很多。

可以在CREATE TABLE 語句中指定pack_keys引數來控制索引壓縮的方式。

冗餘和重複索引

MySQL允許在相同列上建立多個索引，MySQL需要單獨維護重複的索引，並且最佳化器在最佳化查詢的時候需要逐個地進行考慮，影響查詢效能。

重複索引是指在相同的列上按照相同的順序建立相同型別的索引。應該避免這樣建立重複索引，發現以後也應該立即移除。

如下面的程式碼，建立一個主鍵，先加上唯一限制，然後再加上索引以供查詢使用。事實上，MySQL的唯一限制和主鍵限制都是透過索引實現的，因此，實際上在相同的列上建立了三個重複的索引。通常並沒有理由這樣做，除非在同一列上建立不同型別的索引來滿足不同的查詢需求。

create

table

test

（

id

int

not

null

primary

key

，

a

int

not

null

，

b

int

not

null

，

unique

（

id

），

index

（

id

）

）

engine

=

InnoDB

；

冗餘索引和重複索引有一些不同。如果建立了索引（a，b），再建立索引（a）就是冗餘索引，因為這只是前一個索引的字首索引。因此索引（a，b）也可以當作索引（a）來使用（這種冗餘只是對B-Tree索引來說的）。但是如果再建立索引（b，a），則不是冗餘索引，索引（b）也不是，因為b不是索引（a，b）的最左字首列。另外其他不同型別的索引（例如雜湊索引或者全文索引）也不會是B-Tree索引的冗餘索引，而無論覆蓋的索引列是什麼。

冗餘索引通常發生在為表新增新索引的時候。例如，有人可能會增加一個新的索引（a，b）而不是擴充套件已有的索引（a）。還有一種情況是將一個索引擴充套件為（a，id），其中id是主鍵，對於InnoDB來說主鍵列已經包含在二級索引中了，所以這也是冗餘的。

大多數情況下都不需要冗餘索引，應該儘量擴充套件已有的索引而不是建立新索引。但也有時候出於效能方面的考慮需要冗餘索引，因為擴充套件已有的索引會導致其變得太大，從而影響其他使用該索引的查詢的效能。

例如：如果在整數列上有一個索引，現在需要額外增加一個很長的varchar列來擴充套件該索引，那效能可能會急劇下降。特別是有查詢把這個索引當作覆蓋索引，或者這是MyISAM表並且有很多範圍查詢（由於MyISAM的字首壓縮）的時候。

舉例，MyISAM引擎，表userinfo有100W行記錄，每個state_id值大概2W行，在state_id列有一個索引對下面的查詢有用，假設查詢名為Q1：

mysql> select count（*） from userinfo where state_id=5；

查詢測試結果：QPS=115。還有一個相關查詢檢索幾個列的值，而不是統計行數，假設名為Q2：

mysql> select state_id，city，address from userinfo where state_id=5；

查詢測試結果：QPS<10。提升該查詢的效能可以擴充套件索引為為（state_id， city， address），讓索引覆蓋查詢：

mysql

>

ALTER

TABLE

userinfo

DROP

KEY

state_id

，

->

ADD

KEY

state_id_2

（

state_id

，

city

，

address

）；

如果把state_id索引擴充套件為（state_id，city，address），那麼第二個查詢的效能更快了，但是第一個查詢卻變慢了，如果要兩個查詢都快，那麼就必須要把state_id列索引進行冗餘了。但如果是innodb表，不冗餘state_id列索引對第一個查詢的影響並不明顯，因為innodb沒有使用索引壓縮，

MyISAM和InnoDB表使用不同索引策略的查詢QPS測試結果（以下測試資料僅供參考）：

上表結論：

對於MyISAM引擎，把state_id擴充套件為state_id_2（state_id，city，address），Q2的QPS更高，覆蓋索引起作用；但是Q1的QPS下降明顯，受MyISAM的字首壓縮影響需要從索引塊頭開始掃描。

對於InnoDB引擎，把state_id擴充套件為state_id_2（state_id，city，address），Q2的QPS更高，覆蓋索引起作用；但是Q1的QPS下降不明顯，因為InnoDB沒有使用索引壓縮。

MyISAM引擎需要建state_id和state_id_2索引，才能保證Q1/Q2效能最佳；而InnoDB引擎只需state_id_2索引就能保證Q1/Q2效能最佳，從這裡看出，索引壓縮也並不是最好的。

有兩個索引的缺點是索引成本更高，下表是在不同的索引策略時插入InnoDB和MyISAM表100W行資料的速度（以下測試資料僅供參考）：

可以看到，不論什麼引擎，索引越多，插入速度越慢，特別是新增索引後導致達到了記憶體瓶頸的時候，所以，要避免冗餘索引和重複索引。

在刪除索引的時候要非常小心：如果在InnoDB引擎表上有where a=5 order by id 這樣的查詢，那麼索引（a）就會很有用，索引（a，b）實際上是（a，b，id）索引，這個索引對於where a=5 order by id 這樣的查詢就無法使用索引做排序，而只能使用檔案排序（filesort）。

舉例說明，表shop表結構如下：

CREATE TABLE `shop` （ `id` int（11） NOT NULL AUTO_INCREMENT COMMENT ‘記錄ID’， `shop_id` int（11） NOT NULL COMMENT ‘商店ID’， `goods_id` int（11） NOT NULL COMMENT ‘物品ID’， `pay_type` tinyint（1） NOT NULL COMMENT ‘支付方式’， `price` decimal（10，2） NOT NULL COMMENT ‘物品價格’， `comment` varchar（4000） DEFAULT NULL， PRIMARY KEY （`id`）， UNIQUE KEY `shop_id` （`shop_id`，`goods_id`）， KEY `price` （`price`）， KEY `pay_type` （`pay_type`）， KEY `idx_comment` （`comment`（255））） ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8 COMMENT=‘商店物品表’

如下情況，使用pay_type索引：

mysql

>

explain

select

*

from

shop

where

pay_type

=

2

order

by

id\G

***************************

1。

row

***************************

id

：

1

select_type

：

SIMPLE

table

：

shop

type

：

ref

possible_keys

：

pay_type

key

：

pay_type

key_len

：

1

ref

：

const

rows

：

9999

Extra

：

Using

where

如下情況，雖然使用shop_id索引，但是無法使用索引做排序，EXPLAIN出現filesort：

mysql

>

explain

select

*

from

shop

where

shop_id

=

2

order

by

id\G

***************************

1。

row

***************************

id

：

1

select_type

：

SIMPLE

table

：

shop

type

：

ref

possible_keys

：

shop_id

key

：

shop_id

key_len

：

4

ref

：

const

rows

：

1

Extra

：

Using

where

；

Using

filesort

如下情況，當WHERE 條件覆蓋索引shop_id的所有值時，使用索引做排序，EXPLAIN沒有filesort：

mysql

>

explain

select

*

from

shop

where

shop_id

=

2

and

goods_id

=

2

order

by

id\G

***************************

1。

row

***************************

id

：

1

select_type

：

SIMPLE

table

：

shop

type

：

const

possible_keys

：

shop_id

key

：

shop_id

key_len

：

8

ref

：

const

，

const

rows

：

1

Extra

：

索引和鎖

索引可以讓查詢鎖定更少的行。如果你的查詢從不訪問那些不需要的行，那麼就會鎖定更少的行，從兩個方面來看這對效能都有好處。首先，雖然InnoDB的行鎖效率很高，記憶體使用也很少，但是鎖定行的時候仍然會帶來額外開銷；其次，鎖定超過需要的行會增加鎖爭用並減少併發性。

InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數，從而減少鎖的數量。但這隻有當InnoDB在儲存引擎層能夠過濾掉所有不需要的行時才有效。如果索引無法過濾掉無效的行，那麼在InnoDB檢索到資料並返回給伺服器層以後，MySQL伺服器才能應用 where子句。這時已經無法避免鎖定行了：InnoDB已經鎖住這些行，到適當的時候才釋放。在MySQL5。1及更新的版本中，InnoDB可以在伺服器端過濾掉行後就釋放鎖。

下面的例子再次使用Sakila很好的解釋這些情況：

圖1 索引和鎖（1）

這條查詢只返回2~4行資料，實際上獲取1~4行排他鎖。InnoDB鎖住第1行，因為MySQL為該查詢選擇的執行計劃是索引範圍掃描：

mysql

>

explain

select

actor_id

from

actor

where

actor_id

<

5

and

actor_id

<>

1

FOR

UPDATE\G

***************************

1。

row

***************************

id

：

1

select_type

：

SIMPLE

table

：

actor

type

：

range

possible_keys

：

PRIMARY

key

：

PRIMARY

key_len

：

2

ref

：

NULL

rows

：

3

Extra

：

Using

where

；

Using

index

換句話說，底層儲存引擎的操作是“從索引的開頭獲取滿足條件

actor_id < 5

的記錄”，伺服器並沒有告訴InnoDB可以過濾第1行的WHERE 條件。Explain的Extra出現“Using Where”表示MySQL伺服器將儲存引擎返回行以後再應用WHERE 過濾條件。

我們來證明第1行確實是被鎖定，保持這個終端連結不關閉，然後我們開啟另一個終端。如圖2，這個查詢會掛起，直到第1個事務釋放第1行的鎖。

圖2 索引和鎖（2）

按照這個例子，即使使用索引，InnoDB也可能鎖住一些不需要的資料。如果不能使用索引查詢和鎖定行的話，結果會更糟。MySQL會全表掃描並鎖住所有的行，而不管是不是需要。