当前位置：首页 > news >正文

Mysql读写分离分库分表

news 2025/7/15 2:10:10

读写分离

什么是读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能。一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

如何实现读写分离

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

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

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

代理方式

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。提供类似功能的中间件有MySQL Router（官方， MySQL Proxy 的替代方案）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。

组件方式

这也是比较推荐的一种方式，可以通过引入第三方组件来帮助我们读写请求。推荐使用sharding-jdbc ，直接引入jar包即可使用，非常方便。同时，也节省了很多运维的成本。

主从复制原理是什么

binlog主要记录了MySQL数据库中数据的所有变化(所有DDL和DML语句)。因此，我们根据主库的binlog就能够将主库的数据同步到从库中。

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

如何避免主从延迟

读写分离对于提升数据库的并发非常有效，但是，同时也会引来一个问题：主库和从库的数据同步存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。如果我们的业务场景无法容忍主从同步延迟的话，应该如何避免呢？

强制将读请求路由到主库处理

这种方案虽然会增加主库的压力，但是，实现起来比较简单，也是我了解到的使用最多的一种方式。比如Sharding-JDBC就是采用的这种方案。通过使用Sharding-JDBC的HintManager分片键值管理器，我们可以强制使用主库。

延迟读取

如果业务上允许的话，既然主从同步存在延迟，那就在延迟之后读取，比如主从同步延迟0.5s，那我就1s之后再读取数据。

不过对于一些对数据比较敏感的场景，可以在完成写请求之后避免立即进行请求操作。比如你支付成功之后，跳转到一个支付成功的页面，当你点击返回之后才返回自己的账户。

什么情况下会出现主从延迟？如何尽量减少延迟

要搞懂什么情况下会出现主从延迟，我们需要先搞懂什么是主从延迟。

MySQL主从同步延时是指从库的数据落后于主库的数据，这种情况可能由以下两个原因造成：

从库I/O线程接收binlog的速度跟不上主库写入binlog的速度，导致从库relay log的数据滞后于主库binlog的数据；
从库SQL线程执行relay log的速度跟不上从库I/O线程接收binlog的速度，导致从库的数据滞后于从库relay log的数据。

与主从同步有关的时间点主要有3个：

主库执行完一个事务，写入binlog，将这个时刻记为T1；
从库I/O线程接收到binlog并写入relay log的时刻记为T2；
从库SQL线程读取relay log同步数据本地的时刻记为T3。

可以得出：

T2和T1的差值反映了从库I/O线程的性能和网络传输的效率，这个差值越小说明从库I/O线程的性能和网络传输效率越高。
T3和T2的差值反映了从库SQL线程执行的速度，这个差值越小，说明从库SQL线程执行速度越快。

那什么情况下会出现出从延迟呢？这里列举几种常见的情况：

从库机器性能比主库差：从库接收binlog并写入relay log以及执行SQL语句的速度会比较慢，进而导致延迟。解决方法是选择与主库一样规格或更高规格的机器作为从库，或者对从库进行性能优化，比如调整参数、增加缓存、使用SSD等。
从库处理的读请求过多：从库需要执行主库的所有写操作，同时还要响应读请求，如果读请求过多，会占用从库的CPU、内存、网络等资源，影响从库的复制效率。解决方法是引入缓存（推荐）、使用一主多从的架构，将读请求分散到不同的从库，或者使用其他系统来提供查询的能力，比如将binlog接入到Hadoop、Elasticsearch等系统中。
大事务：运行时间比较长，长时间未提交的事务就可以称为大事务。由于大事务执行时间长，并且从库上的大事务会比主库上的大事务花费更多的时间和资源，因此非常容易造成主从延迟。解决办法是避免大批量修改数据，尽量分批进行。类似的情况还有执行时间较长的慢SQL ，实际项目遇到慢SQL应该进行优化。
从库太多：主库需要将binlog同步到所有的从库，如果从库数量太多，会增加同步的时间和开销。解决方案是减少从库的数量，或者将从库分为不同的层级，让上层的从库再同步给下层的从库，减少主库的压力。
网络延迟：如果主从之间的网络传输速度慢，或者出现丢包、抖动等问题，那么就会影响binlog的传输效率，导致从库延迟。解决方法是优化网络环境，比如提升带宽、降低延迟、增加稳定性等。
单线程复制：MySQL5.5 及之前，只支持单线程复制。为了优化复制性能，MySQL 5.6 引入了多线程复制，MySQL 5.7 还进一步完善了多线程复制。
复制模式：MySQL默认的复制是异步的，必然会存在延迟问题。全同步复制不存在延迟问题，但性能太差了。半同步复制是一种折中方案，相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟。MySQL 5.5 开始，MySQL 以插件的形式支持semi-sync半同步复制。并且，MySQL 5.7 引入了增强半同步复制。

