建站
咨詢
售后
建站咨詢
新聞中心
前置條件
數(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é)：如果索引列參與了計算，不會命中索引。像這種情況可以變換一下等式，把運算放到等號右邊，就會命中索引。
EXPLAIN SELECT * FROM `user` WHERE id = 2 - 1
查詢條件帶or
上面所說的最左匹配原則中，只要查詢語句中包含username就會走索引，但如果我們把and條件換成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進行查詢，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。
一般來說，至少要達到range級別，當(dāng)type=index或者ALL時，表示效率低下，需要優(yōu)化。我們看到只查詢一條記錄時，不但走了索引，而且type=const，效率較高。我們擴大下范圍：
SELECT * FROM test WHERE id IN (1,2,3)
從執(zhí)行結(jié)果上看，也走了索引，但此時type=range，效率降低了。再擴大下范圍：
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行，其實這正是我們需要的。
但如果我們加個排序，變成這樣：
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呢?
因為在有多個索引的情況下，mysql優(yōu)化器一般會通過比較掃描行數(shù)、是否需要臨時表以及是否需要排序等，來作為選擇索引的判斷依據(jù)。在這個例子中，優(yōu)化器看到根據(jù)b來進行排序，認為使用b效率更高，所以走了索引b。實際上，我們應(yīng)該使用a索引。
這種情況可以使用force index來強制使用索引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行。
當(dāng)前題目：聊聊索引失效的經(jīng)典場景
文章來源：http://www.5511xx.com/article/dpsipoo.html
