数据库索引

为什么使用 B+ 树？

B+ 树 vs B 树

特性	B 树	B+ 树
数据存储	所有节点都存数据	只有叶子节点存数据
叶子节点	独立存在	通过指针连接成链表
查询效率	不稳定（可能在非叶子节点找到）	稳定（必须到叶子节点）
范围查询	需要中序遍历	直接遍历叶子链表
单节点存储	键 + 数据，占用大	只存键，占用小

B+ 树优势

磁盘 I/O 更少
- 非叶子节点不存数据，单页能存更多键
- 树高度更低（通常 3-4 层），I/O 次数更少
范围查询高效
- 叶子节点通过指针连接
- 范围查询只需遍历链表，不需要回溯
查询效率稳定
- 所有查询都要到叶子节点
- 时间复杂度稳定 O(log n)

B+ 树示意图

                        [根节点: 20 | 50]
                       /        |        \
              [10|20]           [30|40]           [50|60]
              /     \           /     \           /     \
          [5|10] [15|20]   [25|30] [35|40]   [45|50] [55|60]
             ↓       ↓        ↓       ↓        ↓       ↓
           数据    数据      数据    数据      数据    数据
             ←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←←
                          叶子节点通过指针连接

索引类型

1. 聚簇索引（Clustered Index）

数据存储在索引的叶子节点中，一张表只能有一个聚簇索引。

聚簇索引结构（以主键为例）：

        [根节点: 20 | 50]
           /        \
      [10|20]      [30|40]
       /    \        /    \
   [id=5, 数据] [id=10, 数据] [id=20, 数据] ...
   
叶子节点 = 索引键 + 完整行数据

特点：

InnoDB 表必须有聚簇索引
默认使用主键作为聚簇索引
没有主键则使用第一个非空唯一索引
都没有则生成隐藏的 6 字节 ROW_ID

优势：

主键查询极快（直接定位到数据）
数据物理存储按主键顺序

劣势：

插入速度依赖主键顺序
更新主键代价大（需要移动数据）
二级索引存储主键值，可能占用更多空间

2. 非聚簇索引（二级索引）

叶子节点存储索引键 + 主键值，需要回表查询完整数据。

二级索引结构（name 字段索引）：

        [根节点: '李' | '王']
           /          \
     ['张','李']    ['李','王']
       /      \        /      \
[name='张', id=5] [name='李', id=10] ...

叶子节点 = 索引键 + 主键值

回表过程：

SELECT * FROM user WHERE name = '张三';
 
-- 执行过程：
-- 1. 在 name 索引树找到 '张三' → 得到 id=5
-- 2. 回表：在聚簇索引树查找 id=5 → 得到完整数据

3. 联合索引

多个字段组成的索引，遵循最左前缀原则。

CREATE INDEX idx_name_age ON user(name, age);
 
-- 索引结构：(name, age) → 主键

最左前缀原则：

-- 能走索引
SELECT * FROM user WHERE name = '张三';
SELECT * FROM user WHERE name = '张三' AND age = 20;
SELECT * FROM user WHERE name = '张三' AND age > 18;
 
-- 不能走索引（缺少最左列 name）
SELECT * FROM user WHERE age = 20;
 
-- 部分走索引（只用 name 部分）
SELECT * FROM user WHERE name = '张三' AND age > 18 ORDER BY create_time;

索引类型对比

类型	叶子节点存储	数量限制	适用场景
聚簇索引	完整行数据	1个	主键查询
二级索引	索引键 + 主键	多个	非主键查询
唯一索引	索引键 + 主键	多个	唯一约束
全文索引	分词 + 文档 ID	多个	全文搜索
空间索引	空间数据	多个	地理位置

索引优化原则

1. 适合创建索引的场景

-- 1. WHERE 条件字段
SELECT * FROM user WHERE name = '张三';
CREATE INDEX idx_name ON user(name);
 
-- 2. ORDER BY / GROUP BY 字段
SELECT * FROM user ORDER BY create_time;
CREATE INDEX idx_create_time ON user(create_time);
 
-- 3. JOIN 关联字段
SELECT * FROM order o JOIN user u ON o.user_id = u.id;
CREATE INDEX idx_user_id ON order(user_id);
 
-- 4. 区分度高的字段（选择性高）
-- 好：手机号、邮箱、身份证
-- 差：性别、状态（只有几个值）
SELECT COUNT(DISTINCT name) / COUNT(*) FROM user; -- 越接近 1 越好

2. 不适合创建索引的场景

-- 1. 区分度低的字段
CREATE INDEX idx_gender ON user(gender); -- 只有男/女，区分度低
 
-- 2. 频繁更新的字段
-- 更新索引需要维护 B+ 树，成本高
 
-- 3. 数据量小的表
-- 全表扫描可能更快
 
-- 4. 不在 WHERE/ORDER BY/GROUP BY 中出现的字段

3. 联合索引设计

原则：区分度高的放前面，范围查询放后面

-- 假设查询：WHERE name = '张三' AND age > 18 AND city = '北京'
 
-- 区分度：name > city > age
-- 正确顺序：name, city, age
CREATE INDEX idx_name_city_age ON user(name, city, age);
 
-- 原因：
-- 1. name 等值查询，可以精确匹配
-- 2. city 等值查询，可以精确匹配
-- 3. age 范围查询，放最后（范围查询后的索引列不会被使用）