分库分表

什么是分库

分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

水平分库是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

什么是分表

分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表是对数据表列的拆分，把一张列比较多的表拆分为多张表。

水平分表是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

什么情况下需要分库分表

遇到下面几种场景可以考虑分库分表：

单表的数据达到千万级别以上，数据库读写速度比较缓慢。
数据库中的数据占用的空间越来越大，备份时间越来越长。
应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。

不过，分库分表的成本太高，如非必要尽量不要采用。而且，并不一定是单表千万级数据量就要分表，毕竟每张表包含的字段不同，它们在不错的性能下能够存放的数据量也不同，还是要具体情况具体分析。

常见的分片算法有哪些

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。

常见的分片算法有：

哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。
范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如将id为1~299999的记录分到第一个表，300000~599999的分到第二个表。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。
一致性哈希分片：将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。
地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
融合算法分片：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

分片键如何选择

分片键（Sharding Key）是数据分片的关键字段。分片键的选择非常重要，它关系着数据的分布和查询效率。一般来说，分片键应该具备以下特点：

具有共性，即能够覆盖绝大多数的查询场景，尽量减少单次查询所涉及的分片数量，降低数据库压力；
具有离散性，即能够将数据均匀地分散到各个分片上，避免数据倾斜和热点问题；
具有稳定性，即分片键的值不会发生变化，避免数据迁移和一致性问题；
具有扩展性，即能够支持分片的动态增加和减少，避免数据重新分片的开销。

实际项目中，分片键很难满足上面提到的所有特点，需要权衡一下。并且，分片键可以是表中多个字段的组合，例如取用户ID后四位作为订单ID后缀。

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

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

join操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。不过建议尽量不要使用join操作，因为join的效率低，并且会对分库分表造成影响。对于需要用到join操作的地方，可以采用多次查询业务层进行数据组装的方法。不过，这种方法需要考虑业务上多次查询的事务性的容忍度。
事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。这个时候，我们就需要引入分布式事务了。
分布式 ID：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式ID了。
跨库聚合查询问题：分库分表会导致常规聚合查询操作，如group by，order by等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序，而不是在单个数据库上进行。为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。

分库分表有没有什么比较推荐的方案

Apache ShardingSphere是一款分布式的数据库生态系统，可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

ShardingSphere绝对可以说是当前分库分表的首选！ShardingSphere的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。ShardingSphere提供的功能如下：在这里插入图片描述

分库分表后，数据怎么迁移呢

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨2点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计1小时。

然后，你写一个脚本将老库的数据都同步到新库中。如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。

具体原理是：

对老库的更新操作（增删改）同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。
在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具Canal做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

总结

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能。
读写分离基于主从复制，MySQL主从复制是依赖于binlog。
分库就是将数据库中的数据分散到不同的数据库上。分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。
引入分库分表之后，需要系统解决事务、分布式id、无法join操作问题。
现在很多公司都是用的类似于TiDB这种分布式关系型数据库，不需要我们手动进行分库分表，也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离）！如果公司条件允许的话，个人也是比较推荐这种方式！
如果必须要手动分库分表的话，ShardingSphere是首选！ShardingSphere的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。另外，ShardingSphere的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

