写点什么

MySQL 面试八股文:索引篇

作者:Java你猿哥
  • 2023-05-03
    湖南
  • 本文字数:2195 字

    阅读完需:约 7 分钟

MySQL面试八股文:索引篇

索引的定义

索引是数据库中用来加速数据查询的一种数据结构。它可以将数据表中的某一列或多列进行排序,以便快速查找数据,减少数据库的扫描次数,提高查询速度。

索引的优缺点

索引的优点是可以大幅度提高数据查询的速度,尤其是对于大型数据库。同时,索引可以保证数据的唯一性、减少冗余数据,提高数据库的完整性和安全性。然而,索引也有缺点。首先,索引需要占用一定的存储空间,如果建立了太多的索引,可能会占用过多的空间。其次,索引的更新和维护会增加数据库的开销,因此在一些写操作较多的场景下,索引会影响性能。

何时需要建立索引

在一些大型的数据表中,如果需要快速查询某些数据,可以通过建立索引来提高查询速度。通常情况下,对于经常查询、排序、分组的字段需要建立索引,这些字段包括主键、外键、经常用于查询的字段等。需要注意的是,建立索引不是无脑加,应该根据实际情况进行选择,过多的索引会浪费存储空间和影响性能。

何时不需要建立索引

  1. where 条件中用不到的字段不适合建立索引

  2. 表记录较少。比如只有几百条数据,没必要加索引。

  3. 需要经常增删改。需要评估是否适合加索引

  4. 参与列计算的列不适合建索引

  5. 区分度不高的字段不适合建立索引,如性别,只有男/女/未知三个值。加了索引,查询效率也不会提高。

索引的数据结构

常见的索引数据结构包括 B 树索引、B+树索引和 Hash 索引。其中,B 树索引是一种自平衡的多叉树结构,能够快速地查找数据,但是效率并不如 B+树索引。B+树索引是一种基于 B 树的索引结构,相对于 B 树索引,具有更高的查询效率和更好的存储能力。Hash 索引是一种利用哈希函数进行索引的结构,适用于等值查询和查询范围较小的场景,但是不支持范围查询和排序操作。

B+树索引

B+ 树是基于 B 树和叶子节点顺序访问指针进行实现,它具有 B 树的平衡性,并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中,节点中的 key 从左到右递增排列,如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。



进行查找操作时,首先在根节点进行二分查找,找到 key 所在的指针,然后递归地在指针所指向的节点进行查找。直到查找到叶子节点,然后在叶子节点上进行二分查找,找出 key 所对应的数据项。

Hash 索引和 B+树索引的区别

Hash 索引和 B+树索引在实现上有很大的区别。Hash 索引是通过哈希函数将索引列的值映射到一个哈希表中,然后在哈希表中查找对应的数据。这种方式可以快速定位到数据,但是对于范围查询和排序操作不太友好。而 B+树索引是一种多层平衡树结构,可以通过二分查找快速定位到数据。相对于 Hash 索引,B+树索引支持范围查询和排序操作,适用范围更广。

为什么 B+树比 B 树更适合实现数据库索引?

B+树相对于 B 树具有更好的存储和查询效率。首先,B+树的非叶子节点不保存数据,只保存关键字,因此可以容纳更多的关键字,从而减少树的高度,提高查询效率。其次,B+树的叶子节点通过指针连接形成了一个有序的链表,可以方便地进行范围查询和排序操作。此外,B+树对于磁盘 I/O 的利用率也更高,因为 B+树每次读取的都是整个节点,相对于 B 树更加高效。

索引的分类

索引可以根据不同的特征进行分类,常见的分类有聚集索引和非聚集索引、唯一索引和非唯一索引、单列索引和多列索引等。

最左匹配原则

最左匹配原则是指对于复合索引,查询时只能使用从左到右的部分列作为查询条件,不能跳过其中的列。例如,对于索引(a,b,c),只能按照(a)、(a,b)和(a,b,c)的顺序使用,不能仅使用(b,c)或(c)。

聚集索引

聚集索引是一种特殊的索引方式,它的索引顺序与物理顺序一致,即数据按照索引顺序进行存储。聚集索引可以有效地提高数据的查询速度,因为它可以直接定位到数据所在的物理位置。在 MySQL 中,每个表只能有一个聚集索引,通常是主键索引。

覆盖索引

覆盖索引是指查询语句可以通过索引直接获取所需的数据,而不必访问数据表,从而提高查询效率。例如,对于查询语句 SELECT id FROM table WHERE name='abc',如果表中存在(name,id)的复合索引,则查询可以直接在索引上完成,不必访问表中的其他列,从而提高查询速度。

索引的设计原则

在设计索引时,需要考虑以下几个原则:

  • 尽量选择区分度高的列作为索引列,以减少索引的重复率。

  • 尽量选择数据量小的列作为索引列,以减少索引的空间占用。

  • 尽量选择频繁查询的列作为索引列,以提高查询效率。

  • 避免使用过多的索引,因为过多的索引会增加维护成本,并且可能会影响写操作的性能。

  • 对于复合索引,需要根据最左匹配原则选择索引列。

  • 对于聚集索引,通常选择主键作为聚集索引。

  • 对于覆盖索引,需要根据查询语句的需求选择索引列。

索引什么时候会失效?

索引会失效的情况主要包括以下几种:

  • 查询条件中使用了函数或表达式,导致无法使用索引。

  • 查询条件中使用了不等于(<>)操作符或者不在(NOT IN)操作符,也可能导致无法使用索引。

  • 查询条件中使用了模糊匹配操作符(LIKE),如果匹配字符串以通配符开头,也可能导致无法使用索引。

  • 查询条件中使用了 OR 操作符,如果 OR 操作符的两侧都有可用的索引,则可以使用索引,否则无法使用索引。

  • 索引列上的数据类型与查询条件的数据类型不一致,导致无法使用索引。

  • 表中数据分布不均匀,导致索引失效。

什么是前缀索引?

前缀索引是指只对索引列的一部分进行索引,从而减少索引的空间占用。例如,对于 VARCHAR 类型的列,可以只对其前几个字符进行索引。前缀索引的缺点是精度不够高,可能会导致查询结果不准确。

用户头像

Java你猿哥

关注

一只在编程路上渐行渐远的程序猿 2023-03-09 加入

关注我,了解更多Java、架构、Spring等知识

评论

发布
暂无评论
MySQL面试八股文:索引篇_Java_Java你猿哥_InfoQ写作社区