MySQL

MySQL 索引

以目前使用最多的 InnoDB 为例，这篇文章聊聊 MySQL 里那些绕不开的索引。

#MySQL#数据库#索引#SQL

1. 索引基础与实现

B+Tree（B+ 树） 是 InnoDB 默认用来构建二级索引和主键索引的底层设计。

你可以把它想象成一本排版极其讲究的字典：它的叶子节点都是按顺序排好队的，而且彼此之间还手拉手连成了链表，这让它在处理“帮我找从 A 到 C 开头的所有数据”这种范围查询和排序时，简直如鱼得水。

索引 B+ 树结构

根节点Root Node非叶子节点Internal Nodes(仅存索引键与指针)叶子节点Leaf Nodes(双向链表相连·存数据)1556771815305668ID: 1Data 行ID: 5Data 行ID: 8Data 行ID: 11Data 行ID: 15Data 行ID: 24Data 行ID: 56Data 行ID: ...Data 行

InnoDB 的存储模型：

简单来说，InnoDB 把整个表的数据都“绑定”在了主键索引上，这也就是我们常说的 聚簇索引（clustered index），换句话说，“主键索引的叶子节点里，塞的就是完完整整的行数据”。

而所有的 二级索引 就没这么好命了，它们的叶子节点里不存完整数据，只存对应的那条数据的 主键值（相当于存了个指向正文的页码），所以，顺着二级索引查到主键后，通常还得拿着这个主键跑回聚簇索引去查完整数据（这就叫回表）。

不着急，下面会进一步说明这些概念。

Note

InnoDB 的 B+ 树节点里不存 MVCC 的那些版本控制信息，MVCC 是靠行版本记录（undo log）来搞定的，隔离级别会影响你能查到哪些数据，但这跟索引的数据结构本身没什么直接关系。

主键索引就是数据的大本营（聚簇索引），而二级索引充当的则是指向大本营的“路标”。

2. 索引类型

有 主键索引、非主键索引、 聚簇索引、非聚簇索引，我们挨个说。

2.1 主键索引

  • 在 InnoDB 里，主键索引就是 聚簇索引 ，只要你定义了主键，InnoDB 就会按照主键的顺序来排列所有的数据行。
  • 主键必须要唯一（如果你建表时偷懒没设主键，InnoDB 也会在后台偷偷塞一个隐藏的聚簇主键 db_row_id 给你）。
  • 最主要的是，主键索引的叶子节点就是完整的行数据本身。

优点嘛，因为数据就在手边，基于主键去搞范围扫描或者排序完全不需要“回表”，效率很高。

2.2 聚簇索引 vs 非聚簇索引

  • 聚簇索引（clustered index）：数据行按照索引键的顺序挨个排好（InnoDB 一张表只允许有一个聚簇索引，通常就是主键），它的叶子节点带着整行数据。
  • 非聚簇索引（non-clustered/secondary index）：也叫二级索引，它的叶子节点里只有对应的主键值（像个指针一样），没有完整数据,要想看整行数据，得拿着主键值再去聚簇索引跑一趟，也就是“回表”。

聚簇索引与非聚簇索引对比图示

聚簇索引Clustered Index通常为主键非聚簇索引Secondary Index二级索引 / 辅助索引B+ 树 叶子节点Key (索引键)主键 ID = 10绑定Value (完整行数据 Row Data){ id: 10, name: 'Alice', age: 25, ... }B+ 树 叶子节点Key (索引列)Name = 'Alice'绑定Value (仅主键值)主键 ID = 10※ 缺失行数据，若需完整数据则必须触发 "回表"

2.3 唯一索引与普通索引

  • 唯一索引 顾名思义，就是保证这一列的值绝对不重样，它在底层的长相跟普通索引一模一样，只不过在你插入数据时，它会严格查岗防重复。在某些绝佳场景下，唯一索引也能配合查询列玩出“覆盖索引”的提速操作。

