MySQL-隐式类型转换导致索引失效以及可能的查询数据异常

前言

数据库优化是一个任重而道远的任务，想要做优化必须深入理解数据库的各种特性。在开发过程中我们经常会遇到一些原因很简单但造成的后果却很严重的疑难杂症，这类问题往往还不容易定位，排查费时费力最后发现是一个很小的疏忽造成的，又或者是因为不了解某个技术特性产生的。

于数据库层面，最常见的恐怕就是索引失效了，且一开始因为数据量小还不易被发现。但随着业务的拓展数据量的提升，性能问题慢慢的就体现出来了，处理不及时还很容易造成雪球效应，最终导致数据库卡死甚至瘫痪。造成索引失效的原因可能有很多种，相关技术博客已经有太多了，今天我要记录的是隐式转换造成的索引失效。

数据准备

首先使用存储过程生成 1000 万条测试数据，测试表一共建立了 7 个字段（包括主键），num_int和num_str保存的是和ID一样的顺序数字，其中num_str是字符串类型。 type1和type2保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是type2是没有建立索引的。 str1和str2都是保存了一个 20 位长度的随机字符串，str1不能为NULL，str2允许为NULL，相应的生成测试数据的时候我也会在str2字段生产少量NULL值（每 100 条数据产生一个NULL值）。

