Mysql索引（一）_索引实际过程中每页快速定位记录-CSDN博客

本文链接：https://blog.csdn.net/javaJasonjava/article/details/144165626

概述

索引是存储引擎用于快速找到记录的一种数据结构。

举个例子：如果查找一本书中的某个特定主题，一般会先看书的目录（类似索引），找到对应页面。在MySQL，存储引擎采用类似的方法使用索引，高效获取查找的数据。

索引的分类

从存储结构上来划分
- Btree索引（B+tree，B-tree)
- hash哈希索引
- full-index全文索引
从应用层次上来划分
- 普通索引：一个索引只包含单个列，一个表可以有多个普通索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：一个索引包含多个列
从表记录的排列顺序和索引的排列顺序是否一致来划分
- 聚簇索引（主键）：表记录的排列顺序和索引的排列顺序一致。
- 非聚簇索引：表记录的排列顺序和索引的排列顺序不一致。
索引的原理
索引一般以文件形式存在磁盘中（也可以存于内存中），存储的索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全表扫描，有多少条数据
就进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的key值放在一个n叉树（BTree）中。因为BTree的特点就是
在磁盘等直接存储设备上进行动态查找，每次以索引进行条件查询时都会根据key值去树上直接搜索。
MySQL索引设计的核心目标是有效平衡数据检索的速度与存储效率。它使数据库能迅速定位数据，但数据库的挑战更为复杂因为需要处理各种查询类型，如等值查询、范围查询和
模糊查询等。因此MySQL通过InnoDB 存储引擎采取了以下具体措施提高查询性能，也保证了数据的安全性和一致性：
1. B+ 树索引结构：
  - 高效范围查询：B+树数据结构的平衡树特性保证了即使在大量数据中也能保持较低的查询深度，特别是对于范围查询，可以快速通过叶节点链表遍历相关数据
  - 优化读写性能：B+树的结构减少了节点分裂的频率，保持了树的平衡，从而提高了读写操作的效率
2. 聚簇索引设计：
  - 直接存储数据：使用聚簇索引，其中表数据直接存储在索引的叶节点上。这意味着数据物理顺序与键值顺序一致，优化了顺序访问的性能
  - 减少I/O操作：通过聚簇索引，查询主键时直接定位到数据，无需额外的数据指针跳转，从而减少了磁盘I/O操作
3. 数据页及预读机制：
  - 数据页单位操作：InnoDB以数据页为基本的I/O单位（默认 16 KB），这比单条记录的读写更高效，因为一次I/O可以加载多条记录到内存
  - 预读优化：利用操作系统的预读特性，InnoDB预测并提前加载可能访问的数据页到内存，减少了未来的I/O需求，尤其在顺序访问模式下效果显著
4. 自适应哈希索引：
  - 内存级索引加速：当某些数据页被频繁访问时，InnoDB会在内存中自动构建哈希索引来加速这些数据页的访问，进一步减少了数据查找时间
5. 写入缓冲与日志：
  - 写入合并：通过写入前日志（Write-Ahead Logging, WAL）和更改缓冲区（Change Buffer）技术，合并多个写入操作，减少对磁盘的直接写入，优化了写的性能。

索引的适用与不适用场景

索引的适用场景：

经常用于查询的字段
经常用于连接的字段建立索引，可以加快连接的速度
经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

索引的不适用场景：

where 条件中用不到的字段不适合建立索引
表记录较少
需要经常增删改
参与列计算的列不适合建索引
要参与计算的列级区分度不高的列（如性别）

索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能产生影响，高效的索引对获得良好的性能非常重要，设计索引时要考虑以下方面：

