https://blog.csdn.net/wenyuan65/article/details/83715471?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-1.pc_relevant_paycolumn_v3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-1.pc_relevant_paycolumn_v3&utm_relevant_index=2

相比于Spring基于AbstractRoutingDataSource实现的分库分表功能，Sharding jdbc在单库单表扩展到多库多表时，兼容性方面表现的更好一点。例如，spring实现的分库分表sql写法如下：

 

  1.  
    select id, name, price, publish, intro 
     
  2.  
    from book${tableIndex}
     
  3.  
    where id = #{id,jdbcType=INTEGER}
     
 

sql中的表名book需要加一个分表的后缀tableIndex，也就是需要在sql注入的参数中指定插入哪个表。相比，Sharding jdbc在这一块封装的更好一点。其sql中，根本不需要指定tableIndex，而是根据分库分表策略自动路由。
 

 

  1.  
    select id, name, price, publish, intro
     
  2.  
    from book 
     
  3.  
    where id = #{id,jdbcType=INTEGER}
     
 

Sharding jdbc的这种特性，在水平扩展的时候无疑更具有吸引力。试想一下，一个项目开发一段时间后，单库单表数据量急剧上升，需要分库分表解决数据库的访问压力。而现有sql配置都是基于单库单表实现的，如果基于spring的AbstractRoutingDataSource实现，需要修改每一个相关表的sql，修改涉及较多地方，出错概率较大。而基于Sharding jdbc实现时，sql无需修改，只需要在spring中添加Sharding jdbc的相关配置即可，减少了修改面，大大简化分库分表的实现难度。
 

那么，Sharding jdbc是如何实现这种分库分表的逻辑呢？下面我们用一段简单、易懂的代码描述Sharding jdbc的原理。
 

通常我们在写一段访问数据库的数据时，逻辑是这样的：
 

 

  1.  
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("application.xml");
     
  2.  
    DataSource dataSource = ctx.getBean("dataSource", DataSource.class);
     
  3.  
    Connection connection = dataSource.getConnection();
     
  5.  
    String sql = "select id, name, price, publish, intro from book where id = 111";
     
  6.  
    PreparedStatement ps = connection.prepareStatement(sql);
     
  7.  
    ResultSet rs = ps.executeQuery();
     
  8.  
    // handle ResultSet...
     
 

Sharding jdbc是基于JDBC协议实现的，当我们获得dataSource时，这个dataSource是Sharding jdbc自己定义的一个SpringShardingDataSource类型的数据源，该数据源在返回getConnection()及prepareStatement()时，分别返回ShardingConnection和ShardingPreparedStatement的实例对象。然后在executeQuery()时，ShardingPreparedStatement做了这样的一件事：
 

  1. 根据逻辑sql，经过分库分表策略逻辑计算，获得分库分表的路由结果SQLRouteResult；
  2. SQLRouteResult中包含真实的数据源以及转换后的真正sql，利用真实的数据源去执行获得ResultSet；
  3. 将ResultSet列表封装成一个可以顺序读的ResultSet对象IteratorReducerResultSet。
 

 

  1.  
    class ShardingPreparedStatement implements PreparedStatement {
     
  3.  
    @Override
     
  4.  
    public ResultSet executeQuery() throws SQLException {
     
  5.  
    List<SQLRouteResult> routeResults = routeSql(logicSql);
     
  7.  
    List<ResultSet> resultSets = new ArrayList<>(routeResults.size());
     
  8.  
    for (SQLRouteResult routeResult : routeResults) {
     
  9.  
    PreparedStatement ps = routeResult.getDataSource().getConnection.prepareStatement(routeResult.getParsedSql());
     
  10.  
    ResultSet rs = ps.executeQuery();
     
  11.  
    resultSets.add(rs);
     
  12.  
    }
     
  14.  
    return new IteratorReducerResultSet(resultSets);
     
  15.  
    }
     
  16.  
    .....
     
  18.  
    }
     
 

