mysql jdbc 原理_理解Sharding jdbc原理

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

select id, name, price, publish, intro

from book${tableIndex}

where id = #{id,jdbcType=INTEGER}

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

select id, name, price, publish, intro

from book

where id = #{id,jdbcType=INTEGER}

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

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

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

ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(“application.xml”);

DataSource dataSource = ctx.getBean(“dataSource”, DataSource.class);

Connection connection = dataSource.getConnection();

String sql = “select id, name, price, publish, intro from book where id = 111″;

PreparedStatement ps = connection.prepareStatement(sql);

ResultSet rs = ps.executeQuery();

// handle ResultSet…

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

根据逻辑sql，经过分库分表策略逻辑计算，获得分库分表的路由结果SQLRouteResult；

SQLRouteResult中包含真实的数据源以及转换后的真正sql，利用真实的数据源去执行获得ResultSet；

将ResultSet列表封装成一个可以顺序读的ResultSet对象IteratorReducerResultSet。

class ShardingPreparedStatement implements PreparedStatement {

@Override

public ResultSet executeQuery() throws SQLException {

List routeResults = routeSql(logicSql);

List resultSets = new ArrayList<>(routeResults.size());

for (SQLRouteResult routeResult : routeResults) {

PreparedStatement ps = routeResult.getDataSource().getConnection.prepareStatement(routeResult.getParsedSql());

ResultSet rs = ps.executeQuery();

resultSets.add(rs);

}

return new IteratorReducerResultSet(resultSets);

}

…..

}

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

// 环境准备

@SuppressWarnings(“resource”)

ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(“application.xml”);

SpringShardingDataSource dataSource = ctx.getBean(SpringShardingDataSource.class);

Field field = SpringShardingDataSource.class.getSuperclass().getDeclaredField(“shardingContext”);

field.setAccessible(true);

ShardingContext sctx = (ShardingContext)field.get(dataSource);

ShardingRule shardingRule = sctx.getShardingRule();

String logicSql = “select id, name, price, publish, intro from book where id = ?”;

List parameters = new ArrayList<>();

parameters.add(2000);

// sql解析

MySqlStatementParser parser = new MySqlStatementParser(logicSql);

MySQLSelectVisitor visitor = new MySQLSelectVisitor();

SQLStatement statement = parser.parseStatement();

visitor.getParseContext().setShardingRule(shardingRule);

statement.accept(visitor);

SQLParsedResult parsedResult = visitor.getParseContext().getParsedResult();

if (visitor.getParseContext().isHasOrCondition()) {

new OrParser(statement, visitor).fillConditionContext(parsedResult);

}

visitor.getParseContext().mergeCurrentConditionContext();

System.out.println(“Parsed SQL result: ” + parsedResult);

System.out.println(“Parsed SQL: ” + visitor.getSQLBuilder());

parsedResult.getRouteContext().setSqlBuilder(visitor.getSQLBuilder());

parsedResult.getRouteContext().setSqlStatementType(SQLStatementType.SELECT);

// 分库分表路由

SQLRouteResult result = new SQLRouteResult(parsedResult.getRouteContext().getSqlStatementType(), parsedResult.getMergeContext(), parsedResult.getGeneratedKeyContext());

for (ConditionContext each : parsedResult.getConditionContexts()) {

Collection

final Set logicTables = new HashSet<>();

tables.forEach(a -> logicTables.add(a.getName()));

SingleTableRouter router = new SingleTableRouter(shardingRule,

logicTables.iterator().next(),

each,

parsedResult.getRouteContext().getSqlStatementType());

RoutingResult routingResult = router.route();

// sql改写 –> routingResult.getSQLExecutionUnits()

// —> SingleRoutingTableFactor.replaceSQL(sqlBuilder).buildSQL()

// 结果合并

result.getExecutionUnits().addAll(routingResult.getSQLExecutionUnits(parsedResult.getRouteContext().getSqlBuilder()));

}

// amendSQLAccordingToRouteResult(parsedResult, parameters, result);

