首页  :: 新随笔  :: 管理

mysql分页查询

Posted on 2023-06-16 17:26  高&玉  阅读(23)  评论(0)  编辑  收藏  举报

创建测试表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进行分页查询，执行时间和扫描成本更少。