mysql数据库中的分区表与分区策略

分区表并非万能性能开关，仅在数据量极大、冷热分离明确且查询总命中分区键时有效；否则可能因未剪枝或跨分区操作导致性能下降。

分区表在 mysql 中不是万能的性能开关

MySQL 的分区表（PARTITION）常被误认为“加了就能提速”，实际它只在特定场景下有效：数据量极大（单表 > 1TB）、历史数据冷热分离明确、且查询条件总能命中分区键。如果 WHERE 条件不包含分区字段，或频繁跨分区 JOIN / ORDER BY，性能反而可能下降。

常见分区策略与对应使用场景

MySQL 支持 RANGE、LIST、HASH、KEY 四种原生分区类型，选错策略会导致无法剪枝或数据倾斜：

• RANGE：适合按时间（如 created_at）或数值区间归档，例如每月一个分区；但必须是有序连续值，不能跳过范围
• LIST：适用于离散有限值（如 region_id IN (1,2,3)），新增值需手动 ALTER table ... REORGANIZE PARTITION
• HASH：按表达式取模分桶（如 HASH(YEAR(created_at))），均匀分布但无法做范围裁剪，只支持等值查询
• KEY：类似 HASH，但底层用 MySQL 内部哈希函数，支持非整数列（如 VARCHAR），推荐代替 HASH

必须避开的几个硬坑

分区表在 DDL 和运维层面有隐性约束，踩中会直接报错或失效：

• 主键/唯一索引必须包含所有分区字段 —— 否则建表失败：Error 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
• 不能对分区表使用 MyISAM 引擎，仅支持 InnoDB 和 NDB
• ALTER TABLE ... DROP PARTITION 会直接删除数据，不可回滚；清空分区请用 TRUNCATE PARTITION p_name
• 分区数上限为 8192，但超过 100 个分区后，EXPLaiN PARTITIONS 输出难以阅读，元数据操作变慢

验证是否真正用上分区剪枝

光看 SHOW CREATE TABLE 不代表生效。必须用 EXPLAIN PARTITIONS 检查实际扫描的分区：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date >= '2024-01-01';

输出中的 partitions 列应只显示匹配的几个分区名（如 p202401,p202402）。如果显示 NULL 或全部分区名，说明没剪枝 —— 很可能是 WHERE 条件类型不匹配（比如对分区字段用了函数：WHERE YEAR(order_date) = 2024）或字段类型隐式转换。

分区字段类型、查询写法、索引设计三者必须咬合，否则分区只是摆设。尤其注意时间字段是否带时区、是否被函数包裹、是否和分区定义中的表达式完全一致。