精华内容
下载资源
问答
我要提问
  • mysql分库分表
    2022-04-08 22:55:31

    Mysql分库分表

    数据库拆分主要指分库分表，其目的主要是分散数据库压力，达到横向扩展，满足均衡访问等。

    文章目录

    一、Mysql分表分库

    1.垂直拆分

    将不同业务功能相关的表放到不同的数据库中，也就是类似于微服务架构中：订单数据库/支付数据库/会员数据库

    2.水平拆分

    对同一张表数据实现拆分放到多个不同的表中存放。
    【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。 说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。 摘自：阿里巴巴java开发手册

    二、如何分表分库

    1.常用数据库中间件

    Mycat：基于服务器端 分表分库中间件
    优点：能够保证数据库的安全性
    缺点：效率比较低

    shadingjdbc：基于客户端改写sql 分表分库中间件
    优点：效率比较高
    缺点：不能够保证数据库的安全性、客户端容易内存溢出

    2.分表分库策略

    分表分库需要考虑点：扩容性、每张表数据均匀性。

    1.取余/取模
    优点：可以保证每张表数据均匀性
    缺点：后期无法做扩容

    2.按照范围分片
    优点：容易扩容、每张表数据都是均匀的存放
    缺点：前期可能会有数据表空闲

    3.按照日期进行分片
    优点：容易扩容
    缺点：每张表数据不均匀

    4.按照枚举值分片
    优点：容易扩容
    缺点：每张表数据不均匀

    5.二进制取模范围分片
    6.一致性hash分片
    7.按照目标字段前缀指定的进行分区
    8.按照前缀ASCII码和值进行取模范围分片

    三.Shadingjdbc整合

    1.Maven依赖

    <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.0.RELEASE</version>
</parent>
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
    </dependency>

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.62</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>3.1.0</version>
    </dependency>

</dependencies>

    2.相关配置

    a.整合分表

    # 数据源 testdb
sharding:
  jdbc:
    datasource:
      names: testdb
      # 第一个数据库
      testdb:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: root
    # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
    config:
      sharding:
        # 分库策略
        #        default-database-strategy:
        #          inline:
        #            sharding-column: id
        #            algorithm-expression: testdb
        # 分表策略 其中test_user为逻辑表 分表主要取决于id字段
        tables:
          test_user:
            ### testdb test_user_0 test_user_1
            actual-data-nodes: testdb.test_user_$->{0..1}
            table-strategy:
              inline:
                sharding-column: id
                # 分片算法表达式 test_user_1%2 test_user_2%2 test_user_3%2
                algorithm-expression: test_user_$->{id % 2}

      # 打印执行的数据库
      props:
        sql:
          show: true

# 打印执行的sql语句
spring:
  main:
    allow-bean-definition-overriding: true

    b.整合分表

    # 数据源 testdb
sharding:
  jdbc:
    datasource:
      names: testdb
      # 第一个数据库
      testdb:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: root
    # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
    config:
      sharding:
        tables:
          test_user:
            actual-data-nodes: testdb.test_user_$->{0..1}
            table-strategy:
              standard:
                precise-algorithm-class-name: com.test.config.TestRangeShardingAlgorithm
                sharding-column: id
      # 打印执行的数据库
      props:
        sql:
          show: true

# 打印执行的sql语句
spring:
  main:
    allow-bean-definition-overriding: true


    @Slf4j
