Sharding-JDBC实现分库分表+读写分离

本篇主要自己记录使用，如有需要请自行阅读。
纯手码笔记，都是入门的案例，大佬请跳过

分库分表

为啥要分库分表？

数据量达到一定程度，对性能造成了影响
生产当中，数据量过大，对关系型数据库性能造成影响

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目 的。

怎么分？

垂直分表	水平分表
垂直分库	水平分库

垂直分表

按照字段分表，常用的放一张表，不常用的、数据量大的放一张

垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段

将访问频次低的商品描述信息单独存放在一张表中，

访问频次较高的商品基本信息单独放在一张表中。

好处：

1.避免IO争抢减少锁表的几率，数据库本身具备行锁，比如商品信息和商品详情放两张表，修改商品详情的话，不会影响商品信息的访问
2.使热点数据的操作效率变高，避免访问低频的数据造成累赘
	商品信息是热点数据，商品详情数据量大，IO效率低，读取时间长；
	而且每页显示的数据也多了，因为商品信息数据比较短

垂直分库：

垂直分表只解决了单表数据量大的问题，但是其他这些表还在同一个服务器上，
因此表与表之间还是会竞争同一个物理机的CPU、内存、网络IO、磁盘

因此最好再把表放在不同的服务器上，这样就不在竞争资源了

一个数据库拆成多个数据库，每个数据库做一件事情，专库专用

商品信息和商品详情耦合较高，放一个库 --->  商品库
店铺信息独立一点，再单独放一个库 ---> 店铺库

小结：将库放在不同的服务器，专库专用

好处：

1.解决业务层面的耦合，业务清晰
2.对不同业务的数据方便维护，管理
3.分摊服务器的压力

水平分库：

现在是分成了两个库，
一个商品库，有商品信息表和商品详情表
一个卖家库，只有卖家表

日益增加的商品，商品数量很大了，虽然垂直分表了，但单表的数量达到了1500w
可以把商品数据库拆成两个数据库
product_db01 和 product_db02

两个库的表结构是一样的

存入的时候可以 根据商品id判断 比如 id%2+1 判断存入哪个db01还是db02

水平分表：

和水平分库 类似
存入的时候同样可以 使用 商品id做判断，决定存储到哪张表上，
比如分两张表 就id%2，分三张表 就id%3
（上面分库可以使用店铺id来运算 决定存到哪个库）

分库分表小结：

垂直分表：
	按字段进行表的拆分，将热点字段放一张表，将访问低频的字段放一张表
	如商品简介和商品详情，可以有效提高热点字段的访问效率，并且能减少锁表的几率
垂直分库：
	专库专用，比如商品存一个库，卖家信息存一个库
	分摊服务器压力，便于对不同的数据进行维护，管理
水平分表：
	按行进行拆分，减轻单表的存储压力，避免IO争抢，减少锁表的概率
水平分库：
	和水平分表类似，减少单个数据库的存储压力
	
不得己情况下，数据量不是很多，访问压力不是特别大的情况下
最好不要进行分库分表，系统复杂度高了，难维护
而且最头疼的就是分布式事务的问题

可以优先考虑使用缓存(redis)，读写分离(InnoDB和MyISAM),给字段加索引

分库分表问题：

1.事务的一致性问题
	分布式问题
2.跨节点关联查询
	比如商品信息和店铺信息垂直分库后在两个库了，无法关联查询，要查两次
3.跨节点分页、排序

4.主键要避免重复
	主键自增就不适用了
5.公共表

Sharding-JDBC介绍：

核心功能就是 数据分片 和 读写分离

Sharding-JDBC的核心功能为数据分片和读写分离，通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库 分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

何谓透明：
	只要导入了Sharding-JDBC的依赖之后
	就像操作独立的数据库和独立的表一样 就可以操作分好的多个数据库和多个表
	程序员甚至不需要知道分了几个库几个表（被拆分之后）
	虽然要面对很多数据源，但有了Sharding-JDBC就像操作单个  一样，

和JDBC关系

对JDBC的增强

主要分库分表的场景效率高
单裤单表比JDBC效率略低

Sharding-JDBC水平分表

订单表的水平分表，t_order分为 t_order1 和 t_order2

