在springboot项目中使用mybatis 集成 Sharding-JDBC

前段时间写了篇如何使用Sharding-JDBC进行分库分表的例子,相信能够感受到Sharding-JDBC的强大了,而且使用配置都非常干净。官方支持的功能还包括读写分离、分布式主键、强制路由等。这里再介绍下如何在分库分表的基础上集成读写分离的功能。

读写分离的概念

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

设置主从库后,第一个问题是如何进行主从的同步。官方不支持主从的同步,也不支持因为主从同步延迟导致的数据不一致问题。工程实践上进行主从同步有很多做法,一种常用的做法是每天定时同步或者实时同步。这个话题太大,暂不展开。

读写分离快速入门

读写可以单独使用,也可以配合分库分表进行使用,由于上个分库分表的例子是基于1.5.4.1版本进行说明的,这里为了紧跟官方的步伐,升级Sharding-JDBC到最新的2.0.0.M2

项目结构如下:

项目结构

pom依赖



org.springframework.boot
spring-boot-starter-web

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>

         <!-- Sharding-JDBC核心依赖 -->
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
        </dependency>

        <!-- Sharding-JDBC Spring Boot Starter -->
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</code>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

### 主从数据库配置

在配置前,我们希望分库分表规则和之前保持一致:

> 基于`t_user`表,根据`city_id`进行分库,如果`city_id mod 2`为奇数则落在`ds_master_1`库,偶数则落在`ds_master_0`库;根据`user_id`进行分表,如果`user_id mod 2`为奇数则落在`t_user_1`表,偶数则落在`t_user_0`表

读写分离规则:

> 读都落在从库,写落在主库

因为使用`Sharding-JDBC Spring Boot Starter`,所以只需要在properties配置文件配置主从库的数据源即可:

<pre name="code" class="prettyprint"><code class="language-java hljs has-numbering">
spring.application.name=spring-boot-mybatis-sharding-jdbc-masterslave
server.context-path=/springboot

mybatis.config-location=classpath:mybatis-config.xml

# 所有主从库
sharding.jdbc.datasource.names=ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1

# ds_master_0
sharding.jdbc.datasource.ds_master_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_0?useSSL=false
sharding.jdbc.datasource.ds_master_0.username=travis
sharding.jdbc.datasource.ds_master_0.password=

# slave <span class="hljs-keyword">for ds_master_0
sharding.jdbc.datasource.ds_master_0_slave_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0_slave_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0_slave_0.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_0_slave_0?useSSL=false
sharding.jdbc.datasource.ds_master_0_slave_0.username=travis
sharding.jdbc.datasource.ds_master_0_slave_0.password=
sharding.jdbc.datasource.ds_master_0_slave_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0_slave_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0_slave_1.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_0_slave_1?useSSL=false
sharding.jdbc.datasource.ds_master_0_slave_1.username=travis
sharding.jdbc.datasource.ds_master_0_slave_1.password=

# ds_master_1
sharding.jdbc.datasource.ds_master_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_1?useSSL=false
sharding.jdbc.datasource.ds_master_1.username=travis
sharding.jdbc.datasource.ds_master_1.password=

# slave <span class="hljs-keyword">for ds_master_1
sharding.jdbc.datasource.ds_master_1_slave_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1_slave_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1_slave_0.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_1_slave_0?useSSL=false
sharding.jdbc.datasource.ds_master_1_slave_0.username=travis
sharding.jdbc.datasource.ds_master_1_slave_0.password=
sharding.jdbc.datasource.ds_master_1_slave_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1_slave_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1_slave_1.url=jdbc:mysql:<span class="hljs-comment">//127.0.0.1:3306/ds_master_1_slave_1?useSSL=false
sharding.jdbc.datasource.ds_master_1_slave_1.username=travis
sharding.jdbc.datasource.ds_master_1_slave_1.password=

# 分库规则
sharding.jdbc.config.sharding.<span class="hljs-keyword">default-database-strategy.inline.sharding-column=city_id
sharding.jdbc.config.sharding.<span class="hljs-keyword">default-database-strategy.inline.algorithm-expression=ds_${city_id % <span class="hljs-number">2}

