PostgreSQL 索引

摘要:索引是数据库中一种快速查询数据的方法.索引是提高数据库性能的常用途径。比起没有索引，使用索引可以让数据库服务器更快找到并获取特定行。但是索引同时也会增加数据库系统的日常管理负担，因此我们应该聪明地使用索引。

1. 索引的功能
2. 索引的分类以及对应的匹配操作符

3. 索引的优势及缺点

4.索引常用的语法

索引的功能: 加快对表中的查询记录的查找与排序.

索引的分类以及对应的匹配操作符:

        PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索
引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创
建适合于大部分情况的B-tree 索引。
B-tree可以在可排序数据上的处理等值和范围查询。特别地，PostgreSQL的查询规划器会在
任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引：
< <
=
= >
=
>
将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。同样，在索引
列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。
优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式是
一个常量且被固定在字符串的开头—例如：col LIKE ’foo%’或者col ~ ’^foo’, 但在col
LIKE ’%bar’上则不会。但是，如果我们的数据库没有使用C区域设置，我们需要创建一
个具有特殊操作符类的索引来支持模式匹配查询，参见下面的Section 11.9。同样可以将Btree索引用于ILIKE和~*，但仅当模式以非字母字符开始，即不受大小写转换影响的字符。
B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快，但是总是有用
的。
Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较
时，查询规划器将考虑使用一个Hash索引。下面的命令将创建一个Hash索引：
CREATE INDEX name ON table USING HASH (column);
Caution
Hash索引操作目前不被WAL记录，因此存在未写入修改，在数据库崩溃后需要
用REINDEX命令重建Hash索引。同样，在完成初始的基础备份后，对于Hash索
引的改变也不会通过流式或基于文件的复制所复制，所以它们会对其后使用它
们的查询给出错误的答案。正因为这些原因，Hash索引已不再被建议使用。
GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施。相应
地，可以使用一个GiST索引的特定操作符根据索引策略（操作符类）而变化。作为一个例
子，PostgreSQL的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类，它用来
支持使用下列操作符的索引化查询：
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
323
Chapter 11. 索引
<@
~=
&&
（这些操作符的含义见Section 9.11）Table 61-1中给出了标准发布中所包括的 GiST 操作符
类。contrib集合中还包括了很多其他GiST操作符类，可见Chapter 61。
GiST索引也有能力优化“最近邻”搜索，例如：
SELECT * FROM places ORDER BY location <-> point ’(101,456)’ LIMIT 10;
它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特
定操作符类。 在Table 61-1中，“Ordering Operators”列中列出了可以在这种方法中使用的操作
符。
和GiST相似，SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众
多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d树和radix树。作为一个例
子，PostgreSQL的标准捐献包中包含了一个用于二维点的SP-GiST操作符类，它用于支持使
用下列操作符的索引化查询：
<<
>>
~=
<@
<^
>^
（其含义见Section 9.11）Table 62-1中给出了标准发布中所包括的 SP-GiST 操作符类。更多信
息参见Chapter 62。
GIN 索引是“倒排索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一
个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。
与 GiST 和 SP-GiST相似， GIN可以支持多种不同的用户定义的索引策略和特定操作符，通
过它一个GIN索引可以被根据索引策略被使用。作为一个例子，PostgreSQL的标准捐献包中
包含了用于一维数组的GIN操作符类，它用于支持使用下列操作符的索引化查询：
<@
@>
=
&&
（这些操作符的含义见Section 9.18）Table 63-1中给出了标准发布中所包括的 GIN 操作符
类。在contrib集合中还有更多其他GIN操作符类，更多信息参见Chapter 63。
BRIN 索引（块范围索引的缩写）存储有关存放在一个表的连续物理块范围上的值摘要信
息。与 GiST、SP-GiST 和 GIN 相似，BRIN可以支持很多种不同的索引策略，并且可以与一
个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，
被索引的数据对应于每个块范围的列中值的最小值和最大值，使用这些操作符来支持用到索
引的查询：
< <
=
= >
=
>

 索引的优势与缺点:

