利用sharding-jdbc分库分表

时间：2023-12-21 18:12:20

sharding-jdbc是当当开源的一款分库分表的数据访问层框架，能对mysql很方便的分库、分表，基本不用修改原有代码，只要配置一下即可，完整的配置参考以下内容：

 <?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"

        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">

     <context:component-scan base-package="com.cnblogs.yjmyzz.sharding"/>

     <bean id="propertiesFactoryBean"

           class="org.springframework.beans.factory.config.PropertiesFactoryBean">

         <property name="locations">

             <list>

                 <value>classpath:properties/jdbc.properties</value>

             </list>

         </property>

     </bean>

     <context:property-placeholder properties-ref="propertiesFactoryBean" ignore-unresolvable="true"/>

     <!--父数据源-->

     <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"

           destroy-method="close">

         <property name="driverClassName" value="${jdbc-driver}"/>

         <property name="url" value="${jdbc-url-0}"/>

         <property name="username" value="${jdbc-user-0}"/>

         <property name="password" value="${jdbc-password-0}"/>

         <property name="filters" value="stat"/>

         <property name="maxActive" value="20"/>

         <property name="initialSize" value="1"/>

         <property name="maxWait" value="60000"/>

         <property name="minIdle" value="1"/>

         <property name="timeBetweenEvictionRunsMillis" value="3000"/>

         <property name="minEvictableIdleTimeMillis" value="300000"/>

         <property name="validationQuery" value="SELECT 'x'"/>

         <property name="testWhileIdle" value="true"/>

         <property name="testOnBorrow" value="false"/>

         <property name="testOnReturn" value="false"/>

         <property name="poolPreparedStatements" value="true"/>

         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>

         <property name="connectionInitSqls" value="set names utf8mb4;"/>

     </bean>

     <!--数据源0-->

     <bean id="ds_0" parent="parentDataSource">

         <property name="driverClassName" value="${jdbc-driver}"/>

         <property name="url" value="${jdbc-url-0}"/>

         <property name="username" value="${jdbc-user-0}"/>

         <property name="password" value="${jdbc-password-0}"/>

     </bean>

     <!--数据源1-->

     <bean id="ds_1" parent="parentDataSource">

         <property name="driverClassName" value="${jdbc-driver}"/>

         <property name="url" value="${jdbc-url-1}"/>

         <property name="username" value="${jdbc-user-1}"/>

         <property name="password" value="${jdbc-password-1}"/>

     </bean>

     <!--数据源2-->

     <bean id="ds_2" parent="parentDataSource">

         <property name="driverClassName" value="${jdbc-driver}"/>

         <property name="url" value="${jdbc-url-2}"/>

         <property name="username" value="${jdbc-user-2}"/>

         <property name="password" value="${jdbc-password-2}"/>

     </bean>

     <!--真正使用的数据源-->

     <bean id="dataSource" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule">

         <constructor-arg>

             <map>

                 <entry key="ds_0" value-ref="ds_0"/>

                 <entry key="ds_1" value-ref="ds_1"/>

                 <entry key="ds_2" value-ref="ds_2"/>

             </map>

         </constructor-arg>

     </bean>

     <!--t_order的"分表"设置:分N个表 -->

     <bean id="orderTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule">

         <constructor-arg value="t_order" index="0"/>

         <constructor-arg index="1">

             <list>

                 <value>t_order_0</value>

                 <value>t_order_1</value>

             </list>

         </constructor-arg>

         <constructor-arg index="2" ref="dataSource"/>

     </bean>

     <!--分库的sharding规则:按user_id分库 -->

     <bean id="databaseShardingStrategy"

           class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy">

         <constructor-arg index="0" value="user_id"/>

         <constructor-arg index="1">

             <bean class="com.cnblogs.yjmyzz.sharding.algorithm.SingleKeyModuloDatabaseShardingAlgorithm">

                 <!--dbount的值要跟上面dataSource的个数匹配-->

                 <property name="dbCount" value="3"/>

             </bean>

         </constructor-arg>

     </bean>

     <!--分表的规则:按order_id分表-->

     <bean id="tableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy">

         <constructor-arg index="0" value="order_id"/>

         <constructor-arg index="1">

             <bean class="com.cnblogs.yjmyzz.sharding.algorithm.SingleKeyModuloTableShardingAlgorithm">

                 <!--tableCount的值要跟上面t_order表设置的分表个数保持一致-->

                 <property name="tableCount" value="2"/>

             </bean>

         </constructor-arg>

     </bean>

     <!--sharding规则Bean-->

     <bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule">

         <constructor-arg index="0" ref="dataSource"/>

         <constructor-arg index="1">

             <list>

                 <ref bean="orderTableRule"/>

             </list>

         </constructor-arg>

         <constructor-arg index="2" ref="databaseShardingStrategy"/>

         <constructor-arg index="3" ref="tableShardingStrategy"/>

     </bean>

     <!--sharding数据源-->

     <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource">

         <constructor-arg ref="shardingRule"/>

     </bean>

     <!--sharding事务管理器-->

     <!--<bean id="transactionManager"-->

           <!--class="org.springframework.jdbc.datasource.DataSourceTransactionManager">-->

         <!--<property name="dataSource" ref="shardingDataSource"/>-->

     <!--</bean>-->

     <!--<tx:annotation-driven transaction-manager="transactionManager"/>-->

     <!--mybatis配置-->

     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

         <property name="configLocation" value="classpath:mybatis-config.xml"></property>

         <property name="dataSource" ref="shardingDataSource"/>

         <property name="mapperLocations" value="classpath:mybatis/OrderMapper.xml"/>

     </bean>

     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

         <property name="basePackage" value="com.cnblogs.yjmyzz.sharding.mapper"/>

         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>

     </bean>

 </beans>

