一文快速入门分库分表中间件 Sharding-JDBC （必修课）（二）

SQL 解析

SQL解析过程分为词法解析和语法解析两步，比如下边这条查询用户订单的SQL，先用词法解析将SQL拆解成不可再分的原子单元。在根据不同数据库方言所提供的字典，将这些单元归类为关键字，表达式，变量或者操作符等类型。

SELECT order_no,price FROM t_order_ where user_id = 10086 and order_status > 0

接着语法解析会将拆分后的SQL转换为抽象语法树，通过对抽象语法树遍历，提炼出分片所需的上下文，上下文包含查询字段信息（Field）、表信息（Table）、查询条件（Condition）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit）等，并标记出 SQL中有可能需要改写的位置。

 

抽象语法树

执⾏器优化

执⾏器优化对SQL分片条件进行优化，处理像关键字 OR这种影响性能的坏味道。

 

SQL 路由

SQL 路由通过解析分片上下文，匹配到用户配置的分片策略，并生成路由路径。简单点理解就是可以根据我们配置的分片策略计算出 SQL该在哪个库的哪个表中执行，而SQL路由又根据有无分片健区分出 分片路由 和 广播路由。

 官方路由图谱

有分⽚键的路由叫分片路由，细分为直接路由、标准路由和笛卡尔积路由这3种类型。

 

标准路由

标准路由是最推荐也是最为常⽤的分⽚⽅式，它的适⽤范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。

 

当 SQL分片健的运算符为 = 时，路由结果将落⼊单库（表），当分⽚运算符是BETWEEN 或IN 等范围时，路由结果则不⼀定落⼊唯⼀的库（表），因此⼀条逻辑SQL最终可能被拆分为多条⽤于执⾏的真实SQL。

SELECT * FROM t_order  where t_order_id in (1,2)

SQL路由处理后

SELECT * FROM t_order_0  where t_order_id in (1,2)
SELECT * FROM t_order_1  where t_order_id in (1,2)

直接路由

直接路由是通过使用 HintAPI 直接将 SQL路由到指定⾄库表的一种分⽚方式，而且直接路由可以⽤于分⽚键不在SQL中的场景，还可以执⾏包括⼦查询、⾃定义函数等复杂情况的任意SQL。

 

比如根据 t_order_id 字段为条件查询订单，此时希望在不修改SQL的前提下，加上 user_id作为分片条件就可以使用直接路由。

 

笛卡尔积路由

笛卡尔路由是由⾮绑定表之间的关联查询产生的，查询性能较低尽量避免走此路由模式。

 
无分⽚键的路由又叫做广播路由，可以划分为全库表路由、全库路由、 全实例路由、单播路由和阻断路由这 5种类型。

 

全库表路由

全库表路由针对的是数据库 DQL和 DML，以及 DDL等操作，当我们执行一条逻辑表 t_order SQL时，在所有分片库中对应的真实表 t_order_0 ···  t_order_n 内逐一执行。

 

全库路由

全库路由主要是对数据库层面的操作，比如数据库 SET 类型的数据库管理命令，以及 TCL 这样的事务控制语句。

 

对逻辑库设置 autocommit 属性后，所有对应的真实库中都执行该命令。

SET autocommit=0;

全实例路由

全实例路由是针对数据库实例的 DCL 操作（设置或更改数据库用户或角色权限），比如：创建一个用户 order ，这个命令将在所有的真实库实例中执行，以此确保 order 用户可以正常访问每一个数据库实例。

CREATE USER order@127.0.0.1 identified BY '程序员内点事';

单播路由

单播路由用来获取某一真实表信息，比如获得表的描述信息：

DESCRIBE t_order; 

t_order 的真实表是 t_order_0 ···· t_order_n，他们的描述结构相完全同，我们只需在任意的真实表执行一次就可以。

 

阻断路由

⽤来屏蔽SQL对数据库的操作，例如：

USE order_db;

这个命令不会在真实数据库中执⾏，因为

 ShardingSphere 

