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

我们先来看一下一条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 参数包含多个类型、字符集类型或校对规则不一致等

参考
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

到此这篇关于Mysql 5.6 “隐式转换”导致的索引失效和数据不准确的问题的文章就介绍到这了,更多相关Mysql 5.6隐式转换导致的索引失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 解析MySQL隐式转换问题

    解析MySQL隐式转换问题

    一.问题描述 root@mysqldb 22:12: [xucl]> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.0

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

  • mysql的in会不会让索引失效?

    mysql的in会让索引失效吗?不会! 看结果: mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_t

  • 谈谈MySQL中的隐式转换

    工作过程中会遇到比较多关于隐式转换的案例,隐式转换除了会导致慢查询,还会导致数据不准.本文通过几个生产中遇到的案例来. 基础知识 关于比较运算的原则,MySQL官方文档的描述: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html 如果 判断符号左右两边有一个为NULL,结果就是null,除非使用安全的等值判断 <=> (none) 05:17:16 >select  null = null; +------------

  • mysql索引失效的五种情况分析

    mysql索引失效的五种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况 show status lik

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

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

    背景 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢.这个varchar字段有一个复合索引.其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据. 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字.不然就会报错 还有可能查出来的数据不是我们想要的数据.如下图 分析 从执行结果来

  • 浅谈MySql整型索引和字符串索引失效或隐式转换问题

    目录 问题概述 问题重现 问题引申 结论 问题概述 今天在上班时,DBA突然找出来一段sql,表示该sql存在隐式转换,不走索引.经过我们的查看后,发现是类型varchar的字段, 我们使用条件传入了数值型的值,由于担心违反保密协议,在此就不贴图了,由我重现一下类似情况给大家看一下. 问题重现 首先我们先创建一张用户表test_user,其中USER_ID为了效果我们设置为varchar类型且加上唯一索引. CREATE TABLE test_user ( ID int(11) NOT NULL

  • MySQL隐式类型转换导致索引失效的解决

    目录 问题 复现 隐式转换 总结 参考 问题 在工作中发现,有一个接口只执行一条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 COM

  • MySQL令人大跌眼镜的隐式转换

    MySQL令人大跌眼镜的隐式转换

    目录 MySQL的隐式转换 一.问题描述 二.源码解释 三.结论 MySQL的隐式转换 一.问题描述 show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1

  • MySQL索引失效之隐式转换的问题

    目录 常见索引失效: 一.常见索引失效场景 1.条件字段函数操作 2.条件字段运算操作 3.隐式类型转换 4.隐式字符编码转换 二.类型转换 1.字符串转整型 2.时间类型转换 常见索引失效: 1. 条件索引字段"不干净":函数操作.运算操作 2. 隐式类型转换:字符串转数值:其他类型转换 3. 隐式字符编码转换:按字符编码数据长度大的方向转换,避免数据截取 一.常见索引失效场景 root@test 10:50 > show create table t_num\G ******

  • Java基础之隐式转换vs强制转换

    Java中,经常可以遇到类型转换的场景,从变量的定义到复制.数值变量的计算到方法的参数传递.基类与派生类间的造型等,随处可见类型转换的身影.Java中的类型转换在Java编码中具有重要的作用. 在定义变量时,有许多要注意的问题,一不小心就会出现损失精度或者不兼容类型等问题. 例如: 1.定义长整型数据时,必须加后缀l或L long l =123456789012345L 2.定义单精度类型时(7-8位有效数字),必须加后缀 f 或 F float f = 12.5F 3. boolean类型不可

  • SQL Server 中的数据类型隐式转换问题

    写这篇文章的时候,还真不知道如何取名,也不知道这个该如何将其归类.这个是同事遇到的一个案例,案例比较复杂,这里抽丝剥茧,仅仅构造一个简单的案例来展现一下这个问题.我们先构造测试数据,如下所示: CREATE TABLE TEST ( ID INT, GOOD_TYPE VARCHAR(12), GOOD_WEIGHT NUMERIC(18,2) ) INSERT INTO dbo.TEST VALUES( 1, 'T1',1.27) SELECT GOOD_TYPE, CASE WHEN ( G

  • 隐式转换引起的sql慢查询实战记录

    隐式转换引起的sql慢查询实战记录

    引言 实在很无语呀,遇到一个mysql隐式转换问题,问了周边的dba大拿该问题,他们居然反问我,你连这个也不知道?白白跟他们混了那么长   尼玛,我还真不知道.罪过罪过-. 问题是这样的,一个字段叫task_id, 本身是varchar字符串类型,但是因为老系统时间太长了,我以为是int或者bigint,所以直接在代码写sql跑数据,结果等了好久就是没有反应,感觉要坏事呀.在mysql processlist里看到了该sql语句,直接kill掉. 该字段是有索引的,并且他的sql选择性很高,索引

随机推荐