Optimisation de Base de Données : Performance SQL et Indexation

Pourquoi la performance de la base de données importe

Les requêtes lentes de base de données augmentent le temps de réponse de l'application. Les requêtes de plus de 100ms affectent négativement l'expérience utilisateur.

Le goulot d'étranglement de la base de données peut affecter 80% de la performance de l'application. La base de données est généralement le plus grand goulot d'étranglement plutôt que le CPU ou la mémoire.

L'optimisation réduit les coûts matériels. Des requêtes plus efficaces nécessitent moins de ressources serveur.

Stratégies d'Indexation

Index B-Tree : Le type d'index le plus courant. Efficace pour les requêtes d'égalité (=) et de plage (<, >, BETWEEN). Les clés primaires et étrangères sont automatiquement indexées.

Index composite : Index contenant plusieurs colonnes. Déterminez l'ordre des colonnes selon l'ordre de la requête. La première colonne dans la clause WHERE doit être la plus sélective.

Index covering : Index contenant toutes les colonnes utilisées dans la requête. Pas besoin d'accès à la table, seul l'index est lu.

Index partiel : Index pour les lignes répondant à une condition spécifique. Comme WHERE is_active = true. Réduit la taille de l'index.

Maintenance des index : Supprimez les index inutilisés. Chaque index ralentit les opérations INSERT/UPDATE/DELETE. Vérifiez l'utilisation de l'index avec EXPLAIN.

Optimisation des Requêtes

EXPLAIN / EXPLAIN ANALYZE : Analysez le plan d'exécution de la requête. Visez Index Scan au lieu de Seq Scan (scan complet de table).

Problème N+1 : Récupérez les données liées en une seule requête (JOIN) au lieu de faire une requête séparée pour chaque enregistrement. Utilisez eager loading dans les ORMs.

N'utilisez pas SELECT * : Sélectionnez uniquement les colonnes nécessaires. Cela réduit le trafic réseau et l'utilisation de la mémoire.

Optimisation des JOIN : Évitez les JOINs inutiles. Faites JOIN des tables plus petites d'abord. Utilisez INNER JOIN au lieu de LEFT JOIN (si possible).

Subquery vs JOIN : Les sous-requêtes corrélées sont lentes. Utilisez JOIN ou CTE (Common Table Expression) quand possible.

Cache et Connection Pooling

Query cache : Mettez en cache les résultats des requêtes fréquemment utilisées. Faites du caching au niveau application avec Redis ou Memcached.

Connection pooling : Réutilisez les connexions de base de données. Ouvrir une nouvelle connexion pour chaque requête est coûteux. Utilisez pgBouncer (PostgreSQL) ou HikariCP (Java).

Read replicas : Routez les opérations de lecture vers les serveurs replica. Gardez les opérations d'écriture sur le serveur primaire. Cela distribue la charge de lecture.

Materialized views : Créez une materialized view pour les requêtes d'agrégation complexes. Rafraîchissez périodiquement.

Conception de Base de Données

Normalisation : Réduisez la répétition des données (1NF, 2NF, 3NF). Cependant, la sur-normalisation augmente le nombre de JOIN et réduit la performance.

Dénormalisation : La dénormalisation améliore la performance dans les systèmes à forte lecture (analytics, reporting). Trade-off : cohérence des données vs performance.

Partitioning : Partitionnez les grandes tables (millions de lignes). Le range partitioning (basé sur la date) est la méthode la plus courante.

Types de données : Utilisez les bons types de données. INT au lieu de VARCHAR, VARCHAR (taille appropriée) au lieu de TEXT. Les types de données plus petits occupent moins d'espace et sont traités plus rapidement.

Surveillance et Profiling

Slow query log : Loggez les requêtes lentes. PostgreSQL : log_min_duration_statement = 100. MySQL : slow_query_log = 1.

Performance Schema (MySQL) / pg_stat_statements (PostgreSQL) : Collectez les statistiques de requêtes. Identifiez les requêtes qui prennent le plus de temps.

Outils APM : Surveillez la performance de la base de données avec Datadog, New Relic ou Dynatrace. Suivez les temps de requête et les taux d'erreur.

Utilisation des index : Vérifiez l'utilisation des index avec pg_stat_user_indexes (PostgreSQL). Supprimez les index inutilisés.