如果程序员自己向两张表插入数据的话，肯定会分别的写SQL
但是通过Sharding-JDBC就可以操作这两张表，
只要定义好分片规则，(比如主键偶数进入t_order1，奇数进t_order2)
我们主要操作逻辑表 t_order ， 
然后Sharding-JDBC就会自动根据 分片规则 将数据存放在数据库中

下面我们来简单看下配置

我们创建一个数据库 ---> order_db

并在此库中创建了两张表 ---> t_order1 和 t_order2 用来存放订单

使用boot搭建的项目，导入依赖，写好启动类开始进行配置

1.配置

使用Sharding-JDBC 最重要的就是分片规则的配置

即指定Sharding-JDBC分库分表的规则

application.properties 配置文件中

# ------------------------sharding-jdbc分片规则配置------------------------------------------
# 数据源声明
spring.shardingsphere.datasource.names = m1

# 数据源的信息，先连接上数据库 order_db
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root


# order_db数据库中有数据表 t_order_1 和 t_order_2，将两个节点配置起来
# 指定t_order表的数据分布情况，配置数据节点 m1.t_order_1,m1.t_order_2
# 这里的t_order是逻辑数据表的名字，这里写啥，sql写啥，Sharding-JDBC会根据sql自动映射过来
# $->{1..2} $ 最终会被{ } 中的1和2替代，表达式意为 --> m1.t_order_1和m1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{1..2}


# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

# 指定t_order表的分片策略，分片策略包括分片键和分片算法
# t_order_$->{order_id % 2 + 1} --> 两种情况 t_order_1 或 t_order_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 % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
# ------------------------sharding-jdbc分片规则配置------------------------------------------


小结：
1.首先配置数据源，可以连接上数据库
2.配置表的数据节点，水平拆分为几个表，都配置上
3.配置主键的生成策略(如：雪花算法)
4.指定表的分片策略，包括分片键(根据哪个键分片)和分片算法(对id取模..)

2.新增测试

测试一下，dao层

@Mapper
@Component
public interface OrderDao {

    /**
     * 插入订单
     * @param price
     * @param userId
     * @param status
     * @return
     */
    @Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
    int insertOrder(@Param("price")BigDecimal price,
                    @Param("userId")Long userId,
                    @Param("status")String status);
}

测试类

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

    @Autowired
    OrderDao orderDao;

    @Test
    public void testInsertOrder(){
        for(int i=1;i<20;i++){
            orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS");
        }
    }
}

然后执行之后会发现，我们执行的逻辑SQL，我们自己写的SQL，是：(t_order表是不存在的)

: Logic SQL: insert into t_order(price,user_id,status)values(?,?,?)

然后会被Sharding-JDBC拦截，解析之后执行

: SQLStatement: ...
: Actual SQL: m1 ::: insert into t_order_2 (price, user_id, status, order_id) VALUES (?, ?, ?, ?) ::: [19, 1, SUCCESS, 528153217263992833]

为啥往 t_order_2 插入？因为id是奇数，因为配置了分片策略

t_order_$->{order_id % 2 + 1} 所以结果为 ---> t_order_2

3.查询测试

dao层

@Mapper
@Component
public interface OrderDao {

