MySQL 表分区与分表实战

核心概念

当单表数据量过大时，需要通过分区或分表来提升性能。

分表时机

分区 vs 分表

表分区

分区类型

MySQL 支持 4 种分区类型：

1. RANGE 分区：按范围分区
2. LIST 分区：按枚举值分区
3. HASH 分区：按哈希值分区
4. KEY 分区：按 MySQL 内部哈希分区

RANGE 分区

-- 按时间范围分区
CREATE TABLE orders (
    id BIGINT NOT NULL,
    create_time DATETIME NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, create_time)
)
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询优化（分区裁剪）
SELECT * FROM orders WHERE create_time >= '2023-01-01';
-- 只扫描 p2023 分区

LIST 分区

-- 按地区分区
CREATE TABLE users (
    id BIGINT NOT NULL,
    city_id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id, city_id)
)
PARTITION BY LIST (city_id) (
    PARTITION p_beijing VALUES IN (1, 2, 3),
    PARTITION p_shanghai VALUES IN (4, 5, 6),
    PARTITION p_guangzhou VALUES IN (7, 8, 9),
    PARTITION p_other VALUES IN (0)
);

-- 查询优化
SELECT * FROM users WHERE city_id = 1;
-- 只扫描 p_beijing 分区

HASH 分区

-- 按用户 ID 哈希分区
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 16;

-- 数据均匀分布到 16 个分区

KEY 分区

-- 类似 HASH，使用 MySQL 内部哈希
CREATE TABLE users (
    id BIGINT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)
)
PARTITION BY LINEAR KEY (id)
PARTITIONS 8;

分区管理

添加分区

-- 添加新分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 添加多个分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

删除分区

-- 删除分区（数据会丢失）
ALTER TABLE orders DROP PARTITION p2020;

-- 删除分区并保留数据（合并到下一个分区）
ALTER TABLE orders REORGANIZE PARTITION p2020 INTO (
    PARTITION p2020 VALUES LESS THAN (2021)
);

分区维护

-- 查看分区信息
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_EXPRESSION,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';

-- 重建分区（整理碎片）
ALTER TABLE orders REBUILD PARTITION (p2023);

-- 分析分区
ALTER TABLE orders ANALYZE PARTITION (p2023);

-- 优化分区
ALTER TABLE orders OPTIMIZE PARTITION (p2023);

水平分表

分表策略

1. 按 ID 范围分表
   orders_0, orders_1, ..., orders_9

2. 按 ID 哈希分表
   hash(id) % 10

3. 按时间分表
   orders_202401, orders_202402, ...

4. 按地域分表
   orders_beijing, orders_shanghai, ...

分表实现

应用层分表：

// 分表路由
public class TableRouter {
    
    // 按 ID 哈希分表
    public String getTableName(Long orderId) {
        int index = Math.abs(orderId.hashCode() % 10);
        return "orders_" + index;
    }
    
    // 按时间分表
    public String getTableNameByTime(Date createTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return "orders_" + sdf.format(createTime);
    }
}

// 使用
public void insertOrder(Order order) {
    String tableName = tableRouter.getTableName(order.getId());
    String sql = "INSERT INTO " + tableName + " (...) VALUES (...)";
    jdbcTemplate.update(sql, params);
}

中间件分表（ShardingSphere）：

# application.yml
spring:
  shardingsphere:
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds.orders_$->{0..9}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: orders-hash
    
        sharding-algorithms:
          orders-hash:
            type: HASH_MOD
            props:
              sharding-count: 10

垂直分表

垂直分表场景

场景：大字段、不常用字段影响性能

原始表：

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    description TEXT,      -- 大字段
    detail JSON,           -- 大字段
    create_time DATETIME,
    update_time DATETIME
);

垂直拆分后：

-- 主表（常用字段）
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    create_time DATETIME,
    update_time DATETIME
);

-- 扩展表（大字段）
CREATE TABLE products_detail (
    product_id BIGINT PRIMARY KEY,
    description TEXT,
    detail JSON,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

垂直分表优势

1. 主表更小，更多数据可缓存
2. 减少 IO，提升查询性能
3. 大字段单独存储，便于管理

分库分表

分库策略

1. 垂直分库
   - 按业务模块拆分
   - 订单库、用户库、商品库

2. 水平分库
   - 按数据分布拆分
   - 用户 ID 哈希、地域分布

分库分表架构

架构 1：客户端分片
应用 → ShardingSphere-JDBC → 多个数据库

架构 2：代理分片
应用 → ShardingSphere-Proxy → 多个数据库

架构 3：自定义路由
应用 → 路由层 → 多个数据库

分库分表问题

1. 跨库 JOIN

-- 问题：无法跨库 JOIN
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id;

-- 解决：
-- 方案 1：应用层组装
-- 方案 2：字段冗余
-- 方案 3：全局表

2. 分布式 ID

// 雪花算法生成唯一 ID
public class SnowflakeIdGenerator {
    private long workerId;
    private long sequence = 0L;
    
    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();
        sequence = (sequence + 1) & 4095;
        return (timestamp << 12) | sequence;
    }
}

3. 分布式事务

使用方案：
- TCC
- 本地消息表
- 事务消息
- Saga

4. 数据迁移

迁移步骤：
1. 双写新旧库
2. 历史数据迁移
3. 校验数据一致性
4. 切换读流量
5. 切换写流量
6. 停止旧库

最佳实践

分区表适用场景

✅ 适合：
- 历史数据归档
- 按时间范围查询
- 数据量 1000 万 -1 亿

❌ 不适合：
- 超大数据量（> 1 亿）
- 需要跨分区查询
- 频繁变更分区键

分表适用场景

✅ 适合：
- 数据量 > 1 亿
- 高并发写入
- 需要水平扩展

❌ 不适合：
- 数据量小
- 复杂查询多
- 跨表 JOIN 多

参考资料

• MySQL 官方文档 - 分区
• ShardingSphere 官方文档
• 《分布式数据库中间件实践》