知识模块
☕ Java 知识模块
十二、性能优化
SQL 索引优化

SQL 索引优化

面试高频考点：索引设计原则、索引失效场景、Explain 分析、联合索引最左匹配、索引下推、覆盖索引

一、索引基础

什么是索引？

索引是数据库中用于加速查询的数据结构，类似于书的目录，可以快速定位到需要的数据行。

-- 创建索引
CREATE INDEX idx_user_name ON user(name);
 
-- 查看索引
SHOW INDEX FROM user;
 
-- 删除索引
DROP INDEX idx_user_name ON user;

索引的优缺点

优点缺点
加快数据检索速度占用磁盘空间
加速排序和分组降低写操作性能（INSERT/UPDATE/DELETE）
加速表连接需要维护索引结构
保证数据唯一性（唯一索引）索引过多会影响优化器选择

索引类型

-- 1. 普通索引
CREATE INDEX idx_name ON table_name(column);
 
-- 2. 唯一索引（允许 NULL，但只能有一个 NULL）
CREATE UNIQUE INDEX uk_email ON user(email);
 
-- 3. 主键索引（不允许 NULL，每表只能一个）
ALTER TABLE user ADD PRIMARY KEY (id);
 
-- 4. 联合索引
CREATE INDEX idx_name_age ON user(name, age);
 
-- 5. 全文索引
CREATE FULLTEXT INDEX ft_content ON article(content);
 
-- 6. 前缀索引（适用于长字符串）
CREATE INDEX idx_phone ON user(phone(7));

二、索引数据结构

B+ 树结构

MySQL InnoDB 使用 B+ 树作为索引结构：

                        [根节点: 20|50]
                       /       |       \
              [10|15]          [30|40]          [60|70]
              /   \            /   \            /   \
         [叶子节点]        [叶子节点]        [叶子节点]
         5→10→15           20→30→40          50→60→70
              ↑                ↑                ↑
              └────────────────┴────────────────┘
                         链表连接（范围查询高效）

B+ 树特点：

  • 非叶子节点只存储键值，不存储数据
  • 叶子节点存储所有数据，并通过指针连接
  • 树高度低（通常 3-4 层），减少磁盘 I/O
  • 范围查询高效（叶子节点链表）

为什么用 B+ 树而不是 B 树？

特性B 树B+ 树
数据存储所有节点都存数据只有叶子节点存数据
范围查询需要中序遍历叶子节点链表直接遍历
单节点存储键少（数据占空间）键多（只有键值）
树高度较高较低
查询稳定性不稳定（可能在非叶子节点找到）稳定（都要到叶子节点）

聚簇索引 vs 非聚簇索引

聚簇索引（Clustered Index）：

  • 主键索引，数据存储在叶子节点
  • 一张表只能有一个聚簇索引
  • InnoDB 默认使用主键作为聚簇索引

非聚簇索引（Secondary Index）：

  • 二级索引，叶子节点存储主键值
  • 需要回表查询获取完整数据
  • 一张表可以有多个非聚簇索引
聚簇索引结构：
[主键索引 B+ 树]
叶子节点直接存储完整数据行

非聚簇索引结构：
[二级索引 B+ 树]
叶子节点存储: (索引列值, 主键值)
查询流程: 二级索引 → 主键值 → 聚簇索引 → 完整数据（回表）

三、索引设计原则

1. 适合建索引的场景

-- 1. WHERE 条件列
SELECT * FROM user WHERE name = '张三';
CREATE INDEX idx_name ON user(name);
 
-- 2. ORDER BY 排序列
SELECT * FROM user ORDER BY create_time DESC;
CREATE INDEX idx_create_time ON user(create_time);
 
-- 3. JOIN 连接列
SELECT * FROM orders o JOIN user u ON o.user_id = u.id;
CREATE INDEX idx_user_id ON orders(user_id);
 
-- 4. GROUP BY 分组列
SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;
CREATE INDEX idx_dept_id ON employee(dept_id);
 
-- 5. 区分度高的列
-- 区分度 = COUNT(DISTINCT column) / COUNT(*)
SELECT COUNT(DISTINCT name) / COUNT(*) FROM user;  -- 越接近 1 越好

