返回所有指南
性能··阅读约需 8 分钟

数据库索引策略:加速 SQL 查询

了解数据库索引的工作原理、何时使用它们以及常见的陷阱。通过 SQL 示例涵盖 B 树、复合索引和覆盖索引。

复合索引示例,旁边是 B 树查找,沿着从 40 到 60 再到键 50 的路径
在本指南中
  1. 什么是数据库索引?
  2. 何时应使用索引?
  3. 索引的类型
  4. 常见索引错误
  5. 监视索引使用情况
  6. 架构设计和 ERD 中的索引
  7. 参考资料与延伸阅读

除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。

什么是数据库索引?

一个 索引 是数据库引擎维护的数据结构,它提供表行数据的快速查找路径。对于选择性筛选,索引可以减少读取的行数或页数。对于小型表或返回大量行的查询,优化器仍可能选择表扫描。测量计划和计时,而不是假设固定的加速。

B-树索引的工作原理

PostgreSQL、MySQL、SQL Server 和 SQLite 中最常见的索引类型是 B-tree (平衡树)。可以把它想象成电话簿:你不需要从 A 到 Z 阅读每一个名字,而是翻到大致的区段,缩小到正确的页面,然后找到条目。B 树的工作原理类似:

  • 树被组织成 节点。每个内部节点都包含已排序的键和指向子节点的指针。
  • 查找开始于 根, 将搜索值与节点键进行比较,并通过 分支 节点。
  • 它达到了 叶子 包含匹配的条目。根据引擎和索引的不同,条目会标识行、包含聚集键或保存行数据本身。
  • 树保持平衡,但其深度和 I/O 取决于键宽度、页面大小、行数、缓存以及查询是否也获取表页面。
-- Basic index on a single column
CREATE INDEX idx_employees_email ON employees (email);

-- The database now maintains a B-tree on the email column.
-- A selective filter is a candidate for an index lookup; check EXPLAIN:
SELECT * FROM employees WHERE email = '[email protected]';

何时应使用索引?

索引并非免费的。它们会消耗磁盘空间,并为每个操作增加开销 INSERT, UPDATE, 以及 DELETE 因为引擎必须保持索引与表数据同步。请有策略地使用它们:

  • 列在 WHERE 子句。 合适的索引可以帮助频繁的选择性过滤;其益处取决于数据分布和所选计划。
  • 列在 JOIN 条件。 索引可能有助于外键连接和父级更新或删除;大型连接仍然可能更偏向于扫描。
  • 列在 ORDER BY 和 GROUP BY. 索引可以提供预排序的数据,从而省去代价高昂的排序操作。
  • 高基数列。 具有许多不同值(如电子邮箱或 UUID)的列比具有较少不同值的列(如布尔值 is_active 标志)。
  • 大型表。 拥有 100 行的表很少需要额外的索引。拥有 1000 万行的表几乎肯定需要。

作为一般规则: 在没有额外索引的情况下开始,通过以下方式测量查询性能 EXPLAIN, 然后在数据指向的位置添加索引。

索引的类型

单列索引

最简单的形式。在单列上创建 B 树。非常适合仅基于该列进行筛选或排序的查询。

-- PostgreSQL / MySQL / SQLite
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders (customer_id);

复合(多列)索引

包含两个或多个列的索引。前导列上的等值过滤以及下一列上的范围过滤通常会形成高效的 B 树访问路径。其他条件仍可能使用扫描或跳过扫描;支持和成本因引擎和版本而异。

CREATE INDEX idx_orders_status_date
    ON orders (status, ordered_at);

-- Leading equality plus a date range is a candidate for this index:
SELECT * FROM orders
WHERE status = 'shipped' AND ordered_at > '2025-01-01';

-- Filtering on the leading column may also use this index:
SELECT * FROM orders WHERE status = 'pending';

-- A date-only filter may scan or use a skip scan; inspect the plan:
SELECT * FROM orders WHERE ordered_at > '2025-01-01';

从相等过滤器、范围条件、排序和覆盖需求中选择列顺序。“最严格的先选”并非普遍规则。PostgreSQL 18 可以对合适的非前导过滤器使用 B 树跳跃扫描。

唯一索引

强制执行唯一性约束,同时提供基于索引的查找性能。当您定义以下内容时,大多数数据库会自动创建一个唯一索引: UNIQUE 约束或 PRIMARY KEY.

CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Enforces uniqueness; constraint metadata and eligible FK targets vary by engine:
-- ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

部分(过滤)索引

仅对满足以下条件的行建立索引: WHERE 条件。这使索引保持较小且高度针对性。

