一口气盘点 Myaql底层原理（高阶）

B+树索引是 MySQL 中最常用的索引结构，它是一种自平衡的多叉树，每个节点最多可以有 M 个子节点，每个子节点最多可以有 N 个键值对。B+树索引的叶子节点存储了实际的数据行，而非叶子节点则存储了索引的键值和指向叶子节点的指针。
B+树索引的优势在于它可以快速地进行范围查询和排序操作，同时也可以很好地支持并发访问。B+树索引的实现原理是将数据按照键值的大小进行排序，并将其存储在 B+树的各个节点中。B+树的搜索过程是从根节点开始，依次比较键值与当前节点的键值，如果相等则找到对应的数据，如果小于当前节点的键值则继续向下搜索左子树，如果大于当前节点的键值则继续向下搜索右子树。
B+树索引的一个重要特点是它的叶子节点之间是按照键值的大小顺序链接的，形成了一个有序链表。这样可以在范围查询时避免不必要的磁盘 I/O，提高查询效率。
B+树索引还有一些其他的知识点，比如索引的创建和维护、索引的选择和优化等。如果你想更深入地了解 B+树索引，可以参考 MySQL 的官方文档或者相关的数据库教程。

 

1-1关于 B+树索引的重要知识点：
1. 索引的存储结构：B+树索引是一种自平衡的多叉树，每个节点最多可以有 M 个子节点，每个子节点最多可以有 N 个键值对。B+树索引的叶子节点存储了实际的数据行，而非叶子节点则存储了索引的键值和指向叶子节点的指针。

2. 索引的创建和维护：在 MySQL 中，可以通过 CREATE INDEX 语句来创建 B+树索引。索引的维护包括插入、删除和更新数据时对索引的调整，以保持索引的平衡和有效性。

3. 索引的选择和优化：在创建索引时，需要考虑索引的列、顺序和选择性等因素。选择合适的索引可以大大提高查询效率，但过多或不合适的索引也会导致性能下降。可以通过分析查询计划来评估索引的效果，并进行必要的优化。

4. 索引的覆盖和利用：B+树索引可以支持索引覆盖查询，即查询可以只使用索引而不需要访问实际的数据行。这对于一些简单的查询非常有效，可以避免不必要的磁盘 I/O。

5. 索引的失效和重建：当表中的数据发生大规模的更新或删除操作时，可能会导致索引失效。可以通过重建索引来修复索引的有效性，但重建索引会消耗一定的时间和资源。

6. 索引的排序和联合：B+树索引的叶子节点是按照键值的大小顺序排序的，可以利用索引的排序特性进行排序操作。同时，多个索引可以通过联合操作来提高查询效率。

7. 索引的前缀和压缩：在某些情况下，可以使用索引的前缀来减少索引的大小和维护开销。此外，MySQL 还支持索引的压缩，以节省存储空间。

注：全文索引：是一种特殊类型的索引，它用于文本检索。全文索引可以在文本中查找单词或短语，并返回包含这些单词或短语的记录。全文索引的核心概念是将文本拆分成单词或短语，并为每个单词或短语建立一个索引项。在查询时，MySQL 会根据索引项来查找匹配的记录。全文索引的优点是可以快速进行文本检索，缺点是需要占用大量的存储空间，并且对于大规模的数据集，索引维护开销较大。

哈希索引：是一种基于哈希表的索引结构，它可以快速查找某个值是否存在于索引中。哈希索引的核心概念是将索引列的值通过哈希函数映射到一个固定大小的哈希表中，并为每个哈希表项维护一个指向实际数据行的指针。在查询时，MySQL 会根据索引列的值计算哈希值，并在哈希表中查找对应的指针。哈希索引的优点是查找速度非常快，适用于值比较集中的情况。缺点是不支持范围查找和排序操作，并且当哈希表冲突较多时，性能会下降。

联合索引：是一种将多个索引组合在一起的索引结构，它可以同时支持多个查询条件。联合索引的核心概念是将多个单列索引组合成一个复合索引，MySQL 会在查询时同时利用多个索引来加速查询。联合索引的优点是可以同时利用多个列的信息来优化查询，适用于经常使用多个列进行条件过滤的情况。缺点是如果查询条件没有按照索引的顺序从左到右使用列，可能会导致索引失效。

回表：是指在使用索引进行查询时，如果查询条件没有利用到索引的最左列，或者需要获取索引列之外的数据行信息，就会导致 MySQL 需要在索引树中查找对应的值，并通过书签查找来获取实际的数据行，这个过程就称为回表。回表的核心概念是在索引树中查找对应的值，并通过书签查找来获取实际的数据行。回表的优点是可以在索引中查找满足条件的记录，避免全表扫描。缺点是如果需要回表获取大量的数据行信息，可能会导致性能下降

覆盖索引：是一种特殊类型的索引，它可以在查询时直接获取需要的数据行信息，而不需要额外的回表操作。覆盖索引的原理是在索引中包含了查询所需的所有列信息，这样在查询时就可以直接从索引中获取数据行，而不需要再通过书签查找来获取实际的数据行。覆盖索引的查询效率通常比普通索引更高，因为它可以避免回表操作。为了创建覆盖索引，需要在索引定义中包含查询所需的所有列信息。例如，如果需要查询一个包含列 A、B、C 的表中的数据，并且查询条件只涉及列 A 和 B，那么可以创建一个覆盖索引，索引列顺序为 A、B。这样在查询时，MySQL 就可以直接从索引中获取列 A、B、C 的值，而不需要回表操作

