MySQL 索引小白面试详解

优点	缺点
大幅提升查询速度	占用额外存储空间
加速排序和分组	增删改时需要维护索引，降低写入速度
保证数据唯一性（唯一索引）	创建和维护索引需要时间

二、索引数据结构（重点中的重点）

2.1 为什么不用 Hash 索引

Hash 索引的原理是将 key 通过哈希函数映射到桶中：

hash("john") → bucket[3]
hash("jane") → bucket[7]

特性	Hash 索引	B+Tree 索引
精确匹配	✅ O(1)，极快	✅ O(log n)
范围查询	❌ 不支持	✅ 支持
排序	❌ 不支持	✅ 支持
最左前缀	❌ 不支持	✅ 支持
Hash 冲突	⚠️ 性能下降	无此问题

结论：MySQL 选择 B+Tree 作为默认索引结构，因为实际业务中范围查询和排序太常见了。

2.2 B+Tree 结构

MySQL InnoDB 的 B+Tree 结构：

                    [15 | 30]              ← 根节点（只有索引键值）
                   /      \
        [10 | 12 | 15]  [20 | 25 | 30]    ← 非叶子节点（只有索引键值）
         /   |    \       /   |    \
      [10] [12] [15]   [20] [25] [30]     ← 叶子节点（存完整数据/主键）
        ←———————— 链表连接 ————————→        ← 叶子节点双向链表

B+Tree 的核心特点：

特点	说明
所有数据在叶子节点	非叶子节点只存索引键，能容纳更多索引项，树更矮
叶子节点用链表连接	范围查询时，找到起始点后沿链表顺序扫描即可
树高度很低	三层 B+Tree 可存 2000 万+ 数据，每次查询只需 3 次磁盘 IO

2.3 为什么不用 B-Tree

B-Tree：每个节点都存数据 → 节点大 → 每页存的索引少 → 树更高 → IO 更多
B+Tree：只有叶子节点存数据 → 节点小 → 每页存的索引多 → 树更矮 → IO 更少

-- 查看 InnoDB 页大小（默认 16KB）
SHOW VARIABLES LIKE 'innodb_page_size';
-- 结果：16384 (16KB)

-- 假设一个索引项占 14 字节（bigint 8B + 指针 6B）
-- 16KB / 14B ≈ 1170 个索引项/页
-- 三层 B+Tree：1170 × 1170 × 16 ≈ 2000万条数据

三、索引分类

3.1 按功能分类

-- 1. 主键索引（Primary Key）：自动创建，不允许 NULL
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 2. 唯一索引（Unique Index）：值必须唯一，允许 NULL（但只能有一个 NULL）
CREATE UNIQUE INDEX idx_email ON users(email);

-- 3. 普通索引（Normal Index）：最基本的索引，无唯一性约束
CREATE INDEX idx_name ON users(name);

-- 4. 联合索引（Composite Index）：多个列组成一个索引
CREATE INDEX idx_name_age ON users(name, age);

-- 5. 全文索引（Fulltext Index）：用于文本搜索
CREATE FULLTEXT INDEX idx_content ON articles(content);

3.2 按存储方式分类（面试重点）

这是面试最高频的问题之一：

类型	说明	InnoDB	MyISAM
聚簇索引（Clustered）	叶子节点存完整行数据	✅ 有且仅有一个	❌ 没有
非聚簇索引（Secondary/Non-Clustered）	叶子节点存主键值	✅ 可以有多个	✅ 叶子节点存数据地址

InnoDB 的聚簇索引选择规则：

1. 如果有主键 → 主键就是聚簇索引
2. 如果没有主键但有唯一索引 → 第一个非空唯一索引是聚簇索引
3. 如果都没有 → InnoDB 会生成一个隐藏的 row_id 作为聚簇索引

3.3 回表查询

假设表 users(id, name, age, email)，id 是主键，name 上有普通索引

查询 SELECT * FROM users WHERE name = '张三'

过程：
1. 走 name 索引树 → 找到 name='张三' → 叶子节点存的是 id=100
2. 拿着 id=100 → 回到主键索引树 → 找到完整行数据

这就是【回表】：先查二级索引，再查主键索引，查了两棵树
-- 用 EXPLAIN 验证回表
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type: ref
-- Extra: (无额外信息，说明回表了)

EXPLAIN SELECT id, name FROM users WHERE name = '张三';
-- 如果是覆盖索引，Extra 会显示 Using index

四、覆盖索引

什么是覆盖索引

查询的列全部包含在索引中，不需要回表。

-- 联合索引：idx_name_age (name, age)

-- ❌ 回表（SELECT * 包含了 email，索引里没有 email）
SELECT * FROM users WHERE name = '张三';

