jinfeng_wang

數(shù)據(jù)庫模式

本文檔中提供了兩個數(shù)據(jù)源db0和db1，每個數(shù)據(jù)源之中包含了兩組表t_order_0和t_order_1，t_order_item_0和t_order_item_1。這兩組表的建表語句為：

CREATE TABLE IF NOT EXISTS `t_order_x` (   `order_id` INT NOT NULL,   `user_id`  INT NOT NULL,   PRIMARY KEY (`order_id`) ); CREATE TABLE IF NOT EXISTS `t_order_item_x` (   `item_id`  INT NOT NULL,   `order_id` INT NOT NULL,   `user_id`  INT NOT NULL,   PRIMARY KEY (`item_id`) ); 

邏輯表與實際表映射關系

均勻分布

數(shù)據(jù)表在每個數(shù)據(jù)源內(nèi)呈現(xiàn)均勻分布的態(tài)勢

db0   ├── t_order_0    └── t_order_1  db1   ├── t_order_0    └── t_order_1 

表規(guī)則可以使用默認的配置

 TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1")).dataSourceRule(dataSourceRule).build(); 

自定義分布

數(shù)據(jù)表呈現(xiàn)有特定規(guī)則的分布

db0   ├── t_order_0    └── t_order_1  db1   ├── t_order_2   ├── t_order_3   └── t_order_4 

表規(guī)則可以指定每張表在數(shù)據(jù)源中的分布情況

 TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("db0.t_order_0", "db0.t_order_1", "db1.t_order_2", "db1.t_order_3", "db1.t_order_4")).dataSourceRule(dataSourceRule).build(); 

本教程采用的數(shù)據(jù)分布例子

db0   ├── t_order_0               user_id為偶數(shù)   order_id為偶數(shù)   ├── t_order_1               user_id為偶數(shù)   order_id為奇數(shù)   ├── t_order_item_0          user_id為偶數(shù)   order_id為偶數(shù)   └── t_order_item_1          user_id為偶數(shù)   order_id為奇數(shù) db1   ├── t_order_0               user_id為奇數(shù)   order_id為偶數(shù)   ├── t_order_1               user_id為奇數(shù)   order_id為奇數(shù)   ├── t_order_item_0          user_id為奇數(shù)   order_id為偶數(shù)   └── t_order_item_1          user_id為奇數(shù)   order_id為奇數(shù) 

邏輯表與實際表

配置分庫分表的目的是將原有一張表的數(shù)據(jù)分散到不同庫不同表中，且不改變原有SQL語句的情況下來使用這一張表。那么從一張表到多張的映射關系需要使用邏輯表與實際表這兩種概念。下面通過一個例子來解釋一下。假設在使用PreparedStatement訪問數(shù)據(jù)庫，SQL如下：

select * from t_order where user_id = ? and order_id = ?; 

當user_id=0且order=0時，Sharding-JDBC將會將SQL語句轉(zhuǎn)換為如下形式：

select * from db0.t_order_0 where user_id = ? and order_id = ?; 

其中原始SQL中的t_order就是 邏輯表，而轉(zhuǎn)換后的db0.t_order_0就是 實際表

規(guī)則配置

以上分庫分表的形式Sharding-JDBC是通過規(guī)則配置來進行的描述的，下面講通過幾個小節(jié)來描述規(guī)則的詳細配置：

 ShardingRule shardingRule = ShardingRule.builder()         .dataSourceRule(dataSourceRule)         .tableRules(Arrays.asList(orderTableRule, orderItemTableRule))         .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))         .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())))         .build(); 

數(shù)據(jù)源配置

首先我們來構(gòu)造DataSourceRule對象，它是來描述數(shù)據(jù)源的分布規(guī)則的。

 DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap); 

這里構(gòu)造器需要一個入?yún)ⅲ簲?shù)據(jù)源名稱與真實數(shù)據(jù)源之間的映射關系，這個關系的構(gòu)造方法如下

Map<String, DataSource> dataSourceMap = new HashMap<>(2); dataSourceMap.put("ds_0", createDataSource("ds_0")); dataSourceMap.put("ds_1", createDataSource("ds_1")); 

真實的數(shù)據(jù)源可以使用任意一種數(shù)據(jù)庫連接池，這里使用DBCP來舉例

private static DataSource createDataSource(final String dataSourceName) {     BasicDataSource result = new BasicDataSource();     result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());     result.setUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName));     result.setUsername("root");     result.setPassword("");     return result; } 

策略配置

數(shù)據(jù)源策略與表策略

Sharding-JDBC認為對于分片策略存有兩種維度 - 數(shù)據(jù)源分片策略DatabaseShardingStrategy：數(shù)據(jù)被分配的目標數(shù)據(jù)源 - 表分片策略TableShardingStrategy：數(shù)據(jù)被分配的目標表，該目標表存在與該數(shù)據(jù)的目標數(shù)據(jù)源內(nèi)。故表分片策略是依賴與數(shù)據(jù)源分片策略的結(jié)果的 這里注意的是兩種策略的API完全相同，以下針對策略API的講解將適用于這兩種策略

