MySQL索引详解

发表于 Waline： 阅读次数： 本文字数： 19k 阅读时长 ≈ 17 分钟

MySQL索引是一种用于加速数据检索的数据结构，类似于书籍目录。通过索引可以快速定位到目标数据行，避免全表扫描。索引的本质是以空间换时间，在写入性能和数据检索速度之间做权衡。

为什么需要索引

在没有索引的情况下，MySQL 必须从第一行开始扫描整张表，直到找到目标数据行。随着数据量的增长，全表扫描的代价呈线性增长。

以 1000 万行数据的用户表为例：

1
2
3
4
5
6
7
8
-- 无索引：全表扫描，扫描 1000 万行
SELECT * FROM users WHERE email = 'user@example.com';
-- 执行时间：数秒甚至更久

-- 有索引：B+Tree 查找，仅需 3-4 次磁盘 I/O
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- 执行时间：毫秒级

索引的核心代价：

  • 空间代价：每个索引都占用额外的磁盘空间，联合索引和覆盖索引会增加较大的存储开销
  • 写入代价：INSERT / UPDATE / DELETE 时，除了修改数据行外，还需要维护所有相关索引
  • 锁竞争：索引维护过程中可能产生额外的锁竞争，影响并发写入性能

一张表的索引不是越多越好。每增加一个索引，写入性能就会相应下降。需要在查询性能和写入性能之间找到平衡点。

索引分类

B+Tree 索引（最常用）

InnoDB 和 MyISAM 的默认索引结构，也是 MySQL 中最核心的索引类型。数据按顺序存储在 B+Tree 的叶子节点中，支持等值查询、范围查询和排序。

B+Tree 之所以成为数据库索引的首选数据结构，原因在于：

  • 高度平衡：无论查找哪个值，I/O 次数都稳定在 O(log n)，不存在退化情况
  • 叶子节点有序且链表连接：天然支持范围查询和 ORDER BY
  • 扇出率高：每个节点可以存储几百到上千个键值，树的高度通常为 2-4 层
  • 磁盘友好：节点大小通常设计为与磁盘页大小匹配（InnoDB 默认 16KB），一次 I/O 读取一个完整节点
1
2
3
4
5
6
7
8
9
10
11
-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 唯一索引：索引列的值必须唯一，允许 NULL（多个 NULL 视为不相等）
CREATE UNIQUE INDEX idx_email ON users(email);

-- 联合索引：多个列组成一个索引，遵循最左前缀原则
CREATE INDEX idx_name_age ON users(name, age);

-- 前缀索引：仅索引列值的前 N 个字符，节省空间，但无法用于 ORDER BY
CREATE INDEX idx_title_prefix ON articles(title(20));

各索引类型的适用场景：

索引类型 适用场景 注意事项
普通索引 高频查询列 无特殊限制，最常用
唯一索引 业务唯一标识（邮箱、手机号） 同时保证了数据完整性
联合索引 多条件查询 + 排序 注意列的顺序设计
前缀索引 长字符串列（TEXT / 长 VARCHAR） 需权衡区分度和空间
主键索引 每表必有的行标识 InnoDB 中即聚簇索引

哈希索引

Memory 引擎默认支持显式哈希索引，InnoDB 通过自适应哈希索引（AHI）自动在热点数据上构建哈希索引。

1
2
3
4
5
6
-- Memory 表显式创建哈希索引
CREATE TABLE memory_test (
    id INT,
    name VARCHAR(50),
    INDEX idx_name USING HASH (name)
) ENGINE=MEMORY;

哈希索引的特点：

  • 仅支持等值查询：不支持范围查询、排序、部分匹配
  • 查询效率极高：O(1) 时间复杂度，适合热点数据的等值查找
  • 冲突处理：哈希冲突严重时性能下降

InnoDB 自适应哈希索引（Adaptive Hash Index, AHI）：
InnoDB 会监控 B+Tree 索引的查询模式，如果发现某些数据页被频繁访问，会自动在内存中为这些热点页建立哈希索引，加速后续等值查询。整个过程对用户透明，无需手动干预。

1
2
3
4
5
6
7
8
9
10
-- 查看 AHI 状态
SHOW ENGINE INNODB STATUS\G
-- 关注 "INSERT BUFFER AND ADAPTIVE HASH INDEX" 部分

