数据库基础面试——索引_索引是什么?有什么作用以及优缺点?面试题-CSDN博客

本文链接：https://blog.csdn.net/zhendong825/article/details/134235811

1.索引了解吗？简单介绍一下

索引是一种用于快速查询和检索数据的数据结构。

索引的作用

数据库中数据存储在磁盘，磁盘的顺序查询速度是很慢的，所以的顺序查找指的是读取磁盘中的数据⼀条⼀条的进⾏查找。索引就是为了避免顺序查询，提高查询速度的。

数据库索引是一种数据结构，用于加速数据库中数据的检索操作。它类似于书中的目录，可以帮助数据库系统快速定位并访问存储在表中的数据。

为什么使用数据库索引？

使用数据库索引有几个重要的理由：

1. 加速数据检索：索引可以显著提高数据库查询的速度，特别是在大型表中进行检索操作时，因为索引使数据库引擎能够更快速地定位所需的数据。
2. 减少数据读取量：通过使用索引，数据库引擎只需读取索引中的元数据，而不是整个表的数据，从而减少了读取的数据量，提高了查询性能。
3. 优化排序和分组：索引可以使数据库在进行排序和分组操作时更加高效，因为数据库可以利用索引的排序顺序来避免额外的排序操作。
4. 加速连接操作：当在多个表之间进行连接查询时，索引可以提高连接操作的性能，因为它们可以帮助数据库引擎快速定位连接条件匹配的数据。

介绍一下索引优缺点及使用场景

优点

创建索引可以提高系统的性能，具体体现在以下几个方面：

（1）通过创建唯一索引时，可以确保每一行数据的唯一性
（2）当创建索引后，可以大大提高检索的速度，同时在分组和对数据排序后使用索引，可以减少排序和分组的时间
（3）在表和表之间连接时，在保证数据的完整性有很大作用，另外通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点

(1)创建索引和维护索引需要耗费许多时间。插入、更新和删除操作需要维护索引，可能会影响性能。索引创建后，它的维护成本很高，因为它所使用的是B+树，每次在添加删除时，都会整理树结构，将这些操作写入到索引文件中
(2)索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件
(3)过多的索引可能导致查询优化器选择不恰当的索引，影响性能。创建过多的索引，对优化器也会造成很多负担

（4）范围查询和排序操作可能受到索引的限制。

使用场景

(1)当数据库中的数据量比较大时，可以创建
(2)创建索引去查询经常用到的列

注意事项
(1)索引会占用额外的磁盘空间，或磁盘空间不足时，不考虑创建索引,如果是不常使用的列，或经常做插入、修改操作的列，不适合加索引
(2)不要在线上环境去创建索引，也就是再有用户在使用此系统时，创建索引会锁表，线上数量大会导致其他所有接口不可用，导致系统性崩溃

如何优化索引性能？

优化索引性能的方法包括：

选择合适的索引：根据查询需求，选择创建适当的索引，避免创建过多或不必要的索引。
考虑索引顺序：复合索引的顺序对于查询性能影响很大，应根据查询频率和过滤条件进行选择。
定期维护索引：定期重建或重新组织索引，减少碎片化，保持查询性能。
考虑覆盖索引：创建覆盖索引，以避免回表操作，减少磁盘I/O。
监控性能：使用性能监控工具，分析查询执行计划，找出潜在的索引性能问题。

索引分类

