单表三千万数据分页优化方案

直接用变通的查询方式，查询时间高的吓人，因为有些数据查询是不连贯的，不能用大于多少或等于多少来查询；
[sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 199980, 20;
[time] => 0.438656
[sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 1999980, 20;
[time] => 26.835051
[sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 19999980, 20;
[time] => 31.343988
[sql] => SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 29999980, 20;
[time] => 32.138655

现在的做法是通过先查询取出 id
SELECT id FROM `article_comments` LIMIT 19999980, 20;

然后用 id 去取数据
SELECT * FROM `article_comments` WHERE id IN('1','2'....);
虽然这样优化了很多，但是也不是很理想，如果取的 ID 间隔大，也会进行全表扫描；
看 V2 能人很多，不知道还有没更优化的方法？

select

Limit

desc

from

36 条回复 • 2017-08-30 06:38:14 +08:00

af463419014

2017-08-09 15:20:36 +08:00

数据库查询规范第一条 : 绝对不允许使用 select *

yzongyue

2017-08-09 15:27:05 +08:00

在实际的项目中会有这种需求么
> SELECT id FROM `article_comments` LIMIT 19999980, 20;
连个 where article_id = xxx 都没有

Aluhao

2017-08-09 15:30:32 +08:00

@yzongyue 上面只是一个显示所有留言的列表，等同于留言管理吧，加判断也是有需求的；

cxh116

2017-08-09 15:34:03 +08:00

改分页查询条件吧, limit offset 几十万,能不慢.
用当前页最大评价 id ,下一页查询大于此 id 即可.

https://juejin.im/entry/5865c81fac502e006d5e72de

Aluhao

2017-08-09 15:36:41 +08:00

@cxh116 这方法不适用有些场景的，如，需要查询用户回复的留言，这个留言 ID 非顺序的，很乱的，在几千万条数据中断断的出现几千条；

w0000

2017-08-09 15:40:14 +08:00

@Aluhao 这个 id 是建了索引的么，这么慢吗

Aluhao

2017-08-09 15:41:18 +08:00

@w0000 肯定建了索引了，因为数据太多了，单表三千多万，如果只是几十万就很快。

XiaoFaye

2017-08-09 15:45:06 +08:00

论一定时间后关闭评论的重要性

nullcc

2017-08-09 15:46:32 +08:00

采用 limit offset 这种方式，如果表数据量一大就会坑爹，如果索引没建好，一个很大的 offset 需要让数据库先扫描前面的很多数据，到后期效率和全表扫描也没什么区别了。
你可以考虑在 article_comments 的创建日期上创建聚集索引，文章评论满足“既不能绝大多数都相同，又不能只有极少数相同”的条件，比较适合在这种列上创建聚集索引。查询的时候可以根据日期先做筛选，然后再跟上你的其他查询条件。

Aluhao

2017-08-09 15:46:37 +08:00

@XiaoFaye 这个不太好吧

nullcc

2017-08-09 15:48:41 +08:00

补充下，文章评论的创建日期满足“既不能绝大多数都相同，又不能只有极少数相同”的条件，上面笔误。

Sunshow

2017-08-09 15:54:20 +08:00

把翻页都通过 id > 的条件转换成取第一页的请求

sujin190

2017-08-09 15:55:25 +08:00

大量数据要翻页一般不使用页码，而是使用游标，游标那么久可以包含很多信息了，当前页码，翻页方向，当前 id 值，每页条数等等，使用的方式 where id>last_id offset per_page_count，这样就可以充分使用索引来翻页了

Immortal

2017-08-09 15:59:28 +08:00

@Aluhao 上面说的根据 id 大于的方式查询我想了下没问题吧虽然是断断续续的 limit 后数量还是准确的吧?

daimazha

2017-08-09 16:16:38 +08:00

@Immortal #14 没问题，而且这么多数据可以考虑分表了。

vipvideoshare

2017-08-09 16:18:47 +08:00

试试给 id 加反向 index
```
CREATE INDEX id_desc_index ON article_comments (id DESC)
```

Immortal

2017-08-09 16:20:12 +08:00

@daimazha 是的如果用户 id 顺序可以直接取模分表也简单的

zhaopengme

2017-08-09 16:27:49 +08:00

肯定是根据业务场景来优化了,单纯的用 sql 么有意义.
eg.
1. 比如把数据分为常用数据和历史数据
2. 比如做数据统计,增加中间表
3. 数据快速处理,使用数据库编程,游标很有效率
等等等,具体问题具体对象.

gouchaoer

2017-08-09 16:38:39 +08:00

select * 没有问题，limit 那种写法不可以，你需要这样给 article_comments 弄个自增的 int 健，然后 select * frome table where id_inc > 10000 and id_inc<10100;

suconghou

2017-08-09 17:43:22 +08:00

ID 加索引了吧,可以这样分两次查询 select id from mytable order by id limit 13456901,1; 先查出分页的首页的 ID

得到 ID x

然后 select * from mytable order by id where id > x limit 10;
得到数据.

由于 ID 有索引,整体时间就在于第一条 SQL 的时间. 性能能提升 2-3 倍.

bolink5

2017-08-09 20:24:47 +08:00

有一家比较大的公司做法是，把主键 id 放在 property 文件中，作用是存放 id
sql 是 select * from xx where id>'' limit yy 做完了后更新 id 到文件中
id 的效率是最高的

wsc449

2017-08-09 22:57:49 +08:00

给出业务场景才有优化方案

dexterzzz

2017-08-09 23:23:47 +08:00

有列存储索引的话，不叫个事

sagaxu

2017-08-09 23:57:34 +08:00 via Android

先确保内存够大，MySQL 配置够大，index 能全部载入内存，并且还有富余。哦，MySQL 要用 5.6 以上版本，引擎用 innodb，然后再来解决 late row lookup 的事情。

syncher

2017-08-10 00:03:23 +08:00 via Android

1. 大数据下分页舍弃精度换效率，一般都用 where id > 199980。
2. 可以试试可以延迟关联，
select * from tableName inner join (select id from tableName limit 199980，20) as tmp using(id);
3. 如果一定要追求精度建议数据不进行物理删除，删除时做逻辑删除，这样 id 就不会缺失。

syncher

2017-08-10 00:32:58 +08:00

> 现在的做法是通过先查询取出 id
SELECT id FROM `article_comments` LIMIT 19999980, 20;

另外，如果这条 SQL 查询速度很慢那么是不是因为你使用的是 InnoDB 引擎，InnoDB 下默认为聚集索引，聚集索 ID 和数据存一起，所以通过 id 查询很慢，如果是这样可以考虑在其他列和 id 列上建立联合索引，查询时使用索引覆盖。

ALTER TABLE article_comments ADD INDEX id_c1(id,c1);

SELECT id FROM `article_comments` ORDER BY id,c1 LIMIT 19999980, 20;

AsterOcclu

2017-08-10 01:20:08 +08:00

你可以参考一下这篇文章： https://github.com/zhangyachen/zhangyachen.github.io/issues/117
可以通过 inner join 的方法来解决

Romanticlizhi

2017-08-10 09:00:44 +08:00

in 在大数据量的时候本来就不建议使用，而且再纠正一点，并不是所有情况下都是用索引扫描最快，当你需要查询的数据超过总数据一定百分比，使用全表扫描肯定是比索引扫描快的。

Aluhao

2017-08-10 09:28:28 +08:00

@Romanticlizhi 经过测试用 IN()取值很快呀。

liuanxin

2017-08-10 10:27:19 +08:00

你开下查询分析看下时间是消耗在哪里了

SET profiling=1;
SELECT * FROM `article_comments` ORDER BY id DESC LIMIT 1999980, 20;
SHOW PROFILE;
SET profiling=0;

in 里面如果放的是实际的值而不是 in (select xxx ...) 这种, 性能是很快的.

syncher

2017-08-10 11:53:01 +08:00 via Android

等优化好了期待楼主给个最终方案供大家学习

luw2007

2017-08-10 16:16:45 +08:00

id 保证连续。最好可以外部生成。
多维度的数据索引直接放在 redis 的 sorted_set 中。用空间换时间，提前放好所有查询用到的维度。
按照类别，按照用户，等等。
目前线上一个 UGC 自增 id 马上到 1 亿，

stabc

2017-08-10 17:30:11 +08:00

>LIMIT 29999980, 20
这种查询相当于读取数百万条数据，肯定慢。

>因为有些数据查询是不连贯的
差值不大的话，建议还是用“大于号小于号”先取出一部分数据（比如范伟控制在 100 ），然后因为差值可能只拿到了 60 条，然后从这 60 条取前 20 条。这样做可以把每次查询都控制在毫秒级别。不过需要每次分页时带入一个参数，用来记录上次取到的最大 id。

stabc

2017-08-10 17:32:45 +08:00

如果是条件稍微复杂的查询，也可以通过上次记录的 id 值，估算出 10000 条记录区间，然后在这 10000 条里面做查询。也比直接查快上很多。

opengps

2017-08-30 06:35:28 +08:00 via Android

对表进行表分区，这样查询就成了查询多个小表再组合结果的效果

opengps

2017-08-30 06:38:14 +08:00 via Android

忘了说了，我的坐标表最多时候 10 亿行数据，无 id 设计，聚集索引用写入时间倒序(优化写入速度)。然后只给设备码和 GPS 时间列建立索引(优化读取速度)。用的时候这张表也规定只使用这么单一的查询条件