转:大数据分页方案

软件开发中，常用要用到分页、计算总数，数据量超过千万、上亿的时候，往往count 的需要超过 1s 的执行时间，甚至 3-5s，对于一个追求性能的前沿团队来说，这个不能忍啊！

为什么会慢？

mysql 会对所有符合的条件做一次扫描。

select count(*) from table_a where a = '%d' ...

如果 a=%d 的数据有 1000W 条，那么数据库就会扫描一次 1000W 条数据库。如果不带查询条件，那这种全表扫描将更可怕。

count(*) 和 count(1)、count(0)

• count(expr) 为统计 expr 不为空的记录

• count(*) 它会计算总行数，不管你字段是否有值都会列入计算范围。

• coount(0),count(1) 没有差别，它会计算总行数

Example 1:

mysql> explain extended select count(*) from user;
...
1 row in set, 1 warning (0.34 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------+
| Note | 1003 | select count(0) AS `count(*)` from `user` |

Example 2:

mysql> select count(*) from login_log
 -> ;
+----------+
| count(*) |
+----------+
| 2513 |
+----------+
1 rows in set (0.00 sec)

mysql> select count(logoutTime) from login_log;
+-------------------+
| count(logoutTime) |
+-------------------+
| 308 |
+-------------------+
1 rows in set (0.00 sec)

怎么解决？

MyISAM DB

MyISAM 引擎很容易获得总行数的统计，查询速度变得更快。因为 MyISAM 存储引擎已经存储了表的总行数。
MyISAM 会为每张表维护一个 row count 的计数器，每次新增加一行，这个计数器就加 1。但是如果有查询条件，那么 MyISAM 也 game over 了，MyISAM 引擎不支持条件缓存。

On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index

其他 DB 引擎

受到 MySIAM DB 的启发，我们可以手动维护总数缓存在表的索引中了。

1. 如果 ID 连续，且基本不会断开。直接取最大值 ID

2. 如果表中存在连续的数字列并设为索引，那么通过页码即可计算出此字段的范围，直接作范围查询即可：

   start = (page-1)*pagesize+1 
   end = page*pagesize 
   select * from table where id >start and id <=end 

3. 涉及到总数操作，专门维护一个总数。新增一个用户，总数值加 1, 需要总数的时候直接拿这个总数, 比如分页时。如果有多个条件，那么就需要维护多个总数列。该方案的扩展性更好，随着用户表数量增大, 水平切分用户表，要获取用户总数，直接查询这个总数表即可。

分页正反偏移

数据库自带的 skip 和 limit 的限制条件为我们创建了分页的查询方式，但是如果利用不对，性能会出现千倍万倍差异。
简单一点描述：limit 100000,20 的意思扫描满足条件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行，问题就在这里。如果我反向查询 oder by xx desc limit 0,20，那么我只要索引 20 条数据。

Example 3

mysql> select count(*) from elastic_task_log_copy;
+----------+
| count(*) |
+----------+
| 1705162 |
+----------+
1 rows in set (2.31 sec)

正向偏移查询。超级浪费的查询，需要先 skip 大量的符合条件的查询。

mysql> select id from elastic_task_log_copy order by id asc limit 1705152,10;
+---------+
| id |
+---------+
| 1705157 |
| 1705158 |
| 1705159 |
| 1705160 |
| 1705161 |
| 1705162 |
| 1705163 |
| 1705164 |
| 1705165 |
| 1705166 |
+---------+
10 rows in set (2.97 sec)

反向偏移查询。同样的查询结果，千差万别的结果。

mysql> select id from elastic_task_log_copy order by id desc limit 0,10;
+---------+
| id |
+---------+
| 1705166 |
| 1705165 |
| 1705164 |
| 1705163 |
| 1705162 |
| 1705161 |
| 1705160 |
| 1705159 |
| 1705158 |
| 1705157 |
+---------+
10 rows in set (0.01 sec)

这两条 sql 是为查询最后一页的翻页 sql 查询用的。由于一次翻页往往只需要查询较小的数据，如 10 条，但需要向后扫描大量的数据，也就是越往后的翻页查询，扫描的数据量会越多，查询的速度也就越来越慢。

由于查询的数据量大小是固定的，如果查询速度不受翻页的页数影响，或者影响最低，那么这样是最佳的效果了（查询最后最几页的速度和开始几页的速度一致）。

在翻页的时候，往往需要对其中的某个字段做排序（这个字段在索引中），升序排序。那么可不可以利用索引的有序性 来解决上面遇到的问题。

比如有 10000 条数据需要做分页，那么前 5000 条做 asc 排序，后 5000 条 desc 排序，在 limit startnum，pagesize 参数中作出相应的调整。

但是这无疑给应用程序带来复杂，这条 sql 是用于论坛回复帖子的 sql，往往用户在看帖子的时候，一般都是查看前几页和最后几页，那么在翻页的时候最后几页的翻页查询采用 desc 的方式来实现翻页，这样就可以较好的提高性能。

游标：上一页的最大值或者最小值

如果你知道上一页和下一页的临界值，那么翻页查询也是信手拈来了，直接就告诉了数据库我的起始查询在哪，也就没有什么性能问题了。我更愿意称这个东西为游标 (Cursor)。
如果做下拉刷新，那么就直接避免掉分页的问题了。根据上一页的最后一个值去请求新数据。

mysql> select id from elastic_task_log_copy where id >= 1699999 limit 10;
+---------+
| id |
+---------+
| 1699999 |
| 1700000 |
| 1700001 |
| 1700002 |
| 1700003 |
| 1700004 |
| 1700005 |
| 1700006 |
| 1700007 |
| 1700008 |
+---------+
10 rows in set (0.01 sec)

缓存和不精准

数据量达到一定程度的时候，用户根本就不关心精准的总数, 没人关心差几个。看看知乎、微博、微信订阅号，不精准的统计到处都是。

如果每次点击分页的时候都进行一次 count 操作，那速度肯定不会快到哪里去。他们一般也是采用计数器的办法。每次新增加一个粉丝，就把值加 1，直接在用户信息存储一个总数，一段时间后重新查询一次，更新该缓存。这样分页的时候直接拿这个总数进行分页，显示的时候直接显示模糊之就行。

那为什么微信公众号的阅读量只有 10W+ 这个量级呢？100W+ 级去哪了！

其他大神的建议

1. mysql 的数据查询, 大小字段要分开, 这个还是有必要的, 除非一点就是你查询的都是索引内容而不是表内容, 比如只查询 id 等等

2. 查询速度和索引有很大关系也就是索引的大小直接影响你的查询效果, 但是查询条件一定要建立索引, 这点上注意的是索引字段不能太多，太多索引文件就会很大那样搜索只能变慢,

3. 查询指定的记录最好通过 Id 进行 in 查询来获得真实的数据. 其实不是最好而是必须，也就是你应该先查询出复合的 ID 列表, 通过 in 查询来获得数据

4. mysql 千万级别数据肯定是没问题的, 毕竟现在的流向 web2.0 网站大部分是 mysql 的

5. 合理分表也是必须的, 主要涉及横向分表与纵向分表, 如把大小字段分开, 或者每 100 万条记录在一张表中等等, 像上面的这个表可以考虑通过 uid 的范围分表, 或者通过只建立索引表, 去掉相对大的字段来处理.

6. count() 时间比较长, 但是本身是可以缓存在数据库中或者缓存在程序中的, 因为我们当时使用在后台所以第一页比较慢但是后面比较理想

7. SELECT id 相对 SELECT 差距还是比较大的, 可以通过上面的方法来使用 SELECT id + SELECT ... IN 查询来提高性能

8. 必要的索引是必须的, 还是要尽量返回 5%-20% 的结果级别其中小于 5% 最理想;

9. mysql 分页的前面几页速度很快, 越向后性能越差, 可以考虑只带上一页, 下一页不带页面跳转的方法, 呵呵这个比较垃圾但是也算是个方案, 只要在前后多查一条就能解决了. 比如 100,10 你就差 99,12 呵呵，这样看看前后是否有结果.

10. 前台还是要通过其他手段来处理, 比如 lucene/Solr+mysql 结合返回翻页结果集, 或者上面的分表

11. 总数可能是存在内存中, 这样分页计算的时候速度很快。累加操作的时候将内存中的值加 1。总数这个值要持久化，还是要存到磁盘上的，也就是数据库中 (可以是关系型数据库，也可以是 mongdb 这样的数据库很适合存储计数)。把总数放在内存中，只是避免频繁的磁盘 i/0 操作 (操作数据库就要涉及到磁盘读写)。

如果你还有更好的建议，请在评论里面告诉我吧。