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

干货满满张哈希
• 阅读 998

由一次 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_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 优化器理解的)

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

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

点赞
收藏
评论区
推荐文章
光头强的博客 光头强的博客
1星期前
Java面向对象试题
1、 请创建一个Animal动物类,要求有方法eat()方法,方法输出一条语句“吃东西”。 创建一个接口A,接口里有一个抽象方法fly()。创建一个Bird类继承Animal类并实现 接口A里的方法输出一条有语句“鸟儿飞翔”,重写eat()方法输出一条语句“鸟儿 吃虫”。在Test类中向上转型创建b对象,调用eat方法。然后向下转型调用eat()方
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录 问题 用navicat导入数据时,报错: 原因这是因为当前的MySQL不支持datetime为0的情况。 解决修改sql\mode: sql\mode:SQL Mode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。 全局s
Wesley13 Wesley13
11个月前
Java爬虫之JSoup使用教程
title: Java爬虫之JSoup使用教程 date: 2018-12-24 8:00:00 +0800 update: 2018-12-24 8:00:00 +0800 author: me cover: [https://img-blog.csdnimg.cn/20181224144920712](https://www.oschin
Wesley13 Wesley13
11个月前
030 SSM综合练习06
**1.权限操作涉及的三张表** (1)用户表信息描述users ![](https://oscimg.oschina.net/oscnet/a4a2b1f943cbc2db1c8ddd613e7ed00a9ae.png) sql语句: CREATE TABLE users ( id VARCHAR2 ( 32 ) DEFAU
Stella981 Stella981
11个月前
Android So动态加载 优雅实现与原理分析
> 背景:漫品Android客户端集成适配转换功能(基于目标识别(So库35M)和人脸识别库(5M)),导致apk体积50M左右,为优化客户端体验,决定实现So文件动态加载. ![](https://oscimg.oschina.net/oscnet/00d1ff90e4b34869664fef59e3ec3fdd20b.png) 点击上方“蓝字”关注我
Wesley13 Wesley13
11个月前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序 select * from table_name order id desc; 2.按照指定(多个)字段排序 select * from table_name order id desc,status desc; 3.按照指定字段和规则排序 selec
Stella981 Stella981
11个月前
Angular material mat
Icon Icon Name mat-icon code _add\_comment_ add comment icon <mat-icon> add\_comment</mat-icon> _attach\_file_ attach file icon <mat-icon> attach\_file</mat-icon> _attach\
Wesley13 Wesley13
11个月前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
#### 背景描述 # Time: 2019-01-24T00:08:14.705724+08:00 # User@Host: **[**] @ [**] Id: ** # Schema: sentrymeta Last_errno: 0 Killed: 0 # Query_time: 0.315758 Lock_
Easter79 Easter79
11个月前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用 0x00 SQL注入 ---------- 反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下 1)表名 payload:select \* from \`users\` where user\_id=1 limit 0,1; ![](https://o
helloworld_34035044 helloworld_34035044
2个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。 uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid() 或 uuid(sep)参数说明:sep 布尔值,生成的uuid中是否包含分隔符'',缺省为