postgresql索引介绍

创建索引通常能够提高数据库的性能,使查询速度大幅提高。同时索引的存在也为数据库中的数据维护带来的负面影响,增加的数据库与系统的资源消耗。

适当的建立索引总能利大于弊。
postgresql数据库中可以创建多字段、唯一、函数、部分索引多种类型的索引。可能基于主要面向OLTP系统的应用没有引入位图索引(个人猜测)。

测试表:

CREATE TABLE test1 (
  id integer,
  content varchar
  );

 

查询语句:
SELECT content FROM test1 WHERE id = constant;

 

在没有索引的情况下,数据库需要一行行的对表test1进行扫描,test1含有多少数据块就需要扫描多少数据块,对于想要获取非常少量数据的情况下,大量的IO开销显的非常重要,对系统会带来严重的影响。通过创建索引,大大减少了数据库的扫描块数,减少了数据匹配的验证次数,更少更快的扫描路径,提高查询的性能。同样能够提高带有查询语句的delete、update操作的性能。
创建索引:
CREATE INDEX test1_id_index ON test1 (id);

 

删除索引:
DROP INDEX test1_id_index;

 

并行创建:
索引创建时需要与表数据保持同步,执行创建语句时系统需要对表进行锁定,期间将会对表的delete、update、insert事务进行阻塞。在生产系统中创建索引需要注意创建的时间,如果对大数据量的表创建索引时,更应该选在系统空闲时。
为了加快索引的创建速度postgresql引入了CONCURRENTLY选项,对索引进行并发创建。CONCURRENTLY选项能够使系统在不阻塞write操作的情况下执行索引创建。其实质是执行两次的表扫描,尽管解决了write的问题,但是需要更多的IO和CPU开销。

以下是CREATE INDEX语法:

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

 

posted on 2010-08-17 16:19  大肚熊  阅读(7688)  评论(0编辑  收藏  举报

导航