MySQL隐式类型转换导致索引失效的解决
作者:CodeGo 发布时间:2024-01-12 22:45:25
目录
问题
复现
隐式转换
总结
参考
问题
在工作中发现,有一个接口只执行一条SQL查询语句,并且SQL明明使用了主键列,但是速度很慢。
在MySQL中EXPLAINN后发现,执行时并没有使用主键索引,而是进行了全表扫描。
复现
数据表DDL如下,使用 user_id 作为主键索引:
CREATE TABLE `user_message` (
`user_id` varchar(50) NOT NULL COMMENT '用户ID',
`msg_id` int(11) NOT NULL COMMENT '消息ID',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
执行下面的查询语句,发现虽然 key 显示使用了主键索引,但是 rows显示扫描了全表,主键索引并没有起作用:
EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = 1;
id|select_type|table |partitions|type |possible_keys|key |key_len|ref|rows |filtered|Extra |
--+-----------+------------+----------+-----+-------------+-------+-------+---+-----+--------+------------------------+
1|SIMPLE |user_message| |index|PRIMARY |PRIMARY|206 | |10000| 10.0|Using where; Using index|
经过排查发现,数据表中 user_id 字段是 VARCHAR 类型,SQL语句中 user_id是INT 类型。MySQL 在执行语句时会对类型做转换,应该是在类型转换后导致主键索引失效。
隐式转换
MySQL 的官方文档:https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html,介绍了 MySQL类型隐式转换的规则:
当算子两边的操作数类型不一致时,MySQL会发生类型转换以使操作数兼容,这些转换是隐式发生的。下面描述了比较操作的隐式转换:
如果一个或两个参数均为NULL,则比较结果为NULL;但是 <=> 相等比较运算符除外,对于NULL <=> NULL,结果为true,无需转换。
如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。
如果两个参数都是整数,则将它们作为整数进行比较。
如果不将十六进制值与数字进行比较,则将其视为二进制字符串。
如果参数之一是TIMESTAMP或DATETIME列,而另一个参数是常量,则在执行比较之前,该常量将转换为时间戳。对于IN() 的参数不执行此操作。为了安全起见,在进行比较时,请始终使用完整的日期时间,日期或时间字符串。例如,要在将BETWEEN与日期或时间值一起使用时获得最佳结果,请使用CAST()将这些值显式转换为所需的数据类型。
一个或多个表中的单行子查询不视为常量。例如,如果子查询返回的整数要与DATETIME值进行比较,则比较将作为两个整数完成,整数不转换为时间值。参见上一条,这种情况下请使用CAST()将子查询的结果整数值转换为DATETIME。
如果参数之一是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整数值,则将参数作为十进制值进行比较;如果另一个参数是浮点值,则将参数作为浮点值进行比较。
在所有其他情况下,将参数作为浮点数(实数)进行比较。例如,将字符串和数字操作数进行比较,将其作为浮点数的比较。
根据上述规则的最后一条,在前面的SQL语句中,字符串与整数的比较会被转换成两个浮点数比较,左边是字符串类型 "1" 转换成浮点数为1.0,右边 INT类型的 1 转换成浮点数 1.0 。
按理说,两边都是浮点数,那么应该能使用索引,为什么执行时没有使用到?
原因在于,MySQL 中字符串转浮点型时的转换规则,规则如下:
1、不以数字开头的字符串都将转换为0:
SELECT CAST('abc' AS UNSIGNED)
CAST('abc' AS UNSIGNED)|
-----------------------+
0|
2、以数字开头的字符串转换时会进行截取,从第一个字符截取到第一个非数字内容为止:
SELECT CAST(' 0123abc' AS UNSIGNED)
CAST(' 0123abc' AS UNSIGNED)|
----------------------------+
123|
所以,在 MySQL 里 "1"、 " 1"、"1a" 、"01"这样的字符串转成数字后都是 1 。
MySQL在执行上面的SQL语句时,会把每一行主键列的值转换成浮点数(在主键上执行了函数CAST),再与条件参数做比较。在索引列上使用函数,会导致索引失效,所以最后导致了全表扫描。
我们只需要把前面SQL中传入的参数改为字符串,就可以使用到主键索引:
EXPLAIN SELECT COUNT(*) FROM user_message WHERE user_id = '1';
id|select_type|table |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
--+-----------+------------+----------+----+-------------+-------+-------+-----+----+--------+-----------+
1|SIMPLE |user_message| |ref |PRIMARY |PRIMARY|202 |const| 135| 100.0|Using index|
总结
1、条件列是字符串时,如果传入的条件参数是整数,会先转换成浮点数,再全表扫描,导致索引失效;
2、条件参数要尽可能与列的类型相同,避免隐式转换,或者在传入的参数上执行转换函数,转换成与索引列相同的类型。
参考
1、浅析 MySQL 的隐式转换
来源:https://juejin.cn/post/6994006962870419470
猜你喜欢
- 进程进程是程序的一次动态执行过程,它对应了从代码加载、执行到执行完毕的一个完整过程。进程是系统进行资源分配和调度的一个独立单位。进程是由代码
- 在设计中保持一致性(uniformity)是网页设计中一个重要的组成部分,它能使你的设计有效地传达信息而不会导致用户迷惑或焦虑。保证一致性的
- 什么是合并多行字符串(连接字符串)呢,例如: SQL> desc test; Name Type Nullable Default C
- 我们再用Jupyter-notebook,ipython-console,qtconsole的时候,有的时候画图希望不弹出窗口,直接画在co
- 读视频和写视频一直由于编解码的问题给程序员造成很多麻烦。对此进行了一些探索。用Python读取视频有两种主要方法,分别是基于imageio库
- 但GAE、Django并没有直接将pyExcelerator导出为Excel的方法。我的思路是先用把数据导入到Workbook和Worksh
- 1. 引言今天来给小伙伴推荐两款实用的便于调试Python代码的工具,可以方便展示我们调试代码的中间状态,提升大家的编码效率。2. 动机在日
- 根据google最新的算法规则:用户行为模式的重视程度越来越高,这也就要求网页设计的时候应注意“合理的网页结构”,SEO业界也有个共识“网页
- Index.asp:程序代码<html><head><meta http-equiv="Conten
- 这是base2的作者Dean Edwards 2007年3月份的一篇文章。最近正在折腾JavaScript库,感觉Dean给出的这些规则很中
- 在一些面试或者力扣题中都要求用双向链表来实现,下面是基于python的双向链表实现。一、构建链表节点class Node: &n
- 将list拼接为一个字符串在 python 中如果想将 list 拼接为一个字符串,可使用 join() 方法。join() 方法描述将序列
- 简介Pycharm安装以后必须激活后,才能正常的使用。否则就不能使用。【激活码激活】修改hosts文件添加下面一行到hosts文件,目的是屏
- 有时候很多朋友访问自己的asp或php等程序页面时出现一些错误,就是页面无法显示也没有详细的错误信息,就算iis中开启了显示详细的错误也不能
- 1.创建虚拟环境首先创建一个新文件夹在PyCharm终端中切换到这个文件夹,输入 python -m venv 环境名 创建虚拟环境&nbs
- ftp登陆连接from ftplib import FTP #加载ftp模块ftp=FTP() &n
- 'subject 信息标题 'company 发布信息的公司名称 'cont
- 一、前言听说python很流行,因为有很多模块资源,而且导入模块,操作和理解起来很简单。所以在这里记录一下学习python的过程,我相信最重
- 本文实例讲述了python实现超简单端口转发的方法。分享给大家供大家参考。具体如下:代码非常简单,实现了简单的端口数据转发功能,用于真实环境
- 案例故事:大部分带彩色屏幕的终端设备,不管是手机,车机,电视等等,都需要涉及图片的显示,作为一名专业的多媒体测试人员,我们需要一堆的规范化标