← 返回首页 📚 实战课程
📑 本篇章节导航 (共5章,点击直达)
本节目标(学完能回答的面试题)核心知识(1小时精讲内容)面试速记卡(考前30秒速看)练习题(自测)学习资料

L18 - MySQL分库分表

本节目标(学完能回答的面试题)

核心知识(1小时精讲内容)

一、何时需要分库分表


触发条件(满足任一):
┌─────────────────────────────────────────────────┐
│ 1. 单表数据超过 500万行 / 单表超过 5GB            │
│ 2. QPS超过单机承载能力(通常5000~10000)            │
│ 3. 数据库连接数成为瓶颈                           │
│ 4. 慢SQL优化到极致仍然无法满足性能要求             │
└─────────────────────────────────────────────────┘

不分库分表的替代方案(优先考虑):
1. 读写分离(主库写,从库读) — QPS问题
2. 数据归档(冷热分离) — 数据量问题
3. 缓存(Redis) — 热点数据问题
4. 分区表(PARTITION) — 简单数据量问题

结论: 分库分表是最后手段,能不分就不分

二、拆分策略

2.1 垂直拆分


垂直分库: 按业务模块拆分到不同数据库
┌──────────────┐
│   单库        │
│ ┌──┬──┬──┐   │
│ │用│订│商│   │
│ │户│单│品│   │
│ └──┴──┴──┘   │
└──────────────┘
       │ 垂直分库
       ▼
┌──────┐ ┌──────┐ ┌──────┐
│用户库 │ │订单库 │ │商品库 │  每个库独立部署
└──────┘ └──────┘ └──────┘

垂直分表: 按字段冷热拆分到不同表
┌───────────────────────────┐
│ user表(全部字段)            │
│ id|name|age|bio|avatar|...│  bio/avatar大字段,不常用
└───────────────────────────┘
         │ 垂直分表
         ▼
┌──────────────┐  ┌────────────────┐
│ user表(热数据) │  │ user_ext表(冷) │
│ id|name|age   │  │ id|bio|avatar  │
└──────────────┘  └────────────────┘

2.2 水平拆分


水平分表: 同结构数据按规则分散到多个表/库

┌─────────────┐
│ order表      │
│ (5000万行)   │
└─────────────┘
       │ 水平分表(按order_id取模)
       ▼
┌────────┐┌────────┐┌────────┐┌────────┐
│t_order_0││t_order_1││t_order_2││t_order_3│
│~1250万行││~1250万行││~1250万行││~1250万行│
└────────┘└────────┘└────────┘└────────┘

三、三种分片策略对比


┌──────────────────────────────────────────────────────────┐
│                 分片策略对比                               │
├──────────┬──────────────────┬───────────────────────────┤
│ 策略      │ 原理              │ 优缺点                    │
├──────────┼──────────────────┼───────────────────────────┤
│ Hash取模 │ shard = id % N   │ ✅ 数据均匀分布            │
│          │                  │ ❌ 扩容需迁移大量数据       │
│          │                  │ 适用: 用户/订单ID           │
├──────────┼──────────────────┼───────────────────────────┤
│ Range范围│ id 1-1000w→t0    │ ✅ 扩容只需加新表           │
│          │ 1000w-2000w→t1  │ ❌ 数据可能不均匀(热点)     │
│          │                  │ 适用: 时间维度(按月分表)     │
├──────────┼──────────────────┼───────────────────────────┤
│ 一致性   │ 虚拟环 + 顺时针   │ ✅ 扩容只影响相邻节点       │
│ Hash     │ 定位节点          │ ❌ 数据可能不均匀            │
│          │                  │ 适用: 动态扩容场景           │
└──────────┴──────────────────┴───────────────────────────┘

一致性Hash示意:


                ┌───────┐
         ┌─────│ Node-C │─────┐
         │     └───────┘     │
    ┌────▼───┐          ┌────▲───┐
    │Node-A  │          │Node-B  │
    └────────┘          └────────┘

