「独家解析」ShardingSphere分库分表技术实践，助力MySQL性能提升

Posted on by hackdl

Apache ShardingSphere 是一个开源的分布式数据库中间件解决方案组成的生态圈。它由三个产品组成：JDBC、Proxy 和 Sidecar。这些产品相互独立，但可以混合部署和配合使用，以提供标准化的数据分片、分布式事务和数据库治理功能。

JDBC 是 ShardingSphere 的基础组件，提供数据分片和读写分离等功能，可以与任何标准的 JDBC 应用程序集成使用，而且不需要修改原有的代码，同时也支持主流的 ORM 框架。

Proxy 是一种数据库代理，提供数据分片、读写分离和分布式事务等功能，可以与任何应用程序集成使用，而且不需要修改原有的代码。Proxy 还提供了高可用性和负载均衡功能，以及对 SQL 解析和路由的支持。

Sidecar 是 ShardingSphere 的规划中产品，目前尚未发布。它是一个轻量级代理，可以在应用程序内部部署，提供数据分片和读写分离等功能，以及对数据库连接的池化和缓存的支持。

Apache ShardingSphere 可以适用于各种多样化的应用场景，如 Java 同构、异构语言、云原生等。它可以为用户提供高可用性、水平扩展和性能优化的解决方案，同时还提供了数据库治理功能，如动态数据源管理、SQL 防火墙、运行时数据治理等。

1.一套开源的分布式数据库中间件解决方案。
2.有三个产品：JDBC、Proxy、Sidecar。

1.什么是分库分表

数据库拆分是一种通过某种特定的条件或者维度，将原本存储在单一数据库中的数据，分散存储到多个数据库或者主机上的技术。通过数据库拆分，可以达到负载均衡和扩展性的目的，提高系统的可用性和性能。

数据库拆分可以根据不同的需求采用不同的拆分策略，例如按照数据表、数据字段、数据范围等进行拆分。但是需要注意，数据库拆分也会引入新的问题，如数据一致性、分布式事务、跨节点查询等，需要在设计和实现时充分考虑和解决。

当使用读写分离、索引和缓存等优化手段仍然无法满足应用程序的需求时，数据库拆分可以是解决问题的一种方法。但是在使用数据库拆分之前，需要充分了解系统的数据结构和访问模式，并进行合理的设计和规划，以避免引入新的问题和风险。

2.分库分表之垂直拆分

专库专用。一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。如下图：

(1).优点
拆分后业务清晰，拆分规则明确。
系统之间整合或扩展容易。
数据维护简单。
(2).缺点
部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度。
受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
事务处理复杂。

3.分库分表之水平切分

垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。

相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式。如下图：

(1).优点
不存在单库大数据，高并发的性能瓶颈。
对应用透明，应用端改造较少。
按照合理拆分规则拆分，join 操作基本避免跨库。
提高了系统的稳定性跟负载能力。
(2).缺点
拆分规则难以抽象。
分片事务一致性难以解决。
数据多次扩展难度跟维护量极大。
跨库 join 性能较差。

4.什么是 ShardingSphere-JDBC

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

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

需要注意的是，分库分表并不是由 ShardingSphere-JDBC 来做，它是用来负责操作已经分完之后的 CRUD 操作。

5.Sharding-JDBC 分表实操

环境使用：Springboot 2.2.11 + MybatisPlus + ShardingSphere-JDBC 4.0.0-RC1 + Druid 连接池

具体 Maven 依赖：


        
            org.springframework.boot
            spring-boot-starter
        

        
            org.springframework.boot
            spring-boot-starter-test
        

        
            com.alibaba
            druid-spring-boot-starter
            1.1.20
        

        
            mysql
            mysql-connector-java
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.0.0-RC1
        

        
            com.baomidou
            mybatis-plus-boot-starter
            3.0.5
        

        
            org.projectlombok
            lombok
(1).按照水平分表来创建数据库
创建数据库 course_db
创建表 course_1 、 course_2
约定规则：如果添加的课程 id 为偶数添加到 course_1 中，奇数添加到 course_2 中。

SQL 如下：

create database course_db;

use course_db;

create table course_1 (
    cid bigint(20) primary key ,
    cname varchar(50) not null,
    user_id bigint(20) not null ,
    status varchar(10) not null
) engine = InnoDB;