-- 查看 AHI 使用情况
SELECT
    name,
    count
FROM information_schema.innodb_metrics
WHERE name LIKE '%adaptive_hash%';

全文索引

用于大文本字段的模糊搜索，InnoDB 从 5.6 版本开始支持。相比 LIKE ‘%keyword%’ 的全表扫描，全文索引通过倒排索引实现高效的文本搜索。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- 自然语言模式：按相关性排序
SELECT *, MATCH(title, content) AGAINST('MySQL索引优化') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL索引优化' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- 布尔模式：支持布尔操作符
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 查询扩展模式：自动扩展相关词
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);

全文索引的限制：

  • 最小搜索词长度由 innodb_ft_min_token_size（InnoDB，默认 3）或 ft_min_word_len（MyISAM，默认 4）控制
  • 存在停用词表（stopword list），常见词不会被索引
  • 中文分词需要借助 ngram parser（MySQL 5.7.6+）或第三方分词插件
1
2
3
4
5
6
7
8
9
-- 中文全文索引：使用 ngram 分词器
CREATE FULLTEXT INDEX idx_content_cn ON articles(title, content) WITH PARSER ngram;

-- 配置分词大小
SET GLOBAL ngram_token_size = 2;  -- 默认 2，每个汉字为一个字符

-- 中文搜索示例
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库索引' IN NATURAL LANGUAGE MODE);

空间索引（R-Tree）

MySQL 5.7 开始 InnoDB 也支持空间索引，用于地理位置数据的查询。

1
2
3
4
5
6
7
8
9
10
11
-- 创建空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    geo POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_geo (geo)
);

-- 空间范围查询
SELECT name FROM locations
WHERE ST_Within(geo, ST_GeomFromText('POLYGON((...))', 4326));

索引数据结构深入

B+Tree 结构详解

1
2
3
4
5
6
7
           [50 | 100]              ← 非叶子节点（只存索引键 + 子节点指针）
          /    |     \
 [10|20|30] [60|80] [120|150]      ← 非叶子节点
  /  |  |  \   /  \    /  |   \
[1] [15] [25] [35] ...           ← 叶子节点（存完整数据或主键，双向链表连接）
↑________________________↑
       双向链表连接

B+Tree 核心特点：

  1. 所有数据存储在叶子节点：非叶子节点不存储数据，只存储索引键和子节点的指针
  2. 非叶子节点的扇出率极高：由于不存储数据，一个 16KB 的页可以存储大量索引键
  3. 叶子节点之间通过双向链表连接：这使 B+Tree 天然支持范围查询和正序/逆序遍历
  4. 树的高度通常为 2-4 层：对于千万级别的数据，树高通常为 3 层，一次查询需要 2-3 次 I/O
  5. 叶子节点存储的数据取决于存储引擎：
    • InnoDB：叶子节点存储完整行数据（聚簇索引）或主键值（二级索引）
    • MyISAM：叶子节点存储数据行的物理地址指针

为什么 B+Tree 而不是 B-Tree？

特性 B-Tree B+Tree
数据存储位置 所有节点都存储数据 仅叶子节点存储数据
非叶子节点扇出率 低（需要存储数据） 高（只存键值和指针）
树的高度 相对较高 相对较低，I/O 次数更少
范围查询 需要中序遍历，跨层较多 叶子节点链表直接扫描
排序查询 效率较低 天然支持

总结：B+Tree 将数据集中在叶子节点，非叶子节点不存数据，使得非叶子节点可以容纳更多索引键，树更矮，I/O 更少。加之叶子节点的链表结构，范围查询和排序操作也更高效。

为什么不是二叉搜索树 / 红黑树？

二叉搜索树在极端情况下会退化为链表（O(n) 查找），红黑树虽然保持平衡，但每个节点只有两个子节点，树高过大。对于 1000 万行数据，红黑树的高度约为 24 层，意味着一次查询最多需要 24 次磁盘 I/O，这在数据库中是不可接受的。

B+Tree 的核心优势：大幅降低树的高度，一次查询仅需 2-4 次 I/O。

聚簇索引与非聚簇索引

这是理解 InnoDB 索引体系最核心的概念之一。