索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能，因为当表中的数据更改时索引也会相应的调整和更新
避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引
数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
在条件表达式中区分度高的列上创建索引，区分度低的列上不要建立索引。如果建立了索引不但不会提升效率，反而严重降低数据的更新速度
在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引
最左前缀原则：对于复合索引，确保查询条件能够利用索引的“最左前缀”。这意味着，查询条件应该从复合索引的第一个字段开始匹配，并且按照索引字段的顺序进行
选择性原则：优先为具有高选择性的列创建索引。选择性是指列中唯一值的比例，唯一值越多的列（接近列的总行数），选择性越高，索引效果越好
避免冗余和重复索引：检查并避免创建冗余或重复（一个索引是另一个索引前缀的）索引，因为这会增加额外的维护成本和空间消耗，而不会带来任何查询性能的提升
索引覆盖：如果一个查询可以通过访问索引就能获取所需的全部数据，那么这个索引被称为“覆盖索引”。设计时尽可能让索引覆盖更多查询，可以显著减少对磁盘的访问次数，提高查询效率
使用前缀索引以节约空间：对于长文本字段，可以考虑使用字段的前缀作为索引。前缀索引可以节省索引空间，降低维护成本，但需要权衡前缀长度和查询效率
索引维护：定期维护索引，包括重建或优化索引，以确保索引结构的效率。随着数据的增加和变化，索引可能会变得碎片化

索引的失效

在有些时候因为使用上的一些瑕疵就会导致索引的失效，无法达到我们使用索引的预期效果，下面介绍几种索引失效的场景：

列的对比：例如：某个表中，有两列 id 和 c_id 都建了单独索引，where 条件后为 id = c_id，这种情况会被认为还不如走全表扫描
存在Null值条件：如果索引列是可空的，是不会给其建索引的
存在Not条件：当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描
Like通配符：前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。所以业务设计的时候，尽量考虑模糊搜索更多的使用后置通配符
条件上包括函数：查询条件上尽量不要对索引列使用函数，因为索引在建立时会和计算后可能不同，无法定位到索引
复合索引前导列区分大：当复合索引前导列区分小的时候我们有INDEX SKIP SCAN，当前导列区分度大且查后导列的时候，前导列的分裂会非常耗资源，执行计划想还不如全表扫描来的快，然后就索引失效了
数据类型的转换：当查询条件存在隐式转换时，索引会失效。
使用非最左前缀查询：若索引是多列的（比如(a, b, c)），查询条件不是以索引的最左列开始，则索引可能不会被使用

InnoDB索引实现

InnoDB数据页有7个组成部分，各个数据页可以组成一个双向链表。而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储的记录生成一个页目录。在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

页和记录的关系示意图如下：索引同样存储在数据页中，只不过目录项中的两个列是主键和页号。在这里插入图片描述

那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢？是根据记录头信息里的record_type属性，它的各个取值代表的意思如下：
0：普通的用户记录；1：目录项记录；2：最小记录；3：最大记录

如果我们想根据主键值查找一条用户记录大致需要3个步骤：

确定目录项记录页。
通过目录项记录页确定用户记录真实所在的页。
在真实存储用户记录的页中定位到具体的记录。

聚簇索引

什么是聚簇索引

InnoDB存储引擎表是索引组织表，表中数据按照主键顺序存放。其聚簇索引就是按照每张表的主键顺序构造一颗B+Tree，其叶子节点中存放的就是整张表的行记录数据，这些叶子节点称为数据页。

聚簇索引的存储并不是物理上连续的，而是逻辑上连续的，叶子节点间按照主键顺序排序，通过双向链表连接。多数情况下，查询优化器倾向于采用聚簇索引，因为聚簇索引能在

叶子节点直接找到数据，并且因为定义了数据的逻辑顺序，能特别快的访问针对范围值的查询。

聚簇索引的这个特性决定了索引组织表中的数据也是索引的一部分。由于表里的数据只能按照一颗B+Tree排序，因此一张表只能有一个聚簇索引。

在InnoDB中聚簇索引默认就是主键索引。如果没有主键，则按照下列规则来建聚簇索引：

