第六章｜索引与性能优化

本章目标

学习数据库索引的原理和应用，掌握查询性能分析和优化技巧，理解 EXPLAIN 的使用方法。

从索引的基本概念开始，理解 B-Tree、Hash 等索引类型，学会创建和管理索引，掌握 EXPLAIN 分析查询计划，最后学习查询优化的常见技巧。

学习路径

按顺序阅读以下文章，每篇包含讲解、示例和练习：

1. 索引类型：B-Tree、Hash、GiST、GIN
   理解不同索引类型的原理和适用场景。

2. 索引管理：创建、删除、维护
   掌握索引的创建、删除、重建等操作。

3. EXPLAIN 分析：理解查询计划
   使用 EXPLAIN 分析查询性能瓶颈。

4. 查询优化：常见技巧与最佳实践
   掌握查询优化的实用技巧。

关键概念

索引加速查询

索引是数据库的目录，加速数据检索：

-- 没有索引：全表扫描
SELECT * FROM users WHERE email = 'alice@example.com';  -- 慢

-- 创建索引
CREATE INDEX idx_email ON users(email);

-- 有索引：快速定位
SELECT * FROM users WHERE email = 'alice@example.com';  -- 快

B-Tree 索引

最常用的索引类型，支持范围查询：

CREATE INDEX idx_order_date ON orders(order_date);

-- 索引生效
SELECT * FROM orders WHERE order_date > '2026-01-01';
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

EXPLAIN 分析执行计划

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;

输出：

Seq Scan on orders  (cost=0.00..25.00 rows=5 width=100) (actual time=0.012..0.045 rows=5 loops=1)
  Filter: (user_id = 1)

查询优化原则

1. 避免全表扫描：创建合适的索引
2. **避免 SELECT ***：只查询需要的列
3. 避免函数包裹列：WHERE YEAR(date) = 2026 → WHERE date BETWEEN ... AND ...
4. 使用覆盖索引：索引包含查询所需的所有列

前置知识

• 掌握 SELECT、WHERE、JOIN
• 理解数据库存储结构
• 了解查询执行过程

后续章节

完成本章后，继续学习：

• 第七章：数据定义与约束
  掌握 DDL 操作、约束、范式化。

练习答案

本章所有练习的答案和详细解释：第六章练习答案

深入阅读

为什么需要索引？

没有索引时，数据库必须扫描整张表才能找到匹配的行（O(n)）。索引通过树形结构组织数据，将查找时间降低到 O(log n)。

但索引不是免费的：

• 占用额外空间
• 写入操作变慢（需要更新索引）
• 需要维护（碎片整理）

何时应该创建索引？

• WHERE 子句中经常使用的列
• JOIN 条件列
• ORDER BY / GROUP BY 列
• 外键列

何时不应该创建索引：

• 表数据很少（< 1000 行）
• 列值重复度高（如性别）
• 频繁更新的列

返回SQL 学习路线 · 开始第一节