索引是什么

索引是帮助MySQL高效获取数据的数据结构。最通俗的理解：索引就像一本书的目录，你想找某个知识点，翻目录比一页页翻书快得多。

没有索引时，MySQL会全表扫描（逐行读取），时间复杂度O(n)。有了索引，可以通过特定的数据结构快速定位数据，时间复杂度能降低到O(log n)甚至O(1)。

索引的数据结构

MySQL默认存储引擎InnoDB使用B+树作为索引的数据结构。

B+ 树的特点

flowchart TD
    subgraph 根节点[根节点 - 只存键值]
        n1["50"]
    end
    subgraph 内部节点[内部节点 - 只存键值]
        n2["20"]
        n3["80"]
    end
    subgraph 叶子节点[叶子节点 - 存键值+数据指针/行数据]
        l1["10 → 行"] --- l2["20 → 行"]
        l3["30 → 行"] --- l4["50 → 行"]
        l5["60 → 行"] --- l6["80 → 行"]
        l7["90 → 行"] --- l8["100 → 行"]
        l1 -.-> l3
        l3 -.-> l5
        l5 -.-> l7
    end

    n1 --> n2
    n1 --> n3
    n2 --> l1
    n2 --> l2
    n3 --> l5
    n3 --> l7

    style l1 fill:#e6ffe6
    style l2 fill:#e6ffe6
    style l3 fill:#e6ffe6
    style l4 fill:#e6ffe6
    style l5 fill:#e6ffe6
    style l6 fill:#e6ffe6
    style l7 fill:#e6ffe6
    style l8 fill:#e6ffe6

B+树相比其他数据结构的优势：

  • vs Hash索引：Hash索引只支持等值查询（=、IN），不支持范围查询（>、<、BETWEEN），也不支持排序。
  • vs 红黑树：红黑树本质上是一棵二叉树，高度比B+树高很多（数据量大时），磁盘IO次数更多。B+树因为每个节点可以存储多个键值，树的高度通常只有3~4层，查询效率稳定。
  • vs B树：B+树的非叶子节点只存键值，单节点能存更多键值，树更矮。而且B+树的叶子节点之间用指针串联，范围查询时可以直接顺序读取。

为什么 B+ 树适合磁盘

磁盘IO是按页读取的（默认16KB/页），B+树的每个节点对应一个磁盘页。因为非叶子节点只存键值（不存数据），一页能存上千个键值，3层B+树就能存上千万条数据。也就是说，查询任意一条数据最多只需要3次磁盘IO。

索引类型

聚簇索引 vs 非聚簇索引

InnoDB的索引分为聚簇索引（Clustered Index）和二级索引（Secondary Index，也叫非聚簇索引）。

