SQLite Index
Un index est une table de recherche spéciale que le moteur de recherche de la base de données utilise pour accélérer la récupération des données. 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.
Prenez l'exemple des pages d'index d'un dictionnaire chinois. Vous pouvez rapidement trouver le caractère dont vous avez besoin à l'aide de tableaux 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 sans affecter les données.
Utilisez l'instruction CREATE INDEX pour créer un index. Elle vous permet de nommer l'index, de spécifier la table et la ou les colonnes à indexer, et d'indiquer si l'index est en 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
CREATE INDEXLa syntaxe de base est la suivante :
CREATE INDEX index_name ON table_name;
Index sur une seule colonne
Un index à 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 unique
L'utilisation d'un index unique ne sert pas seulement à la performance, mais aussi à l'intégrité des données. Un index unique n'autorise aucune valeur en double à être insérée dans la table. La syntaxe de base est la suivante :
CREATE UNIQUE INDEX index_name on table_name (column_name);
Index composite
Un index composite est un index créé sur deux colonnes ou plus d'une table. La syntaxe de base est la suivante :
CREATE INDEX index_name on table_name (column1, column2);
Le choix de créer un index à une seule colonne ou un index composite dépend des colonnes que vous utilisez très fréquemment dans la clause WHERE comme conditions de filtre de requête.
Si une seule colonne est utilisée, choisissez un index à une seule colonne. Si deux colonnes ou plus sont fréquemment utilisées dans la clause WHERE comme filtres, choisissez un index composite.
Index implicite
Les index implicites sont automatiquement créés par le serveur de base de données lorsque des objets sont créés. Les index sont automatiquement créés pour les contraintes de clé primaire et les contraintes d'unicité.
Exemple
Voici un exemple. Nous allons créer un index sur la colonne salary de la table COMPANY :
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
Maintenant, utilisons la commande.indicesou.indexespour lister tous les index disponibles sur la table COMPANY, comme indiqué ci-dessous :
sqlite> .indices COMPANY
Cela produira le résultat suivant, oùsqlite_autoindex_COMPANY_1est l'index implicite créé lors de la création de la table.
salary_index sqlite_autoindex_COMPANY_1
Vous pouvez lister tous les index de la base de données comme suit :
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
Commande DROP INDEX
Un index peut être supprimé à l'aide de la commandeDROPde SQLite. Il faut particulièrement faire attention lors de la suppression d'un index, car les performances peuvent soit diminuer, soit s'améliorer.
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 le but des index soit d'améliorer les performances de la base de données, il existe plusieurs situations où les index doivent être évités. Tenez compte des recommandations suivantes lors de l'utilisation des index :
Les index ne doivent pas être utilisés sur les petites tables.
Les index ne doivent pas être utilisés sur les tables qui subissent fréquemment des opérations de mise à jour ou d'insertion à grande échelle.
Les index ne doivent pas être utilisés sur les colonnes qui contiennent un grand nombre de valeurs NULL.
Les index ne doivent pas être utilisés sur les colonnes qui sont fréquemment manipulées.