百亿级数据分表后怎么分页查询? 分表数据汇总

当业务规模达到一定规模之后，数据库面对海量的数据压力，根据传统的优化经验，我们主要的优化方案会有如下几点:

1、创建适当的索引

通过创建适当的索引，可以加速查询操作。索引可以提高查询语句的执行效率，尤其是对于常用的查询条件和排序字段进行索引，可以显著减少查询的扫描范围和 IO 开销。

2、优化查询语句

优化查询语句本身，避免全表扫描和大数据量的关联查询。可以优化查询条件，使用合适的索引、合理的查询策略，减少不必要的字段和数据返回。

3、缓存查询结果

对于一些相对稳定的查询结果，可以将其缓存在内存中，避免重复查询数据库，提高查询速度。

缓存的查询速度一定比直接查询数据库的效率高，这是因为缓存具备以下特征：

1.内存访问速度快：缓存通常将数据存储在内存中，而数据库将数据存储在磁盘上。相比于磁盘访问，内存访问速度更快，可以达到纳秒级别的读取速度，远远快于数据库的毫秒级别的读取速度。

2.IO 操作次数少：数据库通常需要进行磁盘 IO 操作，包括读取和写入磁盘数据。而缓存将数据存储在内存中，避免了磁盘 IO 的开销。内存访问不需要进行磁盘寻址和机械运动，相对来说速度更快。

3.特殊的数据结构：缓存的数据结构通常为 key-value 形式的，也就是说缓存可以做到任何数据量级下的查询数据复杂度为 O(1)，所以它的查询效率是非常高的；而数据库采用的是传统数据结构设计，可能需要查询二叉树、或全文搜索、或回表查询等操作，所以其查询性能是远低于缓存系统的。

4、提升硬件配置

对于大数据量的表，可以考虑采用更高性能的硬件设备，如更快的存储介质（如固态硬盘），更大的内存容量等，以提升查询的 IO 性能。

上述4点其实还是比较常规和传统的，随着近几年微服务体系的快速发展，分库分表的思想越来越流行，也成为解决大数据量下的首选了。

像淘宝日订单量在5000万单以上，美团3000万单以上，这种数据规模的数据库架构默认即为分库分表，不过，事物都有2面性，而分库分表之后一些常规的查询可能都会产生问题，最常见的就是比如分页查询的问题。一般我们把分表的字段称作shardingkey，比如订单表按照用户ID作为shardingkey，那么如果查询条件中不带用户ID查询怎么做分页？又比如更多的多维度的查询都没有shardingkey又怎么查询？这种分页是项目中常见而又比较棘手的问题。

在讲述主题之前，首先了解下分布式环境下关于唯一主键和分表的内容。这2点是本次内容的基础。

唯一主键

一般我们数据库的主键都是自增的，那么分表之后主键冲突的问题就是一个无法避免的问题，最简单的办法就是以一个唯一的业务字段作为唯一的主键，比如订单表的订单号肯定是全局唯一的。

常见的分布式生成唯一ID的方式很多，最常见的雪花算法Snowflake、滴滴Tinyid、美团Leaf。

雪花算法一共64位数。

第一位不使用，默认都是0，41位时间戳精确到毫秒，可以容纳69年的时间，10位工作机器ID高5位是数据中心ID，低5位是节点ID，12位序列号每个节点每毫秒累加，累计可以达到2^12 4096个ID。

更多雪花详细内容可查看之前的公众号内容：雪花算法及分布式主键生成策略详解

表的唯一标识解决了，然后就是分表的策略。

分表

说到分表，我们必须明确分表的时机，也并不是遇到性能问题就直接进行分表。

总体来说：当性能出现瓶颈，并且其他优化手段无法很好的解决的时候。

我们必须首先明确分库分表一般是作为最终的解决手段，我们会优先使用其他的方法来进行优化。常见的优化手段在文章开头已经说了，这里就不再进行重复。当我们使用这些手段都无法解决的时候，就需要来考虑分库分表。

分表的首要核心就是根据自身的业务量和增量来考虑分表的大小。

举个例子，现在我们日单量是10万单，预估一年后可以达到日100万单，根据业务属性，一般我们就支持查询半年内的订单，超过半年的订单需要做归档处理。

那么以日订单100万半年的数量级来看，不分表的话我们订单量将达到100万X180=1.8亿，以这个数据量级部分表的话肯定单表是扛不住的，就算你能扛，对应的响应时间也根本无法接受吧。根据经验单表几百万的数量对于数据库是没什么压力的，那么只要分256张表就足够了，1.8亿/256≈70万，如果为了保险起见，也可以分到512张表。那么考虑一下，如果业务量再增长10倍达到1000万单每天，分表1024就是比较合适的选择。

通过分表加上超过半年的数据归档之后，单表70万的数据就足以应对大部分场景了。接下来对订单号hash，然后对256取模的就可以落到具体的哪张表了。

