分区表 - MySQL 教程

讲解

分区（PARTITION）把一张逻辑表拆成多个物理分区存储，对应用透明——查询还是查一张表，MySQL 自动决定数据落在哪个分区。最常用的形态是 RANGE 分区按时间切：日志表、订单流水表按月或按年分成 p2024、p2025 这样的分区。它解决两个真实痛点：查询裁剪和快速归档。

查询裁剪（partition pruning）是性能收益的来源：WHERE 条件落在某个时间段时，优化器只扫描命中的分区，EXPLAIN 的 partitions 列会明确列出实际访问了哪几个分区，其余分区完全不碰。快速归档是运维收益：删除「2023 年以前的数据」如果用 DELETE 要删几小时、产生海量 redo；而 ALTER TABLE ... DROP PARTITION 是元数据操作，秒级完成，磁盘立刻释放。

分区有一条硬性约束：表上所有唯一键（包括主键）必须包含分区表达式用到的全部列。按 sold_on 分区，主键就得是 (id, sold_on) 而不能只是 id。这条约束常常逼你调整主键设计，是分区方案落地的第一道坎。另外分区不是银弹：分区数过多（几百上千）会让元数据操作变慢；分区维度必须匹配主要查询条件，按错了维度分区等于白做；真正的水平扩展（单机装不下）还是要靠分库分表。

示例

建一张按日期 RANGE 分区的流水表——注意主键被迫带上分区列 sold_on：

CREATE TABLE sales_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sold_on DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, sold_on)
) PARTITION BY RANGE COLUMNS(sold_on) (
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

INSERT INTO sales_log (sold_on, amount) VALUES
  ('2024-06-15', 100.00), ('2024-12-01', 200.00),
  ('2025-03-08', 150.00), ('2025-11-20', 300.00),
  ('2026-01-05', 250.00);

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_log';

验证分区裁剪：查询条件落在 2025 年，EXPLAIN 的 partitions 列显示只访问 p2025：

EXPLAIN SELECT * FROM sales_log WHERE sold_on >= '2025-01-01' AND sold_on < '2026-01-01'\G

秒级归档：DROP PARTITION 直接拿掉 2024 分区，比 DELETE 快几个数量级：

ALTER TABLE sales_log DROP PARTITION p2024;

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sales_log';

常见坑

唯一键不含分区列：CREATE TABLE 直接报 1503（A PRIMARY KEY must include all columns...）。按时间分区时把分区列并进主键，或用普通索引替代部分唯一约束。
分区维度和查询条件不匹配：查询都按 user_id 过滤，表却按时间分区，每个查询扫全部几十个分区，比不分区还慢。分区键必须来自最高频的过滤条件。
分区数量失控：按天分区建了几千个分区，元数据膨胀、DDL 变慢。配合 DROP PARTITION 定期裁剪，保留滚动窗口（如最近 24 个月）。
把分区当分库分表的替代：分区解决的是单机上的大表管理，单机容量和连接数到顶时，需要的是水平拆分而不是更多分区。

小结

RANGE 时间分区带来查询裁剪（看 EXPLAIN 的 partitions 列）和秒级归档（DROP PARTITION）；唯一键必须包含分区列，分区维度要匹配查询模式。下一章看 MySQL 高可用的基石：主从复制。