MySQL 四种日志实战

一、错误日志

只有错误日志这一段我是在 docker 里做的，后面几种日志都是 Linux 服务器上装的 MySQL。

1. 文件和配置

默认日志文件是 /var/log/mysql/ 下的 mysqld.log。

# 目录挂载位置
docker run -d \
  --name mysql01 \
  -p 3366:3306 \
  -e MYSQL_ROOT_PASSWORD=123321 \
  -v /opt/mysql/data:/var/lib/mysql \
  -v /opt/mysql/conf.d:/etc/mysql/conf.d \
  # 日志存放目录
  -v /opt/mysql/logs:/var/log/mysql \
  mysql

# 查看日志位置
show variables like '%log_error%';
# 看日志50行(docker 版本不能这样)
tail -n 50 /opt/mysql/logs/mysqld.log

# 用了docker可以这样看
docker logs mysql01 --tail 50

docker 版本里 log_error 的值是 stderr：

stderr 是标准错误流，会被 docker 捕获，只能用 docker logs 看，也就是说 MySQL 不会主动创建 /opt/mysql/logs/mysqld.log。

修改 docker 日志文件方式（不推荐）

让 MySQL 强行输出到那个文件也行，得改 my.cnf：

# 修改容器/etc/my.cnf 但是docker不支持vim
# 之前提到my.cnf会统一读取conf.d下的配置
[mysqld]
log_error = /var/log/mysql/mysqld.log

docker restart mysql01

my.cnf 会统一读取 conf.d 这个片段目录下的配置，而且是递归读的，谁配置层级最小谁生效，所以不用管原来的配置怎么写的，直接在 Linux 挂载的配置目录里加一个配置文件就行，比如 /opt/mysql/conf.d/error.cnf —— 注意后缀是 cnf，片目录配置不能叫 conf。

vim /opt/mysql/conf.d/error.cnf

[mysqld]
log_error = /var/log/mysql/mysqld.log

docker restart mysql01

到这儿其实就差权限了，可权限是容器创建的时候定下来的，事后改不了，所以这段我不演示了，老老实实用 docker logs 看就行。

2. 看日志报错

# 实时查看最后50行错误信息
docker logs --tail 50 -f mysql01

vim /opt/mysql/data/auto.cnf
[auto]
server-uuid=8aa2ab3e-97cb-11f1-997e-f2fbcfa28f7d
# 删去末尾的d，重启mysql，然后看报错
# 截取片段
2026-08-26T01:59:18.773132Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 26.7.0) starting as process 1
2026-08-26T01:59:18.773150Z 0 [System] [MY-015603] [Server] MySQL Server has access to 2 logical CPUs.
2026-08-26T01:59:18.773166Z 0 [System] [MY-015603] [Server] MySQL Server has access to 3900534784 bytes of physical memory.
2026-08-26T01:59:18.777258Z 0 [ERROR] [MY-010073] [Server] The server_uuid stored in auto.cnf file is not a valid UUID.
2026-08-26T01:59:18.777284Z 0 [ERROR] [MY-010076] [Server] Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID.
2026-08-26T01:59:18.777291Z 0 [ERROR] [MY-010119] [Server] Aborting
2026-08-26T01:59:18.778837Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 26.7.0)  MySQL Community Server - GPL.
2026-08-26T01:59:18.778855Z 0 [System] [MY-015016] [Server] MySQL Server - end.

# 报错无效UUID，改回来就行

报的就是 UUID 无效，把那个 d 加回去就好了。

二、二进制日志

1. 文件和位置

二进制日志记录了数据库、表、数据的变更。

mysql> show variables like "%log_bin%";
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |# 日志开启
| log_bin_basename                | /var/lib/mysql/binlog       |# 位置
| log_bin_index                   | /var/lib/mysql/binlog.index |# 二进制索引
| log_bin_trust_function_creators | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
5 rows in set (0.009 sec)


root@VM-4-16-ubuntu:/var/lib/mysql# ll
-rw-r-----  1 mysql mysql      181 Aug 26 12:42  binlog.000001
-rw-r-----  1 mysql mysql      492 Aug 26 13:05  binlog.000002

# 索引记录了当前 mysql 关联的日志文件
-rw-r-----  1 mysql mysql       32 Aug 26 12:42  binlog.index

# 日志文件从 1 开始自增，会自动覆盖旧的日志

root@VM-4-16-ubuntu:/var/lib/mysql#  cat binlog.index
./binlog.000001
./binlog.000002

2. 日志格式

一共三种格式：

STATEMENT 记 SQL 语句、ROW 记每一行数据变更、MIXED 混合两者

