MySQL 8.0不可见索引与降序索引的组合优化实战

MySQL 8.0索引优化新特性的工程价值

MySQL 8.0引入了不可见索引（Invisible Index）和降序索引（Descending Index）两个重要特性。不可见索引让DBA可以在不删除索引的情况下验证移除索引对查询性能的影响，避免删索引后性能骤降再紧急重建的风险。降序索引则解决了组合索引中列方向不一致导致的排序失效问题，显著减少filesort操作。两者组合使用，可以在索引调优过程中实现零风险操作。

不可见索引的使用场景与操作

不可见索引对查询优化器不可见，但仍然被DML操作维护。这意味着索引不会被SELECT使用，但INSERT/UPDATE/DELETE仍然会更新索引数据。

-- 创建不可见索引
ALTER TABLE orders ADD INDEX idx_create_time (create_time) INVISIBLE;

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

-- 恢复索引可见性
ALTER TABLE orders ALTER INDEX idx_user_status SET VISIBLE;

-- 查看索引可见性状态
SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_schema = 'mydb' AND table_name = 'orders';

不可见索引的典型使用流程：

1. 发现一个索引可能冗余，但不确认是否有隐藏查询依赖它

2. 将索引设为INVISIBLE

3. 观察慢查询日志，确认没有查询性能退化

4. 确认安全后执行DROP INDEX

5. 如果出现性能问题，立即SET VISIBLE恢复，秒级回滚

-- 完整的索引验证流程
-- Step 1: 标记为不可见
ALTER TABLE orders ALTER INDEX idx_status SET INVISIBLE;

-- Step 2: 检查是否有查询回退到全表扫描
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- 如果type=ALL，说明该索引被依赖，需要恢复

-- Step 3: 观察slow log
SET GLOBAL long_query_time = 1;
-- 运行业务一段时间后检查slow log

-- Step 4: 安全则删除，不安全则恢复
ALTER TABLE orders DROP INDEX idx_status;
-- 或
ALTER TABLE orders ALTER INDEX idx_status SET VISIBLE;

降序索引解决排序方向冲突

MySQL 8.0之前，CREATE INDEX idx (a DESC, b ASC)中的DESC会被忽略，实际创建的是ASC索引。当查询需要ORDER BY a DESC, b ASC时，优化器无法使用索引排序，会触发filesort。

MySQL 8.0的降序索引真正支持混合方向存储：

-- 创建降序索引
CREATE INDEX idx_order_time_amount ON orders (create_time DESC, amount ASC);

-- 该查询可以完全利用索引排序，避免filesort
EXPLAIN SELECT * FROM orders 
ORDER BY create_time DESC, amount ASC LIMIT 100;
-- Extra列不会出现"Using filesort"

对比旧版本的behavior：

-- MySQL 5.7: DESC被忽略，等同于(a ASC, b ASC)
-- 查询 ORDER BY a DESC, b ASC 无法利用索引排序

-- MySQL 8.0: 真正创建(a DESC, b ASC)索引
-- 查询 ORDER BY a DESC, b ASC 直接走索引

不可见索引+降序索引的组合调优场景

当需要为一个频繁排序的查询新增降序索引时，可以先创建为不可见索引验证效果：

-- 场景：订单列表页按时间倒序+金额正序排列
-- 当前只有idx_create_time(create_time)单列索引
-- 查询执行计划显示Using filesort

-- Step 1: 创建不可见的降序组合索引
ALTER TABLE orders 
ADD INDEX idx_time_desc_amount_asc (create_time DESC, amount ASC) INVISIBLE;

-- Step 2: 开启optimizer_switch测试
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- Step 3: 验证新索引的执行计划
EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2026-08-01'
ORDER BY create_time DESC, amount ASC 
LIMIT 50;
-- 确认Extra列不再有"Using filesort"

-- Step 4: 确认有效后设为可见
ALTER TABLE orders ALTER INDEX idx_time_desc_amount_asc SET VISIBLE;

-- Step 5: 验证旧索引是否冗余
ALTER TABLE orders ALTER INDEX idx_create_time SET INVISIBLE;
-- 观察是否有查询性能退化，无退化则删除旧索引

use_invisible_indexes会话变量允许在当前连接中临时启用不可见索引，这对索引验证非常方便——只影响当前会话，不影响生产流量。

降序索引对范围查询的影响

降序索引在等值查询和排序场景表现优秀，但在范围查询中需要注意索引列的方向一致性：

-- 降序索引: (create_time DESC, amount ASC)
-- 以下查询可以同时利用索引过滤和排序
SELECT * FROM orders
WHERE create_time BETWEEN '2026-07-01' AND '2026-08-01'
ORDER BY create_time DESC, amount ASC;
-- 优化器使用索引范围扫描，无需filesort

-- 以下查询无法利用索引排序（方向冲突）
SELECT * FROM orders
WHERE create_time BETWEEN '2026-07-01' AND '2026-08-01'
ORDER BY create_time ASC, amount DESC;
-- 优化器只能用索引做范围扫描，filesort不可避免

如果业务同时存在两种排序方向的查询，需要创建两个方向的索引，或接受其中一个走filesort。实际权衡时，优先为高频查询创建匹配方向的索引。

索引维护成本评估

不可见索引虽然不被查询使用，但DML操作仍会维护它。在验证阶段，不可见索引会消耗写入性能。大批量INSERT场景下，额外的不可见索引可能使写入延迟增加5-15%。验证完成后应尽快决定保留或删除。

-- 监控索引维护开销
SELECT 
    index_name,
    rows_examined,
    rows_affected
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'orders';

-- 评估索引大小
SELECT 
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND table_name = 'orders'
AND stat_name = 'size';