public class TestRangeShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    private Long TABLE_SIZE = 5l;
    private String TABLE_NAME = "meite_user";

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        Double temp = Double.valueOf(preciseShardingValue.getValue()) / TABLE_SIZE;
        String tableName = TABLE_NAME + (int) Math.ceil(temp);
        log.info("<tableName{}>", tableName);
        return tableName;
    }
}

    四、分表分库语句查询原理

    分表分库之后如何查询的呢？
    查询语句没有带上分片字段–查询所有
    询语句带上分片字段–根据该分片字段 计算具体表查询

    1.查询语句条件后面带上分片字段

    截取该查询sql语句中 where 条件后面的分片字段，如果带上了分片字段 ，则根据该分片字段计算出具体存放在哪张表。
    只会发送一条sql语句。

    2.查询语句条件后面没有带上分片字段

    判断该 查询语句没有分片字段，直接查询所有。
    将每张表都会查询一遍。

    Shadingjdbc建议查询的过程中带上分片的字段

    3.分页查询，查询条件且没有带上分片字段

    Shadingjdbc 将每张表数据做一个查询，在交给Shadingjdbc 分页 返回给 客户端。

    4.排序查询，查询条件且没有带上分片字段

    Shadingjdbc 将每张表数据做一个查询，在交给Shadingjdbc 排序 返回给 客户端。

    sql 优化
    更多相关内容

  • 程序员面试刷题的书哪个好-mysqlsplit:mysql分库分表,分布式事务

     2021-07-07 11:37:49
    分库分表 基本分库分表: 1:分库分表 2:分库表冗余 3:分区表 分布式事务 1:XA分布式事务 2:TCC分布式事务 3:消息分布式事务 Mycat分片规则 Mycat读写分离 Mycat故障切换 Mycat+Percona+Haproxy+keepalived Zookeeper...
    收起

  • php mysql分库分表实例

     2021-08-28 09:09:03
    php分库分表
    收起

  • MySQL分库分表

     千次阅读 2022-03-14 08:37:02
    MySQL分库分表

    1.分库分表产生的背景

    采用单数据库存储存在以下的性能瓶颈：

    ①IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。

    ②CPU瓶颈：排序，分组，连接查询，聚合统计等SQL会消耗大量的CPU资源，请求数太多，CPU出现瓶颈。

    分库分表将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题。

    2.拆分策略：

    水平拆分：水平分表，水平分库；

    垂直拆分：垂直分表，垂直分库。

    垂直分库：以表为依据，根据业务将不同表拆分到不同库中。特点：①每个库的表结构都不一样；②每个库的数据也不一样；③所有库的并集是全量数据。下图为垂直分库案例。

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

    垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。特点：①每个表的结构都不一样；②每个表的数据也不一样，一般通过一列（主键/外键）关联；③所有表的并集是全量数据。下图为垂直分表的案例，两张表以主键id关联。

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。特点：①每个库的表结构都一样；②每个库的数据都不一样；③所有库的并集是全量数据。下图为水平分库。

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

    水平分表：以字段为依据，按照一定的策略，将一个表的数据拆分到多个表中。特点：①每个表的表结构都一样；②每个表的数据都不一样；③所有表的并集是全量数据。下图为水平分表。

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

     3.分库分表的实现技术

    shardingJDBC:基于AOP原理，在应用程序对本地执行的SQL进行拦截，解析，改写，路由处理。需要自行编码配置实现，支持java语言，性能较高。

    MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及shardingJDBC。

    4.MyCat

    MyCat是一个数据库中间件，使用MyCat也很简单，把我们之前连接数据库换成连接MyCat即可。

    6698af3262854497a052961c0d52d0ac.png

     mycat的核心概念：mycat中不存储数据，数据都是存储在节点主机中的，依照分片规则来决定存储在哪个节点主机；mycat只是一个逻辑结构，它是无感知的。watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

     5.mycat分片配置

    schema.xml涵盖了mycat的逻辑库，逻辑表，分片规则，分片节点及数据源的配置。主要包含三组标签：schema标签，datanode标签，datahost标签

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

     配置完schema.xml后，还要修改同级目录的server.xml文件，将schemas换成我们配置的schema;

    server.xml配置文件包含了mycat的系统配置信息，主要有两个重要标签：system,user

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

    rule.xml中定义所有拆分表的规则，在使用过程中可以灵活使用分片算法，或对同一个分片算法使用不同的参数，它让分片过程可配置化，主要保护局两类标签：tableRule，function。

    watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YyX57-f,size_20,color_FFFFFF,t_70,g_se,x_16

     6.mycat启动

    mycat启动后，占用端口8066。 

    7.mycat分片

    垂直分库

    mycat分片情况下，涉及跨库查询（跨分片查询）会报错，因为mycat无法确定该SQL应该路由到哪个分片。

    解决方案：将涉及的表设置为全局表（在schema.xml中table标签加上type='global'，dataNode为所有的节点），mycat对全局表任意节点进行DML时，所有节点都会同步进行

    水平分表

    水平分表的核心在于分片规则，只有水平分表才需要填写分片规则。

    8.常见的分片规则：

    范围分片：根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片。rule='auto-sharding-long';

    取模分片：根据指定的字段与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。rule='mod-long';

     

    一致性hash分片：根据指定的字段，算出字段的hash值，根据运算结果，来决定该数据属于哪一个分片。rule='sharding-by-murmur';

     

    枚举分片：通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份，性别，状态拆分数据等业务。rule='sharding-by-intfile-enumstatus';

    超过枚举值的数据要指定一个节点存储。

     

    应用指定分片：运行阶段由应用自主决定路由到哪个分片，直接根据字符串（必须是数字）计算分片号。rule='sharding-by-substring';

     

    固定分片hash算法：该运算类似于十进制的求模运算。例如：取id的二进制低10位与1111111111进行位&运算。rule='sharding-by-long-hash';

    位&运算：同为1则为1，有一个0则为0。例如：
