mysql5.6.20開啟慢查詢日志以及建立索引優化慢查詢

[root@localhost ~]# egrep "slow_query_log*|long_query_time|slow-query-log-file" /usr/local/mysql5.6/my.cnf

long_query_time = 1  （慢查詢時間）

slow_query_log=1

slow-query-log-file = /data/mysql3307/log/mysql-slow.log

log_queries_not_using_indexes=1 （#記錄沒有使用索引的查詢）

在mysql控制台修改,無需重新開機mysqld服務:

#開啟慢查詢日志記錄

mysql> set global slow_query_log=on;

Query OK, 0 rows affected (0.00 sec)

#查詢時間超過0.1秒的sql語句會被記錄

mysql> set global long_query_time=0.1;

Query OK, 0 rows affected (0.03 sec)

#記錄慢查詢日志的檔案位址

mysql> set global slow_query_log_file="/var/lib/mysql/localhost-slow.log";

Query OK, 0 rows affected (0.04 sec)

#記錄沒有使用索引的查詢

mysql> set global log_queries_not_using_indexes=on;

建立聯合索引：

SELECT `user_id`,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM `tab_user_login_record` WHERE `login_time` BETWEEN 1501862400 AND 1503158399 GROUP BY user_id;

原先表字段user_id,login_time建立的都是單個索引，

SELECT `user_id`,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM `tab_user_login_record` WHERE `login_time` BETWEEN 1501862400 AND 1503158399 GROUP BY 

執行查詢花費是時間為3.2s

記錄到慢查詢日志中：

[root@localhost ~]# tailf /data/mysql3307/log/mysql-slow.log 

# Time: 170913 15:57:05

# User@Host: root[root] @ localhost []  Id:    12

# Query_time: 2.523547  Lock_time: 0.000160 Rows_sent: 18760  Rows_examined: 1742609

SET timestamp=1505289425;

# Time: 170913 15:57:27

# Query_time: 2.501662  Lock_time: 0.000149 Rows_sent: 18760  Rows_examined: 1742609

SET timestamp=1505289447;

給字段建立聯合索引：

create index union_index on tab_user_login_record (user_id,login_time);

清除查詢緩存：

reset query cache;

花費的時間是0.62秒

沒有再記錄到慢查詢日志檔案中

分析慢查詢日志mysqlsla：

[root@localhost ~]# mysqlsla -lt slow /data/mysql3307/log/mysql-slow.log -sf "+SELECT" -top 2 -sort t_sum

Report for slow logs: /data/mysql3307/log/mysql-slow.log

6 queries total, 1 unique

Sorted by 't_sum'

Grand Totals: Time 17 s, Lock 0 s, Rows sent 112.56k, Rows Examined 10.46M

______________________________________________________________________ 001 ___

Count         : 6  (100.00%)

Time          : 16.583032 s total, 2.763839 s avg, 2.501662 s to 4.005698 s max  (100.00%)

Lock Time (s) : 1.774 ms total, 296 μs avg, 149 μs to 954 μs max  (100.00%)

Rows sent     : 18.76k avg, 18.76k to 18.76k max  (100.00%)

Rows examined : 1.74M avg, 1.74M to 1.74M max  (100.00%)

Database      : s2166ptzy

Users         : 

root@localhost  : 100.00% (6) of query, 100.00% (6) of all users

Query abstract:

SELECT user_id,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM tab_user_login_record WHERE login_time BETWEEN N AND N GROUP BY user_id;

Query sample:

 本文轉自 wjw555 51CTO部落格，原文連結:http://blog.51cto.com/wujianwei/1964987