MySQL索引优化是数据库运维中最具性价比的性能调优手段。一条SQL从扫描百万行降到扫描几百行，通常只需要调整索引结构。MySQL 8.0引入了隐藏索引、降序索引、函数索引等新特性，配合覆盖索引和Index Condition Pushdown（ICP）机制，能把查询性能提升一个数量级。

覆盖索引的工作原理

覆盖索引指查询所需的所有列都包含在索引中，MySQL不需要回表读取数据行。InnoDB的辅助索引叶子节点存储主键值，如果查询列都在索引中，引擎直接从索引返回数据，跳过回表操作。

-- 示例表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    status TINYINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_status(user_id, status),
    INDEX idx_created(created_at)
);

-- 这条查询需要回表
SELECT user_id, status, amount FROM orders WHERE user_id = 1001 AND status = 1;

-- 创建覆盖索引，避免回表
ALTER TABLE orders ADD INDEX idx_uid_status_amount(user_id, status, amount);

-- 再次执行，Extra列显示 Using index（覆盖索引生效）
EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 1001 AND status = 1;

覆盖索引的代价是索引体积增大，写入开销增加。在写多读少的表上要谨慎添加覆盖索引。SQL查询优化时，先用EXPLAIN分析回表次数，再决定是否创建覆盖索引。

ICP（Index Condition Pushdown）机制

MySQL 5.6引入的ICP机制，把WHERE条件的一部分下推到存储引擎层执行，减少回表次数。没有ICP时，引擎层根据索引找到匹配的主键，逐条回表取数据行，再由Server层应用WHERE条件过滤。开启ICP后，引擎层先用索引上可用的条件过滤，减少回表行数。

-- 复合索引上的ICP效果
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at);

-- 查询特定用户在日期范围内的订单
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001
  AND created_at >= '2026-01-01'
  AND created_at < '2026-04-01'
  AND status IN (0, 1, 2);

-- Extra列显示 Using index condition 表示ICP生效
-- status列不在索引中，但Server层把status条件下推到引擎层
-- 引擎层用user_id和created_at走索引范围扫描
-- 在索引上过滤后再回表，减少不必要的回表操作

ICP默认开启，可通过optimizer_switch控制：SET optimizer_switch=’index_condition_pushdown=on’; 关闭ICP做性能对比能直观看到差异。

复合索引最左前缀原则实操

复合索引遵循最左前缀匹配。索引(a, b, c)能用于查询a、a AND b、a AND b AND c，但不能用于b AND c或c单独查询。

-- 索引 idx_user_status_created(user_id, status, created_at)

-- 能利用索引（完整匹配）
SELECT * FROM orders WHERE user_id = 1001 AND status = 1 AND created_at > '2026-01-01';

-- 能利用索引（前两列匹配，第三列范围扫描）
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- 能利用索引（第一列匹配，第二列用IN仍在索引内）
SELECT * FROM orders WHERE user_id = 1001 AND status IN (0, 1);

-- 只能利用第一列索引
SELECT * FROM orders WHERE user_id = 1001 AND created_at > '2026-01-01';
-- status列被跳过，created_at无法利用索引

-- 完全无法利用索引
SELECT * FROM orders WHERE status = 1 AND created_at > '2026-01-01';
-- 缺少user_id前缀

SQL查询优化时需要分析所有查询条件的组合模式，选择区分度高的列放在索引左侧。范围查询列放在等值查询列之后。

MySQL 8.0隐藏索引安全操作

删除线上索引有风险——如果该索引被关键查询使用，删除后可能导致性能骤降。MySQL 8.0的隐藏索引（Invisible Index）可以先对优化器隐藏索引，观察无影响后再物理删除：

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

-- 查询是否受影响
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;
-- 如果Extra不再显示Using index，说明走了其他索引或全表扫描

-- 观察一至两周确认无影响后物理删除
ALTER TABLE orders DROP INDEX idx_user_status;

-- 如果发现有问题，快速恢复
ALTER TABLE orders ALTER INDEX idx_user_status VISIBLE;

隐藏索引对优化器不可见，但仍然占用存储空间和写入开销。它是一个安全的变更过渡工具，不是长期方案。

函数索引与降序索引应用

MySQL 8.0支持函数索引，解决WHERE条件中使用函数导致索引失效的问题：

-- 传统方案：这种查询不走索引
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-05';

-- 创建函数索引
ALTER TABLE orders ADD INDEX idx_date_created((DATE(created_at)));

-- 现在走函数索引
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-08-05';

-- 降序索引：优化ORDER BY DESC查询
ALTER TABLE orders ADD INDEX idx_user_created_desc(user_id, created_at DESC);

-- 降序查询避免filesort
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 20;

数据备份恢复和分库分表方案设计阶段，索引规划需要全局考虑。线上索引变更建议在低峰期执行，大表创建索引使用ONLINE DDL或pt-online-schema-change工具避免锁表。定期使用pt-index-usage工具分析索引使用率，清理冗余索引提升写入性能。