1010101010&1111111111 = 1010101010

    特点：①如果是求模，连续的值分别分配到各个不同的分片，但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度；②可以均匀分配，也可以非均匀分配；③分片字段必须为数字类型

    字符串hash解析分片：截取字符串中的指定位置的字符串，进行hash算法，算出分片。rule='sharding-by-stringhash'; 

     按（天）日期分片：从开始时间开始，每10天（可以自行设置）为一个分片，到达结束时间后，会重复开始分片插入。rule='sharding-by-date'; 

    配置表的DataNode的分片，必须和分片规则数量一致，例如2022-01-01到2022-12-31，每10天一个分片，一共需要37个分片。因此，开始日期和结束日期一定要注意选择。

     按自然月分片：按照月份分片，每个自然月为一个分片。rule='sharding-by-month'; 

    配置表的DataNode的分片，必须和分片规则数量一致，例如2022-01-01到2022-12-31，一共需要12个分片。因此，开始日期和结束日期一定要注意选择。

     9.mycat的监控与管理

    9.1、mycat的原理

    每一个节点都只存储了一部分数据，因此，聚合处理、排序处理和分页处理等在各个节点处理是没有任何意义的，mycat会先将查询的结果合并然后再进行处理。

     9.2、mycat管理

    mycat默认开通2个端口，可以在server.xml中进行修改。8066数据访问端口和9066数据库管理端口。

     9.3、mycat图形化界面mycat-eye

    mycat-eye是对mycat-server提供监控服务，功能不局限于对mycat-server使用，通过JDBC连接对mycat，mysql监控，监控远程服务器（目前仅限于linux系统）的cpu、内存、网络、磁盘。

    mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。

     

    收起
    展开全文
    java

  • MySQL第七讲：MySQL分库分表详解

     2022-06-12 10:34:13
    概念：分库，就是把数据拆分到不同的 MySQL 库中去分表：在数据量不变的情况下，把数据拆分到同一个库的多张表里面分库分表：数据库数量和表数量都发生变更使用场景为什么进行分库分表？分库分表的目的分库分表有...

    MySQL分库分表详解

    摘要：本文是MySQL第七讲：MySQL分库分表详解流程，非常全面的总结，强烈建议保存下来，在需要时看看

    文章目录

    1、什么是分库分表，何时要分库分表

    背景知识：

    1、海量数据的存储问题

    • 传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。有些场合NoSQL是无法满足使用场景的，比如有事务与安全指标的。从单机mysql --》memcached+mysql+垂直拆分 --》mysql主从复制 --》分库分表+水平拆分+mysql集群

    2、如果使用关系型数据库解决海量存储的问题呢？

    • 此时就需要做数据库集群，为了提高查询性能将一个数据库的数据分散到不同的数据库中存储。

    3、mysql性能瓶颈分析

    • 数据库连接数，默认是100个连接数
    • 单表数据量大，阿里规定单表数据量500万条 数据量大，IO操作就多
    • 硬件资源（QPS/TPS 每秒查询数/每秒事务量）

    4、大数据量数据库性能的解决方案？

    • 分库分表
    • 索引优化（数据量1个亿且查询不太复杂时，没什么问题）
    • 读写分离

    1.1、数据库分片

    • 1、什么是数据库分片
      指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库上面，以达到分散单台设备负载的效果

    概念：

    • 分库，就是把数据拆分到不同的 MySQL 库中去

      • 如下图所示，库里面放了两张表：SPU和SKU表，我们将这两张表拆分到两个不同的库里面在这里插入图片描述

    • 分表：在数据量不变的情况下，把数据拆分到同一个库的多张表里面

      • 如下图所示，将SPU表由一张拆分为四张在这里插入图片描述

    • 分库分表：数据库数量和表数量都发生变更

      • 如下图所示，库里面放了一张SPU表，我们将这张表拆分到两个不同的库里面，每个库拆分成两张表
        在这里插入图片描述

    • 使用场景

      • 绝大部分的电商大厂，只有 MySQL 这类关系型数据库，才能提供金融级的事务保证

    1.2、为什么需要分库分表

    为什么进行分库分表？

    • MySQL 支持不了这么大的数据量，这么高的并发，但还必须要用它

    分库分表的目的

    • ①数据量太大查询慢（事务中的查询和更新操作）
      • 只读的查询可以通过缓存和主从分离来解决
      • 解决查询慢，只要减少每次查询的数据总量就可以了，也就是说，分表就可以解决问题
    • ②应对高并发的问题
      • 一个数据库实例撑不住，就把并发请求分散到多个实例中去
    • 数据量大，就分表；并发高，就分库
    • 这时候分多少个库，多少张表，分别用预估的并发量和数据量来计算就可以
    • 越简单的设计可靠性越高

    1.3、拆分方式

    分库分表有垂直切分和水平切分

    • ①垂直切分(复杂度低)：即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。我们会建立定义数据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等
      垂直拆分带来的问题？
      分布式事务（通过模块调用接口来解决）
      –》从连接数和硬件资源两方面进行了解决
    • ②水平切分(复杂度高)：当一个表中的数据量过大(超过1000万，就要警觉起来)时，我们可以把该表的数据按照某种规则，例如userID散列，进行划分，然后存储到多个结构相同的表，和不同的库上。

    如下图所示
    在这里插入图片描述

    垂直拆分如图所示：
    在这里插入图片描述

    水平拆分如图所示：
    在这里插入图片描述

    • 总体原则：那就是能不拆就不拆，能少拆不多拆，先才用加索引、读写分离解决性能问题，实在不行后，才考虑使用分库分表

    • 读写分离（主从模式）

      • 1、什么是读写分离

        • 在实际的应用中，绝大部分情况都是读远大于写，Mysql提供了读写分离的机制，所有的写操作都必须对应到Master，读操作可以在Master和Slave机器上进行，Slave与Master的结构完全一样，甚至Slave下还可以挂Slave,通过此方式可以有效的提高DB集群的每秒查询率。 一个写节点Master后面跟着多个读节点，所有的写操作都是先在Master上操作，然后同步更新到Slave上

      • 2、MySql基于binlog的主从复制原理（通过数据库厂商提供的bin log机制）

        • master–》binary log《-》io Thread–write–》relay log（中继日志）《–》sql Thread
        • 复制的基本原理？
          • 1、binlog事件 //master将改变记录到二进制文件binary log；
          • 2、relay log //slave将master的binlog事件拷贝到他的中继日志
          • 3、slave重做中继日志中的事件，将改变应用到自己的数据库中，Mysql复制是异步的且串行化

      • 3、主从复制带来的问题？

        • 可能出现时延，使用级联主从来解决，或是代码判断，要是真的出现时延，再进行解决；
        • 判断主从是否出现延时seconds-behind-master：0；
        • 若是超过5秒，则强制去主库读取数据
          • =>一般的业务不可能把主从搞出延时，老师只遇见过一次：到同行数据时，600万用户数据同时加载
          • =》因为数据库之间是在内网传输，延时一般很小

      • 4、总结：

        • 1、当读压力很大的时候，可以考虑添加Slave机器的分式解决，但是当Slave机器达到一定的数量就得考虑分库了；
        • 2、当写压力很大的时候，就必须得进行分库操作；
        • 3、全局的ID(不能使用mysql的自增id，要使用全局的id)

    1.4、何时分库分表

    总体来说：性能出现瓶颈，并且其他优化手段无法很好的解决问题

    • 单表出现瓶颈：
      • 单表数据量较大，导致读写性能较慢
    • 单库出现瓶颈
      • CPU压力过大(busy，load过高)，导致读写性能较慢
      • 内存不足(缓存池命中率较低、磁盘读写IOPS过高)，导致读写性能较慢
      • 磁盘空间不足，导致无法正常写入数据
      • 网络带宽不足，导致读写性能较慢

    Action : 数据量超过多少应该分库分表：看对应业务复杂情况，如果是表字段较为简单，即使数据量超过亿级，整体读写性能也较好，不用分表；如果表比较复杂，可能即使数据量超过百万，读写性能就达到瓶颈。

    1.5、如何选择分库分表

    • 只分表：
      • 单表数据量较大，单表读写性能出现瓶颈
      • 经过评估单库的容量和性能可以支撑未来几年的增长
    • 只分库：
      • 数据库(读)写压力较大，数据库出现存储性能瓶颈
    • 分库分表
      • 单表数据量较大，单表读写性能出现瓶颈；
      • 数据库(读）写压力较大，数据库出现存储性能瓶颈

    2、十亿级商品数据，分库分表核心流程详解

    2.1、完整流程如下所述

    • 评估是否需要拆分
      • 是否有其他更轻量的优化手段可以解决问题
    • 拆分详细技术方案
      • 流程梳理及影响评估
      • 方案选型
      • 拆分SOP(标准操作程序，重要)
        • 1、目标评估
          • 评估：拆成几个库、几个表
          • 目标：读写能力提升X倍、负载降低Y%，容量要支持未来Z年的发展
          • 举例：当前2亿，5年后评估为10亿。分几个表？ 分几个库？
          • 解答：一个合理的答案，128个表，16个库。按128个表算，拆分完单表156万，5年后为781万
        • 2、切分策略–范围切分
          • 优点：天然水平扩展；单表大小可控
          • 缺点：热点数据一般为新增数据，存在明显的写偏移
          • 适用场景：数据归档在这里插入图片描述
          • 切分策略–中间表映射
            • 优点：灵活；
            • 缺点：引入了额外的单点，增加了流程复杂度。在这里插入图片描述
          • 切分策略–hash切分
            • 优点：数据分片比较均匀，不容易出现热点和并发访问的瓶颈；
            • 缺点：后续扩容需要迁移数据、存在跨节点查询等问题；
            • 适用场景：大部分场景下都能适用。在这里插入图片描述
        • 3、分表字段(sharding key)
          • 核心思路：合理选择，尽量减少出现跨库、跨表查询
          • 例子：10个库，1000张表：0 ~ 99、100 ~ 199、200~999、…
          • 分表字段：shopId，值为1234
          • 数据表编号：shopId% 1000 =1234 % 1000 =234
          • 数据库编号：shopId% 1000 =1234 % 1000 / 10 = 2
          • 思考：订单应该如何选择分表字段？ 见下文
        • 4、资源准备、代码改造
          • 核心流程：
            • 数据库资源准备
            • 分库分表规则配置等
            • 代码改造：
              • 写入：单写老库、双写、单写新库
              • 读取：读老、读新、部分读老部分读新
              • 灰度：指定门店灰度、比例灰度
            • 所需资源尽早跟dba申请
              • 1、将新集群的数据源引入到我们的服务中；
              • 2、支持灵活的灰度读写切换；
              • 3、数据全量迁移和一致性校验等任务；
              • 4、增量数据同步：双写
        • 5、常见的双写方案
          • 作用：保证增量数据在新库和老库都存在
          • 方案：
            • 1、同步双写：同步写新库和老库
            • 2、异步双写：写老库，监听binlog 异步同步到新表
            • 3、中间件同步工具：通过一定的规则将数据同步到目标库表
          • 我们公司使用的是阿里云DTS
          • 注意点：
            • 写新库异常不能影响流程
          • 实现方案：
            • 底层通过AOP方式实现，不会修改全部写逻辑
        • 6、全量数据迁移
          • 作用：迁移老库历史数据，保证新库有全量数据
          • 方案：
            • 1、开发Job：查询老库数据，写入新表；
            • 2、中间件同步工具：通过一定的规则将数据同步到目标库表(建议)
          • 注意点：
            • 控制好同步速率
            • 和增量数据的并发问题
        • 7、数据一致性校验、优化、补偿 (最重要)
          • 作用：确保新库数据正确，达到切读标准、检查是否存在改造遗漏点
          • 方案：增量数据校验、全量数据校验、人工抽检
          • 核心流程：
            • 读取老库数据
            • 读取新库数据
            • 比较新老库数据，一致则继续比较下一条数据
            • 不一致则进行补偿：
              • 新库存在，老库不存在：新库删除数据
              • 新库不存在，老库存在：新库增加数据
              • 新库存在，老库不存在：比较所有字段，不一致则将新库更新为老库数据
        • 8、灰度切读
          • 作用：开始将读流量切到新库
          • 原则：
            • 有问题及时切回老库
            • 灰度放量先慢后快，每次放量观察一段时间
            • 支持灵活的规则：门店维度灰度、百分比灰度
        • 9、databus 切新库
          • 作用：使用新库的databus、canal
          • 核心流程：
            • 启动新库databus、canal，此时下游会同时收到新老库的binlog
            • 观察一段时间是否正常
            • 有问题及时关闭
            • 没问题后，关闭老库databus、canal
        • 10、下游切换数据源
          • 作用：确保下游迁移到新数据源，主要是数仓
          • 数仓一般是每天同步一次数据，因此在指定时间内切换即可。
        • 11、停写老库
          • 原则：确认老库数据源全部迁移后，停写老库
          • 至此，核心拆分流程结束，后续逐步将老数据库资源逐渐下线。
          • 整体流程图汇总如下：
          • 在这里插入图片描述
      • 稳定性保障
    • 技术方案内部评审及优化
    • 同步相关影响方
    • 进行拆分

    2.2、使用到的分库分表工具

    • binlog监听工具
      • Databus
      • Canal
    • 分库分表工具
      • 1、增强版的JDBC驱动
        • 以客户端 jar包形式提供了对JDBC的封装，客户端直连数据库
        • 开源：Sharding-JDBC、TDDL、Zebra
      • 2、数据库代理
        • 需要单独部署，客户端连接代理服务，代理服务负责跟数据库打交道
        • 开源：Sharding-Proxy、MyCat
        • 收费：阿里云DRDS

    3、分库分表带来的问题及如何解决(重点)

    问题1：分布式id：分库分表后，保证id的唯一性(要保证单调递增)

    • 解决方案1：UUID
      • 优点：
        • 本地生成，性能高
      • 缺点：
        • 更占用存储空间，一般为长度36的字符串
        • 不适合作为MySQL主键
          • 无序性会导致磁盘随机IO、叶分裂等问题
          • 普通索引需要存储主键值，导致B+树“变高”，IO次数变多
        • 基于MAC地址生成的算法可能导致MAC地址泄漏
    • 方案2：雪花算法
      • 在这里插入图片描述
      • 41 bit时间戳：可用69年
      • 10bit工作机器：可部署1024台服务器
      • 12bit序列号：每毫秒可生成4096个ID，每秒也就是409万
    • 方案3：号段模式
      • 在这里插入图片描述
      • 原来：是按数据表自增得到唯一id，现在：批量生成1000条数据，拿到其ids

    以美团外卖为例，商品的分表

    • 用户视角：查询商品时，会展示shopId
    • 商家视角：查询商品时，也会展示shopId
    • shopId覆盖了最高频的几个使用场景

    订单的分表

    • 用户视角：查询到自己的所有订单，用户id
    • 商家视角：商品查询自己的所有订单，商家id
    • 运营视角：订单id字段
      • 为了支持按订单号查询，可以把用户id的后几位放到订单号中

    如何选择 Sharding Key？

    • 选择一个合适的列或者说是属性，作为分表的依据，这个属性一般称为 Sharding Key

    问题2、分布式事务问题

    • 原本在同一个数据库中不同的表可以在同一个事务中修改，业务分库后，表分散到不同的数据库中，无法通过事务统一修改。虽然数据库厂商提供了一些分布式事务的解决方案（例如，MySQL 的 XA），但性能实在太低，与高性能存储的目标是相违背的。
    • 解决方案
      • 1、2PC(Two Phase Commitment)
        • 如下图所示，核心思想是将事务操作分为两个阶段，第一阶段是协调者首先询问所有事务参与者是否可以执行事务的提交操作；第二阶段是协调者根据所有参与者返回结果决定是否提交操作，如果全部的参与者都返回成功，则协调者向所有参与者发送事务提交请求；否则协调者向所有参与者发送事务中断回滚请求
        • 优点：流程简单
        • 缺点：存在同步阻塞、协调者单点等问题
        • 数据库层面的处理
        • 在这里插入图片描述
      • 方案2：TCC （try confirm cancel）
        • 核心思想：针对每个操作都有一个对应的确定和取消操作 ，TCC中有主服务(可以理解为交易)和从服务(可以理解为订单和库存)两个角色。首先主服务会调用所有从服务的try接口进行业务检查和资源预留，然后主服务会根据所有从服务返回结果决定是否提交事务；如果从服务确认成功，则调用所有从服务的confirm接口进行事务确认提交操作，否则会调用所有从服务的cancel接口执行事务的取消，并释放预留资源；
        • 应用层面的处理在这里插入图片描述
        • 实际业务中的使用方式（保证最终一致即可，金融场景是个特例）
        • 回滚
        • 重试
        • 监控
        • 告警
        • 幂等
        • 对账
        • 人工补偿(终极手段)

    问题3、跨库join / 分页查询问题

    • 业务分库后，原本在同一个数据库中的表分散到不同数据库中，导致无法使用 SQL 的 join
      查询。

    拆分后：影响了哪些操作

    • join操作
      水平分表后，数据分散在多个表中，如果需要与其他表进行 join 查询，需要在业务代码或
      者数据库中间件中进行多次 join 查询，然后将结果合并。

    • count操作
      水平分表后，虽然物理上数据分散到多个表中，但某些业务逻辑上还是会将这些表当作一个表来处理。例如，获取记录总数用于分页或者展示，水平分表前用一个 count() 就能完成的操作，在分表后就没那么简单了。常见的处理方式有下面两种：

      • ①count() 相加：具体做法是在业务代码或者数据库中间件中对每个表进行 count() 操作，然后将结果相加。这种方式实现简单，缺点就是性能比较低。例如，水平分表后切分为 20 张表，则要进行 20 次 count(*) 操作，如果串行的话，可能需要几秒钟才能得到结果。
      • ②记录数表：具体做法是新建一张表，假如表名为“记录数表”，包含 table_name、row_count 两个字段，每次插入或者删除子表数据成功后，都更新“记录数表”。

    • order by 操作
      水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或者数据库中间件分别查询每个子表中的数据，然后汇总进行排序。

    业界主流解决方案：

    • 方案1：选择合适的分表字段(sharding key)

      • 合理选择，避免过多的跨库查询 ，保证绝大多数高频查询场景
      • 在这里插入图片描述

    • 方案2：使用搜索引擎支持ES

      • 数据冗余到ES，使用ES支持复杂查询
      • 核心流程：
        • 使用ES查询出关键字段，例如：店铺id和商品id
        • 再使用关键字段去数据库查询完整数据
      • 注意点
        • ES只存储需要搜索的字段

    • 方案3：分开查询，内存中聚合

      • 先查询出A表数据，然后根据A表的结果查询B表
      • 注意点：
        • 查询出来的数据量
        • 占用内存情况

    • 方案4：冗余字段

      • A表查询需要B表的field1字段，则将B表的field存储一份到A表上
      • 适用场景：
        • 只需要少量字段，则可以直接冗余

    问题4、成本问题

    • 业务分库同时也带来了成本的代价，本来 1 台服务器搞定的事情，现在要 3 台，如果考虑
      备份，那就是 2 台变成了 6 台。

    4、日千万级的订单系统分库分表核心内容简要分享

    todo

    5、阿里云drds方案处理分库分表

    • 商品中心的方案

      • 背景：商品主表共1亿6千多万条数据(截止20210810，有效数据共1亿1千万条数据)
      • 架构：使用阿里云提供的drds，分为三层，第一层是drds代理层(读写库sg85 只读库wr20),第二层是主rds数据库，共4个实例（16台机器），从rds数据库，共8个实例(32台机器)
        项目的读操作，只会读取从rds数据库中的部分实例

    • 用户中心的方案

      • todo

    6、MySql分库分表与读写分离

    6.1、什么是MyCat？

    • 面向企业应用开发的“大数据集群”，支持事务、ACID、一个数据库中间件产品（作为后面MySql集群的proxy使用，默认端口：8066）
    • 作用：提高可用数据分片集群，支持读写分离，支持MySql双主多从，一主（写数据库）多从（读数据库）
    • 当添加一条记录时要向哪个数据库中插入呢？这些问题处理起来都是非常的麻烦。这种情况下可以使用一个数据库中间件mycat来解决相关的问题
    • 用法：现在把数据库中所有的item一千五百万本（图书表）分片存储到三个数据节点中。可以是三台mysql数据库。
      • 节点一：db1存储500万条数据 节点二：db2 500万；节点三：db3 500万；
      • 如何分配：1、根据主键id按段切分 2、根据id取模运算
        • 配置schema。xml文件，管理mycat的逻辑库，表，分片规则，数据节点，数据源 rule= auto-sharding-long mycat会根据此规则自动分片
      • 读写分离：一个写节点master后面跟着多个读节点。读节点的数量取决于系统的压力。写数据库和读数据库建立主从复制，使用mysql厂商自带的binlog，然后建立mycat和mysql的心跳检查。
      • 使用：mysql主数据库修改my.conf文件，musqld下面添加数据库及IP的配置信息 状态：File/position/binlog_Do_DB/binlog_ignore-DB。 从数据库修改my.conf文件 change master to master_host=‘’,master_post=‘’,master_user=‘’,master_password=‘‘master_log_file=’’,master_log_pos=‘’
        当slave_io_Running:YES且slave_SQL_Running:YES,说明配置成功
        mycat配置 支持mysql主从复制状态绑定的读写分离机制 balance writeType switchType

    6.2、中间件操作数据库的步骤

    • 1、解析sql语句
    • 2、数据源管理
    • 3、数据源分配
    • 4、请求/响应
    • 5、结果整合

    6.3、MyCat使用案例

    1、概念：逻辑库（使用mycat来管理）db_user,db_store 逻辑表：分片表user，全局表（数据字典 冗余），ER表（user地址 只能存放在单一的数据库中，跟着主表一起走），非分片表（门店表，店员表）
    应用程序分为db_user(用户表、数据字典，用户地址表)和db_store(订单表，店员表)
    经常使用的数据不变性，在后台的每个mysql中都保存一份（如数据字典最好冗余查询**）
    用户表进行模2运算，store表进行主从复制

    2、conf目录下，有三个关键的配置文件

    • scheme.xml管理mycqt实例中的逻辑库，表，分片规则，DataNode，DataSource
    • server.xml
    • rule.xml 定义了我们队表进行拆分所设计到的规则定义
      有连续分片和离散分片两种
      连续分片扩容无需迁移数据，范围查询资源消耗小 但是存在热点数据问题
      离散分片：分片均匀，并发强 缺点：移植性差

    3、现有系统如何使用MyCat？

    • 当当、美团正在做这方面的工作
    • mysql Dump命令生成backup.sql文件， 控制台：mysql -f backup.sql或Source backup.sql

    6.4、MyCat关联查询的问题

    解决方案：
    1、用好ER表，子表跟随父表
    2、善用全局表 冗余数据
    3、注解方式

    6.5、Mycat的分布式事务？XA ？20181222

    如何保证强一致性
    两阶段提交 弱XA的两阶段提交
    TCC补偿机制

    当你的才华还撑不起你的野心时，就应该静下心来学习

    收起
    展开全文
    读写分离

  • Mysql 分库分表

     千次阅读 2021-11-14 22:30:28
    使用分库分表时，主要有垂直拆分和水平拆分两种拆分模式，都属于物理空间的拆分。 分库分表方案：只分库、只分表、分库又分表。 垂直拆分：由于表数量多导致的单个库大。将表拆分到多个库中。 水平拆分：由于表...

    使用分库分表时，主要有垂直拆分和水平拆分两种拆分模式，都属于物理空间的拆分。

     

    分库分表方案：只分库、只分表、分库又分表。

    垂直拆分：由于表数量多导致的单个库大。将表拆分到多个库中。

    水平拆分：由于表记录多导致的单个库大。将表记录拆分到多个表中。

    一，垂直拆分

    垂直拆分又称为纵向拆分，垂直拆分是将表按库进行分离，或者修改表结构按照访问的差异将某些 列拆分出去。应用时有垂直分库和垂直分表两种方式，一般谈到的垂直拆分主要指的是垂直分库。 

    垂直分库：

    垂直分表：将一张表中不常用的字段拆分到另一张表中，从而保证第一张表中的字段较少，避免 出现数据库跨页存储的问题，从而提升查询效率。

    可解决问题：一个表中字段过多，还有有些字段经常使用，有些字段不经常使用，或者还有text等字段信 息。可以考虑使用垂直分表方案。

     垂直拆分优点：

    1. 拆分后业务清晰，拆分规则明确；
    2. 易于数据的维护和扩展；
    3. 可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次 数；
    4. 可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起；
    5. 便于实现冷热分离的数据表设计模式。

    垂直拆分缺点：

    1. 主键出现冗余，需要管理冗余列；
    2. 会引起表连接 JOIN 操作，可以通过在业务服务器上进行 join 来减少数据库压力，提高了系统的复杂度；
    3. 依然存在单表数据量过大的问题；
    4. 事务处理复杂。 

    用例： 职位基本信息表与职位详情表进行垂直拆分，从原本职位表中垂直分表

     

    二，水平拆分

    水平拆分又称为横向拆分。 相对于垂直拆分，它不再将数据根据业务逻辑分类，而是通过某个字 段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个表仅包含数据的一部分，下面的图为使用类Hash算法去拆分的过程。

    水平分表是将一张含有很多记录数的表水平切分，不同的记录可以分开保存，拆分成几张结构相同 的表。如果一张表中的记录数过多，那么会对数据库的读写性能产生较大的影响，虽然此时仍然能 够正确地读写，但读写的速度已经到了业务无法忍受的地步，此时就需要使用水平分表来解决这个 问题。

    水平拆分优点：

    1. 拆分规则设计好，join 操作基本可以数据库做；
    2. 不存在单库大数据，高并发的性能瓶颈；
    3. 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可；
    4. 提高了系统的稳定性和负载能力。

    水平拆分缺点：

    1. 拆分规则难以抽象；
    2. 跨库Join性能较差；
    3. 分片事务的一致性难以解决；
    4. 数据扩容的难度和维护量极大。 

    用例：电商中的商家的订单表 ，若在一个库中分表存储，还是存在IO瓶颈，如若数据量很大，可以将单个表拆分了分到不同的服务器中去，但是分到不同的服务器中去之后，需要考虑如何高效的进行数据获取，如果每次查询都要跨越多个节点则会影响查询的效率。

    分库之后的问题：

     1，事务问题：一次投递需要插入两条记录，且分布在不同的服务器上，数据需要保障一致性。

     2，跨库联合查询的问题。

     3，额外的数据管理负担和数据运算压力：数据库扩容、维护成本变高

    三，垂直拆分 & 水平拆分

    水平拆分：解决表中记录过多问题。

    垂直拆分：解决表过多或者是表字段过多问题

    附： 参考资料《MySQL技术内幕 InnoDB存储引擎》  

    收起
    展开全文
    mysql java

  • MySQL分库分表总结讲解

     2020-12-16 00:54:01
    当出现这种情况时，我们可以考虑分库分表，即将单个数据库或表进行拆分，拆分成多个库和多个数据表，然后用户访问的时候，根据一定的算法与逻辑，让用户访问不同的库、不同的表，这样数据分散到多个数据表中，减少了...
    收起

  • MySQL 分库分表的实现原理及演示案例

     2018-04-17 21:25:28
    MySQL 分库分表的实现原理及演示案例，非常不错，可以看看
    收起

  • Mysql分库分表方案

     2022-05-22 12:58:26
    分库分表概念 分库分表就是业务系统将数据写请求分发到master节点，而读请求分发到slave 节点的一种方案，可以大大提高整个数据库集群的性能。但是要注意，分库分表的 一整套逻辑全部是由客户端自行实现的。而...
    收起
    java

  • mysql分库分表

     千次阅读 2022-01-20 10:08:19
    一、如何进行分库分表 1、概念： 将原本存储在单个数据库上的数据拆分到多个数据库中（分库），把原来存储在单张表的数据拆分到多张数据表中，实现数据切分，从而提生数据库操作性能。分库分表的实现方式分为两种：...
    收起
    nosql

  • 实际应用中MySQL分库分表实践总结原理

     2022-04-23 13:56:49
    实际应用中MySQL分库分表实践总结原理 互联网系统需要处理大量用户的请求。比如微信日活用户破10亿，海量的用户每天产生海量的数量；美团外卖，每天都是几千万的订单，那这些系统的用户表、订单表、交易流...
    收起
    程序人生 深度学习 java

  • 如何实现MYSQL分库分表

     2022-07-07 20:37:46
    如何实现MYSQL分库分表
    收起
    java 开发语言

  • MySQL 分库分表的基本概念和常见问题

     千次阅读 2021-09-04 10:59:21
    简单介绍了分库分表的概念以及相关问题。
    收起
    java

  • 【mysql】MySQL 分库分表方案，总结的非常好！

     千次阅读 2021-08-17 21:35:45
    分库分表方案产品 目前市面上的分库分表中间件相对较多，其中基于代理方式的有MySQL Proxy和Amoeba， 基于Hibernate框架的是Hibernate Shards，基于jdbc的有当当sharding-jdbc， 基于mybatis的类似maven插件式的有...
    收起

  • MySQL优化详解（五）——MySQL分库分表

     2022-01-09 17:14:54
    天继续给大家介绍MySQL相关知识，本文主要内容是MySQL数据库的分库和分表思路。 一、分库分表基本原理 二、分库分表存在的问题
    收起
    mysql mysql优化

  • Mysql分库分表工具ShardingSphere

     千次阅读 2022-03-30 10:15:32
    ShardingSphere是一款起源...并逐渐由原本只关注于关系型数据库增强工具的ShardingJDBC升级成为一整套以数据分片为基础的数据生态圈，更名为ShardingSphere。到2020年4月，已经成为了Apache软件基金会的顶级项目。 Sha
    收起
    java 分布式

  • mysql 分库分表 建表MySQL常用操作

     2021-04-19 09:08:50
    通过命令行启动、停止MySQL服务器：start: ...mysql -h127.0.0.1 -uroot -p断开MySQL服务器：mysql>quit;创建数据库：create database db_name;查看数据库：show databases;选择数据库：use db_name;删除...
    收起

  • MyBatis实现Mysql数据库分库分表操作和总结（推荐）

     2020-08-29 15:38:49
    主要介绍了MyBatis实现Mysql数据库分库分表操作和总结,需要的朋友可以参考下
    收起

  • MySQL分库分表原理

     2021-08-26 23:09:58
    文章目录1、为什么要分库分表02、分库分表03、不停机分库分表数据迁移4、分库分表实现 1、为什么要分库分表 分库分表目的：解决高并发，和数据量大的问题。 1、高并发情况下，会造成IO读写频繁，自然就会造成读写...
    收起
    java

  • MySQL 分库分表实践

     千次阅读 2022-04-07 09:46:55
    一、为什么要分库分表 二、库表太大产生的问题 三、垂直拆分 1. 垂直分库 2. 垂直分表 四、水平分库分表 2. 测试水平分表
    收起

  • Mysql分库分表实战（一）——一文搞懂Mysql数据库分库分表

     万次阅读 多人点赞 2019-11-05 10:29:15
    由于业务需要，需要对Mysql数据库进行分库分表，故而最近一直在整理分库分表的相关知识，现手上的工作也告一段落了，抽空将自己最近的学习结果转化为博文，分享给大家，本博文打算做成一个系列的，首先是分库分表的...
    收起
    分布式事务

  • 数据库技术与应用专场——02_58同城mysql分库分表实践-沈剑

     2016-05-19 13:23:36
    数据库技术与应用专场——02_58同城mysql分库分表实践-沈剑
    收起

  • 干货 : 常用MySQL分库分表方案

     2021-03-17 01:52:08
    五、分库分表问题 1、非partition key的查询问题 基于水平分库分表，拆分策略为常用的hash法。 端上除了partition key只有一个非partition key作为条件查询 映射法 基因法 注：写入时，基因法生成user_id，如图。...
    收起

  • MySQL分库分表及中间件Mycat

     2021-11-20 14:23:41
    文章目录一、前言1.1 垂直切分1.2 垂直切分的优缺点：1.3 水平切分1.3.1 水平分表1.3.2 水平分库1.4 水平...当用户量级和业务进一步提升后，写请求越来越多，这时我们开始使用了分库分表 如何解决？ 数据切分 简单来
    收起

  • MySQL分库分表的分页查询解决方案

     千次阅读 2021-02-09 17:54:27
    问题的提出我们知道，当我们的数据量达到一定数量时，需要将数据表进行水平拆分，从而...单库上，可以通过简单的sql实现分页查询。select * from t_user order by time limit 200,1001select *fromt_userorderbyt...
    收起

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 69,024
精华内容 27,609
热门标签
关键字：

mysql分库分表

mysql 订阅