MySQL 索引完全指南：从小白到入门

读完本文，你应该能做到：

看懂别人表结构里各种索引的含义

知道一条 SQL 该不该加索引、加在哪里

能解释清楚什么是"最左前缀""回表""覆盖索引""索引下推"

能用 EXPLAIN 判断一条 SQL 有没有走索引

一、为什么要有索引？先从"查字典"讲起

1.1 一个非常朴素的问题

假设你有一张用户表 user，里面有 1000 万条记录：

CREATE TABLE user (
    id       BIGINT       NOT NULL AUTO_INCREMENT,
    name     VARCHAR(50)  NOT NULL,
    age      INT          NOT NULL,
    city     VARCHAR(50)  NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

鲸鱼
现在你要执行：

SELECT * FROM user WHERE name = '张三';

如果 name 字段上没有索引，MySQL 会怎么做？

答案是：从第一行扫到最后一行，挨个比较 name 是不是等于 '张三'——这就是所谓的全表扫描（Full Table Scan）。1000 万行数据，哪怕每行比较只要 1 微秒，也要 10 秒钟。

1.2 索引的本质：一本按字段排好序的"目录"

想象一下你手里有一本 1000 页的《新华字典》，如果字典没有部首目录，你要查"鑫"字，就只能从第 1 页翻到第 1000 页。

而字典前面那几页按部首、拼音排序的目录，就是索引。你先在目录里找到"鑫"字在第 872 页，然后直接翻过去，三两下就找到了。

索引的本质就是这样一张"目录表"：它保存了被索引字段的值，并且是排好序的，同时记录了每个值对应的原始数据行在哪里。

索引 = 字典的部首目录。目录本身也要占纸（占磁盘），目录也要跟着正文更新（写入变慢），但查起来飞快。

1.3 索引的代价

既然索引这么好用，为啥不给每个字段都加上？因为索引不是白来的：

维度	影响
磁盘空间	每个索引都是一棵 B+ 树，要占额外的磁盘
写入性能	`INSERT / UPDATE / DELETE` 时，除了改数据还要改所有相关索引
内存	索引常驻内存（Buffer Pool）才快，索引多了内存也吃紧

所以合理地建索引，而不是"多多益善"，才是工程师该有的态度。

二、索引底层：为什么是 B+ 树？

2.1 为什么不是二叉树、红黑树？

你可能学过二叉搜索树、红黑树，查询复杂度都是 O(log n)，看起来很快。但它们有个致命问题：树太高了。

1000 万条数据的二叉树，树高大约 23 层。
查一次数据，最坏要从根节点走到叶子节点，也就是 23 次磁盘 IO。
而一次磁盘 IO（机械盘）大约 10ms，23 次就是 230ms——慢到爆炸。

2.2 B+ 树：矮胖的多叉树

B+ 树的思路是：每个节点塞很多个 key，这样树就变矮了。

MySQL InnoDB 的 B+ 树，通常一个节点（一页，16KB）能放几百上千个 key。结果就是：

3~4 层的 B+ 树，就能存下几千万条记录。
查一条数据，只要 3~4 次磁盘 IO。
而且 B+ 树的叶子节点之间用链表连接，范围查询非常快（比如 WHERE age BETWEEN 20 AND 30）。

                [根节点: 10, 50, 100]
               /         |          \
       [1,3,7]  [10,20,30,40]  [50,60,70,80,90] ...
          ↓           ↓              ↓
       （叶子节点之间用双向链表串起来，方便范围扫描）

B+ 树不是因为它"理论更优"，而是因为它"磁盘友好"——降低树高，减少 IO。

三、必知必会的五个核心概念

这五个概念是 MySQL 索引的"常客"，我们先列出来，后面每一个都会详细讲：

主键索引 vs 普通索引 的区别
最左前缀匹配原则
索引下推（ICP）
覆盖索引
联合索引

四、索引分类：一次讲明白

4.1 主键索引（Primary Key / 聚簇索引 / 聚集索引）

在 InnoDB 里，主键索引特别特殊：它的叶子节点直接存的就是整行数据，而不是"指向数据的指针"。这种"索引和数据长在一起"的结构，叫做聚簇索引（Clustered Index）。

InnoDB 规定每张表必须有且只有一个聚簇索引，规则如下：

如果你指定了 PRIMARY KEY，那就用它当聚簇索引。
如果没指定，InnoDB 会找第一个非空的 UNIQUE 索引顶上。
还找不到？InnoDB 自己悄悄生成一个 6 字节的隐藏字段 row_id 当主键。

CREATE TABLE user (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)   -- 这就是聚簇索引
);