2. 不适合建索引的场景

-- 1. 区分度低的列（如性别、状态）
-- 性别只有男/女，区分度约 50%，索引效果差
SELECT * FROM user WHERE gender = '男';  -- 不建议建索引
 
-- 2. 频繁更新的列
UPDATE user SET status = 1 WHERE id = 1;  -- 更新会导致索引重建
 
-- 3. 数据量小的表
-- 数据量 < 1000 行，全表扫描可能更快
 
-- 4. 不在 WHERE/ORDER BY/GROUP BY 中使用的列
-- 不会被查询使用，建索引浪费空间

3. 联合索引设计

最左前缀原则：联合索引按照定义顺序，从左到右匹配。

-- 联合索引
CREATE INDEX idx_name_age_city ON user(name, age, city);
 
-- 能使用索引的情况
WHERE name = '张三';                           -- 匹配 name
WHERE name = '张三' AND age = 25;              -- 匹配 name, age
WHERE name = '张三' AND age = 25 AND city = '北京';  -- 全部匹配
 
-- 不能使用索引的情况
WHERE age = 25;                    -- 缺少 name，不匹配
WHERE city = '北京';               -- 缺少 name 和 age，不匹配
WHERE name = '张三' AND city = '北京';  -- 匹配 name，city 走索引但效率较低

联合索引设计原则：

1. 等值条件放前面，范围条件放后面
   WHERE name = '张三' AND age > 25
   索引顺序: (name, age)  -- name 等值过滤后，age 范围查询

2. 区分度高的列放前面
   索引 (name, gender) 比 (gender, name) 好

3. 覆盖索引优化
   SELECT name, age FROM user WHERE name = '张三';
   索引 (name, age) 可以覆盖查询，无需回表

四、索引失效场景

1. 索引列参与运算

-- 索引失效
SELECT * FROM user WHERE age + 1 = 26;
 
-- 优化方案
SELECT * FROM user WHERE age = 25;

2. 索引列使用函数

-- 索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2024;
 
-- 优化方案：使用范围查询
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

3. LIKE 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张%';
SELECT * FROM user WHERE name LIKE '%张';
 
-- 索引有效
SELECT * FROM user WHERE name LIKE '张%';
 
-- 优化方案：使用全文索引或 Elasticsearch
CREATE FULLTEXT INDEX ft_name ON user(name);
SELECT * FROM user WHERE MATCH(name) AGAINST('张');

4. OR 条件中有非索引列

-- 假设 name 有索引，status 没有索引
-- 索引失效
SELECT * FROM user WHERE name = '张三' OR status = 1;
 
-- 优化方案：给 status 也建索引，或拆分为 UNION
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE status = 1;

5. 隐式类型转换

-- phone 是 VARCHAR 类型
-- 索引失效（字符串与数字比较，发生隐式转换）
SELECT * FROM user WHERE phone = 13800138000;
 
-- 索引有效
SELECT * FROM user WHERE phone = '13800138000';

6. != 或 <> 操作符

-- 索引可能失效（取决于数据分布）
SELECT * FROM user WHERE status != 1;
 
-- 优化方案
SELECT * FROM user WHERE status IN (0, 2, 3);
-- 或
SELECT * FROM user WHERE status = 0 OR status = 2 OR status = 3;

7. IS NULL / IS NOT NULL

-- 索引可能失效（取决于数据分布和 NULL 值数量）
SELECT * FROM user WHERE name IS NULL;
 
-- 建议：设计表时尽量设置 NOT NULL 默认值
ALTER TABLE user MODIFY name VARCHAR(50) NOT NULL DEFAULT '';

8. NOT IN 操作符

-- 索引失效
SELECT * FROM user WHERE status NOT IN (1, 2, 3);
 
-- 优化方案：使用 LEFT JOIN + IS NULL
SELECT u.* FROM user u
LEFT JOIN (SELECT 1 AS status UNION SELECT 2 UNION SELECT 3) e
ON u.status = e.status
WHERE e.status IS NULL;

五、EXPLAIN 执行计划分析

使用 EXPLAIN

EXPLAIN SELECT * FROM user WHERE name = '张三';
 
