一文彻底搞懂 MYSQL分库分表方案_mysql水平分表

MYSQL分库分表方案

垂直分表

把一部分表字段放入一张表，另一部分放入其他的表。按照表字段的使用频次分门别类的划分。

例如：在商品列表查询时，列表中只是展示部分字段，同时这个列表查询比详情信息查询更加高频，并不需要把所有字段都展示，我们可以单独把列表信息单独拿出来当一张表。

1. 为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响
2. 充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。

当表数据量很大时，可以将表按字段切开，将热门字段、冷门字段分开放置在不同表中，这些库可以放在不同的存储设备上，避免IO争抢。垂直切分带来的性能提升主要集中在热门数据的操作效率上，而且磁盘争用情况减少。 同理也可以大字段进行拆分扩展表想法。

垂直分库

通过垂直分表达到一定的程度之后，库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

它带来的提升是：

1. 解决业务层面的耦合，业务清晰
2. 能对不同业务的数据进行分级管理、维护、监控、扩展等
3. 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈

垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。

水平分库

我们已经通过垂直分表，垂直分库解决了一部分的性能问题，但是随着业务的增长，单表和单库的性能越来越差，此时我们考虑将其中一半数据放一个库，这种就叫水平分库（为啥叫水平分库，因为分出来的库的结构是相同的。）。比如id为单数放在库一，id为双数放在库二。

水平分表

水平分库可以解决单表数据过多的问题，但是过多的库同时也会对运维什么压力会增大，

水平分只是对数据进行拆分，不改变结构

在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表

分库分表带来的问题

• 事务一致性问题

  由于分库分表把数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题。

• 跨节点关联查询

  在没有分库前，我们检索商品时可以通过以下SQL对店铺信息进行关联查询：

  SELECT p.*,r.[地理区域名称],s.[店铺名称],s.[信誉]
  FROM [商品信息] p
  LEFT JOIN [地理区域] r ON p.[产地] = r.[地理区域编码]
  LEFT JOIN [店铺信息] s ON p.id = s.[所属店铺]
  WHERE...ORDER BY...LIMIT...
  1
  2
  3
  4
  5

  但垂直分库后[商品信息]和[店铺信息]不在一个数据库，甚至不在一台服务器，无法进行关联查询。

  解决方案：

  • 全局表：所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库join查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

  • **字段冗余：**这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免join查询。

    举个电商业务中很简单的场景：

    “订单表”中保存“卖家Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。

    字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，可以借助数据库中的触发器或者在业务代码层面去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了Name之后，是否需要在订单信息中同步更新呢？

  • **数据组装：**分两次查询，第一次先查出关联数据id，第二次根据id查询出关联数据，然后将获得的数据进行字段拼装。

  • **ER分片：**关系型数据库中，如果能够确定好表与表之间的关系，可以将有关联关系的数据放在同一个分片上。

• 跨节点分页、排序函数

  跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据
  进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。
  如，进行水平分库后的商品库，按ID倒序排序分页，取第一页：

image.png

• 主键避重

  在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。

MyCat是一个基于第三方应用中间件数据库代理框架，客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器中。

Sharding-Jdbc是一个Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。MyCat属于服务器端数据库中间件，而Sharding-Jdbc是一个本地数据库中间件框架。

从设计理念上看确实有一定的相似性。主要流程都是SQL 解析 -> SQL 路由 -> SQL 改写 -> SQL 执行 -> 结果归并。但架构设计上是不同的。Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 的扩展，是以 jar 包的形式提供轻量级服务的。

Sharding-JDBC

基本概念：

逻辑表：

水平拆分的数据表的总称。例：订单数据表根据主键尾数拆分为10张表，分别是 t_order_0 、 t_order_1 到t_order_9 ，他们的逻辑表名为 t_order 。

真实表：

在分片的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9 。

数据节点：

数据分片的最小物理单元。由数据源名称和数据表组成，例： ds_0.t_order_0 。

绑定表：