create table course_2 (
    cid bigint(20) primary key ,
    cname varchar(50) not null,
    user_id bigint(20) not null ,
    status varchar(10) not null
) engine = InnoDB;
(2).配置对应实体类以及 Mapper
/**
 * @Description: Course实体类
 */
@Data
public class Course {

    private Long cid;
    private String cname;
    private Long userId;
    private String status;

}

mapper：

/**
 * @Description: mapper
 */
@Repository
@MapperScan("com.jack.shardingspherejdbc.mapper")
public interface CourseMapper extends BaseMapper {

}

启动类配置 MapperScan

@SpringBootApplication
@MapperScan("com.jack.shardingspherejdbc.mapper")
public class ShardingsphereJdbcDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingsphereJdbcDemoApplication.class, args);
    }

}

6.配置 Sharding-JDBC 分片策略

application.properties 内容：

spring.shardingsphere.datasource.names=m1

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 指定course表分布的情况，配置表在哪个数据库里，表的名称都是什么 m1.course_1,m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 配置分表策略    约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

测试代码运行:

@RunWith(SpringRunner.class)
@SpringBootTest
class ShardingsphereJdbcDemoApplicationTests {

    @Autowired
    private CourseMapper courseMapper;

    //添加课程
    @Test
    public void addCourse() {
        Course course = new Course();
        //cid由我们设置的策略，雪花算法进行生成
        course.setCname("Java");
        course.setUserId(100L);
        course.setStatus("Normal");
        courseMapper.insert(course);
    }

}

运行结果:

我们查询一下看看：

@Test
public void findCourse() {
    QueryWrapper wrapper = new QueryWrapper();
    wrapper.eq("cid", 536248443081850881L);
    courseMapper.selectOne(wrapper);
}

可以看到查询的表也是正确的。

7.Sharding-JDBC 实现水平分库

需求：

创建两个数据库，edu_db_1、edu_db_2。
每个库中包含：course_1、course_2。
数据库规则：userid 为偶数添加到 edu_db_1 库，奇数添加到 edu_db_2。
表规则：如果添加的 cid 为偶数添加到 course_1 中，奇数添加到 course_2 中。

创建数据库和表结构:

create database edu_db_1;
create database edu_db_2;

use edu_db_1;

create table course_1 (
   `cid` bigint(20) primary key,
   `cname` varchar(50) not null,
   `user_id` bigint(20) not null,
   `status` varchar(10) not null
);

create table course_2 (
   `cid` bigint(20) primary key,
   `cname` varchar(50) not null,
   `user_id` bigint(20) not null,
   `status` varchar(10) not null
);

use edu_db_2;

create table course_1 (
   `cid` bigint(20) primary key,
   `cname` varchar(50) not null,
   `user_id` bigint(20) not null,
   `status` varchar(10) not null
);

create table course_2 (
   `cid` bigint(20) primary key,
   `cname` varchar(50) not null,
   `user_id` bigint(20) not null,
   `status` varchar(10) not null
);
(1).配置分片策略

application.properties 内容：

# sharding-jdbc 水平分库分表策略
# 配置数据源，给数据源起别名
# 水平分库需要配置多个数据库
spring.shardingsphere.datasource.names=m1,m2

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第一个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 配置第二个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

# 指定数据库分布的情况和数据表分布的情况
# m1 m2   course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分库策略    约定 user_id 值偶数添加到 m1 库，如果 user_id 是奇数添加到 m2 库
# 默认写法（所有的表的user_id）
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 指定分表策略    约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
(2).测试代码运行
@Test
public void addCourse() {
    Course course = new Course();
    //cid由我们设置的策略，雪花算法进行生成
    course.setCname("python");
    //分库根据user_id
    course.setUserId(100L);
    course.setStatus("Normal");
    courseMapper.insert(course);

    course.setCname("c++");
    course.setUserId(111L);
    courseMapper.insert(course);
}

对应的我们 python 的 userId 为偶数所以添加到 edu_db_1 库中，而 c++是奇数所以添加到 edu_db_2 库中。

(3).运行结果

看下对应的数据库数据，也是没有问题的。

8.Sharding-JDBC 实现垂直分库

需求：

