[Explication] Comment créer un index de base de données dans SQL

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 un analyse de la table complète jusqu'à ce que tous les enregistrements qui satisfont à 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 indice de base de données.

What is a Database Index?

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, non.

Tu vas plutôt consulter l'index découvrir qui les pages font référence au terme et accèdent 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é d'une manière qui accélère 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.

Creating a Database Index in 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 d'extraction à 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 le faire :

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

Passons maintenant à un exemple pratique.

Understanding the Performance Gains of Database Index

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 SQLite. Mais vous pouvez également utiliser d'autres SGBDR de votre choix, comme PostgreSQL , 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 Faker pour remplir la table de base de données avec un million de lignes.

Le script Python suivant :

• Crée et se connecte au customer_db base de données.
• Créer un customers tableau avec les champs : first_name, last_name, cityet au num_orders.
• Génère des données synthétiques et insère des données (un million d'enregistrements) dans le customers tableau.

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 du city colonne. Votre requête SELECT ressemblera à ceci :

SELECT column(s) FROM customers
WHERE condition;

Alors créons le city_idx sur le city colonne dans le customers table:

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 performances sont améliorées lorsque vous avez besoin d'un grand nombre de requêtes : en filtrant sur le city colonne – sera significatif.

Suppression d'un index de base de données

Pour supprimer un index, vous pouvez utiliser le DROP INDEX déclaration 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. Travailler avec 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 le .timer fonctionnalité intégrée à sqlite3 comme suit :

sqlite3 > .timer on
        > <query here>

Parce que nous avons créé un index sur le city colonne, les requêtes qui impliquent un filtrage basé sur le city colonne dans le WHERE la clause sera beaucoup plus rapide.

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:

Nous voyons que les temps de récupération avec index sont plusieurs ordres plus rapides que ceux sans index sur la colonne city.

Best Practices to Create and Use Database Indexes

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. Donc, créer un index de base de données (bien qu'accélère la récupération) de manière significative ralentit insertions et 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.

When Should You Not Create an 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ésumer

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 ces colonnes fréquemment interrogées peut améliorer les performances.
• Pour créer un index sur une seule colonne, utilisez la syntaxe : CREATE INDEX index_name ON table (column). 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 plus 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 base de données modernes, il existe un optimiseur de requête qui vérifie si un index sur une colonne spécifique accélérera l'exécution de la requête. Ensuite, apprenons le meilleures pratiques pour la conception de bases de données.