MySQL教程 / 第 250 节

第25章：分库分表

分库分表是解决海量数据存储和高并发访问的核心方案

25.1 分库分表概述

25.1.1 为什么需要分库分表？

问题场景：

❌ 单表数据量过大（>1000万）
❌ 查询慢，索引失效
❌ 单库连接数不够
❌ 单库I/O瓶颈

示例：

-- 订单表：1亿条数据
SELECT * FROM orders WHERE user_id = 12345;
-- 即使有索引，查询也很慢

-- 问题：
-- 1. 表太大，索引树深度增加
-- 2. Buffer Pool无法缓存所有数据
-- 3. 单表锁竞争激烈

25.1.2 分库分表的优势

分表优势：

✅ 减小单表数据量
✅ 提高查询性能
✅ 减少锁竞争
✅ 提高并发能力

分库优势：

✅ 分散连接数
✅ 分散I/O压力
✅ 提高并发能力
✅ 突破单机性能瓶颈

25.1.3 分库分表类型

垂直分库：

原来：
单库：用户表、订单表、商品表

分库后：
用户库：用户表
订单库：订单表
商品库：商品表

垂直分表：

原来：
用户表：id, name, age, address, avatar, description

分表后：
用户基本表：id, name, age
用户详情表：id, address, avatar, description

水平分库：

原来：
单库：订单表（1亿条）

分库后：
订单库1：订单表（2500万条）
订单库2：订单表（2500万条）
订单库3：订单表（2500万条）
订单库4：订单表（2500万条）

水平分表：

原来：
订单表：1亿条

分表后：
订单表_0：2500万条
订单表_1：2500万条
订单表_2：2500万条
订单表_3：2500万条

25.2 分片策略 ⭐⭐⭐⭐⭐

25.2.1 范围分片（Range）

原理：

按照某个字段的范围分片
例如：按ID、时间范围

示例：

-- 按ID范围分片
订单表_0：id < 2500万
订单表_1：2500万 <= id < 5000万
订单表_2：5000万 <= id < 7500万
订单表_3：id >= 7500万

-- 按时间范围分片
订单表_2023：2023年的订单
订单表_2024：2024年的订单
订单表_2025：2025年的订单

优点：

✅ 简单易理解
✅ 范围查询性能好
✅ 扩容方便

缺点：

❌ 数据分布可能不均匀
❌ 热点问题（最新数据访问频繁）

适用场景：

按时间查询的业务
有明显范围的数据

25.2.2 哈希分片（Hash）

原理：

对分片键进行哈希运算
根据哈希值确定分片

示例：

// 按用户ID哈希分片
int shardIndex = userId % 4;  // 4个分片

// 用户1001 → 1001 % 4 = 1 → 订单表_1
// 用户1002 → 1002 % 4 = 2 → 订单表_2
// 用户1003 → 1003 % 4 = 3 → 订单表_3
// 用户1004 → 1004 % 4 = 0 → 订单表_0

优点：

✅ 数据分布均匀
✅ 避免热点问题

缺点：

❌ 范围查询性能差
❌ 扩容困难（需要重新哈希）

适用场景：

按ID查询的业务
数据分布要求均匀

25.2.3 一致性哈希（Consistent Hash）

原理：

解决哈希分片扩容问题
使用哈希环

示例：

哈希环：0 ~ 2^32-1

节点分布：
节点1：hash(node1) = 100
节点2：hash(node2) = 200
节点3：hash(node3) = 300

数据分布：
数据A：hash(A) = 50  → 节点1（顺时针最近）
数据B：hash(B) = 150 → 节点2
数据C：hash(C) = 250 → 节点3

扩容（增加节点4）：
节点4：hash(node4) = 150
只需迁移部分数据（150-200之间的数据）

优点：

✅ 扩容时只需迁移部分数据
✅ 数据分布相对均匀

缺点：

❌ 实现复杂
❌ 可能出现数据倾斜

25.2.4 地理位置分片（Geo）

原理：

按照地理位置分片
例如：按省份、城市

示例：

-- 按地区分片
订单库_华北：北京、天津、河北的订单
订单库_华东：上海、江苏、浙江的订单
订单库_华南：广东、福建、海南的订单
订单库_华中：湖北、湖南、河南的订单

优点：

✅ 就近访问，延迟低
✅ 符合业务逻辑

缺点：

