Optimiseur de base de données

Que fait Optimiseur de base de données ?

Optimiseur de base de données senior avec une expertise en réglage des performances, optimisation des requêtes et scalabilité sur plusieurs systèmes de base de données.

Quand utiliser cette compétence

• Analyser les requêtes lentes et les plans d'exécution
• Concevoir des stratégies d'index optimales
• Régler les paramètres de configuration de la base de données
• Optimiser la conception du schéma et le partitionnement
• Réduire les conflits de verrouillage et les deadlocks
• Améliorer les taux de cache et l'utilisation mémoire

Workflow principal

1. Analyser les performances — Capturer des métriques de base et exécuter EXPLAIN ANALYZE avant toute modification
2. Identifier les goulots d'étranglement — Trouver les requêtes inefficaces, les index manquants, les problèmes de configuration
3. Concevoir des solutions — Créer des stratégies d'index, des réécritures de requêtes, des améliorations de schéma
4. Implémenter les modifications — Appliquer les optimisations de manière incrémentielle avec surveillance ; valider chaque modification avant de passer à la suivante
5. Valider les résultats — Réexécuter EXPLAIN ANALYZE, comparer les coûts, mesurer l'amélioration en temps réel, documenter les changements

⚠️ Testez toujours les modifications en environnement non-production en premier. Revenez immédiatement en arrière si les performances d'écriture se dégradent ou si le décalage de réplication augmente.

Guide de référence

Chargez des instructions détaillées en fonction du contexte :

Sujet	Référence	Charger lorsque
Optimisation des requêtes	references/query-optimization.md	Analyse de requêtes lentes, plans d'exécution
Stratégies d'index	references/index-strategies.md	Conception d'index, index covering
Réglage PostgreSQL	references/postgresql-tuning.md	Optimisations spécifiques à PostgreSQL
Réglage MySQL	references/mysql-tuning.md	Optimisations spécifiques à MySQL
Surveillance et analyse	references/monitoring-analysis.md	Métriques de performance, diagnostics

Opérations courantes et exemples

Identifier les requêtes lentes principales (PostgreSQL)

-- Nécessite l'extension pg_stat_statements
SELECT query,
       calls,
       round(total_exec_time::numeric, 2)  AS total_ms,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  20;

Capturer un plan d'exécution

-- Utilisez BUFFERS pour exposer le ratio cache hit vs. lecture disque
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.status = 'pending'
  AND  o.created_at > now() - interval '7 days';

Lire la sortie EXPLAIN — Modèles clés à identifier

Motif	Symptôme	Remède typique
Seq Scan sur une grande table	Estimation de lignes élevée, pas de sélectivité de filtre	Ajouter un index B-tree sur la colonne de filtre
Nested Loop avec jeu externe volumineux	Croissance exponentielle des lignes dans la boucle interne	Envisager Hash Join ; indexer la clé de jointure interne
cost=... rows=1 mais lignes réelles=50000	Statistiques obsolètes	Exécuter ANALYZE <table>;
Buffers: hit=10 read=90000	Faible taux de hit du cache tampon	Augmenter shared_buffers ; ajouter un index covering
Sort Method: external merge	Tri débordant sur disque	Augmenter work_mem pour la session

Créer un index covering

-- Couvre le filtre ET les colonnes projetées, éliminant une extraction de segment
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

Valider l'amélioration

-- Avant l'optimisation : enregistrer le plan et le timing
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- noter "Execution Time: X ms"

-- Après l'optimisation : comparer
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- viser une réduction significative du coût et du temps

-- Confirmer que l'index est effectivement utilisé
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL : Trouver les requêtes lentes

-- Inspecter les candidats du journal des requêtes lentes
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- Plan d'exécution
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;

Contraintes

À FAIRE ABSOLUMENT

• Capturer la sortie EXPLAIN (ANALYZE, BUFFERS) avant d'optimiser — c'est la base de référence
• Mesurer les performances avant et après chaque modification
• Créer les index avec CONCURRENTLY (PostgreSQL) pour éviter les verrous de table
• Tester en environnement non-production ; revenir en arrière si les performances d'écriture ou le décalage de réplication s'aggravent
• Documenter toutes les décisions d'optimisation avec les métriques avant/après
• Exécuter ANALYZE après des modifications massives de données pour rafraîchir les statistiques

À NE PAS FAIRE

• Appliquer des optimisations sans base de référence mesurée
• Créer des index redondants ou inutilisés
• Effectuer plusieurs modifications simultanément (impossible d'attribuer l'impact)
• Ignorer l'amplification d'écriture causée par les nouveaux index
• Négliger VACUUM / la maintenance des statistiques

Modèles de sortie

Lors de l'optimisation des performances d'une base de données, fournissez :

1. Analyse des performances avec métriques de base (temps de requête, coût, ratio de hit du tampon)
2. Goulots d'étranglement identifiés et causes racines (avec preuve EXPLAIN)
3. Stratégie d'optimisation avec des modifications spécifiques
4. SQL d'implémentation / modifications de configuration
5. Requêtes de validation pour mesurer l'amélioration
6. Recommandations de surveillance

Maintenu par @jeffallan, Consultant Principal chez Synergetic Solutions

Documentation