大部分情況下，尤其是記錄數(shù)量較少的情況下Mysql總是能正常運(yùn)轉(zhuǎn)的很好，但不可避免的，隨著數(shù)據(jù)庫(kù)記錄數(shù)的增長(zhǎng)以及SQL語(yǔ)句越來(lái)越復(fù)雜，總會(huì)有一些實(shí)際效果與數(shù)據(jù)庫(kù)或SQL設(shè)計(jì)人員理解相違背的情況，這就需要開(kāi)發(fā)者對(duì)Mysql的原理和存在的問(wèn)題有一個(gè)基本的認(rèn)識(shí)。本文主要探討了Mysql索引的使用和相關(guān)知識(shí)，這些知識(shí)并不復(fù)雜，不需要專(zhuān)業(yè)的數(shù)據(jù)庫(kù)學(xué)習(xí)經(jīng)驗(yàn)就能搞明白，理解了這些可以幫助開(kāi)發(fā)人員更好的進(jìn)行數(shù)據(jù)庫(kù)索引設(shè)計(jì)和SQL查詢語(yǔ)句的編寫(xiě)。

1. Mysql 是如何使用索引的

• MyISAM不支持事務(wù)，而Innodb支持。
• MyISAM索引和數(shù)據(jù)的存儲(chǔ)是分開(kāi)的（不同的文件），索引中最終檢索到的是數(shù)據(jù)的物理地址偏移量。而InnoDB中，索引段和數(shù)據(jù)段在同一個(gè)文件中的不同段，查到索引后可以直接取出數(shù)據(jù)。
• MyISAM是非聚集索引，而Innodb則是聚集索引。

所謂聚集索引是指索引和數(shù)據(jù)的邏輯排列順序與實(shí)際物理存儲(chǔ)順序一致，新華字典就是典型的聚集索引，字（葉子索引）和釋意（數(shù)據(jù)）靠在一起，且按一定順序排列的。而“非聚集索引”則相反，索引單獨(dú)放在一塊區(qū)域，并且葉子節(jié)點(diǎn)存放的是數(shù)據(jù)的地址偏移量。

1.1 MyISAM存儲(chǔ)引擎

?

主索引是指主鍵索引，鍵值不可能重復(fù)；輔助索引則是普通索引，鍵值可能重復(fù)。

select * from table_name where id = 3

其中id為主鍵，那么首先檢索的是索引，索引中經(jīng)過(guò)2層查找，找到了索引為3的節(jié)點(diǎn)，值為0xABAB，代表了從.myd文件中偏移量為0xABAB的地方開(kāi)始讀取一行的數(shù)據(jù)。輔助索引對(duì)應(yīng)普通索引，存在相同的鍵值。

1.2 Innodb存儲(chǔ)引擎?

 select * from table_name where id = 3

其中id為主鍵，那么首先檢索的是索引段，索引段中查找下個(gè)索引范圍是通過(guò)地址偏移隨機(jī)訪問(wèn)來(lái)實(shí)現(xiàn)的（這個(gè)步驟還是高效的），查找到對(duì)應(yīng)葉子索引節(jié)點(diǎn)后，需要順序的遍歷和檢索該葉子節(jié)點(diǎn)找到對(duì)應(yīng)的索引值對(duì)應(yīng)的節(jié)點(diǎn)（這個(gè)步驟是遍歷，比較耗時(shí)），就可以立馬讀出數(shù)據(jù)了。

Innodb的表存儲(chǔ)結(jié)構(gòu)由段、簇（區(qū)）、頁(yè)組成，一個(gè)段由若干簇組成，一個(gè)簇默認(rèn)有64頁(yè)，每頁(yè)16KB。

1.3 Mysql索引選取規(guī)則

