数据库读写分离(主从复制)和分库分表详解

颜书凌

已于 2024-04-18 09:38:14 修改

阅读量3.2k

点赞数 47

文章标签：数据库

于 2024-03-31 11:52:13 首次发布

本文链接：https://blog.csdn.net/2302_77971734/article/details/137191869

版权

数据库读写分离(主从复制)和分库分表

在文章开头先抛几个问题：

什么是读写分离？解决了什么问题？使用时应该注意什么？会出现什么问题？

什么是主从复制？他有什么缺点？

我们为什么要分库分表？什么时候才需要分库分表呢？我们的评判标准是什么？

这些问题你都搞清楚了吗？相信看完这篇文章你一定会有答案。

什么是读写分离：

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能。

如何实现读写分离呢？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
系统将写请求交给主数据库处理，读请求交给从数据库处理

落实到项目本身的话，常用的方式有两种：

1. 代理方式

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

提供类似功能的中间件有 MySQL Router（官方， MySQL Proxy 的替代方案）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。

关于 MySQL Router 多提一点：在 MySQL 8.2 的版本中，MySQL Router 能自动分辨对数据库读写/操作并把这些操作路由到正确的实例上。这是一项有价值的功能，可以优化数据库性能和可扩展性，而无需在应用程序中进行任何更改

2. 组件方式

在这种方式中，我们可以通过引入第三方组件来帮助我们读写请求。

这也是我比较推荐的一种方式。这种方式目前在各种互联网公司中用的最多的，相关的实际的案例也非常多。如果你要采用这种方式的话，推荐使用 sharding-jdbc ，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本。

主从复制原理是什么？

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

主库将数据库中数据的变化写入到 binlog
从库连接主库
从库会创建一个 I/O 线程向主库请求更新的 binlog
主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

你一般看到 binlog 就要想到主从复制。当然除了主从复制之外，binlog 还能帮助我们实现数据恢复。

缺点：

1.资源浪费，数据做了冗余

2.主库和从库的数据存在延迟。比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题，这也就是我们经常说的 主从同步延迟 。

3.网络不好的情况出现数据延迟,会出现主从数据库，数据不一致问题

4.单表数据量超过4000w，数据还在增长还需要考虑分表

分库分表

前言

其实分库分表是牵扯到高并发的，因为分库分表无非来说就是为了支撑高并发、数据量大的问题。尤其进入稍微大一点的公司或者互联网公司，这些都是必须掌握的！

场景

假如一个新兴公司，刚开始时，注册用户就40W，每天活跃1W，每天单表数据量1000，高峰期每秒的并发就10，这种一般的单表单库操作就可以搞定。

但是，当用户不断增加、日活跃用户增加、单表数据量增加、高峰期每秒增加到一定数量时，单表单库性能就存在很大的问题了。

为什么要分库分表？

数据库出现性能瓶颈。用大白话来说就是数据库快扛不住了。

当单库或单表的数据量过大时，数据库的处理能力会受到限制，导致查询和写入操作变慢。分库分表通过将数据分散到多个数据库和表中，可以降低单库或单表的数据量，从而提高数据库的处理能力。

在高并发场景下，分库分表可以分散并发请求的压力，避免单个数据库或表成为性能瓶颈。

对于某些特定的查询或操作，分库分表可以优化查询性能，例如，通过将经常一起查询的数据放在同一个表中，可以提高查询效率。

此外，分库分表还有助于提高数据库的可靠性和扩展性。在分布式系统中，当某个数据库节点发生故障时，由于数据已经被分散到多个节点，整体系统仍然可以正常运行

什么是分库分表？

分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。
分表：从单张表拆分成多张表的过程，将数据散落在多张表内。

分为两种:

垂直分表:垂直分表在日常开发和设计中比较常见,通俗的说法叫做“大表拆小表" ,拆分是基于关系型数据库中的“列” (字段)进行的。通常情况,某个表中的字段比较多,可以新建立一张“扩展表”,将不经常使用或者长度较大的字段拆分出去放到“扩展表”中。

水平分表：水平分表:水平分表也称为横向分表,比较容易理解，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行哈希和取模后拆分。

总结

