MySQL 分区表实战：大表治理的利器与陷阱

开场白分区表这个东西我之前一直觉得就是个语法糖直到有一次运维一张 2 亿行的日志表查询慢到飞起索引也建不动了才认真研究分区表。结果发现分区表确实好用但坑也不少——分区键选错了、分区裁剪没生效、跨分区查询性能反而更差……这些问题我一个个都踩过。今天把实战经验分享出来帮你少走弯路。分区表是什么分区表就是把一张大表在物理上拆分成多个小表分区逻辑上还是一张表。对应用层完全透明SQL 不用改但底层存储和查询可以只针对特定分区操作减少扫描量。MySQL 支持的分区类型RANGE按范围分区最常用LIST按枚举值分区HASH按哈希值分区KEY类似 HASH但由 MySQL 内部计算COLUMNSRANGE/LIST 的增强版支持多列和非整数类型RANGE 分区实战最常见的场景按时间分区。CREATETABLEorder_log(idBIGINTAUTO_INCREMENT,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,amountDECIMAL(10,2),statusTINYINT,create_timeDATETIMENOTNULL,PRIMARYKEY(id,create_time))PARTITIONBYRANGE(TO_DAYS(create_time))(PARTITIONp202401VALUESLESS THAN(TO_DAYS(2024-02-01)),PARTITIONp202402VALUESLESS THAN(TO_DAYS(2024-03-01)),PARTITIONp202403VALUESLESS THAN(TO_DAYS(2024-04-01)),PARTITIONp202404VALUESLESS THAN(TO_DAYS(2024-05-01)),PARTITIONpfutureVALUESLESS THAN MAXVALUE);几个要点 **1. 分区键必须包含在主键里** 这条坑了无数人。MySQL 要求分区键必须是主键或唯一键的一部分。所以上面主键是(id,create_time)而不是单纯的id。 如果写成PRIMARYKEY(id)会直接报错ERROR 1503: A PRIMARY KEY must include all columns in the table’s partitioning function**2. MAXVALUE 分区兜底** pfuture VALUES LESS THAN MAXVALUE 是个兜底分区所有超出范围的数据都落到这里。不加的话插入超范围的数据会报错。 **3. 用 TO_DAYS 还是 YEAR** TO_DAYS 粒度更细支持按月甚至按天分区。YEAR() 只能按年分区。按月分区是大表最常见的选择数据量和查询性能比较均衡。 ## RANGE COLUMNS 分区 RANGE COLUMNS 是 RANGE 的增强版直接用日期类型不需要 TO_DAYS 转换 sql CREATE TABLE order_log ( id BIGINT AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2), status TINYINT, create_time DATE NOT NULL, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE COLUMNS (create_time) ( PARTITION p202401 VALUES LESS THAN (2024-02-01), PARTITION p202402 VALUES LESS THAN (2024-03-01), PARTITION p202403 VALUES LESS THAN (2024-04-01), PARTITION pfuture VALUES LESS THAN MAXVALUE ); RANGE COLUMNS 还支持多列分区 sql PARTITION BY RANGE COLUMNS (create_time, region_id) ( PARTITION p202401_east VALUES LESS THAN (2024-02-01, 2), PARTITION p202401_west VALUES LESS THAN (2024-02-01, MAXVALUE), ... ); 不过多列分区用得不多管理复杂度太高。 ## LIST 分区 按枚举值分区适合地区、类型这种离散值 sql CREATE TABLE user_data ( id BIGINT AUTO_INCREMENT, name VARCHAR(50), region_id INT NOT NULL, PRIMARY KEY (id, region_id) ) PARTITION BY LIST (region_id) ( PARTITION p_north VALUES IN (1, 2, 3), PARTITION p_south VALUES IN (4, 5, 6), PARTITION p_east VALUES IN (7, 8, 9), PARTITION p_west VALUES IN (10, 11, 12) ); 注意 LIST 分区没有 MAXVALUE 兜底插入未定义的 region_id 会直接报错。 ## HASH 分区 按哈希均匀分布数据适合没有明显分区维度的场景 sql CREATE TABLE user_data ( id BIGINT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (id) ) PARTITION BY HASH (id) PARTITIONS 8; HASH 分区的好处是数据分布均匀缺点是不能按业务维度裁剪分区查询时基本要扫所有分区。 ## 分区管理操作 ### 新增分区 sql -- RANGE 分区在 MAXVALUE 之前新增 ALTER TABLE order_log REORGANIZE PARTITION pfuture INTO ( PARTITION p202405 VALUES LESS THAN (TO_DAYS(2024-06-01)), PARTITION pfuture VALUES LESS THAN MAXVALUE ); 注意不能直接 ADD PARTITION因为 pfuture 已经是 MAXVALUE 了。要用 REORGANIZE 拆分。 ### 删除分区 sql -- 删除最老的分区数据也一起删了比 DELETE 快得多 ALTER TABLE order_log DROP PARTITION p202401;这是分区表最爽的地方——删旧数据不用 DELETE直接 DROP PARTITION瞬间完成不产生 binlog 大事务。合并分区ALTERTABLEorder_log REORGANIZEPARTITIONp202402,p202403INTO(PARTITIONp2024_q1VALUESLESS THAN(TO_DAYS(2024-04-01)));## 分区裁剪 分区裁剪Partition Pruning是分区表性能优化的核心。如果查询条件包含分区键优化器会自动跳过不相关的分区。 sql-- 只扫描 p202403 分区SELECT*FROMorder_logWHEREcreate_time2024-03-01ANDcreate_time2024-04-01;用 EXPLAIN 查看分区裁剪是否生效EXPLAINPARTITIONSSELECT*FROMorder_logWHEREcreate_time2024-03-15;结果里的partitions列会显示实际扫描的分区。如果只扫了 p202403说明裁剪生效了。裁剪不生效的常见原因查询条件没有分区键对分区键使用了函数比如WHERE YEAR(create_time) 2024函数导致裁剪失效分区键在条件中被隐式类型转换第 2 点是最常见的坑。我之前写过WHERE YEAR(create_time) 2024以为会裁剪分区结果 EXPLAIN 一看全分区扫描。改成WHERE create_time 2024-01-01 AND create_time 2025-01-01就好了。分区表的限制分区表不是万能的限制不少最多 8192 个分区——按天分区的话够用 22 年按小时分区就不够了分区键必须是主键的一部分——前面说过了不支持外键——分区表上不能有外键约束唯一键必须包含分区键——和主键一样的限制所有分区必须用相同的存储引擎分区表上的 INSERT 性能可能更差——需要判断数据该插入哪个分区跨分区查询可能更慢——如果查询条件没有分区键要扫所有分区比普通表还慢实战经验按什么维度分区日志表、订单表按时间月分区方便归档和清理用户表按 HASH 分区数据均匀分布多租户系统按租户 ID 做 LIST 分区自动维护分区手动建分区太容易遗漏了建议用存储过程定时创建DELIMITER//CREATEPROCEDUREmaintain_partitions()BEGINDECLAREnext_monthDATE;SETnext_monthDATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL2MONTH),%Y-%m-01);SETsqlCONCAT(ALTER TABLE order_log REORGANIZE PARTITION pfuture INTO (,PARTITION p,DATE_FORMAT(next_month,%Y%m), VALUES LESS THAN (TO_DAYS(,DATE_ADD(next_month,INTERVAL1MONTH),)),,PARTITION pfuture VALUES LESS THAN MAXVALUE));PREPAREstmtFROMsql;EXECUTEstmt;DEALLOCATEPREPAREstmt;END//DELIMITER;配合定时任务每月执行一次自动添加新分区。 ### 归档旧数据 分区表配合交换分区做数据归档特别方便 sql-- 1. 创建归档表结构和分区表一样CREATETABLEorder_log_archiveLIKEorder_log;ALTERTABLEorder_log_archive REMOVE PARTITIONING;-- 2. 交换分区瞬间完成不复制数据ALTERTABLEorder_log EXCHANGEPARTITIONp202401WITHTABLEorder_log_archive;-- 3. 删除原分区已经空了ALTERTABLEorder_logDROPPARTITIONp202401;EXCHANGE PARTITION 是元数据操作瞬间完成不影响线上业务。归档后的数据可以压缩存储或迁移到冷存储。小结分区表的核心价值是分区裁剪和快速删除旧数据别指望它提升所有查询性能RANGE 分区最常用按时间分区是标准做法分区键必须包含在主键里这个限制决定了分区表不适合所有场景查询条件必须包含分区键才能裁剪且不能对分区键用函数用存储过程定时任务自动维护分区避免遗漏EXCHANGE PARTITION 做数据归档很好用相关阅读MySQL 官方文档 - PartitioningPartition Pruning 详解《高性能MySQL》第7章 MySQL高级特性