2.MySQL面试题之索引

1. 为什么索引要用 B+树来实现呢，而不是 B 树？

MySQL 选择使用 B+ 树来实现索引，而不是 B 树，主要是基于以下几个原因：

1.1 数据存储和访问效率

• B 树：在 B 树中，数据和索引都存储在每个节点中。这意味着在进行范围查询时，可能需要访问大量节点，因为数据分散在不同的节点中。
• B+ 树：在 B+ 树中，所有数据都存储在叶子节点中，内部节点只存储索引。这使得范围查询更加高效，因为只需遍历叶子节点即可。此外，叶子节点通过链表相连，可以顺序访问，进一步提高了范围查询的效率。

1.2 磁盘 I/O 性能

• B 树：由于数据和索引混合存储，节点的大小不一致，导致磁盘页的利用率不高，增加了磁盘 I/O 操作的次数。
• B+ 树：所有叶子节点在同一层，且节点大小一致，磁盘页利用率更高，减少了磁盘 I/O 操作次数。内部节点只存储索引，节点更小，可以在一次磁盘 I/O 操作中加载更多的索引，提高了查询效率。

1.3 更高的平衡性和稳定性

• B 树：由于数据和索引混合存储，插入和删除操作可能导致频繁的节点分裂和合并，影响树的平衡性。
• B+ 树：数据只存储在叶子节点，插入和删除操作只影响叶子节点，内部节点只存储索引，减少了节点分裂和合并的频率，保持了树的平衡性和稳定性。

1.4 更高的查询性能

• B 树：在进行等值查询时，需要逐层查找，直到找到目标节点。
• B+ 树：由于内部节点只存储索引，查询路径更短，查询性能更高。对于范围查询，只需遍历叶子节点，进一步提高了查询效率。

1.5 更好的内存利用率

• B 树：节点大小不一致，内存利用率较低。
• B+ 树：节点大小一致，内存利用率更高，可以在内存中存储更多的索引，提高查询效率。

2. MySQL 中有哪些类型的索引？

• 主键索引（Primary Key）
• 唯一索引（Unique Key）
• 普通索引（Index）
• 全文索引（Fulltext Index）
• 组合索引（Composite Index）
  下面我们分别来介绍下各个索引的用法、作用、使用场景。
  首先，我们创建一个示例表 employees，包含员工的 id、name、department 和 salary 字段。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

2.1 普通索引

普通索引用于加速查询。我们在 name 列上创建一个普通索引：

CREATE INDEX idx_name ON employees(name);

2.2 唯一索引

唯一索引确保列中的所有值都是唯一的。我们在 department 列上创建一个唯一索引：

CREATE UNIQUE INDEX idx_department ON employees(department);

2.3 组合索引

组合索引用于加速多列组合查询。我们在 department 和 salary 列上创建一个组合索引：

CREATE INDEX idx_department_salary ON employees(department, salary);

2.4 使用索引进行查询

2.4.1 等值查询

SELECT * FROM employees WHERE name = 'John Doe';

索引 idx_name 会加速这个查询，因为它直接定位到 name 为 ‘John Doe’ 的记录。

2.4.2 范围查询

使用 salary 列上的索引进行范围查询：

SELECT * FROM employees WHERE salary > 50000;

如果 salary 列上有索引，这个查询会更快，因为索引可以快速定位到 salary 大于 50000 的记录。

2.4.3 组合索引查询

使用 department 和 salary 列上的组合索引进行查询：

SELECT * FROM employees WHERE department = 'Engineering' AND salary > 70000;

索引 idx_department_salary 会加速这个查询，因为它可以同时利用 department 和 salary 列上的索引。

2.5 查看索引

可以使用 SHOW INDEX 语句查看表上的索引：

SHOW INDEX FROM employees;

2.6 删除索引

可以使用 DROP INDEX 语句删除索引：

DROP INDEX idx_name ON employees;

2.7 执行计划分析

使用 EXPLAIN 语句分析查询的执行计划，查看索引的使用情况：

EXPLAIN SELECT * FROM employees WHERE name = 'John Doe';

3. 什么是聚簇索引和非聚簇索引？

• 聚簇索引：数据行的物理顺序与索引的逻辑顺序相同，主键索引通常是聚簇索引。
• 非聚簇索引：数据行的物理顺序与索引的逻辑顺序不同，其他类型的索引通常是非聚簇索引。