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

[Aluhao]

直接用变通的查询方式，查询时间高的吓人，因为有些数据查询是不连贯的，不能用大于多少或等于多少来查询；
[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 能人很多，不知道还有没更优化的方法？

[bolink5]

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

[wsc449]

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

[dexterzzz]

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

[sagaxu]

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

[syncher]

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

[syncher]

> 现在的做法是通过先查询取出 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]

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

[Romanticlizhi]

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

[Aluhao]

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

[liuanxin]

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

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

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

[syncher]

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

[luw2007]

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

[stabc]

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

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

[stabc]

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

[opengps]

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

[opengps]

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