1. 项目背景业务场景某快递公司的运单跟踪系统每天产生 2000 万条物流轨迹记录核心表tracking_log在半年内增长到 36 亿行占用磁盘 2TB。日常查询——“某运单号的最新 10 条轨迹”——在索引帮助下只需 2ms。但运营后台的查询 3 个月前某城市所有已签收运单需要扫描 30 亿行——耗时 20 分钟。同时DBA 每月需要手动归档半年前的旧数据——每次执行DELETE FROM tracking_log WHERE created_at 6个月前——需要分批删除 30 亿行中的 60 亿行跨越多个时间窗口整个过程耗时 3 天期间数据库 IO 打到 100%——业务频繁超时。痛点大表不分区、不归档后果是灾难性的历史查询拖垮核心业务索引高度随行数增长——36 亿行的 BTree 高度已达 6-7 层——即使走索引也需要 6-7 次磁盘 IO。DELETE 即灾难大表删除数据不是回收空间——是标记删除 产生大量 undo log 脏页刷新风暴——相当于对整表做了一次全表扫描级别的 IO 操作。备份窗口不可控每天全量备份 2TB 需要 6 小时——备份窗口超过了业务低谷时间——备份还没完成早高峰就来了。统计信息频繁失真每天新增 2000 万行——ANALYZE TABLE的采样页只覆盖了最近插入的一小部分——旧数据的统计完全不准。本章教你用 RANGE 分区按时间拆分大表、用分区裁剪加速查询、用TRUNCATE PARTITION秒级归档历史数据、并设计冷热数据分离策略。2. 项目设计【场景DBA 连续三天加班做数据归档小胖看着他把 DELETE 语句分批次跑】小胖“大师为什么删数据这么慢DELETE FROM tracking_log WHERE created_at 2026-01-01不应该很快吗又不是全表删除。”大师“因为 MySQL 的 DELETE 并不是’撕掉这一页’——它是’在每一行上写一个删除标记’。你删 1 亿行——InnoDB 需要找到这 1 亿行通过索引或全表扫描——逐行标记删除——生成 1 亿条 undo log——标记 1 亿行所在的数据页为脏页——然后慢悠悠地刷这些脏页到磁盘。整个过程就是一次全表扫描 1 亿次写入。”小白“那分区表怎么解决这个问题”大师“分区表把一张逻辑表拆成多个物理分区——比如按月分区——每个分区就是一个独立的小表。当你执行SELECT * FROM tracking_log WHERE created_at BETWEEN 2026-03-01 AND 2026-03-31时——优化器通过分区裁剪只扫描 3 月这一个分区30 亿行 → 2 亿行——查询量减少为原来的 1/15。当你需要归档 6 个月前的数据时——不需要DELETE——直接ALTER TABLE tracking_log TRUNCATE PARTITION p202601——瞬间完成——因为 MySQL 只是把整个分区文件删掉了。”技术映射分区裁剪 优化器根据 WHERE 条件中的分区键只扫描相关的分区——跳过不相关分区的全量 IO。TRUNCATE PARTITION DDL 操作瞬间删除整个分区——比 DELETE 快 1000 倍。小胖“那 RANGE、LIST、HASH、KEY 四种分区类型怎么选”大师RANGE按时间、数值范围分区如created_at按月、id按 1000 万一个范围。你的运单轨迹表就是最适合 RANGE 分区的场景。LIST按离散值分区如region按华北、华东、华南分区——适合地区隔离。HASH按哈希值均匀分布——适合没有明显范围特征的场景如user_id取模——但无法做范围查询裁剪。KEY类似 HASH但由 MySQL 内置函数计算——适合主键分布均匀的表。小白“分区表有什么坑吗我听说会锁表”大师“最大的坑是——分区操作ADD、DROP、TRUNCATE PARTITION本质是 DDL——需要获取 MDL 排他锁。如果你的表正在被查询或更新——DDL 就会排队等候——等到所有查询结束才执行。所以在生产中——虽然 DROP PARTITION 本身是秒级操作——但排队等待 MDL 锁可能是分钟级甚至小时级。解决方法是——在业务低峰期做——或者用 pt-online-schema-change 等工具辅助。”3. 项目实战3.1 环境准备-- 确认分区支持SHOWPLUGINS;-- 应看到 partition | ACTIVE | STORAGE ENGINE-- 确认当前不支持分区的表引擎MyISAM 支持但生产不用3.2 分步实现步骤一创建 RANGE 分区表——按月份分区-- 步骤目标将 orders_large 表改造为按月份 RANGE 分区USEecommerce;-- 原始 orders_large 表有 100 万行先看看它的大小SELECTTABLE_NAME,ROUND(DATA_LENGTH/1024/1024,2)ASdata_mb,TABLE_ROWSFROMinformation_schema.TABLESWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_large;-- 创建按月份分区的版本DROPTABLEIFEXISTSorders_partitioned;CREATETABLEorders_partitioned(idBIGINTUNSIGNEDAUTO_INCREMENT,user_idBIGINTUNSIGNEDNOTNULL,product_idBIGINTUNSIGNEDNOTNULL,amountDECIMAL(10,2)NOTNULL,statusTINYINTNOTNULLDEFAULT0,created_atDATETIME(3)NOTNULL,PRIMARYKEY(id,created_at),-- 分区键必须包含在主键中INDEXidx_user(user_id),INDEXidx_status_created(status,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp202501VALUESLESS THAN(TO_DAYS(2025-02-01)),PARTITIONp202502VALUESLESS THAN(TO_DAYS(2025-03-01)),PARTITIONp202503VALUESLESS THAN(TO_DAYS(2025-04-01)),PARTITIONp202504VALUESLESS THAN(TO_DAYS(2025-05-01)),PARTITIONp202505VALUESLESS THAN(TO_DAYS(2025-06-01)),PARTITIONp202506VALUESLESS THAN(TO_DAYS(2025-07-01)),PARTITIONp202507VALUESLESS THAN(TO_DAYS(2025-08-01)),PARTITIONp202601VALUESLESS THAN(TO_DAYS(2026-02-01)),PARTITIONp202602VALUESLESS THAN(TO_DAYS(2026-03-01)),PARTITIONp202603VALUESLESS THAN(TO_DAYS(2026-04-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE-- 兜底分区);-- 从原始表导入数据INSERTINTOorders_partitionedSELECT*FROMorders_large;-- 查看各分区的数据分布SELECTPARTITION_NAME,TABLE_ROWS,ROUND(DATA_LENGTH/1024/1024,2)ASdata_mbFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedORDERBYPARTITION_NAME;-- 验证总数SELECTCOUNT(*)FROMorders_partitioned;-- 应与 orders_large 行数一致步骤二验证分区裁剪——对比有/无分区的查询性能-- 步骤目标用 EXPLAIN 观察分区裁剪是否生效-- 有分区的查询分区裁剪生效EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_atBETWEEN2025-03-01AND2025-03-31;-- partitions 列仅显示 p202503而不是 ALL-- → 只扫描一个分区-- 对比无分区的查询全表扫描EXPLAINSELECTCOUNT(*)FROMorders_largeWHEREcreated_atBETWEEN2025-03-01AND2025-03-31;-- partitions 列NULL不分区的表-- 分区裁剪不生效的反例 EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREYEAR(created_at)2025;-- partitions 列ALL所有分区-- 因为用了函数 YEAR() —— 优化器无法推导分区边界-- 正确的写法EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_at2025-01-01ANDcreated_at2026-01-01;-- partitions 列p202501~p202601裁剪成功步骤三TRUNCATE PARTITION——秒级归档历史数据-- 步骤目标演示 TRUNCATE PARTITION 比 DELETE 快多少-- 1. 先看某个分区有多少数据SELECTPARTITION_NAME,TABLE_ROWSFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedANDPARTITION_NAMEp202501;-- 2. 传统 DELETE 方式慢-- DELETE FROM orders_large WHERE created_at 2025-02-01;-- 这将扫描全表 产生大量 undo 逐行标记删除-- 3. TRUNCATE PARTITION秒级ALTERTABLEorders_partitionedTRUNCATEPARTITIONp202501;-- 验证p202501 的数据已被清空SELECTPARTITION_NAME,TABLE_ROWSFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedANDPARTITION_NAMEp202501;-- TABLE_ROWS 0-- 验证总数SELECTCOUNT(*)FROMorders_partitioned;-- 应减少了 p202501 的行数-- 注意TRUNCATE PARTITION 后p202501 分区仍然存在只是内容空了-- 如果不需要这个分区了——可以 DROP PARTITION-- ALTER TABLE orders_partitioned DROP PARTITION p202501;步骤四自动创建未来分区——用 EVENT 调度器-- 步骤目标自动创建下个月的分区避免数据插入到 p_futureMAXVALUE 分区-- 手动创建一个下个月的分区示例2026年5月ALTERTABLEorders_partitioned REORGANIZEPARTITIONp_futureINTO(PARTITIONp202604VALUESLESS THAN(TO_DAYS(2026-05-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE);-- 创建自动添加分区的存储过程DELIMITER//CREATEPROCEDUREadd_monthly_partition(INtable_nameVARCHAR(64),INnext_monthVARCHAR(7))BEGINDECLAREpartition_nameVARCHAR(20);DECLAREboundary_dateDATE;DECLAREsql_stmtTEXT;SETpartition_nameCONCAT(p,REPLACE(next_month,-,));SETboundary_dateDATE_ADD(CONCAT(next_month,-01),INTERVAL1MONTH);SETsql_stmtCONCAT(ALTER TABLE ,table_name, REORGANIZE PARTITION p_future INTO (, PARTITION ,partition_name, VALUES LESS THAN (TO_DAYS(\,boundary_date,\)),, PARTITION p_future VALUES LESS THAN MAXVALUE,));PREPAREstmtFROMsql_stmt;EXECUTEstmt;DEALLOCATEPREPAREstmt;END//DELIMITER;-- 测试添加 2026 年 7 月的分区CALLadd_monthly_partition(orders_partitioned,2026-07);-- 验证新分区已创建SELECTPARTITION_NAME,PARTITION_DESCRIPTIONFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedORDERBYPARTITION_ORDINAL_POSITION;-- 创建 EVENT 每月自动执行CREATEEVENT auto_add_partitionONSCHEDULE EVERY1MONTHSTARTS2026-06-01 00:00:00DOCALLadd_monthly_partition(orders_partitioned,DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL1MONTH),%Y-%m));步骤五冷热数据分离——归档到独立表/独立实例-- 步骤目标将 3 个月前的数据迁移到归档表减少主表体积-- 方案 A同库归档表简单CREATETABLEorders_archive(idBIGINTUNSIGNEDNOTNULL,user_idBIGINTUNSIGNEDNOTNULL,product_idBIGINTUNSIGNEDNOTNULL,amountDECIMAL(10,2)NOTNULL,statusTINYINTNOTNULLDEFAULT0,created_atDATETIME(3)NOTNULL,PRIMARYKEY(id,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp_oldVALUESLESS THAN(TO_DAYS(2025-01-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE);-- 将 3 个月前的数据迁移到归档表分批迁移-- 每次迁移 10000 行防止大事务DELIMITER//CREATEPROCEDUREarchive_old_orders(INbefore_dateDATE)BEGINDECLAREdoneINTDEFAULTFALSE;DECLAREbatch_sizeINTDEFAULT10000;DECLAREaffected_rowsINTDEFAULT1;WHILEaffected_rows0DOSTARTTRANSACTION;INSERTINTOorders_archiveSELECT*FROMorders_largeWHEREcreated_atbefore_dateLIMITbatch_size;SETaffected_rowsROW_COUNT();DELETEFROMorders_largeWHEREcreated_atbefore_dateLIMITbatch_size;COMMIT;-- 每批之间短暂休眠——减少 IO 压力DOSLEEP(1);ENDWHILE;END//DELIMITER;-- 执行归档在业务低峰期-- CALL archive_old_orders(2025-04-01);-- 方案 B归档到独立 MySQL 实例节省主库资源-- 配置一个低配的归档 MySQL 实例-- 用 mysqldump 或 SELECT INTO OUTFILE LOAD DATA INFILE 迁移数据-- 主库只保留近 3 个月的热数据3.3 测试验证-- 验证清单-- 1. 确认分区裁剪生效EXPLAINPARTITIONSSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_atBETWEEN2025-06-01AND2025-06-30;-- 预期partitions 列显示单个分区名不含 ALL-- 2. 确认各分区行数总和等于总数SELECT(SELECTSUM(TABLE_ROWS)FROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitioned)ASpartition_sum,(SELECTCOUNT(*)FROMorders_partitioned)ASactual_count;-- 两个数值应接近TABLE_ROWS 是估算值可能有偏差-- 3. 验证 TRUNCATE PARTITION 的效果-- 执行前后对比-- ALTER TABLE orders_partitioned TRUNCATE PARTITION p202501;-- SELECT COUNT(*) FROM orders_partitioned; -- 应减少-- 4. 验证 EVENT 调度器正常工作SELECTEVENT_NAME,STATUS,LAST_EXECUTEDFROMinformation_schema.EVENTSWHEREEVENT_NAMEauto_add_partition;4. 项目总结优点 缺点维度优点缺点/局限RANGE 分区查询直击目标分区——性能提升 10-100 倍TRUNCATE PARTITION 秒级归档分区键必须包含在主键和唯一键中——可能改变表设计分区裁剪自动由优化器完成无需修改 SQL使用函数包裹分区键会导致裁剪失效自动分区EVENT 调度器免手动维护EVENT 依赖 MySQL 服务运行——服务停了分区就停滞冷热分离主表小→查询快→备份小→Buffer Pool 效率高查询需要跨多个数据源时复杂度增加归档表主库释放大量空间和性能跨表查询历史数据需要 UNION 或应用层聚合适用场景时序数据日志、轨迹、监控指标——按月 RANGE 分区——天然适合。电商订单表按创建时间按月分区——用户查最近 3 个月走热分区客服查历史单走归档表。IoT 传感器数据每天 10 亿条——按天分区——每天的分区自动创建、自动归档。SaaS 多租户按 tenant_id LIST 分区——每个租户数据物理隔离——备份/恢复/删除一个租户的数据只需操作一个分区。对账系统按账期月/季分区——账期关闭后归档并锁定分区。不适用场景随机访问的小表几百 MB 以下的表不需要分区——索引已经足够快。频繁跨分区 JOIN 的场景两个大表按不同维度分区——跨分区 JOIN 需要全量扫描多个分区——性能不如不分区的索引 JOIN。分区键不在查询条件中如果 80% 的查询没有带上分区键——分区裁剪基本不生效——分区没有收益。注意事项分区键必须是主键/唯一键/外键的一部分否则ERROR 1503: A PRIMARY KEY must include all columns in the tables partitioning function。MAXVALUE 分区是兜底但不是万能的所有不匹配其他分区的数据都落入 MAXVALUE 分区——如果忘记定期分裂——最终 p_future 变得巨大。分区数不是越多越好MySQL 在打开表时需要扫描所有分区的元数据——超过 1000 个分区后OPEN TABLE可能变慢。常见踩坑经验故障案例一ADD PARTITION 操作执行了 3 小时还没完成——期间表不可写。根因ADD PARTITION 需要对整个表加 MDL 排他锁——高峰期并发查询一直占用 MDL 共享锁——ADD 排队等了 3 小时。修复在凌晨 3 点执行分区 DDL——并用LOCKNONEMySQL 8.0 ALGORITHMINSTANT 的部分操作。故障案例二某月的数据被插到了相邻月分区。根因VALUES LESS THAN (TO_DAYS(2025-02-01))中的边界是’小于’——2 月 1 日 00:00:00 的数据归入 p202502 而非 p202501。修复撰写边界注释或使用VALUES LESS THAN (2025-02-01)配合RANGE COLUMNS分区。故障案例三归档表的主键 id 和主表发生了冲突。根因归档表的主键是独立的 AUTO_INCREMENT——从主表迁移数据时 id 可能与归档表已有 id 冲突。修复归档表的 id 不使用 AUTO_INCREMENT——直接沿用主表的 id 值。思考题分区裁剪时——WHERE created_at 2026-03-15 AND created_at 2026-04-15会扫描几个分区如果分区边界是月初——如何只扫描需要的分区如果你有一个 10TB 的表需要在线分区不停机——MySQL 原生提供了哪些在线分区操作有哪些分区 DDL 必须使用ALGORITHMCOPY全程锁表答案提示第 1 题——扫描 2 个分区3 月 4 月因为条件跨越了分区边界如果分区粒度是周而非月——可精确裁剪到 1-2 周第 2 题——ADD/DROP/TRUNCATE/COALESCE PARTITION 在 8.0 支持 INSTANT 或 INPLACE不锁表REORGANIZE PARTITION 取决于是否改变分区表达式——多数情况需要 ALGORITHMCOPY锁表。延伸阅读与资源Java 工程师进阶从 JVM 生产排障到OpenJDK原理NumPy 从入门到生产落地全链路实战指南科学计算/向量化Redis 8 实战精讲从 CRUD 到源码构建高可用缓存系统Redis 实战修炼与原理进阶Python 3实战精进从脚本到高并发订单引擎python入门Rquests从菜鸟脚本到企业级SDK的网络实战圣经Milvus向量数据库实战修炼从 0 到 1精通向量检索与生产落地MongoDB 实战进阶与内核修炼后端工程师的 AI 转型第一课Ollama 与私有化大模型实战10倍开发者的 Dify 魔法书从零构建全栈 AI 应用后端工程师转型AI第一课-Ollama 与私有化大模型实战大型语言模型(LLM) vLLM 高性能推理落地实战Agent开发之LlamaIndex 实战修炼与源码进阶大语言模型Transformers 实战修炼与源码剖析 第25章：MySQL分区表、归档与冷热数据治理
1. 项目背景业务场景某快递公司的运单跟踪系统每天产生 2000 万条物流轨迹记录核心表tracking_log在半年内增长到 36 亿行占用磁盘 2TB。日常查询——“某运单号的最新 10 条轨迹”——在索引帮助下只需 2ms。但运营后台的查询 3 个月前某城市所有已签收运单需要扫描 30 亿行——耗时 20 分钟。同时DBA 每月需要手动归档半年前的旧数据——每次执行DELETE FROM tracking_log WHERE created_at 6个月前——需要分批删除 30 亿行中的 60 亿行跨越多个时间窗口整个过程耗时 3 天期间数据库 IO 打到 100%——业务频繁超时。痛点大表不分区、不归档后果是灾难性的历史查询拖垮核心业务索引高度随行数增长——36 亿行的 BTree 高度已达 6-7 层——即使走索引也需要 6-7 次磁盘 IO。DELETE 即灾难大表删除数据不是回收空间——是标记删除 产生大量 undo log 脏页刷新风暴——相当于对整表做了一次全表扫描级别的 IO 操作。备份窗口不可控每天全量备份 2TB 需要 6 小时——备份窗口超过了业务低谷时间——备份还没完成早高峰就来了。统计信息频繁失真每天新增 2000 万行——ANALYZE TABLE的采样页只覆盖了最近插入的一小部分——旧数据的统计完全不准。本章教你用 RANGE 分区按时间拆分大表、用分区裁剪加速查询、用TRUNCATE PARTITION秒级归档历史数据、并设计冷热数据分离策略。2. 项目设计【场景DBA 连续三天加班做数据归档小胖看着他把 DELETE 语句分批次跑】小胖“大师为什么删数据这么慢DELETE FROM tracking_log WHERE created_at 2026-01-01不应该很快吗又不是全表删除。”大师“因为 MySQL 的 DELETE 并不是’撕掉这一页’——它是’在每一行上写一个删除标记’。你删 1 亿行——InnoDB 需要找到这 1 亿行通过索引或全表扫描——逐行标记删除——生成 1 亿条 undo log——标记 1 亿行所在的数据页为脏页——然后慢悠悠地刷这些脏页到磁盘。整个过程就是一次全表扫描 1 亿次写入。”小白“那分区表怎么解决这个问题”大师“分区表把一张逻辑表拆成多个物理分区——比如按月分区——每个分区就是一个独立的小表。当你执行SELECT * FROM tracking_log WHERE created_at BETWEEN 2026-03-01 AND 2026-03-31时——优化器通过分区裁剪只扫描 3 月这一个分区30 亿行 → 2 亿行——查询量减少为原来的 1/15。当你需要归档 6 个月前的数据时——不需要DELETE——直接ALTER TABLE tracking_log TRUNCATE PARTITION p202601——瞬间完成——因为 MySQL 只是把整个分区文件删掉了。”技术映射分区裁剪 优化器根据 WHERE 条件中的分区键只扫描相关的分区——跳过不相关分区的全量 IO。TRUNCATE PARTITION DDL 操作瞬间删除整个分区——比 DELETE 快 1000 倍。小胖“那 RANGE、LIST、HASH、KEY 四种分区类型怎么选”大师RANGE按时间、数值范围分区如created_at按月、id按 1000 万一个范围。你的运单轨迹表就是最适合 RANGE 分区的场景。LIST按离散值分区如region按华北、华东、华南分区——适合地区隔离。HASH按哈希值均匀分布——适合没有明显范围特征的场景如user_id取模——但无法做范围查询裁剪。KEY类似 HASH但由 MySQL 内置函数计算——适合主键分布均匀的表。小白“分区表有什么坑吗我听说会锁表”大师“最大的坑是——分区操作ADD、DROP、TRUNCATE PARTITION本质是 DDL——需要获取 MDL 排他锁。如果你的表正在被查询或更新——DDL 就会排队等候——等到所有查询结束才执行。所以在生产中——虽然 DROP PARTITION 本身是秒级操作——但排队等待 MDL 锁可能是分钟级甚至小时级。解决方法是——在业务低峰期做——或者用 pt-online-schema-change 等工具辅助。”3. 项目实战3.1 环境准备-- 确认分区支持SHOWPLUGINS;-- 应看到 partition | ACTIVE | STORAGE ENGINE-- 确认当前不支持分区的表引擎MyISAM 支持但生产不用3.2 分步实现步骤一创建 RANGE 分区表——按月份分区-- 步骤目标将 orders_large 表改造为按月份 RANGE 分区USEecommerce;-- 原始 orders_large 表有 100 万行先看看它的大小SELECTTABLE_NAME,ROUND(DATA_LENGTH/1024/1024,2)ASdata_mb,TABLE_ROWSFROMinformation_schema.TABLESWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_large;-- 创建按月份分区的版本DROPTABLEIFEXISTSorders_partitioned;CREATETABLEorders_partitioned(idBIGINTUNSIGNEDAUTO_INCREMENT,user_idBIGINTUNSIGNEDNOTNULL,product_idBIGINTUNSIGNEDNOTNULL,amountDECIMAL(10,2)NOTNULL,statusTINYINTNOTNULLDEFAULT0,created_atDATETIME(3)NOTNULL,PRIMARYKEY(id,created_at),-- 分区键必须包含在主键中INDEXidx_user(user_id),INDEXidx_status_created(status,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp202501VALUESLESS THAN(TO_DAYS(2025-02-01)),PARTITIONp202502VALUESLESS THAN(TO_DAYS(2025-03-01)),PARTITIONp202503VALUESLESS THAN(TO_DAYS(2025-04-01)),PARTITIONp202504VALUESLESS THAN(TO_DAYS(2025-05-01)),PARTITIONp202505VALUESLESS THAN(TO_DAYS(2025-06-01)),PARTITIONp202506VALUESLESS THAN(TO_DAYS(2025-07-01)),PARTITIONp202507VALUESLESS THAN(TO_DAYS(2025-08-01)),PARTITIONp202601VALUESLESS THAN(TO_DAYS(2026-02-01)),PARTITIONp202602VALUESLESS THAN(TO_DAYS(2026-03-01)),PARTITIONp202603VALUESLESS THAN(TO_DAYS(2026-04-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE-- 兜底分区);-- 从原始表导入数据INSERTINTOorders_partitionedSELECT*FROMorders_large;-- 查看各分区的数据分布SELECTPARTITION_NAME,TABLE_ROWS,ROUND(DATA_LENGTH/1024/1024,2)ASdata_mbFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedORDERBYPARTITION_NAME;-- 验证总数SELECTCOUNT(*)FROMorders_partitioned;-- 应与 orders_large 行数一致步骤二验证分区裁剪——对比有/无分区的查询性能-- 步骤目标用 EXPLAIN 观察分区裁剪是否生效-- 有分区的查询分区裁剪生效EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_atBETWEEN2025-03-01AND2025-03-31;-- partitions 列仅显示 p202503而不是 ALL-- → 只扫描一个分区-- 对比无分区的查询全表扫描EXPLAINSELECTCOUNT(*)FROMorders_largeWHEREcreated_atBETWEEN2025-03-01AND2025-03-31;-- partitions 列NULL不分区的表-- 分区裁剪不生效的反例 EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREYEAR(created_at)2025;-- partitions 列ALL所有分区-- 因为用了函数 YEAR() —— 优化器无法推导分区边界-- 正确的写法EXPLAINSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_at2025-01-01ANDcreated_at2026-01-01;-- partitions 列p202501~p202601裁剪成功步骤三TRUNCATE PARTITION——秒级归档历史数据-- 步骤目标演示 TRUNCATE PARTITION 比 DELETE 快多少-- 1. 先看某个分区有多少数据SELECTPARTITION_NAME,TABLE_ROWSFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedANDPARTITION_NAMEp202501;-- 2. 传统 DELETE 方式慢-- DELETE FROM orders_large WHERE created_at 2025-02-01;-- 这将扫描全表 产生大量 undo 逐行标记删除-- 3. TRUNCATE PARTITION秒级ALTERTABLEorders_partitionedTRUNCATEPARTITIONp202501;-- 验证p202501 的数据已被清空SELECTPARTITION_NAME,TABLE_ROWSFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedANDPARTITION_NAMEp202501;-- TABLE_ROWS 0-- 验证总数SELECTCOUNT(*)FROMorders_partitioned;-- 应减少了 p202501 的行数-- 注意TRUNCATE PARTITION 后p202501 分区仍然存在只是内容空了-- 如果不需要这个分区了——可以 DROP PARTITION-- ALTER TABLE orders_partitioned DROP PARTITION p202501;步骤四自动创建未来分区——用 EVENT 调度器-- 步骤目标自动创建下个月的分区避免数据插入到 p_futureMAXVALUE 分区-- 手动创建一个下个月的分区示例2026年5月ALTERTABLEorders_partitioned REORGANIZEPARTITIONp_futureINTO(PARTITIONp202604VALUESLESS THAN(TO_DAYS(2026-05-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE);-- 创建自动添加分区的存储过程DELIMITER//CREATEPROCEDUREadd_monthly_partition(INtable_nameVARCHAR(64),INnext_monthVARCHAR(7))BEGINDECLAREpartition_nameVARCHAR(20);DECLAREboundary_dateDATE;DECLAREsql_stmtTEXT;SETpartition_nameCONCAT(p,REPLACE(next_month,-,));SETboundary_dateDATE_ADD(CONCAT(next_month,-01),INTERVAL1MONTH);SETsql_stmtCONCAT(ALTER TABLE ,table_name, REORGANIZE PARTITION p_future INTO (, PARTITION ,partition_name, VALUES LESS THAN (TO_DAYS(\,boundary_date,\)),, PARTITION p_future VALUES LESS THAN MAXVALUE,));PREPAREstmtFROMsql_stmt;EXECUTEstmt;DEALLOCATEPREPAREstmt;END//DELIMITER;-- 测试添加 2026 年 7 月的分区CALLadd_monthly_partition(orders_partitioned,2026-07);-- 验证新分区已创建SELECTPARTITION_NAME,PARTITION_DESCRIPTIONFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitionedORDERBYPARTITION_ORDINAL_POSITION;-- 创建 EVENT 每月自动执行CREATEEVENT auto_add_partitionONSCHEDULE EVERY1MONTHSTARTS2026-06-01 00:00:00DOCALLadd_monthly_partition(orders_partitioned,DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL1MONTH),%Y-%m));步骤五冷热数据分离——归档到独立表/独立实例-- 步骤目标将 3 个月前的数据迁移到归档表减少主表体积-- 方案 A同库归档表简单CREATETABLEorders_archive(idBIGINTUNSIGNEDNOTNULL,user_idBIGINTUNSIGNEDNOTNULL,product_idBIGINTUNSIGNEDNOTNULL,amountDECIMAL(10,2)NOTNULL,statusTINYINTNOTNULLDEFAULT0,created_atDATETIME(3)NOTNULL,PRIMARYKEY(id,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp_oldVALUESLESS THAN(TO_DAYS(2025-01-01)),PARTITIONp_futureVALUESLESS THAN MAXVALUE);-- 将 3 个月前的数据迁移到归档表分批迁移-- 每次迁移 10000 行防止大事务DELIMITER//CREATEPROCEDUREarchive_old_orders(INbefore_dateDATE)BEGINDECLAREdoneINTDEFAULTFALSE;DECLAREbatch_sizeINTDEFAULT10000;DECLAREaffected_rowsINTDEFAULT1;WHILEaffected_rows0DOSTARTTRANSACTION;INSERTINTOorders_archiveSELECT*FROMorders_largeWHEREcreated_atbefore_dateLIMITbatch_size;SETaffected_rowsROW_COUNT();DELETEFROMorders_largeWHEREcreated_atbefore_dateLIMITbatch_size;COMMIT;-- 每批之间短暂休眠——减少 IO 压力DOSLEEP(1);ENDWHILE;END//DELIMITER;-- 执行归档在业务低峰期-- CALL archive_old_orders(2025-04-01);-- 方案 B归档到独立 MySQL 实例节省主库资源-- 配置一个低配的归档 MySQL 实例-- 用 mysqldump 或 SELECT INTO OUTFILE LOAD DATA INFILE 迁移数据-- 主库只保留近 3 个月的热数据3.3 测试验证-- 验证清单-- 1. 确认分区裁剪生效EXPLAINPARTITIONSSELECTCOUNT(*)FROMorders_partitionedWHEREcreated_atBETWEEN2025-06-01AND2025-06-30;-- 预期partitions 列显示单个分区名不含 ALL-- 2. 确认各分区行数总和等于总数SELECT(SELECTSUM(TABLE_ROWS)FROMinformation_schema.PARTITIONSWHERETABLE_SCHEMAecommerceANDTABLE_NAMEorders_partitioned)ASpartition_sum,(SELECTCOUNT(*)FROMorders_partitioned)ASactual_count;-- 两个数值应接近TABLE_ROWS 是估算值可能有偏差-- 3. 验证 TRUNCATE PARTITION 的效果-- 执行前后对比-- ALTER TABLE orders_partitioned TRUNCATE PARTITION p202501;-- SELECT COUNT(*) FROM orders_partitioned; -- 应减少-- 4. 验证 EVENT 调度器正常工作SELECTEVENT_NAME,STATUS,LAST_EXECUTEDFROMinformation_schema.EVENTSWHEREEVENT_NAMEauto_add_partition;4. 项目总结优点 缺点维度优点缺点/局限RANGE 分区查询直击目标分区——性能提升 10-100 倍TRUNCATE PARTITION 秒级归档分区键必须包含在主键和唯一键中——可能改变表设计分区裁剪自动由优化器完成无需修改 SQL使用函数包裹分区键会导致裁剪失效自动分区EVENT 调度器免手动维护EVENT 依赖 MySQL 服务运行——服务停了分区就停滞冷热分离主表小→查询快→备份小→Buffer Pool 效率高查询需要跨多个数据源时复杂度增加归档表主库释放大量空间和性能跨表查询历史数据需要 UNION 或应用层聚合适用场景时序数据日志、轨迹、监控指标——按月 RANGE 分区——天然适合。电商订单表按创建时间按月分区——用户查最近 3 个月走热分区客服查历史单走归档表。IoT 传感器数据每天 10 亿条——按天分区——每天的分区自动创建、自动归档。SaaS 多租户按 tenant_id LIST 分区——每个租户数据物理隔离——备份/恢复/删除一个租户的数据只需操作一个分区。对账系统按账期月/季分区——账期关闭后归档并锁定分区。不适用场景随机访问的小表几百 MB 以下的表不需要分区——索引已经足够快。频繁跨分区 JOIN 的场景两个大表按不同维度分区——跨分区 JOIN 需要全量扫描多个分区——性能不如不分区的索引 JOIN。分区键不在查询条件中如果 80% 的查询没有带上分区键——分区裁剪基本不生效——分区没有收益。注意事项分区键必须是主键/唯一键/外键的一部分否则ERROR 1503: A PRIMARY KEY must include all columns in the tables partitioning function。MAXVALUE 分区是兜底但不是万能的所有不匹配其他分区的数据都落入 MAXVALUE 分区——如果忘记定期分裂——最终 p_future 变得巨大。分区数不是越多越好MySQL 在打开表时需要扫描所有分区的元数据——超过 1000 个分区后OPEN TABLE可能变慢。常见踩坑经验故障案例一ADD PARTITION 操作执行了 3 小时还没完成——期间表不可写。根因ADD PARTITION 需要对整个表加 MDL 排他锁——高峰期并发查询一直占用 MDL 共享锁——ADD 排队等了 3 小时。修复在凌晨 3 点执行分区 DDL——并用LOCKNONEMySQL 8.0 ALGORITHMINSTANT 的部分操作。故障案例二某月的数据被插到了相邻月分区。根因VALUES LESS THAN (TO_DAYS(2025-02-01))中的边界是’小于’——2 月 1 日 00:00:00 的数据归入 p202502 而非 p202501。修复撰写边界注释或使用VALUES LESS THAN (2025-02-01)配合RANGE COLUMNS分区。故障案例三归档表的主键 id 和主表发生了冲突。根因归档表的主键是独立的 AUTO_INCREMENT——从主表迁移数据时 id 可能与归档表已有 id 冲突。修复归档表的 id 不使用 AUTO_INCREMENT——直接沿用主表的 id 值。思考题分区裁剪时——WHERE created_at 2026-03-15 AND created_at 2026-04-15会扫描几个分区如果分区边界是月初——如何只扫描需要的分区如果你有一个 10TB 的表需要在线分区不停机——MySQL 原生提供了哪些在线分区操作有哪些分区 DDL 必须使用ALGORITHMCOPY全程锁表答案提示第 1 题——扫描 2 个分区3 月 4 月因为条件跨越了分区边界如果分区粒度是周而非月——可精确裁剪到 1-2 周第 2 题——ADD/DROP/TRUNCATE/COALESCE PARTITION 在 8.0 支持 INSTANT 或 INPLACE不锁表REORGANIZE PARTITION 取决于是否改变分区表达式——多数情况需要 ALGORITHMCOPY锁表。延伸阅读与资源Java 工程师进阶从 JVM 生产排障到OpenJDK原理NumPy 从入门到生产落地全链路实战指南科学计算/向量化Redis 8 实战精讲从 CRUD 到源码构建高可用缓存系统Redis 实战修炼与原理进阶Python 3实战精进从脚本到高并发订单引擎python入门Rquests从菜鸟脚本到企业级SDK的网络实战圣经Milvus向量数据库实战修炼从 0 到 1精通向量检索与生产落地MongoDB 实战进阶与内核修炼后端工程师的 AI 转型第一课Ollama 与私有化大模型实战10倍开发者的 Dify 魔法书从零构建全栈 AI 应用后端工程师转型AI第一课-Ollama 与私有化大模型实战大型语言模型(LLM) vLLM 高性能推理落地实战Agent开发之LlamaIndex 实战修炼与源码进阶大语言模型Transformers 实战修炼与源码剖析