14、INDEX索引（上）

索引介绍

索引是数据库中用于提高查询效率的一种数据结构，它可以使得数据库的查询速度更快。通过索引可以快速定位到包含要查询数据的数据块，从而提高查询效率；是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。在数据库中，索引是建立在一个或多个表的列上的，常见的索引类型包括B-tree索引、哈希索引、全文索引等。

B-tree索引是MySQL中最常用的索引类型，它是一种树形结构的索引，每个节点可以包含多个子节点，每个节点的子节点数都有一个上限。B-tree索引可以实现快速的查找、插入和删除操作，同时对于范围查询也有较好的支持。

哈希索引则是一种散列表的形式，通过哈希函数将索引列的值映射到一个哈希值上，然后将哈希值和对应行的指针存储在哈希表中。哈希索引可以实现快速的等值查询，但是对于范围查询、模糊查询等操作效率不高。

全文索引则是针对文本类型的数据建立的索引，可以快速实现文本内容的搜索。

在实际应用中，我们需要根据具体的业务需求选择合适的索引类型，并合理地设计索引，避免过多或者不必要的索引造成的性能影响。

索引特征

MySQL索引是一种数据结构，它存储了一个表中的某一列数据的值并指向它们的物理存储位置。索引可大大提高查询数据的效率，降低系统的I/O负载，但也有其优缺点：

优点：

提高查询速度：索引能够快速找到表中的特定数据，避免全表扫描。
缩短排序时间：排序通常需要使用索引。
加快表的连接速度：连接通常需要使用索引，特别是在连接大表时。
增加数据的完整性：可将Unique（唯一）索引建立在某一数据列上，强制保证数据的唯一性。

缺点：

索引需要额外的存储空间：索引将需要额外的存储空间来维护索引树等数据结构。
索引的维护会增加写操作的成本：对表中的数据进行新增、更新和删除操作，涉及到索引的变更，会增加写操作的成本。
索引并不是万能的：在一些情况下，索引可能并不能提高查询效率，或者甚至会降低查询效率。

因此，应当权衡优缺点，在必要的数据列上应建立适当的索引，不过度使用索引以避免出现低效的场景。

索引类型

MySQL支持多种不同类型的索引，下面是一些常用的索引类型：

B-tree 索引：B-tree是MySQL中默认使用的索引类型。它在查询方面性能良好，可用于高效地查找等值、范围和存储排序数据。

哈希索引：哈希索引建立在哈希表上，通过哈希算法将每行数据转为一个哈希值，在哈希表中查找该哈希值所在的位置，以此找到对应行数据。它适用于等值匹配的查询，但不支持范围查询和排序操作。Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持；适用场景：只支持等值比较查询，包括=, <=>, IN()

全文索引：用于对文本内容的全文搜索，它允许查询含有某些特定字符串或词语的所有行。

空间索引：处理空间数据的索引类型，可以用于更加有效地查询地理位置坐标等数据类型。MyISAM支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多

前缀索引：通过对列值的前缀进行索引，可以用更少的存储空间来减少索引建立的开销。但是它也可能会导致查询效率降低，因为前缀长度可能太短而无法定位到正确的行。

聚簇和非聚簇索引，主键、二级索引：

聚簇索引是以表的主键或唯一性约束作为索引的行指针，将数据按照索引列的值进行物理排序，并直接存储在磁盘上。这样，具有相同主键值的行将存储在相邻的磁盘页上，从而提高查询效率。由于数据页是按照索引值物理排序的，所以插入新行时，需要根据主键值来确定该行在数据页中的位置，因此聚簇索引会增加插入的成本。
非聚簇索引使用的是一个单独的数据结构来存储索引值和行指针，它们不影响表的物理存储顺序。在非聚簇索引中，每个数据页上的记录顺序是独立的，因此不会出现插入成本较高的情况。在查询时，系统先按照索引值从非聚簇索引中查找行指针，然后再根据行指针访问表中的数据行。
一般来说，聚簇索引适合那些经常需要扫描整个表的数据列，而非聚簇索引适合那些需要经常查询的列。例如，如果物理地址空间有序，那么使用聚簇索引更能体现出查询的优势；而如果查询经常基于某些字段而不是主键，那么非聚簇索引会更好。

聚簇索引和非聚簇索引的区别主要在于它们的实现方式和使用场景不同：

