SCENE 9 : Optimisation et Maintenance - L'Entretien de la Machine | Faites entrer SQL

La ville vieillit. Les ralentissements aux carrefours. Les fuites dans les canalisations. Les index qui se fragmentent, les statistiques qui mentent. La base de données aussi a besoin d'entretien. Sinon, elle s'effondre.

Le Planificateur de Requêtes – Le Cerveau qui Décide

Quand vous lancez une requête, vous ne dites pas comment la faire.
Le planificateur (query planner) décide.
C'est lui qui choisit les index, l'ordre des jointures, les algorithmes.
Il lit les statistiques. Parfois, il se trompe.

`EXPLAIN` – Lire dans les Pensées du Planificateur

-- Voir le plan sans exécuter
EXPLAIN SELECT * FROM client WHERE nom = 'Morand';

-- Voir le plan ET les temps réels
EXPLAIN ANALYZE SELECT * FROM client WHERE nom = 'Morand';

-- Sortie typique :
-- Seq Scan on client  (cost=0.00..1850.00 rows=1 width=45) (actual time=0.012..12.345 rows=1 loops=1)
--   Filter: (nom = 'Morand')
--   Rows Removed by Filter: 99999
-- Planning Time: 0.123 ms
-- Execution Time: 12.456 ms

Traduction :

Seq Scan = Lecture séquentielle (mauvais signe sur une grosse table)
cost=0.00..1850.00 = Coût estimé (unités arbitraires)
actual time=0.012..12.345 = Temps réel (ms)
rows=1 = Lignes estimées/retournées
Filter = Filtre appliqué après lecture

Les Pièges du Planificateur – Quand le Cerveau Déraille

Statistiques périmées – Il pense qu'il y a 100 lignes, il y en a 1 million.
Paramètres mal estimés – WHERE date > CURRENT_DATE - INTERVAL '?' – le ? trompe le planificateur.
Corrélations ignorées – WHERE ville = 'Paris' AND code_postal LIKE '75%' – liées, mais il ne le sait pas.

Solutions – Aider le Cerveau

-- 1. Mettre à jour les statistiques (PostgreSQL)
ANALYZE table;

-- 2. Forcer un index (en dernier recours)
SET enable_seqscan = OFF;
-- Attention : seulement pour la session, pas en production générale

-- 3. Réécrire la requête
-- Au lieu de :
SELECT * FROM commande WHERE EXTRACT(MONTH FROM date_commande) = 3;
-- Écrire :
SELECT * FROM commande 
WHERE date_commande >= '2024-03-01' 
  AND date_commande < '2024-04-01';

-- 4. Index sur expression
CREATE INDEX idx_commande_mois ON commande (EXTRACT(MONTH FROM date_commande));

📊 Les Statistiques – Les Yeux du Planificateur

Le SGBD garde des statistiques sur les tables :

Nombre de lignes
Distribution des valeurs
Corrélations entre colonnes

Tables de Statistiques

PostgreSQL :

-- Voir les statistiques d'une table
SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables 
WHERE tablename = 'client';

-- n_dead_tup = lignes mortes (à nettoyer par VACUUM)

MySQL :

SHOW TABLE STATUS LIKE 'client';
-- Rows = estimation, Data_length = taille

`ANALYZE` – Ré-ouvrir les Yeux

-- Analyser une table
ANALYZE client;

-- Analyser une colonne spécifique (PostgreSQL)
ANALYZE client (ville);

-- Analyser toute la base
ANALYZE VERBOSE;
-- VERBOSE montre ce qu'il fait

Quand faire ANALYZE ?

Après un gros INSERT/UPDATE/DELETE
Après un VACUUM
Quand les performances se dégradent mystérieusement

🧹 Nettoyage : `VACUUM` et `OPTIMIZE`

Les SGBD n'effacent pas physiquement les données supprimées.
Ils les marquent comme "mortes". En attendant le nettoyeur.

`VACUUM` (PostgreSQL) – Le Nettoyeur de Nuit

