沉梦听雨的编程指南
  • 数据库
  • Mysql
沉梦听雨
2023-06-22
目录

索引篇

# MySQL 索引详解

# 讲一下什么是索引？

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

作用相当于书的目录。

# 了解索引的底层数据结构吗？

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash 表、红黑树。

在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了【B+树】作为索引结构。

# 索引的优缺点了解吗？

优点：

  • 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

# 使用索引一定能提高查询性能吗？

不一定。使用索引可以提高查询速度，但并不是一定能提高查询速度。

  • 在大多数情况下，索引查询都是比全表扫描要快的。
  • 但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升，甚至降低查询速率。

# 索引底层数据结构选型

# Hash 表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value 呢？

原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

hash = hashfunc(key)
index = hash % array_size
1
2

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？

主要是因为 Hash 索引不支持顺序和范围查询。

假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

# 二叉查找树（BST）

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

  1. 左子树所有节点的值均小于根节点的值。（左 < 根 < 右）
  2. 右子树所有节点的值均大于根节点的值。
  3. 左右子树也分别为二叉查找树。
  • 当二叉查找树是平衡的时候，也就是树的每个节点的左右子树深度相差不超过 1 的时候，查询的时间复杂度为 O(log2(N))，具有比较高的效率。
  • 然而，当二叉查找树不平衡时，例如在最坏情况下（有序插入节点），树会退化成线性链表（也被称为斜树），导致查询效率急剧下降，时间复杂退化为 O(N)。

image

也就是说，二叉查找树的性能非常依赖于它的平衡程度，这就导致其不适合作为 MySQL 底层索引的数据结构。

为了解决这个问题，并提高查询效率，人们发明了多种在二叉查找树基础上的改进型数据结构，如平衡二叉树、B-Tree、B+Tree 等

# ALV 树

ALV 树是计算机科学中最早被发明的自平衡二叉查找树。

特点是：保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

是节点的左右【子树高度】不超过 1

image

AVL 树是如何保持平衡的？

AVL 树采用了旋转操作来保持平衡。

主要有四种旋转操作：LL 旋转、RR 旋转、LR 旋转和 RL 旋转。

其中 LL 旋转和 RR 旋转分别用于处理左左和右右失衡，而 LR 旋转和 RL 旋转则用于处理左右和右左失衡。

MySQL 没有选择 ALV 树的原因？

  • 由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了查询性能。
  • 在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO，这非常耗时。

磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

实际应用中，AVL 树使用的并不多。

# 红黑树

红黑树也是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

  1. 每个节点非红即黑；
  2. 根节点总是黑色的；
  3. 每个叶子节点都是黑色的空节点（NIL 节点）；
  4. 如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
  5. 从根节点到叶节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

image

MySQL 没有选择红黑树的原因?

因为红黑树追求的是大致的平衡（AVL 树追求的是严格的平衡），所以红黑树的平衡性相对较弱，可能会导致树的高度较高，进而会导致一些数据需要进行多次磁盘 IO 操作才能查询到，也就意味着查询效率会稍有下降。

# B树和 B+树

B 树和 B+树中的 B 是 Balanced （平衡）的意思。

  • B 树也称 B-树,全称为 多路平衡查找树，

  • B+ 树是 B 树的一种变体。

目前大部分数据库系统及文件系统都采用【B-树】或其变种【B+树】 作为索引结构。

【B树】和【B+树】有何异同呢？

  1. 存放问题
    • B 树的所有节点既存放键(key) 也存放数据(data)；
    • 而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  2. 叶子节点
    • B 树的叶子节点都是独立的；
    • B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  3. 检索过程
    • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。
    • 而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  4. 范围查询
    • 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；
    • 而 B+树的范围查询，只需要对链表进行遍历即可。

总结：【B+树】与【B树】相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询的优势。

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

  • MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。实现的是 非聚集索引。
  • 而 InnoDB 引擎中，其数据文件本身就是索引文件。实现的是 聚集索引。

# 索引类型总结

按照数据结构维度划分

  1. BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。
  2. 哈希索引：类似键值对的形式，一次即可定位。
  3. RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  4. 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分

  1. 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。

  2. 普通索引：仅加速查询。

  3. 唯一索引：加速查询 + 列值唯一（可以有 NULL）。

  4. 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。

  5. 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

    ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);
    1

  6. 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性

  • 隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会在软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
  • 降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引（不指定的情况下，默认升序）。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
  • 函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式

# 最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

