由一次 UPDATE 过慢 SQL 优化而总结出的经验

最新推荐文章于 2024-08-20 14:16:33 发布

一堆土豆33

最新推荐文章于 2024-08-20 14:16:33 发布

阅读量1.2k

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.youkuaiyun.com/xhbzl/article/details/126598893

版权

本文讲述了在线上ETL数据归档时遇到的UPDATE SQL执行缓慢的问题，通过对SQL的EXPLAIN、PROFILING和OPTIMIZER TRACE分析，发现优化器将IN优化为EXISTS导致效率低下。在测试环境中模拟并手动优化SQL，最终通过JOIN和时间条件限制提高性能，总结出在SQL优化中手动干预的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近，线上的 ETL 数据归档 SQL 发生了点问题，有一个 UPDATE SQL 跑了两天还没跑出来：

 update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time  where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa')

这个 SQL 其实就是将 t_retailer_order_record 中 archive_id为
420a7fe7-4767-45e8-a5f5-72280c192faa 的所有记录的订单 id order_id，对应的订单表中的记录的 archive_id 也更新为 420a7fe7-4767-45e8-a5f5-72280c192faa 并且更新时间保持不变（因为表上有 update_time 按当前时间更新的触发器）。

对于 SQL 的优化，我们可以使用下面三个工具进行分析：

EXPLAIN：这个是比较浅显的分析，并不会真正执行 SQL，分析出来的可能不够准确详细。但是能发现一些关键问题。
PROFILING：通过 set profiling = 1 开启的 SQL 执行采样。可以分析 SQL 执行分为哪些阶段，并且每阶段的耗时如何。需要执行并且执行成功 SQL，并且分析出来的阶段不够详细，一般只能通过某些阶段是否存在如何避免这些阶段的出现进行优化（例如避免内存排序的出现等等）。
OPTIMIZER TRACE：详细展示优化器的每一步，需要执行并且执行成功 SQL。MySQL 的优化器由于考虑的因素太多，迭代太多，配置相当复杂，默认的配置在大部分情况没问题，但是在某些特殊情况会有问题，需要我们进行人为干预。

首先，我们针对这个 SQL 进行 EXPLAIN：

+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------