工作总结 - 大数据表分页查询优化

在我们进行数据查询时，不可避免要碰到分页的问题。我们通常会在返回数据表格页面下方显示如下信息用于对分页的信息的操作：

总数: 200  [首页] [上一页] 2，[3]，4，5 [下一页] [尾页]  

对于小表我们可以直接操作数据获取如下信息

使用sql语句计算出符合条件的记录总数
select count(1) from table where xxx in (1,2,3)

通过如下的sql获取指定页面的数据
select * from table where xxx in (1,2,3) order by id limit #offset#, 10

对大表的分页的操作

如果表的数据量达到千万、亿级的数量级时，以上方法就不可行了。使用以上的sql语句计算符合条件的数据总数时，会花费大量的时间。我们可以使用如下方法解决这个问题

避免总数的查询

研究发现：随着时间的推移，越早产生的数据，价值越小，在数据量越大的情况下，用户对总数的敏感性越低。所以在数据量足够大的情况下，我们可以不在结果中返回总数（即去掉分页信息中的总数、首页、尾页信息），避免总数的查询。
没有了总数，我们如何计算上一页，下一页呢？

• 对于上一页：如果当前页为N页面，如上一页是（N-1）。如果N=1，则上一页不可以点击
• 对于下一页：假如每页显示 10 条数据，那么查询数据库的时候，用 “limit #offset#, 11” 取出 11 条记录，页面展现10条。如果取到了 11 条，说明下一页还有数据，在页面展示下一页按钮。如果结果集数量不足 11，说明已经到了最后一页，下一页显示不可以点击。

新的分页的信息的操作格式如下：

[上一页] 2，[3]，4，5 [下一页]

事实上google就是这样做的。在查看第一页搜索结果的时候google只会显示前十页(共100个条目)，并不显示搜索结果条目总共有多少。查看第二页的时候，仅仅会多显示一页。

如果必须要显示总数，可以使用如下方法

• 方法一：缓存前N页的记录
  研究发现：用户比较关心的是最近产生的一小部分数据。所以我们可以在用户查询的时候，一次性从数据库查询符合条件的 前N页条数据缓存起来，足够用户翻个几页，避免每次翻页都要查询数据库。

• 方法二：估算总结果数
  如果用户可以接受不精确的总数，我们可以估算总结果数。如在mysql中通过EXPLAIN的”rows”列来快速估算结果总共有多少记录
  EXPLAIN SELECT * FROM table where xxx in (1,2,3);