【面试资料】MySQL篇 之 索引

〇. 前言

本文将系统地介绍MySQL数据库中索引的相关知识，包括

• 索引的概念 / 原理
• 聚簇索引
• 覆盖索引
• 索引创建
• 索引失效

其中会穿插 回表查询、深度分页、联合索引 等常见问题的解决策略，
最后我们还将梳理 sql优化 的有效方案。

注意：若未特别说明，本文的相关概念、实现原理皆基于MySQL的InnoDB引擎进行解释。

一. 索引概述

1. 概念

• 首先，索引是一种由数据库存储并维护着的数据结构，占用磁盘空间。
• 这种数据结构以某种高效的方式指向表中数据，加快数据读取。
• MySQL的索引是B+树，常见的类型还有B树、红黑树等。

2. 为何高效？

只看概念可能还是比较模糊。下面通过一个例子直观感受：索引有多快，索引为什么快。（该例旨在辅助了解索引，不严谨之处暂且忽略）

# 对于以上 person 表，我们采用下面的 sql 语句进行查询
select name from person where age = 45;

① 字段 age 没有索引
如果不添加索引，查找时会从头到尾遍历 person 表，挨个比对 age。

② 字段 age 使用二叉搜索树作为索引

上图就是给 字段age 添加索引后的查找效果；
按照二叉搜索树“左小右大”的原则组织数据元素，大大提升查找效率；
当数据元素发生变动时，DB会按照二叉搜索树的增删规则维护该索引。

至此，我们总结 索引高效的原因：

① 借助索引，查询过程不需要全表扫描，降低IO成本，加快查询速度；

② 添加索引的字段会进行排序，高效的数据结构会降低数据排序的成本，降低CPU消耗。

3. 常见数据结构

索引 是 高效的、有序的，但索引的实现方式之间亦有优劣之分。

接下来介绍几种可以用于做索引的数据结构，重点掌握其特点与优缺点，插入删除等操作不做详细说明。

（1）BST

① 特点：二叉搜索树的特点是 “左小右大” ；

② 插入时，不管理树的高度；

③ 删除时，选择左右子树中的直接后继去代替删去的结点。

④ 弊端：二叉搜索树不管理树的高度，可能会出现代价 O(n) 的最坏二叉树。

（2）AVL

① 特点：平衡二叉树中，任意结点的左右子树高度差不超过1，否则将会调整；

② 平衡二叉树一定程度上解决了树的高度问题，搜索效率提升；

③ 弊端：AVL 过度频繁的调整会导致增删效率下降，通常不会使用。

（3）红黑树

① 特点：红黑树满足BST的“左小右大”原则，根结点保持黑色。不存在两个相邻的红色结点，一个结点到其所在子树的叶结点的路径上，黑色结点的数量都相同。
“左根右，根叶黑。不红红，黑路同。”

② 优势：红黑树不再关注树的高度，而是关注 “不红红，黑路同” 这两个特性，使得插入删除时调整的频率降低，整体效率提升；

③ 劣势：红黑树依然是一颗二叉树，当数据量巨大时，每一层存储的结点数量非常有限，树的高度会非常大。

（4）B树

B树，全称为 多路平衡查找树 。

① 特点1：m阶B树的每个结点至多拥有m颗子树，并且每个结点至多拥有m-1个关键字，解决了二叉树做索引的痛点；

② 特点2：所有的信息都存储在非叶子结点（内部结点），叶子结点（外部结点）不带信息，仅作为搜索失败的标志。

③ 优势：B树依然采用“左小右大”的搜索树设计原则，但是摒弃“二叉”，转用“多路”，树的高度大大降低，搜索效率提升。

（5）B+树

B+树 是 B树的优化，更适合实现外存储索引结构。MySQL的 InnoDB引擎 就是用 B+树 实现其索引结构的。

① 优化1：m阶B+树的每个结点至多拥有m颗子树，并且每个结点至多拥有m个关键字，数量上比B树多一个。

② 优化2（关键）：所有的信息都存储在叶子结点，非叶子结点只作指针，起引导作用，那么意味着：搜索路径上的非叶子结点时，不需要加载数据（B树在找到目标之前，路径上的非叶子结点数据会一同加载），读取次数降低，速度更快。