# 分表规则
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=ds_${<span class="hljs-number">0.<span class="hljs-number">.1}.t_user_${<span class="hljs-number">0.<span class="hljs-number">.1}
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.shardingColumn=user_id
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user_${user_id % <span class="hljs-number">2}
# 使用user_id作为分布式主键
sharding.jdbc.config.sharding.tables.t_user.keyGeneratorColumnName=user_id

# 逻辑主从库名和实际主从库映射关系
sharding.jdbc.config.sharding.master-slave-rules.ds_0.masterDataSourceName=ds_master_0
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slaveDataSourceNames=ds_master_0_slave_0, ds_master_0_slave_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.masterDataSourceName=ds_master_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.slaveDataSourceNames=ds_master_1_slave_0, ds_master_1_slave_1

</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
### Test

测试代码如下:

<pre name="code" class="prettyprint"><code class="language-java hljs  has-numbering">
<span class="hljs-annotation">@RunWith(SpringRunner.class)
<span class="hljs-annotation">@SpringBootTest
<span class="hljs-keyword">public <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">UserMapperTest {

    <span class="hljs-javadoc">/** Logger */
    <span class="hljs-keyword">private <span class="hljs-keyword">static Logger log = LoggerFactory.getLogger(UserMapperTest.class);

    <span class="hljs-annotation">@Resource
    <span class="hljs-keyword">private UserMapper userMapper;

    <span class="hljs-annotation">@Before
    <span class="hljs-keyword">public <span class="hljs-keyword">void <span class="hljs-title">setup() <span class="hljs-keyword">throws Exception {
        create();
        clear();
    }

    <span class="hljs-keyword">private <span class="hljs-keyword">void <span class="hljs-title">create() <span class="hljs-keyword">throws SQLException {
        userMapper.createIfNotExistsTable();
    }

    <span class="hljs-keyword">private <span class="hljs-keyword">void <span class="hljs-title">clear() {
        userMapper.truncateTable();
    }

    <span class="hljs-annotation">@Test
    <span class="hljs-keyword">public <span class="hljs-keyword">void <span class="hljs-title">insert() <span class="hljs-keyword">throws Exception {
        UserEntity user = <span class="hljs-keyword">new UserEntity();
        user.setCityId(<span class="hljs-number">1);
        user.setUserName(<span class="hljs-string">"insertTest");
        user.setAge(<span class="hljs-number">10);
        user.setBirth(<span class="hljs-keyword">new Date());
        assertTrue(userMapper.insert(user) > <span class="hljs-number">0);
        Long userId = user.getUserId();
        log.info(<span class="hljs-string">"Generated Key--userId:" + userId);
        userMapper.delete(userId);
    }

    <span class="hljs-annotation">@Test
    <span class="hljs-keyword">public <span class="hljs-keyword">void <span class="hljs-title">find() <span class="hljs-keyword">throws Exception {
        UserEntity userEntity = userMapper.find(<span class="hljs-number">138734796783222784L);
        log.info(<span class="hljs-string">"user:{}", userEntity);
    }

}
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
1
2
3
4
5
6
7
8
9
10
11

先运行`insert`方法,插入一条数据后,获取插入的`user_id`为`138734796783222784L`(每次运行会不一样),由于`city_id=1`,读写分离约定,会落在主库,又根据分库规则会落在`ds_master_1`,再根据分表规则,会落在`t_user_0`

