MySQL 5.7中如何定位DDL操作的阻塞问题

mysql> begin; 
Query OK, 0 rows affected (0.00 sec) 

mysql> select * from t1; 
+----+------+------+------+-------+ 
| id | name | age  | num  | num01 | 
+----+------+------+------+-------+ 
|  1 | uu   | NULL | NULL |  NULL | 
|  2 | uu   | NULL | NULL |  NULL | 
|  3 | uu   | NULL | NULL |  NULL | 
|  4 | uu   | NULL | NULL |  NULL | 
|  5 | uu   | NULL | NULL |  NULL | 
|  6 | uu   | NULL | NULL |  NULL | 
+----+------+------+------+-------+ 
6 rows in set (0.00 sec) 

mysql> update test01.t1 set age=12 where id=1; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 

mysql> alter table test01.t1 add c1 int; 
Query OK, 0 rows affected (0.14 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql> select  object_type , object_schema , object_name , lock_type , lock_duration , lock_status , owner_thread_id  from  performance_schema . metadata_locks; 
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+ 
| object_type | object_schema      | object_name    | lock_type   | lock_duration | lock_status | owner_thread_id | 
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+ 
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   | GRANTED     |              27 | 
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   | PENDING     |              29 | 
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+ 
1 row in set (0.00 sec) 

这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL 

3、如何找出引起阻塞的会话 
结合owner_thread_id，可以可到，是29号线程在等待27号线程的MDL，此时，可kill掉52号线程。 
但需要注意的是，owner_thread_id给出的只是线程ID，并不是show processlist中的ID。如果要查找线程对应的processlist id，需查询performance_schema.threads表。 

mysql> select * from performance_schema.threads where thread_id in(27,29)\G; 
*************************** 1. row *************************** 
          THREAD_ID: 27 
               NAME: thread/sql/one_connection 
               TYPE: FOREGROUND 
     PROCESSLIST_ID: 21 
   PROCESSLIST_USER: root 
   PROCESSLIST_HOST: localhost 
     PROCESSLIST_DB: test01 
PROCESSLIST_COMMAND: Sleep 
   PROCESSLIST_TIME: 901 
  PROCESSLIST_STATE: NULL 
   PROCESSLIST_INFO: select  object_type , object_schema , object_name , lock_type , lock_duration , lock_status , owner_thread_id  from  performance_schema . metadata_locks 
   PARENT_THREAD_ID: 1 
               ROLE: NULL 
       INSTRUMENTED: YES 
            HISTORY: YES 
    CONNECTION_TYPE: Socket 
       THREAD_OS_ID: 2066 
1 row in set (0.00 sec) 
*************************** 1. row *************************** 
          THREAD_ID: 29 
               NAME: thread/sql/one_connection 
               TYPE: FOREGROUND 
     PROCESSLIST_ID: 12 
   PROCESSLIST_USER: root 
   PROCESSLIST_HOST: localhost 
     PROCESSLIST_DB: test01 
PROCESSLIST_COMMAND: Query 
   PROCESSLIST_TIME: 901 
  PROCESSLIST_STATE: Waiting for table metadata lock 
   PROCESSLIST_INFO:  alter table test01.t1 add c1 int; 
   PARENT_THREAD_ID: 1 
               ROLE: NULL 
       INSTRUMENTED: YES 
            HISTORY: YES 
    CONNECTION_TYPE: Socket 
       THREAD_OS_ID: 2066 
1 row in set (0.00 sec) 

将这两张表结合，借鉴sys.schema_table_lock_waits的输出，实际上我们也可以直观地呈现MDL的等待关系。 
 mysql> select *  from sys.schema_table_lock_waits\G;