4. 覆盖索引

查询的字段都在索引中，不需要回表

-- 创建联合索引
CREATE INDEX idx_name_age ON user(name, age);
 
-- 覆盖索引查询（不需要回表）
SELECT name, age FROM user WHERE name = '张三';
 
-- 需要回表（查询了 id 以外的字段）
SELECT * FROM user WHERE name = '张三';

使用 EXPLAIN 验证：

EXPLAIN SELECT name, age FROM user WHERE name = '张三';
-- Extra: Using index（表示使用了覆盖索引）

索引失效场景

1. 不满足最左前缀

-- 索引：idx_name_age(name, age)
 
-- 失效：缺少 name
SELECT * FROM user WHERE age = 20;
 
-- 有效：有 name
SELECT * FROM user WHERE name = '张三' AND age = 20;

2. 对索引列做运算

-- 失效：对索引列做运算
SELECT * FROM user WHERE YEAR(create_time) = 2024;
SELECT * FROM user WHERE id + 1 = 100;
SELECT * FROM user WHERE SUBSTRING(name, 1, 1) = '张';
 
-- 优化：改为等值条件
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM user WHERE id = 99;
SELECT * FROM user WHERE name LIKE '张%';

3. 类型隐式转换

-- 失效：name 是 varchar，传入数字
SELECT * FROM user WHERE name = 123;
-- 等价于：WHERE CAST(name AS SIGNED) = 123（对索引列做运算）
 
-- 有效：类型匹配
SELECT * FROM user WHERE name = '123';

4. 使用函数

-- 失效：使用函数
SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';
 
-- 优化：范围查询
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

5. LIKE 以通配符开头

-- 失效：% 开头
SELECT * FROM user WHERE name LIKE '%三';
 
-- 有效：% 在后面
SELECT * FROM user WHERE name LIKE '张%';

6. OR 条件

-- 失效：OR 两边有一列没有索引
SELECT * FROM user WHERE name = '张三' OR age = 20;
-- 假设 name 有索引，age 没有索引
 
-- 有效：两列都有索引
CREATE INDEX idx_age ON user(age);
SELECT * FROM user WHERE name = '张三' OR age = 20;

7. NOT IN / NOT LIKE / `<>` / !=

-- 可能失效：取决于优化器
SELECT * FROM user WHERE name != '张三';
SELECT * FROM user WHERE age NOT IN (18, 19, 20);

8. IS NULL / IS NOT NULL

-- 可能失效：取决于数据分布
SELECT * FROM user WHERE name IS NULL;

索引维护

查看索引使用情况

-- 查看表索引
SHOW INDEX FROM user;
 
-- 查看索引基数（区分度）
SHOW INDEX FROM user;
 
-- 分析索引使用统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_database';

删除冗余索引

-- 冗余索引示例
CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_name_age ON user(name, age);
-- idx_name 是冗余的，因为 idx_name_age 可以满足 name 查询
 
-- 删除冗余索引
DROP INDEX idx_name ON user;

重建索引

-- 重建表（优化碎片）
ALTER TABLE user ENGINE = InnoDB;
 
-- 分析表（更新统计信息）
ANALYZE TABLE user;

面试高频问题

Q1: 为什么 MySQL 使用 B+ 树而不是 B 树？

回答要点：

磁盘 I/O 效率
- B+ 树非叶子节点不存数据，单页存更多键
- 树更矮（通常 3-4 层），I/O 次数更少
范围查询性能
- B+ 树叶子节点通过指针连接
- 范围查询直接遍历链表，B 树需要中序遍历
查询稳定性
- B+ 树所有查询都到叶子节点
- B 树可能在非叶子节点找到，效率不稳定

Q2: 聚簇索引和非聚簇索引的区别？

对比项	聚簇索引	非聚簇索引
数据存储	叶子节点存完整数据	叶子节点存主键值
数量	1个	多个
查询方式	直接定位数据	需要回表
插入性能	依赖主键顺序	不影响

Q3: 什么是回表？如何避免？

回表： 二级索引查到主键后，再去聚簇索引查询完整数据。

避免方法：使用覆盖索引

-- 需要回表
SELECT * FROM user WHERE name = '张三';
 
-- 覆盖索引（不需要回表）
SELECT id, name FROM user WHERE name = '张三';
-- 如果有 idx_name(name) 索引

Q4: 联合索引的设计原则？

最左前缀原则：索引 (a, b, c) 可用于 a、ab、abc 查询
区分度高的放前面：选择性高的字段优先
范围查询放后面：范围查询后的列不使用索引
覆盖索引优化：包含查询的所有字段

Q5: 索引什么时候失效？

不满足最左前缀
对索引列做运算
类型隐式转换
使用函数
LIKE 以 % 开头
OR 有一列没索引
NOT IN / <> / !=

总结

索引优化核心原则：
1. 区分度高 → 优先创建
2. 联合索引 → 最左前缀 + 区分度排序
3. 覆盖索引 → 避免回表
4. 避免失效 → 不运算、不转换、不函数、%不在前

最佳实践：

主键使用自增整数（避免页分裂）
联合索引按区分度排序
定期分析索引使用情况
删除冗余和未使用的索引

数据库事务隔离 PreparedStatement