项目中sharding-jdbc的实际应用

简单介绍下最近项目中遇到的场景：敝司最近发展酒店业务，需要从各个供应商接入酒店数据、报价等信息，国内现有正宗酒店大约80万，每个酒店有很多房型（标间、大床间 豪华间等），每个房型有会有多个销售套餐（如含早不含早、有无有窗、带不带优惠等），每个供应商都会有自己的销售套餐和对应的报价（一般支持90天），预计供应商会有40家，这样会有80w 酒店* 15 房型 * 10销售套餐 * 90天报价 ≈ 100亿个报价数据，这些数据需要落地到我们的库里面，以便于进行展示。因此需要将销售套餐及90天报价进行分库分表设计，平摊数据库实例的数据量和并发压力。

一.设计方案：

1）因为这些数据都是单条读写，不会出现复杂的关联查询，因此我们能够接受每张表最多存储500w条数据，总共需要 100亿/500万=5000表 约等于 2^12 = 64 * 16.

所以设计成16个数据库，每个库16张表。

2) RP路由规则：

DBID = supplierCode+cityCode%M

TABLE_ID = hotelCode % N

其中RP_ID为RP主键，M为数据库实例数量， N为每个库分表数量

3) Price路由规则：

DBID = supplierCode+cityCode%M

TABLE_ID = hotelCode % N 

其中RPID为RP主键，M为数据库实例数量， N为每个库分表数量

注：supplierCode、cityCode、hotelCode格式为字符串，考虑转成均匀分布的数字型id，采用FNVHash1算法处理。

sharding-jdbc相关配置（保密原因只能给出示例）

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

    <bean id="hotelPriceDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${project.jdbc.driver}"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/hotel_price"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="ds_0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${project.jdbc.driver}"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/ds_0"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="ds_1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${project.jdbc.driver}"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/ds_1"/>
        <property name="username" value="${sharding.jdbc.username}"/>
        <property name="password" value="${sharding.jdbc.password}"/>
    </bean>

    <bean id="ds_2" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${project.jdbc.driver}"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/ds_2"/>
        <property name="username" value="${sharding.jdbc.username}"/>
        <property name="password" value="${sharding.jdbc.password}"/>
    </bean>

    <bean id="ds_3" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="driverClassName" value="${project.jdbc.driver}"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/ds_3"/>
        <property name="username" value="${sharding.jdbc.username}"/>
        <property name="password" value="${sharding.jdbc.password}"/>
    </bean>

    <rdb:strategy id="databaseShardingStrategy" sharding-columns="supplier_code,city_code"
                  algorithm-class="com.jd.hotel.price.common.sharding.algorithm.DatabaseShardingAlgorithm"/>

    <rdb:strategy id="tableShardingStrategy" sharding-columns="hotel_code"
                  algorithm-class="com.jd.hotel.price.common.sharding.algorithm.TableShardingAlgorithm"/>

    <rdb:data-source id="shardingDataSource">
        <rdb:sharding-rule data-sources="ds_0, ds_1, ds_2, ds_3">
            <rdb:table-rules>
                <rdb:table-rule logic-table="hotel_rate_plan" actual-tables="hotel_rate_plan_${0..3}"
                                database-strategy="databaseShardingStrategy" table-strategy="tableShardingStrategy" />
                <rdb:table-rule logic-table="hotel_price" actual-tables="hotel_price_${0..3}"
                            database-strategy="databaseShardingStrategy" table-strategy="tableShardingStrategy" />
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>

    <!-- 配置Session工厂 -->
    <bean id="shardingSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="typeAliasesPackage" value="com.jd.hotel.sharding.price.entity.sharding"/>
        <property name="configLocation" value="classpath:jdbc/mybatis-config-sharding.xml"/>
        <property name="mapperLocations" value="classpath*:/com/jd/trip/hotel/price/mapper/sharding/*.xml"/>
    </bean>

    <bean id="shardingMapperScannerConfig" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.jd.trip.hotel.price.dao.sharding" />
        <property name="sqlSessionFactoryBeanName" value="shardingSqlSessionFactory" />
    </bean>

</beans>

自定义分库分表策略：

/**
 * @author zhuangguoshuai
 * @description 分库算法
 * @create 2017-10-09 17:40
 **/
public final class DatabaseShardingAlgorithm implements MultipleKeysDatabaseShardingAlgorithm {
    //TODO 为啥这个方法需要返回collection类型值呢？
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue<?>> shardingValues) {

        String sahrdingValueStr = shardingValues.stream().map(sv -> (String)sv.getValue()).reduce("", String::concat);
        int shardingDatabaseSuffix = Math.abs(HashUtils.FNVHash1(String.valueOf(StringUtils.join(sahrdingValueStr))))% CommonConstant.SHARDING_DATABASE_NUM;
        String dbName = availableTargetNames.stream().filter(each -> each.endsWith(String.valueOf(shardingDatabaseSuffix))).findAny().get();
        return  Collections.singletonList(dbName);

    }
}

/**
 * @author zhuangguoshuai
 * @description  分表策略
 * @create 2017-10-09 17:41
 **/
public final class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<String> {

    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<String> shardingValue) {

        int shardingTableSuffix =  Math.abs(HashUtils.FNVHash1(shardingValue.getValue()))% CommonConstant.SHARDING_TABLE_NUM;
            for (String each : availableTargetNames) {
            if (each.endsWith(String.valueOf(shardingTableSuffix))) {
                return each;
            }
        }
        return null;
    }

    @Override
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<String> shardingValue) {
        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<String> shardingValue) {
        return null;
    }
}