SQLite - Index

Les index sont des tables de recherche spéciales que le moteur de recherche de base de données peut utiliser pour accélérer la récupération des données. En termes simples, unindexest un pointeur vers des données dans une table. Un index dans une base de données est très similaire à un index au dos d'un livre.

Par exemple, si vous souhaitez référencer toutes les pages d'un livre qui traitent d'un certain sujet, vous vous référez d'abord à l'index, qui répertorie toutes les rubriques par ordre alphabétique, puis fait référence à un ou plusieurs numéros de page spécifiques.

Un index permet d'accélérer les requêtes SELECT et les clauses WHERE, mais il ralentit la saisie de données, avec les instructions UPDATE et INSERT. Les index peuvent être créés ou supprimés sans effet sur les données.

La création d'un index implique l'instruction CREATE INDEX, qui vous permet de nommer l'index, de spécifier la table et la ou les colonnes à indexer, et d'indiquer si l'index est dans un ordre croissant ou décroissant.

Les index peuvent également être uniques, similaires à la contrainte UNIQUE, en ce que l'index empêche les entrées en double dans la colonne ou la combinaison de colonnes sur laquelle il y a un index.

La commande CREATE INDEX

Voici la syntaxe de base de CREATE INDEX.

CREATE INDEX index_name ON table_name;

Index à une seule colonne

Un index à une seule colonne est créé sur la base d'une seule colonne de table. La syntaxe de base est la suivante -

CREATE INDEX index_name
ON table_name (column_name);

Index uniques

Les index uniques sont utilisés non seulement pour les performances, mais également pour 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 composites

Un index composite est un index sur deux ou plusieurs colonnes d'une table. La syntaxe de base est la suivante -

CREATE INDEX index_name
on table_name (column1, column2);

Que vous souhaitiez créer un index à une seule colonne ou un index composite, prenez en compte la ou les colonnes que vous pouvez utiliser très fréquemment dans la clause WHERE d'une requête comme conditions de filtre.

Si une seule colonne est utilisée, un index à une seule colonne doit être le choix. Si deux colonnes ou plus sont fréquemment utilisées dans la clause WHERE comme filtres, l'index composite serait le meilleur choix.

Index implicites

Les index implicites sont des index qui sont automatiquement créés par le serveur de base de données lors de la création d'un objet. Les index sont automatiquement créés pour les contraintes de clé primaire et les contraintes uniques.

Example

Voici un exemple où nous allons créer un index dans la table COMPANY pour la colonne salaire -

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Maintenant, listons tous les indices disponibles dans la table COMPANY en utilisant .indices commande comme suit -

sqlite> .indices COMPANY

Cela produira le résultat suivant, où sqlite_autoindex_COMPANY_1 est un index implicite qui a été créé lorsque la table elle-même a été créée.

salary_index
sqlite_autoindex_COMPANY_1

Vous pouvez lister tous les index dans toute la base de données comme suit -

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

La commande DROP INDEX

Un index peut être supprimé à l'aide de SQLite DROPcommander. Des précautions doivent être prises lors de la suppression d'un index car les performances peuvent être ralenties ou améliorées.

Voici la syntaxe de base est la suivante -

DROP INDEX index_name;

Vous pouvez utiliser l'instruction suivante pour supprimer l'index créé précédemment.

sqlite> DROP INDEX salary_index;

Quand faut-il éviter les index?

Bien que les index soient destinés à améliorer les performances d'une base de données, il y a des moments où ils doivent être évités. Les directives suivantes indiquent quand l'utilisation d'un index doit être reconsidérée.

Les index ne doivent pas être utilisés dans -

  • Petites tables.
  • Tables qui ont des opérations de mise à jour ou d'insertion par lots fréquentes et importantes.
  • Colonnes contenant un nombre élevé de valeurs NULL.
  • Colonnes fréquemment manipulées.