特性 聚簇索引 (Clustered Index) 二级索引 (Secondary Index)
数据存储 叶子节点存储完整行数据 叶子节点存储主键值
数量限制 一张表只能有一个 一张表可以有多个
默认创建 InnoDB 主键自动创建 手动创建或唯一约束自动创建
查询路径 索引即数据，直接返回 需要拿到主键后回表查询
物理顺序 行数据按主键顺序物理存储 B+Tree 按索引列顺序组织
存储引擎 InnoDB InnoDB / MyISAM

聚簇索引的选择规则

InnoDB 按以下优先级选择聚簇索引：

  1. 显式定义的主键（PRIMARY KEY） → 作为聚簇索引
  2. 第一个非空的唯一索引（UNIQUE NOT NULL） → 作为聚簇索引
  3. 自动生成的隐藏列 row_id（6 字节） → 作为聚簇索引

强烈建议：始终为 InnoDB 表定义显式主键。避免使用过长的主键，因为二级索引的叶子节点存储的就是主键值，主键过长会导致所有二级索引都膨胀。

1
2
3
4
5
6
7
8
9
10
-- InnoDB 表，主键即聚簇索引
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,       -- 聚簇索引（自增主键是最佳实践）
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    created_at DATETIME,
    UNIQUE INDEX idx_order_no (order_no),       -- 二级索引，叶子节点存 id + order_no
    INDEX idx_user_id (user_id)                 -- 二级索引，叶子节点存 id + user_id
);

回表查询过程详解

1
2
-- 通过二级索引 idx_user_id 查询
SELECT * FROM orders WHERE user_id = 100;

执行过程：

1
2
3
4
5
6
7
8
9
步骤 1：在 idx_user_id (user_id → id) 的 B+Tree 中搜索
        定位到 user_id = 100 对应的叶子节点
        拿到该行对应的主键值，例如 id = 520

步骤 2：回表（Bookmark Lookup）
        拿着主键值 id = 520，到聚簇索引的 B+Tree 中搜索
        在叶子节点找到完整行数据

步骤 3：返回结果

回表是一个随机 I/O 操作，当查询涉及大量行时，回表会成为性能瓶颈。覆盖索引正是为了解决这个问题而生。

为什么建议使用自增主键？

1
2
3
4
5
-- 自增主键：新数据追加到 B+Tree 末尾，页分裂概率低
id BIGINT PRIMARY KEY AUTO_INCREMENT

-- 随机主键（如 UUID）：插入位置随机，频繁页分裂，写入性能差
id VARCHAR(36) PRIMARY KEY  -- 不推荐

自增主键的优势：

  • 减少页分裂（page split），提高写入性能
  • 叶子节点按主键顺序排列，范围查询效率高
  • 主键值占用空间小（BIGINT = 8 字节），二级索引开销低

联合索引与最左前缀原则

联合索引（复合索引）是日常 SQL 优化中最常用的工具之一。理解最左前缀原则是正确使用联合索引的前提。

最左前缀原则

联合索引 (a, b, c) 的 B+Tree 结构：先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。

1
CREATE INDEX idx_abc ON test(a, b, c);
1
2
3
4
索引 (a, b, c) 叶子节点排列示意：
(1, 1, 1) → (1, 1, 2) → (1, 2, 1) → (1, 2, 3) → (2, 1, 1) → (2, 2, 1) → (3, 1, 1) ...
          ↑                                                    ↑
     a=1 范围内 b 有序                                     a=2 范围内重新按 b 排序

命中规则：

1
2
3
4
5
6
7
8
9
10
11
-- ✓ 命中索引的查询
SELECT * FROM test WHERE a = 1;                    -- 命中 a
SELECT * FROM test WHERE a = 1 AND b = 2;           -- 命中 a, b
SELECT * FROM test WHERE a = 1 AND b = 2 AND c = 3; -- 命中 a, b, c
SELECT * FROM test WHERE a = 1 AND c = 3;           -- 命中 a（b 跳过了，c 失效）
SELECT * FROM test WHERE a = 1 AND b > 2 AND c = 3; -- 命中 a, b（范围查询后 c 失效）

-- ✗ 无法命中索引的查询
SELECT * FROM test WHERE b = 2;                    -- 缺少最左列 a，不走索引
SELECT * FROM test WHERE b = 2 AND c = 3;           -- 缺少最左列 a，不走索引
SELECT * FROM test WHERE a LIKE '%abc';             -- 模糊查询以 % 开头，索引失效