# 查看二进制日志格式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   | # 记录每一行的数据变更
+---------------+-------+
1 row in set (0.002 sec)

create table score (
    id int auto_increment primary key,
    name varchar(20),
    math int,
    english int,
    chinese int
);

insert into score values
(1, 'Tom', 65, 88, 100),
(2, 'Rose', 99, 66, 90),
(3, 'Jack', 89, 98, 100);
# 执行 update
mysql> update score set math = math + 1;
Query OK, 3 rows affected (0.004 sec)
Rows matched: 3  Changed: 3  Warnings: 0

# 查看二进制日志002（看数字最大的）
# 二进制直接看是看不了的需要客户端工具 mysqlbinlog

mysqlbinlog 的常用参数如下：

最后一个是 -vv 两个 v，不是 w。

Row 格式

Row 格式记录的是库、表、数据的变更。

# 主要看最后一段，因为update 是刚才执行的
# 一般配合 -v，看执行变更的sql
mysql> select * from score;
+----+------+------+---------+---------+
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   66 |      88 |     100 |
|  2 | Rose |  100 |      66 |      90 |
|  3 | Jack |   90 |      98 |     100 |
+----+------+------+---------+---------+


mysqlbinlog -v binlog.000002

'/*!*/;
### UPDATE `itcast`.`score` # 每条update对应了一条记录的变化
### WHERE
###   @1=1     # 指的是第一个字段 id=1
###   @2='Tom' # 指的是第二个字段 name
###   @3=65
###   @4=88
###   @5=100
### SET
###   @1=1
###   @2='Tom'
###   @3=66
###   @4=88
###   @5=100
### UPDATE `itcast`.`score`
### WHERE
###   @1=2
###   @2='Rose'
###   @3=99
###   @4=66
###   @5=90
### SET
###   @1=2
###   @2='Rose'
###   @3=100
###   @4=66
###   @5=90
### UPDATE `itcast`.`score`
### WHERE
###   @1=3
###   @2='Jack'
###   @3=89
###   @4=98
###   @5=100
### SET
###   @1=3
###   @2='Jack'
###   @3=90
###   @4=98
###   @5=100
# at 1730
#260826 18:27:21 server id 1  end_log_pos 1761 CRC32 0x37b8b4fd 	Xid = 47
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@VM-4-16-ubuntu:/var/lib/mysql#

能看到一条 update 动了几行，就展开成几条 ### UPDATE 记录，@1、@2 是按位置对应的字段。

STATEMENT 格式

STATEMENT 格式记录的是执行过的 SQL。

# 设置日志格式
# ubuntu安装mysql的配置文件
root@VM-4-16-ubuntu:/var/lib/mysql# ll /etc/mysql
total 32
drwxr-xr-x   4 root root  4096 Aug 26 12:42 ./
drwxr-xr-x 116 root root 12288 Aug 26 18:43 ../
drwxr-xr-x   2 root root  4096 Aug 26 12:41 conf.d/
lrwxrwxrwx   1 root root    24 Jun  3 21:10 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--   1 root root  1499 Jun  3 21:10 my.cnf.fallback
-rw-r--r--   1 root root  1527 Jun  3 21:10 mysql.cnf.dpkg-new
drwxr-xr-x   2 root root  4096 Aug 26 12:42 mysql.conf.d/
# 由于my.cnf加载片配置目录的机制 !includedir
# 可以直接在 conf.d/ 新建cnf文件

cd mysql.conf.d/
vim binlog_statement.cnf

[mysqld]
binlog_format = STATEMENT

systemctl restart mysql

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.009 sec)

# 配置成功，生成了003，格式改了不会写入原来的了
root@VM-4-16-ubuntu:/var/lib/mysql# ll
total 100452
drwxr-x---  8 mysql mysql     4096 Aug 26 18:52  ./
drwxr-xr-x 51 root  root      4096 Aug 26 12:42  ../
-rw-r-----  1 mysql mysql       56 Aug 26 12:42  auto.cnf
-rw-r-----  1 mysql mysql      181 Aug 26 12:42  binlog.000001
-rw-r-----  1 mysql mysql     1784 Aug 26 18:52  binlog.000002
-rw-r-----  1 mysql mysql      198 Aug 26 18:52  binlog.000003

改完格式，新的 binlog 从 003 开始写，原来那两个不动。

use itcast;
update score set math = math + 1;

# statement 本身记录的就是sql语句不需要 -v
mysqlbinlog binlog.000003

# 日志截取

