给表加个索引，查询从 3 秒变 30 毫秒——几乎每个后端开发者都经历过这种「魔法时刻」。但索引不是玄学：知道它的数据结构，就能预判什么查询能用它、什么写法会让它失效。本文以 InnoDB（MySQL 默认引擎）为背景。
全表扫描为什么慢
没有索引时，找 WHERE id = 9527 要把表里的数据页从头到尾翻一遍，一百万行就是一百万次比较，还伴随大量磁盘 I/O。优化目标很明确：少读数据页。
B+ 树：为磁盘设计的平衡树
二叉查找树在内存里很好，但树太深——百万级数据也有 20 层，每层一次磁盘 I/O 就受不了。B+ 树的做法是让每个节点「变胖」：一个节点放一个数据页（InnoDB 默认 16KB），存几百到上千个键。这样千万级数据的索引也只有 3~4 层，绝大多数查询 3 次 I/O 内命中。
B+ 树的两个结构特征决定了它的使用规则：
- 数据全在叶子节点，非叶子节点只存导航用的键。
- 叶子节点按键有序并用链表串联——所以范围查询（
BETWEEN、>）和排序（ORDER BY 索引列）可以顺着叶子链表扫，不需要回树。
类比：非叶子节点是书的章节目录，叶子链表是按页码排好的正文。
主键索引与回表
InnoDB 的表数据本身就按主键组织成一棵 B+ 树（聚簇索引），叶子节点存整行。二级索引（普通索引）的叶子节点存的是「索引列 + 主键值」。所以用二级索引查到后，如果 SELECT 的列不在索引里，还要拿主键再去主键树查一次——这叫回表。
覆盖索引是避免回表的技巧：SELECT order_id, status FROM orders WHERE status = 'paid'，若 (status, order_id) 上有索引，需要的列全在索引里，EXPLAIN 的 Extra 列会显示 Using index，一跳到位。
联合索引与最左前缀
索引 (a, b, c) 的排序规则是：先按 a 排，a 相同按 b，b 相同按 c。因此它能服务：
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3
但不能直接服务 WHERE b = 2（跳过 a）或 WHERE a = 1 AND c = 3（跳过 b 后 c 不再有序，c 条件只能过滤不能导航）。这就是最左前缀原则：条件必须从索引最左列连续匹配。范围条件（>、<、LIKE 'x%'）之后，索引的导航能力也到此为止，后面的列只能用来过滤。
七种常见的索引失效写法
- 对索引列做运算：
WHERE DATE(create_time) = '2026-09-27'→ 改成范围create_time >= '2026-09-27' AND create_time < '2026-09-28'。 - 函数包裹：
WHERE UPPER(email) = '[email protected]'。 - 隐式类型转换：字符串列用数字查
WHERE phone = 13800000000，列被套了 CAST，索引失效。手机号、验证码列务必用字符串类型并带引号查询。 - 前导通配符：
LIKE '%abc'无法利用有序性；LIKE 'abc%'可以。 - OR 连接非索引列：
WHERE a = 1 OR d = 2，d 无索引时整体退化为全表扫描。 !=、NOT IN：选择性太差时优化器宁可全表扫（严格说这是优化器的成本选择，不是绝对失效）。- 索引列参与表达式：
WHERE age + 1 > 30→ 写成age > 29。
用 EXPLAIN 验证，而不是猜
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';重点看四列：
| 列 | 关注点 |
|---|---|
type | 从好到差大致是 const > eq_ref > ref > range > index > ALL（全表扫描，危险信号） |
key | 实际用了哪个索引；NULL 就是没用 |
rows | 预估扫描行数，越小越好 |
Extra | Using index（覆盖索引，好事）；Using filesort、Using temporary（通常需要优化） |
EXPLAIN ANALYZE（MySQL 8.0.18+）还能给出实际执行耗时，比预估更可靠。
索引不是免费的
每个索引都让写入变慢（INSERT/UPDATE 要维护树），还占磁盘。给表设计索引的原则是：按真实查询模式建少量联合索引，而不是给每列都建一个。慢查询日志（slow_query_log + long_query_time = 1）是发现「该建什么索引」的最佳输入。
延伸阅读
分享文章
生成精美分享图或复制链接，与更多人分享本文。
继续阅读
最后更新于 ，距今已过 24 天
部分内容可能已过时