判断MySQL索引失效的场景
MySQL索引失效
表定义
CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_phone` (`phone`) USING BTREE,
KEY `idx_name_age_gender` (`name`,`age`,`gender`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入数据
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (1, 'Lee', 23, '男', '[email protected]', '深圳', '18888888888');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (2, 'Mike', 22, '男', '[email protected]', '北京', '19999999999');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (3, 'Marry', 18, '女', '[email protected]', '上海', '10086');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (4, 'Neo', 25, '男', '[email protected]', '深圳', '10010');
INSERT INTO `t_user` (`id`, `name`, `age`, `gender`, `email`, `address`, `phone`) VALUES (5, 'Sara', 24, '女', '[email protected]', '广州', '10000');
数据库版本：
8.0.28
索引：
-
主键索引：id -
联合索引：idx_name_age_gender -
唯一索引：idx_phone
1 联合索引不符合最左匹配原则
联合索引需要遵守最左匹配原则，在本文的示例中，联合索引idx_name_age_gender的最左字段是name，则在查询时想要走索引，就必须保证name出现在where条件中。
示例一：
EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `age` = 23 AND `gender` = '男'
结果：上述查询走了idx_name_age_gender索引。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `age` = 23
结果：因为where条件中只有name和age，所以联合索引只走了name和age两列。
示例三：
EXPLAIN SELECT * FROM t_user WHERE `name` = 'lee' AND `gender` = '男'
结果：在where条件中，使用了name和gender，走了联合索引的name列，由于没有使用age，所以联合索引从中间断开了，就没有走到gender列的索引。
示例四：
EXPLAIN SELECT * FROM t_user WHERE `age` = 23 AND `name` = 'lee'
结果：在where条件中，列的先后顺序不会影响到索引，上面虽然name在age之后，但是同样会走联合索引的name和age列。
示例五：
EXPLAIN SELECT * FROM t_user WHERE `age` = 23
结果：但是如果在where条件中没有使用name字段，则不会走联合索引，因为不符合最左匹配原则。同样的，下面这些语句都不会走idx_name_age_gender索引。
EXPLAIN SELECT * FROM t_user WHERE `gender` = '男'
EXPLAIN SELECT * FROM t_user WHERE `age` = 23 AND `gender` = '男'
2 模糊查询时占位符条件位于首部
当使用模糊查询关键字like时，有以下三种情况：
-
like '%lee%' -
like '%lee' -
like 'lee%'
由于1和2中的占位符都在条件的首部，所以1和2是不会走索引的，3的占位符不在首部，所以会走索引。
示例一：
EXPLAIN SELECT * FROM t_user WHERE `name` LIKE 'Lee%'
结果：当占位符不在首部时，可走索引。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `name` LIKE '%Lee%'
EXPLAIN SELECT * FROM t_user WHERE `name` LIKE '%Lee'
结果：当占位符在首部时，不走索引。
3 类型隐式转换导致索引失效
当参数类型为int类型，但是实际索引字段为varchar类型时，MySQL会发生隐式类型转换从而导致索引失效。
示例一：
EXPLAIN SELECT * FROM t_user WHERE `phone` = 10086
结果：在示例中的phone字段为varchar类型，但是SQL语句中的参数为int类型，发生了隐式类型转换，所以导致当前SQL可能走的索引是idx_phone，但是实际没有走这个索引。
只需要将参数加上单引号就可以正常走索引。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `phone` = '10086'
结果：参数和字段类型一致，正常走索引。
4 索引列参与计算
示例一：
EXPLAIN SELECT * FROM t_user WHERE `id` + 1 = 3
结果：由于在SQL语句中id字段参与了计算，所以导致索引失效。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `id` = 3 - 1
结果：参数列参与计算不影响，可正常走索引。
5 索引列使用函数
示例一：
EXPLAIN SELECT * FROM t_user WHERE SUBSTR(`phone`, 1, 3) = '100'
结果：虽然phone列有索引，但是由于索引列使用了函数所以导致索引失效。
6 OR的错误使用
当使用OR关键字时，必须保证OR关键字两边的字段都有索引，才会走索引，如果只有一边的字段有索引则不会走索引。
示例一：
EXPLAIN SELECT * FROM t_user WHERE `id` = 3 OR `address` = '深圳'
结果：由于OR左边的id字段有索引但是右边的address没有索引，所以索引失效。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `id` = 3 OR `phone` = '10086'
结果：OR左边的id和右边的phone字段均有索引，所以可正常走索引。
7 使用select *
示例一：
EXPLAIN SELECT `name`,`age`,`gender` FROM t_user WHERE `age` = 23
结果：当select * 时使用联合索引并且不遵循最左匹配原则时，索引失效。
示例二：
EXPLAIN SELECT `name`,`age`,`gender` FROM t_user WHERE `age` = 23
结果：当select后面的字段均为索引列时，那么where后的条件虽然不遵循最左匹配原则，也可走索引。
这里使用到的是覆盖索引的知识，当select后面的字段都是索引列时，那么这些索引称为覆盖索引，查询这些字段就能够走索引，而使用select * 时，查询的是所有字段，这样就存在查询非索引列的数据，就不会走索引。
8 列对比
示例一：
EXPLAIN SELECT * FROM t_user WHERE `id` = `phone`
结果：当两个列进行比较时，即便两列都有索引，也会导致索引失效。（本示例中id和phone比较仅仅是作为演示，而且两个字段的类型不一致，即使类型一致也不会走索引，可自行测试）
9 not in和not exists
在范围查询中常用的有in、exists、not in、not exists和between and，在这些范围查询中，in、exists和between and都会走索引，not in如果是主键索引则会走索引，如果是普通索引则会失效，not exists同样也会索引失效。
示例一：
explain select * from t_user where `id` in (2,3)
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id = 2 and u2.id = u1.id)
explain select * from t_user where `id` between 1 and 2
以上所用的in、exists和between and都会正常走索引，可自行测试。
示例二：
EXPLAIN SELECT * FROM t_user WHERE `phone` NOT IN ('2','3')
结果：普通索引列使用not in时索引会失效
示例三：
EXPLAIN SELECT * FROM t_user WHERE `id` NOT IN ('2','3')
结果：主键索引列使用not in时正常走索引。
示例四：
EXPLAIN SELECT * FROM t_user u1 WHERE NOT EXISTS (SELECT 1 FROM t_user u2 WHERE `phone` = '10086' AND u1.id = u2.id)
结果：使用not exists时索引会失效，即使是主键索引同样会失效。
10 order by
示例一：
EXPLAIN SELECT * FROM t_user ORDER BY `id`
结果：order by后面接主键列可走主键索引。
示例二：
EXPLAIN SELECT * FROM t_user ORDER BY `phone`
EXPLAIN SELECT * FROM t_user ORDER BY `phone` LIMIT 10
结果：order by后面接普通索引列时，索引失效。
示例三：
EXPLAIN SELECT * FROM t_user ORDER BY `name`
EXPLAIN SELECT * FROM t_user ORDER BY `name`, `age`
EXPLAIN SELECT * FROM t_user ORDER BY `name`, `age`, `gender`
结果：order by后面接联合索引列，并遵循最左匹配原则，同样索引失效，包括在后面加上limit关键字同样索引失效。
示例四：
EXPLAIN SELECT * FROM t_user WHERE `name` = 'Lee' ORDER BY `age`,`gender`
结果：配合where一起使用，并遵循最左匹配原则时，where后面的索引有效，order by后面的索引照样失效，加limit关键字同样失效。
之前在网上有看到，如果在使用order by时，加上where关键字和limit关键字并且遵守最左匹配原则就会正常走索引，但是本文在MySQL8.0.28版本下这些都不会走索引，推断可能是由于数据库版本的原因，如果有使用其他版本的可以自行尝试一下order by在这些情况下是否会走索引。