MYSQL-10.分库分表

最新推荐文章于 2024-09-11 23:33:17 发布

xrl66

最新推荐文章于 2024-09-11 23:33:17 发布

阅读量665

点赞数 17

分类专栏： Mysql 文章标签： mysql 数据库 java 后端 spring

本文链接：https://blog.csdn.net/xrl00/article/details/138975493

版权

Mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

需要分库分表的情况

请求数过高：在高并发场景下，大量请求访问数据库，导致数据库活跃连接数增加，逼近甚至达到其连接数阀值（机器的配置有限，数据库所能承受的连接也是有限的），从而使客户端程序无连接可用，最终导致并发量急剧增加、吞吐量下降、连接出现异常，甚至导致数据库宏机、系统崩溃等问题；
查询缓慢：
1. 单表数据量过大；
2. 单库的整体并发连接数接近系统阀值，容易造成请求获取不到连接，而一直处于等待的情况；
3. CPU负载太大，即使我们获取到了连接，但由于操作系统的执行时通过时间片轮训的方式，当CPU负载很高时，连接很难获得时间片所以会长时间处于阻塞状态
数据量太大：
1. 当库数据量太大时：即使每张表的并发数不多，但库中存在大量的数据表，每张表都存在一定的请求，容易导致单库的连接数到达阀值，从而成为数据库瓶颈；
2. 当表数据量太大时：导致查询性能缓慢，虽然InnoDB存储引擎的表允许的最大行数为10亿，但是表的数据行记录很大，那就算通过索引去查询一条数据，它也需要经过多次磁盘IO，从而导致单表查询速度缓慢；一般一张表的数据行数在800~1200W左右最合适；也可以参考阿里开发手册，当单表行数超过500W行或单表容量超过2GB时推荐进行分库分表；

Mysql数据库瓶颈：

磁盘IO瓶颈（磁盘IO次数）：
1. 热点数据问题：Mysql为了提高读写性能，会将一些经常访问的热点数据放入内存缓存区中，从而减少磁盘之间的IO操作，但当一个库的数据量很大时，就可能会存在大量热点数据，此时内存缓冲区又无法放下这些热点数据时，就会导致大量的读写操作产生磁盘IO，从而导致性能降低；
2. 磁盘读取问题：当单表中一行数据的体积很大时，可能会超出Mysql磁盘IO每次读取16KB的限制，从而导致一条数据需要多次IO读取；
3. 数据处理问题：当一次查询的过程中涉及到的数据很多，内存缓冲区无法载入全部数据，导致无法在内存中完成全部数据检索，如分组、排序、关联查询等场景，只能通过分批处理的方式处理数据，此时又只能经过大量的磁盘IO才能得到最终结果；
网络IO瓶颈：当一个请求的SQL执行后，若得到的结果集数据太多，会导致相应的数据包体积过大，如果网络带宽不足，就会出现拆包然后分批返回操作，从而导致传输过慢，此时也会阻塞其他读写请求；
CPU运算密集型瓶颈：当请求的SQL中包含大量的join联表查询、group by分组查询、order by排序等之类的需要基于数据集作运算的聚合操作，当数据量大时会消耗大量CPU资源，从而导致CPU占用率飙升；
CPU阻塞瓶颈：一张或多张表的数据量特别大，此时基于这些大表做数据检索时，需要扫描的数据行太多，虽然这些SQL语句不会大量消耗CPU资源，但由于数据量过大，会导致长时间占用CPU资源，从而造成其它线程无法获取CPU资源执行；

分库分表的拆分方案

分库方案

垂直分库

解决的问题：单库压力大的情况，从业务上进行拆分；
拆分方式：一般在系统设计时，就会根据业务链路将具备同一业务属性的表放在一个单独库中进行存储；本质上是将客户端的请求根据不同的业务分流到不同库中；
例子：如订单服务访问订单库，用户服务访问用户库；
带来的问题：
1. 跨库联查问题： 需要的数据分在不同库中
  - 字段冗余，尽量避免跨库联查；
  - 通过广播表/网络表/全局表将对应的数据同步到一个相应库中；
  - 在设计库表拆分时，将需要连表查询的表放入到同一个库中；
  - 在Java系统中组合数据，通过服务调用的方式获取数据，然后在程序中组装返回；
  - 依赖第三方中间件实现，如将数据聚合后放入ES中；
2. 分布式事务问题：如何保证不同库数据之间的数据一致性问题；
  - XA、2PC、3PC模式；
  - TCC事务补偿模式；
  - MQ消息队列最终一致性事务模式；
  - 最大努力通知模式；
3. 部分业务任然存在性能问题：部分业务承载的并发流量任然过高；
  - 采用水平分库方式；
  - 搭建集群；

水平分库

