我明明给表建了联合索引,有些查询却还是慢得全表扫描,EXPLAIN 一看根本没走索引,因为我的查询条件不符合最左前缀:一次联合索引最左前缀的深度复盘

那个慢查询是"建了索引却还慢"才暴露的:我有张订单表,经常按用户、状态、时间查,于是建了个联合索引 (user_id, status, created_at),以为"这几个字段的查询都能走这个索引、飞快"。可线上有些查询慢得离谱、像全表扫描。我用 EXPLAIN 一看,傻眼了:那些慢查询根本没走这个联合索引(type=ALL,全表扫)。它们是这样的:一个是 WHERE status = 'paid'(只按状态查,没带 user_id);另一个是 WHERE status = 'paid' AND created_at > '...'(跳过了最左的 user_id)。我查清联合索引的工作原理,才看明白,后背发凉:问题出在我没遵循联合索引的"最左前缀原则(leftmost prefix)"。联合索引 (user_id, status, created_at),是按"先 user_id、再 status、再 created_at"的顺序层层排序组织的(就像先按姓排、同姓的再按名排、同名的再按年龄排的电话簿);它的使用条件是:查询必须从最左列(user_id)开始、连续地用上这些列,才能利用这个索引;而我的查询:只查 status(没带最左的 user_id)→ 用不上索引(就像电话簿按姓排,你却只知道名、不知道姓,没法用它快速定位);status + created_at(跳过了 user_id)→ 也用不上(跳过了最左列,索引的排序就用不上了)。根本原因是:联合索引按列顺序层层排序,只有查询条件"从最左列开始、连续匹配"才能用上它(最左前缀原则);跳过最左列或不带最左列,索引就用不上、退化成全表扫描。问题的根,是查询没遵循联合索引的最左前缀原则(没带最左列 user_id),导致用不上索引、全表扫描。这篇就把这次"联合索引最左前缀"的坑,从头到尾复盘一遍。

故障现场:建了联合索引,查询却没走

问题在于查询条件不符合联合索引的最左前缀:

-- 建了联合索引 (user_id, status, created_at)
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);

-- ✓ 能用上索引的查询(符合最左前缀: 从user_id开始连续):
SELECT * FROM orders WHERE user_id = 100;                              -- 用到 (user_id)
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';          -- 用到 (user_id, status)
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND created_at > '2026-01-01';  -- 全用上

-- ✗ 用不上索引的查询(违反最左前缀):
SELECT * FROM orders WHERE status = 'paid';                            -- ✗ 没带最左列user_id → 用不上!全表扫
SELECT * FROM orders WHERE status = 'paid' AND created_at > '...';     -- ✗ 跳过了最左列user_id → 用不上!
SELECT * FROM orders WHERE created_at > '...';                         -- ✗ 没从最左开始 → 用不上!
-- EXPLAIN 看这些: type=ALL(全表扫描), key=NULL(没用索引), rows很大 → 慢。

-- 为什么? 联合索引(user_id, status, created_at) 是按列顺序"层层排序"的:
-- 1. 先按 user_id 排序; user_id相同的, 再按 status 排序; status也相同的, 再按 created_at 排序;
-- 2. 就像一本电话簿: 先按姓排, 同姓按名排, 同名按出生年排;
-- 3. 你要用它快速查找, 必须"从最左的列(姓)开始、连续地"给条件:
--    - 知道姓(user_id): 能快速定位到那一片 → 用得上;
--    - 只知道名(status)、不知道姓: 整本簿子的"名"是乱序的(只有同姓内才按名排) → 没法用 → 全表扫;
--    - 知道姓+出生年、但不知道名(跳过status): 只能用上"姓"那一层, 后面用不上(下面解释)。

-- 最左前缀原则: 联合索引(a,b,c), 查询条件要从a开始、连续匹配, 才能用上(用到哪连续匹配到哪):
--   where a / where a,b / where a,b,c  → 能用(分别用上1/2/3列);
--   where b / where b,c / where c       → 用不上(没从a开始);
--   where a,c (跳过b)                    → 只能用上a, c用不上(中间断了)。
-- 注意: 优化器有时会调整AND条件顺序, 但"必须包含最左列"是关键; 范围查询(>,<,like)后面的列也用不上。

