MySQL 各类索引详解

MySQL 各类索引详解

本文系统讲解 MySQL 8.0（InnoDB）中 索引的分类、底层结构、适用场景与使用约束，涵盖聚簇/二级、唯一/联合、前缀/全文/空间、函数/降序/不可见等索引形态，以及覆盖索引、索引下推等优化概念。
默认存储引擎为 InnoDB（MySQL 8.0 默认且推荐）。
官方参考：MySQL 8.0 — Optimization and Indexes

建议阅读顺序：先看 一、总览 与 二、B+ 树与 InnoDB 索引结构，再按索引类型分章查阅；实践调优可结合 十二、EXPLAIN 与索引使用判断 与 十三、索引设计实践。

相关文档：02-MySQL增删改查执行过程（优化器如何选索引）、02-MySQL锁机制详解（行锁加在索引上）、01-MySQL-5.7到8.0差异详解（函数索引、降序索引、不可见索引等 8.0 新特性）。

一、总览

1.1 索引是什么

索引是帮助存储引擎 快速定位数据 的数据结构。InnoDB 以 B+ 树 为主（全文、空间索引除外），将 (索引键 → 行定位信息) 组织成有序结构，使 WHERE / JOIN / ORDER BY / GROUP BY 不必每次全表扫描。

无索引查询                         有索引查询
┌─────────────┐                   ┌─────────────┐
│  全表逐行扫描  │  O(n)            │  B+ 树定位   │  O(log n)
│  读取大量无关页 │                   │  只读相关页   │
└─────────────┘                   └─────────────┘
收益 代价
显著降低读延迟 占用额外磁盘与内存
加速排序、去重、唯一约束检查 INSERT / UPDATE / DELETE 需维护索引
支撑外键、锁粒度（行锁依赖索引） 错误索引导致优化器选错计划

1.2 索引分类全景

MySQL 中「索引类型」可从多个维度理解，下表按 最常用分类 组织：

┌──────────────────────────────────────────────────────────────────────┐
│                        MySQL 索引分类全景                              │
├──────────────┬──────────────┬──────────────┬─────────────────────────┤
│  按逻辑角色   │  按数据结构   │  按列数量     │  8.0 增强特性            │
├──────────────┼──────────────┼──────────────┼─────────────────────────┤
│ 聚簇索引(PK)  │ B+ Tree      │ 单列索引      │ 函数索引（表达式）         │
│ 二级索引      │ R-Tree(空间)  │ 联合索引      │ 降序索引                  │
│ 唯一索引      │ 倒排(全文)    │ 前缀索引      │ 不可见索引                │
│ 普通索引      │ Hash(MEMORY) │ 覆盖索引(概念)│ Index Skip Scan         │
└──────────────┴──────────────┴──────────────┴─────────────────────────┘
维度 常见类型 DDL 关键字 / 说明
逻辑角色 聚簇索引、二级索引 InnoDB 表 必有且仅有一个 聚簇索引
唯一性 唯一索引、普通索引 UNIQUE vs 默认非唯一
列数 单列、联合（复合） (col1, col2, ...)
键长度 完整列、前缀 INDEX idx(name(10))
算法 BTREE、HASH、FULLTEXT、SPATIAL USING BTREE 等
可见性 可见、不可见（8.0+） INVISIBLE

1.3 DDL 语法速查

-- 建表时定义
CREATE TABLE users (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,          -- 聚簇索引
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(64),
  bio         TEXT,
  location    POINT NOT NULL SRID 4326,
  UNIQUE KEY uk_email (email),                            -- 唯一二级索引
  KEY idx_name (name),                                    -- 普通二级索引
  KEY idx_email_name (email, name),                       -- 联合索引
  KEY idx_bio (bio(100)),                                 -- 前缀索引（TEXT 常用）
  FULLTEXT KEY ft_bio (bio),                              -- 全文索引
  SPATIAL KEY sp_location (location)                      -- 空间索引
) ENGINE=InnoDB;

-- 建表后追加 / 修改
CREATE INDEX idx_created ON orders (created_at DESC);     -- 8.0 降序索引
CREATE INDEX idx_lower_email ON users ((LOWER(email)));    -- 8.0 函数索引
ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;    -- 8.0 不可见索引
DROP INDEX idx_name ON users;

二、B+ 树与 InnoDB 索引结构

2.1 为什么 InnoDB 选 B+ 树

