mysql索引失效的坑
目录
前言
博客文章分享
隐式类型转换
隐式字符编码转换
原因分析
解决方法：
前言
在MySQL中，有时候会因为语句使用不当导致sql不走索引，从而使数据库的压力变大。本篇分享一下sql不走索引的坑，让大家也避避坑！
博客文章分享
MySQL索引失效的场景
对索引字段做函数操作
DROP TABLE IF EXISTS `t_file`;
CREATE TABLE `t_file` (`id` int(20) NOT NULL COMMENT '主键',`fileid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件ID',`filename` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件名',`sort` int(11) NOT NULL COMMENT '排序',`createtime` datetime NOT NULL COMMENT '创建时间',PRIMARY KEY (`id`) USING BTREE,INDEX `fileid`(`fileid`) USING BTREE,INDEX `createtime`(`createtime`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (11111, '1', '涨了', 1, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (22222, '2', 'aaaa', 2, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (33333, '21', 'asdq', 3, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (44444, '22', 'qweqwe', 4, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (55555, '23', 'wqes', 5, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (66666, '24', 'iuoty', 6, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (77777, '25', 'orooo', 7, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (88888, '26', 'asdq2', 8, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (99999, '27', '哇哦erqwr', 9, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (101010, '12', 'drfsrfe', 10, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (111111, '26', 'jkhd', 11, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (222222, '29', 'ghdwe2', 12, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (333333, '28', '规划局tu', 13, '2023-04-19 16:02:04');
INSERT INTO `high`.`t_file` (`id`, `fileid`, `filename`, `sort`, `createtime`) VALUES (444444, '24', 'sdrt3', 14, '2023-04-19 16:02:04');假如有这么一个文件表，需求查询统计某个月份的文件。
EXPLAIN SELECT COUNT(*) FROM `t_file` where MONTH(createtime) = 4;
结果：
从结果看，Extra 字段的 Using index，表示的是使用了覆盖索引。
首先B+ 树提供的快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但不是说MySQL优化器不走createtime索引了，只是放弃了树搜索功能，优化器可以选择遍历主键索引或者索引 createtime，优化器对比索引大小后发现，索引 createtime更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 createtime。
如果想使用上mysql的快速树搜索功能可以将sql改成下面这样的sql,但是这个sql的话每当有一年的数据就得加一行条件。
EXPLAIN SELECT COUNT(*) FROM `t_file` where
(createtime>=2023-04-01 AND createtime<2023-05-01)
or (createtime>=2022-04-01 AND createtime<2022-05-01);
建议不要在sql中对索引字段进行函数操作
隐式类型转换
还是使用t_file这个表，查询fileid等于22，索引字段是String但是输入是整形Int
EXPLAIN SELECT * FROM `t_file` where fileid = 22;
结果：
从结果可以看出没有走索引，全表扫描了。
规则：在 MySQL 中，字符串和数字做比较时，是将字符串转换成数字。
所以sql语句在优化器中相当于下面这个sql
EXPLAIN SELECT * FROM `t_file` where CAST(fileid AS signed int) = 22;
触发条件对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
那么如果索引字段是int类型，但是输入的参数是string呢？
因为MySQL的规则字符串和数字做比较时，是将字符串转换成数字，所以会走索引不会全表扫描。
隐式字符编码转换
原因分析
首先创建一个CHARACTER SET = utf8的表
DROP TABLE IF EXISTS `file_detail`;
CREATE TABLE `file_detail` (`id` int(11) NOT NULL COMMENT 'id',`fileid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件ID',`fileaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件路径',`user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上传用户',PRIMARY KEY (`id`) USING BTREE,INDEX `fileid`(`fileid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `high`.`file_detail` (`id`, `fileid`, `fileaddress`, `user`) VALUES (1, '1', 'c:/conf', '张三');
INSERT INTO `high`.`file_detail` (`id`, `fileid`, `fileaddress`, `user`) VALUES (2, '21', 'c:/config', '李四');
INSERT INTO `high`.`file_detail` (`id`, `fileid`, `fileaddress`, `user`) VALUES (3, '2', 'd:/high', 'wangwu');
然后查询一下t_file与file_detail中fileid=21的信息，sql如下：
EXPLAIN SELECT fd.* FROM t_file tf,`file_detail` fd where tf.fileid=fd.fileid and tf.id= 33333
结果：
从结果上看sql执行顺序是：首先使用了主键索引从t_file取出ID为33333的这一行数据，然后从这行数据中取到fileid的值，最后用fileid的值去file_detail去做匹配。
问题：file_detail表中fileid设置了索引，但是EXPLAIN结果确没有使用到？
百度结果是：t_file使用的utf8mb4编码，file_detail使用的utf8编码，表连接查询时用不上关键字段的索引。划重点：字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。
MySQL 内部的操作相当于如下sql：
EXPLAIN SELECT * FROM file_detail WHERE CONVERT(fileid USING utf8mb4) = 21
所以说最后用fileid的值去file_detail去做匹配时，连接过程中对索引字段做了函数操作，导致没有用上fileid索引。
知识点： CONVERT(expr USING transcoding_name) 函数是字符串转成 utf8mb4字符集。
解决方法：
1、将表的字符集改变成一致的utf8mb4（生产中不建议该操作）
ALTER TABLE `high`.`file_detail` CHARACTER SET = utf8mb4;
或
2、 不能修改字符集 ，麻烦点修改sql
EXPLAIN SELECT fd.* FROM t_file tf,`file_detail` fd where fd.fileid=CONVERT(tf.fileid USING utf8) and tf.id= 33333
总结：
写sql时多EXPLAIN一下，不要在sql中对索引字段进行函数操作，索引字段的参数可以用函数。