key1 hash → 顺时针找到Node-A
key2 hash → 顺时针找到Node-B
扩容Node-D → 只影响Node-C和Node-D之间的key

四、ShardingSphere配置实战


# application-sharding.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db_0
        username: root
        password: root
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/order_db_1
        username: root
        password: root
    rules:
      sharding:
        tables:
          t_order:
            # 数据节点: ds0.t_order_0, ds0.t_order_1,
            #           ds1.t_order_0, ds1.t_order_1
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
            # 分库策略: 按user_id取模
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
            # 分表策略: 按order_id取模
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-table-mod
            # 主键生成: 雪花算法
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          order-db-mod:
            type: MOD
            props:
              sharding-count: 2
          order-table-mod:
            type: MOD
            props:
              sharding-count: 2
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

ShardingSphere架构:


┌───────────────────────────┐
│        应用层              │
└──────────┬────────────────┘
           │
┌──────────▼────────────────┐
│   ShardingSphere-JDBC     │  ← 嵌入应用内,无代理层
│  (SQL解析 → 路由 → 改写    │
│   → 执行 → 归并)           │
└──────────┬────────────────┘
           │
┌────┬─────┼─────┬────┐
▼    ▼     ▼     ▼    ▼
ds0  ds1   ds2   ds3  (多个数据源)

vs ShardingSphere-Proxy:
JDBC: 嵌入应用, 无网络开销, 但与应用耦合
Proxy: 独立部署, 对应用透明, 但多一层网络

五、分库分表四大问题

5.1 跨库JOIN


问题: 订单按user_id分库,商品在商品库 → JOIN跨库无法执行

解决方案:
1. 冗余字段: 订单表冗余商品名称(空间换时间)
2. 全局表: 每个库都存一份基础数据表(如字典表)
3. 应用层JOIN: 分别查两个库,代码中组装(推荐)
4. 宽表+ES: 将关联数据同步到ES,直接查ES

-- 推荐: 代码层JOIN
Order order = orderMapper.selectById(orderId);
Product product = productMapper.selectById(order.getProductId());
order.setProductName(product.getName());

5.2 跨库分页


问题: SELECT * FROM t_order ORDER BY create_time LIMIT 100000, 10
需要从所有分片各查100010行 → 归并排序 → 取10行 → 性能差

解决方案:
1. 游标分页(推荐):
   WHERE id > last_id ORDER BY id LIMIT 10
   只查各分片少量数据

2. 禁止深分页:
   产品规范限制最多翻到第100页

3. ES宽表:
   需要排序/分页的查询走ES,不在分片表上做

代码:
// 游标分页
public Page<Order> getUserOrders(Long userId, Long lastOrderId, int pageSize) {
    if (lastOrderId == null) {
        return orderMapper.selectPageByUserId(userId, pageSize);
    }
    // WHERE user_id=? AND id < lastOrderId ORDER BY id DESC LIMIT pageSize
    return orderMapper.selectPageByUserIdAfter(userId, lastOrderId, pageSize);
}

5.3 分布式事务


方案对比:
┌────────────────┬──────────┬──────┬─────────────────────────┐
│ 方案            │ 一致性    │ 性能 │ 适用场景                 │
├────────────────┼──────────┼──────┼─────────────────────────┤
│ 2PC/XA         │ 强一致    │ 低   │ 短事务, 数据一致性优先   │
│ TCC            │ 最终一致  │ 中   │ 高并发, 业务侵入大       │
│ Seata AT       │ 最终一致  │ 高   │ 推荐, 自动补偿           │
│ 消息最终一致性  │ 最终一致  │ 高   │ 异步场景, 跨系统         │
└────────────────┴──────────┴──────┴─────────────────────────┘

Seata AT模式原理:
1. 拦截SQL → 生成前镜像(beforeImage)
2. 执行业务SQL
3. 生成后镜像(afterImage)
4. 异常时自动根据镜像回滚

最佳实践: 尽量避免跨库事务
分片键选择原则: 高频查询条件必须是分片键
→ 保证同一用户的操作在同一个库 → 避免跨库

