MySQL 5.6中怎么定位DDL被阻塞的问题

2025-03-16 技术教程

这篇文章将为大家详细讲解有关MySQL 5.6中怎么定位DDL被阻塞的问题，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

对于DDL被阻塞问题的定位，我们主要是基于MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法，颇有种"锦上添花"的意味，而且，也只适用于MySQL 5.7开始的版本。

但在实际生产中，MySQL 5.6还是占绝不多数。虽然MySQL 8.0都已经GA了，但鉴于数据库的特殊性，在对待升级的这个事情上，相当一部分人还是秉持着一种“不主动”的态度。

既然MySQL 5.6用者众多，有没有一种方法，来解决MySQL 5.6的这个痛点呢？

还是之前的测试Demo。

会话1开启了事务并执行了三个操作，但未提交，此时，会话2执行了alter table操作，被阻塞。

session1> begin;

Query OK, rows affected (0.00 sec)

session1> delete from slowtech.t1 where id=2;

Query OK, 1 row affected (0.00 sec)

session1> select * from slowtech.t1;

+------+------+

| id | name |

+------+------+

| 1 | a |

+------+------+

1 row in set (0.00 sec)

session1> update slowtech.t1 set name='c' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings:

session2> alter table slowtech.t1 add c1 int; ##被阻塞

session3> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

| 2 | root | localhost | NULL | Sleep | 51 | | NULL |

+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+

3 rows in set (0.00 sec)

提示，类似代码可左右滑动

其实，导致DDL阻塞的操作，无非两类：

1. 慢查询

2. 表上有事务未提交

其中，第一类比较好定位，通过show processlist即能发现。而第二类基本没法定位，因为未提交事务的连接在show processlist中的输出同空闲连接一样。

如下面Id为2的连接，虽然Command显示为“Sleep”，其实是事务未提交。

mysql>showprocesslist;+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+|2|root|localhost|NULL|Sleep|77||NULL||3|root|localhost|NULL|Query|0|starting|showprocesslist||4|root|localhost|NULL|Query|44|Waitingfortablemetadatalock|altertableslowtech.t1addc1int|+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+3rowsinset(0.00sec)

所以，网上有kill空闲（Command为Sleep）连接的说法，其实也不无道理，但这样做就太简单粗暴了，难免会误杀。

其实，既然是事务，在information_schema. innodb_trx中肯定会有记录，如会话1中的事务，在表中的记录如下，

mysql>select*frominformation_schema.innodb_trx\G***************************1.row***************************trx_id:1050390trx_state:RUNNINGtrx_started:2018-07-1708:55:32trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:4trx_mysql_thread_id:2trx_query:NULLtrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:1trx_lock_structs:2trx_lock_memory_bytes:1136trx_rows_locked:3trx_rows_modified:2trx_concurrency_tickets:0trx_isolation_level:REPEATABLEREADtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking:01rowinset(0.00sec)

其中trx_mysql_thread_id是线程id，结合performance_schema.threads，可以知道当前哪些连接上存在着活跃事务，这样就进一步缩小了可被kill的线程范围。

但从影响程度上，和kill所有Command为Sleep的连接没太大区别，毕竟，kill真正的空闲连接对业务的影响不大。

有没有办法能更精确地定位出阻塞会话呢？

有，答案还是在于之前介绍的performance_ schema.events_statements_history表。

在上篇MySQL 5.7的分析中，我们是首先知道引发阻塞的线程ID，然后利用events_statements_history表，查看该线程的相关SQL。

而在MySQL 5.6中，我们并不知道引发阻塞的线程ID，但是，我们可以反其道而行之，利用穷举法，首先统计出所有线程在当前事务执行过的所有SQL，然后再判断这些SQL中是否包含目标表。

具体SQL如下，

SELECTprocesslist_id,sql_textFROM(SELECTc.processlist_id,substring_index(sql_text,"transaction_begin;",-1)sql_textFROMinformation_schema.innodb_trxa,(SELECTthread_id,group_concat(CASEWHENEVENT_NAME='statement/sql/begin'THEN"transaction_begin"ELSEsql_textENDORDERBYevent_idSEPARATOR";")ASsql_textFROMperformance_schema.events_statements_historyGROUPBYthread_id)b,performance_schema.threadscWHEREa.trx_mysql_thread_id=c.processlist_idANDb.thread_id=c.thread_id)tWHEREsql_textLIKE'%t1%';+----------------+---------------------------------------------------------------------------------------------------------+|processlist_id|sql_text|+----------------+---------------------------------------------------------------------------------------------------------+|2|deletefromslowtech.t1whereid=2;select*fromslowtech.t1;updateslowtech.t1setname='c'whereid=1|+----------------+---------------------------------------------------------------------------------------------------------+1rowinset(0.01sec)

从输出来看，确实也达到了预期效果。

需要注意的是，在MySQL5.6中，events_ statements_history默认是没有开启的。

mysql>SELECT*FROMperformance_schema.setup_consumersWHERENAMELIKE'%statements%';+--------------------------------+---------+|NAME|ENABLED|+--------------------------------+---------+|events_statements_current|YES||events_statements_history|NO||events_statements_history_long|NO||statements_digest|YES|+--------------------------------+---------+4rowsinset(0.00sec)

关于“MySQL 5.6中怎么定位DDL被阻塞的问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。