mysql Waiting for table flush出现的原因及场景复现--analyze table

测试环境

centos7.6+mysql5.6.46

场景复现–analyze table

会话1：模拟慢查询
mysql> select fname,sleep(60) from t1;

会话2：分析表
mysql> analyze table t1;
±--------±--------±---------±---------+
| Table | Op | Msg_type | Msg_text |
±--------±--------±---------±---------+
| yjgk.t1 | analyze | status | OK |
±--------±--------±---------±---------+
会话3：再次查询该被hang住了
mysql> select * from t1;
查看发台进程

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+
| Id | User            | Host      | db   | Command | Time  | State                   | Info                           |
+----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 68624 | Waiting on empty queue  | NULL                           |
|  9 | root            | localhost | yjgk | Query   |    93 | User sleep              | select fname,sleep(64) from t1 |
| 10 | root            | localhost | yjgk | Sleep   |    90 |                         | NULL                           |
| 11 | root            | localhost | yjgk | Query   |    79 | Waiting for table flush | select * from t1               |
| 12 | root            | localhost | yjgk | Query   |     0 | init                    | show processlist               |
+----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+

查看打开的表
mysql> show open tables where in_use>=1;
±---------±------±-------±------------+
| Database | Table | In_use | Name_locked |
±---------±------±-------±------------+
| yjgk | t1 | 1 | 0 |
±---------±------±-------±------------+