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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 1923 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

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

分页

SQL

CRUD

Limit

18 条回复 • 2019-08-29 14:32:59 +08:00

w4n9hu1

2019-08-28 18:15:49 +08:00

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

ikaiguang

2019-08-28 18:17:09 +08:00

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

jaskle

2019-08-28 18:26:48 +08:00 via Android

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

1762628386

2019-08-28 18:29:19 +08:00

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

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

2019-08-28 18:29:26 +08:00 via Android

一般就用插件，比如 pagehelper。

jaskle

2019-08-28 18:31:28 +08:00 via Android

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

Varobjs

2019-08-28 20:17:29 +08:00 via Android

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

tonghuashuai

2019-08-28 22:37:42 +08:00 via iPhone

4 楼正解

B3C933r4qRb1HyrL

2019-08-29 08:58:19 +08:00

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

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)
```

zazalu

2019-08-29 10:52:14 +08:00

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

zazalu

2019-08-29 10:59:14 +08:00

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

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

2. [键集分页]

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

3. [切分优化]

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

zazalu

2019-08-29 11:00:13 +08:00

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

leafre

2019-08-29 11:07:06 +08:00

pagehelper

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)还是很多

zazalu

2019-08-29 13:58:46 +08:00 via Android

@1762628386 ?

zazalu

2019-08-29 14:01:04 +08:00 via Android

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

1762628386

2019-08-29 14:32:59 +08:00

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