没有主键时，会用一个非空且唯一的索引列做为主键，成为此表的聚簇索引
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据也会相邻地存放在磁盘上，写入性能较高。如果是uuid等字符串形式，频繁的插入会使InnoDB频繁地移动磁盘块，

写入性能就比较低了。

InnoDB的B+Tree中的每个节点都是一个数据页，结构示意图如下：在这里插入图片描述
以上图为例子，实现快速查找主键为7的记录：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为7，在 [1, 8) 范围之间，所以到页5中查找更详细的目录项
在非叶子节点页5中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于6，所以就到叶子节点页3查找记录；
接着在叶子节点页3中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为7的记录

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

聚簇索引列的选择

因为表的数据都是存放在聚簇索引的叶子节点里，所以InnoDB存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增id列作为聚簇索引的索引键

非聚簇索引和二级索引

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了B+Tree的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。二级索引的B+Tree如下图，数据部分为主键值：在这里插入图片描述

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后需要去聚簇索引中获得数据行，这个过程就叫作回表。也就是说要查两个B+Tree才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作索引覆盖，也就是只需要查一个B+Tree就能找到数据。

其他索引的分类

索引的分类，可以根据角度的不同来分，在前面的内容，我们已经了解到了按 “数据结构（Hash 索引、B+ Tree 索引）” 以及按 “物理存储（聚合索引、二级索引）” 两种角度的索引分类，那么MySQL 中还存在着哪些形式的索引呢？

按字段特性分类

主键索引：主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

唯一索引：唯一索引建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引：普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列

上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

按字段个数分类

单列索引：建立在单列上的索引称为单列索引，比如主键索引

联合索引：建立在多列上的索引称为联合索引

💡 联合索引范围查询：联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中所有字段都进行了索引查询，也就是可能存在部分字段用到了，部分字段没有用到的情况。这种特殊情况就发生在范围查询，联合索引的最左匹配原则会一直向右匹配直到遇到范围查询就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。比如我们配置了一个 A、B、C 三个字段的联合索引，我们用 A、AB、ABC 的方式都是可以走到联合索引的，但如果是 AC、BC、C 的这种情况则不会使用索引。

索引下推

在MySQL5.6之前，当查询使用到联合索引时，MySQL会先根据索引的最左前缀原则，在索引上查找到满足条件的记录的主键或行指针，然后再根据这些主键或行指针到数据表中

查询完整的行记录。之后，MySQL再根据WHERE子句中的其他条件对这些行进行过滤。这种方式可能导致大量的数据行被检索出来，但实际上只有很少的行满足WHERE子句中

的所有条件。为了解决这个问题，MySQL 5.6引入了索引下推优化。

（Index Condition Pushdown, ICP）是MySQL优化查询的一种方式，其核心思想是将原本在服务层（上层）进行的部分过滤操作下推到存储引擎层（下层）执行，从而减少不必要

的数据行检索，提高查询效率。这样在扫描索引时，就可以提前过滤掉不满足条件的索引项，从而减少后续需要访问的数据行数。

具体说就是当MySQL使用ICP时，它会将WHERE子句分为两部分：一部分是只涉及索引列的条件（称为索引条件），另一部分是涉及非索引列的条件（称为表条件）。MySQL会

先将索引条件下推到索引扫描的过程中，然后再根据表条件对结果进行过滤。

没有使用ICP的查询过程

解析查询：MySQL服务器接收到SQL查询后，首先会解析查询，确定需要访问哪些表和索引。
索引查找：服务器根据解析结果，利用存储引擎提供的接口，在索引中查找满足条件的索引项。这个过程中，存储引擎只会根据索引的键值进行查找，不会考虑WHERE子句中的其他条件。
数据行检索：服务器获取到满足索引条件的索引项后，会进一步根据这些索引项中的指针（或主键值）到数据表中检索出完整的行数据。
过滤行数据：服务器在检索出数据行后，会在服务层根据WHERE子句中的其他条件对这些行进行过滤，只保留满足所有条件的行。
返回结果：最后，服务器将过滤后的结果返回给客户端。

