mysql分库分表

       LZ工作快5年了，一直没有接触过数据库分库分表的业务，这不最近借助跳槽的动力，自己搞了一波，用的是dangdang开源的一个分库分表插件，实现了简单的分库分表的功能，话不多说，代码开路，github地址https://github.com/dangdangdotcom/sharding-jdbc。

       首先是jar包引用，maven以来如下

<dependency>
	<groupId>com.dangdang</groupId>
	<artifactId>sharding-jdbc-core</artifactId>
	<version>1.3.3</version>
</dependency>
<dependency>
	<groupId>com.dangdang</groupId>
	<artifactId>sharding-jdbc-config-spring</artifactId>
	<version>1.3.3</version>
</dependency>

 

       然后就是spring配置，单独建了一个文件，主xml中import一下，配置数据源和分库分表规则，目前的规则逻辑都是按照id%2配置的，具体应用按照自己实际业务来。

<?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: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.dangdang.com/schema/ddframe/rdb
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">

    <bean id="statFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
        <property name="statementExecutableSqlLogEnable" value="false"/>
        <property name="dataSourceLogEnabled" value="false"/>
    </bean>
    <bean id="logFilter" class="com.alibaba.druid.filter.stat.StatFilter">
        <property name="slowSqlMillis" value="50"/>
        <property name="logSlowSql" value="false"/>
        <property name="mergeSql" value="true"/>
    </bean>

    <bean id="master0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb00"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="10"/>
        <property name="initialSize" value="1"/>
        <property name="maxWait" value="60000"/>
        <property name="minIdle" value="1"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <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="50"/>
        <property name="maxOpenPreparedStatements" value="100"/>
        <property name="proxyFilters">
            <list>
                <ref bean="statFilter"/>
                <ref bean="logFilter"/>
            </list>
        </property>
    </bean>

    <bean id="master1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb01"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="10"/>
        <property name="initialSize" value="1"/>
        <property name="maxWait" value="60000"/>
        <property name="minIdle" value="1"/>
        <property name="timeBetweenEvictionRunsMillis" value="60000"/>
        <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="50"/>
        <property name="maxOpenPreparedStatements" value="100"/>
        <property name="proxyFilters">
            <list>
                <ref bean="statFilter"/>
                <ref bean="logFilter"/>
            </list>
        </property>
    </bean>

    <!--非必须的，可以省略掉，下面的rdb:sharding-rule直接配置数据源id即可>
    <rdb:master-slave-data-source id="rbb_0" master-data-source-ref="master0" slave-data-sources-ref="master0"/>
    <rdb:master-slave-data-source id="rbb_1" master-data-source-ref="master1" slave-data-sources-ref="master1"/>

    <rdb:strategy id="idDbSharding" sharding-columns="id"
                  algorithm-class="net.aty.spring.DbAlgorithm"/>

    <rdb:strategy id="idTbSharding" sharding-columns="id"
                  algorithm-class="net.aty.spring.TbAlgorithm"/>

    <rdb:data-source id="wholeDataSource">
        <rdb:sharding-rule data-sources="rbb_0,rbb_1">
            <rdb:table-rules>
                <rdb:table-rule logic-table="user" actual-tables="user_${0..1}"
                                database-strategy="idDbSharding" table-strategy="idTbSharding"/>
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>

</beans>

 

       接下来是分库分表规则类 

package com.feng.splitdbtb;

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

import java.util.Collection;

public class DbAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> {

    @Override
    public String doEqualSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) {
        int id = shardingValue.getValue();

        int index = id % 2;

        for (String each : collection) {
            if (each.endsWith(index + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

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

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

        分表规则类

package com.feng.splitdbtb;

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

import java.util.Collection;

public class TbAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
        int id = shardingValue.getValue();

        int index = id % 2;

        for (String each : availableTargetNames) {
            if (each.endsWith(index + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

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

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

       

       最后是建库建表语句，我只给一个例子，具体的数据库包括dbdemo00(表user_0,user_1)，dbdemo01(表user_0,user_1)

drop database if exists demodb00;
CREATE database demodb00 DEFAULT CHARACTER SET utf8;

CREATE TABLE demodb00.user (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY id_UNIQUE (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        聪明的你是不是会问，多数据库提交的事务问题啊，下一篇介绍分布式事务atomikos