性能文章>由一次 UPDATE 过慢 SQL 优化而总结出的经验>

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

5月前
3157012

最近,线上的 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_recordarchive_id420a7fe7-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 优化器理解的)
分类:
标签:
请先登录,再评论

暂无回复,快来写下第一个回复吧~

为你推荐

5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
Prometheus时序数据库-数据的插入
前言在之前的文章里,笔者详细的阐述了Prometheus时序数据库在内存和磁盘中的存储结构。有了前面的铺垫,笔者就可以在本篇文章阐述下数据的插入过程。 监控数据的插入在这里,笔者并不会去讨论Promt
Prometheus时序数据库-数据的查询
前言在之前的博客里,笔者详细阐述了Prometheus数据的插入过程。但我们最常见的打交道的是数据的查询。Prometheus提供了强大的Promql来满足我们千变万化的查询需求。在这篇文章里面,笔者
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id