❌ 数据分布可能不均匀
❌ 跨地区查询复杂

25.3 分片键选择 ⭐⭐⭐⭐⭐

25.3.1 分片键选择原则

原则1：高频查询字段

-- ✅ 好的分片键
SELECT * FROM orders WHERE user_id = 12345;
-- user_id是高频查询字段，适合作为分片键

-- ❌ 不好的分片键
SELECT * FROM orders WHERE order_status = 1;
-- order_status不是唯一标识，不适合作为分片键

原则2：数据分布均匀

-- ✅ 好的分片键：user_id
-- 每个用户的订单数量相对均匀

-- ❌ 不好的分片键：order_date
-- 某些日期（如双11）订单量特别大

原则3：避免跨分片查询

-- ✅ 好的分片键：user_id
SELECT * FROM orders WHERE user_id = 12345;
-- 只需查询一个分片

-- ❌ 不好的分片键：order_id
SELECT * FROM orders WHERE user_id = 12345;
-- 需要查询所有分片（user_id不是分片键）

原则4：业务相关性

-- ✅ 好的分片键：user_id
-- 订单表按user_id分片
-- 用户表也按user_id分片
-- 可以进行分片内JOIN

-- ❌ 不好的分片键：order_id
-- 订单表按order_id分片
-- 用户表按user_id分片
-- 无法进行分片内JOIN

25.3.2 常见分片键

订单表：

推荐：user_id（用户维度查询多）
备选：order_id（订单维度查询）

用户表：

推荐：user_id

商品表：

推荐：product_id

日志表：

推荐：时间（按天或月分片）

25.4 分库分表中间件

25.4.1 中间件对比

中间件	类型	语言	性能	推荐度
ShardingSphere	客户端	Java	高	⭐⭐⭐⭐⭐
MyCat	代理	Java	中	⭐⭐⭐⭐
Vitess	代理	Go	高	⭐⭐⭐⭐
TDDL	客户端	Java	高	⭐⭐⭐

25.4.2 ShardingSphere实战 ⭐⭐⭐⭐⭐

什么是ShardingSphere？

Apache顶级项目
分库分表、读写分离、分布式事务
支持JDBC、Proxy、Sidecar三种模式

ShardingSphere-JDBC配置：

# application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      
      # 数据源0
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.10:3306/order_db_0
        username: root
        password: password
      
      # 数据源1
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.11:3306/order_db_1
        username: root
        password: password
      
      # 数据源2
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.12:3306/order_db_2
        username: root
        password: password
      
      # 数据源3
      ds3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.13:3306/order_db_3
        username: root
        password: password
    
    rules:
      sharding:
        tables:
          # 订单表分片规则
          orders:
            # 实际节点
            actual-data-nodes: ds$->{0..3}.orders_$->{0..3}
            
            # 分库策略
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            
            # 分表策略
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-inline
            
            # 主键生成策略
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake
        
        # 分片算法
        sharding-algorithms:
          # 分库算法
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 4}
          
          # 分表算法
          table-inline:
            type: INLINE
            props:
              algorithm-expression: orders_$->{user_id % 4}
        
        # 主键生成器
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
    
    props:
      sql-show: true  # 打印SQL

使用示例：

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;

    // 插入订单（自动路由到正确的分片）
    public void createOrder(Order order) {
        orderMapper.insert(order);
        // ShardingSphere自动计算：
        // user_id = 1001
        // 分库：ds$->{1001 % 4} = ds1
        // 分表：orders_$->{1001 % 4} = orders_1
        // 实际执行：INSERT INTO order_db_1.orders_1 ...
    }

    // 查询订单（自动路由）
    public Order getOrder(Long userId, Long orderId) {
        return orderMapper.selectByUserIdAndOrderId(userId, orderId);
        // 自动路由到正确的分片
    }

    // 查询用户所有订单（单分片查询）
    public List<Order> getUserOrders(Long userId) {
        return orderMapper.selectByUserId(userId);
        // 只查询一个分片
    }

    // 查询所有订单（跨分片查询，性能差）
    public List<Order> getAllOrders() {
        return orderMapper.selectAll();
        // 需要查询所有分片，然后合并结果
    }
}

25.5 分库分表问题与解决方案

25.5.1 跨分片查询 ⭐⭐⭐⭐⭐

问题：

