如何减少MySQL二级索引占用的磁盘空间-mysql教程-PHP中文网

二级索引体积过大主因是冗余、设计不合理及长字段全量索引；应通过information_schema查真实占用、删冗余索引、用前缀索引、优化联合索引顺序等手段瘦身。

如何减少mysql二级索引占用的磁盘空间

二级索引比数据本身还大？不是磁盘坏了，是索引设计出了问题。直接删掉冗余索引、限制字段长度、用前缀索引替代全字段索引，基本能砍掉 30%–70% 的索引空间。

查清哪些二级索引在吃空间

别猜，先看 information_schema 里真实占用：

SELECT 
  table_name,
  index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats 
WHERE database_name = 'your_db' 
  AND stat_name = 'size'
ORDER BY size_mb DESC;

注意：mysql.innodb_index_stats 表必须已 ANALYZE（否则统计不准）；stat_value 是页数，乘以 @@innodb_page_size（默认 16KB）才是真实字节数。

如果某个 index_name 的 size_mb > 对应表的 data_length（查 information_schema.tables），说明索引体积已反超数据，必须优先处理
联合索引中列顺序不合理（比如把高区分度列放后面），会导致前导列无法高效过滤，索引实际利用率低，但空间照占
PRIMARY 索引不计入此统计——它属于聚簇索引，和数据共存；所有带 KEY 或 UNIQUE 的才是二级索引

删掉重复或无效的二级索引

MySQL 不会自动合并或去重索引。两个索引 (a) 和 (a,b) 同时存在，前者就是冗余的；(a,b,c) 和 (a,c) 也构成冗余（只要查询条件含 a 且只用到 a,c，前者就能覆盖后者）。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

用 pt-duplicate-key-checker（Percona Toolkit）一键扫描：它能识别 ALTER TABLE t DROP KEY idx_a 这类安全删除项
手动验证：执行 EXPLAIN SELECT * FROM t WHERE a=1 AND c=2;，看是否走 (a,b,c) 而非 (a,c)；若始终走长索引，短索引可删
别删唯一约束索引（如邮箱唯一性校验）、外键关联索引——删了会报错或破坏一致性

把长字段索引换成前缀索引

VARCHAR(2000) 或 TEXT 字段建普通索引，每条索引记录可能达几 KB，B+ 树层级陡增，空间爆炸。用前缀索引是性价比最高的止损方式。

先测区分度：SELECT COUNT(DISTINCT LEFT(content, 50)) / COUNT(*) AS selectivity FROM logs;，若 > 0.95，50 就够了
建前缀索引：CREATE INDEX idx_content_50 ON logs (content(50));，注意括号里是长度，不是字节数（UTF8mb4 下一个汉字占 4 字节）
不能对前缀索引做 ORDER BY 或 GROUP BY——MySQL 无法利用前缀值完成完整排序
前缀长度别盲目设 255：InnoDB 单列索引前缀上限是 3072 字节（innodb_large_prefix=ON 时），但越短越好

避免在变更频繁的大表上建过多二级索引

每次 INSERT/UPDATE/DELETE 都要同步更新所有二级索引。索引越多，写放大越严重，Buffer Pool 中缓存的索引页占比越高，挤占数据页空间，反而拖慢读性能。

写多读少的表（如日志、消息队列），只保留必要查询字段的索引，其他字段用 WHERE ... LIKE '%xxx%' 或应用层分页代替
启用 innodb_change_buffering = all（默认值），让变更缓冲区缓存二级索引修改，降低随机 I/O；但仅对非唯一二级索引有效
批量导入前临时禁用索引：ALTER TABLE t DISABLE KEYS;，导入完再 ENABLE KEYS;（MyISAM 有效，InnoDB 下实际是重建索引，慎用）

最常被忽略的一点：索引瘦身不是一劳永逸的事。业务逻辑改了、查询模式变了、新字段加了，旧索引可能立刻变成负担。建议把 information_schema.innodb_index_stats 的定期采样纳入 DBA 巡检脚本，而不是等磁盘报警才想起查索引。