特性 B+ 树 普通 B 树 Hash
范围查询 叶子链表，高效 数据在各层，范围扫描差 不支持
磁盘 I/O 非叶子只存键，扇出大 非叶子也存数据，扇出小 —
顺序扫描 叶子有序 需中序遍历 —
等值查询 O(log n) O(log n) O(1) 理想情况

InnoDB 页大小默认 16KB。B+ 树 非叶子节点 只存索引键与指针；叶子节点 存完整索引项，并通过双向链表连接，便于范围扫描与排序。

                    ┌─────────────────┐
                    │   根节点 (非叶子)  │
                    │  [10] [30] [50]  │
                    └────────┬────────┘
              ┌──────────────┼──────────────┐
              ▼              ▼              ▼
        ┌──────────┐   ┌──────────┐   ┌──────────┐
        │ 非叶子    │   │ 非叶子    │   │ 非叶子    │
        └────┬─────┘   └────┬─────┘   └────┬─────┘
             ▼              ▼              ▼
   [1,row]↔[5,row]↔[10,row] ... [30,row]↔[35,row] ...   ← 叶子层（有序链表）

2.2 聚簇索引 vs 二级索引

对比项 聚簇索引（Clustered Index） 二级索引（Secondary Index）
数量 每表 1 个 0 ~ 64 个（InnoDB 上限）
叶子存什么 完整行数据 索引列 + 主键值
默认选择 显式 PRIMARY KEY；否则第一个 UNIQUE NOT NULL；再否则隐式 row_id 用户定义的 KEY / INDEX
查询路径 一次 B+ 树到达数据 两次：二级索引找 PK → 回表读聚簇索引 
聚簇索引 (PK = id)                    二级索引 (idx_email)
┌─────────────────────┐              ┌──────────────────┐
│ 叶子: id=1 → 整行数据  │              │ 叶子: email → id  │
│       id=2 → 整行数据  │              │  'a@x.com' → 1    │
└─────────────────────┘              │  'b@y.com' → 2    │
                                     └────────┬─────────┘
                                              │ 回表 (lookup PK)
                                              ▼
                                     聚簇索引按 id 取整行

回表：通过二级索引找到主键后，再到聚簇索引读取完整行。若所需列全在二级索引中，则 无需回表（见 7.1 覆盖索引）。

三、聚簇索引（主键索引）

3.1 定义与作用

聚簇索引 决定 InnoDB 表中 数据的物理存储顺序（逻辑上按主键排序组织页）。它不是单独的「索引文件」，而是表本身的主组织方式。

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,       -- 聚簇索引
  customer_id BIGINT NOT NULL,
  amount      DECIMAL(12,2),
  created_at  DATETIME
);

3.2 主键选择原则

原则 原因
尽量 短、有序、不变 二级索引叶子存主键；主键越长，二级索引越大
优先 自增整型 顺序插入，减少 B+ 树页分裂与碎片
避免用 UUID / 随机字符串 作主键 随机插入导致频繁分裂，写放大
避免频繁 UPDATE 主键 相当于移动整行 + 更新所有二级索引

3.3 无显式主键时

InnoDB 按以下顺序自动选择聚簇索引：

  1. 第一个 NOT NULL 的 UNIQUE 索引；
  2. 若都没有，生成隐藏的 DB_ROW_ID（6 字节，表内递增）。

建议：始终显式定义主键，避免依赖隐式 row_id（复制、某些工具行为不一致）。

四、二级索引（辅助索引）

4.1 普通索引

允许重复值，加速等值与范围查询，不强制唯一性。

CREATE INDEX idx_status ON orders (status);

SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01';
-- 优化器可能用 idx_status 或 idx_created，取决于选择性、统计信息

4.2 唯一索引

保证索引列（或列组合）值唯一；NULL 在 MySQL 中允许多个（NULL != NULL）。

CREATE UNIQUE INDEX uk_email ON users (email);

-- 插入冲突
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice');
-- ERROR 1062: Duplicate entry 'a@b.com' for key 'uk_email'
对比 PRIMARY KEY UNIQUE KEY
数量 1 多个
NULL 不允许 允许多个 NULL
聚簇 是 否（仍是二级索引，除非被选中为聚簇）

4.3 外键与索引

InnoDB 外键 要求引用列与被引用列上有索引（通常在被引用表的 PK 上，引用表侧需对应索引）。