-- 按user_id分片
-- 查询某个用户的订单（单分片查询）
SELECT * FROM orders WHERE user_id = 12345;  -- ✅ 性能好

-- 查询所有待支付订单（跨分片查询）
SELECT * FROM orders WHERE status = 'PENDING';  -- ❌ 性能差
-- 需要查询所有分片，然后合并结果

解决方案1：冗余数据

-- 订单表按user_id分片
-- 订单状态表按status分片
-- 两个表冗余存储

-- 查询用户订单
SELECT * FROM orders WHERE user_id = 12345;

-- 查询待支付订单
SELECT * FROM order_status WHERE status = 'PENDING';

解决方案2：使用ES等搜索引擎

MySQL：存储数据
Elasticsearch：搜索数据

-- 复杂查询走ES
SELECT * FROM orders WHERE status = 'PENDING' AND amount > 100;

解决方案3：汇总表

-- 定期将分片数据汇总到一个表
-- 用于统计和报表查询

-- 汇总表
CREATE TABLE orders_summary (
    date DATE,
    status VARCHAR(20),
    count INT,
    total_amount DECIMAL(10,2)
);

25.5.2 分布式事务 ⭐⭐⭐⭐⭐

问题：

// 跨分片事务
@Transactional
public void transfer(Long fromUserId, Long toUserId, BigDecimal amount) {
    // fromUserId和toUserId可能在不同分片
    accountMapper.deduct(fromUserId, amount);  // 分片1
    accountMapper.add(toUserId, amount);       // 分片2
    // 如何保证事务一致性？
}

解决方案1：避免跨分片事务

// 设计时避免跨分片事务
// 例如：账户表和订单表都按user_id分片
// 同一个用户的数据在同一个分片

解决方案2：两阶段提交（2PC）

// ShardingSphere支持XA事务
@ShardingSphereTransactionType(TransactionType.XA)
@Transactional
public void transfer(Long fromUserId, Long toUserId, BigDecimal amount) {
    accountMapper.deduct(fromUserId, amount);
    accountMapper.add(toUserId, amount);
}

// 缺点：性能差，不推荐

解决方案3：柔性事务（Saga/TCC）

// 使用Seata等分布式事务框架
@GlobalTransactional
public void transfer(Long fromUserId, Long toUserId, BigDecimal amount) {
    accountService.deduct(fromUserId, amount);  // 本地事务
    accountService.add(toUserId, amount);       // 本地事务
}

// 优点：性能好
// 缺点：实现复杂

解决方案4：最终一致性

// 使用消息队列保证最终一致性
public void transfer(Long fromUserId, Long toUserId, BigDecimal amount) {
    // 1. 扣款
    accountMapper.deduct(fromUserId, amount);

    // 2. 发送消息
    mqProducer.send(new TransferMessage(toUserId, amount));
}

// 消费者
@RabbitListener(queues = "transfer")
public void handleTransfer(TransferMessage msg) {
    // 3. 加款
    accountMapper.add(msg.getUserId(), msg.getAmount());
}

25.5.3 全局唯一ID ⭐⭐⭐⭐⭐

问题：

-- 分库分表后，自增ID会重复
-- 分片1：id = 1, 2, 3, ...
-- 分片2：id = 1, 2, 3, ...  -- 重复！

解决方案1：UUID

String id = UUID.randomUUID().toString();
// 优点：简单
// 缺点：无序、占用空间大、索引性能差

解决方案2：雪花算法（Snowflake）

// 64位ID
// 1位符号位 + 41位时间戳 + 10位机器ID + 12位序列号

SnowflakeIdWorker idWorker = new SnowflakeIdWorker(1, 1);
long id = idWorker.nextId();

// 优点：有序、性能好
// 缺点：依赖时钟

解决方案3：数据库号段模式

-- 创建ID生成表
CREATE TABLE id_generator (
    biz_type VARCHAR(50) PRIMARY KEY,
    max_id BIGINT NOT NULL,
    step INT NOT NULL
);

-- 获取ID
UPDATE id_generator SET max_id = max_id + step WHERE biz_type = 'order';
SELECT max_id FROM id_generator WHERE biz_type = 'order';

-- 应用缓存：max_id ~ max_id+step
-- 用完后再获取下一批

解决方案4：Redis生成ID

// 使用Redis的INCR命令
long id = redisTemplate.opsForValue().increment("order_id");

// 优点：简单、性能好
// 缺点：依赖Redis

