分库分表(1) --- ShardingSphere(理论)

2023-09-27 23:08:28

分库分表---理论

当一张表的数据达到几千万时，查询一次所花的时间会变长。业界公认MySQL单表容量在 1千万 以下是最佳状态，因为这时它的BTREE索引树高在3~5之间。

数据切分可以分为：垂直切分和水平切分。

一、垂直切分

垂直切分又可以分为: 垂直分库和垂直分表。

1、垂直分库

概念 就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，

每个微服务使用单独的一个数据库。

如图：

说明

一开始我们是单体服务，所以只有一个数据库，所有的表都在这个库里。

后来因为业务需求,单体服务变成微服务治理。所以将之前的一个商品库，拆分成多个数据库。每个微服务对于一个数据库。

2、垂直分表

概念 把一个表的多个字段分别拆成多个表，一般按字段的冷热拆分，热字段一个表，冷字段一个表。从而提升了数据库性能。

如图：

说明

一开始商品表中包含商品的所有字段，但是我们发现:

1.商品详情和商品属性字段较长。2.商品列表的时候我们是不需要显示商品详情和商品属性信息，只有在点进商品商品的时候才会展示商品详情信息。

所以可以考虑把商品详情和商品属性单独切分一张表，提高查询效率。

3、垂直切分优缺点

优点

- 解决业务系统层面的耦合，业务清晰
- 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点

- 分库后无法Join，只能通过接口聚合方式解决，提升了开发的复杂度
- 分库后分布式事务处理复杂
- 依然存在单表数据量过大的问题（需要水平切分）

二、水平切分

当一个应用难以再细粒度的垂直切分或切分后数据量行数巨大,存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分也可以分为：水平分库和水平分表。

1、水平分库

水平分库的原因

上面虽然已经把商品库分成3个库，但是随着业务的增加一个订单库也出现QPS过高，数据库响应速度来不及,一般mysql单机也就1000左右的QPS，如果超过1000就要考虑分库。

如图

2、水平分表

概念 一般我们一张表的数据不要超过1千万,如果表数据超过1千万，并且还在不断增加数据，那就可以考虑分表。

如图

3、水平切分优缺点

优点

- 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
- 应用端改造较小，不需要拆分业务模块

缺点

- 跨分片的事务一致性难以保证
- 跨库的Join关联查询性能较差
- 数据多次扩展难度和维护量极大

三、数据分片规则

我们我们考虑去水平切分表，将一张表水平切分成多张表，这就涉及到数据分片的规则，比较常见的有：Hash取模分表、数值Range分表、一致性Hash算法分表。

1、Hash取模分表

概念 一般采用Hash取模的切分方式，例如：假设按goods_id分4张表。（goods_id%4 取整确定表）

优点

 - 数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。

缺点

- 后期分片集群扩容时，需要迁移旧的数据很难。
- 容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带goods_id时,将会导致无法定位数据库,从而需要同时向4个库发起查询，
再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

2、数值Range分表

概念 按照时间区间或ID区间来切分。例如：将goods_id为1_{1000的记录分到第一个表，1001}2000的分到第二个表，以此类推。

如图

优点

- 单表大小可控
- 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
- 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点

- 热点数据成为性能瓶颈。
例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

3、一致性Hash算法

一致性Hash算法能很好的解决因为Hash取模而产生的分片集群扩容时，需要迁移旧的数据的难题。至于具体原理这里就不详细说，

可以参考一篇博客：一致性哈希算法(分库分表，负载均衡等)

四、分库分表带来的问题

任何事情都有两面性，分库分表也不例外，如果采用分库分表，会引入新的的问题

1、分布式事务问题

使用分布式事务中间件解决，具体是通过最终一致性还是强一致性分布式事务，看业务需求，这里就不多说。

2、跨节点关联查询 Join 问题

切分之前，我们可以通过Join来完成。而切分之后，数据可能分布在不同的节点上，此时Join带来的问题就比较麻烦了，考虑到性能，尽量避免使用Join查询。

