本文通過不同索引砾脑，不同場(chǎng)景下幼驶，展示了導(dǎo)致索引失效的SQL，幫助大家以后更有效的使用索引查詢拦止。
一县遣、準(zhǔn)備工作
- 創(chuàng)建一張表
t_index，腳本如下：
CREATE TABLE `t_index` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表記錄標(biāo)識(shí)號(hào)汹族，數(shù)據(jù)庫(kù)主鍵萧求，不用于實(shí)際業(yè)務(wù)',
`key1` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段1',
`key2` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段2',
`key3` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段3',
`del_flag` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '刪除標(biāo)志。0未刪除顶瞒，1刪除夸政。',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄修改時(shí)間',
PRIMARY KEY (`id`),
KEY `idx_create_time` (`create_time`),
KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='索引表';
二、普通索引
1.準(zhǔn)備工作
- 在字段
key1上創(chuàng)建索引榴徐，腳本如下：
ALTER TABLE `t_index` ADD INDEX idx_key1(key1);
2.正常走索引情況
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 = '1';
- 查看執(zhí)行計(jì)劃
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1';
-
結(jié)果顯示走索引查詢守问，如下圖
3.如下場(chǎng)景會(huì)導(dǎo)致查詢走全表查詢匀归，不走索引
（1）查詢條件使用不等式
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 <> '1';
-
查看執(zhí)行計(jì)劃，結(jié)果顯示全表掃描耗帕，如下圖
總結(jié)：不等式
<>或!=會(huì)導(dǎo)致索引失效
（2）查詢條件類型不一致
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 = 1;
-
查看執(zhí)行計(jì)劃穆端，結(jié)果顯示全表掃描，如下圖
總結(jié)：字段
key1為字符串仿便，傳入的值為數(shù)字類型体啰，會(huì)導(dǎo)致索引失效
（3）查詢條件使用函數(shù)計(jì)算
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 + 1 = 1;
SELECT * FROM `t_index` WHERE CHAR_LENGTH(key1) = 1;
-
查看執(zhí)行計(jì)劃，結(jié)果顯示全表掃描嗽仪，如下圖
總結(jié)：查詢條件包含
x+1荒勇、x-1、CHAR_LENGTH(x)等函數(shù)會(huì)導(dǎo)致索引失效
（4）模糊查詢
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 LIKE '3';
SELECT * FROM `t_index` WHERE key1 LIKE '%3';
SELECT * FROM `t_index` WHERE key1 LIKE '3%';
-
查看執(zhí)行計(jì)劃闻坚，如下圖
總結(jié)：模糊查詢查詢條件下沽翔，只有全匹配和前綴匹配的模糊查詢才會(huì)走索引（也就是上面的第一、三個(gè)腳本會(huì)走索引）
三窿凤、復(fù)合索引
1.準(zhǔn)備工作
- 刪除剛才的索引仅偎，在字段
key1, key2, key3上創(chuàng)建復(fù)合索引，腳本如下：
DROP INDEX idx_key1 ON `t_index`;
ALTER TABLE `t_index` ADD INDEX idx_key123(key1, key2, key3);
2.正常情況
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
- 查看執(zhí)行計(jì)劃
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
-
結(jié)果顯示走索引查詢卷玉，如下圖
3.如下場(chǎng)景會(huì)導(dǎo)致查詢走全表查詢哨颂，不走索引
（1）查詢條件使用不等式
- 查詢腳本，只要有一個(gè)條件含有不等式相种，都不會(huì)走索引
SELECT * FROM `t_index` WHERE key1 <> '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 <> '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 <> '3';
-
查看執(zhí)行計(jì)劃，結(jié)果顯示全表掃描品姓，三種情況結(jié)果一樣寝并，如下圖
總結(jié)：邏輯普通索引
（2）查詢條件類型不一致
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 = 1 AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = 2 AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = 3;
-
查看執(zhí)行計(jì)劃，結(jié)果顯示（第一個(gè)參數(shù)類型不一致走全表掃描腹备，第二個(gè)參數(shù)類型不一致衬潦，索引僅僅能使用第一列，第三個(gè)參數(shù)類型不一致植酥，索引能使用前兩列）镀岛，如下圖
總結(jié)：從第一個(gè)查詢條件開始，第N個(gè)參數(shù)類型不一致友驮，索引能使用前N-1列
（3）查詢條件使用函數(shù)計(jì)算
- 查詢腳本
SELECT * FROM `t_index` WHERE key1 + 1 = '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 + 1 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 + 1 = '3';
-
查看執(zhí)行計(jì)劃漂羊，結(jié)果同上（第一個(gè)參數(shù)類型不一致走全表掃描，第二個(gè)參數(shù)類型不一致卸留，索引僅僅能使用第一列走越，第三個(gè)參數(shù)類型不一致，索引能使用前兩列）耻瑟，如下圖
總結(jié)：邏輯普通索引
（4）不使用索引首列當(dāng)查詢條件
- 查詢腳本
SELECT * FROM `t_index` WHERE key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key2 = '2';
SELECT * FROM `t_index` WHERE key3 = '3';
-
查看執(zhí)行計(jì)劃旨指，結(jié)果顯示（都不會(huì)走索引）赏酥，三種情況結(jié)果一樣，如下圖
總結(jié)：查詢條件不使用復(fù)合索引的首列谆构，均會(huì)導(dǎo)致索引失效
本文完裸扶。