我们再额外创建一个 user_db 数据库。当我们查询用户信息就去 user_db，课程信息就去 edu_db_1、edu_db_2。

创建数据库和表结构:

create database user_db;

use user_db;

create table t_user(
   `user_id` bigint(20) primary key,
   `username` varchar(100) not null,
   `status` varchar(50) not null
);
(1).配置对应实体类和 Mapper

实体类：

/**
 * @Description:t_user 实体类
 */
@Data
@TableName("t_user")
public class User {
    private Long userId;
    private String username;
    private String status;
}

mapper：

/**
 * @Description: UserMapper
 */
@Repository
public interface UserMapper extends BaseMapper {
}
(2).配置分片策略

application.properties 内容：

# sharding-jdbc 水平分库分表策略
# 配置数据源，给数据源起别名
# 水平分库需要配置多个数据库
# m0为用户数据库
spring.shardingsphere.datasource.names=m1,m2,m0

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第一个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 配置第二个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

# 配置user数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456
# 配置user_db数据库里面t_user  专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m0.t_user
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定分表策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

# 指定数据库分布的情况和数据表分布的情况
# m1 m2   course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分库策略    约定 user_id 值偶数添加到 m1 库，如果 user_id 是奇数添加到 m2 库
# 默认写法（所有的表的user_id）
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 指定分表策略    约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
(3).测试代码运行
@Autowired
private UserMapper userMapper;

@Test
public void addUser(){
    User user = new User();
    user.setUsername("Jack");
    user.setStatus("Normal");
    userMapper.insert(user);
}

@Test
public void findUser() {
  QueryWrapper wrapper = new QueryWrapper();
  wrapper.eq("user_id", 536472243283165185L);
  userMapper.selectOne(wrapper);
}
(4).添加方法运行结果

(5).查询方法运行结果

9.Sharding-JDBC 公共表

概念：

存储固定数据的表，表数据很少发生变化，查询时经常要进行关联。
在每个数据库中都创建出相同结构公共表。
操作公共表时，同时操作添加了公共表的数据库中的公共表，添加记录时，同时添加，删除时，同时删除。

在多个数据库中创建公共表：

# use user_db;
# use edu_db_1;
use edu_db_2;

create table t_dict(
   `dict_id` bigint(20) primary key,
   `status` varchar(100) not null,
   `value` varchar(100) not null
);

配置公共表的实体类和 mapper

实体类：

/**
 * @Description:Dict实体类
 */
@Data
@TableName("t_dict")
public class Dict {
    private Long dictId;
    private String status;
    private String value;
}

mapper：

/**
 * @Description: DictMapper
 */
@Repository
public interface DictMapper extends BaseMapper {
}

配置分片策略：

application.properties：

# sharding-jdbc 水平分库分表策略
# 配置数据源，给数据源起别名
# 水平分库需要配置多个数据库
# m0为用户数据库
spring.shardingsphere.datasource.names=m1,m2,m0

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置第一个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 配置第二个数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

# 配置user数据源的具体内容，包含连接池，驱动，地址，用户名，密码
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456
# 配置user_db数据库里面t_user  专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m0.t_user
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定分表策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

# 指定数据库分布的情况和数据表分布的情况
# m1 m2   course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分库策略    约定 user_id 值偶数添加到 m1 库，如果 user_id 是奇数添加到 m2 库
# 默认写法（所有的表的user_id）
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# 指定分表策略    约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# 公共表配置
spring.shardingsphere.sharding.broadcast-tables=t_dict
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

测试代码运行

@Autowired
private DictMapper dictMapper;

@Test
public void addDict() {
    Dict dict = new Dict();
    dict.setStatus("Normal");
    dict.setValue("启用");
    dictMapper.insert(dict);
}

@Test
public void deleteDict() {
    QueryWrapper wrapper = new QueryWrapper();
    wrapper.eq("dict_id", 536486065947541505L);
    dictMapper.delete(wrapper);
}

添加方法运行结果：

删除方法运行结果：

需要注意的是，在使用 ShardingSphere 进行数据库分库分表时，需要仔细评估系统的需求和限制，并进行合理的设计和规划。此外，还需要注意分库分表会带来新的问题，如数据一致性、跨节点查询等，需要进行充分考虑和解决。

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.e1idc.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: