标签：JDBC ## 数据库分片使用 id mysql Sharding order

Sharding-JDBC使用

一、分库分表

1.1 为何要分库分表

传统的将数据集中存储至单一节点的解决方案，在性能、可用性和运维成本这三方面已经难于满足海量数据的场景

从性能方面来说，由于关系型数据库大多采用 B+ 树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降；同时，高并发访问请求也使得集中式数据库成为系统的最大瓶颈。

从可用性的方面来讲，服务化的无状态性，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。数据库的可用性，已成为整个系统的关键。

从运维成本方面考虑，当一个数据库实例中的数据达到阈值以上，对于 DBA 的运维压力就会增大。数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。一般来讲，单一数据库实例的数据的阈值在 1TB 之内，是比较合理的范围

通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段

1.2 分表解决问题的原理

分表可以提高查询速度

关系型数据库大多采用 B+ 树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降；

简化事务管理

分表虽然无法缓解数据库压力，但却能够提供尽量将分布式事务转化为本地事务的可能

1.3 分库解决问题的原理

分库能够用于有效的分散对数据库单点的访问量,提高数据库的可用性

1.4 数据如何拆分

数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。

数据分片的拆分方式又分为垂直分片和水平分片

1.4.1 垂直拆分

按照业务拆分的方式称为垂直分片，又称为纵向拆分，它的核心理念是专库专用。在拆分之前，一个数据库由多个数据表构成，每个表对应着不同的业务。而拆分之后，则是按照业务将表进行归类，分布到不同的数据库中，从而将压力分散至不同的数据库。

垂直分片往往需要对架构和设计进行调整。通常来讲，是来不及应对互联网业务需求快速变化的；而且，它也并无法真正的解决单点瓶颈。垂直拆分可以缓解数据量和访问量带来的问题，但无法根治。如果垂直拆分之后，表中的数据量依然超过单节点所能承载的阈值，则需要水平分片来进一步处理。

1.4.2 水平拆分

水平分片又称为横向拆分。相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。

水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是数据分片的标准解决方案

1.5 分库分表带来的问题

面对如此散乱的分片之后的数据，应用开发工程师和数据库管理员对数据库的操作变得异常繁重，他们需要知道数据需要从哪个具体的数据库的子表中获取
能够正确的运行在单节点数据库中的 SQL，在分片之后的数据库中并不一定能够正确运行。例如，分表导致表名称的修改，或者分页、排序、聚合分组等操作的不正确处理
跨库事务也是分布式数据库集群要面对的棘手事情。合理采用分表，可以在降低单表数据量的情况下，尽量使用本地事务，善于使用同库不同表可有效避免分布式事务带来的麻烦

二、读写分离

2.1 为什么要读写分离

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力

2.2 同分库分表的区别

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据 SQL 语义的分析，将读操作和写操作分别路由至主库与从库
读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。

2.3 读写分离带来的问题

读写分离虽然可以提升系统的吞吐量和可用性，但同时也带来了数据不一致的问题。这包括多个主库之间的数据一致性，以及主库与从库之间的数据一致性的问题。

三、Sharding-JDBC简介

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

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

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

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

3.1 名词概念

3.1.1 逻辑表

相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。例：订单数据根据主键尾数拆分为 10 张表，分别是 t_order_0 到 t_order_9，他们的逻辑表名为 t_order

3.1.2 真实表

在水平拆分的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9

3.1.3 数据节点

数据分片的最小单元，由数据源名称和真实表组成。例：ds_0.t_order_0

3.1.4 分片键

用于将数据库（表）水平拆分的数据库字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL 中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持，Apache ShardingSphere 也支持根据多个字段进行分片

3.1.5 分片算法

用于将数据分片的算法，支持 =、>=、<=、>、<、BETWEEN 和 IN 进行分片。分片算法可由开发者自行实现，也可使用 Apache ShardingSphere 内置的分片算法语法糖，灵活度非常高

3.1.6 分片策略

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略

3.1.7 分布式主键

数据分片后，不同数据节点生成全局唯一主键是非常棘手的问题。同一个逻辑表内的不同实际表之间的自增键由于无法互相感知而产生重复主键。虽然可通过约束自增主键初始值和步长的方式避免碰撞，但需引入额外的运维规则，使解决方案缺乏完整性和可扩展性。

