MySQL性能优化(八)exists子查询优化一例

俊达2周前技术文章16


一个例子

SELECT u.id userId,       u.mobile,       u.created_date createdDate 
FROM `user` u
    LEFT JOIN user_cash_detail ucd ON u.id= ucd.user_id
WHERE 1= 1
AND EXISTS( SELECT 1 FROM borrow b WHERE b.user_id= u.id AND b.borrow_no LIKE '202001011212XXX%') 
ORDER BY u.id limit 13

这个查询需要执行120秒,


执行计划:

8-1.png


从执行计划中来看都有这良好的索引,而且需要扫描的rows 也很少,此条SQL性能应该比较好,为什么会执行需要120s 的时间呢?

此时需要了解下exists查询的执行过程:先执行主查询,然后用主查询的结果,检查exists子句中的子查询是否能匹配。

如果主表返回的纪录数比较大,exists 运行的次数就会大,导致整个查询的运行时间长。


在上面这个例子中,exists子查询中有一个b.borrow_no的条件,看起来过滤性比较高,可以尝试对SQL进行改写:

SELECT distinct u.id userId,       u.mobile,       u.created_date createdDate  
FROM borrow b 
join `user` u on b.user_id= u.id 
where b.borrow_no LIKE '202001011212XXX%' order by u.id limit 13


执行计划:

8-2.png


将exists子查询改写为常规的表关联,有几个地方需要注意:

1、原始SQL中并没有使用到user_cash_detail表,可以将left join去掉。

2、Exists 改成join后,由于borrow表user_id不唯一,SQL改写后可能会有重复数据出现,需要添加distinct,


SQL改写后,执行时间从120秒提升到3毫秒。


总结

SQL优化的一个基本思想是尽早过滤尽可能多的数据。这本例中,先执行exists中的子查询可以提前过滤掉大量数据。当然有时候需要对SQL进行一些改写。

相关文章

如何用 mysqldump 备份数据

前言mysqldump 是 MySQL 官方提供的一款逻辑备份工具,它将生成一组可以导入数据 库中以重现原始数据库中的数据和数据库对象的SQL语句。可用于备份恢复、表结构导出、备份上云。本 SOP 介...

使用clickhouse-copier迁移数据

说明clickhouse-copier是clickhouse官方提供的一个数据迁移工具。支持将clickhouse表从一个集群迁移到另外一个集群。使用clickhouse-copier有一些限制条件:...

Clickhouse冷热数据分离实践

配置多卷存储策略使用Clickhouse的存储策略功能,可以实现冷热数据分离存储。我们可以将业务上访问频繁的数据放到热存储区(如高性能SSD磁盘),将业务上较少访问的数据放在冷存储区(如价格更便宜、空...

Clickhouse MergeTree原理(二)—— 表和分区的维护

MergeTree是Clickhouse中最核心的存储引擎。上一篇文章中,我们介绍了MergeTree的基本结构。1、MergeTree由分区(partiton)和part组成。2、Part是Merg...

ES运维(三)架构与规划(阿里云)

ES运维(三)架构与规划(阿里云)

1、 阿里云Elasticsearch架构图阿⾥云Elasticsearch和Kibana容器化运⾏在ECS中,监控agent(独⽴进程)负责收集监控指标,通过SLS发送给云监控完成监控报警。实例之间...

harbor数据迁移-SOP

harbor数据迁移-SOP

背景线下自建harbor需要迁移至云上自建harbor迁移方案harbor私有仓库的主从复制实现数据迁移前置条件harbor目标仓库已部署好,并且版本和源仓库版本最好保持一致迁移步骤1、配置slave...

发表评论    

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