开发至今一直写 crud 的菜鸟, 想优化下分页 sql, 从网上得知几种方式经过我自己的测试觉得效果不是很明显(不知道是不是我测试存在问题.)
自己的练习系统一直用的 limit ${pager.start}, ${pager.size}这样的写法, 但是好像数据超过百万后,就很难在 1s 以下完成查询了..
特别想知道大佬们在维护的实际项目的分页都是怎么做的呢?
1
w4n9hu1 2019-08-28 18:15:49 +08:00
昨天看了一个思路,查询带上 sortname 的 maxvalue,分页查询 sql 加上>maxvalue。
|
2
ikaiguang 2019-08-28 18:17:09 +08:00
|
3
jaskle 2019-08-28 18:26:48 +08:00 via Android
性能最好的分页是>上一页最后一个 id limit x,可以参考一下 GitHub 的 release 分页,看看 url。
但是这种分页不能任意跳页,适合手机动态加载列表 |
4
1762628386 2019-08-28 18:29:19 +08:00 6
指导思想:增加有效可利用的索引,其目的是减少每次查询时的系统负载。
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 条 |
5
Takamine 2019-08-28 18:29:26 +08:00 via Android 1
一般就用插件,比如 pagehelper。
|
6
jaskle 2019-08-28 18:31:28 +08:00 via Android
以前公司用游标分页体验非常好他就是数据库查询完以后连接不断,然后前后移动来完成,查询和翻页巨快。
就是有点儿浪费数据库连接。同时在线人数多了就撑不住。 |
7
Varobjs 2019-08-28 20:17:29 +08:00 via Android
4 楼给出了解决办法
就是加 主键 ID,每次限制在 ID 一定范围内再分页 五百万数据也很快分页查完 |
8
tonghuashuai 2019-08-28 22:37:42 +08:00 via iPhone
4 楼正解
|
9
B3C933r4qRb1HyrL 2019-08-29 08:58:19 +08:00
同菜鸟,直接用 limit 加 offset 来查询,不过这样有点跑题,并不能分出页码
|
10
avenger 2019-08-29 09:18:02 +08:00
这样试试速度
``` result = SELECT id FROM table WHERE xxx limit x,y; SELECT * FROM table WHERE id IN(result) ``` |
12
zazalu OP @1762628386 非常感谢, 一开始没看懂, 现在明白了. 我这边写点小总结,可以帮忙看看对不对吗?
分页优化思路: 1. [牺牲空间法]: 额外维护`page`的列或者 pagination(id, page)的表, 这样便可以直接使用如下的 sql 来让提速 ``` SELECT xxxx FROM xxx WHERE page = 29 -- 查询第 29 页数据 ``` 但是这种方式有个明显缺点, 你需要去维护这些属性, 对于有些可以任意修改 pageSize 或者表的行数据是支持删除的系统, 不适用 2. [键集分页] 也就是所谓的每次携带上一页的最大 id, 通过`where id >= xxx`这样去直接缩小扫描范围,从而提速 3. [切分优化] 感觉这个有点难, 非常依赖实际情况, 我先待定, 不总结... |
13
zazalu OP 此外, 我个人测试了下网上一些, 比如使用 join, 或者反转法 . 基本都是没啥大提升的, 建议大家不要盲目相信
|
14
leafre 2019-08-29 11:07:06 +08:00
pagehelper
|
15
1762628386 2019-08-29 11:48:36 +08:00
@zazalu 有个最简单的:利用 mysql 的索引覆盖 (using index),直接从索引中查询并返回结果
例如: 表:user(id,gender,nickname,cityId) 索引:index(cityId); 查询:where cityId=1 的所有用户并分页 查询语句 select id,cityId from user where cityId=1 只要 where 条件的列和索引的列一致,就能直接从索引中返回数据,还是很快的,但查找的行数(rows)还是很多 |
16
zazalu OP @1762628386 ?
|
17
zazalu OP @1762628386 我人晕了 晚上研究下。。。
|
18
1762628386 2019-08-29 14:32:59 +08:00
@zazalu 就想办法减少 explan 时的 rows 就行了,空间换时间
|