注意：MySQL版本對(duì)索引也很重要，不同版本相同場(chǎng)景下锉矢，索引有的版本可能失效藕溅，有的版本可能不失效，本講解版本為 8.0.19
首先我們弄個(gè)表杯矩，造一些數(shù)據(jù)
CREATE TABLE `student` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',
`mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手機(jī)號(hào)',
`age` int DEFAULT NULL COMMENT '年齡',
`gender` varchar(10) DEFAULT NULL COMMENT '性別',
`grade` int DEFAULT NULL COMMENT '年級(jí)',
`create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `UK_icr1qhlwx3lsd0terqn7w65k1` (`name`,`mobile`,`age`) USING BTREE,
KEY `UK_icr1qhlwx3lsd0terqn7w65k2` (`gender`),
KEY `UK_icr1qhlwx3lsd0terqn7w65k3` (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='學(xué)生信息表';
image.png
至于走不走索引栈虚，我們關(guān)注兩個(gè)字段即可
image.png
接下來(lái)我們講講有哪些場(chǎng)景索引會(huì)失效
1、在聯(lián)合索引的場(chǎng)景下史隆，查詢條件不滿足最左匹配原則
根據(jù)最左匹配原則魂务，我們能匹配的組合所索引有哪些？
name | name mobile | name mobile age
只要查詢條件（不論順序位置）能組合成上面的一種就肯定會(huì)跑索引泌射。
跑索引例子
- 跑索引 name
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明';
image.png
- 跑索引 name mobile
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' AND mobile = '156221905631';
image.png
- 跑索引 name mobile age
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' AND age = 18 AND mobile = '156221905631';
image.png
- 跑索引 name mobile age
EXPLAIN SELECT * FROM `student` WHERE age = 18 AND mobile = '156221905631' AND `name` = '小明' AND update_time = '2024-03-14 18:56:14';
image.png
不跑索引例子
- 因?yàn)闆](méi)有 mobile age 組合的索引粘姜，也組合不出對(duì)應(yīng)的索引，所以不跑索引
EXPLAIN SELECT * FROM `student` WHERE age = 18 AND mobile = '156221905631';
image.png
特殊跑索引例子
- 上面明明說(shuō) 沒(méi)有 mobile age 組合的索引熔酷，也組合不出對(duì)應(yīng)的索引孤紧，所以不跑索引。為什么不同查詢字段拒秘，相同的條件号显，這個(gè)就跑索引了呢？
這個(gè)比較特殊翼抠，查詢字段和條件 name, age, mobile 索引都包含了咙轩，不會(huì)產(chǎn)生回表問(wèn)題，通過(guò)索引拿到數(shù)據(jù)比查詢?nèi)砜於嗔艘跤保覕?shù)據(jù)量也小活喊。
查詢的字段和條件如果都存在于某個(gè)索引內(nèi)， 就會(huì)通過(guò)這個(gè)索引拿到數(shù)據(jù)量愧，比全表查詢拿數(shù)據(jù)快很多钾菊，條件再進(jìn)行后續(xù)處理
EXPLAIN SELECT `name`, age, mobile FROM `student` WHERE age = 18 AND mobile = '156221905631';
image.png
- 明明沒(méi)有 name age 這個(gè)索引，為什么還會(huì)跑索引呢偎肃？有沒(méi)有發(fā)現(xiàn)這個(gè)索引的字節(jié)數(shù)跟使用 name 索引的字節(jié)數(shù)是一樣的煞烫，由此我們可以得知是使用了 name 索引，再進(jìn)行 age 數(shù)據(jù)過(guò)濾累颂。
索引就是為了提高查詢速度的滞详， 只要條件能組合成索引就會(huì)使用索引進(jìn)行查詢凛俱，多余的條件再進(jìn)行后續(xù)處理
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' AND age = 18 ;
image.png
2、索引字段參與了運(yùn)算/使用了函數(shù)料饥，會(huì)導(dǎo)致全表掃描蒲犬，索引失效
跑索引例子
- 索引字段值使用了函數(shù)，會(huì)跑索引 name
EXPLAIN SELECT * FROM `student` WHERE `name` = CONCAT('小', '明');
image.png
不跑索引例子
- 索引字段值參與了運(yùn)算岸啡，不跑索引
EXPLAIN SELECT * FROM `student` WHERE `name` = '小'+'明';
image.png
- 索引字段參與了運(yùn)算原叮，不跑索引
EXPLAIN SELECT * FROM `student` WHERE `name` + '明' = '小明';
image.png
- 索引字段使用了函數(shù)，不跑索引
EXPLAIN SELECT * FROM `student` WHERE SUBSTR(`name`, 1, 3) = '小明';
image.png
3巡蘸、模糊查詢時(shí)（like語(yǔ)句）奋隶，模糊匹配的占位符位于條件的首部
- 其中方式二和方式三，由于占位符出現(xiàn)在首部悦荒，導(dǎo)致無(wú)法走索引唯欣。這種情況不做索引的原因很容易理解，索引本身就相當(dāng)于目錄逾冬，從左到右逐個(gè)排序黍聂。而條件的左側(cè)使用了占位符，導(dǎo)致無(wú)法按照正常的目錄進(jìn)行匹配身腻，導(dǎo)致索引失效就很正常了。
跑索引例子
EXPLAIN SELECT * FROM `student` WHERE `name` LIKE '小明%';
image.png
不跑索引例子
EXPLAIN SELECT * FROM `student` WHERE `name` LIKE '%小明';
image.png
EXPLAIN SELECT * FROM `student` WHERE `name` LIKE '%小明%';
image.png
4匹厘、參數(shù)類型與字段類型不匹配嘀趟，導(dǎo)致類型發(fā)生了隱式轉(zhuǎn)換，索引失效
- 嘗試了很多種參數(shù)類型與字段類型不匹配的愈诚，只有參數(shù)類型為字符串她按，字段類型不是字符串的才會(huì)索引失效，估計(jì)Mysql后續(xù)會(huì)優(yōu)化掉這個(gè)問(wèn)題炕柔，了解這種情況即可
跑索引例子
EXPLAIN SELECT * FROM `student` WHERE id = '1';
image.png
EXPLAIN SELECT * FROM `student` WHERE grade = '1';
image.png
不跑索引例子
EXPLAIN SELECT * FROM `student` WHERE `name` = 1;
image.png
5酌泰、查詢條件使用OR關(guān)鍵字，其中一個(gè)字段沒(méi)有創(chuàng)建索引匕累，則會(huì)導(dǎo)致整個(gè)查詢語(yǔ)句索引失效陵刹； OR兩邊為范圍查詢時(shí)，如果無(wú)法合并為一個(gè)條件時(shí)欢嘿，則索引失效
跑索引例子
- 兩邊的字段都有索引
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' OR grade = 1;
image.png
- 可以合并為一個(gè)條件 id != 1
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id < 1;
image.png
- 可以合并為一個(gè)條件 id >= 8
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id >= 8;
image.png
不跑索引例子
- 只有 name 有索引衰琐。可以想一下炼蹦，如果有一邊條件走了全表查詢羡宙，另外一邊就沒(méi)必要走索引浪費(fèi)性能了
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' OR update_time = '2024-03-14 10:41:15';
image.png
- 沒(méi)法合并為一個(gè)條件，等同于沒(méi)有條件掐隐，（查詢字段不被包含在索引的情況）沒(méi)有條件不就相當(dāng)于 SELECT * FROM
student全局查詢了嘛
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id < 8;
image.png
6狗热、兩列數(shù)據(jù)做比較，即便兩列都創(chuàng)建了索引，索引也會(huì)失效
EXPLAIN SELECT * FROM `student` WHERE id = grade;
EXPLAIN SELECT * FROM `student` WHERE id > grade;
EXPLAIN SELECT * FROM `student` WHERE id < grade;
image.png
7匿刮、查詢條件使用不等進(jìn)行比較時(shí)僧凰，也會(huì)進(jìn)行索引
EXPLAIN SELECT * FROM `student` WHERE gender != '女';
image.png
EXPLAIN SELECT * FROM `student` WHERE grade != 1;
image.png
9、查詢條件使用 IS NULL / IS NOT NULL 時(shí)僻焚，走索引允悦。
- 相同 IS NULL 條件下，按索引順序來(lái)決定執(zhí)行哪個(gè)索引虑啤。
EXPLAIN SELECT * FROM `student` WHERE grade IS NULL AND `name` IS NULL;
image.png
- 優(yōu)先執(zhí)行 IS NULL 條件的索引
EXPLAIN SELECT * FROM `student` WHERE `gender` IS NOT NULL AND grade IS NULL;
image.png
10隙弛、范圍查詢 IN、NOT IN (選項(xiàng)特別多或者未知的情況狞山，請(qǐng)使用 EXISTS全闷、NOT EXISTS)
MySQL 8 的 IN / NOT IN 查詢中，如果 IN / NOT IN 列表中的值不多萍启，且被查詢的字段有索引总珠，那么這個(gè)查詢可能會(huì)使用索引。具體使用哪個(gè)索引勘纯，取決于 MySQL 的查詢優(yōu)化器如何評(píng)估成本局服。
如果 IN / NOT IN 列表中的值非常多，或者查詢的字段本身不適合索引驳遵，那么即使有索引淫奔，查詢可能不會(huì)使用它。此外堤结，如果查詢中還涉及到其他非索引字段唆迁，即使可以使用索引，優(yōu)化器可能也會(huì)決定全表掃描更有效
跑索引例子
EXPLAIN SELECT * FROM `student` WHERE grade IN (1, 2);
image.png
EXPLAIN SELECT * FROM `student` WHERE grade NOT IN (1, 2, 3);
image.png
不跑索引例子
EXPLAIN SELECT * FROM `student` WHERE grade IN (1, 2, 3);
image.png
EXPLAIN SELECT * FROM `student` WHERE grade NOT IN (1, 2, 3, 4);
image.png
11竞穷、范圍查詢 EXISTS唐责、NOT EXISTS
MySQL 8 的 EXISTS / NOT EXISTS 查詢通常會(huì)使用索引來(lái)優(yōu)化查詢性能。具體使用多少個(gè)索引取決于查詢的具體情況瘾带。如果 EXISTS 子查詢中的表有多個(gè)可用的索引鼠哥，MySQL 查詢優(yōu)化器會(huì)選擇一個(gè)最優(yōu)的索引來(lái)執(zhí)行查詢
EXPLAIN SELECT * FROM `student` t1 WHERE NOT EXISTS (SELECT 1 FROM `student` t2 WHERE t2.id = t1.id);
image.png
12、范圍查詢 BETWEEN AND 會(huì)使用索引
EXPLAIN SELECT * FROM `student` WHERE grade BETWEEN 1 AND 1000;
image.png
