SQL
Index

Introduction

Remarques

Créer un index

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Cela créera un index pour la colonne EmployeeId dans la table Cars . Cet index améliorera la vitesse des requêtes demandant au serveur de trier ou de sélectionner par valeurs dans EmployeeId , telles que les suivantes:

SELECT * FROM Cars WHERE EmployeeId = 1

L'index peut contenir plus d'une colonne, comme dans la suite;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

Dans ce cas, l'index serait utile pour les requêtes demandant de trier ou de sélectionner toutes les colonnes incluses, si l'ensemble des conditions est ordonné de la même manière. Cela signifie que lors de la récupération des données, il peut trouver les lignes à récupérer à l'aide de l'index, au lieu de parcourir la table complète.

Par exemple, le cas suivant utiliserait le deuxième index;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Si l'ordre diffère, l'index n'a pas les mêmes avantages que dans la suite;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

L'index n'est pas aussi utile car la base de données doit récupérer l'index complet, sur toutes les valeurs de EmployeeId et de CarID, afin de déterminer quels éléments ont OwnerId = 17 .

(L'index peut toujours être utilisé; il se peut que l'optimiseur de requêtes trouve que récupérer l'index et filtrer sur OwnerId , puis récupérer uniquement les lignes nécessaires est plus rapide que de récupérer la table complète, surtout si la table est grande.)

Index clusterisés, uniques et triés

Les index peuvent avoir plusieurs caractéristiques qui peuvent être définies lors de la création ou en modifiant les index existants.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

L'instruction SQL ci-dessus crée un nouvel index clusterisé sur Employees. Les index clusterisés sont des index qui déterminent la structure réelle de la table. la table elle-même est triée pour correspondre à la structure de l'index. Cela signifie qu'il peut y avoir au plus un index cluster sur une table. Si un index clusterisé existe déjà sur la table, l'instruction ci-dessus échouera. (Les tables sans index clusterisés sont également appelées heaps.)

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Cela créera un index unique pour la colonne Email dans la table Customers . Cet index, associé à l’accélération des requêtes comme un index normal, forcera également chaque adresse e-mail de cette colonne à être unique. Si une ligne est insérée ou mise à jour avec une valeur de messagerie non unique, l'insertion ou la mise à jour échouera par défaut.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Cela crée un index sur les clients qui crée également une contrainte de table que l'ID employé doit être unique. (Cela échouera si la colonne n'est pas unique actuellement - dans ce cas, s'il y a des employés qui partagent un ID.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Cela crée un index trié par ordre décroissant. Par défaut, les index (dans MSSQL Server, au moins) sont ascendants, mais cela peut être modifié.

Insérer avec un index unique

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Cela échouera si un index unique est défini dans la colonne Email des clients . Cependant, un autre comportement peut être défini pour ce cas:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: index de chute

Cette commande supprime l'index dans la table. Cela fonctionne sur le serveur SAP ASE .

Syntaxe:

DROP INDEX [table name].[index name]

Exemple:

DROP INDEX Cars.index_1

Index trié

Si vous utilisez un index trié comme vous le feriez, l' SELECT ne ferait pas de tri supplémentaire lors de l'extraction.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Lorsque vous exécutez la requête

SELECT * FROM scoreboard ORDER BY score DESC;

Le système de base de données ne ferait pas de tri supplémentaire, car il peut effectuer une recherche d'index dans cet ordre.

Suppression d'un index ou désactivation et reconstruction

DROP INDEX ix_cars_employee_id ON Cars;  

Nous pouvons utiliser la commande DROP pour supprimer notre index. Dans cet exemple , nous DROP l'index appelé ix_cars_employee_id sur les voitures de table.

Cela supprime entièrement l'index, et si l'index est en cluster, supprimera tout cluster. Il ne peut pas être reconstruit sans recréer l'index, qui peut être lent et coûteux en calculs. En alternative, l'index peut être désactivé:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Cela permet à la table de conserver la structure, ainsi que les métadonnées relatives à l'index.

Critiquement, cela conserve les statistiques d'index, de sorte qu'il est possible d'évaluer facilement le changement. Si cela est justifié, l'index peut ensuite être reconstruit, au lieu d'être recréé complètement;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Index unique permettant NULLS

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Ce schéma permet une relation 0..1 - les personnes peuvent avoir zéro ou un permis de conduire et chaque licence ne peut appartenir qu'à une seule personne

Reconstruire l'index

Au fil du temps, les index B-Tree peuvent être fragmentés en raison de la mise à jour / suppression / insertion de données. Dans la terminologie SQLServer, nous pouvons avoir interne (page d'index à moitié vide) et externe (l'ordre des pages logiques ne correspond pas à l'ordre physique). La reconstruction de l'index est très similaire à la suppression et à la recréation.

Nous pouvons reconstruire un index avec

ALTER INDEX index_name REBUILD; 

Par défaut, l'index de reconstruction est une opération hors ligne qui verrouille la table et empêche la création de DML, mais de nombreux SGBDR permettent la reconstruction en ligne. En outre, certains fournisseurs de COALESCE données proposent des alternatives à la reconstruction d’index, telles que REORGANIZE (SQLServer) ou COALESCE / SHRINK SPACE (Oracle).

Index clusterisé

Lorsque vous utilisez un index clusterisé, les lignes de la table sont triées en fonction de la colonne à laquelle l'index cluster est appliqué. Par conséquent, il ne peut y avoir qu'un seul index cluster sur la table car vous ne pouvez pas commander la table par deux colonnes différentes.

En règle générale, il est préférable d'utiliser l'index clusterisé lors des lectures sur les tables de données volumineuses. Le principal avantage de l'index clusterisé réside dans l'écriture sur la table et la réorganisation des données (recours).

Exemple de création d'un index cluster sur une table Employees sur la colonne Employee_Surname:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Index non clusterisé

Les index non clusterisés sont stockés séparément de la table. Chaque index de cette structure contient un pointeur vers la ligne de la table qu'il représente.

Ces pointeurs sont appelés localisateurs de lignes. La structure du localisateur de lignes dépend de si les pages de données sont stockées dans un segment de mémoire ou une table en cluster. Pour un tas, un localisateur de lignes est un pointeur sur la ligne. Pour une table en cluster, le localisateur de lignes est la clé d'index en cluster.

Exemple de création d'un index non clusterisé sur la table Employees et la colonne Employee_Surname:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Il peut y avoir plusieurs index non clusterisés sur la table. Les opérations de lecture sont généralement plus lentes avec les index non clusterisés qu'avec les index clusterisés, car vous devez d'abord indexer et indexer la table. Il n'y a pas de restrictions dans les opérations d'écriture cependant.

Index partiel ou filtré

SQL Server et SQLite permettent de créer des index contenant non seulement un sous-ensemble de colonnes, mais également un sous-ensemble de lignes.

Considérez un nombre croissant d'ordres avec order_state_id égal à terminé (2) et un nombre stable de commandes avec order_state_id equal à started (1).

Si votre entreprise utilise des requêtes comme celle-ci:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

L’indexation partielle vous permet de limiter l’index, en incluant uniquement les ordres inachevés:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Cet index sera plus petit qu'un index non filtré, ce qui économise de l'espace et réduit les coûts de mise à jour de l'index.