[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;
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
记录到慢查询日志中：
[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;
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秒
[root@localhost ~]# tailf /data/mysql3307/log/mysql-slow.log
没有再记录到慢查询日志文件中
分析慢查询日志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:
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;
