Assistant d'optimisation des performances SQL

Optimisation expert des performances SQL pour ${selection} (ou l'ensemble du projet si aucune sélection). Concentrez-vous sur les techniques d'optimisation SQL universelles qui fonctionnent sur MySQL, PostgreSQL, SQL Server, Oracle et autres bases de données SQL.

🎯 Domaines d'optimisation centraux

Analyse des performances des requêtes

-- ❌ BAD: Motifs de requête inefficaces
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ GOOD: Requête optimisée avec des indices appropriés
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- Index requis :
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Optimisation de la stratégie d'indexation

-- ❌ BAD: Mauvaise stratégie d'indexation
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ GOOD: Indexation composite optimisée
-- Pour les requêtes filtrant d'abord par email, puis triant par created_at
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- Pour les recherches de noms en texte intégral
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- Pour les requêtes de statut utilisateur
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

Optimisation des sous-requêtes

-- ❌ BAD: Sous-requête corrélée
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ GOOD: Approche avec fonction fenêtre
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 Techniques d'optimisation des performances

Optimisation des JOIN

-- ❌ BAD: Ordre des JOIN et conditions inefficaces
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ GOOD: JOIN optimisé avec filtrage
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

Optimisation de la pagination

-- ❌ BAD: Pagination basée sur OFFSET (lente pour les grands décalages)
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ GOOD: Pagination basée sur curseur
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- Ou en utilisant un curseur basé sur l'ID
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

Optimisation de l'agrégation

-- ❌ BAD: Plusieurs requêtes d'agrégation distinctes
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ GOOD: Requête unique avec agrégation conditionnelle
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 Anti-motifs de requête

Problèmes de performance SELECT

-- ❌ BAD: Anti-motif SELECT *
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ GOOD: Sélection explicite de colonnes
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

Optimisation de la clause WHERE

-- ❌ BAD: Appels de fonction dans la clause WHERE
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ GOOD: Clause WHERE compatible avec les index
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- À considérer : CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

Optimisation OR vs UNION

-- ❌ BAD: Conditions OR complexes
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ GOOD: Approche UNION pour une meilleure optimisation
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 Optimisation indépendante de la base de données

Opérations par lot

-- ❌ BAD: Opérations ligne par ligne
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ GOOD: Insertion par lot
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

Utilisation de tables temporaires

-- ✅ GOOD: Utilisation de tables temporaires pour les opérations complexes
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- Utiliser la table temporaire pour d'autres calculs
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ Gestion des index

Principes de conception des index

-- ✅ GOOD: Conception d'index couvrant
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- Syntaxe SQL Server
-- Ou : CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Autres bases de données

Stratégie d'index partiel

-- ✅ GOOD: Index partiels pour des conditions spécifiques
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 Requêtes de surveillance des performances

Analyse des performances des requêtes

-- Approche générique pour identifier les requêtes lentes
-- (La syntaxe spécifique varie selon la base de données)

-- Pour MySQL :
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- Pour PostgreSQL :
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- Pour SQL Server :
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 Liste de contrôle d'optimisation universelle

Structure de requête

• [ ] Éviter SELECT * dans les requêtes en production
• [ ] Utiliser les types de JOIN appropriés (INNER vs LEFT/RIGHT)
• [ ] Filtrer tôt dans les clauses WHERE
• [ ] Utiliser EXISTS au lieu de IN pour les sous-requêtes le cas échéant
• [ ] Éviter les fonctions dans les clauses WHERE qui empêchent l'utilisation d'index

Stratégie d'indexation

• [ ] Créer des index sur les colonnes fréquemment interrogées
• [ ] Utiliser les index composites dans le bon ordre de colonnes
• [ ] Éviter la sur-indexation (impact sur les performances INSERT/UPDATE)
• [ ] Utiliser les index couvrants quand c'est bénéfique
• [ ] Créer des index partiels pour des motifs de requête spécifiques

Types de données et schéma

• [ ] Utiliser les types de données appropriés pour l'efficacité du stockage
• [ ] Normaliser de manière appropriée (3NF pour OLTP, dénormalisé pour OLAP)
• [ ] Utiliser les contraintes pour aider l'optimiseur de requêtes
• [ ] Partitionner les grandes tables le cas échéant

Motifs de requête

• [ ] Utiliser LIMIT/TOP pour le contrôle de l'ensemble de résultats
• [ ] Implémenter des stratégies de pagination efficaces
• [ ] Utiliser les opérations par lot pour les modifications de données en masse
• [ ] Éviter les problèmes de requête N+1
• [ ] Utiliser les instructions préparées pour les requêtes répétées

Tests de performance

• [ ] Tester les requêtes avec des volumes de données réalistes
• [ ] Analyser les plans d'exécution des requêtes
• [ ] Surveiller les performances des requêtes au fil du temps
• [ ] Configurer des alertes pour les requêtes lentes
• [ ] Analyse régulière de l'utilisation des index

📝 Méthodologie d'optimisation

1. Identifier : Utilisez des outils spécifiques à la base de données pour trouver les requêtes lentes
2. Analyser : Examinez les plans d'exécution et identifiez les goulots d'étranglement
3. Optimiser : Appliquez les techniques d'optimisation appropriées
4. Tester : Vérifiez les améliorations de performance
5. Surveiller : Suivez continuellement les métriques de performance
6. Itérer : Examen et optimisation réguliers des performances

Concentrez-vous sur les améliorations de performance mesurables et testez toujours les optimisations avec des volumes de données réalistes et des motifs de requête.