SQL隐式转换导致索引失效_数据类型不一致

梦莱2年前技术文章1077

2.数据类型不一致导致索引失效

示例 SQL 如下,SQL 本身很简单,但通过查看执行计划可以发现,此时走的是主键索引,查看表结构发现表的 kemu 是有索引的,且过滤性相对较好。进一步核实,SQL 为何没有走 kemu 索引。

--当前 SQL
SELECT *
FROM XXX
WHERE kemu = 18                       # 隐式转换
    AND XXX LIKE '%0%'
    AND id NOT IN (47193)
ORDER BY id ASC
LIMIT 2

通过 force index 测试,发现还是无法走上索引。此时,开始猜测是否出现了隐式转换。通过 show warnings 可以看到,Cannot use ref access on index 'kemu' due to type or collation conversion on field 'kemu';此时我们可以知道,由于数据类型或者字符集的原因导致了索引不可用。

后续通过查看表数据结构发现,该列的数据类型为 varchar 类型,而 SQL 中 where 条件在使用时,没有加 '' 号。此时,MySQL是将字符串转换成数字,即此处的 kemu 被CAST(kemu AS signed int),这就相当于对条件字段做了函数操作,优化器放弃⾛树索引定位。

反馈优化意见,kemu 字段为 varchar 类型,SQL查询 where 条件中 kemu = 18存在隐式转换,建议对 SQL 进行改写,具体改写 SQL如下:

--优化后 SQL
SELECT *
FROM XXX
WHERE kemu = '18' # 这里加上了 ''
    AND XXX LIKE '%0%'
    AND id NOT IN (47193)
ORDER BY id ASC
LIMIT 2


相关文章

Linux网络扫描和嗅探工具—Nmap

1、简介Nmap,也就是Network Mapper,是Linux下的网络扫描和嗅探工具包。它由Fyodor编写并维护。由于Nmap品质卓越,使用灵活,它已经是渗透测试人员必备的工具。其基本功能有三个...

stress压测工具

1、stress 概述stress是一个linux的压力测试工具,主要用来模拟系统负载较高时的场景,用于对系统的CPU、IO、内存、负载、磁盘等进行压力测试2、安装yum install -y epe...

Mysql备份及恢复-XtraBackup

Mysql备份及恢复-XtraBackup

1、下载备份软件#不能使用8.0的版本,不支持mysql8.0之前的备份wget -c https://downloads.percona.com/downloads/Percona-XtraBack...

SQL隐式转换导致索引失效_校验规则不一致

4.校验规则不一致导致索引失效首先可以通过检查表的校验规则核实是否存在隐式转化。--查看关联字段的字符集、校验规则SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAM...

kafka高可靠性相关配置

kafka高可靠性相关配置

为保证高可靠可以通过以下方面进行设置:1) 物理机器场景配置项配置说明高可靠高性能不间断电源配置,防止服务器异常断电RAID卡电池配置,防止服务器异常断电RAID卡写缓存开启,提高性能RAID 1配置...

CDH实操--CDH集成flink 1.13.6(二)

CDH实操--CDH集成flink 1.13.6(二)

一、编译flink1、下载flink1.13.6源码包wget https://mirrors.tuna.tsinghua.edu.cn/apache/flink/flink-1.13.6/flink...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。