接上篇,使用文档。
使用文档
数据分片
不使用Spring
引入Maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
Sharding-JDBC的分库分表通过规则配置描述,以下例子是根据user_id取模分库, 且根据order_id取模分表的两库两表的配置。
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0");
dataSource1.setUsername("root");
dataSource1.setPassword("");
dataSourceMap.put("ds0", dataSource1);
// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1");
dataSource2.setUsername("root");
dataSource2.setPassword("");
dataSourceMap.put("ds1", dataSource2);
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");
orderTableRuleConfig.setActualDataNodes("ds${0..1}.t_order${0..1}");
// 配置分库 + 分表策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
// 省略配置order_item表规则...
// ...
// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());
或通过Yaml方式配置,与以上配置等价:
dataSources:
ds0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
ds1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmInlineExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmInlineExpression: t_order${order_id % 2}
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmInlineExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmInlineExpression: t_order_item${order_id % 2}
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(yamlFile);
通过ShardingDataSourceFactory或者YamlShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource,ShardingDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。 以JDBC原生实现为例:
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(yamlFile);
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
使用Spring
引入Maven依赖
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
sharding.jdbc.datasource.names=ds0,ds1
sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=
sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
sharding.jdbc.config.sharding.tables.t-order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
sharding.jdbc.config.sharding.tables.t-order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t-order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t-order-item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
sharding.jdbc.config.sharding.tables.t-order-item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t-order-item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
">
<bean id="ds0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds$->{user_id % 2}" />
<sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order$->{order_id % 2}" />
<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_id" algorithm-expression="t_order_item$->{order_id % 2}" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="ds0,ds1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds$->{0..1}.t_order_item$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy" />
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。
@Resource
private DataSource dataSource;
读写分离
不使用Spring
引入Maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置主库
BasicDataSource masterDataSource = new BasicDataSource();
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://localhost:3306/ds_master");
masterDataSource.setUsername("root");
masterDataSource.setPassword("");
dataSourceMap.put("ds_master", masterDataSource);
// 配置第一个从库
BasicDataSource slaveDataSource1 = new BasicDataSource();
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://localhost:3306/ds_slave0");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("");
dataSourceMap.put("ds_slave0", slaveDataSource1);
// 配置第二个从库
BasicDataSource slaveDataSource2 = new BasicDataSource();
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://localhost:3306/ds_slave1");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("");
dataSourceMap.put("ds_slave1", slaveDataSource2);
// 配置读写分离规则
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave0", "ds_slave1"));
// 获取数据源对象
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new HashMap<String, Object>(), new Properties());
或通过Yaml方式配置,与以上配置等价:
dataSources:
ds_master: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_master
username: root
password:
ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave0
username: root
password:
ds_slave1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave1
username: root
password:
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames: [ds_slave0, ds_slave1]
props:
sql.show: true
configMap:
key1: value1
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile);
通过MasterSlaveDataSourceFactory工厂和规则配置对象获取MasterSlaveDataSource,MasterSlaveDataSource实现自JDBC的标准接口DataSource。然后可通过DataSource选择使用原生JDBC开发,或者使用JPA, MyBatis等ORM工具。 以JDBC原生实现为例:
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(yamlFile);
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while(rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}
}
使用Spring
引入Maven依赖
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
sharding.jdbc.datasource.names=master,slave0,slave1
sharding.jdbc.datasource.master.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master.url=jdbc:mysql://localhost:3306/master
sharding.jdbc.datasource.master.username=root
sharding.jdbc.datasource.master.password=
sharding.jdbc.datasource.slave0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave0.url=jdbc:mysql://localhost:3306/slave0
sharding.jdbc.datasource.slave0.username=root
sharding.jdbc.datasource.slave0.password=
sharding.jdbc.datasource.slave1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave1.url=jdbc:mysql://localhost:3306/slave1
sharding.jdbc.datasource.slave1.username=root
sharding.jdbc.datasource.slave1.password=
sharding.jdbc.config.masterslave.name=ms
sharding.jdbc.config.masterslave.master-data-source-name=master
sharding.jdbc.config.masterslave.slave-data-source-names=slave0,slave1
sharding.jdbc.config.masterslave.props.sql.show=true
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/masterslave
http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd
">
<bean id="ds_master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<master-slave:data-source id="masterSlaveDataSource" master-data-source-name="ds_master" slave-data-source-names="ds_slave0, ds_slave1" >
<master-slave:props>
<prop key="sql.show">${sql_show}</prop>
<prop key="executor.size">10</prop>
<prop key="foo">bar</prop>
</master-slave:props>
</master-slave:data-source>
</beans>
直接通过注入的方式即可使用DataSource,或者将DataSource配置在JPA、Hibernate或MyBatis中使用。
@Resource
private DataSource dataSource;
强制路由
简介
Sharding-Sphere使用ThreadLocal管理分片键值进行Hint强制路由。可以通过编程的方式向HintManager中添加分片条件,该分片条件仅在当前线程内生效。 方式主要使用场景:
1.分片字段不存在SQL中、数据库表结构中,而存在于外部业务逻辑。因此,通过Hint实现外部指定分片结果进行数据操作。
2.强制在主库进行某些数据操作。
基于暗示(Hint)的数据分片
使用hint进行强制数据分片,需要使用HintManager搭配分片策略配置共同使用。若DatabaseShardingStrategy配置了Hint分片算法,则可使用HintManager进行分库路由结果的注入。同理,若TableShardingStrategy配置了Hint分片算法,则同样可 使用HintManager进行分表路由结果的注入。所以使用Hint之前,需要配置Hint分片算法。
参考代码如下:
shardingRule:
tables:
t_order:
actualDataNodes: demo_ds_${0..1}.t_order_${0..1}
databaseStrategy:
hint:
algorithmClassName: io.shardingsphere.userAlgo.HintAlgorithm
tableStrategy:
hint:
algorithmClassName: io.shardingsphere.userAlgo.HintAlgorithm
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: demo_ds_${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
props:
sql.show: true
HintManager hintManager = HintManager.getInstance();
● 使用hintManager.addDatabaseShardingValue来添加数据源分片键值。
● 使用hintManager.addTableShardingValue来添加表分片键值。
分库不分表情况下,强制路由至某一个分库时,可使用hintManager.setDatabaseShardingValue方式添加分片。通过此方式添加分片键值后,将跳过SQL解析和改写阶段,从而提高整体执行效率。
分片键值保存在ThreadLocal中,所以需要在操作结束时调用hintManager.close()来清除ThreadLocal中的内容。
hintManager实现了AutoCloseable接口,可推荐使用try with resource自动关闭。
// Sharding database and table with using hintManager.
String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.addDatabaseShardingValue("t_order", 1);
hintManager.addTableShardingValue("t_order", 2);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
// ...
}
}
}
// Sharding database without sharding table and routing to only one database with using hintManger.
String sql = "SELECT * FROM t_order";
try (HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.setDatabaseShardingValue(3);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
// ...
}
}
}
基于暗示(Hint)的强制主库路由
与基于暗示(Hint)的数据分片相同。
● 使用hintManager.setMasterRouteOnly设置主库路由。
与基于暗示(Hint)的数据分片相同。
String sql = "SELECT * FROM t_order";
try (
HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.setMasterRouteOnly();
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
// ...
}
}
}
数据治理
使用数据治理功能需要指定一个注册中心。配置将全部存入注册中心,可以在每次启动时使用本地配置覆盖注册中心配置,也可以只通过注册中心读取配置。
不使用Spring
引入Maven依赖
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用zookeeper, 请加入下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
</dependency>
<!--若使用etcd, 请下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-etcd</artifactId>
</dependency>
// 省略配置dataSourceMap以及shardingRuleConfig
// ...
// 配置注册中心
RegistryCenterConfiguration regConfig = new RegistryCenterConfiguration();
regConfig.setServerLists("localhost:2181");
regConfig.setNamespace("sharding-sphere-orchestration");
// 配置数据治理
OrchestrationConfiguration orchConfig = new OrchestrationConfiguration("orchestration-sharding-data-source", regConfig, false);
// 获取数据源对象
DataSource dataSource = OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties(), orchConfig);
或通过Yaml方式配置,与以上配置等价:
orchestration:
name: orchestration-sharding-data-source
overwrite: false
registry:
serverLists: localhost:2181
namespace: sharding-sphere-orchestration
DataSource dataSource = YamlOrchestrationShardingDataSourceFactory.createDataSource(yamlFile);
使用Spring
引入Maven依赖
<!-- for spring boot -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用zookeeper, 请加入下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用etcd, 请加入下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-etcd</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用zookeeper, 请加入下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!--若使用etcd, 请加入下面Maven坐标-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-etcd</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
sharding.jdbc.config.orchestration.name=orchestration-sharding-data-source
sharding.jdbc.config.orchestration.overwrite=false
sharding.jdbc.config.orchestration.registry.server-lists=localhost:2181
sharding.jdbc.config.orchestration.registry.namespace=sharding-jdbc-orchestration
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:orchestraion="http://shardingsphere.io/schema/shardingsphere/orchestration"
xmlns="http://www.springframework.org/schema/beans"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/orchestration
http://shardingsphere.io/schema/shardingsphere/orchestration/orchestration.xsd">
<import resource="namespace/shardingDataSourceNamespace.xml" />
<orchestraion:registry-center id="regCenter" server-lists="localhost:3181" namespace="orchestration-spring-namespace-test" operation-timeout-milliseconds="1000" max-retries="3" />
<orchestraion:sharding-data-source id="simpleShardingOrchestration" data-source-ref="simpleShardingDataSource" registry-center-ref="regCenter" />
</beans>
Java配置
配置示例
DataSource getShardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
shardingRuleConfig.getBroadcastTables().add("t_config");
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new ModuloShardingTableAlgorithm()));
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig);
}
TableRuleConfiguration getOrderTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("t_order");
result.setActualDataNodes("ds${0..1}.t_order${0..1}");
result.setKeyGeneratorColumnName("order_id");
return result;
}
TableRuleConfiguration getOrderItemTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("t_order_item");
result.setActualDataNodes("ds${0..1}.t_order_item${0..1}");
return result;
}
Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>();
result.put("ds0", DataSourceUtil.createDataSource("ds0"));
result.put("ds1", DataSourceUtil.createDataSource("ds1"));
return result;
}
DataSource getMasterSlaveDataSource() throws SQLException {
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration();
masterSlaveRuleConfig.setName("ds_master_slave");
masterSlaveRuleConfig.setMasterDataSourceName("ds_master");
masterSlaveRuleConfig.setSlaveDataSourceNames(Arrays.asList("ds_slave0", "ds_slave1"));
return MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRuleConfig, new LinkedHashMap<String, Object>(), new Properties());
}
Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>();
result.put("ds_master", DataSourceUtil.createDataSource("ds_master"));
result.put("ds_slave0", DataSourceUtil.createDataSource("ds_slave0"));
result.put("ds_slave1", DataSourceUtil.createDataSource("ds_slave1"));
return result;
}
DataSource getDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
shardingRuleConfig.getBroadcastTables().add("t_config");
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new ModuloShardingDatabaseAlgorithm()));
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new ModuloShardingTableAlgorithm()));
shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations());
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new HashMap<String, Object>(), new Properties());
}
TableRuleConfiguration getOrderTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("t_order");
result.setActualDataNodes("ds_${0..1}.t_order_${[0, 1]}");
result.setKeyGeneratorColumnName("order_id");
return result;
}
TableRuleConfiguration getOrderItemTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration();
result.setLogicTable("t_order_item");
result.setActualDataNodes("ds_${0..1}.t_order_item_${[0, 1]}");
return result;
}
List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() {
MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration("ds_0", "demo_ds_master_0", Arrays.asList("demo_ds_master_0_slave_0", "demo_ds_master_0_slave_1"));
MasterSlaveRuleConfiguration masterSlaveRuleConfig2 = new MasterSlaveRuleConfiguration("ds_1", "demo_ds_master_1", Arrays.asList("demo_ds_master_1_slave_0", "demo_ds_master_1_slave_1"));
return Lists.newArrayList(masterSlaveRuleConfig1, masterSlaveRuleConfig2);
}
Map<String, DataSource> createDataSourceMap() {
final Map<String, DataSource> result = new HashMap<>();
result.put("demo_ds_master_0", DataSourceUtil.createDataSource("demo_ds_master_0"));
result.put("demo_ds_master_0_slave_0", DataSourceUtil.createDataSource("demo_ds_master_0_slave_0"));
result.put("demo_ds_master_0_slave_1", DataSourceUtil.createDataSource("demo_ds_master_0_slave_1"));
result.put("demo_ds_master_1", DataSourceUtil.createDataSource("demo_ds_master_1"));
result.put("demo_ds_master_1_slave_0", DataSourceUtil.createDataSource("demo_ds_master_1_slave_0"));
result.put("demo_ds_master_1_slave_1", DataSourceUtil.createDataSource("demo_ds_master_1_slave_1"));
return result;
}
DataSource getDataSource() throws SQLException {
return OrchestrationShardingDataSourceFactory.createDataSource(
createDataSourceMap(), createShardingRuleConfig(), new HashMap<String, Object>(), new Properties(),
new OrchestrationConfiguration("orchestration-sharding-data-source", getRegistryCenterConfiguration(), false));
}
private RegistryCenterConfiguration getRegistryCenterConfiguration() {
RegistryCenterConfiguration regConfig = new RegistryCenterConfiguration();
regConfig.setServerLists("localhost:2181");
regConfig.setNamespace("sharding-sphere-orchestration");
return regConfig;
}
配置项说明
数据分片
数据分片的数据源创建工厂。
名称 | 数据类型 | 说明 |
---|---|---|
dataSourceMap | Map<String, DataSource> | 数据源配置 |
shardingRuleConfig | ShardingRuleConfiguration | 数据分片配置规则 |
configMap (?) | Map<String, Object> | 用户自定义配置 |
props (?) | Properties | 属性配置 |
分片规则配置对象。
名称 | 数据类型 | 说明 |
---|---|---|
tableRuleConfigs | Collection | 分片规则列表 |
bindingTableGroups (?) | Collection | 绑定表规则列表 |
broadcastTables (?) | Collection | 绑定表规则列表 |
defaultDataSourceName (?) | String | 未配置分片规则的表将通过默认数据源定位 |
defaultDatabaseShardingStrategyConfig (?) | ShardingStrategyConfiguration | 默认分库策略 |
defaultTableShardingStrategyConfig (?) | ShardingStrategyConfiguration | 默认分表策略 |
defaultKeyGenerator (?) | KeyGenerator | 默认自增列值生成器,缺省使用io.shardingsphere.core.keygen.DefaultKeyGenerator |
masterSlaveRuleConfigs (?) | Collection | 读写分离规则,缺省表示不使用读写分离 |
表分片规则配置对象。
名称 | 数据类型 | 说明 |
---|---|---|
logicTable | String | 逻辑表名称 |
actualDataNodes (?) | String | 由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况 |
databaseShardingStrategyConfig (?) | ShardingStrategyConfiguration | 分库策略,缺省表示使用默认分库策略 |
tableShardingStrategyConfig (?) | ShardingStrategyConfiguration | 分表策略,缺省表示使用默认分表策略 |
logicIndex (?) | String | 逻辑索引名称,对于分表的Oracle/PostgreSQL数据库中DROP INDEX XXX语句,需要通过配置逻辑索引名称定位所执行SQL的真实分表 |
keyGeneratorColumnName (?) | String | 自增列名称,缺省表示不使用自增主键生成器 |
keyGenerator (?) | KeyGenerator | 自增列值生成器,缺省表示使用默认自增主键生成器 |
StandardShardingStrategyConfiguration
ShardingStrategyConfiguration的实现类,用于单分片键的标准分片场景。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumn | String | 分片列名称 |
preciseShardingAlgorithm | PreciseShardingAlgorithm | 精确分片算法,用于=和IN |
rangeShardingAlgorithm (?) | RangeShardingAlgorithm | 范围分片算法,用于BETWEEN |
ComplexShardingStrategyConfiguration
ShardingStrategyConfiguration的实现类,用于多分片键的复合分片场景。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumns | String | 分片列名称,多个列以逗号分隔 |
shardingAlgorithm | ComplexKeysShardingAlgorithm | 复合分片算法 |
InlineShardingStrategyConfiguration
ShardingStrategyConfiguration的实现类,用于配置行表达式分片策略。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumn | String | 分片列名称 |
algorithmExpression | String | 分片算法行表达式,需符合groovy语法,详情请参考行表达式 |
HintShardingStrategyConfiguration
ShardingStrategyConfiguration的实现类,用于配置Hint方式分片策略。
名称 | 数据类型 | 说明 |
---|---|---|
shardingAlgorithmHint | ShardingAlgorithmHint | 分片算法 |
NoneShardingStrategyConfiguration
ShardingStrategyConfiguration的实现类,用于配置不分片的策略。
属性配置项,可以为以下属性。
名称 | 数据类型 | 说明 |
---|---|---|
sql.show (?) | boolean | 是否开启SQL显示,默认值: false |
executor.size (?) | int | 工作线程数量,默认值: CPU核数 |
用户自定义配置。
读写分离
读写分离的数据源创建工厂。
名称 | 数据类型 | 说明 |
---|---|---|
dataSourceMap | Map<String, DataSource> | 数据源与其名称的映射 |
masterSlaveRuleConfig | MasterSlaveRuleConfiguration | 读写分离规则 |
configMap (?) | Map<String, Object> | 用户自定义配置 |
props (?) | Properties | 属性配置 |
读写分离规则配置对象。
名称 | 数据类型 | 说明 |
---|---|---|
name | String | 读写分离数据源名称 |
masterDataSourceName | String | 主库数据源名称 |
slaveDataSourceNames | Collection | 从库数据源名称列表 |
loadBalanceAlgorithm (?) | MasterSlaveLoadBalanceAlgorithm | 从库负载均衡算法 |
用户自定义配置。
属性配置项,可以为以下属性。
名称 | 数据类型 | 说明 |
---|---|---|
sql.show (?) | boolean | 是否打印SQL解析和改写日志,默认值: false |
executor.size (?) | int | 用于SQL执行的工作线程数量,为零则表示无限制。默认值: 0 |
max.connections.size.per.query (?) | int | 每个物理数据库为每次查询分配的最大连接数量。默认值: 1 |
数据治理
OrchestrationShardingDataSourceFactory
数据分片 + 数据治理的数据源工厂。
名称 | 数据类型 | 说明 |
---|---|---|
dataSourceMap | Map<String, DataSource> | 同ShardingDataSourceFactory |
shardingRuleConfig | ShardingRuleConfiguration | 同ShardingDataSourceFactory |
configMap (?) | Map<String, Object> | 同ShardingDataSourceFactory |
props (?) | Properties | 同ShardingDataSourceFactory |
orchestrationConfig | OrchestrationConfiguration | 数据治理规则配置 |
OrchestrationMasterSlaveDataSourceFactory
读写分离 + 数据治理的数据源工厂。
名称 | 数据类型 | 说明 |
---|---|---|
dataSourceMap | Map<String, DataSource> | 同MasterSlaveDataSourceFactory |
masterSlaveRuleConfig | MasterSlaveRuleConfiguration | 同MasterSlaveDataSourceFactory |
configMap (?) | Map<String, Object> | 同MasterSlaveDataSourceFactory |
props (?) | Properties | 同ShardingDataSourceFactory |
orchestrationConfig | OrchestrationConfiguration | 数据治理规则配置 |
数据治理规则配置对象。
名称 | 数据类型 | 说明 |
---|---|---|
name | String | 数据治理实例名称 |
overwrite | boolean | 本地配置是否覆盖注册中心配置,如果可覆盖,每次启动都以本地配置为准 |
regCenterConfig | RegistryCenterConfiguration | 注册中心配置 |
用于配置注册中心。
名称 | 数据类型 | 说明 |
---|---|---|
serverLists | String | 连接注册中心服务器的列表。包括IP地址和端口号。多个地址用逗号分隔。如: host1:2181,host2:2181 |
namespace (?) | String | 注册中心的命名空间 |
digest (?) | String | 连接注册中心的权限令牌。缺省为不需要权限验证 |
operationTimeoutMilliseconds (?) | int | 操作超时的毫秒数,默认500毫秒 |
maxRetries (?) | int | 连接失败后的最大重试次数,默认3次 |
retryIntervalMilliseconds (?) | int | 重试间隔毫秒数,默认500毫秒 |
timeToLiveSeconds (?) | int | 临时节点存活秒数,默认60秒 |
Yaml配置
配置示例
dataSources:
ds0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
ds1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
shardingRule:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
keyGeneratorColumnName: order_id
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_config
defaultDataSourceName: ds0
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
props:
sql.show: true
dataSources:
ds_master: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_master
username: root
password:
ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave0
username: root
password:
ds_slave1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_slave1
username: root
password:
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames:
- ds_slave0
- ds_slave1
dataSources:
ds0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
ds0_slave0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0_slave0
username: root
password:
ds0_slave1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0_slave1
username: root
password:
ds1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
ds1_slave0: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1_slave0
username: root
password:
ds1_slave1: !!org.apache.commons.dbcp.BasicDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1_slave1
username: root
password:
shardingRule:
tables:
t_order:
actualDataNodes: ms_ds${0..1}.t_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
keyGeneratorColumnName: order_id
t_order_item:
actualDataNodes: ms_ds${0..1}.t_order_item${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_config
defaultDataSourceName: ds_0
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ms_ds${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
masterSlaveRules:
ms_ds0:
masterDataSourceName: ds0
slaveDataSourceNames:
- ds0_slave0
- ds0_slave1
loadBalanceAlgorithmType: ROUND_ROBIN
configMap:
master-slave-key0: master-slave-value0
ms_ds1:
masterDataSourceName: ds1
slaveDataSourceNames:
- ds1_slave0
- ds1_slave1
loadBalanceAlgorithmType: ROUND_ROBIN
configMap:
master-slave-key1: master-slave-value1
props:
sql.show: true
#省略数据分片和读写分离配置
orchestration:
name: orchestration_ds
overwrite: true
registry:
namespace: orchestration
serverLists: localhost:2181
配置项说明
dataSources: #数据源配置,可配置多个data_source_name
<data_source_name>: #<!!数据库连接池实现类> `!!`表示实例化该类
driverClassName: #数据库驱动类名
url: #数据库url连接
username: #数据库用户名
password: #数据库密码
# ... 数据库连接池的其它属性
shardingRule:
tables: #数据分片规则配置,可配置多个logic_table_name
<logic_table_name>: #逻辑表名称
actualDataNodes: #由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况
databaseStrategy: #分库策略,缺省表示使用默认分库策略,以下的分片策略只能选其一
standard: #用于单分片键的标准分片场景
shardingColumn: #分片列名称
preciseAlgorithmClassName: #精确分片算法类名称,用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
rangeAlgorithmClassName: #范围分片算法类名称,用于BETWEEN,可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
complex: #用于多分片键的复合分片场景
shardingColumns: #分片列名称,多个列以逗号分隔
algorithmClassName: #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
inline: #行表达式分片策略
shardingColumn: #分片列名称
algorithmInlineExpression: #分片算法行表达式,需符合groovy语法
hint: #Hint分片策略
algorithmClassName: #Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
none: #不分片
tableStrategy: #分表策略,同分库策略
keyGeneratorColumnName: #自增列名称,缺省表示不使用自增主键生成器
keyGeneratorClassName: #自增列值生成器类名称。该类需实现KeyGenerator接口并提供无参数的构造器
logicIndex: #逻辑索引名称,对于分表的Oracle/PostgreSQL数据库中DROP INDEX XXX语句,需要通过配置逻辑索引名称定位所执行SQL的真实分表
bindingTables: #绑定表规则列表
- <logic_table_name1, logic_table_name2, ...>
- <logic_table_name3, logic_table_name4, ...>
- <logic_table_name_x, logic_table_name_y, ...>
bindingTables: #广播表规则列表
- table_name1
- table_name2
- table_name_x
defaultDataSourceName: #未配置分片规则的表将通过默认数据源定位
defaultDatabaseStrategy: #默认数据库分片策略,同分库策略
defaultTableStrategy: #默认表分片策略,同分库策略
defaultKeyGeneratorClassName: #默认自增列值生成器类名称,缺省使用io.shardingsphere.core.keygen.DefaultKeyGenerator。该类需实现KeyGenerator接口并提供无参数的构造器
masterSlaveRules: #读写分离规则,详见读写分离部分
<data_source_name>: #数据源名称,需要与真实数据源匹配,可配置多个data_source_name
masterDataSourceName: #详见读写分离部分
slaveDataSourceNames: #详见读写分离部分
loadBalanceAlgorithmClassName: #详见读写分离部分
loadBalanceAlgorithmType: #详见读写分离部分
configMap: #用户自定义配置
key1: value1
key2: value2
keyx: valuex
props: #属性配置
sql.show: #是否开启SQL显示,默认值: false
executor.size: #工作线程数量,默认值: CPU核数
configMap: #用户自定义配置
key1: value1
key2: value2
keyx: valuex
dataSources: #省略数据源配置,与数据分片一致
masterSlaveRule:
name: #读写分离数据源名称
masterDataSourceName: #主库数据源名称
slaveDataSourceNames: #从库数据源名称列表
- <data_source_name1>
- <data_source_name2>
- <data_source_name_x>
loadBalanceAlgorithmClassName: #从库负载均衡算法类名称。该类需实现MasterSlaveLoadBalanceAlgorithm接口且提供无参数构造器
loadBalanceAlgorithmType: #从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`loadBalanceAlgorithmClassName`存在则忽略该配置
configMap: #用户自定义配置
key1: value1
key2: value2
keyx: valuex
props: #属性配置
sql.show: #是否开启SQL显示,默认值: false
executor.size: #工作线程数量,默认值: CPU核数
dataSources: #省略数据源配置
shardingRule: #省略分片规则配置
masterSlaveRule: #省略读写分离规则配置
orchestration:
name: #数据治理实例名称
overwrite: #本地配置是否覆盖注册中心配置。如果可覆盖,每次启动都以本地配置为准
registry: #注册中心配置
serverLists: #连接注册中心服务器的列表。包括IP地址和端口号。多个地址用逗号分隔。如: host1:2181,host2:2181
namespace: #注册中心的命名空间
digest: #连接注册中心的权限令牌。缺省为不需要权限验证
operationTimeoutMilliseconds: #操作超时的毫秒数,默认500毫秒
maxRetries: #连接失败后的最大重试次数,默认3次
retryIntervalMilliseconds: #重试间隔毫秒数,默认500毫秒
timeToLiveSeconds: #临时节点存活秒数,默认60秒
Yaml语法说明
!! 表示实例化该类
- 表示可以包含一个或多个
[] 表示数组,可以与减号相互替换使用
Spring Boot配置
注意事项
行表达式标识符可以使用...或{...}或...或->{…},但前者与Spring本身的属性文件占位符冲突,因此在Spring环境中使用行表达式标识符建议使用$->{…}。
配置示例
sharding.jdbc.datasource.names=ds0,ds1
sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=
sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id
sharding.jdbc.config.sharding.binding-tables=t_order,t_order_item
sharding.jdbc.config.sharding.broadcast-tables=t_config
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
sharding.jdbc.datasource.names=master,slave0,slave1
sharding.jdbc.datasource.master.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master.url=jdbc:mysql://localhost:3306/master
sharding.jdbc.datasource.master.username=root
sharding.jdbc.datasource.master.password=
sharding.jdbc.datasource.slave0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave0.url=jdbc:mysql://localhost:3306/slave0
sharding.jdbc.datasource.slave0.username=root
sharding.jdbc.datasource.slave0.password=
sharding.jdbc.datasource.slave1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.slave1.url=jdbc:mysql://localhost:3306/slave1
sharding.jdbc.datasource.slave1.username=root
sharding.jdbc.datasource.slave1.password=
sharding.jdbc.config.masterslave.load-balance-algorithm-type=round_robin
sharding.jdbc.config.masterslave.name=ms
sharding.jdbc.config.masterslave.master-data-source-name=master
sharding.jdbc.config.masterslave.slave-data-source-names=slave0,slave1
sharding.jdbc.config.masterslave.props.sql.show=true
sharding.jdbc.datasource.names=master0,master1,master0slave0,master0slave1,master1slave0,master1slave1
sharding.jdbc.datasource.master0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master0.url=jdbc:mysql://localhost:3306/master0
sharding.jdbc.datasource.master0.username=root
sharding.jdbc.datasource.master0.password=
sharding.jdbc.datasource.master0slave0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master0slave0.url=jdbc:mysql://localhost:3306/master0slave0
sharding.jdbc.datasource.master0slave0.username=root
sharding.jdbc.datasource.master0slave0.password=
sharding.jdbc.datasource.master0slave1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master0slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master0slave1.url=jdbc:mysql://localhost:3306/master0slave1
sharding.jdbc.datasource.master0slave1.username=root
sharding.jdbc.datasource.master0slave1.password=
sharding.jdbc.datasource.master1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master1.url=jdbc:mysql://localhost:3306/master1
sharding.jdbc.datasource.master1.username=root
sharding.jdbc.datasource.master1.password=
sharding.jdbc.datasource.master1slave0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master1slave0.url=jdbc:mysql://localhost:3306/master1slave0
sharding.jdbc.datasource.master1slave0.username=root
sharding.jdbc.datasource.master1slave0.password=
sharding.jdbc.datasource.master1slave1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.master1slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.master1slave1.url=jdbc:mysql://localhost:3306/master1slave1
sharding.jdbc.datasource.master1slave1.username=root
sharding.jdbc.datasource.master1slave1.password=
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id
sharding.jdbc.config.sharding.binding-tables=t_order,t_order_item
sharding.jdbc.config.sharding.broadcast-tables=t_config
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=master$->{user_id % 2}
sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=master0
sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0, master0slave1
sharding.jdbc.config.sharding.master-slave-rules.ds1.master-data-source-name=master1
sharding.jdbc.config.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0, master1slave1
sharding.jdbc.datasource.names=ds,ds0,ds1
sharding.jdbc.datasource.ds.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds.driver-class-name=org.h2.Driver
sharding.jdbc.datasource.ds.url=jdbc:mysql://localhost:3306/ds
sharding.jdbc.datasource.ds.username=root
sharding.jdbc.datasource.ds.password=
sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=
sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=
sharding.jdbc.config.sharding.default-data-source-name=ds
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_id
sharding.jdbc.config.sharding.binding-tables=t_order,t_order_item
sharding.jdbc.config.sharding.broadcast-tables=t_config
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=master$->{user_id % 2}
sharding.jdbc.config.orchestration.name=spring_boot_ds_sharding
sharding.jdbc.config.orchestration.overwrite=true
sharding.jdbc.config.orchestration.registry.namespace=orchestration-spring-boot-sharding-test
sharding.jdbc.config.orchestration.registry.server-lists=localhost:2181
配置项说明
sharding.jdbc.datasource.names= #数据源名称,多数据源以逗号分隔
sharding.jdbc.datasource.<data-source-name>.type= #数据库连接池类名称
sharding.jdbc.datasource.<data-source-name>.driver-class-name= #数据库驱动类名
sharding.jdbc.datasource.<data-source-name>.url= #数据库url连接
sharding.jdbc.datasource.<data-source-name>.username= #数据库用户名
sharding.jdbc.datasource.<data-source-name>.password= #数据库密码
sharding.jdbc.datasource.<data-source-name>.xxx= #数据库连接池的其它属性
sharding.jdbc.config.sharding.tables.<logic-table-name>.actual-data-nodes= #由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况
#分库策略,缺省表示使用默认分库策略,以下的分片策略只能选其一
#用于单分片键的标准分片场景
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.sharding-column= #分片列名称
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.precise-algorithm-class-name= #精确分片算法类名称,用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name= #范围分片算法类名称,用于BETWEEN,可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#用于多分片键的复合分片场景
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.complex.sharding-columns= #分片列名称,多个列以逗号分隔
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.complex.algorithm-class-name= #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
#行表达式分片策略
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.inline.sharding-column= #分片列名称
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.inline.algorithm-expression= #分片算法行表达式,需符合groovy语法
#Hint分片策略
sharding.jdbc.config.sharding.tables.<logic-table-name>.database-strategy.hint.algorithm-class-name= #Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
#分表策略,同分库策略
sharding.jdbc.config.sharding.tables.<logic-table-name>.table-strategy.xxx= #省略
sharding.jdbc.config.sharding.tables.<logic-table-name>.key-generator-column-name= #自增列名称,缺省表示不使用自增主键生成器
sharding.jdbc.config.sharding.tables.<logic-table-name>.key-generator-class-name= #自增列值生成器类名称,缺省表示使用默认自增列值生成器。该类需提供无参数的构造器
sharding.jdbc.config.sharding.tables.<logic-table-name>.logic-index= #逻辑索引名称,对于分表的Oracle/PostgreSQL数据库中DROP INDEX XXX语句,需要通过配置逻辑索引名称定位所执行SQL的真实分表
sharding.jdbc.config.sharding.binding-tables[0]= #绑定表规则列表
sharding.jdbc.config.sharding.binding-tables[1]= #绑定表规则列表
sharding.jdbc.config.sharding.binding-tables[x]= #绑定表规则列表
sharding.jdbc.config.sharding.broadcast-tables[0]= #广播表规则列表
sharding.jdbc.config.sharding.broadcast-tables[1]= #广播表规则列表
sharding.jdbc.config.sharding.broadcast-tables[x]= #广播表规则列表
sharding.jdbc.config.sharding.default-data-source-name= #未配置分片规则的表将通过默认数据源定位
sharding.jdbc.config.sharding.default-database-strategy.xxx= #默认数据库分片策略,同分库策略
sharding.jdbc.config.sharding.default-table-strategy.xxx= #默认表分片策略,同分表策略
sharding.jdbc.config.sharding.default-key-generator-class-name= #默认自增列值生成器类名称,缺省使用io.shardingsphere.core.keygen.DefaultKeyGenerator。该类需实现KeyGenerator接口并提供无参数的构造器
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.master-data-source-name= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[0]= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[1]= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[x]= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-class-name= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-type= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.key1= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.key2= #详见读写分离部分
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.keyx= #详见读写分离部分
sharding.jdbc.config.sharding.props.sql.show= #是否开启SQL显示,默认值: false
sharding.jdbc.config.sharding.props.executor.size= #工作线程数量,默认值: CPU核数
sharding.jdbc.config.sharding.config.map.key1= #用户自定义配置
sharding.jdbc.config.sharding.config.map.key2= #用户自定义配置
sharding.jdbc.config.sharding.config.map.keyx= #用户自定义配置
#省略数据源配置,与数据分片一致
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.master-data-source-name= #主库数据源名称
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[0]= #从库数据源名称列表
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[1]= #从库数据源名称列表
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.slave-data-source-names[x]= #从库数据源名称列表
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-class-name= #从库负载均衡算法类名称。该类需实现MasterSlaveLoadBalanceAlgorithm接口且提供无参数构造器
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.load-balance-algorithm-type= #从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若`load-balance-algorithm-class-name`存在则忽略该配置
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.key1= #用户自定义配置
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.key2= #用户自定义配置
sharding.jdbc.config.sharding.master-slave-rules.<master-slave-data-source-name>.config.map.keyx= #用户自定义配置
sharding.jdbc.config.masterslave.props.sql.show= #是否开启SQL显示,默认值: false
sharding.jdbc.config.masterslave.props.executor.size= #工作线程数量,默认值: CPU核数
#省略数据源、数据分片和读写分离配置
sharding.jdbc.config.sharding.orchestration.name= #数据治理实例名称
sharding.jdbc.config.sharding.orchestration.overwrite= #本地配置是否覆盖注册中心配置。如果可覆盖,每次启动都以本地配置为准
sharding.jdbc.config.sharding.orchestration.registry.server-lists= #连接注册中心服务器的列表。包括IP地址和端口号。多个地址用逗号分隔。如: host1:2181,host2:2181
sharding.jdbc.config.sharding.orchestration.registry.namespace= #注册中心的命名空间
sharding.jdbc.config.sharding.orchestration.registry.digest= #连接注册中心的权限令牌。缺省为不需要权限验证
sharding.jdbc.config.sharding.orchestration.registry.operation-timeout-milliseconds= #操作超时的毫秒数,默认500毫秒
sharding.jdbc.config.sharding.orchestration.registry.max-retries= #连接失败后的最大重试次数,默认3次
sharding.jdbc.config.sharding.orchestration.registry.retry-interval-milliseconds= #重试间隔毫秒数,默认500毫秒
sharding.jdbc.config.sharding.orchestration.registry.time-to-live-seconds= #临时节点存活秒数,默认60秒
Spring命名空间配置
注意事项
行表达式标识符可以使用...或{...}或...或->{…},但前者与Spring本身的属性文件占位符冲突,因此在Spring环境中使用行表达式标识符建议使用$->{…}。
配置示例
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<context:annotation-config />
<context:component-scan base-package="io.shardingsphere.example.spring.namespace.jpa" />
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="shardingDataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:database="MYSQL" />
</property>
<property name="packagesToScan" value="io.shardingsphere.example.spring.namespace.jpa.entity" />
<property name="jpaProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.hbm2ddl.auto">create</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory" />
<tx:annotation-driven />
<bean id="ds0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="preciseModuloDatabaseShardingAlgorithm" class="io.shardingsphere.example.spring.namespace.jpa.algorithm.PreciseModuloDatabaseShardingAlgorithm" />
<bean id="preciseModuloTableShardingAlgorithm" class="io.shardingsphere.example.spring.namespace.jpa.algorithm.PreciseModuloTableShardingAlgorithm" />
<sharding:standard-strategy id="databaseShardingStrategy" sharding-column="user_id" precise-algorithm-ref="preciseModuloDatabaseShardingAlgorithm" />
<sharding:standard-strategy id="tableShardingStrategy" sharding-column="order_id" precise-algorithm-ref="preciseModuloTableShardingAlgorithm" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="ds0,ds1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" generate-key-column-name="order_id" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds$->{0..1}.t_order_item$->{0..1}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" generate-key-column-name="order_item_id" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="t_order, t_order_item" />
</sharding:binding-table-rules>
<sharding:broadcast-table-rules>
<sharding:broadcast-table-rule table="t_order" />
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://shardingsphere.io/schema/shardingsphere/masterslave
http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd">
<context:annotation-config />
<context:component-scan base-package="io.shardingsphere.example.spring.namespace.jpa" />
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="masterSlaveDataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:database="MYSQL" />
</property>
<property name="packagesToScan" value="io.shardingsphere.example.spring.namespace.jpa.entity" />
<property name="jpaProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.hbm2ddl.auto">create</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory" />
<tx:annotation-driven />
<bean id="ds_master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_slave1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="randomStrategy" class="io.shardingsphere.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm" />
<master-slave:data-source id="masterSlaveDataSource" master-data-source-name="ds_master" slave-data-source-names="ds_slave0, ds_slave1" strategy-ref="randomStrategy">
<master-slave:props>
<prop key="sql.show">${sql_show}</prop>
<prop key="executor.size">10</prop>
<prop key="foo">bar</prop>
</master-slave:props>
</master-slave:data-source>
</beans>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
http://shardingsphere.io/schema/shardingsphere/masterslave
http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd">
<context:annotation-config />
<context:component-scan base-package="io.shardingsphere.example.spring.namespace.jpa" />
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="shardingDataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:database="MYSQL" />
</property>
<property name="packagesToScan" value="io.shardingsphere.example.spring.namespace.jpa.entity" />
<property name="jpaProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.hbm2ddl.auto">create</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory" />
<tx:annotation-driven />
<bean id="ds_master0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_master0_slave0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master0_slave0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_master0_slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master0_slave1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_master1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_master1_slave0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master1_slave0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="ds_master1_slave1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_master1_slave1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<bean id="randomStrategy" class="io.shardingsphere.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm" />
<master-slave:data-source id="ds_ms0" master-data-source-name="ds_master0" slave-data-source-names="ds_master0_slave0, ds_master0_slave1" strategy-ref="randomStrategy" />
<master-slave:data-source id="ds_ms1" master-data-source-name="ds_master1" slave-data-source-names="ds_master1_slave0, ds_master1_slave1" strategy-ref="randomStrategy" />
<sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds_ms$->{user_id % 2}" />
<sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order$->{order_id % 2}" />
<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_id" algorithm-expression="t_order_item$->{order_id % 2}" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="ds_ms0,ds_ms1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="ds_ms$->{0..1}.t_order$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy" generate-key-column-name="order_id" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds_ms$->{0..1}.t_order_item$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy" generate-key-column-name="order_item_id" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="t_order, t_order_item" />
</sharding:binding-table-rules>
<sharding:broadcast-table-rules>
<sharding:broadcast-table-rule table="t_order" />
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:orchestration="http://shardingsphere.io/schema/shardingsphere/orchestration"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/orchestration
http://shardingsphere.io/schema/shardingsphere/orchestration/orchestration.xsd">
<orchestration:registry-center id="regCenter" server-lists="localhost:2181" namespace="orchestration-spring-namespace-demo" operation-timeout-milliseconds="1000" max-retries="3" />
</beans>
配置项说明
分库分表
命名空间:http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
<sharding:data-source />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
sharding-rule | 标签 | 数据分片配置规则 |
config-map (?) | 标签 | 用户自定义配置 |
props (?) | 标签 | 属性配置 |
<sharding:sharding-rule />
名称 | 类型 | 说明 |
---|---|---|
data-source-names | 属性 | 数据源Bean列表,多个Bean以逗号分隔 |
table-rules | 标签 | 表分片规则配置对象 |
binding-table-rules (?) | 标签 | 绑定表规则列表 |
broadcast-table-rules (?) | 标签 | 广播表规则列表 |
default-data-source-name (?) | 属性 | 未配置分片规则的表将通过默认数据源定位 |
default-database-strategy-ref (?) | 属性 | 默认数据库分片策略,对应sharding:xxx-strategy中的策略Id,缺省表示不分库 |
default-table-strategy-ref (?) | 属性 | 默认表分片策略,对应sharding:xxx-strategy中的策略Id,缺省表示不分表 |
default-key-generator-ref (?) | 属性 | 默认自增列值生成器引用,缺省使用io.shardingsphere.core.keygen.DefaultKeyGenerator。该类需实现KeyGenerator接口 |
<sharding:table-rules />
名称 | 类型 | 说明 |
---|---|---|
table-rule (+) | 标签 | 表分片规则配置对象 |
<sharding:table-rule />
名称 | 类型 | 说明 |
---|---|---|
logic-table | 属性 | 逻辑表名称 |
actual-data-nodes (?) | 属性 | 由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况 |
database-strategy-ref (?) | 属性 | 数据库分片策略,对应sharding:xxx-strategy中的策略Id,缺省表示使用<sharding:sharding-rule />配置的默认数据库分片策略 |
table-strategy-ref (?) | 属性 | 表分片策略,对应sharding:xxx-strategy中的策略Id,缺省表示使用<sharding:sharding-rule />配置的默认表分片策略 |
generate-key-column-name (?) | 属性 | 自增列名称,缺省表示不使用自增主键生成器 |
key-generator-ref (?) | 属性 | 自增列值生成器引用,缺省表示使用默认自增列值生成器.该类需实现KeyGenerator接口 |
logic-index (?) | 属性 | 逻辑索引名称,对于分表的Oracle/PostgreSQL数据库中DROP INDEX XXX语句,需要通过配置逻辑索引名称定位所执行SQL的真实分表 |
<sharding:binding-table-rules />
名称 | 类型 | 说明 |
---|---|---|
binding-table-rule (+) | 标签 | 绑定表规则 |
<sharding:binding-table-rule />
名称 | 类型 | 说明 |
---|---|---|
logic-tables | 属性 | 绑定规则的逻辑表名,多表以逗号分隔 |
<sharding:broadcast-table-rules />
名称 | 类型 | 说明 |
---|---|---|
broadcast-table-rule (+) | 标签 | 广播表规则 |
<sharding:broadcast-table-rule />
名称 | 类型 | 说明 |
---|---|---|
table | 属性 | 广播规则的表名 |
<sharding:standard-strategy />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
sharding-column | 属性 | 分片列名称 |
precise-algorithm-ref | 属性 | 精确分片算法引用,用于=和IN。该类需实现PreciseShardingAlgorithm接口 |
range-algorithm-ref (?) | 属性 | 范围分片算法引用,用于BETWEEN。该类需实现RangeShardingAlgorithm接口 |
<sharding:complex-strategy />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
sharding-columns | 属性 | 分片列名称,多个列以逗号分隔 |
algorithm-ref | 属性 | 复合分片算法引用。该类需实现ComplexKeysShardingAlgorithm接口 |
<sharding:inline-strategy />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
sharding-column | 属性 | 分片列名称 |
algorithm-expression | 属性 | 分片算法行表达式,需符合groovy语法 |
<sharding:hint-database-strategy />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
algorithm-ref | 属性 | Hint分片算法。该类需实现HintShardingAlgorithm接口 |
<sharding:none-strategy />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
<sharding:props />
名称 | 类型 | 说明 |
---|---|---|
sql.show (?) | 属性 | 是否开启SQL显示,默认值: false |
executor.size (?) | 属性 | 工作线程数量,默认值: CPU核数 |
<sharding:config-map />
读写分离
命名空间:http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd
<master-slave:data-source />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | Spring Bean Id |
master-data-source-name | 属性 | 主库数据源Bean Id |
slave-data-source-names | 属性 | 从库数据源Bean Id列表,多个Bean以逗号分隔 |
strategy-ref (?) | 属性 | 从库负载均衡算法引用。该类需实现 |
strategy-type (?) | 属性 | 从库负载均衡算法类型,可选值:ROUND_ROBIN,RANDOM。若strategy-ref存在则忽略该配置 |
config-map (?) | 标签 | 用户自定义配置props (?)标签属性配置 |
<master-slave:config-map />
<master-slave:props />
名称 | 类型 | 说明 |
---|---|---|
sql.show (?) | 属性 | 是否开启SQL显示,默认值: false |
executor.size (?) | 属性 | 工作线程数量,默认值: CPU核数 |
数据分片 + 数据治理
命名空间:http://shardingsphere.io/schema/shardingsphere/orchestration/orchestration.xsd
<orchestration:sharding-data-source />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | ID |
data-source-ref (?) | 属性 | 被治理的数据库id |
registry-center-ref | 属性 | 注册中心id |
overwrite | 属性 | 本地配置是否覆盖注册中心配置。如果可覆盖,每次启动都以本地配置为准。 缺省为不覆盖 |
读写分离 + 数据治理
命名空间:http://shardingsphere.io/schema/shardingsphere/orchestration/orchestration.xsd
<orchestration:master-slave-data-source />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | ID |
data-source-ref (?) | 属性 | 被治理的数据库id |
registry-center-ref | 属性 | 注册中心id |
overwrite | 属性 | 本地配置是否覆盖注册中心配置。如果可覆盖,每次启动都以本地配置为准。 缺省为不覆盖 |
数据治理注册中心
命名空间:http://shardingsphere.io/schema/shardingsphere/orchestration/orchestration.xsd
<orchestration:registry-center />
名称 | 类型 | 说明 |
---|---|---|
id | 属性 | 注册中心的Spring Bean Id |
server-lists | 属性 | 连接注册中心服务器的列表。包括IP地址和端口号。多个地址用逗号分隔。如: host1:2181,host2:2181 |
namespace (?) | 属性 | 注册中心的命名空间 |
digest (?) | 属性 | 连接注册中心的权限令牌。缺省为不需要权限验证 |
operation-timeout-milliseconds (?) | 属性 | 操作超时的毫秒数,默认500毫秒 |
max-retries (?) | 属性 | 连接失败后的最大重试次数,默认3次 |
retry-interval-milliseconds (?) | 属性 | 重试间隔毫秒数,默认500毫秒 |
time-to-live-seconds (?) | 属性 | 临时节点存活秒数,默认60秒 |
JDBC不支持项
DataSource接口
● 不支持timeout相关操作
Connection接口
● 不支持存储过程,函数,游标的操作
● 不支持执行native的SQL
● 不支持savepoint相关操作
● 不支持Schema/Catalog的操作
● 不支持自定义类型映射
Statement和PreparedStatement接口
● 不支持返回多结果集的语句(即存储过程,非SELECT多条数据)
● 不支持国际化字符的操作
对于ResultSet接口
● 不支持对于结果集指针位置判断
● 不支持通过非next方法改变结果指针位置
● 不支持修改结果集内容
● 不支持获取国际化字符
● 不支持获取Array
JDBC 4.1
● 不支持JDBC 4.1接口新功能
性能测试报告
测试结果概述
- 性能损耗测试:服务器资源充足、并发数相同,比较JDBC和Sharding-JDBC性能损耗,Sharding-JDBC相对JDBC损耗不超过7%。
- 性能对比测试:服务器资源使用到极限,相同的场景JDBC与Sharding-JDBC的吞吐量相当。
- 性能对比测试:服务器资源使用到极限,Sharding-JDBC采用分库分表后,Sharding-JDBC吞吐量较JDBC不分表有接近2倍的提升。
- 性能对比测试:服务器资源使用到极限,Sharding-JDBC V1.5.2与V1.4.2对比,性能比较稳定。
基准测试性能对比
业务场景 | JDBC | Sharding-JDBC1.5.2 | Sharding-JDBC1.5.2/JDBC损耗 |
---|---|---|---|
单库单表查询 | 493 | 470 | 4.7% |
单库单表更新 | 6682 | 6303 | 5.7% |
单库单表插入 | 6855 | 6375 | 7% |
JDBC单库两库表与Sharding-JDBC两库各两表对比
业务场景 | JDBC单库两表 | Sharding-JDBC两库各两表 | 性能提升至 |
---|---|---|---|
查询 | 1736 | 3331 | 192% |
更新 | 9170 | 17997 | 196% |
插入 | 11574 | 23043 | 199% |
业务场景 | JDBC单库单表 | Sharding-JDBC两库各一表 | 性能提升至 |
---|---|---|---|
查询 | 1586 | 2944 | 185% |
更新 | 9548 | 18561 | 194% |
插入 | 11182 | 21414 | 192% |
Sharding-JDBC v1.4.2与v1.5.2版本对比
业务场景 | Sharding-JDBC 1.4.2 | Sharding-JDBC 1.5.2 | 1.5.2 / 1.4.2 |
---|---|---|---|
查询 | 2934 | 2944 | 100.34% |
更新 | 18454 | 18561 | 100.58% |
插入 | 21045 | 21414 | 101.75% |
测试目的
● 对比Sharding-JDBC 1.5.2与JDBC性能是否有较大损耗;
● Sharding-JDBC 1.52与1.4.2版本对比,性能是否有损耗;
● Sharding-JDBC 1.5.2是否存在非功能问题,为优化提供依据;
Sharding-Proxy
Proxy启动
- 下载Sharding-Proxy的最新发行版,地址:https://github.com/sharding-sphere/sharding-sphere-doc/raw/master/dist/sharding-proxy-3.0.0.tar.gz
- 解压缩后修改conf/server.yaml和以config-前缀开头的文件,如:conf/config-xxx.yaml文件,进行分片规则、读写分离规则配置. 配置方式请参考配置手册。
- Linux操作系统请运行bin/start.sh,Windows操作系统请运行bin/start.bat启动Sharding-Proxy。如需配置启动端口、配置文件位置,可参考快速入门 进行启动。
- 使用任何MySQL的客户端连接。如: mysql -u root -h 127.0.0.1 -P 3307
注册中心使用
若想使用Sharding-Proxy的数据库治理功能,则需要使用注册中心实现实例熔断和从库禁用功能。详情请参考支持的注册中心。
- Sharding-Proxy默认提供了Zookeeper的注册中心解决方案。您只需按照配置规则进行注册中心的配置,即可使用。
- 将Sharding-Proxy的lib目录下的sharding-orchestration-reg-zookeeper-curator-${sharding-sphere.version}.jar文件删除。
- Maven仓库下载Etcd解决方案的最新稳定版jar包。
- 将下载下来的jar包放到Sharding-Proxy的lib目录下。
- 按照配置规则进行注册中心的配置,即可使用。
- 将Sharding-Proxy的lib目录下的sharding-orchestration-reg-zookeeper-curator-${sharding-sphere.version}.jar文件删除。
- 使用SPI方式实现相关逻辑编码,并将生成的jar包放到Sharding-Proxy的lib目录下。
- 按照配置规则进行注册中心的配置,即可使用。
注意事项
- Sharding-Proxy默认使用3307端口,可以通过启动脚本追加参数作为启动端口号。如: bin/start.sh 3308
- Sharding-Proxy使用conf/server.yaml配置注册中心、认证信息以及公用属性。
- Sharding-Proxy支持多逻辑数据源,每个以config-前缀命名的yaml配置文件,即为一个逻辑数据源。
数据源与分片配置示例
Sharding-Proxy支持多逻辑数据源,每个以config-前缀命名的yaml配置文件,即为一个逻辑数据源。以下是config-xxx.yaml的配置配置示例。
dataSources:
schemaName: sharding_db
dataSources:
ds0:
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds1:
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
shardingRule:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
keyGeneratorColumnName: order_id
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
schemaName: master_slave_db
dataSources:
ds_master:
url: jdbc:mysql://localhost:3306/ds_master
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds_slave0:
url: jdbc:mysql://localhost:3306/ds_slave0
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds_slave1:
url: jdbc:mysql://localhost:3306/ds_slave1
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames:
- ds_slave0
- ds_slave1
schemaName: sharding_master_slave_db
dataSources:
ds0:
url: jdbc:mysql://localhost:3306/ds0
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds0_slave0:
url: jdbc:mysql://localhost:3306/ds0_slave0
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds0_slave1:
url: jdbc:mysql://localhost:3306/ds0_slave1
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds1:
url: jdbc:mysql://localhost:3306/ds1
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds1_slave0:
url: jdbc:mysql://localhost:3306/ds1_slave0
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
ds1_slave1:
url: jdbc:mysql://localhost:3306/ds1_slave1
username: root
password:
autoCommit: true
connectionTimeout: 30000
idleTimeout: 60000
maxLifetime: 1800000
maximumPoolSize: 65
shardingRule:
tables:
t_order:
actualDataNodes: ms_ds${0..1}.t_order${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
keyGeneratorColumnName: order_id
t_order_item:
actualDataNodes: ms_ds${0..1}.t_order_item${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_config
defaultDataSourceName: ds0
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ms_ds${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGeneratorClassName: io.shardingsphere.core.keygen.DefaultKeyGenerator
masterSlaveRules:
ms_ds0:
masterDataSourceName: ds0
slaveDataSourceNames:
- ds0_slave0
- ds0_slave1
loadBalanceAlgorithmType: ROUND_ROBIN
configMap:
master-slave-key0: master-slave-value0
ms_ds1:
masterDataSourceName: ds1
slaveDataSourceNames:
- ds1_slave0
- ds1_slave1
loadBalanceAlgorithmType: ROUND_ROBIN
configMap:
master-slave-key1: master-slave-value1
全局配置示例
Sharding-Proxy使用conf/server.yaml配置注册中心、认证信息以及公用属性。
#省略数据分片和读写分离配置
orchestration:
name: orchestration_ds
overwrite: true
registry:
namespace: orchestration
serverLists: localhost:2181
认证信息
authentication:
username: root
password:
公用属性
props:
executor.size: 16
sql.show: false
数据源与分片配置项说明
schemaName: #逻辑数据源名称
dataSources: #数据源配置,可配置多个data_source_name
<data_source_name>: #与Sharding-JDBC配置不同,无需配置数据库连接池
url: #数据库url连接
username: #数据库用户名
password: #数据库密码
autoCommit: true #hikari连接池默认配置
connectionTimeout: 30000 #hikari连接池默认配置
idleTimeout: 60000 #hikari连接池默认配置
maxLifetime: 1800000 #hikari连接池默认配置
maximumPoolSize: 65 #hikari连接池默认配置
shardingRule: #省略数据分片配置,与Sharding-JDBC配置一致
schemaName: #逻辑数据源名称
dataSources: #省略数据源配置,与数据分片一致
masterSlaveRule: #省略读写分离配置,与Sharding-JDBC配置一致
全局配置项说明
与Sharding-JDBC配置一致。
#省略与Sharding-JDBC一致的配置属性
props:
acceptor.size: #用于设置接收客户端请求的工作线程个数,默认为CPU核数*2
proxy.transaction.enabled: #是否开启事务, 目前仅支持XA事务,默认为不开启
proxy.opentracing.enabled: #是否开启链路追踪功能,默认为不开启。详情请参见链路追踪
权限验证
用于执行登录Sharding Proxy的权限验证。配置用户名、密码后,必须使用正确的用户名、密码才可登录Proxy。
authentication:
username: root
password:
总结
摘自官方文档,所以非常繁琐,比较重复,大家看的时候理解一下,就好了。不同的对接模式,配置这是格式不一样,配置项基本上差不多。所以看懂一种就都一样了,数据分片、主从两种模式看懂就好了。