MySQL分区表的使用场景

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、什么是分区表

#

1.1 分区表概念

将单张表的数据在物理上分散存储到多个分区，但逻辑上仍是一张表。

逻辑视角：
  SELECT * FROM logs;

物理存储：
  logs#P#p202401.ibd
  logs#P#p202402.ibd
  logs#P#p202403.ibd
  logs#P#p202404.ibd

#

1.2 分区 vs 分表

特性	分区表	手动分表
应用改造	无	需要
跨分区查询	自动支持	应用层实现
扩展性	受单实例限制	可跨实例
维护复杂度	低	高
性能上限	单实例上限	可线性扩展

二、分区类型

#

2.1 RANGE分区

按连续范围分区，最常用：

CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    message VARCHAR(500),
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

适用场景：

• 时间序列数据（日志、订单、监控数据）
• 按日期归档和清理

#

2.2 LIST分区

按离散值分区：

CREATE TABLE user (
    id INT,
    region VARCHAR(20),
    name VARCHAR(50)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_south VALUES IN ('广东', '福建', '海南'),
    PARTITION p_other VALUES IN (DEFAULT)
);

适用场景：

• 按地区、状态等离散值分组
• 数据有明显分类特征

#

2.3 HASH分区

按哈希值均匀分布：

CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(10,2)
) PARTITION BY HASH(user_id) PARTITIONS 8;

适用场景：

• 数据分布要求均匀
• 没有明显的时间或分类特征

#

2.4 KEY分区

类似HASH，但使用MySQL内置哈希函数：

CREATE TABLE user (
    id INT,
    name VARCHAR(50)
) PARTITION BY KEY() PARTITIONS 4;

#

2.5 子分区（复合分区）

先按RANGE分区，再按HASH子分区：

CREATE TABLE logs (
    id BIGINT,
    log_time DATETIME,
    message TEXT
) PARTITION BY RANGE (YEAR(log_time))
SUBPARTITION BY HASH(MONTH(log_time))
SUBPARTITIONS 12 (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

三、分区表的优势

#

3.1 查询优化

分区裁剪（Partition Pruning）：

SELECT * FROM logs WHERE log_time >= '2024-01-01' AND log_time < '2024-02-01';

MySQL自动只扫描p202401分区，其他分区不读。

验证裁剪：

EXPLAIN PARTITIONS SELECT * FROM logs 
WHERE log_time >= '2024-01-01' AND log_time < '2024-02-01';
-- partitions列显示: p202401

#

3.2 快速删除

-- 传统方式：DELETE需要逐行删除，写大量undo log
DELETE FROM logs WHERE log_time < '2023-01-01';

-- 分区方式：直接删除分区文件，瞬间完成
ALTER TABLE logs DROP PARTITION p2022;

#

3.3 高效归档

-- 导出分区数据
ALTER TABLE logs EXCHANGE PARTITION p2023 WITH TABLE logs_archive_2023;

-- 归档表可以单独存储到慢速磁盘
CREATE TABLE logs_archive_2023 (...) TABLESPACE = archive_ts;

#

3.4 独立维护

-- 单独优化某个分区
ALTER TABLE logs OPTIMIZE PARTITION p2024;

-- 单独重建某个分区
ALTER TABLE logs REBUILD PARTITION p2024;

-- 单独分析某个分区
ALTER TABLE logs ANALYZE PARTITION p2024;

四、分区表的使用限制

#

4.1 主键和唯一索引限制

分区键必须是主键/唯一索引的一部分：

-- 正确：分区键log_time是主键的一部分
CREATE TABLE logs (
    id BIGINT,
    log_time DATETIME,
    PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) ...

-- 错误：分区键不是主键的一部分
CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) ...
-- ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function

#

4.2 外键限制

分区表不支持外键：

-- 错误
CREATE TABLE orders (...) PARTITION BY RANGE ...
FOREIGN KEY (user_id) REFERENCES user(id);
-- ERROR 1217: Cannot add foreign key constraint

#

4.3 函数限制

分区表达式中可用的函数有限：

-- 允许：YEAR(), TO_DAYS(), TO_SECONDS(), MONTH(), ...
PARTITION BY RANGE (YEAR(log_time))

-- 不允许：自定义函数
PARTITION BY RANGE (my_func(log_time))  -- 错误

