MySQL索引清理实战：用性能视图找出没人用的索引

  1.  Home
  2. Text  
    3. Share to：

索引不是越多越好：一张表上七个索引的代价

很多站长看到慢查询，第一反应就是「加索引」。加完确实快了，于是形成习惯：哪里慢就加一个。半年后打开 phpMyAdmin 一看，一张三万行的文章表上有七个二级索引，写文章时保存按钮要转两秒，后台的「最近修改」列表反而越来越慢。这篇文章讲的不是怎么建索引，而是反过来——怎么判断哪些索引该删，删之前怎么用数据证明它确实没用，以及删错了怎么快速回去。

这件事的技术门槛不高，难的是心态。加索引有即时反馈，删索引要承担风险，所以多数人只做前者。但一张表上的索引是有固定成本的：每个索引都占磁盘和内存，每次 INSERT/UPDATE/DELETE 都要多维护一棵 B+ 树，还要多写一份 redo/undo。小表看不出来，一旦表长到百万行、或者写入变频繁，成本就会集中爆发。

先算清楚：一个二级索引到底吃多少资源

InnoDB 的二级索引叶子节点存的是「索引列 + 主键」。所以一个单列索引的大小，大致等于「索引列的字节长度 + 主键长度 + 每条记录约 15 字节的页内开销」，再乘以行数，再考虑页填充率（默认约 70%~90%）。

举个真实的例子。一张文章表，主键是 BIGINT（8 字节），有 30 万行：

-- 单列索引 views（INT，4 字节）
每条索引记录 ≈ 4 + 8 + 15 = 27 字节
30 万行 × 27B ≈ 8.1 MB，按 75% 填充率折算 ≈ 10.8 MB

-- 单列索引 CHAR(32) 的 slug（utf8mb4，最长 128 字节，实际平均 28 字节）
每条 ≈ 28 + 8 + 15 = 51 字节
30 万行 × 51B ≈ 15.3 MB，折算 ≈ 20 MB

-- 复合索引 (categories, created, views)
每条 ≈ 4 + 8 + 4 + 8 + 15 = 39 字节 → 折算 ≈ 15.6 MB

单看一个索引十几兆不吓人，但七个加起来就是一百多兆，而 buffer pool 可能只有 256MB 或 512MB。这些索引页会跟热数据抢内存，导致真正需要的数据页被挤出。这才是「加了索引反而整体变慢」最常见的机理。

用一条 SQL 就能把每张表、每个索引的实际大小列出来：

