分类

按物理结构

在 B+ 树的索引中，叶子节点可能存储了当前的键值，也可能存储了当前的键值以及整行的数据，这就是聚簇索引和非聚簇索引。在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引
非聚簇索引的逻辑顺序则与数据行的物理顺序不一致，通常会需要回表查询

按数据结构

B-Tree 索引：
- B-Tree（平衡树）索引是最常见的数据库索引类型。
- 它适用于大多数情况，支持等值查询、范围查询和排序操作。
- B-Tree索引对于静态和动态数据都很有效，适用于大多数关系型数据库系统。
B+Tree 索引：
- B+Tree索引是B-Tree的变种，常用于关系型数据库系统。
- 与B-Tree不同，B+Tree索引中只包含叶子节点，内部节点只用于导航，这使得B+Tree索引更适合范围查询和排序操作。
哈希索引：
- 哈希索引使用散列函数将索引键映射到存储桶（存储位置），通常用于等值查询。
- 哈希索引对于等值查询非常高效，但不支持范围查询和排序操作。
- 哈希索引在某些情况下可以提供非常快的查询性能，但不适用于所有场景。
全文索引：
- 全文索引用于搜索文本数据，如文章、博客帖子或文档。
- 它支持文本内容的关键字搜索，允许模糊查询和全文搜索。
- 全文索引通常用于全文搜索引擎或数据库系统的全文搜索功能。

Hash和B+树索引的区别

哈希索引适用于等值查询，具有快速的等值查询性能和较小的存储开销。
B+树索引更通用，适用于各种查询类型，包括等值查询、范围查询和排序操作，但通常需要更多的存储空间。
1. 数据结构：

哈希索引： 哈希索引使用哈希函数将索引键映射到存储桶（或槽位）中。每个存储桶可以包含一个或多个具有相同哈希值的索引键。哈希索引将索引键与存储桶之间的关系存储在内存中或磁盘上。
B+树索引： B+树索引是一种树状结构，通常是平衡树，包括根节点、内部节点和叶子节点。叶子节点包含了实际的索引键和指向数据行的指针。B+树索引的内部节点用于导航和范围查询。
2. 支持的查询类型：
哈希索引： 哈希索引主要适用于等值查询，即查找具有特定键值的行。哈希索引不支持范围查询和排序操作，因为它将键映射到离散的存储桶中，无法提供有序的数据访问。
B+树索引： B+树索引支持等值查询、范围查询和排序操作。它可以在树结构中按照键值的顺序访问数据，因此非常适合各种查询类型。
3. 存储和空间需求：
哈希索引： 哈希索引通常需要相对较小的存储空间，因为它将键映射到存储桶，但需要注意，存储桶的数量可能会影响性能。
B+树索引： B+树索引通常需要更多的存储空间，因为它包含了更多的数据结构，包括内部节点和叶子节点。但由于其平衡树结构，它能够提供高效的范围查询和排序操作。
4. 写操作的性能：
哈希索引： 哈希索引对于等值查询的写操作（插入、更新、删除）通常非常高效，因为它可以在常数时间内找到特定键值。但对于范围删除或更新操作，哈希索引可能需要扫描整个索引，性能较差。
B+树索引： B+树索引对于等值查询和范围查询的写操作都相对高效，因为它的平衡树结构允许根据键值顺序访问数据。

B+树和B树的区别

B+ 树减少了 IO 次数：
- 由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
B+ 树查询效率更稳定：
- 由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。
B+ 树更加适合范围查找：
- B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

为何使用B+树而非二叉查找树做索引

平衡性和稳定性：
- B+树是一种自平衡树结构，而二叉查找树的平衡性不如B+树好。B+树的平衡性保证了在插入和删除操作后，树的高度保持相对稳定，从而保持了查询性能的稳定性。而BST的不平衡性可能会导致树的高度大幅增加，从而降低查询性能。
范围查询的效率：
- B+树非常适合范围查询，因为它的叶子节点构成了一个有序链表，可以轻松执行范围查询操作。而BST需要在不同分支上进行搜索，效率相对较低。
提高磁盘io效率（顺序读取）：
- B+树的节点通常比BST的节点大，因为它需要存储更多的键值对信息。然而，在磁盘上，顺序读取比随机读取更高效。由于B+树的有序性，磁盘IO更加连续，减少了随机读取，从而提高了磁盘IO效率。
叶子节点的存储（所有数据都存储在叶子节点上）：
- B+树的叶子节点包含了所有索引数据，而内部节点仅包含索引键和指向子节点的指针。这样的设计使得B+树的叶子节点更适合内存缓存，因为它们通常更小，可以容纳更多的数据。相比之下，BST的所有节点都可能包含数据，会导致内存开销较大。
支持高度扇出（每个节点可以有多个子节点）：
- B+树支持高度扇出（每个节点有多个子节点），这意味着在相对较低的高度下可以容纳大量数据。这对于大型数据库非常重要，因为它可以减少磁盘IO和提高查询性能。

按应用类型

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；
复合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；
全文索引：只有在 MyISAM 引擎上才能使用（innoDB新版本支持），同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

优缺点

优点

提高检索速度：
- 最明显的优点是加速数据检索。索引允许数据库系统更快地定位和访问符合查询条件的数据行，特别是在大型数据表中，查询性能得到显著提升。
支持快速查找：
- 索引使数据库系统能够在常数时间内快速查找特定值，而不是遍历整个数据表。
排序优化：
- 索引可以提高排序操作的性能，因为数据库可以使用索引按顺序访问数据。
加速连接操作：
- 当执行连接操作（例如JOIN）时，索引可以加速数据的匹配过程，提高关联查询的性能。
帮助确保数据完整性：
- 唯一索引可以确保索引列中的数据唯一性，有助于维护数据完整性。