2.4 全文索引与哈希索引（其他）

  • FULLTEXT 全文索引：遇到复杂的文本内容匹配时派它上场（这是另一种技术，不是 B+ 树）。
  • HASH 索引：比如 MEMORY 引擎爱用的这种，基于哈希表实现，精准点查就很快，但一旦遇到范围查询就直接傻眼不支持了。

3. 什么是回表

3.1 回表定义

其实前面已经提到过两次回表了☝🏻，简单来说，想要拿到数据，要经过这样的流程:

如果是主键索引: id 索引 完整行数据

如果是二级索引: 二级索引值 id 值 完整行数据

也就是当你顺着二级索引千辛万苦找到了目标记录，却发现你要的 SELECT 字段不在这个索引里怎么办？没办法，存储引擎只能拿着二级索引里存的那个主键值，再跑回聚簇索引（表里）去扒出完整的行数据，这来回折腾的额外过程，就是 回表（row lookup / lookup）。

3.2 回表示例

CREATE TABLE t (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name (name)
);

-- 查询（使用 idx_name）
SELECT id, name, age FROM t WHERE name = 'alice';

Tip

这个表有 2 个索引，1 个是主键 id， 1 个是 name 普通索引

现在要根据 name 去查询 id, name, age

注意哈: age 没有索引哦！

就拿上面这段 SQL 来说，它会先乖乖走 idx_name 索引，找到 name = 'alice' 的叶子节点，并拿到了对应的 id（主键），但是你还要查出 age 啊！于是它只能拿着这个 id 回头去找主键索引，把 age 给读出来。

回表

步骤 1查辅助索引树步骤 2回表查主键树结果SELECT id, name, age FROM users WHERE name = 'Alice'条件匹配name = 'Alice'辅助索引 B+ 树获取到主键值ID = 10拿主键去查ID = 10聚簇索引 B+ 树最终获取完整数据{age: 28, name... }✓ 返回结果给客户端 回表

3.3 如何减少回表

  1. 覆盖索引（covering index）：把你要查的所有列都塞进一个 联合索引 里，查询直接在索引层就大功告成，彻底消灭回表, 覆盖索引具体说明下面章节会单独说。
    • 比如：建个 INDEX idx_name_age (name, age)，name 和 age 都在索引里，这时候你查 SELECT age FROM t WHERE name='alice'，直接在索引里就能拿到数据，无需回表。
  2. 少用二级索引：对于那些被疯狂查询的热点数据，干脆把主键设计成常用的检索条件，直接命中聚簇索引最香。
  3. 垂直拆分：遇到又肥又大的热点字段，干脆把它单拎出去建表建索引，免得整行数据太大，导致回表的代价飙升。
  4. 高选择性优先：先按高区分度的索引查出少量数据，减少回表的总次数。比如对“性别”这种低基数字段建索引，回表的开销就非常大了（区分度说明可参考下文 6.1）。
  5. 该删就删，使用 INVISIBLE：这是 8.0 才支持的，就是别给 MySQL 留太多乱七八糟的索引，免得它选了个次优的，导致无畏的大量回表。

4. 索引覆盖与索引下推

4.1 索引覆盖

如果你写的 SELECT 里的列，以及后面的 WHERE、 ORDER BY、 GROUP BY 用到的列，恰好全都在同一个（联合）索引的射程范围内，那么恭喜你，这次查询可以直接在索引里闭环，压根不用回表。这时候用 EXPLAIN 跑一下，你会看到 Extra 字段显示出 Using index，这就是所谓的覆盖索引（index-only scan）。

示例（覆盖）：

CREATE TABLE t (
  id INT PRIMARY KEY,
  a INT,
  b INT,
  c INT,
  INDEX idx_a_b (a,b)
);

-- 覆盖查询：a 和 b 都在索引里
SELECT b FROM t WHERE a = 10;

你要找 b，过滤条件是 a；而联合索引 idx_a_b 刚好把 a,b 都包圆了，所以直接从索引里把 b 拿走就行！

示例（非覆盖）：

SELECT c FROM t WHERE a = 10;

不好意思，c 这个家伙不在 idx_a_b 索引里，必须得乖乖回表去拿，这时候 EXPLAIN 的 Extra 里自然就看不见 Using index 了。