-- MySQL 8.0+ 支持格式化输出
EXPLAIN FORMAT=TREE SELECT * FROM user WHERE name = '张三';
 
-- MySQL 8.0.18+ 支持 ANALYZE（显示实际执行时间）
EXPLAIN ANALYZE SELECT * FROM user WHERE name = '张三';

EXPLAIN 字段解读

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

关键字段：

1. id - 执行顺序

  • id 相同：从上到下执行
  • id 不同：id 越大越先执行

2. type - 访问类型（从好到差）

type说明性能
system单行数据，系统表最优
const主键或唯一索引常量查询优
eq_ref主键或唯一索引连接查询优
ref非唯一索引等值查询良
range索引范围扫描中
index索引全扫描较差
ALL全表扫描最差
-- const: 主键查询
EXPLAIN SELECT * FROM user WHERE id = 1;
 
-- ref: 普通索引查询
EXPLAIN SELECT * FROM user WHERE name = '张三';
 
-- range: 范围查询
EXPLAIN SELECT * FROM user WHERE age > 20 AND age < 30;
 
-- ALL: 全表扫描（无索引或索引失效）
EXPLAIN SELECT * FROM user WHERE age + 1 = 26;

3. key - 实际使用的索引

-- key = NULL: 没有使用索引
-- key = idx_name: 使用了 idx_name 索引

4. key_len - 索引长度

-- key_len 越短越好，说明索引效率高
-- 联合索引可以判断使用了哪些列
 
-- 索引 (name, age)
-- name VARCHAR(50) NOT NULL: key_len = 50*3 + 2 = 152 (utf8mb4)
-- age INT NOT NULL: key_len = 4
-- 全部使用: key_len = 156
 
EXPLAIN SELECT * FROM user WHERE name = '张三';  -- key_len = 152
EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 25;  -- key_len = 156

5. rows - 预估扫描行数

-- rows 越小越好
-- 如果 rows 与实际相差很大，可能需要 ANALYZE TABLE 更新统计信息
ANALYZE TABLE user;

6. Extra - 额外信息

Extra说明
Using index覆盖索引，无需回表
Using where服务器层过滤
Using index condition索引下推
Using temporary使用临时表
Using filesort文件排序（需要优化）
Using join buffer连接缓冲区
-- 覆盖索引（好）
EXPLAIN SELECT name FROM user WHERE name = '张三';
-- Extra: Using index
 
-- 文件排序（需要优化）
EXPLAIN SELECT * FROM user ORDER BY create_time;
-- Extra: Using filesort
 
-- 临时表（需要优化）
EXPLAIN SELECT DISTINCT dept_id FROM employee;
-- Extra: Using temporary

六、高级索引技术

1. 覆盖索引（Covering Index）

覆盖索引是指查询所需的列都在索引中，无需回表。

-- 普通索引 + 回表
-- 需要先查二级索引，再回表查聚簇索引
SELECT * FROM user WHERE name = '张三';
 
-- 覆盖索引
-- 查询列都在索引中，不需要回表
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = '张三';  -- 覆盖索引
 
-- EXPLAIN 中看到 Extra: Using index
EXPLAIN SELECT name, age FROM user WHERE name = '张三';

覆盖索引优点：

  • 减少磁盘 I/O（避免回表）
  • 提高查询性能

2. 索引下推（Index Condition Pushdown）

索引下推是 MySQL 5.6 引入的优化，将 WHERE 条件的过滤下推到存储引擎层。

