Sharding-JDBC详细教程(全网最详细！！)

一、分库分表概述

1、分库分表是什么？

2、分库分表方式(垂直分表)

3、分库分表方式(垂直分库)

4、分库分表方式(水平分库)

5、分库分表方式(水平分表)

6、分库分表带来的问题

二、Sharding-JDBC快速入门

1. 需求说明

2. 环境搭建

3. 编写程序

4. 流程分析

三、案例演示

1、水平分表

2、水平分库

3、垂直分库

4、公共表

五、读写分离

1、理解读写分离

2、mysql主从同步(windows)

3、实现sharding-jdbc读写分离

一、分库分表概述

1、分库分表是什么？

随着业务的快速发展，数据库中的数据量猛增，访问性能也变慢，优化必不可少。究其原因是关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据容量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引性能仍下降严重。

方案一：

通过提升服务器硬件能力来提高数据处理能力，比如增加存储容量、CPU等，这种方案成本较高，并且瓶颈存在与MySQL本身，即使提高硬件提升也有限。

方案二：

把数据分散到不同的数据库中，使得单一数据库的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的，将一个库拆分成多个数据库，数据量较大的表拆分成多个小表，从而达到数据库性能提升的目的。

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

2、分库分表方式(垂直分表)

由于数据的特性不一样，因此考虑将访问频次低的数据特性信息单独放到一张表中(即详情表)，访问频次较高的数据特性单独放到一张表中(即主表)。

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

它带来的提升是：

1、为避免IO挣抢并减少锁表的几率

2、充分发挥热门数据字段的操作效率

3、分库分表方式(垂直分库)

垂直分表可以达到一定的性能提升，但是并不能解决数据存储的问题，因为数据还是存在与同一服务器上，垂直分表只是解决了单表数据量过大的问题。查询还是竞争同一物理机器的CPU、内存、网络IO、磁盘等资源。

经过思考，我们可以将原有数据库按照业务功能分为不同的数据库，并将两个数据库分别部署到不同的服务器上，从而达到优化目的。

所以垂直分库的定义：按照业务将表分类，建立不同的数据库，分布到不同的服务器上，它的核心理念是专库专用。

它带来的提升是：

1、解决业务层面的耦合，业务清晰

2、对不同业务的数据进行分级管理、维护、监控、扩展等

3、高并发场景下，垂直分库一定程度提升了IO、数据库连接数、降低了单机硬件资源的瓶颈

4、分库分表方式(水平分库)

经过垂直分表、垂直分库后，数据库性能得到极大的提升，但是随着业务系统的推广即业务复杂度的提升，分库后的数据量依旧到达了瓶颈，单台的服务器已经无法支撑，此时就要考虑利用多台服务器达到压力水平分布的效果。

所以水平分库的定义：把同一个数据库按照一定的规则拆分成不同分数据库，拆分的数据库保持表结构相同，每个库分布到不同的服务器上。

它带来的提升：

1、解决了单库大数据，高并发的性能瓶颈

2、提高了系统的稳定性及可用性

5、分库分表方式(水平分表)

经过垂直分表、垂直分库、水平分库后，单表的业务数据依旧很大，这时候我们可以参照水平分库的思想，把单表数据过大的表拆分成若干小表，表结构相同，依据一定的规则控制数据的流向。

所以水平分表的定义：在同一数据库内，把同一表的数据按照一定规则拆分到多个表中。

它带来的提升是：

1、优化单一表数据量过大而产生的性能问题

2、避免IO挣抢并减少锁表的几率

6、分库分表带来的问题

1、事务一致性问题

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

2 、跨节点关联查询

没有分库分表前，我们关联查询多个表在一个库一台机器上，分库分表后，数据分布在不同的库甚至不同服务器上，无法进行关联查询。

解决思路：将原有关联查询拆分成多次请求查询，利用关联ID分次获取数据后在对数据进行拼装。

3、跨节点分页、排序函数