    /**
     * 根据id列表查询订单
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            "</script>")
    List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}

测试类

@Test
    public void testSelectOrderbyIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(527987276307759105L);
        ids.add(527987277045956608L);

        List<Map> maps = orderDao.selectOrderbyIds(ids);
        System.out.println(maps);
    }

结果：

[{user_id=1, price=2.00, order_id=528153216727121920, status=SUCCESS}, 
 {user_id=1, price=1.00, order_id=528153216160890881, status=SUCCESS}]

查看日志：

我们自己写的SQL
==>  Preparing: select * from t_order t where t.order_id in ( ? , ? ) 
==> Parameters: 528153216727121920(Long), 528153216160890881(Long)

解析过程
: Rule Type: sharding
: Logic SQL: select *  from t_order t  where t.order_id in   (   ?   ,  ?   )
: SQLStatement:  ...
: Actual SQL: m1 ::: select *  from t_order_1 t  where t.order_id in   (   ?   ,  ?   ) ::: [528153216727121920, 528153216160890881]
: Actual SQL: m1 ::: select *  from t_order_2 t  where t.order_id in   (   ?   ,  ?   ) ::: [528153216727121920, 528153216160890881]

因为两个表个一条数据，所以去查的时候要执行两遍查询的SQL

当Sharding-JDBC拦截SQL，拿到id之后，会根据分片策略进行解析，并知道数据具体在哪张表，然后去查询

4.流程分析：

Sharding-JDBC拦截sql之后，解析sql，获取片键值(即where条件之后的 order_id)，
看是否是定义分片策略中的分片键

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 % 2 + 1}

如果是，就执行下面分片的算法，根据表达式 t_order_$->{order_id % 2 + 1} 来得到要操作的真实的数据表

通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往

t_order_1表插数据，为奇数时，往t_order_2插数据。

然后得到真实操作的表，去改写真实SQL
执行真实SQL
将结果进行汇总，并返回

当然可以使用其他配置方式

yml
xml
java配置类

Sharding-JDBC执行原理

1.基本概念

逻辑表

就是程序员自己操作的表，只是逻辑上的表，水平拆分所有的表的总称

真实表

真实存在的表，真实操作的物理表

数据节点

分片的最小物理单元，由数据源名称和数据表组成，如m1.t_order_1、m1.t_order_2

绑定表

垂直拆分表之后的主表和子表，如商品信息和商品详情，分区键完全相同，为绑定关系的话可以避免笛卡尔积现象

广播表

公共表，所有分片中都存在的表

分片键

用于分片的数据库字段，是水平拆分的关键。一条sql中如果没有分片键将执行全路由

分片算法

通过分片算法将数据分片，支持通过 = 、 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 。

2.大致流程

SQL解析 ---》SQL路由 ---》SQL改写 ---》SQL执行 ---》结果合并

3.SQL解析

SQL解析过程分为词法解析和语法解析。词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据

不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。再使用语法解析器将SQL转换为抽象语法树。

比如如下SQL，解析之后

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

说人话就是 Sharding-JDBC中的解析器会将我们写的逻辑SQL进行拆分，拆分成不可分割的最小单元，

然后进行归类：关键字、表达式，字面量和操作符，然后再解析为语法树

绿色：关键字

红色：变量

灰色：需要进一步拆分

4.SQL路由

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

就是我们自己写的逻辑语句，我们写的数据表是逻辑表，映射到真实表的过程称为路由

路由是由数据库和表的分片策略生成的

根据分片键操作符不同，能分为：

单片路由 (=) 如：where order_id = 1
多片路由 (in) 如：where order_id in (1,2,3)
范围路由 (between) 如：where order_id between (1,3)
广播路由 (不携带分片键)

如：
	select * from 
	select * from where age = 18;
	反正就是没有分片键，无法进行分片处理，就会扫描所有的表，将SQL路由到每一个节点

根据分片键进行路由的场景可分为直接路由、标准路由、笛卡

尔路由等。

标准路由

标准路由是Sharding-Jdbc最为推荐使用的分片方式，它的适用范围是不包含关联查询或仅包含绑定表之间关联查

询的SQL。当分片运算符是等于号时，路由结果将落入单库（表），当分片运算符是BETWEEN或IN时，则路由结

果不一定落入唯一的库（表），因此一条逻辑SQL最终可能被拆分为多条用于执行的真实SQL。举例说明，如果按

照 order_id 的奇数和偶数进行数据分片，一个单表查询的SQL如下：

那么路由的结果应为：

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

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

SELECT * FROM t_order_1 WHERE order_id IN (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);

那么路由的结果应为：

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

可以看到，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);

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

全库表路由

对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路

由、单播路由和阻断路由这5种类型。其中全库表路由用于处理对数据库中与其逻辑表相关的所有真实表的操作，

主要包括不带分片键的DQL(数据查询)和DML（数据操纵），以及DDL（数据定义）等。例如：

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

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

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)

5.SQL改写

SQL改写就是把我们自己写的逻辑SQL，改为真实执行的SQL

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

select * from t_order where order_id = 10086

假设之前配置了分片键为 order_id，然后匹配上分片键之后，会根据分片算法，比如常用的取模算法 order_id%2+1 = 1，所以将路由至分片表1，那么改写之后的SQL为

select * from t_order1 where order_id = 10086

在分组和排序的时候也会进行SQL的改写 --> 增加列

针对GROUP BY和ORDER BY。结果归并时，需要根据 GROUP BY 和 ORDER BY 的字段项进行分组和排序，但如果原始SQL的选择项中若并未包含分组项或排序项，则需要对原始SQL进行改写。先看一下原始SQL中带有结果归并所需信息的场景：

SELECT order_id, user_id FROM t_order ORDER BY user_id;

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

的，因此无需补列。

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

SELECT order_id FROM t_order ORDER BY user_id;

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

是：

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

6.SQL执行

Sharding-JDBC采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源

执行。它不是简单地将SQL通过JDBC直接发送至数据源执行；也并非直接将执行请求放入线程池去并发执行。它

更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最大限度地合理利用并发等问题。执行引擎的目标

是自动化的平衡资源控制与执行效率，他能在以下两种模式自适应切换：

内存限制模式 ：

使用此模式的前提是，Sharding-JDBC对一次操作所耗费的数据库连接数量不做限制。如果实际执行的SQL需要对

某数据库实例中的200张表做操作，则对每张表创建一个新的数据库连接，并通过多线程的方式并发处理，以达成

执行效率最大化。

连接限制模式 ：

使用此模式的前提是，Sharding-JDBC严格控制对一次操作所耗费的数据库连接数量。如果实际执行的SQL需要对

某数据库实例中的200张表做操作，那么只会创建唯一的数据库连接，并对其200张表串行处理。如果一次操作中

的分片散落在不同的数据库，仍然采用多线程处理对不同库的操作，但每个库的每次操作仍然只创建一个唯一的数

据库连接。

内存限制模式适用于OLAP操作，可以通过放宽对数据库连接的限制提升系统吞吐量；连接限制模式适用于OLTP

操作，OLTP通常带有分片键，会路由到单一的分片，因此严格控制数据库连接，以保证在线系统数据库资源能够

被更多的应用所使用，是明智的选择

7.结果归并

从每个节点上获取到数据，在将每个节点上获取的数据组合到一个结果集，然后返回给客户端，就是结果合并

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

关系。

从结构上可分为

流式归并
内存归并
装饰者归并

内存归并：

将所有分片的数据都存储在内存中，再通过统一的分组、排序或者聚合等计算之后，再封装成数据并返回

流式归并：(使用最多)

一边处理一边归并

比如要对三张表所有的数据统一做一个降序的排序

但是所有的数据来源于三张表，怎么排呢？

先对每张表进行一个降序的查询，每张表都是降序的

然后各有一个游标，每张表的游标先指向第一行记录，因为第一条记录是最大的

然后三个游标比较谁的值最大，比出最大的必然是三个表中最大的值

然后把这个最大的值排除掉

每次值被排除之后，游标就会往下一行移动

然后三个游标再次进行比较，再比较最大的值

装饰者模式：

就是在之前归并的结果上进行功能增强，比如要求sum聚合，先分组，求每个分组的sum，然后求整个结果集的sum

Sharding-JDBC水平分库

水平拆分数据库，之前是一个 order_db数据库，现在拆成两个或多个

每个库的数据表是相同的

将order_db拆分成 order_db1 和 order_db2

既然拆分就要配置分片的策略

1.建表+配置

(1)先将原有order_db库拆分为order_db_1、order_db_2

(2)分片规则修改

由于数据库拆分了两个，这里需要配置两个数据源。

分库需要配置分库的策略，和分表策略的意义类似，通过分库策略实现数据操作针对分库的数据库进行操作。

# 数据源声明
spring.shardingsphere.datasource.names = m1,m2

# 数据源的信息，先连接上数据库 order_db
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root


spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/order_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root


# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column = user_id
spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression = m$‐>{user_id % 2 + 1}

2.测试

@Test
    public void testInsertOrder(){
        for(int i=1;i<20;i++){
            orderDao.insertOrder(new BigDecimal(i),1L,"SUCCESS");
        }
    }

3.分析+查验

用户id为1，根据配置的分片规则 m$->{user_id % 2 + 1} 即m2

要执行m2的数据源，m2就是order_db_2的数据库

所以这19条记录都会插入到 order_db_2 数据库当中

然后我们的order_id是雪花算法生成的全局主键

所以数据存进 order_db_2 中之后，会进一步判断是偶数还是奇数

偶数存t_order_1

奇数存t_order_2

4.查询测试

那查询的话，SQL的路由会怎么做呢？

会先根据user_id进行选择分片库，但是如果user_id不给的话，会如何操作呢？

会路由到每一个库吗？可以试一下，只给order_id，不给user_id去查询

@Test
    public void testSelectOrderbyIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(528249266045452288L);
        ids.add(528249265370169345L);

        List<Map> maps = orderDao.selectOrderbyIds(ids);
        System.out.println(maps);
    }

首先这两个order_id都是存在的，运行一下看看结果

: Actual SQL: m1 ::: select *  from t_order_1 t  where t.order_id in   (   ?   ,  ?   ) ::: [528249266045452288, 528249265370169345]
: Actual SQL: m1 ::: select *  from t_order_2 t  where t.order_id in   (   ?   ,  ?   ) ::: [528249266045452288, 528249265370169345]
: <==      Total: 0

日志显示只去m1的数据源查找，很明显结果是什么也没有查询到

这是为什么呢？

我们配置的数据节点，只有m1，没有m2

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m1.t_order_$->{1..2}

由于数据节点只指定了m1，所以就只会路由到m1。所以应该路由到m1和m2

如下修改

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}

再次尝试

结果是查询出来了，看看日志

执行了4条SQL？

为何？

首先，分库是根据user_id的，因为user_id没有给
所以会路由到每一个库，（不携带分片键会导致广播路由）

所以携带着order_id去查询的时候，会去两个库中查找，
因为两个order_id一个是奇数，一个是偶数，所以分别存在于t_order_1和t_order_2中
所以各自查找的时候，会去两个库中的表查找
比如528249266045452288L是存在于m2数据源的t_order_1中的
所以528249266045452288L查询的时候 根据分片策略得知要去 t_order_1 中查找
所以必会去m1的t_order_1查找，再去m2的t_order_1查找

这就为何2条数据要执行4条SQL了

同理可得，一条记录是不是就执行两条SQL，如果只查528249266045452288L是不是会去m1和m2的t_order_1查找呢？再次验证一下

看结果，验证成功！！！

我们再次验证下如果给了 userId 条件之后，看是否只查询一个数据源

dao层加了方法

/**
     * 根据id列表和用户id查询订单
     * @param orderIds
     * @return
     */
    @Select("<script>" +
            "select" +
            " * " +
            " from t_order t " +
            " where t.order_id in " +
            " <foreach collection='orderIds' open='(' separator=',' close=')' item='id'>" +
            " #{id} " +
            " </foreach>" +
            " and user_id = #{userId}" +
            "</script>")
    List<Map> selectOrderbyUserAndIds(@Param("userId")Long userId,@Param("orderIds") List<Long> orderIds);

test新的测试类

@Test
    public void testSelectOrderbyUserAndIds(){
        List<Long> ids = new ArrayList<>();
        ids.add(528249266045452288L);
//        ids.add(528249265370169345L);

        List<Map> maps = orderDao.selectOrderbyUserAndIds(1L,ids);
        System.out.println(maps);
    }

执行看看结果

只查一条数据，并且给了userId的值，所以直接路由到m2数据源，不会广播路由了

小结：

查询的时候最好能给分片键，让他路由到具体的数据节点上，避免广播路由

Sharding-JDBC垂直分库

垂直分库是按业务将表进行分类，达到专库专用的效果

做业务分离，让业务之间互不影响

现在我们将用户表单独抽取出来，单独放一个库

1.建库建表

新建一个user_db库和t_user表

2.分片配置

先配置一个数据源，选择要操作的库

不用分库，所以不要配分库策略

但是要配置数据节点，要告诉Sharding-JDBC要路由到哪个数据节点

# 数据源声明
spring.shardingsphere.datasource.names = m1,m2,m0

# 数据源的信息，先连接上数据库 order_db
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/user_db_1?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = root

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m0.t_user
# 数据源中只有一张表，可以直接硬编码写死

主键生成策略配置不配置都OK，因为现在就一张表，并没有分表，所以也不会有主键冲突的可能

但是分片键和分片算法是一定要进行配置的

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user

配置小结：

1.先配置数据源
2.再看是否分库，分库就配置分库策略
3.无论是否分库分表，都要配置数据节点
4.看是否要配置主键生成策略
5.分片的策略也要进行配置，防止后期进行分表

3.测试

UserDao.java

@Mapper
@Component
public interface UserDao {
    /**
     * 新增用户
     * @param userId 用户id
     * @param fullname 用户姓名
     * @return
     */
    @Insert("insert into t_user(user_id, fullname) value(#{userId},#{fullname})")
    int insertUser(@Param("userId") Long userId,
                   @Param("fullname") String fullname);

