MySQL日志

一、错误日志

1.介绍

错误日志主要用于记录MySQL生命周期内的错误、状态和警告等信息;

2.配置

修改配置文件,后重启MySQL服务;

log_error=目录/文件名
错误日志默认开启,存放于数据路径下,默认文件名称为主机名.err
错误日志必须生成于已有路径下;
错误日志的生成路径的属主必须是Mysql用户(Mysql用户对该路径有操作权限)
[root@db01 /data/mysql/3306]# vim /data/mysql/3306/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
[mysql]
socket=/tmp/mysql.sock

[root@db01 /]# ll /data/mysql/3306/log/ -d
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/
[root@db01 /]# ll /data/mysql/3306/log/ 
total 4
-rw-r----- 1 mysql mysql 3248 Aug 26 13:33 error.log

3.查看日志

less 错误日志
grep 'ERROR' 错误日志
[root@db01 /]# grep '\[Warning\]' /data/mysql/3306/log/error.log 
2019-08-26T05:33:23.503771Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-26T05:33:24.520895Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
#模拟错误
[root@db01 /]# vim /data/mysql/3306/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
fghjkllllllll
[mysql]
socket=/tmp/mysql.sock

[root@db01 /]# systemctl restart mysqld
[root@db01 /]# grep '\[ERROR\]' /data/mysql/3306/log/error.log 
2019-08-26T05:39:31.085947Z 0 [ERROR] unknown option '--fghjkllllllll'
2019-08-26T05:39:31.085964Z 0 [ERROR] Aborting

二、二进制日志

二进制日志是配合数据库备份恢复数据的一个手段,二进制日志文件需要备份

1.概述

  • 功能:数据恢复,主从复制
  • 内容:以二进制格式,记录数据库所有变化的操作(DDL,DCL,DML)

2.配置

配置文件必要参数
server_id=数字 #指定服务ID(0 < ID < 65535)
log_bin=文件前缀 #指定二进制日志文件路径
binlog_format=row #指定二进制日志文件格式为行格式
[root@db01 /]# vim /data/mysql/3306/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
[mysql]
socket=/tmp/mysql.sock
创建二进制日志文件目录
mkdir 目录 #创建二进制日志文件目录
chown -R 属主.属组 目录 #修改目录所属权限
[root@db01 /]# ll -d /data/mysql/3306/log/
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/
重启MySQL服务
systemctl restart mysqld #重启MySQL服务
[root@db01 /]# ll -d /data/mysql/3306/log/
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/
[root@db01 /]# ll /data/mysql/3306/log/
total 20
-rw-r----- 1 mysql mysql 17342 Aug 26 13:40 error.log
[root@db01 /]# systemctl restart mysqld
[root@db01 /]# ll /data/mysql/3306/log/
total 36
-rw-r----- 1 mysql mysql   154 Aug 26 13:47 bin-log.000001
-rw-r----- 1 mysql mysql    36 Aug 26 13:47 bin-log.index
-rw-r----- 1 mysql mysql 24883 Aug 26 13:47 error.log

3.二进制日志格式

对于DDL和DCL的操作,以statement(语句)方式直接记录SQL
对于DML(insert,update,delete)操作,记录的是已经提交的事务
  • RBR(row):记录数据行的变化(推荐使用:逻辑严谨,数据高度一致)
  • SBR(statement):记录SQL语句
  • MBR(mixed):混合模式

4.二进制日志的基本查看

以事件(Event)作为记录的最小单元(两个at之间记录的是一个事件)

系统模式

  • mysqlbinlog 二进制日志 #查看二进制文件
[root@db01 /]# mysqlbinlog /data/mysql/3306/log/bin-log.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190826 13:47:41 server id 6  end_log_pos 123 CRC32 0x09bd862c  Start: binlog v 4, server v 5.7.26-log created 190826 13:47:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
fXJjXQ8GAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB9cmNdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASyGvQk=
'/*!*/;
# at 123
#190826 13:47:41 server id 6  end_log_pos 154 CRC32 0x31b5d32b  Previous-GTIDs
# [empty]
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*/;
参数
--base64-output=decode-rows #解码日志信息
-vvv #示日志详细信息
-d 数据库名 #取指定数据库的日志信息
--start-position=起始序号 #志截取起始位置
--stop-position=结束序号 #截取结束位置
--help #看mysqlbinlog参数帮助
[root@db01 /]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/3306/log/bin-log.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190826 13:47:41 server id 6  end_log_pos 123 CRC32 0x09bd862c  Start: binlog v 4, server v 5.7.26-log created 190826 13:47:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#190826 13:47:41 server id 6  end_log_pos 154 CRC32 0x31b5d32b  Previous-GTIDs
# [empty]
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*/;

