MySQL性能优化:MySQL中的隐式转换造成的索引失效

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

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

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

数据准备
首先使用存储过程生成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,
`num1` int(11) NOT NULL DEFAULT '0',
`num2` 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 `num1` (`num1`),
KEY `num2` (`num2`),
KEY `type1` (`type1`),
KEY `str1` (`str1`),
KEY `str2` (`str2`)
AI 代码解读

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建存储过程
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`, `num1`, `num2`, 
    `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;
AI 代码解读

END;
// DELIMITER ;
-- 执行存储过程
CALL pre_test1();
数据量比较大,还涉及使用MD5生成随机字符串,所以速度有点慢,稍安勿躁,耐心等待即可。

1000万条数据,我用了33分钟才跑完(实际时间跟你电脑硬件配置有关)。这里贴几条生成的数据,大致长这样。

img

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

1: SELECT * FROM test1 WHERE num1 = 10000;
2: SELECT * FROM test1 WHERE num1 = '10000';
3: SELECT * FROM test1 WHERE num2 = 10000;
4: SELECT * FROM test1 WHERE num2 = '10000';
这四条SQL都是有针对性写的,12查询的字段是int类型,34查询的字段是varchar类型。12或34查询的字段虽然都相同,但是一个条件是数字,一个条件是用引号引起来的字符串。这样做有什么区别呢?先不看下边的测试结果你能猜出这四条SQL的效率顺序吗?

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

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

img

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

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

查阅MySQL相关文档发现是隐式转换造成的,看一下官方的描述:

官方文档: 12.2 Type Conversion in Expression Evaluation

当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如,MySQL会根据需要自动将字符串转换为数字,反之亦然。以下规则描述了比较操作的转换方式:

两个参数至少有一个是NULL时,比较的结果也是NULL,特殊的情况是使用<=>对两个NULL做比较时会返回1,这两种情况都不需要做类型转换
两个参数都是字符串,会按照字符串来比较,不做类型转换
两个参数都是整数,按照整数来比较,不做类型转换
十六进制的值和非数字做比较时,会被当做二进制串
有一个参数是TIMESTAMP或DATETIME,并且另外一个参数是常量,常量会被转换为timestamp
有一个参数是decimal类型,如果另外一个参数是decimal或者整数,会将整数转换为decimal后进行比较,如果另外一个参数是浮点数,则会把decimal转换为浮点数进行比较
所有其他情况下,两个参数都会被转换为浮点数再进行比较
根据官方文档的描述,我们的第23两条SQL都发生了隐式转换,第2条SQL的查询条件num1 = '10000',左边是int类型右边是字符串,第3条SQL相反,那么根据官方转换规则第7条,左右两边都会转换为浮点数再进行比较。

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

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

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

INSERT INTO test1 (id, num1, num2, type1, type2, str1, str2) VALUES ('10000001', '10000', '10000a', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO test1 (id, num1, num2, type1, type2, str1, str2) VALUES ('10000002', '10000', '010000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
INSERT INTO test1 (id, num1, num2, type1, type2, str1, str2) VALUES ('10000003', '10000', ' 10000', '0', '0', '2df3d9465ty2e4hd523', '2df3d9465ty2e4hd523');
然后使用第三条SQL语句SELECT * FROM test1 WHERE num2 = 10000;进行查询:

img

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

不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0;
以数字开头的字符串转换时会进行截取,从第一个字符截取到第一个非数字内容为止。比如'123abc'会转换为123,'012abc'会转换为012也就是12,'5.3a66b78c'会转换为5.3,其他同理。
现对以上规则做如下测试验证:

img

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

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

img

分析和总结
通过上面的测试我们发现MySQL使用操作符的一些特性:

当操作符左右两边的数据类型不一致时,会发生隐式转换。
当where查询操作符左边为数值类型时发生了隐式转换,那么对效率影响不大,但还是不推荐这么做。
当where查询操作符左边为字符类型时发生了隐式转换,那么会导致索引失效,造成全表扫描效率极低。
字符串转换为数值类型时,非数字开头的字符串会转化为0,以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。
所以,我们在写SQL时一定要养成良好的习惯,查询的字段是什么类型,等号右边的条件就写成对应的类型。特别当查询的字段是字符串时,等号右边的条件一定要用引号引起来标明这是一个字符串,否则会造成索引失效触发全表扫描。

码海无涯,不进则退,日积跬步,以至千里。本博客所写内容仅为个人在学习和研究MySQL过程中的一些心得体会及总结笔记,仅代表个人观点。本次测试使用的MySQL版本是 5.7.26,随着MySQL版本的更新某些特性可能会发生改变,本文不代表所述观点和结论于MySQL所有版本均准确无误,版本差异请自行甄别。

原文地址https://www.cnblogs.com/guitu18/p/12113495.html

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
177 9
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
81 22
 MySQL秘籍之索引与查询优化实战指南
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
69 10
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
60 8
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
75 7
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
102 5
【MYSQL】 ——索引(B树B+树)、设计栈
索引的特点,使用场景,操作,底层结构,B树B+树,MYSQL设计栈
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
12 0
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
59 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
70 3
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等