核心记忆：MySQL 可以跳过中间的列（如跳过 b 直接用 c），但不能跳过最左边的列。范围查询（>、<、>=、<=、BETWEEN、LIKE ‘prefix%’）本身能命中，但其后的列会失效。

联合索引的列顺序设计

联合索引列的顺序应该基于业务查询模式来设计，核心原则：

1
2
3
1. 等值查询条件列 → 放前面
2. 范围查询条件列   → 放中间
3. 排序 / 分组列    → 放后面
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 典型查询：WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- 索引设计思路：
--   user_id   → 等值条件，放最前
--   status    → 等值条件，放中间
--   created_at → 排序条件，放最后
-- 这样查询可以同时完成过滤和排序，避免 filesort

CREATE INDEX idx_u_s_t ON orders(user_id, status, created_at);

-- EXPLAIN 验证：Extra 不会出现 Using filesort
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC;

常见的列顺序设计模式：

查询模式 索引设计 说明
WHERE a=? AND b=? ORDER BY c (a, b, c) 等值查询 + 排序，完美匹配
WHERE a=? AND b>? ORDER BY c (a, b) c 放索引中也会因范围查询失效
WHERE a=? ORDER BY b (a, b) 避免 filesort
WHERE a=? GROUP BY b (a, b) 避免临时表
WHERE a=? AND c=? (a, c) 或 (a) b 不参与查询，不需要放入

覆盖索引

覆盖索引（Covering Index）是日常 SQL 优化中最常用的手段。查询所需的所有列都包含在索引中时，MySQL 可以直接从索引返回结果，无需回表。

为什么覆盖索引高效？

1
2
3
4
5
普通查询（需要回表）：
  二级索引查找 → 获取主键 → 聚簇索引查找 → 返回数据  （2-3 次 I/O）

覆盖索引查询（不回表）：
  二级索引查找 → 直接返回数据  （1-2 次 I/O）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建覆盖索引：包含查询所需的所有列
CREATE INDEX idx_user_cover ON orders(user_id, amount, created_at, status);

-- 覆盖索引查询：SELECT 的列全在索引中，无需回表
SELECT user_id, amount, created_at, status
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC;

-- EXPLAIN 查看，Extra 列显示 "Using index" 表示使用了覆盖索引
EXPLAIN SELECT user_id, amount, created_at, status
FROM orders WHERE user_id = 100;
-- +------+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
-- | id   | select_type | table  | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
-- +------+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
-- |    1 | SIMPLE      | orders | ref  | idx_user_cover | idx_user_cover | 8       | const |    5 | Using index |
-- +------+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+

覆盖索引的使用限制

1
2
3
4
5
6
7
8
9
10
11
-- ✗ 不能覆盖：SELECT * 包含索引之外的列
SELECT * FROM orders WHERE user_id = 100;

-- ✗ 不能覆盖：索引中不包含 phone 列
SELECT user_id, phone FROM orders WHERE user_id = 100;

-- ✓ 可以覆盖：查询列全在索引中
SELECT user_id, amount, created_at FROM orders WHERE user_id = 100;

-- ✓ 可以覆盖：COUNT(*) 在覆盖索引上直接计算
SELECT COUNT(*) FROM orders WHERE user_id = 100;

实践技巧：在对性能敏感的查询中，可以考虑将 SELECT * 改为具体的列，并为这些列创建覆盖索引。这通常是最低成本的 SQL 优化手段之一。

索引下推 (ICP, Index Condition Pushdown)

MySQL 5.6 引入的重要优化。将 WHERE 条件中与索引相关的过滤条件下推到存储引擎层处理，减少回表次数。

工作原理

1
2
3
4
-- 联合索引 (name, age)
CREATE INDEX idx_name_age ON users(name, age);

SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL 5.5（无 ICP）：
  1. 存储引擎：通过 name LIKE '张%' 找到所有匹配的索引记录
  2. 存储引擎：逐条回表，取出完整行数据
  3. 存储引擎：将完整行数据返回给 Server 层
  4. Server 层：在完整行数据上过滤 age = 25
  → 问题：name 匹配但 age 不匹配的行也回表了

MySQL 5.6+（有 ICP）：
  1. 存储引擎：通过 name LIKE '张%' 找到匹配的索引记录
  2. 存储引擎：在索引层直接检查 age = 25 → 过滤掉不匹配的记录
  3. 存储引擎：仅对满足两个条件的记录回表
  4. 返回结果
  → 优势：减少了不必要的回表操作

