MySQL DDL简析（1）：inplace DDL 主要stage介绍

数据运维 2023-08-15 LOVEHL^ˇ^ 手机阅读
作者简介：高鹏，笔名八怪。《深入理解MySQL主从原理》图书作者，同时运营个人公众号“MySQL学习”，持续分享遇到的有趣case以及代码解析！
本文只是描述DDL中最慢的一个环节是在完成哪些工作，深入程度有限，如果有误请谅解。主要从DBA的角度来看这个问题，也是我一直比较迷惑的地方。基于代码8.0.23。

本文术语：

  • DML log：使用官方文档的描述，用于表示在进行online DDL时，对现有表进行的DML操作记录的日志。
  • sort buffer：本文中sort buffer是innodb_sort_buffer_size，不是我们平时说的用于语句返回记录排序的sort buffer。
  • 阶段：本文用阶段代替stage

一、主要阶段概述

我们在做DDL的时候，经常会用到online DDL（inplace），当然某些online DDL是比较慢的比如：

  • 增加索引（新建二级索引）
  • 增加字段（5.7）（重建主键，重建所有二级索引）
  • 增加字段并且指定顺序（8.0）（重建主键，重建所有二级索引）

这是因为，这些操作会导致读取全表的主键数据，同时需要新建二级索引或者重建整个表。

在官方文档中我们可以看到有如下一些主要的阶段（做了删减，否则太长）：

  • stage/innodb/alter table (read PK and internal sort): This stage is active when ALTER TABLE is in the reading-primary-key phase.
  • stage/innodb/alter table (merge sort): This stage is repeated for each index added by the ALTER TABLE operation.
  • stage/innodb/alter table (insert): This stage is repeated for each index added by the ALTER TABLE operation.
  • stage/innodb/alter table (log apply index): This stage includes the application of DML log generated while ALTER TABLE was running.
  • stage/innodb/alter table (flush): Before this stage begins, WORK_ESTIMATED is updated with a more accurate estimate, based on the length of the flush list.
  • stage/innodb/alter table (log apply table): This stage includes the application of concurrent DML log generated while ALTER TABLE was running.
  • stage/innodb/alter table (end): Includes any remaining work that appeared after the flush phase.

我们可以通过如下语句在执行DDL时候观察具体达到了哪个阶段：

select *from performance_schema.threads where PROCESSLIST_INFO like '%alter%' G *************************** 1. row ***************************           THREAD_ID: 52                          NAME: thread/sql/one_connection                          TYPE: FOREGROUND                PROCESSLIST_ID: 12              PROCESSLIST_USER: root              PROCESSLIST_HOST: localhost                PROCESSLIST_DB: testPROCESSLIST_COMMAND: Query              PROCESSLIST_TIME: 2  PROCESSLIST_STATE: alter table (read PK and internal sort)              PROCESSLIST_INFO: alter table sales1 add llll int              PARENT_THREAD_ID: NULL                          ROLE: NULL                  INSTRUMENTED: YES                       HISTORY: YES               CONNECTION_TYPE: Socket                  THREAD_OS_ID: 10653