分库分表方案

一、为什么要分库分表？

答案很简单：数据库出现性能瓶颈。用大白话来说就是数据库快扛不住了。

• 大量请求阻塞
  在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态。
• SQL 操作变慢
  如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描，这个查询耗时会非常久。
• 存储出现问题
  业务量剧增，单库数据量越来越大，给存储造成巨大压力。

从机器的角度看，性能瓶颈无非就是CPU、内存、磁盘、网络这些，要解决性能瓶颈最简单粗暴的办法就是提升机器性能，但是通过这种方法成本和收益投入比往往又太高了，不划算，所以重点还是要从软件角度入手。

二、数据库相关优化方案

数据库优化方案主要分为两大类：软件层面、硬件层面。

硬件层面主要是增加机器性能。
软件层面包括：SQL 调优、表结构优化、读写分离、数据库集群、分库分表等。

2.1 SQL 调优

SQL 调优往往是解决数据库问题的第一步，往往投入少部分精力就能获得较大的收益。

SQL 调优主要目的是尽可能的让那些慢 SQL 变快，手段其实也很简单就是让 SQL 执行尽量命中索引。

具体的：开启慢 SQL 记录，如果你使用的是 Mysql，需要在 Mysql 配置文件中配置几个参数即可。

调优的工具：
常常会用到 explain 这个命令来查看 SQL 语句的执行计划，通过观察执行结果很容易就知道该 SQL 语句是不是全表扫描、有没有命中索引。

2.2 表结构优化

举个例子：
“user”表中有 user_id、nickname 等字段，“order”表中有order_id、user_id等字段，如果想拿到用户昵称怎么办？一般情况是通过 join 关联表操作，在查询订单表时关联查询用户表，从而获取导用户昵称。

但是随着业务量增加，订单表和用户表肯定也是暴增，这时候通过两个表关联数据就比较费力了，为了取一个昵称字段而不得不关联查询几十上百万的用户表，其速度可想而知。

这个时候可以尝试将 nickname 这个字段加到 order 表中（order_id、user_id、nickname），这种做法通常叫做数据库表冗余字段。这样做的好处展示订单列表时不需要再关联查询用户表了。

冗余字段的做法也有一个弊端，如果这个字段更新会同时涉及到多个表的更新，因此在选择冗余字段时要尽量选择不经常更新的字段。

2.3 架构优化

当单台数据库实例扛不住，我们可以增加实例组成集群对外服务。

当发现读请求明显多于写请求时，我们可以让主实例负责写，从实例对外提供读的能力；

如果读实例压力依然很大，可以在数据库前面加入缓存如 redis，让请求优先从缓存取数据减少数据库访问。

缓存分担了部分压力后，数据库依然是瓶颈，这个时候就可以考虑分库分表的方案了，后面会详细介绍。

三、分库

水平分库

• 【概念】：以字段 为依据，按照一定策略（hash、range等），将一个库 中的数据拆分到多个库 中。
• 【结果】：
  每个库 的结构 都一样；
  每个库 的数据 都不一样，没有交集；
  所有库 的并集 是全量数据；
• 【场景】：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
• 【分析】：库多了，io和cpu的压力自然可以成倍缓解。

垂直分库

• 【概念】：以表 为依据，按照业务归属不同，将不同的表 拆分到不同的库
• 【结果】：
  每个库 的结构 都不一样；
  每个库 的数据 也不一样，没有交集；
  所有库 的并集 是全量数据；
• 【场景】：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
• 【分析】：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

四、分表

水平分表

【概念】：以字段 为依据，按照一定策略（hash、range等），将一个表 中的数据拆分到多个表 中。
【结果】：
每个表 的结构 都一样；
每个表 的数据 都不一样，没有交集；
所有表 的并集 是全量数据；
【场景】：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
【分析】：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

垂直分表

• 【概念】：以字段 为依据，按照字段的活跃性，将表 中字段拆到不同的表 （主表和扩展表）中。
• 【结果】：
  每个表 的结构 都不一样；
  每个表 的数据 也不一样，一般来说，每个表的字段 至少有一列交集，一般是主键，用于关联数据；
  所有表 的并集 是全量数据；
• 【场景】：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
• 【分析】：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

五、分库分表工具

• sharding-sphere：jar，前身是sharding-jdbc；
• TDDL：jar，Taobao Distribute Data Layer；
• Mycat：中间件。

六、分库分表步骤

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）。