    /**
     * 根据id列表查询多个用户
     * @param userIds 用户id列表
     * @return */
    @Select({"<script>",
            " select",
            " * ",
            " from t_user t ",
            " where t.user_id in",
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"})
    List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
}

测试类

	@Test
    public void testInsertUser() {
        for (int i = 0; i < 10; i++) {
            Long id = i + 1L;
            userDao.insertUser(id, "姓名" + id);
        }
    }


    @Test
    public void testSelectUserbyIds() {
        List<Long> userIds = new ArrayList<>();
        userIds.add(1L);
        userIds.add(2L);
        List<Map> users = userDao.selectUserbyIds(userIds);
        System.out.println(users);
    }

运行看看效果

向m0插入记录

Sharding-JDBC公共表

公共表数据量较小，查询频率高的表，比如数据字典

没必要单独搞一个库存放公共表，于是就每个库各一份

由Sharding-JDBC来保证每个库中公共表的数据一致性

1.建表

分别在user_db、order_db_1、order_db_2中创建t_dict表：

2.配置指定公共表

# 指定t_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict

3.测试

测试看看，Sharding-JDBC保持公共表的数据一致性

dao层

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

@Mapper
@Component
public interface DictDao { 
    /**
     * 新增字典
     * @param type 字典类型 
     * @param code 字典编码 
     * @param value 字典值 
     * @return 
     */
    @Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},# {value})")
    int insertDict(@Param("dictId") Long dictId, 
                   @Param("type") String type, 
                   @Param("code") String code, 
                   @Param("value") String value);

    
    /**
     * 删除字典
     * @param dictId 字典id 
     * @return 
     */
    @Delete("delete from t_dict where dict_id = #{dictId}")
    int deleteDict(@Param("dictId") Long dictId);
}

测试类