指分片规则一致的主表和子表。例如： t_order 表和 t_order_item 表，均按照 order_id 分片,绑定表之间的分区键完全相同，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10,
11);
1
2

在不配置绑定表关系时，假设分片键 order_id 将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
1
2
3
4
5
6
7
8

在配置绑定表关系后，路由的SQL应该为2条：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in
(10, 11);
1
2
3
4

广播表：

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

分片键：

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingJdbc也支持根据多个字段进行分片。

分片算法：

通过分片算法将数据分片，支持通过 = 、 BETWEEN 和 IN 分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。包括：精确分片算法 、范围分片算法 ，复合分片算法 等。例如：where order_id = ? 将采用精确分片算法，where order_id in (?,?,?)将采用精确分片算法，where order_id BETWEEN ? and ? 将采用范围分片算法，复合分片算法用于分片键有多个复杂情况。

分片策略：

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。内置的分片策略大致可分为尾数取模、哈希、范围、标签、时间等。由用户方配置的分片策略则更加灵活，常用的使用行表达式配置分片策略，它采用Groovy表达式表示，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为 t_user_0 到 t_user_7 。

自增主键生成策略：

通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。

sql解析：

SQL解析过程分为词法解析和语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树。

例如，以下SQL：

SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18
1

解析之后的为抽象语法树见下图：

为了便于理解，抽象语法树中的关键字的Token用绿色表示，变量的Token用红色表示，灰色表示需要进一步拆分。

最后，通过对抽象语法树的遍历去提炼分片所需的上下文，并标记有可能需要SQL改写(后边介绍)的位置。 供分片使用的解析上下文包含查询选择项（Select Items）、表信息（Table）、分片条件（Sharding Condition）、自增主键信息（Auto increment Primary Key）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit、Rownum、Top）。

sql路由：

SQL路由就是把针对逻辑表的数据操作映射到对数据结点操作的过程。

根据解析上下文匹配数据库和表的分片策略，并生成路由路径。 对于携带分片键的SQL，根据分片键操作符不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围路由(分片键的操作符是BETWEEN)，不携带分片键的SQL则采用广播路由。根据分片键进行路由的场景可分为直接路由、标准路由、笛卡尔路由等。

标准路由

标准路由是Sharding-Jdbc最为推荐使用的分片方式，它的适用范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。 当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是BETWEEN或IN时，则路由结果不一定落入唯一的库（表），因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。 举例说明，如果按照order_id 的奇数和偶数进行数据分片，一个单表查询的SQL如下：

SELECT * FROM t_order WHERE order_id IN (1, 2);
1

那么路由的结果应为：

SELECT * FROM t_order_0 WHERE order_id IN (1, 2);
SELECT * FROM t_order_1 WHERE order_id IN (1, 2);
1
2

绑定表的关联查询与单表查询复杂度和性能相当。举例说明，如果一个包含绑定表的关联查询的SQL如下：

SELECT * FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE order_id IN (1, 2);
1

那么路由的结果应为：

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
1
2

可以看到，SQL拆分的数目与单表是一致的。

笛卡尔路由

笛卡尔路由是最复杂的情况，它无法根据绑定表的关系定位分片规则，因此非绑定表之间的关联查询需要拆解为笛卡尔积组合执行。 如果上个示例中的SQL并未配置绑定表关系，那么路由的结果应为：

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE order_id IN (1,2);
1
2
3
4

笛卡尔路由查询性能较低，需谨慎使用。

全库表路由

对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路由、单播路由和阻断路由这5种类型。其中全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作，主要包括不带分片键的DQL(数据查询)和DML（数据操纵），以及DDL（数据定义）等。例如：

SELECT * FROM t_order WHERE good_prority IN (1, 10);
1

则会遍历所有数据库中的所有表，逐一匹配逻辑表和真实表名，能够匹配得上则执行。路由后成为