单列索引：是一种普通的索引类型，它用于加速对表中单个列的查询。单列索引的原理是按照索引列的值对表进行排序，并将排序后的结果存储在索引树中。在查询时，MySQL 会利用索引树来快速定位匹配的记录。
 

 

2-1  MySQL 中的默认索引，在 MySQL 中，如果表中没有显式定义索引，那么会自动创建一个名为 PRIMARY 的主键索引。主键索引是一种特殊类型的索引，它用于加速对表中主键列的查询。主键索引的列值必须是唯一的，并且不能为 NULL。除了主键索引之外，MySQL 还会自动创建一些其他类型的索引，例如唯一索引、普通索引等。这些索引的创建取决于表的结构和数据类型。例如，如果表中有一个名为 name 的列，并且该列的数据类型是 VARCHAR(255)，那么 MySQL 会自动为该列创建一个普通索引。

 

3-1  全文索引和单一索引是两种不同的索引类型。
全文索引是一种特殊类型的索引，它可以用于文本检索。全文索引的原理是将文本拆分成单词或短语，并为每个单词或短语建立一个索引项。在查询时，MySQL 会根据索引项来查找匹配的记录。
单一索引是一种普通的索引类型，它用于加速对表中单个列的查询。单一索引的原理是按照索引列的值对表进行排序，并将排序后的结果存储在索引树中。在查询时，MySQL 会利用索引树来快速定位匹配的记录。
默认情况下，MySQL 不会为表中的任何列创建全文索引。如果需要使用全文索引，需要在表定义中显式地指定全文索引列和相关的参数。例如，可以使用 FULLTEXT 关键字来创建全文索引：
CREATE TABLE table_name (
    id INT,
    name VARCHAR(255),
    content TEXT,
    FULLTEXT (content)
);
在上面的示例中，为 content 列创建了一个全文索引。这样在查询时，就可以使用全文索引来加速文本检索。
需要注意的是，全文索引的创建和维护需要一定的开销，因此应该根据实际需求来选择是否使用全文索引。如果只需要对表中的单个列进行快速查询，那么使用单一索引就足够了。

4-1  有好几种情况会导致索引失效。比如，在查询条件中使用了函数、通配符或范围条件，还有在查询中使用了隐式转换，或者在查询中使用了联合索引但是没有按照索引的顺序进行查询，这些都会导致索引失效。要避免索引失效，尽量在查询条件中使用索引列的原始值，避免使用函数、通配符或范围条件，还有要确保查询条件中的列和索引列的数据类型一致。如果需要使用隐式转换，如果需要使用隐式转换，可以考虑创建一个显式的转换函数，并在查询条件中使用这个函数来进行转换。这样可以避免索引失效。如果需要使用联合索引，要确保查询条件中的列按照索引的顺序进行查询。如果查询条件中的列没有按照索引的顺序进行查询，可以考虑重新设计索引或修改查询条件。另外，定期对索引进行维护，例如重建或优化索引，以确保索引的有效性和性能。

sql中常见索引失效：比如，你在查询条件中使用了函数，像这样 WHERE DATE(column_name) = '2023-01-01' ，索引就失效了。还有，如果你在查询条件中使用了通配符 % ，像这样 WHERE column_name LIKE '%value%' ，索引也会失效。还有，如果你在查询条件中使用了范围条件，像这样 WHERE column_name > 10 AND column_name < 20 ，索引也可能失效。另外，如果你在查询中使用了隐式转换，像这样 WHERE column_name = '10' ，但是 column_name 的类型是 INT ，那么索引也会失效。还有，如果你在查询中使用了联合索引，但是没有按照索引的顺序进行查询，像这样 WHERE column_name = 'value' AND column_name2 = 'value2' ，但是联合索引的顺序是 (column_name1, column_name2) ，那么索引也会失效。这些都是常见的导致索引失效的情况，你在写 SQL 语句的时候一定要注意。

好除了上面提到的情况，还有一些其他的原因也可能导致索引失效。比如，如果你在查询中使用了 IS NULL 或 IS NOT NULL ，那么索引可能会失效。这是因为 MySQL 在执行这些查询时无法利用索引进行快速查找。还有，如果你在查询中使用了 OR 操作符，那么索引可能会失效。这是因为 OR 操作符会导致索引的范围扫描，从而降低查询的效率。还有，如果你在查询中使用了 NOT IN 或 NOT EXISTS 子句，那么索引可能会失效。这是因为这些子句会导致索引的范围扫描，从而降低查询的效率。还有，如果你在查询中使用了 <> 或 != 操作符，那么索引可能会失效。这是因为这些操作符会导致索引的范围扫描，从而降低查询的效率。还有，如果你在查询中使用了 >= 或 <= 操作符，并且索引列的值存在重复，那么索引可能会失效。这是因为在这种情况下，MySQL 可能会选择全表扫描而不是使用索引。这些都是可能导致索引失效的情况，你在写 SQL 语句的时候一定要注意