postgresql_索引

1.简介：

索引是用于加速从数据库检索数据的特殊查找表。

索引为出现在索引列中的每个值创建一个条目。

2.特点：

索引使用SELECT查询和WHERE子句加速数据输出，但是会减慢使用INSERT和UPDATE语句输入的数据。

3.创建：

CREATE INDEX index_name ON table_name;

单列索引，CREATE INDEX index_name ON table_name (column_name);

多列索引，CREATE INDEX index_name ON table_name (column1_name, column2_name);

唯一索引【该字段不能重复】，CREATE UNIQUE INDEX index_name on table_name (column_name);

4.删除：

DROP INDEX index_name;

5.规则：

应该避免在小表上使用索引。

不要为具有频繁，大批量更新或插入操作的表创建索引。

索引不应用于包含大量NULL值的列。

不要在经常操作(修改)的列上创建索引。

6.类型:

根据算法不同，索引分为：

B-tree，多层，排列成某些属性的等式，及以下运算符：<,>,=,between in,null,like

Hash，简单的=，不支持多列索引

GiST，SP-GiST，几何数据

GIN，线性数据

缺省为B-tree索引。

7.组成：

排序规则模式，

排序规则，

运算符类别模式，

运算符类别，

排序顺序， DESC倒叙，ASC正序

nulls排序，null在not null之前，或者在not null之后

8.分析：

explain analyze

关闭全表扫描seq scan（set session enable_seqscan = false;）

关闭索引扫描index scan（set session enable_indexscan = false;）

关闭索引条目扫描bitmap scan（set session enable_bitmapscan = false;）

数据量小时，seq scan比index scan有效。index scan发生两次I/O：1.读取索引块，2.读取数据块。

PS，即使存在index，也可能会因为数据量小被系统优化成seq scan。

9.扫描种类：

Bitmap heap scan：合并索引访问的结果子集时会用到这种方式 ，通常用到 "or"，"and"时,出现Bitmap heap scan；

index scan：普通的索引扫描， 一次只读一条索引项，一个 PAGE面有可能被多次访问；

bitmap scan ：一次性将满足条件的索引项全部取出，并在内存中进行排序, 然后根据取出的索引项访问表数据；

Seq Scan：从表的第一行开始顺序扫描，一直扫描到最后满足查询条件的记录。

10.自主优化：

pgsql也会根据自身判定，做一部分自主优化，比如：

设置了索引，但是未被使用；

未设置索引，但是却使用了索引；