目录
一、ShardingSphere
1.1 简介
Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈,它由 JDBC、Proxy 和 Sidecar(规划中)这 3 款相互独立,却又能够混合部署配合使用的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能,可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。
- 一套开源的分布式数据库中间件解决方案。
- 有三个产品:JDBC、Proxy、Sidecar。
1.2 什么是分库分表
当我们使用读写分离、索引、缓存后,数据库的压力还是很大的时候,这就需要使用到数据库拆分了。
数据库拆分简单来说,就是指通过某种特定的条件,按照某个维度,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面以达到分散单库(主机)负载的效果。
1.3 分库分表之垂直拆分
专库专用。一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同的数据库上面,这样也就将数据或者说压力分担到不同的库上面。如下图:
优点:
- 拆分后业务清晰,拆分规则明确。
- 系统之间整合或扩展容易。
- 数据维护简单。
缺点:
- 部分业务表无法 join,只能通过接口方式解决,提高了系统复杂度。
- 受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高。
- 事务处理复杂。
1.4 分库分表之水平切分
垂直拆分后遇到单机瓶颈,可以使用水平拆分。相对于垂直拆分的区别是:垂直拆分是把不同的表拆到不同的数据库中,而水平拆分是把同一个表拆到不同的数据库中。
相对于垂直拆分,水平拆分不是将表的数据做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,主要有分表,分库两种模式。 如下图:
优点:
- 不存在单库大数据,高并发的性能瓶颈。
- 对应用透明,应用端改造较少。
- 按照合理拆分规则拆分,join 操作基本避免跨库。
- 提高了系统的稳定性跟负载能力。
缺点:
- 拆分规则难以抽象。
- 分片事务一致性难以解决。
- 数据多次扩展难度跟维护量极大。
- 跨库 join 性能较差。
二、 ShardingSphere-JDBC
2.1 简介
定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库,以 jar 包形式提供服务,无需额外部署和依赖,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
- 适用于任何基于 JDBC 的 ORM 框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
- 支持任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP 等。
- 支持任意实现 JDBC 规范的数据库,目前支持 MySQL,Oracle,SQLServer,PostgreSQL 以及任何遵循 SQL92 标准的数据库。
-
需要注意的是,分库分表并不是由 ShardingSphere-JDBC 来做,它是用来负责操作已经分完之后的 CRUD 操作。
2.2 Sharding-JDBC 分表实操
环境使用:Springboot 2.2.11 + MybatisPlus + ShardingSphere-JDBC 4.0.0-RC1 + Druid 连接池
具体Maven依赖:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
2.3 实现水平分表
准备工作:
- 创建数据库 course_db
- 创建表 course_1 、 course_2
- 约定规则:如果添加的课程 id 为偶数添加到
course_1 中,奇数添加到 course_2 中。
SQL 如下:
create database course_db;
use course_db;
create table course_1 (
cid bigint(20) primary key ,
cname varchar(50) not null,
user_id bigint(20) not null ,
cstatus varchar(10) not null
) engine = InnoDB;
create table course_2 (
cid bigint(20) primary key ,
cname varchar(50) not null,
user_id bigint(20) not null ,
cstatus varchar(10) not null
) engine = InnoDB;
配置对应实体类以及Mapper
@Data
public class Course {
private Long cid;
private String cname;
private Long userId;
private String status;
}
mapper
@Repository
@Mapper
public interface CourseMapper extends BaseMapper<Course> {
}
启动类配置 MapperScan
@SpringBootApplication
@MapperScan(value = "com.beita.shardingspherejdbc.mapper")
public class ShardingsphereApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingsphereApplication.class, args);
}
}
2.3.1 配置 Sharding-JDBC 分片策略
application.properties 内容:
# sharding-jdbc 水平分表策略
# 配置数据源,给数据源起别名
spring.shardingsphere.datasource.names=m1
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
# 配置数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
# 指定course表分布的情况,配置表在哪个数据库里,表的名称都是什么 m1.course_1,m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 配置分表策略 约定 cid 值偶数添加到 course_1 表,如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
测试代码运行
@Test
public void addCourse() {
for (int i = 0; i < 10; i++) {
Course course = new Course();
//cid由我们设置的策略,雪花算法进行生成
course.setCname("Java"+i);
course.setUserId(100L);
course.setStatus("Normal"+i);
courseMapper.insert(course);
}
}
查询数据库中的某一条记录,可以看到查询的表是正确的。
2.4 实现水平分库
准备工作:
- 创建两个数据库,edu_db_1、edu_db_2。
- 每个库中包含:course_1、course_2。
- 数据库规则:userid 为偶数添加到 edu_db_1 库,奇数添加到 edu_db_2。
- 表规则:如果添加的 cid 为偶数添加到 course_1 中,奇数添加到 course_2 中。
创建数据库和表结构
create database edu_db_1;
create database edu_db_2;
use edu_db_1;
create table course_1 (
`cid` bigint(20) primary key,
`cname` varchar(50) not null,
`user_id` bigint(20) not null,
`status` varchar(10) not null
);
create table course_2 (
`cid` bigint(20) primary key,
`cname` varchar(50) not null,
`user_id` bigint(20) not null,
`status` varchar(10) not null
);
use edu_db_2;
create table course_1 (
`cid` bigint(20) primary key,
`cname` varchar(50) not null,
`user_id` bigint(20) not null,
`status` varchar(10) not null
);
create table course_2 (
`cid` bigint(20) primary key,
`cname` varchar(50) not null,
`user_id` bigint(20) not null,
`status` varchar(10) not null
);
配置分片策略
application.properties内容:
# sharding-jdbc 水平分库分表策略
# 配置数据源,给数据源起别名
# 水平分库需要配置多个数据库
spring.shardingsphere.datasource.names=m1,m2
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
# 配置第一个数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
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.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
# 指定数据库分布的情况和数据表分布的情况
# m1 m2 course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 指定分库策略 约定 user_id 值偶数添加到 m1 库,如果 user_id 是奇数添加到 m2 库
# 默认写法(所有的表的user_id)
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定分表策略 约定 cid 值偶数添加到 course_1 表,如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
测试代码运行
@Test
public void addCourse() {
Course course = new Course();
//cid由我们设置的策略,雪花算法进行生成
course.setCname("python");
//分库根据user_id
course.setUserId(100L);
course.setStatus("Normal");
courseMapper.insert(course);
course.setCname("c++");
course.setUserId(111L);
courseMapper.insert(course);
}
对应的我们 python 的 userId 为偶数所以添加到 edu_db_1 库中,而 c++是奇数所以添加到 edu_db_2 库中。
运行结果
看下对应的数据库数据,没有问题。
2.4 实现垂直分库
准备工作:
我们再额外创建一个 user_db 数据库。当我们查询用户信息就去 user_db,课程信息就去 edu_db_1、edu_db_2。
创建数据库和表结构
create database user_db;
use user_db;
create table t_user(
`user_id` bigint(20) primary key,
`username` varchar(100) not null,
`status` varchar(50) not null
);
配置对应实体类和 Mapper
@Data
@TableName("t_user")
public class User {
private Long userId;
private String username;
private String status;
}
mapper
@Repository
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
配置分片策略
application.properties内容:
# sharding-jdbc 水平分库分表策略
# 配置数据源,给数据源起别名
# 水平分库需要配置多个数据库
# m0为用户数据库
spring.shardingsphere.datasource.names=m1,m2,m0
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
# 配置第一个数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
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.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
# 配置user数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
# 配置user_db数据库里面t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m0.t_user
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定分表策略
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
# 指定数据库分布的情况和数据表分布的情况
# m1 m2 course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 指定分库策略 约定 user_id 值偶数添加到 m1 库,如果 user_id 是奇数添加到 m2 库
# 默认写法(所有的表的user_id)
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定分表策略 约定 cid 值偶数添加到 course_1 表,如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
测试代码运行
@Test
public void addUser(){
User user = new User();
user.setUsername("Jack");
user.setStatus("Normal");
userMapper.insert(user);
}
@Test
public void findUser() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_id", 601890366953619457L);
userMapper.selectOne(wrapper);
}
运行结果如下
2.5 公共表
概念
- 存储固定数据的表,表数据很少发生变化,查询时经常要进行关联。
- 在每个数据库中都创建出相同结构公共表。
- 操作公共表时,同时操作添加了公共表的数据库中的公共表,添加记录时,同时添加,删除时,同时删除。
配置公共表的实体类和 mapper
@Data
@TableName("t_dict")
public class Dict {
private Long dictId;
private String status;
private String value;
}
@Repository
@Mapper
public interface DictMapper extends BaseMapper<Dict> {
}
配置分片策略
# sharding-jdbc 水平分库分表策略
# 配置数据源,给数据源起别名
# 水平分库需要配置多个数据库
# m0为用户数据库
spring.shardingsphere.datasource.names=m1,m2,m0
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
# 配置第一个数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
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.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
# 配置user数据源的具体内容,包含连接池,驱动,地址,用户名,密码
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
# 配置user_db数据库里面t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m0.t_user
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定分表策略
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
# 指定数据库分布的情况和数据表分布的情况
# m1 m2 course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
# 指定 course 表里面主键 cid 的生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 指定分库策略 约定 user_id 值偶数添加到 m1 库,如果 user_id 是奇数添加到 m2 库
# 默认写法(所有的表的user_id)
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定只有course表的user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 指定分表策略 约定 cid 值偶数添加到 course_1 表,如果 cid 是奇数添加到 course_2 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
# 公共表配置
spring.shardingsphere.sharding.broadcast-tables=t_dict
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
测试代码运行
@Autowired
private DictMapper dictMapper;
@Test
public void addDict() {
Dict dict = new Dict();
dict.setStatus("Normal");
dict.setValue("启用");
dictMapper.insert(dict);
}
@Test
public void deleteDict() {
QueryWrapper<Dict> wrapper = new QueryWrapper<>();
wrapper.eq("dict_id", 536486065947541505L);
dictMapper.delete(wrapper);
}
运行结果如下
2.6 读写分离
2.6.1 什么是读写分离
了解读写分离前,我们先了解下什么是主从复制。
主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是准实时的业务数据库。一台服务器充当主服务器,而另外一台服务器充当从服务器。
2.6.2 主从复制原理
主库将变更写入 binlog 日志,然后从库连接到主库之后,从库有一个 IO 线程,将主库的 binlog 日志拷贝到自己本地,写入一个 relay 中继日志(relay log)中。接着从库中有一个 SQL 线程会从中继日志读取 binlog,然后执行 binlog 日志中的内容,也就是在自己本地再次执行一遍 SQL 语句,从而使从服务器和主服务器的数据保持一致。
也就是说:从库会生成两个线程,一个 I/O 线程,一个 SQL 线程; I/O 线程会去请求主库的 binlog,并将得到的 binlog 写到本地的 relay-log(中继日志)文件中; 主库会生成一个 log dump 线程, 用来给从库 I/O 线程传 binlog; SQL 线程,会读取 relay log 文件中的日志,并解析成 sql 语句逐一执行。
注意:从库同步主库数据的过程是串行化的,也就是说主库上并行的操作,在从库上会串行执行。
由于从库从主库拷贝日志以及串行执行 SQL 的特点,在高并发场景下,从库的数据是有延时的。
在实际运用中,时常会出现这样的情况,主库的数据已经有了,可从库还是读取不到,可能要过几十毫秒,甚至几百毫秒才能读取到。
- 半同步复制:解决主库数据丢失问题。也叫 semi-sync 复制,指的就是主库写入 binlog 日志之后,就会强制将数据立即同步到从库,从库将日志写入自己本地的 relay log 之后,接着会返回一个 ack 给主库,主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
- 并行复制:解决从库复制延迟的问题。指的是从库开启多个线程,并行读取 relay log 中不同库的日志,然后并行存放不同库的日志,这是库级别的并行。
主从同步延迟问题
MySQL 可以通过 MySQL 命令 show slave status 获知当前是否主从同步正常工作。
另外一个重要指标就是 Seconds_Behind_Master,根据输出的 Seconds_Behind_Master 参数的值来判断:
- NULL,表示 io_thread 或是 sql_thread 有任何一个发生故障。
- 0,表示主从复制良好。
- 正值,表示主从已经出现延时,数字越大表示从库延迟越严重。
导致主从同步延迟情况
- 主库的从库太多,导致复制延迟。
- 从库硬件比主库差,导致复制延迟。
- 慢 SQL 语句过多。
- 主从复制的设计问题,例如主从复制单线程,如果主库写并发太大,来不及传送到从库,就会导致延迟。Mysql5.7
之后可以支持多线程复制。设置参数slave_parallel_workers>0
和slave_parallel_type=’LOGICAL_CLOCK’。 - 网络延迟。
主从同步解决方案
- 使用 PXC 架构
- 避免一些无用的 IO 消耗,可以上 SSD。
- IO 调度要选择 deadline 模式。
- 适当调整 buffer pool 的大小。
- 避免让数据库进行各种大量运算,数据库只是用来存储数据的,让应用端多分担些压力,或者可以通过缓存来完成。
说到底读写分离就是主库进行写操作,从库进行读操作。具体可以搭配一主一从、一主多从、多主多从。根据业务场景来进行选择。
2.6.3 搭建一主一从 MySQL 环境
我使用的是 Centos7 虚拟机,使用docker搭建mysql环境。
这里不讲如何搭建 docker 环境了。
首先安装启动主MySQL服务:
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
启动从MySQL服务:
docker run -p 3307:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
#进入主服务容器内部
docker exec -it mysql /bin/bash
#切换到/etc/mysql目录下
cd /etc/mysql
#然后vim my.cnf对my.cnf进行编辑,需要在容器内部安装vim
apt-get update
apt-get install vim
#然后vim my.cnf配置主服务的配置
[mysqld]
## 同一局域网内注意要唯一
server-id=100
## 开启二进制日志功能,可以随便取(关键)
log-bin=mysql-bin
#重启主服务
docker restart mysql
以上完毕之后我们登录主服务器的 MySQL,用navicat远程连接主库。
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 解决办法 MySQL
select version(),
@@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
#新建一个用户专门用来同步master
CREATE USER 'backup'@'%' IDENTIFIED BY '123456';
#给backup用户分配备份的权限
GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';
#主库配置完成。查看主库状态:
show master status;
记住查询结果,后面会用。
File: mysql-bin.000001
Position: 1083
配置从库
#进入从服务容器内部
docker exec -it mysql-slave /bin/bash
#切换到/etc/mysql目录下
cd /etc/mysql
#然后vim my.cnf对my.cnf进行编辑,需要在容器内部安装vim
apt-get update
apt-get install vim
#然后vim my.cnf配置主服务的配置
#设置从mysql的id
server-id = 2
#启用中继日志
relay-log = mysql-relay
#重启主服务
docker restart mysql-slave
以上完毕之后我们登录从服务器的 MySQL,用navicat远程连接。
从库通过IO线程连接master,所以需要指定master的信息,包括host, port, user, password
change master to master_host='169.254.188.10',
master_port=3306,
master_user='backup',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=1083;
start slave;
查看从库状态,
show slave status;
则说明连接成功。
测试
在master上操作,更改都会显示在slave上。
2.6.4 Sharding-JDBC 实现读写分离
Sharding-JDBC 实现读写分离则是根据sql 语句语义分析,当 sql 语句有 insert、update、delete 时,Sharding-JDBC 就把这次操作在主数据库上执行;当 sql 语句有 select 时,就会把这次操作在从数据库上执行,从而实现读写分离过程。
但 Sharding-JDBC 并不会做数据同步,数据同步是配置 MySQL 后由 MySQL 自己完成的。
搭建环境成功后我们在主库和从库上都建库建表:
create database user_db;
use user_db;
create table t_user(
`user_id` bigint(20) primary key,
`username` varchar(100) not null,
`status` varchar(50) not null
);
配置读写分离策略
application.properties:
# 配置数据源,给数据源起别名
# m0为用户数据库
spring.shardingsphere.datasource.names=m0,s0
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#user_db 主服务器
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://169.254.188.10:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456
#user_db 从服务器
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://169.254.188.10:3307/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=123456
# 主库从库逻辑数据源定义 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
# 配置user_db数据库里面t_user 专库专表
#spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m0.t_user
# t_user 分表策略,固定分配至 ds0 的 t_user 真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
# 配置主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定分表策略
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
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
测试代码运行
@Autowired
private UserMapper userMapper;
@Test
public void addUser(){
User user = new User();
user.setUsername("Jack");
user.setStatus("Normal");
userMapper.insert(user);
}
@Test
public void findUser() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_id", 536553906142969857L);
userMapper.selectOne(wrapper);
}
m0是主库,s0是从库,可以看到添加记录走的是主库,查询走的是从库。