跨节点多库进行查询时，limit分页、order by排序等问题，就会变得比较复杂了，需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果汇总再排序。

4、主键避重

分库分表环境中，由于表数据存在不同库中，主键使用平时的自增将无用武之地，因此需要单独设计全局主键，以避免跨库主键重复问题。

5、公共表

实际业务场景中，针对参数、字典等数据量较小、变动少，而且属于高频联合查询依赖的表，我们会建立为公共表。分库分表环境中，我们可以将这些表在每个数据库中都保存一份，所有对公共表的操作都同时发送到所有分库执行。

二、Sharding-JDBC快速入门

1. 需求说明

本章节使用Sharding-JDBC完成对订单表的水平分表，通过快速入门程序的开发，快速体验Sharding-JDBC的使用方法。
人工创建两张表，t_order_1和t_order_2，这两张表是订单表拆分后的表，通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过Sharding-Jdbc查询数据，根据SQL语句的内容从t_order_1或t_order_2查询数据。

2. 环境搭建

2.1 环境说明

操作系统：Win10

数据库：MySQL-5.7.25
JDK：64位 jdk1.8.0_201
应用框架：spring-boot-2.1.3.RELEASE，Mybatis3.5.0
Sharding-JDBC：sharding-jdbc-spring-boot-starter-4.0.0-RC1

2.2 创建数据库

创建订单库order_db;

CREATE DATABASE order_db CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’；

在order_db中创建t_order_1、t_order_2表

DROP TABLE IF EXISTS t_order_1;
CREATE TABLE t_order_1 (
order_id bigint(20) NOT NULL COMMENT ‘订单id’,
price decimal(10, 2) NOT NULL COMMENT ‘订单价格’,
user_id bigint(20) NOT NULL COMMENT ‘下单用户id’,
status varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘订单状态’,
PRIMARY KEY (order_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS t_order_2;
CREATE TABLE t_order_2 (
order_id bigint(20) NOT NULL COMMENT ‘订单id’,
price decimal(10, 2) NOT NULL COMMENT ‘订单价格’,
user_id bigint(20) NOT NULL COMMENT ‘下单用户id’,
status varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘订单状态’,
PRIMARY KEY (order_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.3 引入maven依赖

引入 sharding-jdbc和SpringBoot整合的Jar包：

 org.apache.shardingsphere sharding‐jdbc‐spring‐boot‐starter 4.0.0‐RC1

3. 编写程序

3.1 分片规则配置

分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策略等。

在application.properties中配置：

server.port=56081
#工程名
spring.application.name = sharding-jdbc-simple-demo
#访问路径
server.servlet.context-path = /sharding-jdbc-simple-demo
spring.http.encoding.enabled = true
spring.http.encoding.charset = UTF-8
spring.http.encoding.force = true
#覆盖重复的bean定义
spring.main.allow-bean-definition-overriding = true
#mybatis驼峰命名
mybatis.configuration.map-underscore-to-camel-case = true
#sharding-jdbc分片规则配置
#数据源，自定义名称m1
spring.shardingsphere.datasource.names = m1
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
# 指定t_order表的数据分布情况，配置数据节点，逻辑表t_order对应的节点是：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，主键为order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
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
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug
logging.level.druid.sql = debug

1.首先定义数据源m1，并对m1进行实际的参数配置。
2.指定t_order表的数据分布情况，分布在m1.t_order_1，m1.t_order_2
3.指定t_order表的主键生成策略为SNOWFLAKE，SNOWFLAKE是一种分布式自增算法，保证id全局唯一
4.定义t_order分片策略，order_id为偶数的数据落在t_order_1，为奇数的落在t_order_2，分表策略的表达式为 t_order_$->{order_id % 2 + 1}

3.2.数据操作

@Mapper
@Component
public interface OrderDao {
    /**
     * 插入订单
     */
    @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);
    /**
     * 根据id列表查询订单
     */
    @Select("")
    List selectOrderbyIds(@Param("orderIds") List orderIds);
}

3.3.测试

①编写单元测试：

@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");
        }
    }
    @Test
    public void testSelectOrderbyIds() {
        List ids = new ArrayList<>();
        ids.add(373897739357913088L);
        ids.add(373897037306920961L);
        List maps = orderDao.selectOrderbyIds(ids);
        System.out.println(maps);
    }
}

