sharding-jdbc结合mybatis实现分库分表功能
最近忙于项目已经好久几天没写博客了，前 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"><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:strategy </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="tableShardingStrategy"</span><span style="color: rgba(255, 0, 0, 1)"> sharding-columns</span><span style="color: rgba(0, 0, 255, 1)">="user_id"</span><span style="color: rgba(255, 0, 0, 1)"> algorithm-class</span><span style="color: rgba(0, 0, 255, 1)">="com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:data-source </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="shardingDataSource"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:sharding-rule </span><span style="color: rgba(255, 0, 0, 1)">data-sources</span><span style="color: rgba(0, 0, 255, 1)">="dataSource"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:table-rules</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:table-rule </span><span style="color: rgba(255, 0, 0, 1)">logic-table</span><span style="color: rgba(0, 0, 255, 1)">="member_index"</span><span style="color: rgba(255, 0, 0, 1)"> actual-tables</span><span style="color: rgba(0, 0, 255, 1)">="member_index_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}"</span><span style="color: rgba(255, 0, 0, 1)"> table-strategy</span><span style="color: rgba(0, 0, 255, 1)">="tableShardingStrategy"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">rdb:table-rule </span><span style="color: rgba(255, 0, 0, 1)">logic-table</span><span style="color: rgba(0, 0, 255, 1)">="member_details"</span><span style="color: rgba(255, 0, 0, 1)"> actual-tables</span><span style="color: rgba(0, 0, 255, 1)">="member_details_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}"</span><span style="color: rgba(255, 0, 0, 1)"> table-strategy</span><span style="color: rgba(0, 0, 255, 1)">="tableShardingStrategy"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">rdb:table-rules</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">rdb:sharding-rule</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">rdb:data-source</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">bean </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="transactionManager"</span><span style="color: rgba(255, 0, 0, 1)"> class</span><span style="color: rgba(0, 0, 255, 1)">="org.springframework.jdbc.datasource.DataSourceTransactionManager"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">property </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="dataSource"</span><span style="color: rgba(255, 0, 0, 1)"> ref</span><span style="color: rgba(0, 0, 255, 1)">="shardingDataSource"</span> <span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">bean</span><span style="color: rgba(0, 0, 255, 1)">></span></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 去管理）
四、实现分表（分库）逻辑，我们的分表逻辑类需要实现 SingleKeyTableShardingAlgorithm 接口的三个方法 doBetweenSharding、doEqualSharding、doInSharding
/** * 分表逻辑 * @author zhangwentao * */ public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)"> * sql between 规则 </span><span style="color: rgba(0, 128, 0, 1)">*/</span> <span style="color: rgba(0, 0, 255, 1)">public</span> Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long><span style="color: rgba(0, 0, 0, 1)"> shardingValue) { Collection</span><String> result = <span style="color: rgba(0, 0, 255, 1)">new</span> LinkedHashSet<String><span style="color: rgba(0, 0, 0, 1)">(tableNames.size()); Range</span><Long> range = (Range<Long><span style="color: rgba(0, 0, 0, 1)">) shardingValue.getValueRange(); </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">long</span> i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++<span style="color: rgba(0, 0, 0, 1)">) { Long modValue </span>= i % 100<span style="color: rgba(0, 0, 0, 1)">; String modStr </span>= modValue < 10 ? "0" +<span style="color: rgba(0, 0, 0, 1)"> modValue : modValue.toString(); </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String each : tableNames) { </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (each.endsWith(modStr)) { result.add(each); } } } </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> result; } </span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)"> * sql == 规则 </span><span style="color: rgba(0, 128, 0, 1)">*/</span> <span style="color: rgba(0, 0, 255, 1)">public</span> String doEqualSharding(Collection<String> tableNames, ShardingValue<Long><span style="color: rgba(0, 0, 0, 1)"> shardingValue) { Long modValue </span>= shardingValue.getValue() % 100<span style="color: rgba(0, 0, 0, 1)">; String modStr </span>= modValue < 10 ? "0" +<span style="color: rgba(0, 0, 0, 1)"> modValue : modValue.toString(); </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String each : tableNames) { </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (each.endsWith(modStr)) { </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> each; } } </span><span style="color: rgba(0, 0, 255, 1)">throw</span> <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> IllegalArgumentException(); } </span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)"> * sql in 规则 </span><span style="color: rgba(0, 128, 0, 1)">*/</span> <span style="color: rgba(0, 0, 255, 1)">public</span> Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long><span style="color: rgba(0, 0, 0, 1)"> shardingValue) { Collection</span><String> result = <span style="color: rgba(0, 0, 255, 1)">new</span> LinkedHashSet<String><span style="color: rgba(0, 0, 0, 1)">(tableNames.size()); </span><span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">long</span><span style="color: rgba(0, 0, 0, 1)"> value : shardingValue.getValues()) { Long modValue </span>= value % 100<span style="color: rgba(0, 0, 0, 1)">; String modStr </span>= modValue < 10 ? "0" +<span style="color: rgba(0, 0, 0, 1)"> modValue : modValue.toString(); </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String tableName : tableNames) { </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (tableName.endsWith(modStr)) { result.add(tableName); } } } </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> 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>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | private static final String SERVICE_PROVIDER_XML = "/spring/member-service.xml"; private static final String BEAN_NAME = "idcacheService"; private ClassPathXmlApplicationContext context = null; IdcacheServiceImpl bean = null; IdcacheDao idcacheDao; @Before public void before() { context= new ClassPathXmlApplicationContext( new String[] {SERVICE_PROVIDER_XML}); idcacheDao=context.getBean("IdcacheDao", IdcacheDao.class); } @Test public void getAllCreditActionTest() { // int id = bean.insertIdcache(); Long s=100l; MemberDetailsDO memberDetailsDO=idcacheDao.getDetailsById(s); System.out.println("QQ---------------------"+memberDetailsDO.getQq()); } |
打印 sql 语句，输出结果：QQ-------------------------------------100，证明成功！
注意点：这次搭建过程中，我有碰到一个小坑，就是执行的时候会报错：，官方文档是有解决方案：引入 <context:property-placeholder location="classpath:/member_service.properties" ignore-unresolvable="true" /> ，引入这行代码的时候，·必须要要把这边管理配配置文件的 bean 删除，换句话说，即 Spring 容器仅允许最多定义一个 PropertyPlaceholderConfigurer(或 <context:property-placeholder/>)，其余的会被 Spring 忽略掉（当时搞了半天啊）
小结：这次给大家分享了 sharding-jdbc 的配置是为了解决大数据量进行分库分表的架构，下一张，我将介绍拆分业务所需的 duboo+zookeeper 的配置（分布式），欢迎关注！