Guide de l'indexation SQL pour l'analyste de données : Réparer les requêtes lentes

Le temps de réponse de vos requêtes SQL laisse à désirer ? Ou peut-être ne savez-vous pas si vos requêtes pourraient être plus rapides. Dans cet article, nous allons vous expliquer comment l'indexation SQL peut vous aider.

Se plonger dans le langage SQL, c'est comme débloquer un superpouvoir. Il s'agit de maîtriser l'art d'interroger efficacement votre base de données afin de récupérer rapidement et facilement les informations dont vous avez besoin. Mais que se passe-t-il lorsque vous posez une question suffisamment complexe pour que le temps de réponse passe de quelques secondes à quelques minutes, voire plus ? C'est à ce moment-là qu'il faut dire bonjour à l'indexation SQL.

Dans cet article, je vais partager avec vous tout ce que vous devez savoir sur l'indexation SQL et pourquoi elle doit être la prochaine étape de l'optimisation de vos requêtes SQL. Plongeons dans l'aventure !

SQL et les requêtes de base de données

SQL est un langage permettant d'interroger et de gérer des bases de données. Il permet de stocker et de visualiser des données, ainsi que de les mettre à jour. La gestion des données est cruciale pour l'analyse des données et la veille stratégique. Nous devons gérer les données sous-jacentes afin de pouvoir les analyser et en tirer des conclusions : Quels sont les produits qui se vendent bien ? Quels sont les clients qui achètent beaucoup ? Quels sont les clients qui ne paient pas à temps ?

Une fois que nous disposons des données, nous pouvons répondre à ce type de questions. Mais d'abord, nous devons "poser des questions" ou "faire des demandes" à propos de nos données. Ces demandes sont appelées requêtes - par exemple, quelque chose comme "Pour les ventes du mois dernier, comptez le nombre de fois où chaque produit a été vendu et donnez-moi la somme des ventes".

Bien entendu, nos requêtes deviennent de plus en plus complexes au fur et à mesure que nous posons des questions plus complexes. Il se peut que nous devions combiner (ou JOINs) des informations provenant de plusieurs sources afin d'extraire les informations dont nous avons besoin.

Par exemple, il est utile de savoir que le produit ID 2123876123 a été le plus vendu au cours du trimestre précédent, mais il est encore plus utile de savoir que ce produit est un sweat-shirt bleu. Au fur et à mesure que la complexité de nos requêtes augmente, il devient plus difficile et plus long pour notre système de base de données de répondre à la requête. Nous devons accélérer nos requêtes SQL, mais comment ?

C'est là que le plaisir commence. Comprendre quand une requête répond lentement et comment améliorer ses performances relève d'un art subtil. Nous pourrions essayer de modifier notre requête et de la rendre plus efficace, mais l'optimisation des performances SQL ne nous mènera pas plus loin. À un moment donné, nous devons procéder à l'optimisation de la requête. Cela permet au système de base de données de récupérer plus facilement les résultats que nous recherchons, ce qui améliore les performances de la requête.

C'est là que les index entrent en jeu.

Qu'est-ce qu'un index SQL ?

Comme l'index d'un livre, les index SQL préparent le système de base de données à une récupération plus efficace des données. La création d'index est simple ; nous avons un cours complet sur les index qui vous apprendra tout ce que vous devez savoir. Cela inclut la façon dont ils sont créés dans la base de données, la syntaxe SQL pour créer un index, et quand créer un index. Il y a plus de 50 exercices et une dizaine d'heures d'apprentissage.

Si vous souhaitez une introduction à l'aspect théorique des index, nous avons également des articles supplémentaires pour vous sur les bases de l'indexation SQL et sur Qu'est-ce qu'un index de base de données ?

En ce qui nous concerne, nous ne nous soucierons pas des détails de la construction d'un index par la base de données ou de sa structure B-tree sous-jacente. Nous nous concentrerons plutôt sur la manière dont la base de données utilise un index. Il suffit de dire qu'un index B-tree (arbre équilibré) nous permet d'accéder à n'importe quelle ligne de la base de données dans le même laps de temps.

L'indexation de la base de données accélère la récupération des données. Reprenons notre analogie : il est plus facile de trouver la page qui fait référence à Abraham Lincoln en cherchant "Lincoln, Abraham" dans l'index du livre. (Comparez cela à la recherche d'une mention de M. Lincoln sur chaque page et vous comprendrez l'idée). En utilisant un index, trouver la référence à une personne particulière dans le livre prendrait le même temps.