-- 关键: 联合索引按列顺序层层排序, 查询必须从最左列开始、连续匹配才能用上(最左前缀原则);
--       没带最左列/跳过最左列, 就用不上索引、退化成全表扫描——建了索引不等于查询一定会用它。

第一次 EXPLAIN 看到那些慢查询 key=NULL(没用索引)时,我又懊恼又意外:"我明明建了包含 status 的联合索引啊,按 status 查怎么会用不上?完全没想到联合索引还讲究'从最左列开始'这个规矩。"这个坑最隐蔽的地方在于:它给人"建了索引就万事大吉"的错觉——索引确实建了、有些查询也确实快;只有那些"不符合最左前缀"的查询才慢,而你不 EXPLAIN 根本不知道它没走索引;数据量小时全表扫也不慢(发现不了),数据一大才暴露。下面就来拆解,联合索引该怎么建、怎么用。

第一件事:搞懂联合索引与最左前缀原则

我顺着这次事故,把联合索引的工作原理彻底理清了。

联合索引为什么讲"最左前缀"? 怎么用得上?

【核心: 联合索引(a,b,c)按列顺序层层排序; 查询必须从最左列a开始、连续匹配才能用上(用到哪连续到哪); 没带最左列/跳过列就用不上】

1. 联合索引的结构: 按列顺序"层层排序"
   - 索引(a,b,c): 先按a排; a相同的按b排; a、b都相同的按c排;
   - 类比电话簿: 先按姓、同姓按名、同名按年龄排——是个"多级有序"的结构。

2. 为什么必须"从最左列开始、连续匹配"(最左前缀):
   - 索引只在"前面的列确定"时, 后面的列才是有序的(同姓的人, 名才是有序的);
   - 所以只有从最左列(a)开始、连续给条件, 才能利用这个有序性快速定位;
   - 跳过a直接查b: 整个索引里b不是全局有序的(只在每个a内部有序) → 用不上;
   - 这就是"最左前缀原则": where条件能从最左列连续匹配几列, 就用上几列。

3. 哪些能用、哪些不能(索引(a,b,c)):
   - where a / a,b / a,b,c → 能用(分别用1/2/3列);
   - where b / c / b,c → 用不上(没从a开始);
   - where a,c(跳过b) → 只用上a(c因为中间断了用不上);
   - where a + b范围(b>x) + c → 用上a、b, 但c用不上(范围列之后的列用不上);
   - order by 也遵循最左前缀(按索引顺序排能用索引排序)。

4. 为什么这么设计(不是bug, 是B+树索引的本性):
   - 一个联合索引在物理上只能按一种顺序排; 这个顺序决定了它能高效服务哪些查询;
   - 它不可能"对任意列组合都有序"——那需要给每种组合都建索引(代价大)。

5. 怎么办: 按查询模式设计索引
   - ① 索引列顺序很重要: 把"最常用作等值查询、选择性高"的列放最左;
   - ② 按你实际的查询条件来建: 如果常按status单独查, 就给status建索引(或调整联合索引顺序);
   - ③ 一个联合索引能服务"它的各个最左前缀"的查询(建(a,b,c)≈同时有(a)、(a,b)、(a,b,c)的能力);
   - ④ 范围查询的列放最后(范围列之后的列用不上);
   - ⑤ 用EXPLAIN验证查询到底走没走索引(key/type/rows), 别想当然。

一句话: 联合索引(a,b,c)按列顺序层层排序, 查询必须从最左列开始、连续匹配才能用上(最左前缀原则); 没带
   最左列/跳过列就用不上、全表扫; 按实际查询模式设计索引列顺序、范围列放最后、用EXPLAIN验证。

这套认知,是整个坑的根。联合索引的结构:按列顺序"层层排序"——(a,b,c) 先按 a 排、a 相同按 b 排、a 和 b 相同按 c 排,像电话簿先按姓再名再年龄,是多级有序结构。为什么必须从最左列开始连续匹配:索引只在"前面的列确定"时后面的列才有序(同姓的名才有序),所以跳过 a 直接查 b,b 不是全局有序的→用不上;这就是最左前缀原则。哪些能用:where a/a,b/a,b,c 能用;where b/c/b,c 用不上;where a,c(跳过 b)只用上 a;范围列之后的列用不上。为什么这么设计:B+树索引物理上只能按一种顺序排,这个顺序决定它能服务哪些查询,不可能对任意列组合都有序。怎么办:按查询模式设计索引列顺序(常用等值且选择性高的放最左)、按实际查询建、一个联合索引服务它的各个最左前缀、范围列放最后、用 EXPLAIN 验证。一句话:联合索引(a,b,c)按列顺序层层排序,查询必须从最左列开始、连续匹配才能用上(最左前缀原则);没带最左列/跳过列就用不上、全表扫;按实际查询模式设计索引列顺序、范围列放最后、用 EXPLAIN 验证。

