索引

PostgreSQL快速入门
600
2023/08/01 13:37:10
postgresql

PostgreSQL 索引是增强数据库查询性能的有效工具。然而，索引增加了数据库系统的写入和存储开销。因此，正确使用它们非常重要。本章我们将讨论如何使用索引。

实际生活中的类比

假如《新华字典》没有按拼音排序，或者没有按部首索引，你要查某个字的含义时，你必须逐页、逐行地查找，直到在某一页某一行查到该字为止。这种查找方式称为顺序扫描：需要遍历所有条目，直到找到所需要的条目。

与此类似，表中存储的数据应按特定顺序进行组织，以加快各种搜索速度。这就是索引发挥的作用。

索引是一种独立的数据结构，它可以加速数据检索，但需要额外的写入和存储来维护它。

创建索引

创建索引的简单版本：

CREATE INDEX 索引名称 ON 表名 [USING 索引方法]
(
    字段名 [ASC | DESC] [NULLS {FIRST | LAST }],
    ...
);

  • 首先指定索引名称

  • 其次指定索引所属的表

  • 然后，指定索引方法。可用的索引方法有：btree(默认值)、hash、gist、spgist、gin和brin。

要检查某个查询是否使用了索引，请使用 EXPLAIN 语句

示例

我们使用示例数据库中的 address 进行演示。

假设我们想查找电话是223664661973 的地址信息：

EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

/* 结果
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on address  (cost=0.00..15.54 rows=1 width=61)
   Filter: ((phone)::text = '223664661973'::text)
*/

从分析结果可知，这个查询使用了顺序扫描。原因在于，我们并没有给 phone 建立索引。为此，我们可以给它建立索引：

CREATE INDEX idx_address_phone 
ON address(phone);

再次分析：

 EXPLAIN SELECT *
FROM address
WHERE phone = '223664661973';

/* 结果
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using idx_address_phone on address  (cost=0.28..8.29 rows=1 width=61)
   Index Cond: ((phone)::text = '223664661973'::text)
*/

已经使用了索引扫描。

删除索引

有时候可能会想要从数据库中删除现有索引，为此可以使用 DROP INDEX 语句，其语法如下：

DROP INDEX  [ CONCURRENTLY]
[ IF EXISTS ]  index_name 
[ CASCADE | RESTRICT ];
  • 当执行 DROP INDEX 时，PostgreSQL 会获取表上的独占锁并阻止其它访问，直到索引删除完成。要强制等待冲突完成后再删除索引，可以加上 CONCURRENTLY 选项。但 CONCURRENTLY 有一些限制：
    • 首先，它不支持 CASCADE
    • 其次，不支持在事务中执行
  • 尝试删除不存在的索引将引发错误，为避免这种情况，可以加上 IF EXISTS。
  • CASCADE：如果索引具有依赖对象，该选项将在删除该索引的同时，自动级联删除那些依赖对象
  • RESTRICT：如果索引具有依赖对象，则拒绝删除该索引。这是默认值。

示例

我们使用示例数据库中的 actor 进行演示。

首先，为 first_name 字段创建索引：

CREATE INDEX idx_actor_first_name 
ON actor (first_name);

我们想查找 first_name 为 John 的演员，分析一波：

EXPLAIN SELECT *
FROM actor
WHERE first_name = 'John';

/* 结果
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
   Filter: ((first_name)::text = 'John'::text)
*/

从分析结果看到， PostgreSQL 并没有使用我们创建的 idx_actor_first_name 索引，而是使用顺序扫描来进行全表扫描：在某些情况下，PostgreSQL 会选择使用顺序扫描而无视索引，这是因为查询优化器认为全表扫描是最优解。这个时候创建索引就没必要了。

基于此，我们删除这个索引：

DROP INDEX idx_actor_first_name;

索引列表

可以使用 SQL 语句来列出所有创建的索引：

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;