SELECT TABLE_NAME, INDEX_NAME,
       ROUND(SUM(STAT_VALUE * @@innodb_page_size) / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE STAT_NAME = 'size'
  AND DATABASE_NAME = 'zz1984'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY size_mb DESC
LIMIT 20;

注意 @@innodb_page_size 默认是 16384，如果不是默认值要按实际填。这个查询在 MySQL 8 上读的是持久化的统计表，需要先确认 innodb_stats_persistent 是打开的（默认 ON）。

怎么找出「从没被用过」的索引

MySQL 的 performance_schema 里有一张 table_io_waits_summary_by_index_usage，它按索引粒度统计读次数。如果一个索引的 COUNT_STAR 是 0，说明自实例启动以来它没有任何一次读操作——包括不能加速任何查询，只是纯写入负担。

SELECT OBJECT_NAME AS tbl, INDEX_NAME, COUNT_STAR AS reads
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'zz1984'
  AND INDEX_NAME IS NOT NULL
  AND INDEX_NAME != 'PRIMARY'
ORDER BY COUNT_STAR ASC, OBJECT_NAME;

几个必须注意的判读细节，否则会误删：

  • 统计从实例/表被打开算起。刚重启的服务、刚打开的表，所有索引都是 0。至少观察一个完整的业务周期（比如一周，覆盖月末备份、周报统计这类低频任务）。
  • COUNT_STAR 为 0 不代表绝对没用。报表类 SQL、运维手工查询可能一个月才跑一次。删之前先看慢查询日志里有没有用到这个索引的语句。
  • 外键列上的索引不能删。InnoDB 要求外键列必须有索引，MySQL 会自动创建，删了会报错。用 SHOW CREATE TABLE 确认 CONSTRAINT 部分。
  • 唯一索引即使 reads=0 也要慎重。它承担的是约束作用，不是查询加速。删掉会让脏数据能写进来。
  • 索引前缀冗余。如果已经有 (a, b, c) 复合索引，那么 (a) 和 (a, b) 通常是冗余的——最左前缀可以直接用复合索引。这条规则能砍掉大部分历史遗留索引。

用 sys 视图一步定位冗余索引

MySQL 自带 sys.schema_redundant_indexes，它已经帮你把「被更长的复合索引完全覆盖」的索引找出来了：

SELECT table_name, redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'zz1984';

还有 sys.schema_unused_indexes，直接把「从未被读过的索引」列出来：

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'zz1984';

这两个视图是基于同一批 performance_schema 数据算出来的，所以上面的「观察周期」问题同样适用。我的习惯是：每周跑一次，把结果存到一张审计表里，连续四周都是 0 才列入删除候选。

删之前：给索引做一次「停用」而不是直接 DROP

MySQL 8.0 之后有个非常好用的能力——索引可以被设为不可见（INVISIBLE）。优化器会完全忽略它，但索引结构还在，写操作仍然维护它的代价还有，但查询侧的选择立刻改变。

ALTER TABLE typecho_contents ALTER INDEX idx_views INVISIBLE;

设成不可见之后观察三天：慢查询有没有新增？后台列表页 P95 有没有变大？两者都没有，就可以放心 DROP。有问题就一条命令复活：

ALTER TABLE typecho_contents ALTER INDEX idx_views VISIBLE;

这个「先隐形再删除」的流程，是我认为 MySQL 8 对站长最实用的改进之一。它把一次不可逆的风险操作，变成了可回滚的实验。如果你的表很大（千万行级），还要注意 DDL 本身的成本，详见下面一节。

实测：三个索引联动重建，写延迟降了 60%

一次真实的清理过程，表是 typecho_contents，约 42 万行，微信文章采集站。原有索引：

PRIMARY KEY (cid)
KEY created (created)
KEY authorId (authorId)
KEY type (type)
KEY status_type (type, status)
KEY type_status_created (type, status, created)
KEY slug (slug)
KEY category_views (categories, views)   -- 手工加的"优化"

分析结论：

  • type 被 (type, status) 覆盖，冗余 → 删
  • status_type 与 type_status_created 列序相同、后者更长 → 删前者
  • category_views 的 COUNT_STAR 为 13（一周内），而全站列表查询实际走的是 (type, status, created)，这个索引只被一次临时统计用到 → 设 INVISIBLE 观察后删除
  • authorId 读次数 0 但被外键约束引用 → 保留

删除三个索引后，单条 UPDATE 的 redo 写入量明显下降。用 SHOW ENGINE INNODB STATUS 里的 Log sequence number 差值粗略测量，在每分钟 200 次更新的人工压测下，LSN 增速从约 2.1MB/s 降到 0.8MB/s；后台保存文章的响应时间中位数从 1.9 秒降到 0.7 秒。这个数据不能当标准答案，但方向是对的：写入变慢，先数索引。

大表删索引的正确姿势：别把 DDL 变成事故

MySQL 8.0 支持 ONLINE 方式的 DROP INDEX，通常不阻塞读写，但仍要遵守三条纪律：

  1. 先在从库或临时实例上演练。mysqldump 一份到测试库，跑一遍 DDL 计时，估算生产窗口。
  2. 看 information_schema.innodb_trx 有没有长事务。DDL 需要拿到 MDL 排他锁，一个跑了 20 分钟的统计查询就会让 DDL 排队，后面的所有查询也跟着排队，形成雪崩。
  3. 用 pt-online-schema-change 或 gh-ost 兜底。写入特别频繁的表，即使 ONLINE DDL 也可能走 rebuild（比如同时删索引和改列类型），那就必须走工具。
-- 执行前的快检，三个都要 0 或 NONE
SELECT COUNT(*) AS long_trx FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

SELECT COUNT(*) AS lock_wait FROM performance_schema.data_lock_waits;

SHOW PROCESSLIST;  -- 人工扫一眼有没有 Sending data 卡很久的

另外 lock_wait_timeout 建议临时调低到 5 秒，让 DDL 在拿不到锁时快速失败而不是无限等待堵住整个队列：

SET SESSION lock_wait_timeout = 5;
ALTER TABLE typecho_contents DROP INDEX type, ALGORITHM = INPLACE, LOCK = NONE;

索引设计的四条硬约束

清理完历史包袱，还要建立新规矩，否则明年又是一堆。我在维护的所有站点上执行这四条：

  • 单表索引不超过 5 个（含主键）。超过就必须写清楚理由，写在迁移脚本的注释里。
  • 新建索引必须是一次真实慢查询驱动的。把慢 SQL、EXPLAIN 前后对比、预期收益写进同一次提交。没有这条证据的索引一律不批。
  • 复合索引列顺序按「等值在前、范围在后、排序列最后」。比如 WHERE type='post' AND status='publish' ORDER BY created DESC，就建 (type, status, created)，一条索引同时解决过滤和排序，避免 filesort。
  • 区分度低的列单独建索引几乎没用。像 status（只有两个值）、type 这种，必须有其他列组合才有价值。

一个容易忽略的点：索引统计信息过期

有时候索引明明存在、明明该用，优化器却全表扫描。常见原因是统计信息不准。innodb_stats_auto_recalc 默认开启，但触发条件是「表修改行数超过 10%」，对一张变化平缓的大表可能几周都不更新。手工刷新：

ANALYZE TABLE typecho_contents;
-- 看基数估计是否合理
SHOW INDEX FROM typecho_contents;

Cardinality 列如果明显偏离真实值（比如 slug 明明全唯一，基数却只有几千），就说明统计过期了，或者 innodb_stats_sample_pages 采样页太少（默认 20）。对超大表可以调到 128，代价是 ANALYZE 变慢。

下线总结：一次清理换来什么

这次清理从 7 个二级索引减到 4 个，表空间小了约 60MB，更新延迟降了六成，后台列表页因为不再被多余索引页挤占 buffer pool，反而快了。整个过程没有停机，没有数据风险，靠的就是三步：读 performance_schema 找证据、INVISIBLE 做灰度、再 DROP。

如果你的站也跑了两年以上，建议这个周末就花半小时跑一下 sys.schema_redundant_indexes 和 sys.schema_unused_indexes。多数个人站会看到至少两三个「历史遗留索引」，它们每天都在安静地拖慢你的写入。

Last modification：October 20th, 2026 at 01:27 pm
© 允许规范转载

相关文章

Leave a Comment