导航：首页 > 数据库 >

Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办

发表于：2026-11-02 作者：千家信息网编辑

千家信息网最后更新 2026年11月02日，本篇文章给大家分享的是有关Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获

千家信息网最后更新 2026年11月02日Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办

本篇文章给大家分享的是有关Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

通过检查发现大量select处于Waiting for table flush 状态，仔细看了一下processlist以及时间段，可以断定是备份加select慢查询引起的！

mysql> show full processlist;
+------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
| 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527333 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 9140 | root | localhost | devops | Query | 564 | User sleep | select *,sleep(1000) from operation_log limit 100 |
| 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 9143 | root | localhost:56880 | NULL | Query | 509 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
终端二执行xtracebackup备份
。。。。。
。。。。。
>> log scanned up to (768745274)
。。。。。备份堵塞
终端一执行一个慢查询
mysql> select *,sleep(1000) from operation_log limit 100

终端3对慢查询涉及到的表进行查询操作
[root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -p***** -e "select * from operation_log limit 10" devops
Warning: Using a password on the command line interface can be insecure.
...堵塞状态

mysql> show full processlist;
+------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
| 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527460 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 9140 | root | localhost | devops | Query | 691 | User sleep | select *,sleep(1000) from operation_log limit 100 |
| 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 9143 | root | localhost:56880 | NULL | Query | 636 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |
| 9150 | root | localhost | devops | Query | 454 | Waiting for table flush | select * from operation_log limit 10 | --查询被堵塞
+------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
步骤1阻塞了步骤二,步骤二导致步骤三需要等待步骤一。

终端四对其它非慢查询中的表进行查询（不堵塞）
[root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from role limit 10" devops
Warning: Using a password on the command line interface can be insecure.
+----+-----------------+--------------------------------+--------+
| id | role_name | description | status |
+----+-----------------+--------------------------------+--------+
| 1 | 超级管理员 | 所有权限 | 1 |
| 3 | 开发工程师 | 开发工程师开发工程师 | 1 |
| 4 | 运维工程师 | 运帷工程师运帷工程师 | 1 |
+----+-----------------+--------------------------------+--------+
[root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from module limit 10" devops
Warning: Using a password on the command line interface can be insecure.
+-----+--------------+--------+------------+
| id | module_name | status | list_order |
+-----+--------------+--------+------------+
| 100 | 系统管理 | 1 | 2 |
| 600 | 环境管理 | 1 | 3 |
+-----+--------------+--------+------------+

原因：
在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables

with read lock就会被阻塞，直到所有的锁被释放。

The thread got a notification that the underlying structure for a table has changed
and it needs to reopen the table to get the new structure.
However, to reopen the table,
it must wait until all other threads have closed the table in question.
This notification takes place if another thread has used FLUSH TABLES
or one of the following statements on the table in question:
FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, orOPTIMIZE TABLE.