sharding-jdbc是当当开源的一款分库分表的数据访问层框架,能对mysql很方便的分库、分表,基本不用修改原有代码,只要配置一下即可,完整的配置参考以下内容:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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:tx="http://www.springframework.org/schema/tx" 6 xsi:schemaLocation="http://www.springframework.org/schema/beans 7 http://www.springframework.org/schema/beans/spring-beans.xsd 8 http://www.springframework.org/schema/tx 9 http://www.springframework.org/schema/tx/spring-tx.xsd 10 http://www.springframework.org/schema/context 11 http://www.springframework.org/schema/context/spring-context.xsd"> 12 13 <context:component-scan base-package="com.cnblogs.yjmyzz.sharding"/> 14 15 <bean id="propertiesFactoryBean" 16 class="org.springframework.beans.factory.config.PropertiesFactoryBean"> 17 <property name="locations"> 18 <list> 19 <value>classpath:properties/jdbc.properties</value> 20 </list> 21 </property> 22 </bean> 23 24 <context:property-placeholder properties-ref="propertiesFactoryBean" ignore-unresolvable="true"/> 25 26 <!--父数据源--> 27 <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" 28 destroy-method="close"> 29 <property name="driverClassName" value="${jdbc-driver}"/> 30 <property name="url" value="${jdbc-url-0}"/> 31 <property name="username" value="${jdbc-user-0}"/> 32 <property name="password" value="${jdbc-password-0}"/> 33 <property name="filters" value="stat"/> 34 <property name="maxActive" value="20"/> 35 <property name="initialSize" value="1"/> 36 <property name="maxWait" value="60000"/> 37 <property name="minIdle" value="1"/> 38 <property name="timeBetweenEvictionRunsMillis" value="3000"/> 39 <property name="minEvictableIdleTimeMillis" value="300000"/> 40 <property name="validationQuery" value="SELECT 'x'"/> 41 <property name="testWhileIdle" value="true"/> 42 <property name="testOnBorrow" value="false"/> 43 <property name="testOnReturn" value="false"/> 44 <property name="poolPreparedStatements" value="true"/> 45 <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/> 46 <property name="connectionInitSqls" value="set names utf8mb4;"/> 47 </bean> 48 49 <!--数据源0--> 50 <bean id="ds_0" parent="parentDataSource"> 51 <property name="driverClassName" value="${jdbc-driver}"/> 52 <property name="url" value="${jdbc-url-0}"/> 53 <property name="username" value="${jdbc-user-0}"/> 54 <property name="password" value="${jdbc-password-0}"/> 55 </bean> 56 57 <!--数据源1--> 58 <bean id="ds_1" parent="parentDataSource"> 59 <property name="driverClassName" value="${jdbc-driver}"/> 60 <property name="url" value="${jdbc-url-1}"/> 61 <property name="username" value="${jdbc-user-1}"/> 62 <property name="password" value="${jdbc-password-1}"/> 63 </bean> 64 65 <!--数据源2--> 66 <bean id="ds_2" parent="parentDataSource"> 67 <property name="driverClassName" value="${jdbc-driver}"/> 68 <property name="url" value="${jdbc-url-2}"/> 69 <property name="username" value="${jdbc-user-2}"/> 70 <property name="password" value="${jdbc-password-2}"/> 71 </bean> 72 73 <!--真正使用的数据源--> 74 <bean id="dataSource" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule"> 75 <constructor-arg> 76 <map> 77 <entry key="ds_0" value-ref="ds_0"/> 78 <entry key="ds_1" value-ref="ds_1"/> 79 <entry key="ds_2" value-ref="ds_2"/> 80 </map> 81 </constructor-arg> 82 </bean> 83 84 <!--t_order的"分表"设置:分N个表 --> 85 <bean id="orderTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule"> 86 <constructor-arg value="t_order" index="0"/> 87 <constructor-arg index="1"> 88 <list> 89 <value>t_order_0</value> 90 <value>t_order_1</value> 91 </list> 92 </constructor-arg> 93 <constructor-arg index="2" ref="dataSource"/> 94 </bean> 95 96 <!--分库的sharding规则:按user_id分库 --> 97 <bean id="databaseShardingStrategy" 98 class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy"> 99 <constructor-arg index="0" value="user_id"/> 100 <constructor-arg index="1"> 101 <bean class="com.cnblogs.yjmyzz.sharding.algorithm.SingleKeyModuloDatabaseShardingAlgorithm"> 102 <!--dbount的值要跟上面dataSource的个数匹配--> 103 <property name="dbCount" value="3"/> 104 </bean> 105 </constructor-arg> 106 </bean> 107 108 <!--分表的规则:按order_id分表--> 109 <bean id="tableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy"> 110 <constructor-arg index="0" value="order_id"/> 111 <constructor-arg index="1"> 112 <bean class="com.cnblogs.yjmyzz.sharding.algorithm.SingleKeyModuloTableShardingAlgorithm"> 113 <!--tableCount的值要跟上面t_order表设置的分表个数保持一致--> 114 <property name="tableCount" value="2"/> 115 </bean> 116 </constructor-arg> 117 </bean> 118 119 <!--sharding规则Bean--> 120 <bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule"> 121 <constructor-arg index="0" ref="dataSource"/> 122 <constructor-arg index="1"> 123 <list> 124 <ref bean="orderTableRule"/> 125 </list> 126 </constructor-arg> 127 <constructor-arg index="2" ref="databaseShardingStrategy"/> 128 <constructor-arg index="3" ref="tableShardingStrategy"/> 129 </bean> 130 131 <!--sharding数据源--> 132 <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource"> 133 <constructor-arg ref="shardingRule"/> 134 </bean> 135 136 <!--sharding事务管理器--> 137 <!--<bean id="transactionManager"--> 138 <!--class="org.springframework.jdbc.datasource.DataSourceTransactionManager">--> 139 <!--<property name="dataSource" ref="shardingDataSource"/>--> 140 <!--</bean>--> 141 142 <!--<tx:annotation-driven transaction-manager="transactionManager"/>--> 143 144 <!--mybatis配置--> 145 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 146 <property name="configLocation" value="classpath:mybatis-config.xml"></property> 147 <property name="dataSource" ref="shardingDataSource"/> 148 <property name="mapperLocations" value="classpath:mybatis/OrderMapper.xml"/> 149 </bean> 150 151 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 152 <property name="basePackage" value="com.cnblogs.yjmyzz.sharding.mapper"/> 153 <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> 154 </bean> 155 156 </beans>
上面的配置,表示T_Order表按user_id进行分成ds_0,ds_1,ds_2共三库,每个库中又按order_id分成T_Order_0,T_Order_1二张表。
分库、分表是按常见的取模算法处理的,需要用户自定义二个类(基本上就是模板代码,不需要什么改动)
SingleKeyModuloDatabaseShardingAlgorithm
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
1 /** 2 * Copyright 1999-2015 dangdang.com. 3 * <p> 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * <p/> 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * <p/> 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 * </p> 16 */ 17 18 package com.cnblogs.yjmyzz.sharding.algorithm; 19 20 import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; 21 import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm; 22 import com.google.common.collect.Range; 23 24 import java.util.Collection; 25 import java.util.LinkedHashSet; 26 27 public final class SingleKeyModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> { 28 29 private int dbCount = 1; 30 31 @Override 32 public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 33 for (String each : availableTargetNames) { 34 if (each.endsWith(shardingValue.getValue() % dbCount + "")) { 35 return each; 36 } 37 } 38 throw new UnsupportedOperationException(); 39 } 40 41 @Override 42 public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 43 Collection<String> result = new LinkedHashSet<>(availableTargetNames.size()); 44 Collection<Integer> values = shardingValue.getValues(); 45 for (Integer value : values) { 46 for (String dataSourceName : availableTargetNames) { 47 if (dataSourceName.endsWith(value % dbCount + "")) { 48 result.add(dataSourceName); 49 } 50 } 51 } 52 return result; 53 } 54 55 @Override 56 public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 57 Collection<String> result = new LinkedHashSet<>(availableTargetNames.size()); 58 Range<Integer> range = shardingValue.getValueRange(); 59 for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { 60 for (String each : availableTargetNames) { 61 if (each.endsWith(i % dbCount + "")) { 62 result.add(each); 63 } 64 } 65 } 66 return result; 67 } 68 69 /** 70 * 设置database分库的个数 71 * @param dbCount 72 */ 73 public void setDbCount(int dbCount) { 74 this.dbCount = dbCount; 75 } 76 }
SingleKeyModuloTableShardingAlgorithm
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
1 /** 2 * Copyright 1999-2015 dangdang.com. 3 * <p> 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * <p/> 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * <p/> 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 * </p> 16 */ 17 18 package com.cnblogs.yjmyzz.sharding.algorithm; 19 20 import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; 21 import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm; 22 import com.google.common.collect.Range; 23 24 import java.util.Collection; 25 import java.util.LinkedHashSet; 26 27 public final class SingleKeyModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> { 28 29 private int tableCount = 1; 30 31 @Override 32 public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 33 for (String each : availableTargetNames) { 34 if (each.endsWith(shardingValue.getValue() % tableCount + "")) { 35 return each; 36 } 37 } 38 throw new UnsupportedOperationException(); 39 } 40 41 @Override 42 public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 43 Collection<String> result = new LinkedHashSet<>(availableTargetNames.size()); 44 Collection<Integer> values = shardingValue.getValues(); 45 for (Integer value : values) { 46 for (String tableNames : availableTargetNames) { 47 if (tableNames.endsWith(value % tableCount + "")) { 48 result.add(tableNames); 49 } 50 } 51 } 52 return result; 53 } 54 55 @Override 56 public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) { 57 Collection<String> result = new LinkedHashSet<>(availableTargetNames.size()); 58 Range<Integer> range = shardingValue.getValueRange(); 59 for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { 60 for (String each : availableTargetNames) { 61 if (each.endsWith(i % tableCount + "")) { 62 result.add(each); 63 } 64 } 65 } 66 return result; 67 } 68 69 /** 70 * 设置分表的个数 71 * 72 * @param tableCount 73 */ 74 public void setTableCount(int tableCount) { 75 this.tableCount = tableCount; 76 } 77 }
然后mybatis里就可以类似常规操作一样来写sql了,具体可参考源码中的示例代码。
不过,经个人测试发现一些小问题,以避免大家踩坑:
1、聚合函数的使用要特别小心,我试了下max/min/count这几个函数,返回时记得给返回结果加字段别名,即: select count(*) order_count from t_order,否则可能返回的结果不正确(已经向作者反馈,估计很快会修复该bug)
2、另外分库的key,不支持范围搜索,类似 select * from t_order where user_id > 100的操作,直接报错,如果需要这样的操作,建议先取max(user_id),比如最大用户id为120,然后user_id in (101,102...120) 或者 between ... and 这样处理。
3、如果采用druid数据源,目前有点不稳定,偶尔会出异常,建议采用dbcp(跟作者反馈了下,说是很快会修复该问题)
4、批量插入问题,insert xxx values(...),(...),(...) 不支持,主要原因是因为要插入的记录,无法定位分片。但是可以适当预处理下变通解决,思路:按db-key将List<T>中的对象先划分成Map<dbkey,List<T>>,然后每个entry的List<T>再按tableKey做同样的map映射,即:将List<T>变成Map<dbkey,Map<tableKey,List<T>> 这种结构,相当于人工把同一分片的数据整理到一起,再做insert批量插入就可以了。
其它一些使用上的限制,参考:
http://dangdangdotcom.github.io/sharding-jdbc/post/limitations/
最后,我在github上放了一个示例代码sharding-jdbc-sample,需要的同学可以参考