4.2 索引下推

索引下推（Index Condition Pushdown, 我们简称: ICP）

ICP 是 InnoDB 变得越来越聪明的一个证明：当它在扫描二级索引时，如果遇到类似 col2 > 100 这样的过滤条件，它会尽可能地先把这个条件“下推”到索引层去做个初步筛选，把那些明显不靠谱的主键直接在门外拦截，只把可能符合条件的主键放进去回表，大大减少了来回跑腿的次数。

但要注意，ICP 并不能取代覆盖索引，因为索引层毕竟只认识索引里的那一亩三分地；如果你的过滤条件非得看非索引列的脸色，最终还是免不了要回表去确认。

何时会用到 ICP：MySQL 5.6+ 的 InnoDB 支持；当你用二级索引扫描，且有些过滤条件没法单靠索引前缀搞定的时候，MySQL 就可能使用 ICP。在 EXPLAIN 里，如果 Extra 出现了 Using index condition，并且能看到 filtered 字段的数据，就说明 ICP 上场了。

示例：

CREATE TABLE t (
  id INT PRIMARY KEY,
  a INT,
  b INT,
  INDEX idx_a (a)
);

-- 查询：根据 a 过滤，但还需检查 b > 100
SELECT id FROM t WHERE a BETWEEN 1 AND 100 AND b > 100;

在这个例子里，idx_a 倒是能用来搞定 a BETWEEN 1 AND 100 的范围扫描，可 b > 100 并不在索引里呀，这时候 ICP 就会在索引层先做个粗筛，把大部分不符合 a 条件的记录踢掉，存储引擎在扫描索引记录时，直接在索引层判断 b <=100 的记录并跳过，无需回表 ，只有那些有可能达标的记录，才会真的去回表检查 b > 100，省下了不少力气。

ICP 的限度：如果是完美的覆盖索引，根本轮不到 ICP 出场；而如果你的复杂条件大部分都跟非索引列沾边，ICP 能帮的忙也就十分有限了。

5. 最左前缀原则

最左前缀原则（Left-most Prefix），可以理解为 “从左往右” 的处理。

假如现在有一个联合索引 INDEX(a,b,c)，它只能利用这个索引最左边连续的前缀列来匹配：比如查 (a)、查 (a,b)、或者查全套 (a,b,c) 都没问题。但如果你上来就单查一个 (b) 或者 (c)，那对不起，MySQL 根本不认识，除非优化器强行做索引合并之类的特殊处理。

例子：

CREATE INDEX idx_abc ON t(a,b,c);

-- 可以顺滑使用 idx_abc：
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 没法直接使用 idx_abc（最左前缀断裂，失效）：
SELECT * FROM t WHERE b = 2; -- 除非有其他索引或索引合并

Warning

注意：有个大坑！如果你在最左边的列上用了范围查询（比如 a > 10 或者 a BETWEEN ...），那么 a 这一列的索引确实是用上了，但是！它后面的 b,c 兄弟可能就没法接着往下精确匹配了（因为范围查询打断了索引的连续处理）。

举个例子，在 WHERE a BETWEEN 1 AND 10 AND b = 2 AND c = 3 中，a 走了范围，后续的 b 和 c 大概率只能沦为 ICP 的辅助过滤条件，精准匹配是没戏了（具体还得看 MySQL 版本和优化器了，在 MySQL 8.0 之前通常如此；8.0+ 优化器更智能，具体需以 EXPLAIN 为准）。

6. 设计索引时要考虑的原则与建议

