共计 5146 个字符，预计需要花费 13 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 MySQL 5.6 中怎么定位 DDL 被阻塞的问题，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
对于 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
rows affected
(0.00
sec)
session1
delete
from
slowtech.t1
where
id=2;
Query
row affected
(0.00
sec)
session1
select
from
slowtech.t1;
+------+------+
|
id
name
|
+------+------+
|
1
a |
+------+------+
1
row
(0.00
sec)
session1
update slowtech.t1
name= c
where
id=1;
Query
row affected
(0.00
sec)
Rows
matched:
Changed:
Warnings:
session2
alter table slowtech.t1 add c1
## 被阻塞
session3
show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
|
User
Host
|
db
Command
Time
State
Info
|
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
|
2
root
localhost
NULL
Sleep
NULL
|
|
3
root
localhost
NULL
Query
starting |
show processlist
|
|
4
root
localhost
NULL
Query
9
Waiting
table metadata
alter table slowtech.t1 add c1
|
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
3
rows
(0.00
sec)
提示，类似代码可左右滑动
其实，导致 DDL 阻塞的操作，无非两类：
1. 慢查询
2. 表上有事务未提交
其中，第一类比较好定位，通过 show processlist 即能发现。而第二类基本没法定位，因为未提交事务的连接在 show processlist 中的输出同空闲连接一样。
如下面 Id 为 2 的连接，虽然 Command 显示为“Sleep”，其实是事务未提交。
mysql show processlist;+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+| 2 | root | localhost | NULL | Sleep | 77 | | NULL || 3 | root | localhost | NULL | Query | 0 | starting | show processlist || 4 | root | localhost | NULL | Query | 44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+3 rows in set (0.00 sec)所以，网上有 kill 空闲（Command 为 Sleep）连接的说法，其实也不无道理，但这样做就太简单粗暴了，难免会误杀。
其实，既然是事务，在 information_schema. innodb_trx 中肯定会有记录，如会话 1 中的事务，在表中的记录如下，
mysql select * from information_schema.innodb_trx\G*************************** 1. row *************************** trx_id: 1050390 trx_state: RUNNING trx_started: 2018-07-17 08:55:32 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 4 trx_mysql_thread_id: 2 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 3 trx_rows_modified: 2 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)其中 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 如下，
SELECT processlist_id, sql_text FROM ( SELECT c.processlist_id, substring_index( sql_text, transaction_begin; ,-1 ) sql_text FROM information_schema.innodb_trx a, ( SELECT thread_id, group_concat( CASE WHEN EVENT_NAME = statement/sql/begin THEN transaction_begin ELSE sql_text END ORDER BY event_id SEPARATOR ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) b, performance_schema.threads c WHERE a.trx_mysql_thread_id = c.processlist_id AND b.thread_id = c.thread_id ) t WHERE sql_text LIKE %t1% +----------------+---------------------------------------------------------------------------------------------------------+| processlist_id | sql_text |+----------------+---------------------------------------------------------------------------------------------------------+| 2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name= c where id=1 |+----------------+---------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)从输出来看，确实也达到了预期效果。
需要注意的是，在 MySQL5.6 中，events_ statements_history 默认是没有开启的。
mysql SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE %statements% +--------------------------------+---------+| NAME | ENABLED |+--------------------------------+---------+| events_statements_current | YES || events_statements_history | NO || events_statements_history_long | NO || statements_digest | YES |+--------------------------------+---------+4 rows in set (0.00 sec)关于“MySQL 5.6 中怎么定位 DDL 被阻塞的问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
