MySQL資料庫執行analyze擷取訊息-mysql教程-PHP中文網

MySQL教學欄執行analyze擷取資訊。

MySQL資料庫執行analyze擷取訊息

故障簡介

之前，有開發找到我，說應用程式的某個功能查詢比以前慢了很多，讓開發提供了慢的SQL語句，去對應的MySQL資料庫看了一下執行計劃，發現執行計劃不正確，第一反應就是其中的一個表的統計資訊不準確，導致了SQL語句的執行計劃不對，從高效的查詢SQL變成了慢SQL。定位到問題之後，自然是 analyze 一下，重新採集信息，這個時候，卻發現 analyze 表上的所有 select 突然卡住了，不返回任何結果，然後應用就炸了，各種告警短信。

故障複盤

當時執行analyze操作的是一個slave函式庫，受影響基本上是select查詢，所以在這裡模擬的是查詢操作。

建立模擬表

mysql> select * from t_test_1;
+----+--------+-------+--------+
| id | name   | name2 | status |
+----+--------+-------+--------+
|  1 | name1  | 1001  |      0 |
|  2 | name1  | 1002  |      1 |
|  3 | name1  | 1003  |      1 |
|  4 | name1  | 1004  |      0 |
|  5 | name1  | 1005  |      1 |
|  6 | name1  | 1006  |      0 |
|  7 | name1  | 1007  |      2 |
|  8 | name1  | 1008  |      0 |
|  9 | name1  | 1009  |      1 |
| 10 | name10 | 1001  |      0 |
+----+--------+-------+--------+
10 rows in set (0.00 sec)复制代码

登入後複製

模擬慢查詢，由於這裡資料量不夠，所以用sleep代替 session1：模擬慢查詢

mysql> select sleep(1000) from t_test_1;复制代码

登入後複製

session2：模擬收集表的統計資訊

mysql> analyze table t_test_1;复制代码

登入後複製

session3：模擬執行analyze指令之後，在t_test_1表上執行一次select查詢

mysql> select * from t_test_1 where id=5;复制代码

登入後複製

session4：查詢所有會話資訊

mysql> select * from processlist order by time desc;
+----+------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------+
| ID | USER | HOST      | DB                 | COMMAND | TIME | STATE                   | INFO                                         |
+----+------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------+
| 21 | root | localhost | testdb             | Query   |  242 | User sleep              | select sleep(1000) from t_test_1             |
| 23 | root | localhost | testdb             | Query   |  180 | Waiting for table flush | analyze table t_test_1                       |
| 24 | root | localhost | testdb             | Query   |    3 | Waiting for table flush | select * from t_test_1 where id=5            |
| 22 | root | localhost | information_schema | Query   |    0 | executing               | select * from processlist order by time desc |
+----+------+-----------+--------------------+---------+------+-------------------------+----------------------------------------------+
4 rows in set (0.00 sec)复制代码

登入後複製

從session4取得的所有會話資訊中，可以看到有2個會話的狀態是「Waiting for table flush」。

Waiting for table flush原因

當MySQL資料庫做FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE這些操作時，會導致需要關閉記憶體中的表，並重新開啟表，載入新的表結構到記憶體中。但關閉表，需要等待所有的在這個表上的操作執行結束（包括select,insert,update,lock table等），所以當有一個特別慢的select一直在執行時，analyze table命令就一直無法結束。

解決方案

現在知道什麼原因導致的Waiting for table flush，就開始定位慢sql語句。這裡可以看到我們執行的是採集t_test_1表，所以需要查詢涉及t_test_1表的慢查詢，並且執行時間比analyze table t_test_1的執行時間還要長的會話。

mysql> select * from processlist where info like '%t_test_1%' and time >=(select time from processlist where id=23)  order by time desc;
+----+------+-----------+--------+---------+------+-------------------------+----------------------------------+
| ID | USER | HOST      | DB     | COMMAND | TIME | STATE                   | INFO                             |
+----+------+-----------+--------+---------+------+-------------------------+----------------------------------+
| 21 | root | localhost | testdb | Query   | 1187 | User sleep              | select sleep(1000) from t_test_1 |
| 23 | root | localhost | testdb | Query   | 1125 | Waiting for table flush | analyze table t_test_1           |
+----+------+-----------+--------+---------+------+-------------------------+----------------------------------+
2 rows in set (0.37 sec)复制代码

登入後複製

用上面的sql語句，很容易就定位到id=21的會話，導致analyze table t_test_1卡死，所以需要kill掉會話21.

mysql> kill 21;
Query OK, 0 rows affected (0.01 sec)

mysql> show full processlist;
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
| Id | User | Host      | db                 | Command | Time | State    | Info                  |
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
| 22 | root | localhost | information_schema | Query   |    0 | starting | show full processlist |
| 23 | root | localhost | testdb             | Sleep   | 1205 |          | NULL                  |
| 24 | root | localhost | testdb             | Sleep   | 1028 |          | NULL                  |
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
3 rows in set (0.00 sec)复制代码

登入後複製

殺掉會話，故障解除。