聚簇索引的叶子节点 = 完整的数据行。所以通过主键查数据最快——找到了就是它，不用再跑第二次。

4.2 普通索引（Key / Index / 二级索引 / 辅助索引）

除主键以外的索引，都叫二级索引（Secondary Index），也叫非聚簇索引。

二级索引的叶子节点存的不是整行数据，而是主键值。所以通过二级索引查数据，通常要走两步：

在二级索引的 B+ 树里，找到对应的主键值
拿这个主键值，去主键索引（聚簇索引）里再查一次，才能拿到整行数据

这个"再查一次"的过程，就是大名鼎鼎的回表（Back to Table）。

-- 给 name 加一个普通索引
ALTER TABLE user ADD INDEX idx_name (name);

-- 执行这条 SQL
SELECT * FROM user WHERE name = '张三';
-- 流程：idx_name 树里找到 name='张三' 的 id → 拿 id 去主键索引里查出整行

4.3 唯一索引（Unique Index）

值不能重复的索引，但允许为 NULL（而且可以有多个 NULL，这一点很多人会搞错）。

-- 建表时指定
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    email VARCHAR(100),
    UNIQUE KEY uk_email (email)
);

-- 给已有表加唯一索引
ALTER TABLE user ADD UNIQUE INDEX uk_email (email);

主键索引 vs 唯一索引的区别：

	主键索引	唯一索引
是否允许 NULL	❌ 不允许	✅ 允许
一个表能有几个	1 个	多个
是否聚簇	✅ 是	❌ 不是

4.4 联合索引（复合索引 / 组合索引）

一个索引覆盖多个列，这是实际工作里用得最多的索引类型。

-- 给 (city, age, name) 三列建联合索引
ALTER TABLE user ADD INDEX idx_city_age_name (city, age, name);

联合索引的 B+ 树，排序规则是先按 city 排，city 相同的再按 age 排，age 也相同的再按 name 排。就像电话本先按姓排，再按名排。

4.4.1 最左前缀匹配原则（重点！）

联合索引 (city, age, name)，只有当你的查询条件从最左边的 city 开始，才能用上这个索引。

查询条件	能否用上 `idx_city_age_name`
`WHERE city = '北京'`	✅ 能（用到 city）
`WHERE city = '北京' AND age = 20`	✅ 能（用到 city + age）
`WHERE city = '北京' AND age = 20 AND name = '张三'`	✅ 能（三个字段都用到）
`WHERE city = '北京' AND name = '张三'`	⚠️ 只能用到 city，name 用不上（中间断了 age）
`WHERE age = 20 AND name = '张三'`	❌ 完全用不上（最左的 city 没出现）
`WHERE name = '张三'`	❌ 完全用不上

联合索引像一串葡萄，你想摘中间或末尾的一颗，必须从最上面那颗开始往下撸，否则葡萄串就掉了。

一个常见误区：查询顺序是否重要？

-- 这条 SQL 能走索引吗？
SELECT * FROM user WHERE age = 20 AND city = '北京';

答案：能！ MySQL 的查询优化器（Optimizer）会自动帮你调整条件顺序，只要字段都在、从最左开始连续就行。所以你不用死记条件的书写顺序，关键是字段要齐。

4.5 全文索引（FULLTEXT）

用来做文本搜索，比如"文章里包含关键词 MySQL"的那种场景。只能建在 CHAR / VARCHAR / TEXT 列上。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT KEY ft_title_body (title, body) WITH PARSER ngram  -- 中文要用 ngram 分词器
) ENGINE=InnoDB;

-- 自然语言模式搜索
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

-- 布尔模式：+ 必含，- 必不含，* 通配符
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +性能 -旧版本' IN BOOLEAN MODE);

注意事项：

中文需要 ngram 分词器（MySQL 5.7.6+ 自带）
ngram_token_size 默认是 2，也就是按 2 个字切词；1 个字的词会被忽略
虽然比 LIKE '%关键词%' 快很多，但写入时维护成本高
真要做中文搜索，生产环境建议直接上 Elasticsearch，全文索引只是应急方案

4.6 前缀索引

