精选数据库“索引“常见30道面试题！面试前冲刺

1、什么是数据库索引？

  数据库索引是一种数据结构，用于加速数据库中数据的检索操作。它类似于书中的目录，可以帮助数据库系统快速定位并访问存储在表中的数据。索引可以在数据库表的一列或多列上创建，以提高特定查询的执行效率。

以下是一个简单的MySQL数据库表创建示例，其中包括一个索引的使用：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

-- 在 "last_name" 列上创建索引
CREATE INDEX idx_last_name ON employees (last_name);

  在这个例子中，我们创建了一个名为 “employees” 的表，用于存储员工信息。表包含 “id”、“first_name”、“last_name”、“department” 和 “salary” 等列。然后，我们通过在 “last_name” 列上创建索引来加速对 “employees” 表的查询操作。

  这个索引将会建立一个按照 “last_name” 列的值排序的数据结构，使得数据库可以更快速地定位具有特定 “last_name” 值的记录。

以下是一个简单的查询示例，演示了如何使用索引进行数据检索：

-- 查询姓氏为 "Smith" 的员工数量
SELECT COUNT(*) FROM employees WHERE last_name = 'Smith';

  在此查询中，由于我们在 “last_name” 列上创建了索引，数据库系统可以通过索引快速定位所有姓氏为 “Smith” 的员工记录，从而提高查询效率。数据库引擎将不需要扫描整个表，而是只需查找索引中匹配的条目，然后返回结果。

2、为什么使用数据库索引？

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

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

以下是一个示例，展示了如何在MySQL数据库中创建索引，以及如何使用索引来加速数据检索：

-- 创建一个示例表
CREATE TABLE students (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    grade VARCHAR(10)
);

-- 在 "last_name" 列上创建索引
CREATE INDEX idx_last_name ON students (last_name);

-- 插入一些示例数据
INSERT INTO students (id, first_name, last_name, age, grade)
VALUES
    (1, 'John', 'Smith', 18, 'A'),
    (2, 'Jane', 'Doe', 20, 'B'),
    (3, 'Michael', 'Johnson', 19, 'A'),
    (4, 'Emily', 'Williams', 21, 'C');

-- 查询姓氏为 "Smith" 的学生数量
SELECT COUNT(*) FROM students WHERE last_name = 'Smith';

  在这个示例中，我们创建了一个名为 “students” 的表，其中包含学生的信息。然后，我们在 “last_name” 列上创建了索引。最后，我们查询姓氏为 “Smith” 的学生数量。由于我们在 “last_name” 列上创建了索引，数据库可以利用索引快速定位匹配的记录，从而提高查询效率。

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

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

1. 聚集索引：

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

2. 非聚集索引：

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

以下是一个简单的示例，演示了聚集索引和非聚集索引的区别：

-- 创建一个示例表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 在 "customer_id" 列上创建非聚集索引
CREATE INDEX idx_customer_id ON orders (customer_id);

-- 查询特定顾客的订单数量
SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE customer_id = 1;

  在这个示例中，我们创建了一个名为 “orders” 的表，用于存储订单信息。然后，我们在 “customer_id” 列上创建了一个非聚集索引。最后，我们查询了顾客ID为1的订单数量。由于我们创建了非聚集索引，数据库可以使用该索引快速定位匹配的记录，从而提高查询效率。

4、什么是唯一索引？

  唯一索引是一种保证索引列中的值都是唯一的索引。它可以用于加速查找操作，同时确保列中的值不会重复。在多个行中，索引列的值必须是唯一的，允许一个空值。

示例：

-- 在 "email" 列上创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

5、什么是主键索引？

  主键索引是一种特殊的唯一索引，用于唯一标识数据表中的每一行数据。主键索引的值必须是唯一的且不能为空。在大多数数据库管理系统中，主键索引会自动创建唯一性约束。

示例：

-- 在 "id" 列上创建主键索引
ALTER TABLE products ADD PRIMARY KEY (id);

6、什么是外键索引？

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

示例：

-- 在 "order_customer_id" 列上创建外键索引，引用 "customers" 表的 "id" 列
ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (order_customer_id) REFERENCES customers(id);

7、什么是覆盖索引（Covering Index）？

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

示例：

-- 在 "title" 列和 "author" 列上创建覆盖索引
CREATE INDEX idx_covering ON books (title, author);
-- 查询只使用了索引而不需要访问数据表
SELECT title, author FROM books WHERE title = 'The Great Gatsby';

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

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

示例：

-- 在 "last_name" 列和 "first_name" 列上创建复合索引
CREATE INDEX idx_name ON employees (last_name, first_name);
-- 查询只使用了索引的最左前缀
SELECT last_name, first_name FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

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

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

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

10、什么是索引选择性？

  索引选择性是指索引列中不同值的唯一性比例。高选择性的索引通常对于查询性能的提升更为有效，因为查询结果更容易被过滤出来，减少了需要检索的数据行数。选择性的范围通常从0到1，越接近1表示越高的选择性。

11、什么是索引碎片化？

  索引碎片化是指索引文件中的数据页或块不是连续存储的，而是被分散在磁盘上。这可能导致查询性能下降，因为数据库引擎需要进行更多的磁盘I/O操作来获取数据。定期进行索引维护操作，如重建或重新组织索引，可以减少碎片化。

12、如何优化索引性能？

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

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

