利用sharding-jdbc分庫分表 - yjmyzz－IT工程師數位筆記本

文章出處

sharding-jdbc是當當開源的一款分庫分表的數據訪問層框架，能對mysql很方便的分庫、分表，基本不用修改原有代碼，只要配置一下即可，完整的配置參考以下內容：

  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>

View Code

上面的配置，表示T_Order表按user_id進行分成ds_0,ds_1,ds_2共三庫，每個庫中又按order_id分成T_Order_0,T_Order_1二張表。

分庫、分表是按常見的取模算法處理的，需要用戶自定義二個類（基本上就是模板代碼，不需要什么改動）

SingleKeyModuloDatabaseShardingAlgorithm

 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 }

View Code

SingleKeyModuloTableShardingAlgorithm

 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 }

View Code

然后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，需要的同學可以參考

文章列表