分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？）。且不可为了分库分表而拆分。
选key很重要，既要考虑到拆分均匀，也要考虑到非partition key的查询。
只要能满足需求，拆分规则越简单越好。

七、分库分表举例

以一个商城系统为例逐步讲解数据库是如何一步步演进。

单应用单数据库

基本就是一个系统包含多个基础功能模块，最后打包成一个 war 包部署，这就是典型的单体架构应用。

如上图，商城系统包括主页 Portal 模板、用户模块、订单模块、库存模块等，所有的模块都共有一个数据库，通常数据库中有非常多的表。

因为用户量不大，这样的架构在早期完全适用。

多应用单数据库

在前期为了抢占市场，这一套系统不停地迭代更新，代码量越来越大，架构也变得越来越臃肿，现在随着系统访问压力逐渐增加，系统拆分就势在必行了。

为了保证业务平滑，系统架构重构也是分了几个阶段进行。

第一个阶段将商城系统单体架构按照功能模块拆分为子服务，比如：Portal 服务、用户服务、订单服务、库存服务等。

多个服务共享一个数据库，这样做的目的是底层数据库访问逻辑可以不用动，将影响降到最低。

多应用多数据库

随着业务推广力度加大，数据库终于成为了瓶颈，这个时候多个服务共享一个数据库基本不可行了。我们需要将每个服务相关的表拆出来单独建立一个数据库，这其实就是“分库”了。

单数据库的能够支撑的并发量是有限的，拆成多个库可以使服务间不用竞争，提升服务的性能。

从一个大的数据中分出多个小的数据库，每个服务都对应一个数据库，这就是系统发展到一定阶段必要要做的“分库”操作。

现在非常火的微服务架构也是一样的，如果只拆分应用不拆分数据库，不能解决根本问题，整个系统也很容易达到瓶颈。

分表

说完了分库，那什么时候分表呢？

如果系统处于高速发展阶段，拿商城系统来说，一天下单量可能几十万，那数据库中的订单表增长就特别快，增长到一定阶段数据库查询效率就会出现明显下降。

因此，当单表数据增量过快，业界流传是超过500万的数据量就要考虑分表了(当然500万只是一个经验值，大家可以根据实际情况做出决策)。

上面介绍了，分表有两个维度，一是水平切分和垂直切分。

水平拆分和垂直拆分

就拿用户表（user）来说，表中有7个字段：id,name,age,sex,nickname,description。

先说垂直拆分，如果 nickname 和 description 不常用，我们可以将其拆分为另外一张表：用户详细信息表，这样就由一张用户表拆分为了用户基本信息表+用户详细信息表，两张表结构不一样相互独立。但是从这个角度来看垂直拆分并没有从根本上解决单表数据量过大的问题，因此我们还是需要做一次水平拆分。

比如表中有一万条数据，我们拆分为两张表，id 为奇数的：1，3，5，7……放在 user1， id 为偶数的：2，4，6，8……放在 user2中，这样的拆分办法就是水平拆分了。

水平拆分的方式也很多，除了上面说的按照 id 拆表，还可以按照时间维度取拆分，比如订单表，可以按每日、每月等进行拆分。

单库内拆分和多库拆分

拿水平拆分为例，每张表都拆分为了多个子表，多个子表存在于同一数据库中。比如下面用户表拆分为用户1表、用户2表。

在一个数据库中将一张表拆分为几个子表在一定程度上可以解决单表查询性能的问题，但是也会遇到一个问题：单数据库存储瓶颈。

所以在业界用的更多的还是将子表拆分到多个数据库中。比如下图中，用户表拆分为两个子表，两个子表分别存在于不同的数据库中。

一句话总结：分表主要是为了减少单张表的大小，解决单表数据量带来的性能问题。

八、分库分表带来的复杂性

既然分库分表这么好，那是不是在项目初期就应该采用这种方案呢？显然不是，分库分表的确解决了很多问题，但是也给系统带来了很多复杂性。

跨库关联查询

在单库未拆分表之前，我们可以很方便使用 join 操作关联多张表查询数据，但是经过分库分表后两张表可能都不在一个数据库中，如何使用 join 呢？

有几种方案可以解决：

• 字段冗余：把需要关联的字段放入主表中，避免 join 操作；
• 数据抽象：通过ETL等将数据汇合聚集，生成新的表；
• 全局表：比如一些基础表可以在每个数据库中都放一份；
• 应用层组装：将基础数据查出来，通过应用程序计算组装；

