mysql添加字段或索引为什么会锁表

前几天，在生产环境对数据库的一张大表进行新增字段的操作，结果直接导致该表被锁，所有该表的增删改查操作全部阻塞，服务器负载飙高，进而影响到了线上业务。

锁表时查看服务器日志，发现有大量如下图所示的报错信息

从上图分析可得数据库连接池已经没有空闲的连接可用了，为了快速能够解决这个问题，使用show processlist显示 数据库所有正在运行的线程相关信息，根据执行时间可以准确判断出新增字段对应的线程(如下图所示)，由于新增字段锁表导致其他增删改该表语句处于等待状态，通过 kill processId 杀掉修改表结构的语句，马上恢复了正常。

事后，通过网上查阅资料，对于造成数据库锁表原因分析和复盘，不同的mysql版本对于添加字段导致锁表有着不同的处理方式。

  • mysql版本5.6之前的处理 :
    • 首先创建新的临时表，复制原表结构，并新增新字段
    • 然后把原表中数据导入到临时表
    • 删除原表
    • 最后重命名临时表为原表名

通过mysql官网得知，MySQL 5.6后支持Online DDL(在线DDL)，Online DDL就是允许在表上执行DDL的操作（例如创建字段）的同时不阻塞并发的DML(SELECT、UPDATE、INSERT、DELETE)操作，也就是说在执行DDL语句时，提供无锁加字段或索引的机制，即在DDL语句后加 ALGORITHM=INPLACE,LOCK=NONE。

  • mysql版本5.6之后的处理 :
    • 例子：ALTER TABLE 表名ADD COLUMN …(此处省略),ALGORITHM = INPLACE, LOCK = NONE;

ALGORITHM 描述的是DDL的实现方式，LOCK 描述的是执行DDL是否加锁

ALGORITHM 有三个参数：
Default (默认，由mysql自行决定) ,
INPLACE (对当前表在引擎层对数据改动)
COPY (采用拷表方式进行表变更，缺点：需要多一倍存储，表很大的时候，可能因为磁盘的容量不足而操作失败)

LOCK 有四个参数
NONE (无锁)
SHARED (共享锁)
DEFAULT (默认，由mysql自行决定)
EXCLUSIVE (互斥锁)

总结：
mysql 5.6后为了避免mysql添加字段或索引锁表的问题只需要在添加字段或索引的执行语句后加 ALGORITHM = INPLACE, LOCK = NONE;

对于大表的修改最好满足以下条件：

  • 尽量选择流量小的时候才执行sql
  • 执行前需要查看是否有未提交的事务
  • 预先在预发布环境先行模拟对应的场景，评估风险