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

最近，线上的 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：

+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
| id | select_type        | table                   | partitions | type  | possible_keys  | key            | key_len | ref   | rows      | filtered | Extra       |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+
|  1 | UPDATE             | t_order_record          | NULL       | index | NULL           | PRIMARY        | 8       | NULL  | 668618156 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t_retailer_order_record | NULL       | ref   | idx_archive_id | idx_archive_id | 195     | const |         1 |    10.00 | Using where |
+----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+

发现 t_order_record 的索引使用有问题，这很奇怪：

t_order_record 在 order_id 上面是有索引的，但是这里走的是主键全扫描（主键不是 order_id 而是 id）
子查询中其实只命中了 3 万多条数据。

一般出现这种情况，肯定又是 SQL 优化器作妖了。

这也不能完全怪 SQL 优化器

我们在日常开发与设计表的时候，很难避免会有一些不合理的使用情况，会有很多索引，可能还会出现 large row。这种千奇百怪的情况中，SQL 优化器需要找到最优的方案确实很难。举一个简单的例子：假设我们有一张表，包含主键 id，有 id = 1 的一条记录，一年后，有了 id = 1000000 的一条记录。然后这时我们同时更新了 id = 1 和 id = 1000000 的记录，那么某个通过其他索引但是命中只有 id = 1 和 id = 1000000 的数据很可能不走索引而是主键搜索。因为最近的更新导致这两条数据跑到了同一页上并且在内存中。

SQL 优化器考虑了很多这种复杂的情况，能在大部分情况下优化 SQL 为更适应当前情况的，但是由于逻辑过于复杂导致某些简单情况下优化的反而很差，这就需要我们根据 OPTIMIZER TRACE 的结果进行手动优化。

使用测试数据库进行 OPTIMIZER TRACE，先分析索引分析前的步骤是否有问题

由于 Optimizer_trace 需要 SQL 真正执行，但是这个 SQL 执行不出来了。Optimizer_trace 可以分析优化器的全步骤，我们可以先在一个数据量很少的测试环境，看看在进入统计数据分析前（例如分析索引的离散型数据来决定走哪个索引，这个用测试环境模拟不出来，因为数据和线上肯定有差异，即使复制线上的数据也不行，因为数据在哪些页，索引经过怎样的更新，文件结构和线上不同，统计器的信息肯定不会完全一样），SQL 改写转换是否有问题。

执行：

mysql> set session optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.20 sec)

mysql>  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');
Query OK, 0 rows affected (2.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE;

steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select `main`.`t_retailer_order_record`.`order_id` from `main`.`t_retailer_order_record` FORCE INDEX (`idx_archive_id`) where (`main`.`t_retailer_order_record`.`archive_id` = '420a7fe7-4767-45e8-a5f5-72280c192faa')"
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "chosen": false
            }
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "chosen": true,
              "evaluating_constant_where_conditions": [
              ]
            }
          }
        ]
      }
    },
    {
      "substitute_generated_columns": {
      }
    },
    {
      "condition_processing": {
        "condition": "WHERE", 
        ## 以下省略

通过 Optimizer_trace 我们发现，优化有问题！将 IN 优化成了 EXISTS。这样导致本来我们想的是使用子查询的每一条记录，去匹配外层订单表的记录，变成了遍历外层订单表的每一条记录，去看是否存在于子查询中，这也解释了为啥 explain 的结果是通过主键遍历订单表的每一条记录进行查询。

这个要改的话，只能改变写法来适应，没法通过关闭优化器选项来实现

于是，我们改写并优化 SQL (使用 JOIN，JOIN 是最接近最容易被优化器理解的编写 SQL 的方式)，并且加上了时间条件（我们本身就想只操作 179 天前的数据，这个 archive_id 对应的数据都是 179 天前的），由于订单 id 中本身就带时间（以时间开头，例如 211211094621ord123421 代表 2021 年 12 月 11 日 9 点 46 分 21 秒的一个订单），所以用订单 id 限制时间：

UPDATE t_order_record
JOIN t_retailer_order_record ON t_order_record.order_id = t_retailer_order_record.order_id 
SET t_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa',
t_order_record.update_time = t_order_record.update_time 
WHERE
	t_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' ) 
	AND t_retailer_order_record.order_id < DATE_FORMAT( now() - INTERVAL 179 DAY, '%y%m%d' ) 
	AND t_retailer_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa'

后续优化经验

如果再遇到这种执行很慢但是实际上更新命中很少数据并且该有的索引都有的情况，可以先在一个数据量很少的测试环境，看看在进入统计数据分析前（例如分析索引的离散型数据来决定走哪个索引，这个用测试环境模拟不出来，因为数据和线上肯定有差异，即使复制线上的数据也不行，因为数据在哪些页，索引经过怎样的更新，文件结构和线上不同，统计器的信息肯定不会完全一样），SQL 改写转换是否有问题。

如果有问题，考虑人为干预手动优化。手动优化的方式包括：