记录下mysql分页查询
创建测试表t1_part，id为自增主键，然后插入5900W条测试数据
CREATE TABLE t1_part (
id int NOT NULL AUTO_INCREMENT,
a varchar(30) DEFAULT NULL,
b varchar(30) DEFAULT NULL,
c varchar(30) DEFAULT NULL,
d timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10000001) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20000001) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (30000001) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (40000001) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (50000001) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (60000001) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (70000001) ENGINE = InnoDB);
使用limit随机分页查看10条数据，可以看到随着分页深度增加需要的时间也增加
mysql> select a,b,c,d from t1_part limit 0,10;
+--------+-------------------------+------------------------+---------------------+
| a | b | c | d |
+--------+-------------------------+------------------------+---------------------+
| name1 | bcdefghijklmnopqrstuvwx | cdefghijklmnopqrstuvwx | 2023-06-16 16:34:40 |
| name2 | bcdefghijklm | fghijklm | 2023-06-16 16:34:40 |
| name3 | bcdefghijklmno | efghijklmno | 2023-06-16 16:34:40 |
| name4 | bcdefghijklmnopqrstuv | fghijklmnopqrstuv | 2023-06-16 16:34:40 |
| name5 | bcdefghijklmnopqr | jklmnopqr | 2023-06-16 16:34:40 |
| name6 | bcdefghijklmnopqr | fghijklmnopqr | 2023-06-16 16:34:40 |
| name7 | bcdefghijklmnopqrst | mnopqrst | 2023-06-16 16:34:40 |
| name8 | bcdefghijklmnop | defghijklmnop | 2023-06-16 16:34:40 |
| name9 | bcdefghijklmnopqrs | defghijklmnopqrs | 2023-06-16 16:34:40 |
| name10 | bcdefghijklmnopqrstuvwx | lmnopqrstuvwx | 2023-06-16 16:34:40 |
+--------+-------------------------+------------------------+---------------------+
10 rows in set (0.00 sec)
mysql> select a,b,c,d from t1_part limit 10000,10;
+-----------+-------------------------+---------------------+---------------------+
| a | b | c | d |
+-----------+-------------------------+---------------------+---------------------+
| name10001 | bcdefghijklmnopqrstu | lmnopqrstu | 2023-06-16 16:34:40 |
| name10002 | bcdefghijklmnopqrstuvwx | fghijklmnopqrstuvwx | 2023-06-16 16:34:40 |
| name10003 | bcdefghijklmnopqrstuvwx | jklmnopqrstuvwx | 2023-06-16 16:34:40 |
| name10004 | bcdefghijklmnopq | jklmnopq | 2023-06-16 16:34:40 |
| name10005 | bcdefghijklmno | lmno | 2023-06-16 16:34:40 |
| name10006 | bcdefghijklmnopq | fghijklmnopq | 2023-06-16 16:34:40 |
| name10007 | bcdefghijklmn | lmn | 2023-06-16 16:34:40 |
| name10008 | bcdefghijklmnop | ijklmnop | 2023-06-16 16:34:40 |
| name10009 | bcdefghijklm | ijklm | 2023-06-16 16:34:40 |
| name10010 | bcdefghijklmnopqr | hijklmnopqr | 2023-06-16 16:34:40 |
+-----------+-------------------------+---------------------+---------------------+
10 rows in set (0.00 sec)
mysql> select a,b,c,d from t1_part limit 1000000,10;
+-------------+--------------------------+-------------------------+---------------------+
| a | b | c | d |
+-------------+--------------------------+-------------------------+---------------------+
| name1000001 | bcdefghijklmnopqrstuv | defghijklmnopqrstuv | 2023-06-16 16:34:54 |
| name1000002 | bcdefghijklmnopqrstuvw | ghijklmnopqrstuvw | 2023-06-16 16:34:54 |
| name1000003 | bcdefghijklmnopqrstuvwxy | cdefghijklmnopqrstuvwxy | 2023-06-16 16:34:54 |
| name1000004 | bcdefghijklmnopq | jklmnopq | 2023-06-16 16:34:54 |
| name1000005 | bcdefghijklmnopqr | hijklmnopqr | 2023-06-16 16:34:54 |
| name1000006 | bcdefghijklmnopqrs | hijklmnopqrs | 2023-06-16 16:34:54 |
| name1000007 | bcdefghijklmno | defghijklmno | 2023-06-16 16:34:54 |
| name1000008 | bcdefghijklmnopqrs | cdefghijklmnopqrs | 2023-06-16 16:34:54 |
| name1000009 | bcdefghijklmnop | efghijklmnop | 2023-06-16 16:34:54 |
| name1000010 | bcdefghijklmnopqr | cdefghijklmnopqr | 2023-06-16 16:34:54 |
+-------------+--------------------------+-------------------------+---------------------+
10 rows in set (0.55 sec)
mysql> select a,b,c,d from t1_part limit 10000000,10;
+--------------+-----------------------+---------------------+---------------------+
| a | b | c | d |
+--------------+-----------------------+---------------------+---------------------+
| name10000001 | bcdefghijklmnopq | ghijklmnopq | 2023-06-16 16:37:14 |
| name10000002 | bcdefghijklmnopqr | fghijklmnopqr | 2023-06-16 16:37:14 |
| name10000003 | bcdefghijklmnopq | mnopq | 2023-06-16 16:37:14 |
| name10000004 | bcdefghijklmn | cdefghijklmn | 2023-06-16 16:37:14 |
| name10000005 | bcdefghijklmnopqr | hijklmnopqr | 2023-06-16 16:37:14 |
| name10000006 | bcdefghijklmnopqrstuv | lmnopqrstuv | 2023-06-16 16:37:14 |
| name10000007 | bcdefghijklmn | jklmn | 2023-06-16 16:37:14 |
| name10000008 | bcdefghijklmnopqrs | ijklmnopqrs | 2023-06-16 16:37:14 |
| name10000009 | bcdefghijklmnopqrstu | fghijklmnopqrstu | 2023-06-16 16:37:14 |
| name10000010 | bcdefghijklmnopqrstu | cdefghijklmnopqrstu | 2023-06-16 16:37:14 |
+--------------+-----------------------+---------------------+---------------------+
10 rows in set (6.59 sec)
mysql> select a,b,c,d from t1_part limit 50000000,10;
+--------------+--------------------------+--------------------+---------------------+
| a | b | c | d |
+--------------+--------------------------+--------------------+---------------------+
| name50000001 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000002 | bcdefghijklmnopqrstu | ghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000003 | bcdefghijklmn | ghijklmn | 2023-06-16 16:47:33 |
| name50000004 | bcdefghijklmnopqrstuv | mnopqrstuv | 2023-06-16 16:47:33 |
| name50000005 | bcdefghijklmno | ghijklmno | 2023-06-16 16:47:33 |
| name50000006 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000007 | bcdefghijklmnopqrs | cdefghijklmnopqrs | 2023-06-16 16:47:33 |
| name50000008 | bcdefghijklm | ghijklm | 2023-06-16 16:47:33 |
| name50000009 | bcdefghijklmnopqrstuvwxy | mnopqrstuvwxy | 2023-06-16 16:47:33 |
| name50000010 | bcdefghijklmnopq | ghijklmnopq | 2023-06-16 16:47:33 |
+--------------+--------------------------+--------------------+---------------------+
10 rows in set (34.04 sec)查看执行计划，走的是全表扫描，扫描了58816813行
mysql> explain select a,b,c,d from t1_part limit 50000000,10;
+----+-------------+---------+-------------------------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+-------------------------------+------+---------------+------+---------+------+----------+----------+-------+
| 1 | SIMPLE | t1_part | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL | NULL | NULL | NULL | NULL | 58816813 | 100.00 | NULL |
+----+-------------+---------+-------------------------------+------+---------------+------+---------+------+----------+----------+-------+
通过主键id + limit查看
mysql> select a,b,c,d from t1_part where id > 50000000 limit 10;
+--------------+--------------------------+--------------------+---------------------+
| a | b | c | d |
+--------------+--------------------------+--------------------+---------------------+
| name50000001 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000002 | bcdefghijklmnopqrstu | ghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000003 | bcdefghijklmn | ghijklmn | 2023-06-16 16:47:33 |
| name50000004 | bcdefghijklmnopqrstuv | mnopqrstuv | 2023-06-16 16:47:33 |
| name50000005 | bcdefghijklmno | ghijklmno | 2023-06-16 16:47:33 |
| name50000006 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000007 | bcdefghijklmnopqrs | cdefghijklmnopqrs | 2023-06-16 16:47:33 |
| name50000008 | bcdefghijklm | ghijklm | 2023-06-16 16:47:33 |
| name50000009 | bcdefghijklmnopqrstuvwxy | mnopqrstuvwxy | 2023-06-16 16:47:33 |
| name50000010 | bcdefghijklmnopq | ghijklmnopq | 2023-06-16 16:47:33 |
+--------------+--------------------------+--------------------+---------------------+
10 rows in set (0.01 sec)查看执行计划 ，走的主键索引，扫描了4875761行
mysql> explain select a,b,c,d from t1_part where id > 50000000 limit 10;
+----+-------------+---------+----------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+----------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t1_part | p5,p6,p7,p8,p9 | range | PRIMARY | PRIMARY | 4 | NULL | 4875761 | 100.00 | Using where |
+----+-------------+---------+----------------+-------+---------------+---------+---------+------+---------+----------+-------------+
使用between .. and ..查看
mysql> select a,b,c,d from t1_part where id between 50000001 and 50000010;
+--------------+--------------------------+--------------------+---------------------+
| a | b | c | d |
+--------------+--------------------------+--------------------+---------------------+
| name50000001 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000002 | bcdefghijklmnopqrstu | ghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000003 | bcdefghijklmn | ghijklmn | 2023-06-16 16:47:33 |
| name50000004 | bcdefghijklmnopqrstuv | mnopqrstuv | 2023-06-16 16:47:33 |
| name50000005 | bcdefghijklmno | ghijklmno | 2023-06-16 16:47:33 |
| name50000006 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000007 | bcdefghijklmnopqrs | cdefghijklmnopqrs | 2023-06-16 16:47:33 |
| name50000008 | bcdefghijklm | ghijklm | 2023-06-16 16:47:33 |
| name50000009 | bcdefghijklmnopqrstuvwxy | mnopqrstuvwxy | 2023-06-16 16:47:33 |
| name50000010 | bcdefghijklmnopq | ghijklmnopq | 2023-06-16 16:47:33 |
+--------------+--------------------------+--------------------+---------------------+
10 rows in set (0.00 sec)查看执行计划，走的主键索引，扫描了10行
mysql> explain select a,b,c,d from t1_part where id between 50000001 and 50000010;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1_part | p4,p5 | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
使用between and + limit查看
mysql> select a,b,c,d from t1_part where id between 50000001 and 50010000 limit 10;
+--------------+--------------------------+--------------------+---------------------+
| a | b | c | d |
+--------------+--------------------------+--------------------+---------------------+
| name50000001 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000002 | bcdefghijklmnopqrstu | ghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000003 | bcdefghijklmn | ghijklmn | 2023-06-16 16:47:33 |
| name50000004 | bcdefghijklmnopqrstuv | mnopqrstuv | 2023-06-16 16:47:33 |
| name50000005 | bcdefghijklmno | ghijklmno | 2023-06-16 16:47:33 |
| name50000006 | bcdefghijklmnopqrstu | defghijklmnopqrstu | 2023-06-16 16:47:33 |
| name50000007 | bcdefghijklmnopqrs | cdefghijklmnopqrs | 2023-06-16 16:47:33 |
| name50000008 | bcdefghijklm | ghijklm | 2023-06-16 16:47:33 |
| name50000009 | bcdefghijklmnopqrstuvwxy | mnopqrstuvwxy | 2023-06-16 16:47:33 |
| name50000010 | bcdefghijklmnopq | ghijklmnopq | 2023-06-16 16:47:33 |
+--------------+--------------------------+--------------------+---------------------+
10 rows in set (0.00 sec)查看执行计划，扫描了19711行
mysql> explain select a,b,c,d from t1_part where id between 50000001 and 50010000 limit 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | t1_part | p4,p5 | range | PRIMARY | PRIMARY | 4 | NULL | 19711 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
总结：
通过有序的“主键”或“唯一键”索引为分页查看效率更高，通过扫描行数来看使用between ..and进行分页查询，执行时间和扫描成本更少。