EXPLAIN 中的 ICP

1
2
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- Extra 列显示 "Using index condition" 表示使用了 ICP

ICP 生效的条件：

  • 查询使用二级索引（聚簇索引不需要 ICP）
  • WHERE 条件中包含索引列的过滤条件
  • 查询需要回表（覆盖索引不需要 ICP）

MySQL 8.0 索引新特性

倒序索引（Descending Index）

MySQL 8.0 开始真正支持 DESC 索引，解决了降序排序时的 filesort 问题。

1
2
3
4
5
6
7
8
9
-- MySQL 8.0：支持混合排序的联合索引
CREATE INDEX idx_mixed ON orders(user_id ASC, created_at DESC);

-- 此索引可以高效支持以下查询
SELECT * FROM orders
WHERE user_id = 100
ORDER BY user_id ASC, created_at DESC;

-- MySQL 5.7：索引中的 DESC 会被忽略，实际仍为 ASC

不可见索引（Invisible Index）

MySQL 8.0 可以将索引设为不可见，优化器不会使用它。这在删除索引前做安全验证非常有用。

1
2
3
4
5
6
7
8
9
10
-- 将索引设为不可见（不会立即删除，先观察影响）
ALTER TABLE orders ALTER INDEX idx_user_id INVISIBLE;

-- 恢复可见
ALTER TABLE orders ALTER INDEX idx_user_id VISIBLE;

-- 查看索引可见性
SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_name = 'orders';

最佳实践：在删除一个”疑似无用”的索引前，先将其设为不可见，观察一段时间确认没有性能下降后，再正式删除。

函数索引（Functional Index）

MySQL 8.0.13 支持在表达式上创建索引，解决了”索引列上使用函数导致索引失效”的经典问题。

1
2
3
4
5
6
7
8
-- MySQL 8.0.13+：直接在 LOWER() 表达式上建索引
CREATE INDEX idx_email_lower ON users((LOWER(email)));

-- 查询时只要表达式一致即可命中索引
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 也支持 JSON 字段的表达式索引
CREATE INDEX idx_city ON users((CAST(attrs->>'$.city' AS CHAR(30))));

索引优化策略

索引设计原则

  1. 高频查询条件列建索引：分析慢查询日志，找出 WHERE / JOIN / ORDER BY / GROUP BY 中出现频率最高的列
  2. JOIN 关联字段建索引：JOIN 列上没有索引时，被驱动表需要全表扫描，性能极差
  3. ORDER BY / GROUP BY 列考虑建索引：注意与 WHERE 条件的复合，避免 filesort 和临时表
  4. 高区分度的列优先：区分度 = SELECT COUNT(DISTINCT col) / COUNT(*) FROM table，越接近 1 越好
  5. 避免在索引列上使用函数或表达式：这会导致索引失效（MySQL 8.0 的函数索引可以解决此问题）
  6. 联合索引代替多个单列索引：减少索引维护开销，同时利用覆盖索引减少回表
  7. 前缀索引减少索引体积：长字符串列只索引前缀，但需要确认区分度足够

索引区分度分析

1
2
3
4
5
6
-- 计算列的区分度（越接近 1 说明区分度越高）
SELECT
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,      -- 通常接近 1，区分度高
    COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,    -- 通常接近 0，区分度低
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity     -- 取决于取值数
FROM users;

区分度低于 0.1 的列一般不建议单独建索引，因为 MySQL 优化器可能认为全表扫描更高效。但如果该列频繁出现在联合查询条件中，可以考虑作为联合索引的一部分。

前缀索引的合理长度

1
2
3
4
5
6
7
8
9
10
-- 测试不同前缀长度的区分度
SELECT
    COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS len_10,
    COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS len_20,
    COUNT(DISTINCT LEFT(title, 30)) / COUNT(*) AS len_30,
    COUNT(DISTINCT title) / COUNT(*)            AS full_len
FROM articles;

-- 选择区分度接近完整列的最短前缀长度
CREATE INDEX idx_title_pref ON articles(title(20));