5.4 分布式ID


为什么不能自增?
分片后不同分片可能生成相同ID → 主键冲突

雪花算法(Snowflake):
┌───────────────────────────────────────────────────────┐
│ 0 | 00000000 00000000 00000000 00000000 00000000 0    │
│   | ←              41位时间戳(69年)              → |   │
│                                                     │   │
│                          | 00000 | 00000 | 000000000000│
│                          |5位DC  |5位机器| 12位序列号   │
│                          |ID     |ID    | (4096/ms)   │
└───────────────────────────────────────────────────────┘

总计64位(8字节) = 1个Long
- 1位符号位(0)
- 41位时间戳(可使用69年)
- 5位数据中心ID(0~31)
- 5位机器ID(0~31)
- 12位序列号(每毫秒4096个ID)

优点: 趋势递增 / 不依赖DB / 高性能
缺点: 时钟回拨问题

时钟回拨解决方案:
┌─────────────────────────────────────────┐
│ 1. 记录上次生成ID的时间戳                │
│ 2. 发现当前时间 < 上次时间 → 时钟回拨    │
│ 3. 回拨<5ms → 等待追上后继续             │
│ 4. 回拨>5ms → 报错 / 用备用workerId     │
│ 5. 生产环境: NTP校准 + 禁止大步长回拨    │
└─────────────────────────────────────────┘

六、数据迁移四步法


┌──────────────────────────────────────────────────────────┐
│              数据迁移四步法(停机窗口最小化)                │
├──────────────────────────────────────────────────────────┤
│                                                          │
│ 第1步: 评估与准备                                         │
│   · 确定分片键、分片数、分片策略                           │
│   · 部署新分片集群                                        │
│   · 编写数据迁移脚本                                      │
│                                                          │
│ 第2步: 双写(新老库同时写)                                  │
│   · 代码改造: 写操作同时写新老库                           │
│   · 读操作仍然读老库                                      │
│   · 运行一段时间确认双写稳定                               │
│                                                          │
│ 第3步: 历史数据迁移                                       │
│   · 用脚本将老库历史数据迁移到新分片库                     │
│   · 迁移期间新数据通过双写已在新库                         │
│   · 校验新老库数据一致性                                   │
│                                                          │
│ 第4步: 切换读+停双写                                      │
│   · 读操作切到新分片库                                    │
│   · 确认稳定后停止双写(停止写老库)                         │
│   · 下线老库                                              │
└──────────────────────────────────────────────────────────┘

七、分库分表方案对比表

维度垂直分库水平分库垂直分表水平分表
拆分依据业务模块数据量/ID字段冷热数据量/ID
解决问题耦合/连接数单库QPS单表字段多单表数据多
复杂度低高低高
JOIN影响跨库JOIN跨库JOIN需要关联查询跨表查询
典型场景用户库/订单库/商品库用户库按ID分3个用户表+用户扩展表订单表按月分

面试话术: "我们的订单系统用ShardingSphere-JDBC做分库分表,2库4表。分库键是user_id保证同一用户的订单在同一个库,分表键是order_id均匀分布数据。主键用雪花算法因为自增ID分片后会冲突。分页查询用游标分页避免深分页问题。跨库JOIN通过冗余字段和代码层组装解决。分布式事务用Seata AT模式自动补偿。数据迁移采用双写方案,先双写再迁历史数据,最后切读停写。"

面试速记卡(考前30秒速看)

练习题(自测)

1. 为什么说分库分表是"最后手段"?在分库分表之前应该优先尝试哪些方案?

2. 用户表按user_id取模分4个表,现在要查"手机号=138xxxx"的用户,怎么路由到正确的表?

3. 雪花算法生成的ID为什么不建议作为MySQL主键?如果要用,主键应该选什么类型?

4. 分库分表后如何保证跨库事务的一致性?Seata AT模式的自动补偿原理是什么?

5. 描述双写方案进行数据迁移的完整流程,每一步的关键点是什么?

学习资料