Problème : Les DELETE et UPDATE (qui sont des DELETE+INSERT) laissent des lignes mortes.
Elles prennent de la place, ralentissent les scans.

-- Voir les lignes mortes
SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;

-- Nettoyage manuel
VACUUM client;
-- Libère l'espace pour réutilisation, ne le rend pas au OS

-- Nettoyage complet (rend l'espace au OS)
VACUUM FULL client;
-- Bloque la table, plus long

-- VACUUM avec analyse
VACUUM ANALYZE client;
-- Nettoie ET met à jour les statistiques

Autovacuum – Le nettoyeur automatique.
Normalement actif. Vérifiez qu'il tourne :

-- Vérifier l'autovacuum
SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';

-- Problèmes courants :
-- 1. autovacuum = off (à ne JAMAIS faire)
-- 2. Seuil trop haut (par défaut : 50 tuples morts + 20% de la table)

`OPTIMIZE TABLE` (MySQL) – Le Défragmenteur

-- Défragmenter et réorganiser
OPTIMIZE TABLE client, commande, produit;

-- Equivalent à :
-- 1. Recréer la table
-- 2. Recopier les données
-- 3. Reconstruire les index
-- Bloquant. À faire pendant une fenêtre de maintenance.

Pourcentage de Fragmentation

-- MySQL : voir la fragmentation
SELECT 
    table_name,
    data_free / 1024 / 1024 as fragmentation_mb,
    (data_free / (data_length + index_length)) * 100 as fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'ma_base'
  AND data_length > 0
ORDER BY fragmentation_pct DESC;

-- > 30% = OPTIMIZE nécessaire

📈 Monitoring – Les Capteurs de la Ville

Métriques Clés à Surveiller

Taille des bases/tables

-- PostgreSQL
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as taille_totale,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as taille_table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - 
                   pg_relation_size(schemaname||'.'||tablename)) as taille_indexes
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Cache Hit Ratio – Combien de lectures viennent du cache

-- PostgreSQL
SELECT 
    sum(heap_blks_read) as disk_reads,
    sum(heap_blks_hit) as cache_hits,
    (sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 0.0001)) * 100 as hit_ratio
FROM pg_statio_user_tables;

-- > 99% = bon
-- < 95% = peut-être besoin de plus de RAM

Longues requêtes

-- PostgreSQL 9.6+
SELECT 
    pid,
    now() - query_start as duree,
    query,
    state
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > interval '5 minutes'
ORDER BY duree DESC;

-- Les tuer si nécessaire
SELECT pg_cancel_backend(pid); -- Annule la requête
SELECT pg_terminate_backend(pid); -- Tue la connexion

Verrous (Locks) – Les embouteillages

-- PostgreSQL
SELECT 
    locktype,
    relation::regclass,
    mode,
    granted,
    pid,
    age(now(), query_start) as age
FROM pg_locks
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE NOT granted OR age(now(), query_start) > interval '1 minute';

Les Logs – Le Journal de Bord

PostgreSQL :

# postgresql.conf
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000  # Log les requêtes > 1s

MySQL :

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # Secondes
log_queries_not_using_indexes = 1

Analyser les logs lents :

# PostgreSQL
pgbadger postgresql-*.log -o rapport.html

# MySQL
mysqldumpslow -s t /var/log/mysql/slow.log

🔧 Maintenance Routine – Le Calendrier de l'Entretien

Quotidien

Vérifier les espaces disque
Vérifier les erreurs dans les logs
Surveiller les longues requêtes

Hebdomadaire

-- PostgreSQL
VACUUM ANALYZE;  -- Pour les tables petites/moyennes
-- ou laisser l'autovacuum faire

-- MySQL
OPTIMIZE TABLE table_critique; -- Si fragmentation > 30%
ANALYZE TABLE table_critique;

Mensuel

Backup complet + restauration test
Vérifier la croissance des tables
Purger les logs/archives
Vérifier la version, les patches de sécurité

Script de Maintenance Automatisé

-- maintenance.sql (PostgreSQL)
BEGIN;

