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, 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采集信息的示例就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！