-- ✅ 覆盖索引（查询的 id, name, age 都在索引里或主键里）
SELECT id, name, age FROM users WHERE name = '张三';

-- ✅ 覆盖索引
SELECT name, age FROM users WHERE name = '张三';
-- EXPLAIN 验证
EXPLAIN SELECT id, name, age FROM users WHERE name = '张三';
-- Extra: Using index ← 这就是覆盖索引

覆盖索引为什么快

回表：索引树 → 主键 → 主键索引树 → 完整数据（两次树查找）
覆盖索引：索引树 → 直接返回数据（一次树查找）

五、最左前缀原则

什么是联合索引

CREATE INDEX idx_a_b_c ON table1(a, b, c);

联合索引在 B+Tree 中的排序规则：先按 a 排序，a 相同按 b 排序，b 相同按 c 排序。

a=1, b=1, c=1
a=1, b=1, c=2
a=1, b=2, c=1
a=2, b=1, c=1
a=2, b=1, c=2

最左前缀规则

-- 索引：idx_a_b_c(a, b, c)

-- ✅ 全部命中
WHERE a = 1 AND b = 2 AND c = 3

-- ✅ 命中 a, b
WHERE a = 1 AND b = 2

-- ✅ 命中 a
WHERE a = 1

-- ❌ 不命中（跳过了 a）
WHERE b = 2 AND c = 3

-- ❌ 不命中（跳过了 a, b）
WHERE c = 3

-- ⚠️ 命中 a（中间断了，b 用不上）
WHERE a = 1 AND c = 3

范围查询导致后续索引失效

-- 索引：idx_a_b_c(a, b, c)

-- ✅ 全部命中
WHERE a = 1 AND b = 2 AND c = 3

-- ⚠️ 只命中 a, b（b 是范围查询，c 用不上）
WHERE a = 1 AND b > 2 AND c = 3

-- MySQL 5.6 之后有索引下推（ICP），可以部分优化

六、索引失效场景（面试必问）

6.1 函数操作导致失效

-- 索引：idx_create_time(create_time)

-- ❌ 对索引列使用函数，索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2026;

-- ✅ 改成范围查询
SELECT * FROM orders 
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

6.2 隐式类型转换导致失效

-- phone 字段是 VARCHAR 类型，索引：idx_phone(phone)

-- ❌ 传入数字，MySQL 会对 phone 做隐式转换 CAST(phone AS SIGNED)，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 传入字符串
SELECT * FROM users WHERE phone = '13800138000';

6.3 LIKE 左模糊导致失效

-- 索引：idx_name(name)

-- ❌ 左模糊，索引失效
SELECT * FROM users WHERE name LIKE '%张';

-- ❌ 左右模糊，索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅ 右模糊，索引有效
SELECT * FROM users WHERE name LIKE '张%';

6.4 OR 条件导致失效

-- name 有索引，age 没有索引

-- ❌ age 没有索引，导致整个 OR 查询走全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- ✅ 给 age 也加索引
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE name = '张三' OR age = 25;
-- 现在会走索引合并 index_merge

-- ✅ 用 UNION 代替 OR
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

6.5 NOT IN / != 导致索引失效

-- 在数据量大时通常不走索引（优化器认为全表扫描更快）

-- ⚠️ 可能失效
SELECT * FROM users WHERE status != 1;

-- ⚠️ 可能失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- 注意：是否失效取决于数据分布，优化器会自动判断

6.6 索引失效速查表

场景	是否失效	解决方案
对索引列使用函数	❌ 失效	改用范围查询
隐式类型转换	❌ 失效	保持类型一致
LIKE 左模糊	❌ 失效	改用右模糊或全文索引
OR 非索引列	❌ 失效	给所有条件列加索引或用 UNION
联合索引不满足最左前缀	❌ 失效	调整查询条件顺序
IS NOT NULL	⚠️ 可能失效	视数据分布而定
NOT IN	⚠️ 可能失效	用 LEFT JOIN + IS NULL
范围查询后的列	❌ 失效	调整索引列顺序

七、索引下推（Index Condition Pushdown, ICP）

MySQL 5.6 之后的优化

-- 联合索引：idx_name_age(name, age)
-- 查询：WHERE name LIKE '张%' AND age = 25

没有 ICP 时（MySQL 5.6 之前）：

1. 存储引擎根据 name LIKE '张%' 找到所有匹配的主键
2. 回表取完整数据
3. Server 层过滤 age = 25

问题：回表了大量不必要的数据

有 ICP 时（MySQL 5.6 之后）：

