分库分表——NWU_LK

最新推荐文章于 2024-07-12 14:26:59 发布

NWU_LK

最新推荐文章于 2024-07-12 14:26:59 发布

阅读量189

点赞数 1

分类专栏：分布式数据库微服务文章标签：数据库 mysql

本文链接：https://blog.csdn.net/NWU_LK/article/details/109298494

版权

微服务同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

分布式

7 篇文章 0 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

分库分表简介

为什么分库分表

当数据量达到一定程度，当扩充硬件（加大CPU或者加到内存）或者优化数据库表结构优化效果不明显时，就需要进行分库分表，分库分表就是为了解决数据库的数据量过大而导致的性能降低的问题

垂直分库

按照业务将数据库分开，比如将商品表放到一个数据库，用户表放到用户数据库，该操作的优点是：

降低业务层的耦合，业务清晰
能对不同业务的数据进行分级管理、维护、监控和拓展
高并发场景下，垂直分库可以降低单机硬件的瓶颈

水平分库

将原本一个数据库中的数据分到两个数据库中，每个库的数据表的结构是完全一样的，该操作的优点是：

解决了单库大数据，高并发的性能场景
提高系统的稳定性和可靠性

垂直分表

按照字段的查找频次将表分成若干子表，每个子表存储其中一部分字段。该操作的优点就是：

避免IO争抢，减少锁表的几率。比如讲商品详情和商品名称放到两个表里，那么查询商品名称和查询商品详情的这俩操作互不影响
充分发挥热门数据的查询效率，减少大字段对整个查询的影响。大字段的查询效率比较低，首先因为数据量大，因此IO的成本就大，其次因为数据库查询时，单页内行数越多越好，因为这样可以减少磁盘IO，但是大字段使得一页显示的数据变少，因此查询的效率降低

通常采用以下原则：

将不经常使用的字段放到一张表
将大字段（比如blob、text）放到一张表
将热门字段放到一个表

水平分表

在一个数据库内对数据表进行拆分，将一个表中的数据分布到多个表中，这些表的结构完全相同，该操作的优点是：

优化了单表操作数据量大的问题
避免IO争抢，并减少锁表的几率

何时分库分表

一般来说，在业务设计阶段就需要进行垂直分库和垂直分表，数据及访问压力不是特别大的时候，首先要考虑缓存、读写分离、索引等技术，若数据量极大时再考虑水平分库分表。

分库分表带来的问题

分库会产生事务的一致性问题，即分布式事务问题
跨节点关联查询，无法像单一数据库一样进行联表查询
跨节点分页、排序。
主键重复问题，因为水平分表之后每个表的ID都是自增的话，那么会产生主键重复
公共表，即那些经常用于联表查询并且数据量比较小的表，解决方案可以是每个数据库中都建立公共表

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

JDBC在单表的情况下要比sharding-JDBC快4%~7%左右，但是在分库分表的情况下，sharding-JDBC的速度接近JDBC的2倍

Sharding JDBC快速入门

环境搭建

创建数据库order，并在其中创建两张表tb_order_1和tb_order_2，表结构如下
创建springboot项目，并引入依赖

<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!--mybatis 持久层-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.2</version>
</dependency>
<!--druid数据源-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.22</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

修改application.yml配置文件

server:
  port: 8001
  servlet:
    context-path: /sharding-jdbc-application
spring:
  application:
    name: sharding-jdbc-application
  shardingsphere:
    datasource:
      # 配置数据源的名字，随便起
      names: m1
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC
        username: root
        password: 123456
    sharding:
      tables:
        # 配置逻辑表的名称
        tb_order:
          # 配置数据节点，指定数据库中表的名称
          actual-data-nodes: m1.tb_order_$->{1..2}
          #配置主键生成策略
          key-generator:
            column: order_id
            type: SNOWFLAKE
          # 配置分片算法
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: tb_order_$->{order_id % 2 + 1}
    #打印sql
    props:
      sql:
        show: true

