SQL隐式转换导致索引失效_函数

梦莱2年前技术文章1471

一、隐式转换分类

1.函数

2.数据类型

3.字符集

4.校验规则

二、常见案例

本节将会针对第一部分提到的四种隐式转换内容,举例说明。

1.索引列使用函数导致索引失效

示例 SQL 如下,该 SQL 的 where 条件中 created_time 列外面带有 DATE() 函数。通过查看表结构发现,该列有索引,且过滤性较好。核实 DATE(t.created_time) = DATE_SUB(curdate(), INTERVAL 0 DAY) where 条件所表达的意义,核实为 create_time 取当天日期数据。

--当前 SQL
SELECT COUNT(DISTINCT t.mobile) 
FROM  t
WHERE DATE(t.created_time) = DATE_SUB(curdate(), INTERVAL 0 DAY)
AND t.loan_id = 'XXX'
GROUP BY t.loan_id, DATE(t.created_time) 

图片11.png

核实 SQL 走不上 create_time 索引原因:对索引字段做函数操作,即 where 条件列上不⼲净时,可能会破坏索引值的有序性(按照 created_time 的值有序组织索引树),因此优化器就决定放弃⾛索引树搜索功能。

补:虽然+1这种操作并没有破坏索引的有序性,但优化器仍然不会使⽤该索引快速定位。因此,等号左边,注意优化掉索引字段上的运算操作。

但条件字段函数操作下,也并⾮完全的⾛全表扫描,优化器并⾮完全的放弃该字段索引。在 SQL 合适情况下,优化器可以选择遍历该索引树,使⽤覆盖索引(Using index),避免回表从而达到优化效果。

反馈 SQL 优化建议,建议把 created_time 列外面的 DATE() 函数去掉,进行 SQL 的改写。举例改写 SQL 如下(在客户环境下跑了一下,因为数据在不停增加,无法得到准确数据是否一致。需客户方自行核实是否可以进行如下改写):

--优化后 SQL
SELECT COUNT(DISTINCT t.mobile)
FROM t
WHERE t.created_time<date(curdate())+1 and t.created_time >=date(curdate())
AND t.loan_id = 'XXX'
GROUP BY t.loan_id, DATE(t.created_time)


相关文章

MySQL 组复制一致性保证

MySQL 组复制一致性保证

说明本篇文章介绍,MySQL 组复制作为一个分布式系统,如何保证事务一致性?1. 一致性级别MySQL 8.0.14 版本开始,提供组复制变量:group_replication_consistenc...

开源大数据集群部署(十八)Hive 安装部署

开源大数据集群部署(十八)Hive 安装部署

2.7.1创建hive Kerberos主体bash /root/bigdata/getkeytabs.sh /etc/security/keytab/hive.keytab hive2.7.2 安装...

MySQL 复制-无数据环境搭建异步复制

MySQL 复制-无数据环境搭建异步复制

全新初始化(新环境,无数据)搭建 MySQL 异步复制 & GTID 复制标准文档。一、前言环境说明:操作系统 CentOS 7  & 数据库版本 5.7.32参数要求:主库必须开启...

MySQL 通过 MTS 多线程恢复增量日志备份

MySQL 通过 MTS 多线程恢复增量日志备份

一、前言当发生 DDL 误删数据时,只能通过 备份 + 增量日志 来恢复,备份恢复到新实例后,将增量日志备份进行应用,此时只能单线程效率不高,我们可以使用 MySQL 5.7 推出的 MTS 多线程复...

Scheduler调度器

一、论 Pod 调度在 kubernetes 中,无论是 Deployment、Statefulset 等多种控制器,它最终都是创建 Pod,在 Pod 创建是需要被调度到 Kubernetes 集群...

Hadoop 重新编译-解决root用户提交任务报错Running as root is not allowed

Hadoop 重新编译-解决root用户提交任务报错Running as root is not allowed

本文主要解决Hadoop root用户无法提交问题Running as root is not allowedLinux架构:arm或者x86都可以Jdk版本:jdk1.8cmake版本:3.19Ha...

发表评论    

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