MySQL-隐式类型转换导致索引失效以及可能的查询数据异常_索引列上进行了隐式类型转换-CSDN博客

本文链接：https://blog.csdn.net/wuxiaopengnihao1/article/details/127532291

数据库优化中，索引失效是一个常见问题，尤其在数据量增大时影响显著。本文通过实例分析了MySQL中隐式类型转换如何导致索引失效，特别是在字符串字段与数字进行比较时，隐式转换规则可能导致全表扫描，从而影响查询效率。建议在编写SQL时，确保条件类型与字段类型匹配，避免隐式转换，以保持索引效能。

摘要由CSDN通过智能技术生成

前言

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

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

数据准备

首先使用存储过程生成 1000 万条测试数据，测试表一共建立了 7 个字段（包括主键），num1和num2保存的是和ID一样的顺序数字，其中num2是字符串类型。 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 ( 11 ) NOT NULL,
	`num_int` INT ( 11 ) NOT NULL DEFAULT '0',
	`num_str` VARCHAR ( 11 ) NOT NULL DEFAULT '',
	`type1` INT ( 4 ) NOT NULL DEFAULT '0',
	`type2` INT ( 4 ) NOT NULL DEFAULT '0',
	`str1` VARCHAR ( 100 ) NOT NULL DEFAULT '',
	`str2` VARCHAR ( 100 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
	KEY `idx_num_int` ( `num_int` ),
	KEY `idx_num_str` ( `num_str` ),
	KEY `idx_type1` ( `type1` ),
	KEY `idx_str1` ( `str1` ),
	KEY `idx_str2` ( `str2` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
-- 创建存储过程
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);
        -- 事务优化，每一万条数据提交一次事务