/*!*/;
# at 372
#260826 18:55:11 server id 1  end_log_pos 496 CRC32 0xd727ada0 	Query	thread_id=10	exec_time=0	error_code=0
use `itcast`/*!*/;
SET TIMESTAMP=1787741711/*!*/;
update score set math = math + 1 # 这里直接记录了sql语句
/*!*/;
# at 496
#260826 18:55:11 server id 1  end_log_pos 527 CRC32 0xf9618466 	Xid = 13
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


# 创建表
mysql> create table binlogtest(id int, name varchar(10));

# 日志截取
# sql记录的位置在 /*!*/; 的上面
COMMIT/*!*/;
# at 527
#260826 18:58:55 server id 1  end_log_pos 604 CRC32 0xbdfa3975 	GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1787741935353397	immediate_commit_timestamp=1787741935353397	transaction_length=218
# original_commit_timestamp=1787741935353397 (2026-08-26 18:58:55.353397 CST)
# immediate_commit_timestamp=1787741935353397 (2026-08-26 18:58:55.353397 CST)
/*!80001 SET @@session.original_commit_timestamp=1787741935353397*//*!*/;
/*!80014 SET @@session.original_server_version=90701*//*!*/;
/*!80014 SET @@session.immediate_server_version=90701*//*!*/;
SET @@SESSION.GTID_NEXT= '907cd40a-a108-11f1-9f2b-5254008757a3:8'/*!*/;
# at 604
#260826 18:58:55 server id 1  end_log_pos 745 CRC32 0x79f66318 	Query	thread_id=11	exec_time=0	error_code=0	Xid = 23
SET TIMESTAMP=1787741935/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table binlogtest(id int, name varchar(10))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3. 删除日志

在 mysql 环境里执行，常用的删日志指令就这三条：

reset master、purge master logs to、purge master logs before 三个指令的区别

# purge 译为净化
purge master logs to "binlog.000002";

# 高版本语法会变
mysql> purge binary logs to 'binlog.000002';
Query OK, 0 rows affected (0.006 sec)

# ll查看000001，删除了

purge binary logs before "2026-08-25 00:00:00";

自动删除配置

# expire 译为期满，mysql默认单位是秒(s)
mysql> SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |# 默认为30天
+----------------------------+---------+
1 row in set (0.004 sec)

# 想调整直接在配置文件设置参数，方法和修改日志格式是一样的

三、通用查询日志

顾名思义，MySQL 服务器收到的每一条客户端请求它都记：连接、断开，以及所有执行的 SQL 语句（增删改查加 DDL）。

这份日志文件会非常大，用不上就关掉。

需要主动开启（也可以顺手改日志路径）：

mysql> show variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | OFF                               |# 默认关闭
| general_log_file | /var/lib/mysql/VM-4-16-ubuntu.log |
+------------------+-----------------------------------+
2 rows in set (0.002 sec)

root@VM-4-16-ubuntu:/etc/mysql/conf.d# vim generallogtest.cnf

[mysqld]
general_log=ON

systemctl restart mysql

mysql> show variables like '%general_log%';
+------------------+-----------------------------------+
| Variable_name    | Value                             |
+------------------+-----------------------------------+
| general_log      | ON                                |
| general_log_file | /var/lib/mysql/VM-4-16-ubuntu.log |
+------------------+-----------------------------------+
2 rows in set (0.002 sec)

# 查看文件
root@VM-4-16-ubuntu:/var/lib/mysql# ll

-rw-r-----  1 mysql mysql     1130 Aug 26 19:20  VM-4-16-ubuntu.log
-rw-r-----  1 mysql mysql        8 Aug 26 19:18  VM-4-16-ubuntu.pid

追踪日志

tail -f /var/lib/mysql/VM-4-16-ubuntu.log

# 另一会话执行sql
mysql> show databases;
mysql> use itcast;
mysql> select * from score;

# 截取部分日志

