Apache ShardingSphere 分布式数据库中间件详解
基础概念
1. Apache ShardingSphere 概述
Apache ShardingSphere 是一套开源的分布式数据库中间件生态系统，包含 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar 三个独立产品。它专注于在分布式环境中合理运用关系型数据库的能力，提供标准化的数据分片、分布式事务和数据库治理功能。
2. 数据分片技术
随着业务增长，单库单表的数据量会急剧增加，导致性能下降和单点故障风险。数据分片通过将大数据集拆分到多个数据库实例中来解决这些问题。
2.1 分片方式
数据分片主要分为垂直分片和水平分片：
- 垂直分片：按业务模块将不同表分布到不同数据库
- 水平分片：按数据特征将同一表的数据分布到多个数据库
2.2 垂直分片特点
优点：
- 业务边界清晰，易于管理和维护
- 系统集成和扩展相对简单
缺点：
- 跨库 JOIN 操作复杂
- 单库性能仍可能成为瓶颈
- 事务处理变得困难
2.3 水平分片特点
优点：
- 有效控制单表数据量，提升性能
- 表结构统一，应用层改动较小
- 增强系统稳定性和负载能力
缺点：
- 跨库 JOIN 性能较差
- 分片事务一致性难以保证
- 数据扩容和维护复杂度高
Sharding-JDBC 实践
1. 产品介绍
Sharding-JDBC 是 ShardingSphere 的核心组件，定位为轻量级 Java 框架，在 JDBC 层提供额外服务。它以 JAR 包形式提供服务，无需额外部署，完全兼容 JDBC 和各种 ORM 框架。
2. 水平分表实战
2.1 需求分析
通过 Sharding-JDBC 实现订单表的水平分表，将数据按主键奇偶性分发到不同表中。
2.2 数据库准备
CREATE DATABASE `sample_db` CHARACTER SET utf8 COLLATE utf8_general_ci;
DROP TABLE IF EXISTS `order_table_1`;
CREATE TABLE `order_table_1` (
`id` bigint(20) NOT NULL COMMENT '订单ID',
`amount` decimal(10, 2) NOT NULL COMMENT '金额',
`customer_id` bigint(20) NOT NULL COMMENT '客户ID',
`state` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `order_table_2`;
CREATE TABLE `order_table_2` (
`id` bigint(20) NOT NULL COMMENT '订单ID',
`amount` decimal(10, 2) NOT NULL COMMENT '金额',
`customer_id` bigint(20) NOT NULL COMMENT '客户ID',
`state` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2.3 依赖配置
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
2.4 规则配置
# 服务配置
server.port=8080
# 应用配置
spring.application.name=sharding-jdbc-demo
# 数据源配置
spring.shardingsphere.datasource.names=primaryDS
spring.shardingsphere.datasource.primaryDS.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.primaryDS.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.primaryDS.url=jdbc:mysql://localhost:3306/sample_db?useUnicode=true
spring.shardingsphere.datasource.primaryDS.username=root
spring.shardingsphere.datasource.primaryDS.password=root
# 分片规则 - 订单表
spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=primaryDS.order_table_${1..2}
# 主键生成策略
spring.shardingsphere.sharding.tables.order_info.key-generator.column=id
spring.shardingsphere.sharding.tables.order_info.key-generator.type=SNOWFLAKE
# 分表策略
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_table_${id % 2 + 1}
# SQL显示配置
spring.shardingsphere.props.sql.show=true
3. 水平分库实现
将数据按用户ID分发到不同数据库：
# 配置多个数据源
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/order_db_0?useUnicode=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root
# 分库策略
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=customer_id
spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds${customer_id % 2}
4. 垂直分库配置
按业务模块分离数据：
# 用户数据库配置
spring.shardingsphere.datasource.userDS.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.userDS.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.userDS.url=jdbc:mysql://localhost:3306/user_database?useUnicode=true
spring.shardingsphere.datasource.userDS.username=root
spring.shardingsphere.datasource.userDS.password=root
# 用户表分片规则
spring.shardingsphere.sharding.tables.customer_info.actual-data-nodes=userDS.customer_info
spring.shardingsphere.sharding.tables.customer_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.customer_info.table-strategy.inline.algorithm-expression=customer_info
5. 广播表设置
对于需要在所有分片中都存在的表（如字典表）：
spring.shardingsphere.sharding.broadcast-tables=dict_info
6. 读写分离配置
配置主从数据库分离读写操作：
# 从库配置
spring.shardingsphere.datasource.slaveDS.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slaveDS.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slaveDS.url=jdbc:mysql://localhost:3307/sample_db?useUnicode=true
spring.shardingsphere.datasource.slaveDS.username=root
spring.shardingsphere.datasource.slaveDS.password=root
# 主从规则
spring.shardingsphere.sharding.master-slave-rules.userDB.master-data-source-name=primaryDS
spring.shardingsphere.sharding.master-slave-rules.userDB.slave-data-source-names=slaveDS
Sharding-Proxy 配置
1. 产品概述
Sharding-Proxy 定位为数据库代理端，提供数据库二进制协议的服务端版本，支持 MySQL/PostgreSQL 协议，对 DBA 更加友好。
2. 配置示例
2.1 server.yaml 配置
authentication:
username: admin
password: admin123
props:
acceptor.size: 16
executor.size: 32
proxy.transaction.type: XA
proxy.transaction.enabled: true
sql.show: true
2.2 config-sharding.yaml 配置
schemaName: proxy_db
dataSources:
dataSource0:
url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=UTF-8
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 60
dataSource1:
url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF-8
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 60
shardingRule:
tables:
product_order:
actualDataNodes: dataSource${0..1}.product_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: product_order${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
