theme: orange

👩🏽‍💻个人主页：阿木木AEcru

🔥 系列专栏：《Docker容器化部署系列》 《Java每日面筋》

💹每一次技术突破，都是对自我能力的挑战和超越。

一、什么是MySQL索引？

想象一下，你正在图书馆找一本特定的书。如果没有索引，你需要走过每一个书架，查看每一本书的标题，这会非常耗时。但如果有一个索引卡片，告诉你每本书的位置，你就可以直接走到那本书所在的书架，快速找到你想要的书。在MySQL数据库中，索引就类似于这个索引卡片，它帮助数据库快速定位到存储在表中的数据。

索引的好处

• 快速查找：就像索引卡片帮助快速找到图书馆的书一样，数据库索引可以加快查找数据的速度。
• 数据排序：索引可以帮助数据按照一定的顺序排列，这样当你需要按顺序查看数据时，数据库就可以更高效地提供。
• 提高效率：在执行数据库查询时，索引可以让数据库系统更快地完成任务，提高整体的工作效率。

索引的坏处

• 创建索引就像建立图书馆的索引卡片系统，需要额外的空间和资源。在数据库中，这意味着需要更多的存储空间和时间来维护索引。
• 当你在图书馆中添加或移除书籍时，索引卡片也需要更新。同样，在数据库中，当你添加、修改或删除数据时，索引也需要更新，这会增加额外的工作。
• 对于一些小的表或者不常被查询的表，索引可能不会带来太大帮助，有时候甚至可能因为维护索引而降低性能。

二、索引为什么会快？

1. 高效的数据结构:

索引使用的数据结构（如B+ree）允许快速地在磁盘上存储和检索数据。这种结构支持快速的插入、删除和查找操作，因为它总是保持平衡，确保任何数据的查找路径长度都大致相同。

这就像是拥有一个详尽的目录，可以迅速定位到书籍在图书馆中的位置，而不需要逐个书架查找。

2. 减少全表扫描:

当没有索引时，数据库必须执行全表扫描来查找满足查询条件的行，这称为表扫描。全表扫描需要逐行读取整个表的数据，对于大型表来说非常耗时。有了索引，数据库可以快速定位到相关的数据行，大大减少了需要读取的数据量。

例如，如果你有一个包含数百万行的订单表，并且根据订单日期进行查询，那么在订单日期列上创建索引将大大减少查询时间，因为数据库可以直接跳到相关日期的数据，而不是扫描所有行。

3. 磁盘I/O优化:

索引文件通常比实际的数据文件小，因为它们只包含关键信息和指向数据的指针。这意味着数据库在执行查询时，可以更快地从磁盘读取索引文件。较小的索引文件也更容易被缓存到内存中，从而减少对磁盘的访问次数。

例如，当查询一个特定ID的用户信息时，如果ID列上有索引，数据库可以快速读取索引并找到用户信息的位置，而不需要从表的开始处逐行读取。

4. 缓存效率:

索引提高了数据访问的局部性，使得相关的数据更有可能被同时缓存到内存中。当多个查询访问相同的数据时，这些数据可以被缓存，从而避免了重复的磁盘I/O操作。

例如，如果多个用户同时查询同一天的交易记录，而这一天的记录已经被索引并缓存，那么后续的查询可以直接从内存中获取数据，而不需要再次访问磁盘。

5.排序和分组:

索引还可以帮助数据库引擎在不需要额外排序操作的情况下返回有序的结果集。这是因为索引本身就按照某种顺序存储数据。

例如，如果你经常查询按照销售额降序排列的前十个销售代表，那么在销售额列上创建索引可以让数据库快速返回排序后的结果，而不需要对所有结果进行额外的排序处理。

三、索引为什么使用B+树？

B+树作为MySQL中InnoDB存储引擎的默认索引结构，因其独特的特性，在处理大量数据时提供了高效的查询性能。

1. 树的矮胖结构： B+树的非叶子节点仅存储键值，不存储实际数据。这种设计使得每个节点能够容纳更多的键值，从而降低了树的高度。在16KB的页大小下，B+树可以存储更多的索引项，使得树更矮更胖，减少了查找数据时的磁盘I/O次数，提高了查询效率。
2. 高效的范围查询： B+树的所有数据都存储在叶子节点，并且数据之间通过链表连接，形成了一个有序的结构。这使得范围查询、排序查找、分组查找以及去重查找变得非常简单和高效。相比之下，B树需要在多个节点间进行搜索，效率较低。
3. 稳定的磁盘I/O性能： 由于所有数据都存储在叶子节点，B+树的I/O次数在查询时更加稳定。这意味着即使在数据量不断增长的情况下，B+树索引的性能也不会出现大幅波动。
4. 强大的全局扫描能力： B+树的叶子节点存储了所有数据，并且通过链表连接，这使得全局扫描（全表扫描）操作只需要遍历叶子节点即可完成。与B树相比，后者需要遍历整个树结构，效率较低。
5. 优化的数据插入策略： 使用自增的整型数据作为主键可以减少数据插入时叶子节点分裂的问题，因为新插入的数据会自然地被添加到链表的末尾，避免了频繁的节点分裂和数据重组，从而提高了数据插入的效率。

四、索引失效场景

在使用MySQL数据库时，索引是提高查询效率的重要工具。然而，在某些情况下，索引可能不会生效，导致查询性能下降。以下是一些可能导致索引失效的常见场景，以及优化后的描述：

