跳到主要内容

数据库索引原理

问题​

数据库索引的底层原理是什么？如何正确使用索引？

答案​

为什么需要索引​

没有索引时，查询需要全表扫描（逐行比较）。有索引后，通过数据结构（B+ 树）快速定位，将时间复杂度从 O(n)O(n) 降到 O(log⁡n)O(\log n)。

B+ 树结构​

B+ 树特点：

  • 非叶子节点只存索引，不存数据
  • 叶子节点通过链表连接（支持范围查询）
  • 树高通常 3-4 层，千万级数据也只需 3-4 次 IO

索引类型​

类型说明示例
主键索引聚簇索引，叶子存完整行PRIMARY KEY (id)
唯一索引值唯一UNIQUE INDEX (email)
普通索引最常用INDEX (name)
联合索引多列组合INDEX (city, age, name)
全文索引文本搜索FULLTEXT INDEX (content)

聚簇索引 vs 非聚簇索引​

最左前缀原则​

left-prefix.sql
-- 联合索引 INDEX(city, age, name)
-- 相当于创建了三个索引：
-- (city)
-- (city, age)
-- (city, age, name)

-- ✅ 能使用索引
SELECT * FROM users WHERE city = '北京';                    -- 最左列
SELECT * FROM users WHERE city = '北京' AND age > 20;       -- 前两列
SELECT * FROM users WHERE city = '北京' AND age = 25 AND name = 'Tom'; -- 全部

-- ❌ 不能使用索引
SELECT * FROM users WHERE age = 25;             -- 跳过了最左列 city
SELECT * FROM users WHERE age = 25 AND name = 'Tom';  -- 跳过了 city

-- ⚠️ 部分使用索引
SELECT * FROM users WHERE city = '北京' AND name = 'Tom';
-- 只能用 city 索引，name 无法使用（中间跳过了 age）

覆盖索引​

covering-index.sql
-- INDEX(name, age)

-- 覆盖索引：查询的列都在索引中，不需要回表
SELECT name, age FROM users WHERE name = 'Tom'; -- ✅ 直接从索引取

-- 非覆盖索引：需要回表查完整行
SELECT * FROM users WHERE name = 'Tom';  -- 需要回表取其他列
覆盖索引

覆盖索引是性能优化的利器。如果查询只需要索引中的列，就不需要回表，减少一次 IO。

常见面试问题​

Q1: 索引失效的常见场景？​

答案：

  1. 对索引列使用函数：WHERE YEAR(created_at) = 2024
  2. 隐式类型转换：WHERE phone = 13800138000（phone 是 VARCHAR）
  3. LIKE 左模糊：WHERE name LIKE '%Tom'
  4. OR 条件未全部建索引
  5. 使用 != 或 NOT IN
  6. 联合索引不满足最左前缀

Q2: 为什么用 B+ 树而不是哈希表？​

答案：

维度B+ 树哈希表
等值查询O(log⁡n)O(\log n)O(1)O(1)
范围查询✅ 叶子链表支持❌ 不支持
排序✅ 天然有序❌ 无序
模糊查询✅ 前缀匹配❌ 不支持

数据库多数场景需要范围查询和排序，所以 B+ 树更适合。

Q3: 索引越多越好吗？​

答案：

不是。索引的代价：

  1. 写性能下降：每次 INSERT/UPDATE/DELETE 都要维护索引
  2. 存储空间：索引占用磁盘空间
  3. 优化器选择困难：索引太多，优化器可能选错

建议：针对查询定向建索引，避免冗余索引。

Q4: 什么是索引下推（ICP）？​

答案：

MySQL 5.6+ 的优化。在联合索引中，将 WHERE 条件的判断下推到存储引擎层，在索引遍历时直接过滤，减少回表次数。

Q5: 如何判断查询是否使用了索引？​

答案：

使用 EXPLAIN 分析：

EXPLAIN SELECT * FROM users WHERE name = 'Tom';
-- 关注 type、key、rows、Extra 列
-- type = const/ref/range 表示用了索引
-- type = ALL 表示全表扫描

相关链接​