Apache ShardingSphere 不仅提供了内置的分布式主键生成器，例如 UUID、SNOWFLAKE，还抽离出分布式主键生成器的接口，方便用户自行实现自定义的自增主键生成器。

3.1.8 行表达式

行表达式是为了解决配置的简化与一体化这两个主要问题，对于常见的分片算法，使用 Java 代码实现并不有助于配置的统一管理。

通过行表达式书写分片算法，可以有效地将规则配置一同存放，更加易于浏览与存储。

行表达式支持数据节点和分片算法这两个部分的配置，行表达式中如果出现连续多个 ${ expression } 或 $->{ expression } 表达式，整个表达式最终的结果将会根据每个子表达式的结果进行笛卡尔组合。

例如，以下行表达式：

${['online', 'offline']}_table${1..3}

最终会解析为：

online_table1, online_table2, online_table3, offline_table1, offline_table2, offline_table3

3.2 使用docker搭建主从复制

3.2.1 创建数据卷目录

mkdir db
cd db
mkdir -p mysql_{master,slave}/{conf,data,logs}

3.2.2 修改my.cnf文件

自定义my.cnf文件映射到/etc/mysql/conf.d目录下，注意不要直接复制my.cnf文件，要建空白文件增加需要的配置

## 主库cnf

[mysqld]
## 设置server_id,注意要唯一
server-id=201

## 开启日志功能，mysql-bin是日志文件名，可自定义
log-bin=mysql-bin 

## 需要同步的数据库名
## 如果需要同步多个数据库，则添加多个binlog-do-db
binlog-do-db=ds0
binlog-do-db=ds1

## 从库cnf

[mysqld]
## 设置server_id,注意要唯一
server-id=202

3.2.3 编写`docker-compose.yml`文件

version: '3.8'
services:
    mysql_master:
        restart: always
        images: daocloud.io/library/mysql:5.7.5-m15
        container_name: mysql_master
        ports:
            - 3307:3306
        environment:
            TZ: Asia/Shanghai
            MYSQL_ROOT_PASSWORD: 123456
        volumes:
        # 映射mysql配件文件、数据文件、日志文件
            - /home/weixia/db/mysql_master/conf:/etc/mysql/conf.d
            - /home/weixia/db/mysql_master/data:/var/lib/mysql
            - /home/weixia/db/mysql_master/logs:/var/log/mysql
            
    mysql_slave:
        restart: always
        images: daocloud.io/library/mysql:5.7.5-m15
        container_name: mysql_slave
        ports:
            - 3308:3306
        environment:
            TZ: Asia/Shanghai
            MYSQL_ROOT_PASSWORD: 123456
        volumes:
        # 映射mysql配件文件、数据文件、日志文件
            - /home/weixia/db/mysql_slave/conf:/etc/mysql/conf.d
            - /home/weixia/db/mysql_slave/data:/var/lib/mysql
            - /home/weixia/db/mysql_slave/logs:/var/log/mysql

## 运行容器
sudo docker compose up -d

3.2.4 主库开启远程访问

## 远程访问
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
## 刷新权限
flush privileges;
## 显示主库状态
show master status;

3.2.5 从库开启远程访问并启动主从配置

## 远程访问
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
## 远程访问
flush privileges;

## 配置要跟从的主库
## port是数字类型
use ds0;
change master to master_host='192.168.152.132',master_port=3307,master_user='root',master_password='123456';

## 开启主从
start slave;

## 查看主从状态
show slave status\G

主库创建数据库后，从库不用主动手工创建数据库，启用主从后会自动创建数据库

3.3 简单使用

请注意引入的依赖名及版本，务必使用4.X版本，官网示例的配置还停留在4.X版本，即便如此官网好多配置还不兼任4.X版本

引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

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

引入druid连接池时，不要使用springboot start版本，因为其会自动创建默认的数据源，同sharding-jdbc创建的数据源冲突

配置

server:
  port: 8080

spring:
  application:
    name: sharding-jdbc-demo

  shardingsphere:
  ## 全局属性
  ## 显示SQL语句
    props:
      sql:
        show: true

    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3307/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3308/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

如果不配置分库分表策略，查询操作随机命中数据源

3.4 读写分离配置

spring.sharding.master-slave-rules配置主从节点：

