前言:
对于一个web而言,性能作为比较重要的一个指标,侧面影响着用户的体验,对于一个web的发展起着至关重要的作用。
sql优化又作为性能优化一个重要的部分,对于sql的优化应该给予足够的重视。在此前提下,我们对sql进行优化,降低响应时间,提升用户体验。
如今问题来了,我们怎么知道哪些sql需要优化,哪些sql性能低下等问题。在此背景下,慢查日志出现了。
慢查日志可以帮我们去捕获服务器上的所有查询,从而供我们去分析sql语句存在的问题。
(1).part1
我们先来了解一些慢查日志相关的参数
log_output 输出的日志存在在什么文件中,有4种,分别为"file","table","file,table","none". "file"为存放在指定的文件中。"table"为存放在slow_log数据表中。
"file,table"为存放在指定的文件和slow_log数据表中."none"为不记录查询日志。默认设置为"file".
slow_query_log 是否开启慢查日志 。默认为off
slow_query_log_file 慢查日志存在的位置
long_query_time 超过多少时间被认定为慢查。默认为10sec
long_queries_not_using_indexes 没有使用索引是否被记录到慢查日志中。
(2)开启慢查日志,及配置相关选项
查看慢查日志的信息
show variables like "%slow_query_log%";
mysql> show variables like "%slow_query_log%";
+---------------------+----------------------------------------------+
| Variable_name | Value |+---------------------+----------------------------------------------+| slow_query_log | OFF || slow_query_log_file | /Applications/MAMP/db/mysql56/bogon-slow.log
设置long_query_time为3sec
mysql> show variables like "%long_query_time%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> set long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%long_query_time%";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
设置log_outputmysql> set global log_output='file,table';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%log_output%";
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)
打开slow_query_log
mysql> show variables like "%slow_query_log%";
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /Applications/MAMP/db/mysql56/bogon-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like "%slow_query_log%";
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /Applications/MAMP/db/mysql56/bogon-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.00 sec)
执行sql语句,获得慢查日志
mysql> select count(*) from test.card;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (5.12 sec)
mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+--------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+--------------------------------+-----------+
| 2017-10-04 12:07:08 | root[root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 10000000 | | 0 | 0 | 0 | select count(*) from test.card | 1 |
| 2017-10-04 16:51:52 | root[root] @ localhost [] | 00:00:05 | 00:00:00 | 1 | 10000000 | | 0 | 0 | 0 | select count(*) from test.card | 1 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+--------------------------------+-----------+
2 rows in set (0.01 sec)
查询慢查日志的细节进入Applications/MAMP/db/mysql56/bogon-slow.log
# Time: 171004 16:51:52
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 5.123174 Lock_time: 0.003564 Rows_sent: 1 Rows_examined: 10000000
SET timestamp=1507107112;
select count(*) from test.card;