垂直分表，可以理解为按列分表，如果一个表的字段太多了，可以按照使用频率分成不同的表，优化查询性能（就不用一次查询出所有的字段）。比如商品表可以分为商品类型表，商品详情表，商品促销表等等

垂直分库，为了减轻单个数据库压力，我们可以按照业务类型，拆分成多个数据库，比如分布式架构，不同的模块可以有不同的数据库

水平分表，可以理解为按行分表，如果一个表的数据有千万行，查询性能太低，可以拆分成10张小表，每张表保存一百万行数据【范围法，hash算法，雪花算法进行水平分表】

水平分库，我们做了水平分表后，表数量太多了也会影响数据库查询效率，我们可以将这些表分到多个数据库中。

什么时候才需要分库分表呢?

当数据库出现性能瓶颈我们就要考虑分库分表了，对外表现有几个方面：

大量请求阻塞在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态。
SQL 操作变慢如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描，这个查询耗时会非常久。
存储出现问题业务量剧增，单库数据量越来越大，给存储造成巨大压力。

那我们如何进行分库分表呢？分库分表有没有什么比较推荐的方案？

**分库分表框架**
1.TDDL（阿里的，收费的）
2.mycat(是一个第三方的数据库中间件，学习成本比较高，一般需要单独招一个人去维护)
3.shareding-jdbc(java语言开发，比较轻量级)

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。

另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

作者是比较推荐使用sharding-jdbc这个框架的，但是还是要根据实际开发场景和业务需求来选择的，没有绝对的好与坏

下面跟作者一起来看看什么是sharding-jdbc，以及如何使用吧

Sharding-JDBC

一、Sharding-JDBC是什么

定位为轻量级 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 标准的数据库。
Sharding-JDBC直接封装JDBC API，可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零

二、Sharding-JDBC的不足

主要针对于分表，分库对于开发的影响较低。

1.增加了开发的难度，如如何使用分布式id，如何进行调优减少路由表的数目。如何写分库分表的算法等。

2.对于跨表的查询，有时反而会降低性能，而且调试困难。

3.对部分sql语法支持不太好。如分页越是靠后的Limit分页效率就会越低，也越浪费内存。

4.对于非分片建的查询会扫描所有表，系统性能不高。

5.对于设计到统计相关的如group by等支持得不够友好

三、Sharding-JDBC的使用

**分库分表的规则**
1.通过id做hash取模的方式划分 order_0,order_1,order_2,（扩展性比较差）
2.根据时间区间分表 order_202301, order_202302, order_202303(扩展性比较好)
作者这里使用的是通过id做hash取模的方式进行分表

首先我们要导入shardingsphere的依赖

注！！：版本不一样的小伙伴要注意了，如果版本不一样，一定要寻找合适的版本

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.atguigu.gulimall.oms</groupId>
    <artifactId>gmall-oms</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>gmall-oms</name>
    <description>订单服务</description>
    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Greenwich.SR3</spring-cloud.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>

        <!-- sharding sphere -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>


        <dependency>
            <groupId>com.atguigu</groupId>
            <artifactId>gmall-common</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-amqp</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- 模板引擎 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- 引入redis -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
            <version>2.1.0.RELEASE</version>
        </dependency>


        <dependency>
            <groupId>io.seata</groupId>
            <artifactId>seata-all</artifactId>
            <version>0.9.0</version>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

然后加入配置(作者使用的是application.properties文件)

# 显示sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
# 下面是sharding sphere 配置
spring.shardingsphere.datasource.names=oms
# 主数据源
spring.shardingsphere.datasource.oms.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.oms.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.oms.url=jdbc:mysql://服务器IP:3306/数据库?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.oms.username=用户名
spring.shardingsphere.datasource.oms.password=密码

# 自定义复杂分片算法--根据什么来进行分片
spring.shardingsphere.sharding.tables.oms_order.table-strategy.complex.sharding-columns=user_id,order_sn
spring.shardingsphere.sharding.tables.oms_order.table-strategy.complex.algorithm-class-name=com.atguigu.gulimall.oms.service.impl.OrderComplexKeysShardingAlgorithm
spring.shardingsphere.sharding.tables.oms_order.actual-data-nodes=oms.oms_order_$->{0..9}
# 配置生成主键-雪花算法生成主键
spring.shardingsphere.sharding.tables.oms_order.key-generator.column=id
spring.shardingsphere.sharding.tables.oms_order.key-generator.type=SNOWFLAKE