举个例子，如果有一个联合索引包含两个字段 A 和 B，需要进行查询时，如果只使用字段 B 作为查询条件，那么查询时将无法充分利用索引，需要遍历整个索引才能找到匹配的记录，这样查询效率会非常低。而如果使用字段 A 作为查询条件，那么可以直接定位到索引中对应的位置，然后再在这个范围内使用 B 进行匹配，从而快速找到匹配的记录，查询效率将会大大提高。

# 索引类型

这里是对几种常见的数据库索引类型的简要解释：

  1. 主键索引（PRIMARY index）：
    • 主键索引是一种用于唯一标识数据库表中记录的索引。每个表只能有一个主键索引，它确保表中的每行都具有唯一的标识符，通常与主键约束一起使用。主键索引通常能够加速检索操作。
    • 主键列不允许有 NULL 值。在 InnoDB 存储引擎下，主键索引也被用于聚类索引，意味着数据的存放顺序与主键值的顺序是相同的。
  2. 全文索引（FULLTEXT index）：
    • 全文索引是用于在文本数据中进行全文本搜索的索引类型。与普通索引不同，全文索引允许在文本列中进行更复杂的搜索，包括全文搜索和模糊搜索。
    • 这种索引适用于包含大量文本信息的列，如文章或博客内容。
  3. 普通索引（NORMAL index）：
    • 普通索引是最基本的索引类型，用于加速对表中数据的检索。它们并不具有特殊的约束或限制，可以用于任何类型的列。
    • 普通索引通过创建一个按照列值排序的数据结构来提高检索效率。
  4. 空间索引（SPATIAL index）：
    • 空间索引用于处理空间数据，例如地理位置信息。这种索引使得数据库能够更有效地处理空间查询，如范围搜索、最近邻搜索等。
    • 在处理地理信息系统（GIS）数据或其他需要考虑空间关系的应用中，空间索引是很有用的。
  5. 唯一索引（UNIQUE index）：
    • 唯一索引确保列中的所有值都是唯一的，类似于主键索引，但唯一索引允许有一个特殊的 NULL 值。
    • 如果在唯一索引列中插入 NULL 值，数据库将允许一行包含 NULL，但对于 非 NULL 值，每个值必须是唯一的。
    • 唯一索引可用于确保某列或一组列中的值是唯一的，但不一定是主键。

# 疑惑解答

# 为什么变成索引就会快，从 sql 语句来看不一样是正常查询一个字段吗？

索引之所以能够提高查询性能，是因为它们可以让数据库更快地定位到需要的数据，而不必全表扫描。让我们通过一个例子来解释为什么索引可以提高查询速度：

假设您有一个包含成千上万条记录的数据库表，其中有一个列叫做 "last_name" 存储了人们的姓氏。现在，如果您要执行一个 SQL 查询，找出所有姓氏为 "Smith" 的人。

有两种方式可以实现：

  1. 使用索引的查询： 如果 "last_name" 列上有一个索引，数据库可以直接查找索引，找到所有姓氏为 "Smith" 的记录的位置。这个过程类似于查字典的索引，非常快速，而且不受表中记录数量的影响。
  2. 不使用索引的查询： 如果没有索引，数据库只能进行全表扫描，也就是逐行检查每一条记录的 "last_name" 字段，看是否等于 "Smith"。这个过程会随着记录数量的增加而变得非常慢。

从 SQL 语句的角度来看，无论是否使用索引，查询的语法都是一样的。但是，索引可以让数据库引擎在执行查询时更有效地定位数据，从而提高了查询速度。

总之，索引是一种优化数据库查询性能的重要工具，通过减少数据扫描的需求，可以显著提高查询效率。因此，在设计数据库时，通常会考虑哪些列需要创建索引以支持常用的查询操作。

详细解释

  1. 使用索引查询： 当您使用索引进行查询时，数据库引擎首先会查找索引，找到匹配查询条件的记录的位置。然后，它将使用这些位置信息去检索整个记录，包括索引标记的字段以及表中的其他字段。所以，索引可以帮助数据库引擎快速定位记录，但查询仍然需要访问整个记录以获取完整的信息。
  2. 全表扫描查询： 如果没有索引，数据库将不得不对整个表进行全表扫描，逐行检查所有记录，以查找匹配查询条件的记录。这会导致性能下降，特别是在大型表上。

因此，索引的作用是加速数据定位，但一旦定位到记录，数据库仍然需要访问记录的所有字段以满足查询的需求。这就是为什么在选择哪些字段创建索引时，需要根据查询的需求进行权衡。不应该过度索引，因为索引会增加写操作的成本，但在常用的查询字段上创建索引可以显著提高读操作的性能。