(1)索引的优点

①  建立索引的列可以保证行的唯一性，生成唯一的rowId

②  建立索引可以有效缩短数据的检索时间

③  建立索引可以加快表与表之间的连接

④  为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

(2). 索引的缺点：

①  创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

②  创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

③  会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

(3). 什么样的表跟列要建立索引：

①  总的来说就是数据量大的，经常进行查询操作的表要建立索引

②  表中字段建立索引应该遵循几个原则：

1)        越小的数据类型通常更好：越小的数据类型通常在磁盘、内存中都需要更少的空间，处理起来更快。

2)          简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂，处理起来也更耗时。

3)        尽量避免NULL：应该指定列为NOT NULL。含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4)        对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义，所以索引的建立应当更多的选取唯一性更高的字段。

③  表与表连接用于多表联合查询的约束条件的字段应当建立索引

④  用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。

⑤  添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发。

⑥  如果有些表注定只会进行查询所有，也就没必要添加索引，因为查询全部只能进行全量搜索即扫描全表。

索引的简单用法:

1.创建索引.

        在创建索引的过程中,会把表中的数据扫描一遍,时间有标的大小决定,PostgreSQL提供一种并发创建索引的方法,在对表创建索引的同时可以对表增删改等操作.

建立一张联系人例表:

me=# create table test_table(
me(# id int primary key,
me(# name varchar(40),
me(# phone varchar(32)[],
me(# address text);

为name 创建一个简单的B-tree 索引

me=# create index index_test_name on test_table(name);
CREATE INDEX

如果想查询某一个电话号码是谁的,由于此字段是一个数组,前面所建的B-tree索引不在起作用,因此可以创建一个GIN索引,如下:

me=# create index index_test_phone on test using gin(phone);
CREATE INDEX

假设查询 "17866666666" 号码是谁的,建立下列查询语句:

me=# select *from test_table where phone @> array['17866666666'::varchar(32)];
 id | name |     phone     | address 
----+------+---------------+---------
  1 | li   | {17866666666} | china
(1 行记录)

创建索引的时候可以指定储存参数,"with(storage_parameter = value)",常用的储存参数为FILLFACTOR.

me=# create index index_text_name on test(name) with (FILLFACTOR = 50);
CREATE INDEX

按降序建立索引:

me=# create index index_test_name1 on test(name desc);
CREATE INDEX

指定空值在非空值前面:

me=# create index index_test_name2 on test(name desc nulls first);
CREATE INDEX

指定空值在后面:

create index index_test_name3 on test(name desc nulls last);
CREATE INDEX

\d+   查阅test:

me=# \d+ test
                         资料表 "public.test"
 栏位  |          型别           | 修饰词 |   存储   | 统计目标 | 描述 
-------+-------------------------+--------+----------+----------+------
 id    | integer                 |        | plain    |          | 
 name  | text                    |        | extended |          | 
 phone | character varying(32)[] |        | extended |          | 
索引：
    "index_test_name1" btree (name DESC)
    "index_test_name2" btree (name DESC)
    "index_test_name3" btree (name DESC NULLS LAST)
    "index_test_phone" gin (phone)
    "index_text_name" btree (name) WITH (fillfactor=50)
    "test_index" btree (id)
    "test_phone_index" gin (phone)
有 OIDs: 否

修改索引: 默认是"restrict

me=# drop index if exists index_test_name3;
DROP INDEX

"cascade" 选项:表示当有依赖这个索引的对象时,一并吧这些对象删除.

me=# drop index if exists index_test_name2 cascade;
DROP INDEX

持续更新．．．．

参考资料：［１］http://blog.csdn.net/miracleww/article/details/53352738

                         [2]  Postgresql-9.6.0-中文文档-彭煜玮

                           [3]https://www.postgresql.org/docs/10/static/index.html

                            [4] PostgreSQL修炼之道  -唐成.