Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题
作者:Jeff、yuan 发布时间:2024-01-22 04:07:47
背景
在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。
使用的是mysql 5.6版本,innoDB引擎 实际情况如下
下面我们来看一下执行的结果
在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字。不然就会报错
还有可能查出来的数据不是我们想要的数据。如下图
分析
从执行结果来看,使用了单引号的走了对应的索引。没有使用单引号的没有走索引,进行了全表扫描。
为什么会这样呢? mysql的优化器怎么不直接进行类型转换呢?
在SQL语句中单引号的引入也就是代表这个类型是字符串数据类型CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM,和 SET。。
不加单引号也就代表这是一个字符串之外的类型,如int,bigDecimal类型等
如果给一串有字幕和特殊符号的字符串不加单引号,后果就是类型转换失败导致SQl不能执行。
如上图所述:
1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s
我们先来看一下一条SQL的执行过程
(网图)
我们先得出结论:如果对索引字段做函数操作(本例是cast函数做了隐式的转换),可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。(https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
意思也就是:请注意,如果您使用BINARY,CAST()或CONVERT()转换索引列,则MySQL可能无法有效使用索引。
查出来的数据不准确,也是因为隐式转换,转换后导致数值类型不一样,导致不等变为相等。
隐式转换
1. 产生条件
当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。则会发生转换隐式
发生隐式转换的条件:
两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
两个参数都是字符串,会按照字符串来比较,不做类型转换
两个参数都是整数,按照整数来比较,不做类型转换
十六进制的值和非数字做比较时,会被当做二进制串
有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
所有其他情况下,两个参数都会被转换为浮点数再进行比较
2. 分析实际遇到的情况
1.那我们也就清楚了,上面我提出的例子是整数和字符串的比较,那就属于其他情况了。那我们就先来分析一下索引失效的原因
由于属于隐式转换的其他情况,所以对比值都得转换为浮点数进行比较
我们先将查询条件值进行转换为浮点数,再着将表的记录值也得进行转换,所以这个时候此前已经创建好的索引排序已经不能生效了。因为隐式转换(函数)已经改变了原来的值,所以说优化器在这里就直接不选用索引,直接使用全表扫描。
2.查询出不匹配的值(或者说是部分匹配的值),如上面的查询结果。这真得看看源码了,这也就是MYsql的隐式转换规则。这里不就细分析了(因为没有查到相关的文档)
由于历史原因,需要兼容旧的设计,可以使用 MySQL 的类型转换函数 cast 和 convert,来明确的进行转换。
总结
隐式转换和函数的使用会导致索引失效和select出的数据不准确
隐式转换的发生条件以及规则
隐式转换导致索引失效的具体原因,由于需要将对比值都要进行类型转换导致失效。
避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、in 参数包含多个类型、字符集类型或校对规则不一致等
参考
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/
https://zhuanlan.zhihu.com/p/95170837
来源:https://blog.csdn.net/weixin_40413961/article/details/110743406
猜你喜欢
- 前言前段时间我有个朋友看到一些小姐姐的照片,想全部下载下来,叫我帮个忙。于是花费了半天给他全部下载了下来。引入库import timeimp
- Python中对象的行为是由它的类型 (Type) 决定的。所谓类型就是支持某些特定的操作。数字对象在任何编程语言中都是基础元素,支持加、减
- asyncio的关键字说明event_loop事件循环:程序开启一个无限循环,把一些函数注册到事件循环上,当满足事件发生的时候,调用相应的协
- 前言我们在日常文本处理中,经常会将数据结构保存在列表中,如果将列表中的项进行关联,创建我们想要的字典结构,存取就会十分方便!示例详解比如说将
- 安装jenkins安装jenkins很简单,可以用多种方式安装,这里知道的有:在官网下载rpm包,手动安装,最费事centos系统通过yum
- 引言现在本地创建一个excel表,以及两个sheet,具体数据如下:sheet1: sheet2:读取excel文件pandas.
- 前言手里有一点点公司的股票, 拿不准在什么时机抛售, 程序员也没时间天天盯着看,不如动手写个小程序, 把股票趋势每天早上发到邮箱里,用 py
- 我的读者知道我是一个喜欢痛骂Python3 unicode的人。这次也不例外。我将会告诉你用unicode有多痛苦和为什么我不能闭嘴。我花了
- 统计十篇新闻TF-IDF统计TF-IDF词频,每篇文章的 top10 的高频词存储为 json 文件TF-IDFTF-IDF(term fr
- 静态页面由于其稳定性快速性,的确给SE、用户及站长带来了方便。但有时,需要记住用户的信息,如用户留下评论后,下一次再来,就要记住该用户的信息
- # -*- coding: utf8 -*- #! python print(repr("测试报警,xxxx是大猪头".
- 1.数据结构说明:数据结构如下:modification字段为修改数据时间字段,格式为 年,月,日,时,分,秒。案例场景为,根据modifi
- 网站能切换几套CSS风格早已不是什么新鲜事了。大家也都知道怎么去弄。早上发现一个有意思得站点 http://www.leemunroe.co
- XMLHTTP对象及其方法------------------MSXML中提供了Microsoft.XMLHTTP对象,能够完成从数据包到R
- 目录OpenCV先决条件我们会在本文中涵盖7个主题读,写和显示图像imread():imshow():imwrite():读取视频并与网络摄
- slice 可以用来获取数组片段,它返回新数组,不会修改原数组。除了正常用法,slice 经常用来将 array-like 对象转换为 tr
- 本文简单的分析了Python中常用的内置函数的用法,分享给大家供大家参考之用。具体分析如下:一般来说,在Python中内置了很多有用的函数,
- 为方便维护和实现开放性,我把调查的好几个主题都放到同一个数据库的同一个表名当中去了但问题是在查询其中一个调查主题时,往往还会显示不相关主题的
- 导言:在前面的3章里我们为处理二进制数据添加了很多的功能。我们首先在表Categories里添加BrochurePath列,并更新了体系结构
- 处理excel表格的时候经常遇到合并单元格的情况,使用xlrd中的merged_cells的方法可以获取当前文档中的所有合并单元格的位置信息