1. Flush tables簡介

官方手冊中關于Flush tables的介紹

Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

其解析就是關閉所有已打開的表對象，同時將查詢緩存中的結果清空。就是說Flush tables的一個效果就是會等待所有正在運行的SQL請求結束。

因為，語句在執(zhí)行前，都會打開相應的表對象，如select * from t1語句，會找到t1表的frm文件，并打開表內(nèi)存對象。

為了控制表對象使用的內(nèi)存空間和其他資源，MySQL會隱式（后臺表對象管理線程）或顯式（flush tables等）來關閉已打開但并沒有使用的表對象。

然而，正在使用的表對象是不能關閉的（如SQL請求仍在運行），因此，F(xiàn)lush Tables操作會被正在運行的SQL請求阻塞。

2. Flush tables的影響

2.1 Flush tables

由于Flush tables會等待所有正在運行的SQL請求，那么，該操作會阻塞其他會話嗎？

下面，我們通過一個例子來說明。

• 會話1：

Select sleep(50) from t1 limit 1;
--阻塞，執(zhí)行時間是50s
+-----------+
| sleep(50) |
+-----------+
| 0 |
+-----------+
1 row in set (50.13 sec)

• 會話2：

Flush tables;
--阻塞，直到會話1結束
Query OK, 0 rows affected (48.27 sec)

• 會話3：

Select c1 from t1 limit 1;
--阻塞，直到會話1和會話2結束
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (47.23 sec)

• 會話4：

Select c1 from t2 limit 1;
--無阻塞
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

執(zhí)行4個會話的并發(fā)測試，并且4會話語句依次執(zhí)行。

• 1 會話1通過sleep(50)構造一個執(zhí)行時間為50秒的SQL請求，操作表是t1
• 2 會話2執(zhí)行flush tables
• 3 會話3同樣對t1執(zhí)行一個普通查詢
• 4 會話4對t2執(zhí)行一個普通查詢

由測試現(xiàn)象知，會話4無阻塞，會話2和會話3等待會話1的完成，因此，

1）flush tables會等待正在運行的所有語句執(zhí)行結束，即使運行的是查詢請求；

2）如果flush tables等待SQL請求操作的表集合為{tables}，這里的tables組合應該是當前正在有sql在運行的，已經(jīng)打開的表，同一個庫中，沒有sql運行的表是不計算在內(nèi)，因此 若 庫star中有3張表，tx1和tx2被打開了，，的若有新請求操作{tables}中的任意一表，這些請求都會阻塞（即使是普通查詢），如會話3；

3）如果其他會話新請求操作{tables}外的其他表，不會被阻塞，如會話4。

從此可知，flush tables操作可認為是{tables}所有表的表級排他鎖，會阻塞其他會話關于{tables}表上的所有操作。假設一個大查詢或長事務過程中（如會話1）執(zhí)行flush tables操作，那么flush tables會等待長事務的結束（如會話1），同時阻塞關于{tables}的新請求。

2.2 Flush tables with read lock

Flush tables with read lock是另一個常見的操作，與Flush tables的作用是一樣的，同樣會等待所有正在運行的SQL請求結束，只是增加了一個全局讀鎖，即阻塞所有庫所有表的寫操作，直到unlock tables操作完成。

通過一個例子來說明它們的區(qū)別：

• 會話1：

Select sleep(50) from t1 limit 1;
--阻塞，執(zhí)行時間是50s
+-----------+
| sleep(50) |
+-----------+
| 0 |
+-----------+
1 row in set (50.13 sec)

• 會話2：

Flush tables with read lock;
--阻塞，直到會話1結束
Query OK, 0 rows affected (48.27 sec)

Unlock tables;
Query OK, 0 rows affected (0.01 sec)

會話3：

Select c1 from t1 limit 1;
--阻塞，直到會話1和會話2結束
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (47.23 sec)

• 會話4：

Select c1 from t2 limit 1;
--無阻塞
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

• 會話5：

Insert into t2(c1) values(10);
--阻塞，直到會話2中執(zhí)行unlock tables
Query OK, 1 row affected (50.23 sec)

與第一個例子類似，依次執(zhí)行多個會話。不同的是會話2執(zhí)行Flush tables with read lock，同時增加會話5對t2表插入記錄。

由測試現(xiàn)象知，該操作有以下特點：

1）與flush tables一樣，flush tables with read lock會等待正在運行的所有語句執(zhí)行結束（如會話1）；

2）如果flush tables with read lock等待SQL請求操作的表集合為{tables}，若有新請求操作{tables}中的任意一表，這些請求都會阻塞

• a) 如果是查詢請求，在flush tables with read lock結束后就可執(zhí)行，如會話3;
• b) 如果是插入、更新等寫請求，必須等待unlock tables釋放讀鎖，跟會話5類似
• 3) 如果其他會話新請求操作{tables}外的其他表，則
• a) 如果是查詢請求，不會被阻塞，如會話4;
• b) 如果是寫請求，必須等待unlock tables釋放讀鎖，如會話5

因此，flush tables with read lock操作是{tables}所有表的表級排他鎖，同時是庫級讀鎖，會阻塞庫上所有寫操作，直到執(zhí)行unlock tables。其影響面比flush tables更大。

也就是比flush tables多一點影響，阻塞了｛tables}之外的表的寫操作，不影響其讀操作，只有unlock tables之后，釋放了這個對庫的全局讀鎖之后，才可以寫

3. 一致性備份的問題

一般情況下，很少會主動使用flush tables和flush tables with read lock操作。

更多使用這兩個命令是mysqldump進行數(shù)據(jù)備份的時候。

如果使用mysqldump進行一致性備份時，一般指定了--master-data和--single-transaction這兩個參數(shù)，那么在備份操作執(zhí)行前，先執(zhí)行flush tables和flush tables with read lock這兩個命令，以獲得此一致性讀的binlog位置。

獲得binlog位置的過程為：

• 1) flush tables操作是等待正在運行的所有操作結束;
• 2) flush tables with read locks是為了加 庫級全局讀鎖，禁止寫操作;
• 3) 通過show master status獲得此時binlog位置;
• 4) unlock tables釋放全局讀鎖，允許寫請求。

先執(zhí)行flush tables而不是直接執(zhí)行flush tables with read locks的原因是，flush tables阻塞其他請求的可能性更少。假設flush tables的過程中出現(xiàn)大查詢，從前面的分析知道，僅影響其他會話關于{tables}表的請求，而不像flush tables with read locks會阻塞所有寫操作。

然而，以上操作只是大大減少了全局讀鎖的影響范圍，如果在flush tables和flush tables with read locks之間出現(xiàn)大事務，還是有可能會出現(xiàn)所有寫操作hang住的情況。因此，必須謹慎使用一致性備份的功能。

另外，經(jīng)測試，如果出現(xiàn)flush tables阻塞其他會話的情況，如會話3、會話5的操作，是不會記錄慢查詢?nèi)罩镜?，但事實上，應用程序可能是得不到迅速的響應了?/p>

解決

1：show open tables where in_use >=1;

2：找到阻塞該表的查詢語句

SELECT * FROM information_schema.`PROCESSLIST`
WHERE info IS NOT NULL
AND state NOT LIKE 'Waiting for table flush'
AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'
AND INFO LIKE '%處于打開狀態(tài)的表名%'
ORDER BY TIME DESC

3：kill id 殺死該sql進程

備注：還可以殺死flush tables的進程，該操作慎用，可能在執(zhí)行一致性備份

SELECT * FROM information_schema.`PROCESSLIST`
WHERE info LIKE '%flush tables%'
AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'

總結

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論