SELECT * FROM t_order_0 WHERE good_prority IN (1, 10);
SELECT * FROM t_order_1 WHERE good_prority IN (1, 10);
SELECT * FROM t_order_2 WHERE good_prority IN (1, 10);
SELECT * FROM t_order_3 WHERE good_prority IN (1, 10);
1
2
3
4

4.sql改写

工程师面向逻辑表书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL改写为在真实数据库中可以正确执行的SQL。

如一个简单的例子，若逻辑SQL为：

SELECT order_id FROM t_order WHERE order_id=1;
1

假设该SQL配置分片键order_id，并且order_id=1的情况，将路由至分片表1。那么改写之后的SQL应该为：

SELECT order_id FROM t_order_1 WHERE order_id=1;
1

再比如，Sharding-JDBC需要在结果归并时获取相应数据，但该数据并未能通过查询的SQL返回。 这种情况主要是针对GROUP BY和ORDER BY。结果归并时，需要根据GROUP BY 和ORDER BY 的字段项进行分组和排序，但如果原始SQL的选择项中若并未包含分组项或排序项，则需要对原始SQL进行改写。先看一下原始SQL中带有结果归并所需信息的场景：

SELECT order_id, user_id FROM t_order ORDER BY user_id;
1

由于使用user_id进行排序，在结果归并中需要能够获取到user_id的数据，而上面的SQL是能够获取到user_id数据的，因此无需补列。

如果选择项中不包含结果归并时所需的列，则需要进行补列，如以下SQL：

SELECT order_id FROM t_order ORDER BY user_id;
1

由于原始SQL中并不包含需要在结果归并中需要获取的user_id，因此需要对SQL进行补列改写。补列之后的SQL是：

SELECT order_id, user_id AS ORDER_BY_DERIVED_0 FROM t_order ORDER BY user_id;
1

5.SQL执行

Sharding-JDBC采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行；也并非直接将执行请求放入线程池去并发执行。它更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最大限度地合理利用并发等问题。 执行引擎的目标是自动化的平衡资源控制与执行效率，他能在以下两种模式自适应切换：

内存限制模式

使用此模式的前提是，Sharding-JDBC对一次操作所耗费的数据库连接数量不做限制。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，则对每张表创建一个新的数据库连接，并通过多线程的方式并发处理，以达成执行效率最大化。

连接限制模式

使用此模式的前提是，Sharding-JDBC严格控制对一次操作所耗费的数据库连接数量。 如果实际执行的SQL需要对某数据库实例中的200张表做操作，那么只会创建唯一的数据库连接，并对其200张表串行处理。 如果一次操作中的分片散落在不同的数据库，仍然采用多线程处理对不同库的操作，但每个库的每次操作仍然只创建一个唯一的数据库连接。内存限制模式适用于OLAP操作，可以通过放宽对数据库连接的限制提升系统吞吐量；连接限制模式适用于OLTP操作，OLTP通常带有分片键，会路由到单一的分片，因此严格控制数据库连接，以保证在线系统数据库资源能够被更多的应用所使用，是明智的选择。

6.结果归并

将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。

Sharding-JDBC支持的结果归并从功能上可分为遍历、排序、分组、分页和聚合5种类型，它们是组合而非互斥的关系。

归并引擎的整体结构划分如下图:

结果归并从结构划分可分为流式归并、内存归并和装饰者归并。流式归并和内存归并是互斥的，装饰者归并可以在流式归并和内存归并之上做进一步的处理。

内存归并很容易理解，他是将所有分片结果集的数据都遍历并存储在内存中，再通过统一的分组、排序以及聚合等计算之后，再将其封装成为逐条访问的数据结果集返回。

流式归并是指每一次从数据库结果集中获取到的数据，都能够通过游标逐条获取的方式返回正确的单条数据，它与数据库原生的返回结果集的方式最为契合。

