数据库索引优化面试：B+树原理与SQL调优实战

在数据库面试中，索引优化是必考的核心知识点。无论是校招还是社招，面试官都会深入考察候选人对B+树原理的理解、索引设计能力以及SQL调优经验。

为什么索引如此重要？

想象一下，如果一本字典没有目录和索引，你要查找"数据库"这个词，只能从第一页翻到最后一页——这就是全表扫描的效率。而有了索引，就像有了字典的拼音索引，你可以快速定位到目标内容。

在数据库中，索引的作用正是如此：

查询加速：将O(n)的全表扫描优化为O(log n)的索引查找
排序优化：利用索引的有序性避免filesort
唯一约束：通过唯一索引保证数据完整性

B+树：数据库索引的基石

B+树的结构特点

B+树（B+ Tree）是大多数关系型数据库索引的底层数据结构。

B+树的核心特点：

所有数据存储在叶子节点：非叶子节点只存储键值和指针
叶子节点形成有序链表：便于范围查询和排序
节点大小与磁盘页对齐：通常为16KB，减少磁盘I/O
高度较低：通常3-4层即可存储千万级数据

为什么选择B+树而非B树？

这是面试中的经典问题：

更适合磁盘存储：

B+树非叶子节点不存储数据，单页能存储更多键值，树更矮胖
B树每个节点都存储数据，单页键值少，树更高，I/O次数多

范围查询效率更高：

B+树叶子节点形成链表，范围查询只需遍历链表
B树需要中序遍历整棵树，效率较低

聚簇索引与非聚簇索引

聚簇索引

聚簇索引将数据行和索引存储在一起，一张表只能有一个聚簇索引。

MySQL InnoDB的聚簇索引特点：

主键自动成为聚簇索引
如果没有主键，选择第一个非空唯一索引
叶子节点存储完整的行数据

非聚簇索引（二级索引）

非聚簇索引的叶子节点存储的是索引键值 + 主键值，需要回表查询完整数据。

回表的代价：每次回表都是一次随机I/O，当查询大量数据时，回表开销可能超过全表扫描。

覆盖索引：避免回表的利器

当查询的所有字段都在索引中时，无需回表，直接从索引获取数据。

-- 创建联合索引
CREATE INDEX idx_email_name ON users(email, name);

-- 覆盖索引查询
SELECT email, name FROM users WHERE email = '[email protected]';
-- 不需要回表

索引优化策略

最左前缀原则

联合索引遵循最左前缀原则，查询必须从索引的最左列开始匹配。

-- 联合索引 (a, b, c)
CREATE INDEX idx_abc ON table_name(a, b, c);

-- ✅ 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 无法使用索引
WHERE b = 2
WHERE c = 3

避免索引失效的场景

1. 对索引列使用函数或运算

-- ❌ 索引失效
WHERE YEAR(create_time) = 2024

-- ✅ 索引有效
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'

2. 隐式类型转换

-- 假设id是INT类型
-- ❌ 索引失效
WHERE id = '100'

-- ✅ 索引有效
WHERE id = 100

3. LIKE以通配符开头

-- ❌ 索引失效
WHERE name LIKE '%张'

-- ✅ 索引有效
WHERE name LIKE '张%'

高频面试题解析

Q1：什么情况下应该建索引？

WHERE条件频繁使用的列
JOIN关联的列
ORDER BY或GROUP BY的列
区分度高的列（唯一性高）

Q2：什么情况下不建索引？

表数据量很小
频繁更新的列
区分度低的列（如性别）
很少用于查询的列

Q3：如何分析慢查询？

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';