谈一谈分页查询

sorra 发表于 2016 12/20 12:03 修改于 01/11 21:47 阅读数917

分页查询是互联网和企业应用开发中的常见问题。

MySQL的分页查询语法是在query尾部添加limit m, n,表示从第m+1项开始取得n项。Web上的查询通常是用每页大小和页码来控制,如“每页10个,取第1页”,会被翻译成SQL的0, 10,完成查询。

问题1:性能

分页查询的SQL性能是个问题。

  • 如果是单条件查询,只要有相应的单列索引,就能命中索引,否则就要全表扫描。
  • 如果是多条件查询,情况就复杂一些,可能适合联合索引,也可能适合单列索引。
  • 还有一种特别的情况:在一个列上过滤,却在另一个列上排序,这个很容易触发全表扫描(网上能找到优化方法,如这篇:[慢查优化]建索引时注意字段选择性 & 范围查询注意组合索引的字段顺序)。

能避免访问数据库就更好了,可以建立缓存。**考虑到前几页的访问频次最高,可以缓存前几页。**为了节省内存,可以只缓存主键,而非完整对象。

社交网络服务面临的问题更复杂,以微博为例:用户关注了100个人,现在他要刷微博,第1页要有20条,怎么查询?

[这段叙述有误]SQL层面无法解决这一问题。只能——对每一个关注,取对方最新的20条微博。一共取得20*100=2000条,排序,取最新的20条返回给用户,其余1980条丢弃。(NoSQL在把有limit的查询分发到各个分片时,取回的也是limit*shards条数据,在内存中排序挑出limit条返回给用户。)

很浪费吧?所以要用缓存!那么问题来了如果有人发了新微博,缓存怎么更新?Redis支持List, Set, Map等常见数据结构,可用一个list来缓存用户的微博流。用消息队列把新微博广播给所有在线的关注者(每个关注者有一个list),把新微博(可以只是主键)放进list。

有点复杂吧?不用急着优化,到了需要的时候再说。

还有一个问题——怎么查下一页的微博?微博场景没有严格的页码,可把上一页最后一条微博的主键或时间戳传给后端,让后端查询在此之后的20条。如果主键是顺序增长的,可按主键倒序排序;如果主键是乱序的,可按时间戳倒序排序——嗯?两条微博的时间戳相同怎么办?后端可允许冗余,把两条微博都传给前端,前端知道已显示了哪些微博,把已显示的过滤掉,就不会冗余了。

问题2:分页被打乱

这样有一个问题:如果添加或删除了数据,分页会被打乱。例如:

  • 在翻到第2页之前,第9项被别人删除了,第2页的所有数据就会整体前移一位,原本的第2页第1项不见了(跑去第1页了)
  • 在翻到第2页之前,别人在第5项的位置加了一条数据,第2页的所有数据就会整体后移一位,原本的第2页最后1项跑去第3页了。

如果时而碰到这种现象,用户体验会颇为糟糕。

可惜的是,很难解决。如果影响不大,通常是不予解决的。(如果查询条件是主键或时间戳,像微博那样不严格区分页码就行了。)

解决方法倒是有,我试举一个。按前文所说,建立缓存——如果表中发生了数据增删,下次分页查询就绕过缓存去查数据库,把查到的元素列表与缓存比较一下,就知道有什么差异了。这个差异可直接报告给用户,也可以在后台悄悄处理。