MySQL 8 索引原理详细分析-阿里云开发者社区

千山万水总是情，

问问索引行不行？

轻舟已过万重山，

有种尽管来发难。

索引是在数据库优化时的重要手段之一，今天 V 哥从索引的角度展开讲一讲索引的各个要点，希望可以通过这篇文章，帮助大家彻底搞透索引的关键点。

1.索引的定义与作用

2.索引的类型

3.索引原理

4.二分查找法

5.Hash结构

6.B+Tree 结构

7.聚簇索引和辅助索引

8.索引分析与优化

9.查询优化

10.索引的优势与劣势

1.索引的定义与作用

索引是数据库中的一种数据结构，它允许数据库管理系统（DBMS）快速检索表中的数据。索引的主要目的是提高查询效率，它通过提供一个快速查找机制来减少数据检索所需的时间。没有索引的数据库表被称为堆表，其查询效率通常较低，尤其是在处理大量数据时。

2.索引的类型

普通索引：基本的索引类型，无特殊限制。

唯一索引：索引字段值必须唯一，允许有空值。

主键索引：特殊的唯一索引，不允许空值。

复合索引：在多个列上建立的索引。

全文索引：适用于文本数据的搜索，支持自然语言查询。

3.索引原理

索引是存储引擎用于快速查找记录的数据结构，存储在数据文件中。
索引可以加快数据检索速度，但会降低增删改操作速度。
索引涉及的理论知识包括二分查找法、Hash 结构和 B+Tree 结构。

当执行查询时，MySQL首先检查是否可以使用索引来加快查询速度。如果可以，MySQL会使用索引来定位数据。索引查找的过程通常如下：

初始化：从根节点开始，根节点包含整个表的索引信息。
遍历：根据查询条件，沿着树向下遍历到相应的叶子节点。
查找：在叶子节点上进行查找，找到匹配的键值。
回表（对于辅助索引）：如果使用的是辅助索引，需要根据叶子节点中的指针回聚簇索引中查找完整的数据记录。

4. 二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

首先定位left和right两个指针
计算(left+right)/2
判断除2后索引位置值与目标值的大小比对
索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：

第一次查找

第二次查找

第三次查找

第四次查找

5. Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。
Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引、InnoDB 自适应哈希索引。
InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。
InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。
InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。
自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

6.B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

1. B-Tree结构

索引值和data数据分布在整棵树结构中。
每个节点可以存放多个索引值及对应的data数据。
树节点中的多个索引值从左到右升序排列。

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

2. B+Tree结构

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。
叶子节点包含了所有的索引值和data数据。
叶子节点用指针连接，提高区间的访问性能。

MySQL中的索引主要基于B+Tree数据结构。B+Tree是一种自平衡的树，它保持数据有序，允许在对数时间内进行搜索、顺序访问、插入和删除。B+Tree的特点是：

节点：B+Tree的内部节点不存储数据，只存储键值（索引），而数据存储在叶子节点。
叶子节点：叶子节点包含了实际的数据记录，以及指向下一个叶子节点的指针，形成一个链表结构，便于范围查询。
分裂与合并：当一个节点中的键值超过一定数量时，会进行分裂；当节点中的键值数量太少时，会与相邻节点合并。

7.聚簇索引和辅助索引

B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

聚簇索引：InnoDB 中的主键索引，数据按主键顺序存储。
辅助索引：InnoDB 中的非主键索引，叶子节点存储主键值和索引字段值。

聚簇索引（Clustered Index）和辅助索引（Secondary Index）是数据库索引的两种主要类型，它们在MySQL中扮演着至关重要的角色，尤其是在InnoDB存储引擎中。以下是对这两种索引的详细解释：

7.1 聚簇索引（Clustered Index）

定义：聚簇索引是一种将数据行的存储位置与索引的逻辑顺序结合在一起的索引。在InnoDB存储引擎中，聚簇索引决定了表数据的物理存储顺序。每个表只能有一个聚簇索引。
特点：数据行的存储顺序与聚簇索引的键值顺序相同。聚簇索引通常是基于表的主键创建的。聚簇索引的叶子节点直接包含了完整的数据记录，不需要额外的查找步骤。
工作原理：当执行查询时，如果查询条件与聚簇索引的键值匹配，数据库可以直接在聚簇索引中找到数据，无需进行额外的查找操作。由于数据行与索引是一起存储的，聚簇索引对于范围查询和顺序访问非常高效。
优势：提供了数据的有序存储，有助于优化范围查询和顺序访问。减少了磁盘I/O操作，因为相关数据往往相邻存储。
劣势：插入、删除和更新操作可能导致聚簇索引的页面分裂，影响性能。聚簇索引的顺序存储限制了数据的物理存储位置，可能影响数据的随机访问性能。

