解决Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

雨浩大大_8796

雨浩大大_8796

2020-12-07

3993人浏览

转载

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的执行过程

在这里插入图片描述

(网图)

WindClaw
WindClaw

一款AI工具,主要用于万得(Wind)推出的专注于金融投资领域的AI智能体工具,适合需要提升相关任务效率的用户。

下载
  • 我们先得出结论:如果对索引字段做函数操作(本例是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培训栏目!

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
SQL优化与排查(MySQL版)
SQL优化与排查(MySQL版)

共26课时 | 3.3万人学习