2026-08-26T11:18:13.909521Z	    9 Connect	root@localhost on  using Socket
2026-08-26T11:18:13.909763Z	    9 Query	show variables like '%general_log%'
2026-08-26T11:18:49.761244Z	    9 Query	show variables like '%general_log%'
2026-08-26T11:20:09.141649Z	    9 Quit	
2026-08-26T11:22:22.330086Z	   10 Connect	root@localhost on  using Socket
2026-08-26T11:22:22.330259Z	   10 Query	select @@version_comment limit 1
2026-08-26T11:22:36.966274Z	   10 Query	show databases
2026-08-26T11:22:41.822850Z	   10 Query	SELECT DATABASE()
2026-08-26T11:22:41.823137Z	   10 Init DB	itcast
2026-08-26T11:22:41.824084Z	   10 Query	show databases
2026-08-26T11:22:41.824802Z	   10 Query	show tables
2026-08-26T11:22:41.826488Z	   10 Field List	binlogtest 
2026-08-26T11:22:41.832107Z	   10 Field List	score 
2026-08-26T11:22:49.884760Z	   10 Query	select * from score
2026-08-26T11:22:49.884760Z	   10 Query	select * from score
# 还有执行失败的错误记录
2026-08-26T11:27:06.821959Z	   10 Query	update score set engilsh = english + 1
2026-08-26T11:27:32.751700Z	   10 Query	update score set engilsh = english + 1 where id = 1
2026-08-26T11:28:07.562770Z	   10 Query	update score set engilsh = 60 where id = 1
2026-08-26T11:28:29.558681Z	   10 Query	update score set english = 60 where id = 1

写错的 SQL 也照样记进去，所以这份日志拿来排"到底是谁执行的"特别管用，代价就是体积。

四、慢查询日志

1. 配置并准备数据

慢查询日志在索引那一篇的性能分析里提过，顾名思义就是记录执行慢的 SQL（超过预设时间）。

慢查询日志记的是超过 long_query_time 且扫描行数达到 min_examined_row_limit 的语句

mysql> show variables like '%slow_query%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | OFF                                    |
| slow_query_log_file | /var/lib/mysql/VM-4-16-ubuntu-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.004 sec)


# 配置开启状态
root@VM-4-16-ubuntu:/etc/mysql/conf.d# vim slowlogtest.cnf

[mysqld]
slow_query_log=ON # 这里也可以写1
long_query_time=2
systemctl restart mysql


# 查看配置是否成功
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.002 sec)

mysql> show variables like '%slow_query%';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | ON                                     |
| slow_query_log_file | /var/lib/mysql/VM-4-16-ubuntu-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.003 sec)


# 文件生成了
root@VM-4-16-ubuntu:/var/lib/mysql# ll

-rw-r-----  1 mysql mysql      180 Aug 26 20:45  VM-4-16-ubuntu-slow.log

默认情况下，管理语句和不走索引的查询是不记的，想记就得再加两个参数：

log_slow_admin_statements 和 log_queries_not_using_indexes 两个开关

数据导入

把 .sql 文件传到服务器的 /root/ 下，再挪到 MySQL 允许导入的目录：

source 逐条执行 SQL、LOAD DATA INFILE 批量导入数据的对比

# mysql只允许指定目录导入数据
mv /root/load_user_100w_sort.sql /var/lib/mysql-files/


