1. Flush tables簡(jiǎn)介

官方手冊(cè)中關(guān)于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.

其解析就是關(guān)閉所有已打開(kāi)的表對(duì)象，同時(shí)將查詢緩存中的結(jié)果清空。就是說(shuō)Flush tables的一個(gè)效果就是會(huì)等待所有正在運(yùn)行的SQL請(qǐng)求結(jié)束。

因?yàn)椋Z(yǔ)句在執(zhí)行前，都會(huì)打開(kāi)相應(yīng)的表對(duì)象，如select * from t1語(yǔ)句，會(huì)找到t1表的frm文件，并打開(kāi)表內(nèi)存對(duì)象。

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

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

2. Flush tables的影響

2.1 Flush tables

由于Flush tables會(huì)等待所有正在運(yùn)行的SQL請(qǐng)求，那么，該操作會(huì)阻塞其他會(huì)話嗎？

下面，我們通過(guò)一個(gè)例子來(lái)說(shuō)明。

• 會(huì)話1：

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

• 會(huì)話2：

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

• 會(huì)話3：

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

• 會(huì)話4：

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

執(zhí)行4個(gè)會(huì)話的并發(fā)測(cè)試，并且4會(huì)話語(yǔ)句依次執(zhí)行。

• 1 會(huì)話1通過(guò)sleep(50)構(gòu)造一個(gè)執(zhí)行時(shí)間為50秒的SQL請(qǐng)求，操作表是t1
• 2 會(huì)話2執(zhí)行flush tables
• 3 會(huì)話3同樣對(duì)t1執(zhí)行一個(gè)普通查詢
• 4 會(huì)話4對(duì)t2執(zhí)行一個(gè)普通查詢

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

1）flush tables會(huì)等待正在運(yùn)行的所有語(yǔ)句執(zhí)行結(jié)束，即使運(yùn)行的是查詢請(qǐng)求；

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

3）如果其他會(huì)話新請(qǐng)求操作{tables}外的其他表，不會(huì)被阻塞，如會(huì)話4。

從此可知，flush tables操作可認(rèn)為是{tables}所有表的表級(jí)排他鎖，會(huì)阻塞其他會(huì)話關(guān)于{tables}表上的所有操作。假設(shè)一個(gè)大查詢或長(zhǎng)事務(wù)過(guò)程中（如會(huì)話1）執(zhí)行flush tables操作，那么flush tables會(huì)等待長(zhǎng)事務(wù)的結(jié)束（如會(huì)話1），同時(shí)阻塞關(guān)于{tables}的新請(qǐng)求。

2.2 Flush tables with read lock

Flush tables with read lock是另一個(gè)常見(jiàn)的操作，與Flush tables的作用是一樣的，同樣會(huì)等待所有正在運(yùn)行的SQL請(qǐng)求結(jié)束，只是增加了一個(gè)全局讀鎖，即阻塞所有庫(kù)所有表的寫(xiě)操作，直到unlock tables操作完成。

通過(guò)一個(gè)例子來(lái)說(shuō)明它們的區(qū)別：

• 會(huì)話1：

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

• 會(huì)話2：

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

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

會(huì)話3：

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

• 會(huì)話4：

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

• 會(huì)話5：

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

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

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

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

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

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

因此，flush tables with read lock操作是{tables}所有表的表級(jí)排他鎖，同時(shí)是庫(kù)級(jí)讀鎖，會(huì)阻塞庫(kù)上所有寫(xiě)操作，直到執(zhí)行unlock tables。其影響面比f(wàn)lush tables更大。

也就是比f(wàn)lush tables多一點(diǎn)影響，阻塞了｛tables}之外的表的寫(xiě)操作，不影響其讀操作，只有unlock tables之后，釋放了這個(gè)對(duì)庫(kù)的全局讀鎖之后，才可以寫(xiě)

3. 一致性備份的問(wèn)題

一般情況下，很少會(huì)主動(dòng)使用flush tables和flush tables with read lock操作。

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

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

獲得binlog位置的過(guò)程為：

• 1) flush tables操作是等待正在運(yùn)行的所有操作結(jié)束;
• 2) flush tables with read locks是為了加 庫(kù)級(jí)全局讀鎖，禁止寫(xiě)操作;
• 3) 通過(guò)show master status獲得此時(shí)binlog位置;
• 4) unlock tables釋放全局讀鎖，允許寫(xiě)請(qǐng)求。

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

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

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

解決

1：show open tables where in_use >=1;

2：找到阻塞該表的查詢語(yǔ)句

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 '%處于打開(kāi)狀態(tài)的表名%'
ORDER BY TIME DESC

3：kill id 殺死該sql進(jìn)程

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

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

總結(jié)

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