ShardingSphere内置ID生成器：

# 雪花算法
key-generators:
  snowflake:
    type: SNOWFLAKE
    props:
      worker-id: 1

# UUID
key-generators:
  uuid:
    type: UUID

25.5.4 数据迁移

问题：

原来：单库单表
现在：分库分表
如何迁移数据？

方案1：停机迁移

# 1. 停止应用
# 2. 导出数据
mysqldump -u root -p mydb orders > orders.sql

# 3. 导入到分片
# 根据分片规则，将数据导入到不同分片

# 4. 验证数据
# 5. 启动应用

方案2：双写迁移（推荐）

// 阶段1：双写
public void createOrder(Order order) {
    // 写旧库
    oldOrderMapper.insert(order);
    // 写新库（分片）
    newOrderMapper.insert(order);
}

// 阶段2：迁移历史数据
// 使用脚本将旧库数据迁移到新库

// 阶段3：双读验证
public Order getOrder(Long orderId) {
    Order oldOrder = oldOrderMapper.selectById(orderId);
    Order newOrder = newOrderMapper.selectById(orderId);
    // 对比数据，验证一致性
    return newOrder;
}

// 阶段4：切换到新库
public Order getOrder(Long orderId) {
    return newOrderMapper.selectById(orderId);
}

// 阶段5：下线旧库

25.6 分库分表最佳实践

25.6.1 设计原则

1. 能不分就不分
   - 单表<1000万，不需要分表
   - 优化SQL、添加索引、升级硬件

2. 优先垂直拆分
   - 按业务拆分（用户库、订单库、商品库）
   - 简单、易维护

3. 再考虑水平拆分
   - 单表>1000万时考虑
   - 选择合适的分片键

4. 避免跨分片查询
   - 设计时考虑查询场景
   - 冗余数据或使用ES

5. 避免跨分片事务
   - 设计时避免
   - 或使用分布式事务

6. 预留扩容空间
   - 分片数量设置为2的幂次（2、4、8、16）
   - 方便扩容

25.6.2 常见错误

-- ❌ 错误1：分片键选择不当
-- 问题：导致大量跨分片查询
-- 解决：选择高频查询字段作为分片键

-- ❌ 错误2：分片数量过多
-- 问题：管理复杂、连接数过多
-- 解决：根据数据量合理规划

-- ❌ 错误3：不考虑扩容
-- 问题：扩容时需要重新分片
-- 解决：使用一致性哈希或预留分片

-- ❌ 错误4：忽略分布式事务
-- 问题：数据不一致
-- 解决：避免跨分片事务或使用分布式事务

-- ❌ 错误5：不监控数据倾斜
-- 问题：某些分片数据过多
-- 解决：监控各分片数据量，及时调整

25.7 本章总结

本章学习内容：

✅ 分库分表概述
✅ 分片策略（范围、哈希、一致性哈希、地理位置）⭐⭐⭐⭐⭐
✅ 分片键选择（原则、常见分片键）⭐⭐⭐⭐⭐
✅ 分库分表中间件（ShardingSphere）
✅ 分库分表问题（跨分片查询、分布式事务、全局ID）⭐⭐⭐⭐⭐
✅ 分库分表最佳实践

重点掌握：

单表>1000万考虑分表
分片键选择：高频查询字段、数据均匀
避免跨分片查询和事务
全局ID：雪花算法
ShardingSphere配置
能不分就不分

分片策略：

范围分片：按时间、ID范围
哈希分片：数据均匀
一致性哈希：方便扩容
地理位置分片：就近访问

分片键选择原则：

高频查询字段
数据分布均匀
避免跨分片查询
业务相关性

核心问题：

跨分片查询：冗余数据、ES
分布式事务：避免、2PC、柔性事务
全局ID：雪花算法、号段模式

面试重点：

为什么需要分库分表
分片策略有哪些
如何选择分片键
如何解决跨分片查询
如何生成全局唯一ID
分布式事务如何处理

下一章预告： MySQL监控体系

练习题

什么时候需要分库分表？
垂直分库和水平分库有什么区别？
分片策略有哪些？各有什么优缺点？
如何选择分片键？
跨分片查询如何优化？
如何生成全局唯一ID？
分布式事务如何处理？
ShardingSphere如何配置？
如何进行数据迁移？
设计一个分库分表方案

继续学习： 第26章：MySQL监控体系