缺点

存储开销：
- 索引占用额外的存储空间，因为索引数据结构需要额外的内存和磁盘空间。对于大型数据表，索引可以占据相当大的空间。
更新性能降低：
- 当插入、更新或删除数据行时，索引需要维护，这可能会导致更新性能下降。每次更新都需要更新索引结构，这会增加写操作的开销。
查询性能下降（部分情况）：
- 在某些情况下，使用不当的索引或过多的索引可以导致查询性能下降。过多的索引可能会导致查询优化器选择错误的索引，增加了查询的成本。
维护成本：
- 数据库维护索引需要额外的工作，包括创建、重新构建和优化索引。
空间复杂性：
- 对于复杂的查询，涉及多个表和多个索引时，查询优化可能变得复杂，不容易预测性能。

索引设计原则

选择唯一索引：
- 唯一索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
- 主键索引和唯一索引，在查询中使用是效率最高的。
为经常需要排序、分组和联合操作的字段建立索引：
- 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
为常作为查询条件的字段建立索引：
- 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
尽量使用前缀来索引：
- 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
限制索引的数目：
- 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
尽量使用数据量少的索引：
- 尽量使用字段长度小的列创建索引。如果索引的值很长，那么查询的速度会受到影响。
删除不再使用或者很少使用的索引：
- 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

补充

最左匹配原则?

最左匹配原则是数据库索引优化的一个关键概念，它指导数据库系统在使用复合索引（Composite Index）时如何有效地匹配查询条件。
在最左匹配原则中，索引的复合键（Composite Key）中的多个列按照索引定义的顺序依次匹配查询条件。具体来说，当执行一个查询，涉及到复合索引的多个列时，数据库系统会首先使用索引的最左边的列来过滤数据，然后再逐渐向右匹配更多的列，直到满足查询条件或者不再匹配。
这个原则的关键在于，如果查询条件中涉及到复合索引的多个列，那么只有在最左边的列被用于查询时，索引才会发挥作用。如果最左边的列不在查询条件中，那么索引将无法有效利用。

举个例子，假设有一个复合索引 (A, B, C)，按照最左匹配原则：

如果查询条件包括 A，那么索引可以被充分利用。
如果查询条件包括 A 和 B，那么索引也可以被充分利用。
如果查询条件包括 A、B 和 C，那么索引仍然可以被充分利用。
但如果查询条件只包括 B 或者 C，而不包括 A，那么索引将无法有效使用。
其余情况下（A,C），只有A能走索引而C无法走索引

覆盖索引

覆盖索引（Covering Index）是一种特殊类型的数据库索引，它包含了查询所需的所有列，而不仅仅是索引键列。当一个查询可以完全通过索引本身满足，而不需要访问实际数据表中的数据行时，就称之为覆盖索引。

覆盖索引具有以下特点和优点：

减少磁盘IO： 因为覆盖索引包含了查询所需的数据列，数据库系统可以直接从索引中获取数据，而无需访问数据表，从而减少了磁盘IO操作。这通常会显著提高查询性能，尤其是在大型数据表上。
减少内存占用： 由于覆盖索引通常比完整数据行小，所以可以减少内存使用。这对于数据库缓存的性能有积极影响，因为更多的索引页可以驻留在内存中。
加速查询速度： 覆盖索引可以加速查询速度，因为不需要额外的数据检索操作。这对于频繁执行的查询特别有用，如报表查询和分析查询。
降低锁定的持续时间： 当查询使用覆盖索引时，需要锁定的数据行较少，因此可以减少锁定的持续时间，提高并发性。
减少网络开销： 对于分布式数据库系统，使用覆盖索引可以减少数据传输的网络开销，因为不需要传输完整的数据行，只需要索引和查询的列。

索引下推

在不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器判断数据是否符合条件。
而有了索引下推之后，如果存在某些被索引列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。
总而言之，就是在进行多条件匹配时，不通过索引下推，引擎层可能会将满足第一个条件的所有行提交给服务器端进行筛选（这个过程可能产生多次回标），而有了索引下推后，服务器端将条件告诉引擎层，在引擎层就可以多匹配到条件，减少服务器端回表查询的次数。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

索引的维护

索引的维护包括页分裂（Page Split）和页合并（Page Merge）两个主要方面，它们是数据库系统在动态数据增删改的情况下，保持索引结构的平衡和高效的重要手段。

页分裂（Page Split）：
- 当一个数据页（Index Page）已满，无法再容纳新的数据时，数据库系统会进行页分裂操作。
- 页分裂将满页分成两个新的数据页，以腾出空间来存储新的数据。通常是将数据页中的一部分数据移动到新的数据页中，然后在原页中插入新的数据，保持数据的有序性。
- 在 B-Tree 索引结构中，页分裂通常发生在树的中间节点或叶子节点上，以保持树的平衡性。
页合并（Page Merge）：
- 当一个数据页中的数据删除后，导致页面的利用率过低时，数据库系统可能会进行页合并操作。
- 页合并将相邻的两个数据页合并成一个更大的数据页，以提高页面的利用率。通常是将两个数据页中的数据合并到一个新的数据页中，并更新父节点的索引。
- 在 B-Tree 索引结构中，页合并通常发生在叶子节点上，以减少索引的层次，提高查询性能。
  页分裂和页合并是数据库系统中的动态操作，它们根据数据的增删改变化来保持索引结构的平衡和高效。通过合理的页分裂和页合并操作，数据库系统可以在保持索引结构的同时，保持较低的空间开销和高效的查询性能。