分布式事务

单数据库可以用本地事务搞定，使用多数据库就只能通过分布式事务解决了。

常用解决方案有：基于可靠消息（MQ）的解决方案、两阶段事务提交、柔性事务等。

排序、分页、函数计算问题

在使用 SQL 时 order by， limit 等关键字需要特殊处理，一般来说采用分片的思路：

先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果。

分布式 ID

常用的分布式 ID 解决方案有：

• UUID
• 基于数据库自增单独维护一张 ID表
• 号段模式
• Redis 缓存
• 雪花算法（Snowflake）
• 百度uid-generator
• 美团Leaf
• 滴滴Tinyid

多数据源

分库分表之后可能会面临从多个数据库或多个子表中获取数据，一般的解决思路有：客户端适配和代理层适配。

业界常用的中间件有：

• shardingsphere（前身 sharding-jdbc）
• Mycat

Sharding-JDBC

Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。 它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

sharding-jdbc后续发展为Sharding-Sphere，包含sharding-jdbc、Sharding-Proxy、Sharding-Sidecar

Sharding-JDBC直接封装JDBC API，可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零：可适用于任何基于Java的ORM框架，如JPA、Hibernate、Mybatis、Spring JDBC Template或直接使用JDBC。可基于任何第三方的数据库连接池，如DBCP、C3P0、 BoneCP、Druid等。

3.0之后，增加了类似mycat角色的sharding-proxy无状态服务器（代理层可以有，但是不应该作为应用直接访问的入口，如下图所示），以及类似于Service Mesh的Database Mesh。不过核心没有变化，对SQL语法增加了部分新的支持。所以3.0的架构如下：

就分库分表核心来说，我们就只要关心sharding-jdbc就可以了，不需要关心sharding-sphere的其他部分。

分片规则配置

Sharding-JDBC的分片逻辑非常灵活，支持分片策略自定义、复数分片键、多运算符分片等功能。

如：根据用户ID分库，根据订单ID分表这种分库分表结合的分片策略；或根据年分库，月份+用户区域ID分表这样的多片键分片。

Sharding-JDBC除了支持等号运算符进行分片，还支持in/between运算符分片，提供了更加强大的分片功能。

Sharding-JDBC提供了spring命名空间用于简化配置，以及规则引擎用于简化策略编写。

JDBC规范重写

Sharding-JDBC对JDBC规范的重写思路是针对DataSource、Connection、Statement、PreparedStatement和ResultSet五个核心接口封装，将多个真实JDBC实现类集合（如：MySQL JDBC实现/DBCP JDBC实现等）纳入Sharding-JDBC实现类管理。

Sharding-JDBC尽量最大化实现JDBC协议，包括addBatch这种在JPA中会使用的批量更新功能。但分片JDBC毕竟与原生JDBC不同，所以目前仍有未实现的接口，包括Connection游标，存储过程和savePoint相关、ResultSet向前遍历和修改等不太常用的功能。此外，为了保证兼容性，并未实现JDBC 4.1及其后发布的接口（如：DBCP 1.x版本不支持JDBC 4.1）。

SQL解析

SQL解析作为分库分表类产品的核心，性能和兼容性是最重要的衡量指标。目前常见的SQL解析器主要有fdb/jsqlparser和Druid。Sharding-JDBC使用Druid作为SQL解析器，经实际测试，Druid解析速度是另外两个解析器的几十倍。

目前Sharding-JDBC支持join、aggregation（包括avg）、order by、 group by、limit、甚至or查询等复杂SQL的解析。目前不支持union、部分子查询、函数内分片等不太应在分片场景中出现的SQL解析。

SQL改写

SQL改写分为两部分，一部分是将分表的逻辑表名称替换为真实表名称。另一部分是根据SQL解析结果替换一些在分片环境中不正确的功能。这里具两个例子：

第1个例子是avg计算。在分片的环境中，以avg1 +avg2+avg3/3计算平均值并不正确，需要改写为（sum1+sum2+sum3）/（count1+count2+ count3）。这就需要将包含avg的SQL改写为sum和count，然后再结果归并时重新计算平均值。

第2个例子是分页。假设每10条数据为一页，取第2页数据。在分片环境下获取limit 10, 10，归并之后再根据排序条件取出前10条数据是不正确的结果。正确的做法是将分条件改写为limit 0, 20，取出所有前2页数据，再结合排序条件算出正确的数据。可以看到越是靠后的Limit分页效率就会越低，也越浪费内存。有很多方法可避免使用limit进行分页，比如构建记录行记录数和行偏移量的二级索引，或使用上次分页数据结尾ID作为下次查询条件的分页方式。

