ShardingSphere入门，从水平分表👉水平分库👉垂直分库

这是我参与2022首次更文挑战的第4天，活动详情查看：2022首次更文挑战

1. 基本概念

1.1. 什么是Sharding Sphere？

• 一套开源的分布式数据库中间件解决方案

• 有三个产品：Sharding-JDBC, Sharding-Proxy 和 Sharding Sidecar（规划中）

• 定位为关系型数据库中间件，合理在分布式环境下使用关系型数据库操作

  

  shardingsphere.apache.org/index_zh.ht…

1.2. 分库分表

1.2.1. 问题

• 由于数据库数据量的不可控，随着时间和业务发展，表里面的数据越来越多，这时对数据库表进行CRUD操作时，会有相关性能问题产生。

1.2.2. 方案：

• 方案一：从硬件方面解决
• 方案二：分库分表

1.2.3. 分库分表的方式

1.2.3.1 垂直切分 - 垂直分表 - 垂直分库

1.2.3.2. 水平切分 - 水平分表 - 水平分库

1.2.4. 垂直切分

1.2.4.1. 垂直分表

• 将数据表中的字段根据业务需求拆分成若干新表，每张表中仅保存当前业务所需字段。

• 表结构不一致。

  

1.2.4.2. 垂直分库

• 把单一数据库按照业务进行划分，专库专表。

• 库结构不一致。

  

1.2.5. ** 水平切分**

1.2.5.1. 水平分表

• 解决单库单表数据量过大的性能问题。

• 表结构一致。

  

1.2.5.2. 水平分库

• 解决单库单表数据量过大的性能问题。

• 库结构一致。

  

1.3. 分库分表应用和问题

1.3.1. 应用

• 在数据库设计时候考虑垂直分库和垂直分表

• 随着数据库数据量增加，不要马上考虑做水平切分

  • 缓存处理
  • 读写分离
  • 索引

  以上几种方式均不能满足需求，再考虑做水平分库和水平分表操作。

1.3.2. 分库分表问题

• 跨节点连接查询问题(分页、排序)

• 多数据源管理问题

  

2. Sharding-JDBC

2.1. 简介

• 主要用来简化对分库分表之后数据相关才做。

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；

• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；

• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

  

2.2. 水平分表

2.2.1. 需求

• 新增数据以主键奇偶数进行区分，分别存入两张表中。
• 以主键奇偶数区分，分别从不同表中查询数据。

2.2.2. 搭建环境

• SpringBoot：2.3.4.RELEASE
• MyBatisPlus：3.4.2
• Sharding-JDBC：4.0.0-RC1
• Druid连接池：1.2.2
• mysql：8.0.21

2.2.3. 数据库

• 创建数据库 sharding_demo_1
• 创建表 sharding_1 和 sharding_2
• 规则：对ID取余，偶数将数据添加到 sharding_1 ，奇数将数据添加到 sharding_2

2.2.4. 代码实现

• 创建SpringBoot项目，生成entity实体类和dao。

<dependencies>
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
      <version>2.3.4.RELEASE</version>
  </dependency>

  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <version>2.3.4.RELEASE</version>
  </dependency>

  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>2.3.4.RELEASE</version>
  </dependency>

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.2.2</version>
  </dependency>

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.21</version>
  </dependency>

  <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.0.0-RC1</version>
  </dependency>

  <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.4.2</version>
  </dependency>

  <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.12</version>
  </dependency>
</dependencies>

• 配置Sharding-JDBC分片策略

# 数据源配置
spring.shardingsphere.datasource.names=s1
spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://host:port/sharding_demo_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.s1.username=username
spring.shardingsphere.datasource.s1.password=password

# 指定配置表在哪个数据库，表名称是什么。spring.shardingsphere.sharding.tables.表名.actual-data-nodes=datasource-names.table-name_$->{start..end}
spring.shardingsphere.sharding.tables.sharding.actual-data-nodes=s1.sharding_$->{1..2}

