到目前为止，我们已经学习了如何设计表、保证数据完整性、以及如何用各种方式查询数据。但当我们的表从几十行增长到几百万、甚至上亿行时，一个之前只需要 0.1 秒的查询，可能会变成需要几分钟甚至几小时的“灾难”。

这时，我们就必须进入数据库性能优化的领域了。而在这个领域中，最核心、最立竿见影的工具，就是索引 (Index)。

什么是索引？
想象一本很厚的书，比如一本字典。如果你想查找一个词，你肯定不会从第一页开始一页一页地往后翻。你更有可能会利用书前面的“目录”或“索引”，它会告诉你某个词在哪一页，让你能直接翻到那一页。

数据库索引的工作原理与此非常相似。它是一个独立于数据表、专门用于加速查询的数据结构。当你对某（几）个列创建索引后，数据库会维护这个索引结构。当你用这些列作为 WHERE 条件进行查询时，数据库就可以利用索引快速地“定位”到符合条件的行，而不需要扫描整张表（这个过程被称为“全表扫描”，Full Table Scan）。

索引的代价
索引并非没有成本。它就像书的目录一样，本身也需要占用存储空间。更重要的是，当你对表进行 INSERT, UPDATE, DELETE 操作时，数据库不仅要修改表中的数据，还必须同时更新对应的索引结构，这会带来额外的写操作开销。

所以，索引是一把双刃剑：它能极大地加速**读（SELECT）操作，但会稍微降低写（INSERT, UPDATE, DELETE）**操作的性能。

何时使用索引？

• 列经常出现在 WHERE 子句中。
• 列经常出现在 ORDER BY 子句中。
• 列经常被用于 JOIN 的 ON 条件中。
• 列的“基数”（Cardinality，即不重复值的数量）很高。给一个只有“男”、“女”两个值的性别列创建索引，效果通常不大。

20.1 创建索引 (CREATE INDEX)

基本语法:

CREATE INDEX index_name ON table_name (column1, column2, ...);

场景：在我们的 users 表中，username 和 email 字段经常被用来查询。虽然它们已经因为 UNIQUE 约束而自动创建了索引，但我们这里可以手动模拟一下。

-- 为 users 表的 username 列创建一个名为 idx_users_username 的索引
CREATE INDEX idx_users_username ON users (username);

• 给索引起一个有意义的名字（如 idx_表名_列名）是一个非常好的习惯。

多列索引（复合索引）
我们也可以在一个索引中包含多个列。这对于那些 WHERE 子句中经常同时出现多个列的查询非常有用。

-- 为 orders 表的 user_id 和 order_date 创建一个复合索引
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

这个索引对于 WHERE user_id = ? AND order_date > ? 这样的查询会非常高效。

20.2 B-Tree, Hash, GiST, GIN 等索引类型简介

PostgreSQL 提供了多种不同算法的索引类型，以适应不同的查询场景。CREATE INDEX 的完整语法是 CREATE INDEX ... ON ... USING method (...)。

• B-Tree (默认): 这是最通用、最重要的索引类型。当你执行 CREATE INDEX 而不指定 USING 子句时，默认创建的就是 B-Tree 索引。
• 适用场景：几乎所有常规场景！特别适用于 =、>、<、>=、<=、BETWEEN、IN 以及 LIKE 'prefix%' (前缀匹配) 的查询。我们之前创建的所有索引都是 B-Tree 索引。
• Hash: Hash 索引只能处理精确相等 (=) 的查询。它的构建速度和查询速度在某些特定情况下可能比 B-Tree 更快，但功能非常有限，且无法保证数据崩溃后的安全性（需要手动 REINDEX），所以不常用。
• GiST (Generalized Search Tree): 一种通用的索引结构，可以用来实现很多种不同的索引策略。它最著名的应用是在 PostGIS 扩展中，用于加速地理空间数据的查询（比如“找出我附近 5 公里内的所有餐馆”）。
• GIN (Generalized Inverted Index): 倒排索引。它天生就是为了处理那些“一个字段包含多个值”的情况而生的。
• 适用场景：
• 全文搜索 (Full-text Search)：当你想实现像搜索引擎那样的文本搜索时。
• 数组 (ARRAY)：加速对数组元素的查询（比如 tags @> ARRAY['sql']）。
• JSONB：加速对 JSONB 内部键值的查询（比如 profile ->> 'name' = '张三'）。
• 示例：为 articles 表的 tags 数组字段创建一个 GIN 索引。
• CREATE INDEX idx_articles_tags_gin ON articles USING GIN (tags);

20.3 使用EXPLAIN和EXPLAIN ANALYZE分析查询计划

创建了索引，我们怎么知道它是否真的被数据库使用了呢？答案是——查看查询计划 (Query Plan)。

查询计划是数据库决定如何执行一个查询的“作战方案”。EXPLAIN 命令就是用来显示这个方案的。

EXPLAIN: 只显示计划，不实际执行查询。
EXPLAIN ANALYZE: 实际执行查询，并显示计划以及真实的执行时间、返回行数等信息。这是分析慢查询的终极武器。

场景：我们来分析一个查询是否用到了索引。

第一步：没有索引的情况

EXPLAIN ANALYZE
SELECT * FROM users WHERE username = 'zhangsan';

你可能会看到类似这样的输出：

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..2053.93 rows=1 width=100) (actual time=0.5... rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..1053.83 rows=1 width=100) (actual time=0.1... rows=1 loops=3)
         Filter: (username = 'zhangsan'::text)
 Planning Time: 0.1 ms
 Execution Time: 0.6 ms

关键在于 Seq Scan on users，这表示数据库进行了顺序扫描（全表扫描）。

第二步：创建索引

CREATE INDEX idx_users_username ON users (username);

第三步：再次分析

EXPLAIN ANALYZE
SELECT * FROM users WHERE username = 'zhangsan';

现在，输出应该会变成这样：

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_users_username on users  (cost=0.42..8.44 rows=1 width=100) (actual time=0.02... rows=1 loops=1)
   Index Cond: (username = 'zhangsan'::text)
 Planning Time: 0.2 ms
 Execution Time: 0.05 ms

看到 Index Scan using idx_users_username 了吗？这清楚地表明，数据库使用了我们创建的索引，并且 Execution Time (执行时间) 大大减少了！

本章小结

你已经掌握了数据库性能优化的核心武器！

• 我们理解了索引是什么，以及它在加速读和减速写之间的权衡。
• 学会了用 CREATE INDEX 来为表创建单列或多列索引。
• 了解了 PostgreSQL 提供的多种索引类型，特别是默认的 B-Tree 和用于处理数组、JSONB、全文搜索的 GIN 索引。
• 掌握了使用 EXPLAIN ANALYZE 这一终极武器来分析查询计划，验证我们的索引是否生效。

索引是数据库性能调优的起点，也是最重要的一环。一个精心设计的索引策略，能让你的应用在数据量增长时依然保持响应迅速。

到此，我们教程的第四部分也已完成。从下一章开始，我们将进入第五部分：PostgreSQL 高级特性与管理。我们将学习事务、用户角色、函数、备份恢复等更深入的话题。准备好成为一个更全面的 PostgreSQL 专家了吗？我们下一章见！