第二件事:正解——按查询模式设计索引、用 EXPLAIN 验证

知道了最左前缀,正解就清楚了:让索引列顺序匹配查询模式,并用 EXPLAIN 验证。

-- 假设业务上有两类高频查询:
--   A. WHERE user_id=? AND status=? AND created_at>?   (查某用户某状态某时间后的订单)
--   B. WHERE status=? AND created_at>?                  (查某状态某时间后的订单, 跨用户, 如运营后台)

-- 错误做法: 只建一个(user_id, status, created_at), 以为够了
--   → 查询A能走(从最左user_id开始连续) ✓
--   → 查询B用不上(没带最左user_id) ✗ → 全表扫, 慢!

-- 正解1: 按各自的查询模式, 建合适的索引
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);  -- 服务查询A
CREATE INDEX idx_status_time      ON orders (status, created_at);           -- 服务查询B
-- 现在 A 走 idx_user_status_time, B 走 idx_status_time, 都快。

-- 正解2: 索引列顺序的讲究——把"等值查询、选择性高(区分度大)"的列放最左
--   (status, created_at): status是等值查询放前, created_at是范围查询放后(范围列之后的列用不上);
--   若把范围列放前: (created_at, status) 查 created_at>? AND status=? → 只能用上created_at, status用不上。

-- 正解3: 一个联合索引能服务"它的各个最左前缀"——别重复建冗余索引
--   有了(user_id, status, created_at), 就不必再单独建(user_id)、(user_id, status):
--   因为查 user_id、查 user_id+status 都能走这个联合索引的最左前缀(节省索引空间和写入开销)。

-- 正解4: 永远用 EXPLAIN 验证查询有没有走索引、走的哪个
EXPLAIN SELECT * FROM orders WHERE status='paid' AND created_at > '2026-01-01';
--   看 key: 显示用的哪个索引(NULL=没走索引);
--   看 type: ref/range=用上索引, ALL=全表扫描(慢);
--   看 rows: 预估扫描行数, 越小越好;
--   看 Extra: Using index(覆盖索引, 好) / Using filesort(没用索引排序, 可能需优化)。

-- 正解5: 覆盖索引——查询要的列都在索引里, 不用回表(更快)
CREATE INDEX idx_cover ON orders (user_id, status, amount);
SELECT amount FROM orders WHERE user_id=100 AND status='paid';  -- 要的列都在索引, Using index, 不回表。

-- 核心: 索引不是建了就万能, 它的"能力"由列顺序决定(只服务符合最左前缀的查询);
--   按实际查询模式设计列顺序(等值在前、范围在后、选择性高的靠前)、必要时建多个索引、用EXPLAIN验证。

这套正解的关键,是让索引去匹配查询,而不是建了索引就指望所有查询都用它。按查询模式建索引:有几类高频查询,就分析每类需要什么样的索引,必要时建多个(查询 A 和查询 B 用不同的索引)。列顺序的讲究:等值查询的列放前、范围查询的列放后(范围列之后的列用不上)、选择性高的列靠前。善用最左前缀避免冗余:一个 (a,b,c) 索引能服务 (a)、(a,b)、(a,b,c) 的查询,不必重复建。覆盖索引:查询要的列都在索引里,不用回表,更快。而贯穿始终的铁律是:用 EXPLAIN 验证——看 key(走没走索引、走哪个)、type(ref/range 还是 ALL)、rows(扫多少行)、Extra(覆盖索引/filesort),别凭感觉以为"建了就走"。

第三件事:其他几个"索引建了却用不上"的坑

顺着最左前缀,我把"索引失效"的常见原因也一并理了:

-- 坑1: 在索引列上用函数/运算 → 索引失效(同342篇隐式转换的亲戚)
WHERE YEAR(created_at) = 2026;            -- ✗ 对索引列用函数, 用不上created_at的索引
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';  -- ✓ 改成范围, 能用索引

