5.PG基础之索引-程序员充电站

索引介绍

索引是数据库中的一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系，就好比是一本书前面的目录，通过目录中页码就能快速定位到我们需要查询的内容。

索引的代价

增加了数据库的存储空间
在插入和修改数据时要花费较多的时间，因为索引也要随之更新

索引其他用途

除了加快查询以外，比如唯一索引还可以起到唯一约束的作用。

索引的分类

BTree：最常用的索引，适合用于处理等值查询和范围查询。
HASH：只能处理简单的等值查询。
GiST：不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。
SP-GiST：即空间分区 GiST 索引，PostgreSQL 9.2 引入。
GIN：反转索引，可以处理包含多个键的值。

创建索引

案例表结构

CREATETABLEcontacts(idintPRIMARYKEY,namevarchar(40),phonevarchar(32)[],addresstext);

1. 简单 BTree 索引

为了实现按姓名name快速查询，可以在字段name上建一个 BTree 索引：

CREATEINDEXidx_contacts_nameONcontacts(name);

2. GIN 索引（用于数组字段）

如果想按电话号码phone字段做快速查询（例如查询某个电话号码是谁的），由于此字段是一个数组，BTree 索引不再适用，可以建一个 GIN 索引：

CREATEINDEXidx_contacts_phoneONcontactsUSINGgin(phone);

查询示例：

SELECT*FROMcontactsWHEREphone @>ARRAY['13422334455'::varchar(32)];

解释：@>是数组操作符，表示“包含”的意思，GIN 索引能在@>上起作用。

创建索引时带参数

CREATEINDEXidx_contacts_nameONcontacts(name)WITH(FILLFACTOR=50);

按降序创建索引

CREATEINDEXidx_contacts_nameONcontacts(nameDESC);

指定空值排序

-- 空值排在非空值前面CREATEINDEXidx_contacts_nameONcontacts(nameDESCNULLSFIRST);-- 空值排在非空值后面CREATEINDEXidx_contacts_nameONcontacts(nameDESCNULLSLAST);

并发创建索引

默认方式的问题

默认情况下，创建索引时会锁定表以防止写入，然后对表做全表扫描。在此过程中：

其他用户可以读取表
插入、更新、删除等操作将一直被阻塞，直到索引创建完毕

示例

CREATETABLEtesttab01(idintPRIMARYKEY,noteint);INSERTINTOtesttab01SELECTgenerate_series(1,5000000),generate_series(1,5000000);

在一个窗口中创建索引：

CREATEINDEXidx_testtab01_noteONtesttab01(note);

在另一个窗口中执行删除操作将被阻塞：

DELETEFROMtesttab01WHEREid=1;

使用`CONCURRENTLY`选项创建索引

这种方式不会长时间阻塞 DML 操作，但耗时更长，因为需要两次扫描表。

示例

DROPINDEXIFEXISTSidx_testtab01_note;CREATEINDEXCONCURRENTLY idx_testtab01_noteONtesttab01(note);

另一个窗口的删除语句可以正常执行：

DELETEFROMtesttab01WHEREid=2;DELETEFROMtesttab01WHEREid=3;

并发创建索引的“坑”

如果在并发创建索引过程中取消操作，可能会导致索引残留（状态为INVALID）。

处理方法

-- 查看表结构，确认无效索引\d testtab01-- 手动删除无效索引DROPINDEXidx_testtab01_note;

修改索引

重命名索引

ALTERINDEXidx_contacts_nameRENAMETOidx_contacts_name_old;

移动索引到指定表空间

ALTERINDEXidx_contacts_name_oldSETTABLESPACEtbs_data01;

修改填充因子

ALTERINDEXidx_contacts_name_oldSET(fillfactor=75);

查看索引信息

\d+idx_contacts_name_old

删除索引

安全删除（存在才删除）

DROPINDEXIFEXISTSidx_contacts_name_old;

默认`RESTRICT`行为

如果有对象依赖该索引，删除会失败。

示例

CREATETABLEclass(class_noint,class_namevarchar(40));CREATEUNIQUEINDEXindex_unique_class_noONclass(class_no);CREATETABLEstudent(student_nointPRIMARYKEY,student_namevarchar(40),ageint,class_nointREFERENCESclass(class_no));-- 删除索引会失败，因为有外键依赖DROPINDEXindex_unique_class_no;