为什么索引能把查询从秒级降到毫秒级

一张没有索引的订单表，当数据量涨到几百万行时，一次按订单号查询就会变得让人难以忍受。MySQL 只能从第一行开始逐行比对，这种行为叫全表扫描。它的问题不仅是慢，更在于代价随数据量线性增长：今天扫十万行用 50 毫秒，明天扫一千万行就是 5 秒，业务方还会以为数据库“卡住了”。

索引解决的就是这个“查找”问题。它把无序的数据行按某个字段重新组织成一种有序结构，让 MySQL 能够像翻字典一样，用几次比较就定位到目标行，而不是从头翻到尾。查询从几次磁盘随机读变成两三次有序读，代价从 O(n) 变成 O(log n)，这就是几百万行表也能毫秒级返回的根本原因。

但索引不是白送的。每一个索引都是一份额外的数据结构，占用磁盘、消耗内存，并且会在写入时同步维护。因此索引优化的核心从来不是“加得越多越好”，而是让每一条慢查询都有合适的索引可走，同时不养无用的索引。

本文核心知识点
  • B+ 树矮胖的多路平衡树，非叶子节点只存键，叶子节点串成链表，兼顾点查与范围查。
  • 回表二级索引只存主键，命中后还要回主键索引再取一次完整行。
  • 最左前缀联合索引按定义顺序生效，跳过前导列就用不上索引。
  • EXPLAIN看 type、key、rows、Extra 四个字段，判断到底有没有走索引。
MySQL 索引示意：蓝色渐变背景上的发光数据库圆柱体，上方生长出层层分叉的索引树结构，一道查询光束自底部数据行向上汇聚被聚焦
索引是一棵长在数据之上的分叉树：查询光束不用扫过每一行，只需沿树向下几次比较就能收敛到目标数据。

B+ 树：MySQL 索引的底层结构

InnoDB 的索引默认使用 B+ 树。理解它只需要抓住三个特性。第一，它是多路的：一个节点不是存一两个键，而是存几百个键，因此树非常“矮”，一千万行数据通常也只需要三到四层，查一次最多三四次磁盘 IO。第二，非叶子节点只存键不存数据，这让每个磁盘页能装下更多键，进一步压低树高。第三，所有数据都在叶子节点，并且叶子节点之间用双向链表串起来，所以它既能做等值点查，也能高效地做范围扫描和排序。

对比一下其他结构就清楚了。哈希索引等值查询是 O(1)，但完全不支持范围和排序，也不支持最左前缀；二叉树在数据量大时树高太高，退化成链表更糟；B 树把数据放在所有节点上，范围查询要反复中序遍历，效率不如 B+ 树。这就是 MySQL 选择 B+ 树的理由。

下面这段 SQL 建一张订单表，并在高频查询字段上建索引，后续示例都基于它：

CREATE TABLE t_order (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no    VARCHAR(32)     NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  amount_cent INT             NOT NULL DEFAULT 0,
  created_at  DATETIME        NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status_time (user_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 一次走联合索引的等值 + 范围查询
EXPLAIN SELECT id, amount_cent
FROM t_order
WHERE user_id = 10086 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

聚簇索引与二级索引：回表是怎么回事

InnoDB 的表本身就是一棵以主键为键的 B+ 树，叶子节点直接存放完整的数据行，这棵树叫聚簇索引。也就是说，数据并不是单独存放的，而是“长”在主键索引上的。这也是为什么 InnoDB 强烈建议每张表都用自增主键：主键有序，插入都在最右侧追加，不会造成页分裂；如果主键是随机的 UUID，插入位置飘忽，页分裂与碎片会显著拖慢写入。

二级索引（也叫辅助索引）是另一棵独立的 B+ 树，它的叶子节点存放的不是完整数据行，而是索引列的值加主键值。所以当一条查询先用二级索引定位，再拿着主键回到聚簇索引取整行数据时，就发生了回表。单次回表不贵，但如果一个查询要匹配几万行，就要回表几万次，代价迅速放大。

理解回表，是后续所有优化的钥匙：能少回表就少回表（覆盖索引），能让范围条件落在索引上就不要落在回表上。凡是看到执行计划里 rows 很大而 Extra 里没有 Using index，就该怀疑这里在大量回表。

联合索引与最左前缀原则

联合索引把多个列按定义顺序拼在一起建树。idx_user_status_time (user_id, status, created_at) 的排序规则是：先按 user_id 排，user_id 相同的再按 status 排，再相同才按 created_at 排。由此得出最左前缀原则：查询条件必须从索引的最左列开始连续使用，才能利用索引的有序性。

查询条件能否用上索引说明
user_id = ?能匹配最左列
user_id = ? AND status = ?能连续两列
user_id = ? AND status = ? AND created_at > ?能三列都用上，范围列放最后
status = ? AND user_id = ?能WHERE 顺序无关，优化器会自动重排
status = ? AND created_at = ?不能跳过最左列 user_id
user_id = ? AND created_at > ?部分只用到 user_id，created_at 因跳列无法走索引排序

一张表里范围条件会“截断”后续列：一旦某一列用了范围（>、<、between、like 前缀），它右边的列就无法再用于索引定位，只能做过滤。所以联合索引的列顺序应遵循等值在前、范围在后、排序列紧跟等值列的经验：把频繁用于等值匹配的列放前面，范围列放最后，能顺带消除排序的排序列尽量紧邻前面的等值列。

覆盖索引与索引条件下推

如果一条查询需要的所有列都能从索引里拿到，就不用回表，这叫覆盖索引。执行计划的 Extra 会出现 Using index。把常见查询的字段补进联合索引尾部，往往能让一个重要接口直接快好几倍。

例如上面那句“按 user_id 与 status 查、按时间倒序取 20 条、只要 id 和金额”，如果只建 (user_id, status)，它需要回表 20 次拿 amount_cent；把索引扩成 (user_id, status, created_at, amount_cent)，虽然还是回表取金额，但排序已被索引免掉。若再让查询只取 id（id 本就在二级索引叶子里），就变成纯覆盖索引扫描，一次回表都不需要。

索引条件下推（Index Condition Pushdown，ICP）是另一个常被忽略的优化：当查询里存在只能靠回表才能判断的条件时，MySQL 会把过滤条件下推到存储引擎层，在遍历索引时先做一次过滤，只对通过的记录回表。这能显著减少回表次数。ICP 生效时 Extra 会出现 Using index condition。

用 EXPLAIN 读懂执行计划

优化慢查询的第一步不是改 SQL，而是看懂它在执行计划里到底走了什么路。EXPLAIN 输出十几列，真正要盯住的是这几列。

字段含义关注点
type访问类型从好到差：system > const > ref > range > index > ALL，出现 ALL 即全表扫描
key实际使用的索引为 NULL 说明没用索引，要结合 possible_keys 排查
rows预估扫描行数数值越大越危险，是优化的第一直觉指标
filtered过滤后剩余比例很低说明大量行被过滤掉，索引选择性差
Extra附加信息出现 Using index 是好事；Using filesort 与 Using temporary 要警惕

经验上，一条在线接口的单表查询，type 至少要到 range，rows 最好控制在几百以内，Extra 里不应同时出现 filesort 与 temporary。满足这几条，接口的响应时间通常就稳了。