乐趣区

关于mysql:由一次-UPDATE-过慢-SQL-优化而总结出的经验

jiezi

最近，线上的 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 的优化，咱们能够应用上面三个工具进行剖析：

  1. EXPLAIN：这个是比拟通俗的剖析，并不会真正执行 SQL，剖析进去的可能不够精确具体。然而能发现一些关键问题。
  2. PROFILING：通过 set profiling = 1 开启的 SQL 执行采样。能够剖析 SQL 执行分为哪些阶段，并且每阶段的耗时如何。须要执行并且执行胜利 SQL，并且剖析进去的阶段不够具体，个别只能通过某些阶段是否存在如何防止这些阶段的呈现进行优化（例如防止内存排序的呈现等等）。
  3. 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 的索引应用有问题，这很奇怪：

  1. t_order_record 在 order_id 下面是有索引的，然而这里走的是主键全扫描（主键不是 order_id 而是 id）
  2. 子查问中其实只命中了 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 改写转换是否有问题。

如果有问题，思考人为干涉手动优化。手动优化的形式包含：

  1. force index 强制用某个索引
  2. 敞开以后会话的 MySQL 优化器的某些选项
  3. 改写 SQL 让优化器更易懂（JOIN 是最容易被 SQL 优化器了解的）

微信搜寻“我的编程喵”关注公众号，每日一刷，轻松晋升技术，斩获各种 offer：

退出移动版