数据冷热分离

什么是数据冷热分离

数据冷热分离是指根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。

冷数据和热数据

热数据是指经常被访问和修改且需要快速访问的数据，冷数据是指不经常访问，对当前项目价值较低，但需要长期保存的数据。

冷热数据到底如何区分呢？有两个常见的区分方法：

时间维度区分：按照数据的创建时间、更新时间、过期时间等，将一定时间段内的数据视为热数据，超过该时间段的数据视为冷数据。例如，订单系统可以将1年前的订单数据作为冷数据，1年内的订单数据作为热数据。这种方法适用于数据的访问频率和时间有较强的相关性的场景。
访问频率区分：将高频访问的数据视为热数据，低频访问的数据视为冷数据。例如，内容系统可以将浏览量非常低的文章作为冷数据，浏览量较高的文章作为热数据。这种方法需要记录数据的访问频率，成本较高，适合访问频率和数据本身有较强的相关性的场景。

几年前的数据并不一定都是热数据，例如一些优质文章发表几年后依然有很多人访问，大部分普通用户新发表的文章却基本没什么人访问。这两种区分冷热数据的方法各有优劣，

实际项目中，可以将两者结合使用。

冷热分离的思想

冷热分离的思想非常简单，就是对数据进行分类，然后分开存储。冷热分离的思想可以应用到很多领域和场景中，而不仅仅是数据存储，例如：

邮件系统中，可以将近期的比较重要的邮件放在收件箱，将比较久远的不太重要的邮件存入归档。
日常生活中，可以将常用的物品放在显眼的位置，不常用的物品放入储藏室或者阁楼。
图书馆中，可以将最受欢迎和最常借阅的图书单独放在一个显眼的区域，将较少借阅的书籍放在不起眼的位置。

数据冷热分离的优缺点

优点：热数据的查询性能得到优化（用户的绝大部分操作体验会更好）、节约成本（可以冷热数据的不同存储需求，选择对应的数据库类型和硬件配置，比如将热数据放在 SSD 上，将冷数据放在HDD上）
缺点：系统复杂性和风险增加（需要分离冷热数据，数据错误的风险增加）、统计效率低（统计的时候可能需要用到冷库的数据）。

冷数据如何迁移

业务层代码实现：当有对数据进行写操作时，触发冷热分离的逻辑，判断数据是冷数据还是热数据，冷数据就入冷库，热数据就入热库。这种方案会影响性能且冷热数据的判断逻辑不太好确定，还需要修改业务层代码，因此一般不会使用。
任务调度：可以利用xxl-job或者其他分布式任务调度平台定时去扫描数据库，找出满足冷数据条件的数据，然后批量地将其复制到冷库中，并从热库中删除。这种方法修改的代码非常少，非常适合按照时间区分冷热数据的场景。
监听数据库的变更日志binlog ：将满足冷数据条件的数据从binlog中提取出来，然后复制到冷库中，并从热库中删除。这种方法可以不用修改代码，但不适合按照时间维度区分冷热数据的场景。

冷数据如何存储

冷数据的存储要求主要是容量大，成本低，可靠性高，访问速度可以适当牺牲。

冷数据存储方案：

中小厂：直接使用 MySQL/PostgreSQL 即可（不改变数据库选型和项目当前使用的数据库保持一致），比如新增一张表来存储某个业务的冷数据或者使用单独的冷库来存放冷数据（涉及跨库查询，增加了系统复杂性和维护难度）
大厂：Hbase（常用）、RocksDB、Doris、Cassandra

如果公司成本预算足的话，也可以直接上TiDB这种分布式关系型数据库，直接一步到位。TiDB6.0 正式支持数据冷热存储分离，可以降低SSD使用成本。使用TiDB 6.0的数据放置

功能，可以在同一个集群实现海量数据的冷热存储，将新的热数据存入SSD，历史冷数据存入HDD。