sharding-jdbc结合mybatis实现分库分表功能

╰+攻爆jí腚メ 2022-05-22 07:49 137阅读 0赞

源自：https://www.cnblogs.com/zwt1990/p/6762135.html

最近忙于项目已经好久几天没写博客了，前2篇文章我给大家介绍了搭建基础springMvc+mybatis的maven工程，这个简单框架已经可以对付一般的小型项目。但是我们实际项目中会碰到很多复杂的场景，比如数据量很大的情况下如何保证性能。今天我就给大家介绍数据库分库分表的优化，本文介绍mybatis结合当当网的sharding-jdbc分库分表技术（原理这里不做介绍）

首先在pom文件中引入需要的依赖

<dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>1.4.2</version>
            </dependency>
            <dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-config-spring</artifactId>
                <version>1.4.0</version>
            </dependency>

二、新建一个sharding-jdbc.xml文件，实现分库分表的配置

<?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:tx="http://www.springframework.org/schema/tx"
        xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
                            http://www.springframework.org/schema/beans/spring-beans.xsd 
                            http://www.springframework.org/schema/tx 
                            http://www.springframework.org/schema/tx/spring-tx.xsd
                            http://www.springframework.org/schema/context 
                            http://www.springframework.org/schema/context/spring-context.xsd
                            http://www.dangdang.com/schema/ddframe/rdb 
                            http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
                    
        <rdb:strategy id="tableShardingStrategy" sharding-columns="user_id" algorithm-class="com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm"/>
        
        <rdb:data-source id="shardingDataSource">
            <rdb:sharding-rule data-sources="dataSource">
                <rdb:table-rules>
                    <rdb:table-rule logic-table="member_index" actual-tables="member_index_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}"  table-strategy="tableShardingStrategy"/>
                    <rdb:table-rule logic-table="member_details" actual-tables="member_details_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}"  table-strategy="tableShardingStrategy"/>
                </rdb:table-rules>
            </rdb:sharding-rule>
        </rdb:data-source>
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="shardingDataSource" />
        </bean>
    </beans>

这里我简单介绍下一些属性的含义，

<rdb:strategy id="tableShardingStrategy" sharding-columns="user\_id" algorithm-class="com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm"/>  配置分表规则器  sharding-columns：分表规 则

依赖的名（根据user\_id取模分表），algorithm-class:分表规则的实现类

<rdb:sharding-rule data-sources="dataSource"> 这里填写关联数据源（多个数据源用逗号隔开）,

<rdb:table-rule logic-table="member\_index" actual-tables="member\_index\_tbl\_$\{\[0,1,2,3,4,5,6,7,8,9\]\}$\{0..9\}"  table-strategy="tableShardingStrategy"/>  logic-table：逻辑表名（mybatis中代替的表名）actual-tables：

数据库实际的表名，这里支持inline表达式，比如：member\_index\_tbl\_$\{0..2\}会解析成member\_index\_tbl\_0，member\_index\_tbl\_1，member\_index\_tbl\_2；member\_index\_tbl\_$\{\[a,b,c\]\}会被解析成

member\_index\_tbl\_a,member\_index\_tbl\_b和member\_index\_tbl\_c,两种表达式一起使用的时候，会采取笛卡尔积的方式：member\_index\_tbl\_$\{\[a,b\]\}$\{0..2\}解析为member\_index\_tbl\_a0,member\_index\_tbl\_a1                                       member\_index\_tbl\_a2,member\_index\_tbl\_b0,member\_index\_tbl\_b1,member\_index\_tbl\_b2;table-strategy:前面定义的分表规则器；

三、配置好改文件后，需要修改之前我们的spring-dataSource的几个地方，把sqlSessionFactory和transactionManager原来关联的dataSource统一修改为shardingDataSource（这一步作用就是把数据源全部托管给sharding去管理）

![1140944-20170425141731006-390893009.png][]![1140944-20170425142050365-891428347.png][]