②执行testInsertOrder：

通过日志可以发现order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表，达到预期目标。

③执行testSelectOrderbyIds：

通过日志可以发现，根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表检索数据，达到预期目标。

4. 流程分析

通过日志分析，Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿：
（1）解析sql，获取分片键值，在本例中是order_id
（2）Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该往 t_order_1表插数据，为奇数时，往t_order_2插数据。
（3）于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语句。
（4）执行改写后的真实sql语句
（5）将所有真正执行sql的结果进行汇总合并，返回。

三、案例演示

1、水平分表

前面已经介绍过，水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。在Sharding-JDBC快速入门里，我们已经对水平分库进行实现，这里不再重复介绍。

2、水平分库

前面已经介绍过，水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。接下来看一下如何使用Sharding-JDBC实现水平分库，咱们继续对Sharding-JDBC快速入门中的例子进行完善。

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

(2) 分片规则修改

由于数据库拆分了两个，这里需要配置两个数据源。
分库需要配置分库的策略，和分表策略的意义类似，通过分库策略实现数据操作针对分库的数据库进行操作。

# 定义多个数据源 
spring.shardingsphere.datasource.names = m1,m2
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 = roo
# 分库策略，以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}
# 指定t_order表的数据分布情况，配置数据节点，逻辑表t_order对应的节点是：m1.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = m$->{1..2}.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE，主键为order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法
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}

分库策略定义方式如下：

#分库策略database‐strategy，如何将一个逻辑表映射到多个数据源 
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值
#分表策略table‐strategy，如何将一个逻辑表映射为多个实际表 
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值

Sharding-JDBC支持以下几种分片策略：

不管理分库还是分表，策略基本一样。

standard：标准分片策略，对应StandardShardingStrategy。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。 RangeShardingAlgorithm是可选的，用于处理BETWEEN AND分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。

complex：复合分片策略，对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, IN和 BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

inline：行表达式分片策略，对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和 IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为 t_user_0 到 t_user_7 。

hint：Hint分片策略，对应HintShardingStrategy。通过Hint而非SQL解析的方式分片的策略。对于分片字段非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。

none：不分片策略，对应NoneShardingStrategy。不分片的策略。

(3) 插入测试

修改testInsertOrder方法，插入数据中包含不同的user_id

@Test
public void testInsertOrder(){ 
	for (int i = 0 ; i<10; i++){ 
		orderDao.insertOrder(new BigDecimal((i+1)*5),1L,"WAIT_PAY"); 
	}
	for (int i = 0 ; i<10; i++){ 
		orderDao.insertOrder(new BigDecimal((i+1)*10),2L,"WAIT_PAY"); 
	} 
}

执行testInsertOrder:

通过日志可以看出，根据user_id的奇偶不同，数据分别落在了不同数据源，达到目标。

(4) 查询测试

调用快速入门的查询接口进行测试：

List selectOrderbyIds(@Param(“orderIds”)List orderIds);

通过日志发现，sharding-jdbc将sql路由到m1和m2：

问题分析：由于查询语句中并没有使用分片键user_id，所以sharding-jdbc将广播路由到每个数据结点。

下边我们在sql中添加分片键进行查询。

在OrderDao中定义接口：

@Select({""})
    List selectOrderbyUserAndIds(@Param("userId") Integer userId, @Param("orderIds") List orderIds);

编写测试方法：

@Test
public void testSelectOrderbyUserAndIds() {
    List ids = new ArrayList<>();
    ids.add(776166091381538816L);
    // 查询条件中包括分库的键user_id
    int user_id = 1;
    List maps = orderDao.selectOrderbyUserAndIds(user_id, ids);
    System.out.println(maps);
}

