mysql如何使用分区表提高查询性能_mysql分区表优化

分区表非万能加速器，需查询条件含分区键才有效；RANGE分区注意边界与NULL处理；REORGANIZE比DROP更安全；唯一索引和主键必须包含分区键。

分区表不是万能加速器，先确认是否真适合你的查询模式

mysql 分区表对性能的提升有严格前提：查询条件中必须包含分区键（PARTITION BY 所用的列），否则 MySQL 仍需扫描所有分区（ALL PARTITIONS），甚至因元数据开销反而更慢。常见误用是按 created_at 分区，但业务查询却只查 user_id —— 这类查询不会受益，还增加维护成本。

判断是否适用，执行 EXPLaiN PARTITIONS select ...，观察 partitions 列是否只列出少量分区。如果不是，别急着建分区。

RANGE 分区最常用，但要注意边界值和 NULL 处理

按时间范围（如月/年）做 RANGE 分区最直观，但容易踩两个坑：

• VALUES less THAN 是严格左闭右开，LESS THAN (202401) 不包含 20240101，得写成 LESS THAN (202402) 才能覆盖整个 2024 年 1 月
• NULL 值会被归入第一个分区（即使你定义的是 LESS THAN (202301)），若业务中 created_at 允许为空，务必在建表前清理或改用 LIST COLUMNS 避免数据错位

示例建表语句片段：

PARTITION BY RANGE (YEAR(created_at)) (   PARTITION p2022 VALUES LESS THAN (2023),   PARTITION p2023 VALUES LESS THAN (2024),   PARTITION p2024 VALUES LESS THAN (2025) );

ALTER table … REORGANIZE PARTITION 比 DROP + ADD 更安全

日常维护中常需新增分区或合并旧分区。直接 DROP PARTITION 会永久删除数据；而 REORGANIZE PARTITION 可无损重划范围，且支持在线操作（MySQL 8.0+ 配合 ALgoRITHM=INPLACE）。

比如要为 2025 年添加分区：

ALTER TABLE orders REORGANIZE PARTITION p_max INTO (   PARTITION p2025 VALUES LESS THAN (2026),   PARTITION p_max VALUES LESS THAN MAXVALUE );

注意：p_max 必须存在且为 MAXVALUE 分区，否则语句报错 Error 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed。

唯一索引和主键必须包含分区键

这是硬性限制：如果表启用了分区，所有唯一索引（含 PRIMARY KEY）的字段组合必须包含分区键列，否则建表失败，报错 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function。

例如按 order_date 分区，就不能只用 id 当主键；得改成 PRIMARY KEY (id, order_date) 或直接用 (order_date, id)。这会影响二级索引大小和查询写法——以后按 id 单查，实际仍是全分区扫描。

所以分区前务必梳理主键和唯一约束的设计，不是加个 PARTITION BY 就完事。