server:
  port: 8080

spring:
  application:
    name: sharding-jdbc-demo

  shardingsphere:
    props:
      sql:
        show: true

    datasource:
      names: master0,master1,slave0,slave1
      master0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3307/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3308/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

      master1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3307/ds1?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.152.132:3308/ds1?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
        username: root
        password: 123456

    ## 读写分离策略,可配置多组主从节点
    sharding:
      master-slave-rules:
        ## 一组主从节点的命名
        master0:
          master-data-source-name: master0
          slave-data-source-names: slave0
          ## 负载均衡策略
          ## ROUND_ROBIN 轮询
          ## RANDOM 随机
          ## WEIGHT 权重
          load-balance-algorithm-type: ROUND_ROBIN

        master1:
          master-data-source-name: master1
          slave-data-source-names: slave1
          load-balance-algorithm-type: random

      ## 如果未配置分库规则，则默认使用本数据据源进行读写
      ## 如果不显示定义主从关系，默认所有数据源均是从库，只能读不能写（写操作报错）
      #default-data-source-name: master1

如果不配置分片规则，会随机路由到每组主从节点上
每组主从节点的逻辑名应是主库数据源名，如果同主库数据源名不一致就会导致应用分片策略时路由失败

3.5 分片规则配置

spring.sharding.tables配置分库分表：

server:
port: 8080

spring:
application:
  name: sharding-jdbc-demo

shardingsphere:
  props:
    sql:
      show: true

  datasource:
    names: master0,master1,slave0,slave1
    master0:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.152.132:3307/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
      username: root
      password: 123456

    slave0:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.152.132:3308/ds0?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
      username: root
      password: 123456

    master1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.152.132:3307/ds1?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
      username: root
      password: 123456

    slave1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.152.132:3308/ds1?serverTimezone=UTC&useSSL=false&characterEncoding=utf-8
      username: root
      password: 123456

  ## 读写分离策略,可配置多组主从节点
  sharding:
    master-slave-rules:
      ## 一组主从节点的命名
      master0:
        master-data-source-name: master0
        slave-data-source-names: slave0
        ## 负载均衡策略
        ## ROUND_ROBIN 轮询
        ## RANDOM 随机
        ## WEIGHT 权重
        load-balance-algorithm-type: ROUND_ROBIN

      master1:
        master-data-source-name: master1
        slave-data-source-names: slave1
        ## 负载均衡策略
        ## ROUND_ROBIN 轮询
        ## RANDOM 随机
        ## WEIGHT 权重
        load-balance-algorithm-type: random

    ## 如果未配置分库规则，则默认使用本数据据源进行读写
    ## 如果不显示定义主从关系，默认所有数据源均是从库，只能读不能写（写操作报错）
    #default-data-source-name: master1

    tables:
      ## 逻辑表的名字
      user:
      ## 枚举数据节点：数据源.真实表名
        actual-data-nodes: master$->{0..1}.user$->{0..1}
        ## 分库策略
        database-strategy:
          inline:
            sharding-column: id
            algorithm-expression: master$->{id % 2}
  	  ## 分表策略
        table-strategy:
          inline:
            sharding-column: age
            algorithm-expression: user$->{age % 2}

分片键的值应在路由前应是确定值，这意味不能使用mysql自增主键作为分片键，如果把数据库自增主键作为分片键就会枚举所有可能的数据节点进行操作，写操作就会重复插入数据
分片算法一般会使用取模算法，数据源及真实表命名时应从0开始命名，避免模值为0时没有对应的数据节点进行匹配
如果配置了读写分离，分片规则数据节点的库名应同读写分离逻辑名保持一致

3.6 分布式序列配置

tables:
	user:
	## 分布式序列配置，支持雪花算法和UUID
		key-generator:
			column: id
			type: SNOWFLAKE
		actual-data-nodes: master$->{0..1}.user$->{0..1}
		database-strategy:
			inline:
				sharding-column: id
				algorithm-expression: master$->{id % 2}

		table-strategy:
			inline:
				sharding-column: age
				algorithm-expression: user$->{age % 2}

3.7 自定义分片算法

标准分片策略
对应StandardShardingStrategy，提供对SQL语句中的=,>, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy仅支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN、AND、>、<、>=、<=分片，如果不配置RangeShardingAlgorithm, SQL中的BETWEEN、AND等操作将按照全库路由处理。

