长大后想做什么？做回小孩！

0%

MySQL索引必知必会

发表于 更新于 分类于 Valine：
本文字数： 6.2k

MySQL索引必知必会

数据结构的选择

Hash表

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

哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。 通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。 就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

MySQL 的 InnoDB 存储引擎不直接支持常规的哈希索引，但是，InnoDB 存储引擎中存在一种特殊的“自适应哈希索引”（Adaptive Hash Index），自适应哈希索引并不是传统意义上的纯哈希索引，而是结合了 B+Tree 和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。

自适应哈希索引的每个哈希桶实际上是一个小型的 B+Tree 结构。这个 B+Tree 结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高了索引的效率。关于 Adaptive Hash Index 的详细介绍，可以查看 MySQL 各种“Buffer”之 Adaptive Hash Indexopen in new window。

为什么 MySQL 没有使用其作为索引的数据结构呢？ Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个。

二叉查找树（BST）

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

  1. 左子树所有节点的值均小于根节点的值。
  2. 右子树所有节点的值均大于根节点的值。
  3. 左右子树也分别为二叉查找树。

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

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

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

自平衡二叉查找树（AVL树）

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

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能。并且， 在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

红黑树

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

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

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

B树和B+树

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

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

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

‌InnoDB和MyISAM区别

  • ‌数据存储方式（聚簇索引和非聚簇索引的优缺点）‌：

    • ‌InnoDB‌：数据和索引存储在同一个文件中，采用聚集索引方式，索引的叶子节点存储的是整个数据行。

      聚簇索引的优缺点：

      优点：

      • 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
      • 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

      缺点：

      • 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
      • 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

    • ‌MyISAM‌：数据和索引分开存储，数据文件（.MYD）存储数据，索引文件（.MYI）存储索引。

      非聚簇索引的优缺点：

      优点：

      更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

      缺点：

      • 依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据
      • 可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。（覆盖索引时不回表： 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。 ）

  • ‌事务支持‌：

    • ‌InnoDB‌：支持事务处理，使用ACID（原子性、一致性、隔离性、持久性）来保证数据的完整性和一致性。
    • ‌MyISAM‌：不支持事务处理，无法保证数据的一致性。

  • ‌锁机制‌：

    • ‌InnoDB‌：采用行级锁定，只锁定需要修改的行，提高并发性能。
    • ‌MyISAM‌：采用表级锁定，锁定整个表，导致并发性能较低。

  • ‌外键约束‌：

    • ‌InnoDB‌：支持外键约束，可以实现关联查询和级联删除等功能。
    • ‌MyISAM‌：不支持外键约束。

  • ‌恢复能力‌：

    • ‌InnoDB‌：具有事务日志，可以在数据库崩溃后根据日志文件进行恢复。
    • ‌MyISAM‌：没有事务日志，恢复能力较差。

  • ‌查询性能‌：

    • ‌InnoDB‌：查询过程中需要维护数据缓存，查询速度相对较慢。
    • ‌MyISAM‌：可以直接定位到数据所在的内存地址，查询速度较快。

  • ‌表结构文件‌：

    • ‌InnoDB‌：表结构文件为.frm和.ibd（或多个ibd文件），存储数据和索引。
    • ‌MyISAM‌：表结构文件为.frm、.MYD（数据文件）和.MYI（索引文件）。

  • ‌适用场景‌：

    • ‌InnoDB‌：适用于需要事务处理、外键约束和高并发性能的场景，如银行、金融等对数据一致性要求高的应用。
    • ‌MyISAM‌：适用于读多写少的场景，如博客、新闻等，因为其查询性能较好且不支持事务处理和外键约束。

在MySQL 5.5之前的版本中，默认存储引擎是MyISAM，但从5.5版本开始，InnoDB成为了默认的存储引擎。在实际应用开发中，InnoDB因其支持事务处理和外键约束等功能，被广泛应用于需要支持事务处理的应用程序中。

索引类型

按照数据结构维度划分

  • BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
  • 哈希索引：类似键值对的形式，一次即可定位。
  • RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

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

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

按照应用维度划分

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

    InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

  • 普通索引：仅加速查询。

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

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

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

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

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

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

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 where 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。

适用于 InnoDB 引擎和 MyISAM 引擎的查询。

适用于执行计划是 range, ref, eq_ref, ref_or_null 的范围查询。

对于 InnoDB 表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用索引下推 不会减少 I/O。

子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的。

存储过程不能使用索引下推，因为存储引擎无法调用存储函数。

使用索引的一些原则

合适的字段

  1. 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

  2. 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。

  3. 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。

  4. 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

  5. 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

  6. 被频繁更新的字段应该慎重建立索引： 维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

  7. 限制每张表上的索引数量： 建议单张表索引不超过 5 个 。索引有维护成本，太多的索引降低 会降低插入和更新的效率。增加优化器选择如何优化查询的时间，变相降低了查询性能。

  8. 尽可能的考虑建立联合索引而不是单列索引： 索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。 如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。

    联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升 。

  9. 避免冗余索引：冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

  10. 字符串类型的字段使用前缀索引代替普通索引： 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

避免索引失效

  1. SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;

  2. 创建联合索引，但查询条件未遵守最左匹配原则（ 联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 就会停止匹配 ）;

  3. 在索引列上进行计算、函数、类型转换等操作;

  4. 以 % 开头的 LIKE 查询比如 LIKE '%abc';;

  5. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

  6. IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);

  7. 发生隐式类型转换；

优化索引的基本手段

  1. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

    MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

  2. 使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。 EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。