最新公告
  • 欢迎您光临吆喝资源网,本站提供站长亲测的网站源码下载,打造专业的源码网!立即加入我们
  • 解决Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    mysql视频教程栏目介绍解决Mysql 5.6索引失效和数据不准确问题

    相关免费学习推荐:mysql视频教程

    背景

    • 在一次进行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

    我们先来看一下一条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. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
    2. 两个参数都是字符串,会按照字符串来比较,不做类型转换
    3. 两个参数都是整数,按照整数来比较,不做类型转换
    4. 十六进制的值和非数字做比较时,会被当做二进制串
    5. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
    6. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
    7. 所有其他情况下,两个参数都会被转换为浮点数再进行比较

    2. 分析实际遇到的情况

    1.那我们也就清楚了,上面我提出的例子是整数和字符串的比较,那就属于其他情况了。那我们就先来分析一下索引失效的原因

    • 由于属于隐式转换的其他情况,所以对比值都得转换为浮点数进行比较
    • 我们先将查询条件值进行转换为浮点数,再着将表的记录值也得进行转换,所以这个时候此前已经创建好的索引排序已经不能生效了。因为隐式转换(函数)已经改变了原来的值,所以说优化器在这里就直接不选用索引,直接使用全表扫描。

    2.查询出不匹配的值(或者说是部分匹配的值),如上面的查询结果。这真得看看源码了,这也就是MYsql的隐式转换规则。这里不就细分析了(因为没有查到相关的文档)
    由于历史原因,需要兼容旧的设计,可以使用 MySQL 的类型转换函数 cast 和 convert,来明确的进行转换。
    总结

    • 隐式转换和函数的使用会导致索引失效和select出的数据不准确
    • 隐式转换的发生条件以及规则
    • 隐式转换导致索引失效的具体原因,由于需要将对比值都要进行类型转换导致失效。
    • 避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、in 参数包含多个类型、字符集类型或校对规则不一致等

    想了解更多编程学习,敬请关注php培训栏目!

    以上就是解决Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题的详细内容,更多请关注吆喝资源网其它相关文章!

    本文转自PHP中文网,吆喝网(http://yaoohe.com)搜集整理,如有疑问请联系本站客服!

    1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
    2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
    3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
    4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
    5. 如有链接无法下载、失效或广告,请联系管理员处理!
    6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
    7. 如遇到加密压缩包,默认解压密码为"yaoohe.com",如遇到无法解压的请联系管理员!
    8. 特别声明:本站源码除标明“已测试”外,其它都未测试,有BUG概本站概不负责。
    吆喝资源网 » 解决Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    吆喝资源网
    一个高级程序员模板开发平台

    发表评论

    • 6518会员总数(位)
    • 19045资源总数(个)
    • 100本周发布(个)
    • 41 今日发布(个)
    • 1793稳定运行(天)

    专业的网站源码网,提供精品php网站源码下载,小程序源码下载等!!

    小程序源码 了解详情
    升级SVIP尊享更多特权立即升级