从进公司第一天接手线上日志库开始我就被“月底怎么切分区”这件事折磨过。那时候业务表一天涨几百万行靠着分区表把单表拆成了按月分片可每到月底就得手工执行一遍ALTER TABLE加新分区、删旧分区。白天业务高峰不敢动只能半夜爬起来操作稍不留神就会因为漏建分区导致第二天的数据写不进去。后来我把这套动作做成了“分区自动增删管理”——用MySQL自带的事件调度器驱动存储过程每天凌晨自动检查、自动补齐未来分区、自动回收过期分区。从此我再也没在凌晨被报警电话叫醒过。这篇文章就以“MySQL分区表—自动增删管理分区”为题把整套方案、建表设计、存储过程代码、定时事件配置、上线后的排错经验一次性讲透。适合正在维护大表、被分区手工管理困扰的DBA和后台开发也适合准备给业务表引入分区机制、希望少走弯路的人。1. 分区表到底解决什么问题为什么必须“自动”1.1 什么时候该用分区表而不是分库分表先聊清楚分区表的定位。分区表本质上是一张逻辑上的大表底层被拆成了多个物理存储的分区查询时优化器会根据条件做“分区裁剪”只扫必要的那几个分区。最典型的场景就是日志表、流水表、订单历史表这类数据随时间持续增长、旧数据又基本不会被改动的表。我的判断标准很简单单表数据量超过千万级并且有明显的日期维度查询基本都带上这个日期范围才会考虑分区。如果一张表只有几百万行、查询条件里又没有日期字段分区带来的收益很有限反而增加了建表和维护的复杂度。至于“和分库分表怎么选”——两者并不冲突。分库分表解决的是单机容量和写入并发的问题分区表解决的是单表内部数据组织和管理效率的问题。很多系统是“先分区、后分库”在单库单表内部先用分区把数据横向切开真到了单机扛不住的时候再上中间件做库级拆分。1.2 手工管理分区的痛点做过的都懂没做自动化之前我的常规操作是每个月月底先手工计算当前最大分区的边界再执行一条ALTER TABLE ADD PARTITION把下个月的分区建出来隔一段时间还得手动删掉几个月前的历史分区释放空间。这套流程的问题很明显。第一操作窗口窄。加分区本质上是DDL虽然不像老版本那样全程锁表但线上大表在业务高峰执行总归有风险只能挑凌晨。第二容易遗漏。一个人管十几张分区表每张表的保留周期还不一样不可能全靠脑子记。只要漏了一张表的分区等月初数据写入时发现“Table has no partition for value”这种错误后果就是业务直接被挡住。第三不好回滚。DDL不像DML出错了很难快速“撤销”现场的紧张感很强。所以自动化不是为了炫技是刚需。把“什么时候加、什么时候删、保留多久”变成一组规则交给数据库自己周期执行比任何人力记忆都可靠。1.3 自动增删管理方案的选型思路MySQL自带的方案有两条路线一条是用事件调度器Event Scheduler配合存储过程全在数据库内部解决另一条是在外部用脚本比如通过系统crontab定时跑一堆SQL。我最终选的是前者。原因很简单事件调度器本身是MySQL内核能力不依赖额外部署只要实例活着就会按计划执行而外部脚本需要额外维护一台执行机还要考虑脚本连库权限、失败告警、执行日志等问题。存储过程的好处是逻辑可以写在数据库里和表结构待在一起迁移、复盘时一眼能看到全部规则。配合系统告警如果某天事件执行失败我们在监控上配置一张校验表的分区数量有异常就报警整个链路就闭环了。2. 建表与分区设计一上来就决定成败的细节2.1 用RANGE COLUMNS按日期分区自动增删管理要落地前提是分区设计足够规整。我的习惯是直接使用RANGE COLUMNS分区并且分区键用日期字段本身而不是用TO_DAYS之类的函数表达式。很多老教程喜欢写PARTITION BY RANGE (TO_DAYS(created_at))这在早期版本可以用但分区键一旦带上函数后续约束限制会变多可读性也差。RANGE COLUMNS可以直接基于datetime类型列做范围分区LESS THAN的边界值直接写日期字符串简单直观。比如按月分区边界就是每个月的1号CREATE TABLE order_log ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (id, created_at), KEY idx_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 PARTITION BY RANGE COLUMNS (created_at) ( PARTITION p202307 VALUES LESS THAN (2023-08-01), PARTITION p202308 VALUES LESS THAN (2023-09-01), PARTITION p202309 VALUES LESS THAN (2023-10-01), PARTITION p202310 VALUES LESS THAN (2023-11-01), PARTITION p202311 VALUES LESS THAN (2023-12-01), PARTITION p202312 VALUES LESS THAN (2024-01-01) );注意这里建了6个月的分区不是只建当前月一个。因为自动加分区也是需要时间的万一调度器临时故障多留几个未来分区就多一层缓冲。初始手工建分区时我一般会覆盖当前月加未来两到三个月。分区边界约定要记牢命名p202307实际含义是“存放2023年7月的分区”它的边界是2023-08-01也就是7月1号含到8月1号不含的数据都进这个分区。这个约定后面写自动增删存储过程时非常关键分区名、边界、实际数据范围三者必须严格对应。2.2 主键和唯一键必须包含分区键这是新手最容易踩的坑。MySQL分区表有一条硬性规则表上的每个唯一键包括主键必须包含分区表的所有分区列。如果业务想继续用id做主键同时又要按created_at分区必须把分区键加进主键比如写成PRIMARY KEY (id, created_at)。这样确实改变了主键语义但对大部分流水类表并非不能接受因为业务查询的单行主键查找通常还是会带上id再加上created_at做二次限定也没问题。如果你在设计上完全无法接受改主键那就只能放弃按这个字段分区另想办法。同理表上如果有其他唯一索引比如uk_order_no它也必须要包含created_at列否则建表会直接报错。理解这条规则背后的原因并不难唯一性校验必须在单个分区内完成如果唯一索引不包含分区键相同值的行可能分散在不同分区数据库就无法保证全局唯一。提示这个限制是声明式的。改分区结构前先看下现有表上有哪些唯一键推演一下改动方案再动手别等建表时报错才去返工。2.3 已有表改为分区表的迁移方式如果是一张线上的存量表想改成分区表最稳妥的是“先建新表、导数据、再切换”而不是直接在原表上执行ALTER TABLE分区。尤其是数据量已经上亿后在原表上做分区重建会长时间锁写风险很大。我常用的流程是先按统一规范建好带分区的表结构再用INSERT INTO ... SELECT ...分批把历史数据导入导入时注意保持写入时间顺序和分区边界一致。数据导完后短暂停写做一次rename切换把新表替换为原表名。若公司有在线DDL工具也可以用它来做分区操作但分区结构本身比较复杂我建议还是先在测试环境把流程跑通再上生产。3. 自动增删分区的核心实现3.1 事件调度器的开关与权限自动化的执行引擎是MySQL事件调度器首先要确保它是开启状态并且配置被持久化到配置文件里。光在会话里执行一次SET GLOBAL event_scheduler ON数据库重启后会失效。-- 查看状态 SHOW VARIABLES LIKE event_scheduler; -- 临时开启 SET GLOBAL event_scheduler ON;在my.cnf的[mysqld]段落里加上一行并重启或者直接写入配置文件中等待后续重启生效[mysqld] event-schedulerON注意账户权限。除了ALTER权限执行用户还需要EVENT权限。如果权限不够建事件时会报权限错误。建议单独建一个管理账号只授权给它分区管理相关的权限不要用超级账号裸奔。GRANT EVENT, ALTER ON db_order.* TO partition_admin%;3.2 自动创建未来分区的存储过程加分区的基本逻辑是先查出当前表的最大分区月份计算目标需要的最大分区月份如果还缺就按月逐个补齐。我按分区名解析月份约定分区名统一为pYYYYMM这样取最大分区时直接读information_schema即可DELIMITER $$ CREATE PROCEDURE sp_auto_add_partitions(IN p_keep_future INT) BEGIN DECLARE v_max_month DATE; DECLARE v_target_month DATE; DECLARE v_missing INT DEFAULT 0; DECLARE v_partition_date DATE; DECLARE v_next_boundary DATE; DECLARE v_i INT DEFAULT 0; DECLARE v_sql VARCHAR(2000); SELECT MAX(STR_TO_DATE(SUBSTRING(partition_name, 2), %Y%m)) INTO v_max_month FROM information_schema.PARTITIONS WHERE table_schema DATABASE() AND table_name order_log AND partition_name REGEXP ^p[0-9]{6}$; SET v_target_month DATE_ADD(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL p_keep_future MONTH); SET v_missing PERIOD_DIFF(DATE_FORMAT(v_target_month, %Y%m), DATE_FORMAT(v_max_month, %Y%m)); IF v_missing 0 THEN SET v_partition_date DATE_ADD(v_max_month, INTERVAL 1 MONTH); WHILE v_i v_missing DO SET v_next_boundary DATE_ADD(v_partition_date, INTERVAL 1 MONTH); SET v_sql CONCAT( ALTER TABLE order_log ADD PARTITION (PARTITION p, DATE_FORMAT(v_partition_date, %Y%m), VALUES LESS THAN (, DATE_FORMAT(v_next_boundary, %Y-%m-%d), )) ); SET ddl v_sql; PREPARE stmt FROM ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_partition_date v_next_boundary; SET v_i v_i 1; END WHILE; END IF; END$$ DELIMITER ;调用方式很简单CALL sp_auto_add_partitions(3)表示始终让未来3个月的分区都存在。比如当前是5月最大分区到5月那就会补齐6月、7月、8月三个分区。这里有几个细节值得说明。第一为什么用PERIOD_DIFF而不是自己数月份因为它处理跨年计算很方便格式是YYYYMM非常稳定。第二动态SQL里的单引号要写成两个连续单引号这是拼接SQL时最烦人的地方后面排错部分我会专门展开。第三如果某天数据里出现了未来时间的记录比如有人写了一条明年6月的数据而最大分区只到今年8月INSERT就会报错。针对这个问题一是把预设分区跨度适当拉大二是推动接入层对写入时间做合法性校验。3.3 自动删除过期分区的存储过程删分区的逻辑比加分区更敏感因为删错了就是数据丢失。我的策略是“先比日期只删过期分区”每个分区的数据区间是[分区月初, 下月1日)只要分区的最终边界早于“当前时间减保留天数”这个截止日期这个分区就可以安全删除。DELIMITER $$ CREATE PROCEDURE sp_auto_drop_partitions(IN p_keep_days INT) BEGIN DECLARE v_partition_name VARCHAR(64); DECLARE v_partition_start DATE; DECLARE v_deadline DATE; DECLARE v_done INT DEFAULT 0; DECLARE v_count INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT partition_name, STR_TO_DATE(SUBSTRING(partition_name, 2), %Y%m) AS p_start FROM information_schema.PARTITIONS WHERE table_schema DATABASE() AND table_name order_log AND partition_name REGEXP ^p[0-9]{6}$; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; SELECT COUNT(*) INTO v_count FROM information_schema.PARTITIONS WHERE table_schema DATABASE() AND table_name order_log AND partition_name REGEXP ^p[0-9]{6}$; IF v_count 1 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT partition count too low, skip drop; END IF; SET v_deadline DATE_SUB(CURDATE(), INTERVAL p_keep_days DAY); OPEN cur; loop_label: LOOP FETCH cur INTO v_partition_name, v_partition_start; IF v_done THEN LEAVE loop_label; END IF; IF DATE_ADD(v_partition_start, INTERVAL 1 MONTH) v_deadline THEN SET ddl CONCAT(ALTER TABLE order_log DROP PARTITION , v_partition_name); PREPARE stmt FROM ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END LOOP; CLOSE cur; END$$ DELIMITER ;每次DROP单独走一条ALTER不要让一次ALTER同时删多个分区。原因是大表DROP分区会涉及物理数据页的清理单个分区删除的时间更可控万一中间失败也方便定位到底哪一步卡住了。安全阀一定要有如果整张表只剩一个分区禁止删除。别笑真有配置错误的时候比如保留天数被人为改得过短跟分区实际覆盖月份错位就可能触发这种极端情况。提示删除分区前如果业务有归档需求需要先把分区数据导到归档库再删。这里我简化处理为直接DROP生产环境建议在DROP前加一步“统计并备份”逻辑比如把要删的分区数据先导出到归档表。3.4 定义定时事件让一切自动化加分区和删分区两个存储过程就绪后把它们挂到一个定时事件里。我选择每天凌晨2点半执行一次既避开了晚间业务高峰又保证了不管当天有没有人值班早晨起来时分区已经是准备好的状态。DELIMITER $$ CREATE EVENT ev_order_log_partition_auto ON SCHEDULE EVERY 1 DAY STARTS 2024-01-01 02:30:00 ON COMPLETION PRESERVE ENABLE DO BEGIN CALL sp_auto_add_partitions(3); CALL sp_auto_drop_partitions(60); END$$ DELIMITER ;关于保留期这里稍微展开讲一下。比如p_keep_days60我的理解是“允许数据保留60天”。由于分区是按自然月切的实际删除时只能整月删所以与你预期的精确天数会有少量误差。比如今天是5月10日deadline是3月11日那么3月及更早的分区会被删掉4月的分区虽然部分数据已经超过60天但依然被保留。这种按自然月对齐的误差异常是可以接受的也更容易向业务解释。3.5 手动验证与安全演练在正式启用事件之前建议手动把两个存储过程各执行一遍确认分区数量和边界符合预期。我最常用的检查SQLSELECT partition_name, partition_description, table_rows FROM information_schema.PARTITIONS WHERE table_schema DATABASE() AND table_name order_log ORDER BY partition_name ASC;观察输出确认未来分区已经补齐、过期分区已经被删除。接着再跑一次事件确认重复执行不会产生重复分区。这个幂等性很重要因为事件调度器偶尔会因为实例重启等原因重复执行同一批任务如果存储过程没有做“存在判断”就可能建出重复分区报错。4. 上线后的常见问题与排查技巧实录4.1 事件不执行先查调度器、事件状态和权限自动化上线后第一次没执行成功大家用的排错顺序基本是一致的。先看调度器开关是不是ON再看事件是否存在、状态是否为ENABLED最后看执行记录有没有报错日志。SHOW VARIABLES LIKE event_scheduler; SHOW EVENTS\G;从库上还有一个特别容易踩的坑事件状态显示SLAVESIDE_DISABLED。这是MySQL为了保证主从一致性默认会在从库禁用事件。所以不要让主库和从库各自跑一个事件正确做法是只在主库创建事件让操作通过binlog同步到从库如果从库也配置了事件它会被禁用掉这也是符合预期的。权限类的报错常见于ERROR 1545说明当前用户没有EVENT权限。用前面提到的管理账号去创建事件就不会有这个问题。4.2 动态SQL拼接的引号坑写存储过程动态SQL时最让人抓狂的是拼接字符串里的单引号。举个例子SET v_sql CONCAT( ALTER TABLE order_log ADD PARTITION (PARTITION p, DATE_FORMAT(v_partition_date, %Y%m), VALUES LESS THAN (, DATE_FORMAT(v_next_boundary, %Y-%m-%d), )) );目标SQL是这样的ALTER TABLE order_log ADD PARTITION (PARTITION p202408 VALUES LESS THAN (2024-09-01))所以在VALUES LESS THAN (后面要先写一个单引号这个单引号在SQL字符串里要写成而日期字符串结束后还得再写一个单引号同样要写成。我刚开始写的时候经常数错引号对数后来养成习惯先手工拼出目标SQL再逐个替换单引号最后再贴进存储过程里。排查时如果报语法错误第一反应先检查动态SQL里拼接出来的字符串长什么样。可以先用一个简单的SELECT v_sql打印出来看或者把v_sql写到日志表里方便复盘。4.3 大表DDL的锁行为与主从延迟虽然MySQL 5.6以后ADD PARTITION和DROP PARTITION都是在线DDL不要求全程锁表但不要把“在线”理解为“无感”。对大表执行DROP分区时物理删除数据页会产生IO压力对主库性能有影响同时会给从库带来复制延迟。我的经验是分区管理任务尽量固定在凌晨执行如果表特别大比如单分区数据量超过50GB不要一个事件里连续删好几个分区可以把每次任务的删除上限设成1到2个分区让IO压力平摊到多个晚上。自动化的理想状态是“无人值守”但前提是任务峰值不会冲破系统冗余。4.4 分区边界与数据日期不匹配有一次上线后业务反映某个写入接口报了“Table has no partition for value”。查下来是一笔测试数据把created_at写到了下个月而当前分区只预建到了未来2个月边界没覆盖到。解决思路有两条。第一把预创建分区月份从2个月扩大到3到6个月给调度故障留出冗余第二在接入层对写入数据的时间字段做范围校验拒绝极端异常时间。另外还要警惕时区问题如果程序用UTC时间存库而分区边界按北京时间凌晨切那每天会有8小时的写入落在“前一天”的分区里这在跨月时尤其容易造成混淆。分区管理的日期口径必须和业务口径完全一致这是我在项目启动时就反复强调的。4.5 分区命名和保留策略的“约定大于配置”排错排到最后你会发现大多数问题出在命名和策略没有标准化。分区表的管理一旦铺开到多张表每张表的分区命名、保留天数、预创建个数最好沿用同一套规范。我自己的约定如下项目约定值分区粒度自然月分区命名pYYYYMM边界策略LESS THAN 下月1日 00:00:00预创建未来分区数3个月默认保留天数60天调度时间每天 02:30这套规范写进团队文档后大家处理新表时直接照做省掉了大量沟通成本。自动化的前提是标准化没有统一规范脚本写得再溜也是给自己挖坑。5. 影响范围与适用场景分析分区自动增删管理并不是银弹它的影响范围要提前评估清楚。对写入端表一旦做了分区所有INSERT都必须落在已有分区的边界内否则会直接失败。所以接入层要清楚分区边界和当前时间的关系比如预创建未来分区不是无限多写未来三个月以后的数据仍会报错。对查询端只有查询条件包含分区键时分区裁剪才有意义。如果业务经常不按时间条件查询分区表会变成“看起来拆开了、实际全扫一遍”这时候分区收益非常有限。要让分区表发挥价值查询SQL必须把created_at范围条件带上至少要能给优化器一个精确的边界。对运维端影响体现在三个方面备份恢复、主从复制、监控告警。分区表备份时要考虑按分区备份的策略从库不能重复执行事件监控上必须增加“分区数量异常”和“最大分区边界滞后”两类指标。我已经不止一次靠分区数量检查告警及时发现漏加分区的问题这个监控比任何人工巡检都可靠。另外还有一些场景我明确不建议用分区表。比如单表数据量还没到千万级分区裁剪的意义不大表上有多个复杂唯一约束硬加分区键会导致索引设计非常别扭分区键是会被频繁更新的字段因为更新触发跨分区移动数据代价很高项目使用的某些迁移或中间件工具不支持分区表上线后被卡脖子。这些情况更适合先做冷热数据分离或归档表把旧数据挪走比分区方案更简单有效。6. 测试环境验证步骤与上线清单最后把整个落地过程整理成一份可复用的上线清单方便直接照做。先用业务真实表的一个小样本在测试环境建出分区表把主键、唯一键、索引都按生产结构做一遍。创建两个存储过程手动调用加分区、删分区观察分区名、边界和保留天数是否符合预期。创建定时事件但先把调度时间设为1分钟后的一个临时时间点验证事件能正常触发执行。查看SHOW EVENTS确认事件状态为ENABLED观察实例错误日志和主从状态。连续两三天观察事件是否在指定时间成功执行确认分区数量和最大分区边界稳定。上生产前把保留天数、预创建分区数、执行时间等参数和团队确认一遍并补充分区数量监控告警。最后再对线上已有的一张待分区表进行迁移演练确认导入数据没有落入未覆盖分区。我在测试环境还做过一个极限实验把事件调度器停掉一周模拟调度故障然后手工执行一次存储过程观察它能否一次性把缺失的分区全部补回来。结果是能。所以即使自动化链路中途故障了只要我们及时发现并手工触发一次系统就能自动恢复“未来3个月分区都在”的目标状态。这就是把分区管理规则化的好处——人只需要保证调度入口活着剩下的交给规则本身。我个人在实际操作中最深的体会是分区自动增删的价值不在于替你省掉几条ALTER语句而在于把“月底切分区”这种高风险操作从“靠人盯”变成“靠规则”。只要分区命名统一、边界约定明确、保留策略固定存储过程和事件调度器就是最可靠的执行者。即便某天真出了岔子你查看分区现状、手工补一次任务也比以前月底熬夜改十几张表要从容得多。