数据库索引的工作原理——以PostgreSQL为例编写的实用指南

每个开发人员最终都会遇到查询速度缓慢的问题。当表格中的行数从几百条增加到几百万条时，原本只需要几毫秒就能完成的查询现在可能需要几秒钟甚至更长的时间才能完成。

解决这个问题的方法通常是创建索引。

数据库索引是一种数据结构，它可以帮助数据库更快地找到所需的记录，而无需扫描整个表格。它的原理与教科书末尾的索引非常相似：你不需要逐页阅读内容来查找某个主题，而是可以通过索引直接找到对应的页码并跳到该位置进行查询。

在本教程中，你将学习索引在底层是如何工作的，如何在PostgreSQL中有效地创建和使用索引，以及如何避免那些会使得索引变得毫无用处甚至带来负面影响的常见错误。

目录

• 先决条件

• 为什么需要索引？

• 索引在底层的工作原理

• 如何创建你的第一个索引

• 如何使用EXPLAIN ANALYZE来评估性能

• PostgreSQL中的索引类型

• 如何创建复合索引

• 如何创建部分索引

• 如何创建表达式索引

• 如何创建唯一索引

• 如何管理索引

• 当索引起反作用时

• 阻碍索引使用的常见错误

• 创建索引的最佳实践

• 结论

先决条件

为了能够跟随示例进行学习，你需要具备以下条件：

• 基本的SQL知识（SELECT、INSERT、UPDATE、DELETE、WHERE、JOIN等操作语句）

• 一个正在运行的PostgreSQL实例（版本12或更高版本）

• 一个SQL客户端工具，例如psql、pgAdmin或DBeaver

如果你没有在本地安装PostgreSQL，也可以使用Neon或Supabase等提供的免费云托管实例。

为什么需要索引？

当你执行像SELECT * FROM users WHERE email = 'jane@example.com'这样的查询时，数据库需要找到匹配的记录。如果没有索引，PostgreSQL会进行顺序扫描——它会读取表格中的每一行数据，然后检查email列的值是否与查询条件相符。

对于包含100行的表格来说，这种处理方式完全没问题；但对于包含1000万行的表格而言，其查询速度就会慢得令人难以忍受。

索引通过创建一种独立的、经过排序的数据结构来解决这个问题，这种结构能够将列值与对应的行位置建立关联。这样一来，PostgreSQL无需遍历全部1000万行数据，而是可以直接在索引中查找目标值，然后直接跳转到相应的行。这样，查询时间可以从几秒缩短到几毫秒。

然而，使用索引并非没有代价。在到处添加索引之前，你必须了解其中所涉及的各种权衡因素。在本教程中，你将会了解到这些权衡点。

索引的内部工作原理

PostgreSQL默认使用的索引类型是B树（平衡树）。了解B树的工作原理，将有助于你更明智地决定何时以及如何使用索引。

B树会将数据组织成一种有序的、层次结构分明的数据结构，这种结构包含三个层级：

1. 根节点——位于树的顶层。它包含一些用于将数据范围划分开来的值。

2. 内部节点——每个内部节点都会进一步缩小数据范围的范围。

3. 叶节点——位于结构的最底层。这些节点保存着实际被索引的值，同时还包含指向表中对应行的指针。

当PostgreSQL使用B树索引来查找某个值时，它会从根节点开始，沿着能够匹配目标值的路径依次访问各个内部节点，最终到达正确的叶节点。这种查找过程被称为树遍历，即使对于包含数百万行的表格来说，这一过程通常也只需要3到4步即可完成。

可以把这个过程想象成翻电话簿：你不会从第一页开始逐个查看所有名字，而是先找到相应的分类目录（相当于根节点），然后再缩小范围找到具体的页面（相当于内部节点），最后在 해당 页面上查找所需的信息（相当于叶节点）。