使用ICP的查询过程

解析查询：MySQL服务器会首先解析查询，确定需要访问的表和索引。
索引查找与部分过滤：与没有使用ICP不同的是，在使用ICP时，服务器会将WHERE子句中的部分条件（索引条件）下推到存储引擎层。存储引擎在查找索引项的过程中，会同时根据这些下推的条件进行过滤，只返回满足索引条件和部分WHERE条件的索引项。
数据行检索与最终过滤：服务器根据过滤后的索引项检索出数据行，此时的数据行已经大大减少了。然后，服务器会在服务层根据WHERE子句中的剩余条件对这些行进行最终的过滤。
返回结果：服务器将最终过滤后的结果返回给客户端。

💡 通过ICP优化，可以在存储引擎层就过滤掉大量不满足条件的数据行，从而减少了数据行检索的数量和服务层过滤的工作量，提高了查询性能。尤其是在涉及到大量数据行和复杂WHERE条件的情况下，ICP优化的效果更为显著。

如何在执行计划中查看ICP的使用

通过EXPLAIN命令来查看查询的执行计划，判断是否使用了ICP优化。当执行计划中的Extra列显示Using index condition时，表示查询使用了ICP优化

使用限制

复合索引查询：当查询使用到复合索引并且WHERE子句中有非索引列的条件时，ICP能够将索引列的条件下推到索引扫描的过程中，提前过滤不满足条件的索引项。
访问方法限制：

range：当使用范围查询时，ICP可以有效地在索引扫描过程中过滤不满足条件的记录。

ref、eq_ref、ref_or_null：这些访问方法通常涉及到通过索引查找单个或多个匹配的行。在这些情况下，ICP可以帮助减少不必要的行查找。

存储引擎限制：

InnoDB：InnoDB从5.6开始支持ICP，默认就是开启ICP的，想关闭的话可以通过命令SET optimizer_switch = 'index_condition_pushdown=off'

MyISAM：虽然MyISAM不支持事务处理，但它在某些场景下可能因为其高速的读取性能而被使用,MyISAM同样支持ICP

索引类型限制：ICP优化只适用于二级索引（辅助索引）。在InnoDB中，主键索引的叶子节点直接包含行数据，而二级索引的叶子节点包含的是对应主键的值。因此，当使用二级索引进行查询时，MySQL首先查找到主键值，然后再根据主键值去查找实际的行数据。在这个过程中，ICP可以在查找主键值之前就过滤掉不满足条件的索引项，从而提高查询效率。
优化器决策：即使查询满足上述条件，MySQL的优化器也不一定会选择使用ICP。优化器会根据查询成本估算来决定是否使用ICP。如果优化器认为全表扫描或者其他访问方法更快，它可能不会选择ICP。

索引合并

Index Merge Optimization是MySQL查询优化器在处理复杂查询时使用的一种高级技术。当查询的WHERE子句中有多个独立的条件，且每个条件都可以使用不同的索引时，

MySQL会尝试将这些索引合并起来，以提高查询效率。这种优化策略允许数据库在一个查询中同时使用多个索引，从而避免全表扫描或减少需要扫描的数据量。

为什么会有索引合并

因为在某些情况下，单独使用任何一个索引都无法高效地获取到完整的结果集。而通过合并多个索引的扫描结果，我们可以更精确地定位到满足所有条件的记录，从而提高查询效

率。考虑一个场景，你有一个大型的订单库，其中包含商品信息。你可能需要根据商品的名称、价格、分类等多个条件来检索商品。如果只依赖单个字段的索引，那么查询效率可

能会很低，因为你需要扫描大量的不相关记录。通过索引合并，MySQL可以利用多个字段上的索引来加速查询。它首先分别扫描每个索引，获取满足相应条件的记录集，然后再将

这些记录集合并，得到最终的结果。

主要类型

