MySQL进阶训练y---MySQL 分区表用法与性能调优技巧

单表破千万，查询从毫秒变秒级，DDL 改表结构直接锁表到超时——MySQL 分区表是解决这类问题最直接的手段。但用错了，比不分区还慢。本文讲清楚怎么用、怎么避坑、怎么调优。

一、分区表解决什么问题？

一句话：让大表的查询只扫描该扫的分区，让维护操作只影响该影响的分区。

核心机制叫"分区裁剪"（Partition Pruning）——查询带上分区键条件时，优化器自动跳过无关分区，只扫命中的那几个。

实测：一张 8000 万行的日志表，按月分区后，查单月数据从 12 秒降到 0.4 秒，不是优化索引能做到的。

二、MySQL 支持哪些分区类型？

选型铁律：分区键必须是高频查询的 WHERE 条件列。 选错了，优化器无法裁剪，全分区扫描比不分区还慢。

三、实战：按月分区日志表

以 5000 万行的访问日志为例，按 log_date 范围分区。

第一步：建父表

sqlCREATE TABLE access_log (
    id         BIGINT AUTO_INCREMENT,
    user_id    INT NOT NULL,
    log_date   DATE NOT NULL,
    url        VARCHAR(512),
    PRIMARY KEY (id, log_date)  -- ⚠️ 主键必须包含分区键
) PARTITION BY RANGE (TO_DAYS(log_date)) (
    PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

⚠️ 坑1：用 TO_DAYS(log_date) 做分区键，不是 log_date 本身。 因为 RANGE 分区不支持 DATE 类型直接比较，必须转成整数。

⚠️ 坑2：主键必须包含分区键。 id 单独做主键会报错，必须是 (id, log_date) 联合主键。

第二步：插入数据，自动路由

sqlINSERT INTO access_log (user_id, log_date, url)
VALUES (1001, '2025-01-15', '/api/users');
-- 自动落入 p202501，应用层无感知

第三步：验证分区裁剪

sqlEXPLAIN PARTITIONS
SELECT * FROM access_log
WHERE log_date = '2025-01-15';

输出会显示 partitions: p202501——只扫了一个分区，这就是分区裁剪。

四、五个性能调优关键点

1. 分区数控制在 50 以内

分区太多，优化器选择执行计划的开销会飙升。建议单个业务表不超过 50 个分区，日志类可以到 100。

2. 每个分区建议 500 万~2000 万行

太小：管理成本高，裁剪收益低。太大：单个分区仍然很慢，失去分区意义。

3. 一定要建全局索引

MySQL 5.7+ 支持全局索引，自动覆盖所有分区：

sqlCREATE INDEX idx_user_date ON access_log (user_id, log_date);
-- 自动应用到所有分区，不用每个分区单独建

4. 删除数据用 ALTER TABLE ... DROP PARTITION

sql-- ❌ 慢：DELETE FROM access_log WHERE log_date < '2025-01-01';
-- 产生大量死元组，VACUUM 开销巨大

-- ✅ 快：直接删分区，秒级释放空间
ALTER TABLE access_log DROP PARTITION p202412;

这是分区表最大的运维优势——删数据不产生死元组，不触发 VACUUM。

5. 定期 ANALYZE TABLE

分区表的统计信息需要手动更新，否则优化器可能选错执行计划：

sqlANALYZE TABLE access_log;

建议写 cron 每周执行一次。

五、避坑清单

写在最后

MySQL 分区表不是银弹，但它是处理千万级以上数据最确定的手段。核心就三句话：

1. 分区键选对，查询快 10 倍。
2. 删数据用 DROP PARTITION，别用 DELETE。
3. 分区数别贪多，全局索引必须建。

选对分区键，管好分区数，你的大表就从"定时炸弹"变成"可控资产"。