MySQL 5.7大表分区实战：视频播放记录表性能优化

1. 为什么视频系统的大表要做分区我接手那套内部视频系统的时候正在用 MySQL 5.7也正在被一张大表折磨。播放记录表 play_log 已经闷声涨到两千多万行每天还在以四五十万行的速度往上走那段时间最直观的感受是后台几个统计接口的响应时间从几百毫秒一路飙到三秒开外半夜定时任务跑统计时主库 IO 直接被打满连带着视频上传、转码回调这些核心链路都跟着受影响。后来我把这套系统的播放记录、操作日志等大表全部改成了 MySQL 5.7 表分区问题才算真正落地解决。我对着慢查询日志数了一下排名前十的慢 SQL 里有七条都落在 play_log 和 audit_log 这两张表上。原因其实不难理解单表两千万行之后InnoDB 聚簇索引的 B 树深度已经到了三四层每次回表都伴随多次随机 IO而像SELECT COUNT(*) ... WHERE create_time BETWEEN ...这类统计查询就算有索引也得扫描范围内的大量数据。索引不是万能药它没法降低“数据规模本身”带来的 IO 成本。真正要做的是把数据物理拆碎让每次查询只面对一小块数据。这个场景在视频系统里特别典型。视频系统里最头疼的不是视频信息表这种百万级的基础表而是播放记录、操作日志、评论流水这一类只增不改、持续写入的大表。它们有几个共同特征写入量线性增长没有明显的冷热窗口却有明确的时间维度查询几乎总是带时间范围条件历史数据访问频率低但是又不能直接删。这些特征叠在一起几乎就是“该做表分区”的标准画像。1.1 分区能解决哪些实际问题分区表从应用层来看还是一张表SQL 不用改但数据在物理存储上会被拆到多个分区文件里。MySQL 5.7 里做分区我拿到的几个最实在的好处查询提速。查询条件带上分区键时优化器会做分区裁剪只扫描命中的分区。play_log 按天分区后一个查三天的统计 SQL需要扫描的数据从两千万行直接缩到几百万行响应时间从三秒掉到两三百毫秒。清理变快。流水表最怕删历史数据。以前 DELETE 三个月前的记录逐行删几百万行主从延迟能拉到很高跑两三个小时很正常。分区之后一条ALTER TABLE play_log DROP PARTITION p202401秒级完成效果等价于删掉整批数据而且不会产生巨量 binlog。备份更有弹性。可以针对单个分区做导出归档也可以把不常用的历史分区单独处理备份周期明显缩短。索引维护更轻。每个分区的索引规模变小重建索引、更新统计信息的成本都低不少加索引时对在线业务的影响也小。1.2 为什么没上分库分表当时确实认真评估过分库分表但最终还是放弃了。视频系统的数据量虽然单表很大但总量还没有超过单实例的能力范围核心瓶颈是“一张表里数据太多”而不是“一台机器放不下”。上分库分表意味着要引入中间件或者自研路由规则数据访问层要重写全局唯一 ID、跨节点聚合、分布式事务这些复杂度全都会冒出来。对一个还在成长期的中小型视频系统来说这个成本太高了。也有同事建议把历史数据迁到归档表。这条路的问题是所有涉及历史数据的查询逻辑都得改而且“当前表和归档表”的边界一旦移动统计口径就容易对不上。分区方案在应用层是零改造的删除和归档都是数据库内部操作这是它当时最吸引我的地方。注意分区不是银弹。如果业务查询条件五花八门没有稳定高频的过滤维度分区可能不会有明显收益。视频系统适合分区恰恰是因为流水表的查询高度集中在时间维度上。2. 分区类型选型看完这四类你就知道该用哪个MySQL 5.7 官方支持 RANGE、LIST、HASH、KEY 四种分区方式。选型搞错分区不但不帮忙反而可能拖慢性能。下面先给一张对比表再逐个说我在视频系统里的选择逻辑。分区类型分区键要求特点视频系统典型用法RANGE整数或能返回整数的表达式按连续区间切分适合时间、ID 区间播放记录按天/按月分区LIST整数或能返回整数的表达式按离散枚举值匹配视频信息按业务线/分类分区HASH整数或能返回整数的表达式按哈希均匀分布适合无明显规律用户表按 user_id 分散读写KEY任意类型字符串也可以类似 HASH但内部哈希支持非整数按业务单号/手机号分布2.1 RANGE 分区是视频系统的首选在视频系统里最值得做分区的就是播放记录、操作日志、评论流水这几类表。它们的查询有一个共同点几乎都带时间范围条件。播放记录查“某视频某天有多少播放”操作日志查“某段时间内谁改了什么”评论列表查“某视频最近多少天的新评论”全部落在时间维度上。RANGE 分区按时间切分查询天然能命中分区裁剪收益最大。play_log 我最终选的是按play_date做 RANGE 分区。为什么不是按video_id做 HASHHASH 分区能把写入分散到固定数量的分区上但查询按时间范围走时它完全没法跳过无关分区。WHERE video_id 123 AND play_date BETWEEN ...这种高频 SQL最需要的还是快速过滤掉时间上不相干的分区。选分区键的第一原则永远是哪个条件在业务查询里出现频率最高分区键就选哪个而不是看哪个字段数据分布最均匀。2.2 分区键和主键的硬约束这是分区表最容易被坑的一个点分区表的所有分区键字段必须包含在表的所有唯一键包括主键里。换句话说如果表有主键分区键必须是主键的一部分如果表有唯一索引分区键也必须包含在那个唯一索引里。背后的原因是InnoDB 只能在单个分区内部做唯一性校验。若分区键不在唯一键里同一个唯一键值可能落在不同分区引擎根本无法在全局范围内保证唯一。MySQL 在创建分区表时会直接报错错误信息通常形如A PRIMARY KEY must include all columns in the tables partitioning function。实践中有两种处理方式一是接受联合主键把分区键加进主键二是取消分区表上无关的唯一索引把唯一性约束交给业务层去保证。我给 play_log 设计表结构时原本的主键是自增id分区键是play_date最后改成了PRIMARY KEY (id, play_date)。既满足分区约束又保证了 AUTO_INCREMENT 列必须是某个索引第一列的要求。2.3 选分区键要避开的坑结合踩过的和帮同事排查过的经验选分区键时有三个坑建议提前避开分区键不能选允许 NULL 的字段或者必须有默认值兜底。RANGE 分区里 NULL 会被 MySQL 当作比任何值都小统一路由到最小的分区。业务一旦没传日期脏数据全堆在第一个分区那个分区越来越大查询裁剪效果也会变差。分区键要能覆盖高频查询条件。见过有人把视频信息表按status做 LIST 分区但系统里所有查询都以category_id或publish_time为核心条件分区键几乎不出现在 WHERE 里每次查询都扫全部分区性能比不分还差。不要选更新频繁的字段做分区键。分区键一旦被更新InnoDB 要把记录从原分区移到目标分区代价远高于普通更新。视频信息表里转码状态、审核状态都会变这类字段明显不合适。3. 实战视频播放记录表的分区设计与实现铺垫了这么多下面进入实操环节。我以 play_log 为例子完整走一遍从表结构设计到分区建表落地的过程。3.1 表结构设计play_log 记录用户每次播放视频的行为核心字段包括id自增主键、video_id视频 ID、user_id用户 ID、play_date播放日期、play_start_time播放发生时间、play_duration播放时长、device_type设备类型、channel来源渠道、ip客户端 IP。因为分区键必须包含在主键里我把主键设计成联合主键PRIMARY KEY (id, play_date)。业务上同一视频可以被不同用户反复播放video_id不需要唯一约束也就没有画蛇添足加唯一索引。高频查询是“按 video_id 查某时间范围播放量”和“按 user_id 查播放历史”所以建了对应的两个辅助索引。辅助索引有一个额外好处二级索引的叶子节点会自动带上主键值而主键里有play_date所以这两个索引在按时间范围统计时有机会减少回表。3.2 建表 SQL按天 RANGE 分区建表 SQL 如下按天分区建表时把近期和未来的分区都提前建好CREATE TABLE play_log ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 自增主键, video_id BIGINT NOT NULL COMMENT 视频ID, user_id BIGINT NOT NULL COMMENT 用户ID, play_date DATE NOT NULL COMMENT 播放日期分区键, play_start_time DATETIME NOT NULL COMMENT 播放发生时间, play_duration INT NOT NULL DEFAULT 0 COMMENT 播放时长秒, device_type TINYINT NOT NULL DEFAULT 0 COMMENT 设备类型 1Web 2App 3小程序, channel VARCHAR(32) NOT NULL DEFAULT COMMENT 来源渠道, ip VARCHAR(64) NOT NULL DEFAULT COMMENT 客户端IP, PRIMARY KEY (id, play_date), KEY idx_video_date (video_id, play_date), KEY idx_user_date (user_id, play_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 PARTITION BY RANGE (TO_DAYS(play_date)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)), PARTITION p202503 VALUES LESS THAN (TO_DAYS(2025-04-01)), PARTITION p202504 VALUES LESS THAN (TO_DAYS(2025-05-01)) );这里有三个点需要解释清楚为什么用TO_DAYS(play_date)而不是直接用play_dateRANGE 分区要求分区表达式返回整数DATE 类型不能直接作为分区键参与边界比较TO_DAYS()把日期转成天数整数这是官方文档给出的标准做法。类似的还有YEAR()、UNIX_TIMESTAMP()但UNIX_TIMESTAMP()在 5.7 某些小版本上有时区兼容性问题我基本只用TO_DAYS()。分区边界为什么是下个月 1 号VALUES LESS THAN是不含上界的VALUES LESS THAN (TO_DAYS(2025-02-01))表示存放所有play_date 2025-02-01的数据也就是 2025 年 1 月的数据。最后一个分区必须留一个“未来边界”否则插入边界之后的数据会因为找不到分区直接报Table has no partition for value ...错误。生产环境里要配合自动建分区任务确保永远有一个分区能容纳未来数据。数据是怎么落进分区的引擎在插入时对分区键算一遍表达式比如TO_DAYS(2025-03-15)然后按边界比较找到p202503这个分区写入对应分区文件。整个过程对应用透明业务 SQL 完全不用关心分区存在。3.3 查询怎么才能命中分区分区表的核心收益来自分区裁剪——优化器只扫描命中分区。举个例子按周统计播放量的 SQLEXPLAIN SELECT DATE(play_start_time) AS play_date, COUNT(*) AS play_cnt FROM play_log WHERE play_date 2025-03-01 AND play_date 2025-03-08 GROUP BY DATE(play_start_time)\GMySQL 5.7 的EXPLAIN结果里有partitions列会列出实际扫描的分区。如果查询写对这列只出现p202503如果没写分区键条件就会列出全部分区。5.7 里还支持EXPLAIN PARTITIONS SELECT ...这种旧写法输出更直观但官方已标记为 deprecated建议直接用普通EXPLAIN看partitions列。我总结出三条保证分区裁剪生效的经验WHERE 里带分区键并写成范围形式。play_date 2025-03-01 AND play_date 2025-03-08是最好识别的写法BETWEEN也可以。不要在分区键上套函数。WHERE DATE_FORMAT(play_date, %Y-%m-%d) 2025-03-01看着没问题但函数包裹列后优化器无法把条件映射到分区边界裁剪直接失效。注意隐式类型转换。分区键是 DATE 类型条件传字符串 2025-03-01 一般没问题但传不规范格式可能导致边界无法匹配尽量保持类型一致。3.4 上线前先做这几项验证分区表上线不是改完 DDL 就完事我当时的验证清单是这样的建表后立即查分区信息SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMAvideo_db AND TABLE_NAMEplay_log确认分区数量和边界正确。验证分区裁剪把核心统计 SQL 全部跑一遍EXPLAIN确认partitions列只出现预期分区没有全扫。验证写入路由分别插入当天、未来边界附近的数据再用SELECT * FROM play_log PARTITION (p202503)确认数据落在正确分区。压测分区键开销分区键的表达式计算会增加少量 CPU 开销写入量大时要观察峰值写入性能正常情况下影响很小。跑一遍备份和恢复演练确认物理备份工具能正确处理分区表归档流程也能按预期导出目标分区。4. 分区维护实操自动建分区、归档和清理分区建好只是第一步真正的挑战在运维。流水表的分区必须持续向前滚动历史分区则要按保留策略归档清理这些不能靠人肉完成。4.1 存储过程 事件调度器自动建分区play_log 按天分区如果哪天忘了给未来建新分区当天写入超过最后一个边界就会直接报错。我写了一个存储过程每天自动确保未来两天内有可用分区DELIMITER $$ CREATE PROCEDURE sp_add_play_log_partition() BEGIN DECLARE max_part_date DATE; DECLARE next_date DATE; DECLARE part_name VARCHAR(16); DECLARE part_desc VARCHAR(64); SELECT FROM_DAYS(MAX(CAST(PARTITION_DESCRIPTION AS UNSIGNED))) INTO max_part_date FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA video_db AND TABLE_NAME play_log; WHILE max_part_date DATE_ADD(CURDATE(), INTERVAL 2 DAY) DO SET next_date DATE_ADD(max_part_date, INTERVAL 1 DAY); SET part_name CONCAT(p, DATE_FORMAT(next_date, %Y%m%d)); SET part_desc CONCAT(, TO_DAYS(next_date), ); SET ddl CONCAT( ALTER TABLE play_log ADD PARTITION (PARTITION , part_name, VALUES LESS THAN (, part_desc, )) ); PREPARE stmt FROM ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET max_part_date next_date; END WHILE; END$$ DELIMITER ;这段存储过程的关键逻辑在INFORMATION_SCHEMA.PARTITIONS查询里。PARTITION_DESCRIPTION字段存的是分区上界的实际值比如TO_DAYS(2025-04-01)会存成一个整数天数用FROM_DAYS()转回日期再判断是否需要补分区。之所以把标准定成“未来两天”是为事件调度器偶尔没执行留一个缓冲第二天还能自动补上。然后创建一个事件每天凌晨 2 点调用SET GLOBAL event_scheduler ON; CREATE EVENT ev_daily_add_play_log_partition ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 02:00:00 ON COMPLETION PRESERVE DO CALL sp_add_play_log_partition();这里有个容易忽略的点event_scheduler默认可能是 OFF要先打开并且把event_schedulerON写进 my.cnf否则 MySQL 重启后会失效。另外事件只需要在主库建从库会同步 DDL不用重复跑。4.2 历史数据归档与清理视频系统的播放记录一般保留几个月到一年超过保留期的要清理。分区表的清理特别优雅一条语句ALTER TABLE play_log DROP PARTITION p202401;这条 DDL 直接删除整个分区文件速度快不会像 DELETE 那样产生巨量 binlog、引发主从延迟。如果不想物理删除而是先归档可以先把目标分区数据导入归档表确认没问题后再 DROP-- 按原表结构建一张归档表并取消分区 CREATE TABLE archive_db.play_log_202401 LIKE video_db.play_log; ALTER TABLE archive_db.play_log_202401 REMOVE PARTITIONING; -- 把目标分区数据搬过去 INSERT INTO archive_db.play_log_202401 SELECT * FROM video_db.play_log PARTITION (p202401); -- 确认归档无误后删除原分区 ALTER TABLE video_db.play_log DROP PARTITION p202401;这段流程我在生产环境跑过多轮。注意批量插入几百万行会有一定 IO 压力建议放在业务低峰期并且执行前先SHOW PROCESSLIST确认没有长事务占用。4.3 维护操作的注意事项写一下我在生产环境积累的几个要点分区 DDL 会持有元数据锁。ALTER TABLE ... ADD PARTITION或DROP PARTITION执行期间会阻塞同表的部分 DML必须放在低峰期执行并且执行前检查是否存在长事务。分区数量和表文件数直接挂钩。每新增一个分区InnoDB 就多一个.ibd文件。分区太多会造成文件句柄占用过高、表打开变慢、崩溃恢复时间变长。MySQL 5.7 单表允许最多 8192 个分区这是上限不是建议值实际到几百个分区运维就会开始吃力。如果发现分区粒度太细可以考虑把日分区改成月分区。备份策略要跟上。物理备份比如 Percona XtraBackup对分区表透明逻辑备份则整体导出不区分分区。想单独归档某个分区就用 4.2 里的导出方式。环境差异要注意。MySQL 5.7 的分区特性在 x86 和 ARM64 平台上行为一致但部署参数会有差别。如果是在 ARM64 服务器上跑 5.7安装配置时尤其要检查open_files_limit、table_open_cache这些参数很多云厂商的默认配置偏保守分区一多就容易触碰文件句柄上限。5. 常见问题与排查技巧实录把线上踩过的、帮别人排查过的分区表问题整理成一份巡检手册按发生频率排序。5.1 EXPLAIN 显示全分区扫描问题出在哪这是分区表上线后最常遇到的困惑明明建了分区查询还是很慢一看执行计划partitions列列了全部分区。按下面的顺序排查基本能定位WHERE 里有没有分区键没有一定全扫。特别是用了 ORM 的代码可能只传了video_id没传play_date。分区键上有没有套函数有函数包装就会失效改成范围比较。是不是 JOIN 导致的关联查询时如果驱动表不是分区表或者分区键条件没有直接作用在分区表的过滤条件上5.7 的优化器经常无法完成裁剪。可以把 SQL 改写先缩小区间再关联。有没有隐式类型转换分区键是整数条件传字符串或者日期字段传了不规范的字符串都会影响边界匹配。最容易出问题的 SQL 模型是这种-- 错误函数包裹分区键 SELECT COUNT(*) FROM play_log WHERE DATE_FORMAT(play_date, %Y-%m-%d) 2025-03-01; -- 推荐范围条件 SELECT COUNT(*) FROM play_log WHERE play_date 2025-03-01 AND play_date 2025-03-02;5.2 分区键为 NULL数据全堆在最小分区RANGE 分区对 NULL 的处理很特殊NULL 被视为比所有值都小统一路由到第一个分区。线上经常出现的情况是老接口没传日期play_date默认为 NULL脏数据全写进第一个分区那个分区越涨越大。解决办法分两层。表结构上把分区键设为NOT NULL DEFAULT给一个业务兜底日期应用层面在所有写入入口做参数校验没有日期就不允许入库。改造存量系统时尤其要两边一起做历史接口太多单靠代码层很难堵住所有口子。5.3 建分区表报唯一键错误如果建表或改造旧表时看到ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function原因就是我前面说的硬约束。处理方式二选一联合主键把分区键塞进主键比如PRIMARY KEY (id, play_date)。把业务唯一索引改成普通索引。原来UNIQUE KEY uk_video (video_id)没法把分区键加进去的话就改成KEY idx_video (video_id)唯一性交给业务层或幂等逻辑保证。用ALTER TABLE改造存量表也是同一个逻辑改造前先把表上的所有唯一索引列出来检查一遍。5.4 分区数量太多文件句柄被打满有段时间服务器上频繁报Too many open files排查下来就是分区表太多七八张表都做了分区每张表又有大半年日分区加起来几百个.ibd文件撞上了open_files_limit。措施操作调大文件句柄限制适当调大open_files_limit同步确认系统 ulimit 允许控制分区粒度日分区过细就改成按周或按月分区数量立刻降下来及时清理过期分区维护任务里加 DROP 过期分区别让历史分区无限膨胀关注 table_open_cache每个分区类似一张表分区多了会影响打开表缓存命中率6. 收尾分区之外的一些心里话最后聊聊分区之外的东西算是一点个人体会。6.1 分区不是什么万能药分区方案解决了我那套视频系统的燃眉之急但我现在回头看依然坚持一个态度分区是让大表“可控”的手段不是让烂 SQL “变快”的魔法。分区键没有出现在 WHERE 里该慢还是慢业务持续增长到单实例容量上限分区也替代不了真正的水平扩展。它解决的问题非常具体——单表数据量大导致查询、清理、备份都吃力。6.2 我在实战里的三条建议如果你也准备在 MySQL 5.7 上做分区改造我建议按这三条来先试点再铺开。挑播放记录这种“写入大、查询模式稳定”的表先做不要一把梭把评论表、日志表、视频信息表全改了。验证分区裁剪确实生效、维护任务稳定之后再逐步推广。分区键优先覆盖 80% 的核心查询。设计阶段就把业务里的高频 SQL 列出来看哪个过滤维度出现次数最多不要凭感觉选。维护任务要配上监控告警。自动建分区的事件、清理过期分区的任务都要有日志和告警。哪天事件调度器没跑至少能在分区写满前发现问题而不是等线上报Table has no partition for value才手忙脚乱。我在实际运维中最大的体会是分区让 DBA 和开发都轻松了但也多了一批必须盯住的“定时活”。只要把自动建分区和过期清理这两件事做成常态化分区表基本就能稳定跑下去。