MySQL索引失效

原创已于 2023-04-09 11:14:27 修改 · 726 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#索引失效 #SQL 索引失效 #MySQL索引 #索引 #最左匹配原则

于 2022-05-04 17:22:59 首次发布

SQL调优专栏收录该内容

1 篇文章

订阅专栏

本文介绍了MySQL环境下，针对sys_user表的创建和测试数据插入，然后讨论了多种可能导致索引失效的场景，包括模糊查询、数据类型不匹配、使用函数、NULL值处理、字段运算、最左匹配原则违反等，并举例展示了这些场景。通过理解这些情况，可以更好地优化数据库查询性能。

环境：mysql-5.7.28

环境准备

-- 查看版本
select version();

-- 创建sys_user表
CREATE TABLE `sys_user`  (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `telephone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(3) NOT NULL,
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `status` int(2) NULL DEFAULT NULL,
  `operator_time` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_username_telephone_mail`(`username`, `telephone`, `mail`) USING BTREE,
  INDEX `idx_password`(`password`) USING BTREE,
  INDEX `idx_age`(`age`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- 插入测试数据
insert into `sys_user` 
(`id`, `username`, `telephone`, `mail`, `age`, `password`, `remark`, `status`, `operator_time`) 
values 
('000001', '张三', '12345678901', '12345678@qq.com', 18, '123456', NULL, 1, '1970-01-01'),
('111111', '李四', '12345678902', '123456789@qq.com', 19, 'abcdef', NULL, 2, '1970-01-01'),
('222222', '王五', '12345678903', '1234567890@qq.com', 20, '000000', NULL, 3, '1970-01-01'),
('333333', '赵六', '12345678904', '1234567891@qq.com', 21, '', NULL, NULL, '1970-01-01'),
('444444', '孙七', '12345678905', '1234567892@qq.com', 22, '123456', NULL, 5, '1970-01-01'),
('555555', '周八', '12345678906', '1234567893@qq.com', 22, 'abcdef', NULL, 5, '1970-01-01'),
('666666', '吴九', '12345678907', '1234567894@qq.com', 22, '000000', NULL, 5, '1970-01-01'),
('777777', '郑十', '12345678908', '1234567895@qq.com', 22, 'qwerty', NULL, 5, '1970-01-01');

索引失效场景

模糊查询

% 在前面

explain select * from sys_user where id like '%1%'
explain select * from sys_user where id like '%1'

在这里插入图片描述

数据类型不一致

错误的数据类型，如varchar字段使用了int

explain select * from sys_user where id = 111111

索引字段使用了函数

索引字段使用内部函数

explain select * from sys_user where length(id) = 2

null

非空字段，is null / is not null 都不走索引

explain select * from sys_user where id is null
explain select * from sys_user where id is not null

字段允许为空，is not null 不走索引

explain select * from sys_user where password is not null

索引字段有运算

索引字段进行（+，-，*，/，!, !=, ）等运算

explain select * from sys_user where age + 1 = 20 
explain select * from sys_user where age != 20

最左匹配原则

多列索引 (idx_username_telephone_mail) 没有匹配到最左边username的索引字段会导致索引失效，当遇到范围查询(>、<、between、like)就会停止匹配

explain select * from sys_user where mail = '12345678@qq.com'
explain select * from sys_user where telephone = '12345678901'
explain select * from sys_user where telephone = '12345678901' and mail = '12345678@qq.com'

全表扫描快于索引

explain select * from sys_user where age = 22

not in

explain select * from sys_user where age not in (19,20)

or

or连接的字段没有索引

explain select * from sys_user where id = '000001' or status = 1