复合分片策略
对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=,>, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

行表达式分片策略
对应InlineShardingStrategy.使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发,如: t_ user_ $->{u_ id名8}表示t user表根据u id模8，而分成8张表,表名称为t user_ 0到t_ user_ 7

Hint分片策略
对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

不分片策略
对应NoneShardingStrategy。不分片的策略

3.7.1 标准分片策略——PreciseShardingAlgorithm

自定义分库/分表算法类，实现PreciseShardingAlgorithm<T extends Comparable<?>接口

// PreciseShardingAlgorithm<T extends Comparable<?>中的T指分片键所属类型
public class MyDatabaseAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * @Param collection 数据源/真实表的集合
     * @Param preciseShardingValue 分片参数：逻辑表名、分片键、传入的分片键的值
     * @Return 返回命中的数据源/真实表名称
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        // 获取逻辑表名称
        String logicTableName = preciseShardingValue.getLogicTableName();
        // 获取分片键名称
        String columnName = preciseShardingValue.getColumnName();
        // 获取分片键传入的值
        Long value = preciseShardingValue.getValue();

        int i = (int) (value % 2);
        if (i == 0) {
            return "master0";
        } else {
            return "master1";
        }
    }
}

配置自定义分片算法

tables:
	user:
		actual-data-nodes: master$->{0..1}.user$->{0..1}

		database-strategy:
			standard:
				sharding-column: id
				precise-algorithm-class-name: com.weixia.shardingjdbcdemo.algorithm.database.MyDatabaseAlgorithm

		table-strategy:
			inline:
				sharding-column: age
				algorithm-expression: user$->{age % 2}

3.8 绑定表配置

绑定表指分片规则一致的一组分片表，使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。如：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

在不配置绑定表关系时，假设上面使用奇偶分片则分片键 order_id 将数值 10 路由至第 0 片，将数值 11 路由至第 1 片，那么路由后的 SQL 应该为 4 条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

绑定表配置如下：

sharding:
	binding-tables:
		- t_order, t_order_item

绑定表配置时使用逻辑表名，所以要在spring.sharding.tables中先进行配置

3.9 广播表

指所有的分片数据源中都存在的表，表结构及其数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表

进行广播表配置后，任一数据源中的广播表更新都会同时更新其它数据源中的广播表

sharding:
	broadcast-tables:
		- t_config

广播表使用真实表名，不用在spring.sharding.tables中配置

四、注意事项

如果SQL查询时条件不包含分片键，则会进行全库表路由，枚举所有数据节点进行SQL查询
如果分库和分表所用的分片键不一致，SQL条件查询仅包含一个分片键时，也会进行全局路由，枚举不确定的数据源或真实表进行SQL查询

标签：JDBC,##,数据库,分片,使用,id,mysql,Sharding,order
From： https://www.cnblogs.com/weixia-blog/p/16586348.html

Sharding-JDBC使用

Sharding-JDBC使用

一、分库分表

1.1 为何要分库分表

1.2 分表解决问题的原理

1.3 分库解决问题的原理

1.4 数据如何拆分

1.4.1 垂直拆分

1.4.2 水平拆分

1.5 分库分表带来的问题

二、读写分离

2.1 为什么要读写分离

2.2 同分库分表的区别

2.3 读写分离带来的问题

三、Sharding-JDBC简介

3.1 名词概念

3.1.1 逻辑表

3.1.2 真实表

3.1.3 数据节点

3.1.4 分片键

3.1.5 分片算法

3.1.6 分片策略

3.1.7 分布式主键

3.1.8 行表达式

3.2 使用docker搭建主从复制

3.2.1 创建数据卷目录

3.2.2 修改my.cnf文件

3.2.3 编写docker-compose.yml文件

3.2.4 主库开启远程访问

3.2.5 从库开启远程访问并启动主从配置

3.3 简单使用

3.4 读写分离配置

3.5 分片规则配置

3.6 分布式序列配置

3.7 自定义分片算法

3.7.1 标准分片策略——PreciseShardingAlgorithm

3.8 绑定表配置

3.9 广播表

四、注意事项

相关文章

赞助商

阅读排行

3.2.3 编写`docker-compose.yml`文件