其中，分库分表策略的sql路由过程，我们将Sharding jdbc中的相关代码全部抽出来，放到一起来观看这个过程的实现：
 

 

  1.  
    // 环境准备
     
  2.  
    @SuppressWarnings("resource")
     
  3.  
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("application.xml");
     
  4.  
    SpringShardingDataSource dataSource = ctx.getBean(SpringShardingDataSource.class);
     
  5.  
    Field field = SpringShardingDataSource.class.getSuperclass().getDeclaredField("shardingContext");
     
  6.  
    field.setAccessible(true);
     
  7.  
    ShardingContext sctx = (ShardingContext)field.get(dataSource);
     
  8.  
    ShardingRule shardingRule = sctx.getShardingRule();
     
  10.  
    String logicSql = "select id, name, price, publish, intro from book where id = ?";
     
  11.  
    List<Object> parameters = new ArrayList<>();
     
  12.  
    parameters.add(2000);
     
  14.  
    // sql解析
     
  15.  
    MySqlStatementParser parser = new MySqlStatementParser(logicSql);
     
  16.  
    MySQLSelectVisitor visitor = new MySQLSelectVisitor();
     
  17.  
    SQLStatement statement = parser.parseStatement();
     
  18.  
    visitor.getParseContext().setShardingRule(shardingRule);
     
  19.  
    statement.accept(visitor);
     
  21.  
    SQLParsedResult parsedResult = visitor.getParseContext().getParsedResult();
     
  22.  
    if (visitor.getParseContext().isHasOrCondition()) {
     
  23.  
    new OrParser(statement, visitor).fillConditionContext(parsedResult);
     
  24.  
    } 
     
  25.  
    visitor.getParseContext().mergeCurrentConditionContext();
     
  26.  
    System.out.println("Parsed SQL result: " + parsedResult);
     
  27.  
    System.out.println("Parsed SQL: " + visitor.getSQLBuilder());
     
  28.  
    parsedResult.getRouteContext().setSqlBuilder(visitor.getSQLBuilder());
     
  29.  
    parsedResult.getRouteContext().setSqlStatementType(SQLStatementType.SELECT);
     
  31.  
    // 分库分表路由
     
  32.  
    SQLRouteResult result = new SQLRouteResult(parsedResult.getRouteContext().getSqlStatementType(), parsedResult.getMergeContext(), parsedResult.getGeneratedKeyContext());
     
  33.  
    for (ConditionContext each : parsedResult.getConditionContexts()) {
     
  34.  
    Collection<Table> tables = parsedResult.getRouteContext().getTables();
     
  35.  
    final Set<String> logicTables = new HashSet<>();
     
  36.  
    tables.forEach(a -> logicTables.add(a.getName()));
     
  38.  
    SingleTableRouter router = new SingleTableRouter(shardingRule, 
     
  39.  
    logicTables.iterator().next(), 
     
  40.  
    each, 
     
  41.  
    parsedResult.getRouteContext().getSqlStatementType());
     
  43.  
    RoutingResult routingResult = router.route();
     
  45.  
    // sql改写 --> routingResult.getSQLExecutionUnits() 
     
  46.  
    // ---> SingleRoutingTableFactor.replaceSQL(sqlBuilder).buildSQL()
     
  47.  
    // 结果合并
     
  48.  
    result.getExecutionUnits().addAll(routingResult.getSQLExecutionUnits(parsedResult.getRouteContext().getSqlBuilder()));
     
  49.  
    }
     
  50.  
    // amendSQLAccordingToRouteResult(parsedResult, parameters, result);
     
  51.  
    for (SQLExecutionUnit each : result.getExecutionUnits()) {
     
  52.  
    System.out.println(each.getDataSource() + " " + each.getSql() + " " + parameters);
     
  53.  
    }
     
 

  1. 准备环境。由于Sharding jdbc分库分表中ShardingRule这个类是贯穿整个路由过程，我们在Spring中写好Sharding jdbc的配置，利用反射获取一个这个对象。（Sharding jdbc版本以及配置，在文章最后列出，方便debug这个过程）
  2. sql解析。Sharding jdbc使用阿里的Druid库解析sql。在这个过程中，Sharding jdbc实现了一个自己的sql解析内容缓存容器SqlBuilder。当语法分析中解析到一个表名的时候，在SqlBuilder中缓存一个sql相关的逻辑表名的token。并且，Sharding jdbc会将sql按照语义解析为多个segment。例如，"select id, name, price, publish, intro from book where id = ？"将解析为，"select id, name, price, publish, intro | from | book | where | id = ?"。
  3. 分库分表路由。根据ShardingRule中指定的分库分表列的参数值，以及分库分表策略，实行分库分表，得到一个RoutingResult 。RoutingResult 中包含一个真实数据源，以及逻辑表名和实际表名。
  4. sql改写。在SqlBuilder中，查找sql中解析的segment，将和逻辑表名一致的segment替换成实际表名。（segment中可以标注该地方是不是表名）
 