# 会 sql 调优，那你知道索引合并吗？

是的，知道。索引合并是一种用于优化查询性能的数据库技术，通常发生在涉及多个列的复合索引情况下。

当查询中的条件涉及到多个列，并且这些列分别存在单列索引时，数据库优化器可能会选择将这些单列索引合并成一个更大的复合索引，以提高查询性能。这个合并过程通常在查询执行计划中完成，优化器会根据查询的复杂性和数据分布来决定是否执行索引合并。

使用索引合并有什么好处？

索引合并的优点包括：

  1. 减少索引的数量：合并后的复合索引可以减少索引的数量，降低存储开销。
  2. 提高查询性能：通过使用更大的复合索引，数据库可以更有效地执行包含多个列的查询，减少了索引的查找次数。
  3. 更好的维护：维护一个复合索引比维护多个单列索引更容易，因为减少了索引的数量。

应用场景

需要注意的是，索引合并并不是在所有情况下都适用的优化技术，它通常在以下情况下发挥作用：

  • 查询中涉及多个列，并且这些列分别存在单列索引。
  • 查询的条件和数据分布适合合并成一个复合索引。
  • 索引的维护成本和存储成本可以接受。

在具体实施索引合并时，需要根据数据库管理系统的特性和性能要求来进行权衡和决策。不同的数据库系统可能具有不同的索引合并策略和优化器行为。

# 大概多少数据行才算大型表？

通常情况下，一个表的数据行数量达到【十万行】或更多时，可以被视为大型表。此时添加索引通常会提高查询性能。

# 什么情况下是需要加索引，什么字段才可以加索引

在数据库中，索引是一种提高查询性能的有效手段。然而，并非所有字段都适合加索引，而且过度使用索引可能导致性能问题。以下是一些情况下适合加索引的场景：

  1. WHERE 子句中的列： 那些常用于 WHERE 子句中的列，特别是经常用于过滤数据的列，适合加索引。例如，用于查询条件的列。

    CREATE INDEX idx_example ON your_table(your_column);
    1

  2. JOIN 操作的连接列： 如果你经常使用 JOIN 操作，那么连接列也是索引的好选择。这有助于提高连接操作的性能。

    CREATE INDEX idx_join_column ON table1(join_column);
    1

  3. 主键和唯一键： 主键和唯一键默认已经有索引，但如果你在其他列上有唯一性要求，也可以考虑加唯一索引。

    CREATE UNIQUE INDEX idx_unique_column ON your_table(unique_column);
    1

  4. 经常用于排序和分组的列： 如果你的查询经常包含 ORDER BY 或 GROUP BY 子句，那么对于这些列加索引可能会提高性能。

    CREATE INDEX idx_sort_column ON your_table(sort_column);
    1

  5. 频繁用于搜索的列： 如果某一列的搜索频率很高，例如经常用于检索的列，考虑加索引。

    CREATE INDEX idx_search_column ON your_table(search_column);
    1

  6. 覆盖索引： 考虑创建覆盖索引，使索引能够涵盖查询所需的所有列，从而避免回表操作。

    CREATE INDEX idx_covering_index ON your_table(column1, column2, ...);
    1

避免在以下情况下过度使用索引：

  • 小表： 对于小表，索引可能带来的性能提升相对较小，而索引本身的维护成本可能较高。
  • 频繁的写操作： 索引会影响写操作的性能，因为每次写入都需要更新索引。
  • 不均匀的数据分布： 如果数据分布不均匀，某些列的值重复度很高，索引的效果可能较差。

在决定添加索引时，最好根据具体的查询模式、数据分布以及数据库引擎的特性进行评估。使用数据库的性能分析工具，观察查询执行计划，可以帮助确定哪些索引是有益的。

# 深度考察

  1. 为什么索引能提高查询速度？
  2. 聚集索引和非聚集索引的区别？
  3. 非聚集索引一定回表查询吗？
  4. 索引这么多优点，为什么不对表中的每一个列创建一个索引呢？(使用索引一定能提高查询性能吗?)
  5. 索引底层的数据结构了解么？
  6. Hash 索引和 B+树索引优劣分析
  7. B+树做索引比红黑树好在哪里？
  8. 最左前缀匹配原则了解么？
  9. 什么是覆盖索引？
  10. 如何查看某条SQL语句是否用到了索引？
上次更新: 2024/9/25 11:16:13
MySQL基础小结
思维导图

← 思维导图→

Theme by Vdoing | Copyright © 2023-2024 沉梦听雨 | MIT License