# 建表
CREATE TABLE `tb_user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(50) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `birthday` DATE DEFAULT NULL,
  `sex` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

# 导入数据
LOAD DATA INFILE '/var/lib/mysql-files/load_user_100w_sort.sql'
INTO TABLE tb_user
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, username, password, name, birthday, sex);

# 输出示例
mysql> LOAD DATA INFILE '/var/lib/mysql-files/load_user_100w_sort.sql'
    -> INTO TABLE tb_user
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (id, username, password, name, birthday, sex);

Query OK, 1000000 rows affected (12.807 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

2. 怎么导入 txt 备份

先说清那个文件长什么样：load_user_100w_sort.sql 虽然后缀是 .sql，但它其实就是个纯文本文件，内容是一行一条数据，字段之间用逗号隔开：

1,user1,123456,张三,1990-01-01,男
2,user2,123456,李四,1991-02-02,女
3,user3,123456,王五,1992-03-03,男

看懂这个，下面那段语法就全懂了：

LOAD DATA INFILE '/var/lib/mysql-files/load_user_100w_sort.sql'  -- ① 读哪个文件
INTO TABLE tb_user                                               -- ② 导入到哪张表
FIELDS TERMINATED BY ','                                          -- ③ 字段之间用逗号分隔
LINES TERMINATED BY '\n'                                          -- ④ 一行数据用换行符结尾
(id, username, password, name, birthday, sex);                    -- ⑤ 文件里的6列，依次对应表的哪些字段

① 文件路径
② 目标表
③ 对应文件里"列和列之间"的分隔符（就是那个逗号）
④ 对应文件里"一条记录"的结束符（\n 就是换行）
⑤ 文件第 1 列给 id，第 2 列给 username，依次一一对应

整句话的意思就是：把这个文件里的每一行按逗号拆开，塞进 tb_user 表对应的列里。

那为什么必须先 mv 到 /var/lib/mysql-files/？因为 MySQL 有个安全变量 secure_file_priv 限制着导入导出目录，LOAD DATA INFILE 只认这个目录下的文件，放别处会直接报错。顺手看一眼现在的值：

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.001 sec)

导出和导入的语法对着看：

# 导出
SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
FROM tb_user;
# 导入
load data infile '文件路径'
into table 表名
fields terminated by '分隔符'
lines terminated by '换行符'
(列1, 列2, 列3...);


mysqldump -uroot -p --single-transaction --quick -T /var/lib/mysql/tb_user tb_user

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| binlogtest       |
| score            |
| tb_user          |
+------------------+


root@VM-4-16-ubuntu:/var/lib/mysql-files# ll
total 113740
drwxrwx---  2 mysql mysql     4096 Aug 26 21:31 ./
drwxr-xr-x 51 root  root      4096 Aug 26 12:42 ../
-rw-r--r--  1 root  root      1373 Aug 26 21:31 binlogtest.sql
-rw-r-----  1 mysql mysql        0 Aug 26 21:31 binlogtest.txt
-rw-r--r--  1 root  root      2920 Aug 26 21:05 itcast.sql
-rw-r--r--  1 root  root  57650380 Aug 26 21:12 load_user_100w_sort.sql
-rw-r--r--  1 root  root      1495 Aug 26 21:31 score.sql
-rw-r-----  1 mysql mysql       50 Aug 26 21:31 score.txt
-rw-r--r--  1 root  root      1571 Aug 26 21:31 tb_user.sql
-rw-r-----  1 mysql mysql 58777792 Aug 26 21:31 tb_user.txt

这里的 mysqldump -T 是把表结构和数据分开导出的：.sql 里是建表语句，.txt 里是纯数据。所以导入的时候要先执行 sql 文件把表建出来，再 load data 灌数据。如果只是一个单纯的 sql 文件，导入就是一句句执行 SQL，慢得多。

3. 日志输出演示

# 查询耗费性能sql
mysql> desc tb_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(50) | NO   | UNI | NULL    |                |
| password | varchar(50) | NO   |     | NULL    |                |
| name     | varchar(20) | NO   |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| sex      | char(1)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+


# 看一下索引有主键和username索引
mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |      996555 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | unique_user_username |            1 | username    | A         |      980210 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.001 sec)


# 查询
mysql> select * from tb_user order by name,username limit 900000,10;
+--------+------------------+------------+------------+------------+------+
| id     | username         | password   | name       | birthday   | sex  |
+--------+------------------+------------+------------+------------+------+
|  62137 | XkMNihMfiM62137  | XkMNihMfiM | XkMNihMfiM | 2020-04-03 | 1    |
| 490622 | XkmnOsJCOG490622 | XkmnOsJCOG | XkmnOsJCOG | 2020-10-06 | 2    |
| 706116 | XKmNRzWSIW706116 | XKmNRzWSIW | XKmNRzWSIW | 2020-04-14 | 0    |
| 154807 | xKMNxNiNgb154807 | xKMNxNiNgb | xKMNxNiNgb | 2020-01-15 | 1    |
| 343445 | xkMOfeIcjU343445 | xkMOfeIcjU | xkMOfeIcjU | 2020-04-22 | 2    |
|  26101 | XKMohOMpoJ26101  | XKMohOMpoJ | XKMohOMpoJ | 2020-05-13 | 1    |
| 915714 | xKMoKxIeCZ915714 | xKMoKxIeCZ | xKMoKxIeCZ | 2020-04-08 | 0    |
| 115183 | XKmoPEeQJA115183 | XKmoPEeQJA | XKmoPEeQJA | 2020-09-21 | 1    |
| 304261 | XkmOZVykwp304261 | XkmOZVykwp | XkmOZVykwp | 2020-09-01 | 1    |
| 180727 | XkmPPqjeZS180727 | XkmPPqjeZS | XkmPPqjeZS | 2020-03-17 | 1    |
+--------+------------------+------------+------------+------------+------+
10 rows in set (2.501 sec)

# Time: 2026-08-26T14:18:19.968486Z
# User@Host: root[root] @ localhost []  Id:    13
# Query_time: 2.453699  Lock_time: 0.000004 Rows_sent: 10  Rows_examined: 1900010
SET timestamp=1787753897;
select * from tb_user order by name,username limit 900000,10;
# 这里记录了慢的sql语句

排序加上 limit 900000,10 要扫 190 万行，自然就超过我设的 2 秒了，这条 SQL 原封不动进了慢查询日志。

最后这个是了解性质的，慢查询日志还能这么用：

#MySQL #日志 #binlog #慢查询