-- 创建测试数据表
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1`  (
  `id` int NOT NULL,
  `num_int` int NOT NULL DEFAULT 0,
  `num_str` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `type1` int NOT NULL DEFAULT 0,
  `type2` int NOT NULL DEFAULT 0,
  `str1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `str2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_num_int`(`num_int` ASC) USING BTREE,
  INDEX `idx_num_str`(`num_str` ASC) USING BTREE,
  INDEX `idx_type1`(`type1` ASC) USING BTREE,
  INDEX `idx_str1`(`str1` ASC) USING BTREE,
  INDEX `idx_str2`(`str2` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- 创建存储过程
DROP PROCEDURE IF EXISTS pre_test1;
DELIMITER;
CREATE PROCEDURE `pre_test1`()
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    WHILE i < 10000000 DO
        SET i = i + 1;
        SET @str1 = SUBSTRING(MD5(RAND()),1,20);
        -- 每100条数据str2产生一个null值
        IF i % 100 = 0 THEN
            SET @str2 = NULL;
        ELSE
            SET @str2 = @str1;
        END IF;
        INSERT INTO test1 (`id`, `num_int`, `num_str`,
        `type1`, `type2`, `str1`, `str2`)
        VALUES (CONCAT('', i), CONCAT('', i),
        CONCAT('', i), i%5, i%5, @str1, @str2);
        -- 事务优化，每一万条数据提交一次事务
        IF i % 10000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END;
DELIMITER;
-- 执行存储过程
CALL pre_test1();

数据量比较大，还涉及使用MD5生成随机字符串，所以速度有点慢，稍安勿躁，耐心等待即可。

1000 万条数据，30分钟左右才跑完（实际时间跟你电脑硬件配置有关）。这里贴几条生成的数据，大致长这样。

SQL 测试

先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。

SELECT * FROM `test1` WHERE num_int = 10000;
SELECT * FROM `test1` WHERE num_int = '10000';
SELECT * FROM `test1` WHERE num_str = 10000;
SELECT * FROM `test1` WHERE num_str = '10000';

这四条 SQL 都是有针对性写的，1、2 查询的字段是 int 类型，3、4 查询的字段是varchar类型。1、2 或 3、4 查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。这样做有什么区别呢？先不看下边的测试结果你能猜出这四条 SQL 的效率顺序吗？

经测试这四条 SQL 最后的执行结果却相差很大，其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.01~0.05 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。

为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：

可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大。

仔细观察你会发现，34 两条 SQL 查询的字段num2是varchar类型的，查询条件等号右边加引号的第 4 条 SQL 是用到索引的，那么是查询的数据类型和字段数据类型不一致造成的吗？如果是这样那 12 两条 SQL 查询的字段num1是int类型，但是第 2 条 SQL 查询条件右边加了引号为什么还能用上索引呢。

查阅 MySQL 相关文档发现是隐式转换造成的

隐式转换

MySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html，

以下规则描述了比较操作如何进行转换：

如果一个或两个参数是NULL，则比较的结果是NULL，但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL，结果为真。无需转换。

如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。

如果两个参数都是整数，则将它们作为整数进行比较。

如果不与数字比较，十六进制值将被视为二进制字符串。

如果其中一个参数是 a TIMESTAMP或 DATETIME列，而另一个参数是常量，则在执行比较之前将常量转换为时间戳。这样做是为了对 ODBC 更友好。这不适用于的参数 IN()。为了安全起见，在进行比较时，请始终使用完整的日期时间、日期或时间字符串。例如，要在使用 BETWEEN日期或时间值时获得最佳结果，请使用CAST()将值显式转换为所需的数据类型。

来自一个或多个表的单行子查询不被视为常量。例如，如果子查询返回要与值进行比较的整数DATETIME ，则比较将作为两个整数进行。整数不会转换为时间值。要将操作数作为 DATETIME值进行比较，请使用 CAST()将子查询值显式转换为DATETIME.

如果其中一个参数是十进制值，则比较取决于另一个参数。如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较，如果另一个参数是浮点值，则将其作为浮点值进行比较。

在所有其他情况下，参数将作为浮点（双精度）数字进行比较。例如，字符串和数字操作数的比较是作为浮点数的比较进行的。

按照上述规则的最后一条，我们的查询SQL中，字符串与整数的比较会被转换成两个浮点数比较，左边是字符串类型 "1" 转换成浮点数为1.0，右边 INT类型的 1 转换成浮点数 1.0 。

根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件num1 = '10000'，左边是int类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。

先看第 2 条 SQL：SELECT * FROMtest1WHERE num1 = '10000'; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型'10000'，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

第 3 条 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左边是字符串类型'10000'，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。

关于这个隐式转换我们可以通过查询测试验证一下，先插入几条数据，其中num2='10000a'、'010000'和'10000'：

INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO `test1` (`id`, `num_int`, `num_str`, `type1`, `type2`, `str1`, `str2`) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');

然后使用第三条 SQL 语句SELECT * FROM test1 WHERE num_str = 10000;进行查询：

从结果可以看到，后面插入的三条数据也都匹配上了。那么这个字符串隐式转换的规则是什么呢？为什么num_str='10000a'、'010000'和'10000'这三种情形都能匹配上呢？查阅相关资料发现规则如下：

不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；
以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123a'会转换为123，'0123abc'会转换为0123也就是123，'03.8abc'会转换为3.8，其他同理。

现对以上规则做如下测试验证：

SELECT 123 = '123a'; # 1
SELECT 123 = '0123a'; # 1
SELECT 3.8 = '03.8abc'; # 1
SELECT -3.80 = '-03.8000abc'; # 1

如此也就印证了之前的查询结果了。

再次写一条 SQL 查询 str1 字段：SELECT * FROM test1 WHERE str1 = 1234;

分析和总结

通过上面的测试我们发现 MySQL 使用操作符的一些特性：

当操作符左右两边的数据类型不一致时，会发生隐式转换。
当查询字段为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
当查询字段为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描

1、索引列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效；

2、条件参数要尽可能与列的类型相同，避免隐式转换，或者把传入的条件参数转换成索引列的类型。

参考阅读

MySQL 5.7官方文档： https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

MySQL 8.0 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

浅析 MySQL 的隐式转换：https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/

MySQL-隐式类型转换导致索引失效 https://mp.weixin.qq.com/s/DGEvqiHPhf3BrutgheC4Wg

MySQL性能优化：MySQL中的隐式转换造成的索引失效 https://www.guitu18.com/post/2019/11/24/61.html

附录

1、MySQL 中字符串转浮点型时的规则如下：

不以数字开头的字符串都将转换为0：

SELECT CAST('abc' AS UNSIGNED)
-----------------------+
                      0|

以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止：

SELECT CAST(' 0123abc' AS UNSIGNED)

----------------------------+
                         123|

我的个人博客 https://blog.52ipc.top/

posted @ 2022-10-26 14:23 Micky233 阅读(313) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

极客世界

MySQL-隐式类型转换导致索引失效以及可能的查询数据异常

MySQL-隐式类型转换导致索引失效以及可能的查询数据异常

前言

数据准备

SQL 测试

隐式转换

分析和总结

参考阅读

附录

公告