如何在MySQL高频更新表上安全地评估并删除低效索引？-mysql教程-PHP中文网

必须启用performance_schema并开启相关consumers/instruments后，才能通过table_io_waits_summary_by_index_usage表识别冷索引；COUNT_FETCH=0仅表明未用于查询，不等于可直接删除，需结合外键、唯一约束、执行计划及业务场景综合判断。

如何在mysql高频更新表上安全地评估并删除低效索引？

先确认索引是否真被“冷落”，别只看 `SHOW INDEX`

直接查 information_schema.STATISTICS 只能看到索引定义，看不到它到底有没有被用。MySQL 8.0+ 必须启用 performance_schema 才能拿到真实读写计数。先检查开关状态：

SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'performance_schema';

如果是 OFF，得先开：SET GLOBAL performance_schema = ON;（需 SUPER 权限），再启用对应 instruments：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_waits_current';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'wait/io/table/%';

之后才能跑真正有效的冷索引扫描语句：

SELECT OBJECT_SCHEMA AS db, OBJECT_NAME AS table_name, INDEX_NAME AS index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_READ = 0 AND COUNT_FETCH = 0
  AND COUNT_INSERT = 0 AND COUNT_UPDATE = 0 AND COUNT_DELETE = 0;

注意：这个结果只是初筛，不是删除指令。

高频更新表上，`COUNT_STAR = 0` 的索引也不能轻删

因为更新操作本身会触发索引维护，COUNT_UPDATE > 0 才说明该索引参与了 DML 定位；但 COUNT_STAR = 0 只代表没被查询走，不代表它没价值。尤其要警惕以下三类“伪冷索引”：

仅用于 ORDER BY 或 GROUP BY 的覆盖索引——比如 INDEX (status, created_at) 被 SELECT id FROM orders WHERE status = 1 ORDER BY created_at LIMIT 20 依赖，EXPLAIN 显示 key 是空的，但删了就会出现 Using filesort
低频但高权重的后台任务索引——如凌晨对账 SQL 使用 INDEX (batch_id, updated_at)，白天流量里完全不露头，但删了会导致超时失败
为未来查询预留的复合索引前缀——比如现有业务只查 WHERE tenant_id = ?，但索引建的是 (tenant_id, user_id, action)，后续加了 AND user_id = ? 就能复用，删了等于自废扩展能力

删之前必须验证外键、唯一约束和执行计划

高频更新表往往也是核心业务表，结构变更风险更高。执行 ALTER TABLE ... DROP INDEX 前，这三件事缺一不可：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

查外键引用：SELECT CONSTRAINT_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'your_table' AND REFERENCED_COLUMN_NAME IN (SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_NAME = 'your_table' AND INDEX_NAME = 'target_idx');
确认非唯一性保障：主键（PRIMARY）和唯一索引（UNIQUE KEY）不能只因“没被查询用”就删——哪怕 COUNT_READ = 0，它仍承担数据完整性校验，删了可能让重复数据悄悄入库
逐条过核心 SQL 的 EXPLAIN FORMAT=TRADITIONAL：重点盯 key 字段是否出现目标索引名；别漏掉 JOIN 条件、GROUP BY 排序、覆盖查询（Extra: Using index）这些隐性依赖场景

特别注意：MySQL 8.0+ 统一用 ALTER TABLE tbl_name DROP INDEX idx_name，别用 DROP INDEX idx_name ON tbl_name —— 后者是 legacy 语法，InnoDB 下实际等价但易引发跨引擎误解。

删索引本身快，但锁表时机很关键

删索引是 in-place 操作，毫秒级完成，但它需要获取 MDL（metadata lock）写锁。这意味着：在语句执行期间，所有对该表的 DML（INSERT/UPDATE/DELETE）和 DDL 都会被阻塞。高频更新表上，哪怕只卡 200ms，也可能导致应用层连接池耗尽或事务堆积。

所以务必：

避开业务高峰时段操作
执行前先 SHOW PROCESSLIST，确认没有长事务正持有该表的 MDL（State: Waiting for table metadata lock 是危险信号）
不要在从库上直接删——除非你明确配置了 replicate_ignore_table 或 GTID 模式下已排除该操作

最常被忽略的一点：删索引不会释放磁盘空间，只是解除索引结构引用；如果后续有大量 INSERT，页分裂可能让碎片更严重。真要瘦身，得配合 OPTIMIZE TABLE（但那是另一场锁表风暴，慎用）。