1. 存储引擎根据 name LIKE '张%' 找到匹配的索引记录
2. 在存储引擎层就判断 age = 25（不需要回表就先过滤）
3. 只有同时满足条件的才回表

好处：减少了回表次数

-- 验证 ICP
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- Extra: Using index condition ← 表示使用了索引下推

八、索引设计原则（实战）

8.1 什么时候该建索引

-- ✅ 适合建索引
WHERE 条件中的列 -- 频繁出现在 WHERE 中
ORDER BY 的列 -- 避免额外排序
GROUP BY 的列 -- 避免额外分组
JOIN 的关联列 -- 外键字段
高选择性的列 -- 值分布广（如手机号、邮箱）

-- ❌ 不适合建索引
数据量小的表 -- 几百条数据，全表扫描更快
频繁更新的列 -- 维护索引成本高
选择性低的列 -- 如性别（只有男/女），加索引没意义

8.2 选择性计算

-- 选择性 = COUNT(DISTINCT column) / COUNT(*)
-- 越接近 1，选择性越高，越适合建索引

SELECT
COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity, -- 约 0.0001
COUNT(DISTINCT email) / COUNT(*) AS email_selectivity -- 约 1.0
FROM users;

-- email 选择性高 → 适合建索引
-- gender 选择性低 → 不适合单独建索引

8.3 联合索引列顺序原则

-- 原则：把选择性最高的列放在最前面
-- 但也要考虑实际查询场景

-- 场景1：大部分查询是 WHERE name = ? AND age = ?
CREATE INDEX idx_name_age ON users(name, age);

-- 场景2：name 等值查询多，age 经常排序
CREATE INDEX idx_name_age ON users(name, age);
-- 这样 ORDER BY age 可以利用索引排序，避免 filesort

九、EXPLAIN 分析（面试实战）

核心字段解读

EXPLAIN SELECT * FROM users WHERE name = '张三' ORDER BY age;

字段	含义	关注点
type	访问类型	从好到坏：system > const > eq_ref > ref > range > index > ALL
key	实际使用的索引	NULL 表示没用索引
rows	预估扫描行数	越小越好
Extra	额外信息	Using index（覆盖索引）、Using filesort（需要排序）、Using temporary（临时表）

type 详解

-- const：主键或唯一索引等值查询（最快）
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref：多表关联时，被驱动表使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- range：索引范围查询
EXPLAIN SELECT * FROM users WHERE age > 18;

-- index：全索引扫描（比 ALL 好，但数据量大时也慢）
EXPLAIN SELECT name FROM users;

-- ALL：全表扫描（最差）
EXPLAIN SELECT * FROM users WHERE email IS NULL;

Extra 常见值

Using index → 覆盖索引，最优
Using index condition → 索引下推
Using where → Server 层过滤
Using filesort → 需要额外排序，考虑加索引优化
Using temporary → 使用临时表，常见于 GROUP BY

十、面试高频问题汇总

Q1：为什么 InnoDB 推荐使用自增主键？

-- 自增主键：插入时总是追加到最后，不会导致页分裂
-- UUID 主键：随机插入，频繁触发页分裂，性能差

-- ✅ 推荐
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);

-- ❌ 不推荐（聚簇索引用 UUID 会导致严重的页分裂）
CREATE TABLE users (
id CHAR(36) PRIMARY KEY, -- UUID
name VARCHAR(50)
);

Q2：页分裂是什么？

B+Tree 叶子节点满了（16KB），需要分裂：

插入 id=7 到 [1, 3, 5, 9] 所在页
↓
页 [1, 3, 5, 9] 已满
↓
分裂为 [1, 3, 5] 和 [7, 9]
↓
产生新的索引项，需要更新上层节点

自增插入：总是在最后一页追加，几乎不分裂
随机插入：频繁触发页分裂，严重影响性能

Q3：count(*) vs count(1) vs count(字段)

-- InnoDB 中：
SELECT COUNT(*) FROM users; -- 推荐，MySQL 专门优化过
SELECT COUNT(1) FROM users; -- 和 COUNT(*) 性能基本一样
SELECT COUNT(email) FROM users; -- 会跳过 email 为 NULL 的行，且不走覆盖索引（除非 email 有索引）

Q4：一条 SQL 是如何走索引的？

SELECT * FROM users WHERE name = '张三' AND age = 25;

1. 优化器分析：有哪些可用索引、数据分布如何
2. 选择执行计划：决定走 idx_name_age 索引
3. 存储引擎：在 B+Tree 中查找 name='张' 开头的记录
4. 索引下推：在存储引擎层判断 age=25
5. 回表：拿主键 ID 去主键索引取完整行数据
6. 返回结果给 Server 层