06｜索引原理

前面写的查询，表里几行数据时秒回。但表里几百万行后，同样的查询要几秒甚至几十秒。差别在哪？在于有没有索引、索引建得对不对。

索引是让查询少扫数据的机制。本篇讲索引怎么工作、怎么设计联合索引、什么情况索引会失效。下一篇讲怎么用 EXPLAIN 看查询实际走了什么索引。

一、没有索引时发生了什么

一张几百万行的 access_logs 表，查某个用户的记录：

SELECT * FROM access_logs WHERE user_id = 1001;

如果 user_id 上没索引，MySQL 怎么找到这些行？只能从头到尾扫整张表（full table scan），逐行判断 user_id 等不等于 1001。几百万行全扫一遍，几秒就过去了。

加了索引后，MySQL 不用扫全表，直接通过索引结构定位到 user_id=1001 的行。从"扫几百万行"变成"扫几十行"，毫秒级返回。索引的本质就是这个——提供一条少扫描数据的路径。

二、B+Tree：索引长什么样

InnoDB 的索引结构是 B+Tree（B 加树）。不用懂它的算法细节，理解一个关键点：它让数据按索引列的值排好序，查找时能像翻字典一样快速定位，而不是逐页翻。

字典按拼音排序，找一个字不用从头翻到尾，按拼音首字母直接跳到那一块。B+Tree 类似——索引列的值在树里排好序，查找时一层层缩小范围，几下就定位到。

聚簇索引和二级索引

InnoDB 的索引分两种：

类型	叶子节点存什么	怎么用
聚簇索引	主键 + 整行数据	按主键找，直接拿到整行
二级索引	索引列 + 主键值	按索引列找到主键，可能还要回聚簇索引取整行

每张表只有一个聚簇索引——就是主键索引，数据本身就按主键顺序存在这棵树里。其他索引都是二级索引，叶子节点存的是"索引列的值 + 对应的主键值"。

回表

用二级索引查数据时，分两步：

1. 在二级索引树里找到 user_id=1001 的记录，拿到它的主键 id
2. 拿着 id 回聚簇索引树找整行数据

第二步叫"回表"——回到聚簇索引取整行。回表有开销，多一次树查找。

回表不是必须的。如果查询要的列全在二级索引里，不用回聚簇索引，这叫覆盖索引。比如索引是 (user_id, created_at)，查询只要 user_id 和 created_at：

SELECT user_id, created_at FROM access_logs WHERE user_id = 1001;

这两个列都在索引里，不用回表，直接返回。EXPLAIN 里看到 Extra: Using index 通常就是覆盖索引。

主键长度影响所有索引

二级索引的叶子节点要存主键值。主键越长（比如用 UUID、长字符串），每个二级索引的叶子节点就越大，索引占空间多、扫描慢。生产表主键用自增整型（BIGINT UNSIGNED AUTO_INCREMENT），短、递增、紧凑。

三、联合索引和最左前缀

索引可以建在多个列上，叫联合索引：

KEY idx_user_time (user_id, created_at)

这个索引同时管 user_id 和 created_at 的组合查询。但它有个"最左前缀"规则——联合索引 (A, B) 能用于查 A、查 A+B，但不能用于只查 B。

WHERE user_id = 1001                              -- ✓ 用到索引（最左列）
WHERE user_id = 1001 AND created_at >= '...'     -- ✓ 用到索引
WHERE created_at >= '...'                         -- ✗ 用不上（跳过了 user_id）

为什么？B+Tree 按联合索引的列顺序排序——先按 user_id 排，user_id 相同再按 created_at 排。跳过 user_id 直接查 created_at，树里 created_at 是分散的，没法定位。

联合索引的列顺序

列顺序决定了哪些查询能用上索引。原则：

• 等值条件的列放前面（WHERE user_id = 1001）
• 范围条件的列放等值列后面（WHERE created_at >= '...'）
• 排序的列尽量和索引顺序一致

KEY idx_app_status_time (app_name, status_code, created_at)

适合查 WHERE app_name='api' AND status_code=500 AND created_at>='...'——app_name 和 status_code 是等值放前面，created_at 是范围放后面。

如果反过来，范围条件放前面：

KEY idx_time_app_status (created_at, app_name, status_code)

WHERE created_at >= '...' AND app_name='api'——created_at 是范围，匹配后 app_name 在树里是分散的，用不上索引。这是联合索引设计最容易踩的坑。

四、索引什么时候失效

建了索引不代表查询一定用它。几种常见情况索引会失效：

函数和运算

WHERE DATE(created_at) = '2026-06-23'     -- ✗ 对列用了函数，失效
WHERE created_at >= '2026-06-23'
  AND created_at < '2026-06-24'           -- ✓ 改成范围，能用索引

对索引列套函数、做运算，索引失效。把条件改成等价的不带函数的形式。

隐式类型转换

WHERE user_id = '1001'     -- user_id 是 BIGINT，传字符串

MySQL 会把 '1001' 转成数字比较，但这个转换可能让索引失效。传值时类型要对上——数字列传数字、字符串列传字符串。

LIKE 前通配

WHERE hostname LIKE 'web-%'     -- ✓ 前缀固定，走索引
WHERE hostname LIKE '%web'      -- ✗ 前面通配，失效

B+Tree 按前缀排序定位，前面是通配符没法定位，只能全扫。

OR 的坑

WHERE user_id = 1001 OR app_name = 'api'

OR 两边都要有索引才走索引，一边没有就退化成全表扫。这种情况考虑用 UNION ALL 拆成两个走索引的查询。

不等于和 NOT IN

WHERE status != 1
WHERE user_id NOT IN (1, 2, 3)

不等于、NOT IN 这类条件，索引通常帮不上——B+Tree 适合找"等于某值"或"在某范围"，找"不等于"得扫遍所有行。

五、索引不是越多越好

每个索引都是一棵 B+Tree，占存储空间。写数据时（INSERT/UPDATE/DELETE）所有相关索引都要更新，索引多了写入变慢。

原则：查得多建索引，写得多慎建。高频查询的 WHERE、JOIN、ORDER BY 条件建索引，很少查的列别建。建了不用的索引是负担。

索引原理之后

知道了索引怎么工作、最左前缀、为什么会失效。但实际某条查询到底走了没走索引、扫了多少行，光靠想不准。下一篇讲 EXPLAIN——让 MySQL 自己说出这条查询的实际执行计划。