分庫分表是解決關係數據庫大表的一個很重要的方式，本文將詳細講解如何使用噹噹網開源的框架來實現分庫分表的具體實現，本文是不可多得的詳細講解並親自實踐的Sharding-jdbc的文章，請大家收藏學習。

一：數據庫分片方案

• 客戶端代理： 分片邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實現。 噹噹網的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實現。
• 中間件代理： 在應用和數據中間加了一個代理層。分片邏輯統一維護在中間件服務中。 我們現在談的 Mycat、360的Atlas、網易的DDB等等都是這種架構的實現

二：Sharding-JDBC

Sharding-JDBC: https://github.com/dangdangdotcom/sharding-jdbc

Sharding-JDBC是一個開源的適用於微服務的分佈式數據訪問基礎類庫，它始終以雲原生的基礎開發套件為目標。

Sharding-JDBC定位為輕量級java框架，使用客戶端直連數據庫，以jar包形式提供服務，未使用中間層，無需額外部署，無其他依賴，DBA也無需改變原有的運維方式，可理解為增強版的JDBC驅動，舊代碼遷移成本幾乎為零。

Sharding-JDBC完整的實現了分庫分表，讀寫分離和分佈式主鍵功能，並初步實現了柔性事務。從2016年開源至今，在經歷了整體架構的數次精煉以及穩定性打磨後，如今它已積累了足夠的底蘊，相信可以成為開發者選擇技術組件時的一個參考。

1.分庫分表

• SQL解析功能完善，支持聚合，分組，排序，LIMIT，TOP等查詢，並且支持級聯表以及笛卡爾積的表查詢
• 支持內、外連接查詢
• 分片策略靈活，可支持=，BETWEEN，IN等多維度分片，也可支持多分片鍵共用，以及自定義分片策略
• 基於Hint的強制分庫分表路由

2.讀寫分離

• 一主多從的讀寫分離配置，可配合分庫分表使用
• 基於Hint的強制主庫路由

3.柔性事務

• 最大努力送達型事務
• TCC型事務(TBD)

4.分佈式主鍵

• 統一的分佈式基於時間序列的ID生成器

5.兼容性

• 可適用於任何基於java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC
• 可基於任何第三方的數據庫連接池，如：DBCP, C3P0, BoneCP, Druid等
• 理論上可支持任意實現JDBC規範的數據庫。目前支持MySQL，Oracle，SQLServer和PostgreSQL

6.靈活多樣的配置

• Java
• YAML
• Inline表達式
• Spring命名空間
• Spring boot starter

7.分佈式治理能力 (2.0新功能)

• 配置集中化與動態化，可支持數據源、表與分片策略的動態切換(2.0.0.M1)
• 客戶端的數據庫治理，數據源失效自動切換(2.0.0.M2)
• 基於Open Tracing協議的APM信息輸出(2.0.0.M3)

架構圖

三：sharding-jdbc + jpa + druid集成

1. 數據庫準備