正是这种有序的结构，使得B树索引在处理像WHERE price > 50 AND price < 100这样的范围查询时能够发挥出良好的效果。数据库会先在树结构中找到起始位置，然后依次遍历那些已经按顺序排列的叶节点，从而快速完成查询。

如何创建你的第一个索引

让我们通过一个实际例子来学习如何操作。你会创建一个表格，向其中插入数据，然后亲身体会索引带来的性能提升效果。

步骤1 – 创建表格并插入示例数据

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

现在，我们需要插入大量数据，以便能够清楚地看到索引带来的性能差异。以下代码会生成50万条示例数据：

INSERT INTO customers (first_name, last_name, email, city)
SELECT
    'User' || gs,
    'Last' || gs,
    'user' || gs || '@example.com',
    (ARRAY['Lagos', 'London', 'New York', 'Berlin', 'Tokyo')[1 + (gs % 5)]
FROM generate_series(1, 500000) AS gs;

步骤 2 – 不使用索引进行查询

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

您会看到类似以下的输出结果：

对 customers 表进行顺序扫描（成本=0.00..11374.00，行数=1，列宽=52）（实际耗时=45.123..91.456秒，行数=1，循环次数=1）
  过滤条件：((email)::text = 'user250000@example.com'::text)
  被过滤掉的行数：499999
  规划时间：0.085毫秒
  执行时间：91.502毫秒

这里的关键点是顺序扫描——PostgreSQL扫描了全部500,000行数据才找到了匹配的结果。这意味着有499,999行数据被白白扫描了，造成了大量的资源浪费。

步骤 3 – 创建索引

CREATE INDEX idx_customers_email ON customers (email);

这条命令会在email列上创建一个B树索引。索引名称idx_customers_email遵循了常见的命名规则：以idx_为前缀，接着是表名，最后是列名。

步骤 4 – 使用索引进行查询

再次运行相同的查询：

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'user250000@example.com';

现在您会看到如下输出结果：

如何使用EXPLAIN ANALYZE来评估性能
EXPLAIN ANALYZE是您了解PostgreSQL如何执行查询命令的重要工具。在上一节中您已经看到了它的用法，现在让我们详细分析一下这些输出信息的含义。
EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Lagos';

输出结果会告诉您以下几项信息：


扫描类型——PostgreSQL是使用了顺序扫描、索引扫描、位图索引扫描，还是其他访问方式


成本——以任意单位表示的预估执行成本。第一个数字代表初始化成本，第二个数字代表总成本


匹配的行数——PostgreSQL估计会找到多少行数据，实际找到的又是多少行


实际执行时间——查询命令实际完成所需的时间，单位为毫秒


被过滤掉的行数

——虽然被扫描了，但并不满足查询条件的行数



如果你在一个大型表上看到使用了选择性WHERE子句的Seq Scan操作，那么这通常说明你需要创建索引。而当看到Index Scan或Index Only Scan时，就意味着你的索引正在正常发挥作用。
需要记住的一点是：EXPLAIN命令在没有ANALYZE选项的情况下，只会显示查询的执行计划而不会实际执行该查询。EXPLAIN ANALYZE则会执行查询并显示真实的执行时间数据。在分析性能问题时，一定要使用EXPLAIN ANALYZE；但对于那些可能会修改数据的查询来说，使用这个命令时要特别小心——比如EXPLAIN ANALYZE DELETE FROM ...这样的命令实际上会删除数据表中的记录。因此，在使用这类命令时，应该将其放在事务中执行，并在操作完成后进行回滚：
BEGIN;
EXPLAIN ANALYZE DELETE FROM customers WHERE city = 'Berlin';
ROLLBACK;

PostgreSQL中的索引类型
PostgreSQL支持多种类型的索引，每种索引都针对不同的查询模式进行了优化。
B树（默认类型）
B树是默认的索引类型，适用于绝大多数使用场景。它支持相等性检查(=)、范围查询(<, >, <=, >=, BETWEEN),排序操作(ORDER BY),以及IS NULL/IS NOT NULL判断。
-- 这两种语句是等效的——B树是默认类型
CREATE INDEX idx_name ON customers (last_name);
CREATE INDEX idx_name ON customers USING btree (last_name);

除非有特别的理由需要使用其他类型的索引，否则应该优先选择B树。
哈希索引
哈希索引专为相等性比较(=)而设计，不支持范围查询或排序操作。实际上，B树在处理相等性检查时速度也几乎与哈希索引相当，因此很少有必要使用哈希索引。
CREATE INDEX idx_email_hash ON customers USING hash (email);

只有当你拥有一个规模非常大的表，并且经常需要对表中的数据进行仅针对相等性条件的查询，同时又希望节省一些索引空间时，才可以考虑使用哈希索引。
GIN（通用倒排索引）
GIN索引适用于那些包含多个元素的值，比如数组、JSONB格式的数据或全文搜索所需的数据结构。与传统的索引不同，GIN索引会为这些值中的每个元素分别创建索引条目，而不是只为每行数据创建一个索引。
-- 添加一个JSONB类型的列
ALTER TABLE customers ADD COLUMN preferences JSONB DEFAULT '{}';

-- 为这个JSONB列创建索引
CREATE INDEX idx_preferences ON customers USING gin (preferences);

-- 现在，这个查询就会使用GIN索引了
SELECT * FROM customers WHERE preferences @> '{"newsletter": true '}';

