AliSQL DDL最佳实践

方式一：基于Performance Schema（PFS）评估

从MySQL 5.7版本开始在 PFS加入了DDL相关Stage统计信息，可以通过processlist和P_S库下的events_stages_current表查看DDL进度，并且评估剩余时间。

• 适用场景：DDL执行中需实时监控进度。

• 查询语句：

  SELECT * FROM information_schema.processlist WHERE ID = <替换为实际ID>;
  SELECT THREAD_ID, EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 AS PROGRESS  
  FROM performance_schema.events_stages_current WHERE THREAD_ID = <替换为实际ID>;  

  说明

  执行SHOW Processlist命令根据执行的SQL获取ID。

• 示例：本地Sysbench 10 GB的表，查询结果TIME=116，PROGRESS=83.9961%，如下所示：

  mysql> SELECT * FROM information_schema.processlist WHERE ID = 18;
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  | ID | USER | HOST      | DB       | COMMAND | TIME | STATE          | INFO                   |
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  | 18 | root | localhost | ddl_test | Query   |  116 | altering table | optimize table sbtest1 |
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current WHERE THREAD_ID = 32;
  +-----------+----------+----------------------------------------+----------------+----------------+----------+
  | THREAD_ID | EVENT_ID | EVENT_NAME                             | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
  +-----------+----------+----------------------------------------+----------------+----------------+----------+
  |        32 |      127 | stage/innodb/alter table (insert)      |        2273884 |        2707129 |  83.9961 |
  +-----------+----------+----------------------------------------+----------------+----------------+----------+
  1 row in set (0.00 sec)

  • 计算时间 = TIME / PROGRESS = 116 / (83.9961%) = 138.1 s

  • 实际执行时间：2 min 3.14 s = 123.14 s

    执行 optimize table sbtest1; 命令，实际耗时 2 分 3.14 秒（约 123 秒），与 PFS 评估的 138.1 秒基本吻合。

    mysql>
    mysql> optimize table sbtest1;
    +------------------+----------+----------+-------------------------------------------------------------------+
    | Table            | Op       | Msg_type | Msg_text                                                          |
    +------------------+----------+----------+-------------------------------------------------------------------+
    | ddl_test.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
    | ddl_test.sbtest1 | optimize | status   | OK                                                                |
    +------------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (2 min 3.14 sec)

方式二：基于表空间大小评估

• 适用场景：无需开启PFS（消耗内存且会有性能影响，RDS MySQL默认关闭）的估算。

• 步骤：

  1. 查询表大小：

     SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA =  <替换为实际的数据库名> AND TABLE_NAME = <替换为实际的表名>;  

  2. 根据查询到的DATA_LENGTH大小，直接估算DDL的时间，以OPTIMIZE TABLE操作为例：

     • 云盘实例（高性能云盘和ESSD云盘）：按照30~60 MB/s的速度进行估算。

     • 高性能本地盘实例：按照50~100 MB/s的速度进行估算。

       说明

       30~60 MB/s和50~100 MB/s的速度是根据实例的IO能力估算的值，实际可能会有偏差。

  3. （可选）索引影响：如果表存在很多二级索引，还需考虑二级索引排序和构建的时间，按主表速度的1/3估算索引构建时间。

• 示例：本地Sysbench 10 GB的表，查询结果如下：

  mysql> SELECT * from information_schema.tables WHERE TABLE_SCHEMA = 'ddl_test' AND TABLE_NAME = 'sbtest1'\G
  *************************** 1. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: ddl_test
       TABLE_NAME: sbtest1
       TABLE_TYPE: BASE TABLE
           ENGINE: InnoDB
          VERSION: 10
       ROW_FORMAT: Dynamic
       TABLE_ROWS: 39452112
   AVG_ROW_LENGTH: 260
      DATA_LENGTH: 10270785536
  MAX_DATA_LENGTH: 0
     INDEX_LENGTH: 665829376
        DATA_FREE: 3145728
   AUTO_INCREMENT: 40000001
      CREATE_TIME: 2025-05-09 15:58:33
      UPDATE_TIME: NULL
       CHECK_TIME: NULL
  TABLE_COLLATION: utf8_general_ci
         CHECKSUM: NULL
   CREATE_OPTIONS:
    TABLE_COMMENT:
  1 row in set (0.00 sec)

  • 计算时间 = DATA_LENGTH / 1024 / 1024 / 100 + INDEX_LENGTH / 1024 / 1024 / 100 * 3 = 10270785536 / 1024 / 1024 / 100 + 665829376 / 1024 / 1024 / 100 * 3 = 117 s

  • 实际执行时间：2 min 3.14 s = 123.14 s

基本原则

1. DDL是变更操作，尽量避开业务高峰。

2. 支持INSTANT算法的DDL，或其他仅修改元数据的DDL，可以放心执行（为避免操作开始和结束阶段无法获取MDL X锁，仍需重点关注慢查询和大事务）。

3. 只支持COPY算法的DDL，执行过程中表只读，无法进行写操作，需要谨慎执行。

4. 其他支持INPLACE算法的DDL，执行过程中表可以读写，但是需要综合评估执行时间、空间占用，以及可能导致的复制延迟问题。

AliSQL DDL相关优化功能

• 大表删除优化

  • 功能：异步清理大文件，避免文件系统抖动。

  • 参数：innodb_data_file_purge=ON（默认开启）。

• Buffer Pool管理优化

  • 功能：减少DDL的执行耗时，降低DDL操作带来的性能影响。

  • 参数：loose_innodb_rds_faster_ddl=ON（需手动开启）。

• 秒级加列

  • 功能：元数据变更，无需重建表。

  • 参数：loose_innodb_instant_ddl_enabled=ON（MySQL 8.0版本默认开启，5.7及以下版本需手动开启）。

• 其他优化

  • AHI清理优化：优化删除表和索引过程中AHI清理导致的性能问题。参数：innodb_rds_drop_ahi_ahead，默认为OFF。

  • 唯一索引冲突优化：优化DDL期间唯一索引冲突导致DDL失败的问题。

  • MDL锁等待逻辑优化：将MDL X锁的阻塞式等待优化为非阻塞式等待，解决了DDL长期拿不到MDL锁导致业务面积性阻塞的问题。

  • ANALYZE TABLE优化：解决了MySQL 5.7 版本ANALYZE TABLE可能导致业务阻塞的问题。

  • Parallel DDL：修复DDL性能回退问题。

相关文档

• 升级至AliSQL最新版本以获取全部优化功能

• AliSQL内核小版本发布记录