# 指定主键字段以及生成算法。spring.shardingsphere.sharding.tables.表名
spring.shardingsphere.sharding.tables.sharding.key-generator.column=id
spring.shardingsphere.sharding.tables.sharding.key-generator.type=SNOWFLAKE

# 指定表分片策略（根据主键取余）。spring.shardingsphere.sharding.tables.表名
spring.shardingsphere.sharding.tables.sharding.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.sharding.table-strategy.inline.algorithm-expression=sharding_$->{id % 2 + 1}

# 控制台显示SQL语句
spring.shardingsphere.props.sql.show=true

# 允许一个实体类对应多张表
spring.main.allow-bean-definition-overriding=true

• 代码

@Data
public class Sharding extends Model<Sharding> implements Serializable {

    private Long id;

    private String name;

    private Long userId;

    private String status;
}

@Repository
public interface ShardingDao extends BaseMapper<Sharding> {
}

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

    @Autowired
    private ShardingDao shardingDao;

    @Test
    void addTest() {
        for (int i = 0; i < 10; i++) {
            Sharding sharding = new Sharding();
            sharding.setName("java");
            sharding.setUserId(100L);
            sharding.setStatus("Normal");
            shardingDao.insert(sharding);
        }
    }

    @Test
    void selectTest() {
        System.out.println(shardingDao.selectById(1475415331560464386L));
    }
}

2.2.5. 最终效果

2.3. 水平分库

2.3.1. 需求

• 新增数据以主键奇偶数进行区分，分别存入不同数据库中。
• 以主键奇偶数区分，分别从不同数据库中查询数据。
• 表需求与水平分表 2.2.1. 需求 一致

2.3.2. 环境搭建

• 环境与水平分表 2.2.2. 环境搭建 一致

2.3.3. 数据库

• 创建数据库 sharding_demo_1 和 sharding_demo_2

• 分别创建表 sharding_1 和 sharding_2

• 规则：

  • user_id：偶数添加到 sharding_demo_1 数据库中，奇数添加到 sharding_demo_2 数据库中
  • id：偶数将数据添加到 sharding_1 ，奇数将数据添加到 sharding_2

2.3.4. 代码实现

• 配置

# 添加数据源
spring.shardingsphere.datasource.names=s1,s2
spring.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s2.url=jdbc:mysql://host:port/sharding_demo_2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.s2.username=username
spring.shardingsphere.datasource.s2.password=password

# 修改数据库表达式
spring.shardingsphere.sharding.tables.sharding.actual-data-nodes=s$->{1..2}.sharding_$->{1..2}

# 指定数据库分片策略 约定 user_id 是偶数添加 s1，奇数添加 s2
# 默认所有表
# spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=s$->{user_id % 2 + 1}
# 指定表。spring.shardingsphere.sharding.tables.表名
spring.shardingsphere.sharding.tables.sharding.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.sharding.database-strategy.inline.algorithm-expression=s$->{user_id % 2 + 1}

• 代码

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

    @Autowired
    private ShardingDao shardingDao;

    @Test
    void addDatabaseTest() {
        for (long i = 0; i < 10; i++) {
            Sharding sharding = new Sharding();
            sharding.setName("java");
            sharding.setUserId(i);
            sharding.setStatus("Normal");
            shardingDao.insert(sharding);
        }
    }

    @Test
    void selectDatabaseTest() {
        System.out.println(shardingDao.selectOne(new QueryWrapper<Sharding>().eq("id", 1475503844612063234l)));
        System.out.println(shardingDao.selectOne(new QueryWrapper<Sharding>().eq("user_id", 0).eq("id", 1475503844612063234l)));
    }
}

2.3.5. 最终效果

2.4. 垂直分库

2.4.1. 需求

• 某功能模块业务量太大，数据库相关操作影响到了其它功能模块，需要将其单独拆分出来，独立一个数据库。

2.4.2. 环境搭建

• 环境与水平分表 2.2.2. 环境搭建 一致