采⽤的是逻辑 Schema（数据库的组织和结构） ⽅式，所以无需将切换数据库的命令发送⾄真实数据库中。

 

SQL 改写

将基于逻辑表开发的SQL改写成可以在真实数据库中可以正确执行的语句。比如查询 t_order 订单表，我们实际开发中 SQL是按逻辑表 t_order 写的。

SELECT * FROM t_order

但分库分表以后真实数据库中 t_order 表就不存在了，而是被拆分成多个子表 t_order_n 分散在不同的数据库内，还按原SQL执行显然是行不通的，这时需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。

SELECT * FROM t_order_n

SQL执⾏

将路由和改写后的真实 SQL 安全且高效发送到底层数据源执行。但这个过程并不是简单的将 SQL 通过JDBC 直接发送至数据源执行，而是平衡数据源连接创建以及内存占用所产生的消耗，它会自动化的平衡资源控制与执行效率。

 

结果归并

将从各个数据节点获取的多数据结果集，合并成一个大的结果集并正确的返回至请求客户端，称为结果归并。而我们SQL中的排序、分组、分页和聚合等语法，均是在归并后的结果集上进行操作的。

 

四、快速实践

下面我们结合 Springboot + mybatisplus 快速搭建一个分库分表案例。

 

1、准备工作

先做准备工作，创建两个数据库 ds-0、ds-1，两个库中分别建表 t_order_0、t_order_1、t_order_2 、t_order_item_0、t_order_item_1、t_order_item_2，t_config，方便后边验证广播表、绑定表的场景。

 表结构如下：

 

t_order_0 订单表

