数据库相关面试题
数据库索引是用于加速数据查询的一种数据结构，类似于书籍的目录。它通过建立特定字段的快速访问路径，减少数据库全表扫描的开销，从而提升查询效率。核心作用提高查询速度：快速定位目标数据（如 [代码] 、 [代码] 条件）。强制数据唯一性：唯一索引确保字段值不重复（如主键）。排序优化：索引可避免排序操作的临时表生成（如 [代…
作者：TaotaoYao
数据库索引是用于加速数据查询的一种数据结构，类似于书籍的目录。它通过建立特定字段的快速访问路径，减少数据库全表扫描的开销，从而提升查询效率。
核心作用
- 提高查询速度：快速定位目标数据（如
WHERE、JOIN条件）。 - 强制数据唯一性：唯一索引确保字段值不重复（如主键）。
- 排序优化：索引可避免排序操作的临时表生成（如
ORDER BY）。
常见类型
- B-tree 索引：默认类型，支持等值查询和范围查询（如数字、字符串）。
- 哈希索引：仅支持精确匹配，查询效率极高（如内存表）。
- 全文索引：针对文本内容的关键词搜索优化（如
LIKE "%keyword%"）。 - 复合索引：基于多个字段组合，满足多条件查询。
优缺点
- 优点：显著加速查询，减少磁盘 I/O。
- 缺点：占用额外存储空间；增删改数据时需维护索引，影响写入性能。
设计原则
- 高频查询字段优先建索引。
- 区分度高的字段（如用户 ID）效果更佳。
- 避免对频繁更新的字段过度索引。
示例：为 users 表的 email 字段创建索引：
CREATE INDEX idx_email ON users(email);
聚集索引和非聚集索引的区别：
1. **物理存储**：聚集索引决定数据物理存储顺序，数据行按索引顺序排列；非聚集索引不改变数据物理顺序，索引与数据分开存储。
2. **数量限制**：一个表只能有一个聚集索引；可创建多个非聚集索引。
3. **结构组成**：聚集索引本身就是数据表；非聚集索引是独立结构，包含指向数据行的指针。
4. **查找效率**：聚集索引直接定位数据；非聚集索引需先查索引再访问数据，可能产生额外I/O。
5. **适用场景**：聚集索引适合范围查询；非聚集索引适合精确匹配查询。
MySQL选择B+树作为索引底层结构的核心原因：
- 矮胖树结构优化I/O
- 多路平衡特性使树高度远低于二叉树（3-4层可存储千万级数据）
- 单次查询减少磁盘I/O次数（节点大小匹配磁盘页）
- 数据存储结构优势
- 非叶子节点仅存索引（可容纳更多键值）
- 数据全存于有序叶子节点（链表连接相邻节点）
- 特殊适配场景
- 范围查询：直接遍历叶子节点链表即可
- 全表扫描：只需遍历所有叶子节点
- 排序查询：天然有序结构避免额外排序
- 对比淘汰方案
- 哈希表：无法支持范围查询
- 二叉树：树高导致I/O次数剧增
- B树：非叶节点存数据导致树更高，范围查询效率低
- 稳定查询性能
- 任何查询都需到叶子节点（稳定O(logn)复杂度）
- 避免B树因数据位置不同导致的性能波动
回表：当查询字段不在非聚集索引中时，数据库需要通过索引查找到主键，再通过主键回到聚集索引中获取完整数据行的过程。
核心要点
1. 触发条件：使用非聚集索引且查询字段未被索引完全覆盖
2. 执行过程（两步查找）：
- 通过非聚集索引查找目标记录的主键
- 通过主键在聚集索引中获取完整数据行
3. 性能影响：比直接使用聚集索引多一次索引查找（额外I/O）
示例说明
-- 创建索引
CREATE INDEX idx_email ON users(email);
-- 发生回表的查询（需获取未索引的name字段）
SELECT name FROM users WHERE email = '[email protected]';
执行过程：
1. 在idx_email索引树查找[email protected]对应的主键ID
2. 用该ID在聚集索引中查找完整的用户数据行
3. 从数据行中提取name字段返回
优化方案
- 覆盖索引：创建包含所需字段的复合索引
sql CREATE INDEX idx_email_name ON users(email, name);
- 索引设计：将查询频次高的字段包含进索引
最左前缀原则
核心定义
复合索引的生效规则：查询条件必须从索引最左侧列开始，且不跳过中间字段，才能触发索引使用。
匹配规则
- 连续左匹配：
- 索引
(A,B,C) - 有效：
WHERE A=?/WHERE A=? AND B=? - 无效：WHERE B=? / WHERE B=? AND C=?
- 断点失效（中间列缺失）： WHERE A=? AND C=? → 仅A列索引生效
- 范围查询截断： WHERE A>? AND B=? → B列不参与索引过滤
典型示例
-- 创建复合索引
CREATE INDEX idx_name_phone ON users(last_name, first_name, phone);
-- ✅ 触发索引的查询
SELECT * FROM users
WHERE last_name = '张'
AND first_name = '伟'; -- 连续使用前两列
-- ❌ 未触发索引的查询
SELECT * FROM users
WHERE first_name = '伟' -- 未从最左列开始
AND phone = '13800138000';
设计建议
- 高频列前置：将WHERE子句中最常出现的字段放左侧
- 范围查询右置：范围查询字段（如
>,<）尽量安排在索引末尾 - 避免冗余设计：索引
(A,B)与(A)功能重叠，可只保留前者
类比理解（电话簿模型）
- 索引
(姓氏, 名字, 城市)犹如电话簿排序：
必须先按姓氏查找 → 再按名字过滤 → 最后按城市筛选
直接按名字或城市查找无效
索引失效的常见情况
使用函数或表达式
对索引列使用函数或表达式会导致索引失效：
-- 失效：对索引列使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
-- 失效：对索引列进行计算
SELECT * FROM orders WHERE total_amount * 0.8 > 1000;
不等操作符
使用!=、<>或NOT等于操作符通常无法使用索引：
-- 失效
SELECT * FROM products WHERE price <> 100;
通配符查询
LIKE以通配符开头时索引失效：
-- 失效：前导通配符
SELECT * FROM articles WHERE title LIKE '%数据库%';
-- 有效：后置通配符
SELECT * FROM articles WHERE title LIKE '数据库%';
OR连接条件
OR连接的条件中有一个没有使用索引时，整个索引可能失效：
-- 失效：status字段无索引
SELECT * FROM users WHERE name = '张三' OR status = 'active';
类型不匹配
字符串和数字类型不匹配或隐式类型转换导致索引失效：
-- 失效：id为数字类型
SELECT * FROM orders WHERE id = '1001';
违反最左前缀原则
复合索引未从最左列开始查询或跳过中间列：
-- 索引为(last_name, first_name, phone)
-- 失效：未从最左列开始
SELECT * FROM users WHERE first_name = '伟';
NULL值查询
对索引列使用IS NULL/IS NOT NULL在某些数据库中索引效果有限：
-- 可能失效：大量NULL值
SELECT * FROM products WHERE category IS NULL;
负向查询
使用NOT、NOT LIKE、NOT IN等否定操作通常无法使用索引：
-- 失效
SELECT * FROM customers WHERE NOT city IN ('北京', '上海');
表达式运算
对索引列进行数学运算或函数调用：
-- 失效：create_date为索引列
SELECT * FROM orders WHERE YEAR(create_date) = 2023;
数据量小
表数据量过小，全表扫描可能比使用索引更快。