CREATE TABLE order_items (
  id        BIGINT PRIMARY KEY,
  order_id  BIGINT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders (id)
);
-- order_id 上会自动创建索引（若未手动建）

五、联合索引（复合索引）

5.1 结构与最左前缀

联合索引将多列组合成一个 有序键序列，按 从左到右 的字典序排列。

CREATE INDEX idx_abc ON t (a, b, c);
-- 逻辑键顺序: (a1,b1,c1) < (a1,b1,c2) < (a1,b2,c1) < (a2,...)

最左前缀原则：查询条件必须从索引 最左列 开始连续匹配，才能充分利用该索引。

查询条件 能否用 idx_abc
WHERE a = 1 能
WHERE a = 1 AND b = 2 能
WHERE a = 1 AND b = 2 AND c = 3 能（完整匹配）
WHERE b = 2 不能（跳过 a）
WHERE a = 1 AND c = 3 部分：只用 a；c 通常需回表后过滤或 ICP
WHERE a > 1 AND b = 2 部分：a 做 range 后 b 往往无法继续用索引排序

5.2 列顺序设计

建议 说明
等值列放前，范围列放后 WHERE status = ? AND created_at > ? → (status, created_at)
高选择性列优先（在等值场景） 区分度高的列放前，缩小扫描范围
考虑 ORDER BY 若 ORDER BY b, c 且 WHERE a = ?，索引 (a, b, c) 可避免 filesort
避免冗余索引 (a,b) 已存在时，单独 (a) 通常冗余（最左前缀已覆盖 a） 
-- 典型电商订单查询
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);

SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- 理想情况：索引范围扫描 + 无需额外排序

5.3 索引合并（Index Merge）

当单索引无法覆盖条件时，优化器可能 合并多个索引 的结果：

-- 可能存在 Index Merge: intersection
SELECT * FROM t WHERE a = 1 OR b = 2;
-- 分别扫描 idx_a、idx_b，再求交/并

EXPLAIN 中 type=index_merge。通常不如设计一个合适联合索引高效，但在特定 OR 条件下有用。

六、前缀索引

6.1 适用场景

对 长字符串（VARCHAR / TEXT / BLOB）只索引前 N 个字符，节省空间、缩短比较时间。

CREATE INDEX idx_url ON pages (url(64));
CREATE INDEX idx_title ON articles (title(20));

6.2 长度选择

前缀越短，索引越小，但 区分度 下降，冲突增多。

-- 估算前缀选择性：越接近 1 越好
SELECT
  COUNT(DISTINCT url) / COUNT(*)                    AS full_selectivity,
  COUNT(DISTINCT LEFT(url, 32)) / COUNT(*)          AS prefix_32,
  COUNT(DISTINCT LEFT(url, 64)) / COUNT(*)          AS prefix_64
FROM pages;
限制 说明
不能用于 ORDER BY / GROUP BY 该列 只有前缀参与排序
不能作为覆盖索引提供完整列值 需回表
唯一索引 前缀唯一 不等于 全列唯一

七、覆盖索引与索引下推

7.1 覆盖索引

覆盖索引 不是独立索引类型，而是指：查询所需列 全部出现在同一索引 中，引擎无需回表。

CREATE INDEX idx_cover ON orders (user_id, status, amount);

-- 覆盖索引：Extra 显示 Using index
SELECT user_id, status, amount FROM orders WHERE user_id = 1001;
收益 说明
减少回表 I/O 只读二级索引页
更小数据量 索引页比聚簇页更紧凑 
EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 1001;
-- Extra: Using index

7.2 索引条件下推（ICP, Index Condition Pushdown）

MySQL 5.6+ / InnoDB：在 存储引擎层 用索引项先过滤，再回表，减少回表次数。

CREATE INDEX idx_ab ON t (a, b);

SELECT * FROM t WHERE a > 1 AND b = 5;
-- 无 ICP：仅按 a 范围扫索引，所有 a>1 的行都回表，再过滤 b
-- 有 ICP：在索引层同时判断 b=5，满足才回表

EXPLAIN 中 Extra: Using index condition 表示启用 ICP。

八、全文索引（FULLTEXT）

8.1 适用场景

对 自然语言文本 做关键词搜索，替代低效的 LIKE '%keyword%'（无法走 B+ 树）。

