mysql5.6.20开启慢查询日志以及创建索引优化慢查询

32哦哦

[iyunv@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;
Query OK, 0 rows affected (0.00 sec)



创建联合索引：

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

记录到慢查询日志中：
[iyunv@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;
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;
# Time: 170913 15:57:27
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 2.501662  Lock_time: 0.000149 Rows_sent: 18760  Rows_examined: 1742609
SET timestamp=1505289447;
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;


给字段创建联合索引：
create index union_index on tab_user_login_record (user_id,login_time);

清除查询缓存：
reset query cache;
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

花费的时间是0.62秒
[iyunv@localhost ~]# tailf /data/mysql3307/log/mysql-slow.log
没有再记录到慢查询日志文件中

分析慢查询日志mysqlsla：

[iyunv@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:
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;