1. 尋找可能的（可選）索引：根據(jù)用戶的WHERE條件，查看每個(gè)字段是否匹配某個(gè)索引，如果匹配，就把這個(gè)索引加入待選列表中。所謂字段匹配索引有兩種情況：1）某個(gè)查詢字段上建立了單列索引；2）某個(gè)查詢字段按照最左匹配原則（下文有詳細(xì)描述）匹配了某個(gè)組合索引,即為該組合索引的第一列。3）某幾個(gè)查詢字段按照最左匹配原則匹配了某個(gè)組合索引?？蛇x索引列表可使用EXPLAIN查看（possible keys）[3]。待選列表如果為空，GOTO 3。
2. 索引擇優(yōu)算法：Mysql的索引擇優(yōu)算法很復(fù)雜，一般來(lái)說(shuō)有這幾個(gè)影響因素：1）索引對(duì)應(yīng)的掃描行數(shù)，在沒(méi)有Order by的情況下，一般掃描行數(shù)少的索引會(huì)被選擇；2）查詢語(yǔ)句中有Order By或者group by時(shí)，如果不使用Order By后的字段做索引的話，filesort（對(duì)應(yīng)索引排序，索引排序很快，而filesort則需要對(duì)結(jié)果集進(jìn)行實(shí)時(shí)排序，所以很慢）會(huì)被使用，這時(shí)候Mysql給filesort的負(fù)權(quán)重很高，很容易導(dǎo)致Mysql放棄最優(yōu)索引（哪怕該索引估計(jì)掃描行數(shù)比實(shí)際使用的索引對(duì)應(yīng)的掃描行數(shù)小很多），轉(zhuǎn)而使用Order By之后的索引字段。我們?cè)谏a(chǎn)中遇到過(guò)這樣的情況，部分文獻(xiàn)也有記載[6]；3）Limit 限定也會(huì)影響索引的使用，甚至Limit后的值也會(huì)影響索引的使用（有時(shí)候確實(shí)會(huì)令人費(fèi)解）；4）話不能說(shuō)滿，官網(wǎng)文檔寫(xiě)的實(shí)在含糊，所以這里不敢打包票沒(méi)有其他影響因素了。如果優(yōu)選的掃描索引不為空，GOTO 4。
3. 如果沒(méi)找到可用索引的話，再考察查詢字段，也就是Select之后Where之前的那些字段。尋找查詢字段所匹配的索引，并得到一個(gè)可用索引結(jié)果集。根據(jù)最小估算掃描行數(shù)優(yōu)先原則，可以得到最優(yōu)的索引。如果可用索引結(jié)果集仍然為空，那么就會(huì)使用全表掃描(Full table scan)。
4. 如果根據(jù)最終選擇的索引估算出的掃描行數(shù)占據(jù)了表的很大一部分比例，那么Mysql優(yōu)化器可能會(huì)放棄使用該索引，而退化為使用全表掃描。這是因?yàn)槭褂盟饕谟行┣闆r下并不高效，比如索引出來(lái)的數(shù)據(jù)量很大，需要頻繁的改變文件讀取指針去獲取數(shù)據(jù)塊，可能效果還不如從頭到位把整個(gè)表都掃描一邊，也省去了去查找索引和頻繁重定向讀取指針（尤其在磁盤(pán)存儲(chǔ)器[4]上）帶來(lái)的開(kāi)銷(xiāo)。

5. 如果到這步Mysql搜索優(yōu)化器仍然決定用某個(gè)索引，那么就會(huì)在實(shí)際查詢時(shí)使用該索引了。這個(gè)索引也是EXPLAIN分析語(yǔ)句結(jié)果集中key的值。

關(guān)于使用索引隨機(jī)讀取大量記錄和順序讀取大量記錄之間的取舍問(wèn)題，本文并沒(méi)有去研究Mysql在優(yōu)化的時(shí)候是否考慮到了存儲(chǔ)器的類(lèi)型，比如是磁盤(pán)還是SSD，對(duì)于SSD這種高效隨機(jī)存儲(chǔ)器來(lái)說(shuō)，頻繁重定向讀取指針幾乎不耗時(shí)。如果沒(méi)有考慮到，而只是給這種頻繁讀取操作預(yù)設(shè)了一個(gè)成本常量（消耗的時(shí)間）參與估算的話，可能優(yōu)化結(jié)果并不恰當(dāng)。

1.4 何時(shí)會(huì)全表掃描