数据库模式

  • show binary logs; #查看二进制文件名称
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |       154 |
+----------------+-----------+
1 row in set (0.00 sec)
  • flush logs; #刷新(切割)二进制文件
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |       199 |
| bin-log.000002 |       154 |
+----------------+-----------+
2 rows in set (0.00 sec)
  • show master status #查看目前使用的二进制文件
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 |      154 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • show binlog events in '日志文件名' #查看二进制日志事件信息
mysql> show binlog events in 'bin-log.000002';
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| bin-log.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin-log.000002 | 123 | Previous_gtids |         6 |         154 |                                       |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

5.日志截取与数据恢复

step 0 模拟数据丢失

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| binlog             |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| stu              |
| test             |
| time             |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from time;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2019-08-26 14:46:21 |
|    2 | 2019-08-26 14:46:21 |
|    3 | 2019-08-26 14:46:21 |
|    4 | 2019-08-26 14:46:21 |
|    5 | 2019-08-26 14:46:38 |
|    6 | 2019-08-26 14:46:38 |
+------+---------------------+
6 rows in set (0.00 sec)
mysql> drop database binlog;
Query OK, 3 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.00 sec)

step 1 查看当前使用的二进制文件

show master status;
mysql> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin-log.000001 |       199 |
| bin-log.000002 |      1711 |
+----------------+-----------+
2 rows in set (0.00 sec)

step 2 查看日志

show binlog events in '日志文件'
mysql> show binlog events in 'bin-log.000002';
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name       | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                 |
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| bin-log.000002 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                                                                                                                |
| bin-log.000002 |  123 | Previous_gtids |         6 |         154 |                                                                                                                                                      |
| bin-log.000002 |  154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 |  219 | Query          |         6 |         319 | create database binlog                                                                                                                               |
| bin-log.000002 |  319 | Anonymous_Gtid |         6 |         384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 |  384 | Query          |         6 |         596 | use `binlog`; create table stu (id int primary key not null auto_increment,name char(10) not null,age tinyint not null default '18',telnum char(13)) |
| bin-log.000002 |  596 | Anonymous_Gtid |         6 |         661 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 |  661 | Query          |         6 |         764 | use `binlog`; create table test(id int)                                                                                                              |
| bin-log.000002 |  764 | Anonymous_Gtid |         6 |         829 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 |  829 | Query          |         6 |         960 | use `binlog`; create table time(id int,time datetime default now())                                                                                  |
| bin-log.000002 |  960 | Anonymous_Gtid |         6 |        1025 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 | 1025 | Query          |         6 |        1107 | BEGIN                                                                                                                                                |
| bin-log.000002 | 1107 | Table_map      |         6 |        1158 | table_id: 108 (binlog.time)                                                                                                                          |
| bin-log.000002 | 1158 | Write_rows     |         6 |        1233 | table_id: 108 flags: STMT_END_F                                                                                                                      |
| bin-log.000002 | 1233 | Xid            |         6 |        1264 | COMMIT /* xid=26 */                                                                                                                                  |
| bin-log.000002 | 1264 | Anonymous_Gtid |         6 |        1329 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 | 1329 | Query          |         6 |        1411 | BEGIN                                                                                                                                                |
| bin-log.000002 | 1411 | Table_map      |         6 |        1462 | table_id: 108 (binlog.time)                                                                                                                          |
| bin-log.000002 | 1462 | Write_rows     |         6 |        1517 | table_id: 108 flags: STMT_END_F                                                                                                                      |
| bin-log.000002 | 1517 | Xid            |         6 |        1548 | COMMIT /* xid=27 */                                                                                                                                  |
| bin-log.000002 | 1548 | Anonymous_Gtid |         6 |        1613 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                 |
| bin-log.000002 | 1613 | Query          |         6 |        1711 | drop database binlog                                                                                                                                 |
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

step 3 截取日志

mysqlbinlog --start-position=起始序号 --stop-position=结束序号 '日志文件' >备份文件.sql
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1613 '/data/mysql/3306/log/bin-log.000002' >/tmp/backup.sql
[root@db01 ~]# ll /tmp/
total 4767
-rw-r--r-- 1 root  root      4761 Aug 26 15:05 backup.sql
srwxrwxrwx 1 mysql mysql        0 Aug 26 13:47 mysql.sock
-rw------- 1 mysql mysql        6 Aug 26 13:47 mysql.sock.lock

step 4 数据恢复

mysql -u用户名 -p密码 < 备份文件.sql
[root@db01 ~]# mysql -uroot -p123 </tmp/backup.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| binlog             |
| mysql              |
| performance_schema |
| sys                |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> use binlog;
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> select * from time;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2019-08-26 14:46:21 |
|    2 | 2019-08-26 14:46:21 |
|    3 | 2019-08-26 14:46:21 |
|    4 | 2019-08-26 14:46:21 |
|    5 | 2019-08-26 14:46:38 |
|    6 | 2019-08-26 14:46:38 |
+------+---------------------+
6 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| stu              |
| test             |
| time             |
+------------------+
3 rows in set (0.00 sec)

