MySQL數(shù)據(jù)庫(kù)執(zhí)行analyze采集信息的示例

這篇文章給大家分享的是有關(guān)MySQL數(shù)據(jù)庫(kù)執(zhí)行analyze采集信息的示例的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧。

故障簡(jiǎn)介

之前，有開(kāi)發(fā)找到我，說(shuō)應(yīng)用的某個(gè)功能查詢比以前慢了很多，讓開(kāi)發(fā)提供了慢的SQL語(yǔ)句，去對(duì)應(yīng)的MySQL數(shù)據(jù)庫(kù)看了一下執(zhí)行計(jì)劃，發(fā)現(xiàn)執(zhí)行計(jì)劃不正確，第一反應(yīng)就是其中的一個(gè)表的統(tǒng)計(jì)信息不準(zhǔn)確，導(dǎo)致了SQL語(yǔ)句的執(zhí)行計(jì)劃不對(duì)，從高效的查詢SQL變成了慢SQL。定位到問(wèn)題之后，自然是 analyze 一下，重新采集信息，這個(gè)時(shí)候，卻發(fā)現(xiàn) analyze 表上的所有 select 突然卡住了，不返回任何結(jié)果，然后應(yīng)用就炸了，各種告警短信。

故障復(fù)盤(pán)

當(dāng)時(shí)執(zhí)行analyze操作的是一個(gè)slave庫(kù)，受影響基本是select查詢，所以在這里模擬的是查詢操作。

創(chuàng)建模擬表

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)復(fù)制代碼

模擬慢查詢，由于這里數(shù)據(jù)量不夠，所以用sleep代替 session1：模擬慢查詢

mysql> select sleep(1000) from t_test_1;復(fù)制代碼

session2：模擬收集表的統(tǒng)計(jì)信息

mysql> analyze table t_test_1;復(fù)制代碼

session3：模擬執(zhí)行analyze命令之后，在t_test_1表上執(zhí)行一次select查詢

mysql> select * from t_test_1 where id=5;復(fù)制代碼

session4：查詢所有會(huì)話信息

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)復(fù)制代碼

從session4獲取的所有會(huì)話信息中，可以看到有2個(gè)會(huì)話的狀態(tài)是“Waiting for table flush”。

Waiting for table flush原因

當(dāng)MySQL數(shù)據(jù)庫(kù)做FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE這些操作時(shí)，會(huì)導(dǎo)致需要關(guān)閉內(nèi)存中的表，并重新打開(kāi)表，加載新的表結(jié)構(gòu)到內(nèi)存中。但是關(guān)閉表，需要等待所有的在這個(gè)表上的操作執(zhí)行結(jié)束（包括select,insert,update,lock table等），所以當(dāng)有一個(gè)特別慢的select一直在執(zhí)行時(shí)，analyze table命令就一直無(wú)法結(jié)束。

解決方案

既然知道什么原因?qū)е碌腤aiting for table flush，就開(kāi)始定位慢sql語(yǔ)句。在這里可以看到我們執(zhí)行的是采集t_test_1表，所以需要查詢涉及t_test_1表的慢查詢，并且執(zhí)行時(shí)間比analyze table t_test_1的執(zhí)行時(shí)間還要長(zhǎng)的會(huì)話。

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)復(fù)制代碼

用上面的sql語(yǔ)句，很容易就定位到id=21的會(huì)話，導(dǎo)致analyze table t_test_1卡死，所以需要kill掉會(huì)話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)復(fù)制代碼

殺掉會(huì)話，故障解除。

建議

生產(chǎn)執(zhí)行analyze table建議 1.執(zhí)行之前，先估算一下表的數(shù)據(jù)量，根據(jù)經(jīng)驗(yàn)預(yù)估需要消耗的時(shí)間，同時(shí)查看是否有采集信息表的慢SQL，長(zhǎng)事務(wù)在執(zhí)行。

2.避免在業(yè)務(wù)高峰期執(zhí)行analyze table進(jìn)行統(tǒng)計(jì)信息采集。

感謝各位的閱讀！關(guān)于MySQL數(shù)據(jù)庫(kù)執(zhí)行analyze采集信息的示例就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！