<code>-- 在db0數據庫上分別創建t_order_0、t_order_1表
USE db0;
DROP TABLE IF EXISTS t_order_0; 
CREATE TABLE t_order_0 ( 
order_id bigint(20) NOT NULL, 
user_id bigint(20) NOT NULL, 
PRIMARY KEY (order_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 
DROP TABLE IF EXISTS t_order_1; 
CREATE TABLE t_order_1 ( 
order_id bigint(20) NOT NULL, 
user_id bigint(20) NOT NULL, 
PRIMARY KEY (order_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 


-- 在db1數據庫上分別創建t_order_0、t_order_1表
USE db1;
DROP TABLE IF EXISTS t_order_0; 
CREATE TABLE t_order_0 ( 
order_id bigint(20) NOT NULL, 
user_id bigint(20) NOT NULL, 
PRIMARY KEY (order_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 

DROP TABLE IF EXISTS t_order_1; 
CREATE TABLE t_order_1 ( 
order_id bigint(20) NOT NULL, 
user_id bigint(20) NOT NULL, 
PRIMARY KEY (order_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 
/<code>

2. 引入依賴

<code>
<project>         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelversion>4.0.0/<modelversion>

    <groupid>com.company/<groupid>
    <artifactid>sharding-jdbc/<artifactid>
    <version>0.0.1-SNAPSHOT/<version> 

    <packaging>jar/<packaging>

    <name>sharding-jdbc/<name>
    <description>Demo project for Spring Boot/<description>

    <parent>
        <groupid>org.springframework.boot/<groupid>
        <artifactid>spring-boot-starter-parent/<artifactid>
        <version>2.0.3.RELEASE/<version>
        <relativepath> 
    /<parent>

    <properties>
        <project.build.sourceencoding>UTF-8/<project.build.sourceencoding>
        <project.reporting.outputencoding>UTF-8/<project.reporting.outputencoding>
        <java.version>1.8/<java.version>
    /<properties>

    <dependencies>
        <dependency>
            <groupid>mysql/<groupid>
            <artifactid>mysql-connector-java/<artifactid>
            <version>5.1.41/<version>
        /<dependency>

        <dependency>
            <groupid>com.alibaba/<groupid>
            <artifactid>druid/<artifactid>
            <version>1.1.10/<version>
        /<dependency>

        <dependency>
            <groupid>org.springframework.boot/<groupid>
            <artifactid>spring-boot-starter-data-jpa/<artifactid>
        /<dependency>

        <dependency>
            <groupid>com.dangdang/<groupid>
            <artifactid>sharding-jdbc-core/<artifactid>
            <version>1.5.4/<version>
        /<dependency>
        <dependency>
            <groupid>org.springframework.boot/<groupid>
            <artifactid>spring-boot-starter-web/<artifactid>
        /<dependency>

        <dependency>
            <groupid>org.projectlombok/<groupid>
            <artifactid>lombok/<artifactid> 

            <optional>true/<optional>
        /<dependency>
        <dependency>
            <groupid>org.springframework.boot/<groupid>
            <artifactid>spring-boot-starter-test/<artifactid>
            <scope>test/<scope>
        /<dependency>
    /<dependencies>

    <build>
        <plugins>
            <plugin>
                <groupid>org.springframework.boot/<groupid>
                <artifactid>spring-boot-maven-plugin/<artifactid>
            /<plugin>
        /<plugins>
    /<build>
/<project>
/<code>

注意mysql-connector-java的版本不要太高了

3. application.yml

<code>spring:
  jpa:
    database: mysql
    show-sql: true
    hibernate:
      ddl-auto: none
/<code>

注意：hibernate.ddl-auto=none 是因為分表就會有多個表，例如torder0、torder1等，而ORM只能映射成一個，所以關閉自動的ddl語句。

4. domain

<code>@Entity
@Table(name = "t_order")
@Data
public class Order {
    @Id
    private Long orderId;

    private Long userId;
}
/<code> 

注意：orderId上使用@Id註解並沒有使用@GeneratedValue(strategy = GenerationType.AUTO)的主鍵生成策略，原因是分表必須要保證所有表的主鍵id不重複，如果使用mysql的自動生成，那麼id就會重複，這裡的id一般要使用分佈式主鍵id來通過代碼來生成。

5. Repository

<code>import com.company.shardingjdbc.domain.Order;
import org.springframework.data.repository.CrudRepository;

public interface OrderRepository extends CrudRepository<order> {
}
/<order>/<code>

6. Controller

<code>import com.company.shardingjdbc.domain.Order;
import com.company.shardingjdbc.repository.OrderRepository;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private KeyGenerator keyGenerator;

    @RequestMapping("/create")
    public Object add() {
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setUserId((long) i);
            order.setOrderId((long) i);
            orderRepository.save(order);
        }
        for (int i = 10; i < 20; i++) {
            Order order = new Order();
            order.setUserId((long) i + 1); 

            order.setOrderId((long) i);
            orderRepository.save(order);
        }

//        for (int i = 0; i < 30; i++) {
//            Order order = new Order();
//            order.setOrderId(keyGenerator.generateKey().longValue());
//            order.setUserId(keyGenerator.generateKey().longValue());
//            orderRepository.save(order);
//        }

        return "success";
    }

    @RequestMapping("query")
    private Object queryAll() {
        return orderRepository.findAll();
    }
}
/<code>

7. Configuration

<code>package com.company.shardingjdbc.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.company.shardingjdbc.common.ModuleDatabaseShardingAlgorithm;
import com.company.shardingjdbc.common.ModuleTableShardingAlgorithm;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import com.mysql.jdbc.Driver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;


@Configuration
public class DataSourceConfiguration {
    @Bean 

    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        // 設置分庫映射
        Map<string> dataSourceMap = new HashMap<>(2);
        // 添加兩個數據庫db0,db1到map裡
        dataSourceMap.put("db0", createDataSource("db0"));
        dataSourceMap.put("db1", createDataSource("db1"));
        // 設置默認db為db0，也就是為那些沒有配置分庫分表策略的指定的默認庫
        // 如果只有一個庫，也就是不需要分庫的話，map裡只放一個映射就行了，只有一個庫時不需要指定默認庫，但2個及以上時必須指定默認庫，否則那些沒有配置策略的表將無法操作數據
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, "db0");

        // 設置分表映射，將t_order_0和t_order_1兩個實際的表映射到t_order邏輯表
        // 0和1兩個表是真實的表，t_order是個虛擬不存在的表，只是供使用。如查詢所有數據就是select * from t_order就能查完0和1表的
        TableRule orderTableRule = TableRule.builder("t_order")
                .actualTables(Arrays.asList("t_order_0", "t_order_1"))
                .dataSourceRule(dataSourceRule)
                .build();

        // 具體分庫分表策略，按什麼規則來分
        ShardingRule shardingRule = ShardingRule.builder()
                .dataSourceRule(dataSourceRule)
                .tableRules(Arrays.asList(orderTableRule))
                .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuleDatabaseShardingAlgorithm()))
                .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuleTableShardingAlgorithm())).build();

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
 

        return dataSource;
    }

    private static DataSource createDataSource(final String dataSourceName) {
        // 使用druid連接數據庫
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(Driver.class.getName());
        result.setUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName));
        result.setUsername("root");
        result.setPassword("root123");
        return result;
    }

    @Bean
    public KeyGenerator keyGenerator() {
        return new DefaultKeyGenerator();
    }
}
/<string>/<code>