SQL路由

SQL路由是根据分片规则配置，将SQL定位至真正的数据源。主要分为单表路由、Binding表路由和笛卡尔积路由。

单表路由最为简单，但路由结果不一定落入唯一库（表），因为支持根据between和in这样的操作符进行分片，所以最终结果仍然可能落入多个库（表）。

Binding表可理解为分库分表规则完全一致的主从表。举例说明：订单表和订单详情表都根据订单ID作为分片键，任意时刻分片逻辑均相同。这样的关联查询和单表查询难度和性能相当。

笛卡尔积查询最为复杂，因为无法根据Binding关系定位分片规则的一致性，所以非Binding表的关联查询需要拆解为笛卡尔积组合执行。查询性能较低，而且数据库连接数较高，需谨慎使用。

SQL执行

路由至真实数据源后，Sharding-JDBC将采用多线程并发执行SQL，并完成对addBatch等批量方法的处理。

结果归并

结果归并包括4类：普通遍历类、排序类、聚合类和分组类。每种类型都会先根据分页结果跳过不需要的数据。

普通遍历类最为简单，只需按顺序遍历ResultSet的集合即可。

排序类结果将结果先排序再输出，因为各分片结果均按照各自条件完成排序，所以采用归并排序算法整合最终结果。

聚合类分为3种类型，比较型、累加型和平均值型。比较型包括max和min，只返回最大（小）结果。累加型包括sum和count，需要将结果累加后返回。平均值则是通过SQL改写的sum和count计算，相关内容已在SQL改写涵盖，不再赘述。

分组类最为复杂，需要将所有的ResultSet结果放入内存，使用map-reduce算法分组，最后根据排序和聚合条件做相关处理。最消耗内存，最损失性能的部分即是此，可以考虑使用limit合理的限制分组数据大小。

结果归并部分目前并未采用管道解析的方式，之后会针对这里做更多改进。

集成

1. 引入maven依赖

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>${latest.release.version}</version>
</dependency>

2. 规则配置
   Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。详情请参见配置手册。
3. 创建DataSource
   通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。

DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig);

4. Sharding-JDBC的分库分表通过规则配置描述，以下例子是根据user_id取模分库, 且根据order_id取模分表的两库两表的配置。
   基于Yaml的规则配置:

dataSources:
  ds0: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds0
    username: root
    password: 
  ds1: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds1
    username: root
    password: 

tables:
  t_order: 
    actualDataNodes: ds${0..1}.t_order${0..1}
    databaseStrategy: 
      inline:
        shardingColumn: user_id
        algorithmInlineExpression: ds${user_id % 2}
    tableStrategy: 
      inline:
        shardingColumn: order_id
        algorithmInlineExpression: t_order${order_id % 2}
  t_order_item: 
    actualDataNodes: ds${0..1}.t_order_item${0..1}
    databaseStrategy: 
      inline:
        shardingColumn: user_id
        algorithmInlineExpression: ds${user_id % 2}
    tableStrategy: 
      inline:
        shardingColumn: order_id
        algorithmInlineExpression: t_order_item${order_id % 2}

总结：

如果出现数据库问题不要着急分库分表，先看一下使用常规手段是否能够解决。

分库分表会给系统带来巨大的复杂性，不是万不得已建议不要提前使用。作为系统架构师可以让系统灵活性和可扩展性强，但是不要过度设计和超前设计。

补充一：mycat和sharding-jdbc区别

• sharding-jdbc作为一个组件集成在应用内，而mycat则作为一个独立的应用需要单独部署
• 使用mycat时不需要改代码，而使用sharding-jdbc时需要修改代码
• 从架构上看sharding-jdbc更符合分布式架构的设计，直连数据库，没有中间应用，理论性能是最高的（实际性能需要结合具体的代码实现，理论性能可以理解为上限，通过不断优化代码实现，逐渐接近理论性能）。同时缺点也很明显，由于作为组件存在，需要集成在应用内，意味着作为使用方，必须要集成到代码里，使得开发成本相对较高；另一方面，由于需要集成在应用内，使得需要针对不同语言（java、C、PHP……）有不同的实现（事实上sharding-jdbc目前只支持java），这样组件本身的维护成本也会很高。最终将应用场景限定在由java开发的应用这一种场景下。