避免索引失效的场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 1. 列上使用函数
-- ✗ 索引失效：DATE() 函数导致全表扫描
SELECT * FROM users WHERE DATE(created_at) = '2026-05-09';
-- ✓ 改为范围查询：可以使用索引
SELECT * FROM users WHERE created_at >= '2026-05-09' AND created_at < '2026-05-10';

-- 2. 隐式类型转换
-- ✗ 索引失效：phone 是 VARCHAR，传入整数导致隐式转换
SELECT * FROM users WHERE phone = 13800138000;
-- ✓ 保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

-- 3. OR 条件中部分列无索引
-- ✗ 全表扫描：id 有索引但 age 无索引
SELECT * FROM users WHERE id = 1 OR age = 25;
-- ✓ 改写为 UNION ALL
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE age = 25 AND id != 1;

-- 4. NOT IN / != / <> 大概率不走索引
-- 此类否定条件无法有效利用 B+Tree 的范围查找特性
SELECT * FROM users WHERE status != 0;

-- 5. LIKE 以 % 开头
-- ✗ 索引失效：% 开头无法进行前缀匹配
SELECT * FROM users WHERE name LIKE '%张';
-- ✓ 可以使用索引：前缀匹配有效
SELECT * FROM users WHERE name LIKE '张%';

-- 6. IS NULL / IS NOT NULL
-- NULL 值在 B+Tree 中的存储方式特殊，是否走索引取决于数据分布
-- 如果大部分数据为 NULL，IS NOT NULL 大概率全表扫描
SELECT * FROM users WHERE deleted_at IS NULL;

隐式类型转换的陷阱

MySQL 的类型转换规则是”字符串转数字”而不是”数字转字符串”：

1
2
3
4
-- phone 列是 VARCHAR(20)
SELECT * FROM users WHERE phone = 13800138000;
-- MySQL 实际执行：WHERE CAST(phone AS SIGNED) = 13800138000
-- 相当于在索引列上使用了函数 → 索引失效！

EXPLAIN 分析索引使用

EXPLAIN 是 MySQL 中最常用的 SQL 执行计划分析工具。掌握 EXPLAIN 的解读，是进行 SQL 优化的基础。

核心字段解读

1
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
字段 说明 关键值
id 查询的序列号 相同 id 从上往下执行；id 越大优先级越高
select_type 查询类型 SIMPLE / PRIMARY / SUBQUERY / DERIVED / UNION
table 访问的表名 -
type 访问类型（最关键的字段） 性能从好到差排列
possible_keys 可能使用的索引 候选索引列表
key 实际使用的索引 NULL 表示未使用索引
key_len 使用的索引字节数 可推断联合索引命中了哪些列
ref 与索引比较的列或常量 const 表示常量
rows 预估扫描行数 越小越好
filtered 按条件过滤后的行数百分比 越大说明 WHERE 条件越有效
Extra 额外但关键的信息 Using index / Using filesort / Using temporary

type 访问类型详解（性能从好到差）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- const：主键或唯一索引等值查询，最多返回一行
EXPLAIN SELECT * FROM orders WHERE id = 100;
-- type = const

-- eq_ref：JOIN 时被驱动表使用主键或唯一索引关联
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- o 表 type = ref, u 表 type = eq_ref

-- ref：非唯一索引等值查询，可能返回多行
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type = ref

-- range：索引范围扫描（>, <, >=, <=, BETWEEN, IN, IS NULL）
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- type = range

-- index：全索引扫描（扫描整个索引，比全表扫描快但仍是全扫描）
EXPLAIN SELECT user_id FROM orders ORDER BY user_id;
-- type = index（覆盖索引 + 无 WHERE，只能扫全索引）

-- ALL：全表扫描，需要重点关注并优化
EXPLAIN SELECT * FROM orders WHERE remark LIKE '%关键词%';
-- type = ALL

优化目标：至少达到 range 级别，最好达到 ref 级别。ALL 是必须优化的信号。

Extra 字段关键信息

Extra 值 含义 是否需优化
Using index 覆盖索引，无需回表 ✓ 优秀
Using index condition 使用了 ICP ✓ 良好
Using where Server 层额外过滤 视情况而定
Using filesort 额外排序操作 ⚠ 需关注
Using temporary 使用了临时表 ⚠ 需关注
Using join buffer JOIN 使用了缓冲 ⚠ 需关注 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Using filesort 示例及优化
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY amount;
-- Extra: Using filesort  → 索引不支持按 amount 排序