上面的配置，表示T_Order表按user_id进行分成ds_0,ds_1,ds_2共三库，每个库中又按order_id分成T_Order_0,T_Order_1二张表。

分库、分表是按常见的取模算法处理的，需要用户自定义二个类（基本上就是模板代码，不需要什么改动）

SingleKeyModuloDatabaseShardingAlgorithm

 /**

  * Copyright 1999-2015 dangdang.com.

  * <p>

  * Licensed under the Apache License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  * <p/>

  * http://www.apache.org/licenses/LICENSE-2.0

  * <p/>

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  * </p>

  */

 package com.cnblogs.yjmyzz.sharding.algorithm;

 import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;

 import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;

 import com.google.common.collect.Range;

 import java.util.Collection;

 import java.util.LinkedHashSet;

 public final class SingleKeyModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {

     private int dbCount = 1;

     @Override

     public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         for (String each : availableTargetNames) {

             if (each.endsWith(shardingValue.getValue() % dbCount + "")) {

                 return each;

             }

         }

         throw new UnsupportedOperationException();

     }

     @Override

     public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

         Collection<Integer> values = shardingValue.getValues();

         for (Integer value : values) {

             for (String dataSourceName : availableTargetNames) {

                 if (dataSourceName.endsWith(value % dbCount + "")) {

                     result.add(dataSourceName);

                 }

             }

         }

         return result;

     }

     @Override

     public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

         Range<Integer> range = shardingValue.getValueRange();

         for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {

             for (String each : availableTargetNames) {

                 if (each.endsWith(i % dbCount + "")) {

                     result.add(each);

                 }

             }

         }

         return result;

     }

     /**

      * 设置database分库的个数

      * @param dbCount

      */

     public void setDbCount(int dbCount) {

         this.dbCount = dbCount;

     }

 }
SingleKeyModuloTableShardingAlgorithm
 /**

  * Copyright 1999-2015 dangdang.com.

  * <p>

  * Licensed under the Apache License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  * <p/>

  * http://www.apache.org/licenses/LICENSE-2.0

  * <p/>

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  * </p>

  */

 package com.cnblogs.yjmyzz.sharding.algorithm;

 import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;

 import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;

 import com.google.common.collect.Range;

 import java.util.Collection;

 import java.util.LinkedHashSet;

 public final class SingleKeyModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

     private int tableCount = 1;

     @Override

     public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         for (String each : availableTargetNames) {

             if (each.endsWith(shardingValue.getValue() % tableCount + "")) {

                 return each;

             }

         }

         throw new UnsupportedOperationException();

     }

     @Override

     public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

         Collection<Integer> values = shardingValue.getValues();

         for (Integer value : values) {

             for (String tableNames : availableTargetNames) {

                 if (tableNames.endsWith(value % tableCount + "")) {

                     result.add(tableNames);

                 }

             }

         }

         return result;

     }

     @Override

     public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) {

         Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

         Range<Integer> range = shardingValue.getValueRange();

         for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {

             for (String each : availableTargetNames) {

                 if (each.endsWith(i % tableCount + "")) {

                     result.add(each);

                 }

             }

         }

         return result;

     }

     /**

      * 设置分表的个数

      *

      * @param tableCount

      */

     public void setTableCount(int tableCount) {

         this.tableCount = tableCount;

     }

 }

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

相关文章