解释一下

四、这里提到了分片，那么什么是分片呢？

数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。数据分片的有效手段是对关系型数据库进行分库和分表。分库和分表均可以有效的避免由数据量超过可承受阈值而产生的查询瓶颈

配置文件中分片，属于行表达式分片，实际业务中，可以自己实现官方的接口，实现自己业务需要的分库、分表算法，具体实现的4个接口参见分片。
shardingsphere有四种分片算法，因此shardingsphere提供了4种类型的接口，在sharding-core-api模块下的org.apache.shardingsphere.api.sharding包内，类名为PreciseShardingAlgorithm（精准分片）、RangeShardingAlgorithm（范围分片）、HintShardingAlgorithm（Hint分片）、ComplexKeysShardingAlgorithm（复杂分片）。
shardingsphere分片策略：包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。

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

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

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

(4)Hint分片策略：对应HintShardingStrategy。通过Hint而非SQL解析的方式分片的策略。

(5)不分片策略：对应NoneShardingStrategy。不分片的策略。

我们要写一个实现类，制定sharding-jdbc的分片组合策略

public class OrderComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        //collection,分表的所有表名称
        //complexKeysShardingValue，组合分片的键的参数
        System.out.println("collection:"+ JSON.toJSONString(collection));
        System.out.println("complexKeysShardingValue:"+JSON.toJSONString(complexKeysShardingValue));
        List<Long> userList = (List<Long>)complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("user_id");
        List<String> orderList = (List<String>)complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("order_sn");
        if(!CollectionUtils.isEmpty(userList)){
            Long userId = userList.get(0);
            Integer mode = Math.abs(userId.hashCode())%10;
            List list = Lists.newArrayList("oms_order_"+mode);
            return list;
        }

        if(!CollectionUtils.isEmpty(orderList)){
            String orderSn = orderList.get(0);
            String userStr = orderSn.substring(10);
            Long userId = Long.parseLong(userStr);
            Integer mode = Math.abs(userId.hashCode())%10;
            List list = Lists.newArrayList("oms_order_"+mode);
            return list;
        }

        //orderSN = 143q42424324324+000000001
        //complexKeysShardingValue.getColumnNameAndShardingValuesMap().get()
        //路由的表
        List list = Lists.newArrayList("oms_order");
        return list;
    }

}

订单号生成的我们要采用比较特殊的方式来生成，将来用户需要通过订单号来查询的时候，要定位到是那张表里面(作者是通过截取的方式来获取到用户的Id)

 public static String getOrderSn(Long userId){

  String orderSn = IdUtil.getSnowflake(1,1).nextIdStr().substring(8)+StringUtils.leftPad(String.valueOf(userId),9,"0");
  return orderSn;
 }

下面，我们来使用添加和查询的方式来测试一下，我们所制定的分片策略是否成功，生效

我们来调用这个方法测试一下

可以看到返回的结果是成功了，我们来查看日志，看看这条数据最后进入了那张表

可以看到，我们往oms_order_2这张表插入了这条数据

在通过查询的方式，我们来检测一下

可以看到通过user_id和order_sn都可以查询到数据，

也可以看到他是去了那张表查询数据，如果我们传入的参数没有user_id和order_sn

他就不会走这个策略，而是把所有的表都查询一遍，然后把结果合并然后返回。

分库分表会带来什么问题呢？

记住，你在公司做的任何技术决策，不光是要考虑这个技术能不能满足我们的要求，是否适合当前业务场景，还要重点考虑其带来的成本。

引入分库分表之后，会给系统带来什么挑战呢？

join 操作 ：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。
事务问题 ：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。
分布式 id ：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。
……

另外，引入分库分表之后，一般需要 DBA 的参与，同时还需要更多的数据库服务器，这些都属于成本。

各位大哥都看到这里，是否可以给小弟来一个暴击三连呢，跪谢！！