解决这个问题的一些方法：

全局表

全局表，也可看做是 "数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库Join查询，可以将 这类表在每个数据库中都保存一份。这些数据通常

很少会进行修改，所以也不担心一致性的问题。

字段冗余

利用空间换时间，为了性能而避免join查询。例：订单表保存userId时候,也将userName冗余保存一份，这样查询订单详情时就不需要再去查询"买家user表"了。

数据组装

在系统层面，分两次查询。第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

3、跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现Limit分页、Order by排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；

当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。

4、全局主键避重问题

如果都用主键自增肯定不合理，如果用UUID那么无法做到根据主键排序，所以我们可以考虑通过雪花ID来作为数据库的主键，

有关雪花ID可以参考我之前写的博客：静态内部类单例模式实现雪花算法

5、数据迁移问题

采用双写的方式，修改代码，所有涉及到分库分表的表的增、删、改的代码，都要对新库进行增删改。同时，再有一个数据抽取服务，不断地从老库抽数据，往新库写，

边写边按时间比较数据是不是最新的。

ShardingSphere---理论

ShardingSphere在中小企业需要分库分表的时候用的会比较多，因为它维护成本低，不需要额外增派人手;而且目前社区也还一直在开发和维护，还算是比较活跃。

但是中大型公司一般会选择选用 Mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 Mycat，

然后大量项目直接透明使用即可。

一、ShardingSphere概念

1、概念

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar这3款相互独立的产品组成。

他们均提供标准化的数据分片、分布式事务 和 数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

如图

2、功能列表

数据分片

- 分库 & 分表
- 读写分离
- 分片策略定制化
- 无中心化分布式主键

分布式事务

- 标准化事务接口
- XA强一致事务
- 柔性事务

数据库治理

- 配置动态化
- 编排 & 治理
- 数据脱敏
- 可视化链路追踪
- 弹性伸缩(规划中)

3、项目状态

二、分库分表---结果归并

概念 将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。

我们在实现分库分表之后，遍历、排序、分组、分页 和 聚合 操作变成不在一张表上进行SQL,而是多张表执行的结果进行归并。

所以我们来看下ShardingSphere实现这些操作的原理。

1、遍历归并

它是最为简单的归并方式。只需将多个数据结果集合并为一个单向链表即可。在遍历完成链表中当前数据结果集之后，将链表元素后移一位，继续遍历下一个数据结果集即可。

2、排序归并

由于在SQL中存在ORDER BY语句，每个数据结果集自身是有序的，所以我们要做的就是对多个有序的数组进行排序

ShardingSphere在对排序的查询进行归并时，将每个结果集的当前数据值进行比较（通过实现Java的Comparable接口完成），并将其放入优先级队列。

每次获取下一条数据时，只需将队列顶端结果集的游标下移，并根据新游标重新进入优先级排序队列找到自己的位置即可。

举例

下图是一个通过分数进行排序的示例图。图中展示了3张表返回的数据结果集，每个数据结果集已经根据分数排序完毕，但是3个数据结果集之间是无序的。

将3个数据结果集的当前游标指向的数据值进行排序，并放入优先级队列，t_score_0的第一个数据值最大，t_score_2的第一个数据值次之，t_score_1的第一个数据值最小，

因此优先级队列根据t_score_0，t_score_2和t_score_1的方式排序队列。

如图

下图则展现了进行next调用的时候，排序归并是如何进行的。通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_0将会被弹出队列，并且将当前

游标指向的数据值（也就是100）返回至查询客户端，并且将游标下移一位(90)之后，重新放入优先级队列。根据当前数值，t_score_0排列在队列的最后一位。之前队列中

排名第二的t_score_2的数据结果集则自动排在了队列首位。

在进行第二次next时，只需要将目前排列在队列首位的t_score_2弹出队列，并且将其数据结果集游标指向的值返回至客户端，并下移游标，继续加入队列排队，以此类推。