索引合并是MySQL优化复杂查询的一种有效策略，它允许数据库在单个查询中高效地利用多个索引。索引合并主要有三种类型：交集合并（Intersection Merge）、并集合并

（Union Merge）和排序并集合并（Sort-Union Merge），分别适用于不同的查询场景。在实际应用中，最好通过EXPLAIN命令来查看查询的执行计划，并根据实际情况进行调整

和优化。

交集合并（Intersection Merge）：当查询需要满足多个条件（使用 AND 连接），并且每个条件都可以使用不同的索引时，MySQL会分别扫描这些索引，然后取结果的交集。

并集合并（Union Merge）：在某些情况下，查询可能只需要满足多个条件中的任意一个（使用 OR 连接）。MySQL会分别扫描这些索引，然后取结果的并集。

排序并集合并（Sort-Union Merge）：这是一种特殊情况，主要发生在需要对结果进行排序，并且排序的字段也有索引时。MySQL会分别扫描索引，然后合并并排序结果。实际

上，MySQL的Index Merge策略并不直接支持排序并集合并。如果优化器决定使用索引合并，它可能会先执行交集或并集合并，然后再对结果进行排序。这里提到的“排序并集合并”

更多是为了理论上的完整性，实际执行计划可能会有所不同。

工作原理流程

条件分析：MySQL 优化器首先分析查询的WHERE子句，确定其中有多少个独立的条件。
索引选择：对于WHERE子句中的每个独立条件，优化器检查是否存在可用的索引。如果存在，它会评估使用这些索引的成本。
索引扫描：优化器决定使用哪些索引后，它会分别对这些索引进行扫描，获取满足每个条件的记录集。
结果合并：扫描完所有选定的索引后，MySQL将这些记录集合并，以产生最终的结果集。合并的方式可以是交集、并集或排序并集，具体取决于查询的条件和所需的结果。
返回结果：最终优化器将合并后的结果集返回给客户端。

应用场景

多条件查询：当查询的WHERE子句中包含多个独立的条件时，每个条件都可以利用不同的索引。
复合索引不完全匹配：即使你有一个复合索引（例如，INDEX(col1, col2)），但查询条件只涉及col1或col2时，MySQL可能会选择使用单列索引，并通过索引合并来优化查询。
排序和限制：当查询需要排序或限制结果集大小时，索引合并可以帮助快速定位到满足条件的记录。

如何在执行计划中查看IMO的使用

使用EXPLAIN语句可以查看查询的执行计划，如果type列显示为 index_merge，则说明MySQL使用了Index Merge Optimization。

使用限制

在早期版本的MySQL中（特别是 5.6.7 之前），使用Index Merge Optimization有一个重要的前提，没有范围查询条件可以使用。这个限制在后续版本中得到了放宽。
优化器基于统计信息和成本估算来决定是否使用Index Merge Optimization。如果优化器认为其他访问方法更高效，它可能会选择不使用索引合并。
索引合并主要适用于SELECT查询。对于INSERT、UPDATE和DELETE操作，索引合并通常不适用。
不是所有类型的索引都可以参与索引合并。通常，B-tree索引是参与索引合并的主要类型。
索引合并最适用于WHERE子句中有多个独立条件的查询。这些条件应该能够分别使用不同的索引。
在某些情况下，如果存在范围查询（如BETWEEN、<、>等）或ORDER BY子句，MySQL可能不会使用索引合并，而是选择使用单个索引或进行全表扫描。特别是当范围查询与索引合并不兼容时，优化器可能会放弃使用索引合并。
如果某个索引的选择性很差（即该索引列中有大量重复值），则优化器可能不会选择该索引进行合并，因为它认为这样做不够高效。
MySQL优化器会根据统计信息、系统配置（如index_merge相关配置）以及查询的具体内容来决定是否使用索引合并。在某些情况下，即使满足了上述条件，优化器也可能选择不使用索引合并，因为它认为有更高效的执行计划。