-- PostgreSQL partial index
CREATE INDEX idx_orders_active
    ON orders (customer_id)
    WHERE status NOT IN ('delivered', 'cancelled');

-- SQL Server: a supported simple comparison filter
CREATE INDEX idx_orders_pending
    ON orders (customer_id)
    WHERE status = 'pending';

部分或过滤索引可以帮助小型且频繁查询的子集。查询谓词必须暗示过滤器。SQL Server 的过滤语法比 PostgreSQL 的更为受限;请勿在它们之间随意复制谓词。

其他索引类型

索引类型最适合数据库支持
哈希精确相等查找(=)PostgreSQL, MySQL (Memory engine)
GIN全文搜索、JSONB、数组PostgreSQL
GiST几何和范围数据类型PostgreSQL
BRIN具有自然排序数据的非常大的表PostgreSQL
全文文本搜索,带相关性排名MySQL, SQL Server

常见索引错误

即使是有经验的开发者也会陷入这些陷阱。请避免它们以保持良好的数据库性能。

1. 过度索引

在每个列上“以防万一”地添加索引会浪费磁盘空间并减慢写入速度。每次插入、更新和删除时都必须更新每个索引。具有 15 个索引的表的写入吞吐量将明显低于具有 3 个精心挑选的索引的表。

2. 为低基数列建立索引

对布尔值建立索引 is_active 仅包含以下的列 TRUE 和 FALSE 当值高度倾斜或索引覆盖狭窄的查询时会很有用。仅凭基数无法决定索引是否有帮助。如果您必须在此类列上进行过滤,请将它们与具有更高基数的列组合在复合索引中,或者使用部分索引。

3. 忽略查询模式

索引只有在您的实际查询能够利用它时才有用。务必检查应用程序中的慢查询 首先, 然后设计针对这些特定访问模式的索引。

-- Use EXPLAIN to see if your index is being used
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 7 AND status = 'shipped';

-- Look for "Index Scan" or "Index Only Scan" in the output.
-- Bitmap Index Scan is another index access path. Seq Scan can be appropriate.

4. 忘记为外键建立索引

在 PostgreSQL 中,外键列是 不 自动建立索引(与 MySQL 的 InnoDB 不同,后者默认在外键上创建索引)。索引有助于选择性连接和引用检查,但优化器可能会选择扫描或哈希连接。在添加另一个索引之前,请检查现有的前导列已经覆盖该外键的索引。

-- Consider these only when equivalent access paths do not exist
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

5. 从不审查未使用的索引

随着时间推移,应用程序代码会发生变化,旧的索引可能不再服务于任何查询。未使用的索引仍然会消耗存储空间并减慢写入速度,而没有任何读取收益。

监视索引使用情况

现代数据库提供系统视图,用于报告每个索引的使用频率。定期审查这些视图有助于您删除未使用的索引并发现缺失的索引。

PostgreSQL

-- Find unused indexes (0 scans since last stats reset)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

MySQL

-- MySQL: no row fetches recorded since these counters were reset
SELECT
    object_schema,
    object_name AS table_name,
    index_name,
    count_fetch AS row_fetches
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_fetch = 0
  AND object_schema = 'your_database';

将零次读取视为候选审查对象,而不是删除索引的许可。检查计数器何时重置、是否启用了检测工具以及是否需要进行季节性查询、约束或外键检查。使用代表性工作负载测试建议的删除。

架构设计和 ERD 中的索引

虽然 ERD 主要关注表、列和关系,但索引是整个模式设计的重要组成部分。在规划数据库时,请将索引与表定义一起考虑:

  • A 主键 通常有一个引擎管理的访问路径。SQLite INTEGER PRIMARY KEY 使用 rowid 而不是单独的唯一索引;在添加重复索引之前检查引擎。
  • 审查访问路径以便 外键。PostgreSQL 不会自动创建子侧索引,但现有的复合索引可能已经覆盖了该关系。
  • 包含以下列的 唯一约束 接收一个自动的唯一索引。
  • 应将针对频繁查询的列的附加索引与您的架构 DDL 一起记录,以免在部署期间被遗忘。
-- Include indexes in your schema DDL files for reproducibility
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category_id INT REFERENCES categories (id),
    price       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_products_created ON products (created_at DESC);

通过将 DDL 粘贴到以下位置来可视化您的完整模式—表、关系以及您计划索引的列 Erd Go 编辑器。视觉化地查看结构通常会暴露出需要建立索引的列(如频繁进行 JOIN 的外键),而这些列在阅读原始 SQL 时可能会被您忽略。

参考资料与延伸阅读

更多文章