CREATE TABLE articles (
  id      INT PRIMARY KEY,
  title   VARCHAR(200),
  body    TEXT,
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL index' IN NATURAL LANGUAGE MODE);

8.2 要点

项 说明
引擎 InnoDB（5.6+）、MyISAM 均支持
算法 倒排索引（词 → 文档列表），非 B+ 树
最小词长 默认由 innodb_ft_min_token_size / ft_min_word_len 控制
中文 需 ngram 或 MeCab 等 parser（8.0 可配置 WITH PARSER ngram）
排序 MATCH ... AGAINST 返回相关度，可 ORDER BY 相关度 
-- ngram 中文全文（2-gram 示例）
CREATE FULLTEXT INDEX ft_content ON posts (content) WITH PARSER ngram;
SET GLOBAL innodb_ft_min_token_size = 2;  -- 配合 ngram 使用，需重启或 reload

九、空间索引（SPATIAL）

9.1 适用场景

地理空间数据（点、线、面）的范围查询，使用 R-Tree 结构。

CREATE TABLE places (
  id   INT PRIMARY KEY,
  name VARCHAR(100),
  loc  POINT NOT NULL SRID 4326,
  SPATIAL INDEX sp_loc (loc)
) ENGINE=InnoDB;

SELECT name FROM places
WHERE ST_DWithin(
  loc,
  ST_GeomFromText('POINT(116.4 39.9)', 4326),
  5000  -- 米（需按坐标系理解）
);

9.2 约束

约束 说明
列类型 GEOMETRY 及其子类型（POINT 等）
8.0 之前 仅 MyISAM；8.0 InnoDB 完整支持
SRID 8.0 建议显式声明；不同 SRID 混用受限
锁 使用 Predicate Lock（见 锁机制文档）

十、Hash 索引

10.1 MEMORY 引擎

CREATE TABLE cache (
  k VARCHAR(64),
  v TEXT,
  KEY (k) USING HASH
) ENGINE=MEMORY;
特点 说明
仅 MEMORY 显式支持 USING HASH 等值查询快
不支持范围、排序 不适合 >, <, ORDER BY
数据在内存 重启丢失

10.2 InnoDB 自适应 Hash 索引（AHI）

InnoDB 自动 在 Buffer Pool 热点页上建立 Hash 映射，对用户透明，无法手动创建或控制。

  • 加速 等值 且 频繁访问 的 B+ 树路径；
  • 高并发下可能成为争用点（可通过 innodb_adaptive_hash_index 关闭做对比测试）。

十一、MySQL 8.0 索引增强

11.1 降序索引（Descending Index）

5.7 及更早：DESC 在索引定义中被 忽略，实际仍升序存储；ORDER BY col DESC 可能额外 filesort。

8.0 起支持真正的降序索引键：

CREATE INDEX idx_time_desc ON orders (created_at DESC);

-- 混合升降序
CREATE INDEX idx_mixed ON orders (user_id ASC, created_at DESC);

优化器可 正向扫描升序索引 或 反向扫描；显式降序索引在混合排序场景更有优势。

11.2 函数索引（Functional / Expression Index）

对 表达式 建索引，使 WHERE 中的函数与索引定义一致时可用索引。

CREATE INDEX idx_lower_email ON users ((LOWER(email)));

SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- 可用 idx_lower_email
注意 说明
表达式需 完全匹配 WHERE email = ... 不会用 LOWER(email) 索引
生成列替代方案 5.7 可用 GENERATED COLUMN + 普通索引；8.0 函数索引更直接
仅部分函数允许 需确定性与合法表达式 
-- 5.7 / 8.0 均可用：虚拟生成列 + 索引
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255)
  AS (LOWER(email)) VIRTUAL,
  ADD INDEX idx_gen (email_lower);

11.3 不可见索引（Invisible Index）

索引 对优化器不可见，但 仍在后台维护（INSERT/UPDATE/DELETE 仍更新），用于安全验证「删索引」影响。

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;
-- 优化器不再考虑该索引
ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;

-- 会话级强制使用（测试用）
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

11.4 Index Skip Scan

8.0 优化器在 联合索引缺少最左列 时，若其他列选择性足够高，可能跳过前缀扫描：

CREATE INDEX idx_gender_age ON employees (gender, age);
SELECT * FROM employees WHERE age = 30;
-- 8.0 可能 Index Skip Scan：按 gender 分段扫描 age=30