③ B+树 优势总结：
i. 仅叶子结点存储数据，磁盘读写代价更低，速度更快；
ii. 所有的搜索路径都是 从根节点到叶子结点，查找效率更稳定；
iii. B+树的叶子结点用链表相连，支持顺序查找，便于扫库与区间查找。

二. 聚簇索引（物理层面分类）

想要全面的了解 聚簇索引，就要一同了解 非聚簇索引 以及 回表查询 的相关内容。

1. 聚簇索引

聚簇索引 又称 聚集索引。

• 数据 与 索引 一同存储，索引的叶子结点保存完整的行数据。
• 数量：每张数据表 有且仅有一个（= 1）。
• 优先级：主键 → 第一个唯一键 → InnoDB自动生成rowid作隐藏的聚簇索引。

2. 非聚簇索引

非聚簇索引 又称 二级索引。

• 数据 与 索引 分开存储，索引的叶子结点仅关联对应的主键。
• 数量：每张数据表 允许存在多个（0 ~ n）。

3. 回表查询

执行sql语句时，首先在 普通索引 中找不到所需的 完整信息 ，迫不得已要执行回表查询，再回到聚集索引中查询数据，从而执行了两次B+树查询。

以上图数据举例，假设执行下面的sql语句：

select * from user where name = 'Arm';

那么执行过程如下：

我们意图查询 name = ‘Arm’ 的 user 的所有信息；
但是，在上面这种简单的非聚簇索引中无法实现，
所以，只能拿到主键后，返回聚集索引重新查询。

三. 覆盖索引（设计层面优化）

1. 概念

我们首先讨论看一个“有趣”的问题：覆盖索引到底是不是索引？

我们看一看国外专注于MySQL相关问题的 PlanetScale平台 怎么解释这一问题。（有条件的同学可以直接看原网站，里面有完整的视频与文字讲解 ——参考资料）

Covering indexes are not a separate type of index in MySQL, but rather a special situation in which an index covers the entire set of requirements for a single query.
覆盖索引在MySQL中并不是一种单独的索引类型，而是索引覆盖单个查询的全部需求集的一种特殊情况。

A covering index is a regular index that provides all the data required for a query without having to access the actual table. When a query is executed, the database looks for the required data in the index tree, retrieves it, and returns the result. This eliminates the need for the engine to access the actual table, saving a secondary traversal to gather the rest of the data.
覆盖索引是一种常规索引，它提供查询所需的所有数据，而不必访问实际的表。当执行查询时，数据库在索引树中查找所需的数据，检索它，并返回结果。这消除了引擎访问实际表的需要，节省了收集其余数据的二次遍历。

For an index to be considered a covering index, it must have all the data needed for a particular query. This includes the columns being selected, the columns being filtered on, and the columns being used for sorting. If an index satisfies all of these requirements, it is said to be a “covering index” for that query.
要将索引视为覆盖索引，它必须具有特定查询所需的所有数据。 这包括被选择的列、被过滤的列以及用于排序的列。如果一个索引满足所有这些要求，它就被称为该查询的“覆盖索引”。

总结一下：

① 覆盖索引 是 索引。

在一条查询语句中，当该索引包含了所需的所有数据，那么它就是覆盖索引，它是该查询的覆盖索引。

② 覆盖索引 不是 索引类型。

索引类型可以按照不同的维度进行划分。例如：从逻辑层面可分为主键索引、唯一索引、普通索引、联合索引、空间索引；物理实现层面可分为聚簇索引、二级索引…

当一个索引树生成之后，它的类型应该是确定的、普适的。例如：该索引树的叶子结点包含了完整行信息，那么他确定为聚簇索引；为“非空不重复”的主键字段创建的索引，就是主键索引…

而一个索引是不是覆盖索引，是由具体的sql查询语句确定的。 若不存在sql语句，则无法判断一个索引是不是覆盖索引。所以我们不应该把覆盖索引当作索引的一个类型来看待，它依赖于具体的情况。


⭐ 覆盖索引的具体概念：
覆盖索引 是 一个包含了特定查询所需的所有数据的索引。

2. 使用场景

（1）回表查询