当你在JSONB数据中执行查询、使用@>或&&来搜索数组中的元素，或者使用tsvector进行全文搜索时，应该考虑使用GIN索引。
GiST（通用搜索树）
GiST索引支持几何数据、范围值以及全文搜索功能。它们通常与PostGIS结合使用，用于执行地理空间相关的查询。
-- 范围类型的示例
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE
);

CREATE INDEX idx_event_duration ON events USING gist (duration);

-- 查找重叠的事件
SELECT * FROM events WHERE duration && '[2025-01-01, 2025-01-31]'::tsrange;

在处理空间数据、范围类型的数据，或者需要使用重叠/包含操作符时，请使用GiST索引。
BRIN（块范围索引）
BRIN索引体积非常小，且适用于那些物理行顺序与被索引列的值相匹配的大型表格。一个常见的例子就是那些只能进行追加操作的表格中的时间戳列——新添加的行总是具有更晚的时间戳。
CREATE INDEX idx_created_at_brin ON customers USING brin (created_at);

BRIN索引会为每组连续的行存储汇总信息（如最小值/最大值），而不是对每一行都进行索引处理。因此，这种索引的结构要比B树索引小得多，但它只适用于数据本身已经有序的情况。
对于那些数据量非常大、且只能进行追加操作、同时数据又具有自然排序顺序的表格来说，BRIN索引是非常适用的——比如日志记录、事件信息或时间序列数据等。
如何创建复合索引
复合索引（也称为多列索引）能够覆盖多个列。当你的查询经常需要同时根据多个列进行筛选或排序时，使用复合索引会非常方便。
CREATE INDEX idx_city_lastname ON customers (city, last_name);

在复合索引中，列的排列顺序非常重要。PostgreSQL可以使用这种索引来执行那些仅基于city列进行筛选的查询，也可以用来执行同时基于city和last_name列进行筛选的查询；但是，对于那些只基于last_name列进行筛选的查询来说，PostgreSQL就无法高效地利用这种索引。
可以把这种机制想象成按城市先排序、然后再在每个城市内按姓氏排序的电话簿：你可以轻松地找到拉各斯的所有居民，也可以找到拉各斯所有姓“Adeyemi”的人；但要想找到所有城市中姓“Adeyemi”的人，就必须扫描整本电话簿。
这个原则被称为最左前缀规则：PostgreSQL可以使用复合索引来执行那些包含索引中最左边列的查询，但对于那些跳过了最左边列的查询，则无法使用这种索引。
-- ✅ 使用了索引（匹配了最左边的列）
SELECT * FROM customers WHERE city = 'Lagos';

