前置條件

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

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

然后用explain來(lái)解析一下有沒(méi)有走索引。

最左匹配原則

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

首先執(zhí)行以下幾個(gè)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)都走了索引。

接下來(lái)再執(zhí)行幾個(gè)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)沒(méi)有走索引。

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

索引列上有計(jì)算

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

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

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

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

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

查詢(xún)條件帶or

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

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

可以看到并沒(méi)有走索引，而是全表掃描，所以在帶有or的查詢(xún)語(yǔ)句中，索引將失效，除非所有條件都帶有索引。也就是說(shuō)，username有索引，jobno也必須要建一個(gè)索引才會(huì)生效。

like查詢(xún)

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

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

使用前置%：

使用后置%:

前后都用%：

結(jié)論：模糊查詢(xún)中，只要使用了%都不會(huì)走索引，不使用%號(hào)時(shí)可以走索引。

字段類(lèi)型不同

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

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

肯定是會(huì)走索引的，但如果我們不用引號(hào)引用起來(lái)，而是：

SELECT * FROM `user` WHERE username = 100

會(huì)發(fā)現(xiàn)沒(méi)有走索引：

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

查詢(xún)語(yǔ)句包含in

這種就比較特殊了，走不走索引不是絕對(duì)的，跟所查詢(xún)的數(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í)行以下語(yǔ)句：

SELECT * FROM test WHERE id IN (1)

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

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

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

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

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

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

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

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

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

mysql選錯(cuò)索引

我們?cè)俳ㄒ粋€(gè)demo表來(lái)說(shuō)明這種情況：

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í)這正是我們需要的。

但如果我們加個(gè)排序，變成這樣：

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ù)，這樣效率顯然會(huì)降低，為什么會(huì)走索引b呢?

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

這種情況可以使用force index來(lái)強(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;

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