PostgreSQL 索引
索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。简单地说,索引是指向表中数据的指针。数据库中的索引与书后的索引非常相似。
例如,如果你想参考一本讨论某个主题的书中的所有页面,你必须首先参考索引,该索引按字母顺序列出所有主题,然后参考一个或多个特定页码。
索引有助于加速 SELECT 查询和 WHERE 子句;但是,它会通过 UPDATE 和 INSERT 语句减慢数据输入速度。可以在不影响数据的情况下创建或删除索引。
创建索引涉及 CREATE INDEX 语句,该语句允许你命名索引、指定表和要索引的列,以及指示索引是按升序还是降序排列。
索引也可以是唯一的,类似于 UNIQUE 约束,因为索引可以防止存在索引的列或列组合中的重复条目。
CREATE INDEX 命令
的基本语法 创建索引 如下:
CREATE INDEX index_name ON table_name;
索引类型
PostgreSQL 提供了几种索引类型:B-tree、Hash、GiST、SP-GiST 和 GIN。每种索引类型使用最适合不同类型查询的不同算法。默认情况下,CREATE INDEX 命令创建适合最常见情况的 B 树索引。
单列索引
单列索引是仅基于一个表列创建的索引。基本语法如下:
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);
隐式索引
隐式索引是在创建对象时由数据库服务器自动创建的索引。索引是为主键约束和唯一约束自动创建的。
以下是我们将在其上创建索引的示例 COMPANY 工资栏表:
# CREATE INDEX salary_index ON COMPANY (salary);
现在,让我们列出 COMPANY 表上可用的所有索引 \d 公司 command.
# \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 command:
DROP INDEX 命令
可以使用 PostgreSQL 删除索引 DROP 命令。删除索引时应该小心,因为性能可能会降低或提高。
基本语法如下:
DROP INDEX index_name;
你可以使用以下语句删除之前创建的索引:
# DROP INDEX salary_index;
什么时候应该避免使用索引?
尽管索引旨在提高数据库的性能,但有时也应避免使用索引。以下指南指示何时应重新考虑索引的使用:
-
不应在小表上使用索引。
-
具有频繁、大批量更新或插入操作的表。
-
不应在包含大量 NULL 值的列上使用索引。
-
不应为频繁操作的列编制索引。