7.2 辅助索引（Secondary Index）

定义：辅助索引是一种独立的索引，它不直接存储数据行，而是存储索引键值和指向数据行的指针（通常是主键）。每个表可以有多个辅助索引。
特点：辅助索引的叶子节点存储的是主键值和关联的数据行指针。辅助索引通常基于表的非主键列创建。辅助索引允许在非主键列上进行快速查找。
工作原理：当执行查询时，如果查询条件与辅助索引的键值匹配，数据库首先在辅助索引中找到主键值，然后使用这个主键值去聚簇索引中查找完整的数据记录。这个过程称为“回表”，因为需要从辅助索引跳转到聚簇索引。
优势：允许在非主键列上进行快速查询。可以有多个辅助索引，为不同的查询模式提供优化。
劣势：需要额外的“回表”操作来获取完整的数据记录，可能会增加查询的复杂性和开销。如果主键索引发生变化，辅助索引也需要更新，增加了维护成本。

小结：聚簇索引和辅助索引在MySQL中共同工作，以提高数据检索的效率。聚簇索引提供了数据的有序存储和快速范围查询能力，而辅助索引则允许在非主键列上进行快速查找。在设计数据库索引时，需要根据实际的查询需求和数据访问模式来合理选择和使用这两种索引。

8. 索引分析与优化

8.1 EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

select_type

表示查询的类型。常用的值如下：

SIMPLE ：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询
EXPLAIN SELECT * from user WHERE id < 3;
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快。

possible_keys

表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

key：表示查询时真正使用到的索引，显示的是索引名称。
rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。key_len的计算规则如下：

字符串类型

字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n)：n*字符集长度

varchar(n)：n * 字符集长度 + 2字节

数值类型

TINYINT：1个字节

SMALLINT：2个字节

MEDIUMINT：3个字节

INT、FLOAT：4个字节

BIGINT、DOUBLE：8个字节

时间类型

DATE：3个字节

TIMESTAMP：4个字节

DATETIME：8个字节

字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using where表示查询需要通过索引回表查询数据。

Using index表示查询需要通过索引，索引就可以满足所需数据。

Using filesort表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。

Using temprorary查询使用到了临时表，一般出现于去重、分组等操作。

如果type显示为ALL，意味着进行了全表扫描，这通常不是期望的结果。
如果type显示为ref或eq_ref，表示查询使用了非唯一索引或唯一索引进行查找。
如果Extra中包含Using index，表示查询可以直接从索引中获取结果，无需回表。

8.2 回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚簇索引查询记录信息。

8.3 覆盖索引

在SQL-Server官网的介绍如下：

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

8.4 最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

8.5 LIKE查询

面试题：MySQL在使用like模糊查询时，索引能不能起作用？

回答：MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name like '%o%'; //不起作用 select * from user where name like 'o%'; //起作用 select * from user where name like '%o'; //不起作用

8.6 NULL查询

面试题：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节。

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如'1970-01-01 00:00:00'。

8.7 索引优化

索引优化的目标是确保查询尽可能高效地执行，同时减少对数据库性能的影响。以下是一些常见的索引优化策略：

创建合适的索引:

根据查询模式创建索引，特别是针对经常作为查询条件的列。
考虑创建复合索引（多列索引）以优化多列查询。

避免不必要的索引:

过多的索引会增加写操作的开销，因为每次插入、更新或删除数据时，所有相关索引都需要更新。
定期审查和删除不再使用的索引。

优化现有索引:

考虑索引的顺序，特别是在复合索引中，最左侧的列应该是查询中最常用的过滤条件。
对于经常进行范围查询的列，避免将其作为复合索引的最左侧列。

使用覆盖索引:

覆盖索引是指查询所需的所有数据都可以从索引中获取，无需访问数据表本身。
通过包含所有查询所需的列，可以减少回表操作，提高查询效率。

