Week1017_tip

ARTS - Tip

谈谈MySQL慢查询

什么是慢查询

MySQL的慢查询是MySQL提供的一种记录查询慢的SQL的工具。方便做系统优化。

开启慢查询日志

默认情况下是关闭的，

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /usr/local/var/mysql/bogon-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.01 sec)


ysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置方法

全局变量设置

#将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON'; 

#设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

#查询超过1秒就记录

mysql> set global long_query_time=1;

explain 分析

设置慢查询日志后，就会在日志里记录慢查询SQL， 然后使用 explain 分析。

mysql> explain select * from Employee where AGE > 30;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | Employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+