从 14 秒到 1 秒:MySQL DDL 性能优化实战

1. 问题背景

MySQL版本:8.0.30
测试表数据量:200万

在 MySQL 中,研发人员最初执行了以下 SQL 语句,向表 t_email 中添加了一个允许为 NULL 的列 id3,并设置了默认值为 0:

ALTER TABLE t_email ADD COLUMN id3 INT NULL DEFAULT 0;

随后发现需要将该列修改为 NOT NULL,于是尝试执行以下语句:

ALTER TABLE t_email MODIFY COLUMN id3 INT NOT NULL DEFAULT 0;

然而,直接修改时发现无法使用 ALGORITHM=INSTANT,只能使用 ALGORITHM=INPLACE 或 ALGORITHM=COPY,导致操作耗时较长(14.67 秒)。

后续的测试中改为先删除列,再重新添加列的方式,成功使用了 ALGORITHM=INSTANT,速度显著提升。

2. 问题分析

  • ALGORITHM=INSTANT 的限制
    8.0.12 新添加了INSTANT算法。
    MySQL 的 ALGORITHM=INSTANT 是一种快速修改表结构的算法,但它仅支持特定的 DDL 操作。
    添加和删除列是支持的,修改列的NULL或NOT NULL不支持.
    在尝试将列从 NULL 修改为 NOT NULL 时,ALGORITHM=INSTANT 不支持此操作,因此 MySQL 只能使用 ALGORITHM=INPLACE 或 ALGORITHM=COPY,导致操作耗时较长。
    Table 17.18 Online DDL Support for Column Operations
  • ALGORITHM=INPLACE 和 ALGORITHM=COPY 的开销
    ALGORITHM=INPLACE:在表上直接修改元数据,但仍可能需要重建表数据。
    ALGORITHM=COPY:创建新表并复制数据,完成后删除旧表。这种方式通常较慢,尤其是在表数据量较大时。
  • 删除列并重新添加列的优化
    通过删除列并重新添加列的方式,可以绕过 MODIFY COLUMN 的限制,直接使用 ALGORITHM=INSTANT,从而显著提升性能。

3. 解决方案

  • 原始方案
    添加允许为NULL的列id3,并设置了默认值为0
    mysql> alter table t_email add column id3 int null default 0;
    Query OK, 0 rows affected (0.86 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    将列的属性修改为不允许为NULL,其他不变
    mysql> alter table t_email modify column id3 int not null default 0;
    Query OK, 0 rows affected (14.67 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
  • 优化后的方案
    添加允许为NULL的列id3,并设置了默认值为0。
    mysql> alter table t_email add column id3 int null default 0, ALGORITHM=INSTANT;
    Query OK, 0 rows affected (0.85 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    删除id3列,耗时0.98秒。
    mysql> alter table t_email drop column id3, ALGORITHM=INSTANT;
    Query OK, 0 rows affected (0.98 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    重新添加id3列,将属性改为NOT NULL,并设置了默认值为0,耗时0.68秒。
    mysql> alter table t_email add column id3 int not null default 0, ALGORITHM=INSTANT;
    Query OK, 0 rows affected (0.68 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

4. 总结

ALGORITHM=INSTANT 的限制:不支持修改列的 NULL 属性。仅支持特定的 DDL 操作(如添加列、删除列)。虽然不会操作不会阻塞DML,但是在DDL刚开始和结束时,都要获取MDL,如果这两个阶段表上有事务,DDL会因为获取不到MDL而被阻塞。

优化思路:对于不支持 ALGORITHM=INSTANT 的操作,可以尝试通过删除列并重新添加列的方式绕过限制。这种方式在表数据量越大时,性能提升越明显。

posted @   ideal_x  阅读(421)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示