执行testSelectOrderbyUserAndIds：

查询条件user_id为1，根据分片策略m$->{user_id % 2 + 1}计算得出m2，此sharding-jdbc将sql路由到m2，见上图日志。

3、垂直分库

前面已经介绍过，垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。接下来看一下如何使用Sharding-JDBC实现垂直分库。

(1) 创建数据库

创建数据库user_db

CREATE DATABASE user_db CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’;

在user_db中创建t_user表

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
user_id BIGINT ( 20 ) NOT NULL COMMENT ‘用户id’,
fullname VARCHAR ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户姓名’,
user_type CHAR ( 1 ) DEFAULT NULL COMMENT ‘用户类型’,
PRIMARY KEY ( user_id ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

(2) 在Sharding-JDBC规则中修改

# 新增m0数据源，对应user_db 
spring.shardingsphere.datasource.names = m0,m1,m2
...
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?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = root
....
# t_user分表策略，固定分配至m0的t_user真实表
# t_user没有分表也要配分片策略，以便后期分表
spring.shardingsphere.sharding.tables.t_user.actual‐data‐nodes = m$‐>{0}.t_user
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

(3) 数据操作

新增UserDao：

@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({""
    })
    List selectUserbyIds(@Param("userIds") List userIds);
}

(4) 测试

新增单元测试方法：

@Test
public void testInsertUser(){
    for (int i = 0 ; i < 10; i++){
        Long id = i + 1L;
        userDao.insertUser(id,"姓名"+ id );
    }
}
@Test
public void testSelectUserbyIds(){
    List userIds = new ArrayList<>();
    userIds.add(1L);
    userIds.add(2L);
    List users = userDao.selectUserbyIds(userIds);
    System.out.println(users);
}

执行testInsertUser：

通过日志可以看出t_user表的数据被落在了m0数据源，达到目标。

执行testSelectUserbyIds：

通过日志可以看出t_user表的查询操作被落在了m0数据源，达到目标。

4、公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如何使用 Sharding-JDBC实现公共表。

(1) 创建数据库

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

