MySQL高可用与分库分表海量数据解决方案作者黒漂技术佬适用读者单机数据量过千万变慢、想搞分库分表但没头绪的同学关联场景无人售货柜全国订单库、智慧农业多年历史数据一、单机MySQL的瓶颈什么时候该上分布式单机MySQL不是不行而是有天花板。三道天花板天花板1数据量 - 单表超过5000万行索引再好查询也开始变慢 - B树层级从3层涨到4层多一次磁盘IO - 售货柜订单每天100万行一年3.65亿行 → 单表炸了 天花板2并发量 - 单机QPS理论5万实际2-3万就开始排队 - 秒杀/双11场景瞬时10万QPS → 单机扛不住 天花板3可用性 - 一台机器挂了就停服 - 不论是硬件故障还是机房断电都是单点遇到这三道墙就要从两个方向解决高可用解决单点故障 分库分表解决容量和并发。二、高可用方案让MySQL不挂、挂了能切2.1 MHAMaster High Availability老牌主从切换方案。MHA Manager监控主库主库挂掉时自动选一个从库提升为主。MHA架构 MHA Manager独立监控节点 ↓ 监控 主库 ←→ 从库1 / 从库2 / 从库3 主库挂了 1. MHA选binlog最新的从库从库2 2. 把从库1、从库3的relay log补到从库2 3. 从库2提升为新主 4. 从库1、3重新指向新主优点成熟稳定社区资料多。缺点Manager是单点切换有秒级数据丢失可能异步复制。2.2 MGRMySQL Group ReplicationMySQL官方组复制多个节点用Paxos变体协议同步数据自带故障检测和切换。MGR集群 节点1 ←→ 节点2 ←→ 节点3 Paxos共识 3节点挂1个还能用过半数2/3 5节点挂2个还能用过半数3/5优点强一致写要过半数节点确认自带切换无外部组件。缺点写性能受共识协议拖累跨机房延迟敏感。2.3 Orchestrator开源的MySQL拓扑管理和故障切换工具提供Web界面可视化操作。Orchestrator能做 - 实时显示主从拓扑 - 拖拽手动切换主库 - 故障自动切换可配置策略 - 拓扑重构从库改挂到别的节点方案强一致性切换速度复杂度适合MHA弱异步30秒级中老项目兼容MGR强Paxos秒级中高强一致场景Orchestrator弱异步秒级中需要可视化管理选型建议新项目首选MGR官方支持强一致无外部依赖。老系统维护选MHA或Orchestrator。金融级强一致还得配合半同步或MGR。三、分库分表策略垂直 vs 水平分库分表有两大方向垂直拆分按业务/字段拆和水平拆分按数据行拆。3.1 垂直分库按业务拆把一个库里不同业务模块的表拆到不同库。拆分前单库shop shop.user 用户表 shop.product 商品表 shop.orders 订单表 shop.payment 支付表 shop.cabinet 售货柜表 拆分后按业务分库 user_db → user, address product_db → product, sku order_db → orders, order_item pay_db → payment, refund cabinet_db → cabinet, device好处业务隔离一个库挂了不影响其他业务按业务分配资源。坏处跨库JOIN做不了要用接口调用或冗余字段。3.2 垂直分表按字段拆把一张表的字段拆成多张表按热度分。拆分前product表字段太多 product_id, name, price, stock, weight, description(大字段), image_url(大字段), create_time 拆分后 product热数据 product_id, name, price, stock, weight, create_time product_detail冷数据大字段 product_id, description, image_url好处热表行变小一页能放更多行查询效率高。坏处查详情要JOIN两张表。3.3 水平分表按行拆把一张表的数据按某种规则分散到多张表。这是真正解决单表数据量爆炸的招数。分表策略1按ID取模orders表拆成8张orders_0 ~ orders_7 插入订单ID1001 1001 % 8 1 → 写入 orders_1 查询订单ID1001 1001 % 8 1 → 从 orders_1 查分表策略2按范围分表orders按时间范围分表 orders_2024_01 → 1月订单 orders_2024_02 → 2月订单 ... orders_2024_12 → 12月订单 查1月订单 → 直接查 orders_2024_01 跨月查询 → UNION ALL 多张表策略优点缺点适合取模数据均匀分布扩容要重新分布数据ID明确、查询点查多范围扩容简单加新表容易热点最新表压力大时间序列数据工程经验售货柜订单天然适合按时间范围分表按月分表因为订单查询绝大多数是按时间范围筛选。按取模分表适合用户ID点查场景。四、分库分表带来的四大难题分库分表不是免费的午餐带来一堆新问题。4.1 跨库JOIN做不了分库后 orders 在 order_db user 在 user_db product 在 product_db 查询订单列表用户名商品名 原本一条JOIN SQL搞定 现在跨库JOIN不了 → 要应用层组装解法应用层组装分别查三个库在内存里拼装。冗余字段订单表冗余存user_name和product_name避免关联查询。宽表/数据湖把需要JOIN的数据同步到ES或数仓做宽表查询。4.2 分布式事务下单涉及 order_db.orders → 插订单 product_db.product → 扣库存 pay_db.payment → 创建支付单 三个库在不同MySQL实例本地事务管不了 → 要分布式事务解法2PC两阶段提交协调者统一提交性能差。TCCTry-Confirm-Cancel业务层补偿复杂但性能好。Seata AT模式自动生成补偿SQL开发友好。本地消息表MQ最终一致性最常用。4.3 全局ID单库时用自增ID分库后每个库各自自增会重复。解法方案1UUID 优点不重复 缺点无序、索引碎片、查询慢 方案2Snowflake雪花算法 64位 时间戳(41位) 机器ID(10位) 序列号(12位) 优点有序、全局唯一、高性能 缺点依赖机器时钟 方案3数据库号段 预分配一段ID给应用用完再申请 优点简单可靠 缺点扩容要小心4.4 分页查询变难原分页SELECT * FROM orders LIMIT 10000, 20 分表后8张表 每张表都 LIMIT 10000, 20 → 拿到8×20160条 应用层排序后取第10001~10020条 → 第1页没问题第10000页要拉8×10020条排序慢到爆炸解法限制最大翻页数、用游标分页last_id方式、走搜索引擎。五、ShardingSphere分库分表实战5.1 准备工作假设要分8库×4表存售货柜订单。先规划分库规则cabinet_id % 8 → 分到8个库 分表规则order_id % 4 → 分到4张表5.2 配置spring:shardingsphere:datasource:names:ds0,ds1,ds2,ds3,ds4,ds5,ds6,ds7ds0:type:com.zaxxer.hikari.HikariDataSourcejdbc-url:jdbc:mysql://db0:3306/order_db_0username:rootpassword:xxx# ... ds1~ds7 类似rules:sharding:tables:orders:actual-data-nodes:ds${0..7}.orders_${0..3}database-strategy:standard:sharding-column:cabinet_idsharding-algorithm-name:cabinet-modtable-strategy:standard:sharding-column:order_idsharding-algorithm-name:order-modsharding-algorithms:cabinet-mod:type:MODprops:sharding-count:8order-mod:type:MODprops:sharding-count:4key-generators:# 全局IDsnowflake:type:SNOWFLAKE5.3 代码使用// 应用代码完全无感知分库分表ServicepublicclassOrderService{AutowiredprivateOrderMapperorderMapper;publicvoidcreateOrder(Orderorder){// order_id用雪花算法生成配置里指定// ShardingSphere根据cabinet_id路由到对应库// 根据order_id路由到对应表orderMapper.insert(order);}publicOrdergetById(LongcabinetId,LongorderId){// 必须带cabinet_id否则广播到8库32表查 → 性能差returnorderMapper.selectByCabinetAndOrder(cabinetId,orderId);}}关键点分库分表后查询尽量带上分片键cabinet_id。不带分片键的查询会广播到所有分片性能急剧下降。5.4 分片键选择分片键选错了分库分表基本失败。售货柜订单表分片键选择 候选1order_id → 查询时如果只按order_id查会广播 候选2cabinet_id → 大多数查询都带cabinet_id按门店查订单好 候选3user_id → 跨门店用户消费分析看场景 最优解用cabinet_id做分片键 → 售货柜维度查询占90%都能精准路由分片键选择原则选业务查询最常带的字段。售货柜场景门店维度查询最多选cabinet_id。六、分库分表后的数据迁移方案6.1 迁移挑战老的单库表有几亿行数据怎么平滑迁移到分库分表还不能停服。迁移难点 1. 几亿行数据迁移可能要几小时 2. 迁移期间还在写新数据怎么保证不丢 3. 迁移后要校验数据一致性 4. 切换时业务不能中断6.2 双写迁移方案推荐步骤1建分库分表新结构老库不动步骤2应用层双写TransactionalpublicvoidcreateOrder(Orderorder){// 写老库oldOrderMapper.insert(order);// 同时写新分库分表newShardingOrderMapper.insert(order);}步骤3数据全量同步用DataX或自研脚本把老库存量数据同步到新分库分表按分片规则路由。步骤4增量同步补偿用Canal监听老库binlog把增量变更同步到新库。或继续靠双写。步骤5读切流量灰度灰度策略 1. 10%读流量切到新库观察数据一致性 2. 50%读流量切到新库 3. 100%读流量切到新库 4. 下线老库双写 5. 老库归档步骤6清理双写新库稳定后去掉老库双写代码老库归档或下线。迁移期间最难的是数据校验。用自研脚本抽样比对或用DataX的校验功能。任何不一致要停下来排查不能强行切换。七、总结概念一句话高可用方案MHA老、MGR新强一致、Orchestrator可视化垂直分库按业务拆库解耦但跨库JOIN难垂直分表按字段热度拆表热表变小、查询快水平分表按ID取模或范围拆解决单表数据量四大难题跨库JOIN、分布式事务、全局ID、分页ShardingSphereJDBC层透明分库分表应用无感数据迁移双写全量同步灰度切流量分库分表是MySQL走向大规模的必经路但代价不小——一旦分了复杂度永久上升。先用单机主从撑撑不住再分。下一篇聊性能调优把单机压榨到极限。