前置條件

數(shù)據(jù)庫版本為5.7.17。

創(chuàng)建一張user表，預(yù)置500w條數(shù)據(jù)，每個字段的值都是前綴加上從0開始計數(shù)的數(shù)字，累加到5000000，如圖：

然后用explain來解析一下有沒有走索引。

最左匹配原則

我們首先建一個組合索引，username，jobno，company三個字段：

首先執(zhí)行以下幾個sql：

EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND jobno = 'jn1000000' AND company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND jobno = 'jn1000000';
EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' AND username = 'cxj1000000';
EXPLAIN SELECT * FROM `user` WHERE username = 'cxj1000000' AND company = 'com1000000';

發(fā)現(xiàn)都走了索引。

接下來再執(zhí)行幾個sql：

EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' AND company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE company = 'com1000000';
EXPLAIN SELECT * FROM `user` WHERE jobno = 'jn1000000' ;

發(fā)現(xiàn)沒有走索引。

結(jié)論：最左匹配原則要求查詢的sql語句中，必須包含最左邊的字段，在username，jobno，company的組合索引中，username是最左邊的字段，那么查詢的sql語句中的where條件中，必須包含username字段，而與sql語句中username的使用順序無關(guān)。

索引列上有計算

根據(jù)主鍵ID查詢，毫無疑問會走主鍵索引，但如果像下面這種：

EXPLAIN SELECT * FROM `user` WHERE id + 1 = 2

可以看到?jīng)]有走索引。

總結(jié)：如果索引列參與了計算，不會命中索引。像這種情況可以變換一下等式，把運(yùn)算放到等號右邊，就會命中索引。

EXPLAIN SELECT * FROM `user` WHERE id = 2 - 1

查詢條件帶or

上面所說的最左匹配原則中，只要查詢語句中包含username就會走索引，但如果我們把a(bǔ)nd條件換成or，即：

SELECT * FROM `user` WHERE username = 'cxj13' OR jobno = 'jn13'

可以看到并沒有走索引，而是全表掃描，所以在帶有or的查詢語句中，索引將失效，除非所有條件都帶有索引。也就是說，username有索引，jobno也必須要建一個索引才會生效。

like查詢

在username字段上新建索引user_idx_normal_username，不使用%模糊查詢：

SELECT * FROM `user` WHERE username LIKE 'cxj'

使用前置%：

使用后置%:

前后都用%：

結(jié)論：模糊查詢中，只要使用了%都不會走索引，不使用%號時可以走索引。

字段類型不同

還是使用username進(jìn)行查詢，username的字段類型是字符串類型，我們知道以下語句：

SELECT * FROM `user` WHERE username = 'cxj13'

肯定是會走索引的，但如果我們不用引號引用起來，而是：

SELECT * FROM `user` WHERE username = 100

會發(fā)現(xiàn)沒有走索引：

結(jié)論：字符串的索引字段在查詢時數(shù)據(jù)需要用引號引用，否則索引失效。

查詢語句包含in

這種就比較特殊了，走不走索引不是絕對的，跟所查詢的數(shù)據(jù)量跟總表數(shù)據(jù)量的比例有關(guān)。

我們新建一張表：

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `dept` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

然后往這張表里插入10條數(shù)據(jù)：

首先執(zhí)行以下語句：

SELECT * FROM test WHERE id IN (1)

執(zhí)行結(jié)果：

可以看到走了索引，需要注意的是type，我們知道type表示所走索引的一個效率值，它的結(jié)果的好壞依次為：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

一般來說，至少要達(dá)到range級別，當(dāng)type=index或者ALL時，表示效率低下，需要優(yōu)化。我們看到只查詢一條記錄時，不但走了索引，而且type=const，效率較高。我們擴(kuò)大下范圍：

SELECT * FROM test WHERE id IN (1,2,3)

從執(zhí)行結(jié)果上看，也走了索引，但此時type=range，效率降低了。再擴(kuò)大下范圍：

SELECT * FROM test WHERE id IN (1,2,3,4,5)

可以看到?jīng)]有走索引了，type=ALL，全表掃描。

結(jié)論：mysql優(yōu)化器會根據(jù)所查詢的數(shù)據(jù)量決定是走索引還是全表掃描。

mysql選錯索引

我們再建一個demo表來說明這種情況：

CREATE TABLE `demo` (
  `id` int(11) NOT NULL,
  `a` int(11) NOT NULL default 0,
  `b` int(11) NOT NULL default 0,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

然后插入100w條數(shù)據(jù)，執(zhí)行以下sql:

select * from demo where (a between 1000 and 2000) and (b between 50000 and 100000) limit 1

看一下執(zhí)行情況：

可以看到走了索引a，且只掃描了1001行，其實(shí)這正是我們需要的。

但如果我們加個排序，變成這樣：

SELECT * FROM demo WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1

再看下執(zhí)行情況：

可以看到走了索引b，并且掃描了5w多行數(shù)據(jù)，這樣效率顯然會降低，為什么會走索引b呢?

因?yàn)樵谟卸鄠€索引的情況下，mysql優(yōu)化器一般會通過比較掃描行數(shù)、是否需要臨時表以及是否需要排序等，來作為選擇索引的判斷依據(jù)。在這個例子中，優(yōu)化器看到根據(jù)b來進(jìn)行排序，認(rèn)為使用b效率更高，所以走了索引b。實(shí)際上，我們應(yīng)該使用a索引。

這種情況可以使用force index來強(qiáng)制使用索引a。

SELECT * FROM demo FORCE INDEX(a) WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1;

可以看到，查詢走了索引a，并且只掃描了1001行。