-- 坑2: 索引列与常量类型不匹配, 隐式转换 → 失效(342篇)
WHERE phone = 13800138000;                -- phone是varchar, 传数字触发转换, 用不上索引
WHERE phone = '13800138000';              -- ✓ 类型一致

-- 坑3: 前导模糊匹配 LIKE '%xxx' → 用不上索引(像电话簿不知道开头, 没法定位)
WHERE name LIKE '%张';                     -- ✗ 以%开头, 用不上
WHERE name LIKE '张%';                     -- ✓ 后缀模糊, 能用(最左前缀思想: 知道开头)

-- 坑4: OR 连接的条件, 有一边没索引 → 可能整个用不上
WHERE user_id = 100 OR remark = 'x';      -- remark无索引 → 可能全表扫(可用union或给remark加索引)

-- 坑5: 范围查询后面的列用不上(最左前缀的范围版)
-- 索引(a, b, c), WHERE a=1 AND b>10 AND c=5 → 用上a、b, c用不上(b是范围, 之后断了)。

-- 坑6: != / NOT IN / IS NOT NULL 等, 选择性差时优化器可能放弃索引走全表(不绝对, EXPLAIN看)。

-- 坑7: 数据量太小时, 优化器认为全表扫更快, 故意不走索引(正常, 数据量大了会走)。

-- 共同的根: 索引能不能用, 取决于"查询的写法是否让索引的有序性派上用场";
--   破坏了这个有序性(函数运算、类型转换、前导模糊、跳过最左列、范围后的列), 索引就用不上。

这些坑看似五花八门,根却是同一个:索引(尤其 B+树索引)的本事,来自它把数据按某种顺序排好了;任何"破坏了这个有序性的利用"的写法——对索引列做函数/运算、类型隐式转换、前导模糊 LIKE '%x'、跳过最左列、范围查询之后的列——都会让索引用不上。理解了这个根("索引靠有序性,别破坏对有序性的利用"),就不用死记硬背每条规则了。

第四件事:能用 / 用不上索引——一张对照表

我把联合索引 (a, b, c) 上各种查询能不能用索引,整理成一张表,贴在了团队的 SQL 规范里:

这张表的规律一目了然:能走索引的,都是"从最左列 a 开始、连续匹配"的(用到哪连续到哪);用不上的,要么没带最左列(查 b、查 b,c),要么对索引列做了函数/前导模糊(破坏了有序性的利用);"部分能用"的,是中间断了(跳过 b)或遇到范围列(b>?之后的 c)。记住一条:索引能用上几列,看查询从最左列起能"连续等值匹配"到第几列(遇到范围列还能再用上那一列、但之后就断了)。

第五件事:索引"建了"不等于"用得上"——几组容易混淆的概念

这次事故也让我厘清了几组关于索引的、容易想当然的概念:

这张表里,我栽的就是第一行和第二行:以为"建了包含 status 的联合索引,按 status 查就会走",却不知道列顺序决定一切、status 不在最左就用不上。厘清这些,核心是一个意识:索引是个"有特定使用条件(最左前缀、不破坏有序性)的工具",不是"建了就对所有相关查询生效的魔法";要让查询去匹配索引的使用条件,并始终用 EXPLAIN 验证它真的生效了。

第六件事:建索引/排查慢查询时,我现在的自检习惯

现在每当我要建索引、或排查一个慢查询,我都会先按这张图问自己:

这张图的精髓,是"先看实际查询、等值列在前范围列在后、多类查询建多个索引、EXPLAIN 验证"。设计前先看高频查询、列顺序等值在前范围在后/选择性高靠前、多模式建多个索引、最后EXPLAIN 验证生效。这套习惯,让我从"凭感觉建个联合索引就完事"变成了"按查询设计、用 EXPLAIN 验证"——核心始终是:联合索引按列顺序层层排序、只服务符合最左前缀的查询;按实际查询模式设计列顺序(等值前、范围后、选择性高靠前)、必要时建多个索引、永远用 EXPLAIN 验证索引真的生效。

我立下的几条规矩

这场"建了联合索引、有些查询却全表扫"的事故,换来了我用数据库索引时,刻进骨子里的几条铁律:

