MySQL慢查询日志基本配置
查看是否开启慢查询日志功能
mysql> show variables like 'slow_query%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/MiWiFi-R4CM-srv-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.06 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)参数说明：
slow_query_log：慢查询日志是否开启
slow_query_log_file：慢查询日志开启后，日志文件存放位置。这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录
long_query_time：这是一个重要参数，SQL语句执行时间超过这个时间点的都会被记录到日志文件当中，默认为10秒。
慢查询日志功能配置与实现
临时配置，重启后失效：
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.11 sec)
mysql> set global slow_query_log_file='/var/lib/mysql/MiWiFi-R4CM-srv-slow.log';
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=2; ##超过两秒的都被记录到这里
Query OK, 0 rows affected (0.00 sec)
注意：本次会话不会看到修改，先退出mysql，重新连接：
mysql> exit;
Bye
[root@MiWiFi-R4CM-srv test_db-master]# mysql -uroot -p
Enter password:
重新连接后：
mysql> show variables like 'slow_query%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/MiWiFi-R4CM-srv-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)测试
这里使用MySQL官方测试数据库Employees，数据超过30万，包含八个表：
官方地址：https://dev.mysql.com/doc/employee/en/
GitHub地址：https://github.com/datacharmer/test_db
官方安装教程：https://dev.mysql.com/doc/employee/en/employees-installation.html
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from current_dept_emp;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (2.04 sec)
mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.06 sec)
mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
1 row in set (0.21 sec)
mysql> select count(*) from dept_emp_latest_date;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.67 sec)
mysql> select count(*) from dept_manager;
+----------+
| count(*) |
+----------+
| 24 |
+----------+
1 row in set (0.06 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.28 sec)
mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (1.66 sec)
mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
| 443308 |
+----------+
1 row in set (0.49 sec)结果分析
这里验证执行超时的SQL语句是否被记录到日子当中，具体分析需要explain分析，这里暂时不作介绍：
从上面的执行语句当中，可以发现有一条SQL语句（select count(*) from current_dept_emp）的执行时间超过2秒，
查看下日志，已经被记录到日志当中，说明慢查询日志配置成功：
[root@MiWiFi-R4CM-srv 下载]# cat /var/lib/mysql/MiWiFi-R4CM-srv-slow.log
/usr/sbin/mysqld, Version: 5.6.48 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.6.48 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200503 22:37:24
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 2.042059 Lock_time: 0.000412 Rows_sent: 1 Rows_examined: 963230
use employees;
SET timestamp=1588516644;
select count(*) from current_dept_emp;在生产环境中，会有很多日志记录，如果要手工分析日志，查找、分析SQL，显然是个体力活。
MySQL提供了日志分析工具mysqldumpslow：
mysqldumpslow帮助：
[root@MiWiFi-R4CM-srv 下载]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time例子：
1、返回记录集最多的5条SQL语句：
[root@MiWiFi-R4CM-srv 下载]# mysqldumpslow -s r -t 5 /var/lib/mysql/MiWiFi-R4CM-srv-slow.log2、返回访问次数最多的5条SQL：
[root@MiWiFi-R4CM-srv 下载]# mysqldumpslow -s c -t 5 /var/lib/mysql/MiWiFi-R4CM-srv-slow.log3、返回按照时间排序的前5条里面含有左连接的查询语句】
mysqldumpslow -s t -t 5 -g “left join” /var/lib/mysql/MiWiFi-R4CM-srv-slow.log
