PostgreSQL 인덱스

인덱스는 데이터베이스에서 데이터를 검색하는 데 필요한 특별한 테이블 쿼리입니다. 간단히 말해서, 인덱스는 테이블 데이터를 가리키는 포인터입니다. 데이터베이스의 인덱스는 책의 인덱스 목차와 매우 유사합니다.

중국어 사전의 목차 페이지(인덱스)를 예로 들어, 풍자, 글자수, 부수 등으로 정렬된 목차(인덱스)를 통해 필요한 글자를 빠르게 찾을 수 있습니다.

인덱스는 SELECT 쿼리와 WHERE 서브쿼리를 가속화하는 데 도움이 되지만, UPDATE와 INSERT 문장을 사용하여 데이터 입력을 늦추게 됩니다. 인덱스는 생성이나 제거할 수 있지만 데이터에 영향을 미치지 않습니다.

CREATE INDEX 문장을 사용하여 인덱스를 생성하면, 이름을 지정하고 테이블 및 인덱싱할 열을 지정하고, 인덱스가 오름차순 정렬이거나 내림차순 정렬인지 지정할 수 있습니다.

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

CREATE INDEX 命令

CREATE INDEX （创建索引）的语法如下：

CREATE　INDEX　index_name　ON　table_name;

索引类型

单列索引

单列索引是一个只基于表的一个列上创建的索引，基本语法如下：

CREATE　INDEX　index_name
ON　table_name　(column_name);

组合索引

组合索引是基于表的多列上创建的索引，基本语法如下：

CREATE　INDEX　index_name
ON　table_name　(column1_name,　column2_name);

不管是单列索引还是组合索引，该索引必须是在 WHEHE 子句的过滤条件中使用非常频繁的列。

如果只有一列被使用到，就选择单列索引，如果有多列就使用组合索引。

唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：

CREATE　UNIQUE　INDEX　index_name
on　table_name　(column_name);

局部索引

局部索引 是在表的子集上构建的索引；子集由一个条件表达式上定义。索引只包含满足条件的行。基础语法如下：

CREATE　INDEX　index_name
on　table_name　(conditional_expression);

隐式索引

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

在线示例

下面示例将在 COMPANY 表的 SALARY 列上创建索引：

#　CREATE　INDEX　salary_index　ON　COMPANY　(salary);

现在，用 　\d company 命令列出 COMPANY 表的所有索引：

#　\d　company

得到的结果如下，company_pkey 是隐式索引 ，是表创建表时创建的：

w3codeboxdb=#　\d　company
　　　　　　　　　　　　　　　　　　Table　"public.company"
　Column　　|　　　　　Type　　　　　　|　Collation　|　Nullable　|　Default　
---------+---------------+-----------+----------+---------
　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 명령어를 사용하여 데이터베이스에 있는 모든 INDEX를 나열할 수 있습니다:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　관계 목록
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3　rows)

DROP INDEX (INDEX 제거)

INDEX는 PostgreSQL의 DROP 명령어로 제거할 수 있습니다.

DROP INDEX index_name;

이전에 생성된 INDEX를 제거하기 위해 다음 문장을 사용할 수 있습니다:

# DROP INDEX salary_index;

삭제된 후, salary_index가 INDEX 목록에서 제거되었음을 확인할 수 있습니다:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　관계 목록
　Schema | Name | Type | Owner | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2　rows)

INDEX를 사용하지 않아야 할 경우는 무엇인가요?

INDEX의 목적은 데이터베이스 성능을 향상시키는 것이지만, INDEX를 사용하지 않아야 할 몇 가지 경우가 있습니다.

INDEX를 사용할 때는 다음 규칙을 고려해야 합니다:

• INDEX는 작은 테이블에 사용되지 않아야 합니다。

• INDEX는 자주 대량의 업데이트나 삽입 작업이 이루어지는 테이블에 사용되지 않아야 합니다。

• INDEX는 많은 NULL 값이 포함된 열에 사용되지 않아야 합니다。

• INDEX는 자주 작업되는 열에 사용되지 않아야 합니다。