1. 使用OR条件： 当查询条件中包含OR时，MySQL可能无法有效地使用索引，因为它需要检查多个条件中的每一个，这可能导致全表扫描。
2. 字符串字段未用引号括起来： 如果查询条件中的字符串字段没有用单引号括起来，MySQL可能无法正确匹配索引中的值，从而导致索引失效。
3. 使用LIKE通配符： 当使用LIKE操作符时，尤其是当通配符位于字符串的开始位置（例如%keyword），MySQL可能无法利用索引进行快速查找。
4. 联合索引的条件列顺序问题： 如果查询条件中使用的列不是联合索引中的第一个列，MySQL可能不会使用索引，因为索引的使用依赖于查询条件与索引列的顺序匹配。
5. 在索引列上使用内置函数： 对索引列应用MySQL内置函数，如DATE()或UPPER()，会使得MySQL无法直接使用索引进行查找。
6. 索引列上的运算： 在索引列上执行算术运算（如加、减、乘、除）会使得MySQL无法利用索引进行数据查找。
7. 使用不等于或范围查询： 使用!=、<>、NOT IN等操作符进行查询时，MySQL可能不会使用索引，尤其是当这些操作符用于索引列的开头时。
8. 索引字段上的NULL检查： 使用IS NULL或IS NOT NULL检查索引字段可能导致索引失效，因为MySQL可能无法直接定位到NULL值的位置。
9. 连接查询中的字段编码不一致： 在左连接或右连接查询中，如果关联的字段编码格式不一致，MySQL可能无法使用索引进行有效的数据匹配。
10. MySQL优化器的选择： MySQL优化器会根据表的大小和索引的选择性来决定是否使用索引。如果优化器估计全表扫描比使用索引更快，它将选择全表扫描。

五、索引类型

索引是数据库中用于提高数据检索速度的重要工具。在MySQL中，有多种类型的索引，每种索引都有其特定的用途和优化场景。

主键索引：

• 主键索引是唯一的，不允许数据重复，并且不允许为NULL。
• 一个表中只能有一个主键索引，通常用于唯一标识表中的每条记录。
• 例如，用户表中的UserID列，每个用户都有一个唯一的ID，适合作为主键索引。

唯一索引：

• 唯一索引确保索引列中的值唯一，但允许有空值（NULL）。
• 一个表可以有多个唯一索引，适用于需要确保数据唯一性但允许某些记录值缺失的场景。
• 例如，在订单表中，OrderNumber列可以设置为唯一索引，以确保每个订单号只出现一次。

普通索引：

• 普通索引是最基本的索引类型，没有唯一性要求，允许重复值和NULL值。
• 适用于大多数查询场景，可以显著提高查询速度。
• 例如，如果经常根据CreatedAt列查询最近的记录，可以在此列上创建普通索引。

全文索引：

• 全文索引用于对文本内容进行高效搜索，支持分词和模糊匹配。
• 适用于搜索引擎和需要对大量文本数据进行搜索的场景。
• 例如，博客平台可以在文章内容上创建全文索引，以便用户能够通过关键词搜索相关文章。

覆盖索引：

• 覆盖索引是指查询中所需的所有列都包含在索引中，这样数据库引擎可以直接从索引中获取数据，无需访问数据行。
• 适用于查询只涉及索引列的情况，可以减少I/O操作，提高查询效率。
• 例如，如果查询经常只访问UserName和Email两列，可以在这两列上创建一个覆盖索引。

组合索引：

• 组合索引由多个列的值组成，用于优化多列的组合查询。
• 适用于经常需要根据多个列进行查询的场景，其效率通常高于单独为每个列创建索引。
• 例如，如果经常根据Country和City列进行查询，可以在这两个列上创建一个组合索引

六、聚簇索引和非聚簇索引

在MySQL的InnoDB存储引擎中，聚集索引（Clustered Index）是一种特殊类型的索引，它定义了表中数据的物理存储方式。聚集索引是基于表的主键构建的，因此每个表只能有一个聚集索引。在InnoDB中，数据行实际上是存储在B+树的叶子节点中的，这意味着聚集索引不仅定义了数据的索引结构，还决定了数据的物理存储顺序。

非聚集索引（Non-Clustered Index），也称为二级索引，是除了主键索引之外的其他索引。这些索引独立于数据的物理存储，它们的叶子节点包含索引键值和指向数据行的指针（通常是主键值），用于快速定位到数据行。

在InnoDB中，如果没有明确指定主键，InnoDB会自动创建一个隐藏的聚簇索引来存储表的数据行。通常建议使用自增ID（auto_increment）作为主键，因为这样可以保证数据的连续存储，从而提高写入和查询的性能。如果使用随机生成的ID（如UUID），可能会导致数据在磁盘上分散存储，增加随机I/O操作，降低性能。

聚集索引的优势在于它能够优化范围查询和排序操作，因为它按照索引键值的顺序存储数据。然而，由于每个表只能有一个聚集索引，因此在设计数据库时需要谨慎选择主键，以确保数据的有效组织和高效访问。同时，聚集索引的维护成本相对较高，尤其是在插入和更新操作频繁的情况下，可能会引起页分裂和数据重组，影响性能。因此，在选择合适的主键和索引策略时，需要根据应用的具体需求和数据访问模式进行权衡。

七、结尾

感谢您的观看! 如果本文对您有帮助，麻烦用您发财的小手点个三连吧！您的支持就是作者前进的最大动力！再次感谢！