数据库面试知识点（二）索引 蔚落 2021-09-29 09:26 288阅读 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 的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。 根本区别： 聚集索引和非聚集索引的**根本区别是表记录的排列顺序和与索引的排列顺序**是否一致。
还没有评论，来说两句吧...