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

梦莱2年前技术文章1059

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


相关文章

sparksql集成ranger权限测试

sparksql集成ranger权限测试

##启动thriftserver cd /opt/dtstack/spark/spark_pkg/sbin ./start-thriftserver.sh \ --master yarn \...

MySQL运维实战之备份和恢复(8.7)将数据库恢复到指定时间点的另外一种方法

使用mysql原生复制功能实现时间点恢复使用mysqlbinlog解析并执行binlog是实现mysql时间点恢复的一种常用的方法。这里提供另外一种实现时间点恢复的方法:使用mysql的复制功能来实现...

MySQL运维实战(3.2) 常见数据库连接失败问题排查

如果数据库连接失败,可以从如下几方面来排查:1、客户端到服务端的网络是否畅通,服务端端口是否能连通。使用ping、telnet等工具探测服务端的端口是否能访问。[root@box3 ~]#&...

ES运维(一)底层数据存储原理

ES运维(一)底层数据存储原理

1、ES底层数据存储原理架构图Segment工作流程:A、 新的文档在内存中组织B、 每隔一段时间,buffer将会被提交:生成一个新的segment(一个额外的新的倒序索引)并被写到磁盘,同时一个新...

副本集同步原理

一、Initial Sync大体来说,MongoDB副本集同步主要包含两个步骤:1. Initial Sync,全量同步2. Replication,即sync oplog先通过init sync同步...

可观测未来OpenTelemertry-结构化数据价值

可观测未来OpenTelemertry-结构化数据价值

前言开源软件和云供应商的软件开发模式已经改变了我们构建和部署软件的方式。集成开源软件,我们可以在很短时间内构建和部署一个应用程序。但这并不意味着使用和维护它们也变得更简单,随着应用程序的扩充,程序的调...

发表评论    

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