-- 此参数配置信息，表示是否激活启动慢日志记录功能，默认处于关闭状态
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

-- 此参数配置信息，表示慢日志文件保存的路径信息；建议日志文件路径与数据存放路径进行分离；
mysql> select @@slow_query_log_file;
+----------------------------------+
| @@slow_query_log_file            |
+----------------------------------+
| /data/3306/data/db01-51-slow.log |
+----------------------------------+
1 row in set (0.00 sec)

-- 此参数信息配置，表示记录慢日志的条件，默认是大于10s执行的语句，就会记录为慢查询语句；(建议时间为0.01~0.1)
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

-- 此参数信息配置，表示慢日志中会记录没有使用索引的语句信息；
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

# 修改日志默认状态（激活日志）：
-- 可以对以上参数信息进行在线调整
mysql> set global slow_query_log=1;
mysql> set global long_query_time=0.01;
mysql> set global log_queries_not_using_indexes=1;
mysql> set global slow_query_log_file=/data/3306/log/slow.log

-- 永久修改，写完配置文件后记得重启mysql
[root@db01-51 ~]# vim /data/3306/data/my.cnf
...
...
[mysqld]
slow_query_log=1
slow_query_log_file=/data/3306/logs/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1
...
...

mysql> use zq;
mysql> show index from t100w;
-- 删除数据表中索引信息(如果没有索引，本步骤可以进行忽略)
mysql> alter table t100w drop index idx;
-- 模拟执行慢查询的操作语句
mysql> select * from t100w limit 100;
mysql> select * from t100w where id=10;
mysql> select * from t100w where id=20;
mysql> select count(*) from t100w group by num limit 10;
...

# 查看核实慢日志文件是否生成
[root@db01-51 ~]# ll /data/3306/logs/slow.log
-rw-r----- 1 mysql mysql 3426 Mar 10 10:53 /data/3306/logs/slow.log
-- 会按照执行语句的操作时间顺序，进行慢查询日志信息的记录；
[root@db01-51 ~]# head -20 /data/3306/logs/slow.log
/usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2025-03-10T02:52:59.434475Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.002490  Lock_time: 0.000152 Rows_sent: 100  Rows_examined: 100
use zq;
SET timestamp=1741575179;
select * from t100w limit 100;
# Time: 2025-03-10T02:53:00.856282Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.000289  Lock_time: 0.000127 Rows_sent: 100  Rows_examined: 100
SET timestamp=1741575180;
select * from t100w limit 100;
# Time: 2025-03-10T02:53:01.792466Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.000310  Lock_time: 0.000125 Rows_sent: 100  Rows_examined: 100
SET timestamp=1741575181;
select * from t100w limit 100;
# Time: 2025-03-10T02:53:03.408176Z

-- 按照慢查询语句的重复执行次数(c)进行排序(-s)，取出其中靠前(t)的前三名慢查询语句
-- 还可以扩展使用pt-query-digest更好的分析慢查询日志，支持图形化展示
-- what to sort by (al, at, ar, c, l, r, t), 'at' is default
[root@db01-51 ~]# mysqldumpslow -s c -t 3 /data/3306/logs/slow.log

Reading mysql slow query log from /data/3306/logs/slow.log
Count: 5  Time=0.52s (2s)  Lock=0.00s (0s)  Rows=10.0 (50), root[root]@localhost
  select count(*) from t100w group by num limit N

Count: 5  Time=0.30s (1s)  Lock=0.00s (0s)  Rows=1.0 (5), root[root]@localhost
  select * from t100w where id=N

Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (400), root[root]@localhost
  select * from t100w limit N