优化器怎么选择索引

在关系型数据库（MySQL、PostgreSQL、Oracle 等）中，优化器（Optimizer）选择索引的过程，本质上是一个基于代价（Cost-Based）的决策过程。下面从“整体流程 → 判断依据 → 常见影响因素 → 实战建议”四个层面说明。

一、优化器选择索引的整体流程

一般数据库优化器选择索引会经历以下步骤：

1. 解析 SQL

   • 生成解析树
   • 确定涉及哪些表、列、条件

2. 生成候选执行计划

   • 全表扫描（Full Table Scan）
   • 不同索引的可能使用方式（Index Scan / Range Scan / Index Lookup）

3. 估算每种计划的代价（Cost）

   • 扫描行数
   • IO 成本
   • CPU 成本
   • 回表成本（是否需要聚集索引）

4. 选择代价最小的计划

   • 使用哪个索引
   • 是否回表
   • join 顺序等

二、优化器判断“用不用索引”的核心依据

1. 索引是否“可用”

索引必须满足：

• WHERE / JOIN / ORDER BY / GROUP BY 中用到索引列
• 索引列没有被函数或隐式转换破坏

❌ 错误示例（索引失效）：

WHERE DATE(create_time) = '2024-01-01'
WHERE phone = 13800000000   -- phone 是字符串

✅ 正确示例：

WHERE create_time >= '2024-01-01'
  AND create_time <  '2024-01-02'

2. 选择性（Selectivity / Cardinality）

选择性越高，越容易走索引

优化器会估算：

选择性 = 满足条件的行数 / 总行数

• 选择性低（如性别 = ‘男’）→ 倾向全表扫描
• 选择性高（如 user_id = 123）→ 倾向索引

3. 回表代价（是否覆盖索引）

• 覆盖索引（Using index）：不需要回表 → 代价低
• 非覆盖索引：索引 → 回表 → 代价高

-- 覆盖索引
INDEX(user_id, status)
SELECT status FROM user WHERE user_id = 1;

4. 扫描方式 & 行数估算

优化器会判断：

• 范围扫描（Range Scan）
• 等值扫描（Ref / Const）
• 全索引扫描（Index Full Scan）

如果估算扫描行数超过一定比例（如 20%~30%），可能放弃索引。

5. 统计信息（非常关键）

优化器依赖：

• 表行数
• 列的直方图
• 索引分布

统计信息不准 → 选错索引

-- MySQL
ANALYZE TABLE user;

-- PostgreSQL
ANALYZE user;

三、为什么“有索引却没用”？

常见原因：

四、如何“引导”优化器选对索引？

✅ 1. 写对 SQL

• 避免函数
• 避免隐式转换
• 使用最左前缀

✅ 2. 建对索引

• 高频查询字段
• 联合索引顺序合理
• 尽量覆盖索引

✅ 3. 更新统计信息

• 大批量数据变更后

✅ 4. 使用 Hint（谨慎）

-- MySQL
SELECT * FROM user FORCE INDEX(idx_user_status)
WHERE user_id = 1;

-- Oracle
SELECT /*+ INDEX(t idx_name) */ * FROM t;

⚠️ Hint 是“最后手段”，不是首选

五、一句话总结

优化器不是“看有没有索引”，而是“算用索引划不划算”
索引是否被选中 = 选择性 + 回表成本 + 统计信息 + SQL 写法

如果你愿意，可以贴一条 具体 SQL + 表结构 + 执行计划，我可以帮你分析为什么没走索引。