分库分表策略

我来详细讲解MySQL分库分表的策略、实现方式以及核心难点这是高并发系统设计的必考点。一、为什么需要分库分表┌─────────────────────────────────────────┐ │ 单表瓶颈InnoDB │ │ - 数据量5000万行BTree高度增加IO上升 │ │ - 单表文件10GB备份/DDL加索引耗时数小时 │ │ - 单库连接数上限默认151max_connections │ │ - 写瓶颈单机CPU/磁盘IO有上限 │ └─────────────────────────────────────────┘ ↓ ┌─────────────────────────────────────────┐ │ 分库分表目标 │ │ 1. 分散存储压力数据水平拆分 │ │ 2. 分散访问压力读写流量分散到多实例 │ │ 3. 突破单机连接数限制 │ └─────────────────────────────────────────┘二、分表 vs 分库 vs 分库分表方案定义解决问题复杂度分表单库内表拆分为多张单表数据量过大、索引膨胀低分库数据分散到多个数据库实例单库连接数瓶颈、写性能上限中分库分表先分库库内再分表数据量连接数双重瓶颈高三、分表策略单库内1. 垂直分表按列拆分┌─────────────────────────────┐ ┌─────────────────────────────┐ │ user表宽表 │ │ user_basic热数据 │ │ id, name, avatar, │ → │ id, name, avatar, phone │ │ phone, address, bio, │ │ 频繁查询常驻内存 │ │ login_log, order_history │ └─────────────────────────────┘ │ 100字段行大小4KB │ ┌─────────────────────────────┐ └─────────────────────────────┘ │ user_extra冷数据 │ │ id, address, bio, login_log │ │ 偶尔查询可存SSD或归档 │ └─────────────────────────────┘原则将访问频率不同、数据大小差异大的字段分离。热数据行小Buffer Pool命中率高冷数据可压缩存储甚至归档到HBase/ClickHouse2. 水平分表按行拆分┌─────────────────────────────┐ │ user表1000万行 │ └─────────────────────────────┘ ↓ 按 user_id % 4 ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │user_000│ │user_001│ │user_002│ │user_003│ │ 0-249w │ │250-499w│ │500-749w│ │750-999w│ └────────┘ └────────┘ └────────┘ └────────┘分片键选择user_id查询维度最频繁的字段四、分库策略多实例1. 垂直分库按业务域拆分┌─────────────────────────────────────────┐ │ 单体数据库所有业务 │ │ user | order | product | payment | log │ └─────────────────────────────────────────┘ ↓ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ user-db │ │ order-db │ │ product-db │ │ (用户核心) │ │ (订单交易) │ │ (商品库存) │ │ 主从复制 │ │ 主从复制 │ │ 主从复制 │ └─────────────┘ └─────────────┘ └─────────────┘特点业务解耦独立扩容跨库Join需应用层组装或用宽表/ES冗余分布式事务问题后文详述2. 水平分库同业务数据分散┌─────────────────────────────────────────┐ │ order-db单机瓶颈 │ │ 日增100万订单写入压力过大 │ └─────────────────────────────────────────┘ ↓ 按 user_id % 4 ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ order-db-00 │ │ order-db-01 │ │ order-db-02 │ │ order-db-03 │ │ user_id%40│ │ user_id%41│ │ user_id%42│ │ user_id%43│ │ (0,4,8...) │ │ (1,5,9...) │ │ (2,6,10..) │ │ (3,7,11..) │ └─────────────┘ └─────────────┘ └─────────────┘ └─────────────┘五、分片算法Sharding Algorithm1. 取模/哈希分片// 简单取模 int dbIndex userId % 4; // 分4库 int tableIndex (userId / 4) % 8; // 每库8表共32张表 // 一致性哈希解决扩容时数据迁移问题 // 节点增减只影响相邻区间无需全量迁移优点数据分布均匀点查高效缺点扩容时数据迁移量大需rehash范围查询需扫所有分片2. 范围分片// 按时间范围适合时序数据 db_2024_q1: 2024-01-01 ~ 2024-03-31 db_2024_q2: 2024-04-01 ~ 2024-06-30 // 按ID范围 db_0: user_id 0 ~ 1000万 db_1: user_id 1000万 ~ 2000万优点扩容简单新增区间即可范围查询友好缺点热点问题新数据集中在最新分片需配合读写分离3. 混合分片推荐// 先范围确定库再取模确定表 // 避免取模扩容迁移又避免范围热点 // 步骤1按时间范围选库如2024年数据在db_2024 String db db_ year; // 步骤2库内按user_id % 1024 分表 int table (userId % 1024);适用日志、订单等时间敏感型数据。4. 分片算法对比算法数据分布扩容迁移范围查询适用场景取模均匀大全量rehash差用户ID等离散键范围可能不均无新增区间优时间序列、日志一致性哈希较均匀小相邻节点差缓存分片Redis混合可控中等中等订单、交易流水六、分库分表实现方式方式1客户端Sharding应用层// 使用ShardingSphere-JDBC轻量无中间件依赖 Configuration public class ShardingConfig { Bean public DataSource shardingDataSource() throws SQLException { // 配置分片规则 ShardingRuleConfiguration rule new ShardingRuleConfiguration(); // 分库策略user_id取模 rule.setDefaultDatabaseShardingStrategy( new StandardShardingStrategyConfiguration( user_id, new InlineShardingStrategyConfiguration(ds_${user_id % 4}) ) ); // 分表策略每库8表 rule.setDefaultTableShardingStrategy( new StandardShardingStrategyConfiguration( user_id, new InlineShardingStrategyConfiguration(user_${user_id % 32}) ) ); return ShardingSphereDataSourceFactory.createDataSource( createDataSourceMap(), Collections.singleton(rule), new Properties() ); } }优点性能损耗低直接路由到真实数据源无单点缺点配置侵入代码语言绑定Java方式2代理中间件Proxy┌─────────┐ ┌─────────────┐ ┌─────────┐ ┌─────────┐ │ 应用 │────→│ Sharding │────→│ MySQL-0 │ │ MySQL-1 │ │ (任意语言)│ │ -Proxy │ │ 主从 │ │ 主从 │ └─────────┘ │ (独立进程) │ └─────────┘ └─────────┘ └─────────────┘ 自动解析SQL路由改写优点对应用透明语言无关集中管控缺点多一层网络跳转延迟~1msProxy本身高可用需保障代表产品ShardingSphere-Proxy、MyCat、Vitess方式3云原生/数据库中间件NewSQL-- TiDB/OceanBase 自动分片应用无感知 CREATE TABLE user ( id BIGINT PRIMARY KEY, name VARCHAR(50) ) ENGINEInnoDB PARTITION BY HASH(id) PARTITIONS 16; -- 自动分布式 -- 应用像访问单机MySQL一样访问TiDB优点完全透明自动扩缩容强一致分布式事务缺点成熟度、生态、运维复杂度七、核心难点与解决方案难点1分布式主键全局唯一ID// 方案1雪花算法Snowflake- 推荐 // 41位时间戳 10位机器ID 12位序列号 64位Long // 优点趋势递增插入性能高避免BTree频繁分裂 // 缺点依赖时钟时钟回拨会重复需NTP校准或等待 // 方案2号段模式Leaf // 从DB批量获取ID区间如[1000,2000)内存分配 // 优点性能极高无时钟依赖 // 缺点需额外服务美团Leaf // 方案3数据库自增 步长 // DB-0: 1, 5, 9... (step4, offset1) // DB-1: 2, 6, 10... (step4, offset2) // 缺点扩展困难非连续难点2跨分片查询/聚合-- 场景查询所有用户的订单总额需聚合4库8表共32张表 SELECT user_id, SUM(amount) FROM order GROUP BY user_id; -- 方案1应用层聚合ShardingSphere自动处理 // Proxy层并行查询各分片内存归并结果 // 缺点大数据量时内存压力大延迟高 -- 方案2异构索引表冗余存储 // 将聚合结果实时同步到ClickHouse/ES // 查询走OLAP引擎原始数据走MySQL分片 -- 方案3限制查询维度 // 强制带分片键WHERE user_id ?点查 // 禁止无分片键的全局扫描或走离线数仓难点3分布式事务// 场景下单扣库存跨order-db和inventory-db // 方案1最终一致性Saga/TCC- 互联网主流 Compensable(confirmMethod confirm, cancelMethod cancel) public void createOrder(Order order) { orderService.save(order); // 本地事务 inventoryService.deduct(order); // RPC调用可能失败 } // 失败时执行cancel回滚订单 释放库存 // 方案2Seata AT模式自动补偿 // 代理数据源解析SQL生成Undo Log全局协调器驱动二阶段提交 // 方案3XA协议强一致性能差 // 两阶段提交阻塞协议很少用于高并发场景难点4数据迁移与扩容平滑扩容流程取模→取模如4库扩8库 1. 双写阶段2周 应用层同时写旧分片4库和新分片8库 读走旧分片保证一致性 2. 历史数据迁移 脚本将旧4库数据rehash到新8库 对比校验MD5/行数 3. 切读阶段 灰度将读流量切到新8库 观察一周比对数据一致性 4. 停写旧库 关闭双写只写新8库 保留旧库备份1月后清理八、分库分表设计 checklist1. 是否真的需要 - 数据量1000万先优化索引/SQL加缓存读写分离 - QPS1万单机主从足够 2. 分片键选择 - 查询维度最多的字段如user_id - 避免跨分片查询如按user_id分片但大量查询按time_range 3. 分几片 - 预估3-5年数据量取模数选2^n方便后续分裂 - 示例当前500万/年预期5年2500万分32片每片~80万健康 4. 全局表小数据量配置表 - 每个库冗余一份避免跨库Join - 或放分布式缓存Redis 5. 非分片键查询 - 建立异构索引表如按手机号查询需额外维护mobile→user_id映射 - 或同步到Elasticsearch九、高频面试追问Q分库分表后如何分页查询-- 场景ORDER BY time LIMIT 100000, 10深分页 -- 问题需每个分片查100010条内存归并排序性能极差 -- 方案1禁止深分页产品层限制只能前100页 -- 方案2游标分页上次最后一条的time作为起点 WHERE time 2024-01-01 12:00:00 ORDER BY time DESC LIMIT 10; -- 方案3聚合到ES/ClickHouse分库分表只支持点查Q分库分表后Join怎么办// 同库JoinShardingSphere支持如果关联表分片策略相同 // 跨库Join应用层组装或宽表冗余 // 反范式设计空间换时间 // 订单表冗余用户姓名、商品名称避免Join CREATE TABLE order ( order_id BIGINT, user_id BIGINT, user_name VARCHAR(50), -- 冗余避免查user表 product_id BIGINT, product_name VARCHAR(100), -- 冗余 ... );Q分库分表和NewSQLTiDB怎么选维度分库分表ProxyNewSQLTiDB成熟度高生态完善中快速迭代运维成本高自研Proxy、扩容脚本低自动化性能接近原生MySQL~70%原生MySQL分布式事务开销复杂查询受限跨分片性能差友好自动分布式执行计划团队规模需DBA中间件专家可少配DBA建议已有成熟MySQL生态团队强 → 分库分表新业务团队小想快速上线 → TiDB/OceanBase分库分表是**最后一招**在此之前应穷尽优化手段索引、缓存、读写分离、归档。一旦拆分架构复杂度不可逆上升需配套完善的运维体系和监控能力。