索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。

分类

B-tree：最常用的索引，B-tree 索引适合处理等值查询和范围查询
Hash：只能处理简单的等只查询
GiST：不是一种单独的索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。GiST 索引定义的特定操作符可以用于特定索引策略。PostgreSQL 的标准发布中包含了用于二维几何数据类型的 GiST 操作符类。比如，一个图形包含另一个图形的操作符“@>”，一个图形在另一个图形的左边没有重叠的操作符“<<”，等等
SP-GiST：SP-GiST 是“space-partitioned GiST”的缩写，即空间分区 GiST 索引。主要是通过一些新的索引算法提高 GiST 索引再某个情况下的性能。
GIN：反转索引，它可以处理包含多个键的值，如数组等。与 GiST 类似，GIN 支持用户定义的索引策略，可通过定义 GIN 索引的特定操作符类型实现不同的功能。比如，它支持包含操作符“@>”、被包含操作符“<@”、相等操作符“=”、重叠操作符“&&”，等等。

CREATE

语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

CONCURRENTLY：并发创建索引。当该选项被使用时，PostgreSQL 会执行表的两次扫描，因此该方法需要更长一些的时间来创建索引

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE INDEX index_name ON table_name (column_name);

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

CREATE INDEX index_name ON table_name (column1_name, column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHERE 子句的过滤条件中使用非常频繁的列。

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

局部索引

局部索引是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

CREATE INDEX index_name ON table_name (conditional_expression);

隐式索引

隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

查看

\d：查看表下的索引

testdb=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

\di：查看所有索引

runoobdb=# \di
                    List of relations
 Schema |      Name       | Type  |  Owner   |   Table    
--------+-----------------+-------+----------+------------
 public | company_pkey    | index | postgres | company
 public | department_pkey | index | postgres | department
 public | salary_index    | index | postgres | company
(3 rows)

SELECT

select * from pg_indexes where tablename ='table_name';

删除索引

DROP INDEX index_name;

修改索引

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

准则

创建

主键自动建立唯一索引；
频繁作为查询条件的字段应该创建索引；
查询中与其他表有关联的字段，例如外键关系；
高并发的情况下一般选择复合索引；
查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
查询中统计或者分组的字段；
存储空间固定的字段更适合选作索引的关键字，例：与 text 类型的字段相比， char 类型的字段较为适合选作索引关键字

不创建

索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。
经常增删改的表，不需要创建索引；
表中包含大量重复数据，不需要创建索引，例如性别字段，只有男女，不适合建立索引；

常见失效原因

没有查询条件，或者查询条件没有建立索引
在查询条件上没有使用引导列
查询的数量是大表的大部分，应该是 30％以上
使用内部函数导致索引失效，例：
```
select * from test where round(id)=1
```
表记录较少
隐式转换导致索引失效，例如：表的字段 tu_mdn 定义为 varchar2(20)，但在查询时把该字段作为 number 类型以 where 条件传给数据库：

错误的例子：

select * from test where tu_mdn=13333333333;

正确的例子：

select * from test where tu_mdn='13333333333';

索引列进行运算导致索引失效，运算包括(+，-，*，/，! 等)，例：
```
select * from test where round(id)=1
```
like "%_" 百分号在前
单独引用复合索引里非第一位置的索引列
B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
并发创建索引过程中，如果被强行取消，可能会留下一个无效的索引，这个索引仍然会导致更新变慢。如果所创建的是一个唯一索引，这个无效的索引还会导致插入重复值失败。手工删除重建即可修复

在线重新创建索引

CREATE INDEX CONCURRENTLY ON <index_name> USING btree(id);

ITPub博客

PostgreSQL：INDEX

分类