ModuleDatabaseShardingAlgorithm

<code>package com.company.shardingjdbc.common;

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;

/**
 * 單鍵數據庫分片算法.
 *
 * 支持單鍵和多鍵策略
 * 

 *     
單鍵 SingleKeyDatabaseShardingAlgorithm

 *     
多鍵 MultipleKeysDatabaseShardingAlgorithm

 * 

 *
 * 支持的分片策略 

 * 

 *     
 = doEqualSharding 例如 where order_id = 1 

 *     
 IN doInSharding 例如 where order_id in (1, 2)

 *     
 BETWEEN doBetweenSharding 例如 where order_id between 1 and 2 

 * 

 *
 * @author mengday
 */
public class ModuleDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<long> {

    /**
     * 分片策略 相等=
     * @param availableTargetNames 可用的目標名字(這裡指數據名db0、db1)
     * @param shardingValue 分片值[logicTableName="t_order" 邏輯表名, columnName="user_id" 分片的列名, value="20" 分片的列名對應的值(user_id=20)]
     * @return
     */
    @Override
    public String doEqualSharding(Collection<string> availableTargetNames, ShardingValue<long> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<string> doInSharding(Collection<string> availableTargetNames, ShardingValue<long> shardingValue) {
        Collection<string> result = new LinkedHashSet<>(availableTargetNames.size());
        for (Long value : shardingValue.getValues()) {
            for (String tableName : availableTargetNames) {
                if (tableName.endsWith(value % 2 + "")) {
                    result.add(tableName);
                }
            }
        }
        return result;
    } 


    @Override
    public Collection<string> doBetweenSharding(Collection<string> availableTargetNames,
                                                ShardingValue<long> shardingValue) {
        Collection<string> result = new LinkedHashSet<>(availableTargetNames.size());
        Range<long> range = shardingValue.getValueRange();
        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i % 2 + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}
/<long>/<string>/<long>/<string>/<string>/<string>/<long>/<string>/<string>/<long>/<string>/<long>/<code>

<code>package com.company.shardingjdbc.common;

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 ModuleTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<long> {

    /**
     * doEqualSharding =
     * @param tableNames 實際物理表名
     * @param shardingValue [logicTableName="t_order", columnName="order_id", value=20]
     * 
     *  select * from t_order from t_order where order_id = 11
     *          └── SELECT *  FROM t_order_1 WHERE order_id = 11
     *  select * from t_order from t_order where order_id = 44
     *          └── SELECT *  FROM t_order_0 WHERE order_id = 44
     */
     *  select * from t_order from t_order where order_id = 11
     *          └── SELECT *  FROM t_order_1 WHERE order_id = 11
     *  select * from t_order from t_order where order_id = 44
     *          └── SELECT *  FROM t_order_0 WHERE order_id = 44
     */
    public String doEqualSharding(final Collection<string> tableNames, final ShardingValue<long> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        } 

        throw new IllegalArgumentException();
    }

    /**
     *  select * from t_order from t_order where order_id in (11,44)
     *          ├── SELECT *  FROM t_order_0 WHERE order_id IN (11,44)
     *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,44)
     *  select * from t_order from t_order where order_id in (11,13,15)
     *          └── SELECT *  FROM t_order_1 WHERE order_id IN (11,13,15)
     *  select * from t_order from t_order where order_id in (22,24,26)
     *          └──SELECT *  FROM t_order_0 WHERE order_id IN (22,24,26)
     */
    public Collection<string> doInSharding(final Collection<string> tableNames, final ShardingValue<long> shardingValue) {
        Collection<string> result = new LinkedHashSet<>(tableNames.size());
        for (Long value : shardingValue.getValues()) {
            for (String tableName : tableNames) {
                if (tableName.endsWith(value % 2 + "")) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
    /**
     *  select * from t_order from t_order where order_id between 10 and 20
     *          ├── SELECT *  FROM t_order_0 WHERE order_id BETWEEN 10 AND 20
     *          └── SELECT *  FROM t_order_1 WHERE order_id BETWEEN 10 AND 20
     */
    public Collection<string> doBetweenSharding(final Collection<string> tableNames, final ShardingValue<long> shardingValue) {
        Collection<string> result = new LinkedHashSet<>(tableNames.size());
        Range<long> range = shardingValue.getValueRange();
        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : tableNames) {
                if (each.endsWith(i % 2 + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}
/<long>/<string>/<long>/<string>/<string>/<string>/<long>/<string>/<string>/<long>/<string>/<long>/<code>

8. localhost:8080/order/create

db0 ├── torder0 userid為偶數 orderid為偶數 ├── torder1 userid為偶數 orderid為奇數 db1 ├── torder0 userid為奇數 orderid為偶數 ├── torder1 userid為奇數 orderid為奇數

四：sharding-jdbc + mybatis + druid集成

此示例是在jap原有的集成上集成mybatis

1. 引入mybatis依賴

<code><dependency>
    <groupid>org.mybatis.spring.boot/<groupid>
    <artifactid>mybatis-spring-boot-starter/<artifactid>
    <version>1.3.2/<version>
/<dependency>
/<code>

2. 在Application上添加註解@MapperScan

<code>@MapperScan("com.company.shardingjdbc.mapper")
@SpringBootApplication
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }
}
/<code>

3. application.yml

<code># Mybatis 配置
mybatis:
  typeAliasesPackage: com.company.shardingjdbc.domain
  mapperLocations: classpath:mapper/*.xml
  configuration.map-underscore-to-camel-case: true

# 打印mybatis中的sql語句和結果集
logging:
  level.com.company.shardingjdbc.mapper: TRACE
/<code>

4. OrderMapper

<code>import org.apache.ibatis.annotations.Param;

import java.util.List;
 

public interface OrderMapper {

    void insert(Order order);

    List<order> queryById(@Param("orderIdList") List<long> orderIdList);
}
/<long>/<order>/<code>

5. OrderMapper.xml

<code>

<mapper>
    <select>
        SELECT * FROM t_order WHERE order_id IN
        <foreach>
            #{orderId}
        /<foreach>
    /<select>

    <insert>
        INSERT INTO t_order (order_id, user_id) VALUES (#{orderId}, #{userId})
    /<insert>
/<mapper>
/<code>

6. OrderController

<code>import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderMapper orderMapper;

    @RequestMapping("/insert")
    public Object insert() {
        for (int i = 20; i < 30; i++) {
            Order order = new Order();
            order.setUserId((long) i); 

            order.setOrderId((long) i);
            orderMapper.insert(order);
        }
        for (int i = 30; i < 40; i++) {
            Order order = new Order();
            order.setUserId((long) i + 1);
            order.setOrderId((long) i);
            orderMapper.insert(order);
        }

        return "success";
    }

    @RequestMapping("queryById")
    public List<order> queryById(String orderIds) {
        List<string> strings = Arrays.asList(orderIds.split(","));
        List<long> orderIdList = strings.stream().map(item -> Long.parseLong(item)).collect(Collectors.toList());
        return orderMapper.queryById(orderIdList);
    }
}
/<long>/<string>/<order>/<code>

7. 插入數據

localhost:8080/order/insert

• ModuleDatabaseShardingAlgorithm: 先根據分片鍵user_id及值來確定要操作的數據庫是db0還是db1
• ModuleTableShardingAlgorithm: 再根據分片鍵orderid及值來確定要操作的數據庫對應的表是torder0還是torder_1
• 當數據庫名和表名都確定了就可以操作數據庫了

localhost:8080/order/queryById?orderIds=20,31,30,21

五：源碼

歡迎關注Java實用技術，及時閱讀每天的優質文章。獲取源碼請大家 轉發+關注 該文章使更多的朋友學習並私信獲取文章源碼，謝謝大家的支持。

閱讀更多 Java實用技術 的文章

關鍵字: Server 源碼 分表