-- ✅ 使用了索引（同时匹配了最左边和右边的列）
SELECT * FROM customers WHERE city = 'Lagos' AND last_name = 'Adeyemi';

-- ❌ 无法高效地使用此索引（跳过了最左边的列）
SELECT * FROM customers WHERE last_name = 'Adeyemi';

在决定列的排列顺序时，应该将那些能够最大程度缩小查询结果范围的列放在最前面。
如何创建部分索引
部分索引仅覆盖表中的一部分行。你可以通过在索引定义中使用WHERE子句来指定这一部分行。
当你只需要查询数据中的特定部分时，这种索引非常有用。例如，如果你有一个orders表，而你经常查询待处理的订单信息，但很少查看已完成的订单：
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 仅为状态为'pending'的记录创建索引
CREATE INDEX idx_orders_pending ON orders (customer_id)
WHERE status = 'pending';

由于这种索引会跳过所有不符合WHERE条件的记录，因此它的大小会比全索引小。体积较小的索引会占用更少的磁盘空间、消耗更少的内存，并且在写入数据时维护起来也会更快。
要想让索引能够被查询语句使用，你的查询中的WHERE子句必须与索引的条件相匹配：
-- ✅ 使用了部分索引
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

-- ❌ 无法使用部分索引（状态不同）
SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 42;

如何创建表达式索引
有时，你需要为函数或表达式的计算结果创建索引，而不是为原始的列值创建索引。表达式索引（也称为功能索引）就可以满足这种需求。
一个常见的例子就是不区分大小写的电子邮件查询。如果你的查询中使用了LOWER(email)函数，那么在email列上创建的普通索引是无法帮助你完成查询的——因为PostgreSQL会将这个函数调用视为一个不同的表达式。
-- 在email列上创建的普通索引对使用LOWER()函数的查询没有帮助
CREATE INDEX idx_email ON customers (email);

-- 下面的查询不会使用上面的索引
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

为了解决这个问题，你需要在表达式本身上创建一个索引：
CREATE INDEX idx_email_lower ON customers (LOWER(email));

现在，那些在WHERE子句中使用了LOWER(email)的查询就会使用这个新创建的索引了：
-- ✅ 使用了表达式索引
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

规则很简单：你的查询中的表达式必须与索引中的表达式完全一致。如果索引是基于LOWER(email)这个表达式创建的，那么你的查询中也必须使用LOWER(email)。
如何创建唯一索引
唯一索引可以确保被索引列中的任何两行都不会具有相同的值（或值组合）。它具有双重作用：既能够保证数据的一致性，又能提高查询效率。
CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);
如果你尝试插入重复的值，PostgreSQL会拒绝这个操作：
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'user1@example.com', 'Lagos');
-- 错误：重复的键值违反了唯一性约束 "idx_customers_email_unique"

你可能会想知道，这与 UNIQUE 约束有什么不同。实际上，PostgreSQL 是通过创建唯一索引来实现 UNIQUE 约束的。从功能上来说，这两种方式是完全相同的。
区别在于它们的目的：UNIQUE 约束体现的是数据完整性规则，而唯一索引则主要是为了提升查询性能，同时顺便满足唯一性要求。
如何管理索引
随着数据库规模的扩大，你将需要定期检查、监控并维护这些索引。
如何列出表上的所有索引
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'customers';

这条查询可以显示表上每个索引的名称及其完整定义。
如何检查索引大小
SELECT
    pg_size_pretty(pg_relation_size('idx_customers_email')) AS index_size;

如果你想全面了解所有索引及其大小，可以执行以下查询：
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'customers'
ORDER BY pg_relation_size(indexrelid) DESC;

