索引优化：让查询从全表扫描到秒回

你写了个查询，数据量小的时候嗖一下就出结果。等表涨到千万行，页面转圈十几秒还超时。DBA 过来瞥一眼：“你这查询没走索引，全表扫的。“你一脸懵：索引到底是什么？为什么加了就快？

这一篇我们讲清索引的原理、怎么建、怎么验证生效，以及为什么”建了索引还是慢”。

索引到底是什么

没有索引时，数据库要找一条数据，只能从头到尾逐行扫描（全表扫描），表越大越慢。索引就像书的目录：告诉你”第 5 章在第 80 页”，你直接翻过去，不用一页页翻。

数据库最常用的索引是 B+ 树（一种平衡多叉树）。它把数据按索引列排序组织，查找时从树根出发，几步就能定位到目标行，复杂度从”扫全表 O(N)“降到”树高 O(log N)“。千万行数据的树高也就三四层，所以极快。

什么时候该建索引

经验法则：

WHERE 条件里的列：经常用来过滤的字段（如 user_id、status）该建。
JOIN 的关联列：两表关联字段必须建，否则 JOIN 退化成笛卡尔积。
ORDER BY / GROUP BY 的列：索引本身有序，能避免额外排序。
高区分度的列优先：性别只有男女，建索引意义不大；手机号、用户 ID 区分度高，建了效果好。

怎么验证：用 EXPLAIN 看执行计划

别靠猜，用 EXPLAIN 让数据库告诉你它打算怎么执行：

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

重点看几个字段：

type：访问类型。ALL 是灾难（全表扫描），ref/range/const 是好的。目标是别出现 ALL。
key：实际用到的索引。如果是 NULL，说明没用上索引。
rows：预计扫描的行数。越小越好。
Extra：出现 Using filesort（额外排序）、Using temporary（用临时表）通常是性能信号。

对比两条：有索引时 type=ref、key=idx_user_id、rows 很小；没索引时 type=ALL、rows 接近全表。

索引失效：建了却没用上

这是最气人的情况——明明建了索引，查询还是全表扫。常见原因：

对索引列做函数/运算：WHERE YEAR(create_time) = 2026 会让索引失效。改成 create_time >= '2026-01-01' AND create_time < '2027-01-01'。
隐式类型转换：列是字符串，你传了数字 WHERE phone = 13800138000，MySQL 会转类型导致失效。务必类型匹配。
前导模糊查询：LIKE '%abc' 左模糊无法用索引（不知道开头）；LIKE 'abc%' 右模糊可以用。
最左前缀失效：联合索引 (a,b,c)，查询只用 b 或 c 而没用 a，索引用不上（必须从最左列开始）。
用 OR 连接非索引列：WHERE a=1 OR b=2，若 b 无索引，整体可能放弃索引走全表。

联合索引与最左前缀

实际中常建联合索引（多列组合），比如 (user_id, status, create_time)。它遵循最左前缀原则：查询条件必须从最左列开始连续使用，索引才有效。

WHERE user_id=1 AND status=2 → 用上（用了前 two）。
WHERE user_id=1 AND create_time>'...' → 只用到 user_id（跳过 status，create_time 部分失效）。
WHERE status=2 → 完全用不上（没最左列）。

设计联合索引时，把区分度高、最常用作过滤的列放左边。还能用”覆盖索引”技巧：查询的所有字段都在索引里，数据库不用回表，更快。

常见坑位提醒

索引越多越好：错。索引占空间，且拖慢写入（每插入/更新都要维护索引）。写多读少的表，索引要克制。
给低区分度列建索引：如性别、状态(只有几个值)，优化器可能直接放弃索引走全表。这种列更适合放联合索引的”辅助”位置。
不看执行计划就调优：凭感觉加索引容易加错。先 EXPLAIN 定位慢在哪。
忘了定期分析统计信息：数据库靠统计信息选执行计划，统计过时可能选错索引。定期 ANALYZE TABLE。
长字符串直接建索引：对很长的文本列建索引很占空间，可用”前缀索引”CREATE INDEX idx ON t(col(20)) 只取前 20 字符。

聚簇索引 vs 非聚簇索引

以 MySQL InnoDB 为例，要分清两种索引：