当一个结果集中已经没有数据了，则无需再次加入队列。

可以看到，ShardingSphere的排序归并，是在维护数据结果集的纵轴和横轴这两个维度的有序性。

纵轴是指每个数据结果集本身，它是天然有序的，它通过包含ORDER BY的SQL所获取。

横轴是指每个数据结果集当前游标所指向的值,它需要通过优先级队列来维护其正确顺序。每一次数据结果集当前游标的下移都需要将该数据结果集重新放入优先级队列排序，

而只有排列在队列首位的数据结果集才可能发生游标下移的操作。

3 、分组归并

分组归并的情况最为复杂，它分为流式分组归并和内存分组归并。流式分组归并要求SQL的排序项与分组项的字段以及排序类型（ASC或DESC）必须保持一致，否则只能

通过内存归并才能保证其数据的正确性。

举例

假设根据科目分片，表结构中包含考生的姓名（为了简单起见，不考虑重名的情况）和分数。通过SQL获取每位考生的总分，可通过如下SQL：

SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;

在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游标所指向的数据值，因此可以采用流式归并。如下图所示

进行归并时，逻辑与排序归并类似。下图展现了进行next调用的时候，流式分组归并是如何进行的。

通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_java将会被弹出队列，并且将分组值同为“Jetty”的其他结果集中的数据一同弹出队列。在获取了

所有的姓名为“Jetty”的同学的分数之后，进行累加操作，那么，在第一次next调用结束后，取出的结果集是“Jetty”的分数总和。与此同时，所有的数据结果集中的游标都将

下移至数据值“Jetty”的下一个不同的数据值，并且根据数据结果集当前游标指向的值进行重排序。因此，包含名字顺着第二位的“John”的相关数据结果集则排在的队列的前列。

流式分组归并与排序归并的区别仅仅在于两点：

- 它会一次性的将多个数据结果集中的分组项相同的数据全数取出。
-  它需要根据聚合函数的类型进行聚合计算。

4、聚合归并

无论是流式分组归并还是内存分组归并，对聚合函数的处理都是一致的。除了分组的SQL之外，不进行分组的SQL也可以使用聚合函数。因此，聚合归并是在之前介绍的归并类

的之上追加的归并能力，即装饰者模式。聚合函数可以归类为比较、累加和求平均值这3种类型。

比较类型的聚合函数是指MAX和MIN。它们需要对每一个同组的结果集数据进行比较，并且直接返回其最大或最小值即可。

累加类型的聚合函数是指SUM和COUNT。它们需要将每一个同组的结果集数据进行累加。

求平均值的聚合函数只有AVG。它必须通过SQL改写的SUM和COUNT进行计算，相关内容已在SQL改写的内容中涵盖，不再赘述。

5、分页归并

上文所述的所有归并类型都可能进行分页。分页也是追加在其他归并类型之上的装饰器，ShardingSphere通过装饰者模式来增加对数据结果集进行分页的能力。分页归并负责

将无需获取的数据过滤掉。

ShardingSphere的分页功能比较容易让使用者误解，用户通常认为分页归并会占用大量内存。在分布式的场景中，将LIMIT 10000000, 10改写为LIMIT 0, 10000010，

才能保证其数据的正确性。用户非常容易产生ShardingSphere会将大量无意义的数据加载至内存中，造成内存溢出风险的错觉。其实，通过流式归并的原理可知，会将

数据全部加载到内存中的只有内存分组归并这一种情况。而通常来说，进行OLAP的分组SQL，不会产生大量的结果数据，它更多的用于大量的计算，以及少量结果产出的场景。

除了内存分组归并这种情况之外，其他情况都通过流式归并获取数据结果集，因此ShardingSphere会通过结果集的next方法将无需取出的数据全部跳过，并不会将其存入内存。