如何查找未使用的索引
那些从未被使用过的索引会浪费磁盘空间，并降低数据写入的速度。你可以通过查询 pg_stat_user_indexes 来找到这些索引：
SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_userindexes
WHERE relname = 'customers'
AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

如果某个索引在经过一段时间的正常使用后，其 idx_scan 值仍然为 0，那么这个索引就可以被删除了。不过，在做出决定之前，请确保观察整个业务周期内的使用情况——因为有些索引可能只在每月生成报告或进行季节性操作时才会被使用。
如何删除索引
DROP INDEX IF EXISTS idx_customers_email;
如果你要删除生产环境中的表上的索引，并且希望避免因此导致写操作被锁定，可以使用 CONCURRENTLY 关键字：
DROP INDEX CONCURRENTLY IF EXISTS idx_customers_email;
如何重建索引
随着时间的推移，由于数据的插入、更新和删除操作，索引文件的大小可能会逐渐增大。你可以通过重建索引来释放占用的空间：
REINDEX INDEX idx_customers_email;
或者，可以重建表中的所有索引：
REINDEX TABLE customers;
在生产系统中，应使用 REINDEX CONCURRENTLY（PostgreSQL 12及以上版本支持）来避免锁定表格：
REINDEX INDEX CONCURRENTLY idx_customers_email;
当索引反而带来负面影响时
索引并非毫无成本。每当添加一个索引，都会产生相应的开销：


写操作的开销 — 每次执行 INSERT、UPDATE 或 DELETE 操作时，都需要更新表中所有的索引。如果一个表有10个索引，而你插入了一条记录，PostgreSQL实际上会执行11次写操作（一次针对表本身，另外10次分别针对每个索引）。对于那些需要频繁进行写操作的表格来说，过多的索引会显著降低数据修改的效率。


存储成本 — 索引会占用磁盘空间。对于大型表格而言，索引所占用的空间可能与表本身相当，甚至更多。你可以使用 pg_relation_size 命令来查看这些信息。


内存消耗 — PostgreSQL 会将经常被使用的索引缓存在内存中。索引越多，内存压力就会越大，这可能会导致一些有用的数据被从缓存中移除，从而影响其他查询的效率。


维护成本 — 索引需要定期进行维护（如清除无效数据、重新构建索引等），这些操作会增加数据库架构迁移的复杂性。



因此，我们应该问自己的不是“是否应该添加索引？”，而是“对于当前这个表格的工作负载来说，读取性能的提升是否足以弥补写入操作所带来的开销？”
阻碍索引使用的常见错误
即使你创建了完美的索引，PostgreSQL也可能不会使用它。以下是一些最常见的原因。
在函数中使用了被索引的列
-- 为 email 列创建索引
CREATE INDEX idx_email ON customers (email);

-- ❌ 因为使用了 LOWER() 函数，PostgreSQL 无法使用该索引
SELECT * FROM customers WHERE LOWER(email) = 'user1@example.com';

-- ✅ 解决方法：为 LOWER(email) 创建一个表达式索引
CREATE INDEX idx_email_lower ON customers (LOWER(email));

如果在 WHERE 子句中使用了被索引的列，并且该列被放在了函数内部，那么 PostgreSQL 就不会使用这个索引。此时，你需要创建一个与所使用的函数相匹配的表达式索引。
隐式类型转换
-- id 是一个带有索引的 INTEGER 类型列
-- ❌ 将字符串值赋给这个列会触发类型转换，从而导致索引无法被使用
SELECT * FROM customers WHERE id = '42';

-- ✅ 应该使用正确的数据类型
SELECT * FROM customers WHERE id = 42;

当查询中的值类型与列的类型不匹配时，PostgreSQL 会尝试进行类型转换，而这种转换可能会导致索引无法被使用。
在不同列上使用 OR 条件
-- ❌ 在不同的列上使用 OR 运算会阻碍索引的使用
SELECT * FROM customers WHERE email = 'user1@example.com' OR city = 'Lagos';

