Jersey's Blog

外观

第五章 索引与性能基础 ​

数据量小的时候，怎么查都很快。数据量一大，没有索引的查询可能从毫秒级变成分钟级。索引是数据库性能优化的第一课。

5.1 什么是索引 ​

一个直觉类比 ​

想象一本 500 页的书：

  • 没有目录：要找"事务"这个词，你只能从第 1 页翻到第 500 页 → 全表扫描（Seq Scan）
  • 有目录：翻到目录页，看到"事务 → 第 198 页"，直接翻过去 → 索引扫描（Index Scan）

索引就是数据库为表建的"目录"。它是一种额外的数据结构，帮助数据库快速定位到目标行，而不需要逐行扫描。

索引的代价 ​

索引不是免费的午餐：

好处代价
大幅加速查询（SELECT）占用额外磁盘空间
加速排序（ORDER BY）插入/更新/删除时需要同步维护索引，变慢
加速关联（JOIN）过多无用索引反而拖慢整体性能

核心原则：只在真正需要的地方建索引。

5.2 创建与管理索引 ​

创建索引 ​

sql
-- 在 name 列上创建索引
CREATE INDEX idx_students_name ON students(name);

这会创建一个 B-Tree 索引（PostgreSQL 的默认索引类型），适用于 =、<、>、BETWEEN、ORDER BY 等操作。

复合索引（多列索引） ​

sql
CREATE INDEX idx_courses_name_score ON courses(course_name, score);

复合索引的列顺序很重要——最左前缀原则：

sql
-- 能用到索引
SELECT * FROM courses WHERE course_name = '数据库原理';
SELECT * FROM courses WHERE course_name = '数据库原理' AND score > 80;

-- 不能用到索引（跳过了第一列）
SELECT * FROM courses WHERE score > 80;

唯一索引 ​

sql
CREATE UNIQUE INDEX idx_students_email ON students(email);

唯一索引 = 普通索引 + UNIQUE 约束。实际上，当你给列加 UNIQUE 约束时，PostgreSQL 会自动创建一个唯一索引。

查看表上的索引 ​

sql
-- psql 命令
\di

-- 或者查系统视图
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'students';

删除索引 ​

sql
DROP INDEX idx_students_name;
DROP INDEX IF EXISTS idx_students_name;

5.3 PostgreSQL 的索引类型 ​

PostgreSQL 支持多种索引类型，不同场景选用不同类型：

索引类型适用场景示例
B-Tree（默认）等值查询、范围查询、排序WHERE age = 20、ORDER BY name
Hash只做等值查询WHERE email = 'xxx'
GIN全文检索、JSONB、数组WHERE tags @> ARRAY['pg']
GiST地理空间、范围类型PostGIS 空间查询
BRIN数据物理上有序的大表（如时间序列）按时间递增的日志表
sql
-- 创建 GIN 索引（用于 JSONB）
CREATE INDEX idx_events_data ON events USING GIN (data);

-- 创建 BRIN 索引（用于时间序列）
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

入门阶段只需掌握 B-Tree，其他类型在遇到具体需求时再学。

5.4 用 EXPLAIN 分析查询 ​

EXPLAIN 是查看查询执行计划的工具，能告诉你数据库"打算怎么执行这条 SQL"。

基本用法 ​

sql
EXPLAIN SELECT * FROM students WHERE name = '张三';

输出：

                      QUERY PLAN
------------------------------------------------------
 Seq Scan on students  (cost=0.00..1.05 rows=1 width=...)
   Filter: ((name)::text = '张三'::text)
  • Seq Scan：全表扫描（没用到索引）
  • cost：预估的执行成本（越小越好）
  • rows：预估返回的行数

加上索引后再看 ​

sql
CREATE INDEX idx_students_name ON students(name);
EXPLAIN SELECT * FROM students WHERE name = '张三';

输出变成：

                      QUERY PLAN
------------------------------------------------------
 Index Scan using idx_students_name on students  (cost=0.00..0.30 rows=1 width=...)
   Index Cond: ((name)::text = '张三'::text)

