mysql5.6.20 turn on slow query log and create INDEX optimization slow query

[Email protected] ~]# egrep "Slow_query_log*|long_query_time|slow-query-log-file"/usr/local/mysql5.6/my.cnf

Long_query_time = 1 (slow query time)

Slow_query_log=1

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

Log_queries_not_using_indexes=1 (#记录没有使用索引的查询)

Modify the MySQL console without restarting the MYSQLD service:

#开启慢查询日志记录

mysql> set global slow_query_log=on;

Query OK, 0 rows Affected (0.00 sec)

#查询时间超过0.1 seconds of SQL statements are logged

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)

To create a federated index:

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;

The original table field User_id,login_time created a single index,

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

Execution of the query takes time to 3.2s

Log in to slow query logs:

[Email protected] ~]# Tailf/data/mysql3307/log/mysql-slow.log

# time:170913 15:57:05

# [email protected]: 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

# [email protected]: 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;

To create a federated index for a field:

Create INDEX Union_index on Tab_user_login_record (user_id,login_time);

Clear the query cache:

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

It takes 0.62 seconds.

[Email protected] ~]# Tailf/data/mysql3307/log/mysql-slow.log

No more records in the slow query log file

Parsing slow query log Mysqlsla:

[Email protected] ~]# 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-S, Lock 0 s, rows sent 112.56k, rows examined 10.46M

______________________________________________________________________ 001 ___

Count:6 (100%)

time:16.583032 s Total, 2.763839 s AVG, 2.501662 s to 4.005698 s Max (100%)

Lock time (s): 1.774 MS Total, 296μs AVG, 149μs to 954μs Max (100%)

Rows sent:18.76k avg, 18.76k to 18.76k Max (100%)

Rows examined:1.74m avg, 1.74M to 1.74M Max (100%)

Database:s2166ptzy

Users:

[Email protected]: 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 A ND 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;

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1964987

mysql5.6.20 turn on slow query log and create INDEX optimization slow query