16.千万级及以上数据量如何做分页查询

2024-12-29 约 200 字预计阅读 1 分钟

方式一：基本分页

SELECT * FROM table LIMIT offset, size;

方式二：子查询优化

随着数据量的增长，分页数量自然也越来越多，可以通过子查询的方式提升分页效率。在基本查询的基础上，先通过一个子查询检索出该页的起始主键ID，然后作为主查询WHERE语句条件进行再次检索。


SELECT * FROM table WHERE id > (SELECT id FROM table LIMIT offset, 1) LIMIT size;

方式三：JOIN分页

使用JOIN语句进行关联查询，同样可以达到分页的效果，而且当数据量过多时，比基本分页效率更高。

SELECT * FROM table AS t1 JOIN (SELECT id FROM table LIMIT offset, 1) AS t2 WHERE t1.id >= t2.id LIMIT size;

子查询和JOIN关联查询类似，都是优先走一次子查询，实测耗时情况也差不多；
子查询通常是在索引上完成，而普通分页查询是在数据文件上完成，索引文件通常要比数据文件小得多，所以操作起来更快；

方式四：NextToken&MaxResults

如果每次查询都能返回下一页开始的位置，SQL语句中我们就不用使用LIMIT双参数模式，而是使用主键去过滤、LIMIT单参数即可

SELECT * FROM user WHERE id > last LIMIT size;

示例：

表user

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_name` varchar(256) DEFAULT '' COMMENT '名称',
  `sex` tinyint(1) unsigned DEFAULT '0' COMMENT '性别：0表示男、1表示女',
  `phone_num` varchar(20) DEFAULT '' COMMENT '手机号',
  `description` varchar(512) DEFAULT '' COMMENT '描述信息',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_num` (`phone_num`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表'
;

正序分页查询，每页10条记录，查询第1页，参数设置为
- - NextToken = null，默认从1开始；
- MaxResults = 10
```
SELECT * FROM user WHERE id > 0 LIMIT 10;
```
- 返回的10条计量中最后一条的记录的主键ID为10，我们将最后一条计量的主键ID可以使用NextToken进行加密，然后返回给调用者，下一次调用时使用该Token会解析出上一次查询最后一条记录的主键ID，第二次查询

第二次请求时：携带上第一次请求的NextToken

SELECT * FROM user WHERE id > 10 LIMIT 10;

依次分页查询下去，直到第1000001页时：

SELECT * FROM user WHERE id > 10000000 LIMIT 10;