解决的问题：当在进行垂直分库后，某类业务的并发量依旧很高，单库难以承受，此时就需要提高某类业务的吞吐量；
拆分方式：基于一个节点，然后直接横向拓展，这也就意味着同一业务的数据库，各节点之间的库结构完全相同，但每个节点中的数据是否相同则需要开发去进行决定，不同的业务下满足的需求不同；
例子：将一个用户库水平拆分成多个用户库；
带来的问题：
1. 聚合操作和连表问题：一般作了水平分库后，会将库中的所有表进行水平切分，也就是不同库中的表存储的数据也是不同的，那么就会存在数据聚合操作、连表操作等无法进行；解决办法与水平分表类似，先确定读写数据位于那个库中的哪个表再去执行操作；
2. 数据分页问题：水平分库后由于存在多个数据源，所以不能通过limit index , n的方式进行分页；
  - 读取每个子库中的分页数据后，再在JAVA程序中进行聚合操作；
  - 利用第三方中间件如ES中；
  - 将数据聚合到某一个表中；
  - 利用大数据技术搭建数据中台，将子库数据汇聚到数据中台中；
3. ID主键的唯一性问题：
  - 通过设置数据自增机制的起始值和步长，来控制不同节点ID的交叉增长；
  - 使用算法生成分布式ID，如雪花算法、Snowflake算法等；
  - 利用第三方中间件生成ID，如redis的incr命令；
4. 数据落库问题：写数据时需要根据一定的规则去落库，否则查询时无法定位的数据属于哪个库；数据的拆分规则被称之为路由规则，拆分时需注意数据分布均匀、查询方便、扩容/迁移容易等几点原则，最好相关数据存储在相同的库中；
  1. 随机分片：随机分法写数据请求，但查询时需要读取全部节点才难拿到数据，一般不用；
  2. 连续分片：每个阶段负责存储一个范围内的数据；
  3. 取模分片：根据整数型ID值与水平库的节点数据做取模运算；
  4. 一致性哈希：将某个具备唯一性的字段计算哈希值，然后根据哈希值进行取模分片；
    
    …
5. 多维度查询问题：假设有一张user表，当单库的情况下既可以通过id查询数据，也可以通过name查询数据，但是在水平分库后，数据需要查询哪个库是由路由键和路由算法决定的，假设这里路由键是id，此时通过id查询是没有问题的，但如果想通过name查询，就无法通过路由键定位具体库；
  - 数据量小时，可以通过缓存或者ES维护路由键的二级索引如：1:name1, 2:name2；
  - 使用水平库集群，每个集群中的数据完全相同，但路由键不同，可以根据不同的路由键查询不容的水平库；
6. 外键约束问题：存在关联的表最好落入一个库中，比如有一个订单数据对应了三条订单详情数据，路由键是oder_id = 1和oder_item_id = （1, 2, 3），若根据取模算法进行路由，则会出现oder_id = 1的数据存在DB1中但明细数据存储DB0、DB2中；

升级问题

