MySQL / 系统课时

估算时间与临时空间

估算时间与临时空间

表结构变更上线前,最容易低估的是工作量、持续时间和峰值磁盘,而不是 SQL 语法。本节用表与索引体量、有效吞吐和临时对象建立可复核的时间与空间预算。

学习目标

  • 判断一次 DDL 是元数据变更、索引构建还是需要重建整表
  • 用表与索引体量、有效吞吐估算执行时间区间
  • 计算临时空间、在线变更日志和安全余量,给出峰值磁盘预算

先判定真正要处理的数据量

估时前先确认变更路径。ALGORITHM=INSTANT 一类元数据操作通常不搬运整表;需要构建二级索引的在线 DDL 会扫描现有数据并生成新索引;重建表的操作还要写出新的表和索引。路径不同,实际工作量可能相差一个数量级。

先读取目标表的逻辑体量。下面示例以 app.orders 为目标表,在真实环境中替换库表名即可。

sql
SELECT
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length,
    data_free,
    ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gib
FROM information_schema.tables
WHERE table_schema = 'app'
  AND table_name = 'orders';

预期结果

text
+--------------+------------+------------+-------------+--------------+-----------+-----------+
| table_schema | table_name | table_rows | data_length | index_length | data_free | total_gib |
+--------------+------------+------------+-------------+--------------+-----------+-----------+
| app          | orders     | <VALUE>    | <VALUE>     | <VALUE>      | <VALUE>   | <VALUE>   |
+--------------+------------+------------+-------------+--------------+-----------+-----------+

table_rows 对 InnoDB 可能是估算值,因此主要用于辅助判断规模;估算重建成本时优先使用 data_length + index_length,再根据 ALTER 是否新增、删除或重写索引修正工作量。

DDL 时间预算从工作量到执行窗口的估算关系

把容量换成时间预算

不要直接拿磁盘标称带宽做除法。DDL 与线上查询、后台刷脏、复制和其他任务共享 CPU 与 IO,真正有用的是业务高峰或相似窗口下可持续的有效吞吐。可以从历史监控或预发布压测取得稳定吞吐,并保留安全折扣。

实用模型是:基础时间 = 需要处理的字节量 / 有效吞吐。计划时间再乘历史安全系数,用来覆盖排序、索引页写入、并发 DML 合并、缓存命中变化以及尾部元数据锁等待。安全系数应来自同类变更历史,而不是固定套一个百分比。

继续用具体数字计算:表数据量为 120 GiB,现有索引为 40 GiB,计划重建后需处理的新对象按 160 GiB 估算;预发布实测有效吞吐为 80 MiB/s。则基础时间为 160 GiB ÷ 80 MiB/s = 2048 秒,约 34.1 分钟。若同类变更历史安全系数为 1.4,计划时间约为 47.7 分钟。排窗时还应向上取整,并额外留出观测和尾部时间。

估算时至少给出区间而不是单点:乐观值用于理解纯处理下界,计划值用于排变更窗口,最坏值用于判断是否需要拆分、限流或换工具。若计划值已经逼近维护窗口上限,就不应靠现场跑快一点来兜底。

估算临时空间与峰值占用

在线不等于没有临时空间。重建表可能同时存在旧表和新表;创建或重建索引可能产生排序临时文件;允许并发写入时还需要在线变更日志,直到 DDL 尾部把这些修改应用到新结构。临时文件还可能落在数据目录、tmpdir 或 InnoDB 专用临时目录,因此要分别检查挂载点余量。

sql
SHOW VARIABLES
WHERE Variable_name IN (
    'tmpdir',
    'innodb_tmpdir',
    'innodb_online_alter_log_max_size'
);

预期结果

text
+-----------------------------------+------------------+
| Variable_name                     | Value            |
+-----------------------------------+------------------+
| innodb_online_alter_log_max_size  | <VALUE>          |
| innodb_tmpdir                     | <PATH>           |
| tmpdir                            | <PATH>           |
+-----------------------------------+------------------+

空间预算可以按“现有对象 + 变更新增对象 + 临时文件 + 在线变更日志实际增长 + 业务自然增长 + 保底余量”计算。对于整表重建,新对象上界至少按新表完整数据与索引考虑;对于只新增索引的操作,则重点估算新索引、排序文件和在线变更日志。

用一组完整算例复核:当前数据为 120 GiB,现有索引为 40 GiB,所以当前对象合计 160 GiB;重建后新对象暂按 160 GiB 估算;排序临时文件峰值 8 GiB;并发写入导致在线变更日志实际累计 4 GiB;配置的 innodb_online_alter_log_max_size 上限按 8 GiB 作为门槛校验;变更窗口内业务自然增长 6 GiB;保底余量 30 GiB。因此峰值磁盘预算约为 160 + 160 + 8 + 4 + 6 + 30 = 368 GiB。

这里要区分“实际占用”和“配置上限”:在线变更日志上限为 8 GiB,不代表这次变更一定消耗 8 GiB;实际值取决于 DDL 持续时间、并发写入速率和具体实现。预算应覆盖可预期峰值,同时把配置上限作为额外的失败边界。

data_free 描述的是表空间内部状态,不等同于文件系统空闲空间,不能直接当作可用磁盘。还要确认 tmpdir、innodb_tmpdir 与数据目录是否位于不同挂载点;数据盘有空间,不代表排序临时目录一定安全。

形成可上线的预算

上线单至少保留四个数字:目标表与索引体量、计划处理字节量、计划执行时长、最低保留磁盘空间;再明确允许的业务增长量,以及达到哪个磁盘水位或执行时长后必须终止变更。

评估项 计算依据 上线判断
处理字节量 数据 120 GiB + 索引 40 GiB,按变更路径修正 明确是扫描量、写入量还是两者
执行时间 160 GiB ÷ 80 MiB/s × 1.4 ≈ 47.7 分钟 计划值必须落在维护窗口内
临时空间 排序文件 8 GiB + 在线日志实际峰值 4 GiB 分挂载点核对,不只看总容量
峰值磁盘 160 + 160 + 8 + 4 + 6 + 30 = 368 GiB 低于预设保底线才允许上线

估算的目标不是给出精确到分钟的承诺,而是提前暴露最坏情况。只要无法解释时间预算采用了什么吞吐、空间预算覆盖了哪些临时对象,这次 DDL 就还没有达到可上线状态。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

估算时间与临时空间

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通