下边举例说明排序归并的过程，如下图是一个通过分数进行排序的示例图，它采用流式归并方式。 图中展示了3张表返回的数据结果集，每个数据结果集已经根据分数排序完毕，但是3个数据结果集之间是无序的。 将3个数据结果集的当前游标指向的数据值进行排序，并放入优先级队列，t_score_0的第一个数据值最大，t_score_2的第一个数据值次之，t_score_1的第一个数据值最小，因此优先级队列根据t_score_0，t_score_2和t_score_1的方式排序队列。

下图则展现了进行next调用的时候，排序归并是如何进行的。 通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_0将会被弹出队列，并且将当前游标指向的数据值（也就是100）返回至查询客户端，并且将游标下移一位之后，重新放入优先级队列。 而优先级队列也会根据t_score_0的当前数据结果集指向游标的数据值（这里是90）进行排序，根据当前数值，t_score_0排列在队列的最后一位。 之前队列中排名第二的t_score_2的数据结果集则自动排在了队列首位。

在进行第二次next时，只需要将目前排列在队列首位的t_score_2弹出队列，并且将其数据结果集游标指向的值返回至客户端，并下移游标，继续加入队列排队，以此类推。 当一个结果集中已经没有数据了，则无需再次加入队列。

可以看到，对于每个数据结果集中的数据有序，而多数据结果集整体无序的情况下, Sharding-JDBC无需将所有的数据都加载至内存即可排序。 它使用的是流式归并的方式，每次next仅获取唯一正确的一条数据，极大的节省了内存的消耗。

装饰者归并是对所有的结果集归并进行统一的功能增强，比如归并时需要聚合SUM前，在进行聚合计算前，都会通过内存归并或流式归并查询出结果集。因此，聚合归并是在之前介绍的归并类型之上追加的归并能力，即装饰者模式。

分库分表SpringBoot 集成样例Demo:

分库分表

这里我们目前的打算分四个库，每个库2个表，以订单表为例

数据库结构如下:

db_00–
|–t_order_0000
|–t_order_0001
db_01–
|–t_order_0000
|–t_order_0001
db_02–
|–t_order_0000
|–t_order_0001
db_03–
|–t_order_0000
|–t_order_0001

建表准备

分别在 db_00,db_01,db_02.db_03创建 t_order_0000, t_order_0001

