02 - 索引优化实战

上一章讲了索引原理。这一章讲实战中最常用、也最容易踩坑的几个点：联合索引怎么用、覆盖索引省了什么、以及为什么你明明建了索引却「不走索引」。

一句话先记住：建了索引 ≠ 会用索引。索引能不能生效，取决于你的查询「配不配合」它。

2.1 联合索引与「最左前缀原则」

联合索引（复合索引）= 在多个列上一起建的索引，比如：

CREATE INDEX idx_user ON users (last_name, first_name, age);
-- 这是一个建立在 (姓, 名, 年龄) 三列上的联合索引

关键规则：最左前缀原则——联合索引只能「从最左边的列开始、连续地」用。

索引 (last_name, first_name, age) 相当于按这个顺序排好序：
  先按 last_name 排，last_name 相同再按 first_name 排，再相同才按 age 排
  就像电话簿：先按姓排，同姓按名排

能用上索引的查询（从最左开始、连续）：
  ✅ WHERE last_name = '张'
  ✅ WHERE last_name = '张' AND first_name = '三'
  ✅ WHERE last_name = '张' AND first_name = '三' AND age = 20

用不上（或只能用一半）的查询：
  ❌ WHERE first_name = '三'              （跳过了最左的 last_name）
  ❌ WHERE age = 20                       （跳过了前面两列）
  ⚠️ WHERE last_name = '张' AND age = 20  （只能用到 last_name，age 用不上，因为中间断了 first_name）

类比：电话簿按「姓→名」排序。你知道姓「张」，很好找；但你只知道名叫「三」不知道姓，那还是得全本翻——因为整本书不是按「名」排的。

实战建议：

• 把区分度高、最常用于查询的列放在联合索引的最左边
• 一个精心设计的联合索引，往往能顶好几个单列索引

2.2 覆盖索引：连回表都省了

回顾第 01 章：PostgreSQL 查到索引后，通常还要「回表」去取数据行。如果查询需要的字段，索引里全都有，那就不用回表了——这叫覆盖索引。

查询：SELECT age FROM users WHERE last_name = '张';
索引：(last_name, first_name, age)

因为 age 就在索引里 → 数据库在索引里就拿到了 age，不用回表！
→ 少一次「去数据表取行」的磁盘访问，更快

在 PostgreSQL 里，还可以用 INCLUDE 显式把额外字段塞进索引，专门为覆盖索引服务：

CREATE INDEX idx_cover ON users (last_name) INCLUDE (age, email);
-- 查 last_name 时，顺带就能返回 age、email，不回表

重要提示：SELECT * 通常无法用覆盖索引（因为索引里不可能有所有字段）。只查你需要的列，是用上覆盖索引的前提——这也是不要滥用 SELECT * 的原因之一。

2.3 索引失效：建了却不走（高频踩坑）

明明建了索引，查询却还是全表扫描？大概率踩了下面这些坑：

① 在索引列上做运算或用函数

-- ❌ 索引失效：对列做了运算/函数，索引的有序性被破坏
WHERE age + 1 = 30
WHERE UPPER(email) = 'A@X.COM'
WHERE EXTRACT(YEAR FROM created_at) = 2024

-- ✅ 改写：让索引列「干干净净」地待在一边
WHERE age = 29
WHERE email = 'a@x.com'                       -- 或建函数索引
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

PostgreSQL 支持函数索引：CREATE INDEX ON users (UPPER(email));，专门应对「非要在列上用函数」的情况。

② 用 LIKE 且以通配符开头

-- ❌ 失效：开头就是 %，无法利用「前缀有序」
WHERE name LIKE '%三'
WHERE name LIKE '%三%'

-- ✅ 有效：前缀是确定的
WHERE name LIKE '张%'

道理和电话簿一样：知道开头才能定位，开头是通配符就只能全翻。

③ 隐式类型转换

-- 假设 phone 是 VARCHAR 类型
-- ❌ 传了数字，触发隐式转换，可能导致索引失效
WHERE phone = 13800138000

-- ✅ 类型对上
WHERE phone = '13800138000'

④ 违反最左前缀（见 2.1）

⑤ 区分度太低 / 数据量太小

数据库是「智能」的：如果它估算「走索引还不如全表扫描快」
（比如表就几百行，或者这个条件能匹配到大半张表），
它会主动放弃索引，选择全表扫描。这不是 bug，是优化器的正确决策。

2.4 索引优化的实战流程

1. 找出慢查询（看慢查询日志，或监控）
2. 对慢 SQL 执行 EXPLAIN，看它有没有走索引、走的哪个（见第 03 章）
3. 如果全表扫描 → 分析 WHERE / JOIN / ORDER BY 涉及哪些列
4. 针对性地建索引（优先联合索引、考虑覆盖索引）
5. 检查是否踩了「索引失效」的坑，改写 SQL
6. 再 EXPLAIN 验证，确认走了索引、变快了

本章小结

下一章预告：反复提到 EXPLAIN。它到底怎么看？怎么判断一条 SQL 是快是慢、走没走索引？下一章教你读懂执行计划。

上一章 ← 01 - 索引的本质 ｜ 下一章 → 03 - 用 EXPLAIN 读懂执行计划