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

持续更新....


参考资料:[1]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修炼之道  -唐成.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值