MySQL之索引失效的情况

什么情况下索引会失效？

1. 违反最左前缀原则
2. 范围查询右边的列不能使用索引
3. 不要在索引列上进行运算操作
4. 字符串不加单引号导致索引失效
5. 以%开头的like模糊查询

什么情况下索引会失效？

示例，有user表如下

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '技术主键',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `vip` varchar(2) DEFAULT NULL COMMENT 'vip等级',
  `tel` varchar(50) DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`) USING BTREE,
  KEY `idx_2` (`vip`,`tel`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

1、违反最左前缀原则

如果是复合索引，要遵守最左前缀原则，指的是查询从索引的最左前列开始，且不跳过索引中的列，匹配最左前缀走索引。

如下，复合索引idx_2(vip,tel)，它最多的前缀有两个：vip、vip tel。

那如果，有索引idx(col1,col2,col3)，那么它最多的前缀有三个：col1、col1 col2、col1 col2 col3。

正例

反例

2、范围查询右边的列不能使用索引

3、不要在索引列上进行运算操作

4、字符串不加单引号导致索引失效

字符串没加单引号，MySQL的查询优化器会自动进行“类型转换”，导致索引失效。

正例：

反例：

5、以%开头的like模糊查询

如果仅仅是尾部模糊匹配（'137%'），索引不会失效，如果是头部模糊匹配（'%137'），索引会失效。

正例：

反例：