	@Autowired
    DictDao dictDao;

    @Test
    public void testInsertDict() {
        dictDao.insertDict(1L, "user_type", "0", "管理员");
        dictDao.insertDict(2L, "user_type", "1", "操作员");
    }

    @Test
    public void testDeleteDict() {
        dictDao.deleteDict(1L);
        dictDao.deleteDict(2L);
    }

现在我们三个库的 t_dict 表都是空的，现在执行新增，看是否路由到三个数据节点

三个节点，新增 OK！！

删除也如此

Sharding-JDBC读写分离

对于同一时刻又大量并发读操作和较少写操作

可以将数据库拆分一个主库，一个从库

主库负责处理事务性的增删改，从库负责读、查询

能够有效避免由数据更新导致的行锁

使整个系统的查询性能得到极大改善

可以一主多从

也可以多主多从，提升系统的吞吐量，提升可用性

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库**。它提供透明化

读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。

Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用，同一线程且同一数据库连接

内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。

mysql主从同步

5.7自身支持主从同步（win版本的）

一、新增mysql实例

将mysql文件夹复制一份，比如原始的是MySQL Server 5.7，复制之后的叫MySQL Server 5.7-s1作为从库

找到从库的 my.ini 文件并进行修改

端口号 改为 3307
配置安装目录 改为自己的
设置mysql数据库的数据存放目录

示例：
[mysqld] 
#设置3307端口 
port = 3307 

# 设置mysql的安装目录 
basedir=D:\mysql‐5.7.25‐s1

# 设置mysql数据库的数据的存放目录 
datadir=D:\mysql‐5.7.25‐s1\data

然后将从安装为Windows本地服务，注意配置文件位置：

进到从库的bin目录中，然后打开cmd
然后执行如下命令
mysqld install mysqls1 ‐‐defaults‐file="D:\mysql‐5.7.25‐s1\my.ini"
(注意从库my.ini的文件位置)

然后去本地服务中查看一下，已经有了，启动一下就OK了

二、修改主、从的ini

主库新增配置

[mysqld] 
# 开启日志
log-bin=mysql-bin

#设置服务id，主从不能一致 
server-id=1

#设置需要同步的数据库 
binlog-do-db=user_db

#屏蔽系统库同步
binlog-ignore‐db=mysql
binlog-ignore‐db=information_schema
binlog-ignore‐db=performance_schema

设置同步的数据库，要同步哪个数据库，设置好

不设置将所有的数据库都同步了

忽略哪些数据库不同步

从库新增配置

[mysqld] 
#开启日志 
log-bin=mysql-bin

#设置服务id，主从不能一致 
server-id=2 

#设置需要同步的数据库 
replicate_wild_do_table=user_db.% 

#屏蔽系统库同步 
replicate_wild_ignore_table=mysql.% 
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

配置好之后，在服务中将主库和从库都重新启动一下

都能重启成功，说明配置没问题

在连接工具中连接一下从库，改端口，然后使用和主库相同的用户名和密码

发现目录和主库是一样的

三、主、从库关联

授权主从复制专用账号

#切换至主库bin目录，登录主库 
mysql ‐h localhost ‐uroot ‐p 

#授权主备复制专用账号(创建一个db_sync的用户，用于向从数据库同步数据)
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync'; 

#刷新权限 
FLUSH PRIVILEGES; 

#确认位点 记录下文件名以及位点 
show master status;

可以在连接工具中运行此命令

#授权主备复制专用账号(创建一个db_sync的用户，用于向从数据库同步数据)
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync'; 

#刷新权限 
FLUSH PRIVILEGES;

然后去mysql自带的数据库中看users 是否有db_sync用户

然后执行如下命令

#确认位点 记录下文件名以及位点 
show master status;

作用是看当前数据文件的名称是什么，包括当前数据写的位置

下面是从库连主库

登录到从库，执行

#先停止同步
STOP SLAVE;

然后执行

#修改从库指向到主库，使用上一步记录的文件名以及位点 
CHANGE MASTER TO master_host = 'localhost', 
master_user = 'db_sync', # 指定刚刚授权同步的用户名
master_password = 'db_sync', 
master_log_file = 'mysql‐bin.000002', # 这里是主库中show master status命令之后的File
master_log_pos = 154; # 这里是主库中show master status命令之后的Position

然后启动同步

# 启动同步 
START SLAVE;

然后查看连接状态

#查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功，如果不为Yes，请检查error_log，然后 排查相关异常。
show slave status

Slave_IO_Runing是为NO的

请注意，主从MySQL下的数据(data)目录下有个文件auto.cnf，文件中定义了uuid，要保证主从数据库实例的uuid不一样，建议直接删除掉，重启服务后将会重新生成。

进从库文件夹将auto.cnf所有文件都删除掉

然后重启从库服务

然后在从库中再次

STOP SLAVE;

START SLAVE;

启动完成都是YES了

都是YES之后就可以同步了

Sharding-JDBC读写分离

主库和从库数据同步已经配置好了

现在我们希望Sharding-JDBC帮助我们将SQL分别路由到主库和从库

开始配置

配置从库的数据源之后，告知Sharding-JDBC，谁是主库谁是从库，然后配置数据节点

# 从库 数据源
spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3309/user_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = root

# 主库从库逻辑数据源定义 ds0为user_db
spring.shardingsphere.sharding.master‐slave‐rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master‐slave‐rules.ds0.slave-data-source-names=s0

# t_user分表策略，固定分配至ds0的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes = ds0.t_user

插数据走主库，查数据走从库

配置好之后，Sharding-JDBC会根据你的SQL把具体的SQL路由到主从数据库

查询语句路由到从库

插入语句路由到主库

不同的SQL路由到不同的数据库是有Sharding-JDBC完成

主从之间的复制是mysql完成的

posted @ 2020-10-28 21:58 阿亮在努力阅读(1218) 评论(0) 编辑收藏举报

刷新页面返回顶部

可以说晚安了吗

Sharding-JDBC实现分库分表+读写分离

分库分表

垂直分表

垂直分库：

水平分库：

水平分表：

分库分表小结：

分库分表问题：

Sharding-JDBC介绍：

Sharding-JDBC水平分表

1.配置

2.新增测试

3.查询测试

4.流程分析：

Sharding-JDBC执行原理

1.基本概念

2.大致流程

3.SQL解析

4.SQL路由

5.SQL改写

6.SQL执行

7.结果归并

Sharding-JDBC水平分库

1.建表+配置

2.测试

3.分析+查验

4.查询测试

Sharding-JDBC垂直分库

1.建库建表

2.分片配置

3.测试

Sharding-JDBC公共表

1.建表

2.配置 指定公共表

3.测试

Sharding-JDBC读写分离

mysql主从同步

一、新增mysql实例

二、修改主、从的ini

三、主、从库关联

Sharding-JDBC读写分离

公告

2.配置指定公共表