-- ✅ 将查询改写为 UNION 可以更好地利用索引
SELECT * FROM customers WHERE email = 'user1@example.com'
UNION
SELECT * FROM customers WHERE city = 'Lagos';

在 LIKE 查询中使用前置通配符
-- ❌ 前置通配符会使得数据库无法使用 B 树索引
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- ✅ 后置通配符可以使用 B 树索引
SELECT * FROM customers WHERE email LIKE 'user1%';

B 树索引是按照从左到右的顺序进行排序的。如果查询中使用前置通配符（如 %something），数据库就无法利用这种排序结构，而不得不进行顺序扫描。如果你需要根据后缀或子字符串来进行搜索，可以考虑使用带有 pg_trgm 扩展名的 GIN 索引。
选择度低
如果某列中不同值的数量相对于总行数来说非常少（即选择度较低），PostgreSQL 可能会认为进行顺序扫描比使用索引更快。
例如，如果一个 status 列只有三种可能的值（'pending'、'shipped'、'delivered'），且每种值对应的记录数大约占表格总记录数的三分之一，那么仅为 status 列创建索引并不会带来太大效果。PostgreSQL 仍然需要读取表格中的大部分数据，而额外的索引查询操作只会增加性能开销。
在这种情况下，创建部分索引通常会是更好的解决方案。
建立索引的最佳实践
以下是一些关键原则的总结：


为那些出现在 WHERE、JOIN 和 ORDER BY 子句中的列创建索引。这些正是数据库需要进行搜索、匹配或排序的列。优先考虑那些执行频率最高或耗时最长的查询。


使用 EXPLAIN ANALYZE 来测试创建索引前后的性能差异。千万不要凭猜测来决定是否创建索引。先使用 EXPLAIN ANALYZE 执行查询，然后创建索引后再重新执行查询。如果执行时间没有明显改善，那就说明这个索引并没有起到预期的作用。


不要为所有列都创建索引。每个索引都会降低写入速度并占用更多存储空间。要根据实际的查询模式来有针对性地创建索引。


对于需要同时过滤多个列的数据，使用复合索引会更高效。如果你的查询经常同时涉及 city 和 last_name 这两列，那么为 (city, last_name) 创建复合索引会比分别为这两列创建单独的索引更有效率。



在复合索引中，将选择度最高的列放在首位。能够最有效地缩小搜索范围的列应该被排在索引的第一位。


当只需要查询数据的一部分时，使用部分索引会更有优势。如果 90% 的查询都是针对那些 status = 'active' 的记录进行的，那么为这些记录创建部分索引会比创建完整索引更加高效且占用更少的存储空间。



定期监控索引的使用情况。通过执行 pg_stat_user_indexes 命令来检查哪些索引没有被使用到，并及时删除它们。



定期重新构建那些变得臃肿的索引。对于那些经常发生大量更新或删除操作的表格，索引很容易变得庞大且效率低下。在生产环境中，可以使用 REINDEX CONCURRENTLY 命令来重新构建索引。


结论
通过本教程，你了解了什么是数据库索引，以及为什么它们对查询性能至关重要。你学习了B树索引在内部是如何工作的，创建了多种类型的索引（单列索引、复合索引、部分索引、表达式索引和唯一索引），并使用EXPLAIN ANALYZE命令来评估这些索引的实际效果。
你还了解了索引会带来的一些权衡因素——写入开销、存储成本以及内存占用问题——同时也认识到了那些会阻碍PostgreSQL有效利用这些索引的常见错误。
核心原则很简单：要根据你的实际查询需求有针对性地创建索引，然后测试其效果，及时删除那些无法发挥应有作用的索引。
如果你觉得本教程对你有帮助，可以在freeCodeCamp上阅读我更多的文章，在LinkedIn和X平台上与我保持联系。