CREATE TABLE t_dict ( dict_id bigint(20) NOT NULL COMMENT ‘字典id’,
type varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典类型’,
code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典编码’,
value varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘字典值’,
PRIMARY KEY (dict_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

(2) 在Sharding-JDBC规则中修改

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

(3) 数据操作

新增DictDao：

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

(4) 字典操作测试

新增单元测试方法：

@Test
public void testInsertDict(){
    dictDao.insertDict(3L,"user_type","2","超级管理员");
    dictDao.insertDict(4L,"user_type","3","二级管理员");
}
@Test
public void testDeleteDict(){
    dictDao.deleteDict(3L);
    dictDao.deleteDict(4L);
}

执行testInsertDict：

通过日志可以看出，对t_dict的表的操作被广播至所有数据源。

(5) 字典关联查询测试

字典表已在各各分库存在，各业务表即可和字典表关联查询。

定义用户关联查询dao：

在UserDao中定义：

@Mapper
@Component
public interface UserDao {
    /**
     * 根据id列表查询多个用户
     * @param userIds 用户id列表
     * @return
     */
    @Select({""
    })
    List selectUserInfobyIds(@Param("userIds") List userIds);
}

定义测试方法：

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

执行测试方法，查看日志，成功关联查询字典表：

五、读写分离

1、理解读写分离

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统的性能。

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。

Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用，同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。

接下来，咱们对上面例子中user_db进行读写分离实现。为了实现Sharding-JDBC的读写分离，首先，要进行 mysql的主从同步配置。

2、Mysql主从同步(windows)

2.1. 新增mysql实例

复制原有mysql如：D:\mysql-5.7.25(作为主库) -> D:\mysql-5.7.25-s1(作为从库)，并修改以下从库的my.ini：

[mysqld] #设置3307端口
port = 3307
#设置mysql的安装目录
basedir=D:\mysql‐5.7.25‐s1
#设置mysql数据库的数据的存放目录
datadir=D:\mysql‐5.7.25‐s1\data

然后将从库安装为windows服务，注意配置文件位置：

D:\mysql‐5.7.25‐s1\bin>mysqld install mysqls1 ‐‐defaults‐file=“D:\mysql‐5.7.25‐s1\my.ini”

由于从库是从主库复制过来的，因此里面的数据完全一致，可使用原来的账号、密码登录。

2. 2、修改主、从库的配置文件(my.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.%

重启主库和从库：

net start [主库服务名]
net start [从库服务名mysqls1]

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

3. 授权主从复制专用账号

#切换至主库bin目录，登录主库
mysql ‐h localhost ‐uroot ‐p
#授权主备复制专用账号
GRANT REPLICATION SLAVE ON . TO ‘db_sync’@‘%’ IDENTIFIED BY ‘db_sync’;
#刷新权限 FLUSH PRIVILEGES;
#确认位点记录下文件名以及位点
show master status;

4. 设置从库向主库同步数据、并检查链路

#切换至从库bin目录，登录从库
mysql ‐h localhost ‐P3307 ‐uroot ‐p
#先停止同步 STOP SLAVE;
#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
master_host = ‘localhost’,
master_user = ‘db_sync’,
master_password = ‘db_sync’,
master_log_file = ‘mysql‐bin.000002’,
master_log_pos = 154;
#启动同步 START SLAVE;
#查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功，如果不为Yes，请检查error_log，然后排查相关异常。
show slave status\G
#注意如果之前此备库已有主库指向需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL ‘’;
reset slave all;

最后测试在主库修改数据库，看从库是否能够同步成功。

3、实现sharding-jdbc读写分离

(1) 在Sharding-JDBC规则中修改

# 主库从库逻辑数据源定义 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

#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m0,m1,m2,s0
...
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:3307/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
....

(2) 测试

执行testInsertUser单元测试：

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

通过日志可以看出，所有写操作落入m0数据源。

执行testSelectUserbyIds单元测试：

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

通过日志可以看出，所有写操作落入s0数据源，达到目标。

分类:热门推荐日期:2024-06-25浏览:1评论:0

一、分库分表概述

1、分库分表是什么？

2、分库分表方式(垂直分表)

3、分库分表方式(垂直分库)

4、分库分表方式(水平分库)

5、分库分表方式(水平分表)

6、分库分表带来的问题

二、Sharding-JDBC快速入门

1. 需求说明

2. 环境搭建

3. 编写程序

4. 流程分析

三、案例演示

1、水平分表

2、水平分库

3、垂直分库

4、公共表

五、读写分离

1、理解读写分离

2、Mysql主从同步(windows)

3、实现sharding-jdbc读写分离

数据 分片

相关推荐

Sharding-JDBC详细教程(全网最详细！！)

分类:热门推荐日期:2024-06-25浏览:1评论:0

一、 分库分表概述

1、 分库分表是什么？

2、 分库分表方式(垂直分表)

3、 分库分表方式(垂直分库)

4、 分库分表方式(水平分库)

5、 分库分表方式(水平分表)

6、 分库分表带来的问题

二、Sharding-JDBC快速入门

1. 需求说明

2. 环境搭建

3. 编写程序

4. 流程分析

三、案例演示

1、水平分表

2、水平分库

3、垂直分库

4、公共表

五、读写分离

1、理解读写分离

2、Mysql主从同步(windows)

3、实现sharding-jdbc读写分离

数据分片

相关推荐

一、分库分表概述

1、分库分表是什么？

2、分库分表方式(垂直分表)

3、分库分表方式(垂直分库)

4、分库分表方式(水平分库)

5、分库分表方式(水平分表)

6、分库分表带来的问题