Skip to main content
 首页 » 数据库

必须了解的PostgreSQL索引知识

2022年07月19日219zhengyun_ustc

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

1. 表达式索引

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

CREATE INDEX index_name  
ON table_name (expression); 

一旦有了函数索引,当定义的表达式出现在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'; 

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

EXPLAIN 
SELECT  
    customer_id,  
    first_name,  
    last_name  
FROM  
    customer  
WHERE  
    LOWER(last_name) = 'purdy'; -- 因为字段上使用了函数 

这时可以定义函数索引:

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

这时就可以利用索引了。

2. 组合索引

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

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

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

WHERE a = v1 and b = v2 and c = v3; 
 
-- 或者 
 
WHERE a = v1 and b = v2; 
 
-- 或者 
 
WHERE a = v1; 

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

WHERE  c = v3; 
 
-- 或者 
 
WHERE b = v2 and c = v3;     

3. 局部索引

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

4. 重建索引

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

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

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

重建单个索引,可以使用下面语句:

REINDEX INDEX index_name; 

为了重建表上的所有索引,语法如下:

REINDEX TABLE table_name;    

重建schema下的所有索引,语法:

REINDEX SCHEMA schema_name; 

重建数据库上的所有索引,语法:

REINDEX DATABASE database_name; 

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

REINDEX SYSTEM database_name; 

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

  • 重建索引

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

  • 删除索引再创建

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

5. 总结

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


本文参考链接:https://blog.csdn.net/neweastsun/article/details/113482756
阅读延展