专栏首页用户4352451的专栏Mysql 5.6 “隐式转换”导致的索引失效和数据不准确

Mysql 5.6 “隐式转换”导致的索引失效和数据不准确

背景

  • 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。
  • 使用的是mysql 5.6版本,innoDB引擎

实际情况如下

  • 下面我们来看一下执行的结果
  • 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字。不然就会报错
  • 还有可能查出来的数据不是我们想要的数据。如下图

分析

  1. 从执行结果来看,使用了单引号的走了对应的索引。没有使用单引号的没有走索引,进行了全表扫描。
  2. 为什么会这样呢? mysql的优化器怎么不直接进行类型转换呢?
  • 在SQL语句中单引号的引入也就是代表这个类型是字符串数据类型CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM,和 SET。。
  • 不加单引号也就代表这是一个字符串之外的类型,如int,bigDecimal类型等
  • 如果给一串有字幕和特殊符号的字符串不加单引号,后果就是类型转换失败导致SQl不能执行。如上图所述:
1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s
  1. 我们先来看一下一条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. 产生条件

  1. 当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。则会发生转换隐式
  2. 发生隐式转换的条件:
    • 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
    • 两个参数都是字符串,会按照字符串来比较,不做类型转换
    • 两个参数都是整数,按照整数来比较,不做类型转换
    • 十六进制的值和非数字做比较时,会被当做二进制串
    • 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
    • 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
    • 所有其他情况下,两个参数都会被转换为浮点数再进行比较

2. 分析实际遇到的情况

  1. 那我们也就清楚了,上面我提出的例子是整数和字符串的比较,那就属于其他情况了。那我们就先来分析一下索引失效的原因
    1. 由于属于隐式转换的其他情况,所以对比值都得转换为浮点数进行比较
    2. 我们先将查询条件值进行转换为浮点数,再着将表的记录值也得进行转换,所以这个时候此前已经创建好的索引排序已经不能生效了。因为隐式转换(函数)已经改变了原来的值,所以说优化器在这里就直接不选用索引,直接使用全表扫描。
  2. 查询出不匹配的值(或者说是部分匹配的值),如上面的查询结果。这真得看看源码了,这也就是MYsql的隐式转换规则。这里不就细分析了(因为没有查到相关的文档) 由于历史原因,需要兼容旧的设计,可以使用 MySQL 的类型转换函数 cast 和 convert,来明确的进行转换。

总结

  1. 隐式转换和函数的使用会导致索引失效和select出的数据不准确
  2. 隐式转换的发生条件以及规则
  3. 隐式转换导致索引失效的具体原因,由于需要将对比值都要进行类型转换导致失效。
  4. 避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 常用的数据库应用设计优化方法

    vincehuang
  • 值得收藏:一份非常完整的 MySQL 规范(一)

    7.禁止在表中建立预留字段预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型,所以无法选择合适的类型 对预留字段类型的修改,会对表进行锁定

    码农编程进阶笔记
  • Mysql服务器SQL模式 (官方精译)

    MySQL服务器可以在不同的SQL模式下运行,并且可以根据sql_mode系统变量的值对不同的客户端应用不同的模式。DBA可以设置全局SQL模式以匹配站点服务器...

    sunsky
  • 技术分享 | 常见索引问题处理

    数据库技术爱好者,爱可生 DBA 团队成员,负责 MySQL 日常问题处理以及数据库运维平台的问题排查,擅长 MySQL 主从复制及优化,喜欢钻研技术问题,还有...

    爱可生开源社区
  • 一份完整的 MySQL 开发规范,进大厂必看!

    https://www.cnblogs.com/huchong/p/10219318.html

    Java技术栈
  • 一份完整的 MySQL 开发规范,进大厂必看!

    https://www.cnblogs.com/huchong/p/10219318.html

    良月柒
  • 史上最全的MySQL高性能优化规范建议

    没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5...

    秃头哥编程
  • MySQL高性能优化规范建议,值得收藏

    •所有数据库对象名称必须使用小写字母并用下划线分割•所有数据库对象名称禁止使用 MySQL 保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)•数...

    用户1516716
  • MySQL高性能优化规范建议,速度收藏

    •所有数据库对象名称必须使用小写字母并用下划线分割•所有数据库对象名称禁止使用 MySQL 保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)•数...

    用户5224393
  • 值得收藏:一份非常完整的 MySQL 规范

    · 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)

    Python数据科学
  • 11条MySQL规范,你知道的有几个?

    · 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)

    程序员追风
  • 值得收藏:一份非常完整的 MySQL 规范

    · 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)

    Java团长
  • 值得收藏:一份非常完整的 MySQL 规范

    · 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)

    芋道源码
  • 值得收藏:一份非常完整的 MySQL 规范

    · 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)

    Java团长
  • 关于Mysql高性能优化规范的一点建议

    没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5...

    极乐君
  • Mysql高性能优化规范建议

    没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5...

    lyb-geek
  • 面试官:你对MySQL高性能优化有什么规范建议?

    没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5...

    Java_老男孩
  • 今儿聊一聊Mysql的性能优化

    没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5...

    程序员小明
  • MySQL高性能优化规范建议

    没有特殊要求(即 Innodb 无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用 Innodb 存储引擎(MySQL5.5 之前默认使用 My...

    一点博客

扫码关注云+社区

领取腾讯云代金券