注意：MySQL版本对索引也很重要，不同版本相同场景下，索引有的版本可能失效，有的版本可能不失效，本讲解版本为 8.0.19
首先我们弄个表，造一些数据
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 '手机号',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` varchar(10) DEFAULT NULL COMMENT '性别',
`grade` int DEFAULT NULL COMMENT '年级',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
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='学生信息表';
image.png
至于走不走索引，我们关注两个字段即可
image.png
接下来我们讲讲有哪些场景索引会失效
1、在联合索引的场景下，查询条件不满足最左匹配原则
根据最左匹配原则，我们能匹配的组合所索引有哪些？
name | name mobile | name mobile age
只要查询条件（不论顺序位置）能组合成上面的一种就肯定会跑索引。
跑索引例子
- 跑索引 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
不跑索引例子
- 因为没有 mobile age 组合的索引，也组合不出对应的索引，所以不跑索引
EXPLAIN SELECT * FROM `student` WHERE age = 18 AND mobile = '156221905631';
image.png
特殊跑索引例子
- 上面明明说 没有 mobile age 组合的索引，也组合不出对应的索引，所以不跑索引。为什么不同查询字段，相同的条件，这个就跑索引了呢？
这个比较特殊，查询字段和条件 name, age, mobile 索引都包含了，不会产生回表问题，通过索引拿到数据比查询全表快多了，而且数据量也小。
查询的字段和条件如果都存在于某个索引内， 就会通过这个索引拿到数据，比全表查询拿数据快很多，条件再进行后续处理
EXPLAIN SELECT `name`, age, mobile FROM `student` WHERE age = 18 AND mobile = '156221905631';
image.png
- 明明没有 name age 这个索引，为什么还会跑索引呢？有没有发现这个索引的字节数跟使用 name 索引的字节数是一样的，由此我们可以得知是使用了 name 索引，再进行 age 数据过滤。
索引就是为了提高查询速度的， 只要条件能组合成索引就会使用索引进行查询，多余的条件再进行后续处理
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' AND age = 18 ;
image.png
2、索引字段参与了运算/使用了函数，会导致全表扫描，索引失效
跑索引例子
- 索引字段值使用了函数，会跑索引 name
EXPLAIN SELECT * FROM `student` WHERE `name` = CONCAT('小', '明');
image.png
不跑索引例子
- 索引字段值参与了运算，不跑索引
EXPLAIN SELECT * FROM `student` WHERE `name` = '小'+'明';
image.png
- 索引字段参与了运算，不跑索引
EXPLAIN SELECT * FROM `student` WHERE `name` + '明' = '小明';
image.png
- 索引字段使用了函数，不跑索引
EXPLAIN SELECT * FROM `student` WHERE SUBSTR(`name`, 1, 3) = '小明';
image.png
3、模糊查询时（like语句），模糊匹配的占位符位于条件的首部
- 其中方式二和方式三，由于占位符出现在首部，导致无法走索引。这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。
跑索引例子
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、参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效
- 尝试了很多种参数类型与字段类型不匹配的，只有参数类型为字符串，字段类型不是字符串的才会索引失效，估计Mysql后续会优化掉这个问题，了解这种情况即可
跑索引例子
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关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； OR两边为范围查询时，如果无法合并为一个条件时，则索引失效
跑索引例子
- 两边的字段都有索引
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' OR grade = 1;
image.png
- 可以合并为一个条件 id != 1
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id < 1;
image.png
- 可以合并为一个条件 id >= 8
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id >= 8;
image.png
不跑索引例子
- 只有 name 有索引。可以想一下，如果有一边条件走了全表查询，另外一边就没必要走索引浪费性能了
EXPLAIN SELECT * FROM `student` WHERE `name` = '小明' OR update_time = '2024-03-14 10:41:15';
image.png
- 没法合并为一个条件，等同于没有条件，（查询字段不被包含在索引的情况）没有条件不就相当于 SELECT * FROM
student全局查询了嘛
EXPLAIN SELECT * FROM `student` WHERE id > 1 OR id < 8;
image.png
6、两列数据做比较，即便两列都创建了索引，索引也会失效
EXPLAIN SELECT * FROM `student` WHERE id = grade;
EXPLAIN SELECT * FROM `student` WHERE id > grade;
EXPLAIN SELECT * FROM `student` WHERE id < grade;
image.png
7、查询条件使用不等进行比较时，也会进行索引
EXPLAIN SELECT * FROM `student` WHERE gender != '女';
image.png
EXPLAIN SELECT * FROM `student` WHERE grade != 1;
image.png
9、查询条件使用 IS NULL / IS NOT NULL 时，走索引。
- 相同 IS NULL 条件下，按索引顺序来决定执行哪个索引。
EXPLAIN SELECT * FROM `student` WHERE grade IS NULL AND `name` IS NULL;
image.png
- 优先执行 IS NULL 条件的索引
EXPLAIN SELECT * FROM `student` WHERE `gender` IS NOT NULL AND grade IS NULL;
image.png
10、范围查询 IN、NOT IN (选项特别多或者未知的情况，请使用 EXISTS、NOT EXISTS)
MySQL 8 的 IN / NOT IN 查询中，如果 IN / NOT IN 列表中的值不多，且被查询的字段有索引，那么这个查询可能会使用索引。具体使用哪个索引，取决于 MySQL 的查询优化器如何评估成本。
如果 IN / NOT IN 列表中的值非常多，或者查询的字段本身不适合索引，那么即使有索引，查询可能不会使用它。此外，如果查询中还涉及到其他非索引字段，即使可以使用索引，优化器可能也会决定全表扫描更有效
跑索引例子
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 查询通常会使用索引来优化查询性能。具体使用多少个索引取决于查询的具体情况。如果 EXISTS 子查询中的表有多个可用的索引，MySQL 查询优化器会选择一个最优的索引来执行查询
EXPLAIN SELECT * FROM `student` t1 WHERE NOT EXISTS (SELECT 1 FROM `student` t2 WHERE t2.id = t1.id);
image.png
12、范围查询 BETWEEN AND 会使用索引
EXPLAIN SELECT * FROM `student` WHERE grade BETWEEN 1 AND 1000;
image.png
