MySQL数据库施行analyze采集信息

发布时间：12/01 来源：未知浏览：关键词：

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)复制代码

杀掉会话，故障解除。