跳到主要内容

数据库索引原理

问题​

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

面试速答版

数据库索引的底层原理是什么？ MySQL InnoDB 用的是 B+ 树（不是 B 树、不是哈希）：

  • 非叶子节点只存索引键，叶子节点存完整行数据（或主键），叶子之间用双向链表连起来，所以范围查询很快。
  • 树高通常只有 34 层，千万级数据查找也只要 34 次磁盘 IO，时间复杂度从 O(n) 降到 O(log n)。
  • 聚簇索引：主键索引，叶子直接存行数据；非聚簇索引（二级索引）：叶子存主键，需要「回表」再查一次主键索引。

怎么正确用索引？ 关键就一条：最左前缀原则 + 避免索引失效：

  • 联合索引 (a,b,c) 只在 WHERE a= / WHERE a= AND b= 这类从左往右连续匹配时生效。
  • 覆盖索引：SELECT 的列都在索引里，不用回表，性能最好。
  • 索引失效场景：在索引列上做函数/运算、LIKE '%xxx'（前缀模糊）、隐式类型转换、OR 中有非索引列。
  • 不是越多越好：写入会变慢、占空间，热点字段（高频查询条件、JOIN 关联键）才加。

答案​

为什么需要索引​

没有索引时，查询需要全表扫描（逐行比较）。有索引后，通过数据结构（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 表示全表扫描

相关链接​