Optimisation des index PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez comment optimiser les performances d'une base de données PostgreSQL grâce à l'indexation. Vous commencerez par créer une table users d'exemple et la remplir avec des données. Ensuite, vous créerez un index à une seule colonne, analyserez les plans de requête à l'aide de EXPLAIN, construirez un index multi-colonnes et, enfin, apprendrez à supprimer un index inutilisé afin de maintenir l'efficacité de la base de données. Cette expérience pratique vous fournira des compétences pratiques en matière de gestion des index PostgreSQL.

Skills Graph

Créer un index à une seule colonne

Dans cette étape, vous allez créer une table d'exemple nommée users et ensuite créer un index à une seule colonne sur la colonne email. Les index sont essentiels pour améliorer les performances des requêtes de base de données, en particulier lorsqu'il s'agit de grandes tables.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Maintenant, créez la table users. Exécutez la commande SQL suivante :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

Cette commande crée une table nommée users avec des colonnes pour id, username, email et created_at. La colonne id est la clé primaire et s'incrémente automatiquement.

Ensuite, insérez des données d'exemple dans la table users. Exécutez les commandes SQL suivantes :

INSERT INTO users (username, email, created_at) VALUES
('john_doe', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('jane_smith', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('peter_jones', '[email protected]', NOW());
INSERT INTO users (username, email, created_at) VALUES
('mary_brown', '[email protected]', NOW());

Vous avez maintenant inséré quatre lignes de données dans la table users.

Pour accélérer les requêtes basées sur la colonne email, créez un index sur la colonne email. Exécutez la commande SQL suivante :

CREATE INDEX idx_users_email ON users (email);

Cette commande crée un index nommé idx_users_email sur la colonne email de la table users.

Pour vérifier que l'index a été créé, vous pouvez utiliser la commande \di dans psql. Exécutez la commande suivante :

\di

Vous devriez voir l'index idx_users_email listé dans la sortie.

Enfin, quittez l'interpréteur psql en tapant :

\q

Utiliser EXPLAIN pour analyser les plans de requête

Dans cette étape, vous apprendrez à utiliser la commande EXPLAIN dans PostgreSQL pour analyser les plans de requête. Comprendre les plans de requête est essentiel pour optimiser les requêtes de base de données et garantir des performances efficaces.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Maintenant, utilisons la commande EXPLAIN pour analyser une requête simple. Exécutez la commande suivante :

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Cette commande affichera le plan de requête pour l'instruction SELECT. La sortie montre comment PostgreSQL a l'intention d'exécuter la requête, y compris s'il utilisera un index.

Pour obtenir des informations plus détaillées, y compris le coût, vous pouvez utiliser EXPLAIN ANALYZE. Cependant, pour cet exemple de base, EXPLAIN est suffisant.

Maintenant, analysons une requête qui pourrait ne pas utiliser l'index. Exécutez la commande suivante :

EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

La sortie affichera probablement un "Seq Scan" (Sequential Scan - parcours séquentiel) sur la table users, ce qui signifie que PostgreSQL analyse l'ensemble de la table pour trouver les lignes correspondantes. C'est moins efficace que d'utiliser un index.

En analysant les plans de requête avec EXPLAIN, vous pouvez identifier les goulots d'étranglement potentiels en matière de performances et déterminer si vos index sont utilisés efficacement.

Enfin, quittez l'interpréteur psql en tapant :

\q

Créer un index multi-colonnes

Dans cette étape, vous apprendrez à créer un index multi-colonnes dans PostgreSQL. Un index multi-colonnes est un index qui inclut deux colonnes ou plus. Il peut améliorer considérablement les performances des requêtes lorsque les requêtes filtrent ou trient simultanément par plusieurs colonnes.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Supposons que vous interrogiez souvent la table users en fonction des colonnes username et email. Pour optimiser ces requêtes, vous pouvez créer un index multi-colonnes sur ces deux colonnes. Exécutez la commande SQL suivante :

CREATE INDEX idx_users_username_email ON users (username, email);

Cette commande crée un index nommé idx_users_username_email sur les colonnes username et email de la table users. L'ordre des colonnes dans la définition de l'index est important. L'index est plus efficace lorsque la requête filtre sur les colonnes dans le même ordre que celui dans lequel elles apparaissent dans l'index.

Pour vérifier que l'index a été créé, vous pouvez utiliser la commande \di dans psql. Exécutez la commande suivante :

\di

Vous devriez voir l'index idx_users_username_email listé dans la sortie.

Maintenant, analysons une requête qui peut bénéficier de cet index multi-colonnes. Exécutez la commande suivante :

EXPLAIN SELECT * FROM users WHERE username = 'john_doe' AND email = '[email protected]';

La sortie devrait montrer que PostgreSQL utilise l'index idx_users_username_email pour exécuter la requête.

Enfin, quittez l'interpréteur psql en tapant :

\q

Supprimer un index inutilisé

Dans cette étape, vous apprendrez à supprimer un index inutilisé dans PostgreSQL. Les index améliorent les performances des requêtes, mais ils consomment également de l'espace de stockage et peuvent ralentir les opérations d'écriture (insertions, mises à jour et suppressions). Par conséquent, il est important d'identifier et de supprimer les index qui ne sont plus utilisés.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Supposons qu'après avoir analysé vos modèles de requêtes, vous déterminez que l'index idx_users_email est rarement utilisé. Pour supprimer cet index, exécutez la commande SQL suivante :

DROP INDEX idx_users_email;

Cette commande supprime l'index nommé idx_users_email de la base de données.

Pour vérifier que l'index a été supprimé, vous pouvez utiliser la commande \di dans psql. Exécutez la commande suivante :

\di

Vous ne devriez plus voir l'index idx_users_email listé dans la sortie.

Avant de supprimer un index, il est essentiel de s'assurer qu'il est réellement inutilisé. Vous pouvez utiliser le collecteur de statistiques de PostgreSQL pour recueillir des informations sur l'utilisation de l'index. Cependant, l'activation et l'analyse de ces statistiques dépassent le cadre de ce labo. Dans un scénario réel, vous surveilleriez l'utilisation de l'index sur une période donnée avant de décider de le supprimer.

La suppression d'un mauvais index peut avoir un impact négatif sur les performances des requêtes. Par conséquent, soyez prudent et analysez attentivement vos modèles de requêtes avant de supprimer un index.

Enfin, quittez l'interpréteur psql en tapant :

\q

Résumé

Dans ce labo, vous avez appris à créer des index à une seule colonne et des index multi-colonnes dans PostgreSQL pour améliorer les performances des requêtes. Vous avez également appris à analyser les plans d'exécution des requêtes à l'aide de EXPLAIN pour déterminer si vos index sont utilisés efficacement. Enfin, vous avez appris à supprimer un index inutilisé pour maintenir l'efficacité de la base de données. Ces compétences sont essentielles pour optimiser les performances d'une base de données PostgreSQL.