13、什么是全文索引？

  全文索引是一种用于在文本数据中进行全文搜索的索引。与传统索引只匹配精确值不同，全文索引允许模糊搜索和匹配关键字、短语、同义词等。全文索引在处理大量文本数据的应用中非常有用，例如搜索引擎、博客、新闻网站等。

14、什么是B树索引？

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

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

15、什么是B+树索引？

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

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

以下是Python代码示例来展示B树和B+树的基本结构：

# B树节点
class BTreeNode:
    def __init__(self, leaf=True):
        self.keys = []
        self.children = []
        self.leaf = leaf

# B+树节点
class BPlusTreeNode:
    def __init__(self, leaf=True):
        self.keys = []
        self.values = []
        self.next = None
        self.parent = None
        self.leaf = leaf

16、什么是哈希索引？

  哈希索引使用哈希函数将索引列的值映射到存储桶，以加速数据检索。但是，哈希索引对于范围查询和排序等操作效果较差，适用于等值查询。哈希索引的典型应用是在内存数据库中，以快速检索数据。

17、什么是自适应索引？

  自适应索引是指根据查询模式自动调整索引结构的概念。它可以根据查询频率和类型，动态地选择适合的索引类型，以提供更好的查询性能。这样的索引可以减少人工干预，自动优化查询性能。

18、什么是倒排索引（Inverted Index）？

  倒排索引是一种用于全文搜索的索引结构，用于关键词检索。它将文档中的单词映射到出现该单词的文档列表。这种索引结构允许快速查找包含特定关键字的文档，常用于搜索引擎和文本检索系统中。

19、什么是索引优化器？

  索引优化器是数据库管理系统中的一部分，负责选择最优索引以及执行查询的计划。当你提交一个查询时，数据库系统会通过索引优化器来决定如何使用现有索引、哪些索引适合查询，以及如何在查询计划中选择合适的操作顺序。

20、什么是索引覆盖扫描？

  索引覆盖扫描指的是查询可以直接从索引中获取所需数据，而不需要回到数据表中查找。当查询只需要索引中包含的列时，数据库可以通过索引覆盖扫描来避免额外的数据表访问，从而提高查询性能。

以下是一个简单的代码示例，演示索引覆盖扫描的概念：

-- 创建一个示例表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- 在 "name" 列上创建索引
CREATE INDEX idx_name ON products (name);

-- 查询所有产品的名称和价格，只使用索引
SELECT name, price FROM products WHERE name = 'Widget';

21、什么是索引下推（Index Pushdown）？

  索引下推是指数据库优化器在执行查询时，尽量将过滤条件推到索引层面进行筛选，从而减少不必要的数据访问。这意味着，只有满足查询条件的数据行会被检索到，而不是检索所有数据后再进行过滤。

22、数据库中的NULL值如何处理索引？

  对于大多数数据库系统，NULL值在索引中被处理为独立的一组。当在索引中包含NULL值时，它们会被存储在一个单独的位置，以便可以进行NULL值的查询。因此，在查询中包含或排除NULL值时，索引可以起到作用。

23、索引的缺点是什么？

索引可以提高查询性能，但也有一些缺点：

• 索引会占用存储空间，尤其是对于大型数据表。
• 插入、更新和删除操作需要维护索引，可能会影响性能。
• 过多的索引可能导致查询优化器选择不恰当的索引，影响性能。
• 范围查询和排序操作可能受到索引的限制。

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

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

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

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

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

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

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

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

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

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

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

-- 创建一个复合索引
CREATE INDEX idx_name_age ON persons (last_name, age);

-- 查询可以使用索引
SELECT * FROM persons WHERE last_name = 'Smith';
SELECT * FROM persons WHERE last_name = 'Smith' AND age = 30;

-- 查询无法使用索引（不符合最左前缀原则）
SELECT * FROM persons WHERE age = 30;

28、如何监控和调优索引性能？

监控和调优索引性能是确保数据库查询性能的关键。一些方法包括：

• 使用数据库管理系统提供的性能监控工具来分析查询执行计划和索引使用情况。
• 定期检查索引的碎片化情况，进行必要的维护操作，如重建或重新组织索引。
• 根据查询需求和访问模式，考虑增加、删除或修改索引。
• 使用覆盖索引来减少磁盘I/O操作。
• 监控查询执行时间，识别慢查询，进行适当的索引优化。

29、索引和分区表之间有什么联系？

  索引和分区表是两种优化数据库性能的方法，它们可以结合使用。分区表将大表拆分为更小的分区，每个分区可以有自己的索引，从而提高查询性能。分区表的分区键通常也可以作为索引的列，以进一步加速特定分区的查询操作。

30、什么是索引的选择性（Selectivity），以及它如何影响查询性能？

  索引的选择性是指索引列不同值的唯一性比例。选择性越高，索引在查询时的过滤效果越好。高选择性索引可以大大减少需要检索的数据行数，提高查询性能。相反，低选择性索引可能导致查询优化器选择全表扫描而不使用索引。选择性影响了查询计划的选择，从而直接影响查询性能。

以下是一个简单的代码示例来说明索引的选择性对查询性能的影响：

-- 创建一个示例表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

-- 在 "order_date" 列上创建索引
CREATE INDEX idx_order_date ON orders (order_date);

-- 查询一个特定日期范围内的订单
-- 高选择性的索引可以减少检索的行数
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

  在上述示例中，如果 “order_date” 列的索引选择性较高，查询将只需要检索特定日期范围内的订单，从而提高查询性能。如果选择性较低，查询优化器可能会选择全表扫描，降低查询性能。