以上代码执行结果如下：
 

 

  1.  
    Parsed SQL result: SQLParsedResult(routeContext=RouteContext(tables=[Table(name=book, alias=Optional.absent())], sqlStatementType=null, sqlBuilder=null), generatedKeyContext=GeneratedKeyContext(columns=[], columnNameToIndexMap={}, valueTable={}, rowIndex=0, columnIndex=0, autoGeneratedKeys=0, columnIndexes=null, columnNames=null), conditionContexts=[ConditionContext(conditions={})], mergeContext=MergeContext(orderByColumns=[], groupByColumns=[], aggregationColumns=[], limit=null))
     
  2.  
    Parsed SQL: SELECT id, name, price, publish, intro FROM [Token(book)] WHERE id = ?
     
  3.  
    dataSource1 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
     
  4.  
    dataSource2 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]
     
  5.  
    dataSource1 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]
     
  6.  
    dataSource2 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
     
  7.  
    dataSource0 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
     
  8.  
    dataSource0 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
     
  9.  
    dataSource2 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
     
  10.  
    dataSource1 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
     
  11.  
    dataSource0 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]
     
 

实际上，我们可以用更通俗易懂的代码表示sql改写的这个过程：
 

 

  1.  
    String logicSql = "select id, name, price, publish, intro from book where id = 111";
     
  2.  
    MySqlStatementParser parser = new MySqlStatementParser(logicSql);
     
  3.  
    SQLStatement statement = parser.parseStatement();
     
  4.  
    MySQLSimpleVisitor visitor = new MySQLSimpleVisitor();
     
  5.  
    statement.accept(visitor);
     
  7.  
    String logicTable = "book";
     
  8.  
    String realTable = "book_00";
     
  9.  
    String token = "\\$\\{" + logicTable + "\\}";
     
  11.  
    String sqlBuilder = visitor.getAppender().toString();
     
  12.  
    String sql = sqlBuilder.replaceAll(token, realTable);
     
  14.  
    System.out.println(sqlBuilder);
     
  15.  
    System.out.println(sql);
     
 

MySQLSimpleVisitor代码如下：
 

 

  1.  
    public class MySQLSimpleVisitor extends MySqlOutputVisitor {
     
  3.  
    public MySQLSimpleVisitor() {
     
  4.  
    super(new StringBuilder());
     
  5.  
    }
     
  7.  
    @Override
     
  8.  
    public boolean visit(SQLExprTableSource x) {
     
  9.  
    StringBuilder sb = new StringBuilder();
     
  10.  
    sb.append("${");
     
  11.  
    sb.append(x.getExpr().toString()).append('}');
     
  12.  
    print(sb.toString());
     
  14.  
    if (x.getAlias() != null) {
     
  15.  
    print(' ');
     
  16.  
    print(x.getAlias());
     
  17.  
    }
     
  19.  
    for (int i = 0; i < x.getHintsSize(); ++i) {
     
  20.  
    print(' ');
     
  21.  
    x.getHints().get(i).accept(this);
     
  22.  
    }
     
  24.  
    return false;
     
  25.  
    }
     
  27.  
    }
     
 

结果如下：
 

 

  1.  
    SELECT id, name, price, publish, intro
     
  2.  
    FROM ${book}
     
  3.  
    WHERE id = 111
     
  4.  
    SELECT id, name, price, publish, intro
     
  5.  
    FROM book_00
     
  6.  
    WHERE id = 111
     
 

以上，大致将Sharding jdbc的原理及实现过程介绍了一下，如果想要了解正真的实现过程和细节，还需要对照代码仔细推敲。
 

本文的实现环境：
 

 

  1.  
    <dependency>
     
  2.  
                <groupId>com.dangdang</groupId>
     
  3.  
                <artifactId>sharding-jdbc-core</artifactId>
     
  4.  
                <version>1.4.2</version>
     
  5.  
            </dependency>
     
  6.  
            <dependency>
     
  7.  
                <groupId>com.dangdang</groupId>
     
  8.  
                <artifactId>sharding-jdbc-config-spring</artifactId>
     
  9.  
                <version>1.4.0</version>
     
  10.  
            </dependency>
     
 