全局默認策略與特定表策略

策略是作用在特定的表規(guī)則上的，數(shù)據(jù)源策略與表策略與特定表相關

 TableRule orderTableRule = TableRule.builder("t_order")          .actualTables(Arrays.asList("t_order_0", "t_order_1")          .dataSourceRule(dataSourceRule)          .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))          .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())))          .build(); 

如果分片規(guī)則中的所有表或大部分表的分片策略相同，可以使用默認策略來簡化配置。以下兩種配置是等價的:

  //使用了默認策略配置   TableRule orderTableRule = TableRule.builder("t_order")           .actualTables(Arrays.asList("t_order_0", "t_order_1")           .dataSourceRule(dataSourceRule)           .build();   TableRule orderItemTableRule = TableRule.builder("t_order_item")             .actualTables(Arrays.asList("t_order_item_0", "t_order_item_1")             .dataSourceRule(dataSourceRule)             .build();   ShardingRule shardingRule = ShardingRule.builder()             .dataSourceRule(dataSourceRule)             .tableRules(Arrays.asList(orderTableRule, orderItemTableRule))             .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))             .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())))             .build(); 

  //未使用默認策略配置   TableRule orderTableRule = TableRule.builder("t_order")           .actualTables(Arrays.asList("t_order_0", "t_order_1")           .dataSourceRule(dataSourceRule)           .build();   TableRule orderItemTableRule = TableRule.builder("t_order_item")             .actualTables(Arrays.asList("t_order_item_0", "t_order_item_1")             .dataSourceRule(dataSourceRule)             .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))             .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())))             .build();   ShardingRule shardingRule = ShardingRule.builder()             .dataSourceRule(dataSourceRule)             .tableRules(Arrays.asList(orderTableRule, orderItemTableRule))             .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))             .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())))             .build(); 

分片鍵

分片鍵是分片策略的第一個參數(shù)。分片鍵表示的是SQL語句中WHERE中的條件列。分片鍵可以配置多個

• 單分片策略

new TableShardingStrategy("order_id", new SingleKeyShardingAlgorithm())) 

• 多分片策略

new TableShardingStrategy(Arrays.asList("order_id", "order_type", "order_date"), new MultiKeyShardingAlgorithm())) 

分片算法

分片算法接口類圖關系如下：

綁定表

綁定表代表一組表，這組表的邏輯表與實際表之間的映射關系是相同的。比如t_order與t_order_item就是這樣一組綁定表關系,它們的分庫與分表策略是完全相同的,那么可以使用它們的表規(guī)則將它們配置成綁定表

new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule)) 

那么在進行SQL路由時，如果SQL為

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=? 

其中t_order在FROM的最左側(cè)，Sharding-JDBC將會以它作為整個綁定表的主表。所有路由計算將會只使用主表的策略，那么t_order_item表的分片計算將會使用t_order的條件。故綁定表之間的分區(qū)鍵要完全相同。

分片算法詳解

單分片鍵算法與多分片鍵算法

這兩種算法從名字上就可以知道前者是針對只有一個分片鍵，后者是針對有多個分片鍵的。單分片鍵算法是多分片鍵算法的一種簡便形式，所以完全可以使用多分片算法去替代單分片鍵算法。下面兩種形式是等價的

new TableShardingStrategy("order_id", new SingleKeyShardingAlgorithm())) new TableShardingStrategy(Arrays.asList("order_id"), new MultiKeyShardingAlgorithm())) 

同時在算法內(nèi)部，doSharding等方法的shardingValue入?yún)⒏鶕?jù)使用算法類型不同而不同 單分片鍵算法，方法簽名

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

多分片鍵算法，方法簽名

public Collection<String> doSharding(final Collection<String> availableTargetNames, final Collection<ShardingValue<?>> shardingValues) 

分片鍵算法類型

根據(jù)數(shù)據(jù)源策略與表策略、單分片與多分片，這兩種組合，一共產(chǎn)生了4種可供實現(xiàn)的分片算法的接口

• 單分片鍵數(shù)據(jù)源分片算法SingleKeyDatabaseShardingAlgorithm
• 單分片表分片算法SingleKeyTableShardingAlgorithm
• 多分片鍵數(shù)據(jù)源分片算法MultipleKeyDatabaseShardingAlgorithm
• 多分片表分片算法MultipleKeyTableShardingAlgorithm

單分片鍵算法

單分片鍵算法需要實現(xiàn)三個方法，下面以”單分片鍵數(shù)據(jù)源分片算法“舉例

@Override public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue)  @Override public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue)  @Override public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Integer> shardingValue) 