变成了 Index Scan，成本从 1.05 降到 0.30。

EXPLAIN ANALYZE — 真实执行 ​

sql
EXPLAIN ANALYZE SELECT * FROM students WHERE name = '张三';

ANALYZE 会真正执行查询，返回实际耗时：

 Index Scan using idx_students_name on students (cost=... rows=1 ...) (actual time=0.023..0.025 rows=1 loops=1)
   Index Cond: ((name)::text = '张三'::text)
 Planning Time: 0.085 ms
 Execution Time: 0.042 ms

读懂 EXPLAIN 输出的关键点 ​

关注点好的信号不好的信号
扫描方式Index Scan, Index Only ScanSeq Scan（大表时）
实际行数 vs 预估行数接近差距很大（统计信息过期）
执行时间毫秒级秒级或更慢

注意：小表（几百行）Seq Scan 可能反而比 Index Scan 更快，因为索引有额外的查找开销。数据库会自己选择最优方案。

5.5 索引使用的最佳实践 ​

应该建索引的列 ​

  1. 主键和外键：主键自动有索引；外键建议手动加索引（JOIN 时用得到）
  2. 经常出现在 WHERE 中的列：WHERE name = ...、WHERE age > ...
  3. 经常出现在 ORDER BY 中的列：可以避免排序操作
  4. 经常出现在 JOIN 条件中的列：加速表关联
sql
-- 外键列加索引（PostgreSQL 不自动为外键建索引）
CREATE INDEX idx_courses_student_id ON courses(student_id);

不应该（或不需要）建索引的列 ​

  1. 数据量很小的表（几百行）：全表扫描更快
  2. 低基数列（如性别、布尔值）：索引区分度太低，效果不大
  3. 很少被查询的列：索引只对查询有用，白占空间
  4. 频繁大量写入的表：索引会拖慢写操作

索引失效的常见原因 ​

sql
-- 1. 对列使用函数 → 索引失效
SELECT * FROM students WHERE UPPER(name) = 'ZHANGSAN';    -- ✗ 用不到 name 的索引
-- 解决：建函数索引
CREATE INDEX idx_students_name_upper ON students(UPPER(name));

-- 2. 隐式类型转换 → 索引失效
SELECT * FROM students WHERE age = '20';    -- age 是 INTEGER，'20' 是字符串
-- 解决：确保类型一致
SELECT * FROM students WHERE age = 20;

-- 3. LIKE 以通配符开头 → 索引失效
SELECT * FROM students WHERE name LIKE '%三';    -- ✗ 无法使用 B-Tree 索引
SELECT * FROM students WHERE name LIKE '张%';    -- ✓ 可以使用索引

5.6 实战练习 ​

sql
-- 1. 在没有索引的情况下，分析查询
EXPLAIN ANALYZE SELECT * FROM students WHERE name = '张三';

-- 2. 创建索引
CREATE INDEX idx_students_name ON students(name);

-- 3. 再次分析，对比变化
EXPLAIN ANALYZE SELECT * FROM students WHERE name = '张三';

-- 4. 为外键列创建索引
CREATE INDEX idx_courses_student_id ON courses(student_id);

-- 5. 分析 JOIN 查询
EXPLAIN ANALYZE
SELECT s.name, c.course_name, c.score
FROM students s
JOIN courses c ON s.id = c.student_id
WHERE s.name = '张三';

-- 6. 查看当前所有索引
\di

本章小结 ​

你学到了要点
索引是什么数据库的"目录"，加速查询
B-Tree 索引默认类型，适用于等值/范围/排序查询
EXPLAIN查看执行计划，判断是否走索引
EXPLAIN ANALYZE真实执行并返回实际耗时
建索引原则WHERE/JOIN/ORDER BY 常用列建索引，低基数列不建
索引代价占空间、拖慢写操作

下一章：第六章 事务与并发控制

坚持是一种品格

Copyright © 2024-2026 Jersey