为什么mysql更改表结构时，varchar超过255会锁表

在 MySQL 中，当修改表结构并将 VARCHAR 字段的长度设置为超过 255 时，可能会出现锁表的情况。这与 MySQL 的存储引擎（主要是 InnoDB）以及表的底层存储方式相关。

原因分析

1. 行格式变化
   InnoDB 存储引擎支持多种行格式（如 COMPACT、REDUNDANT、DYNAMIC、COMPRESSED）。

   • 当 VARCHAR 的长度从 255 扩展到更大时，可能会导致行格式发生变化（例如字段从固定长度变为动态长度存储），进而需要重建整个表。
   • 特别是在某些情况下，VARCHAR 的长度超过 255 时，存储方式会从ROW 中直接存储变为外部溢出存储（特别是长字符串），导致表结构调整需要重新写入数据。

2. 页分裂和数据重排

   • 数据页的结构可能需要调整以容纳更大的 VARCHAR 字段。
   • 超过 255 的 VARCHAR 可能需要额外的存储空间，而这些调整通常需要对整个表进行重新组织。

3. 表定义变更导致表重建
   MySQL 在大多数情况下会选择直接重建表来实现字段类型或长度的调整（ALGORITHM=INPLACE 可能不起作用）。在重建表期间，MySQL 会获取表级锁，阻止其他线程对表的读写操作，导致锁表的现象。

4. 存储引擎限制

   • 不同存储引擎对列长度的限制不同。在 InnoDB 中，单行的最大存储长度为 65535 字节，超过部分会溢出到外部存储。
   • 如果超出存储限制，MySQL 可能会通过复杂操作进行调整，间接导致表锁问题。

解决办法

1. 在线DDL操作
   在修改表结构时，可以通过以下方式减少锁表的风险：

   ALTER TABLE your_table MODIFY COLUMN your_column VARCHAR(500), ALGORITHM=INPLACE, LOCK=NONE;
   

   • ALGORITHM=INPLACE：尝试在线模式修改，避免重建表。
   • LOCK=NONE：允许 DDL 操作时读写并发。

   但并不是所有情况下 INPLACE 都适用，可能会回退到 COPY 模式。

2. 减少锁表时间

   • 分批操作：将表拆分成多个小表，先调整小表的结构，再合并数据。
   • 降低并发影响：在业务低峰期执行操作。

3. 在线工具
   使用工具如 pt-online-schema-change 或 gh-ost，在不锁表的情况下完成修改。这些工具通过创建临时表和增量数据复制，最终切换表来避免锁表。

总结

VARCHAR 字段长度超过 255 时，可能涉及行格式变化或表重建，导致锁表问题。通过在线DDL操作或使用专用工具，可以有效缓解或避免锁表带来的影响。