CREATE TABLE `t_order_0` (
  `order_id` bigint(200) NOT NULL,
  `order_no` varchar(100) DEFAULT NULL,
  `create_name` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

t_order_0 与 t_order_item_0  互为关联表

CREATE TABLE `t_order_item_0` (
  `item_id` bigint(100) NOT NULL,
  `order_no` varchar(200) NOT NULL,
  `item_name` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

广播表 t_config

  `id` bigint(30) NOT NULL,
  `remark` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ShardingSphere 提供了4种分片配置方式：

 

 •  Java 代码配置

Yaml 、properties 配置

Spring 命名空间配置

Spring Boot配置

为让代码看上去更简洁和直观，后边统一使用 properties 配置的方式，引入 shardingsphere 对应的 sharding-jdbc-spring-boot-starter 和 sharding-core-common 包，版本统一用的 4.0.0-RC1。

 

2、分片配置

<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
 <version>4.0.0-RC1</version>
</dependency>

<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-core-common</artifactId>
 <version>4.0.0-RC1</version>
</dependency>

准备工作做完（ mybatis 搭建就不赘述了），接下来我们逐一解读分片配置信息。

 

我们首先定义两个数据源 ds-0、ds-1，并分别加上数据源的基础信息。

# 定义两个全局数据源
spring.shardingsphere.datasource.names=ds-0,ds-1

# 配置数据源 ds-0
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=root

# 配置数据源 ds-1
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=root

配置完数据源接下来为表添加分库和分表策略，使用 sharding-jdbc 做分库分表需要我们为每一个表单独设置分片规则。

# 配置分片表 t_order
# 指定真实数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..2}

actual-data-nodes 属性指定分片的真实数据节点，$是一个占位符，{0..1}表示实际拆分的数据库表数量。

 

ds-$->{0..1}.t_order_$->{0..2}  表达式相当于 6个数据节点

 

 •  ds-0.t_order_0
 •  ds-0.t_order_1
 •  ds-0.t_order_2
 •  ds-1.t_order_0
 •  ds-1.t_order_1
 •  ds-1.t_order_2

### 分库策略
# 分库分片健
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
# 分库分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}

为表设置分库策略，上边讲了 sharding-jdbc 它提供了四种分片策略，为快速搭建我们先以最简单的行内表达式分片策略来实现，在下一篇会介绍四种分片策略的详细用法和使用场景。

database-strategy.inline.sharding-column  属性中 database-strategy  为分库策略，inline  为具体的分片策略，sharding-column  代表分片健。

 

database-strategy.inline.algorithm-expression 是当前策略下具体的分片算法，ds-$->{order_id % 2} 表达式意思是 对 order_id字段进行取模分库，2 代表分片库的个数，不同的策略对应不同的算法，这里也可以是我们自定义的分片算法类。

# 分表策略
# 分表分片健
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分表算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3}
# 自增主键字段
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 自增主键ID 生成方案
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

分表策略 和 分库策略 的配置比较相似，不同的是分表可以通过 key-generator.column 和 key-generator.type 设置自增主键以及指定自增主键的生成方案，目前内置了SNOWFLAKE 和 UUID 两种方式，还能自定义的主键生成算法类，后续会详细的讲解。

# 绑定表关系
spring.shardingsphere.sharding.binding-tables= t_order,t_order_item

必须按相同分片健进行分片的表才能互为成绑定表，在联合查询时就能避免出现笛卡尔积查询。

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=t_config

广播表，开启 SQL解析日志，能清晰的看到 SQL分片解析的过程

# 是否开启 SQL解析日志
spring.shardingsphere.props.sql.show=true

3、验证分片

分片配置完以后我们无需在修改业务代码了，直接执行业务逻辑的增、删、改、查即可，接下来验证一下分片的效果。

 

我们同时向 t_order、t_order_item 表插入 5条订单记录，并不给定主键 order_id ，item_id 字段值。

public String insertOrder() {

   for (int i = 0; i < 4; i++) {
       TOrder order = new TOrder();
       order.setOrderNo("A000" + i);
       order.setCreateName("订单 " + i);
       order.setPrice(new BigDecimal("" + i));
       orderRepository.insert(order);

       TOrderItem orderItem = new TOrderItem();
       orderItem.setOrderId(order.getOrderId());
       orderItem.setOrderNo("A000" + i);
       orderItem.setItemName("服务项目" + i);
       orderItem.setPrice(new BigDecimal("" + i));
       orderItemRepository.insert(orderItem);
   }
   return "success";
}

看到订单记录被成功分散到了不同的库表中， order_id 字段也自动生成了主键ID，基础的分片功能就完成了。

 基础分片

那向广播表 t_config 中插入一条数据会是什么效果呢？

public String config() {

    TConfig tConfig = new TConfig();
    tConfig.setRemark("我是广播表");
    tConfig.setCreateTime(new Date());
    tConfig.setLastModifyTime(new Date());
    configRepository.insert(tConfig);
    return "success";
}

发现所有库中 t_config 表都执行了这条SQL，广播表和 MQ广播订阅的模式很相似，所有订阅的客户端都会收到同一条消息。

广播表

简单SQL操作验证没问通，接下来在试试复杂一点的联合查询，前边我们已经把 t_order 、t_order_item 表设为绑定表，直接联表查询执行一下。

 关联查询

通过控制台日志发现，逻辑表SQL 经过解析以后，只对  t_order_0 和 t_order_item_0 表进行了关联产生一条SQL。

 绑定表SQL

那如果不互为绑定表又会是什么情况呢？去掉 spring.shardingsphere.sharding.binding-tables试一下。

 

发现控制台解析出了 3条真实表SQL，而去掉 order_id 作为查询条件再次执行后，结果解析出了 9条SQL，进行了笛卡尔积查询。所以相比之下绑定表的优点就不言而喻了。

 笛卡尔积查询

五、总结

以上对分库分表中间件 sharding-jdbc 的基础概念做了简单梳理，快速的搭建了一个分库分表案例，但这只是实践分库分表的第一步，下一篇我们会详细的介绍四种分片策略的具体用法和使用场景（必知必会），后边将陆续讲解自定义分布式主键、分布式数据库事务、分布式服务治理，数据脱敏等。