-- 优化：创建支持排序的联合索引
CREATE INDEX idx_user_amount ON orders(user_id, amount);
-- Extra: 不再出现 Using filesort

-- Using temporary 示例及优化
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*);
-- Extra: Using temporary; Using filesort

-- 优化：为 GROUP BY 列建索引
CREATE INDEX idx_status ON orders(status);
-- 有了索引，GROUP BY 可以利用索引的有序性

使用 EXPLAIN ANALYZE（MySQL 8.0.18+）

传统 EXPLAIN 只能给出预估信息，EXPLAIN ANALYZE 会实际执行查询并给出精确的时间和行数。

1
2
3
4
5
6
7
-- 实际执行并测量时间
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 输出包含每个步骤的实际执行时间和行数
-- -> Filter: (orders.status = 'paid')
--     -> Index lookup on orders using idx_user_id (user_id=100)
--         (actual time=0.123..0.256 rows=50 loops=1)

优化器追踪（Optimizer Trace）

当需要理解 MySQL 为什么选择了某个索引而不是另一个时，可以开启优化器追踪：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 开启优化器追踪
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;

-- 执行查询
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 查看优化器的决策过程
SELECT * FROM information_schema.optimizer_trace\G
-- 关注 "considered_execution_plans" 和 "rows_estimation" 部分

-- 关闭追踪
SET optimizer_trace = 'enabled=off';

索引维护

查看索引信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查看表上的所有索引
SHOW INDEX FROM orders;

-- 查看索引的区分度（Cardinality）
-- Cardinality 是一个估算值，表示索引列中不同值的数量
-- Cardinality / 总行数 ≈ 区分度，越高越好
SHOW INDEX FROM orders WHERE key_name = 'idx_user_id';

-- 查看索引大小（InnoDB）
SELECT
    database_name,
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb'
  AND table_name = 'orders'
  AND stat_name = 'size'
ORDER BY size_mb DESC;

-- 查看冗余和未使用的索引（MySQL 8.0）
-- MySQL 8.0 通过 performance_schema 可以检查索引使用情况（需开启）
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

索引维护操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 删除不再使用的索引
DROP INDEX idx_unused ON orders;

-- 分析表，更新索引统计信息（Cardinality）
-- InnoDB 会自动更新统计信息，此命令主要用于手动触发
ANALYZE TABLE orders;

-- 碎片整理（会重建表，大表执行期间会锁表，需谨慎）
-- 仅当碎片率超过 30% 时考虑使用
OPTIMIZE TABLE orders;

-- 查看表的碎片率
SELECT
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_free / 1024 / 1024, 2) AS fragment_mb,
    ROUND(data_free / (data_length + index_length) * 100, 2) AS fragment_pct
FROM information_schema.tables
WHERE table_schema = 'mydb'
  AND table_name = 'orders';

统计信息管理

MySQL 优化器依赖统计信息来选择索引。了解统计信息的更新机制有助于排查”为什么 MySQL 不用我的索引”这类问题。

1
2
3
4
5
6
7
8
9
-- 查看统计信息持久化设置（InnoDB 默认持久化统计信息）
SHOW VARIABLES LIKE 'innodb_stats%';

-- innodb_stats_persistent = ON：统计信息持久化到磁盘，重启不丢失
-- innodb_stats_auto_recalc = ON：表数据变更超过 10% 时自动重新计算
-- innodb_stats_sample_pages = 20：统计信息采样的页数

-- 手动设置表的统计采样页数（提高统计精度）
ALTER TABLE orders STATS_SAMPLE_PAGES = 100;

索引设计实战

场景一：电商订单表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

分析典型查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Q1: 用户查自己订单（高频）→ 等值 user_id + 等值 status + 排序 created_at
SELECT * FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 10;

-- Q2: 按订单号查询（高频）→ 等值查询
SELECT * FROM orders WHERE order_no = ?;

-- Q3: 后台时间范围统计（中频）→ 范围 created_at + 等值 status
SELECT COUNT(*) FROM orders
WHERE created_at >= ? AND created_at < ? AND status = ?;

-- Q4: 用户分页列表（高频）→ 等值 user_id + 排序 created_at
SELECT * FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- Q5: 后台按状态查询（中频）→ 等值 status + 排序 created_at
SELECT * FROM orders
WHERE status = ?
ORDER BY created_at DESC
LIMIT 20;

