Maintenance de la base de données SQLite

Introduction

Dans ce laboratoire, vous apprendrez comment maintenir votre base de données SQLite pour une performance optimale. Nous aborderons des techniques essentielles telles que l'utilisation de la commande VACUUM pour récupérer de l'espace, la reconstruction des index pour accélérer les requêtes, et l'analyse des statistiques de table pour aider SQLite à prendre de meilleures décisions. Commençons !

Skills Graph

Créer une base de données et une table d'exemple

Avant de nous plonger dans la maintenance, créons une base de données SQLite et une table d'exemple avec quelques données. Cela nous donnera quelque chose sur quoi travailler.

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Pour créer une base de données SQLite nommée mydb.db, exécutez la commande suivante :

sqlite3 mydb.db

Cette commande créera le fichier de base de données (s'il n'existe pas) et ouvrira l'outil de ligne de commande SQLite, vous connectant à la base de données. Vous verrez l'invite sqlite>.

Maintenant, créons une table nommée users pour stocker les informations des utilisateurs. Exécutez la commande SQL suivante :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);

Cette commande crée une table avec trois colonnes : id (un entier qui identifie de manière unique chaque utilisateur), name (le nom de l'utilisateur) et email (l'adresse e-mail de l'utilisateur). La contrainte PRIMARY KEY garantit que chaque id est unique.

Ensuite, insérons des exemples de données dans la table users :

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
INSERT INTO users (name, email) VALUES ('Charlie', '[email protected]');

Ces commandes ajoutent trois lignes à la table users.

Pour vérifier que la table et les données ont été créées avec succès, exécutez la commande suivante :

SELECT * FROM users;

Résultat attendu :

1|Alice|[email protected]
2|Bob|[email protected]
3|Charlie|[email protected]

Cette sortie affiche le contenu de la table users.

Simuler la suppression de données

Pour démontrer l'effet de VACUUM, nous devons simuler la suppression de données, ce qui peut entraîner une fragmentation dans le fichier de base de données.

Supprimons une ligne de la table users :

DELETE FROM users WHERE id = 2;

Cette commande supprime la ligne où l'id est 2 (l'enregistrement de Bob).

Pour confirmer la suppression, exécutez la commande suivante :

SELECT * FROM users;

Résultat attendu :

1|Alice|[email protected]
3|Charlie|[email protected]

Vous remarquerez que l'enregistrement de Bob n'est plus dans la table. Cette suppression laisse un espace vide dans le fichier de base de données, que VACUUM peut aider à combler.

Exécuter VACUUM pour récupérer de l'espace

Maintenant, utilisons la commande VACUUM pour défragmenter le fichier de base de données et récupérer l'espace laissé par l'enregistrement supprimé.

Exécutez la commande suivante dans l'interpréteur (shell) SQLite :

VACUUM;

Cette commande réécrit l'intégralité du fichier de base de données, consolidant les données et supprimant l'espace vide.

Vous ne verrez aucune sortie spécifique après avoir exécuté VACUUM, mais il travaille en arrière-plan pour optimiser votre base de données.

Pour quitter l'interpréteur SQLite, exécutez :

.exit

Vous êtes maintenant de retour dans votre terminal.

Créer un index

Les index sont essentiels pour accélérer les requêtes. Créons un index sur la colonne email de la table users.

Tout d'abord, reconnectez-vous à la base de données SQLite :

sqlite3 mydb.db

Maintenant, créez l'index en utilisant la commande suivante :

CREATE INDEX idx_users_email ON users (email);

Cette commande crée un index nommé idx_users_email sur la colonne email. SQLite utilisera cet index pour trouver rapidement les utilisateurs en fonction de leur adresse e-mail.

Reconstruire l'index

Avec le temps, les index peuvent se fragmenter, surtout après de nombreuses modifications de données. Reconstruire l'index peut améliorer son efficacité.

Pour reconstruire l'index que nous venons de créer, utilisez la commande REINDEX :

REINDEX idx_users_email;

Cette commande reconstruit l'index idx_users_email, garantissant qu'il est optimisé pour les données actuelles.

Pour quitter l'interpréteur (shell) SQLite, exécutez :

.exit

Vous êtes maintenant de retour dans votre terminal.

Analyser les statistiques de la table

SQLite utilise des statistiques sur vos données pour optimiser les requêtes. Il est recommandé de mettre à jour ces statistiques périodiquement.

Tout d'abord, reconnectez-vous à la base de données SQLite :

sqlite3 mydb.db

Pour analyser la table users, exécutez la commande suivante :

ANALYZE users;

Cette commande collecte des statistiques sur la table users, que l'optimiseur de requêtes (query optimizer) peut utiliser pour améliorer les performances des requêtes.

Enfin, quittez l'interpréteur (shell) SQLite :

.exit

Vous êtes maintenant de retour dans votre terminal.

Résumé

Dans ce labo, vous avez appris à effectuer des tâches de maintenance essentielles sur votre base de données SQLite. Vous avez utilisé la commande VACUUM pour récupérer de l'espace, créé et reconstruit un index pour améliorer les performances des requêtes, et analysé les statistiques de la table pour aider SQLite à optimiser les requêtes. Ces techniques sont cruciales pour assurer le bon fonctionnement et l'efficacité de votre base de données.