mybatis:
  type-aliases-package: com.lk.sharding.entity
  mapper-locations: classpath:mapper/*.xml

创建dao层

@Component
@Mapper
public interface OrderDao {
    @Insert(value = "insert into tb_order(price,user_id,status) values(#{price},#{user_id},#{status})")
    void insert(@Param("price")BigDecimal price,@Param("user_id") Long user_id,@Param("status") String status);

    @Select("<script>"
            + "SELECT * FROM tb_order WHERE order_id IN "
            + "<foreach item='orderId' collection='orderIds'  open='(' separator=',' close=')'>"
            + "#{orderId}"
            + "</foreach>"
            + "</script>")
    List<Map> getByOrderIds(@Param("orderIds") List<Long> orderIds);

    @Select(value = "SELECT * FROM tb_order WHERE price = #{price}  ")
    List<Map> getByPrice(@Param("price") BigDecimal price);
}

测试

@SpringBootTest(classes = ShardingApplication.class)
public class test {
    @Autowired
    private OrderDao orderDao;
    @Test
    public void test1(){
        for (int i=0;i<10;i++){
            orderDao.insert(new BigDecimal(13),1L,"SUCCESS");
        }
    }
    @Test //测试查询数据，这俩数据都来自于其中一张表
    public void test2(){
        List<Long> list=new ArrayList<>();
        list.add(527901051974582272L);
        list.add(527901053098655744L);
        List<Map> byOrderIds = orderDao.getByOrderIds(list);
    }
    @Test //按照价格查询则会取两张表分别去查
    public void test3(){
        List<Map> result = orderDao.getByPrice(new BigDecimal(13));
    }
}

此时查看数据库的两张表，发现两张表已经各被插入了5条数据

Sharding JDBC简介

基本概念

逻辑表：即水平拆分的数据表的总称。列订单表tb_order被拆分为tb_order_1和tb_order_2，那么该逻辑表的名称就可以被起名为tb_order
真实表：即数据库中真实存在的表。比如tb_order_1和tb_order_2
数据节点：数据分片的最小物理单元。由数据源名称和数据表组成，例：m1.tb_order_1
绑定表：指分片规则一致的主表和子表，例如将订单表拆分为订单基本信息表和订单详情表，那么这俩表的主键都是order_id，并且都按照order_id分区,绑定表之间的多表查询不会出现笛卡尔积
广播表：指所有的分片数据源都存在的表
分片键：指定要分片的字段
分片策略：内置的分片策略包含尾数取模、哈希、范围、标签、时间等
自增主键生成策略：雪花算法

执行原理

在这里插入图片描述

SQL解析。解析过程分为词法解析和语法解析。词法解析器将SQL拆分为不可再分的原子符号，称为token。并根据数据库提供的方言将其归类为关键字、表达式、字面量和操作符。再使用语法解析器将SQL转换为抽象语法树。例如将以下的sql转换为语法树。然后在语法树上标记出可能要改写的词
SELECT id,name FROM t_user WHERE status ='active' AND age>18
SQL路由。根据解析语法树来匹配数据库和表的分片策略，并生成路由路径。对于携带分片键的SQL，根据分片键操作符不同可以划分为单片路由（分片键的操作符是等号）、多片路由（分片键的操作符是IN）和范围路由（分片键的操作符是BETWEEN）。不携带分片建的则采用广播路由。根据分片键进行路由的场景可分为直接路由、标准路由、笛卡尔路由等。
SQL改写。将逻辑表名改为真实表名，某些情况会补充查询字段（比如分页、排序、分组时）。
SQL执行。负责将路由和改写完成之后的真实SQL安全高效的发送到底层数据源执行。SQL执行有两种模式，分别是内存限制模式和链接限制模式。内存限制模式对一次操作所耗费的数据库连接数不做限制，在一个数据库中可以对多表同时进行操作。链接限制模式严格控制对一次操作所耗费的数据库连接数，在一个数据库中串行的表进行操作。
结果归并。支持的结果集归并包括5种类型，分别是遍历、排序、分组、分页、聚合。归并类型有内存归并（归并在内存中进行）、流式归并（各个表先排好序，然后用游标比较，一条一条归并）、装饰者归并（对结果集进行统一的功能增强，比如计算sum会先进行内存归并和流式归并）

水平分库

创建数据库db_order_1和db_order_2，并分别创建两张表tb_order_1和tb_order_2，表结构如快速入门中的表结构
配置多数据源、分库策略

server:
  port: 8001
  servlet:
    context-path: /sharding-jdbc-application
spring:
  application:
    name: sharding-jdbc-application
  shardingsphere:
    datasource:
      # 配置数据源的名字，随便起
      names: m1,m2
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_order_2?serverTimezone=UTC
        username: root
        password: 123456
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_order_1?serverTimezone=UTC
        username: root
        password: 123456
    sharding:
      tables:
        # 配置逻辑表的名称
        tb_order:
          # 配置分库的分片算法
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: m$->{user_id % 2 + 1}
          # 配置数据节点，指定数据库中表的名称
          actual-data-nodes: m$->{1..2}.tb_order_$->{1..2}
          #配置主键生成策略
          key-generator:
            column: order_id
            type: SNOWFLAKE
          # 配置分表的分片算法
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: tb_order_$->{order_id % 2 + 1}

    #打印sql
    props:
      sql:
        show: true
mybatis:
  type-aliases-package: com.lk.sharding.entity
  mapper-locations: classpath:mapper/*.xml

垂直分库

再创建一个数据库，db_user
修改配置文件，首先添加数据源，然后配置逻辑表名，因为该不进行水平分库和水平分表，因此不用配置分库策略和主键生成策略，但是分表策略仍然需要配置。

```yaml
server:
  port: 8001
  servlet:
    context-path: /sharding-jdbc-application
spring:
  application:
    name: sharding-jdbc-application
  shardingsphere:
    datasource:
      # 配置数据源的名字，随便起
      names: m1,m2,m0
      m2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_order_2?serverTimezone=UTC
        username: root
        password: 123456
      m1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_order_1?serverTimezone=UTC
        username: root
        password: 123456
      m0:
      	type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_user?serverTimezone=UTC
        username: root
        password: 123456
    sharding:
      tables:
        # 配置逻辑表的名称
        tb_user:
          # 配置数据节点，指定数据库中表的名称
          actual-data-nodes: m0.tb_user
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: tb_user
   #其余配置省略

公共表

有些公共表需要在每个数据库中都存在，那么如果在进行修改操作时，需要对所有库中的一张表进行操作，那就需要配置公共表。

spring:
  shardingsphere:
    sharding:
      broadcast-tables: tb_pub

读写分离

sharding JDBC可以实现读写分离，但是不支持数据库数据逇同步，只能由mysql实现主从同步。此处对mysql的主从配置不多介绍。

spring:
  shardingsphere:
    datasource:
      # 配置数据源的名字，随便起
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/user?serverTimezone=UTC
        username: root
        password: 123456
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/user?serverTimezone=UTC
        username: root
        password: 123456
    masterslave:
      name: master-slave
      #主数据库数据源名字
      master-data-source-name: master
      #从数据库数据源名字
      slave-data-source-names: slave
    # 配置数据节点
    sharding:
      tables:
        tb_user:
          actual-data-nodes: master-slave.tb_user