6.日志滚动

  • flush logs #手动进行日志滚动
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 |     3089 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
[root@db01 ~]# mysql -uroot -p123 -e 'flush logs;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000003 |      154 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
  • 重启数据库,可以触发数据库二进制日志文件滚动;
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000003 |      154 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000004 |      154 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
  • 超过日志设定的最大尺寸(默认1GB)
select @@max_binlog_size; #查看日志最大尺寸
[root@db01 ~]# mysql -uroot -p123 -e 'select @@max_binlog_size/1024/1024/1024;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+
| @@max_binlog_size/1024/1024/1024 |
+----------------------------------+
|                   1.000000000000 |
+----------------------------------+
  • 某些备份参数
每次全备之前,可以进行手动刷新二进制日志文件

7.清除日志

  • 手动删除
reset master #清除所有二进制日志,并重置二进制文件编号为1;
purge binary logs to '日志文件' #删除起始日志至该日志之间的日志文件(不包含指定日志)
purge binary logs before '时间' #删除指定时间之前的日志文件
mysql> help purge;
Name: 'PURGE BINARY LOGS'
......
URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
  • 自动删除
select @@expire_logs_days; #查询日志的过期时间(0为永久不过期)
一般最少设置为全备周期+1天;
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

三、慢日志(Slow Log)

1.概述

慢日志:记录MySQL运行期间执行较慢的语句

2. 配置(编辑配置文件)

慢日志默认没有开启

[root@db01 /data/mysql/3306]# vim my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/log/slow.log
long_query_time=0.1
log_queries_not_using_indexes=ON
[mysql]
socket=/tmp/mysql.sock
slow_query_log=ON #开启MySQL慢日志
slow_query_log_file=文件位置 #指定日志存放位置(默认存放在MySQL数据路径下)
long_query_time=时间(单位:秒;默认时间为10s)
log_queries_not_using_indexes=ON #开启慢日志记录非索引查询语句
mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /data/mysql/data/db03-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.16 sec)
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
mysql> show variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
2 rows in set (0.00 sec)

3. 分析日志文件

mysqldumpslow 慢日志分析工具

[root@db01 /data/mysql/3306/log]# mysqldumpslow slow.log 

Reading mysql slow query log from slow.log
Count: 1  Time=1.32s (1s)  Lock=0.00s (0s)  Rows=1000000.0 (1000000), root[root]@localhost
  select * from t100w

Count: 2  Time=0.34s (0s)  Lock=0.00s (0s)  Rows=515.0 (1030), root[root]@localhost
  select * from t100w where k1='S'

Count: 4  Time=0.30s (1s)  Lock=0.00s (0s)  Rows=525.0 (2100), root[root]@localhost
  select id,num from t100w where k1='S' order by num limit N

Count: 8  Time=0.29s (2s)  Lock=0.00s (0s)  Rows=91.0 (728), root[root]@localhost
  select k1,k2 from t100w where k1='S' and k2 like 'S'

Count: 1  Time=0.28s (0s)  Lock=0.00s (0s)  Rows=1030.0 (1030), root[root]@localhost
  select k1,k2 from t100w where k1='S

-s 排序规则 排序(自动合并重复执行语句)

c #按SQL语句执行次数排序
[root@db01 /data/mysql/3306/log]# mysqldumpslow -s c slow.log 

Reading mysql slow query log from slow.log
Count: 8  Time=0.29s (2s)  Lock=0.00s (0s)  Rows=91.0 (728), root[root]@localhost
  select k1,k2 from t100w where k1='S' and k2 like 'S'

Count: 4  Time=0.30s (1s)  Lock=0.00s (0s)  Rows=525.0 (2100), root[root]@localhost
  select id,num from t100w where k1='S' order by num limit N

Count: 2  Time=0.34s (0s)  Lock=0.00s (0s)  Rows=515.0 (1030), root[root]@localhost
  select * from t100w where k1='S'

Count: 1  Time=0.28s (0s)  Lock=0.00s (0s)  Rows=1030.0 (1030), root[root]@localhost
  select k1,k2 from t100w where k1='S'

Count: 1  Time=1.32s (1s)  Lock=0.00s (0s)  Rows=1000000.0 (1000000), root[root]@localhost
  select * from t100w

-t 数字 显示前指定数目的日志记录

[root@db01 /data/mysql/3306/log]# mysqldumpslow -s c -t 3 slow.log 

Reading mysql slow query log from slow.log
Count: 8  Time=0.29s (2s)  Lock=0.00s (0s)  Rows=91.0 (728), root[root]@localhost
  select k1,k2 from t100w where k1='S' and k2 like 'S'

Count: 4  Time=0.30s (1s)  Lock=0.00s (0s)  Rows=525.0 (2100), root[root]@localhost
  select id,num from t100w where k1='S' order by num limit N

Count: 2  Time=0.34s (0s)  Lock=0.00s (0s)  Rows=515.0 (1030), root[root]@localhost
  select * from t100w where k1='S'

附:思维导图