psql 提供了对应的命令：

\d 表名

索引类型

PostgreSQL 有多种索引类型，下面分别进行介绍。

B-tree 索引

它是一种自平衡树，它维护排序的数据并允许在对数时间内，搜索、插入、删除和顺序访问。每当索引涉及使用以下比较运算符之一时，PostgreSQL 查询规划器将考虑使用该种索引：

<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

此外，如果模式是常量，并且锚定在查询的开头，则查询规划器对涉及 LIKE 和 ~ 的查询使用该种索引。

如果模式以非字母字符开头，则查询规划器将考虑对 ILIKE 和 ~* 使用该种索引。

大部分情况下，如果你不知道使用何种类型的索引，选择B-tree即可。

Hash 索引

Hash 索引只能处理简单的相等比较（=）。这意味着，每当索引字段涉及使用=运算符时，查询规划器都考虑使用该种索引 。要创建 Hash 索引，可以这样：

CREATE INDEX 索引名 
ON 表名 USING HASH (字段名);

Gin 索引

Gin 索引代表广义倒排索引（ generalized inverted）。当字段中存储多个值（例如 hstore、array、jsonb 和 range 等数据类型）时，该索引最有用。

BRIN 索引

BRIN 代表块范围索引（ block range indexes），与B-Tree相比，它更小、维护成本也更低。它允许在非常大的表上使用索引，这在没有使用水平分区之前，B-Tree 根本不可能实现。

它通常用于具有线性排序的顺序字段，比如销售订单表的创建日期字段。

GiST索引

SP-GiST 索引

SP-GiST 代表空间划分的 GiST（space-partitioned GiST）。它支持分区搜索树，有助于开发各种不同的非平衡数据结构。对于具有自然聚类元素具不是相等平衡树的数据最有用，比如 GIS、多媒体、电话路由和IP路由等。

表达式索引

除了对某个/某些字段建立索引，有时候也需要对涉及字段的表达式创建索引，这就是表达式索引。它也称为基于函数的索引。

创建表达式索引的语法：

CREATE INDEX 索引名 
ON 表名 (表达式);

一旦定义了表达式索引，那么在 WHERE 、ORDER BY 子句中，PostgreSQL 都将考虑使用该表达式索引。

请注意，表达式上的索引维护起来非常昂贵，因为 PostgreSQL 必须在插入或更新每一行时计算该表达式，并使用结果进行索引。因此，当检索速度比插入和更新速度更重要时，您应该在表达式上使用索引。

示例

为 curstomer 表创建 LOWER(last_name) 的表达式索引：

CREATE INDEX idx_ic_last_name
ON customer(LOWER(last_name));

之后，分析一波：

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    LOWER(last_name) = 'purdy';

部分索引

PostgreSQL 甚至允许为某些数据行创建索引。这部分索引有助于加快查询速度，同时减少索引大小。

如果 WHERE 条件使用常量值，则部分索引非常有用，比如：

SELECT * 
FROM 表名
WHERE 字段名 = 常量值;

创建部分索引的语法如下：

CREATE INDEX 索引名
ON 表名(字段列表)
WHERE 条件;

CREATE INDEX idx_customer_inactive
ON customer(active)
WHERE active = 0;

分析一波：

EXPLAIN SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

重建索引

在实际生活中，由于硬件故障或软件错误，可能导致索引损坏。可以使用 REINDEX 来重建索引，以恢复受损的索引。它的语法如下：

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;
  • VERBOSE 是可选的，如果包括该关键字，则在重建过程中会显示进度

重建单个索引：

REINDEX INDEX 索引名;

重建某个表中的所有索引：

REINDEX TABLE 表名;

重建模式中的所有索引：

REINDEX SCHEMA 模式名;

重建数据库中所有索引：

REINDEX DATABASE 数据库名;

重建数据中，系统目录上的所有索引：

REINDEX SYSTEM 数据库名;