6.1 常规原则

  1. 一切为了查询：别为了建索引而建索引，先梳理业务里最常出现的 WHERE、 JOIN、 ORDER BY、 GROUP BY，照方抓药建索引才是最适合的。
  2. 区分度：基数高（值域广、重样少）的列更适合建索引，像“性别”、“状态码”这种翻来覆去就几个值的字段，单拎出来建索引简直是浪费感情，收益极低。实在要建，就跟数据倾斜高的、高选择性的列绑在一起搞联合索引。
  3. 给索引“瘦身”：索引列越多、字段越宽，B+ 树的节点就越臃肿，IO 压力就越大，能用短整型或短字符串就别用长文本。
  4. 清理那些占着茅坑不拉屎的冗余索引：重复建一堆功能差不多的索引，除了拖慢你 insert、update、delete 的写入速度，毫无用处，可以过段时间 SHOW INDEX FROM table 检查一下，看看实际使用情况，该合并合并，该删就删。
  5. 主键设计：主键最好是那种稳定的、唯一的、定长的，最好还是单列的（比如自增 ID 或雪花 ID），因为所有的二级索引叶子里都得存一份主键，主键越小，二级索引也就越轻巧。
  6. 尽量避免在频繁写入的列上建索引：每次数据变动，数据库都得去维护背后的那堆索引树，索引越多，写数据就越会拉垮。

6.2 联合索引策略

  1. 把最常用于筛选的列放在联合索引的最前面，牢记最左前缀法则。
  2. 在对付多条件组合过滤时，把区分度高（高选择性）的列排在前面往往效果更好。
  3. 如果你的业务特别喜欢用 ORDER BY a,b 或是 GROUP BY a,b，搞个 INDEX(a,b) ，既省了内存排序的开销，又能避免去回表。
  4. 如果你经常只查 a，但也频频连着 a,b 一起查，那就可以设计一个 INDEX(a,b) 就能把这两类查询全包了，如果这时候你表里还有一个单独的 INDEX(a)，就可以考虑删掉它了，留下组合的就够了，还能腾出来一点空间。

6.3 实战建议

  • 首先，跑个 EXPLAIN 看看执行计划到底长啥样。
  • 如果觉得优化器不稳定、不清醒，选错了索引，用 ANALYZE TABLE 更新一下统计信息。
  • 对于那些被疯狂使用的热点查询，可以想办法凑个覆盖索引，避免回表。
  • 如果非要在长篇大论的字符串上建索引，试试前缀索引（比如 VARCHAR(255) 只拿前 100 个字节建 INDEX(col(100))）。不过代价就是，前缀索引没法保证绝对唯一，而且在排序和覆盖索引方面会有点力不从心。
  • 养成好习惯，定期查水表（结合慢查询日志、pt-index-usage/pt-query-digest 等利器），清理掉一些僵尸索引。

7. 如何排查索引是否失效及优化

7.1 常见导致索引失效的场景

写 SQL 就像扫雷，一不小心你的索引就作废了：

  1. 对列用函数或表达式：比如写个 WHERE UPPER(name) = 'A'，名字都被你改头换面了，索引树直接不认识了（除非你搞个高级的函数索引）。
  2. 类型转换：明明是个整数 id，你偏要传个字符串 WHERE id = '123'，虽然聪明的优化器有时候能兜底，但在复杂表达式下，可能会导致索引罢工。
  3. 模糊查询把 % 放前面：LIKE '%abc' 这种查法，由于不知道打头的是什么字母，B+ 树根本无从下手；所以可以写 LIKE 'abc%' 就不要写双 LIKE '%abc%' , 才能走索引。
  4. 最左前缀没对齐：比如建了联合索引，查询时却完美避开了最左边的那一列，直接 WHERE b = ?。
  5. OR 关键字（部分情况）：像 WHERE a = 1 OR b = 2 这种，就算 a 和 b 都有各自的索引，优化器也可能觉得麻烦，干脆搞个索引合并，甚至直接摆烂去全表扫描（具体行不行，得看 EXPLAIN 的结果了）。
  6. 优化器优化：如果你建的索引区分度极差（比如扫一下发现 90% 的行都符合），优化器心里一盘算：“用索引查完还要回表，这成本比老子直接全表扫描还高！”，于是果断弃用索引。
  7. 隐式或显式的 NOT IN：有时候也会影响优化器的处理，导致选错路线不走索引，NOT IN、!= 在低选择性时优化器可能选择全表扫描。

7.2 用 EXPLAIN 排查（示例）

