[Explained] Comment créer un index de base de données en SQL

par


Cherchez-vous à optimiser la vitesse de vos requêtes de base de données ? Découvrez comment mettre en place un index de base de données à l’aide de SQL, améliorer l’efficacité des requêtes et accélérer la récupération des informations.

Lorsque vous sollicitez des données d’une table, il est fréquent de devoir effectuer des filtrages en fonction de colonnes spécifiques.

Imaginez que vous écrivez une requête SQL pour extraire des informations selon des critères particuliers. Par défaut, l’exécution de la requête implique une analyse complète de la table, jusqu’à ce que tous les enregistrements correspondant aux conditions soient identifiés, avant de vous fournir les résultats.

Cette approche peut se révéler extrêmement lente, surtout lorsqu’il s’agit d’interroger une vaste base de données comportant des millions de lignes. Pour accélérer ces requêtes, la solution réside dans la création d’un index de base de données.

Qu’est-ce qu’un index de base de données ?

Lorsque vous recherchez une information spécifique dans un livre, vous n’allez pas examiner chaque page l’une après l’autre, n’est-ce pas ?

Vous allez plutôt consulter l’index pour identifier les pages contenant le terme recherché et accéder directement à celles-ci. Un index de base de données fonctionne de manière similaire aux index de livres.

Un index de base de données est un ensemble de pointeurs ou de références vers les données réelles, organisés de manière à optimiser la vitesse de récupération. Sur le plan technique, un index peut être implémenté en utilisant des structures de données comme les arbres B+ ou les tables de hachage. Ainsi, un index de base de données accroît considérablement la rapidité et l’efficacité des opérations de recherche de données.

Création d’un index de base de données en SQL

Maintenant que nous comprenons ce qu’est un index et comment il peut accélérer l’accès aux données, voyons comment créer un index de base de données en SQL.

Lors de vos opérations de filtrage, utilisant une clause WHERE, il est fort probable que vous interrogiez certaines colonnes plus fréquemment que d’autres.

CREATE INDEX index_name ON table (column)

Où :

  • index_name est le nom de l’index que vous souhaitez créer.
  • table fait référence à la table dans votre base de données relationnelle.
  • column est le nom de la colonne de la table sur laquelle vous voulez créer l’index.

Il est également possible de créer des index sur plusieurs colonnes (index multi-colonnes) si nécessaire. Voici la syntaxe pour cela :

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

Passons à un exemple concret.

Comprendre l’amélioration des performances grâce à l’index de base de données

Pour mesurer l’intérêt de créer un index, nous devons utiliser une table de base de données avec un volume important d’enregistrements. Les exemples de code qui suivent sont destinés à SQLite, mais vous pouvez les adapter à d’autres systèmes de gestion de base de données (SGBD) comme PostgreSQL ou MySQL.

Remplir une table de base de données avec des enregistrements

Il est possible d’utiliser le module random de Python pour générer et insérer des enregistrements dans une base de données. Toutefois, nous utiliserons ici Faker pour remplir la table avec un million de lignes.

Le script Python ci-dessous :

  • Crée une base de données nommée customer_db et s’y connecte.
  • Crée une table clients avec les champs : first_name, last_name, city et num_orders.
  • Génère des données fictives et insère un million d’enregistrements dans la table clients.

Le code source complet est également disponible sur GitHub.

# main.py
# imports
import sqlite3
from faker import Faker
import random

# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()

# create table
db_cursor.execute('''CREATE TABLE customers (
                  id INTEGER PRIMARY KEY,
                  first_name TEXT,
                  last_name TEXT,
                  city TEXT,
                  num_orders INTEGER)''')

# create a Faker object
fake = Faker()
Faker.seed(27)

# create and insert 1 million records
num_records = 1_000_000

for _ in range(num_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    city = fake.city()
    num_orders = random.randint(0,100)
    db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))

# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()

Nous pouvons maintenant commencer à effectuer des requêtes.

Création d’un index sur la colonne Ville

Imaginons que vous souhaitiez obtenir des informations client en filtrant sur la colonne Ville. Votre requête SELECT ressemblerait à ceci :

SELECT column(s) FROM customers
WHERE condition;

Créons donc un index city_idx sur la colonne city de la table customers :

