联合索引与索引失效¶

核心问题：为什么建了索引还是慢？索引在什么情况下会失效？

它解决了什么问题？¶

索引失效是线上慢查询最常见的原因。很多开发者建了索引却不生效，根本原因是不理解 B+ 树的排序规则。掌握本章后，你能： - 设计合理的联合索引，减少索引数量 - 快速判断 SQL 是否会走索引 - 用 EXPLAIN 验证索引是否生效

联合索引最左前缀原则¶

-- 建立联合索引：INDEX(a, b, c)

-- ✅ 能走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 2          -- a 走索引，b 范围查询后 c 不走

-- ❌ 不能走索引
WHERE b = 2                    -- 跳过了 a
WHERE b = 2 AND c = 3          -- 跳过了 a
WHERE c = 3                    -- 跳过了 a、b

flowchart LR
    subgraph "联合索引(a, b, c)的排序规则"
        A["先按 a 排序"] --> B["a 相同时按 b 排序"] --> C["a、b 相同时按 c 排序"]
    end
    note["跳过 a 直接查 b\n= 在按姓氏排序的电话簿里按名字查\n无法利用有序性 → 全表扫描"]

为什么有最左前缀限制：联合索引 (a, b, c) 在 B+ 树中按 a → b → c 的顺序排列。如果跳过 a 直接查 b，相当于在一本按姓氏排序的电话簿里按名字查找，无法利用有序性，只能全表扫描。

索引失效的 5 大场景¶

❌ 场景1：对索引列使用函数¶

-- ❌ 函数破坏了索引的有序性
WHERE YEAR(create_time) = 2024

-- ✅ 改为范围查询
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'

❌ 场景2：隐式类型转换¶

-- ❌ phone 是 varchar，传入数字，MySQL 自动转换相当于加了函数
WHERE phone = 13800138000

-- ✅ 类型匹配
WHERE phone = '13800138000'

为什么类型转换会导致索引失效：MySQL 对 varchar 字段做数字比较时，相当于执行了 CAST(phone AS SIGNED)，这个隐式函数操作破坏了索引的有序性。

❌ 场景3：LIKE 前缀通配符¶

-- ❌ 前缀通配符，无法利用 B+ 树的有序性
WHERE name LIKE '%Tom%'
WHERE name LIKE '%Tom'

-- ✅ 后缀通配符可以走索引
WHERE name LIKE 'Tom%'

❌ 场景4：OR 条件中有非索引列¶

-- ❌ name 无索引，整个 OR 条件退化为全表扫描
WHERE id = 1 OR name = 'Tom'

-- ✅ 两个条件都有索引才能走索引
-- 或者改用 UNION ALL
SELECT * FROM user WHERE id = 1
UNION ALL
SELECT * FROM user WHERE name = 'Tom'

❌ 场景5：联合索引不满足最左前缀¶

-- 索引：INDEX(a, b, c)
-- ❌ 跳过最左列 a
WHERE b = 2 AND c = 3

索引设计原则¶

原则	说明
区分度高的列放前面	性别（区分度低）不适合单独建索引
覆盖查询的列	把 WHERE + SELECT 的列都放进联合索引
避免冗余索引	`(a)` 和 `(a, b)` 同时存在，前者是冗余的
索引不是越多越好	每个索引都要维护，写操作会变慢

工作中的坑¶

-- ❌ 坑：对 varchar 类型的 phone 字段传入数字
-- 线上慢查询，EXPLAIN 显示 type=ALL（全表扫描）
SELECT * FROM user WHERE phone = 13800138000;

-- ✅ 修复：类型匹配
SELECT * FROM user WHERE phone = '13800138000';
-- EXPLAIN 显示 type=ref（走索引）

面试高频问题¶

Q：联合索引的最左前缀原则是什么？为什么有这个限制？

联合索引 (a, b, c) 在 B+ 树中按 a → b → c 顺序排列，查询必须从最左列开始，否则无法利用有序性。就像按姓氏排序的电话簿，跳过姓氏直接按名字查，只能逐页翻。

Q：哪些情况会导致索引失效？

① 对索引列使用函数；② 隐式类型转换；③ LIKE 前缀通配符；④ OR 条件中有非索引列；⑤ 联合索引不满足最左前缀。

Q：如何验证 SQL 是否走了索引？

使用 EXPLAIN 查看执行计划，重点看 type（ALL 表示全表扫描）、key（实际使用的索引）、Extra（Using index 表示覆盖索引）。