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：