application.xml
 

 

  1.  
    <?xml version="1.0" encoding="UTF-8"?>
     
  2.  
    <beans xmlns="http://www.springframework.org/schema/beans"
     
  3.  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     
  4.  
    xmlns:tx="http://www.springframework.org/schema/tx"
     
  5.  
    xmlns:context="http://www.springframework.org/schema/context"
     
  6.  
    xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"
     
  7.  
    xsi:schemaLocation="
     
  8.  
    http://www.springframework.org/schema/beans 
     
  9.  
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
     
  10.  
    http://www.springframework.org/schema/context 
     
  11.  
    http://www.springframework.org/schema/context/spring-context-4.0.xsd 
     
  12.  
    http://www.springframework.org/schema/tx 
     
  13.  
    http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
     
  14.  
    http://www.dangdang.com/schema/ddframe/rdb 
     
  15.  
    http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
     
  17.  
    <context:property-placeholder location="classpath:jdbc.properties" ignore-unresolvable="true" />
     
  19.  
    <bean id="dataSource0" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     
  20.  
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
     
  21.  
    <property name="url" value="${jdbc.mysql.url0}" />
     
  22.  
    <property name="username" value="${jdbc.mysql.username0}" />
     
  23.  
    <property name="password" value="${jdbc.mysql.password0}" />
     
  24.  
    </bean>
     
  26.  
    <bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     
  27.  
    <property name="driverClassName" value="${driver}" />
     
  28.  
    <property name="url" value="${jdbc.mysql.url1}" />
     
  29.  
    <property name="username" value="${jdbc.mysql.username1}" />
     
  30.  
    <property name="password" value="${jdbc.mysql.password1}" />
     
  31.  
    </bean>
     
  33.  
    <bean id="dataSource2" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
     
  34.  
    <property name="driverClassName" value="${driver}" />
     
  35.  
    <property name="url" value="${jdbc.mysql.url2}" />
     
  36.  
    <property name="username" value="${jdbc.mysql.username2}" />
     
  37.  
    <property name="password" value="${jdbc.mysql.password2}" />
     
  38.  
    </bean>
     
  40.  
    <!-- sharding jdbc -->
     
  41.  
    <rdb:strategy id="tableShardingStrategy" sharding-columns="id" 
     
  42.  
    algorithm-class="com.wy.sharding.MemberSingleKeyTableShardingAlgorithm" />
     
  44.  
    <rdb:data-source id="shardingDataSource">
     
  45.  
    <rdb:sharding-rule data-sources="dataSource0,dataSource1,dataSource2">
     
  46.  
    <rdb:table-rules>
     
  47.  
    <rdb:table-rule logic-table="book" 
     
  48.  
    actual-tables="book_0${0..2}" 
     
  49.  
    table-strategy="tableShardingStrategy"/>
     
  50.  
    </rdb:table-rules>
     
  51.  
    </rdb:sharding-rule>
     
  52.  
    </rdb:data-source>
     
  53.  
    </beans>
     
 

MemberSingleKeyTableShardingAlgorithm.java
 

 

  1.  
    public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {
     
  3.  
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
     
  4.  
    String routeDBSuffix = getRouteDBSuffix(shardingValue.getValue());
     
  5.  
    for (String each : availableTargetNames) {
     
  6.  
    if (each.endsWith(routeDBSuffix)) {
     
  7.  
    return each;
     
  8.  
    }
     
  9.  
    }
     
  10.  
    throw new IllegalArgumentException();
     
  11.  
    }
     
  13.  
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
     
  14.  
    Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
     
  15.  
    for (int value : shardingValue.getValues()) {
     
  16.  
    String routeDBSuffix = getRouteDBSuffix(value);
     
  17.  
    for (String tableName : availableTargetNames) {
     
  18.  
    if (tableName.endsWith(routeDBSuffix)) {
     
  19.  
    result.add(tableName);
     
  20.  
    }
     
  21.  
    }
     
  22.  
    }
     
  23.  
    return result;
     
  24.  
    }
     
  26.  
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
     
  27.  
    ShardingValue<Integer> shardingValue) {
     
  28.  
    Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
     
  29.  
    Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
     
  30.  
    for (int i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
     
  31.  
    String routeDBSuffix = getRouteDBSuffix(i);
     
  32.  
    for (String each : availableTargetNames) {
     
  33.  
    if (each.endsWith(routeDBSuffix)) {
     
  34.  
    result.add(each);
     
  35.  
    }
     
  36.  
    }
     
  37.  
    }
     
  38.  
    return result;
     
  39.  
    }
     
  41.  
    public String getRouteDBSuffix(Integer shardingCode) {
     
  42.  
    int modValue = shardingCode % 3;
     
  43.  
    return "0" + modValue;
     
  44.  
    }
     
  46.  
    }
     
 

 
  

        # 数据库
      
 
 
 Logo 
华为云开发者联盟
 
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
 
 
  
更多推荐
  
cover
 
解锁HDC 2024之旅：从购票到报名，全程攻略
 
 
avatar 华为云开发者联盟
 
cover
 
从原始边列表到邻接矩阵Python实现图数据处理的完整指南
 
 
avatar 华为云开发者联盟
 
cover
 
华为云云原生FinOps解决方案，释放云原生最大价值
 
 
avatar 华为云开发者联盟