mysql show query log_MySQL 日志之--慢查询日志（slow-query-log）
慢查询日志：MySQL慢查询日志记录下所有执行超过long_query_time时间的SQL语句，帮你找到执行慢的SQL，方便我们对这些SQL进行优化。慢查询日志的配置：
默认情况下，mysql没有启用慢查询日志。
[root@rh64 ~]# mysql -u root -p
mysql> show variables like '%slow%';
+------------------------------------+------------------------------+
| Variable_name | Value |
+------------------------------------+------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_filter | |
| log_slow_rate_limit | 1 |
| log_slow_rate_type | session |
| log_slow_slave_statements | OFF |
| log_slow_sp_statements | ON |
| log_slow_verbosity | |
| max_slowlog_files | 0 |
| max_slowlog_size | 0 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_always_write_time | 10.000000 |
| slow_query_log_file | /var/lib/mysql/rh64-slow.log |
| slow_query_log_timestamp_always | OFF |
| slow_query_log_timestamp_precision | second |
| slow_query_log_use_global_control | |
+------------------------------------+------------------------------+
16 rows in set (0.01 sec)1、可以配置my.cnf文件，服务启动时自动配置
[root@rh64 ~]# cat /etc/my.cnf
重启server后，查看：
mysql> show variables like '%slow%';
2、在系统中配置slow-query-log
mysql> set @@global.slow_query_log = on;
mysql> show variables like '%slow%';
3、查看慢查询日志信息
[root@rh64 mysql]# tail rh64-slow.log
记录没有使用索引的语句：
mysql> set @@global.log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
测试：
mysql> select count(*) from emp1 where empno=7788;
+----------+
| count(*) |
+----------+
| 688128 |
+----------+
1 row in set (4.03 sec)[root@rh64 mysql]# tail rh64-slow.log
4、通过mysqldumpslow工具查看慢查询日志
[root@rh64 mysql]# mysqldumpslow
[root@rh64 mysql]# mysqldumpslow --help
[root@rh64 mysql]# mysqldumpslow rh64-slow.log
按照平均锁定时间进行排序，查找前10名：
[root@rh64 mysql]# mysqldumpslow -s al -n 10 rh64-slow.log
Reading mysql slow query log from rh64-slow.log
Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
SET timestamp=N;
insert into emp1 select * from emp1
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
use prod;
SET timestamp=N;
insert into emp1 select * from emp1
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
# Schema: prod Last_errno: N Killed: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N Rows_affected: N
# Bytes_sent: N
SET timestamp=N;
select count(*) from emp1 where empno=N