实现方式：聚簇索引将数据按照主键或唯一性列的值物理排序，并将数据直接存储在磁盘上。非聚簇索引则单独存储索引值和行指针，不影响表的物理存储顺序。
使用场景：聚簇索引适合那些经常需要跨一些指定行执行范围查询，这通常会影响数据的顺序访问。例如，对大型表的整个范围进行扫描查询，或对定位特定行的数据执行修改操作。而非聚簇索引则可以用于任意类型的数据列，适合那些需要频繁访问的列或需要快速筛选数据行的查询。

冗余和重复索引：

冗余索引：指的是一个表上存在多个索引，但是只有其中一个索引是有用的。比如，在一个包含多个列的复合索引中，某些列已经在其他的单独索引中进行了覆盖。这会占用大量的存储空间并导致索引的更新和查询成本增加，如（A），（A，B）

重复索引：已经有索引，再次建立索引

索引结构

二叉树

二叉树（Binary Tree）是一种特殊的树结构，它的每个节点最多只有两个子节点，分别称为左子节点和右子节点。
二叉树的特点包括
每个节点最多只有两个子节点，左子节点和右子节点。
左子节点和右子节点的顺序不能交换，即左子节点在前，右子节点在后。
二叉树的根节点没有父节点，叶子节点没有子节点。
在二叉树中，每个节点包括三部分：一个数据元素，以及左子树和右子树的指针。如果某个节点没有左子树或右子树，则指针为空。二叉树有很多种类，如满二叉树、完全二叉树、平衡二叉树等。

红黑树

红黑树是一种自平衡的二叉查找树，它的每个节点都有一个额外的颜色属性，可以是红色或黑色。除了普通的二叉查找树的要求之外，红黑树还需要满足以下几个性质：
每个节点都是红色或黑色；
根节点是黑色；
每个叶子节点（NIL节点，空节点）都是黑色的；
如果一个节点是红色的，则它的两个子节点必须都是黑色的；
任意一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点。

B-tree索引结构

B+Tree索引

B-Tree和B+Tree都是数据库中常用的索引结构，它们具有一些相似之处，但在某些方面又有所不同。

相似之处：
都是用于在大规模数据集中快速定位记录和检索数据的数据结构。
都是多路平衡搜索树，支持高效的查找和范围查询。
两者都可以通过插入、删除节点来动态调整自身的结构，从而保持平衡。

不同之处：
B-Tree中每个节点既可以是内部节点，又可以是叶节点，而B+Tree内部节点只存储索引，数据只存储在叶节点中。这使得B+Tree在范围查询、遍历以及排序时的性能优于B-Tree。
B+Tree比B-Tree需要更少的I/O操作，因为大部分数据存储在叶子节点上。B-Tree虽然在内部节点和叶子节点都保存了关键字，但内部节点中的关键字也会被重复存储在下方节点中的叶子节点上。这意味着在从根节点到叶节点的路径上，需要进行更多的I/O操作。
在B+Tree中，叶子节点形成了一个单向链表，使得范围查询和遍历过程更容易实现，而B-Tree则需要进行回溯操作，结果更加复杂。
B+Tree通常比B-Tree还有更高的分支因子（即度数），这进一步减少了搜索路径的节点数量，提高了检索效率。
综上所述，B+Tree相对于B-Tree在范围查询、遍历、排序及磁盘I/O操作等方面具有更好的性能。因此，在现代数据库系统中，B+Tree已成为默认的索引结构。

注 B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

B+TREE索引语句使用类型

使用 B+Tree 索引可以加速以下类型的查询：

等值查询：例如，WHERE column_name = value ，这种查询可以在 B+Tree 索引中使用二分查找策略加快查找速度。

区间查询：例如，WHERE column_name BETWEEN value1 AND value2，B+Tree 索引以叶子节点的形式存储数据，将数据从小到大排列，因此可以直接查找一个区间范围内的所有数据，而不需要扫描整个表。

以某列开头的查询：例如，WHERE column_name LIKE 'value%'，以及其他使用匹配表达式开头的查询，B+Tree 索引也可以加速，因为它可以按照键的前缀匹配查找对应的数据。

排序查询：B+Tree 索引按照键的大小对数据进行排序存储，因此对于需要排序的查询，可以直接使用 B+Tree 索引查找数据，而不需要先将整个表扫描一遍进行排序。

需要注意的是，B+Tree 索引不适用于以下情况：

不等值查询：例如，WHERE column_name > value 或 WHERE column_name != value，这些查询可能需要扫描整个表才能找到对应的数据。

使用函数的查询：例如，WHERE func(column_name) = value，由于函数的返回值无法预测，B+Tree 索引无法直接使用。

多表关联查询：如果查询涉及多个表之间的关联操作，那么 B+Tree 索引的效率就无法发挥，需要使用其他类型的索引来优化。