聚簇索引（Clustered）：叶子节点直接存整行数据，一个表只能有一个（通常就是主键）。按主键查极快，因为找到索引就拿到数据。
非聚簇索引（Secondary，二级索引）：叶子节点存的是主键值，不是行数据。通过它查非主键列时，要先拿到主键，再”回表”去聚簇索引取完整行——这一步叫回表。

理解了回表，就懂了”覆盖索引”为何快：如果查询的所有列都已在二级索引里（如 SELECT id, user_id FROM orders WHERE user_id=1，而索引是 (user_id, status) 且只取这两个），数据库不用回表，直接在索引里拿到结果，速度翻倍。所以设计索引时，可把”常一起查的字段”放进联合索引，做成覆盖索引。

索引选择性：建还是不建

不是所有列都值得建索引。用**选择性（区分度）**判断：

选择性 = 不重复值的数量 / 总行数

接近 1（如用户 ID、手机号）选择性高，建索引效果好；接近 0（如”性别”只有男女、“是否删除”只有 0/1）选择性极低，优化器往往直接放弃索引走全表，因为”走索引再回表”反而比全表扫更慢。这类低区分度列，适合作为联合索引的辅助列（放后面），而非单独建索引。

实战：完整读一次执行计划

一条真实慢查询 SELECT * FROM orders WHERE status='paid' AND create_time > '2026-01-01' ORDER BY create_time LIMIT 20，建联合索引 (status, create_time) 后 EXPLAIN 可能显示：

type: ref：用了索引等值匹配 status。
key: idx_status_ct：实际命中我们的联合索引。
Extra: Using index condition：索引条件下推，高效。
rows: 35：预计只扫 35 行。

对比没索引时 type: ALL、rows: 980000——差距一目了然。所以调索引，永远用 EXPLAIN 验证，别凭感觉。

索引与排序的隐藏陷阱

排序（ORDER BY）和索引关系密切，常见的坑：

排序字段不在索引里：数据库得把结果集拉出来再排序，出现 Using filesort，数据量大时极慢。把排序列加进联合索引（放在等值条件列之后）可避免。
排序方向不一致：联合索引 (a ASC, b ASC)，若查询 ORDER BY a ASC, b DESC，b 的方向反了，索引无法同时服务两种方向，可能退化为 filesort。MySQL 8 起支持降序索引，可建 (a ASC, b DESC) 精确匹配。
范围查询打断索引：联合索引 (a, b, c)，若 a=1 AND b>10 AND c=2，b 是范围，c 就用不上索引了（范围后的列失效）。设计时要权衡”范围列放最后”。

什么时候”不该”建索引

前面说该建，但也要知道反例，避免滥用：

写多读少的表：每写一次都要维护所有索引，索引越多写入越慢。此类表索引要克制，只保留最关键的一两个。
低区分度列单独建：如”性别""是否删除”，优化器往往放弃索引走全表，单独建意义不大，放进联合索引当辅助列更合适。
小表：几千行的小表，全表扫本身就快，建索引反而增加维护成本和存储空间，收益甚微。
会频繁变更的列：索引列频繁更新，维护代价高，且易产生索引碎片，需定期 OPTIMIZE/REBUILD。

索引是”以空间换时间、以写开销换读速度”的权衡，建之前先想清楚”读多还是写多、查得多不多”。

小测验：看看你掌握了没

问题一：为什么索引能加速查询？答案：B+ 树把数据有序组织，查找从 O(N) 全表扫降到 O(log N)。
问题二：LIKE '%手机' 能走索引吗？答案：不能，左模糊无法利用索引有序性；右模糊 手机% 可以。
问题三：联合索引 (a,b,c)，只查 b=1 为什么失效？答案：违反最左前缀原则，必须从最左列 a 开始。

这一篇你该记住的

索引像目录，基于 B+ 树把查找从全表扫 O(N) 降到 O(log N)。
该建：WHERE/JOIN/ORDER BY 高频列、高区分度列优先。
用 EXPLAIN 验证：type 别是 ALL、key 非 NULL、rows 越小越好。
失效写法：列上函数、隐式类型转换、左模糊、违反最左前缀、OR 接非索引列。
索引非越多越好，会拖慢写入；长文本用前缀索引。

索引是单条 SQL 的加速器，但慢查询往往不止索引问题——下一篇我们系统讲慢查询定位与 SQL 改写。

数据库