想听听大家对分页 sql 的看法

[zazalu]

开发至今一直写 crud 的菜鸟, 想优化下分页 sql, 从网上得知几种方式经过我自己的测试觉得效果不是很明显(不知道是不是我测试存在问题.)

自己的练习系统一直用的 limit ${pager.start}, ${pager.size}这样的写法, 但是好像数据超过百万后,就很难在 1s 以下完成查询了..

特别想知道大佬们在维护的实际项目的分页都是怎么做的呢?

[w4n9hu1]

昨天看了一个思路，查询带上 sortname 的 maxvalue，分页查询 sql 加上>maxvalue。

[ikaiguang]

GO 语言写的
https://github.com/ikaiguang/go-pagination

[jaskle]

性能最好的分页是>上一页最后一个 id limit x，可以参考一下 GitHub 的 release 分页，看看 url。
但是这种分页不能任意跳页，适合手机动态加载列表

[1762628386]

指导思想：增加有效可利用的索引，其目的是减少每次查询时的系统负载。

0.用固定页码做索引: 前提条件是页码总数只增不减少, pageTotal%pageSize 例如第 1-100 条数据的索引就是 index_page_1,以此类推.
1.每次携带最大的主键 id,使其查询时带上 id :例如: where id>{maxId} limit 10
2.优化：根据现有业务按列分表,将用于排序的列数做最小拆分，尽量不要有大规模字符串,查询后用 in(ids)方式聚合
3.增加一个扩展表,用于分页: pagination(id,page_number) 原理同第 0 条

[Takamine]

一般就用插件，比如 pagehelper。

[jaskle]

以前公司用游标分页体验非常好他就是数据库查询完以后连接不断，然后前后移动来完成，查询和翻页巨快。
就是有点儿浪费数据库连接。同时在线人数多了就撑不住。

[Varobjs]

4 楼给出了解决办法
就是加 主键 ID，每次限制在 ID 一定范围内再分页
五百万数据也很快分页查完

[tonghuashuai]

4 楼正解

[cuvii]

同菜鸟，直接用 limit 加 offset 来查询，不过这样有点跑题，并不能分出页码

[avenger]

这样试试速度

```
result = SELECT id FROM table WHERE xxx limit x,y;
SELECT * FROM table WHERE id IN(result)
```

[zazalu]

@avenger 这种我测试过, 提升速度不太明显 耗时主要还是 limit 那里的问题. 4 楼给出的答案非常不错

[zazalu]

@1762628386 非常感谢, 一开始没看懂, 现在明白了. 我这边写点小总结,可以帮忙看看对不对吗?

分页优化思路:
1. [牺牲空间法]:
额外维护`page`的列或者 pagination(id, page)的表, 这样便可以直接使用如下的 sql 来让提速
```
SELECT xxxx
FROM xxx
WHERE page = 29 -- 查询第 29 页数据
```
但是这种方式有个明显缺点, 你需要去维护这些属性, 对于有些可以任意修改 pageSize 或者表的行数据是支持删除的系统, 不适用

2. [键集分页]

也就是所谓的每次携带上一页的最大 id, 通过`where id >= xxx`这样去直接缩小扫描范围,从而提速

3. [切分优化]

感觉这个有点难, 非常依赖实际情况, 我先待定, 不总结...

[zazalu]

此外, 我个人测试了下网上一些, 比如使用 join, 或者反转法 . 基本都是没啥大提升的, 建议大家不要盲目相信

[leafre]

pagehelper

[1762628386]

@zazalu 有个最简单的：利用 mysql 的索引覆盖 (using index),直接从索引中查询并返回结果
例如:
表:user(id,gender,nickname,cityId)
索引：index(cityId);
查询：where cityId=1 的所有用户并分页
查询语句 select id,cityId from user where cityId=1
只要 where 条件的列和索引的列一致，就能直接从索引中返回数据，还是很快的，但查找的行数(rows)还是很多

[zazalu]

@1762628386 ?

[zazalu]

@1762628386 我人晕了 晚上研究下。。。

[1762628386]

@zazalu 就想办法减少 explan 时的 rows 就行了，空间换时间