for (SQLExecutionUnit each : result.getExecutionUnits()) {

System.out.println(each.getDataSource() + ” ” + each.getSql() + ” ” + parameters);

}

准备环境。由于Sharding jdbc分库分表中ShardingRule这个类是贯穿整个路由过程，我们在Spring中写好Sharding jdbc的配置，利用反射获取一个这个对象。(Sharding jdbc版本以及配置，在文章最后列出，方便debug这个过程)

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 = ?”。

分库分表路由。根据ShardingRule中指定的分库分表列的参数值，以及分库分表策略，实行分库分表，得到一个RoutingResult 。RoutingResult 中包含一个真实数据源，以及逻辑表名和实际表名。

sql改写。在SqlBuilder中，查找sql中解析的segment，将和逻辑表名一致的segment替换成实际表名。(segment中可以标注该地方是不是表名)

以上代码执行结果如下：

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))

Parsed SQL: SELECT id, name, price, publish, intro FROM [Token(book)] WHERE id = ?

dataSource1 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]

dataSource2 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]

dataSource1 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]

dataSource2 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]

dataSource0 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]

dataSource0 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]

dataSource2 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]

dataSource1 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]

dataSource0 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]

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

String logicSql = “select id, name, price, publish, intro from book where id = 111″;

MySqlStatementParser parser = new MySqlStatementParser(logicSql);

SQLStatement statement = parser.parseStatement();

MySQLSimpleVisitor visitor = new MySQLSimpleVisitor();

statement.accept(visitor);

String logicTable = “book”;

String realTable = “book_00″;

String token = “\\$\\{” + logicTable + “\\}”;

String sqlBuilder = visitor.getAppender().toString();

String sql = sqlBuilder.replaceAll(token, realTable);

System.out.println(sqlBuilder);

System.out.println(sql);

结果如下：

SELECT id, name, price, publish, intro

FROM ${book}

WHERE id = 111

SELECT id, name, price, publish, intro

FROM book_00

WHERE id = 111

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

本文的实现环境：

com.dangdang

sharding-jdbc-core

1.4.2

com.dangdang

sharding-jdbc-config-spring

1.4.0

application.xml:

xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

xmlns:tx=”http://www.springframework.org/schema/tx”

xmlns:context=”http://www.springframework.org/schema/context”

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-4.0.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context-4.0.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-4.0.xsd

http://www.dangdang.com/schema/ddframe/rdb

http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd”>

algorithm-class=”com.wy.sharding.MemberSingleKeyTableShardingAlgorithm” />

actual-tables=”book_0${0..2}”

table-strategy=”tableShardingStrategy”/>

MemberSingleKeyTableShardingAlgorithm.java:

public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm {

public String doEqualSharding(Collection availableTargetNames, ShardingValue shardingValue) {

String routeDBSuffix = getRouteDBSuffix(shardingValue.getValue());

for (String each : availableTargetNames) {

if (each.endsWith(routeDBSuffix)) {

return each;

}

}

throw new IllegalArgumentException();

}

public Collection doInSharding(Collection availableTargetNames, ShardingValue shardingValue) {

Collection result = new LinkedHashSet(availableTargetNames.size());

for (int value : shardingValue.getValues()) {

String routeDBSuffix = getRouteDBSuffix(value);

for (String tableName : availableTargetNames) {

if (tableName.endsWith(routeDBSuffix)) {

result.add(tableName);

}

}

}

return result;

}

public Collection doBetweenSharding(Collection availableTargetNames,

ShardingValue shardingValue) {

Collection result = new LinkedHashSet(availableTargetNames.size());

Range range = (Range) shardingValue.getValueRange();

for (int i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {

String routeDBSuffix = getRouteDBSuffix(i);

for (String each : availableTargetNames) {

if (each.endsWith(routeDBSuffix)) {

result.add(each);

}

}

}

return result;

}

public String getRouteDBSuffix(Integer shardingCode) {

int modValue = shardingCode % 3;

return “0” + modValue;

}

}