PostgreSQL教程

PostgreSQL - INDEXES

索引是数据库搜索引擎可用于加速数据检索的特殊查找表.简单地说，索引是指向表中数据的指针.数据库中的索引与书后面的索引非常相似.

例如，如果要引用讨论某个主题的书中的所有页面，首先引用索引，它按字母顺序列出所有主题，然后引用一个或多个特定页码.

索引有助于加快SELECT查询和WHERE子句;但是，它使用UPDATE和INSERT语句减慢了数据输入.可以创建或删除索引而不影响数据.

创建索引涉及CREATE INDEX语句，该语句允许您命名索引，指定表以及哪些列或列索引，并指示索引是按升序还是降序.

索引也可以是唯一的，类似于UNIQUE约束，因为索引可以防止列中的重复条目或有索引的列的组合.

CREATE INDEX命令

CREATE INDEX 的基本语法是as跟随 :

CREATE INDEX index_name ON table_name;

索引类型

PostgreSQL提供了几种索引类型:B-tree，Hash，GiST，SP-GiST和GIN .每种索引类型使用最适合不同类型查询的不同算法.默认情况下，CREATE INDEX命令创建适合最常见情况的B树索引.

单列索引

单列index是仅基于一个表列创建的索引.基本语法如下 :

CREATE INDEX index_name 
ON table_name(column_name);

多列索引

多列索引是在表的多个列上定义的.基本语法如下 :

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

无论是创建单列索引还是多列索引，都要考虑在查询的WHERE子句中经常使用的列.作为过滤条件.

如果只使用一列，则应选择单列索引.如果在WHERE子句中经常使用两列或多列作为过滤器，则多列索引将是最佳选择.

唯一索引

唯一索引不仅用于性能，还用于数据完整性.唯一索引不允许将任何重复值插入表中.基本语法如下"

CREATE UNIQUE INDEX index_name
on table_name (column_name);

部分索引

部分索引是在表的子集上构建的索引;子集由条件表达式(称为部分索引的谓词)定义.索引仅包含满足谓词的表行的条目.基本语法如下"

CREATE INDEX index_name 
on table_name(conditional_expression);

隐式索引

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

示例

以下是我们将在公司表格，用于工资栏;

# CREATE INDEX salary_index ON COMPANY (salary);

现在，让我们使用 \d company 命令列出COMPANY表格中可用的所有索引.

 
# \d company

这将产生以下结果，其中 company_pkey 是一个隐式索引，它是在创建表时创建的.

       Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 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 命令列出整个索引数据库 :