DROP TABLE IF EXISTS t_order_0000;
CREATE TABLE t_order_0000 (
id int(11) NOT NULL AUTO_INCREMENT,
user_id bigint(20) DEFAULT NULL,
order_id bigint(20) DEFAULT NULL,
user_name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1239 DEFAULT CHARSET=utf8;

---

– Table structure for t_order_0001

---

DROP TABLE IF EXISTS t_order_0001;
CREATE TABLE t_order_0001 (
id int(11) NOT NULL AUTO_INCREMENT,
user_id bigint(20) DEFAULT NULL,
order_id bigint(20) DEFAULT NULL,
user_name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1239 DEFAULT CHARSET=utf8;

同时在ds0单独见一个order_config表

DROP TABLE IF EXISTS order_config;
CREATE TABLE order_config (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
pay_timeout int(11) DEFAULT NULL COMMENT ‘支付超时时间;单位：分钟’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘订单配置表’;

引入依赖

​

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- 实现对 MyBatis 的自动化配置 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!-- 实现对 Sharding-JDBC 的自动化配置 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>
        <!-- 方便等会写单元测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

说明： sharding-jdbc-spring-boot-starter 暂时不要使用最新的 4.0.0-RC3，会有一些奇怪bug，
连接池尽量不要加入 druid，不然也会出现sharing-jdbc 的DataSource冲突，如果非要用，可自行百度找解决方法，
这是官网给出的说明

配置文件

# ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
# ds2
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://localhost:3306/ds2?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456

# ds3
spring.shardingsphere.datasource.ds3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds3.jdbc-url=jdbc:mysql://localhost:3306/ds3?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds3.username=root
spring.shardingsphere.datasource.ds3.password=123456

#数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds0,ds1,ds2,ds3
#未配置分片规则的表将通过默认数据源定位-适用于单库单表，该表无需配置分片规则
spring.shardingsphere.sharding.default-data-source-name=ds0

# 分库 以 user_id 为分片键，因为分四个库，所以取模 4，虚拟表名为 t_order
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 4}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id


# 分表 分片键位order_id,每个库分为两表，所以取模2
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_000$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 这里由于分库分表字段不相同配置，不然会导致使用user_id 查询找不到相应的表，如果我们分库分表都使用 user_id 则不需要这个配置
spring.shardingsphere.sharding..tables.t_order.actual-data-nodes = ds$->{0..3}.t_order_000$->{0..1}

#是否开启SQL显示，默认值: false
spring.shardingsphere.props.sql.show= true

# mybatsi 配置
# 别名
mybatis.type-aliases-package=com.zou.entity
mybatis.mapper-locations=classpath:mapper/*.xml
# 开启驼峰自动转大写
mybatis.configuration.mapUnderscoreToCamelCase=true

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

其他实体类和dao

@Data
@ToString
public class Order {

    private String id;
    private Long userId;
    private Long orderId;
    private String userName;

}

1
2
3
4
5
6
7
8
9
10
11

@Data
public class OrderConfig {


    private Integer id;
    private Integer payTimeout;
}

1
2
3
4
5
6
7
8

@Repository
public interface OrderConfigDao {
    List<OrderConfig> getOrderConfig();
}
1
2
3
4

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zou.dao.OrderDao">


    <select id="getOrders" parameterType="Order"
            resultType="Order">
        select
            t.id ,
            t.user_id ,
            t.order_id ,
            t.user_name
        from t_order t
        where t.user_id=#{userId}
	</select>

    <insert id="addOrder" parameterType="Order" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO t_order (
            user_id,user_name,order_id
        ) VALUES (
            #{userId},#{userName},#{orderId}
        )
    </insert>

</mapper>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zou.dao.OrderConfigDao">


    <select id="getOrderConfig" parameterType="Integer" resultType="OrderConfig">
        SELECT *
        FROM order_config
    </select>

</mapper>

1
2
3
4
5
6
7
8
9
10
11
12

测试

@SpringBootTest(classes = ShardingSphereApplication.class)
@RunWith(SpringRunner.class)
public class OrderDaoTest {

    @Autowired
    OrderDao orderDao;


    @Test
    public void insert() {
        for (int i = 0; i < 1000; i++) {
            long userId = i;
            long orderId = i + 1;
            Order order = new Order();
            order.setUserName("阿离");
            order.setUserId(userId);
            order.setOrderId(orderId);
            int result = orderDao.addOrder(order);
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

插入数据逻辑：
userId = 0 --> 0%4 = 0 对应数据库 db0
orderId = 1 --> 1%2 = 1 对应表 t_order_0001

所以第一条数据就落到 db0数据库的 t_order_0001表

其他的也是如此

查询

@Test
    public void testQueryList() {
        Order order = new Order();
        order.setUserId(2l);

        List<Order> list = orderDao.getOrders(order);
        list.forEach(s -> System.out.println(s));
    }
1
2
3
4
5
6
7
8

由于我们只指定了分库片 userId查询，所以这里就会查询 ds2数据库，但是无法确定是那张表，所以会查询所有表，然后将所有表的结果归并返回，我们看sql可以看出

@Test
    public void getOrders() {
        Order order = new Order();
        order.setUserId(8l);
        order.setOrderId(8l);
       orderDao.getOrders(order);
1
2
3
4
5
6

如果我们指定 userId 和 orderId就可以找到对应的库和表，就只会查询一张表

如果我们查询默认没有分库分表的普通表 order_config，则会自动去我们配置的默认数据库去找到相应的表

@Autowired
    OrderConfigDao orderConfigDao;

    @Test

  public  void getOrderConfig() {
        orderConfigDao.getOrderConfig();

    }


1
2
3
4
5
6
7
8
9
10
11