mysql分库分表sharding-jdbc

叁歲伎倆 2022-05-25 02:20 174阅读 0赞

前一段时间研究阿里的分库框架cobar-client，cobar-client是基于ibatis的SqlMapClientTemplate进行了一层薄薄的封装，分装成CobarSqlMapClientTemplate，在用户在CRUD的时候可以透明的进行操作，算是现在大多公司分库的一个成熟解决方案，不过现在面临的一些问题：

①不支持分表

②基于ibatis而且2013年后基本维护了，没有进行升级，所以大多公司都基于该思想进行了自己的重写

来看下当当开源的sharding-jdbc，官方网址：[点击打开链接][Link 1]

先允许我盗一波图：

![Center][]

![Center 1][]

好了，看了这么多的介绍，感觉还是很高大上的，注意点有：

①对JDBC API进行了原生态的分装，这是与cobar-client不一样的地方，这就是他可以支持多个第三方ORM框架的关键

②可支持`=`，`BETWEEN`，`IN`等操作，说明，JDBC返回结果后，sharding进行了合并操作，这里面肯定会有性能损耗

③支持分表，这也是cobar-client不支持的地方

好了，先简单的按照官方网址的demo实践一发：

先在[MySQL][]中建2个库

![Center 2][]

分别在这2个库中运行：

\[sql\]  [view plain][]   [copy][view plain]

