最近,线上的 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:
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------