這三種算法作用如下 - doEqualSharding在WHERE使用=作為條件分片鍵。算法中使用shardingValue.getValue()獲取等=后的值 - doInSharding在WHERE使用IN作為條件分片鍵。算法中使用shardingValue.getValues()獲取IN后的值 - doBetweenSharding在WHERE使用BETWEEN作為條件分片鍵。算法中使用shardingValue.getValueRange()獲取BETWEEN后的值

下面是一個余2的算法的例子，當分片鍵的值除以2余數(shù)就是實際表的結(jié)尾。注意注釋中提供了一些算法生成SQL的結(jié)果，參數(shù)tableNames集合中有兩個參數(shù)t_order_0和t_order_1

 public final class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {          /**     *  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<Integer> 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<Integer> shardingValue) {         Collection<String> result = new LinkedHashSet<>(tableNames.size());         for (Integer 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<Integer> shardingValue) {         Collection<String> result = new LinkedHashSet<>(tableNames.size());         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();         for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {             for (String each : tableNames) {                 if (each.endsWith(i % 2 + "")) {                     result.add(each);                 }             }         }         return result;     } } 

多分片鍵算法

多分片鍵試用于使用場景比較復雜，為了能提供更高的靈活性，故只提供實現(xiàn)一個方法。

@Override public Collection<String> doSharding(final Collection<String> availableTargetNames, final Collection<ShardingValue<?>> shardingValues) 

算法實現(xiàn)的時候根據(jù)shardingValue.getType()來獲取條件是=，IN或者BETWEEN。然后根據(jù)業(yè)務進行靈活的實現(xiàn)。

如果表的數(shù)據(jù)分布如下

db0   ├── t_order_00               user_id以a偶數(shù)   order_id為偶數(shù)   ├── t_order_01               user_id以a偶數(shù)   order_id為奇數(shù)   ├── t_order_10               user_id以b奇數(shù)   order_id為偶數(shù)   └── t_order_11               user_id以b奇數(shù)   order_id為奇數(shù)  

算法實現(xiàn)如下:

public final class MultipleKeysModuloTableShardingAlgorithm implements MultipleKeysTableShardingAlgorithm {          @Override     public Collection<String> doSharding(final Collection<String> availableTargetNames, final Collection<ShardingValue<?>> shardingValues) {         Set<Integer> orderIdValueSet = getShardingValue(shardingValues, "order_id");         Set<Integer> userIdValueSet = getShardingValue(shardingValues, "user_id");              List<String> result = new ArrayList<>();         /*         userIdValueSet[10,11] + orderIdValueSet[101,102] => valueResult[[10,101],[10,102],[11,101],[11,102]]          */         Set<List<Integer>> valueResult = Sets.cartesianProduct(userIdValueSet, orderIdValueSet);         for (List<Integer> value : valueResult) {             String suffix = Joiner.on("").join(value.get(0) % 2, value.get(1) % 2);             for (String tableName : availableTargetNames) {                 if (tableName.endsWith(suffix)) {                     result.add(tableName);                 }             }                  }         return result;     }          private Set<Integer> getShardingValue(final Collection<ShardingValue<?>> shardingValues, final String shardingKey) {         Set<Integer> valueSet = new HashSet<>();         ShardingValue<Integer> shardingValue = null;         for (ShardingValue<?> each : shardingValues) {             if (each.getColumnName().equals(shardingKey)) {                 shardingValue = (ShardingValue<Integer>) each;                 break;             }         }         if (null == shardingValue) {             return valueSet;         }         switch (shardingValue.getType()) {             case SINGLE:                 valueSet.add(shardingValue.getValue());                 break;             case LIST:                 valueSet.addAll(shardingValue.getValues());                 break;             case RANGE:                 for (Integer i = shardingValue.getValueRange().lowerEndpoint(); i <= shardingValue.getValueRange().upperEndpoint(); i++) {                     valueSet.add(i);                 }                 break;             default:                 throw new UnsupportedOperationException();         }         return valueSet;     } } 

構(gòu)造ShardingDataSource

完成規(guī)則配置后，我們可以通過ShardingDataSourceFactory工廠得到ShardingDataSource

DataSource dataSource = new ShardingDataSourceFactory.createDataSource(shardingRule); 

使用ShardingDataSource

通過一個例子來看看如何使用該數(shù)據(jù)源

String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";         try (                 Connection conn = dataSource.getConnection();                 PreparedStatement preparedStatement = conn.prepareStatement(sql);                 ) {             preparedStatement.setInt(1, 10);             preparedStatement.setInt(2, 1001);             ResultSet rs = preparedStatement.executeQuery();             while (rs.next()) {                 System.out.println(rs.getInt(1));                 System.out.println(rs.getInt(2));                 System.out.println(rs.getInt(3));             }             rs.close();         } 

該數(shù)據(jù)源與普通數(shù)據(jù)源完全相同，你可以通過上例的API形式來使用，也可以將其配置在Spring，Hibernate等框架中使用。

如果希望不依賴于表中的列傳入分片鍵值，參考：基于暗示(Hint)的分片鍵值注冊方法