在使用sql查询语句时，我们尽量使用覆盖索引，精简查询的目标字段，这样可以避免回表查询，提升查询的效率。

注意：在MySQL的InnoDB引擎中，使用主键索引的sql查询语句，基本上可以确定为覆盖查询，但是这并不确保其他的数据库引擎也具有相同特性。

（2）深度分页

当执行 limit偏移量非常大 的分页查询语句时，数据库需要花费大量的时间和资源 扫描并跳过 前面的数据，最后返回10条数据，这样的效率会很低。

其中一类常见的的优化方案是：子查询 + 覆盖索引。

下面提供两种sql写法：

# 普通的深度分页
select * from big_tb limit 5000000, 10;

# 子查询 + 覆盖索引（字段id 索引树扫描）+ 主键索引（t.id）
select * from big_tb t, (select id from big_tb limit 5000000, 10) a where t.id = a.id;

# 子查询 + 覆盖索引（字段id 索引树扫描）+ 范围查询
select * from big_tb where id >= (select id from big_tb limit 5000000, 1) limit 10;

通过以上的案例，我们可以总结出一些有价值 “深度分页问题”优化经验：

• 普通深度分页查询 使用的是全盘扫描（type = ALL），毫无疑问是效率最低的。
• 子查询的作用是：付出较少代价，锁定目标分页位置。
• 覆盖索引体现在：子查询里面的sql在查询的时候，扫描字段id的索引树；虽然索引树扫描是低效的索引使用方式，但是这已经比直接全盘扫描好太多。
• 字段数量越多的表单，优化效果越明显；所以任何时候都要精简查询字段，避免大量使用 select *。

上述示例直接使用了字段id的主键索引，在MySQL中这必定有覆盖查询的效果。

下面再给出一个常见案例，体会覆盖索引在优化过程中起到的作用。
参考链接——Mysql千万级大表进行深度分页优化

# 子查询 + 覆盖索引（字段create_time 索引树扫描）+ 主键索引（t.id）
select * from t_order t INNER JOIN (
select id from t_order order by create_time DESC limit 1000000, 100
) tmp ON t.id = tmp.id order by create_time DESC;

说明：id为主键，create_time为datetime类型，并已经创建非空普通索引。

该例采用了 INNER JOIN…ON 内连接语法。子查询中的sql会扫描字段create_time 的 二级索引树，但目标是主键id的值，满足覆盖查询，无需回表。

并且本次查询对create_time字段有排序要求，order by 关键字也可以使用到create_time的索引树进行排序，提升排序的效率。

想让深度分页查询有质的提升，应该考虑 Elasticsearch（ES）、加缓存、数据分区分片（分库分表） 等方式，过大的表单数据量 总是会导致效率低下。
👉 【面试资料】MySQL篇 之 分库分表

四. 索引创建

下面介绍几种 适合创建索引的情况 ，即索引创建的原则。

提示：其中1.表单与2.字段的基本原则、5.联合索引、6.索引数量要重点掌握，可以做深度理解。

1. 表单原则

表单的数据量大，并且表单查询较频繁。单表超过10万数据，即可考虑添加索引，增加用户体验。

2. 字段原则

常用于作查询条件（where）、排序（order by）、分组（group by）操作的字段适合建立索引。因为只有通过以上的操作才能体现出索引真正的意义——加快查询速度。

3. 字段区分度

尽量选择区分度高的字段添加索引，尽量添加唯一索引，不重复的数据区分度最高，适用索引的效率也最高。

例如：字段 name 与 address 相比，name 的区分度肯定更高，仅存在少量重复；而来自同一个地方的人可能会有很多。所以我们更倾向为 name 添加索引。

4. 字段长度

如果添加索引的字段是字符串类型，并且长度较长，可以针对字段的特点，建立前缀索引——截取长字符串的前几个字符来建立索引，否则索引的存储压力会非常大。

5. 联合索引

尽量选择建立联合索引，减少单列索引。

联合索引（又称 复合索引）能够有针对性地选择常用的字段进行组合，所以在查询时，联合索引经常可以作为覆盖索引，节省存储空间，避免回表，提升查找效率。

