技術文章 » MySQL資料庫執行analyze採集資訊

MySQL資料庫執行analyze採集資訊

2020-11-19 18:01:32

欄目執行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, or 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)複製程式碼

殺掉對談，故障解除。

建議

生產執行analyze table建議 1.執行之前，先估算一下表的資料量，根據經驗預估需要消耗的時間，同時檢視是否有采集資訊表的慢SQL，長事務在執行。

2.避免在業務高峰期執行analyze table進行統計資訊採集。

更多相關免費學習推薦：(視訊)

以上就是MySQL資料庫執行analyze採集資訊的詳細內容，更多請關注TW511.COM其它相關文章！