mysql修改字段卡住问题总结

前言

线上环境如何修改表字段才能保证服务的稳定运行呢？大概方法罗列：

方案1：业务低峰期进行alter table操作。

方案2：创建临时表、加字段、插入旧数据，增量数据插入和重命名表名。

方案3:用第三方开源工具实现，具体实现步骤看链接:

方案4：在从库进行添加字段操作，然后进行mysql主从切换
注意事项:主从复制日志格式如果为row格式，添加的新列必须是在表的最后。

参考文章：http://blog.itpub.net/15498/viewspace-2153012/

笔者就是采用方案1来操作的，但是遇到一个问题，执行完这个表就卡住了。。。。。。。。

笔者当时的汗-_-||。。。一万个草拟吗在心中咆哮，这个表锁住了，app的重要首页基本就显示不出数据来了。这尼玛绝对走人的节奏。

还好笔者有点东西，立马 show processlist 查看进程 然后kill掉刚才执行的操作。表也恢复正常了。

虽然暂时问题解决了，但是没办法该表呀，赶紧查原因。

查看正在运行的进程

可以通过show processlist查看正在执行的sql进程

说明各列的含义和用途，

id列:一个标识，你要kill 一个语句的时候很有用。

user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。

db列:显示这个进程目前连接的是哪个数据库。

command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

 通常代表资源未释放，如果是通过连接池，sleep状态应该恒定在一定数量范围内

 实战范例：因前端数据输出时（特别是输出到用户终端）未及时关闭数据库连接，导致因网络连接速度产生大量sleep连接，在网速出现异常时，数据库too many connections挂死。

 简单解读，数据查询和执行通常只需要不到0.01秒，而网络输出通常需要1秒左右甚至更长，原本数据连接在0.01秒即可释放，但是因为前端程序未执行close操作，直接输出结果，那么在结果未展现在用户桌面前，该数据库连接一直维持在sleep状态！

time列:此这个状态持续的时间，单位是秒。

state列:显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。

info列:显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

更多内容参考：https://blog.youkuaiyun.com/dhfzhishi/article/details/81263084

查看正在运行的事务

单凭show processlist有时不能查看到问题之所在，就像前言中的问题，笔者通过show processlist没有发现问题。

通过运行

SELECT * FROM information_schema.innodb_trx

查看正在运行的事务

Field	Type	Null	Key	Default	Extra
trx_id	varchar(18)	NO			事务ID
trx_state	varchar(13)	NO			事务状态：
trx_started	datetime	NO		0000-00-00 00:00:00	事务开始时间；
trx_requested_lock_id	varchar(81)	YES		NULL	innodb_locks.lock_id
trx_wait_started	datetime	YES		NULL	事务开始等待的时间
trx_weight	bigint(21) unsigned	NO		0	事务权重
trx_mysql_thread_id	bigint(21) unsigned	NO		0	事务线程ID
trx_query	varchar(1024)	YES		NULL	具体SQL语句
trx_operation_state	varchar(64)	YES		NULL	事务当前操作状态
trx_tables_in_use	bigint(21) unsigned	NO		0	事务中有多少个表被使用
trx_tables_locked	bigint(21) unsigned	NO		0	事务拥有多少个锁
trx_lock_structs	bigint(21) unsigned	NO		0
trx_lock_memory_bytes	bigint(21) unsigned	NO		0	事务锁住的内存大小（B）
trx_rows_locked	bigint(21) unsigned	NO		0	事务锁住的行数
trx_rows_modified	bigint(21) unsigned	NO		0	事务更改的行数
trx_concurrency_tickets	bigint(21) unsigned	NO		0	事务并发票数
trx_isolation_level	varchar(16)	NO			事务隔离级别
trx_unique_checks	int(1)	NO		0	是否唯一性检查
trx_foreign_key_checks	int(1)	NO		0	是否外键检查
trx_last_foreign_key_error	varchar(256)	YES		NULL	最后的外键错误
trx_adaptive_hash_latched	int(1)	NO		0
trx_adaptive_hash_timeout	bigint(21) unsigned	NO		0

更多内容参考文章：https://blog.youkuaiyun.com/J080624/article/details/80596958

可以通过运行的事务关联 showprocesslist联合查询，然后kill 进程

SELECT B.*, A.trx_started FROM  information_schema.innodb_trx A
LEFT JOIN (SELECT * FROM information_schema.`PROCESSLIST`) B ON A.trx_mysql_thread_id=B.id

杀掉进程后，在修改表字段很快就会完事。当然基于表的数据，表数据越大，修改的时间会稍微长一点。

 慢查询的影响

这里要明确一点，当你执行任何一个sql的时候，都是带着事务的，就算你执行一个查询的sql，也是有事务的。

为什么要说慢查询，因为当你修改表字段属性的时候，切好有一个正在执行的慢查询，预估这个慢查询会持续100s，那么你修改表字段的属性时会一直卡着等待，直到这个慢查询执行完，它才会去修改表属性。

所以，在生产环境，修改表字段属性的时候，一定要注意先查看当前表是否有正在执行的事务。假如有先让这个事务执行完，否则修改会一直阻塞。

这里要明白一点，查询的事务，只会阻塞修改表属性等操作，不会影响其他增删改查的事务操作。但是它会间接影响增删改查事务操作。

为什么说是间接呢，假如当前有个正在执行的事务，然后你修改表字段实行，那么修改表字段属性会阻塞，它的阻塞会直接影响后续对该表的所有增删改查操作都阻塞。

所以，修改表字段属性一定要慎重和谨慎，稍有不慎，会使整个表瘫痪，间接影响其他关联这个表的所有操作。

慢查询同时会使cpu飙升，间接影响所有对mysql的操作。

总结

最后通过查看运行中的事务，然后结束掉进程就ok了，修改和添加表字段也没有出现卡住不动的情况。到此结束。记录一下，便于以后查看。