但同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到ShardingSphere的内存空间。因此，采用LIMIT这种方式分页，并非最佳实践。由于LIMIT并不能通过索引

查询数据，因此如果可以保证ID的连续性，通过ID进行分页是比较好的解决方案，例如：

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;

或通过记录上次查询结果的最后一条记录的ID进行下一页的查询，例如：

SELECT * FROM t_order WHERE id > 10000000 LIMIT 10;

6、总结

用最后一张图来总结归并引擎的整体结构划分

补充 有关ShardingSphere其它的知识概述这里就不在讲了,这篇文章也是完全根据官方文档加上个人理解写的，所以想想要更加详细的了解可以去看官网。

ShardingSphere实现读写分离

有关ShardingSphere概念前面写了两篇博客:

1、分库分表(1) --- 理论

2、分库分表(2) --- ShardingSphere(理论)

下面就这个项目做个整体简单介绍，并在文章最下方附上项目Github地址。

一、项目概述

1、技术架构

项目总体技术选型

SpringBoot2.0.6 + shardingsphere4.0.0-RC1 + Maven3.5.4  + MySQL + lombok(插件)

2、项目说明

场景 如果实际项目中Mysql是 Master-Slave (主从)部署的，那么数据保存到Master库，Master库数据同步数据到Slave库，数据读取到Slave库，

这样可以减缓数据库的压力。

3、数据库设计

我们这个项目中Mysql服务器并没有实现主从部署,而是同一个服务器建立两个库，一个当做Master库，一个当做Slave库。所以这里是不能实现的功能就是Master库

新增数据主动同步到Slave库。这样也更有利于我们测试看效果。

Master库

Slave库

从两幅图中可以看出，我这里在同一个服务器建两个数据库来模拟主从数据库。为了方便看测试效果，这里主从数据库中的数据是不一样的。

二、核心代码

说明 完整的代码会放到GitHub上，这里只放一些核心代码。

1、pom.xml

    <properties>
        <java.version>1.8</java.version>
        <mybatis-spring-boot>2.0.1</mybatis-spring-boot>
        <druid>1.1.16</druid>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot}</version>
        </dependency>
        <!--mybatis驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid}</version>
        </dependency>
        <!--shardingsphere最新版本-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <!--lombok实体工具-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

2、application.properties

server.port=8088
#指定mybatis信息
mybatis.config-location=classpath:mybatis-config.xml

spring.shardingsphere.datasource.names=master,slave0
# 数据源 主库
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/master?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
# 数据源 从库
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://localhost:3306/slave?characterEncoding=utf-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

# 读写分离
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
#打印sql
spring.shardingsphere.props.sql.show=true

Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。具体可以看官网。

3、UserController

@RestController
public class UserController {

    @Autowired
    private UserService userService;
    /**
     * @Description: 保存用户
     */
    @PostMapping("save-user")
    public Object saveUser() {
        return userService.saveOne(new User("小小", "女", 3));
    }
    /**
     * @Description: 获取用户列表
     */
    @GetMapping("list-user")
    public Object listUser() {
        return userService.list();
    }
}

三、测试验证

1、读数据

我们可以发现读取的数据是Slave库的数据。我们再来看控制台打印的SQL。可以看到读操作是Slave库。

2、写数据

请求

localhost:8088/save-user?name=小小&sex=女&age=3

查看Mater数据库

发现Master数据库已经多了一条数据了，再看控制台打印的SQL。

这个时候如果去看Slave库的话这条新增的数据是没有的，因为没有同步过去。

Github地址：https://github.com/yudiandemingzi/spring-boot-sharding-sphere

ShardingSphere实现分表

有关分库分表前面写了三篇博客:

1、分库分表(1) --- 理论

2、分库分表(2) --- ShardingSphere(理论)

3、分库分表(3) ---SpringBoot + ShardingSphere实现读写分离

这篇博客通过ShardingSphere实现分表不分库，并在文章最下方附上项目Github地址。