EXPLAIN FORMAT=TREE 可见 Skip scan 计划。不能替代合理的联合索引设计，但是有用的兜底。

十二、EXPLAIN 与索引使用判断

12.1 type 与索引（由好到差）

type 含义 典型索引场景
system 系统表单行 —
const 主键/唯一索引等值，最多一行 WHERE pk = 1
eq_ref JOIN 时主键/唯一索引等值 JOIN ... ON pk
ref 非唯一索引等值 WHERE status = 'paid'
ref_or_null ref + NULL 查找 WHERE col = x OR col IS NULL
range 索引范围 BETWEEN, >, IN
index 全索引扫描 覆盖索引但扫描整个索引
ALL 全表扫描 无合适索引 
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';

12.2 关键 Extra 字段

Extra 含义
Using index 覆盖索引
Using index condition 索引条件下推 ICP
Using where Server 层过滤（可能已回表）
Using filesort 额外排序，索引未满足 ORDER BY
Using temporary 使用临时表（GROUP BY / DISTINCT 等）
Backward index scan 8.0 反向扫描索引满足 DESC

12.3 常见「有索引却不用」原因

原因 示例
违反最左前缀 (a,b) 索引但 WHERE b = 1
对索引列 运算 WHERE YEAR(created_at) = 2026
类型隐式转换 字符串列与数字比较
选择性太低 优化器判断全表扫描更便宜
统计信息过期 ANALYZE TABLE t
索引不可见 INVISIBLE 且未开启 use_invisible_indexes

十三、索引设计实践

13.1 何时建索引

适合建 谨慎 / 不建
WHERE / JOIN 高频列 低选择性列单独索引（如性别）
ORDER BY / GROUP BY 常用列 写多读少的小表
外键列 重复索引 (a,b) 与 (a)
唯一业务约束 过度索引拖慢写入

13.2 维护与监控

-- 更新统计信息
ANALYZE TABLE orders;

-- 查看索引 cardinality
SHOW INDEX FROM orders;

-- 8.0 直方图（辅助优化器估算）
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, created_at WITH 64 BUCKETS;

-- 查找未使用索引（需 performance_schema 开启）
SELECT * FROM sys.schema_unused_indexes;

13.3 DDL 注意事项

操作 说明
Online DDL InnoDB 多数 ADD INDEX 可在线，不阻塞读写（大表仍耗 I/O）
ALGORITHM=INPLACE, LOCK=NONE 显式控制；见官方 DDL 矩阵
大表建索引 低峰期；8.0 可调 innodb_ddl_threads 并行建索引
删索引前 8.0 先用 INVISIBLE 观察 
ALTER TABLE large_table
  ADD INDEX idx_x (col),
  ALGORITHM=INPLACE, LOCK=NONE;

十四、索引类型对照总表

名称 结构 唯一性 典型用途 版本
聚簇索引 B+ 树 是 主键组织数据 全版本
二级普通索引 B+ 树 否 加速查询 全版本
唯一索引 B+ 树 是（NULL 除外） 业务唯一 + 加速 全版本
联合索引 B+ 树 可选 UNIQUE 多列组合查询 全版本
前缀索引 B+ 树（部分键） 可选 长文本 全版本
全文索引 倒排 否 关键词搜索 5.6+ InnoDB
空间索引 R-Tree 否 地理范围 8.0 InnoDB
Hash 索引 Hash 否 MEMORY 等值 MEMORY
降序索引 B+ 树 — DESC 排序优化 8.0+
函数索引 B+ 树（表达式） 可选 函数条件 8.0+
不可见索引 B+ 树 — 删索引前验证 8.0+

十五、小结

要点 一句话
InnoDB 核心 一个聚簇索引 + 多个 B+ 树二级索引
联合索引 最左前缀 + 等值在前、范围在后
性能关键 覆盖索引减回表，ICP 减无效回表
特殊场景 全文用 FULLTEXT，地理用 SPATIAL
8.0 增强 降序、函数、不可见、Skip Scan
验证手段 EXPLAIN FORMAT=TREE + ANALYZE TABLE

索引是 MySQL 性能优化 最先考虑 的杠杆，但每多一个索引都会增加写入成本。理解各类索引的 结构差异与适用边界，比盲目「给 WHERE 列全建索引」更重要。

参考链接

posted @ 2026-06-26 15:45  一个老码农  阅读(20)  评论(0)    收藏  举报