2.4.3. 数据库

• 新建数据库 sharding_demo_3
• 新建表sharding_big
• 规则：针对sharding_big业务的请求统一指向sharding_demo_3数据库进行相关操作。

2.4.4. 代码实现

• 实体类

@Data
//@TableName("sharding_big") // 如果报 "Missing the data source name: 'null'", 则需指定实体类对应表名
public class ShardingBig extends Model<ShardingBig> implements Serializable {

    private Long id;

    private String name;

    private Long userId;

    private String status;
}

• Dao

@Repository
public interface ShardingBigDao extends BaseMapper<ShardingBig> {
}

• 配置

# 数据源配置
spring.shardingsphere.datasource.names=s1,s2,s3
spring.shardingsphere.datasource.s3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s3.url=jdbc:mysql://host:port/sharding_demo_2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.s3.username=username
spring.shardingsphere.datasource.s3.password=password

# 垂直分库，将 sharding_big 指向专属数据库
spring.shardingsphere.sharding.tables.sharding_big.actual-data-nodes=s$->{3}.sharding_big
spring.shardingsphere.sharding.tables.sharding_big.key-generator.column=id
spring.shardingsphere.sharding.tables.sharding_big.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.sharding_big.table-strategy.inline.sharding-column=id
# 这里只有单表，可以直接指定表名
spring.shardingsphere.sharding.tables.sharding_big.table-strategy.inline.algorithm-expression=sharding_big

• 代码

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

    @Autowired
    private ShardingBigDao ShardingBigDao;

    @Test
    void addBigTest() {
        ShardingBig shardingBig = new ShardingBig();
        shardingBig.setName("big data");
        shardingBig.setUserId(100L);
        shardingBig.setStatus("S");

        ShardingBigDao.insert(shardingBig);
    }

    @Test
    void selectBigTest() {
        System.out.println(ShardingBigDao.selectById(1475633650611724289L));
    }
}

2.4.5. 最终效果

2.5. 公共表操作

2.5.1. 需求

• 存储固定数据的表，表结构很少发生变化，常用于关联查询操作。

2.5.2. 环境搭建

• 环境与水平分表 2.2.2. 环境搭建 一致

2.5.3. 数据库

• 在每个数据库都建立一张公共表 t_dict

2.5.4. 代码实现

• 实体类

@Data
public class TDict extends Model<TDict> implements Serializable {

    private Long dictId;

    private String status;

    private String value;
}

• Dao

@Repository
public interface TDictDao extends BaseMapper<TDict> {
}

• 配置

# 公共表配置
# 指定公共表名
spring.shardingsphere.sharding.broadcast-tables=dict
# 公共表主键
spring.shardingsphere.sharding.tables.dict.key-generator.column=dict_id
# 公共表主键生成算法
spring.shardingsphere.sharding.tables.dict.key-generator.type=SNOWFLAKE

• 代码

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

    @Autowired
    private TDictDao dictDao;

    @Test
    void addDictTest(){
        TDict dict = new TDict();
        dict.setStatus("S");
        dict.setValue("True");

        dictDao.insert(dict);
    }

    @Test
    void deleteDictTest(){
        // 这里要采用 QueryWrapper 来添加条件，使用BaseMapper.deleteById()会导致属性映射为null
        dictDao.delete(new QueryWrapper<TDict>().eq("dict_id", 682541286644776961L));
    }
}

2.5.5. 最终效果

2.6. 读写分离

2.6.1. 需求

• 由于业务系统读请求较多，写请求较少，为了避免读请求堵塞写请求，需将数据库进行读写分离操作。

2.6.2. 环境搭建

• 环境与水平分表 2.2.2. 环境搭建 一致

2.6.3. 数据库

• 新增从数据库，将其与 2.4. 垂直分库 数据库进行主从配置关联。

2.6.4. 代码实现

• 代码部分与 2.4. 垂直分库 一致
• 配置