![结果](http://7xkjk9.com1.z0.glb.clouddn.com/test.png)

再运行`find`方法,指定userId,你会发现查出来是空的,这是因为Sharding-JDBC不支持主从同步以及主从同步延迟造成的数据不一致。这里我们显然术语第一种,因为根本就没有进行主从同步,那么从从库读取肯定是空的。

我们可以反向推理下,假如开启了主从同步,现在数据落在主库`ds_master_1`,这个主库有两个从库:`ds_master_1_slave_0`和`ds_master_1_slave_1`,所以我们可以往这两个主库的`t_user_0`表插入刚才的数据,语句如下:

<pre name="code" class="prettyprint"><code class="language-java hljs has-numbering">INSERT INTO t_user_0(user_id,city_id,user_name,age,birth) values(<span class="hljs-number">138734796783222784,<span class="hljs-number">1,<span class="hljs-string">'insertTest',<span class="hljs-number">10,<span class="hljs-string">'2017-11-18 00:00:00');
</span></span></span></span></span></code>

先往ds_master_1_slave_0t_user_0表插入该条数据,可以理解为主库同步到从库的数据。重新运行find方法,发现返回的数据和主库的一致,表明Sharding-JDBC从ds_master_1的从库ds_master_1_slave_0t_user_0表查到了数据。

再删掉ds_master_1_slave_0t_user_0表的数据,往ds_master_1_slave_1t_user_0表插入刚才那条数据,重新运行发现返回的结果为空,表明从ds_master_1的从库ds_master_1_slave_1t_user_0表没有查到数据。

最后往ds_master_1_slave_0t_user_0表重新插入刚才的数据,再运行发现又返回了数据。

基于以上现象,可以推论选择从库查询的时候经过了某种算法得到访问的从库,然后在从库根据分表规则查询数据。

## 读写分离实现

这里包括几个问题:

1.  读写分离的查询流程?
2.  如何做结果归并?
3.  如何路由到某个从库进行查询?
4.  可以强制路由主库进行读操作吗?

### 读写分离的流程

1.  获取主从库配置规则,数据源封装成`MasterSlaveDataSource`
2.  根据路由计算,得到PreparedStatementUnit单元列表,合并每个PreparedStatementUnit的执行结果返回
3.  执行每个PrepareStatementUnit的时候需要获取连接,这里根据轮询负载均衡算法`RoundRobinMasterSlaveLoadBalanceAlgorithm`得到从库数据源,拿到连接后就开始执行具体的SQL查询了,这里通过`PreparedStatementExecutor.execute()`得到执行结果
4.  结果归并后返回

MasterSlaveDataSource:

<pre name="code" class="prettyprint"><code class="language-java hljs  has-numbering">
<span class="hljs-keyword">public <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">MasterSlaveDataSource <span class="hljs-keyword">extends <span class="hljs-title">AbstractDataSourceAdapter {

    <span class="hljs-keyword">private <span class="hljs-keyword">static <span class="hljs-keyword">final ThreadLocal<Boolean> DML_FLAG = <span class="hljs-keyword">new ThreadLocal<Boolean>() {

        <span class="hljs-annotation">@Override
        <span class="hljs-keyword">protected Boolean <span class="hljs-title">initialValue() {
            <span class="hljs-keyword">return <span class="hljs-keyword">false;
        }
    };

    <span class="hljs-comment">// 主从配置关系
    <span class="hljs-keyword">private MasterSlaveRule masterSlaveRule;

    <span class="hljs-keyword">public <span class="hljs-title">MasterSlaveDataSource(<span class="hljs-keyword">final MasterSlaveRule masterSlaveRule) <span class="hljs-keyword">throws SQLException {
        <span class="hljs-keyword">super(getAllDataSources(masterSlaveRule.getMasterDataSource(), masterSlaveRule.getSlaveDataSourceMap().values()));
        <span class="hljs-keyword">this.masterSlaveRule = masterSlaveRule;
    }

    <span class="hljs-keyword">private <span class="hljs-keyword">static Collection<DataSource> <span class="hljs-title">getAllDataSources(<span class="hljs-keyword">final DataSource masterDataSource, <span class="hljs-keyword">final Collection<DataSource> slaveDataSources) {
        Collection<DataSource> result = <span class="hljs-keyword">new LinkedList<>(slaveDataSources);
        result.add(masterDataSource);
        <span class="hljs-keyword">return result;
    }

    ...省略部分代码
<span class="hljs-comment">// 获取数据源
<span class="hljs-keyword">public NamedDataSource <span class="hljs-title">getDataSource(<span class="hljs-keyword">final SQLType sqlType) {
        <span class="hljs-comment">// 强制路由到主库查询
        <span class="hljs-keyword">if (isMasterRoute(sqlType)) {
            DML_FLAG.set(<span class="hljs-keyword">true);
            <span class="hljs-keyword">return <span class="hljs-keyword">new NamedDataSource(masterSlaveRule.getMasterDataSourceName(), masterSlaveRule.getMasterDataSource());
        }
        <span class="hljs-comment">// 获取选中的从库数据源
        String selectedSourceName = masterSlaveRule.getStrategy().getDataSource(masterSlaveRule.getName(), 
                masterSlaveRule.getMasterDataSourceName(), <span class="hljs-keyword">new ArrayList<>(masterSlaveRule.getSlaveDataSourceMap().keySet()));
        DataSource selectedSource = selectedSourceName.equals(masterSlaveRule.getMasterDataSourceName())
                ? masterSlaveRule.getMasterDataSource() : masterSlaveRule.getSlaveDataSourceMap().get(selectedSourceName);
        Preconditions.checkNotNull(selectedSource, <span class="hljs-string">"");
        <span class="hljs-keyword">return <span class="hljs-keyword">new NamedDataSource(selectedSourceName, selectedSource);
    }
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

MasterSlaveRule:

<pre name="code" class="prettyprint"><code class="language-java hljs has-numbering"><span class="hljs-keyword">public <span class="hljs-keyword">final <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">MasterSlaveRule {
<span class="hljs-comment">// 名称(这里是ds_0和ds_1)
<span class="hljs-keyword">private <span class="hljs-keyword">final String name;

<span class="hljs-comment">// 主库数据源名称(这里是ds_master_0和ds_master_1)
<span class="hljs-keyword">private <span class="hljs-keyword">final String masterDataSourceName;

<span class="hljs-comment">// 主库数据源
<span class="hljs-keyword">private <span class="hljs-keyword">final DataSource masterDataSource;

<span class="hljs-comment">// 所属从库列表,key为从库数据源名称,value是真实的数据源
<span class="hljs-keyword">private <span class="hljs-keyword">final Map<String, DataSource> slaveDataSourceMap;

<span class="hljs-comment">// 主从库负载均衡算法
<span class="hljs-keyword">private <span class="hljs-keyword">final MasterSlaveLoadBalanceAlgorithm strategy;
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

RoundRobinMasterSlaveLoadBalanceAlgorithm:

<pre name="code" class="prettyprint"><code class="language-java hljs  has-numbering"><span class="hljs-comment">// 轮询负载均衡策略,按照每个从节点访问次数均衡
<span class="hljs-keyword">public <span class="hljs-keyword">final <span class="hljs-class"><span class="hljs-keyword">class <span class="hljs-title">RoundRobinMasterSlaveLoadBalanceAlgorithm <span class="hljs-keyword">implements <span class="hljs-title">MasterSlaveLoadBalanceAlgorithm {

    <span class="hljs-keyword">private <span class="hljs-keyword">static <span class="hljs-keyword">final ConcurrentHashMap<String, AtomicInteger> COUNT_MAP = <span class="hljs-keyword">new ConcurrentHashMap<>();

    <span class="hljs-annotation">@Override
    <span class="hljs-keyword">public String <span class="hljs-title">getDataSource(<span class="hljs-keyword">final String name, <span class="hljs-keyword">final String masterDataSourceName, <span class="hljs-keyword">final List<String> slaveDataSourceNames) {
        AtomicInteger count = COUNT_MAP.containsKey(name) ? COUNT_MAP.get(name) : <span class="hljs-keyword">new AtomicInteger(<span class="hljs-number">0);
        COUNT_MAP.putIfAbsent(name, count);
        count.compareAndSet(slaveDataSourceNames.size(), <span class="hljs-number">0);
        <span class="hljs-keyword">return slaveDataSourceNames.get(count.getAndIncrement() % slaveDataSourceNames.size());
    }
}
</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>```

DefaultResultSetHandler:

<pre name="code" class="prettyprint"><code class="language-java hljs  has-numbering">
<span class="hljs-annotation">@Override
  <span class="hljs-keyword">public List<Object> <span class="hljs-title">handleResultSets(Statement stmt) <span class="hljs-keyword">throws SQLException {
    ErrorContext.instance().activity(<span class="hljs-string">"handling results").object(mappedStatement.getId());

    <span class="hljs-comment">// 返回的结果集
    <span class="hljs-keyword">final List<Object> multipleResults = <span class="hljs-keyword">new ArrayList<Object>();

    <span class="hljs-keyword">int resultSetCount = <span class="hljs-number">0;
    ResultSetWrapper rsw = getFirstResultSet(stmt);

    List<ResultMap> resultMaps = mappedStatement.getResultMaps();
    <span class="hljs-keyword">int resultMapCount = resultMaps.size();
    validateResultMapsCount(rsw, resultMapCount);
    <span class="hljs-keyword">while (rsw != <span class="hljs-keyword">null && resultMapCount > resultSetCount) {
      ResultMap resultMap = resultMaps.get(resultSetCount);
      <span class="hljs-comment">// 将ResultSetWrapper的结果集添加到multipleResults中
      handleResultSet(rsw, resultMap, multipleResults, <span class="hljs-keyword">null);
      rsw = getNextResultSet(stmt);
      cleanUpAfterHandlingResultSet();
      resultSetCount++;
    }

    String[] resultSets = mappedStatement.getResultSets();
    <span class="hljs-keyword">if (resultSets != <span class="hljs-keyword">null) {
      <span class="hljs-keyword">while (rsw != <span class="hljs-keyword">null && resultSetCount < resultSets.length) {
        ResultMapping parentMapping = nextResultMaps.get(resultSets[resultSetCount]);
        <span class="hljs-keyword">if (parentMapping != <span class="hljs-keyword">null) {
          String nestedResultMapId = parentMapping.getNestedResultMapId();
          ResultMap resultMap = configuration.getResultMap(nestedResultMapId);
          handleResultSet(rsw, resultMap, <span class="hljs-keyword">null, parentMapping);
        }
        rsw = getNextResultSet(stmt);
        cleanUpAfterHandlingResultSet();
        resultSetCount++;
      }
    }

    <span class="hljs-keyword">return collapseSingleResultList(multipleResults);
  }

<span class="hljs-keyword">private <span class="hljs-keyword">void <span class="hljs-title">handleResultSet(ResultSetWrapper rsw, ResultMap resultMap, List<Object> multipleResults, ResultMapping parentMapping) <span class="hljs-keyword">throws SQLException {
    <span class="hljs-keyword">try {
      <span class="hljs-keyword">if (parentMapping != <span class="hljs-keyword">null) {
        handleRowValues(rsw, resultMap, <span class="hljs-keyword">null, RowBounds.DEFAULT, parentMapping);
      } <span class="hljs-keyword">else {
        <span class="hljs-keyword">if (resultHandler == <span class="hljs-keyword">null) {
          DefaultResultHandler defaultResultHandler = <span class="hljs-keyword">new DefaultResultHandler(objectFactory);
          <span class="hljs-comment">// 按照resultMap解析到defaultResultHandler中
          handleRowValues(rsw, resultMap, defaultResultHandler, rowBounds, <span class="hljs-keyword">null);
          <span class="hljs-comment">// 最后的结果就是这里加进去的
          multipleResults.add(defaultResultHandler.getResultList());
        } <span class="hljs-keyword">else {
          handleRowValues(rsw, resultMap, resultHandler, rowBounds, <span class="hljs-keyword">null);
        }
      }
    } <span class="hljs-keyword">finally {
      <span class="hljs-comment">// issue #228 (close resultsets)
      closeResultSet(rsw.getResultSet());
    }
  }</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>```</pre></pre></pre></pre></pre></pre></pre>
感谢支持原创技术分享
显示 Gitment 评论