MySQL中ALTER TABLE在不同数据量表的耗时及结构修改疑问

一、新增列的情况 #

• 结论：多数情况下表B（100万行）耗时会更长，但不一定严格和行数成比例
  • 若使用InnoDB引擎且MySQL版本≥5.6（支持Online DDL），新增允许NULL的列或带默认值的列（默认值无需逐行写入），仅需更新表的元数据（表结构定义），不需要修改现有数据行，这种情况下两张表的耗时差异极小，几乎与行数无关。
  • 但如果是需要重建整张表的场景（比如新增列到表的中间位置、MySQL版本过低不支持Online DDL、新增列要求非NULL且无默认值），则需要把所有数据行复制到新的表结构中，此时行数越多，复制的数据量越大，耗时自然越长，且耗时与行数大致呈正相关，但受磁盘IO、CPU性能影响，不会是严格的线性比例。

二、修改现有列结构的情况 #

• 结论：表B的耗时会远长于表A，且耗时基本和行数成正相关
  • 不管是修改列的数据类型（比如INT改BIGINT、CHAR改VARCHAR）、调整列的长度（比如VARCHAR(10)改VARCHAR(50)且现有数据需要重新存储）、修改列的NOT NULL属性，InnoDB都需要逐行读取原有数据，修改对应列的内容后重新写入磁盘。
  • 这种操作本质是遍历并修改每一行数据，100万行的表需要处理的数据量是1000行表的1000倍，所以耗时会大幅增加，且在硬件资源稳定的前提下，耗时和行数接近线性比例。

三、底层存储机制的本质原因 #

InnoDB采用聚簇索引组织表数据，所有行数据都存储在主键索引的叶子节点中：

• 新增列（末尾）：如果不需要修改旧数据，新写入的行会包含新列，旧行读取时自动补NULL或默认值，仅需更新表结构元数据，无需触碰现有数据；只有当必须重建表时，才需要复制所有行数据。
• 修改现有列：现有列的存储位置和内容已经固定，修改结构意味着要改变每一行中该列的存储格式或内容，必须逐行处理，因此耗时直接和行数挂钩。

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