-- 1. VACUUM des tables avec beaucoup de dead tuples
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT schemaname, relname 
        FROM pg_stat_user_tables 
        WHERE n_dead_tup > 1000
          AND last_autovacuum < now() - interval '1 day'
    LOOP
        EXECUTE format('VACUUM ANALYZE %I.%I', r.schemaname, r.relname);
        RAISE NOTICE 'VACUUM de %.% fait', r.schemaname, r.relname;
    END LOOP;
END $$;

-- 2. Réindexer les index avec plus de 30% de délétions
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT schemaname, tablename, indexname
        FROM pg_stat_user_indexes 
        WHERE idx_scan = 0  -- Jamais utilisé
    LOOP
        EXECUTE format('DROP INDEX IF EXISTS %I.%I', r.schemaname, r.indexname);
        RAISE NOTICE 'Index %.% supprimé (inutilisé)', r.schemaname, r.indexname;
    END LOOP;
END $$;

-- 3. Nettoyer les anciennes données
DELETE FROM logs_acces WHERE date < now() - interval '90 days';
DELETE FROM sessions WHERE expire < now() - interval '7 days';

COMMIT;

🚨 Les Situations d'Urgence

1. La Base est Pleine

-- Voir l'espace
SELECT pg_size_pretty(pg_database_size('ma_base'));

-- Solutions rapides :
-- a) Supprimer les données temporaires
-- b) Augmenter l'espace
-- c) Archivage d'urgence

-- Trouver les grosses tables
SELECT * FROM pg_ls_dir('.') 
WHERE pg_stat_file(pg_ls_dir).size > 1000000000; -- >1GB

2. Une Requête Tue le Serveur

-- Identifier le coupable
SELECT pid, query, now() - query_start as age
FROM pg_stat_activity 
WHERE state = 'active'
ORDER BY age DESC;

-- Le tuer
SELECT pg_cancel_backend(12345);  -- Gentil
SELECT pg_terminate_backend(12345); -- Brutal

3. Corruption de Données

-- PostgreSQL : vérifier l'intégrité
CHECKPOINT;  -- Force l'écriture sur disque
-- Redémarrer avec fsync = on (toujours on en production!)

-- MySQL
CHECK TABLE client, commande;
REPAIR TABLE table_corrompue;

4. Perte de Performances Brutale

Check-list :

🎯 Optimisation Avancée – Les Raccourcis du Vétéran

Partitionnement – Découper la Bête

Pour les tables de > 100 Go.
Découper par date, par région, par catégorie.

-- PostgreSQL 12+
CREATE TABLE commande (
    id SERIAL,
    date_commande DATE NOT NULL,
    client_id INTEGER,
    montant DECIMAL(10,2)
) PARTITION BY RANGE (date_commande);

-- Partitions mensuelles
CREATE TABLE commande_2024_01 PARTITION OF commande
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE commande_2024_02 PARTITION OF commande
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Avantages :
-- 1. DELETE vieilles données = DROP PARTITION (instantané)
-- 2. Index plus petits
-- 3. Parallelisation

Tables Temporaires vs CTE vs Sous-requêtes

-- Mauvais : CTE materialisée (PostgreSQL < 12)
WITH ventes AS (
    SELECT client_id, SUM(montant) as total
    FROM commande
    GROUP BY client_id
)
SELECT * FROM ventes WHERE total > 1000;
-- La CTE est exécutée, matérialisée, puis relue

-- Meilleur : Sous-requête
SELECT * FROM (
    SELECT client_id, SUM(montant) as total
    FROM commande
    GROUP BY client_id
) AS ventes WHERE total > 1000;
-- Peut être intégrée dans la requête principale

-- Temporaire : pour les requêtes complexes multi-étapes
CREATE TEMP TABLE ventes_intermediaire AS ...;
-- Vie pendant la session, propre à chaque utilisateur

Configuration du Serveur – Les Réglages Fins

PostgreSQL (postgresql.conf) :

