MySQL常见的索引失效场景梳理-实例

1、创建一张演示表

-- t_demo.t_user definition

CREATE TABLE `t_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '用户名',
  `status` varchar(100) DEFAULT NULL COMMENT '用户状态',
  `address` varchar(100) NOT NULL COMMENT '用户地址',
  `sex` tinyint DEFAULT NULL COMMENT '性别：1男，2女',
  `post` varchar(100) DEFAULT NULL COMMENT '岗位',
  PRIMARY KEY (`id`),
  KEY `t_user_name_IDX` (`name`,`status`,`address`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

2、违反最左前缀法制

联合索引或复合索引下，查询从索引的最左前列开始，不能跳过索引中的列。

说白了就是索引是按照先字段name再字段status之后字段address顺序创建的联合索引，使用时也按照这个顺序使用就不会导致索引失效啦。

# 注意这条SQL 条件中缺少联合索引中的第二个字段status
select * from t_user where name = 'xx' and address = 'xx';

3、范围查询右边的列

这个也好理解，比如还是先字段name再字段status之后字段address顺序创建的联合索引，字段status使用了范围查询，比如status>1，那么字段status和字段address就不走索引了，当然如果字段status使用了返回，那么字段address就不走索引了，以此类推。

# 注意这条SQL中status字段使用了大于号
select * from t_user where name = 'xx' and status > '1' and address = 'xx';

4、不能再索引列上进行运算操作

在使用索引查询时，如果where条件语句中使用了数据库函数，比如：substring(name, 4, 3) ，那么将会不走索引。

select * from t_user where substring(name, 4, 3) = 'xx' and status = '1' and address = 'xx';

5、字符串不加单引号（类型转换了）

如果字段B是一个字符串类型的状态值，在使用where条件查询时，字符串需要使用单引号包括起来，如果不使用引号那么将会导致MySQL内部的类型转换，这时它上面创建的索引就没用了。

select * from t_user where name = 'xx' and status = 1 and address = 'xx';

6、以%开头的like查询

就是在where字句查询时，like语句后面的匹配规则问题，使用双匹配和前匹配都不会走索引

select * from t_user where name = '%xx%' and status = 1 and address = 'xx';
select * from t_user where name = '%xx' and status = 1 and address = 'xx';

7、 补充：

可以使用explain命令查询执行计划，如下：

explain select * from t_user where name = '%xx' and status = 1 and address = 'xx';