flowchart LR
    subgraph 聚簇索引[聚簇索引 - 主键索引]
        c1["叶子节点
存整行数据"]
    end
    subgraph 二级索引[二级索引 - 普通索引]
        s1["叶子节点
存主键值"]
    end

    s1 -->|"回表查询"| c1

聚簇索引：表的存储顺序和索引顺序一致。InnoDB中，表本身就是按聚簇索引组织的，所以每个表只能有一个聚簇索引。聚簇索引的叶子节点直接存储整行数据。

  • 如果表有主键，主键就是聚簇索引
  • 如果没有主键，选择第一个唯一索引
  • 都没有，InnoDB自动生成一个6字节的ROWID作为聚簇索引

二级索引：叶子节点存储的是主键值。通过二级索引查询时，需要先找到主键值，再回聚簇索引中查完整数据，这个过程叫回表。

覆盖索引

如果查询所需的所有字段都在二级索引上（不需要回表），就称之为覆盖索引。覆盖索引是性能优化的重要手段。

1
2
3
4
5
6
-- 假设有联合索引 (name, age)
-- 这个查询只需要 name 和 age，索引上已经有了 → 覆盖索引，无需回表
SELECT name, age FROM user WHERE name = '张三';

-- 这个查询还需要 address，索引上没有 → 需要回表
SELECT name, age, address FROM user WHERE name = '张三';

通过EXPLAIN的Extra列可以看到 Using index，说明使用了覆盖索引。

联合索引的最左前缀原则

联合索引 (a, b, c) 实际会建立三个索引：(a)、(a,b)、(a,b,c)。以下查询都能用上索引：

1
2
3
WHERE a = 1           -- 用上 a
WHERE a = 1 AND b = 2 -- 用上 a,b
WHERE a = 1 AND c = 3 -- 用上 a，c 用不上（b 被跳过，c 不能利用索引）

以下查询用不上索引（缺失最左列）：

1
2
WHERE b = 2           -- 用不上索引
WHERE c = 3           -- 用不上索引

EXPLAIN 解读

EXPLAIN是MySQL提供的查询分析工具，能够在前加上EXPLAIN，就能看到执行计划。

1
EXPLAIN SELECT * FROM user WHERE name = '张三'\G

重点关注以下几个字段：

字段 含义 优化目标
type 访问类型 从好到差：const > ref > range > index > ALL
key 实际使用的索引 尽量避免 NULL
rows 预估扫描的行数 越小越好
Extra 额外信息 出现 Using filesort、Using temporary 一般需要优化

type字段说明：

  • const：主键或唯一索引等值查询，最多返回一条，性能最好
  • ref：非唯一索引等值查询
  • range：范围查询（>, <, BETWEEN, IN）
  • index：遍历索引树（不如 ALL 那么差，但也需要优化）
  • ALL：全表扫描，最差

Extra中常见的两个需要警惕的值：

  • Using filesort：在内存或磁盘上排序，而非利用索引排序
  • Using temporary：使用了临时表保存中间结果，通常出现在GROUP BY、DISTINCT

索引失效的场景

遇到下面这些情况，即使有索引，MySQL也可能不走索引：

1. 对索引列做了函数操作

1
2
3
4
5
-- name 有索引，不走
SELECT * FROM user WHERE LEFT(name, 1) = '张';

-- 走索引
SELECT * FROM user WHERE name LIKE '张%';

函数操作改变了索引列的值，B+树无法直接定位。

2. 隐式类型转换

1
2
3
4
5
6
-- phone 是 varchar 类型，有索引
-- 传了数字，不走索引
SELECT * FROM user WHERE phone = 13800138000;

-- 传字符串，走索引
SELECT * FROM user WHERE phone = '13800138000';

MySQL会做类型转换，本质上还是对索引列使用了函数。

3. 不等于条件

1
2
3
4
-- 不走索引（!= 或 <> 通常不走索引）
SELECT * FROM user WHERE status != 1;
-- NOT IN 同理
SELECT * FROM user WHERE id NOT IN (1, 2, 3);

4. OR 条件中存在无索引列

1
2
-- name 有索引，age 无索引 → 不走索引
SELECT * FROM user WHERE name = '张三' OR age = 20;

OR两边必须都有索引才会走，否则MySQL认为全表扫描更优。

优化实践

借着上面学的知识，来看几个实际案例。

案例一：分页偏移量大

1
2
-- offset 很大时，需要扫描并丢弃前 100000 行
SELECT * FROM order WHERE status = 1 LIMIT 100000, 20;

优化方案：延迟关联或基于游标的分页

1
2
3
4
5
6
7
-- 延迟关联：先快速定位主键，再回表查完整数据
SELECT o.* FROM order o
INNER JOIN (SELECT id FROM order WHERE status = 1 LIMIT 100000, 20) tmp
ON o.id = tmp.id;

-- 或基于游标（记录上一页最后一条的 id）
SELECT * FROM order WHERE status = 1 AND id > 100860 LIMIT 20;

案例二：ORDER BY导致的文件排序

1
2
3
4
5
6
-- (status, create_time) 有联合索引
-- 这个查询会 Using filesort
SELECT * FROM order WHERE status = 1 ORDER BY create_time DESC;

-- 优化后：利用了索引排序
-- 但如果 WHERE 是范围查询，排序仍然可能用不上索引

联合索引的字段顺序通常遵循：等值条件放前面，排序字段放后面。

案例三：IN + ORDER BY

1
2
3
-- (type, create_time) 有联合索引
-- IN 条件会导致 Using filesort，因为多条数据的排序无法利用索引
SELECT * FROM order WHERE type IN (1, 2) ORDER BY create_time DESC;

优化方式：分多次查询再在内存中合并，或业务层面调整排序需求。

总结

flowchart TD
    A[遇到慢查询] --> B[EXPLAIN 分析]
    B --> C{type 是 ALL 或 index?}
    C -->|是| D[检查是否有合适的索引]
    D --> E{key 是 NULL?}
    E -->|是| F[考虑加索引]
    E -->|否| G[检查是否触发了索引失效]
    G --> H{函数操作/
类型转换/
不等于/
OR?}
    H -->|是| I[改写 SQL 避开]
    H -->|否| J[检查 rows 和 Extra]
    F --> K[验证效果]
    I --> K
    J --> K
    C -->|否| L{Extra 有 filesort
或 temporary?}
    L -->|是| M[优化 ORDER BY / GROUP BY
配合索引顺序]
    L -->|否| N[性能 OK 了]

索引优化的核心原则：

  1. 理解数据结构：B+树的特性决定了等值和范围查询的高效，也决定了函数操作会失效
  2. 关注最左前缀：联合索引的顺序设计要慎重
  3. 尽量减少回表：覆盖索引是最简单的加速手段
  4. 不迷信索引：索引有维护成本，写多读少的场景加太多索引反而降低性能