接上篇,使用文档。html
引入Maven依赖node
<dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>${sharding-sphere.version}</version> </dependency>
Sharding-JDBC的分库分表经过规则配置描述,如下例子是根据user_id取模分库, 且根据order_id取模分表的两库两表的配置。mysql
// 配置真实数据源 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方式配置,与以上配置等价:git
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原生实现为例:github
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)); } } }
引入Maven依赖web
<!-- 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;算法
引入Maven依赖spring
<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方式配置,与以上配置等价:sql
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)); } } }
引入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进行强制数据分片,须要使用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)的数据分片相同。
● 使用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()) { // ... } } }
使用数据治理功能须要指定一个注册中心。配置将所有存入注册中心,能够在每次启动时使用本地配置覆盖注册中心配置,也能够只经过注册中心读取配置。
引入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);
引入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>
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 | 自增列值生成器,缺省表示使用默认自增主键生成器 |
ShardingStrategyConfiguration的实现类,用于单分片键的标准分片场景。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumn | String | 分片列名称 |
preciseShardingAlgorithm | PreciseShardingAlgorithm | 精确分片算法,用于=和IN |
rangeShardingAlgorithm (?) | RangeShardingAlgorithm | 范围分片算法,用于BETWEEN |
ShardingStrategyConfiguration的实现类,用于多分片键的复合分片场景。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumns | String | 分片列名称,多个列以逗号分隔 |
shardingAlgorithm | ComplexKeysShardingAlgorithm | 复合分片算法 |
ShardingStrategyConfiguration的实现类,用于配置行表达式分片策略。
名称 | 数据类型 | 说明 |
---|---|---|
shardingColumn | String | 分片列名称 |
algorithmExpression | String | 分片算法行表达式,需符合groovy语法,详情请参考行表达式 |
ShardingStrategyConfiguration的实现类,用于配置Hint方式分片策略。
名称 | 数据类型 | 说明 |
---|---|---|
shardingAlgorithmHint | ShardingAlgorithmHint | 分片算法 |
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 |
数据分片 + 数据治理的数据源工厂。
名称 | 数据类型 | 说明 |
---|---|---|
dataSourceMap | Map<String, DataSource> | 同ShardingDataSourceFactory |
shardingRuleConfig | ShardingRuleConfiguration | 同ShardingDataSourceFactory |
configMap (?) | Map<String, Object> | 同ShardingDataSourceFactory |
props (?) | Properties | 同ShardingDataSourceFactory |
orchestrationConfig | OrchestrationConfiguration | 数据治理规则配置 |
读写分离 + 数据治理的数据源工厂。
名称 | 数据类型 | 说明 |
---|---|---|
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秒 |
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秒
!! 表示实例化该类
行表达式标识符可使用 ->{…},但前者与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环境中使用行表达式标识符建议使用$->{…}。
<?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秒 |
● 不支持timeout相关操做
● 不支持存储过程,函数,游标的操做
● 不支持执行native的SQL
● 不支持savepoint相关操做
● 不支持Schema/Catalog的操做
● 不支持自定义类型映射
● 不支持返回多结果集的语句(即存储过程,非SELECT多条数据)
● 不支持国际化字符的操做
● 不支持对于结果集指针位置判断
● 不支持经过非next方法改变结果指针位置
● 不支持修改结果集内容
● 不支持获取国际化字符
● 不支持获取Array
● 不支持JDBC 4.1接口新功能
业务场景 | JDBC | Sharding-JDBC1.5.2 | Sharding-JDBC1.5.2/JDBC损耗 |
---|---|---|---|
单库单表查询 | 493 | 470 | 4.7% |
单库单表更新 | 6682 | 6303 | 5.7% |
单库单表插入 | 6855 | 6375 | 7% |
业务场景 | 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 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的数据库治理功能,则须要使用注册中心实现实例熔断和从库禁用功能。详情请参考支持的注册中心。
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:
摘自官方文档,因此很是繁琐,比较重复,你们看的时候理解一下,就行了。不一样的对接模式,配置这是格式不同,配置项基本上差很少。因此看懂一种就都同样了,数据分片、主从两种模式看懂就行了。