传统分页
通常在分页时(以 HTTP 协议 GET 方法举例),会携带两个可选参数,limit
和offset
。或者是携带一个 page
参数,如果是 limit
,offset
参数,在后端从数据库获取数据时,便是直接的使用 LIMIT
关键字查询,如下
使用 page
参数控制翻页的实现方式与上面大同小异,无非是 offset = (page-1) * size
这样完全可以实现翻页,但是在数据量和偏移量上去之后,查询性能便会直线下降。
数据缺失
除了性能问题,上面提到的数据缺失,假设有这样情况,先前表里有10条数据,如下
+---------------------------+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | +------------------- || 参数:limit=3&offset=0,得到 +-----------+ | 1 | 2 | 3 | +-----------+ || 删除一条数据(2) +-----------------------+ | 1 | 3 | 4 | 5 | 6 | 7 | +-----------------------+ || 参数:limit=3&offset=3 期望:在上次取出 1,2,3 的基础上,这次取出4,5,6 实际:取出的是 5,6,7,这就导致4这条记录没有被取到
数据重复
还是上面的例子
+---------------------------+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | +---------------------------+ || 参数:limit=3&offset=0,得到 +-----------+ | 1 | 2 | 3 | +-----------+ || 插入一条数据(8) +-------------------------------+ | 1 | 8 | 2 | 3 | 4 | 5 | 6 | 7 | +-------------------------------+ || 参数:limit=3&offset=3 期望:在 1,2,3 的基础上取出4,5,6,(这里甚至取出的结果很奇怪) 实际:3,4,5,与第一次取出的1,2,3 重复了 3 这条数据 +---------------------------+ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | +---------------------------+ || 参数:limit=3&offset=0,得到 +-----------+ | 1 | 2 | 3 | +-----------+ || 插入一条数据(8) +-------------------------------+ | 1 | 8 | 2 | 3 | 4 | 5 | 6 | 7 | +-------------------------------+ || 参数:limit=3&offset=3 期望:在 1,2,3 的基础上取出4,5,6,(这里甚至取出的结果很奇怪) 实际:3,4,5,与第一次取出的1,2,3 重复了 3 这条数据 # 游标分页 游标分页是通过 `cursor` 和 `size` 这样的类似两个参数来控制翻页,简单的 SQL 语句如下mysql> select \* from data where id>cursor limit size这样再来看上面的两个问题,第一个参数变为
cursor=3&size=3
,结果为 4,5,6,符合预期
第二个参数取出结果为 4,5,6,也符合预期。看来数据缺失和数据重复的问题得到了解决性能对比
查询时间为三次查询取平均值
$ docker run --name cursor_paged -e MYSQL_ROOT_PASSWORD=x1ah -itd mysql:latest $ mysql -d 127.0.0.1 -P 3306 -u root -p mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.19 | +-----------+ 1 row in set (0.00 sec)
测试的表结构如下
mysql> desc data; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | content | varchar(128) | NO | | NULL | | +---------+------------------+------+-----+---------+----------------+<clipboard-copy aria-label="Copy" class="ClipboardButton btn js-clipboard-copy m-2 p-0 tooltipped-no-delay" data-copy-feedback="Copied!" data-tooltip-direction="w" value="mysql> desc data;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| content | varchar(128) | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+向表内事先插入 3000000 条记录
mysql> delimiter // mysql> create procedure fake_data(in num int) -> begin -> declare string char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -> declare rand_str char(2); -> declare i int default 0; -> while iset rand_str=concat(substring(string,1+floor(rand()\*61),1),substring(string,1+floor(rand()\*61),1)); -> set i=i+1; -> insert into data (content) values (rand_str); -> end while; -> end; -> // mysql> call fake_data(3000000); Query OK, 1 row affected (24 min 42.63 sec) 使用
limit
,offset
翻页mysql> select \* from data limit 2000000, 10; +---------+---------+ | id | content | +---------+---------+ | 2000001 | RP | | 2000002 | m8 | | 2000003 | tN | | 2000004 | rE | | 2000005 | MQ | | 2000006 | GI | | 2000007 | oG | | 2000008 | 37 | | 2000009 | iU | | 2000010 | xL | +---------+---------+ 10 rows in set (0.36 sec) mysql> explain select \* from data limit 2000000, 10\G; \***************************[ 1. row ]\*************************** id | 1 select_type | SIMPLE table | data partitions |type | ALL possible_keys | key | key_len | ref | rows | 2712992 filtered | 100.0 Extra | 1 row in set Time: 0.017s 使用游标翻页
mysql> select \* from data where id > 2000000 limit 10; +---------+---------+ | id | content | +---------+---------+ | 2000001 | RP | | 2000002 | m8 | | 2000003 | tN | | 2000004 | rE | | 2000005 | MQ | | 2000006 | GI | | 2000007 | oG | | 2000008 | 37 | | 2000009 | iU | | 2000010 | xL | +---------+---------+ 10 rows in set (0.00 sec) mysql> explain select \* from data where id > 2000000 limit 10\G \***************************[ 1. row ]\*************************** id | 1 select_type | SIMPLE table | data partitions |type | range possible_keys | PRIMARY key | PRIMARY key_len | 4 ref | rows | 1356496 filtered | 100.0 Extra | Using where 1 row in set Time: 0.019s 这里从查询时间还是从
explain
看,都是第二种方式性能要好,第一种直接扫全表了