Vous souhaitez accélérer les requêtes de base de données ? Découvrez comment créer un index de base de données à l’aide de SQL, optimiser les performances des requêtes et accélérer la récupération des données.
Lorsque vous récupérez des données d’une table de base de données, vous devrez filtrer plus souvent en fonction de colonnes spécifiques.
Supposons que vous écriviez une requête SQL pour récupérer les données en fonction de conditions spécifiques. Par défaut, l’exécution de la requête exécute une analyse de la table complète jusqu’à ce que tous les enregistrements satisfaisant à la condition aient été trouvés, puis renvoie les résultats.
Cela peut s’avérer extrêmement inefficace lorsque vous devez interroger une grande table de base de données comportant plusieurs millions de lignes. Vous pouvez accélérer ces requêtes en créant un index de base de données.
Table des matières
Qu’est-ce qu’un index de base de données ?
Lorsque vous souhaitez rechercher un terme spécifique dans un livre, effectuerez-vous une numérisation complète du livre, une page après l’autre, à la recherche du terme particulier ? Eh bien, ce n’est pas le cas.
À la place, vous consulterez l’index pour savoir quelles pages font référence au terme et accéderez directement à ces pages. Un index dans une base de données fonctionne un peu comme les index d’un livre.
Un index de base de données est un ensemble de pointeurs ou de références vers les données réelles, mais triés de manière à accélérer la récupération des données. En interne, un index de base de données peut être implémenté à l’aide de structures de données telles que des arbres B+ et des tables de hachage. Par conséquent, un index de base de données améliore la vitesse et l’efficacité des opérations de récupération de données.
Création d’un index de base de données dans SQL
Maintenant que nous savons ce qu’est un index de base de données et comment il peut accélérer la récupération des données, apprenons à créer un index de base de données en SQL.
Lorsque vous effectuez des opérations de filtrage (en spécifiant la condition de récupération à l’aide d’une clause WHERE), vous souhaiterez peut-être interroger une colonne particulière plus fréquemment que d’autres.
CREATE INDEX index_name ON table (column)
Ici,
- index_name est le nom de l’index à créer
- table fait référence à la table dans la base de données relationnelle
- column fait référence au nom de la colonne de la table de la base de données sur laquelle nous devons créer l’index.
Vous pouvez également créer des index sur plusieurs colonnes (un index multi-colonnes) en fonction des besoins. Voici la syntaxe pour ce faire :
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
Passons maintenant à un exemple pratique.
Comprendre les gains de performances de l’index de base de données
Pour comprendre l’avantage de créer un index, nous devons créer une table de base de données avec un grand nombre d’enregistrements. Les exemples de code sont pour SQLiteName. Mais vous pouvez également utiliser d’autres SGBDR de votre choix, tels que PostgreSQL et MySQL.
Remplir une table de base de données avec des enregistrements
Vous pouvez également utiliser le module aléatoire intégré de Python pour créer et insérer des enregistrements dans la base de données. Cependant, nous utiliserons Faux pour remplir la table de base de données avec un million de lignes.
Le script Python suivant :
- Crée et se connecte à la base de données customer_db.
- Créez une table clients avec les champs : first_name, last_name, city et num_orders.
- Génère des données synthétiques et insère des données (un million d’enregistrements) dans la table clients.
Vous pouvez également trouver le code 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 à interroger.
Création d’un index sur la colonne Ville
Supposons que vous souhaitiez obtenir les informations client en filtrant en fonction de la colonne Ville. Votre requête SELECT ressemblera à ceci :
SELECT column(s) FROM customers WHERE condition;
Créons donc le city_idx sur la colonne city de la table clients :
CREATE INDEX city_idx ON customers (city);
⚠ La création d’un index prend un temps non négligeable et est une opération ponctuelle. Mais les avantages en termes de performances lorsque vous avez besoin d’un grand nombre de requêtes (en filtrant sur la colonne ville) seront significatifs.
Suppression d’un index de base de données
Pour supprimer un index, vous pouvez utiliser l’instruction DROP INDEX comme ceci :
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 par défaut pour obtenir les temps d’exécution des requêtes.
Vous pouvez également exécuter les requêtes à l’aide du client de ligne de commande sqlite3. Pour utiliser customer_db.db à l’aide du client de ligne de commande, exécutez la commande suivante sur le terminal :
$ sqlite3 customer_db.db;
Pour obtenir les temps d’exécution approximatifs, vous pouvez utiliser la fonctionnalité .timer intégrée à sqlite3 comme ceci :
sqlite3 > .timer on
> <query here>Comme nous avons créé un index sur la colonne city, les requêtes qui impliquent un filtrage basé sur la colonne city dans la clause WHERE seront beaucoup plus rapides.
Tout d’abord, exécutez les requêtes. Ensuite, créez l’index et réexécutez les requêtes. Notez les temps d’exécution dans les deux cas. Voici quelques exemples:
QueryTime sans IndexTime avec IndexSELECT * FROM clients
OÙ la ville COMME ‘Nouveau%’
LIMITE 10;0,100 s0,001 sSELECT * FROM clients
WHERE city=’New Wesley’;0.148 s0.001 sSELECT * FROM clients
OÙ ville DANS (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0,247 s0,003 s
On voit que les temps de récupération avec index sont plusieurs ordres plus rapides que ceux sans index sur la colonne city.
Meilleures pratiques pour créer et utiliser des index de base de données
Vous devez toujours vérifier si les gains de performances sont supérieurs à la surcharge liée à la création d’un index de base de données. Voici quelques bonnes pratiques à garder à l’esprit :
- Choisissez les bonnes colonnes pour créer un index. Évitez de créer trop d’index en raison de la surcharge importante.
- Chaque fois qu’une colonne indexée est mise à jour, l’index correspondant doit également être mis à jour. Ainsi, la création d’un index de base de données (bien qu’elle accélère la récupération) ralentit considérablement les insertions et les opérations de mise à jour. Par conséquent, vous devez créer des index sur les colonnes fréquemment interrogées mais rarement mises à jour.
Quand ne devriez-vous pas créer d’index ?
À présent, vous devriez avoir une idée du moment et de la manière de créer un index. Mais indiquons également quand l’index de base de données n’est peut-être pas nécessaire :
- Lorsque la table de la base de données est petite et ne contient pas un grand nombre de lignes, l’analyse de la table complète pour récupérer les données n’est pas aussi coûteuse.
- Ne créez pas d’index sur des colonnes rarement utilisées pour la récupération. Lorsque vous créez des index sur des colonnes qui ne sont pas fréquemment interrogées, le coût de création et de maintenance d’un index dépasse les gains de performances.
Résumé
Passons en revue ce que nous avons appris :
- Lorsque vous interrogez une base de données pour récupérer des données, vous devrez peut-être filtrer plus fréquemment en fonction de certaines colonnes. Un index de base de données sur des colonnes aussi fréquemment interrogées peut améliorer les performances.
- Pour créer un index sur une seule colonne, utilisez la syntaxe : CREATE INDEX nom_index SUR table (colonne). Si vous souhaitez créer un index multi-colonnes, utilisez : CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
- Chaque fois qu’une colonne indexée est modifiée, l’index correspondant doit également être mis à jour. Par conséquent, choisissez les bonnes colonnes (fréquemment interrogées et beaucoup moins fréquemment mises à jour) pour créer un index.
- Si la table de base de données est relativement petite, le coût de création, de maintenance et de mise à jour d’un index sera supérieur aux gains de performances.
Dans la plupart des systèmes de gestion de bases de données modernes, il existe un optimiseur de requêtes qui vérifie si un index sur une colonne spécifique accélérera l’exécution de la requête. Apprenons ensuite les meilleures pratiques en matière de conception de bases de données.