shared_buffers = 25% de la RAM  # Cache
work_mem = 4-64MB  # Mémoire par opération de tri
maintenance_work_mem = 256MB-1GB  # Pour VACUUM, CREATE INDEX
effective_cache_size = 50-75% de la RAM  # Estimation pour le planificateur

MySQL (my.cnf) :

innodb_buffer_pool_size = 70-80% de la RAM  # Le plus important
innodb_log_file_size = 1-4GB  # Logs transactionnels
query_cache_type = 0  # Désactivé dans MySQL 8+

📚 Checklist de Survie en Production

Avant Mise en Production

Index sur les clés étrangères
ANALYZE après peuplement initial
Backup configuré et testé
Monitoring configuré (alertes disque, RAM, CPU)
Utilisateurs avec droits minimum

Surveillance Quotidienne

Espace disque libre (> 20%)
Pas d'erreurs dans les logs
Temps de réponse moyen < seuil
Cache hit ratio > 99%

Hebdomadaire

Vérifier la fragmentation
Vérifier les index inutilisés
Purge des données temporaires
Vérifier la croissance

Mensuel

Test de restauration de backup
REINDEX des tables critiques
Révision des performances
Mise à jour des statistiques long terme

Épilogue – La Ville qui Dure

L'optimisation, ce n'est pas une action, c'est une discipline.
La maintenance, ce n'est pas un incident, c'est une routine.
Une base de données bien entretenue dure des années.
Une base négligée s'effondre au premier pic de charge.

Les 10 Commandements :

Tu ANALYZEras après chaque gros changement.
Tu surveilleras l'espace disque.
Tu ne désactiveras pas l'autovacuum.
Tu feras des backups et les testeras.
Tu utiliseras les droits minimum.
Tu monitoreras les requêtes lentes.
Tu partitionneras les grosses tables.
Tu documenteras chaque trigger, chaque procédure.
Tu garderas les logs assez longtemps, mais pas trop.
Tu planifieras la croissance.

La ville est maintenant entre tes mains.
Elle a ses lois, ses automatismes, ses pièges.
Elle dort d'un œil, mais elle veille.
Comme toi.

SCENE 9 : Optimisation et Maintenance - L'Entretien de la Machine

Le Planificateur de Requêtes – Le Cerveau qui Décide

EXPLAIN – Lire dans les Pensées du Planificateur

Les Pièges du Planificateur – Quand le Cerveau Déraille

Solutions – Aider le Cerveau

📊 Les Statistiques – Les Yeux du Planificateur

Tables de Statistiques

ANALYZE – Ré-ouvrir les Yeux

🧹 Nettoyage : VACUUM et OPTIMIZE

VACUUM (PostgreSQL) – Le Nettoyeur de Nuit

OPTIMIZE TABLE (MySQL) – Le Défragmenteur

Pourcentage de Fragmentation

📈 Monitoring – Les Capteurs de la Ville

Métriques Clés à Surveiller

Les Logs – Le Journal de Bord

🔧 Maintenance Routine – Le Calendrier de l'Entretien

Quotidien

Hebdomadaire

Mensuel

Script de Maintenance Automatisé

🚨 Les Situations d'Urgence

1. La Base est Pleine

2. Une Requête Tue le Serveur

3. Corruption de Données

4. Perte de Performances Brutale

🎯 Optimisation Avancée – Les Raccourcis du Vétéran

Partitionnement – Découper la Bête

Tables Temporaires vs CTE vs Sous-requêtes

Configuration du Serveur – Les Réglages Fins

📚 Checklist de Survie en Production

Avant Mise en Production

Surveillance Quotidienne

Hebdomadaire

Mensuel

Épilogue – La Ville qui Dure

`EXPLAIN` – Lire dans les Pensées du Planificateur

`ANALYZE` – Ré-ouvrir les Yeux

🧹 Nettoyage : `VACUUM` et `OPTIMIZE`

`VACUUM` (PostgreSQL) – Le Nettoyeur de Nuit

`OPTIMIZE TABLE` (MySQL) – Le Défragmenteur