四、实现分表（分库）逻辑，我们的分表逻辑类需要实现SingleKeyTableShardingAlgorithm接口的三个方法doBetweenSharding、doEqualSharding、doInSharding

/**
     * 分表逻辑
     * @author zhangwentao
     *
     */
    public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
        
        /**
         * sql between 规则
         */
        public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
            Collection<String> result = new LinkedHashSet<String>(tableNames.size());
            Range<Long> range = (Range<Long>) shardingValue.getValueRange();
            for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
                Long modValue = i % 100;
                String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
                for (String each : tableNames) {
                    if (each.endsWith(modStr)) {
                        result.add(each);
                    }
                }
            }
            return result;
        }
    
        /**
         * sql == 规则
         */
        public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
            Long modValue = shardingValue.getValue() % 100;
            String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
            for (String each : tableNames) {
                if (each.endsWith(modStr)) {
                    return each;
                }
            }
            throw new IllegalArgumentException();
        }
    
        /**
         * sql in 规则
         */
        public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
    
            Collection<String> result = new LinkedHashSet<String>(tableNames.size());
            for (long value : shardingValue.getValues()) {
                Long modValue = value % 100;
                String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
                for (String tableName : tableNames) {
                    if (tableName.endsWith(modStr)) {
                        result.add(tableName);
                    }
                }
            }
            return result;
        }
        
    }

五、以上四步，我们就完成了sharding-jdbc的搭建，我们可以写一个测试demo来检查我们的成果

<select id="getDetailsById" resultType="com.meiren.member.dataobject.MemberDetailsDO"
            parameterType="java.lang.Long">
            select user_id userId ,qq,email from member_details where     user_id =#{userId} limit 1
        </select>

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>private</code> 
      <code>static</code> 
      <code>final</code> 
      <code>String SERVICE_PROVIDER_XML = </code> 
      <code>"/spring/member-service.xml"</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>static</code> 
      <code>final</code> 
      <code>String BEAN_NAME = </code> 
      <code>"idcacheService"</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>ClassPathXmlApplicationContext context = </code> 
      <code>null</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>IdcacheServiceImpl bean = </code> 
      <code>null</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>IdcacheDao idcacheDao;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Before</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>void</code> 
      <code>before() { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>context= </code> 
      <code>new</code> 
      <code>ClassPathXmlApplicationContext(</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>new</code> 
      <code>String[] {SERVICE_PROVIDER_XML});</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>idcacheDao=context.getBean(</code> 
      <code>"IdcacheDao"</code> 
      <code>, IdcacheDao.</code> 
      <code>class</code> 
      <code>);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Test</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>void</code> 
      <code>getAllCreditActionTest() { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>// int id = bean.insertIdcache();</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>Long s=100l;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>MemberDetailsDO memberDetailsDO=idcacheDao.getDetailsById(s);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>System.out.println(</code> 
      <code>"QQ---------------------"</code> 
      <code>+memberDetailsDO.getQq());</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

打印sql语句，输出结果：QQ-------------------------------------100，证明成功！

![1140944-20170425143528584-770659721.png][]

注意点：这次搭建过程中，我有碰到一个小坑，就是执行的时候会报错：，官方文档是有解决方案：引入 <context:property-placeholder location="classpath:/member\_service.properties" ignore-unresolvable="true" />  ，引入这行代码的时候，·必须要要把这边管理配配置文件的bean删除，**换句话说，即Spring容器仅允许最多定义一个PropertyPlaceholderConfigurer(或<context:property-placeholder/>)，其余的会被Spring忽略掉**（当时搞了半天啊）

[1140944-20170425141731006-390893009.png]: https://images2015.cnblogs.com/blog/1140944/201704/1140944-20170425141731006-390893009.png
[1140944-20170425142050365-891428347.png]: /images/20220522/a74d3e89ce0b4ba584ee6b943b05ca60.png
[1140944-20170425143528584-770659721.png]: /images/20220522/f7b41e2ac60f4974bef63de3d7f24377.png