1.  **CREATE** **TABLE** IF NOT EXISTS \`t\_order\_0\` (  
2.    \`order\_id\` **INT** NOT NULL,  
3.    \`user\_id\`  **INT** NOT NULL,  
4.    **PRIMARY** **KEY** (\`order\_id\`)  
5.  );  
6.  **CREATE** **TABLE** IF NOT EXISTS \`t\_order\_item\_0\` (  
7.    \`item\_id\`  **INT** NOT NULL,  
8.    \`order\_id\` **INT** NOT NULL,  
9.    \`user\_id\`  **INT** NOT NULL,  
10.   **PRIMARY** **KEY** (\`item\_id\`)  
11. );  
12. **CREATE** **TABLE** IF NOT EXISTS \`t\_order\_1\` (  
13.   \`order\_id\` **INT** NOT NULL,  
14.   \`user\_id\`  **INT** NOT NULL,  
15.   **PRIMARY** **KEY** (\`order\_id\`)  
16. );  
17. **CREATE** **TABLE** IF NOT EXISTS \`t\_order\_item\_1\` (  
18.   \`item\_id\`  **INT** NOT NULL,  
19.   \`order\_id\` **INT** NOT NULL,  
20.   \`user\_id\`  **INT** NOT NULL,  
21.   **PRIMARY** **KEY** (\`item\_id\`)  
22. );

新建maven项目

![Center 3][]

Maven依赖的pom.xml

\[html\]  [view plain][]   [copy][view plain]

1.  **<****project** xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
2.      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"**>**  
3.      **<****modelVersion****>**4.0.0**</****modelVersion****>**  
4.    
5.      **<****groupId****>**org.study**</****groupId****>**  
6.      **<****artifactId****>**sharding-jdbc**</****artifactId****>**  
7.      **<****version****>**0.0.1-SNAPSHOT**</****version****>**  
8.      **<****packaging****>**jar**</****packaging****>**  
9.    
10.     **<****name****>**sharding-jdbc**</****name****>**  
11.     **<****url****>**http://maven.apache.org**</****url****>**  
12.   
13.     **<****properties****>**  
14.         **<****project.build.sourceEncoding****>**UTF-8**</****project.build.sourceEncoding****>**  
15.         **<****spring.version****>**3.2.5.RELEASE**</****spring.version****>**  
16.         **<****mybatis.version****>**3.2.4**</****mybatis.version****>**  
17.     **</****properties****>**  
18.   
19.     **<****dependencies****>**  
20.         **<****dependency****>**  
21.             **<****groupId****>**junit**</****groupId****>**  
22.             **<****artifactId****>**junit**</****artifactId****>**  
23.             **<****version****>**4.10**</****version****>**  
24.         **</****dependency****>**  
25.         **<****dependency****>**  
26.             **<****groupId****>**com.dangdang**</****groupId****>**  
27.             **<****artifactId****>**sharding-jdbc-core**</****artifactId****>**  
28.             **<****version****>**1.0.0**</****version****>**  
29.         **</****dependency****>**  
30.         **<****dependency****>**  
31.             **<****groupId****>**org.springframework**</****groupId****>**  
32.             **<****artifactId****>**spring-orm**</****artifactId****>**  
33.             **<****version****>**$\{spring.version\}**</****version****>**  
34.         **</****dependency****>**  
35.         **<****dependency****>**  
36.             **<****groupId****>**commons-dbcp**</****groupId****>**  
37.             **<****artifactId****>**commons-dbcp**</****artifactId****>**  
38.             **<****version****>**1.4**</****version****>**  
39.         **</****dependency****>**  
40.         **<****dependency****>**  
41.             **<****groupId****>**org.mybatis**</****groupId****>**  
42.             **<****artifactId****>**mybatis-spring**</****artifactId****>**  
43.             **<****version****>**1.2.2**</****version****>**  
44.         **</****dependency****>**  
45.         **<****dependency****>**  
46.             **<****groupId****>**org.mybatis**</****groupId****>**  
47.             **<****artifactId****>**mybatis**</****artifactId****>**  
48.             **<****version****>**$\{mybatis.version\}**</****version****>**  
49.         **</****dependency****>**  
50.   
51.         **<****dependency****>**  
52.             **<****groupId****>**org.springframework**</****groupId****>**  
53.             **<****artifactId****>**spring-expression**</****artifactId****>**  
54.             **<****version****>**$\{spring.version\}**</****version****>**  
55.         **</****dependency****>**  
56.         **<****dependency****>**  
57.             **<****groupId****>**org.springframework**</****groupId****>**  
58.             **<****artifactId****>**spring-aop**</****artifactId****>**  
59.             **<****version****>**$\{spring.version\}**</****version****>**  
60.         **</****dependency****>**  
61.         **<****dependency****>**  
62.             **<****groupId****>**org.springframework**</****groupId****>**  
63.             **<****artifactId****>**spring-beans**</****artifactId****>**  
64.             **<****version****>**$\{spring.version\}**</****version****>**  
65.         **</****dependency****>**  
66.         **<****dependency****>**  
67.             **<****groupId****>**org.springframework**</****groupId****>**  
68.             **<****artifactId****>**spring-context**</****artifactId****>**  
69.             **<****version****>**$\{spring.version\}**</****version****>**  
70.         **</****dependency****>**  
71.         **<****dependency****>**  
72.             **<****groupId****>**org.springframework**</****groupId****>**  
73.             **<****artifactId****>**spring-context-support**</****artifactId****>**  
74.             **<****version****>**$\{spring.version\}**</****version****>**  
75.         **</****dependency****>**  
76.         **<****dependency****>**  
77.             **<****groupId****>**org.springframework**</****groupId****>**  
78.             **<****artifactId****>**spring-test**</****artifactId****>**  
79.             **<****version****>**$\{spring.version\}**</****version****>**  
80.         **</****dependency****>**  
81.         **<****dependency****>**  
82.             **<****groupId****>**org.springframework**</****groupId****>**  
83.             **<****artifactId****>**spring-tx**</****artifactId****>**  
84.             **<****version****>**$\{spring.version\}**</****version****>**  
85.         **</****dependency****>**  
86.         **<****dependency****>**  
87.             **<****groupId****>**mysql**</****groupId****>**  
88.             **<****artifactId****>**mysql-connector-java**</****artifactId****>**  
89.             **<****version****>**5.1.28**</****version****>**  
90.         **</****dependency****>**  
91.         **<****dependency****>**  
92.             **<****groupId****>**log4j**</****groupId****>**  
93.             **<****artifactId****>**log4j**</****artifactId****>**  
94.             **<****version****>**1.2.16**</****version****>**  
95.         **</****dependency****>**  
96.         **<****dependency****>**  
97.             **<****groupId****>**org.slf4j**</****groupId****>**  
98.             **<****artifactId****>**slf4j-log4j12**</****artifactId****>**  
99.             **<****version****>**1.7.5**</****version****>**  
100.         **</****dependency****>**  
101.     **</****dependencies****>**  
102. **</****project****>**

ShardingJdbc

\[java\]  [view plain][]   [copy][view plain]

1.  **package** com.study.base;  
2.    
3.  **import** java.sql.Connection;  
4.  **import** java.sql.PreparedStatement;  
5.  **import** java.sql.ResultSet;  
6.  **import** java.sql.SQLException;  
7.  **import** java.util.Arrays;  
8.  **import** java.util.HashMap;  
9.  **import** java.util.Map;  
10.   
11. **import** javax.sql.DataSource;  
12.   
13. **import** org.apache.commons.dbcp.BasicDataSource;  
14.   
15. **import** com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource;  
16. **import** com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;  
17. **import** com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;  
18. **import** com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;  
19. **import** com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;  
20. **import** com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;  
21. **import** com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;  
22.   
23. **public** **class** ShardingJdbc \{  
24.       
25.     **public** **static** **void** main(String\[\] args) **throws** SQLException \{  
26.           
27.         //数据源  
28.         Map<String, DataSource> dataSourceMap = **new** HashMap<>(2);  
29.         dataSourceMap.put("sharding\_0", createDataSource("sharding\_0"));  
30.         dataSourceMap.put("sharding\_1", createDataSource("sharding\_1"));  
31.           
32.         DataSourceRule dataSourceRule = **new** DataSourceRule(dataSourceMap);  
33.           
34.         //分表分库的表，第一个参数是逻辑表名，第二个是实际表名，第三个是实际库  
35.         TableRule orderTableRule = **new** TableRule("t\_order", Arrays.asList("t\_order\_0", "t\_order\_1"), dataSourceRule);  
36.         TableRule orderItemTableRule = **new** TableRule("t\_order\_item", Arrays.asList("t\_order\_item\_0", "t\_order\_item\_1"), dataSourceRule);  
37.           
38.           
39.           
40.         /\*\* 
41.          \* DatabaseShardingStrategy 分库策略 
42.          \* 参数一：根据哪个字段分库 
43.          \* 参数二：分库路由函数 
44.          \* TableShardingStrategy 分表策略 
45.          \* 参数一：根据哪个字段分表 
46.          \* 参数二：分表路由函数 
47.          \*  
48.          \*/  
49.         ShardingRule shardingRule = **new** ShardingRule(dataSourceRule, Arrays.asList(orderTableRule, orderItemTableRule),  
50.                 Arrays.asList(**new** BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))),  
51.                 **new** DatabaseShardingStrategy("user\_id", **new** ModuloDatabaseShardingAlgorithm()),  
52.                 **new** TableShardingStrategy("order\_id", **new** ModuloTableShardingAlgorithm()));  
53.           
54.           
55.         DataSource dataSource = **new** ShardingDataSource(shardingRule);  
56.         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=?";  
57.         **try** (  
58.                 Connection conn = dataSource.getConnection();  
59.                 PreparedStatement pstmt = conn.prepareStatement(sql)) \{  
60.                 pstmt.setInt(1, 10);  
61.                 pstmt.setInt(2, 1001);  
62.             **try** (ResultSet rs = pstmt.executeQuery()) \{  
63.                 **while**(rs.next()) \{  
64.                     System.out.println(rs.getInt(1));  
65.                     System.out.println(rs.getInt(2));  
66.                     System.out.println(rs.getInt(3));  
67.                 \}  
68.             \}  
69.         \}  
70.     \}  
71.   
72.     /\*\* 
73.      \* 创建数据源 
74.      \* @param dataSourceName 
75.      \* @return 
76.      \*/  
77.     **private** **static** DataSource createDataSource(String dataSourceName) \{  
78.         BasicDataSource result = **new** BasicDataSource();  
79.         result.setDriverClassName(com.mysql.jdbc.Driver.**class**.getName());  
80.         result.setUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName));  
81.         result.setUsername("root");  
82.         result.setPassword("");  
83.         **return** result;  
84.     \}  
85.   
86. \}

ModuloDatabaseShardingAlgorithm

\[java\]  [view plain][]   [copy][view plain]

1.  **package** com.study.base;  
2.    
3.  **import** java.util.Collection;  
4.  **import** java.util.LinkedHashSet;  
5.    
6.  **import** com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
7.  **import** com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;  
8.  **import** com.google.common.collect.Range;  
9.    
10. /\*\* 
11.  \*  
12.  \* @author lyncc 
13.  \* 
14.  \*/  
15. **public** **class** ModuloDatabaseShardingAlgorithm **implements** SingleKeyDatabaseShardingAlgorithm<Integer>\{  
16.   
17.     @Override  
18.     **public** String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) \{  
19.         **for** (String each : availableTargetNames) \{  
20.             **if** (each.endsWith(shardingValue.getValue() % 2 + "")) \{  
21.                 **return** each;  
22.             \}  
23.         \}  
24.         **throw** **new** IllegalArgumentException();  
25.     \}  
26.   
27.     @Override  
28.     **public** Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) \{  
29.         Collection<String> result = **new** LinkedHashSet<>(availableTargetNames.size());  
30.         **for** (Integer value : shardingValue.getValues()) \{  
31.             **for** (String tableName : availableTargetNames) \{  
32.                 **if** (tableName.endsWith(value % 2 + "")) \{  
33.                     result.add(tableName);  
34.                 \}  
35.             \}  
36.         \}  
37.         **return** result;  
38.     \}  
39.   
40.     @Override  
41.     **public** Collection<String> doBetweenSharding(Collection<String> availableTargetNames,  
42.             ShardingValue<Integer> shardingValue) \{  
43.         Collection<String> result = **new** LinkedHashSet<>(availableTargetNames.size());  
44.         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
45.         **for** (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) \{  
46.             **for** (String each : availableTargetNames) \{  
47.                 **if** (each.endsWith(i % 2 + "")) \{  
48.                     result.add(each);  
49.                 \}  
50.             \}  
51.         \}  
52.         **return** result;  
53.     \}  
54.   
55. \}

ModuloTableShardingAlgorithm.java

\[java\]  [view plain][]   [copy][view plain]

1.  **package** com.study.base;  
2.  **import** java.util.Collection;  
3.  **import** java.util.LinkedHashSet;  
4.    
5.  **import** com.dangdang.ddframe.rdb.sharding.api.ShardingValue;  
6.  **import** com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;  
7.  **import** com.google.common.collect.Range;  
8.    
9.  **public** **final** **class** ModuloTableShardingAlgorithm **implements** SingleKeyTableShardingAlgorithm<Integer> \{  
10.       
11.     /\*\* 
12.     \*  select \* from t\_order from t\_order where order\_id = 11  
13.     \*          └── SELECT \*  FROM t\_order\_1 WHERE order\_id = 11 
14.     \*  select \* from t\_order from t\_order where order\_id = 44 
15.     \*          └── SELECT \*  FROM t\_order\_0 WHERE order\_id = 44 
16.     \*/  
17.     **public** String doEqualSharding(**final** Collection<String> tableNames, **final** ShardingValue<Integer> shardingValue) \{  
18.         **for** (String each : tableNames) \{  
19.             **if** (each.endsWith(shardingValue.getValue() % 2 + "")) \{  
20.                 **return** each;  
21.             \}  
22.         \}  
23.         **throw** **new** IllegalArgumentException();  
24.     \}  
25.       
26.     /\*\* 
27.     \*  select \* from t\_order from t\_order where order\_id in (11,44)   
28.     \*          ├── SELECT \*  FROM t\_order\_0 WHERE order\_id IN (11,44)  
29.     \*          └── SELECT \*  FROM t\_order\_1 WHERE order\_id IN (11,44)  
30.     \*  select \* from t\_order from t\_order where order\_id in (11,13,15)   
31.     \*          └── SELECT \*  FROM t\_order\_1 WHERE order\_id IN (11,13,15)   
32.     \*  select \* from t\_order from t\_order where order\_id in (22,24,26)   
33.     \*          └──SELECT \*  FROM t\_order\_0 WHERE order\_id IN (22,24,26)  
34.     \*/  
35.     **public** Collection<String> doInSharding(**final** Collection<String> tableNames, **final** ShardingValue<Integer> shardingValue) \{  
36.         Collection<String> result = **new** LinkedHashSet<>(tableNames.size());  
37.         **for** (Integer value : shardingValue.getValues()) \{  
38.             **for** (String tableName : tableNames) \{  
39.                 **if** (tableName.endsWith(value % 2 + "")) \{  
40.                     result.add(tableName);  
41.                 \}  
42.             \}  
43.         \}  
44.         **return** result;  
45.     \}  
46.       
47.     /\*\* 
48.     \*  select \* from t\_order from t\_order where order\_id between 10 and 20  
49.     \*          ├── SELECT \*  FROM t\_order\_0 WHERE order\_id BETWEEN 10 AND 20  
50.     \*          └── SELECT \*  FROM t\_order\_1 WHERE order\_id BETWEEN 10 AND 20  
51.     \*/  
52.     **public** Collection<String> doBetweenSharding(**final** Collection<String> tableNames, **final** ShardingValue<Integer> shardingValue) \{  
53.         Collection<String> result = **new** LinkedHashSet<>(tableNames.size());  
54.         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();  
55.         **for** (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) \{  
56.             **for** (String each : tableNames) \{  
57.                 **if** (each.endsWith(i % 2 + "")) \{  
58.                     result.add(each);  
59.                 \}  
60.             \}  
61.         \}  
62.         **return** result;  
63.     \}  
64. \}

log4j.xml

\[html\]  [view plain][]   [copy][view plain]

1.  **<?****xml** version="1.0" encoding="UTF-8"**?>**      
2.  <!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd"**>**    
3.  **<****log4j:configuration** xmlns:log4j="http://jakarta.apache.org/log4j/"**>**    
4.        
5.    **<****appender** name="console" class="org.apache.log4j.ConsoleAppender"**>**    
6.       **<****param** name="encoding" value="GBK" **/>**    
7.       **<****param** name="target" value="System.out" **/>**    
8.       **<****layout** class="org.apache.log4j.PatternLayout"**>**    
9.         **<****param** name="ConversionPattern" value="%-5p %c\{2\} - %m%n" **/>**    
10.      **</****layout****>**    
11.   **</****appender****>**    
12.     
13.       
14.   **<****appender** name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender"**>**    
15.      **<****param** name="File" value="$\{webapp.root\}/logs/common-default.log" **/>**    
16.      **<****param** name="Append" value="true" **/>**    
17.      **<****param** name="encoding" value="GBK" **/>**    
18.      **<****param** name="DatePattern" value="'.'yyyy-MM-dd'.log'" **/>**    
19.      **<****layout** class="org.apache.log4j.PatternLayout"**>**    
20.     **<****param** name="ConversionPattern" value="%d %-5p %c\{2\} - %m%n" **/>**    
21.      **</****layout****>**    
22.    **</****appender****>**    
23.     
24.        
25.    **<****appender** name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender"**>**    
26.      **<****param** name="File" value="$\{webapp.root\}/logs/common-error.log" **/>**    
27.      **<****param** name="Append" value="true" **/>**    
28.      **<****param** name="encoding" value="GBK" **/>**    
29.      **<****param** name="threshold" value="error" **/>**    
30.      **<****param** name="DatePattern" value="'.'yyyy-MM-dd'.log'" **/>**    
31.      **<****layout** class="org.apache.log4j.PatternLayout"**>**    
32.         **<****param** name="ConversionPattern" value="%d %-5p %c\{2\} - %m%n" **/>**    
33.      **</****layout****>**    
34.    **</****appender****>**    
35.     
36.        
37.    **<****appender** name="COMPONENT-APPENDER"    
38. class="org.apache.log4j.DailyRollingFileAppender"**>**    
39.      **<****param** name="File" value="$\{webapp.root\}/logs/logistics-component.log" **/>**    
40.      **<****param** name="Append" value="true" **/>**    
41.      **<****param** name="encoding" value="GBK" **/>**    
42.      **<****param** name="DatePattern" value="'.'yyyy-MM-dd'.log'" **/>**    
43.      **<****layout** class="org.apache.log4j.PatternLayout"**>**    
44.     **<****param** name="ConversionPattern" value="%d %-5p %c\{2\} - %m%n" **/>**    
45.      **</****layout****>**    
46.    **</****appender****>**    
47.     
48.        
49.    **<****logger** name="LOGISTICS-COMPONENT"**>**    
50.       **<****level** value="$\{loggingLevel\}" **/>**    
51.       **<****appender-ref** ref="COMPONENT-APPENDER" **/>**    
52.       **<****appender-ref** ref="ERROR-APPENDER" **/>**    
53.    **</****logger****>**    
54.     
55.        
56.    **<****root****>**    
57.        **<****level** value="$\{rootLevel\}"**>****</****level****>**    
58.        **<****appender-ref** ref="DEFAULT-APPENDER" **/>**    
59.        **<****appender-ref** ref="ERROR-APPENDER" **/>**    
60.        **<****appender-ref** ref="console" **/>**   
61.        **<****appender-ref** ref="COMPONENT-APPENDER" **/>**   
62.    **</****root****>**    
63. **</****log4j:configuration****>**

好了，按照官方教程说明：

![Center 4][]

我们现在user\_id是10，order\_id是1001

我们应该在sharding0库中的t\_order\_1和t\_order\_item\_1中新建数据：

\[java\]  [view plain][]   [copy][view plain]

1.  INSERT INTO \`t\_order\_1\` VALUES ('1001', '10');  
2.    
3.  INSERT INTO \`t\_order\_item\_1\` VALUES ('4', '1001', '2');

好了，准备工作做完了，我们运行main函数，运行结果为：

![Center 5][]

好了，sharding-jdbc正常工作了

[Link 1]: https://github.com/dangdangdotcom/sharding-jdbc
[Center]: /images/20220525/680449a7dd6b4670a3ea4923bb122075.png
[Center 1]: /images/20220525/e9f7e42cd48e49d3a0eb57a71dda68d2.png
[MySQL]: http://lib.csdn.net/base/mysql
[Center 2]: /images/20220525/74d718ac94da44cba6fa320365c71f80.png
[view plain]: http://blog.csdn.net/linuu/article/details/50929635#
[Center 3]: /images/20220525/35efb114db794b1089001e4c85efe6d1.png
[Center 4]: /images/20220525/ece0f0b653c1406cb3eda43dd45ecd13.png
[Center 5]: /images/20220525/fbf3d0fb6e2f421cb14704fff6c9a61a.png