#

4.4 性能限制

• 分区过多（>100）影响性能
• 非分区键查询需要扫描所有分区
• 分区表打开文件数增加

五、分区表实践案例

#

5.1 日志表按月分区

CREATE TABLE system_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    create_time DATETIME NOT NULL,
    level VARCHAR(10) NOT NULL,
    module VARCHAR(50),
    message TEXT,
    PRIMARY KEY (id, create_time),
    KEY idx_level_time (level, create_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

#

5.2 自动维护脚本

-- 每月初添加新分区
DELIMITER //
CREATE PROCEDURE add_month_partition()
BEGIN
    DECLARE next_month DATE;
    DECLARE partition_name VARCHAR(20);
    DECLARE less_than_value INT;
    
    SET next_month = DATE_ADD(DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01'), INTERVAL 1 MONTH);
    SET partition_name = CONCAT('p', DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y%m'));
    SET less_than_value = TO_DAYS(next_month);
    
    SET @sql = CONCAT(
        'ALTER TABLE system_logs REORGANIZE PARTITION p_future INTO (
            PARTITION ', partition_name, ' VALUES LESS THAN (', less_than_value, '),
            PARTITION p_future VALUES LESS THAN MAXVALUE
        )'
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 创建定时事件
CREATE EVENT add_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-02-01 00:00:00'
DO CALL add_month_partition();

#

5.3 定期清理旧分区

-- 删除3个月前的分区
DELIMITER //
CREATE PROCEDURE drop_old_partitions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE partition_name VARCHAR(20);
    DECLARE cur CURSOR FOR 
        SELECT partition_name 
        FROM information_schema.partitions 
        WHERE table_name = 'system_logs' 
        AND partition_name != 'p_future'
        AND partition_name < CONCAT('p', DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, '%Y%m'));
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO partition_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @sql = CONCAT('ALTER TABLE system_logs DROP PARTITION ', partition_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

六、分区表性能优化

#

6.1 选择合适的分区数

• 建议分区数：10-100
• 过多分区增加管理开销
• 过少分区失去分区意义

#

6.2 确保查询能裁剪

-- 能裁剪（直接比较分区键）
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'

-- 不能裁剪（函数操作）
WHERE DATE(create_time) = '2024-01-01'

-- 能裁剪（等效写法）
WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time < '2024-01-02 00:00:00'

#

6.3 分区键选择

-- 好的选择：查询条件中最常用的列
-- 日志表：按时间分区
-- 订单表：按用户ID哈希分区或按时间RANGE分区

-- 不好的选择：经常更新的列
-- 避免选择会被UPDATE的列作为分区键

七、分区表监控

#

7.1 查看分区信息

-- 查看表的分区情况
SELECT 
    partition_name,
    partition_method,
    partition_expression,
    table_rows,
    data_length,
    index_length
FROM information_schema.partitions
WHERE table_name = 'system_logs'
ORDER BY partition_name;

-- 查看分区裁剪情况
EXPLAIN PARTITIONS SELECT * FROM system_logs WHERE create_time >= '2024-01-01';

#

7.2 分区大小监控

SELECT 
    partition_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.partitions
WHERE table_name = 'system_logs'
ORDER BY data_length DESC;

八、分区表 vs 分库分表选择

场景	推荐方案
单表5000万-5亿数据	分区表
单表超过5亿数据	分库分表
主要按时间查询	分区表（RANGE）
需要跨实例扩展	分库分表
简单归档需求	分区表
超高并发写入	分库分表

九、总结

分区类型	适用场景	特点
RANGE	时间序列数据	方便按时间归档
LIST	离散分类数据	按地区、状态等分组
HASH	均匀分布	避免热点
KEY	简单均匀分布	MySQL自动哈希

分区表的核心价值：

1. 透明性：应用无需改造
2. 查询优化：分区裁剪减少扫描
3. 维护便利：快速删除和归档
4. 适度扩展：单实例内的水平扩展

使用分区表前，务必确认：

• 分区键选择合理
• 查询能利用分区裁剪
• 了解并接受分区限制
• 建立分区维护机制

核心要点

1. 索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型

2. SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN

3. 事务隔离级别的选择：根据业务需求选择合适的级别

4. 常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。