当一个字段很长（比如 email、URL），把整个字段都塞进索引既占空间又慢。这时可以只索引前 N 个字符：

CREATE TABLE user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_email_full (email),       -- 完整索引
    INDEX idx_email_pre  (email(10))    -- 只索引前 10 个字符
);

前缀索引的优缺点：

✅ 节省空间
❌ 无法使用覆盖索引（后面会讲），因为 MySQL 不确定"截断的前缀"够不够还原原值，必须回表
❌ 排序（ORDER BY）和分组（GROUP BY）也用不上

怎么选前缀长度？ 看区分度：

-- 完整字段的区分度
SELECT COUNT(DISTINCT email) / COUNT(*) FROM user;

-- 前 5、7、10 个字符的区分度
SELECT
    COUNT(DISTINCT LEFT(email, 5))  / COUNT(*) AS r5,
    COUNT(DISTINCT LEFT(email, 7))  / COUNT(*) AS r7,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS r10
FROM user;

区分度越接近 1 越好，一般能到 0.95 以上就可以用。

4.7 空间索引（SPATIAL）

给 GIS 数据（地理坐标、多边形等）用的，字段类型必须是 GEOMETRY / POINT / LINESTRING / POLYGON，而且必须 NOT NULL。日常业务开发基本用不上，知道有这东西就行。

4.8 外键索引（Foreign Key）

严格说外键是一种约束，不是索引，但 MySQL 建外键时会自动建一个索引来加速约束检查。互联网公司实际开发里，一般不用物理外键，改用应用层保证一致性，性能和运维都更好。

五、聚簇索引 vs 非聚簇索引：一图看懂

这是很多人晕头的地方，我们用一张表讲清楚：

	聚簇索引（主键索引）	非聚簇索引（二级索引）
叶子节点存什么？	整行数据	索引列值 + 主键值
一个表有几个？	只有 1 个	可以有多个
查询流程	一次搞定	先查二级索引拿主键，再回表查聚簇索引

一句话总结：

聚簇索引 = 索引和数据是同一棵树
非聚簇索引 = 索引树 + 回表查数据树

六、回表、覆盖索引、索引下推：一条查询的三种命运

我们用同一张表，看同一类查询，在不同索引情况下分别发生了什么。

CREATE TABLE student (
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age  INT,
    city VARCHAR(50),
    INDEX idx_name_age (name, age)     -- 联合索引
);

6.1 回表（Back to Table）

SELECT * FROM student WHERE name = '小李';

流程：

在 idx_name_age 树里找到 name='小李' 的记录，拿到 id
拿着 id 去主键索引树再查一次，取出整行数据

第 2 步就是回表。因为 SELECT * 要取所有列，而二级索引里只有 id / name / age，city 必须回表才能拿到。

6.2 覆盖索引（Covering Index）

SELECT age FROM student WHERE name = '小李';

流程：

在 idx_name_age 树里找到 name='小李' 的记录，这个节点上已经有 age 了
直接返回 age，不回表

这就是覆盖索引：你要的数据，索引树里都能提供，不用再跑主键树了。

覆盖索引 = "我要的你都有，不用再跑一趟"。

怎么验证走了覆盖索引？ 用 EXPLAIN：

EXPLAIN SELECT age FROM student WHERE name = '小李';

看 Extra 列，如果出现 Using index，恭喜，你用上了覆盖索引。

覆盖索引和联合索引什么关系？
没本质区别。覆盖索引是一种"SQL 刚好命中了联合索引所有字段"的现象，不是一种新索引类型。同一个联合索引，对 SQL A 可能是覆盖索引，对 SQL B 就不是。

6.3 索引下推（Index Condition Pushdown, ICP）

MySQL 5.6 加的优化。还是上面的联合索引 idx_name_age (name, age)：

SELECT * FROM student WHERE name LIKE '李%' AND age = 20;

5.6 之前：因为 name LIKE '李%' 只用到最左的 name，MySQL 会先从索引里拿出所有姓李的 id，一个个回表，再在回表后的数据上过滤 age = 20。如果有 10000 个姓李的，就要回表 10000 次。
5.6 之后（有 ICP）：MySQL 在索引层就能看到 age（因为是联合索引），直接先用 age = 20 过滤掉不匹配的，只回表真正满足的那几条。回表次数大幅下降。

EXPLAIN 里能看到 Using index condition 就说明用了 ICP。

