环境：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 `index_username_telephone_mail`(`username`, `telephone`, `mail`) USING BTREE,
INDEX `index_password`(`password`) USING BTREE,
INDEX `index_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
最左匹配原则
多列索引在使用了 select * 的情况下没有匹配到最左边的索引字段
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