⭐推荐一篇联合索引的 学习参考资料 ，里面详细的介绍了联合索引的概念、原理、使用方法以及常见错误，写的非常棒！

6. 索引数量

控制索引数量，索引不是多多益善。索引越多，维护索引数据结构的代价越大，直接影响数据库增删改的效率。

7. NOT NULL

如果在业务逻辑中，我们确定某个字段 / 索引不能存储NULL值，则在创建表的时候应使用NOT NULL去约束它。

因为数据库通常都有优化器，当优化器知道每列是否包含NULL值时，它可以自主选择更高效的索引去完成查询。

与以上索引创建原则相对应的，我们可以总结出几个不适合创建索引的情况：

① 表单数据量过小；

② where等关键字用不到的字段；

③ 数据重复的字段，例如：男1 女0；

④ 经常 增删改 的表单与字段不适合建立索引，索引频繁的更新维护会增加IO负担。

五. 索引失效

下面介绍几种会导致索引失效的常见情况，在设计索引、使用索引的时候一定要避免。

我们常用 EXPLAIN关键字 检查索引是否失效，具体使用方法详见【面试资料】MySQL篇 之 慢查询

1. 联合索引

联合索引的不规范使用可能会导致索引失效，详情可以参考【四. 索引创建 → 5. 联合索引】中提供的链接文章。此处做简单的总结归纳。

（1）最左前缀

# 联合索引创建示例
create  index  indexName  on  tableName (a,b(length),c)

① 在使用关键字where时，查询条件中索引的组合只有三种情况：(a)、(a,b)、(a,b,c)。若组合不规范，会导致索引失效。

② 在配合order by使用时，先按照 “最左前缀” 单独清算where中的索引命中情况，然后再判断 order by 是否命中。

• 若想 “order by a” 生效，不需要额外条件；
• 若想 “order by b” 生效，where中必须 命中索引a 且 不使用范围查询；（或where中出现过 b = …）
• 若想 “order by c” 生效，where中必须 命中索引a和b 且 不使用范围查询。（或where中出现过 c = …）

典例：

# 正确，索引a、b、c全部生效
select * from mytable where  c=4 and b=6 and a=3;

# 错误1，索引c失效
select * from mytable where a=3 and c=7;

# 错误2，索引b、c皆失效
select * from mytable where b=3 and c=4;

# 正确，索引a、b全部生效
select * from mytable where a=3 order by b;

# 错误3，索引c失效，Extra 出现 using filesort -> 无法按照索引c排序
select * from mytable where a=3 order by c;

# 错误4，索引b失效，Extra 未出现 using filesort -> 可以按照索引a排序
select * from mytable where b=3 order by a;

（2）范围查询

按照本人通俗的理解，联合索引有 “整体性” ，即在一条sql语句中，如果 联合索引(a,b,c) 中的某个索引使用了范围查询，那么其右侧的所有索引 在where的任何位置 都不会再生效。

典例：

# 索引a、b生效
SELECT * FROM mytable WHERE a = 3 and b > 3;
SELECT * FROM mytable WHERE a = 3 and b > 3 ORDER BY b;

# 索引a、b生效，c失效
SELECT * FROM mytable WHERE a = 3 and b > 3 ORDER BY c;
SELECT * FROM mytable WHERE a = 3 and b > 3 and c = 3;

# where中，索引a、b生效，c失效；但 Extra 未出现 using filesort -> 可以按照索引c排序
SELECT * FROM mytable WHERE a = 3 and b > 3 and c = 3 ORDER BY c;

# where中，索引a、b生效，c失效；但 Extra 出现 using filesort -> 无法按照索引c排序
SELECT * FROM mytable WHERE a = 3 and b > 3 and c > 3 ORDER BY c;

2. 运算操作

不要在索引列上进行任何形式的运算操作，否则索引将失效，包括：四则运算、字符串截取等等…

典例：

# type = const，命中主键索引
select * from composite_index where id = 2;

# type = index，未命中索引
select * from composite_index where id % 2 = 0;

3. 隐式类型转换

错误高发情况：字段A的数据类型为varchar，并添加索引；A存储的是数字字符串（0、1、2…），而sql直接把A当作整型处理…此过程不会报错，但是内部发生了 varchar转int 的隐式类型转换，这会导致索引失效。

