在MySQL中对大数据量表执行DDL操作如何避免死锁

最近，我公司的一个团队在业务高峰时段对生产环境中的一个数据库进行了DDL（数据定义语言）操作，不幸引发了死锁问题。这一事件导致了多个应用无法正常访问，进而引起了大量客户投诉。

下面记录下出现该情况下应该怎么处理：

一、紧急处理

方法一：

1. 登录到MySQL数据库 首先，使用MySQL命令行或者图形界面登录到你的MySQL数据库。

2. 查询正在运行的进程 在MySQL命令行中运行以下命令以显示当前MySQL服务器上所有活动进程的列表：

SHOW PROCESSLIST;

1. 分析查询结果 该命令将返回一份进程列表，包括每个进程的ID, 用户, 主机, 数据库, 命令, 时间, 状态等信息。你需要仔细查看该列表，特别是命令列和状态列。如果看到有状态是“Locked”或者确认是造成死锁的查询，这很可能是死锁发生的原因。

2. 确定死锁的进程 找出造成死锁问题的查询的对应进程ID（process ID，也就是第一列的ID）。

3. 杀死死锁进程 使用以下命令杀死死锁的进程，其中process_id是发现的导致死锁的进程ID：

KILL process_id;

1. 重新运行SHOW PROCESSLIST命令 再次运行SHOW PROCESSLIST;命令，以确保死锁进程已经被杀死，并检查是否还有其他的死锁进程。

2. 监控系统状态 如果死锁的问题被解决，你应当密切监视系统的状态，以确保没有新的死锁发生。

注意：在生产环境中，强制杀死进程可能会导致某些查询或事务没有得到正常的完成，因此，在杀死任何进程之前，请确保了解可能产生的后果，并在需要时，与dba或团队成员进行沟通。

方法二：

万能重启大法，重启数据库

二、正常操作

在MySQL中执行DDL语句如添加索引或修改表结构时，通常情况下会对相关的表加上元数据锁（meta data lock），这在表数据较多或DML操作频繁的情况下会导致较长时间的锁表，从而影响业务的正常运行。如果不借助第三方工具，可以尝试以下步骤解决此问题：

1. 创建一个与原表结构一致的新表：

   CREATE TABLE a_new LIKE a;
   

2. 复制数据到新表： 逐步将原表（a）的数据批量复制到新表（a_new）中。在这个过程中，我们可以使用不锁表的方式如下：

   INSERT INTO a_new SELECT * FROM a WHERE id BETWEEN x AND y;
   

   上面的x和y表示主键或唯一索引的范围，可以分多次进行，以减小对原表的影响。

3. 对新表执行DDL操作： 在新表上执行所需的DDL操作，比如添加索引或改变列定义等。

   ALTER TABLE a_new ADD INDEX (column_name);
   

4. 同步原表最新数据到新表： 在执行此步骤前，需要在a表上添加一个低级别的锁，以最小化对业务的影响。可以使用读锁：

   LOCK TABLES a READ;
   

   然后，把此时a表上新增的数据同步到a_new上。同样，可以使用不锁表的插入操作。

5. 原子性重命名表： 在同步数据完成后，通过一次原子操作将原表重命名为其他表，新表重命名为原表名，从而实现无缝切换：

   RENAME TABLE a TO a_old, a_new TO a;
   

6. 解锁原表： 解锁之前锁定的a表：

   UNLOCK TABLES;
   

7. 测试并验证新表： 完成切换后，必须马上对新表进行测试，确认所有索引和数据是否正确无误。

8. 清理： 在确认新表一切正常后，可以删除旧表：

   DROP TABLE a_old;
   

此方案的核心思想是通过创建新表、同步数据、执行DDL操作再切换的方式来避免长时间锁表。可能会暂时占用更多的存储空间。此方法的主要风险在于，如果在执行任何步骤期间出现问题，可能需要快速回滚。如果你的环境能容忍短时间内的读写停滞，那么这是一个可行的解决方案。如果不能，推荐使用支持在线DDL操作的第三方工具，比如pt-online-schema-change等。

最后：

    1、上线需谨慎，应在业务低峰期进行上线，确保影响范围最小。

    2、线下环境应进行相关方案预演确保上线不出问题。

    3、另外平时业务开发时应根据不同场景多考虑程序健壮性及后续维护。