必须了解的PostgreSQL索引知识_postgresql重建索引和删除后新建索引的区别

索引可以增强数据库性能，利用索引可以快速查找到特定数据行。但索引增加存储空间，因此适当地使用索引非常重要。本文介绍与索引相关的几个非常重要知识点。

1. 表达式索引

除了可以在一个或多个字段上定义索引，也可以基于与字段相关的表达式创建索引————表达式索引，也就是基于函数的索引。语法如下：

CREATE INDEX index_name 
ON table_name (expression);
1
2

一旦有了函数索引，当定义的表达式出现在where或order by子句中，PostgreSQL会使用函数索引。需要提醒的是，表达式索引维护成本较高，当插入或更新记录时都需要评估表达式更新索引信息。只有当查询性能比插入和更新更重要时才应该使用。

示例表：customer(customer_id,first_name,last_name,email,address_id,create_date,last_update,active)，我们在last_name上定义索引idx_last_name。则下面语句会使用索引。

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    last_name = 'Purdy';
1
2
3
4
5
6
7
8
9

但是下面语句不会使用上面定义的索引：

EXPLAIN
SELECT 
    customer_id, 
    first_name, 
    last_name 
FROM 
    customer 
WHERE 
    LOWER(last_name) = 'purdy'; -- 因为字段上使用了函数
1
2
3
4
5
6
7
8
9

这时可以定义函数索引：

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

这时就可以利用索引了。

2. 组合索引

组合字段索引最多支持32列，该参数可以在构建PostgreSQL时修改pg_config_manual.h文件。组合索引仅支持B-tree, GIST, GIN, BRIN类型索引。语法如下：

CREATE INDEX index_name
ON table_name(a,b,c,...);
1
2

在定义组合索引时，应该将经常在WHERE子句中使用的列放在前面，将不太经常在条件的字段放在后面。对于上面的组合索引，下面语句可以利用索引：

WHERE a = v1 and b = v2 and c = v3;

-- 或者

WHERE a = v1 and b = v2;

-- 或者

WHERE a = v1;
1
2
3
4
5
6
7
8
9

但下面查询语句不能使用到组合索引：

WHERE  c = v3;

-- 或者

WHERE b = v2 and c = v3;    
1
2
3
4
5

3. 局部索引

可以参考上篇PostgreSQL 局部索引(Partial Index)教程。

4. 重建索引

在实践中，由于硬件故障或软件bug，索引可能会损坏，不再包含有效数据。语法如下：

REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;
1

VERBOSE是可选的，包括VERBOSE时，语句显示每个索引重建的进度报告。

重建单个索引，可以使用下面语句：

REINDEX INDEX index_name;
1

为了重建表上的所有索引，语法如下：

REINDEX TABLE table_name;   
1

重建schema下的所有索引，语法：

REINDEX SCHEMA schema_name;
1

重建数据库上的所有索引，语法：

REINDEX DATABASE database_name;
1

下面语句重建系统目录的索引：

REINDEX SYSTEM database_name;
1

重建索引即从头开始重新索引内容，与删除索引再重新创建索引效果类似。但在锁机制上有差异。

• 重建索引

有写锁，但没有读锁。获取正在处理的索引上的排他锁，该锁阻塞试图使用该索引的读取操作。

• 删除索引再创建

首先DROP索引通过获取表上的排他锁来锁定索引所属的表的写和读操作。随后的CREATE INDEX语句锁定索引对应表的写操作，而不是读操作。然而，在创建索引期间，读取可能是昂贵的。

5. 总结

本文介绍了索引的一些知识，函数索引、组合索引、局部索引，以及如何重建索引。了解这些知识可以在实际应用中争取应用提升数据库性能。