# shardingsphere-example **Repository Path**: cjiangbo/shardingsphere-example ## Basic Information - **Project Name**: shardingsphere-example - **Description**: 此示例参照官网，仅作为学习用途 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2023-05-10 - **Last Updated**: 2023-12-28 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README ### 1、主从搭建 #### 1.1、环境说明 主机 192.168.200.143，在该主机上部署四个 MySQL 服务，搭建两主两从的架构，两主可互为主备，只复制 **shardingsphere** 库 mysql 服务： - 3306 - master01 - 3307 - master02 - 13306 - slave01 - 13307 - slave02 #### 1.2、配置说明 目录层级： - ./mysql/**docker-compose.yaml**：mysql 服务启动编排 - ./mysql/conf/**master01.cnf**：主节点 master01 配置文件 - ./mysql/conf/**master02.cnf**：主节点 master02 配置文件 - ./mysql/conf/**slave01.cnf**：从节点 slave01 配置文件 - ./mysql/conf/**slave02.cnf**：从节点 slave02 配置文件 ##### 1.2.1 docker 编排文件 ```yaml version: '3.2' services: mysql-master01: container_name: mysql-master01 image: mysql:8.0.20 ports: - 3306:3306 privileged: true volumes: - $PWD/conf/master01.cnf:/etc/mysql/my.cnf environment: MYSQL_ROOT_PASSWORD: "123456" command: [ '--character-set-server=utf8mb4', '--collation-server=utf8mb4_general_ci', '--max_connections=100' ] networks: - net-mysql mysql-master02: container_name: mysql-master02 image: mysql:8.0.20 ports: - 3307:3306 privileged: true volumes: - $PWD/conf/master02.cnf:/etc/mysql/my.cnf environment: MYSQL_ROOT_PASSWORD: "123456" command: [ '--character-set-server=utf8mb4', '--collation-server=utf8mb4_general_ci', '--max_connections=100' ] networks: - net-mysql mysql-slave01: container_name: mysql-slave01 image: mysql:8.0.20 ports: - 13306:3306 privileged: true volumes: - $PWD/conf/slave01.cnf:/etc/mysql/my.cnf environment: MYSQL_ROOT_PASSWORD: "123456" command: [ '--character-set-server=utf8mb4', '--collation-server=utf8mb4_general_ci', '--max_connections=100' ] networks: - net-mysql mysql-slave02: container_name: mysql-slave02 image: mysql:8.0.20 ports: - 13307:3306 privileged: true volumes: - $PWD/conf/slave02.cnf:/etc/mysql/my.cnf environment: MYSQL_ROOT_PASSWORD: "123456" command: [ '--character-set-server=utf8mb4', '--collation-server=utf8mb4_general_ci', '--max_connections=100' ] networks: - net-mysql networks: net-mysql: driver: bridge ``` ##### 1.2.2 master01.cnf ``` [mysqld] # 服务器唯一id，默认值1 server-id=1 # 设置日志格式，默认值ROW binlog_format=STATEMENT # 二进制日志名，默认binlog # log-bin=binlog # 设置需要复制的数据库，默认复制全部数据库 binlog-do-db=shardingsphere # 设置不需要复制的数据库 #binlog-ignore-db=mysql #binlog-ignore-db=infomation_schema secure_file_priv=/var/lib/mysql ``` ##### 1.2.3 master02.cnf ``` [mysqld] # 服务器唯一id，默认值1 server-id=2 # 设置日志格式，默认值ROW binlog_format=STATEMENT # 二进制日志名，默认binlog # log-bin=binlog # 设置需要复制的数据库，默认复制全部数据库 binlog-do-db=shardingsphere # 设置不需要复制的数据库 #binlog-ignore-db=mysql #binlog-ignore-db=infomation_schema secure_file_priv=/var/lib/mysql ``` ##### 1.2.4 slave01.cnf ``` [mysqld] # 服务器唯一id，默认值1 server-id=3 # 中继日志文件名，默认为 xxx-relay-bin relay-log=relay-bin secure_file_priv=/var/lib/mysql ``` ##### 1.2.5 slave02.cnf ``` [mysqld] # 服务器唯一id，默认值1 server-id=4 # 中继日志文件名，默认为 xxx-relay-bin relay-log=relay-bin secure_file_priv=/var/lib/mysql ``` ##### 1.2.6 binlog_format 说明 - Statement：基于 SQL 语句的复制，不需要记录每一行的变化，减少了 binlog 日志量，节约了IO，提高性能 - Row：基于行的复制，记录每一行数据修改的细节，可能会产生大量的日志 - Mixed：混合模式，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog , MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种 #### 1.3、主从配置 ##### 1.3.1 创建主从同步用户 主节点创建主从同步并授权，主节点为 3306、3307 两个节点 ```sql -- 192.168.200.143:3306 节点 -- 创建slave用户 CREATE USER 'slave'@'%'; -- 设置密码校验格式为 mysql_native_password，设置密码为 123456 ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%'; -- 刷新权限 FLUSH PRIVILEGES; -- 192.168.200.143:3307 节点 -- 创建slave用户 CREATE USER 'slave'@'%'; -- 设置密码校验格式为 mysql_native_password，设置密码为 123456 ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%'; -- 刷新权限 FLUSH PRIVILEGES; ``` ##### 1.3.2 3306 主 & 13306 从 ```sql -- 在 3306 节点上执行，查看 3306 主节点 binlog 文件名及 position 位置 SHOW MASTER STATUS; ``` ```sql -- 在 13306 节点上执行，配置主从关系，主：192.168.200.143:3306，从：192.168.200.143:13306 -- MASTER_HOST 主节点 host -- MASTER_USER 主节点用于主从同步的用户 -- MASTER_PASSWORD 对应主从同步用户密码 -- MASTER_PORT 主节点 port -- MASTER_LOG_FILE binlog 主从同步起始文件 -- MASTER_LOG_POS binlog 主从同步起始偏移位置 CHANGE MASTER TO MASTER_HOST='192.168.200.143', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1324; -- 在 13306 节点上执行，启动从节点 START SLAVE; -- 在 13306 节点上执行，查看从节点状态 -- Slave_IO_Running：Yes 说明从节点读取 binlog 线程就绪 -- Slave_SQL_Running：Yes 说明从节点处理中继日志线程就绪 SHOW SLAVE STATUS; ``` ##### 1.3.3 3307 主 & 13307 从 ```sql -- 在 3307 节点上执行，查看 3307 主节点 binlog 文件名及 position 位置 SHOW MASTER STATUS; ``` ```sql -- 在 13307 节点上执行，配置主从关系，主：192.168.200.143:3307，从：192.168.200.143:13307 CHANGE MASTER TO MASTER_HOST='192.168.200.143', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3307, MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1324; -- 在 13307 节点上执行，启动从节点 START SLAVE; -- 在 13307 节点上执行，查看从节点状态 -- Slave_IO_Running：Yes 说明从节点读取 binlog 线程就绪 -- Slave_SQL_Running：Yes 说明从节点处理中继日志线程就绪 SHOW SLAVE STATUS; ``` ##### 1.3.4 3306 主 & 3307 从 ```sql -- 在 3306 节点上执行，查看 3306 主节点 binlog 文件名及 position 位置 SHOW MASTER STATUS; ``` ```sql -- 在 3307 节点上执行，配置主从关系，主：192.168.200.143:3306，从：192.168.200.143:3307 CHANGE MASTER TO MASTER_HOST='192.168.200.143', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1324; -- 在 3307 节点上执行，启动从节点 START SLAVE; -- 在 3307 节点上执行，查看从节点状态 -- Slave_IO_Running：Yes 说明从节点读取 binlog 线程就绪 -- Slave_SQL_Running：Yes 说明从节点处理中继日志线程就绪 SHOW SLAVE STATUS; ``` ##### 1.3.5 3307 主 & 3306 从 ```sql -- 在 3307 节点上执行，查看 3307 主节点 binlog 文件名及 position 位置 SHOW MASTER STATUS; ``` ```sql -- 在 3306 节点上执行，配置主从关系，主：192.168.200.143:3307，从：192.168.200.143:3306 CHANGE MASTER TO MASTER_HOST='192.168.200.143', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3307, MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1324; -- 在 3306 节点上执行，启动从节点 START SLAVE; -- 在 3306 节点上执行，查看从节点状态 -- Slave_IO_Running：Yes 说明从节点读取 binlog 线程就绪 -- Slave_SQL_Running：Yes 说明从节点处理中继日志线程就绪 SHOW SLAVE STATUS; ``` #### 1.4、主从验证 ```sql -- 只在 3306 节点上执行如下语句 -- 创建库 shardingsphere CREATE DATABASE shardingsphere; -- 切换到库 shardingsphere USE shardingsphere; -- 创建表 t_user CREATE TABLE t_user ( id BIGINT AUTO_INCREMENT, uname VARCHAR(30), PRIMARY KEY (id) ); -- 往 shardingsphere.t_user 新增两条记录 INSERT INTO t_user(uname) VALUES('张三'); INSERT INTO t_user(uname) VALUES(@@hostname); -- 在 3307 节点上执行如下语句，往 shardingsphere.t_user 新增两条记录 INSERT INTO t_user(uname) VALUES('李四'); INSERT INTO t_user(uname) VALUES(concat('王五', now()); -- 分别在 3306、3307、13306、13307 上查询 t_user 表记录 SELECT * FROM shardingsphere.t_user; ``` 3306： 3307： 13306： 13307： - 从 3306 或 3307 节点添加的记录，在 3306、3307、13306、13307 节点中均能查询到 - **id 为 2 的 name 取值为 @@hostname，分别是四个 docker 容器的主机名，取值不一致跟主节点的 [mysqld] 中 binlog_format 配置值相关** #### 1.5、主从重置 ```sql -- 在从机上执行，停止 I/O 线程和 SQL 线程的操作 stop slave; -- 在从机上执行，删除 SLAVE 数据库的 relaylog 日志文件，并重新启用新的 relaylog 文件 reset slave; -- 在主机上执行，删除所有的 binglog 日志文件，并将日志索引文件清空，重新开始所有新的日志文件 -- 可用于第一次进行搭建主从库时，进行主库 binlog 初始化工作 reset master; ``` ### 2、读写分离 - 主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善 - 读写分离是根据 SQL 语义的分析，将读操作和写操作分别路由至主库与从库 - 通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力 - 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的**可用性**，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行 #### 2.1、主从同步 1. master 将数据改变记录到 二进制日志（binary log）中 2. slave 上执行 start slave 命令之后，slave 会创建一个 IO 线程用来连接master，请求 master 中的 binlog 3. slave 连接 master 时，master 会创建一个 log dump 线程，用于发送 binlog 的内容，在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁 4. slave 的 IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到中继日志（relay log）中 5. slave 的 SQL线程，读取 relay log 日志，并解析成具体操作，从而实现主从操作一致，最终数据一致 #### 2.2、shardingsphere-jdbc 配置 .\shardingsphere-example\shardingsphere-rw\src\main\resources\application-shardingsphere-rw.yml ```yaml spring: # 如果使用 druid 连接池需去除 druid-spring-boot-starter 依赖中的自动配置 autoconfigure: exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure shardingsphere: mode: # 不配置则默认内存模式 # 可选配置：Memory、Standalone、Cluster type: Memory # 开启 SQL 打印，可查看 Logic SQL 和 Actual SQL props: sql-show: true # 配置实际数据源 datasource: # 配置数据源名称 names: ,, # 对应数据源名称下具体详细配置 : url: jdbc:mysql://192.168.200.143:3306/shardingsphere username: root password: 123456 # 提取数据源相同配置，此处为引用，具体配置查看代码 <<: *datasource-common # 对应数据源名称下具体详细配置 : url: jdbc:mysql://192.168.200.143:13306/shardingsphere username: root password: 123456 <<: *datasource-common # 对应数据源名称下具体详细配置 : url: jdbc:mysql://192.168.200.143:13307/shardingsphere username: root password: 123456 <<: *datasource-common # 规则配置 rules: # 读写分离配置 readwrite-splitting: data-sources: # 读写分离逻辑数据源名称 : # 读写分离类型，比如：Static，Dynamic type: Static # 负载均衡算法名称，需与下文负载策略名称对应 loadBalancerName: props: # 自动发现数据源名称（与数据库发现配合使用） # auto-aware-data-source-name: # 写库数据源名称 write-data-source-name: master01 read-data-source-names: slave01,slave02 # 负载均衡算法配置 load-balancers: # 负载策略名称 : # 负载均衡算法类型-轮询 type: ROUND_ROBIN # 负载策略名称 : # 负载均衡算法类型-随机 type: RANDOM # 负载策略名称 : # 负载均衡算法类型-权重 type: WEIGHT # 负载均衡算法属性配置 props: slave01: 1 slave02: 3 ``` #### 2.3、配置说明 ##### 2.3.1、运行模式 - Memory：内存模式，默认的运行模式，内存模式下用户无需配置任何持久化组件和策略，无论是本地初始化的配置还是通过 SQL/DistSQL 操作造成的元数据变更，仅在当前进程中生效， 服务重启后配置将会被还原，内存模式适用于集成测试环境，方便整合功能测试中集成 ShardingSphere 而无需清理运行痕迹 - Standalone：单机模式，ShardingSphere 为单机模式默认提供了本地文件的持久化方式，能够将数据源和规则等元数据信息持久化到本地文件中，而在服务重启的时候，依然能够从本地文件中读取配置，保持元数据和重启之前的版本一致 - Cluster：集群模式，生产环境建议使用该模式，集群模式提供了分布式治理的功能，通过集成独立部署的第三方注册中心，除了能够持久化元数据之外，同时实现了多个实例之间的数据共享以及分布式场景下的状态协调 ##### 2.3.2、负载策略 参考：https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/load-balance/ 高本版内置更多负载算法 - ROUND_ROBIN：轮询算法，事务内，读请求路由到 primary，事务外，采用轮询策略路由到 replica - RANDOM：随机算法，事务内，读请求路由到 primary，事务外，采用轮询策略路由到 replica - WEIGHT：权重负载算法，事务内，读请求路由到 primary，事务外，采用轮询策略路由到 replica ##### 2.3.3、事务处理 在 ShardingSphere 5.1.1 版本中，ROUND_ROBIN、RANDOM、WEIGHT 三种负载策略在开启事务的情况下，为了保证主从库之间的事务一致性，避免跨库事务，读写均采用主库，更高版本中内置更多负载策略，根据不同的策略有不同的处理方式 ### 3、数据分片 - 将存放在单一数据库中的数据分散地存放至多个数据库或表中，以达到提升性能瓶颈以及可用性的效果，数据分片的有效手段是对关系型数据库进行分库和分表，数据分片的拆分方式又分为垂直分片和水平分片 - 单表切分为多表后，新的表即使在同一个数据库服务器中，也可能带来可观的性能提升，如果性能能够满足业务要求，可以不拆分到多台数据库服务器，毕竟业务分库也会引入很多复杂性 - 如果单表拆分为多表后，单台服务器依然无法满足性能要求，那就需要将多个表分散在不同的数据库服务器中 #### 3.1、垂直分片 ##### 3.1.1、初始化库表 shardingsphere01.USER_INFO 在 3306 节点上，shardingsphere02.ORGANIZATION_INFO 在 3307 节点上 ```sql -- 在 3306 执行如下 SQL CREATE DATABASE shardingsphere01; USE shardingsphere01; DROP TABLE IF EXISTS `USER_INFO`; CREATE TABLE `USER_INFO` ( `ID` bigint NOT NULL AUTO_INCREMENT, `NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `ORG_ID` bigint DEFAULT NULL, `ORG_NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- 在 3307 执行如下 SQL CREATE DATABASE shardingsphere02; USE shardingsphere02; DROP TABLE IF EXISTS `ORGANIZATION_INFO`; CREATE TABLE `ORGANIZATION_INFO` ( `ID` bigint NOT NULL AUTO_INCREMENT, `PID` bigint NOT NULL, `NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `DESCRIPTION` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; SELECT * FROM shardingsphere02.ORGANIZATION_INFO; ``` ##### 3.1.2、shardingsphere-jdbc 配置 ```yaml spring: autoconfigure: exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure shardingsphere: mode: # 不配置则默认内存模式 # 可选配置：Memory、Standalone、Cluster type: Memory # 开启 SQL 打印，可查看 Logic SQL 和 Actual SQL props: sql-show: true # 配置实际数据源 datasource: # 配置数据源名称 names: , # 对应数据源名称下具体详细配置 : url: jdbc:mysql://192.168.200.143:3306/shardingsphere01 username: root password: 123456 # 提取数据源相同配置，此处为引用 <<: *datasource-common # 对应数据源名称下具体详细配置 : url: jdbc:mysql://192.168.200.143:3307/shardingsphere02 username: root password: 123456 <<: *datasource-common rules: sharding: # 分片表配置，即逻辑表和对应的物理表 tables: # 逻辑表名 : # 对应物理表名 actual-data-nodes: shardingsphere01.USER_INFO : actual-data-nodes: shardingsphere02.ORGANIZATION_INFO ``` #### 3.2、水平分片 ##### 3.2.1、初始化库表 - USER_INFO 在 3306、3307 节点上各存在两个个分片，分别为 USER_INFO_01、USER_INFO_02 - APP_LOG 在 3306、3307 节点上各存在两个分片，分别为 APP_LOG_01、APP_LOG_02 ```sql -- 分别在 3306、3307 节点执行如下 SQL CREATE DATABASE shardingsphere03; USE shardingsphere03; DROP TABLE IF EXISTS `USER_INFO_01`; CREATE TABLE `USER_INFO_01` ( `ID` bigint NOT NULL, `NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `ORG_ID` bigint DEFAULT NULL, `ORG_NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS `USER_INFO_02`; CREATE TABLE `USER_INFO_02` ( `ID` bigint NOT NULL, `NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `ORG_ID` bigint DEFAULT NULL, `ORG_NAME` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS `APP_LOG_01`; CREATE TABLE `APP_LOG_01` ( `ID` bigint(20) NOT NULL COMMENT '编号', `USER_ID` bigint(20) DEFAULT NULL COMMENT '操作用户', `START_TIME` datetime DEFAULT NULL COMMENT '操作时间', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS `APP_LOG_02`; CREATE TABLE `APP_LOG_02` ( `ID` bigint(20) NOT NULL COMMENT '编号', `USER_ID` bigint(20) DEFAULT NULL COMMENT '操作用户', `START_TIME` datetime DEFAULT NULL COMMENT '操作时间', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ``` ##### 3.2.2、shardingsphere-jdbc 配置 - 对应代码 application-sharding-horizontal-bak.yml - 两个数据源：sharding0、sharding1 - 两个逻辑表：USER_INFO、APP_LOG - USER_INFO 对应物理表：sharding0.USER_INFO_01、sharding0.USER_INFO_02、sharding1.USER_INFO_01、sharding1.USER_INFO_02 - APP_LOG 对应物理表：sharding0.APP_LOG_01、sharding0.APP_LOG_02、sharding1.APP_LOG_01、sharding1.APP_LOG_02 - 分库策略 - USER_INFO 分库策略 USER_INFO.ID 对 2 取模 - APP_LOG 分库策略 APP_LOG.USER_ID 对 2 取模 - USER_INFO.ID = APP_LOG.USER_ID，故相同的用户对应 USER_INFO 和 APP_LOG 记录会指向同一个数据源，避免跨库 JOIN - USER_INFO 分表策略：USER_INFO.NAME 取 hashcode 对 2 取模 - APP_LOG 分表策略：APP_LOG.ID 对 2 取模 ```yaml spring: autoconfigure: exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure shardingsphere: mode: # 不配置则默认内存模式 # 可选配置：Memory、Standalone、Cluster type: Memory # 开启 SQL 打印，可查看 Logic SQL 和 Actual SQL props: sql-show: true # 配置实际数据源 datasource: # 配置数据源名称 names: sharding0,sharding1 # 对应数据源名称下具体详细配置 sharding0: url: jdbc:mysql://192.168.200.143:3306/shardingsphere03 username: root password: 123456 # 提取数据源相同配置，此处为引用 <<: *datasource-common # 对应数据源名称下具体详细配置 sharding1: url: jdbc:mysql://192.168.200.143:3307/shardingsphere03 username: root password: 123456 <<: *datasource-common rules: sharding: # 分片表配置，即逻辑表和对应的物理表 tables: # 逻辑表名 USER_INFO: # 物理表 actual-data-nodes: sharding$->{0..1}.USER_INFO_0$->{1..2} # 分库策略 database-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: ID # 指定分片算法 sharding-algorithm-name: mod-id # 分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: NAME # 指定分片算法 sharding-algorithm-name: custom-hash-mod-id # 逻辑表名 APP_LOG: # 物理表 actual-data-nodes: sharding$->{0..1}.APP_LOG_0$->{1..2} # 分库策略 database-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: USER_ID # 指定分片算法 sharding-algorithm-name: mod-id # 分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: ID # 指定分片算法 sharding-algorithm-name: inline-id # 配置分片算法 sharding-algorithms: # 自定义分片算法名称 inline-id: # 分片算法类型-行表达式分片算法 type: INLINE # 分片算法属性设置-两个分片，对2取模，+1表示下标从1开始（即下标为 1、2） props: algorithm-expression: APP_LOG_0$->{ID % 2 + 1} custom-hash-mod-id: # 分片算法类型-自定义分片算法 type: CLASS_BASED # 分片算法属性设置 props: # 支持 STANDARD、COMPLEX 或 HINT strategy: STANDARD # 分片算法实现类 algorithmClassName: com.onde.shardinghorizontal.config.CustomHashModShardingAlgorithm mod-id: # 分片算法类型-根据字段取模，字段需是整数，取模下标从0开始 type: MOD # 分片算法属性设置，分片数量 props: sharding-count: 2 ``` ##### 3.2.3、分片算法 ###### 3.2.3.1 INLINE - 行表达式分片算法 - 表达式：algorithmExpression: $->{分片键 % 分片数} - 使用 Groovy 的表达式，提供对 SQL 语句中的 `=` 和 `IN` 的分片操作支持，表达式可以包括算术运算符、逻辑运算符、函数调用等，可根据实际需求进行组合和嵌套 - 分片键是指用于分片的字段，必须是表中的某个字段，可以是数字、字符串等类型，但取值必须为数字，如是字符串文本类型，可以使用 hashCode() 函数将分片键映射为数字类型 - 分片数是指分片的数量，必须是正整数，通常是 2 的幂次方 - 只支持单分片键 - 只能用于分表，不能用于分库 示例： ```yaml # 此示例从上文 3.2.2 中拆解 # 所有分片 sharding0.APP_LOG_01、sharding0.APP_LOG_02、sharding1.APP_LOG_01、sharding1.APP_LOG_02 # 此处不关注分库策略，只关注 APP_LOG 的分表策略，每个库分别有 APP_LOG_01、APP_LOG_02 两个分片 # 表达式：algorithm-expression: APP_LOG_0$->{ID % 2 + 1}，ID 为分片键对应数据库字段，两个分片，故对2取模，+1表示下标从1开始（默认0开始），即下标为 1、2 # 物理表：actual-data-nodes: sharding$->{0..1}.APP_LOG_0$->{1..2}，APP_LOG_0$->{1..2} 表示物理表集合为[APP_LOG_01,APP_LOG_02] # 配置分片算法 sharding-algorithms: # 自定义分片算法名称 : # 分片算法类型-行表达式分片算法 type: INLINE # 分片算法属性设置-两个分片，对2取模，+1表示下标从1开始（即下标为 1、2） props: algorithm-expression: APP_LOG_0$->{ID % 2 + 1} # 引用 # 逻辑表名 : # 物理表 actual-data-nodes: sharding$->{0..1}.APP_LOG_0$->{1..2} # 分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: ID # 此处引用分片算法 sharding-algorithm-name: ``` ###### 3.2.3.2 CLASS_BASED - 自定义类分片算法 - 步骤 1. 配置分片规则 2. 实现自定义类分片 CustomHashModShardingAlgorithm 3. `META-INF/services` 目录下新建文件 `org.apache.shardingsphere.sharding.spi.ShardingAlgorithm` 添加自定义类的全限定名（SPI 机制） 配置示例： ```yaml # 此示例从上文 3.2.2 中拆解 # 所有分片 sharding0.USER_INFO_01、sharding0.USER_INFO_02、sharding1.USER_INFO_01、sharding1.USER_INFO_02 # 此处不关注分库策略，只关注 USER_INFO 的分表策略，每个库分别有 USER_INFO_01、USER_INFO_02 两个分片 # 物理表：actual-data-nodes: sharding$->{0..1}.USER_INFO_0$->{1..2}，USER_INFO_0$->{1..2} 表示物理表集合为[USER_INFO_01,USER_INFO_02] # 配置分片算法 sharding-algorithms: # 自定义分片算法名称 : # 分片算法类型-自定义分片算法 type: CLASS_BASED # 分片算法属性设置 props: # 支持 STANDARD、COMPLEX 或 HINT strategy: STANDARD # 分片算法实现类 algorithmClassName: com.onde.shardinghorizontal.config.CustomHashModShardingAlgorithm # 引用 # 逻辑表名 : # 物理表 actual-data-nodes: sharding$->{0..1}.USER_INFO_0$->{1..2} # 分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: NAME # 此处引用分片算法 sharding-algorithm-name: ``` CustomHashModShardingAlgorithm 示例： ```java package com.onde.shardinghorizontal.config; import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm; import java.util.Collection; /** * 自定义哈希取模分片策略 * * @author cjiangbo * @date 2023/4/25 */ public class CustomHashModShardingAlgorithm> implements StandardShardingAlgorithm { private final static int THRESHOLD = 10; /** * 当条件为单个值时进入 * * @param collection 所有分片集合，此示例为 [USER_INFO_01, USER_INFO_02]，对应 sharding$->{0..1}.USER_INFO_0$->{1..2} 中 USER_INFO_0$->{1..2}表示的集合 * @param preciseShardingValue 分片键取值 * @return */ @Override public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) { // hashCode 有可能是负数，通过按位与运算符 & 和十六进制数 0x7FFFFFFF 转 long 类型正数 int value = preciseShardingValue.getValue().hashCode() & 0x7FFFFFFF; int index = (value % collection.size()) + 1; String suffix; if (index < THRESHOLD) { suffix = "_0" + index; } else { suffix = "_" + index; } for (String each : collection) { if (each.endsWith(suffix)) { return each; } } throw new UnsupportedOperationException("CUSTOM_HASH_MOD NO SUPPORT" + value); } /** * 当条件为多个值时进入 * * @param collection * @param rangeShardingValue * @return */ @Override public Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) { return collection; } /** * 初始化 */ @Override public void init() { } /** * 自定义算法类型名称 * * @return */ @Override public String getType() { return "CUSTOM_HASH_MOD"; } } ``` org.apache.shardingsphere.sharding.spi.ShardingAlgorithm 文件示例： ``` com.onde.shardinghorizontal.config.CustomHashModShardingAlgorithm ``` ###### 3.2.3.3 其他内置算法 参照官方文档 https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/sharding/ ##### 3.2.4、JOIN 查询放大 ###### 3.2.4.1 问题重现 初始化数据接口： - 需在 3.2.2 的分库分表策略下重现 - 添加测试数据：/app-log/add - 查询数据，重现查询放大：/app-log/list 示例代码： ```java /** * 获取日志列表 * * @return */ @Select({ "SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime", "FROM USER_INFO a", "JOIN APP_LOG b", "ON a.ID = b.USER_ID"}) List logList(); ``` ```sql -- 对应 SQL 如下 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO a JOIN APP_LOG b ON a.ID = b.USER_ID ``` shardingsphere-jdbc 实际执行 SQL 如下： ```sql -- Logic SQL total=11483 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO a JOIN APP_LOG b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=1507 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=1497 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=1699 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=1696 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=905 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=891 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=1648 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=1640 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID ``` 问题描述： - 逻辑表 USER_INFO、APP_LOG 分别有两个 3306、3307 两个节点，每个节点各有两个分片 - 分库策略（sharding0、sharding1） - USER_INFO 分库策略 USER_INFO.ID 对 2 取模 - APP_LOG 分库策略 APP_LOG.USER_ID 对 2 取模 - **分库策略一致，USER_INFO.ID = APP_LOG.USER_ID，故相同的用户对应 USER_INFO 和 APP_LOG 记录会指向同一个数据源，避免跨库 JOIN 存在的问题** - 分表策略（USER_INFO_01、USER_INFO_02、APP_LOG_01、APP_LOG_02） - USER_INFO 分表策略：USER_INFO.NAME 取 hashcode 对 2 取模 - APP_LOG 分表策略：APP_LOG.ID 对 2 取模 - **分表策略不一致导致了上述 JOIN 查询放大的问题，即 1 次关联查询的 Logic SQL 实际需要到 sharding0、sharding1 两个数据源中共执行 8 次 SQL** ###### 3.2.4.2 解决方式 调整分表策略为一致： - 调整后的分片配置对应代码 application-sharding-horizontal.yml - **调整后相同用户的 USER_INFO 记录和 APP_LOG 记录指向相同的分片，即要么指向 USER_INFO_01 & APP_LOG_01 或 USER_INFO_02 & APP_LOG_03，不会产生 USER_INFO_01 & APP_LOG_02 或 USER_INFO_02 & APP_LOG_01 的记录** ```yaml # USER_INFO 旧分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: NAME # 指定分片算法，对应 3.2.3.2 的自定义分片算法 sharding-algorithm-name: custom-hash-mod-id # USER_INFO 调整后的分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: ID # 指定分片算法，对应 com.onde.shardinghorizontal.config.CustomModRangeShardingAlgorithm，此处不展开 sharding-algorithm-name: custom-mod-range-id # APP_LOG 旧分表策略 table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: ID # 指定分片算法 sharding-algorithm-name: inline-id # APP_LOG 调整后的分表策略，注 APP_LOG.USER_ID = USER_INFO.ID table-strategy: standard: # 分片键，对应物理表具体字段 sharding-column: USER_ID # 指定分片算法，对应 com.onde.shardinghorizontal.config.CustomModRangeShardingAlgorithm，此处不展开 sharding-algorithm-name: custom-mod-range-id # 添加绑定配置 spring: shardingsphere: rules: sharding: binding-tables[0]: USER_INFO,APP_LOG ``` 重置数据接口： - 调整配置后重启进行调试 - 清楚数据：/app-log/clear - 添加测试数据：/app-log/add - 查询数据，是否存在查询放大：/app-log/list shardingsphere-jdbc 实际执行 SQL 如下： - **效果：分库策略一致，分表策略一致，同时将 USER_INFO、APP_LOG 作为同一组绑定表，只需执行 4 次 SQL** - 绑定表：指分片规则一致的一组分片表，使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率 ```sql -- Logic SQL: total=3040 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO a JOIN APP_LOG b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=330 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding0 ::: total=1892 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=521 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_01 a JOIN APP_LOG_01 b ON a.ID = b.USER_ID -- Actual SQL: sharding1 ::: total=297 SELECT a.ID AS userId, a.`NAME` AS userName, b.ID AS logId, b.START_TIME AS startTime FROM USER_INFO_02 a JOIN APP_LOG_02 b ON a.ID = b.USER_ID ``` #### 3.3、广播表 ##### 3.3.1、说明 广播表指所有的分片数据源中都存在的表，表结构及其数据在每个数据源中均完全一致，适用于数据量不大且需要与海量数据的表进行关联查询的场景，如字典表 - 插入、更新、删除等操作会实时在所有节点上执行，保持各个分片的数据一致性 - 查询操作，只从一个节点获取 - 广播表可与其他任意表进行 JOIN ##### 3.3.2、初始化库表 ```sql -- 分别在 3306、3307 节点执行如下 SQL USE shardingsphere03; DROP TABLE IF EXISTS `SYS_DICT`; CREATE TABLE `SYS_DICT` ( `ID` bigint, `NAME` varchar(255) DEFAULT NULL, `TYPE` varchar(64) NOT NULL, `VALUE` varchar(128) NOT NULL, PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ``` ##### 3.3.3、shardingsphere-jdbc 配置 ```yaml spring: shardingsphere: rules: sharding: broadcast-tables[0]: SYS_DICT ```