记录一次线上Mysql 添加索引导致服务不可用，系统崩溃！

问题出现场景

在一次生产环境导出操作中，数据体量过大导致导出时间过长，超过nginx 超时时间。在本地连接线上数据库，对导出过慢进行复现，对导出查询 SQL 慢查询进行优化，需要给表添加索引，表作为系统所有业务的大表主表，过多业务对该表进行频繁操作。在添加索引时，导致表锁死，造成数据库服务崩溃，后台服务连接数据库超时，nacos 注册中心连接 mysql 失败导致服务下线，系统崩溃。

问题出现原因

表作为系统的大表热表，添加索引直接导致该表被锁，所有涉及到该表的增删查改的操作都被阻塞。

解决方案

通过 show processlist 查看 mysql 中有哪些线程正在执行，可以查看添加索引导致锁表的线程（alter table key …），该线程的状态是 Waiting for table metadata lock，在这个操作的后续操作也都是这个状态，该添加索引操作导致表被锁，后续所有对该表的 DDL 操作都处于阻塞状态。

show processlist

通过上述命令找到对应的阻塞语句的线程ID，运行kill 命令，被锁住的表就可以恢复使用

 kill 线程ID

如何安全操作线上数据库

1. 生产环境的任何大表、小表或者的热表，DDL 操作都要小心谨慎，最好选择在业务低峰值进行。对于数据一致性要求更高的，应该选择在空表中完成对应操作，对数据进行备份，根据时间节点对数据同步到空表，并完成表的切换。
2. 在对线上数据库的操作中，尽量只有 select 查询语句，不出现update、delete 语句，避免误操作导致数据不可回溯和丢失。
3. 使用navicat 查询字段尽量在对用表的 DDL 中查找相关字段，避免在设计表中查找。
4. 在设计上，尽可能避免大事务，长事务会各种锁问题，以及引发复制延迟、回滚空间爆满等问题。
5. 设置参数 lock_wait_timeout 为较小值，被阻塞主动停止。
6. 操作 ddl 语句之前，先通过 select * from information_schema.INNODB_TRX; 查询是否有长事务。