读写分离

概念

为了缓解数据库压力，将写入和读取操作分离为不同数据源，写库称为主库，读库称为从库，一主库可配置多从库。

支持项

  • 提供了一主多从的读写分离配置，可独立使用，也可配合分库分表使用。
  • 独立使用读写分离支持SQL透传。
  • 同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。
  • Spring命名空间。
  • 基于Hint的强制主库路由。

不支持范围

  • 主库和从库的数据同步。
  • 主库和从库的数据同步延迟导致的数据不一致。
  • 主库双写或多写。

代码开发示例

仅读写分离

  1. // 构建读写分离数据源, 读写分离数据源实现了DataSource接口, 可直接当做数据源处理. masterDataSource, slaveDataSource0, slaveDataSource1等为使用DBCP等连接池配置的真实数据源
  2. Map<String, DataSource> dataSourceMap = new HashMap<>();
  3. dataSourceMap.put("masterDataSource", masterDataSource);
  4. dataSourceMap.put("slaveDataSource0", slaveDataSource0);
  5. dataSourceMap.put("slaveDataSource1", slaveDataSource1);
  7. // 构建读写分离配置
  8. MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration();
  9. masterSlaveRuleConfig.setName("ms_ds");
  10. masterSlaveRuleConfig.setMasterDataSourceName("masterDataSource");
  11. masterSlaveRuleConfig.getSlaveDataSourceNames().add("slaveDataSource0");
  12. masterSlaveRuleConfig.getSlaveDataSourceNames().add("slaveDataSource1");
  14. DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig);

分库分表 + 读写分离

  1. // 构建读写分离数据源, 读写分离数据源实现了DataSource接口, 可直接当做数据源处理. masterDataSource0, slaveDataSource00, slaveDataSource01等为使用DBCP等连接池配置的真实数据源
  2. Map<String, DataSource> dataSourceMap = new HashMap<>();
  3. dataSourceMap.put("masterDataSource0", masterDataSource0);
  4. dataSourceMap.put("slaveDataSource00", slaveDataSource00);
  5. dataSourceMap.put("slaveDataSource01", slaveDataSource01);
  7. dataSourceMap.put("masterDataSource1", masterDataSource1);
  8. dataSourceMap.put("slaveDataSource10", slaveDataSource10);
  9. dataSourceMap.put("slaveDataSource11", slaveDataSource11);
  11. // 构建读写分离配置
  12. MasterSlaveRuleConfiguration masterSlaveRuleConfig0 = new MasterSlaveRuleConfiguration();
  13. masterSlaveRuleConfig0.setName("ds_0");
  14. masterSlaveRuleConfig0.setMasterDataSourceName("masterDataSource0");
  15. masterSlaveRuleConfig0.getSlaveDataSourceNames().add("slaveDataSource00");
  16. masterSlaveRuleConfig0.getSlaveDataSourceNames().add("slaveDataSource01");
  18. MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration();
  19. masterSlaveRuleConfig1.setName("ds_1");
  20. masterSlaveRuleConfig1.setMasterDataSourceName("masterDataSource1");
  21. masterSlaveRuleConfig1.getSlaveDataSourceNames().add("slaveDataSource10");
  22. masterSlaveRuleConfig1.getSlaveDataSourceNames().add("slaveDataSource11");
  24. // 通过ShardingSlaveDataSourceFactory继续创建ShardingDataSource
  25. ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
  26. shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfig0);
  27. shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfig1);
  29. DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig);

Spring命名空间配置示例

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  4.     xmlns:context="http://www.springframework.org/schema/context"
  5.     xmlns:sharding="http://shardingsphere.io/schema/shardingjdbc/sharding"
  6.     xmlns:masterslave="http://shardingsphere.io/schema/shardingjdbc/masterslave"
  7.     xsi:schemaLocation="http://www.springframework.org/schema/beans 
  8.                         http://www.springframework.org/schema/beans/spring-beans.xsd
  9.                         http://www.springframework.org/schema/context 
  10.                         http://www.springframework.org/schema/context/spring-context.xsd 
  11.                         http://shardingsphere.io/schema/shardingjdbc/sharding
  12.                         http://shardingsphere.io/schema/shardingjdbc/sharding/sharding.xsd
  13.                         http://shardingsphere.io/schema/shardingjdbc/masterslave
  14.                         http://shardingsphere.io/schema/shardingjdbc/masterslave/master-slave.xsd
  15.                         ">
  16.     <!-- 配置真实数据源 -->
  17.     <bean id="dbtbl_0_master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  18.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  19.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_0_master"/>
  20.         <property name="username" value="root"/>
  21.         <property name="password" value=""/>
  22.     </bean>
  24.     <bean id="dbtbl_0_slave_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  25.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  26.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_0_slave_0"/>
  27.         <property name="username" value="root"/>
  28.         <property name="password" value=""/>
  29.     </bean>
  31.     <bean id="dbtbl_0_slave_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  32.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  33.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_0_slave_1"/>
  34.         <property name="username" value="root"/>
  35.         <property name="password" value=""/>
  36.     </bean>
  38.     <bean id="dbtbl_1_master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  39.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  40.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_1_master"/>
  41.         <property name="username" value="root"/>
  42.         <property name="password" value=""/>
  43.     </bean>
  45.     <bean id="dbtbl_1_slave_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  46.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  47.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_1_slave_0"/>
  48.         <property name="username" value="root"/>
  49.         <property name="password" value=""/>
  50.     </bean>
  52.     <bean id="dbtbl_1_slave_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  53.         <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  54.         <property name="url" value="jdbc:mysql://localhost:3306/dbtbl_1_slave_1"/>
  55.         <property name="username" value="root"/>
  56.         <property name="password" value=""/>
  57.     </bean>
  59.     <!-- 定义读写分离数据源, 读写分离数据源实现了DataSource接口, 可直接当做数据源处理 -->
  60.     <master-slave:data-source id="dbtbl_0" master-data-source-name="dbtbl_0_master" slave-data-source-names="dbtbl_0_slave_0, dbtbl_0_slave_1" strategy-type="ROUND_ROBIN" />
  61.     <master-slave:data-source id="dbtbl_1" master-data-source-name="dbtbl_1_master" slave-data-source-names="dbtbl_1_slave_0, dbtbl_1_slave_1" strategy-type="ROUND_ROBIN" />
  63.     <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="dbtbl_${user_id % 2}" />
  64.     <sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order_${order_id % 4}" />
  66.     <sharding:data-source id="shardingDataSource">
  67.         <sharding:sharding-rule data-source-names="dbtbl_0, dbtbl_1">
  68.             <sharding:table-rules>
  69.                 <sharding:table-rule logic-table="t_order" actual-data-nodes="dbtbl_${0..1}.t_order_${0..3}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy"/>
  70.             </sharding:table-rules>
  71.         </sharding:sharding-rule>
  72.     </sharding:data-source>
  73. </beans>

使用Hint强制路由主库示例

  1. HintManager hintManager = HintManager.getInstance();
  2. hintManager.setMasterRouteOnly();
  3. // 继续JDBC操作