• 1.目標(biāo)數(shù)據(jù)表太小了，再去查找索引（key lookup）太麻煩了（有點(diǎn)殺雞焉用牛刀的即視感）。這通常發(fā)生在10行都不到的數(shù)據(jù)表，并且每行很短的情況。（注：10這個(gè)數(shù)字不可靠，這里只是感性的說(shuō)了個(gè)數(shù)字，可能小幾十行的數(shù)據(jù)仍然會(huì)觸發(fā)全表掃描。）
• 2.查詢條件中的字段（WHERE后）沒(méi)有匹配到索引的情況。（也不是說(shuō)匹配不到就一定會(huì)全表掃描，見(jiàn)下文默認(rèn)索引選擇算法）
• 3.查詢條件中的字段與某個(gè)常量比較時(shí)（就比如where age > 8)，并且使用這個(gè)常量值與對(duì)應(yīng)索引篩選出的記錄數(shù)占了總數(shù)的大部分。優(yōu)化器認(rèn)為掃這么大的數(shù)據(jù)還不如掃全表了，所以選擇了掃描全表。大部分怎么定義恐怕只有Mysql開(kāi)發(fā)者才知道，官網(wǎng)也并沒(méi)有給出具體數(shù)值。
• 4.查詢語(yǔ)句匹配到的索引對(duì)應(yīng)的基數(shù)太小（對(duì)應(yīng)SHOW INDEX FROM table_name結(jié)果中的Cardinality?)，并且此時(shí)又有其他列上的查詢條件（比如:select * from user where user_status > 0 and username =’tommy’）。所謂基數(shù)就是表中某列所有值的取值種數(shù)，比如一張表有5行，某一列對(duì)應(yīng)的值分別為：1,2,3,3,2。那么該列基數(shù)就是3，因?yàn)橐还灿腥N取值：1,2,3。基數(shù)小，意味著該索引中每個(gè)索引值對(duì)應(yīng)的目標(biāo)記錄數(shù)很大，在這個(gè)索引值對(duì)應(yīng)的記錄數(shù)中再去一個(gè)個(gè)的檢查其他列上的條件是否滿足，整個(gè)過(guò)程總體的查找速度還未必有全表掃描來(lái)得快。

經(jīng)測(cè)試，第3點(diǎn)當(dāng)且僅當(dāng)比較符號(hào)為非等于時(shí)才生效。如果使用了等于號(hào)，那么只要該列有匹配的索引，一定會(huì)命中，哪怕基數(shù)為1。所以當(dāng)查詢條件中有基數(shù)小的列時(shí)，某個(gè)索引值的條件只是從等于號(hào)改成小于號(hào)，就可能從使用索引退化到掃描全表。查看某個(gè)SQL語(yǔ)句是否使用某個(gè)索引靠譜的做法只有一個(gè)——使用Explain語(yǔ)句分析SQL。

我們?cè)赾ategory上創(chuàng)建了一個(gè)索引，然后我們使用以下SQl語(yǔ)句進(jìn)行查找:

Select * from Animal where category > 1 and name = "asaf"

這時(shí)候category對(duì)應(yīng)的索引數(shù)據(jù)會(huì)如下所示：

2. 多列索引(組合索引)

2.1 組合索引的數(shù)據(jù)構(gòu)成

那么索引數(shù)據(jù)就會(huì)像一樣：

注意：這是官方文檔上打的一個(gè)比方。實(shí)際查詢條件可能是”>、<、<=”這樣的范圍比較符號(hào)，需要對(duì)每一列作比較，所以不會(huì)真正的連接成一個(gè)字符串，這里只是一個(gè)形象的比喻，告知大家組合索引和普通索引在數(shù)據(jù)構(gòu)成上其實(shí)沒(méi)啥區(qū)別。

2.2 組合索引的一種替代方案

SELECT * FROM tbl_name  WHERE hash_col=MD5(CONCAT(val1,val2))  AND col1=val1 AND col2=val2;

很顯然，這種方法只適用于精確匹配的情況，如果用到了范圍比較符號(hào)（>,<,>=等），那就無(wú)法使用了。

2.3 組合索引的命中規(guī)則——最左匹配原則

下面我們?cè)賮?lái)看看哪些查詢會(huì)使用到我們定義的組合索引。假設(shè)一張表的定義如下：

CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX name (last_name,first_name));

SELECT * FROM test WHERE last_name='Widenius';SELECT * FROM test  WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius'  AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test  WHERE last_name='Widenius'  AND first_name >='M' AND first_name < 'N';

如果一個(gè)表有一個(gè)組合索引，那么任何符合**最左匹配原則**的查詢條件都會(huì)觸發(fā)優(yōu)化器使用該索引進(jìn)行數(shù)據(jù)查找。比如，有一個(gè)三列的組合索引(col1, col2, col3),那么當(dāng)查詢：where col1=xxx?、where col1=xxx and col2=xxx、where col1=xxx and col2=xxx and col3=xxx時(shí)，都會(huì)觸發(fā)該索引。

最左匹配原則——在對(duì)查詢條件中的字段進(jìn)行組合索引的匹配時(shí)，只考慮匹配其前N個(gè)字段，比如前一個(gè)（第一個(gè)）、前2個(gè)、前3個(gè)字段等。其他情況視為未匹配。

