MySQL存储空间与索引运维的实战指南

适用版本：mysql 5.7 / 8.0（innodb）
读者对象：dba、运维、后端同学
核心结论先放在这里：库空间看磁盘和 information_schema；索引不会整棵装进内存；生产扩盘通常不用重启 mysql；optimize table 是整表重建，大表上很慢。

线上磁盘告急时，最常见的三个误判是：

1. 以为「删了数据，文件就会变小」
2. 以为「索引会常驻内存，所以索引越大查询越快」
3. 看到 data_length ≈ index_length 就觉得统计坏了，准备整库 optimize

这三项里，真正该先做的永远是同一件事：把空间账算清，再决定扩盘、删索引，还是重建表。

1. 运维视角：空间问题分三层

mysql 的「占用空间」经常被混成一句话。生产上必须拆开：

三层对不上，是日常排障的常态：

• 控制台刚扩完盘，df 还是旧值 —— 文件系统没 resize
• information_schema 显示表不大，磁盘却满了 —— binlog、临时文件、undo、备份打满了另一条路径
• delete 了 50% 行，.ibd 几乎没变 —— innodb 只是把页标成可复用，没有还给操作系统

原则：先 df 和数据目录，再看库表统计，最后才谈 optimize 和删索引。

2. 怎么看某个库、某张表占了多少空间

2.1 某个库一共多大

select
  table_schema as db_name,
  count(*) as table_cnt,
  round(sum(data_length) / 1024 / 1024, 2) as data_mb,
  round(sum(index_length) / 1024 / 1024, 2) as index_mb,
  round(sum(data_free) / 1024 / 1024, 2) as free_mb,
  round(sum(data_length + index_length) / 1024 / 1024, 2) as used_mb,
  round(sum(data_length + index_length + data_free) / 1024 / 1024, 2) as total_mb
from information_schema.tables
where table_schema = 'your_db';

innodb 的 table_rows、data_length 是 估算值。量级判断够用；要对账到文件，看磁盘。

2.2 这个库里谁最大

select
  table_name,
  engine,
  table_rows,
  round(data_length / 1024 / 1024, 2) as data_mb,
  round(index_length / 1024 / 1024, 2) as index_mb,
  round(data_free / 1024 / 1024, 2) as free_mb,
  round((data_length + index_length) / 1024 / 1024, 2) as total_mb,
  round(index_length / greatest(data_length, 1), 2) as idx_data_ratio
from information_schema.tables
where table_schema = 'your_db'
order by (data_length + index_length) desc
limit 30;

生产里 80% 的空间问题，都能在前 10 张表里定位完。不要一上来扫全实例做 ddl。

2.3 对比所有业务库

select
  table_schema as db_name,
  count(*) as table_cnt,
  round(sum(data_length + index_length) / 1024 / 1024, 2) as used_mb
from information_schema.tables
where table_schema not in ('mysql', 'information_schema', 'performance_schema', 'sys')
group by table_schema
order by sum(data_length + index_length) desc;

2.4 和磁盘对账

show variables like 'datadir';

独立表空间（innodb_file_per_table=on，现在默认就是）时，库目录可以直接量：