# 数据源配置
spring.shardingsphere.datasource.names=s1,s2,s3,slave1
spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://host:port/sharding_demo_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.slave1.username=username
spring.shardingsphere.datasource.slave1.password=password

# 读写分离配置
# 指定主数据库与从数据库,定义统一逻辑数据源 ms0。spring.shardingsphere.sharding.master-slave-rules.自定义逻辑数据源
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=s3
# 多个从数据库用逗号隔开
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave1

# 分表策略，固定分配至 ms0。原先为指定数据源s$->{3}.sharding_big，需要修改成逻辑数据源。
spring.shardingsphere.sharding.tables.sharding_big.actual-data-nodes=ms0.sharding_big

# 一主多从需要配置轮询策略
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

2.6.5. 最终效果

3. Sharding-Proxy分库分表操作

3.1. 简介

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用；

• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

  

• 下载地址：archive.apache.org/dist/shardi…

3.2. 安装

• 下载完成后，解压即可。
• 修改server.yaml、config-master_slave.yaml 和 config-sharding.yaml 配置。
• bin/start.* 启动。

3.3.分表/分库配置

• 修改 conf/server.yaml，去除注释。

# sharding_proxy代理数据库相关配置
authentication:
 users:
   root:
     password: root
   sharding:
     password: sharding 
     authorizedSchemas: sharding_db

props:
 max.connections.size.per.query: 1
 acceptor.size: 16  # The default value is available processors count * 2.
 executor.size: 16  # Infinite by default.
 proxy.frontend.flush.threshold: 128  # The default value is 128.
   # LOCAL: Proxy will run with LOCAL transaction.
   # XA: Proxy will run with XA transaction.
   # BASE: Proxy will run with B.A.S.E transaction.
 proxy.transaction.type: LOCAL
 proxy.opentracing.enabled: false
 proxy.hint.enabled: false
 query.with.cipher.column: true
 sql.show: false
 allow.range.query.with.inline.sharding: false

• 修改 conf/config-sharding.yaml，配置分库分表规则，找到对应数据库注释区域，复制驱动包到 lib 目录下。

schemaName: sharding_db

# 配置数据源
dataSources:
 ds_0:
   url: jdbc:mysql://host:port/sharding_demo_1?serverTimezone=UTC&useSSL=false
   username: username
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 ds_1:
   url: jdbc:mysql://host:port/sharding_demo_2?serverTimezone=UTC&useSSL=false
   username: username
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50

shardingRule:
 # 定义表以及分表策略
 tables:
   sharding:
     actualDataNodes: ds_${0..1}.sharding_${0..1}
     tableStrategy:
       inline:
         shardingColumn: id
         algorithmExpression: sharding_${id % 2}
     keyGenerator:
       type: SNOWFLAKE
       column: id
 bindingTables:
   - sharding
 # 默认分库策略
 defaultDatabaseStrategy:
   inline:
     shardingColumn: user_id
     algorithmExpression: ds_${user_id % 2}
 defaultTableStrategy:
   none:

• 修改 config-master_slave.yaml 主从配置。启动sharding_proxy , 对 master_slave_db 数据库进行相关操作即可。

schemaName: master_slave_db

dataSources:
 master_ds:
   url: jdbc:mysql://host:port/sharding_demo_1?serverTimezone=UTC&useSSL=false
   username: username
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 slave_ds_0:
   url: jdbc:mysql://host:port/sharding_demo_1?serverTimezone=UTC&useSSL=false
   username: username
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50
 slave_ds_1:
   url: jdbc:mysql://host:port/sharding_demo_1?serverTimezone=UTC&useSSL=false
   username: username
   password: password
   connectionTimeoutMilliseconds: 30000
   idleTimeoutMilliseconds: 60000
   maxLifetimeMilliseconds: 1800000
   maxPoolSize: 50

masterSlaveRule:
 name: ms_ds
 masterDataSourceName: master_ds
 slaveDataSourceNames:
   - slave_ds_0
   - slave_ds_1