MySQL修改字段卡住问题总结及解决方法

李少兄

于 2024-10-10 20:49:58 发布

阅读量2.1k

点赞数 38

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wenxuankeji/article/details/142831916

版权

MySQL 专栏收录该内容

43 篇文章

订阅专栏

前言

在生产环境中，对MySQL表进行结构修改（如添加或修改字段）时，可能会遇到执行ALTER TABLE命令后表被锁定的情况，这会导致服务暂时不可用。本文将详细讨论几种常见的处理方案以及当遇到此类问题时如何快速诊断和解决问题。

解决方案

方案1：业务低峰期进行ALTER TABLE操作

选择系统负载较低的时间段执行修改操作，可以减少对线上服务的影响。
这种方法适用于大多数情况，但如果表非常大，即使是在非高峰时间也可能需要较长时间来完成修改。

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

步骤1：创建一个新的表，并且包含所有原表的字段加上新要添加的字段。

CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table ADD COLUMN new_column VARCHAR(255);

步骤2：将原表的数据复制到新表中。

INSERT INTO new_table SELECT * FROM original_table;

步骤3：更新应用指向新的表。

步骤4：删除旧表或者保留作为备份。

RENAME TABLE original_table TO old_table, new_table TO original_table;
DROP TABLE old_table;  -- 如果不需要备份

方案3：使用第三方开源工具实现

一些开源工具如pt-online-schema-change (Percona Toolkit) 提供了在线DDL变更的功能，在不影响读写的情况下完成表结构调整。

可以参考官方文档了解具体实施步骤：

pt-online-schema-change D=your_database,t=your_table --alter "ADD COLUMN new_column VARCHAR(255)" --execute

方案4：在从库进行添加字段操作，然后进行MySQL主从切换

如果数据库采用的是主从架构，可以在从服务器上先进行表结构调整，之后再通过主从切换使更改生效。
注意事项: 主从复制日志格式如果为ROW格式，添加的新列必须是在表的最后。

实际案例分析

博主尝试直接在线上环境执行ALTER TABLE命令来修改一个重要的表字段，结果发现这个表被锁住了，导致应用首页无法正常显示数据。此时，采取了如下措施来迅速恢复服务：

查看正在运行的进程

使用以下SQL语句查看当前活动的进程列表：
```
SHOW FULL PROCESSLIST;
```
- id列标识每个进程，用于终止指定进程。
- user列展示发起该进程的用户。
- host列提供发起请求的客户端IP地址和端口号。
- db列显示进程关联的数据库名称。
- command列指示进程当前正在执行的操作类型。
- time列记录进程已持续运行的时间。
- state列展示SQL语句的状态。
- info列则给出正在执行的具体SQL语句内容。
终止阻塞进程

根据SHOW FULL PROCESSLIST;的结果找到阻塞进程的ID，然后执行以下命令来终止它：
```
KILL [process_id];
```
通过这种方式，博主成功解决了表被锁的问题，服务也随即恢复正常。
查看正在运行的事务

有时候，仅仅通过SHOW FULL PROCESSLIST可能不足以定位问题根源。这时可以通过查询information_schema.innodb_trx视图来获取正在进行中的InnoDB事务信息，并结合PROCESSLIST的信息进一步诊断。

执行以下SQL语句来联合查询正在运行的事务及其相关信息：
```
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;
```
- trx_id：事务ID。
- trx_state：事务状态。
- trx_started：事务开始时间。
- trx_requested_lock_id：请求的锁ID。
- trx_wait_started：事务开始等待的时间。
- trx_weight：事务权重。
- trx_mysql_thread_id：事务线程ID。
- trx_query：具体SQL语句。
- trx_operation_state：事务当前操作状态。
- trx_tables_in_use：事务中有多少个表被使用。
- trx_tables_locked：事务拥有多少个锁。
- trx_lock_structs：事务锁结构数量。
- trx_lock_memory_bytes：事务锁住的内存大小（B）。
- trx_rows_locked：事务锁住的行数。
- trx_rows_modified：事务更改的行数。
- trx_concurrency_tickets：事务并发票数。
- trx_isolation_level：事务隔离级别。
- trx_unique_checks：是否唯一性检查。
- trx_foreign_key_checks：是否外键检查。
- trx_last_foreign_key_error：最后的外键错误。
- trx_adaptive_hash_latched：自适应哈希索引锁。
- trx_adaptive_hash_timeout：自适应哈希索引超时。
通过上述查询，可以更全面地了解哪些事务可能阻碍了ALTER TABLE操作。一旦确定了是哪个事务导致的问题，就可以通过其线程ID来终止该事务，从而释放表锁。
慢查询的影响

当你执行任何一个SQL语句时，都是带着事务的。如果你在修改表字段属性时，恰好有一个正在执行的慢查询，那么修改表字段的属性会一直卡着等待，直到这个慢查询执行完。因此，在生产环境中修改表字段属性前，一定要注意查看当前表是否有正在执行的事务。如果有，应先让这个事务执行完，否则修改会一直阻塞。
- 查询当前表是否有正在执行的事务：
```
SELECT * FROM information_schema.innodb_trx WHERE trx_mysql_thread_id IN (SELECT id FROM information_schema.processlist WHERE db = 'your_database' AND info LIKE '%your_table%');
```
- 终止特定事务：
```
KILL [trx_mysql_thread_id];
```