做MySQL分区表维护的同学十有八九都经历过手动加分区的痛。业务表数据量一大按天做RANGE分区是最常见的选择但分区不会自己长出来你得在每天零点之前把下一个分区提前建好。偶尔手一抖漏加一次凌晨业务写入直接报错大半夜爬起来救火。这篇文章想分享的是这个场景的通用解法写一个自动添加分区表的函数落地时用存储过程配合MySQL的事件调度器让分区在后台自己长出来。标题里写函数实际建库时却不建议用CREATE FUNCTION这里面的弯在第2节细说。这套方案我们线上用了很长时间从单表到几十张分区表都是同一套逻辑在管基本做到无人值守。接下来直接讲思路、贴代码、聊踩坑都是可以直接抄作业的干货。1. 为什么要写一个自动加分区的存储过程1.1 分区表解决的可不是查询变快这么简单MySQL的RANGE分区尤其是按日期做的TO_DAYS()分区是互联网业务里处理订单、流水、日志这类时间序数据最常用的手段。很多人一听到分区表第一反应就是查询快其实分区裁剪只是收益之一而且只有当SQL条件能落到分区键上时才有用。真正让我离不开分区表的是另外两个价值。第一个是数据生命周期管理。线上订单表数据量起来之后合规和成本要求我们只能保留最近一年或者两年的数据。如果是普通单表清理一亿行历史数据要跑几个小时甚至更久期间还会产生巨大的redo log和undo压力弄不好就把磁盘打满。而分区表只需要一条ALTER TABLE ... DROP PARTITION秒级释放磁盘空间本质上是改元数据不是逐行删除对业务的影响小得多。第二个是索引和统计信息的维护成本。单表几千万行时每天晚上的统计信息更新和OPTIMIZE操作都会拖累跑批。分区表可以把这些任务按时间维度拆到单个分区上做哪个分区要清理就处理哪个不用每次动全表。所以分区表不是为了炫技它是为了给维护环节省时间、降低操作风险。这也是我为什么坚持让线上大表统一走按天分区的根本原因。有了分区表之后紧接着就会出现一个操作层面的问题分区不会自动出现得有人把它建好而且必须在数据写入到达之前建好这就引出了最折磨人的手动加分区环节。1.2 手动加分区为什么一定会掉链子手动加分区这件事难点从来不在SQL怎么写——不就是一条ALTER TABLE ADD PARTITION吗真正的难点在于别忘而且别算错提前量。假设我们按天分区最大分区边界是2025-02-10那么当业务写入create_time为2025-02-11的数据时MySQL会直接抛错Table has no partition for value from 2025-02-11。这个错误一旦在线上出现受影响的不是一条SQL而是所有往这张表写的请求支付、下单、日志采集全部中断。我见过凌晨两点被叫起来加分区的同事也见过因为活动流量暴涨导致分区提前用完、然后DBA抱着电脑蹲在机房里手动补分区的场景。更麻烦的是多环境问题。测试环境、预发环境、生产环境的分区表结构经常不完全一样你在测试环境写好了一套加分区语句拿到生产环境可能因为现有最大分区不同而报错。每个环境都要单独判断一次现状手工操作量成倍增加。这些事情反复出现之后结论就很明确了必须让加分区变成一段固定的逻辑输入参数只要表名和需要预留的天数剩下的从读分区现状到拼接DDL再到执行全部自动化。这也正是本文要写的东西。1.3 标题写函数落地为什么用存储过程这里有个新手特别容易踩的坑需求叫MySQL自动添加分区表的函数很多人就用CREATE FUNCTION去写结果发现怎么都建不了或者建了之后一执行就报错。原因是MySQL的存储函数FUNCTION有严格限制函数体内不允许执行PREPARE、EXECUTE这类动态SQL语句否则报ERROR 1336: Dynamic SQL is not allowed in stored function。而加分区的核心恰恰是运行时动态拼接SQL分区名和边界值都要先查出来、算出来然后拼成ALTER TABLE语句再执行这必须用动态SQL。MySQL里允许执行动态SQL的存储程序是存储过程PROCEDURE。所以哪怕你平时习惯把这段逻辑叫作函数建库时也一定要用CREATE PROCEDURE。结论顺手记一下名字按习惯叫没问题落地姿势按PROCEDURE走。2. 核心实现一条存储过程搞定自动加分区2.1 设计思路先查最大分区边界再往后推N天写自动加分区之前先想清楚它到底要做什么。给定一张按RANGE(TO_DAYS)分区的表我要告诉它从当前最大分区往后补齐N个分区它就能自动完成三件事。第一步查出这张表当前最大分区的边界。这个信息从information_schema.PARTITIONS里拿对于RANGE TO_DAYS分区每一行分区信息里都有一个PARTITION_DESCRIPTION字段存的是边界日期的TO_DAYS整数值比如边界2025-02-11对应的是739950这样的一个数字。既然是整数直接MAX(PARTITION_DESCRIPTION)就能取到最大边界非常可靠。第二步以这个边界日期为起点按天往后推生成N天的分区定义。每天生成一个分区命名为pYYYYMMDD边界是下一天的TO_DAYS值。第三步把这些分区定义拼成ALTER TABLE ADD PARTITION语句用PREPARE/EXECUTE动态执行。为什么非得动态执行因为SQL语句里的表名、分区名、日期全部是运行时变量静态SQL根本写不出来。为什么要从information_schema取数据而不是直接解析SHOW CREATE TABLE因为information_schema是一张可查询的关系表可以直接聚合、排序、过滤SHOW CREATE TABLE返回的是大段文本解析它又慢又容易出错完全没有必要。这里补充一点如果你用的是MySQL 8.0还可以从performance_schema或更细粒度的字典表取数但information_schema依然是各版本通用、零配置成本的选择。2.2 完整代码实测可用的自动加分区存储过程下面是完整代码直接贴出来已经做了空值判断和重复分区判断比网上很多粗糙版本要稳一些。以MySQL 8.0为例5.7也通用。DELIMITER $$ DROP PROCEDURE IF EXISTS sp_auto_add_partition$$ CREATE PROCEDURE sp_auto_add_partition( IN p_schema_name VARCHAR(64), IN p_table_name VARCHAR(64), IN p_advance_days INT ) BEGIN DECLARE v_max_desc INT DEFAULT 0; DECLARE v_start_date DATE; DECLARE v_next_date DATE; DECLARE v_part_name VARCHAR(16); DECLARE v_sql VARCHAR(4096) DEFAULT ; DECLARE v_has_max INT DEFAULT 0; DECLARE v_part_count INT DEFAULT 0; -- 检查是否存在MAXVALUE分区存在则先拆分否则后续ADD PARTITION会报错 SELECT COUNT(*) INTO v_has_max FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA p_schema_name AND TABLE_NAME p_table_name AND PARTITION_DESCRIPTION IS NULL; IF v_has_max 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 存在MAXVALUE分区请先拆分MAXVALUE分区再执行自动加分区; END IF; -- 取当前最大分区的边界描述 SELECT MAX(PARTITION_DESCRIPTION) INTO v_max_desc FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA p_schema_name AND TABLE_NAME p_table_name; IF v_max_desc IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 未找到分区信息请确认该表是RANGE分区表且有初始分区; END IF; -- 将最大边界整数转成日期 SET v_start_date FROM_DAYS(v_max_desc); -- 循环生成新分区 WHILE p_advance_days 0 DO SET v_next_date DATE_ADD(v_start_date, INTERVAL 1 DAY); SET v_part_name CONCAT(p, DATE_FORMAT(v_next_date, %Y%m%d)); -- 分区名已存在则跳过避免重复添加报错 SELECT COUNT(*) INTO v_part_count FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA p_schema_name AND TABLE_NAME p_table_name AND PARTITION_NAME v_part_name; IF v_part_count 0 THEN SET v_sql CONCAT( ALTER TABLE , p_schema_name, ., p_table_name, , ADD PARTITION (PARTITION , v_part_name, VALUES LESS THAN (TO_DAYS(\, DATE_FORMAT(v_next_date, %Y-%m-%d), \))) ); SET dyn_sql v_sql; PREPARE stmt FROM dyn_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; SET v_start_date v_next_date; SET p_advance_days p_advance_days - 1; END WHILE; END$$ DELIMITER ;接下来说说代码里容易被忽略的几个点。首先是两个SIGNAL判断。第一个判断MAXVALUE分区是最容易被网上版本漏掉的。如果表里最后一个分区的边界是MAXVALUE那么MAX(PARTITION_DESCRIPTION)得到的是NULL整个逻辑直接走偏。而且直接执行ADD PARTITION会报MAXVALUE can only be used in last partition definition。所以必须先把这个问题拦下来。第二个判断是v_max_desc IS NULL这种情况说明这张表压根不是分区表或者是一个没有任何初始分区的RANGE分区表直接报错比执行一条奇怪的ALTER TABLE要友好得多。然后是FROM_DAYS函数。PARTITION_DESCRIPTION里存的是TO_DAYS整数FROM_DAYS能把它还原成DATE这个日期就是当前最大分区的不含当天边界。举个例子分区p20250210的边界是TO_DAYS(2025-02-11)那说明p20250210存的是2025-02-10以及之前的数据FROM_DAYS返回2025-02-11。我们从这里加一天得到2025-02-12作为新分区p20250211的边界逻辑正好扣上。最后说说动态SQL的写法。MySQL里PREPARE/EXECUTE要求SQL语句保存在一个用户变量里所以SET dyn_sql v_sql这一步不能省。PREPARE之后必须EXECUTE最后DEALLOCATE PREPARE释放否则短时间大量调用会累积预处理语句。我在代码里每次循环都做了释放这样即使一次要补30个分区也不会把会话资源拖垮。2.3 边界日期和分区名的对应关系千万别搞反加分区有个非常容易出错的细节分区边界和分区名之间的日期错位。VALUES LESS THAN (TO_DAYS(2025-02-11))的数据含义是所有create_time小于2025-02-11的数据也就是最多存到2025-02-10 23:59:59。所以这个分区实际上装的是2025-02-10这一天的数据。我见过有同事把分区名写成p20250211然后对着数据排查了半天总觉得分区和数据对不上。我的习惯是分区名对应数据日期边界值等于数据日期加一天。也就是说存2月10日数据的分区叫p20250210边界写TO_DAYS(2025-02-11)。上面代码里v_start_date是不含边界的那一天分区名用的是v_next_date也就是新边界的前一天正好符合这个习惯。调用方式也很简单。比如订单表orders在mydb库里要往后补齐未来7个分区CALL sp_auto_add_partition(mydb, orders, 7);执行完再查一下最大分区如果边界已经推进到8天后说明成功了。手动验证的SQL我放到第4节统一讲。3. 部署与调度让分区在后台自己长出来3.1 新表初始化与存量表补救自动加分区的前提是表里至少有一个初始分区。对于新建表建表DDL里就要带上一段初始分区比如CREATE TABLE orders ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, order_status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id, create_time) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p20250201 VALUES LESS THAN (TO_DAYS(2025-02-02)), PARTITION p20250202 VALUES LESS THAN (TO_DAYS(2025-02-03)), PARTITION p20250203 VALUES LESS THAN (TO_DAYS(2025-02-04)) );注意这里有个硬性规则如果表上有主键或唯一索引分区键必须是这些索引的一部分否则MySQL会拒绝分区。所以上面我把主键写成了(id, create_time)这个细节在建表时就要想清楚不然后期想改分区特别痛苦。对于已经跑了一段时间的存量分区表补救方式更简单直接多调用几次存储过程。比如现在最大分区只到2025-02-10想一口气补到3月底可以直接CALL sp_auto_add_partition(mydb, orders, 60)过程是幂等的重复执行也不会报错——每个分区在拼接前都会查一次是否存在。3.2 用Event Scheduler做定时调度存储过程写完只是第一步真正让它自动起来要挂在MySQL的事件调度器Event Scheduler上。先确保调度器是开启状态SET GLOBAL event_scheduler ON;如果想永久生效还要在my.cnf的[mysqld]段里加上event_schedulerON否则MySQL重启后又回到OFF状态。然后创建一个每天执行一次的事件比如每天凌晨3点调用存储过程补齐未来7天的分区CREATE EVENT ev_auto_add_partition_daily ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:00:00 ON COMPLETION PRESERVE ENABLE DO CALL sp_auto_add_partition(mydb, orders, 7);为什么定在凌晨3点而不是零点两点考虑。第一零点经常是业务跑批、日切、对账的高峰DDL操作尽量避开第二我们要求分区提前量足够既然提前7天那凌晨1点还是3点执行都无所谓选一个业务最闲的时间窗口就行。事件创建之后用SHOW EVENTS可以查看状态或者查information_schema.EVENTS确认LAST_EXECUTED字段。只要看到LAST_EXECUTED一直在更新时间说明调度链路是通的。还有一个点要提醒创建事件的用户需要有EVENT权限调用存储过程的用户需要有ALTER权限。如果线上账号体系比较严格为这个事件单独建一个运维账号只给最小权限能减少误操作面。3.3 管理几十张分区表配置表加日志表单表用上面那个事件就够了但如果线上有几十张分区表一张表建一个event会管理得很痛苦。我的做法是加一层配置表和日志表把跑哪一个存储过程变成读一份维护清单。先建一张维护配置表CREATE TABLE partition_plan ( id INT PRIMARY KEY AUTO_INCREMENT, table_schema VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, advance_days INT NOT NULL DEFAULT 7, enabled TINYINT NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_schema_table (table_schema, table_name) );再写一个总控存储过程从这张表里读出所有enabled1的记录逐条调用上面那个sp_auto_add_partition。核心逻辑用游标循环就行每次执行结束后把耗时、状态、影响分区数写进一张执行日志表。这样日常维护就变成了一条SQL哪张表要纳入自动管理INSERT一条配置哪张表暂停维护把enabled改成0。不用再为了加一张表去改存储过程代码。分区计划的变更全部数据化也方便做审计。3.4 失败告警的几种实用做法Event调度器不会因为你存储过程报错就给你发消息执行失败它只是记录一下状态。所以监控不能只盯着事件有没有跑一定要盯着分区的结果是否满足业务时间线。最省事的方案是写一个外部巡检脚本每天调一次下面这条SQLSELECT TABLE_SCHEMA, TABLE_NAME, MAX(PARTITION_DESCRIPTION) FROM information_schema.PARTITIONS WHERE PARTITION_DESCRIPTION IS NOT NULL GROUP BY TABLE_SCHEMA, TABLE_NAME HAVING MAX(PARTITION_DESCRIPTION) TO_DAYS(CURRENT_DATE INTERVAL 3 DAY);这条SQL的作用是找出所有最大分区边界还没覆盖到三天后的表。只要返回任意一行就说明这些表的分区有断档风险立即告警。脚本里调用方可以是Zabbix、Prometheus这类现成监控也可以就是一个crontab加curl触发钉钉或企业微信机器人。我踩过太多次凌晨才发现分区没加上的坑现在都是靠这条SQL每天主动巡检效果比单纯看Event状态可靠得多。4. 常见问题与排查技巧实录4.1 已存在MAXVALUE分区怎么救这个问题在存量表改造时特别常见。以前为了图省事有些人建表会在最后加一个VALUES LESS THAN MAXVALUE的分区兜底防止数据没分区可写。但有了MAXVALUE分区之后我们上面的存储过程会直接报错而且即使不依赖存储过程光执行ADD PARTITION也会报错。MySQL规定MAXVALUE只能用在最后一个分区的定义里。解决办法是先通过REORGANIZE把MAXVALUE分区拆开比如把MAXVALUE分区拆成具体日期分区加新的MAXVALUE分区ALTER TABLE orders REORGANIZE PARTITION pmax INTO ( PARTITION p20250210 VALUES LESS THAN (TO_DAYS(2025-02-11)), PARTITION pmax VALUES LESS THAN MAXVALUE );拆完之后MAXVALUE分区前面就有了具体的日期分区之后存储过程再往后面追加分区就不受影响了。这里要提醒一句如果MAXVALUE分区里已经落了数据REORGANIZE时会扫描和重写MAXVALUE分区里的数据数据量大的话这个操作会有点重最好在低峰期执行并且提前确认MAXVALUE里面到底有没有数据、有多少。说句题外话MAXVALUE这种兜底方案我建议尽量别用。日常按天分区只要保证提前量足够根本不需要MAXVALUE来兜底真到兜底了说明自动化已经失守了。而且数据一旦进了MAXVALUE后续想按时间把它们拆到具体分区操作成本和风险都不小。4.2 重复执行、并发执行分区名冲突怎么办自动加分区脚本最常见的重复执行场景有两个一个是DBA手动补分区之后忘了改调度时间凌晨事件又跑了一次另一个是多实例的监控脚本同时调用了存储过程。如果存储过程里没有查重逻辑第二次执行就会报ERROR 1564: Duplicate partition name。我们这个版本已经在每个分区拼接前都查了一次information_schema.PARTITIONS分区存在就跳过所以重复执行是安全的。这也是我把是否存在MAXVALUE判断放在最前面的原因之一——不要等到循环里拼接完SQL才发现问题。并发执行的问题更隐蔽。两个会话同时查了一下发现分区不存在然后都去执行ALTER TABLE还是会有一个失败。不过实际运维中很少出现两个进程同时调度同一个自动化任务的情况只要你把任务统一收敛到一套调度器比如只用一个event或者只用一个外部巡检任务就不会频繁撞车。真要在高可用架构里做双机调度我建议在配置表里加一个task_lock字段用GET_LOCK或者UPDATE ... WHERE task_lock0这类方式保证同一时刻只有一个执行者。4.3 时区导致的日期边界偏移时区问题在分区表场景里比想象中更容易踩。TO_DAYS()函数会受MySQL系统时区影响如果服务器的time_zone设置和业务约定的北京时间不一致同样的字符串日期经过TO_DAYS转换出来的整数可能就差了一天。我处理这个问题有个原则对分区而言边界的基准要明确。如果业务表的create_time存的就是北京时间那Event里最好显式SET time_zone 08:00让整个会话统一到业务时区。如果公司服务器的系统时区是UTC而你的分区脚本里用了NOW()或CURDATE()来产生起始边界不加处理就会出现该加2月11日的分区结果加成了2月10日这种错位。好在我们这套存储过程的起始边界是来自已有分区的PARTITION_DESCRIPTION不是取当前系统时间所以时区对它的影响相对小。但如果你在外部巡检脚本里用CURRENT_DATE做判断就一定要统一时区。最简单粗暴的办法所有涉及日期比较的地方显式拼接2025-02-11这种字符串而不是依赖NOW()从源头消除歧义。4.4 ALTER TABLE ADD PARTITION到底锁不锁表很多DBA一听ALTER TABLE就紧张觉得会不会锁表锁半天。对InnoDB的RANGE分区表来说ADD PARTITION本质上只是修改表的分区元数据速度非常快通常毫秒级到秒级就能完成因为它不做数据搬运。但要注意它仍然需要持有表的元数据锁MDL。如果在执行ADD PARTITION的时候正好有一个长事务或者长查询持有这张表的MDL加分区的操作会排队等待而排在它后面的写请求也会一起被堵住。所以我的调度原则是把加分区的时间窗口放在业务低峰期同时把提前量给足不要让加分区变成紧急事件。提前7天和提前3天对业务容错来说完全不是一个量级的风险。另外REORGANIZE PARTITION这种操作和ADD PARTITION完全不是一回事它涉及分区数据扫描和移动遇到大分区一定要单独规划维护窗口不能混进自动加分区的流程里。4.5 常用验证SQL和巡检方法最后把我日常用的几条验证SQL整理出来顺便做成一个速查表方便大家复制。第一是查某张表最近几个分区的情况SELECT PARTITION_NAME, PARTITION_DESCRIPTION, FROM_DAYS(PARTITION_DESCRIPTION) AS boundary_date FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA mydb AND TABLE_NAME orders ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 5;第二是全局巡检所有分区表找分区断档SELECT TABLE_SCHEMA, TABLE_NAME, MAX(PARTITION_DESCRIPTION) FROM information_schema.PARTITIONS WHERE PARTITION_DESCRIPTION IS NOT NULL GROUP BY TABLE_SCHEMA, TABLE_NAME HAVING MAX(PARTITION_DESCRIPTION) TO_DAYS(CURRENT_DATE INTERVAL 3 DAY);第三是查看Event调度器是否正常执行查LAST_EXECUTED和STATUSSELECT EVENT_NAME, STATUS, LAST_EXECUTED, LAST_ALTERED FROM information_schema.EVENTS WHERE EVENT_SCHEMA mydb;把这些归纳成下表对照排查现象可能原因处理方式ADD PARTITION报MAXVALUE错误表结构里最后一个分区是MAXVALUE先REORGANIZE拆分MAXVALUE拆完再跑报Duplicate partition name分区已存在重复执行存储过程里加存在性判断已存在则跳过凌晨没有新分区出现Event没开、账号权限不足、存储过程报错检查event_scheduler、SHOW EVENTS、查看错误日志分区边界整体偏一天时区不一致统一time_zone尽量用字符串日期加分区时业务写阻塞MDL锁排队提前量给足调度放在低峰期我个人在实际操作中的体会是自动加分区这件事代码本身不难难的是把边界条件想清楚。时区、MAXVALUE、重复执行、事件调度失效这些零碎问题才是真正让你凌晨起床的元凶。最后再分享一个小习惯我不管自动化脚本跑得多稳每天还是会用上面那条全局巡检SQL扫一遍分区覆盖情况发现今天3天之内没有分区覆盖就直接告警。多一道旁路监控心里踏实很多。这套存储过程思路够覆盖大部分按天分区表的维护场景了希望能帮你把凌晨的时间留给自己。