遇到慢 SQL，赶紧祭出这招：

EXPLAIN FORMAT=TRADITIONAL
SELECT id FROM t WHERE name = 'alice' AND age = 30;

重要的是这几个指标：

  • possible_keys：手里有哪些牌（可能用上的索引）
  • key：实际打出的是哪张牌（最后选定的索引，如果是 NULL 说明在裸奔）
  • rows：估算要翻多少行数据
  • Extra：重中之重！看到 Using where、Using index、Using index condition、Using filesort 等字眼，就能判断效率高低以及是否实现了覆盖。

例子：真假覆盖索引

-- 完美覆盖（Extra : Using index）
EXPLAIN SELECT b FROM t WHERE a = 10;
-- 没盖住，得回表（Extra 通常显示: Using where）
EXPLAIN SELECT c FROM t WHERE a = 10;

7.3 常用优化手段

  1. 查漏补缺：看着 EXPLAIN 的结果和真实的业务查询路径，该加联合索引就加，该调整列的顺序就调。
  2. 重写 SQL：把那些在等号左边作妖的函数操作移到右边去，或者干脆在代码逻辑里处理掉，遇到不走索引的 OR，试着改写成 UNION 也许就能峰回路转。
  3. 覆盖索引：尽可能把要查的列揉进索引里，省去回表那一步。
  4. 减少 SELECT * 的使用：要什么拿什么，说不定少拿几列往往就刚好能凑成覆盖索引了。
  5. 刷新：时不时跑个 ANALYZE TABLE t，让优化器重新评估一下数据基数。
  6. 垂直/水平拆分：当表实在太胖，或者某些字段和另一些字段八竿子打不着时，把表拆开能减轻 IO 负担。

7.4 其余案例

案例 A：覆盖索引（正例）

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id INT,
  status TINYINT,
  created_at DATETIME,
  bus_no VARCHAR(64),
  INDEX idx_user_status_created (user_id, status, created_at)
);

-- 只想查创建时间
SELECT created_at FROM orders WHERE user_id = 123 AND status = 1;
-- 完美！所需字段全在 idx_user_status_created 这个联合索引里，不用回表，起飞！

案例 B：未覆盖（反例）

-- 还是上面那个索引
SELECT order_id, created_at, bus_no FROM orders WHERE user_id = 123 AND status = 1;

Important

bus_no 字段不在索引中, MySQL 拿到索引节点后，还得根据隐藏的或关联的规则回表找完整行，所以 Extra 不会显示 Using index

案例 C：走索引+ICP（正例）

CREATE INDEX idx_a ON t(a);
-- 复合查询
SELECT id FROM t WHERE a BETWEEN 1 AND 100 AND b > 100;
-- 走 idx_a 查 a 的范围时，ICP 发挥作用，提前在索引层把那些连 b > 100 的边都摸不着的记录给干掉了，大大缩减了回表的数量。

案例 D：索引失效（反例）

-- 明明建了 idx_name(name)
SELECT * FROM t WHERE UPPER(name) = 'ALICE'; -- 用了函数，索引失效

-- 左通配符
SELECT * FROM t WHERE name LIKE '%alice%'; -- 不符合最左匹配规则，索引失效

8. 附：常用命令

  • EXPLAIN [FORMAT=JSON|TRADITIONAL] <sql>：SQL 检查利器，看执行计划。
  • SHOW INDEX FROM table：查询表里所有的索引细节（基数、列名全都有）。
  • SHOW CREATE TABLE table：一键查看最初建表和建索引的语句。
  • ANALYZE TABLE table：重新收集数据库中特定表或索引的统计信息。
  • OPTIMIZE TABLE table：主要作用是 整理数据存储、回收空间碎片、重建索引，当表经过频繁的 DELETE、UPDATE 或 INSERT 操作后，它能有效提高查询速度，减少物理磁盘占用。。
  • 慢查询日志（slow_query_log）：查询慢 SQL。
  • pt-index-usage / pt-query-digest：Percona 家族的第三方神仙工具包，分析索引到底有没有被用上。