📚 目录

  1. 概述
  2. 索引的基本概念
  3. 创建索引
  4. 索引类型
  5. 使用索引的场景
  6. 维护和优化索引
  7. 参考资料

🛠️ 概述

索引（Index） 是数据库中的一种数据结构，用于提高查询操作的速度。PostgreSQL 提供了多种类型的索引，它们通过创建一份数据的“索引表”来加速数据检索的过程。索引通常用于加速 SELECT 查询，但也可以加速某些 UPDATE 和 DELETE 操作。尽管索引能够提高查询性能，但它们也可能带来额外的存储开销，并在插入或更新数据时增加性能负担。

📝 索引的基本概念

索引通过为表中的某些列创建指针来加速查询操作。数据库使用这些指针定位数据，而不是扫描整个表。这类似于书籍的目录，能够让你快速找到所需的页面，而不需要逐页翻阅。

优点：

  • 加速查询：在大数据量的表中，索引能够显著提高查询速度，尤其是对 WHERE 子句和 JOIN 操作。
  • 提高排序效率：索引可以加速 ORDER BY 子句和某些聚合操作（如 MAX、MIN）。
  • 加速 LIKE 查询：对于前缀匹配的 LIKE 查询，索引可以显著提高查询性能。

缺点：

  • 增加存储：索引会占用额外的磁盘空间，尤其是当创建多个索引时。
  • 写操作性能下降：在进行 INSERT、UPDATE 或 DELETE 操作时，索引需要同步更新，因此可能会导致这些操作变慢。

📝 创建索引

在 PostgreSQL 中，可以使用 CREATE INDEX 语句来创建索引。

语法：

CREATE INDEX index_name
ON table_name (column_name);
  • index_name：指定索引的名称。
  • table_name：指定创建索引的表名。
  • column_name：指定要为其创建索引的列名。

示例（创建简单索引）：

CREATE INDEX idx_employee_name
ON employees (name);

此语句会为 employees 表的 name 列创建一个简单的索引。

示例（创建复合索引）：

CREATE INDEX idx_employee_name_department
ON employees (name, department_id);

此语句为 employees 表的 name 和 department_id 列创建一个复合索引。

📝 索引类型

PostgreSQL 支持多种索引类型，具体选择哪种类型的索引取决于数据的使用方式和查询模式。

  1. B-tree 索引：这是默认的索引类型，适用于大多数查询。适用于范围查询和等值查询。 示例（创建 B-tree 索引）： CREATE INDEX idx_employee_salary ON employees USING btree (salary);
  2. Hash 索引：用于等值查询，适用于快速的查找操作，然而在 PostgreSQL 中，Hash 索引的使用较少，因为它只适用于等值比较，且不支持范围查询。 示例（创建 Hash 索引）： CREATE INDEX idx_employee_id_hash ON employees USING hash (id);
  3. GiST 索引（Generalized Search Tree）：用于支持空间数据和复杂类型查询，如地理位置数据（PostGIS）、全文索引等。
  4. GIN 索引（Generalized Inverted Index）：常用于支持数组、JSONB 等复杂数据类型的索引。
  5. SP-GiST 索引：用于支持具有层次结构的多维数据结构（如树或图）的索引。
  6. BRIN 索引（Block Range INdexes）：适用于数据按某种顺序存储的情况，适用于极大的表，特别是时间序列数据。

📝 使用索引的场景

  • 加速查询：在频繁进行查询的列上创建索引可以显著提高查询性能，尤其是对 WHERE 子句和 JOIN 操作。
  • 提高排序和聚合性能：对于需要进行排序或聚合操作的查询，索引可以加速这些操作的执行。
  • 加速文本搜索：使用 GIN 索引，可以加速对 JSONB 或 ARRAY 类型字段的搜索。

示例（使用索引加速查询）：

SELECT * FROM employees WHERE name = 'John Doe';

假设 name 列上已创建索引，该查询将通过索引快速定位到 John Doe，而无需扫描整个表。

📝 维护和优化索引

随着数据的增多和更新，索引的效率可能下降。以下是一些常见的索引维护操作：

  • 重建索引：随着时间的推移，索引可能会变得碎片化，重建索引可以提高其效率。 REINDEX INDEX idx_employee_name;
  • 删除索引：如果某个索引不再需要，可以通过 DROP INDEX 删除它，以节省存储空间。 DROP INDEX idx_employee_name;
  • 查看索引使用情况：通过 pg_stat_user_indexes 视图，可以查看索引的使用情况。 SELECT * FROM pg_stat_user_indexes WHERE relname = 'employees';

📘 参考资料