一、哈希分片不能直接分页最头疼的问题普通分页limit 1000,20在哈希分表后完全失效。为什么哈希分片不能直接分页假设把order表哈希分成4 张表order_0、order_1、order_2、order_3数据是均匀打散的order_id1 → 表 1order_id2 → 表 2order_id3 → 表 3order_id4 → 表 0order_id5 → 表 1...普通分页limit 20,10会发生什么中间件必须去每张表查 limit 20,10把 4 张表的结果全部拉回来在内存中重新排序、再分页致命问题深分页完全不能用比如limit 100000,10性能爆炸每张表都要扫描大量数据全部汇总到内存结果不稳定、重复、错乱二、3种解决方案从简单到企业级1. 业务层禁止深分页最简单适用场景后台管理、不需要跳页的列表。做法只提供上一页 / 下一页不提供跳转到第 100 页的功能前端限制最大偏移量比如最多查 1000 条优点几乎不用改代码。缺点体验差不能跳页。2.游标分页最好的通用方案游标分页不用页码用「上一页最后一条数据的唯一标识ID」来查下一页。哈希分片下 90% 企业的标准方案也叫滚动分页。核心原理不用limit offset而是用上一页最后一条数据的 ID来定位下一页。-- 第一页 SELECT * FROM order WHERE id 0 ORDER BY id ASC LIMIT 10; -- 第二页上一页最后一条 id 100 SELECT * FROM order WHERE id 100 ORDER BY id ASC LIMIT 10;为什么哈希分片能用分片键 id每张表只需要查where id 游标不需要全表扫描不需要大量数据汇总性能极高优点性能最好无论分多少张表都稳定代码改造简单缺点不能跳页只能上 / 下一页3. 全局统一索引表支持跳页复杂适用场景必须跳页、必须页码分页如电商商品做法单独建一张全局索引表order_index(id, shard_table)写入订单时把 id 和对应的表号存进去分页时先查索引表得到 id 列表再去对应分片表查数据优点支持完美跳页。缺点架构复杂有额外写入成本。三、关于游标分页1. 游标分页的局限性① 不能跳页最明显不能直接跳去第 10 页、第 100 页只能上一页 / 下一页前端无法显示「页码条」② 必须依赖连续有序、不重复的字段如 id必须有一个唯一、有序、不可变的字段做游标如果按时间、状态等非唯一字段分页会重复 / 漏数据③ 数据插入 / 删除时页码会错乱比如有人在你翻页时插入了新数据传统 limit 会重复 / 跳数据游标分页不会重复但也无法感知新数据④ 无法反向跳页比如从第 5 页直接回到第 2 页必须一步步往前翻2.既然有局限性为什么哈希分片还必须用游标因为哈希分片 数据被随机打散在多张表limit 100000,10这种分页要去每张表查100000条之后的数据全部拉回应用内存再合并、排序、重新分页性能直接爆炸生产环境绝对禁止结论哈希分片下传统分页不能用 → 只能用游标分页。哪怕有局限性也必须接受。3. 业务上必须跳页、必须页码怎么办3 个企业真正能用的终极方案按推荐程度排序1) 业务改造最简单、最常用99% 的列表根本不需要跳页只需要上一页 / 下一页 / 滚动加载订单列表消息列表日志列表个人中心的记录商品列表滚动加载前端改造去掉页码改成滚动加载。2) 使用范围分片而不是哈希分片推荐如果你的业务必须页码分页比如后台管理系统、商品搜索列表解决方案不要用哈希分片改用范围分片order_1id 1~100 万order_2id 100 万200 万order_3id 200 万300 万优点limit 分页完全正常使用可以跳页、页码、任意查询性能极好缺点会有热点表最新数据都在最后一张表3) 引入搜索引擎ES/Solr做分页最强大真正的大数据高并发系统不分页查 MySQL数据写入时同步到 ES分页、排序、筛选、跳页全部查 ESMySQL 只用来存数据优点支持任意分页、跳页、复杂排序性能极高不影响分库分表缺点架构变复杂有数据同步成本4. 总结游标分页确实有局限性不能跳页、必须依赖唯一有序字段必须使用游标分页的场景分库分表尤其是哈希分片数据量超千万列表滚动加载APP / 小程序最常见订单、消息、日志、流水哈希分片下传统 LIMIT 分页根本不能用因此要滚动列表 →游标分页最优要页码跳页 →改用范围分片要复杂查询 →Elasticsearch
分库分表下的分页查询如何实现
一、哈希分片不能直接分页最头疼的问题普通分页limit 1000,20在哈希分表后完全失效。为什么哈希分片不能直接分页假设把order表哈希分成4 张表order_0、order_1、order_2、order_3数据是均匀打散的order_id1 → 表 1order_id2 → 表 2order_id3 → 表 3order_id4 → 表 0order_id5 → 表 1...普通分页limit 20,10会发生什么中间件必须去每张表查 limit 20,10把 4 张表的结果全部拉回来在内存中重新排序、再分页致命问题深分页完全不能用比如limit 100000,10性能爆炸每张表都要扫描大量数据全部汇总到内存结果不稳定、重复、错乱二、3种解决方案从简单到企业级1. 业务层禁止深分页最简单适用场景后台管理、不需要跳页的列表。做法只提供上一页 / 下一页不提供跳转到第 100 页的功能前端限制最大偏移量比如最多查 1000 条优点几乎不用改代码。缺点体验差不能跳页。2.游标分页最好的通用方案游标分页不用页码用「上一页最后一条数据的唯一标识ID」来查下一页。哈希分片下 90% 企业的标准方案也叫滚动分页。核心原理不用limit offset而是用上一页最后一条数据的 ID来定位下一页。-- 第一页 SELECT * FROM order WHERE id 0 ORDER BY id ASC LIMIT 10; -- 第二页上一页最后一条 id 100 SELECT * FROM order WHERE id 100 ORDER BY id ASC LIMIT 10;为什么哈希分片能用分片键 id每张表只需要查where id 游标不需要全表扫描不需要大量数据汇总性能极高优点性能最好无论分多少张表都稳定代码改造简单缺点不能跳页只能上 / 下一页3. 全局统一索引表支持跳页复杂适用场景必须跳页、必须页码分页如电商商品做法单独建一张全局索引表order_index(id, shard_table)写入订单时把 id 和对应的表号存进去分页时先查索引表得到 id 列表再去对应分片表查数据优点支持完美跳页。缺点架构复杂有额外写入成本。三、关于游标分页1. 游标分页的局限性① 不能跳页最明显不能直接跳去第 10 页、第 100 页只能上一页 / 下一页前端无法显示「页码条」② 必须依赖连续有序、不重复的字段如 id必须有一个唯一、有序、不可变的字段做游标如果按时间、状态等非唯一字段分页会重复 / 漏数据③ 数据插入 / 删除时页码会错乱比如有人在你翻页时插入了新数据传统 limit 会重复 / 跳数据游标分页不会重复但也无法感知新数据④ 无法反向跳页比如从第 5 页直接回到第 2 页必须一步步往前翻2.既然有局限性为什么哈希分片还必须用游标因为哈希分片 数据被随机打散在多张表limit 100000,10这种分页要去每张表查100000条之后的数据全部拉回应用内存再合并、排序、重新分页性能直接爆炸生产环境绝对禁止结论哈希分片下传统分页不能用 → 只能用游标分页。哪怕有局限性也必须接受。3. 业务上必须跳页、必须页码怎么办3 个企业真正能用的终极方案按推荐程度排序1) 业务改造最简单、最常用99% 的列表根本不需要跳页只需要上一页 / 下一页 / 滚动加载订单列表消息列表日志列表个人中心的记录商品列表滚动加载前端改造去掉页码改成滚动加载。2) 使用范围分片而不是哈希分片推荐如果你的业务必须页码分页比如后台管理系统、商品搜索列表解决方案不要用哈希分片改用范围分片order_1id 1~100 万order_2id 100 万200 万order_3id 200 万300 万优点limit 分页完全正常使用可以跳页、页码、任意查询性能极好缺点会有热点表最新数据都在最后一张表3) 引入搜索引擎ES/Solr做分页最强大真正的大数据高并发系统不分页查 MySQL数据写入时同步到 ES分页、排序、筛选、跳页全部查 ESMySQL 只用来存数据优点支持任意分页、跳页、复杂排序性能极高不影响分库分表缺点架构变复杂有数据同步成本4. 总结游标分页确实有局限性不能跳页、必须依赖唯一有序字段必须使用游标分页的场景分库分表尤其是哈希分片数据量超千万列表滚动加载APP / 小程序最常见订单、消息、日志、流水哈希分片下传统 LIMIT 分页根本不能用因此要滚动列表 →游标分页最优要页码跳页 →改用范围分片要复杂查询 →Elasticsearch