一、项目概述

1、技术架构

项目总体技术选型

SpringBoot2.0.6 + shardingsphere4.0.0-RC1 + Maven3.5.4  + MySQL + lombok(插件)

2、项目说明

场景 在实际开发中，如果表的数据过大，我们可能需要把一张表拆分成多张表，这里就是通过ShardingSphere实现分表功能，但不分库。

3、数据库设计

这里有个member库，里面的tab_user表由一张拆分成3张，分别是tab_user0、tab_user1、tab_user2。

如图

具体的创建表SQL也会放到GitHub项目里

二、核心代码

说明 完整的代码会放到GitHub上，这里只放一些核心代码。

1、application.properties

server.port=8086

#指定mybatis信息
mybatis.config-location=classpath:mybatis-config.xml

spring.shardingsphere.datasource.names=master

# 数据源 主库
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/member?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

#数据分表规则
#指定所需分的表
spring.shardingsphere.sharding.tables.tab_user.actual-data-nodes=master.tab_user$->{0..2}
#指定主键
spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.sharding-column=id
#分表规则为主键除以3取模
spring.shardingsphere.sharding.tables.tab_user.table-strategy.inline.algorithm-expression=tab_user$->{id % 3}

#打印sql
spring.shardingsphere.props.sql.show=true

Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。具体可以看官网。

2、UserController

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 模拟插入数据
     */
    List<User> userList = Lists.newArrayList();
    /**
     * 初始化插入数据
     */
    @PostConstruct
    private void getData() {
        userList.add(new User(1L,"小小", "女", 3));
        userList.add(new User(2L,"爸爸", "男", 30));
        userList.add(new User(3L,"妈妈", "女", 28));
        userList.add(new User(4L,"爷爷", "男", 64));
        userList.add(new User(5L,"奶奶", "女", 62));
    }
    /**
     * @Description: 批量保存用户
     */
    @PostMapping("save-user")
    public Object saveUser() {
        return userService.insertForeach(userList);
    }
    /**
     * @Description: 获取用户列表
     */
    @GetMapping("list-user")
    public Object listUser() {
        return userService.list();
    }

三、测试验证

1、批量插入数据

请求接口

localhost:8086/save-user

我们可以从商品接口代码中可以看出，它会批量插入5条数据。我们先看控制台输出SQL语句

我们可以从SQL语句可以看出 tab_user1 和 tab_user2 表插入了两条数据，而 tab_user0 表中插入一条数据。

我们再来看数据库

tab_user0

tab_user1

tab_user2

完成分表插入数据。

2、获取数据

我们来获取列表的SQL，这里对SQL做了order排序操作，具体ShardingSphere分表实现order操作的原理可以看上面一篇博客。

  select *  from tab_user order by id

请求接口结果

我们可以看出虽然已经分表，但依然可以将多表数据聚合在一起并可以排序。

码农公寓

一、垂直切分

1、垂直分库

2、垂直分表

3、垂直切分优缺点

二、水平切分

1、水平分库

2、水平分表

3、水平切分优缺点

三、数据分片规则

1、Hash取模分表

2、数值Range分表

3、一致性Hash算法

四、分库分表带来的问题

1、分布式事务问题

2、跨节点关联查询 Join 问题

3、跨节点分页、排序、函数问题

4、全局主键避重问题

5、数据迁移问题

一、ShardingSphere概念

1、概念

2、功能列表

3、项目状态

二、分库分表---结果归并

1、遍历归并

2、排序归并

3 、分组归并

4、聚合归并

5、分页归并

一、项目概述

1、技术架构

2、项目说明

3、数据库设计

二、核心代码

1、pom.xml

2、application.properties

3、UserController

三、测试验证

1、读数据

2、写数据

一、项目概述

1、技术架构

2、项目说明

3、数据库设计

二、核心代码

1、application.properties

2、UserController

三、测试验证

1、批量插入数据

2、获取数据

相关文章