数据库面试：SQL优化与索引策略深度解析

一、索引原理与面试常见陷阱

在数据库面试中，索引几乎是必问题。许多面试者能背出“B+树”“覆盖索引”等名词，但一旦深入细节便漏洞百出。以下从底层结构和实际使用两个维度剖析。

1. B+树与哈希索引的区别

• B+树索引：所有数据存储在叶子节点，叶子之间通过双向链表连接，支持范围查询和排序。非叶子节点仅存储键值指针，因此树的高度较低（通常3~4层），适合磁盘IO场景。
• 哈希索引：通过哈希函数直接定位，等值查询效率O(1)，但无法用于范围查询（如 WHERE age > 18）和排序。Memory引擎默认使用哈希索引，InnoDB则自适应哈希索引（仅用于高频等值查询的优化）。

面试陷阱：提问“为什么MySQL用B+树而不是B树？”关键在于B+树非叶子节点不存数据，使得每个节点可存储更多索引键，降低树高；且叶子节点形成有序链表，便于范围扫描。

2. 联合索引的最左前缀原则

联合索引 (a, b, c) 只有在查询条件从最左列开始匹配时才能生效。例如：

SELECT * FROM t WHERE a = 1 AND b = 2;   -- 走索引
SELECT * FROM t WHERE b = 2 AND c = 3;   -- 不走索引（跳过了a）
SELECT * FROM t WHERE a = 1 AND c = 3;   -- 仅用a列索引，c列无法利用（但可通过索引下推优化）

面试中常问：“给 (name, age) 建了索引，WHERE age = 20 能用到吗？”答案：不能，因为跳过了最左列。

二、SQL优化实战：避免索引失效与慢查询

数据库面试中，考官常会给出一个慢SQL让你分析优化思路。以下为几个高频场景及对应策略。

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

-- 错误写法：对索引列使用函数
SELECT * FROM user WHERE MONTH(create_time) = 7;   -- 索引失效
-- 优化写法：使用范围查询
SELECT * FROM user WHERE create_time >= '2024-07-01' AND create_time < '2024-08-01';

同理，WHERE id + 1 = 5 也会导致索引失效，应改为 WHERE id = 4。

2. 隐式类型转换

当索引列类型为字符串，传入整型时，MySQL会隐式转换导致索引失效：

-- user_id 为 varchar，但传入数值
SELECT * FROM user WHERE user_id = 12345;    -- 索引失效
-- 正确写法
SELECT * FROM user WHERE user_id = '12345';

3. 分页优化（延迟关联）

当分页深度较大时（如 LIMIT 100000, 10），即使有索引也可能扫描大量无用行。可采用“延迟关联”先获取主键，再回表：

-- 低效：先回表再排序
SELECT * FROM order WHERE status = 1 ORDER BY id LIMIT 100000, 10;
-- 高效：先通过覆盖索引拿到id，再关联原表
SELECT a.* FROM order a 
INNER JOIN (SELECT id FROM order WHERE status = 1 ORDER BY id LIMIT 100000, 10) b 
ON a.id = b.id;

三、事务隔离级别与MVCC（重点加餐）

数据库面试中，事务隔离级别是区分初级与高级面试者的关键点。特别是 可重复读 下的幻读问题，以及InnoDB如何通过MVCC+间隙锁解决。

1. 四种隔离级别简表

2. MVCC原理简述

MVCC（多版本并发控制）通过每行记录的隐藏字段 DB_TRX_ID（事务ID）和 DB_ROLL_PTR（回滚指针）实现。在可重复读级别下，事务启动时创建一个 ReadView，记录当前活跃事务ID列表。查询时只读取已提交且小于ReadView最小ID的版本，从而实现快照读，避免不可重复读。

幻读：当使用当前读（SELECT ... FOR UPDATE）时，InnoDB会通过间隙锁（Gap Lock）锁定范围，防止其他事务插入新行。因此MySQL的可重复读实际解决了幻读问题。

掌握以上核心知识点，不仅能在数据库面试中脱颖而出，更能应用于实际生产环境的调优。建议读者结合 EXPLAIN 命令验证每条SQL的执行计划，做到理论与实践合一。