MySQL索引失效场景 ｜Java 刷题打卡

本文正在参加「Java主题月 – Java 刷题打卡」，详情查看 活动链接

题目：MySQL索引失效场景

知识点：

针对索引失效场景，便于优化SQL查询

基于以下表结构验证索引失效场景

CREATE TABLE `dynamic_info` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL COMMENT '工作动态标题',
  `content` text COMMENT '工作动态内容',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator` varchar(64) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `docid` text COMMENT '爱数文件id',
  `version_num` int(11) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工作动态';
复制代码

添加组合索引、全文索引

alter table dynamic_info 
    add index 
    idx_dynamic_titleCreatorVersionNum(title,creator,version_num);
   
 alter table dynamic_info 
    add FULLTEXT 
 idx_dynamic_content(content);
复制代码

列出索引生效相关场景

1. 使用全值匹配，查询条件全使用索引查询，索引有效

EXPLAIN select * from dynamic_info where title = '通知' and creator = '5' and version_num = 1;
复制代码

从执行计划可知索引总长度为1287

2. 查询符合最佳左前缀法则，查询条件仅使用组合索引第一个字段，索引有效

EXPLAIN select * from dynamic_info where title = '通知';
复制代码

3. 使用组合索引第一二个字段作为查询条件，索引有效

EXPLAIN select * from dynamic_info where title = '通知' and creator = '5';
复制代码

4.使用组合索引第一三个字段作为查询条件，索引有效

EXPLAIN select * from dynamic_info where title = '通知' and version_num = 2;
复制代码

总结：第1、2、3、4种情况因查询条件命中组合索引情况不同，而使用的索引长度

索引失效相关场景如下：

1.使用组合索引第二三个字段作为查询条件，索引失效

EXPLAIN select * from dynamic_info where creator = '5' and version_num = 1;
复制代码

2. 在索引字段上进行计算、函数、类型转换，索引失败

EXPLAIN select * from dynamic_info where LEFT(title,1) = '通';
复制代码

使用left(str,length)函数函数对字符串截取，导致索引失效

3. 存储引擎不能使用索引中范围条件右边的列，索引部分失效

EXPLAIN select * from dynamic_info where title = '通知' and creator = '5';

EXPLAIN select * from dynamic_info where title = '通知' and creator > '5' and version_num = 2;
复制代码

结合之前的key_len分析，索引用到一个、二个、三个字段时，key_len分别对应1023、1282、1287，此处的查询只用到title和creator字段，由于creator是个范围查询，导致索引version_num字段上索引失效

4. 尽量使用覆盖索引，select索引字段查询，减少使用select *

EXPLAIN select title,creator,version_num from dynamic_info where title = '通知' and version_num = 1 and creator = '5';

EXPLAIN select * from dynamic_info where title = '通知' and creator = '5' and  version_num = 1 ;
复制代码

select title,creator,version_num 用到覆盖索引，索引类型为ref,性能好于range，此外Extra显示用到Using index，表名只需要从索引树中寻找数据，不需在表中查找，而select * 可能需要查询非索引其他字段，Extra显示用到Using where，表明需要到表中查找。所以select * 比select 索引字段 性能要低

5. 索引字段为字符串类型，查询条件没添加引号，索引失效

EXPLAIN select * from dynamic_info where title = 20210520;
复制代码

MySQL执行一条SQL语句先会优化SQL，将数字转换成String来查询，导致索引失效
解决索引失效方法：字段类型为varchar,查询的值为数字，需要添加单引号，索引才有效

6. 查询条件使用不等于（!= 或<>），索引失效

EXPLAIN select * from dynamic_info where title != '通知';
EXPLAIN select * from dynamic_info where title <> '通知';
复制代码

7. 查询条件使用is null，is not null，索引失效

EXPLAIN select * from dynamic_info where content is null;
EXPLAIN select * from dynamic_info where content is not null;
复制代码

8. 使用like以通配符开头(%?%),索引失效

EXPLAIN select * from dynamic_info where content like '%通知';
EXPLAIN select * from dynamic_info where content like '%通知%';
EXPLAIN select * from dynamic_info where content like '通知%';
复制代码

针对已有组合索引，查询使用like以通配符开头的SQL，可使用覆盖索引，即select的字段全部都被建立了索引

EXPLAIN select title,creator,version_num from dynamic_info where title like '%通知%';
复制代码

9. 使用or连接条件，索引失效

EXPLAIN select content from dynamic_info where content = '通知' or content = '5';
复制代码