4. 模糊查询

以 %(0~n个字符) 或 _(1个字符) 开头的Like模糊查询，会造成索引失效。

六. sql优化

我们应该从多方面考虑sql优化的问题，从多角度指定优化方案。下面给出5种常见的优化策略，抓住一个核心：提升效率！

1. 数据表

参考书目：阿里开发手册《嵩山版》

① 设置合适的数值类型：tinyint、int、bigint，节约存储空间；

② 设置合适的字符串类型：char定长效率高，varchar变长效率低；

…

2. 索引

【面试资料】MySQL篇 之 慢查询 和 本文前面的内容都涉及到了索引优化的问题。此处仅给出一些面试的回答思路，不做详细阐述。

答出5~6条，确保够用！

（1）索引创建阶段

① 大数据表设计索引：发挥索引优势；

② 常用字段添加索引：条件查询、排序、分组…

③ 尽量保证索引的唯一性（区分度）与非空性（not null）：提升查询效率；

④ 设计联合索引：可以在查询中做覆盖索引；

⑤ 适量创建索引：控制维护代价；

⑥ 考虑前缀索引：减小索引的存储压力。

（2）索引使用阶段

① 避免索引失效：规范查询逻辑——模糊、联合（最左 + 范围）…

② 注意索引类型（type）：使用高效索引；

③ 使用覆盖索引：“一次查完”，避免回表；

3. sql语句

① 避免直接使用 【select *】，务必说明字段名称；

② 避免索引失效的写法，例如：where中的表达式操作；

③ 尽量使用 union all 代替 union，因为 union 会多过滤一次，效率低；

select * from user where id > 2
union all | union
select * from user where id < 5
说明：
union all 会完整的把两句的搜索结果拼接起来
union 不仅会拼接结果，还多一步【去重】操作

④ 尽量用 内连接 inner join 代替 left join / right join，若必须使用则以小表为驱动。内连接会对两个表进行优化——小表在外、大表在内，连接次数 = 外侧表（小表）数据量；左/右连接位置固定，不会重新调整顺序。

• left join：左表是驱动表，右表是被驱动表（left是左驱动）；
• right join：右表是驱动表，左表是被驱动表（right是右驱动）。

# 正确写法（小表A 大表B）
select * from A a left join B b on a.code = b.code;
select * from B b right join A a on a.code = b.code;

4. 主从复制、读写分离

当数据库的使用场景是 “读多写少” 时，我们就应该注意不应该让写操作 影响 大量的读操作，避免造成性能影响。

读写分离主要用于解决读写压力不均衡的问题，提高系统的读取性能

• 读写分离：解决 “数据库 写入影响查询效率” 的问题；
• 主从复制：解决 “数据库 内部数据一致性” 的问题。

👉 我们再次复习 Redis确保“双写一致”的方案 —— 异步通知（弱一致）

（1）数据库 读写分离：
① 【DB】=【Master】+【Slave】
② 【Master：接收写操作】→（同步数据）→【Slave：接收读操作】；

（2）缓存 双写一致：
① 【项目服务】=【DB服务】+【Cache服务】+…
② （新数据）→【DB服务】→（MQ）→【Cache服务】→（缓存更新）

⭐图（1）的业务流程 只是 图（2）item-service 中的 一个环节！

Master主库 完成写操作之后：
① 向 Slave从库 同步数据；
② 向 MQ 发送消息（向 Cache缓存 同步数据）。

关于【MySQL主从分离、主从同步】的流程与原理，请进一步阅读 【面试资料】MySQL篇 之 主从同步原理 的内容。

5. 分库分表

分库分表 是将单个大型数据库表按照一定的规则拆分成多个小表，或者将整个数据库按照一定的规则拆分成多个数据库实例，每个实例包含一部分数据。

分库分表主要用于解决

• 单表数据量过大
• 单库性能瓶颈

等问题，可以有效地分解数据库的存储压力，提高系统的扩展性和性能。

分库分表的内容较多，拥有较独立的知识体系，所以我们将另开新篇去介绍这部分内容。请继续关注 【面试资料】MySQL篇 的后续内容！

👉 【面试资料】MySQL篇 之 分库分表