流量迁移：线上环境从单库切换到分库分表模式，数据该如何迁移才能保证线上业务不受影响，对于这个问题来说，首先得写脚本将老库的数据同步到分库分表后的各个节点中，然后条件允许的情况下先上灰度发布，划分一部分流量过来做运营测试；需要注意程序代码从单库改到分库分表需要完善、数据迁移要做好、程序调试无误，并且要支持版本回滚，若出现问题可以快速切换回之前的老库；
容量规划：根据业务数据规模和并发量确定切分多少个接待你合适，并且分库节点需要保证是2的倍数，方便后续扩容；
节点扩容：考虑数据的路由规则带来的影响；一般采用水平双倍扩容法、异步双写扩容法；
1. 水平双倍扩容法：原先节点数需要为2的整数倍，同时路由规则要`数值取模法、或Hash取模法，否则依旧会造成扩容难度直线提升；还存在一种进阶方法，被称之为从库升级法，也就是给原本每个节点都配置一个从库，然后同步主节点的所有数据，当需要扩容时仅需将从库升级为主节点即可，
  1. 过程如下：
  - 若采用取模运算法，此时两节点的数据分别为DB0(2, 4...)、DB1(1, 3...)；且从节点回同步主节点的所有数据当需要水平扩容时，可直接将从节点升级为主节点
    - 此时路由算法变为%4，数据分别为DB0(4, 8...)、DB1(1, 5...)、DB2(2, 6...)、DB3(3, 7...)；结合扩容数据，DB0(2, 4, 6, 8...),DB2(2, 6, 10...)，可以知道扩容后DB2中落入的数据，原本都是存在DB0中的，而DB2又是原DB0的子节点，所以也具备DB0的数据，因此采用这种扩容法基本上无需做数据迁移；
      
      好比现在要查询ID=10的数据，根据原本Hash(XX)%2的路由算法，会落入到DB0中读取数据，而根据现在Hash(XX)%4的路由算法，应该落入到DB2中读取数据，因为DB2具备原本DB0的数据，所以也无需在扩容后，再次从DB0中将数据迁移过来;
      
      为了不占用存储空间，也可以在凌晨两点到六点这段业务低峰期，去跑脚本删除重复的数据，因为目前DB0、DB2之间的数据完全相同，都包含了对方要负责的分片数据，所以在跑脚本的时候就是要从自身库中删除对方的数据；
      
      但这种从库升级扩容法有一种弊端会浪费一杯的机器，所以适用于一些流量特别大的场景；
    - 若不采用从库升级扩容法而采用双倍扩容法，需要手动同步原本库中的数据，若数据量较大时，数据迁移时间会较长，所以只能做离线迁移，单在离线迁移的过程中，线上数据还存在变更可怜，所以离线迁移后还需要河段数据的一致性，过程较为繁琐；
  1. 适用于水平分库后的第N次扩容
2. 异步双写扩容法：
  1. 就是在需要扩容的时候，将新数据写入到老库中，之后通过mq进行同步新库，并且在这期间新库回去同步老库中原有数据，最后待数据同步完成后，再以老库为基准校验数据，再将模式切换为扩容后的分库模式
  2. 主要的流程就是：
    - 第一步：修改应用服务代码，加上MQ双写方案，配置新库同步老库数据，然后部署。
    - 第二步：等待新库同步复制老库中所有老数据，期间新写入的数据也会通过MQ写入新库。
    - 第三步：老库中的所有老数据全部同步完成后，以老库作为校验基准，校对新库中的数据。
    - 第四步：校对新老库之间的数据无误后，修改应用配置和代码，将双写改为路由分片，再次部署。
  3. 更适用于垂直分库后的第一次单节点扩容

分表方案

主要针对单表字段过多或者数据量过大的情况，通过垂直分表和水平分表的手段，能很好的解决单表由于字段、数据量多的产生的一系列负面影响，需要建立在单库压力不高，且单表性能不够的情况下进行；

垂直分表

解决的问题：当一张表中字段过多，或存在某些不经常访问且体积较大的字段时，会导致每一行数据体积变大，从而出现磁盘IO瓶颈问题；所以在单表字段过多，或某些字段体积大等情况时可以考虑垂子分表；
拆分方式：将字段拆分到不同的表中存储，一般按照字段冷热（访问频率）作为拆分条件；
使用场景：若一个用户信息中存在着账号名、年龄、头像等基本信息，也存在个性签名、个人介绍、教育信息等详情信息，一般来说，账号名、年龄、头像等基本信息是访问频繁的数据，而详情信息访问频率一般不会很高且体积较大，此时就可以将用户数据会分为users、user_infos；
带来的问题：
1. 当试图获取一条完整数据时，需要查询多个表来获取数据，在表切分需设置好映射的外键字段；
2. 在增、删、改时一般需要同时操作多个表，需要开启事物来保证数据的一致性；

水平分表

解决的问题：当单表数据数据行过多时，即使数据库中有索引也需要进行多次IO得到数据，并且查询SQL可能还伴随着分组、排序、过滤、函数处理、连表查询等操作，这种情况下及时走了索引效率也不好很高；
拆分方式：将表中的数据按照一定的策略分为多个表，可以使用ID来进行数据分表，也可以按照时间周期来分表，还可以根据不同的用户或者是不同的租户进行分表；水平拆分之后的两张或多张表，每张表的表、索引等结构完全相同，各表之间不同的地方在于数据，每张表中会存储不同范围的数据，不过拆分之后的水平表究竟会存储哪个范围的数据，是根据分表时的策略来决定的；
使用场景：假设有一张30000w数据的表，我们可以将这张3000w数据的表拆分成6张500w数据的表，假设是通过自增id去划分，我们可以使用id/500w来判断数据属于哪张表；
带来的问题：
1. 多表联查问题：水平分表后，同一张业务表存在多个小表，此时需要确定连表查询时去查询哪张小表；
  1. 若分表数量固定，可以直接对所有表进行连接查询，但这种方式性能开销较大，无法发挥水平分表的作用；
  2. 根据分表规则确定要连接哪张表后再去查询；
  3. 如果联查的字段数据很少且字段体积不大的情况下，可以将数据冗余到当前表，从而避免连表查询；
2. 增删改数据问题：跟查询问题一样，需要先确定需求操作哪张表；当存在批量操作时，也需要先定位到具体表；
3. 聚合操作：水平分表后，若想对数据进行聚合操作之前给予单表的sum()、count()、order by、gorup by等各类聚合操作时会存在问题；
  1. 依赖第三方中间件实现，如将数据放入ES中；需考虑数据一致性问题；
  2. 定时跑脚本，将一些常用的聚合数据放入缓存中，然后从缓存中读取；数据存在一定的延迟性；
  3. 从各表中查询出各自数据，然后放入java中进行聚合操作；获取数据的耗时较长；