优化查询语句:

确保查询语句尽可能简洁，避免不必要的计算和条件。
使用EXPLAIN分析查询，根据结果调整查询逻辑。

考虑数据类型和索引选择:

选择合适的数据类型可以减少索引大小，提高索引效率。
避免在索引列上使用函数或表达式，因为这可能导致索引失效。

处理大数据量的表:

对于大表，考虑分批处理或使用分区技术来优化查询性能。

通过以上策略，可以有效地分析和优化MySQL中的索引，从而提升查询性能和数据库的整体效率。索引分析与优化是一个持续的过程，需要根据系统的实际运行情况不断调整和改进。

9. 查询优化

查询优化是数据库管理中的一个重要方面，它涉及到确保数据库查询尽可能高效地执行，以减少响应时间和系统资源的使用。以下是对查询优化内容的详细分析和解释：

9.1 慢查询定位

慢查询是指那些执行时间超过特定阈值的SQL查询。MySQL通过慢查询日志（Slow Query Log）来记录这些执行时间较长的查询，这对于识别和优化低效的SQL语句非常有用。

慢查询日志的启用：

通过设置slow_query_log系统变量为ON来启用慢查询日志。
可以通过long_query_time变量设置记录慢查询的阈值，默认值为10秒。

分析慢查询日志：

使用SHOW VARIABLES LIKE 'slow_query_log'查看慢查询日志的文件位置。
使用mysqldumpslow工具或其他第三方工具（如Percona Toolkit）分析慢查询日志，找出常见的慢查询模式。

9.2 慢查询优化

一旦识别出慢查询，就可以采取以下措施进行优化：

索引优化：

为频繁作为查询条件的列添加索引。
优化复合索引，确保查询条件与索引的最左前缀匹配。
移除不必要的索引，减少更新和维护的开销。

查询重写：

简化查询逻辑，避免复杂的子查询和连接操作。
使用更有效的查询语法，例如，使用JOIN代替子查询。

使用覆盖索引：

确保查询只访问索引中的数据，避免额外的磁盘I/O操作。

避免全表扫描：

通过适当的WHERE子句条件限制返回的行数。
使用LIMIT子句来限制查询结果的数量。

优化数据类型和比较操作：

使用适当的数据类型以减少数据转换的开销。
避免在索引列上使用函数或计算，这可能导致索引失效。

使用查询缓存：

对于重复执行且结果不经常变化的查询，可以利用查询缓存来提高性能。

9.3 分页查询优化

分页查询是数据库操作中常见的一种场景，尤其是在Web应用中。优化分页查询可以显著提高用户体验和系统性能。

使用覆盖索引：

为分页查询的排序字段创建索引，确保查询效率。
使用复合索引来包含排序和过滤字段。

避免深度分页问题：

对于深度分页（如查询第1000页），可以使用基于主键的范围查询，或者使用游标来减少数据扫描量。

使用延迟加载：

对于分页查询中的数据，可以使用延迟加载技术，只在需要时才加载相关数据。

优化LIMIT子句：

通过调整LIMIT子句的偏移量和数量，减少不必要的数据读取。

通过上述优化策略，可以显著提高查询性能，减少数据库的负载，并提升用户满意度。查询优化是一个持续的过程，需要根据系统的实际运行情况和查询模式不断进行调整和改进。

10. 索引的优势与劣势

优势：

加快查询速度：索引可以显著提高查询效率，特别是对于大量数据的表。
维护数据顺序：聚簇索引可以维护数据的物理存储顺序，有助于优化范围查询。

劣势：

额外开销：索引需要额外的存储空间，并且在插入、删除或更新数据时，索引也需要维护，这会增加开销。
性能权衡：虽然索引可以加快查询速度，但如果不当使用（如过多的索引或不恰当的索引设计），可能会降低写操作的性能。

通过以上分析，我们可以看到索引在MySQL数据库中的重要性，以及如何通过合理的索引设计和查询优化来提升数据库的性能。索引的选择和使用直接影响到数据库的查询效率和整体性能。

最后

了解索引的详细原则，不仅有助于优化，能把索引搞清楚的，面试中优势也会很突显。

关于数据库优化的话题，V哥觉得还有很多地方可以聊，如果你有兴趣，欢迎关注一起讨论。