数据库面试知识点（二）索引

蔚落 2021-09-29 09:26 343阅读 0赞

### 1. 什么是索引？ ###

数据库索引，是数据库管理系统中一个**排序的数据结构**，索引的实现通常使用 B 树及其变种 B + 树。

在数据之外，数据库系统还维护着**满足特定查找算法的数据结构**，这些数据结构以某种方式引用（指向）数据，这样就可以在这些**数据结构上实现高级查找算法**。这种数据结构，就是索引。

--------------------

### 2. 索引的作用？它的优点缺点是什么？ ###

索引作用：

*  协助快速查询、更新数据库表中数据。

为表设置索引要付出代价的：

*  增加了数据库的存储空间
 *  在插入和修改数据时要花费较多的时间 (因为索引也要随之变动)。

--------------------

### 3. 索引的优缺点？ ###

**创建索引可以大大提高系统的性能（优点）**：

1.  通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.  可以大大**加快数据的检索速度**，这也是创建索引的最主要的原因。
3.  可以**加速表和表之间的连接**，特别是在实现数据的参考完整性方面特别有意义。
4.  在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

**增加索引也有许多不利的方面 (缺点)**：

1.  **创建索引和维护索引要耗费时间**，这种时间随着数据量的增加而增加。
2.  索引需要**占物理空间**，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.  当对表中的数据进行**增加、删除和修改的时候，索引也要动态的维护**，这样就降低了数据的维护速度。

--------------------

### 4. 哪些列适合建立索引、哪些不适合建索引？ ###

索引是**建立在数据库表中的某些列**的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引：

（1）在**经常需要搜索的列**上，可以加快搜索的速度；

（2）在**作为主键的列**上，强制该列的唯一性和组织表中数据的排列结构；

（3）在**经常用在连接**的列上，这些列主要是一些外键，可以加快连接的速度；

（4）在经常需要**根据范围进行搜索**的列上创建索引，因为索引已经排序，其指定的范围是连续的；

（5）在经常需要**排序的列**上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

（6）在经常使用在 **WHERE 子句中的列**上面创建索引，加快条件的判断速度。

对于有些列不应该创建索引：

（1）对于那些在查询中很少使用或者参考的列不应该创建索引。

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

（2）对于那些只有很少数据值的列也不应该增加索引。

这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

（3）对于那些定义为 **text, image 和 bit 数据类型**的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。

(4) 当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

--------------------

### 5. 什么样的字段适合建索引 ###

唯一、不为空、经常被查询的字段

### 6.MySQL B+Tree 索引和 Hash 索引的区别？ ###

Hash 索引和 B + 树索引的特点：

*  Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以**一次定位**；
 *  B + 树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的 IO 访问；

为什么不都用 Hash 索引而使用 B + 树索引？

*  Hash 索引**仅仅能满足 “=”,“IN” 和 “” 查询**，不能使用范围查询，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和 Hash 运算前完全一样；
 *  Hash 索引**无法被用来避免数据的排序操作**，因为 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样；
 *  Hash **索引不能利用部分索引键查询**，对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用；
 *  Hash 索引在任何时候都**不能避免表扫描**，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要回表查询数据；
 *  Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B + 树索引高。

**补充：**

1.  MySQL 中，只有 HEAP/MEMORY 引擎才显示支持 Hash 索引。
2.  常用的 **InnoDB 引擎中默认使用的是 B + 树索引**，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的 “自适应哈希索引缓冲区” 建立哈希索引（在 InnoDB 中默认开启自适应哈希索引），通过观察搜索模式，MySQL 会利用 index key 的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。  
    B + 树索引和哈希索引的明显区别是：
3.  如果是**等值查询，那么哈希索引明显有绝对优势**，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
4.  如果是**范围查询检索，这时候哈希索引就毫无用武之地了**，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；  
    同理，哈希索引没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
5.  哈希索引也不支持多列联合索引的最左匹配规则；
6.  B + 树索引的关键字检索效率比较平均，不像 B 树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
7.  在大多数场景下，都会有范围查询、排序、分组等查询特征，用 B + 树索引就可以了。

--------------------

### 7.B 树和 B + 树的区别 ###

B 树，每个节点都存储 key 和 data，所有节点组成这棵树，并且叶子节点指针为 nul，叶子结点不包含任何关键字信息。

B + 树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而 B 树的非终节点也包含需要查找的有效信息)

--------------------

### 8.为什么说 B + 比 B 树更适合实际应用中操作系统的文件索引和数据库索引？ ###

1.B + 的磁盘读写代价更低

B + 的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

2.B+tree 的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

--------------------

### 9. 聚集索引和非聚集索引区别？ ###

聚合索引 (clustered index):

**聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快**，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。**聚集索引对应的缺点就是修改慢**，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。  
聚集索引类似于新华字典中用拼音去查找汉字，拼音检索表于书记顺序都是按照 a~z 排列的，就像相同的逻辑顺序于物理顺序一样，当你需要查找 a,ai 两个读音的字，或是想一次寻找多个傻 (sha) 的同音字时，也许向后翻几页，或紧接着下一行就得到结果了。

非聚合索引 (nonclustered index):

**非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用 B + 树结构，非聚集索引的叶子层并不和实际数据页相重叠**，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，**不会造成数据重排**。  
非聚集索引类似在新华字典上通过偏旁部首来查询汉字，检索表也许是按照横、竖、撇来排列的，但是由于正文中是 a~z 的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。

根本区别：

聚集索引和非聚集索引的**根本区别是表记录的排列顺序和与索引的排列顺序**是否一致。