Press "Enter" to skip to content

MySQL慢查询日志总结

[方式一]

进入Mysql: mysql -u root -h 127.0.0.1 -p root

查看慢查询相关的参数

show variables like 'slow_query%'; [慢查询是否开启和日志位置]
show variables like 'long_query_time'; [慢查询时间]

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

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

开启

set global slow_query_log='ON';

日志存放位置

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

设置查询时间

set global long_query_time=1;

mysql> set global slow_query_log = 'ON';
Query OK, 0 rows affected (0.37 sec)

mysql> set global slow_query_log_file = '/usr/local/mysql/var/cherry-slow.log';
Query OK, 0 rows affected (0.01 sec)

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

[方式二]

修改配置文件

vi /etc/my.cnf
[mysqld]配置下
slow_query_log=ON
slow_query_log_file='/usr/local/mysql/data/slow.log';
long_query_time=1

重启mysql服务

service mysql restart

测试慢查询

select sleep(2)[执行一条慢查询sql]

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

benchmark(count,expr) [函数可以测试执行count次expr操作需要的时间]

mysql> select benchmark(900000000, 90000000*4);
+----------------------------------+
| benchmark(900000000, 90000000*4) |
+----------------------------------+
+----------------------------------+
1 row in set (10.80 sec)

vi /usr/local/mysql/var/cherry-slow.log [查看慢查询日志]

Time: 180811 11:24:01User@Host: root[root] @ localhost Query_time: 5.000886  Lock_time: 0.000000 Rows_sent: 1  Rows_examined    : 0
SET timestamp=1533957841;
select sleep(5);

Time: 180811 11:34:16
User@Host: root[root] @ localhost []
Query_time: 10.798234  Lock_time: 0.000000 Rows_sent: 1  Rows_examine    d: 0
SET timestamp=1533958456;
select benchmark(900000000, 90000000*4);

慢查询分析

show variables like '%general%'; [当前数据库与版本号相关的信息]

mysql> show variables like '%quer%';
+-------------------------------+--------------------------------------+
| Variable_name                 | Value                                |
+-------------------------------+--------------------------------------+
| ft_query_expansion_limit      | 20                                   |
| have_query_cache              | YES                                  |
| log_queries_not_using_indexes | OFF                                  |
| log_slow_queries              | ON                                   |
| long_query_time               | 10.000000                            |
| query_alloc_block_size        | 8192                                 |
| query_cache_limit             | 1048576                              |
| query_cache_min_res_unit      | 4096                                 |
| query_cache_size              | 8388608                              |
| query_cache_type              | ON                                   |
| query_cache_wlock_invalidate  | OFF                                  |
| query_prealloc_size           | 8192                                 |
| slow_query_log                | ON                                   |
| slow_query_log_file           | /usr/local/mysql/var/cherry-slow.log |
+-------------------------------+--------------------------------------+
14 rows in set (0.00 sec)
1) slow_query_log的值为ON为开启慢查询日志,OFF则为关闭慢查询日志。
2) slow_query_log_file 的值是记录的慢查询日志到文件中(注意:默认名为主机名.log,慢查询日志是否写入指定文件中,需要指定慢查询的输出日志格式为文件,相关命令为:show variables like ‘%log_output%’;去查看输出的格式)。
3) long_query_time 指定了慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认值为10。
4) log_queries_not_using_indexes 如果值设置为ON,则会记录所有没有利用索引的查询(注意:如果只是将log_queries_not_using_indexes设置为ON,而将slow_query_log设置为OFF,此时该设置也不会生效,即该设置生效的前提是slow_query_log的值设置为ON),一般在性能调优的时候会暂时开启。

show variables like '%log_output%'; [日志记录的方式]

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

默认记录方式是文本 可以修改成记录到数据表方式

set global log_output='FILE,TABLE';[设置日志记录的方式]

mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

show global status like '%slow%'; [查询当前慢查询的语句的个数]

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 4     |
+---------------------+-------+
2 rows in set (0.00 sec)

Be First to Comment

发表评论

电子邮件地址不会被公开。 必填项已用*标注