目录

sharding-jdbc读写分离

理解读写分离

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

http://img.cana.space/picStore/20201123111454.png

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

http://img.cana.space/picStore/20201123111512.png

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

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

http://img.cana.space/picStore/20201123111633.png

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

http://img.cana.space/picStore/20201123111833.png

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

mysql主从同步

mysql一主一从搭建

主库mysqld.cnf

 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
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#********** 配置master *******#

# 主服务器ID 必须唯一,一般配置ip尾号,比如主库ip 182.30.0.7
server-id=7

# 开启及设置二进制日志文件名称,会存放在/var/lib/mysql/mysql-bin
log_bin=mysql-bin

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

# 不需要同步的数据库
binlog-ignore-db=mysql    
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys

# 设置logbin格式
binlog_format=statement # binlog日志格式,mysql默认采用statement,建议使用mixed

从库

1
2
3
4
...
#********** 配置slave *******#
server-id=8
relay-log=mysql-relay 

重启两台服务器,让配置生效

1
$ dcp -f mysql-1m-1s.yml restart

主服务器,,授权主从复制专用账号

1
2
3
4
5
6
7
8
-- 创建从机访问用户
CREATE USER 's1'@'182.30.0.8' IDENTIFIED BY '333';
-- 授予s1用户主从同步权限
GRANT REPLICATION SLAVE ON *.* TO 's1'@'182.30.0.8';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态,见下图
SHOW MASTER STATUS;

从服务器,设置从库向主库同步数据、并检查链路

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 从机创建连接主服务器的IP,用户,密码以及日志文件(上图File)和位置(上图Position);
CHANGE MASTER TO MASTER_HOST = '182.30.0.7',
MASTER_USER = 's1',
MASTER_PASSWORD = '333',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 753;
   
-- 启动主从复制
START SLAVE;
   
-- 查看从机状态,见下图
SHOW SLAVE STATUS;

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 创建数据库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;

20201123121358

实现sharding-jdbc读写分离

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
# 数据源名称,m0和s0互为主从
spring.shardingsphere.datasource.names=m0,s0,m1,m2
# 给m0数据源设置连接属性
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:3307/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=333
# 给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:3308/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=333
...
# 主从库逻辑数据源定义,逻辑数据源名称可以随便写,但要始终保持一致
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表分表策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user

完整配置(包括之前的分库分表配置)

 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
53
54
55
56
57
58
59
60
61
62
server.port=8001
spring.application.name=sharding-jdbc-simple
# 使用bean定义覆盖,sharding-jdbc的datasource覆盖druid定义的datasource,如果不覆盖会报
# Invalid bean definition with name 'dataSource' defined in class path resource
spring.main.allow-bean-definition-overriding = true
# 以下是分片规则配置
# 定义数据源
# 数据源名称,m0和s0互为主从
spring.shardingsphere.datasource.names=m0,s0,m1,m2
# 给m0数据源设置连接属性
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:3307/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=333
# 给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:3308/user_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=333
# 给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_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=333
# 给m2数据源设置连接属性
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&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=333

# 主从库逻辑数据源定义,逻辑数据源名称可以随便写,但要始终保持一致
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表分表策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user

# 指定分库策略
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表的数据分布情况,配置数据节点m1_t_order_1,m1_t_order_2,m2_t_order_1,m2_t_order_1
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.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表的分片策略,分片策略包括分片键和分片算法
# 分片键
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_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

测试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
@Test
    public void testInsertUser() {
        for (int i = 0; i < 10; i++) {
            Long id = i + 1L;
            userMapper.insertUser(id, "姓名" + id);
        }
    }

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

执行testInsertUser

http://img.cana.space/picStore/20201123123736.png

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

执行testSelectUserByIds

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