1. 联合索引 (a,b,c) 按列顺序层层排序,查询须从最左列开始、连续匹配才能用上(最左前缀)。
2. 没带最左列、或跳过中间列,索引就用不上、退化成全表扫描。
3. 设计列顺序:等值查询列在前、范围查询列在后(范围列之后的列用不上)、选择性高的靠前。
4. 有多类查询模式,就按各模式建多个索引,别指望一个联合索引全包。
5. 一个 (a,b,c) 索引已覆盖其最左前缀 (a)、(a,b),不必重复建。
6. 别在索引列上用函数/运算,别让类型隐式转换,别用前导模糊 LIKE '%x'。
7. 永远用 EXPLAIN 验证查询是否走索引(看 key/type/rows/Extra),别想当然。

附:索引设计与排查的命令清单

最后,把我现在排查索引问题常用的命令,贴成一份清单:

-- 看一条查询有没有走索引(最常用)
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status='paid';
--   关注: key(用的索引,NULL=没走)、type(ALL=全表扫,ref/range=走了)、rows(扫描行数)、Extra。

-- 看一张表已有哪些索引
SHOW INDEX FROM orders;

-- 看更详细的执行信息(MySQL 8+, 实际执行并统计)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status='paid';

-- 创建联合索引(注意列顺序: 等值列在前、范围列在后、选择性高靠前)
CREATE INDEX idx_status_time ON orders (status, created_at);

-- 删除用不上/冗余的索引(索引也有写入和存储成本)
DROP INDEX idx_unused ON orders;

-- 查找未走索引的慢查询(开慢查询日志后)
-- slow_query_log=ON; long_query_time=1;  然后分析 slow.log 或用 pt-query-digest。

这份清单的核心,还是那句话:EXPLAIN 是验证索引是否真正生效的"体检仪"——任何时候怀疑查询慢、怀疑索引没用上,先 EXPLAIN,看 key/type/rows,用事实说话,而不是凭"我建过索引啊"的想当然。

写在最后

回头看,这场由"建了联合索引、查询却不符合最左前缀"引发的、慢得像全表扫描的事故,真正教给我的,远不止"最左前缀原则"这一个知识点。它让我对"一个工具'具备某种能力', 和它'在你的用法下真的发挥出这种能力', 是两回事; 工具往往有它'发挥作用的条件/正确的使用姿势', 不按这个姿势用, 它就'形同虚设'",有了一次刻骨的体会。我栽跟头,是因为我把"我建了索引"等同于了"查询就会享受到索引带来的快"——我以为"拥有工具"就自动等于"获得工具的好处"。可我忽略了:索引这个工具, 有它"发挥作用的条件"(查询符合最左前缀、不破坏有序性的利用);我建了索引(拥有了工具), 却用了一种"不符合它使用条件"的查询姿势(不带最左列), 于是这个工具就静静地躺在那里、一点忙没帮上, 查询还是全表扫。这让我领悟到一个关于"能力与发挥能力的条件"的深刻认知:很多工具、方法、制度、能力,都不是"有了/建了就自动生效"的,而是"满足它的使用条件、用对方式才生效"的——索引要符合最左前缀才走、缓存要命中才快、文档要被读才有用、规则要被遵守才有约束力、好的建议要被听进去才有价值;"拥有一个好东西" 只是第一步, "在正确的条件下、用正确的方式让它发挥作用" 才是它真正产生价值的关键; 前者容易(花钱/花时间建), 后者才考验真功夫。这给了我一种使用任何工具/方法时的清醒:引入或使用一个工具/方法/能力时,不要满足于"我有了/我用了",而要追问"它发挥作用的条件是什么?我现在的用法满足这个条件吗?它真的生效了吗(如何验证)?"——像我现在用 EXPLAIN 去验证索引"到底走没走"那样, 去验证我引入的工具"到底有没有真的发挥作用", 而不是默认"有了就有效";"区分'拥有能力'和'能力被正确触发/发挥', 并主动验证后者",是避免'以为有了某个东西保底、实则它根本没生效'式幻觉的关键。认清拥有工具不等于发挥出工具的能力、工具有其生效条件和正确用法、要主动验证它是否真的生效——这,是我用一次联合索引最左前缀的事故,换来的、关于数据库、也关于如何真正让工具发挥价值的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次建完索引后,顺手用 EXPLAIN 验一下查询到底走没走它,那我对着那条"建了索引却全表扫"的慢查询排查的这段时间,就值了。

—— 别看了 · 2026