MySQL 8.0不可见索引与降序索引在生产环境的实践指南

不可见索引：安全验证索引效果的利器

MySQL数据库运维中，索引管理是最常见的性能优化手段。添加索引容易，删除索引却让人犹豫——删除后如果查询性能下降，恢复索引需要重建，大表上重建索引可能耗时数小时甚至导致锁表。MySQL 8.0引入的不可见索引（Invisible Index）解决了这个问题：将索引设为不可见后，优化器不再使用该索引，但索引本身仍然存在。可以安全验证索引是否真的被需要，如果确实不需要再删除。

这在数据库运维实践中极大地降低了索引清理的风险。

不可见索引操作与验证流程

将索引设为不可见：

-- 创建索引时直接设为不可见
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at) INVISIBLE;

-- 将已有索引设为不可见
ALTER TABLE orders ALTER INDEX idx_status_created SET INVISIBLE;

-- 恢复可见
ALTER TABLE orders ALTER INDEX idx_status_created SET VISIBLE;

完整的索引验证流程：

-- 第1步：记录当前查询执行计划
EXPLAIN SELECT * FROM orders WHERE status = "PAID" ORDER BY created_at DESC;

-- 第2步：将索引设为不可见
ALTER TABLE orders ALTER INDEX idx_status_created SET INVISIBLE;

-- 第3步：再次执行EXPLAIN，确认优化器不再使用该索引
EXPLAIN SELECT * FROM orders WHERE status = "PAID" ORDER BY created_at DESC;

-- 第4步：在测试环境或低峰时段执行实际查询，对比性能
-- 如果性能无明显下降，说明该索引可以安全删除
-- 如果性能显著劣化，立即恢复
ALTER TABLE orders ALTER INDEX idx_status_created SET VISIBLE;

需要注意：不可见索引对优化器不可见，但对写操作仍然存在。INSERT/UPDATE/DELETE语句仍然需要维护不可见索引，这意味着不可见索引仍然有写入开销。所以不可见索引是验证工具，不是长期保留策略——验证完成后应尽快删除或恢复。

降序索引：ORDER BY DESC的性能飞跃

MySQL 8.0之前，CREATE INDEX中的ASC/DESC关键字只被解析但不实际生效，索引始终按升序存储。这意味着 ORDER BY col DESC 查询无法利用索引避免filesort。MySQL 8.0真正实现了降序索引，索引可以按降序存储数据，直接支持ORDER BY DESC查询。

降序索引的实际收益取决于业务场景中降序查询的频率和表规模。

-- MySQL 5.7：DESC关键字被忽略，索引按升序存储
CREATE INDEX idx_created ON orders(created_at DESC);  -- 实际仍为ASC

-- MySQL 8.0：真正创建降序索引
CREATE INDEX idx_created ON orders(created_at DESC);

-- 更有价值的场景：复合索引中混合升降序
-- 常见查询：WHERE status = "PAID" ORDER BY created_at DESC
CREATE INDEX idx_status_created ON orders(status ASC, created_at DESC);

-- 查看索引方向
SHOW INDEX FROM orders;

降序索引的性能对比实测

在一张1000万行的orders表上实测：

-- 测试查询
SELECT * FROM orders WHERE status = "PAID" ORDER BY created_at DESC LIMIT 100;

测试结果：

MySQL 5.7（升序索引+filesort）：扫描约50000行索引数据，filesort耗时约120ms，总查询耗时约350ms。

MySQL 8.0（降序索引，无filesort）：直接从索引尾部读取100行，无filesort，总查询耗时约5ms。

性能提升约70倍。差距的核心在于filesort被完全消除——MySQL可以直接按索引顺序读取数据，无需额外的排序步骤。

降序索引的常见陷阱

陷阱1：索引方向不匹配。降序索引只能匹配相同方向的ORDER BY。如果索引是(col_a ASC, col_b DESC)，那么 ORDER BY col_a ASC, col_b DESC 可以命中，但 ORDER BY col_a ASC, col_b ASC 不能命中。

-- 能命中降序索引
SELECT * FROM orders WHERE status = "PAID" 
ORDER BY created_at DESC LIMIT 100;  -- 匹配 DESC 索引

-- 不能命中降序索引，会走filesort
SELECT * FROM orders WHERE status = "PAID" 
ORDER BY created_at ASC LIMIT 100;   -- 方向不匹配

-- 解决方案：为两种排序方向各建一个索引
CREATE INDEX idx_status_created_asc ON orders(status, created_at ASC);
CREATE INDEX idx_status_created_desc ON orders(status, created_at DESC);

陷阱2：覆盖索引受影响。降序索引的存储顺序与SELECT列顺序可能不一致，导致无法使用覆盖索引。需要确认EXPLAIN中Extra列是否出现Using index。

陷阱3：Online DDL限制。大表上创建降序索引可能触发长时间锁。MySQL 8.0的ALGORITHM=INPLACE对降序索引的支持需要验证版本，部分版本需要ALGORITHM=COPY，会导致表重建。建议在低峰时段执行，或使用pt-online-schema-change工具。

索引管理的运维建议

1. 建立索引使用率监控。通过sys.schema_unused_indexes视图定期检查未被使用的索引，结合不可见索引特性安全清理。

2. 降序索引优先在分页查询场景应用。业务中最常见的 ORDER BY … DESC LIMIT N 场景收益最大，filesort消除效果最显著。

3. 索引变更走灰度流程。新建索引先设为不可见，在只读副本上验证执行计划，确认无性能回退后再在主库设为可见。