这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 容量规划和在线变更决定数据库能否长期稳定运行。很多故障不是 SQL 写错，而是数据量进入新阶段、连接模型改变、磁盘增长没有被预测、DDL 没有评估锁和复制影响。本章把容量、表结构变更、数据归档和大规模治理放到同一个生产流程里。
33.1 容量规划总览
容量规划要同时看四类资源：
计算资源
|-- CPU：解析、优化、执行、锁等待重试
+-- 并发：连接数、活跃线程、事务并发
内存资源
|-- Buffer Pool：数据页和索引页
|-- 连接内存：sort buffer、join buffer、临时表
+-- 其他全局内存
存储资源
|-- 数据和索引文件
|-- Binlog / Redo / Undo
+-- 临时空间与备份
链路资源
|-- 磁盘 IOPS / 吞吐
|-- 网络带宽
+-- 主从复制延迟
容量规划不是一次性估一个磁盘大小，而是持续回答：
- 当前水位是多少？
- 峰值是多少？
- 增长速度是多少？
- 什么时候到达阈值？
- 到阈值前如何扩容？
- 扩容期间是否影响可用性？
33.2 数据容量评估
33.2.1 查看库表大小
SELECT
table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb,
ROUND(SUM(data_length) / 1024 / 1024 / 1024, 2) AS data_gb,
ROUND(SUM(index_length) / 1024 / 1024 / 1024, 2) AS index_gb,
SUM(table_rows) AS estimated_rows
FROM information_schema.tables
WHERE table_schema NOT IN
('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY size_gb DESC;
查看大表：
SELECT
table_schema,
table_name,
engine,
table_rows,
ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb,
ROUND(data_free / 1024 / 1024 / 1024, 2) AS free_gb
FROM information_schema.tables
WHERE table_schema NOT IN
('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY data_length + index_length DESC
LIMIT 20;
TABLE_ROWS 是估算值，精确行数需要 COUNT(*)。容量评估通常不需要精确到每一行，但要以趋势数据为依据。
33.2.2 单行大小估算
示例表：
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
amount DECIMAL(12, 2) NOT NULL,
remark VARCHAR(500) NULL,
created_at DATETIME(3) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB;
粗略估算：
行记录：约 80 到 150 字节
主键 B+Tree 页开销：约 15% 到 30%
uk_order_no：约 40 字节/行，再加页开销
idx_user_created：约 20 字节/行，再加页开销
假设 1 亿行，逻辑数据可能是几十 GB，加索引后可能达到上百 GB。实际大小必须以表统计和监控为准，估算只用于提前设计。
33.2.3 增长预测
建议每天记录：
日期
库大小
表大小
行数
Binlog 增量
备份大小
QPS / TPS
连接数
磁盘使用率
简单预测：
日均增长 = 最近 30 天数据增量 / 30
可用天数 = (容量上限 - 当前使用) / 日均增长
示例：
磁盘上限：2000 GB
当前使用：1200 GB
安全阈值：80%，即 1600 GB
剩余可用空间：400 GB
最近 30 天日均增长：8 GB
到达阈值天数：400 / 8 = 50 天
不要等磁盘到 90% 才行动。扩容、重建、备份、在线变更和回滚都需要提前预留空间。
33.3 连接与内存容量
33.3.1 连接数估算
实际最大连接 = 应用实例数 × 每实例连接池上限
示例：
应用实例：40 台
每台最大连接：50
理论最大连接：2000
MySQL max_connections：3000
这仍不是安全设计，还要考虑：
- 滚动发布时新旧实例并存；
- 运维工具和临时任务；
- 主从切换后的连接集中；
- 故障后的重试风暴；
- 从库可能挂载更多只读服务；
max_connections不是越高越好。
相关配置：
max_connections：覆盖正常峰值和可控故障重试
thread_cache_size：减少线程创建开销
应用连接池：统一治理最大值
语句超时：避免查询长期占用连接
事务边界：尽早提交，不要等待外部调用
33.3.2 内存估算
常用简化模型：
可用内存
≈ 服务器内存
- 操作系统预留
- MySQL 进程基础开销
- 每连接缓冲峰值
- 其他进程
innodb_buffer_pool_size
≈ 可用内存的 50% 到 70%
示例：
服务器内存：64 GB
操作系统和监控预留：8 GB
复杂连接按 4 MB 估算
如果允许 3000 个复杂连接同时到达，理论峰值可达 12 GB
这说明不能简单把 Buffer Pool 设置为 48 GB 后仍允许无限复杂查询。要么控制活跃连接，要么限制排序和临时查询，要么提高机器规格并重新分配。
查看变量：
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'max_connections';
会话级缓冲按需分配，连接建立时不会一次性全部分配，但极端 SQL 仍可能放大内存风险。
33.4 磁盘与 IO 规划
33.4.1 关键指标
| 指标 | 含义 |
|---|---|
| 使用率 | 磁盘空间水位 |
| IOPS | 每秒 IO 次数 |
| 吞吐 | 每秒传输数据量 |
| 延迟 | 单次 IO 耗时 |
| 读放大 | 一次逻辑读带来的物理 IO |
| 写放大 | 逻辑写入带来的实际落盘 |
观察：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending%';
SHOW GLOBAL STATUS LIKE 'Innodb_log_writes';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
33.4.2 空间组成
除表数据外，还要预留：
- Binlog 保留期空间；
- Redo Log 空间；
- Undo 表空间；
- 临时表空间；
- 备份临时目录；
- DDL 或在线变更工具的磁盘副本；
- 慢日志和错误日志；
- 云盘快照和性能抖动余量。
示例：
数据与索引：800 GB
Binlog 保留 7 天，每天 50 GB，需要 350 GB
在线变更工具可能额外需要 1 到 1.5 倍表空间
安全水位：70% 到 80%
如果机器只有 1 TB 磁盘，就无法同时承担这张表的大变更，需要临时扩容、分批处理或更换方案。
33.5 Online DDL 基础
MySQL 8.0 的 ALTER TABLE 支持不同算法：
| 算法 | 典型场景 | 特点 |
|---|---|---|
INSTANT |
加列、修改默认值等 | 只改元数据，秒级 |
INPLACE |
加二级索引等 | 引擎内执行，减少锁表 |
COPY |
修改列类型等 | 复制表数据，代价高 |
显式指定算法：
ALTER TABLE shop.orders
ADD COLUMN channel VARCHAR(16) NOT NULL DEFAULT 'APP',
ALGORITHM=INSTANT;
如果 MySQL 不支持指定算法，会报错，而不是悄悄降级为高风险方式：
ALTER TABLE shop.orders
MODIFY COLUMN channel VARCHAR(32) NOT NULL DEFAULT 'APP',
ALGORITHM=INSTANT;
改变列类型通常不能 INSTANT，应评估 INPLACE 或 COPY。
33.5.1 常见操作分类
通常 INSTANT：
末尾增加列
增加或删除默认值
修改列名（受版本和条件限制）
设置或删除虚拟列
通常 INPLACE：
增加二级索引
删除二级索引
重命名索引
增加 FULLTEXT 索引（有额外限制）
可能 COPY 或复杂重建：
修改主键
修改列类型
改变字符集
转换存储引擎
增加 AUTO_INCREMENT 属性
不同 MySQL 8.0 小版本对 Instant DDL 的支持范围有增强。执行前必须查阅当前版本文档，并在同版本测试环境验证。
33.5.2 锁语义
ALTER TABLE shop.orders
ADD INDEX idx_merchant_created (merchant_id, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;
LOCK=NONE 表示不允许阻塞 DML；如果当前操作做不到，会失败。这比线上意外锁表更安全。
Online DDL 的“在线”不等于“零影响”：
- 重建占用 CPU、IO 和内存；
- 需要记录在线变更日志；
- 大表会拉长 DDL 时间；
- 主库执行时间长，从库回放也可能延迟；
- DDL 开始前后可能需要短暂元数据锁；
- 长事务会导致 DDL 等待 MDL；
- DDL 等待又可能阻塞后续查询。
33.5.3 Metadata Lock 风险
典型事故链：
长查询持有表 MDL 读锁
-> DDL 等待 MDL 写锁
-> 后续所有查询排队
-> 连接耗尽
-> 业务不可用
设置 DDL 会话锁等待：
SHOW VARIABLES LIKE 'lock_wait_timeout';
SET SESSION lock_wait_timeout = 10;
执行 DDL 前检查长事务和长查询：
SELECT trx_id, trx_state, trx_started,
trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT * FROM sys.session
WHERE conn_id IS NOT NULL
ORDER BY statement_latency DESC;
建议：
- DDL 会话设置较短
lock_wait_timeout； - 低峰执行；
- 先在测试环境估算时间；
- 监控 MDL 等待；
- 拿不到锁时快速失败，而不是无限排队。
33.6 第三方在线变更工具
33.6.1 为什么需要工具
当表很大时，ALTER TABLE 可能面临：
- 主库执行时间不可控；
- 从库延迟明显；
- 磁盘空间不足；
- 失败后回滚代价高；
- 无法限速；
- 无法暂停和续跑；
- 云平台或主从架构有限制。
常用工具：
| 工具 | 思路 |
|---|---|
| gh-ost | 基于 Binlog 的无触发器在线变更 |
| pt-online-schema-change | 创建影子表和触发器，同步增量 |
33.6.2 gh-ost 基本流程
1. 创建幽灵表和相关控制表
2. 按主键分批读取原表数据
3. 写入应用变更后的幽灵表
4. 同时消费 Binlog 增量
5. 校验、限速、观察水位
6. 原子切换原表与幽灵表
示例：
gh-ost \
--host=127.0.0.1 --port=3306 --user=change_user \
--database=shop --table=orders \
--alter="ADD COLUMN channel VARCHAR(16) NOT NULL DEFAULT 'APP'" \
--chunk-size=1000 \
--max-load=Threads_running=50 \
--critical-load=Threads_running=200 \
--cut-over=default \
--allow-on-master \
--execute
生产建议：
- 先在从库或非高峰演练；
- 保留执行前的 dry run 输出；
- 配置
max-load和critical-load； - 确认磁盘可容纳影子表；
- 确认 Binlog 配置和工具权限；
- 明确切流窗口；
- 准备失败终止和重跑方案。
33.6.3 pt-osc 基本流程
1. 创建影子表
2. 在影子表执行 ALTER
3. 在原表创建 INSERT / UPDATE / DELETE 触发器
4. 分批拷贝存量数据
5. 原子 rename 切换
示例：
pt-online-schema-change \
--alter "ADD COLUMN channel VARCHAR(16) NOT NULL DEFAULT 'APP'" \
D=shop,t=orders \
--host=127.0.0.1 --port=3306 \
--user=change_user --ask-pass \
--chunk-size=1000 \
--max-load=Threads_running=50 \
--critical-load=Threads_running=200 \
--progress=time,30 \
--execute
触发器方案要额外关注写入放大和高并发下的触发器开销。工具选择应结合版本、云平台限制、团队经验和表写入模式。
33.7 在线变更流程
33.7.1 变更前检查
1. 表大小、行数、每日增长
2. 当前 QPS / TPS 和峰值时间
3. 是否有长事务和长 SQL
4. 主从延迟水位
5. 磁盘空间和 IOPS 余量
6. 索引是否已被现有查询使用
7. 是否存在同名临时表或工具残留
8. 测试环境同版本演练结果
9. 回滚方案
10. 停止条件
检查索引使用：
SELECT object_schema, object_name, index_name,
count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'shop'
AND object_name = 'orders'
ORDER BY count_star DESC;
MySQL 8.0 可以先隐藏索引：
ALTER TABLE shop.orders ALTER INDEX idx_old INVISIBLE;
观察无影响后删除：
DROP INDEX idx_old ON shop.orders;
33.7.2 变更中监控
必须监控：
Threads_running
CPU / IO 利用率
磁盘空间
主从延迟
锁等待
错误日志
工具复制进度
业务接口 P99 和错误率
停止条件示例：
Threads_running 连续 5 分钟高于 100
主从延迟高于 30 秒
磁盘剩余低于 20%
核心接口错误率高于 0.5%
工具进度长期不推进
出现无法解释的锁等待
33.7.3 变更后验证
SHOW CREATE TABLE shop.orders\G
EXPLAIN
SELECT *
FROM shop.orders
WHERE user_id = 10001
AND created_at >= '2026-08-01';
业务验证：
- 核心写入接口正常；
- 新字段默认值符合预期；
- 查询计划使用新索引；
- 无新增慢 SQL；
- 主从延迟恢复；
- 监控指标回落；
- 清理临时表和工具进程。
33.8 大表数据治理
33.8.1 归档策略
| 策略 | 适用场景 |
|---|---|
| 按时间归档 | 订单、日志、流水 |
| 按状态归档 | 已关闭、已取消数据 |
| 按租户拆分 | 大客户独立存储 |
| 冷热分层 | 热表保近期，冷表保历史 |
| 直接过期 | 明确保留期的行为日志 |
归档前必须确认：
- 法规和业务保留期；
- 报表是否依赖历史数据；
- 审计是否需要明细；
- 用户是否还能查询；
- 关联表是否同步归档；
- 归档存储是否可恢复。
33.8.2 分批删除
不推荐一次性删除：
DELETE FROM audit_logs
WHERE created_at < '2025-01-01';
推荐分批：
DELETE FROM audit_logs
WHERE id IN (
SELECT id
FROM (
SELECT id
FROM audit_logs
WHERE created_at < '2025-01-01'
ORDER BY id
LIMIT 1000
) AS t
);
SELECT ROW_COUNT();
循环执行时每批之间可暂停，并观察延迟和锁等待。更稳妥的方案是按主键范围或时间分区删除整个分区。
33.8.3 分区表
示例：
CREATE TABLE audit_logs (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
created_at DATETIME(3) NOT NULL,
level VARCHAR(10) NOT NULL,
message TEXT NOT NULL,
PRIMARY KEY (id, created_at),
KEY idx_created (created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
删除历史分区：
ALTER TABLE audit_logs DROP PARTITION p202607;
分区表不是万能优化。查询条件应能裁剪分区，否则可能扫描更多分区；主键和唯一键也必须满足分区表达式约束。
33.9 评审模板
变更评审模板：
变更标题：
负责人：
评审人：
目标库表：
表大小：
当前 QPS / TPS：
预计执行时间：
执行窗口：
使用算法或工具：
影响 SQL：
锁风险：
复制风险：
磁盘余量：
停止条件：
回滚方案：
验证 SQL：
通知对象：
容量报表模板：
实例：
角色：主库 / 从库
CPU 峰值：
内存峰值：
磁盘使用：
IOPS 峰值：
IO 延迟：
最大连接：
活跃连接：
QPS / TPS：
最大表：
90 天增长：
预计达到阈值日期：
扩容建议：
负责人：
本章小结
容量规划要覆盖 CPU、内存、连接、磁盘空间、IO 和复制链路，并用增长趋势预测阈值。Online DDL 的算法和锁语义决定变更风险，INSTANT 不适用于所有场景，INPLACE 也不等于零影响。大表变更前必须评估长事务、MDL、磁盘、主从延迟和回滚方案，必要时使用 gh-ost 或 pt-osc 分批迁移和限速。历史数据要通过归档、分区和分批删除治理，所有变更都应有评审模板、停止条件和验证 SQL。
思考题
- 为什么
max_connections很高反而可能带来风险？ - 给一张 500 GB、日增 5 GB 的表做 DDL，需要评估哪些资源？
- Online DDL 的
LOCK=NONE意味着什么？它为什么不能消除所有影响？ - 长查询、DDL 和后续查询之间为什么会形成排队链？
- 为一次“给订单表增加索引”的变更写完整评审单。