Index PostgreSQL

Les index sont une requête spéciale de table qui accélère la recherche de données par le moteur de recherche. En termes simples, un index est un pointeur vers les données d'une table. Un index dans une base de données est très similaire au sommaire d'un livre.

Prenez les pages de sommaire d'un dictionnaire chinois (index) comme exemple, nous pouvons rapidement trouver les caractères nécessaires en ordre alphabétique, en nombre de traits, en composants latéraux, etc.

Les index aident à accélérer les requêtes SELECT et les clauses WHERE, mais ils ralentissent l'insertion de données en utilisant les expressions UPDATE et INSERT. Les index peuvent être créés ou supprimés, mais ils n'affectent pas les données.

L'utilisation de l'expression CREATE INDEX pour créer des index, qui permet de nommer les index, spécifier la table et les colonnes à indexer, et indiquer si l'index est trié en ordre croissant ou décroissant.

索引也可以是唯一的，与 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);

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

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

唯一索引

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

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 | texte | | | pas null |　
　age | entier | | | pas null |　
　address | caractères(|50) | | | |　
　salary | réel | | | |　
Index:
　　　　"company_pkey" CLÉ PRIMAIRE, btree (id)
　　　　"salary_index" btree (salary)

Vous pouvez utiliser la commande \di pour lister tous les index de la base de données :

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Liste des relations
　Schema | Nom | Type | Propriétaire | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3　rows)

DROP INDEX (suppression de l'index)

Un index peut être supprimé en utilisant la commande DROP de PostgreSQL.

DROP INDEX index_name;

Vous pouvez utiliser la phrase suivante pour supprimer l'index créé précédemment :

# DROP INDEX salary_index;

Après suppression, vous pouvez voir que salary_index a été supprimé de la liste des index :

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Liste des relations
　Schema | Nom | Type | Propriétaire | Table　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2　rows)

Dans quels cas faut-il éviter l'utilisation des index ?

Bien que l'objectif des index soit d'améliorer les performances de la base de données, il y a quelques situations où il est nécessaire d'éviter leur utilisation.

Lors de l'utilisation des index, il est nécessaire de prendre en compte les critères suivants :

• Les index ne devraient pas être utilisés sur les tables de petite taille.

• Les index ne devraient pas être utilisés sur les tables avec de nombreuses mises à jour ou insertions massives fréquentes.

• Les index ne devraient pas être utilisés sur les colonnes contenant de nombreux valeurs NULL.

• Les index ne devraient pas être utilisés sur les colonnes très fréquemment mises à jour.