常见分页问题

一、普通分页

最常见的是关系型数据库，关系型数据库中最常见的是MySQL，MySQL分页中最基本的写法是使用limit语法如下：

select * from user limit x,y

x指定查询数据游标的起点且包含x，y指定查询数据的条数；

优点

1. 最普遍，绝大数都是这种方式实现 
2. 最简单，SQl语法简单，程序员第一次学数据库的时候就已经学了
3. 可以统计总的页数

缺点

1. 当查询user表数据量过大的时候，分页查询的数据量变大影响效率

场景

1. 最常见的数据分页，boss展示等

二、id排序的普通分页

通过id（MySQL中自增长）排序，然后每一页查询携带一个查询条件，以上一个页中最后一条数据的id作为下一页的起点，这样每次查询都减少了数据量且每次查询可以只查询第一页，id滚动递增

select * from user where id>m  order by id asc limit x,y 

优点

1. 减少查询数据范围，id作为主键查询条件性能很快
2. 可以每次查询只取一页数据

缺点

1. 条件字段id，排序字段id，必须是有序的，且是唯一
2. 分页只能每次只能查下一页，不能统计总的页数

场景

1. 最常见的数据分页，boss展示且不需要展示页总数
2. 数据迁移查询，跑批查询等需要穷尽当前表数据的场景

三、实时+历史组合分页

我曾经遇到将user(最近3个月)，user_his（存量历史非最近3个月） 组合提供历史数据分页查询的问题，我们当时就是采用组合分页；

步骤

1. 计算user表总页数 x
2. 计算user_his表总页数 y
3. 合并x+y得到总页数
4. 如果当前页码 < x则直接查询user表
5. 如果当前页码需要的数据查询user表不足一页，且user_his表还存在满足条件的数则通过user_his表补齐一页，也就是两个表一起提供一页数据
6. 如果当前页码需要的数据全部在user_his表中则直接查询user_his表

优点

1. 可以让user表和user_his表处于同一个数据库中，有时候业务场景评估就是需要这种形式
2. 可以对外提供标准的分页接口数据
3. user表的数据量只存极少一部分，交易业务性能提升

缺点

1. user表和user_info表合并处理逻辑复杂了
2. 查询需要数据库次数变多，影响性能
3. 限制在一些特定场景

场景

1. 数据库查询性能不足，历史数据又要提供查询，交易业务又不需要历史数据，如果交易和历史混合存一个表，历史查询会影响交易业务的性能。交易，历史查询两个功能的业务优先等级不一样，数据最好的场景就是分开存
2. 如果历史数据在某些场景不能短时间迁移到合适的组件存储且提供查询，且有些场景就是需要和实时数据放到一个库
3. 总的来说这方案是折中，是成本和收益的权衡，如果大数据查询，历史数据查询等底座支持能力强，这个方案就不需要了，但是也有存在的价值

四、多条件高效异构组件分页

假如数据存在MySQL中，查询条件有10个，时间，id，手机号，合同号，类型，状态等，如果每个条件都建索引，且互相建组合索引，那单表的索引数量会很多，索引增加会导致查询性能下降

将查询条件主动构建一份索引数据+原始数据id存入ES，原始交易数据还是存到MySQL中。当查询的时候先通过ES多条件查询，ES查询拿到原始数据主键id后，再通过id到MySQL中查询。比如：

交易类型，时间，状态，产品，类型，手机号，原始数据id作为ES的文档结构

ES的多条件查查询类似主动构建表外二级索引原理

优点

1. 可以减少交易表的查询压力
2. 可以对外提供多条件，比如十几个条件的查询，MySQL条件太多性能就不行了，索引数量不能太对
3. ES的查询性能比MySQL强
4. ES中存入的索引数据量不大

缺点

1. 查询处理逻辑复杂，需要主动构建ES的表外索引数据，如果数据删除，更新，新增都需要主动维护表外索引数据
2. 引入一个新的数据库组件ES，引入架构的复杂性，降低稳定性
3. 限制在一些特定场景，比如宽表多条件查询

场景

1. 宽表多条件查询，条件复杂多到关系型数据库索引性能瓶颈
2. 支持模糊查询等,ES的倒排索引天然支持模糊统计查询
3. 数据不是经常变更的历史数据查询等

五、分布式数据库ES分页查询

分布式数据库标准分页语法会导致深度分页问题,ES的from,size于是限制每次只能查1w条数据
使用ES的 scroll API 检索语法则不会存在这个限制

场景

不要把 scroll 用于实时请求，它主要用于大数据量的场景。例如：将一个索引的内容索引到另一个不同配置的新索引中

缺点

1. 不能大量实时查询
2. 对内存要求高,需要大内存,不让集群容易夯住,我曾经在生产中搞挂了2次ES

五、分布式数据库TiDB分页查询

tidb是关系型分布式数据库,天然支持SQL的标准limit x,y语法,但是同样存在分布式数据库查询深度分页问题,且tidb不支持主键自增长,不能按照本文开头中MySQL那种通过id作为条件的形式

简单方案

按标准limit x,y语法,存在深度分页问题

不能通过创建时间递增条件

曾经我遇到过tibd单表按照创建时间排序做分页,且创建时间在tidb中的精度是秒,如果以创建时间每页查询递增条件则会导致一个问题:当页大小是1000,但是同一秒数数据量如果超过1000则,这个查询死循环了,因为每次查询的时间条件一样

其它替代字段

如果表中有其它字段满足唯一性,且有序则可以同等效果替代id字段实现分页加速

最终方案

将上一页的主键id列表,作为下页的排除条件参数,时间参数还是保留,比如:

select * from user where id not in (preIdList) and create_time >preTime order by create_time  desc limit x,y

优点

1. 减少查询量,不再有深度分页问题
2. 不会死循环

缺点

1. 查询接口需要携带排除idList条件互相传递,不是标准查询接口参数,很多使用方一看就很复杂,就会条件反射不接受,否定
2. 如果数据的并发度根本不高,比如一秒内的数据很少只有几十条m,直接设置一页数据量远远大于m,比如设置1000的页大小就能通过时间作为分页递增参数,避免死循环场景

六、分库分表的分页查询

分库分表后的分页查询,如果查询条件不带分片键,需要每次都遍历所有分片,查询SQL次数增多,降低查询效率,这种场景没得比较好的办法,除了尽量携带分片键以为,因为分库分表带来的数据拆分本质就是会存在副作用的.

脑洞其它缓解方案

将查询条件主动做二级表外索引,和本文第四点类似

七、总结思考

常见问题

1. 查询分页数据量过大
2. 查询分页条件过多
3. 查询分页在分布式数据库,分库分表数场景存在深度分页问题
4. 查询实时性要求,如秒级返回
5. 查询请求qps并发性要求
6. 内存资源限制
7. 大历史数据查询问题,(本质是冷,温,热数据治理不合理)
8. 历史数据和实时数据混合查询问题,(本质架构设计不合理)

常见优化思路

1. 查询语法
2. 程序逻辑
3. 使用索引
4. 拆分SQL

架构调整

1. 分库,分表
2. 多种数据库组件支持
3. 冷温热数据拆分处理
4. 大数据查询组件引入,ES，tidb，Hbase等

业务使用场景改变

1. 页面查询的时候不提供count计算总数,count全表扫描耗时
2. 页面查询不提供计算总的页数,接口只返回下一页是否还有数据,和当前这一页数据,由调用方来触发查询下一页
3. 页面分页展示分级,经常展示的入口只展示第一页数据,如果要看详情列表再触发全量历史分页
4. 业务查询一定强制要带区分度高的索引字段,不能全表去模糊匹配