什么是索引覆盖与索引下推？

索引覆盖与索引下推：MySQL 核心优化技术详解

索引覆盖和索引下推是 MySQL 中两大关键索引优化技术，能显著提升查询性能。下面通过对比表格、原理图解和实例分析详细解析二者的区别与协同效应：

一、核心对比（索引覆盖 vs 索引下推）

二、索引覆盖（Covering Index）深度解析

1. 工作原理

2. 实际案例

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_city_age (city, age)  -- 联合索引
);

-- 索引覆盖查询（所有字段在索引中）
EXPLAIN SELECT city, age FROM users WHERE city = '杭州';

执行计划输出：

+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_city_age  | idx_city_age| 102     | const | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

3. 设计要点

• 字段顺序：按 WHERE > ORDER BY > SELECT 顺序创建联合索引
• 避免 SELECT *：只查询必要字段
• 最大长度限制：单索引长度不超过 3072 字节
• 禁用场景：TEXT/BLOB 类型列无法完全覆盖

三、索引下推（Index Condition Pushdown）深度解析

1. 工作原理

2. 实际案例

-- 使用ICP的查询
EXPLAIN SELECT * FROM users 
WHERE city = '杭州' AND age > 30;

执行计划输出：

+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_city_age  | idx_city_age| 102     | const | 1000 |    33.33 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+

3. 性能提升原理

4. 适用场景

• 联合索引的非前缀列过滤（如 INDEX(a,b) 中 b 的条件过滤）
• LIKE 前缀查询：WHERE name LIKE '张%' AND age>30
• 范围查询组合：WHERE create_time>'2023-01-01' AND status=1

四、协同使用：性能倍增策略

1. 黄金组合案例

-- 表：orders(order_id, user_id, amount, create_time)
-- 索引：idx_user_time(user_id, create_time)

-- 查询：索引覆盖 + ICP
EXPLAIN 
SELECT user_id, create_time 
FROM orders
WHERE user_id = 1001 
  AND create_time BETWEEN '2023-01-01' AND '2023-12-31';

双重优化效果：

• ICP：在索引层过滤时间范围
• 覆盖索引：直接从索引取数据（无需回表）

2. 执行计划分析

+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | range | idx_user_time | idx_user_time  | 9       | NULL | 50   |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+--------------------------+

3. 性能实测对比（100万数据）

五、避坑指南与最佳实践

1. 禁用场景

索引覆盖不可用：

-- 包含非索引列
SELECT * FROM users WHERE city='杭州';

-- 使用函数或表达式
SELECT UPPER(name) FROM users WHERE city='杭州';

ICP 不可用：

-- 子查询条件
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)

-- 使用函数
SELECT * FROM users WHERE city='杭州' AND YEAR(birthday)=1990;

2. 最佳实践

1. 联合索引设计：按 WHERE 等值列 > WHERE 范围列 > ORDER BY > SELECT 列 顺序

   -- 推荐：INDEX(city, age, name)
   SELECT name FROM users 
   WHERE city='杭州' AND age>30 
   ORDER BY age;
   

2. 强制启用 ICP（默认开启）：

   SET optimizer_switch='index_condition_pushdown=on';
   

3. 索引长度优化：

   -- 对长字段取前缀
   ALTER TABLE logs ADD INDEX idx_url_prefix (url(20));
   

4. 监控工具：

   -- 检查ICP使用
   SHOW STATUS LIKE '%handler_icp%';
   
   -- 检查覆盖索引
   EXPLAIN 
   SELECT audit_status, COUNT(*) 
   FROM orders 
   GROUP BY audit_status;
   

六、总结：技术选型决策树

💡 黄金法则：

1. 优先索引覆盖：消除回表开销
2. ICP 补充优化：减少无效回表
3. 监控执行计划：确保优化器正确选择策略
4. 定期优化索引：删除冗余索引，重建碎片索引

通过合理应用这两大技术，可使查询性能提升 3-10 倍，尤其在亿级数据表中效果更为显著。