MySQL 8 ALGORITHM=INSTANT执行耗时过长问题问询

问题描述 #

在3亿行高流量的documents表上执行：

ALTER TABLE documents ADD COLUMN test varchar(255), ALGORITHM=INSTANT;

耗时超100秒，期间所有其他数据库连接均被阻塞等待。按官方文档，ALGORITHM=INSTANT仅需短暂持有元数据锁（MDL），若当前表不支持该算法应直接报错，但实际既未报错又出现长时间阻塞。已确认表满足INSTANT算法的前置条件：ROW_FORMAT=DYNAMIC、无FULLTEXT索引。

附表信息 #

SHOW CREATE TABLE documents

[请补充具体输出内容]

SHOW TABLE STATUS

[请补充具体输出内容]

排查步骤 #

• 检查MDL锁等待：执行以下语句，确认是否有长事务或未提交的DML语句占用该表的MDL共享锁，导致ALTER无法获取所需的排他锁而长时间等待：

  SELECT object_type, object_schema, object_name, lock_type, lock_duration, processlist_id, processlist_info
  FROM performance_schema.metadata_locks
  WHERE object_name = 'documents';
  

• 排查磁盘IO与表碎片：查看SHOW TABLE STATUS LIKE 'documents'\G中的Data_free字段，若表空间碎片过大或磁盘IO性能不足，可能拖慢元数据更新操作。
• 核对版本已知bug：查阅Percona Server 8.0.33-25的版本发布说明，确认是否存在INSTANT算法相关的高并发场景锁异常、元数据更新逻辑缺陷等已知问题。
• 检查隐性表结构限制：
  • 表是否包含ENUM或SET类型字段？部分版本中这类字段可能导致INSTANT算法执行逻辑异常（虽官方文档未明确提及，但需验证）
  • 表是否使用分区？某些分区类型可能不支持INSTANT算法
  • 查询information_schema.innodb_tables中的TABLE_FLAGS字段，确认无特殊标记影响INSTANT执行
• 监控并发DML影响：高流量场景下，大量并发的INSERT/UPDATE/DELETE操作可能导致MDL排他锁的获取等待时间延长——INSTANT虽不锁行，但获取排他锁时需要等待所有共享锁释放，若有长事务持有共享锁，就会导致ALTER长时间阻塞。

验证方法 #

选择低峰期（或在测试环境）重复执行ALTER语句，同时监控performance_schema.events_statements_current和performance_schema.metadata_locks，观察语句的执行阶段和锁等待情况，确认是卡在MDL锁等待环节还是实际执行阶段。

内容的提问来源于stack exchange，提问作者Philippe Vezina