du -sh /var/lib/mysql/your_db
du -sh /var/lib/mysql/your_db/* | sort -h | tail -20

对账时把这些也算进去，它们经常比业务表更先把盘写满：

单表也可以：

show table status from your_db like 'your_table'\g

看 data_length、index_length、data_free。

3. 怎么看索引占用的空间

3.1 按表看：二级索引合计

index_length 就是该表 二级索引 占用的空间。innodb 主键一般算在数据里，不单独计入 这一项。

select
  table_schema as db_name,
  table_name as table_name,
  round(data_length / 1024 / 1024, 2) as data_mb,
  round(index_length / 1024 / 1024, 2) as index_mb,
  round((data_length + index_length) / 1024 / 1024, 2) as total_mb
from information_schema.tables
where table_schema = 'your_db'
order by index_length desc;

看某个库全部索引一共多大：

select
  table_schema,
  round(sum(index_length) / 1024 / 1024, 2) as index_mb
from information_schema.tables
where table_schema = 'your_db'
group by table_schema;

3.2 按「单个索引」估算（mysql 8.0+）

index_length 给不出「idx_user_ctime 到底多少 gb」。innodb 统计表可以估：

select
  s.database_name,
  s.table_name,
  s.index_name,
  round(sum(s.stat_value * @@innodb_page_size) / 1024 / 1024, 2) as index_mb
from mysql.innodb_index_stats s
where s.database_name = 'your_db'
  and s.stat_name = 'size'
group by s.database_name, s.table_name, s.index_name
order by index_mb desc
limit 50;

• stat_name = 'size'：该索引占用的页数
• 乘 innodb_page_size（默认 16kb）≈ 估算大小
• primary 是聚簇索引，和行数据是同一棵树，不要把它和二级索引重复加总去跟 index_length 对

看某个索引：

select
  index_name,
  round(stat_value * @@innodb_page_size / 1024 / 1024, 2) as index_mb
from mysql.innodb_index_stats
where database_name = 'your_db'
  and table_name = 'your_table'
  and index_name = 'idx_xxx'
  and stat_name = 'size';

统计可能滞后。要更接近当前值：

analyze table your_db.your_table;

生产大表上 analyze 也会吃 io，放到低峰。

3.3 和 myisam 不要混着理解

现在生产几乎都是 innodb。用 myisam 的心智模型去读 innodb 的两个 length，会系统性误判。

4. 数据和索引差不多大，正常吗

很常见，不等于统计坏了。

innodb 里：

• data_length ≈ 整行数据 + 主键（聚簇索引）
• index_length ≈ 所有二级索引之和

两者接近，只说明：二级索引加起来，已经和表数据（含主键）一个量级。

4.1 为什么二级索引可以长到这个份上

二级索引叶子节点存的是 索引列 + 主键值（用来回表）。表上有 n 个二级索引，主键就被重复存 n 份。

特别容易把索引做大的情况：

1. 二级索引多（4～8 个常见，再多就容易赶超数据）
2. 主键太宽：uuid、varchar(36)、多列联合主键。每个二级索引都要带着它
3. 索引列很长：varchar(255)、姓名+手机+地址这类组合索引
4. 联合索引字段过多，几乎覆盖半张表
5. 前缀冗余：有 idx(a,b) 又有 idx(a)
6. 行本身很瘦（几个 int），索引相对就会显得很大

一个直观例子：行 100 字节，主键 36 字节 uuid，3 个二级索引各自再带一份主键，仅索引侧就可能接近甚至超过行数据。

4.2 比值怎么读

用前面的 idx_data_ratio（index_length / data_length）：

先找最大的几张表，再 show index：

show index from your_db.your_table;

重点看：列长度、是否重复前缀、主键类型、唯一索引是否多余。

4.3 先确认索引有没有人用

select
  object_schema,
  object_name,
  index_name,
  count_star
from performance_schema.table_io_waits_summary_by_index_usage
where object_schema = 'your_db'
  and count_star = 0
  and index_name is not null
  and index_name != 'primary';

count_star = 0 只说明 自实例启动以来没被用到，不是「永远没用」。刚重启、刚加的索引、只在月末报表用的索引，都会误伤。把它当成 候选清单，对照慢查询、代码、定时任务后再删。

处理优先级（从收益高、风险低往下）：

1. 删确认冗余的索引（有 (a,b) 且查询都能走它，才考虑去掉单独的 (a)）
2. 主键改为 bigint 自增，uuid 放普通唯一索引 —— 能同时缩小 所有 二级索引
3. 超长 varchar 改为前缀索引或哈希列（必须评估能否覆盖查询）
4. 未使用索引列入下个变更窗口

不要因为「索引和数据一样大」就整库 optimize。 那只会把同样大的索引再重建一遍，磁盘和主从都会痛一次，比值几乎不变。

5. 索引加载内存的真实原则

innodb 不会在启动时把整棵索引一次性加载进内存。原则就一句话：

按页按需读入 buffer pool，热页留下，冷页按 lru 淘汰。

5.1 按页加载，不是整棵树

数据和二级索引都按 16kb 页 组成 b+ 树。

• sql 走到哪条路径，才把哪些页读进来
• 根节点、中间节点访问频繁，更容易常驻
• 叶子页在不在内存，取决于有没有被查到

buffer pool 启动后是空的（或从 dump 恢复）。第一次查询才会把相关页读盘。

5.2 按需加载（lazy load）

建了但没被查询用到的索引，不会自动进内存。 它只在写入时增加维护成本，并在磁盘上占空间。

5.3 lru：热页留下，冷页淘汰

buffer pool 用改进版 lru（young / old 两段）：

• 刚读入的页先放 old 区，避免一次全表扫描把热索引挤掉
• 一段时间后再被访问，才升到 young 区
• 内存不够时，从 lru 尾部淘汰最久没用的页

所以：常查的索引页会自然留在内存；很少用的索引即使加载过，也会被换出。

对 buffer pool 来说，主键和二级索引都是页，没有「索引优先于数据」的单独策略。谁被访问得多，谁就占内存。

5.4 容易混淆的几个机制

adaptive hash index

对反复等值查找的热点页，innodb 会在内存里建哈希，把部分 b+ 树查找变成近似 o(1)。这是加速，不是把整棵索引再拷一份；范围扫描用不上。

myisam key buffer

myisam 才更接近「索引专门进内存」：key_buffer_size 只缓存索引，数据靠 os 页缓存。innodb 没有单独的索引缓存，数据和索引共用 innodb_buffer_pool_size。

buffer pool dump（预热）

set global innodb_buffer_pool_dump_at_shutdown = on;
set global innodb_buffer_pool_load_at_startup = on;

关闭时 dump 热点页，启动后再加载。这是把 上次的热页 提前读回来，仍然不是加载全部索引。

5.5 运维含义

1. 索引不是「常驻内存」的保证，只是被访问过的页 可能 在内存里
2. 想让查询稳，关键是工作集（热数据 + 热索引）能放进 buffer pool
3. innodb_buffer_pool_size 通常给机器内存的 50%～70%（本机还跑其他进程就再降）
4. 索引建太多：浪费磁盘、拖慢写入；不会因为建了就自动占满内存

看 buffer pool 里页类型分布（实例很大时这个查询本身也有成本）：

select
  page_type,
  count(*) as pages,
  round(count(*) * @@innodb_page_size / 1024 / 1024, 2) as mb_est
from information_schema.innodb_buffer_page
group by page_type
order by pages desc;

show engine innodb status 里的 buffer pool and memory 更适合日常看命中率、脏页、lru。

6. 生产环境存储是动态扩容的吗，要不要重启

要分开两层：磁盘能不能变大，和 mysql 要不要重启。日常说的「库空间不够了、扩盘」，指的是磁盘容量，不是改 mysql 参数。

6.1 结论

6.2 磁盘层：生产最常见的做法

mysql 只是往文件系统里写 .ibd / redo / binlog。空间够不够，取决于底下这块盘。

云主机云盘（阿里云、aws ebs、腾讯云等）

• 控制台把云盘从 500gb 扩到 1tb，多数支持在线扩容
• 扩完后还要在操作系统里把分区 / 文件系统拉大，例如：

# 示例，具体设备名和文件系统以现场为准
growpart /dev/vda 1
resize2fs /dev/vda1          # ext4
# xfs_growfs /data           # xfs

• mysql 不用重启，新空间马上能写
• 注意：有的「换磁盘类型」（高效云盘 → ssd）可能要卸载盘，那就会中断

lvm

lvextend + resize2fs / xfs_growfs，在线完成，mysql 不用重启。

k8s pvc

storageclass 开启 allowvolumeexpansion: true 后可以改大 pvc。扩的是卷，pod / mysql 一般不用重启，取决于 csi 是否支持在线扩。

物理机直连单盘

单块盘写满了，不能「热变大」。换盘、加盘做 lvm/raid、迁数据目录，通常要停机窗口。

6.3 mysql 自己：文件会自动涨，但不会自动加磁盘

默认 innodb_file_per_table=on：

• 每张表一个 .ibd，数据增多文件自动变大，不用重启、不用人工扩表空间
• 前提是 磁盘还有空闲
• 系统表空间若开了 autoextend，同样自动追加、不重启

所以：

• 表空间文件：自动涨
• 磁盘容量：不会因为 mysql 自动变大；盘满就报 no space left on device

自建 mysql 没有 rds 那种「使用率超 80% 自动加 100gb」。要靠监控 + 人工扩云盘，或脚本调云厂商 api 再 resize2fs。

托管库（阿里云 rds、aws rds 等）可以把自动扩容打开：设阈值、步长、上限和冷却时间。实例一般不重启，按量计费。

6.4 什么时候还是要停机

这些不是「把盘做大」，而是改存储布局：

• 数据目录从 /data1 迁到 /data2
• 单盘改成数据 / binlog / redo 分盘
• 不支持在线扩的存储，或更换磁盘类型需要卸载
• 共享表空间改独立表空间等需要重建的操作

扩容本身通常 不用重启 mysql。重启往往是因为迁盘、换盘类型，或改了需要重启的参数。

6.5 扩盘现场的两个坑

1. 控制台加了容量，df 没变 —— 只扩了云盘，没扩文件系统。这是最高频事故。
2. innodb 删数据不会立刻把空间还给 os —— .ibd 可能还很大，需要重建表才会收缩。盘已经 90%，这时去做 optimize，重建期间还要再占一份表大小，可能直接把盘写爆。

先扩盘，再考虑收缩。永远不要在磁盘剩余不够「一张表大小」时对大表做重建。

7. optimize table 整个过程是不是很慢

对大表来说，往往很慢。慢的是「整表重建」，不是扫一下碎片。

7.1 它实际在干什么

innodb 里：

optimize table t;

基本等价于：

alter table t engine=innodb;
-- 或 alter table t force;

过程大致是：

1. 新建临时表（或 inplace 重建）
2. 把旧表数据 逐行拷过去，同时重建主键和所有二级索引
3. 期间新写入要额外处理（row log / 增量追平）
4. 最后用新表替换旧表，释放旧 .ibd

耗时跟 表大小 + 索引数量 成正比，不是跟「删了多少行」成正比。一张 100gb 的表，即使只删了 1% 数据，也要按 100gb 来重建。

7.2 会慢到什么程度

粗经验（ssd、负载不高，仅供量级感，不能当 sla）：

还会更慢：二级索引多、hdd / 低 iops 云盘、主库持续写入、从库还要再重放一遍 ddl、表上有大字段。

7.3 会不会锁表

这点比「慢」更关键。

mysql 5.6+ innodb 多数情况下是 online ddl：

• 重建期间 还可以 select / insert / update / delete
• 不是整个过程都锁死
• 最后切换瞬间会有 短暂 mdl 锁（秒级，若有长事务可能更久）
• 会多占大约 一份表空间（100gb 表重建时磁盘上可能短暂接近 200gb）
• cpu、io、buffer pool 都会被打高，业务会变慢

所以：不是全程不可用，但是全程很重；结束时有短锁。 有长查询、未提交事务时，最后一下切换会一直等锁，看起来像卡死。

上线前先查：

select * from information_schema.innodb_trx\g
show processlist;

有跑了几小时的事务，先处理事务，再谈 ddl。

7.4 什么时候值得做

先看碎片，而不是凭感觉：

select
  table_name,
  round(data_length/1024/1024, 2) as data_mb,
  round(index_length/1024/1024, 2) as index_mb,
  round(data_free/1024/1024, 2) as free_mb,
  round(data_free / (data_length + index_length + 1) * 100, 2) as frag_pct
from information_schema.tables
where table_schema = 'your_db'
order by data_free desc;

经验上：

• 碎片 5%～10%、data_free 不大：不必优化
• 大批量 delete 后 .ibd 明显虚高、磁盘确实紧：才考虑重建
• 小表可以低峰直接 optimize
• 大表不要在主库裸跑

7.5 生产上更稳妥的做法

1. pt-online-schema-change / gh-ost：影子表 + 追增量，可限流、可暂停、切换更可控
2. 从库重建再切换：避免主库扛重建 io
3. 分区表走 drop partition：按时间删冷数据是秒级还空间，比 optimize 合适得多
4. 大批量删除后换「新建表 + 导热数据 + rename」：和 optimize 本质类似，但可以自己分批、限速

delete 很快；把磁盘还给操作系统 才是这场重建。这就是为什么大家会觉得 optimize 特别慢。

8. 生产处置顺序：先观察，再动手

磁盘告警时，建议按这个顺序，而不是直接 optimize。

1. df / 云监控：哪块盘、使用率、inode
2. du 数据目录：.ibd、binlog、临时文件、undo、备份谁最大
3. information_schema：哪个库、哪张表、数据 vs 索引 vs data_free
4. innodb_index_stats：是某几个宽索引，还是整表数据
5. 判断动作：
   - binlog 过多     → 立刻：调 expire_logs_days / binlog_expire_logs_seconds
   - 磁盘真不够      → 先在线扩盘 + resize 文件系统
   - 碎片极大且磁盘已有余量 → 低峰重建（工具 / 从库）
   - 索引比数据还大  → 审索引和主键，不要先 optimize
   - 长事务顶着 undo → 先杀/等事务，收缩是后话

一句话对照：

9. 监控与容量规划清单

建议至少覆盖这些指标，告警不要只看「磁盘 80%」一条。

容量规划经验：

• 云盘不要等到 90% 再扩。大表重建、加索引、alter 都可能短期再吃 1 倍表大小
• 数据、binlog、备份尽量分盘或分桶
• 增长曲线按「最大表」外推，不要按整库平均值
• 能分区的日志表、流水表，优先分区，把「还空间」从 optimize 变成 drop partition

10. 速查 sql 附录

把 your_db / your_table 换成现场对象即可。

-- a. 某库空间总览
select
  table_schema as db_name,
  round(sum(data_length)/1024/1024, 2) as data_mb,
  round(sum(index_length)/1024/1024, 2) as index_mb,
  round(sum(data_free)/1024/1024, 2) as free_mb,
  round(sum(data_length+index_length)/1024/1024, 2) as used_mb
from information_schema.tables
where table_schema = 'your_db';

-- b. top 表 + 索引/数据比
select
  table_name,
  round(data_length/1024/1024, 2) as data_mb,
  round(index_length/1024/1024, 2) as index_mb,
  round(data_free/1024/1024, 2) as free_mb,
  round(index_length/greatest(data_length,1), 2) as idx_data_ratio
from information_schema.tables
where table_schema = 'your_db'
order by (data_length+index_length) desc
limit 30;

-- c. 单索引估算（mysql 8）
select
  table_name, index_name,
  round(sum(stat_value * @@innodb_page_size)/1024/1024, 2) as index_mb
from mysql.innodb_index_stats
where database_name = 'your_db' and stat_name = 'size'
group by table_name, index_name
order by index_mb desc
limit 50;

-- d. 可能未使用的索引（需结合业务确认）
select object_schema, object_name, index_name, count_star
from performance_schema.table_io_waits_summary_by_index_usage
where object_schema = 'your_db'
  and count_star = 0
  and index_name is not null
  and index_name != 'primary';

-- e. 数据目录
show variables like 'datadir';
show variables like 'innodb_page_size';

11. 总结

把这篇文章收成六句，现场够用：

1. 看空间：information_schema.tables 看库表量级，du / df 和文件对账；binlog、临时表、undo 经常比业务表先把盘写满。
2. 看索引大小：index_length 是二级索引合计，主键在 data_length 里；要拆到某个索引用 mysql.innodb_index_stats。
3. 数据和索引一样大：通常是二级索引多或主键宽，先审索引，不要先整表重建。
4. 索引进内存：页级按需进入 buffer pool，lru 保热汰冷；没有「启动时装载全部索引」这回事。
5. 生产扩容：扩的是磁盘，云盘 / lvm / rds 多数在线完成，mysql 一般不用重启；扩盘后记得扩文件系统。表文件会随写入自动涨，但盘满了引擎不会自己加盘。
6. optimize：innodb 上等于重建表，大表很慢、很吃 io，还要预留约 1 倍空间。小表低峰可以做；大表用 online 工具、从库重建或分区删除。磁盘剩余不够时，先扩盘再收缩。

空间问题看起来像存储，做起来是 观测 → 判断增长来源 → 选最小动作。扩盘、清 binlog、删冗余索引、重建一张表，四者成本差一个数量级。选对动作，比把每条 sql 背下来更重要。

以上就是mysql存储空间与索引运维的实战指南的详细内容，更多关于mysql存储空间与索引运维的资料请关注代码网其它相关文章！