七、SQL 实战：建索引、查索引、删索引

7.1 创建表时建索引

CREATE TABLE live_anchor_subscription (
    rec_id    INT(10)    NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    anchor_id BIGINT(15) NOT NULL                COMMENT '主播 id',
    user_id   BIGINT(15) NOT NULL                COMMENT '用户 id',
    in_dtm    INT(10)    NOT NULL                COMMENT '创建时间',
    status    INT(1)     NOT NULL DEFAULT 1      COMMENT '状态 -1 无效 1 有效',
    PRIMARY KEY (rec_id),
    UNIQUE  KEY uk_user_anchor (user_id, anchor_id),  -- 唯一索引：一个用户只能订阅一个主播一次
    INDEX       idx_anchor_id  (anchor_id),           -- 普通索引
    INDEX       idx_user_id    (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主播订阅表';

💡 命名规范建议：

主键：PRIMARY KEY，不用命名

唯一索引：uk_字段名

普通索引：idx_字段名

联合索引：idx_字段1_字段2

7.2 给已有表加索引

-- 方式一：ALTER TABLE（推荐，能一次加多个）
ALTER TABLE user ADD INDEX idx_name (name);
ALTER TABLE user ADD UNIQUE INDEX uk_email (email);
ALTER TABLE user ADD INDEX idx_city_age (city, age);

-- 方式二：CREATE INDEX（不能建 PRIMARY KEY）
CREATE INDEX idx_name ON user (name);
CREATE UNIQUE INDEX uk_email ON user (email);

⚠️ 线上加索引要小心：大表加索引会锁表或写放大。MySQL 5.6+ 支持 Online DDL，但仍建议：

避开业务高峰
大表（千万级以上）用 pt-online-schema-change 或 gh-ost

7.3 查看索引

SHOW INDEX FROM user;

返回的几个关键字段：

字段	含义
`Table`	表名
`Non_unique`	是否允许重复，0 表示唯一索引
`Key_name`	索引名（`PRIMARY` = 主键）
`Seq_in_index`	索引中的列顺序（联合索引从 1 开始编号）
`Column_name`	索引的列名
`Cardinality`	基数，大致表示不重复值的个数，越大索引越有效
`Index_type`	索引类型，InnoDB 通常是 `BTREE`

7.4 删除索引

-- 删除普通索引 / 唯一索引
ALTER TABLE user DROP INDEX idx_name;
DROP INDEX idx_name ON user;

-- 删除主键
ALTER TABLE user DROP PRIMARY KEY;

八、实战案例：一条慢 SQL 的优化全流程

假设线上这条 SQL 很慢：

SELECT id, name, age
FROM user
WHERE city = '北京' AND age BETWEEN 20 AND 30
ORDER BY age;

第 1 步：用 EXPLAIN 看执行计划

EXPLAIN SELECT id, name, age
FROM user
WHERE city = '北京' AND age BETWEEN 20 AND 30
ORDER BY age;

关键看几个列：

列	重点看什么
`type`	最好 `ref / range`，最差 `ALL`（全表扫描）
`key`	实际用了哪个索引，`NULL` 就是没走索引
`rows`	预估扫描行数，越小越好
`Extra`	`Using index` 覆盖索引好；`Using filesort` 额外排序坏；`Using temporary` 用临时表坏

第 2 步：分析问题

如果 key = NULL、type = ALL：没走索引，全表扫描。
如果 Extra 有 Using filesort：排序没走索引，可能要优化。

第 3 步：建合适的索引

-- 联合索引：city 做等值过滤放最左，age 做范围+排序放第二
ALTER TABLE user ADD INDEX idx_city_age_name (city, age, name);

为什么这么建？

city = '北京'：等值，放最左
age BETWEEN 20 AND 30 以及 ORDER BY age：范围 + 排序，放第二位，刚好能用索引排序，避免 filesort
name 放第三位：让 SELECT id, name, age 能走覆盖索引，不回表

第 4 步：再次 EXPLAIN 验证

理想结果：

type = range
key = idx_city_age_name
Extra = Using where; Using index（用了索引 + 覆盖索引）

九、索引使用的十大注意事项

1. 索引要建在 WHERE / JOIN / ORDER BY 的字段上

加在 SELECT 后面的字段没意义。真正起过滤作用的才需要索引。

2. 区分度太低的字段不适合建索引

比如 sex（只有男、女）、status（只有几种状态）。MySQL 发现走索引反而比全表扫还慢时，会直接放弃索引。

3. `OR` 条件经常让索引失效

-- 这条 SQL，即使 name 和 age 都有单独索引，也可能走全表扫
SELECT * FROM user WHERE name = '张三' OR age = 20;

-- 改用 UNION 往往更快
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE age = 20;

4. 在列上做运算或函数调用，索引失效

-- ❌ 索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2026;
SELECT * FROM user WHERE id + 1 = 100;

-- ✅ 改写
SELECT * FROM user WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
SELECT * FROM user WHERE id = 99;

MySQL 8.0 支持函数索引，可以绕过第一种限制，但一般还是建议改写 SQL。

5. 隐式类型转换会让索引失效

-- phone 是 VARCHAR 类型
-- ❌ 传入数字，MySQL 会对 phone 做 CAST，索引失效
SELECT * FROM user WHERE phone = 13800000000;

-- ✅ 传入字符串
SELECT * FROM user WHERE phone = '13800000000';

6. `LIKE '%xxx'` 失效，`LIKE 'xxx%'` 有效

SELECT * FROM user WHERE name LIKE '张%';    -- ✅ 能用索引
SELECT * FROM user WHERE name LIKE '%张';    -- ❌ 用不了
SELECT * FROM user WHERE name LIKE '%张%';   -- ❌ 用不了

原因：B+ 树是按前缀排序的，给你前缀才能二分查找，给你后缀它懵圈。

7. NULL 值的处理

字段有 NULL 值会让优化器工作变复杂。建议所有字段都加 NOT NULL，用空字符串 '' 或 0 做默认值。

8. `NOT IN`、`!=` 通常不走索引

-- 基本都是全表扫
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE id NOT IN (1, 2, 3);

-- 可以尝试改成 EXISTS / 正向条件

9. 联合索引把最常用 + 区分度最高的字段放最左

根据业务实际查询场景来定，而不是拍脑袋。

10. 一张表索引数量别超过 5 个（经验值）

太多索引会严重影响写入性能，而且大部分情况下优化器也只会用一个。复合索引能覆盖的场景，就别拆成多个单列索引。

十、Key 和 Index 的区别：一点概念澄清

这俩经常混用，但严格来说：

KEY 同时包含两层意思：约束（Constraint）+ 索引（Index）
- PRIMARY KEY：唯一非空约束 + 聚簇索引
- UNIQUE KEY：唯一约束 + 唯一索引
- FOREIGN KEY：引用完整性约束 + 索引
INDEX 只是单纯的索引，不带任何约束含义

所以：

-- 下面这两种写法在"加普通索引"这件事上完全等价
CREATE TABLE t (id INT, KEY idx_id (id));
CREATE TABLE t (id INT, INDEX idx_id (id));

而涉及 PRIMARY / UNIQUE / FOREIGN 时，用 KEY 更准确，因为它强调了约束含义。

十一、总结：一张表带走所有要点

概念	一句话记住它
索引本质	按字段排好序的"目录树"
B+ 树	矮胖多叉树，降低树高减少 IO
聚簇索引	叶子节点 = 整行数据，每表仅一个
二级索引	叶子节点 = 主键值，查数据要回表
回表	用二级索引的主键再查一次聚簇索引
覆盖索引	索引树上就有要的数据，免回表
最左前缀	联合索引从最左列开始用，中间不能断
索引下推（ICP）	能在索引层过滤的条件不等回表后再过滤
前缀索引	索引字段前 N 个字符，省空间但不能覆盖索引
EXPLAIN 关键字	`Using index` 好、`Using filesort` 要警惕、`type=ALL` 要优化

十二、最后的小白建议

新表设计时就把索引规划好，别等上线后慢了再加。
一切以 EXPLAIN 为准，不要凭感觉判断有没有走索引。
慢查询日志（slow_log）开起来，定期捞出慢 SQL 优化。
索引不是越多越好，写多读少的表少加索引。
生产环境改索引，大表务必走 pt-online-schema-change 或 gh-ost，别直接 ALTER TABLE。

索引这块的内容看起来多，但核心就两句话：

读操作用索引加速查找，写操作为索引付出代价。

建对索引，SQL 起飞；建错索引，资源白烧。

祝你在每一次 EXPLAIN 里都能看到满意的 Using index 🎉