mysql高可靠性

1、分库与分表的设计一、分库分表方案水平分库以字段为依据按照一定策略hash、range 等将一个库中的数据拆分到多个库中。水平分表以字段为依据按照一定策略hash、range 等将一个表中的数据拆分到多个表中。垂直分库以表为依据按照业务归属不同将不同的表拆分到不同的库中。垂直分表以字段为依据按照字段的活跃性将表中字段拆到不同的表主表和扩展表中。二、分库分表的优点和目的分库分表的根本目的是突破单库单表的性能与容量瓶颈。当数据量、并发量增长到单机无法承载时通过拆分把压力分散到多个数据库实例或多张表上。三、分库分表的代价四、如何解决分库分表查询问题核心原则跨库 JOIN 的解决方案方案 1字段冗余思路把需要 JOIN 的字段直接冗余到主表。例子订单表冗余 user_name查询时不用 JOIN 用户表。优点查询简单、性能高、无跨库。缺点数据冗余、源数据变更需同步。适用冗余不常变更的字段用户名、商品名。方案 2广播表 / 全局表思路小表在每个分片库都存一份JOIN 在本库完成。例子字典表、配置表、地区表在每个库冗余。优点JOIN 无跨库、性能好。缺点需同步到每个分片。适用数据量小、变更极少的表。方案 3ER 分片绑定表思路关联表用同一个分片键让关联数据落在同一分片。例子订单表和订单明细表都按 user_id 分片。优点JOIN 在本分片完成、性能好。缺点分片键选择受限。适用有明确主子关系的表订单 明细。方案 4数据异构到 ES思路Canal 监听 binlog同步到 ES 建宽表JOIN 在 ES 完成。例子订单、用户、商品同步到 ES 宽表查询走 ES。优点支持任意复杂 JOIN、分页、聚合。缺点需维护 ES、数据准实时、最终一致。适用后台管理、多条件筛选、全文检索。方案 5应用层组装思路拆成多次单库查询应用层代码拼装。例子先查订单再用 user_id 查用户最后组装 VO。优点实现简单、灵活。缺点多次网络往返、N1 问题。适用少量数据的关联查询。跨库查询的解决方案方案 1分片键精准路由思路查询条件带分片键直接路由到单个分片。例子按 user_id 分片查询带 user_id 就能精准定位。优点性能最高、无跨分片。缺点不带分片键的查询无法路由。方案 2基因法思路把分片键的片段编码进业务 ID按业务 ID 也能路由。例子order_id 里编入 user_id 基因按订单号查也能定位分片。优点两种查询都能精准路由。缺点ID 生成复杂。方案 3全分片扫描 内存归并思路查询不带分片键时所有分片都查一遍结果在内存归并。优点实现简单。缺点性能差、分片越多越慢。适用低频查询。方案 4ES 异构查询思路复杂查询统一走 ES。优点支持任意条件、分页、聚合。缺点需维护 ES、准实时。方案 5广播表思路小表在每个分片冗余查询本库完成。适用字典表、配置表。五、如何解决分库分表事务问题分库分表后的事务问题本质是跨库操作无法用单机 ACID 保证一致性。解决思路分两层第一层是设计上尽量避免跨库事务第二层才是用分布式事务方案兜底。一、根本原则尽量避免跨库事务最好的分布式事务方案是不需要分布式事务。设计阶段就要让一个事务的数据落在同一分片ER 分片关联数据同分片订单表和订单明细表用同一个分片键 user_id 同一用户的订单和明细落在同一分片本地事务就能搞定。 order_db_0: order_0, order_item_0 ← user_id %20order_db_1: order_1, order_item_1 ← user_id %21字段冗余减少跨库更新收敛强一致操作到单分片把需要强一致的操作用同一分片键路由保证落在同一库。二、分布式事务方案六、分库分表中间件2、Mysql主从复制原理MySQL 主从复制Replication是构建高可用、读写分离、数据备份架构的基础。核心原理是主库把数据变更记录到 binlog从库拉取并重放这些日志保持数据一致。一、复制流程第一步主库写 binlog主库执行写操作INSERT/UPDATE/DELETE时事务提交前把变更写入 binlogbinlog 按顺序记录所有变更主库的 dump 线程 负责把 binlog 发送给从库主库 写操作 → binlog → dump 线程 → 发送给从库第二步从库拉取 binlogI/O 线程从库的 I/O 线程连接主库请求从指定位置binlog 文件名 position开始的 binlog主库 dump 线程把 binlog 发给从库从库 I/O 线程把收到的 binlog 写入本地 relay log更新 master.info记录已拉取到的位置第三步从库重放 relay logSQL 线程从库的 SQL 线程读取 relay log解析出具体的 SQL 或行变更在从库上重放保持数据一致更新 relay-log.info记录已重放到的位置二、binlog 的三种格式三、复制模式异步复制默认主库写完 binlog 就返回不等从库确认优点性能高缺点主库宕机可能丢数据半同步复制主库等至少一个从库收到 binlog 并写入 relay log 后才返回优点减少丢数据风险缺点性能略降全同步复制主库等所有从库都重放完成才返回优点最强一致缺点性能最差很少用组复制MGRMySQL 5.7 官方高可用方案基于 Paxos 协议支持多主/单主自动故障检测和切换四、主从延迟定义主从延迟指从库的数据落后于主库即主库已经提交的变更从库还没重放完成。后果读写分离下读到旧数据最常见的后果。写走主库读走从库数据不一致多个从库延迟不同读不同从库结果不一致故障切换丢数据主库宕机时从库还没同步完业务逻辑错误依赖从库做幂等判断 → 延迟导致重复处理解决方案读写分离 延迟感知写走主库读走从库中间件ProxySQL、ShardingSphere监控从库延迟延迟超过阈值时自动把读请求路由到主库半同步复制主库等至少一个从库确认收到 binlog 才返回减少主库宕机丢数据风险不直接解决延迟但提升数据安全性MGR组复制基于 Paxos 协议强一致自动故障检测和选主适合对一致性要求高的场景级联复制主库只同步给一个中间从库其他从库从中间从库同步减轻主库复制压力但中间层故障会影响下游多线程复制并行复制MySQL 5.7 支持多线程回放从库 SQL 线程从单线程变为多线程最有效的回放加速手段3、Mysql主主复制原理MySQL 主主复制双主复制Master-Master Replication本质上是两个主从复制关系的叠加节点 A 是节点 B 的主库节点 B 也是节点 A 的主库双方互为主从数据双向同步。每个节点既是 Master 也是 Slave所以必须同时开启log-bin作为主库记录 binlog relay-log作为从库存中继日志一、复制流程第一步A 写 binlog 节点A 写操作 → A 的 binlog → A 的 dump 线程 第二步B 拉取 A 的 binlog B 的 I/O 线程 → 连接 A → 请求 binlog → 写入 B 的 relay log 第三步B 重放 relay log B 的 SQL 线程 → 读 relay log → 重放 → B 数据更新 第四步B 自己写入时反过来同步回 A B 写操作 → B 的 binlog → A 的 I/O 线程拉取 → A 重放二、两个关键的防冲突设计既然两边都能写MySQL 用两个机制防止数据打架。防循环复制server-id每个 MySQL 实例必须有唯一的 server-idbinlog 事件里带着 server-id当节点收到自己发出去的事件时会直接忽略避免无限循环防自增主键冲突auto_increment_increment offset如果两边同时插入数据默认自增会生成相同的 ID。解决办法是让两边的 ID 错开生成server-id 和奇偶自增只能防自增主键插入冲突。 如果两边同时更新同一行或产生唯一键冲突 复制线程会直接报错停止 没有自动冲突解决机制三、实际使用Active-Passive 模式正因为冲突难以自动解决生产上极少让两个节点同时写入。标准做法是 Active-Passive主动-被动。┌─────────┐ ┌─────────┐ │ 节点A │ ←─────→ │ 节点B │ │(Active)│ 互相复制 │(Passive)│ │ 写读 │ │ 只读 │ └─────────┘ └─────────┘ ↑ VIP 漂移特点同一时刻只允许一个节点Active接受写请求另一个节点Passive设为 read_only只读或热备配合 Keepalived 提供 VIPActive 挂掉 → VIP 漂移到 Passive → 它接管写入四、主主 vs 主从