索引覆盖与回表查询

覆盖索引是索引优化的重要技术，通过合理设计索引列避免回表。

回表查询

概念

通过二级索引查询数据时，索引叶子节点只存储索引列值和主键值，查询其他列需要回到聚簇索引获取完整数据。

回表过程

查询：SELECT * FROM orders WHERE user_id = 1;

┌─────────────────────────────────────────────────┐
│              回表查询流程                         │
├─────────────────────────────────────────────────┤
│  Step 1: 查二级索引 idx_user                     │
│          找到 user_id=1 → 获取主键 id=100        │
│              ↓                                  │
│  Step 2: 查聚簇索引                              │
│          用主键 id=100 获取完整数据行            │
│              ↓                                  │
│  Step 3: 返回 SELECT * 的所有列                 │
└─────────────────────────────────────────────────┘

回表开销分析

-- 查找1条记录
SELECT * FROM orders WHERE user_id = 1;
-- 回表次数：1次（可接受）

-- 查找100条记录
SELECT * FROM orders WHERE user_id = 1 LIMIT 100;
-- 回表次数：100次（开销大）

-- 查找10000条记录
SELECT * FROM orders WHERE user_id = 1;
-- 回表次数：可能10000次（性能问题）

覆盖索引

概念

覆盖索引（Covering Index）指查询所需的所有列都在索引中，无需回表即可获取数据。

创建覆盖索引

-- 原表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME
);

-- 创建覆盖索引
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- ️ 覆盖索引查询
SELECT user_id, status, amount FROM orders WHERE user_id = 1;
-- 查询列都在索引中，无需回表

-- ❌ 需回表查询
SELECT * FROM orders WHERE user_id = 1;
-- created_at不在索引中，需回表

执行计划验证

-- 覆盖索引：Extra显示 Using index
EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 1;
-- Extra: Using index

-- 需回表：Extra无 Using index
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- Extra: 无特殊标识（需回表）

覆盖索引设计原则

包含查询列

-- 查询语句
SELECT user_id, status, amount FROM orders WHERE user_id = 1 AND status = 2;

-- ✅ 设计覆盖索引（WHERE列+SELECT列）
CREATE INDEX idx_cover ON orders(user_id, status, amount);
-- 索引列：user_id, status（WHERE条件）+ amount（SELECT列）

-- 查询效率：只扫描索引，无回表

索引列顺序

-- 索引列顺序原则：
-- 1. WHERE条件列在前（遵循最左前缀）
-- 2. SELECT列在后（实现覆盖）

-- 查询
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

-- ✅ 正确顺序
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- ❌ 错误顺序（WHERE列不在最左）
CREATE INDEX idx_amount_status_user ON orders(amount, status, user_id);
-- WHERE user_id=1 无法使用索引

联合索引覆盖多查询

-- 设计一个联合索引覆盖多种查询
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 覆盖查询1
SELECT user_id, status FROM orders WHERE user_id = 1;
-- ✅ 覆盖索引（user_id, status都在索引中）

-- 覆盖查询2
SELECT user_id, status, created_at FROM orders WHERE user_id = 1 AND status = 2;
-- ✅ 覆盖索引（所有列都在索引中）

-- 需回表查询
SELECT * FROM orders WHERE user_id = 1;
-- ❌ amount等不在索引中，需回表

索引下推（ICP）

概念

Index Condition Pushdown（ICP）将WHERE条件过滤下推到索引扫描阶段，减少回表次数。

无ICP流程

无ICP：
1. 二级索引扫描 → 获取所有满足 user_id=1 的主键
2. 回表获取完整数据行
3. 在Server层过滤 status=2
4. 返回结果

回表次数：满足user_id=1的所有记录

有ICP流程

有ICP：
1. 二级索引扫描 → 在索引层过滤 status=2
2. 只对满足条件的记录回表
3. 返回结果

回表次数：满足user_id=1 AND status=2的记录（减少）

ICP示例

-- 创建索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 查询（ICP生效）
SELECT * FROM orders WHERE user_id = 1 AND status LIKE '2%';

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status LIKE '2%';
-- Extra: Using index condition（ICP生效）

-- ICP将status过滤下推到索引层
-- 减少回表次数

ICP适用条件

• 索引支持范围查询
• WHERE条件部分可下推
• 需回表的查询

主键自动覆盖

-- 主键查询自动覆盖（聚簇索引）
SELECT id FROM orders WHERE id = 100;
-- 覆盖索引（主键就是索引列）

-- 主键在二级索引中
CREATE INDEX idx_user ON orders(user_id);
SELECT id, user_id FROM orders WHERE user_id = 1;
-- 覆盖索引（主键id在二级索引叶子节点）

覆盖索引限制

-- ❌ 前缀索引无法覆盖
CREATE INDEX idx_name ON users(name(10));
SELECT name FROM users WHERE name = '张三';
-- 前缀索引无法确认完整匹配，需回表验证

-- ❌ 索引列不含查询列
CREATE INDEX idx_user ON orders(user_id);
SELECT user_id, amount FROM orders WHERE user_id = 1;
-- amount不在索引中，需回表

-- ✅ 解决：扩展索引
CREATE INDEX idx_user_amount ON orders(user_id, amount);

要点总结

• 回表查询需扫描二级索引和聚簇索引，增加IO开销
• 覆盖索引包含查询所有列，无需回表
• 覆盖索引设计：WHERE条件列+SELECT列
• 索引下推（ICP）减少回表次数
• 主键自动在二级索引中，可用于覆盖查询
• 前缀索引无法实现覆盖索引