Index PostgreSQL
Un index est une requête spéciale sur une table qui accélère la récupération des 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 à l'index d'un livre.
Prenons comme analogie les pages d'index d'un dictionnaire chinois. Nous pouvons trouver rapidement les caractères dont nous avons besoin grâce aux répertoires (index) triés par pinyin, nombre de traits, radicaux, etc.
Les index aident à accélérer les requêtes SELECT et les clauses WHERE, mais ils ralentissent la saisie des données lors de l'utilisation des instructions UPDATE et INSERT. Les index peuvent être créés ou supprimés, mais ils n'affectent pas les données.
L'instruction CREATE INDEX est utilisée pour créer un index. Elle permet de nommer l'index, de spécifier la table et la ou les colonnes à indexer, et d'indiquer si l'index est trié par ordre croissant ou décroissant.
Les index peuvent également être uniques, comme la contrainte UNIQUE, empêchant les entrées en double sur une colonne ou une combinaison de colonnes.
Commande CREATE INDEX
La syntaxe de CREATE INDEX (création d'un index) est la suivante :
CREATE INDEX index_name ON table_name;
Types d'index
Index sur une seule colonne
Un index sur une seule colonne est un index créé sur une seule colonne d'une table. La syntaxe de base est la suivante :
CREATE INDEX index_name ON table_name (column_name);
Index composite
Un index composite est un index créé sur plusieurs colonnes d'une table. La syntaxe de base est la suivante :
CREATE INDEX index_name ON table_name (column1_name, column2_name);
Qu'il s'agisse d'un index à une seule colonne ou d'un index composite, l'index doit porter sur des colonnes qui sont utilisées très fréquemment dans les conditions de filtre de la clause WHERE.
Si une seule colonne est utilisée, choisissez un index à une seule colonne ; si plusieurs colonnes sont utilisées, utilisez un index composite.
Index unique
L'utilisation d'un index unique ne sert pas uniquement à améliorer les performances, mais aussi à garantir l'intégrité des données. Un index unique ne permet pas d'insérer des valeurs en double dans la table. La syntaxe de base est la suivante :
CREATE UNIQUE INDEX index_name on table_name (column_name);
Index partiel
Un index partiel est un index construit sur un sous-ensemble d'une table ; le sous-ensemble est défini par une expression conditionnelle. L'index ne contient que les lignes qui satisfont la condition. La syntaxe de base est la suivante :
CREATE INDEX index_name ON table_name(column_list) WHERE condition;
Ici, index_name est le nom de l'index que vous souhaitez créer, table_name est le nom de la table contenant les colonnes que vous souhaitez indexer, column_list est la liste des colonnes que vous souhaitez indexer, et condition est une expression booléenne qui définit quelles lignes seront incluses dans l'index.
Index implicite
Dans PostgreSQL, un index implicite est un index créé automatiquement par le serveur de base de données lorsqu'un objet est créé. Ces index sont généralement créés automatiquement pour les contraintes de clé primaire et les contraintes d'unicité. Lorsque vous déclarez une colonne comme clé primaire, contrainte d'unicité ou clé étrangère lors de la création d'une table, PostgreSQL crée automatiquement un index implicite pour cette colonne. L'avantage est que cela simplifie la gestion des index et améliore les performances de la base de données.
Par exemple, si vous déclarez une colonne nommée "userid" comme clé primaire lors de la création d'une table nommée "users", PostgreSQL créera automatiquement un index implicite pour la colonne "userid". Cela signifie que lors de l'insertion de nouveaux enregistrements, la base de données générera automatiquement une valeur d'index unique pour la colonne "userid".
La création et la gestion des index implicites sont automatiquement prises en charge par PostgreSQL, et les utilisateurs n'ont pas besoin d'intervenir manuellement, ce qui rend la gestion de la base de données plus simple et plus efficace.
Exemple
L'exemple suivant créera un index sur la colonne SALARY de la table COMPANY :
# CREATE INDEX salary_index ON COMPANY (salary);
Maintenant, utilisez la\d companycommande pour lister tous les index de la table COMPANY :
# \d company
Les résultats sont les suivants. company_pkey est un index implicite créé lors de la création de la table :
exampledb=# \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)
Vous pouvez utiliser la\dicommande pour lister tous les index de la base de données :
exampledb=# \di
List of relations
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 (Suppression d'un index)
Un index peut être supprimé à l'aide de la commande DROP de PostgreSQL.
DROP INDEX index_name;
Vous pouvez utiliser l'instruction suivante pour supprimer l'index créé précédemment :
# DROP INDEX salary_index;
Après la suppression, vous pouvez voir que salary_index a été supprimé de la liste des index :
exampledb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)
Quand faut-il éviter d'utiliser les index ?
Bien que les index aient pour but d'améliorer les performances de la base de données, il existe plusieurs situations où l'utilisation des index doit être évitée.
Lors de l'utilisation des index, les directives suivantes doivent être prises en compte :
- Les index ne doivent pas être utilisés sur les petites tables.
- Les index ne doivent pas être utilisés sur des tables soumises à de fréquentes opérations de mise à jour ou d'insertion à grande échelle.
- Les index ne doivent pas être utilisés sur des colonnes contenant un grand nombre de valeurs NULL.
- Les index ne doivent pas être utilisés sur des colonnes faisant fréquemment l'objet d'opérations.