那么，因为唯一主键都是以订单号作为依据，以前写的那些根据主键ID做查询的就不能用了，这就涉及到了历史一些查询功能的修改。不过这都不是事儿对吧，都改成以订单号来查就行了。显然这都不是什么大问题，而真正的问题就是本次要讲的内容。

说到了分表，关于Sharding key必须注意如下内容：

1>Sharding key的设计目标：

合理选择 Sharding key，避免大多数的查询变成重量级操作，

比如：跨库查询 和 全表路由

我的建议是：

合理选择 Sharding key， 尽一切可能减少全表路由、跨库查询， 从而使得大部分查询在 单库实现结果闭环，从而减少 多库之间大的数 据合并和二次排序， 从而提升分库分表的吞吐量和性能。

2>分片键的设计建议：

• 选择具有共性的字段作为分片键，即查询中高频出现的条件字段；
• 分片字段应具有高度离散的特点，分片键的内容不能被更新；
• 可均匀各分片的数据存储和读写压力，避免片内出现热点数据；
• 尽量减少单次查询所涉及的分片数量，降低数据库压力；
• 最后，不要更换分片键，更换分片键需重分布数据，代价较大。

对于分表的经验，更多的还是技术与经验相结合，这样就更容易理解。

接下来我们进入本次的主题。

数据(C端)查询

说了半天，总算到了正题了，那么分表之后查询和分页查询的问题怎么解决？

首先说带shardingkey的查询，比如就通过订单号查询，不管你分页还是怎么样都是能直接定位到具体的表来查询的，显然查询是不会有什么问题的。

如果不是shardingkey的话，上面举例说的以订单号作为shardingkey的话，像APP、小程序这种一般都是通过用户ID查询，那这时候我们通过订单号做的sharding怎么办？很多公司订单表直接用用户ID做shardingkey，那么很简单，直接查就完了。那么订单号怎么办，一个很简单的办法就是在订单号上带上用户ID的属性。举个很简单的例子，原本41位的时间戳你觉得用不完，用户ID是10位的，订单号的生成规则带上用户ID，落具体表的时候根据订单号中10位用户ID hash取模，这样无论根据订单号还是用户ID查询效果都是一样的。

当然，这种方式只是举例，具体的订单号生成的规则，多少位，包含哪些因素根据自己的业务和实现机制来决定。如图:

那么无论你是订单号还是用户ID作为shardingkey，按照以上的两种方式都可以解决问题了。还有一个问题就是如果既不是订单号又不是用户ID查询怎么办？最直观的例子就是来自商户端或者后台的查询，商户端都是以商户或者说卖家的ID作为查询条件来查的，后台的查询条件可能就更复杂了，像我碰到的有些后台查询条件能有几十个，这怎么查？接下来分开说B端和后台的复杂查询。

现实中真正的流量大头都是来自于用户端C端，所以本质上解决了用户端的问题，这个问题就解了大半，剩下来自商户卖家端B端、后台支持运营业务的查询流量并不会很大，这个问题就好解。

其他端查询

针对B端的非shardingkey的查询有两个办法解决。

双写，双写就是下单的数据落两份，C端和B端的各自保存一份，C端用你可以用单号、用户ID做shardingkey都行，B端就用商家卖家的ID作为shardingkey就好了。有些人可能会说了，你双写不影响性能吗？因为对于B端来说轻微的延迟是可以接受的，所以可以采取异步的方式去落B端订单。你想想你去淘宝买个东西下单了，卖家稍微延迟个一两秒收到这个订单的消息有什么关系吗？你点个外卖商户晚一两秒收到这个订单有什么太大影响吗？

这是一个解决方案，另外一个方案就是走离线数仓或者ES查询，订单数据落库之后，不管你通过binlog还是MQ消息的都形式，把数据同步到数仓或者ES，他们支持的数量级对于这种查询条件来说就很简单了。同样这种方式肯定是稍微有延迟的，但是这种可控范围的延迟是可以接受的。

而针对管理后台的查询，比如运营、业务、产品需要看数据，他们天然需要复杂的查询条件，同样走ES或者数仓都可以做得到。如果不用这个方案，又要不带shardingkey的分页查询，那么就只能扫全表查询聚合数据，然后手动做分页了，但是这样查出来的结果是有限制的。

比如你256个片，查询的时候循环扫描所有的分片，每个片取20条数据，最后聚合数据手工分页，那必然是不可能查到全量的数据的。

总结

分库分表后的查询问题，对于有经验的人来说其实这个问题都知道，但是我相信其实大部分人做的业务可能都没来到这个数量级，分库分表可能都停留在概念阶段，面试被问到后就手足无措了，因为没有经验不知道怎么办。

对于基于shardingkey的查询我们可以很简单的解决，对于非shardingkey的查询可以通过落双份数据和数仓、ES的方案来解决，当然，如果分表后数据量很小的话，建好索引，扫全表查询其实也不是什么问题。

以上为全部内容。