-- 联合索引 (name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;
 
-- 无索引下推（MySQL 5.5 及之前）：
-- 1. 存储引擎通过索引找到 name LIKE '张%' 的所有记录
-- 2. 回表获取完整记录
-- 3. Server 层过滤 age = 25
 
-- 有索引下推（MySQL 5.6+）：
-- 1. 存储引擎在索引中同时过滤 name LIKE '张%' AND age = 25
-- 2. 只对满足条件的记录回表
 
-- EXPLAIN 中看到 Extra: Using index condition
EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age = 25;

索引下推优点：

  • 减少回表次数
  • 减少存储引擎和 Server 层的数据传输

3. 索引合并（Index Merge）

索引合并是指 MySQL 使用多个索引完成一次查询。

-- 两个单列索引
CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_age ON user(age);
 
-- 可能使用索引合并
SELECT * FROM user WHERE name = '张三' OR age = 25;
 
-- EXPLAIN 中看到 type: index_merge
-- Extra: Using union(idx_name, idx_age)
EXPLAIN SELECT * FROM user WHERE name = '张三' OR age = 25;

索引合并类型：

  • Using union: OR 条件合并
  • Using intersect: AND 条件合并
  • Using sort_union: OR 条件排序后合并

建议：索引合并通常不如联合索引高效，优先考虑联合索引。

七、索引优化实战

案例 1：优化 ORDER BY

-- 问题 SQL
SELECT * FROM user WHERE status = 1 ORDER BY create_time DESC LIMIT 10;
-- Extra: Using filesort
 
-- 优化方案：联合索引
CREATE INDEX idx_status_createtime ON user(status, create_time);
-- Extra: Using index

案例 2：优化 GROUP BY

-- 问题 SQL
SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id;
-- Extra: Using temporary; Using filesort
 
-- 优化方案：索引
CREATE INDEX idx_dept_id ON employee(dept_id);
-- Extra: Using index

案例 3：优化 JOIN

-- 问题 SQL
SELECT u.name, o.order_no
FROM orders o
JOIN user u ON o.user_id = u.id
WHERE o.status = 1;
 
-- 优化方案
CREATE INDEX idx_status_userid ON orders(status, user_id);  -- 驱动表索引
CREATE INDEX idx_id ON user(id);  -- 被驱动表索引（主键已有）

案例 4：优化 COUNT

-- 问题 SQL：COUNT(*) 性能差
SELECT COUNT(*) FROM user WHERE status = 1;
 
-- 优化方案：覆盖索引
CREATE INDEX idx_status ON user(status);
-- Extra: Using index
 
-- 更好的方案：维护计数表
CREATE TABLE user_count (status INT, cnt INT);
UPDATE user_count SET cnt = cnt + 1 WHERE status = 1;
SELECT cnt FROM user_count WHERE status = 1;

八、面试要点

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

回答要点：

  1. B+ 树非叶子节点只存键值，单节点能存更多键，树更矮，磁盘 I/O 更少
  2. B+ 树叶子节点链表连接，范围查询效率高
  3. B+ 树查询性能稳定，都必须到叶子节点

Q2: 什么是聚簇索引和非聚簇索引？

回答要点：

  • 聚簇索引：数据存储在叶子节点，一张表只能有一个，InnoDB 主键索引
  • 非聚簇索引：叶子节点存储主键值，需要回表查询完整数据
  • 非聚簇索引查询需要两次 B+ 树查找

Q3: 联合索引的最左前缀原则是什么？

回答要点：

  • 联合索引按照定义顺序从左到右匹配
  • 遇到范围查询（>、<、BETWEEN、LIKE）停止匹配
  • 等值条件放前面，范围条件放后面

Q4: 什么是索引下推？

回答要点：

  • MySQL 5.6 引入的优化
  • 将 WHERE 条件过滤下推到存储引擎层
  • 减少回表次数和存储引擎与 Server 层的数据传输
  • EXPLAIN 中 Extra 显示 Using index condition

Q5: 什么是覆盖索引？有什么好处？

回答要点：

  • 查询所需的列都在索引中，无需回表
  • 减少磁盘 I/O，提高查询性能
  • EXPLAIN 中 Extra 显示 Using index

Q6: 索引什么时候会失效？

回答要点：

  1. 索引列参与运算或使用函数
  2. LIKE 以 % 开头
  3. OR 条件中有非索引列
  4. 隐式类型转换
  5. !=、<>、NOT IN、IS NULL（可能失效）
  6. 联合索引不满足最左前缀

小结

  • 索引是加速查询的数据结构，MySQL 使用 B+ 树
  • 聚簇索引数据在叶子节点，非聚簇索引需要回表
  • 联合索引遵循最左前缀原则，设计时区分度高的列放前面
  • 索引失效场景要牢记，使用 EXPLAIN 分析执行计划
  • 覆盖索引和索引下推是重要的优化技术
  • 索引不是越多越好，需要根据实际查询场景设计
概述深度分页优化