1. 分区表到底解决了什么问题从一次半夜告警说起先讲个真实场景。凌晨两点手机连续震动某核心业务库告警Table order_log has no partition for value 2025-06-12。一看表数据当天新增的订单日志全部插入失败前端接口报错一片。原因很简单——这张按天分区的表前一天晚上只维护到了 6 月 10 日的分区而新的一天到了分区还没建出来。这种问题几乎所有用过 MySQL 分区表的人都遇过。手动ALTER TABLE ADD PARTITION看似简单但人不是机器总有忘的时候。一旦忘记轻则数据写不进去重则整个业务链路被拖死。我见过不少公司因为这个在半夜紧急“手工加分区”甚至有人为了保险把分区提前建到半年后结果分区数量膨胀又带来性能和存储空间的额外问题。所以把“添加分区”这件事自动化是分区表运维里最值得做的一件事。你要做的就是写一个函数在 MySQL 里通常用存储过程实现让它自己判断当前时间、计算下一个分区边界、自动执行ALTER TABLE。这篇文章就把我从最初踩坑到最终稳定运行的经验完整拆开讲包括分区表原理、函数设计、完整代码、调度配置以及那些文档里不会写清楚的坑。1.1 分区表的核心机制一句话说清 RANGE 分区MySQL 支持 RANGE、LIST、HASH、KEY 四种分区类型。自动添加分区最常用的场景是 RANGE 分区尤其是按时间范围分区——比如每天一个分区、每个月一个分区。原理其实不复杂表在物理上被拆成多个独立的分区文件具体取决于存储引擎而查询时优化器可以根据条件只扫描匹配的分区而不是全表扫描。以典型的订单日志表为例CREATE TABLE order_log ( id BIGINT NOT NULL AUTO_INCREMENT, order_date DATE NOT NULL, order_no VARCHAR(32), amount DECIMAL(10,2), PRIMARY KEY (id, order_date) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(order_date)) ( PARTITION p20250601 VALUES LESS THAN (TO_DAYS(2025-06-01)), PARTITION p20250602 VALUES LESS THAN (TO_DAYS(2025-06-02)), PARTITION p20250603 VALUES LESS THAN (TO_DAYS(2025-06-03)) );这里分区键是order_date通过TO_DAYS转成整数后按范围划分。插入2025-06-01的数据会被分到第一个分区跳到2025-06-03走第三个分区。当某一天的数据到达时如果表里不存在那天的分区MySQL 就会直接报错——就是开头告警里的那个no partition for value。分区的本质就是“预先划分好数据的去向”。它不能动态感知新数据所以必须提前把边界定义好。这也是为什么自动化维护那么重要你需要在数据真正到来之前把对应的分区建好而不是等它到了再补。1.2 手动维护分区的三个致命痛点第一是“时间差”。业务日期和数据写入时间往往有偏差尤其是跨天、补数据、回放等场景。你前一天晚上建好了今天的分区但凌晨 ETL 回补昨天的数据如果昨天的分区没建一样会卡死。这要求运维者不仅要建“明天”的分区还要兼顾“可能晚到”的昨日分区。靠人肉盯早晚会漏。第二是“扩容逻辑”的重复劳动。每个分区都要写清楚VALUES LESS THAN边界值必须严格递增不能有交集。手工加十几个分区的时候手一抖写错边界MySQL 不会马上报错而是等数据到来时才发现错位。这种隐藏错误非常恶心。第三是分区数量的失控。我接手过一个系统分区每月建一次但运维为了省事一次建了三年的分区。数据库里躺着 36 个空分区每次全表扫描分区元数据耗时增加information_schema查询变慢备份时间也被拖长。手动管理很难做到“刚刚好”不是建多了就是建少了。2. 设计自动添加分区的函数关键决策点要写一个可靠的自动分区函数不能上来就写ALTER TABLE ADD PARTITION。先想清楚几个设计前提否则你的函数可能在线上跑半年后突然翻车。2.1 选择分区粒度日、周、月还是“按需生成”粒度直接影响数据量和分区数量。日志类、流水类数据通常按天分区一天几百万行的话分区文件保持在 1~2 个 G 以内方便归档和清理。业务量小的可以按月比如用户表变更记录。按月的话一年才 12 个分区维护压力小很多。但我的建议是函数设计成通用的粒度作为参数传入。比如一个参数p_interval表示天间隔按天分区传 1按周传 7按月传 30当然月份不固定后面我会给更精确的写法。这样遇到不同表可以复用同一个函数而不是每张表写一份。2.2 分区命名必须可预测、可排序、可识别分区名建议用p_yyyyMMdd或p_yyyyMM。命名不光是给人看的后面做监控、清理、归档都需要按名字后缀来筛选。比如查某个分区大小SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name order_log ORDER BY partition_name DESC LIMIT 5;如果命名是稳定的日期格式就能直接用名字排序来找到最新分区不需要去解析partition_description这个字段存的是TO_DAYS后的整数可读性差。记住分区边界可以变但命名习惯一旦定下就别改很多自动化逻辑会依赖它。2.3 边界值策略预创建到底要建几个预创建几个分区是个经典拉扯问题。建少了怕哪天没来得及跑定时任务建多了空分区占地方。我的实践方案是函数每次执行时确保从当前日期以系统时间为准开始未来p_pre_num个分区都存在。比如p_pre_num 3今天 6 月 1 日函数检查后保证 6 月 2 日、6 月 3 日、6 月 4 日的分区都在。这样任务即使因为极端情况晚跑一天数据也一定落得进去。同时函数还要检查昨天甚至前天的分区是否存在因为可能遇到数据延迟回补。补数据不是常态但还是把前一个分区也兜底检查一次成本很低收益很高。注意预创建分区数不要设得过大通常 3~7 个足够。分区过多时MySQL 优化器做分区裁剪的开销会增加尤其是查询条件没带分区键的时候所有分区都得扫一遍元数据。3. 完整实现MySQL 存储过程自动添加分区直接上代码。这是一个我实际在线上用了很久的存储过程按天、预创建 5 个分区、同时补查昨天的分区。细节我会在下面逐段解释。DELIMITER $$ CREATE PROCEDURE sp_auto_add_daily_partition( IN p_table_name VARCHAR(64), -- 表名 IN p_partition_col VARCHAR(64), -- 分区字段名必须与表定义一致 IN p_pre_num INT, -- 预创建未来分区个数建议 3~7 IN p_cleanup_old BOOLEAN -- 是否顺带清理过期分区可选 ) BEGIN DECLARE v_partition_name VARCHAR(64); DECLARE v_partition_desc INT; DECLARE v_base_date DATE; DECLARE v_sql TEXT; DECLARE v_max_partition_date DATE; DECLARE v_exist_count INT; -- 1. 获取当前日期作为基础日期 SET v_base_date CURDATE(); -- 2. 检查当天分区是否存在不存在则创建当天分区 SET v_partition_name CONCAT(p, DATE_FORMAT(v_base_date, %Y%m%d)); SET v_partition_desc TO_DAYS(DATE_ADD(v_base_date, INTERVAL 1 DAY)); SELECT COUNT(*) INTO v_exist_count FROM information_schema.partitions WHERE table_schema DATABASE() AND table_name p_table_name AND partition_name v_partition_name; IF v_exist_count 0 THEN SET v_sql CONCAT( ALTER TABLE , p_table_name, ADD PARTITION (, PARTITION , v_partition_name, VALUES LESS THAN (, v_partition_desc, )) ); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; -- 3. 预创建未来 p_pre_num 个分区 WHILE p_pre_num 0 DO SET v_base_date DATE_ADD(v_base_date, INTERVAL 1 DAY); SET v_partition_name CONCAT(p, DATE_FORMAT(v_base_date, %Y%m%d)); SET v_partition_desc TO_DAYS(DATE_ADD(v_base_date, INTERVAL 1 DAY)); SELECT COUNT(*) INTO v_exist_count FROM information_schema.partitions WHERE table_schema DATABASE() AND table_name p_table_name AND partition_name v_partition_name; IF v_exist_count 0 THEN SET v_sql CONCAT( ALTER TABLE , p_table_name, ADD PARTITION (, PARTITION , v_partition_name, VALUES LESS THAN (, v_partition_desc, )) ); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; SET p_pre_num p_pre_num - 1; END WHILE; -- 4. 检查前一天分区是否存在如果数据可能延迟回补循环补建前几天的分区 SET v_base_date DATE_SUB(CURDATE(), INTERVAL 1 DAY); SET v_partition_name CONCAT(p, DATE_FORMAT(v_base_date, %Y%m%d)); SET v_partition_desc TO_DAYS(DATE_ADD(v_base_date, INTERVAL 1 DAY)); SELECT COUNT(*) INTO v_exist_count FROM information_schema.partitions WHERE table_schema DATABASE() AND table_name p_table_name AND partition_name v_partition_name; IF v_exist_count 0 THEN SET v_sql CONCAT( ALTER TABLE , p_table_name, ADD PARTITION (, PARTITION , v_partition_name, VALUES LESS THAN (, v_partition_desc, )) ); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END$$ DELIMITER ;3.1 为什么用动态 SQL分区语句不能直接传参很多新手会问不是可以直接写ALTER TABLE order_log ADD PARTITION ...吗为什么非要拼字符串再PREPARE这里有个 MySQL 的硬性限制分区名和边界值不能作为预处理语句的变量绑定只能通过字符串拼接生成完整的 SQL 再执行。所以必须用PREPAREEXECUTE这也要求所有外部输入表名、分区名都要严格校验否则容易造成 SQL 注入风险。我在生产环境中使用这个存储过程时表名白名单是写死在调用方的不允许用户直接传任意表名这是基本的安全底线。3.2 边界值为什么是TO_DAYS(当天 1)分区边界采用“小于某个值”的语义。如果某个分区的边界是TO_DAYS(2025-06-03)意味着这个分区装的是order_date 2025-06-03的所有数据也就是 6 月 2 日全天。因此要为 6 月 2 日建分区边界应该设置为TO_DAYS(2025-06-03)即“下一天的零点”。这里有个很容易犯的错把边界写成TO_DAYS(2025-06-02)那这个分区实际上装的 6 月 1 日的数据你自己会搞混。记住公式为日期 D 建分区边界值 下一天的日期TO_DAYS(D1)或者用UNIX_TIMESTAMP(D1)如果分区键是时间戳。如果分区键是DATETIME类型而且用了TO_DAYS边界值还是整数逻辑一样。如果用的是UNIX_TIMESTAMP边界值就是时间戳整数公式照搬。3.3 防重判断为什么每建一个分区都要查一次information_schema你可能会想我拿到当前日期后直接ALTER TABLE ADD PARTITION不就行了问题是如果定时任务意外重复执行呢比如刚才建的 6 月 3 日分区还没删任务又被触发了一次。缺少防重判断第二次执行会直接报Duplicate partition name。虽然报错不致命但在无人值守的凌晨这种错误会让监控误以为任务失败进而触发告警把你从被窝里拽起来。所以每次建分区前查询一下information_schema.partitions确认分区名不存在才执行ALTER。这样即使任务重复跑也只会做“无操作”不会报错。可能有人会进一步问一个存储过程调用里循环检查会不会有并发问题如果同一张表的同一个分区被两个并发任务同时判断为“不存在”然后又同时执行ALTER还是会撞。为了避免这种情况建议把定时任务设为单实例执行或者在应用层对表名加分布式锁。如果你用的是 MySQL 事件调度器它本身是串行执行的不用担心并发。但如果你通过外部 cron 调用就要考虑幂等性了。4. 部署与调度让函数在无人值守时工作写好存储过程只是第一步你还需要让它能定期执行。MySQL 自带事件调度器Event Scheduler这是最简单可靠的方式不需要额外写外部脚本。4.1 开启事件调度器并创建定时任务检查事件调度器是否开启SHOW VARIABLES LIKE event_scheduler;如果值是OFF临时开启重启失效SET GLOBAL event_scheduler ON;永久开启需要在 MySQL 配置文件的[mysqld]段加一行event_schedulerON创建每天凌晨 1 点执行一次的定时任务CREATE EVENT ev_auto_add_partition_daily ON SCHEDULE EVERY 1 DAY STARTS 2025-06-01 01:00:00 ON COMPLETION PRESERVE ENABLE DO BEGIN -- 为核心日志表自动创建未来 5 个分区并检查前一天分区 CALL sp_auto_add_daily_partition(order_log, order_date, 5, FALSE); CALL sp_auto_add_daily_partition(user_login_log, login_time, 3, FALSE); CALL sp_auto_add_daily_partition(payment_transaction, pay_time, 7, FALSE); END;STARTS建议选择业务低谷期比如凌晨 1 点到 4 点这时候ALTER TABLE虽然需要拿元数据锁但影响最小。注意ALTER TABLE ... ADD PARTITION在 MySQL 8.0 里依然需要小心长时间阻塞读写所以尽量避开业务高峰。4.2 事件的历史数据检查避免因服务器重启导致事件丢失CREATE EVENT默认不会持久化到服务器配置里但它会保存在 MySQL 系统库mysql.event中。只要你是通过 SQL 创建的重启后依然存在。但如果有人手动删掉了事件或者数据库被恢复到旧备份你就得注意检查事件是否还在。我自己习惯在监控系统里加一个探针每天查询一次SELECT event_name, status, last_executed FROM mysql.event WHERE event_name ev_auto_add_partition_daily;如果last_executed太陈旧或status不是ENABLED立即报警。这个探针救过我一次——有一次 DBA 在迁移实例时忘了把事件调度器开起来过了两天才发现分区没建。有了探针就不会出这种事。4.3 验证自动化是否生效一条 SQL 看清分区列表执行完定时任务后最好抽看分区列表SELECT partition_name, partition_ordinal_position, partition_description FROM information_schema.partitions WHERE table_name order_log ORDER BY partition_ordinal_position DESC LIMIT 10;看到类似结果partition_namepartition_ordinal_positionpartition_descriptionp202506088739024p202506077739023p202506066739022说明 6 月 8 日的分区已经预建好了。注意partition_description里的值是TO_DAYS(2025-06-09)的整数739024看起来不直观所以我都用FROM_DAYS(partition_description)转回日期再人工核对SELECT partition_name, FROM_DAYS(partition_description) AS upper_bound FROM information_schema.partitions WHERE table_name order_log ORDER BY partition_ordinal_position DESC LIMIT 5;这条查询在排查问题时非常有用能一眼看出边界有没有越级、有没有漏分区。5. 进阶优化与踩坑实录你以为把函数跑起来就完事了我在实际维护中还是踩了不少坑有些坑非常隐蔽不写出来你可能得花一整晚才能排查出来。5.1 分区键必须包含在主键里——这个坑让人崩溃如果你在建表时定义了主键比如PRIMARY KEY (id)然后却想按order_date分区MySQL 会直接拒绝A PRIMARY KEY must include all columns in the tables partitioning function。这是很多刚接触分区表的人的第一个报错。解决方法是把分区键加进主键变成复合主键PRIMARY KEY (id, order_date)注意这意味着id单独不再是唯一约束。如果你的业务代码依赖id唯一性来去重就要重新考虑分区表设计。订单、日志这类流水表通常能接受复合主键但用户表、配置表就不适合按业务字段分区了。还有一个相关坑如果分区键用TIMESTAMP类型MySQL 5.7 中TO_DAYS和UNIX_TIMESTAMP都支持MySQL 8.0 中TIMESTAMP类型没问题但注意TO_DAYS对DATETIME支持对TIMESTAMP同样支持。如果分区键是日期字符串最好统一存储为 DATE 类型避免隐式转换导致分区裁剪失效。5.2 边界重叠与跳跃MAXVALUE分区救急但不能常用有时候为了不再频繁加分区有人会在表末尾加一个MAXVALUE分区让所有不在明确分区范围内的数据都归到这里。这个做法看起来省事但副作用极强MAXVALUE分区会像一个“垃圾筐”所有来不及建分区的数据都会落入其中导致这个分区无限膨胀。如果你有按天查询的语句一旦数据进到MAXVALUE分区分区裁剪失效查询会扫描整个垃圾分区性能直线下降。我见过某张表就是因为加了MAXVALUE导致晚到一天的数据在凌晨集中涌入后某条大查询直接把数据库 IO 打满整个主库延迟飙升。后来我把它清掉改用“预创建 延迟兜底”的方式才彻底稳下来。如果你的历史表里已经存在MAXVALUE分区又想继续用自动添加分区需要先重建分区表把MAXVALUE去掉或者用REORGANIZE PARTITION技巧收缩。这个操作比较复杂不建议在线重构大表最好通过 pt-osc 或新建表切换的方式来做。5.3 用information_schema动态获取下界而不是死记硬背前面存储过程里我直接假设已有分区是按天连续递增的用CURDATE()推算下一个分区。但如果某天任务停了已有分区和当前日期之间的间隔是天数N调用函数时如果只检查“当天及未来 5 天”就会漏掉中间的 N 天。比如服务器宕机 3 天事件调度器恢复后发现 6 月 1 日、6 月 2 日的分区都没有而我的函数只检查当天和前一天——回来时是 6 月 4 日它会建 6 月 4 日及未来分区但 6 月 1~3 日的分区还是缺的。要彻底解决“补历史”更稳健的写法是先查出当前最大分区边界如果最大边界比今天还早就从这个边界开始一直补到预创建的天数。核心逻辑如下-- 获取当前最大分区边界对应的日期 SELECT MAX(FROM_DAYS(partition_description)) INTO v_max_partition_date FROM information_schema.partitions WHERE table_schema DATABASE() AND table_name p_table_name; -- 如果最大边界日期小于今天说明有缺口从边界日期开始补 IF v_max_partition_date CURDATE() THEN SET v_base_date v_max_partition_date; ELSE SET v_base_date CURDATE(); END IF;注意FROM_DAYS返回的是 DATE 类型可以和CURDATE()直接比较。但如果分区边界用的是UNIX_TIMESTAMP你需要先FROM_UNIXTIME(partition_description)再比较。我在新的存储过程版本里都是先读取最大边界日期作为起点然后循环补到预创建天数为止这样就算事件停摆一周也能一次性补齐所有缺失分区。5.4 分区表备份与归档自动加分区的同时别忘了“减分区”自动添加分区解决了“分区不够”的问题但反过来“分区太多”也需要自动化。比如日志表保留 180 天超过 180 天的分区应该自动删掉或归档。如果你只加不减分区数量会一路涨上去对性能的影响随着时间越来越明显。我通常会在同一个存储过程里加一个可选参数p_retain_days当它大于 0 时执行完建分区逻辑后再删除TO_DAYS(CURDATE()) - p_retain_days之前的分区DELETE FROM table_name PARTITION (p_old_date...);或者直接:ALTER TABLE table_name DROP PARTITION p20250601, p20250531;这个删除动作必须谨慎数据会被物理删除。如果业务需要保留历史建议在删除前把数据导出到归档库或冷存储再删除分区。删除分区比DELETE FROM效率高得多因为它是直接删除物理文件级别的数据不需要记录一堆 binlog。另外提醒一下自动删除分区时要确保你删除的日期确实早于保留天数不要误删正在写入的分区。基本逻辑是取分区边界日期与当前日期做差值小于保留天数就跳过。5.5 分区表的 DDL 锁风险在低峰期执行仍然是底线ALTER TABLE ADD PARTITION虽然是ONLINE DDL支持的一部分MySQL 5.7 之后对 InnoDB 的分区表ADD PARTITION通常不需要复制整表数据但依然需要获取MDL元数据锁在持有 MDL 期间对该表的读写会被阻塞。所以即便自动化了也务必将任务排在业务低谷。在我这边所有自动分区事件都集中在凌晨 02:30~03:00 之间并且按表大小错开执行时间订单表 2:30 跑日志表 2:40 跑。避免同一时间多个大表同时拿锁把数据库搞出“锁等待风暴”。如果你用的是 MySQL 8.0执行ALTER TABLE ... ADD PARTITION时还支持ALGORITHMINPLACE, LOCKNONE的语法但分区操作对特定场景依然有限制不能完全依赖它。实测下来最稳妥的做法还是低峰期执行不要挑战极端。5.6 MySQL 5.7 与 8.0 的兼容性差异很多公司还在用 5.7但新项目开始上 8.0。两个版本对自动添加分区的存储过程无明显语法差异但有几个细节要注意MySQL 8.0 对分区表的限制更多比如外键约束不能用于分区表因此你在设计时就要避开外键。MySQL 8.0 已经弃用了部分分区维护命令但ADD PARTITION、DROP PARTITION这些基础命令还在。information_schema.partitions在 8.0 中依然可用但查询速度在大分区数量下可能比 5.7 慢建议不要频繁扫描这张表尤其是分区数成百上千之后。我自己从 5.7 迁移到 8.0 时发现存储过程无需改动但事件调度器的时区处理略有不同。如果你的事件是每天 1 点执行而服务器时区或 MySQL 的time_zone设置不对可能导致任务在诡异的时间点跑。建议使用例SELECT global.time_zone, session.time_zone;确保是08:00或你期望的时区不然定时任务执行时间会偏。写在最后的一点个人体会这套自动分区函数我从最早的手工脚本迭代到现在的通用存储过程中间踩过的坑基本都在上文里了。最有价值的不是那几行ALTER TABLE而是你对“边界”的理解分区的边界不仅代表了数据归属更决定了查询性能和运维节奏。每次调试分区时我都会用两句 SQL 把当前状态打出来看——哪块空了哪块多了下一跳在哪。心中有数自动才不会变成“自动出乱”。如果你刚开始落地这套方案我的建议是先在测试库建一张与线上结构一致的表用未来日期插入数据故意不跑定时任务跑一遍存储过程观察补分区是否完整。确认无误后再上生产。生产环境上线后头一周每天检查一次mysql.event里的last_executed确保调度器稳定执行。等一周没告警你就可以安心睡整觉了——前提是告警平台里别忘了加上“分区缺失”的监控项。