不啰嗦，直接入正題。問題是這樣的。請(qǐng)問下面的sql語句，要想加快查詢速度，該怎么創(chuàng)建索引?以下，以mysql數(shù)據(jù)庫為準(zhǔn)。

select * from test where a=? and b>? order by c limit 0,100 

結(jié)果可能會(huì)出乎你的意料。我們首先準(zhǔn)備一下運(yùn)行環(huán)境，然后按照最左前綴原則和explain關(guān)鍵字來進(jìn)行驗(yàn)證。結(jié)果真是顛覆了xjjdog多年的認(rèn)知。

準(zhǔn)備階段

為了進(jìn)行驗(yàn)證，我們創(chuàng)建一個(gè)簡(jiǎn)單的數(shù)據(jù)表。里面有a、b、c三個(gè)簡(jiǎn)單的int字段。

CREATE TABLE `test` ( 
  `id` int(11) NOT NULL, 
  `a` int(11) DEFAULT NULL, 
  `b` int(11) DEFAULT NULL, 
  `c` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

接下來，寫一個(gè)簡(jiǎn)單的存儲(chǔ)過程，來插入10w條數(shù)據(jù)。等待大約1分鐘，數(shù)據(jù)插入完畢。

DROP PROCEDURE IF EXISTS test_initData; 
DELIMITER $ 
CREATE PROCEDURE test_initData() 
BEGIN 
    DECLARE i INT DEFAULT 1; 
    WHILE i<=100000 DO 
        INSERT INTO test(id,a,b,c) VALUES(i,i*2,i*3,i*4); 
        SET ii = i+1; 
    END WHILE; 
END $ 
CALL test_initData(); 

由于mysql有最左前綴原則，我們對(duì)abc三列進(jìn)行了全排列，創(chuàng)建了6個(gè)索引。這6個(gè)索引涵蓋了所有的根據(jù)abc查詢的情況。

create INDEX idx_a_b_c on test(a,b,c); 
create INDEX idx_a_c_b on test(a,c,b); 
create INDEX idx_b_a_c on test(b,a,c); 
create INDEX idx_b_c_a on test(b,c,a); 
create INDEX idx_c_a_b on test(c,a,b); 
create INDEX idx_c_b_a on test(c,b,a); 

使用Explain進(jìn)行驗(yàn)證

1. 自動(dòng)選用索引

explain select * from test where a>10 and b >10  order by c 

首先，我們拿上面的sql語句進(jìn)行驗(yàn)證。結(jié)果發(fā)現(xiàn)，查詢使用了索引idx_a_b_c，只用到了前綴a，b。而extra部分，則用到了filesort，也就是性能非常差的方式。

我們嘗試換一下查詢參數(shù)的位置。

explain select * from test where c>10 and b >10   order by a 

這次索引自動(dòng)選擇了idx_b_a_c，但依然使用的filesort，查詢效果是一樣的。按照上面的邏輯，不是應(yīng)該選擇idx_b_c_a么?

2. 指定索引

接下來使用force index方式，強(qiáng)制指定索引。

這里直接給出結(jié)果，就是下面的sql。

explain select * from test 
   FORCE INDEX(idx_c_b_a)    where a>10 and b >10  order by c 

結(jié)果如下。

我們使用force index來指定使用的索引。這次效果非常好，顯示使用了index，使用了where，只在索引上就完成了操作。但掃描的行數(shù)卻增加了。

但是，這與我們的經(jīng)驗(yàn)是相悖的。idx_c_b_a的索引，是在字段(c,b,a)上創(chuàng)建的。按照最左原則，支持的搜索條件有：c,cb,cba。在這個(gè)例子中，order by后面的參數(shù)，卻被當(dāng)作了前綴的頭部信息。

我們刪掉其他索引，只留下idx_c_b_a，然后去掉force index部分。結(jié)果發(fā)現(xiàn)，mysql現(xiàn)在能夠自動(dòng)的選擇索引了。

再看另外一種情況，order by上有兩個(gè)參數(shù)。

explain select * from test 
   FORCE INDEX(idx_b_c_a) 
   where a>10   order by b,c 

結(jié)果如上，使用idx_b_c_a，不走filesort。其他索引都不是最優(yōu)。

3. explain部分返回值意義

我們得出上面的結(jié)論，是根據(jù)mysql自己提供的explain工具。這個(gè)工具能夠輸出一些有用的信息。下面是相關(guān)的部分返回值的意義。

(1) select_type

表示SELECT的類型，常見的取值有：

• SIMPLE 簡(jiǎn)單表，不使用表連接或子查詢。
• PRIMARY 主查詢，即外層的查詢。
• UNION UNION中的第二個(gè)或者后面的查詢語句。
• SUBQUERY 子查詢中的第一個(gè)。

(2) type

表示MySQL在表中找到所需行的方式，或者叫訪問類型。常見訪問類型如下，從下到上，性能越來越差。

• system,const 表只有一行記錄(等于系統(tǒng)表)，這是const類型的特列。
• eq_ref 唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。
• ref 非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體。
• range 只檢索給定范圍的行，使用一個(gè)索引來選擇行，key列顯示使用了哪個(gè)索引。這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)，不用掃描全部索引。
• index Full Index Scan，Index與All區(qū)別為index類型只遍歷索引樹。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。
• all 全表掃描，性能最差

(3) Extra

• using index：表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問了表的數(shù)據(jù)行，效率不錯(cuò)。如果同時(shí)出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找;如果沒有同時(shí)出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作。
• using filesort：說明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”。
• using temporary：使用了用臨時(shí)表保存中間結(jié)果，mysql在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于排序order by和分組查詢group by。

End

可以看到，在我們創(chuàng)建了多個(gè)索引的時(shí)候，mysql的查詢優(yōu)化，并不一定能夠進(jìn)行智能的解析、用到最優(yōu)的方式，需要使用force index指定索引。

mysql中的索引，主要就用在where條件中和排序動(dòng)作中。分兩種情況。

• 先過濾，再排序，會(huì)用到過濾條件中的索引參數(shù)，但是排序會(huì)使用較慢的外部排序。因?yàn)檫@個(gè)結(jié)果集是經(jīng)過過濾的，并沒有什么索引參與。
• 先排序，再過濾，可以使用同一個(gè)索引，排序的優(yōu)先級(jí)高于過濾的優(yōu)先級(jí)。選擇合適的索引，在過濾的同時(shí)就把這個(gè)事給辦了。但是掃描的行數(shù)會(huì)增加。

我想，mysql并不能夠了解到這兩個(gè)過程，到底誰快誰慢，于是選了一個(gè)最通用的方式，直接選用了第一種。甚至在索引非常多的時(shí)候，直接暈菜了。索引建多了，你可能間接把mysql給害了。這是現(xiàn)象，至于深層次的原因，歡迎讀過mysql相關(guān)源碼的給解釋一下。

這對(duì)經(jīng)常變換字段進(jìn)行排序的代碼來說，并不是一個(gè)好的信號(hào)。考慮到程序的穩(wěn)定性，我想應(yīng)該要盡量減少where條件過濾后的結(jié)果集。這種情況下，創(chuàng)建一個(gè)(a，b)的聯(lián)合索引，或許是一個(gè)折衷的方式。