D'un autre côté, imaginons que le livre n'ait pas d'index et que vous deviez parcourir chaque page pour trouver un nom. La localisation d'une référence spécifique prendra un temps inconnu et variable - les personnes qui apparaissent sur les premières pages seront trouvées plus rapidement que celles qui apparaissent à la fin (à moins que nous ne balayions de l'arrière vers l'avant).

L'indexation SQL fonctionne de la même manière. Un index est appliqué à une colonne, ce qui permet à la base de données de renvoyer plus facilement des informations lors d'une recherche.

Devrions-nous créer des index pour chaque colonne d'une table ? Cela n'accélèrerait-il pas la base de données ? L'indexation d'un trop grand nombre de colonnes aura un effet négatif sur les performances de la base de données et ralentira considérablement l'ajout et la mise à jour des lignes. La meilleure pratique actuelle consiste à n'indexer que les colonnes fréquemment utilisées pour ordonner ou trier les données.

Exemple d'indexation de base de données n° 1

Supposons que vous deviez stocker des informations sur des personnes : leur prénom, leur nom, leur nom de rue, leur numéro de rue, leur code postal/ZIP, leur ville, leur pays, leur numéro de téléphone et leur date de naissance.

Supposons maintenant que cette base de données contienne des millions d'enregistrements. Seriez-vous susceptible d'effectuer une recherche (c'est-à-dire une requête) dans la base de données sur la base du nom de famille ? Oui, probablement. Interrogeriez-vous la base de données en utilisant uniquement le prénom ? Non, probablement pas ; il est plus probable que vous fassiez une recherche sur la base du prénom et du nom de famille. Mais peut-être souhaitez-vous savoir à quel moment un prénom particulier a été le plus populaire dans l'ensemble de données. Dans ce cas, vous demanderiez "en quelle année sont nées le plus grand nombre de personnes portant ce prénom".

Chacun de ces cas nécessite une approche différente de l'indexation. Si nous effectuons une recherche par nom de famille uniquement, nous créerons un index sur la colonne last_name. Si la recherche porte sur le prénom et le nom last , nous indexerons à la fois la colonne first_name et la colonne last_name. Dans le troisième cas, nous créerons un index sur la colonne first_name uniquement.

En résumé, il existe différentes techniques d'indexation SQL. En appliquant ces différentes techniques, nous pouvons améliorer les performances de SQL. La création d'index dépend de la manière dont vous allez utiliser les données - ou, en d'autres termes, de la manière dont vous allez interroger les données.

Exemple d'indexation de base de données n°2

Imaginons que nous ayons une table person avec quatre colonnes : ssn (numéro de sécurité sociale, qui est similaire à un numéro d'identification national), first_name, last_name, et zip_code (code postal). Lorsque nous avons des millions d'enregistrements, l'exécution d'une requête SQL pour trouver une ligne basée sur le nom de famille prend presque une minute - même pour une table aussi simple.

Pouvez-vous imaginer attendre une minute pour que l'application récupère les informations dont vous avez besoin à chaque fois que vous l'interrogez? Dans cet exemple, il a fallu 46 secondes pour parcourir 40 millions d'enregistrements. Il n'y avait pas d'index sur la colonne last_name, de sorte que le système de base de données a dû lire chaque enregistrement de la table pour vérifier quels enregistrements correspondaient à un nom de famille particulier. Nous devons absolument accélérer cette requête SQL.

Dans ce cas, nous allons créer un index sur la colonne nom_de_famille. Cela permettra d'accélérer la requête SQL de trois ordres de grandeur (environ 3 000 fois plus rapide) à 15 millisecondes: un gain énorme en termes de performances de la requête SQL.

Pour indexer une base de données, nous devons définir :

1. Le nom de l'index.
2. Quelle(s) colonne(s) sera(ont) indexée(s).
3. Le nom de la table contenant ces colonnes.

N'oubliez pas que nous ne devons pas créer d'index pour chaque colonne ou chaque combinaison de colonnes. Si nous le faisons, nous risquons de tuer la base de données et de créer une application qui n'est pas réactive lorsque les utilisateurs ajoutent ou mettent à jour des enregistrements.

Voyons comment procéder.

Création d'un index SQL

La syntaxe de création d'un index est simple. Pour créer un index simple sur une colonne de la table, nous utilisons l'instruction suivante. Dans ce cas, elle indexera la colonne zip_code de notre table person table :

CREATE INDEX index_zip
ON person (zip_code);

Ce n'est pas compliqué. Cependant, n'oubliez pas que chaque index doit être mis à jour lorsque de nouvelles lignes sont ajoutées et que des lignes existantes sont modifiées ou supprimées. Ces mises à jour prennent du temps ; si vous abusez des index, ceux-ci peuvent ralentir votre base de données et son application, voire la rendre trop lente pour les utilisateurs.

Vous pouvez également modifier les index existants ou les supprimer, mais cela n'entre pas dans le cadre de cet article.

Il existe différents types d'index :

• Les index uniques maintiennent l'intégrité des données en définissant qu'aucune ligne de la table ne peut avoir la même valeur pour l'index unique.
• Lesindex primaires sont un type spécifique d'index unique, mais il ne peut y en avoir qu'un par table. L'index primaire est créé lors de la création de la table. Comme son nom l'indique, il est créé à partir de la clé primaire de la table.
• Les index secondaires sont des index supplémentaires qui sont créés à la demande (à l'aide de CREATE INDEX) et peuvent être supprimés. Les index secondaires peuvent avoir des valeurs dupliquées et non uniques (c'est-à-dire qu'ils peuvent avoir plus d'une ligne avec la même valeur).
• Les index composites (index multi-colonnes) sont des index qui incluent plusieurs colonnes.

Les index sont importants, mais leur surutilisation est aussi préjudiciable que leur sous-utilisation ou leur absence. Une stratégie d'indexation bien mise en œuvre est essentielle. Vous pouvez éviter les index inutiles en comprenant comment les données de vos tables seront gérées. Mais veillez à inclure des index importants en fonction de l'accès à vos données et de leur utilisation.

Meilleures pratiques en matière d'indexation SQL

Examinons les meilleures pratiques en matière d'indexation.

1. N'indexez pas toutes les tables. Les petites tables n'ont pas besoin d'index, car un balayage de la table sera plus efficace que la recherche dans l'index et la récupération des données de la table.
2. N'indexez pas toutes les colonnes. J'espère que les exemples précédents le montrent bien. L'indexation de chaque colonne ajoute une surcharge de travail pour maintenir ces index à jour et ralentit les autres opérations de la base de données. Indexez les colonnes sur lesquelles vous filtrez (c'est-à-dire que vous utilisez souvent dans les clauses WHERE).
3. N'indexez pas les colonnes de grande taille. Un champ volumineux dans votre table entraînera un index volumineux.
4. Indexez les clés étrangères. Cela améliore les performances des JOIN
5. N'utilisez les index multi-colonnes que lorsque c'est nécessaire. Les index multi-colonnes sont excellents. (Rappelez-vous notre exemple d'indexation sur le prénom et le nom de famille afin de pouvoir effectuer des requêtes efficaces sur cette combinaison). Cependant, les index composites sont plus complexes, car il faut tenir compte de l'ordre des colonnes dans l'index. Devons-nous créer un index sur le prénom et le nom de famille ou sur le nom de famille et le prénom ? Il s'agit de deux index différents. Lequel sera le plus efficace ? La réponse dépend des requêtes SQL. En général, un index sur une seule colonne suffit (et permet de gagner du temps).
6. Utilisez les index pour pré-trier les données. Le tri répété des données peut être évité lorsqu'un index est ajouté avec l'ordre de tri (c'est-à-dire ascendant ou descendant).
7. Assurez-vous que les choses fonctionnent comme prévu. Utilisez EXPLAIN PLAN pour vérifier que vos index sont utiles.

Maintenant que nous disposons d'une stratégie de création d'index, examinons comment les maintenir et les optimiser. Vous devez surveiller vos index : vérifiez les statistiques d'index pour comprendre l'utilisation des index et vérifiez le plan de requête pour analyser comment vos requêtes SQL utilisent les index.

La maintenance des index SQL implique la réorganisation ou la reconstruction des index. La réorganisation d'un index est moins intensive qu'une reconstruction complète. La réorganisation et la reconstruction d'un index sont toutes deux gourmandes en ressources et peuvent ou non améliorer les performances. Il convient donc d'analyser vos index avant de les maintenir. Ne partez pas du principe que la maintenance d'un index améliorera les performances des requêtes SQL.

Quelles sont les prochaines étapes de l'indexation SQL ?

Maintenant que nous avons expliqué ce que sont les index et les meilleures pratiques pour les créer et les maintenir, la prochaine étape est de se plonger plus profondément dans les index. Notre cours Comprendre les index explique le fonctionnement des index et vous guide pour savoir quand et comment créer un index. Ses exercices pratiques vous aideront à comprendre rapidement ce dont vous avez besoin pour que vos requêtes soient rapides et efficaces. Bon apprentissage !