Objectif : Comprendre les fondamentaux de MySQL et appliquer des méthodes pratiques d'optimisation SQL pour améliorer les performances des requêtes.

Concepts de base de MySQL

Une base de données sert de conteneur logique pour organiser les informations (ex. : une base de commandes ou d'utilisateurs). Une table stocke les données de manière structurée, avec des colonnes comme id, nom, courriel dans une table utilisateurs.
Le langage SQL se divise en trois catégories :

• DDL : définit la structure (ex. : CREATE TABLE).
• DML : manipule les données (ex. : INSERT, UPDATE).
• DQL : interorge les données (ex. : SELECT).

Exemples de syntaxe SQL essentiels

-- Requête de base
SELECT nom, courriel FROM utilisateurs WHERE id = 1;

-- Requête sur une plage
SELECT * FROM commandes WHERE montant > 100;

-- Insertion et mise à jour
INSERT INTO utilisateurs (nom, courriel) VALUES ('Marie Curie', 'marie.curie@exemple.com');
UPDATE commandes SET statut = 'livré' WHERE reference = 5001;

Pourquoi optimiser les requêtes SQL ?

Des requêtes non optimisées peuvent provoquer des analyses complètes de table, augmentant les temps de réponse (ex. : une requête SELECT * sur des millions de lignes prend plusieurs secondes). Cela surcharge les ressources CPU et mémoire, dégradant l'expérience utilisateur avec des chargements lents et des opérations bloquées.

Inedxation : accélérateur des recherches

Les index améliorent la vitesse des requêtes en évitant les scans complets :

• Index primaire : unique et non nul (clé primaire).
• Index standard : accélère les recherches sur un champ spécifique (ex. : CREATE INDEX idx_nom ON utilisateurs(nom)).
• Index composite : combine plusieurs colonnes (ex. : INDEX idx_nom_age (nom, age)).

-- Sans index (analyse complète de table)
SELECT * FROM utilisateurs WHERE nom = 'Marie Curie';

-- Ajout d'un index pour optimiser
ALTER TABLE utilisateurs ADD INDEX idx_nom(nom);
SELECT * FROM utilisateurs USE INDEX (idx_nom) WHERE nom = 'Marie Curie';

Analyse avec EXPLAIN

Utilisez EXPLAIN pour évaluer l'efficacité des requêtes :

EXPLAIN SELECT * FROM utilisateurs WHERE age > 25;

Champs clés à interpréter :

• type : ALL (analyse complète) indique un manque d'index ; visez ref (recherche par index).
• rows : nombre de lignes parcourues, à minimiser.
• Extra : Using filesort suggère une optimisation du tri nécessaire.

Dix règles d'optimisation des requêtes

1. Évitez SELECT * : sélectionnez uniquement les colonnes requises pour réduire le transfert de données.

2. Limitez l'utilisation de LIKE '%prefixe' : les recherches floues contournent les index ; préférez la recherche plein texte si possible.

3. Optimisez la pagination : ``` -- Inefficace pour les grands offsets SELECT * FROM commandes LIMIT 1000000, 10;

   -- Meilleure approche SELECT * FROM commandes WHERE reference > 1000000 LIMIT 10;

4. Utilisez des jointures à la place des sous-requêtes : les sous-requêtes peuvent générer des tables temporaires, tandis que les jointures sont plus performantes.

Gestion des transactions et des verrous

Pour réduire la contention des verrous :

• Transactions courtes : libérez rapidement les verrous en commettant tôt.
• Verrous au niveau des lignes : utilisez le moteur InnoDB, qui applique des verrous de ligne par défaut, évitant ainsi les verrous de table.

-- Transaction longue (verrouillage prolongé)
BEGIN;
UPDATE comptes SET solde = solde - 50 WHERE client_id = 2;
-- Calculs complexes ici...
COMMIT;

-- Approche optimisée : fractionnez les transactions pour libérer les verrous rapidement.

Bonnes pratiques de conception de bases de données

1. Normalisation : réduisez la redondance (ex. : adresse des clients dans une table séparée).
2. Choix des types de colonnes :
   • Utilisez INT plutôt que VARCHAR pour les identifiants numériques.
   • Pour les dates, préférez DATETIME aux chaînes de caractères.
3. Séparation des données froides et chaudes : archivez les données historiques (ex. : table de commandes divisée par année).

Surveillance des requêtes lentes

Activez le journal des requêtes lentes dans la configuration MySQL :

-- Dans le fichier my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  -- Journaliser les requêtes dépassant 2 secondes

Outils d'analyse :

• mysqldumpslow : pour analyser les journaux.
• Percona Toolkit : pour un diagnostic avancé.