1. SELECT * FROM tbl_name WHERE col1=val1;2. SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;3. SELECT * FROM tbl_name WHERE col2=val2;4. SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

**?默認(rèn)索引選擇算法**——當(dāng)查詢語(yǔ)句的搜索條件沒(méi)有命中任何索引時(shí)，Mysql索引優(yōu)化器會(huì)考量查詢語(yǔ)句中的目標(biāo)字段（select后面，where前面的部分），目標(biāo)字段除去主鍵外，如果恰好是某個(gè)索引（包括組合索引）對(duì)應(yīng)列的子集，那么該索引也會(huì)被使用。如果滿足的索引有多個(gè)，將會(huì)使用索引記錄數(shù)最少的索引。這個(gè)算法在[3]中得到了旁證。

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius' OR first_name='Michael';

3. 索引設(shè)計(jì)建議

1. 索引不是定義的越多越好，對(duì)于查詢條件比較多的情況，避免為每個(gè)字段創(chuàng)建索引，只需要?jiǎng)?chuàng)建一個(gè)聯(lián)合索引即可。創(chuàng)建聯(lián)合索引時(shí)，把可能存在單列查詢的那一列放前面。比如業(yè)務(wù)需求要求以下幾種查詢：

   這時(shí)候，組合索引應(yīng)該建成為:
   
   

   這時(shí)候上述三種查詢都會(huì)使用到該索引。

2. 創(chuàng)建聯(lián)合索引時(shí)，值差異化大的列放在前面，而不是那些取值種類(lèi)很少的列。比如用戶名和用戶狀態(tài)兩列，創(chuàng)建索引的順序應(yīng)當(dāng)是index(用戶名、用戶狀態(tài))，而不是index(用戶狀態(tài)、用戶名)。如果把基數(shù)很小的用戶狀態(tài)放在第一個(gè)，那么如果恰好查詢語(yǔ)句條件是“用戶名=某個(gè)值 AND 用戶狀態(tài)>某個(gè)值”時(shí)，Mysql索引優(yōu)化器很可能根據(jù)用戶狀態(tài)得出要掃描的行數(shù)太多，退化為全表掃描，而后面的精確匹配用戶名的條件就沒(méi)用上了。
3. 由于Mysql索引優(yōu)化器的存在，有時(shí)候會(huì)出現(xiàn)很多意向不到不使用索引的情況。所以每次寫(xiě)無(wú)法確定使用哪個(gè)索引的Sql語(yǔ)句時(shí)（尤其是WHERE條件后是>,<等范圍選擇時(shí)），一定要多用EXPLAIN語(yǔ)句進(jìn)行分析。
4. 可以認(rèn)為Mysql在執(zhí)行SQL語(yǔ)句時(shí)，一個(gè)表只可能使用一個(gè)索引（開(kāi)啟了Index Merge的情況除外）。新人在建表的時(shí)候總是會(huì)忽略這個(gè)事實(shí)，從而為很多列單獨(dú)建立了索引，認(rèn)為這樣會(huì)更快。
5. Mysql索引優(yōu)化規(guī)則是一個(gè)官網(wǎng)都沒(méi)說(shuō)清的問(wèn)題，在復(fù)雜SQL的情況下不可避免的會(huì)產(chǎn)生一些事與愿違的情況（已知的影響因素1.1中有提到），導(dǎo)致Mysql很蠢的使用了不該使用的索引（這種情況的確會(huì)存在[6]，這也佐證了Mysql的執(zhí)行計(jì)劃是估算出來(lái)的，并不總是靠譜）。實(shí)際使用中發(fā)現(xiàn)索引使用錯(cuò)誤的情況，可以使用Force Index/Use Index等引導(dǎo)Mysql搜索優(yōu)化器使用某個(gè)索引，這里有一些可選的解決方案有興趣的也可以看看[6]。

參考文獻(xiàn)：

[1]. How MySQL Uses Indexes. https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

[2]. Multiple-Column Indexes, https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

[3]. EXPLAIN Output Format. https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_key

[4]. 磁盤(pán)存儲(chǔ)器. http://www.baike.com/wiki/%E7%A3%81%E7%9B%98%E5%AD%98%E5%82%A8%E5%99%A8

[5]. Avoiding Full Table Scans. https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html

[6]. 7 ways to convince MySQL to use the right index. http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index