索引设计：

1
2
3
4
5
6
7
8
9
10
11
12
-- Q2: order_no 唯一查询
ALTER TABLE orders ADD UNIQUE INDEX idx_order_no (order_no);

-- Q1 & Q4: user_id + status + created_at 联合索引
-- user_id 等值在前，status 等值在中，created_at 排序在后
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

-- Q3: created_at + status 联合索引
ALTER TABLE orders ADD INDEX idx_time_status (created_at, status);

-- Q5: status + created_at（与 Q3 不同，status 是等值在前）
ALTER TABLE orders ADD INDEX idx_status_time (status, created_at);

索引验证：

1
2
3
4
5
6
7
8
9
10
-- 验证 Q1：应该使用 idx_user_status_time
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC LIMIT 10;
-- type: ref, key: idx_user_status_time, Extra: 无 filesort ✓

-- 验证 Q3：应该使用 idx_time_status
EXPLAIN SELECT COUNT(*) FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' AND status = 1;
-- type: range, key: idx_time_status ✓

内存环境下的表设计：对于亿级数据量的订单表，还需要考虑分表/分区策略。索引设计和分库分表策略需要一起规划。

场景二：用户表的搜索优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    nickname VARCHAR(50),
    gender TINYINT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at DATETIME NOT NULL
);

-- 查询分析：
-- 高频：按 username 登录（等值）
-- 高频：按 email 登录（等值）
-- 高频：按 phone 查询（等值）
-- 中频：后台搜索 nickname（模糊）
-- 中频：按状态和创建时间筛选（范围 + 排序）

-- 索引设计
ALTER TABLE users
    ADD UNIQUE INDEX idx_username (username),
    ADD UNIQUE INDEX idx_email (email),
    ADD UNIQUE INDEX idx_phone (phone),
    ADD INDEX idx_status_created (status, created_at);

-- nickname 模糊搜索考虑使用全文索引（如需支持中文，加 WITH PARSER ngram）
ALTER TABLE users ADD FULLTEXT INDEX idx_nickname (nickname) WITH PARSER ngram;

索引设计的通用方法论

1. 基于查询模式设计（而非基于表结构）

不要因为”这个列很重要”就建索引。索引必须服务于实际的查询。

1
2
错误的思路：表中有哪些列 → 哪些列重要 → 建索引
正确的思路：有哪些查询 → 分析查询条件 → 设计对应的索引

2. 索引裁剪

定期检查并删除不再使用的索引：

1
2
3
4
5
6
7
8
-- MySQL 8.0：查看未使用的索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

-- 查看冗余索引（一个索引是另一个索引的前缀）
-- 例如：(a, b) 冗余于 (a, b, c)，因为 (a, b) 的查询 (a, b, c) 也能覆盖
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'mydb';

3. 慢查询驱动的持续优化

1
2
3
4
5
6
7
8
9
10
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;  -- 100ms 以上的查询被记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 使用 pt-query-digest 分析慢查询（Percona Toolkit）
-- pt-query-digest /path/to/slow.log

4. 索引设计的权衡清单

考量因素 说明
查询频率 高频查询才值得用索引代价去优化
写入频率 写入频繁的表，索引数量需要严格控制
数据量 小表（< 1 万行）全表扫描可能比索引更快
区分度 低区分度列单独建索引意义不大
查询覆盖度 能否设计成覆盖索引，避免回表
磁盘空间 索引占用的磁盘空间是否在可接受范围内

总结

  • B+Tree 是核心：理解 B+Tree 的结构和特性是掌握 MySQL 索引的基础
  • 聚簇索引是灵魂：InnoDB 中主键即数据，二级索引依赖主键回表
  • 最左前缀是规则：联合索引必须从最左列开始，中间跳过会导致后续列失效
  • 覆盖索引是技巧：避免回表是最低成本的 SQL 优化方式
  • EXPLAIN 是工具：每个查询都应在开发阶段通过 EXPLAIN 验证索引使用情况
  • 索引不是越多越好：每个索引都有写入和维护成本，需要在读写之间做权衡
  • 持续监控是习惯：定期检查慢查询日志、未使用索引和冗余索引

索引优化的最终目标：在有限的磁盘空间和写入开销下，让绝大多数查询都能命中合适的索引。