CREATE INDEX city_idx ON customers (city);

⚠ La création d’un index peut prendre un certain temps et constitue une opération ponctuelle. Cependant, les gains de performance seront importants lorsque vous aurez besoin d’effectuer un grand nombre de requêtes (en filtrant sur la colonne ville).

Suppression d’un index de base de données

Pour supprimer un index, il suffit d’utiliser l’instruction DROP INDEX de cette manière :

DROP INDEX index_name;

Comparaison des temps de requête avec et sans index

Si vous souhaitez exécuter des requêtes dans un script Python, vous pouvez utiliser le minuteur standard pour mesurer les temps d’exécution.

Vous pouvez également exécuter les requêtes directement dans le client en ligne de commande sqlite3. Pour utiliser customer_db.db en ligne de commande, entrez la commande suivante dans votre terminal :

$ sqlite3 customer_db.db;

Pour obtenir les temps d’exécution approximatifs, vous pouvez activer la fonctionnalité .timer intégrée à sqlite3 comme ceci :

sqlite3 > .timer on
        > <votre requête ici>

Puisque nous avons créé un index sur la colonne ville, les requêtes utilisant un filtrage basé sur cette colonne dans la clause WHERE seront beaucoup plus rapides.

Exécutez d’abord les requêtes sans index, puis créez l’index et réexécutez les mêmes requêtes. Notez les temps d’exécution dans les deux cas. Voici quelques exemples :

Temps sans index Temps avec index
SELECT * FROM clients WHERE city LIKE ‘Nouveau%’ LIMIT 10; 0,100 s 0,001 s
SELECT * FROM clients WHERE city=’New Wesley’; 0.148 s 0.001 s
SELECT * FROM clients WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’); 0,247 s 0,003 s

On constate que les temps de récupération avec l’index sont considérablement plus rapides que sans index sur la colonne city.

Meilleures pratiques pour créer et utiliser les index de base de données

Il est important de toujours vérifier que les gains de performance justifient la charge liée à la création d’un index. Voici quelques bonnes pratiques à garder à l’esprit :

  • Sélectionnez judicieusement les colonnes pour créer un index. Évitez de créer trop d’index car cela peut entraîner des coûts de maintenance non négligeables.
  • À chaque modification d’une colonne indexée, l’index correspondant doit être mis à jour. Par conséquent, la création d’un index, bien qu’elle accélère la récupération, ralentit les opérations d’insertion et de mise à jour. Il est donc préférable de créer des index sur les colonnes fréquemment interrogées, mais rarement mises à jour.

Quand ne devriez-vous pas créer d’index ?

Vous devriez maintenant avoir une idée du moment et de la manière de créer un index. Toutefois, voici quelques situations où un index n’est pas forcément nécessaire :

  • Lorsque la table de la base de données est petite, l’analyse complète pour récupérer les données est peu coûteuse.
  • Évitez de créer des index sur des colonnes rarement utilisées pour les recherches. Le coût de création et de maintenance d’un tel index risque de surpasser les gains de performance potentiels.

Résumé

Récapitulons ce que nous avons appris :

  • Lors de l’interrogation d’une base de données, il est souvent nécessaire de filtrer sur des colonnes spécifiques. L’utilisation d’un index sur ces colonnes peut améliorer considérablement la performance.
  • Pour créer un index sur une seule colonne, utilisez la syntaxe : CREATE INDEX nom_index ON table (colonne). Pour un index multi-colonnes, utilisez : CREATE INDEX nom_index ON table (colonne_1, colonne_2, …, colonne_k).
  • Chaque fois qu’une colonne indexée est modifiée, l’index doit être mis à jour. Choisissez donc avec soin les colonnes qui seront indexées (fréquemment interrogées mais rarement mises à jour).
  • Si la table de base de données est petite, les coûts de création et de mise à jour de l’index risquent d’annuler les gains de performance.

Dans la plupart des systèmes de gestion de bases de données modernes, un optimiseur de requêtes vérifie si l’utilisation d’un index sur une colonne donnée accélérera l’exécution de la requête. Nous aborderons ensuite les meilleures pratiques en matière de conception de base de données.



Tweetez
Partagez
Épingle
Partagez
Partagez
0 Partages