4. 索引优化策略：最左前缀原则、覆盖索引、索引下推、索引选择性

索引是数据库性能调优的核心武器，但错误地使用索引反而会拖垮查询。本章深入剖析四个关键索引优化策略，帮助你在实际场景中精准设计索引，避免“索引失效”陷阱。

4.1 最左前缀原则（Leftmost Prefix Principle）

最左前缀原则是联合索引（Composite Index）使用的核心规则。它规定：查询条件必须从联合索引的最左列开始，并且不能跳过中间的列，否则索引无法被完整使用。

4.1.1 联合索引匹配规则表

4.1.2 最左前缀原则的 SQL 示例

-- 创建联合索引
CREATE INDEX idx_abc ON orders (status, create_time, user_id);

-- ✅ 有效使用索引（完全匹配最左前缀）
SELECT * FROM orders WHERE status = 'PAID' AND create_time > '2024-01-01';

-- ✅ 有效使用索引（等值 + 范围 + 排序）
SELECT * FROM orders WHERE status = 'PAID' AND create_time > '2024-01-01' ORDER BY user_id;

-- ❌ 索引失效（跳过了 status 列）
SELECT * FROM orders WHERE create_time > '2024-01-01';

-- ❌ 索引部分失效（跳过了 create_time 列）
SELECT * FROM orders WHERE status = 'PAID' AND user_id = 1001;

4.2 覆盖索引（Covering Index）

覆盖索引是指查询所需的所有列都包含在索引中，无需回表（回到聚簇索引或数据行）获取数据。这是减少磁盘 I/O 和提升查询性能的利器。

4.2.1 覆盖索引 vs 非覆盖索引对比

-- 示例：覆盖索引设计
-- 表结构：orders (id, order_no, status, amount, create_time)

-- ❌ 非覆盖索引查询（需要回表）
CREATE INDEX idx_status ON orders (status);
SELECT order_no, amount FROM orders WHERE status = 'PAID';
-- 过程：索引找到 status='PAID' 的 id → 回表读取 order_no, amount

-- ✅ 覆盖索引查询（无需回表）
CREATE INDEX idx_status_covering ON orders (status, order_no, amount);
SELECT order_no, amount FROM orders WHERE status = 'PAID';
-- 过程：索引直接包含所有查询列，无需回表

4.3 索引下推（Index Condition Pushdown, ICP）

索引下推是 MySQL 5.6 引入的优化特性。它允许在索引遍历过程中，提前过滤不符合条件的记录，减少回表次数。ICP 对联合索引中无法使用最左前缀的列尤其有效。

4.3.1 ICP 优化效果对比

-- 示例：索引下推生效场景
-- 联合索引 (status, create_time)
-- 查询：status = 'PAID' AND create_time LIKE '2024-01%'

-- 无 ICP 时：
-- 1. 存储引擎通过 status='PAID' 找到 1000 条记录
-- 2. 全部回表获取完整行
-- 3. Server 层过滤 create_time LIKE '2024-01%'，最终返回 200 条

-- 启用 ICP 时：
-- 1. 存储引擎通过 status='PAID' 找到 1000 条记录
-- 2. 在索引层直接过滤 create_time LIKE '2024-01%'，仅 200 条满足
-- 3. 只对 200 条记录回表

4.4 索引选择性（Index Selectivity）

索引选择性是指索引列中不同值的数量与总行数的比值。选择性越高，索引过滤效果越好，查询效率越高。

4.4.1 选择性评估表

-- 查看索引选择性
SELECT 
    COUNT(DISTINCT column_name) AS cardinality,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 示例：检查 status 列的选择性
SELECT 
    COUNT(DISTINCT status) AS cardinality,  -- 假设只有 3 种状态
    COUNT(*) AS total_rows,                  -- 假设 100 万行
    COUNT(DISTINCT status) / COUNT(*) AS selectivity  -- 0.000003，极低
FROM orders;

4.5 四种策略综合对比与选择

4.6 索引优化策略流程图

4.7 实战案例：订单查询优化

假设有一个订单表 orders，包含 1000 万行数据，常见查询如下：

-- 原始查询（性能差，全表扫描）
SELECT order_no, amount, status 
FROM orders 
WHERE status = 'PAID' AND create_time > '2024-06-01' 
ORDER BY amount DESC 
LIMIT 100;

-- 优化后的索引设计
-- 1. 分析查询列：order_no, amount, status（SELECT 列）
-- 2. 分析条件列：status（等值），create_time（范围）
-- 3. 分析排序列：amount（排序）

-- 最优联合索引（覆盖索引 + 最左前缀）
CREATE INDEX idx_covering ON orders (status, create_time, amount, order_no);

-- 优化效果：
-- ✅ 最左前缀：status 等值匹配 → create_time 范围匹配
-- ✅ 覆盖索引：所有查询列都在索引中，无需回表
-- ✅ 索引选择性：status 选择性低但放在最左，create_time 选择性高
-- ✅ 排序优化：amount 在索引中，避免 filesort

4.8 索引优化检查清单

• 最左前缀检查： 联合索引是否从最左列开始匹配？是否跳过了中间列？
• 覆盖索引检查： SELECT 和 WHERE 中的所有列是否都在索引中？能否消除回表？
• 索引下推检查： 对于无法完全匹配的联合索引列，是否启用了 ICP？EXPLAIN 是否显示 Using index condition？
• 选择性检查： 索引列的选择性是否大于 0.1？低选择性列是否避免了单独建索引？
• 冗余索引检查： 是否存在重复或重叠的索引？例如 (a, b) 和 (a) 同时存在时，(a) 是冗余的。
• 排序优化检查： ORDER BY 列是否在索引中？是否避免了 filesort？