个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 分区表实战RANGE/LIST/HASH/KEY 怎么选分区剪枝与 10 个踩坑一、先想清楚分区解决什么解决不了什么二、四种分区类型速览三、RANGE 分区唯一能优雅做按时间归档的选择3.1 分区函数 vs RANGE COLUMNS这是个大坑3.2 主键 / 唯一键必须包含分区键四、LIST / HASH / KEY 分别什么时候用五、分区管理增、删、清、拆、合、换5.1 MAXVALUE 陷阱5.2 DROP 与 TRUNCATE 的差别5.3 维护类操作六、分区剪枝分区表唯一的提速来源七、分区表的真实开销很多人没算这笔账八、MySQL 8.0 相比 5.7 的变化九、10 个一踩就炸的坑十、生产落地最佳实践十一、什么时候上分区什么时候上分库分表小结MySQL 分区表实战RANGE/LIST/HASH/KEY 怎么选分区剪枝与 10 个踩坑单表 5000 万行加了索引还是慢第一反应往往是要不要分库分表。但在上中间件之前有个成本更低的选项经常被忽略分区表。这篇不列语法手册只讲三件事分区到底靠什么提速分区剪枝、四种分区怎么选、生产上有哪些一踩就炸的坑。一、先想清楚分区解决什么解决不了什么分区表的本质是逻辑上仍是一张表物理上按规则拆成多个 .ibd 文件。应用端 SQL 不用改SELECT * FROM orders WHERE ...还是那句MySQL 自己在底层决定去哪几个文件里找。维度分区表分库分表Sharding应用层改动无SQL 不变需要中间件或路由逻辑数据分布单实例内多实例 / 多库上限受单机磁盘、CPU、IO 限制可水平扩展跨片查询原生支持只是慢需要聚合层非常麻烦分布式事务不涉及需要额外方案运维复杂度低建表 定期增删分区高典型场景冷热分明、按时间或地域归档单机容量或写入扛不住分区表最大的价值从来不是提速而是三件事历史数据秒级清理ALTER TABLE ... DROP PARTITION p202401是元数据级操作比DELETE FROM ... WHERE create_time ...删 3000 万行快几个数量级且不产生大量 undo / redo。让索引变小、更容易进 Buffer Pool单个分区的 B 树比整张大表的索引矮热分区的索引更容易常驻内存。分区剪枝查询条件命中分区键时优化器直接跳过无关分区。分区解决不了的单机写瓶颈、单机容量上限、跨分区的高并发热点。如果你的痛点是写入 TPS 打满 redo分区表帮不了你。二、四种分区类型速览类型分区依据典型场景能否 DROP PARTITION能否均分数据RANGE分区键落在某个连续区间按时间月/天归档、按 ID 段✅❌ 可能倾斜RANGE COLUMNS同 RANGE但支持多列元组与非整型列按DATETIME、VARCHAR分区✅❌LIST分区键属于某个离散值集合按地区、按状态、按租户✅❌HASH对分区键取模想打散、无明显范围查询❌ 只能 COALESCE 收缩✅KEY内部哈希分区键可非整型同 HASH但只能写列名不能写表达式❌✅几个容易记混的点LIST 分区只支持整型或返回整型的表达式8.0 也一样要支持字符串、日期得用LIST COLUMNS。HASH 分区的分区键必须是整型表达式KEY分区可以拿字符串列且不允许写表达式只能写列名。只有 RANGE 和 LIST 支持子分区SUBPARTITIONHASH / KEY 不支持。一张表最多1024 个分区含子分区。三、RANGE 分区唯一能优雅做按时间归档的选择3.1 分区函数 vs RANGE COLUMNS这是个大坑MySQL 早期只能用返回整数的分区函数来分区所以你会看到这种经典老写法PARTITIONBYRANGE(YEAR(login_time))(-- 5.5/5.6 时代的产物PARTITIONp2023VALUESLESS THAN(2024),PARTITIONp2024VALUESLESS THAN(2025),PARTITIONpmaxVALUESLESS THAN MAXVALUE);5.5 之后引入了RANGE COLUMNS推荐直接用它CREATETABLElogin_log(idBIGINTNOTNULLAUTO_INCREMENT,user_idBIGINTNOTNULL,login_timeDATETIMENOTNULL,PRIMARYKEY(id,login_time))ENGINEInnoDBPARTITIONBYRANGECOLUMNS(login_time)(PARTITIONp202401VALUESLESS THAN(2024-02-01),PARTITIONp202402VALUESLESS THAN(2024-03-01),PARTITIONp202403VALUESLESS THAN(2024-04-01),PARTITIONpmaxVALUESLESS THAN(MAXVALUE));为什么推荐 COLUMNS 版本两点分区键上套了函数剪枝条件就必须和这个函数对得上。用YEAR(login_time)分区时只有WHERE YEAR(login_time) 2024这类写法能剪枝而我们最常写的login_time 2024-03-01 AND login_time 2024-04-01反而剪不干净。用RANGE COLUMNS(login_time)分区时范围条件天然可剪枝写起来也更符合直觉。RANGE COLUMNS支持DATE/DATETIME/VARCHAR等类型还能写多列元组PARTITIONBYRANGECOLUMNS(shop_id,order_date)(PARTITIONs1VALUESLESS THAN(10,2024-01-01),PARTITIONs2VALUESLESS THAN(100,2024-01-01),PARTITIONs3VALUESLESS THAN(MAXVALUE,MAXVALUE))元组比较是从左到右先比第一列第一列相等才比第二列。这个规则决定了WHERE shop_id 50 AND order_date 2024-06-01会落在 s2而WHERE shop_id 1000第一列超出 s2 上界但小于 MAXVALUE落在 s3。3.2 主键 / 唯一键必须包含分区键这是新手 100% 会撞的错CREATETABLEt(idBIGINTNOTNULLAUTO_INCREMENT,create_timeDATETIMENOTNULL,PRIMARYKEY(id)-- ❌ 分区键 create_time 不在主键里)PARTITIONBYRANGECOLUMNS(create_time)(...);-- ERROR 1503: A PRIMARY KEY must include all columns in the tables partitioning function原因不是刁难你MySQL 要保证唯一性约束的校验成本可控。如果主键不含分区键插入一行就得扫描全部分区才能确认id是否重复——退化成全表扫描分区就白分了。所以这条规则是硬的表上每个 UNIQUE KEY含 PRIMARY都必须包含分区表达式用到的所有列。改法是写成复合主键PRIMARY KEY (id, create_time)。⚠️ 一般建议写成(id, 分区键)而不是(分区键, id)前者id仍是前缀按主键的点查还能高效走反过来则单独按id查就用不上主键了。另外要记住二级索引是每个分区各自一棵没有全局索引——这点后面还会讲。四、LIST / HASH / KEY 分别什么时候用-- LIST按地区归档值天然离散CREATETABLEorder_by_region(idBIGINTNOTNULL,regionTINYINTNOTNULL,-- 1 华东 2 华北 3 华南 ...amountDECIMAL(12,2),PRIMARYKEY(id,region))PARTITIONBYLIST(region)(PARTITIONp_eastVALUESIN(1),PARTITIONp_northVALUESIN(2),PARTITIONp_southVALUESIN(3),PARTITIONp_otherVALUESIN(4,5,6,7));-- HASH没有范围查询只想把数据打散、缩小单个索引体积CREATETABLEdevice_heartbeat(idBIGINTNOTNULL,device_idBIGINTNOTNULL,tsDATETIMENOTNULL,PRIMARYKEY(id,device_id))PARTITIONBYHASH(device_id)PARTITIONS16;判断口诀你的查询长这样选它WHERE create_time BETWEEN ? AND ?占绝大多数RANGE按时间WHERE region ?或WHERE tenant_id IN (...)LIST基本都是WHERE id ?、WHERE device_id ?点查HASH / KEY既要按时间清理又想在时间分区内再打散RANGE HASH 子分区HASH / KEY 分区的一个硬伤它没有区间概念所以无法只清理某段时间的数据。想删历史数据只能DELETE等于放弃了分区表最大的好处。所以生产上 HASH 分区远没有 RANGE 常见别因为它能均分就选它。五、分区管理增、删、清、拆、合、换下面这张表基本覆盖日常运维的全部动作建议收藏操作语句数据会不会丢加分区ALTER TABLE t ADD PARTITION (PARTITION p6 VALUES LESS THAN (2024-07-01));否删分区ALTER TABLE t DROP PARTITION p1;✅会分区和数据一起没清空分区ALTER TABLE t TRUNCATE PARTITION p1;✅ 清数据但保留分区结构拆分区ALTER TABLE t REORGANIZE PARTITION pmax INTO (PARTITION p6 VALUES LESS THAN (...), PARTITION pmax VALUES LESS THAN (MAXVALUE));否合并分区ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO (PARTITION p12 VALUES LESS THAN (...));否不能跳着合并 p1p3分区交换ALTER TABLE t EXCHANGE PARTITION p1 WITH TABLE t_arch;就是搬家见 5.2HASH 扩分区ALTER TABLE t ADD PARTITION PARTITIONS 2;否会重分布HASH 缩分区ALTER TABLE t COALESCE PARTITION 2;否会重分布改分区键ALTER TABLE t PARTITION BY RANGE COLUMNS(id) (...);否但重建全表非常慢5.1 MAXVALUE 陷阱如果最后一个分区是VALUES LESS THAN MAXVALUE那么ALTERTABLEtADDPARTITION(PARTITIONp6VALUESLESS THAN(2024-07-01));-- ERROR 1493: VALUES LESS THAN value must be strictly increasing for each partition因为分区上界必须严格递增而 MAXVALUE 已经是最大了。正确做法是拆ALTERTABLEt REORGANIZEPARTITIONpmaxINTO(PARTITIONp202407VALUESLESS THAN(2024-08-01),PARTITIONpmaxVALUESLESS THAN(MAXVALUE));于是有两个流派留 MAXVALUE 兜底保证任何数据都能插进去代价是每次加分区都要REORGANIZE。不留 MAXVALUE加分区一条ADD PARTITION搞定但分区用完后插入会直接报错ERROR 1526: Table has no partition for value xxx。生产上更稳的做法不留 MAXVALUE用定时任务提前把未来 2~3 个月的分区建好并配告警监控分区余量。5.2 DROP 与 TRUNCATE 的差别DROP PARTITION数据没了分区定义也没了。TRUNCATE PARTITION数据没了分区还在可以立刻继续写入。归档清理推荐EXCHANGEDROP组合留个后手-- 1. 建一张与主表同结构的普通表不带分区CREATETABLElogin_log_202401LIKElogin_log;ALTERTABLElogin_log_202401 REMOVE PARTITIONING;-- 2. 把 p202401 整个换出去元数据操作秒级ALTERTABLElogin_log EXCHANGEPARTITIONp202401WITHTABLElogin_log_202401;-- MySQL 8.0 可加 WITHOUT VALIDATION 跳过逐行校验更快-- ALTER TABLE login_log EXCHANGE PARTITION p202401 WITH TABLE login_log_202401 WITHOUT VALIDATION;-- 3. login_log_202401 现在是一张独立表慢慢 mysqldump 归档出去-- 4. 确认归档完成后删掉已经空掉的分区ALTERTABLElogin_logDROPPARTITIONp202401;EXCHANGE的前提目标表结构完全一致、自身无分区、且分区内数据满足目标表的约束。5.3 维护类操作ALTERTABLEt REBUILDPARTITIONp0,p1;-- 重建分区删分区 重建 插回数据整理碎片ALTERTABLEtANALYZEPARTITIONp0,p1;-- 重新统计更新 information_schema 里的估算行数ALTERTABLEtCHECKPARTITIONp0,p1;ALTERTABLEt REPAIRPARTITIONp0,p1;-- 主要对 MyISAM/ARCHIVE 有意义InnoDB 基本用不上⚠️InnoDB 分区表不支持OPTIMIZE PARTITION请用REBUILD PARTITIONANALYZE PARTITION代替。六、分区剪枝分区表唯一的提速来源分区剪枝Partition Pruning 优化器在生成执行计划时把不可能包含目标数据的分区直接排除。它生效的唯一前提是WHERE 条件里用到了分区键。EXPLAINSELECT*FROMlogin_logWHERElogin_time2024-03-01ANDlogin_time2024-04-01;MySQL 8.0 的EXPLAIN输出默认就带partitions列直接看它-------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | -------------------------------------------------------------------------------------------------- | 1 | SIMPLE | login_log | p202403 | ALL | NULL | NULL | NULL | NULL | 9876 | 11.11 | --------------------------------------------------------------------------------------------------版本差异5.7 需要写EXPLAIN PARTITIONS SELECT ...才显示该列MySQL 8.0 移除了PARTITIONS和EXTENDED关键字直接EXPLAIN即可。对比一下不剪枝的情况-- ❌ 条件跟分区键无关 → 全分区扫描EXPLAINSELECT*FROMlogin_logWHEREuser_id10086;-- partitions 列会列出 p202401,p202402,p202403,pmax 全部这就是分区表最反直觉的地方分区之后一条不带分区键的查询从扫一个大索引变成了扫 N 个小索引N 个分区的开销加起来可能比原来还大每打开一个分区都是一次 handler 调用且可能伴随一次 IO。几个剪枝失效的典型写法写法能否剪枝说明login_time 2024-03-01 AND login_time 2024-04-01✅最稳login_time BETWEEN 2024-03-01 AND 2024-03-31 23:59:59✅可以login_time INTERVAL 1 DAY ?❌分区键上套了表达式DATE(login_time) 2024-03-05❌函数包裹一般剪不掉SELECT ... FROM t PARTITION (p202403)✅手动指定分区强制剪枝JOIN 条件里带分区键常量✅8.0 支持 JOIN 场景剪枝手动指定分区是个好用的调试与兜底手段SELECTCOUNT(*)FROMlogin_logPARTITION(p202403);七、分区表的真实开销很多人没算这笔账DDL 会拿表级 MDL 排他锁。ADD/DROP/REORGANIZE PARTITION期间读写全阻塞虽然执行很快但它排队拿锁的那一刻后面所有 SQL 都在等着。分区越多打开表的代价越大。查询要打开的分区数 × 并发连接数会吃掉table_open_cache和文件描述符。经验值单表分区数控制在 100 以内几十个最好。没有全局索引。二级索引是每分区一棵不带分区键的查询要在每个分区的索引上都搜一遍。information_schema.PARTITIONS.TABLE_ROWS对 InnoDB 是估算值刚REORGANIZE完会不准要ANALYZE PARTITION。改分区键等于重建全表。ALTER TABLE t PARTITION BY ...会锁表并复制全部数据大表上等同于一次停机。八、MySQL 8.0 相比 5.7 的变化项5.78.0可分区的引擎通用分区层MyISAM 也能分区只支持原生分区引擎InnoDB、NDBMyISAM 分区已移除EXPLAIN PARTITIONS需要关键字关键字已移除默认输出 partitions 列EXCHANGE PARTITION有增加WITHOUT VALIDATION选项跳过逐行校验OPTIMIZE PARTITION部分引擎可用InnoDB 分区表不支持改用 REBUILD ANALYZE分区表 外键不支持仍不支持FULLTEXT / SPATIAL 索引不支持仍不支持最大分区数10241024一句话结论8.0 里谈分区基本等于谈InnoDB 分区别再想着拿 MyISAM 做分区表。九、10 个一踩就炸的坑忘了加分区写入直接失败——不留 MAXVALUE 而定时任务挂了插入会报Table has no partition for value xxx。必须监控最后一个分区的上界距今天数。分区键不在 WHERE 里性能反而下降——分区前先把慢 SQL 全筛一遍确认它们都带这个字段。主键不含分区键建表直接报错——写成PRIMARY KEY (id, 分区键)并注意保留业务点查的前缀能力。给分区键套了函数剪枝失效——优先RANGE COLUMNSWHERE 里用裸列范围条件。以为分区能解决写入瓶颈——不能单机 redo、Buffer Pool、刷脏能力都没变。分区开太多把table_open_cache打满——按月分别按天分三年真要按天就配合很短的归档周期。把DROP PARTITION当删数据随手执行——它是 DDL主从一起删且无法回滚删之前先 EXCHANGE 出去留个后手。分区表和外键二选一——有外键的表不能分区建表前先想清楚。跨分区的ORDER BY ... LIMIT可能更慢——每个分区排序后再归并容易触发临时表与 filesort。以为分区能替代分库分表——分区只是单机内的整理容量和写 TPS 到顶时还是得上 Sharding。十、生产落地最佳实践1. 分区粒度选能覆盖 80% 查询范围 便于归档的粒度。日志 / 流水类按月最舒服订单类按月或按季度监控类可以按天但只保留 30 天。2. 用事件调度器自动维护分区别靠人肉记住SETGLOBALevent_schedulerON;-- 更稳的写法是写进配置文件的 event_schedulerONDELIMITER$$CREATEEVENTIFNOTEXISTSev_add_login_log_partitionONSCHEDULE EVERY1MONTHSTARTS2024-08-01 02:00:00DOBEGINDECLAREv_firstDATEDEFAULTDATE_ADD(LAST_DAY(CURDATE())INTERVAL1DAY,INTERVAL2MONTH);DECLAREv_secondDATEDEFAULTDATE_ADD(v_first,INTERVAL1MONTH);-- 创建下下个月的分区保证始终有充足余量SETsCONCAT(ALTER TABLE login_log REORGANIZE PARTITION pmax INTO (,PARTITION p,DATE_FORMAT(v_first,%Y%m), VALUES LESS THAN (,v_second,), ,PARTITION pmax VALUES LESS THAN (MAXVALUE)));PREPAREstmtFROMs;EXECUTEstmt;DEALLOCATEPREPAREstmt;END$$DELIMITER;3. 归档用 EXCHANGE DROP不要用 DELETE。DELETE FROM t WHERE create_time ?删千万行会带来长事务、undo 暴涨、purge 跟不上、主从延迟、binlog 撑爆磁盘。而EXCHANGE PARTITION是元数据操作秒级完成。4. 监控三件事最后一个非 MAXVALUE 分区的上界还有多久到期、分区总数量、各分区行数。SELECTPARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS,DATA_LENGTHFROMinformation_schema.PARTITIONSWHERETABLE_SCHEMASCHEMA()ANDTABLE_NAMElogin_logORDERBYPARTITION_ORDINAL_POSITION;5. 上线前必做三件事用EXPLAIN确认核心慢 SQL 真的剪枝了在同等数据量的测试环境对比分区前后耗时别凭感觉演练一次归档和一次加分区确认 DDL 的锁影响可接受。十一、什么时候上分区什么时候上分库分表一句话判断数据量还没超过单机磁盘 60%、且查询都带分区键 → 分区表已到单机容量或写入瓶颈 → 分库分表已经上了 ShardingSphere 之类中间件 → 直接用中间件别再叠分区。分区表不是分库分表的替代品而是上分库分表之前最后一个低成本选项。小结分区表的真正价值秒级清理历史数据、索引变小、分区剪枝它不是万能提速药四种类型RANGE 按范围时间归档首选、LIST 按离散值、HASH / KEY 打散只有RANGE / LIST 支持DROP PARTITION这也是它们比 HASH 实用的根本原因优先用RANGE COLUMNS避免分区键套函数导致剪枝失效主键 / 唯一键必须包含分区键否则报 ERROR 1503分区键必须出现在 WHERE 里否则全分区扫描可能比不分区还慢有 MAXVALUE 时不能直接ADD PARTITION要REORGANIZE不留 MAXVALUE 就要监控分区余量归档用EXCHANGE PARTITIONDROP PARTITION千万别用DELETE8.0 只支持 InnoDB / NDB 分区不支持OPTIMIZE PARTITION外键和全文索引都不能与分区共存分区数控制在几十到一百以内DDL 会拿表级 MDL 锁务必在低峰执行下一篇聊 Online DDL——既然分区维护离不开 DDL那就必须搞清楚哪些 DDL 会锁表、MySQL 8.0 的 INSTANT / INPLACE / COPY 三种算法到底差在哪以及 gh-ost 为什么存在。