(1)主键索引：(聚簇/聚集索引）⼀种特殊的唯⼀索引，用于唯一标识数据表中的每一行数据，不允许有空值，⼀般是在建表的时候同时创建主键索引（通过 primary key）

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
(2)非主键索引：(二级索引又称为辅助索引）除了主键索引之外的索引，唯一索引，普通索引，前缀索引等索引属于二级索引。二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
(3)唯一索引：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
(4)普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。外键索引也是
(5)前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

什么是复合索引（Composite Index）？

复合索引是基于多个列的索引，它可以包含两个或更多列的值。复合索引可以在多个列上加快联合查询的性能，但需要注意选择索引列的顺序，因为复合索引只能在索引列的最左前缀部分进行匹配。

什么是最左前缀原则（Leftmost Prefix Rule）？

最左前缀原则是指复合索引中的多个列可以从左到右按顺序使用，但不能跳过中间的列。例如，如果有一个复合索引 (A, B, C)，那么查询中可以只使用列 A 或者使用列 A 和 B，但不能只使用列 B 或只使用列 C。这个原则影响复合索引在查询中的有效性。

什么是外键索引？

外键索引是用于连接两个数据表的索引，它在一个表中创建，指向另一个表的主键列。外键索引用于维护表之间的关系完整性，确保一个表中的外键值必须存在于另一个表的主键中。

聚簇索引和二级索引的区别

1.聚簇索引存储的是行数据，也就是对应的地址，只要查到相对应的id，就可以查到主键对应的行数据，但二级索引中非叶子节点存储的是二级索引的值，叶子节点存储的是主键id

2.执行效率：聚簇索引查询速度更快，因为聚簇索引存储的是数据，而非聚簇索引存储的是主键 ID，需要进⾏回表查询，性能低
3.数量上：聚簇索引⼀个表只能有⼀个，⽽⾮聚簇索引可以有多个

解释一下聚集索引和非聚集索引的区别。

聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是数据库中两种不同类型的索引，它们在物理存储和查询性能方面有着不同的工作方式和影响。

1. 聚集索引：

聚集索引定义了表的物理存储顺序。表中的数据行按照聚集索引的顺序进行存储。一个表只能有一个聚集索引，通常是主键。
当你创建聚集索引时，实际上重新组织了表中的数据，以便它们按照索引的顺序进行存储。因此，表的物理存储顺序与聚集索引的顺序一致。
聚集索引可以提高范围查询的性能，因为相关数据在物理上相邻存储，但插入和更新操作可能会影响性能，因为数据的物理位置可能需要调整。

2. 非聚集索引：

非聚集索引是独立于数据行的附加数据结构，用于加速数据的查找和检索。一个表可以有多个非聚集索引。
非聚集索引中存储了索引的键值及其对应的指向实际数据行的指针，因此查询时需要先通过索引找到数据行的位置，然后再获取实际数据。
非聚集索引适用于提高查找性能，但对于范围查询的性能可能相对较差。

聚集索引

聚集索引即索引结构和数据（行数据）一起存放的索引。主键索引属于聚集索引。

聚集索引的优点:

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点

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

非聚集索引
非聚集索引即索引结构和数据（行数据）分开存放的索引。二级索引属于非聚集索引。

非聚集索引的优点

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

非聚集索引的缺点

跟聚集索引一样，非聚集索引也依赖于有序的数据
可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚集索引一定回表查询吗?

非聚集索引不一定回表查询。如果查询的字段刚好是索引字段(即覆盖索引)，不需要回表查询。

什么是覆盖索引

覆盖索引需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

覆盖索引是一种特殊的索引，它包含了查询中所需的所有列，从而使查询可以直接从索引中获取所需的数据，而不需要回到数据表中查找。这可以大大提高查询性能，减少I/O操作。

索引和约束的区别

(1)定义和作用不同：约束是保证数据的可靠性的；索引是加速查询的，不是⼀回事
(2)可以相互创建：当创建了主键、外键、唯⼀约束也就创建了相应的索引；当创建了唯⼀索引，也就创建了唯⼀约束

什么样的字段适合创建索引？

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

索引选择注意事项

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

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

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

4.考虑在字符串类型的字段上使用前缀索引代替普通索引

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

如何在大数据量表中选择适当的索引？

在大数据量表中选择适当的索引需要考虑查询的类型、频率和数据模式。一般原则是：

选择经常使用于查询的列作为索引列。
考虑复合索引，但不要过多。
对于范围查询，如排序和范围过滤，考虑使用覆盖索引。
监控查询性能，使用数据库性能分析工具找出潜在问题。

什么时候应该避免使用索引？

应避免使用索引的情况包括：

数据表很小：对于小数据表，索引可能不会带来明显的性能提升，而且会增加维护成本。
频繁的数据修改：如果表中的数据频繁地进行插入、更新或删除操作，索引维护会增加系统开销。
查询性能不重要：在一些特定场景中，查询性能可能不是首要考虑因素，而是数据的写入速度。

如何在插入大量数据时维护索引的性能？

在插入大量数据时，可以采取以下措施来维护索引性能：

关闭自动提交，使用批量插入事务，减少提交次数。
使用延迟索引维护，即先插入数据再创建索引。
考虑禁用或延迟非必要的索引，在大批量插入之后再重新创建。

如何在删除或更新操作时处理索引维护？

在删除或更新操作时，可以采取以下策略来处理索引维护：

对于大批量操作，可以先禁用索引，执行操作，然后重新创建索引。
使用延迟索引维护，在删除或更新操作后，再进行索引维护。

MySQL InnoDB 为什么设计B+树，而不是B树，Hash，二叉树，红黑树？

B+树所有的数据行都存储在叶节点中，而这些叶节点可以通过指针依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间。

（1）哈希索引能够以 O(1) 的速度处理单个数据行的增删改查，哈希算法有个 Hash 冲突问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是链地址法。链地址法就是将哈希冲突数据存放在链表中，为了减少链表过长的时候搜索时间过长引入了红黑树。

Hash 索引不支持顺序和范围查询是它最大的缺点：假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。面对范围查询或者排序时就会导致全表扫描的结果。
（2）B树可以在非叶结点中存储数据，由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，造成性能下降。

（3）二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

（4）红黑树：树的高度随着数据量增加而增加，IO代价高。

什么是B树索引？

B树（Balanced Tree）是一种自平衡搜索树，用于在数据库中建立索引。它具有以下特点：

每个节点可以有多个子节点，通常称为阶（order）。
所有叶子节点位于相同的层级，使得树保持平衡。
节点中的键值按照顺序排列。
B树可以支持范围查询、等值查询等多种查询操作。

什么是B+树索引？

B+树是在B树基础上发展而来的索引结构，在数据库中广泛应用。B+树与B树的不同之处在于：

所有键值都在叶子节点上，内部节点只存储索引信息。
叶子节点通过链表连接，使范围查询更加高效。
B+树更适合范围查询和顺序遍历等操作，常用于数据库索引。

B 树& B+树两者有何异同呢？

节点作用差异：B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
叶子节点差异：B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
检索效率差异：B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

什么是索引选择性？

索引的选择性是指索引列不同值的唯一性比例。选择性越高，索引在查询时的过滤效果越好。高选择性索引可以大大减少需要检索的数据行数，提高查询性能。相反，低选择性索引可能导致查询优化器选择全表扫描而不使用索引。选择性影响了查询计划的选择，从而直接影响查询性能。高选择性的索引通常对于查询性能的提升更为有效，因为查询结果更容易被过滤出来，减少了需要检索的数据行数。选择性的范围通常从0到1，越接近1表示越高的选择性。