# Fichier: python_cheats/cheatsheets/introduction_mysql.txt
# Introduction MySQL (Relationnel) - Pour Grands Débutants
# Comprendre MySQL de A à Z : Architecture, Fonctionnement, Pratique

"""
[OK] INTRODUCTION : POURQUOI CE GUIDE ?

# Tu débutes avec les bases de données relationnelles et MySQL ?
# Ce guide est fait pour TOI !

# Objectif :
# Te montrer EXACTEMENT comment MySQL fonctionne "sous le capot"
# Comprendre ce qui se passe quand tu exécutes une requête SQL
# Découvrir TOUS les fichiers créés et leur rôle précis
# Maîtriser les concepts relationnels (tables, jointures, transactions)
# Aucun concept ne sera laissé dans le flou !

# Plan du guide :
# 1. Qu'est-ce qu'une base de données relationnelle et pourquoi MySQL ?
# 2. Histoire et évolution de MySQL
# 3. Installation et fichiers créés (détail complet)
# 4. Architecture interne de MySQL
# 5. Du SQL à l'exécution : voyage complet d'une requête
# 6. Moteurs de stockage (InnoDB vs MyISAM)
# 7. Types de données et modélisation
# 8. Index et optimisation de performance
# 9. Transactions ACID et verrouillage
# 10. Jointures et relations entre tables
# 11. Réplication et haute disponibilité
# 12. Procédures stockées, triggers et vues
# 13. Sécurité et gestion des utilisateurs
# 14. Monitoring et maintenance
# 15. Premiers pas pratiques
# 16. Cas d'usage réels et bonnes pratiques
"""


# [OK] PARTIE 1 : QU'EST-CE QU'UNE BASE DE DONNÉES RELATIONNELLE ?

"""
┌────────────────────────────────────────────────────────────────────────┐
│           BASES DE DONNÉES RELATIONNELLES : LES FONDATIONS             │
└────────────────────────────────────────────────────────────────────────┘

CONTEXTE HISTORIQUE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

AVANT 1970 : ÈRE DES FICHIERS PLATS
-> Données stockées dans des fichiers texte
-> Pas de structure standardisée
-> Accès séquentiel uniquement
-> Redondance massive
-> Pas d'intégrité des données

PROBLÈMES :
[X] Duplication des données partout
[X] Incohérences fréquentes
[X] Difficile de maintenir
[X] Pas de relations entre données
[X] Pas de contrôle d'accès

RÉVOLUTION 1970 : MODÈLE RELATIONNEL (Edgar F. Codd, IBM)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Edgar F. Codd publie "A Relational Model of Data for Large Shared Data Banks"

IDÉES RÉVOLUTIONNAIRES :
-> Organiser les données en TABLES (relations mathématiques)
-> Éliminer la redondance (normalisation)
-> Langage déclaratif (SQL) au lieu de procédural
-> Indépendance physique/logique
-> Intégrité référentielle

NAISSANCE DU SQL (Structured Query Language) :
-> 1974 : IBM développe SEQUEL (Structured English Query Language)
-> 1979 : Renommé SQL
-> 1986 : Standardisation ANSI SQL
-> Devient LE langage universel des bases de données


ANALOGIE : BIBLIOTHÈQUE vs PILE DE LIVRES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FICHIERS PLATS (pile de livres) :
┌────────────────────────────────────────────────────────────────────┐
│ [DOCS] PILE DE LIVRES EN VRAC                                          │
│                                                                    │
│ Livre 1 : "MySQL" | Auteur: Jean | Date: 2020 | Prix: 50€        │
│ Livre 2 : "Python" | Auteur: Marie | Date: 2021 | Prix: 45€      │
│ Livre 3 : "MySQL" | Auteur: Jean | Date: 2020 | Prix: 50€  <- DUP!│
│ Livre 4 : "Java" | Auteur: Pierre | Date: 2019 | Prix: 60€       │
│                                                                    │
│ Problèmes :                                                        │
│ [X] Informations dupliquées (Jean apparaît 2 fois)                  │
│ [X] Si le prix du livre MySQL change -> modifier 2 fois !            │
│ [X] Recherche lente (tout lire séquentiellement)                    │
│ [X] Pas de garantie d'intégrité                                     │
└────────────────────────────────────────────────────────────────────┘

BASE DE DONNÉES RELATIONNELLE (bibliothèque organisée) :
┌────────────────────────────────────────────────────────────────────┐
│ [DOCS] BIBLIOTHÈQUE AVEC SYSTÈME DE FICHES                             │
│                                                                    │
│ TABLE: livres                                                      │
│ ┌────┬─────────┬────────────┬──────┬───────┐                      │
│ │ id │  titre  │ auteur_id  │ date │ prix  │                      │
│ ├────┼─────────┼────────────┼──────┼───────┤                      │
│ │ 1  │ MySQL   │ 101        │ 2020 │ 50€   │                      │
│ │ 2  │ Python  │ 102        │ 2021 │ 45€   │                      │
│ │ 3  │ Java    │ 103        │ 2019 │ 60€   │                      │
│ └────┴─────────┴────────────┴──────┴───────┘                      │
│                                                                    │
│ TABLE: auteurs                                                     │
│ ┌────┬────────┬──────────────────────┐                            │
│ │ id │  nom   │       email          │                            │
│ ├────┼────────┼──────────────────────┤                            │
│ │101 │ Jean   │ jean@example.com     │                            │
│ │102 │ Marie  │ marie@example.com    │                            │
│ │103 │ Pierre │ pierre@example.com   │                            │
│ └────┴────────┴──────────────────────┘                            │
│                                                                    │
│ Avantages :                                                        │
│ [OK] Informations de Jean stockées UNE SEULE FOIS                    │
│ [OK] Relations via clés étrangères (auteur_id)                       │
│ [OK] Recherche ultra-rapide avec index                               │
│ [OK] Intégrité garantie (contraintes)                                │
│ [OK] Pas de redondance                                               │
└────────────────────────────────────────────────────────────────────┘


CONCEPTS CLÉS DU MODÈLE RELATIONNEL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. TABLE (Relation) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Structure bidimensionnelle (lignes × colonnes)
-> Représente un ensemble d'entités similaires
-> Chaque ligne = un enregistrement (tuple)
-> Chaque colonne = un attribut

Exemple :
TABLE utilisateurs
┌────┬──────────┬──────────────────────┬─────┐
│ id │   nom    │        email         │ age │
├────┼──────────┼──────────────────────┼─────┤
│ 1  │ Jean     │ jean@example.com     │ 30  │
│ 2  │ Marie    │ marie@example.com    │ 25  │
│ 3  │ Pierre   │ pierre@example.com   │ 35  │
└────┴──────────┴──────────────────────┴─────┘

2. CLÉ PRIMAIRE (Primary Key) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Identifiant UNIQUE pour chaque ligne
-> Ne peut pas être NULL
-> Immuable (ne change jamais)
-> Généralement un entier auto-incrémenté

Dans l'exemple ci-dessus : "id" est la clé primaire

RÈGLES :
[OK] Une seule clé primaire par table
[OK] Peut être composée (plusieurs colonnes)
[OK] Doit être unique
[OK] Pas de NULL

3. CLÉ ÉTRANGÈRE (Foreign Key) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Référence une clé primaire d'une autre table
-> Établit une RELATION entre tables
-> Garantit l'intégrité référentielle

Exemple :
TABLE commandes
┌────┬──────────────┬────────────┐
│ id │ utilisateur  │   montant  │
│    │     _id      │            │
├────┼──────────────┼────────────┤
│ 1  │ 1            │ 100€       │ <- Référence utilisateur id=1 (Jean)
│ 2  │ 2            │ 150€       │ <- Référence utilisateur id=2 (Marie)
│ 3  │ 1            │ 200€       │ <- Référence utilisateur id=1 (Jean)
└────┴──────────────┴────────────┘

INTÉGRITÉ RÉFÉRENTIELLE :
-> Impossible de créer une commande avec utilisateur_id = 999 (n'existe pas)
-> Impossible de supprimer un utilisateur ayant des commandes (par défaut)

4. NORMALISATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Processus d'organisation des données
-> Éliminer la redondance
-> Minimiser les anomalies d'insertion/modification/suppression
-> Plusieurs niveaux (formes normales) : 1NF, 2NF, 3NF, BCNF, 4NF, 5NF

EXEMPLE DE DÉNORMALISATION (mauvais) :
┌────┬──────────┬──────────────────────┬────────────────┐
│ id │   nom    │        email         │  commandes     │
├────┼──────────┼──────────────────────┼────────────────┤
│ 1  │ Jean     │ jean@example.com     │ 100€, 200€     │ [X] Redondance
│ 2  │ Marie    │ marie@example.com    │ 150€           │
└────┴──────────┴──────────────────────┴────────────────┘

EXEMPLE NORMALISÉ (bon) :
TABLE utilisateurs + TABLE commandes (avec clé étrangère)

5. CONTRAINTES D'INTÉGRITÉ :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Règles appliquées par le SGBD
-> Garantissent la cohérence des données

Types de contraintes :
• PRIMARY KEY : Unicité et non-nullité
• FOREIGN KEY : Intégrité référentielle
• UNIQUE : Valeur unique (mais NULL autorisé)
• NOT NULL : Valeur obligatoire
• CHECK : Validation personnalisée (ex: age >= 18)
• DEFAULT : Valeur par défaut

Exemple :
CREATE TABLE utilisateurs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18),
    pays VARCHAR(50) DEFAULT 'France',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


POURQUOI MySQL ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

MySQL = "My" (prénom fille du co-fondateur) + "SQL"

HISTORIQUE :
-> 1995 : Créé par Michael Widenius et David Axmark (MySQL AB)
-> 2008 : Racheté par Sun Microsystems (1 milliard $)
-> 2010 : Oracle rachète Sun -> Oracle possède MySQL
-> Fork : MariaDB (2009, par Michael Widenius, 100% compatible)

VERSION ACTUELLE : MySQL 8.x (depuis 2018)

POINTS FORTS DE MySQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] Open Source (GPL) + Version commerciale
[OK] Très performant (optimisé pour lecture intensive)
[OK] Facile à installer et configurer
[OK] Écosystème riche (phpMyAdmin, MySQL Workbench, etc.)
[OK] Support communautaire énorme
[OK] ACID complet (avec InnoDB)
[OK] Réplication intégrée
[OK] Multiplateforme (Linux, Windows, macOS)
[OK] Scalabilité verticale et horizontale
[OK] Compatible avec presque tous les langages (PHP, Python, Java, C#, etc.)

UTILISATION :
-> WordPress, Drupal, Joomla (CMS)
-> Facebook, Twitter, YouTube, Netflix
-> Uber, Airbnb, Booking.com
-> GitHub, Slack
-> 70% des sites web utilisant une base de données


QUI UTILISE MySQL ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GÉANTS DU WEB :
-> Facebook : Stockage de métadonnées, messages
-> Twitter : Timeline, tweets
-> YouTube : Métadonnées vidéos
-> Netflix : Catalogue, recommandations
-> Uber : Géolocalisation temps réel
-> GitHub : Repositories, issues, pull requests
-> Slack : Messages, canaux

CMS ET E-COMMERCE :
-> WordPress : 43% du web
-> Shopify : E-commerce
-> Magento : E-commerce
-> PrestaShop : E-commerce


TABLEAU COMPARATIF : MySQL vs PostgreSQL vs SQLite
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌─────────────────────┬──────────────┬──────────────┬──────────────┐
│  Caractéristique    │    MySQL     │ PostgreSQL   │   SQLite     │
├─────────────────────┼──────────────┼──────────────┼──────────────┤
│ Type                │ Client/Serveur│Client/Serveur│ Embarqué     │
│ Licence             │ GPL + Comm.  │ PostgreSQL   │ Public Domain│
│ Performances read   │ Excellent    │ Très bon     │ Très bon     │
│ Performances write  │ Très bon     │ Excellent    │ Bon          │
│ Transactions ACID   │ Oui (InnoDB) │ Oui (natif)  │ Oui          │
│ Concurrence         │ Très bonne   │ Excellente   │ Limitée      │
│ Réplication         │ Oui (native) │ Oui (native) │ Non          │
│ JSON                │ Oui (MySQL≥5.7│Excellent JSONB│Oui (limité)│
│ Full-text search    │ Oui (InnoDB) │ Oui (avancé) │ Basique      │
│ Géospatial          │ Oui (≥5.7)   │ PostGIS (ext)│ Oui (≥3.35)  │
│ Fenêtres (Window)   │ Oui (≥8.0)   │ Oui          │ Oui (≥3.25)  │
│ CTE récursifs       │ Oui (≥8.0)   │ Oui          │ Oui (≥3.8)   │
│ Types personnalisés │ Limité       │ Excellent    │ Non          │
│ Extensibilité       │ Plugins      │ Extensions   │ Non          │
│ Complexité          │ Moyenne      │ Élevée       │ Simple       │
│ Facilité config     │ Très facile  │ Moyenne      │ Aucune       │
│ Cas d'usage         │ Web, CMS     │ Data, BI     │ Mobile, embed│
│ Taille              │ ~500 MB      │ ~300 MB      │ ~1 MB        │
└─────────────────────┴──────────────┴──────────────┴──────────────┘


MySQL vs MongoDB (Relationnel vs NoSQL) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌─────────────────────┬──────────────────────┬─────────────────────┐
│  Aspect             │       MySQL          │      MongoDB        │
├─────────────────────┼──────────────────────┼─────────────────────┤
│ Modèle              │ Relationnel (tables) │ Documents (JSON)    │
│ Schéma              │ Rigide (DDL)         │ Flexible            │
│ Langage             │ SQL                  │ MongoDB Query Lang  │
│ Normalisation       │ Obligatoire          │ Dénormalisation     │
│ Jointures           │ Natives (JOIN)       │ $lookup (limité)    │
│ Transactions        │ ACID complet         │ ACID (depuis 4.0)   │
│ Scalabilité         │ Verticale surtout    │ Horizontale native  │
│ Index               │ B-Tree, Hash, R-Tree │ B-Tree, Texte, Geo  │
│ Cohérence           │ Immédiate (CP)       │ Éventuelle (AP)     │
│ Intégrité réf.      │ Contraintes FK       │ Application         │
│ Courbe apprenti.    │ SQL universel        │ Plus douce          │
│ Cas d'usage         │ Transactionnel, ERP  │ Web évolutif, IoT   │
└─────────────────────┴──────────────────────┴─────────────────────┘

QUAND UTILISER MySQL ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] OUI SI :
-> Données structurées et stables
-> Relations complexes entre entités
-> Intégrité référentielle critique
-> Transactions ACID essentielles
-> Requêtes complexes avec jointures
-> Rapports et analytics (BI)
-> Application traditionnelle (CMS, e-commerce)

[X] NON SI :
-> Schéma très variable (utiliser MongoDB)
-> Besoin de scalabilité horizontale massive (utiliser MongoDB ou Cassandra)
-> Données non structurées (utiliser MongoDB)
-> Application embarquée simple (utiliser SQLite)
"""


# [OK] PARTIE 2 : HISTOIRE ET ÉVOLUTION DE MySQL

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    CHRONOLOGIE MySQL                                   │
└────────────────────────────────────────────────────────────────────────┘

LIGNE DU TEMPS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1995 : MySQL 1.0
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Créé par Michael "Monty" Widenius et David Axmark
-> Objectif : Base de données rapide pour le web
-> Moteur ISAM (pas de transactions)

1996 : MySQL 3.11
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Première version publique
-> Licence GPL (Open Source)
-> Très rapide mais fonctionnalités limitées

2000 : MySQL 3.23
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Support de MyISAM (remplace ISAM)
-> Full-text search
-> Réplication basique

2001 : MySQL 4.0
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Support InnoDB (transactions ACID !)
-> UNION
-> Query cache

2004 : MySQL 4.1
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Subqueries (sous-requêtes)
-> UTF-8
-> Prepared statements

2005 : MySQL 5.0
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Stored procedures (procédures stockées)
-> Triggers
-> Views (vues)
-> Cursors
-> Information_schema

2008 : Sun Microsystems rachète MySQL AB (1 milliard $)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

2009 : MariaDB fork
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Créé par Monty Widenius (créateur original de MySQL)
-> Inquiétudes sur l'avenir de MySQL sous Oracle
-> 100% compatible MySQL
-> Plus d'innovations

2010 : Oracle rachète Sun Microsystems -> MySQL devient propriété Oracle
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

2010 : MySQL 5.5
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> InnoDB devient moteur par défaut (au lieu de MyISAM)
-> Semi-synchronous replication
-> Performances améliorées (3-5x)

2013 : MySQL 5.6
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Online DDL (ALTER TABLE sans downtime)
-> GTID (Global Transaction ID) pour réplication
-> Full-text search pour InnoDB
-> Performance Schema amélioré

2015 : MySQL 5.7
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> JSON native support [BRAVO]
-> Generated columns (colonnes calculées)
-> Performances × 3
-> sys schema (monitoring)
-> Réplication multi-source

2018 : MySQL 8.0 (version actuelle majeure)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Window functions (OVER, PARTITION BY) [BRAVO]
-> CTE récursifs (WITH RECURSIVE) [BRAVO]
-> JSON enhancements (JSON_TABLE)
-> Rôles (ROLES) pour sécurité
-> Invisible indexes
-> Amélioration drastique des performances
-> Data dictionary transactionnel
-> Descending indexes
-> UTF8mb4 par défaut

2024 : MySQL 8.4 LTS (Long Term Support)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Support jusqu'en 2032
-> Améliorations continues des performances


ÉDITIONS MySQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. MySQL Community Edition (Gratuit, GPL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Totalement gratuit
-> Code source disponible
-> Toutes les fonctionnalités de base
-> Support communautaire

2. MySQL Enterprise Edition (Payant)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Support Oracle 24/7
-> MySQL Enterprise Monitor (monitoring avancé)
-> MySQL Enterprise Backup (sauvegarde hot)
-> MySQL Enterprise Security (audit, chiffrement)
-> MySQL Enterprise Firewall
-> Prix : ~5000-10000$/an selon taille

3. MySQL Cluster (Haute disponibilité)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Architecture distribuée
-> Auto-sharding
-> 99.999% uptime
-> Pas de single point of failure
"""


# [OK] PARTIE 3 : INSTALLATION ET FICHIERS CRÉÉS

"""
┌────────────────────────────────────────────────────────────────────────┐
│              INSTALLATION MySQL (LINUX/MAC/WINDOWS)                    │
└────────────────────────────────────────────────────────────────────────┘

MÉTHODES D'INSTALLATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Package manager (apt, yum, brew)
2. Téléchargement binaires officiels
3. Docker (recommandé pour développement)
4. MySQL Installer (Windows)
5. Cloud (AWS RDS, Google Cloud SQL, Azure MySQL)


INSTALLATION UBUNTU/DEBIAN :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Mettre à jour les packages
sudo apt update

# Installer MySQL Server
sudo apt install mysql-server

# Démarrer MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# Vérifier le statut
sudo systemctl status mysql

# Sécurisation (IMPORTANT !)
sudo mysql_secure_installation

Questions posées :
1. Définir un mot de passe root ? -> OUI
2. Supprimer utilisateurs anonymes ? -> OUI
3. Interdire connexion root à distance ? -> OUI (sauf si cluster)
4. Supprimer base de données test ? -> OUI
5. Recharger privilèges ? -> OUI

[ALARM_CLOCK] Durée d'installation : 2-5 minutes


INSTALLATION macOS (Homebrew) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Installer MySQL
brew install mysql

# Démarrer MySQL comme service
brew services start mysql

# Ou démarrer manuellement
mysql.server start

# Connexion
mysql -u root


INSTALLATION WINDOWS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Télécharger MySQL Installer depuis mysql.com
2. Exécuter mysql-installer-community-x.x.xx.msi
3. Choisir "Developer Default" ou "Server only"
4. Suivre l'assistant :
   -> Type d'installation : Standalone
   -> Configuration réseau : Port 3306, TCP/IP
   -> Authentification : caching_sha2_password
   -> Mot de passe root : FORT !
   -> Windows Service : MySQL80 (démarrage automatique)
5. Installer MySQL Workbench (GUI)

Répertoire par défaut :
C:\Program Files\MySQL\MySQL Server 8.0\


INSTALLATION DOCKER (Recommandé pour développement) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Télécharger et démarrer MySQL
docker run -d \
  --name mysql \
  -p 3306:3306 \
  -v mysql_data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=motdepasse123 \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppassword \
  mysql:8.0

# Se connecter au shell MySQL
docker exec -it mysql mysql -u root -p

# Voir les logs
docker logs mysql

Avantages Docker :
[OK] Installation propre (pas de pollution système)
[OK] Versions multiples possibles
[OK] Facile à détruire et recréer
[OK] Même environnement sur tous les OS


STRUCTURE DES FICHIERS MySQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ARBORESCENCE COMPLÈTE (Linux - /var/lib/mysql/) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

/var/lib/mysql/                        <- Répertoire de données (datadir)
├── auto.cnf                           <- UUID du serveur
├── binlog.000001                      <- Binary log (réplication)
├── binlog.000002
├── binlog.index                       <- Index des binary logs
├── ca-key.pem                         <- Certificat SSL (CA)
├── ca.pem
├── client-cert.pem                    <- Certificat client SSL
├── client-key.pem
├── server-cert.pem                    <- Certificat serveur SSL
├── server-key.pem
├── ib_buffer_pool                     <- InnoDB buffer pool snapshot
├── ib_logfile0                        <- InnoDB redo log (OBSOLÈTE ≥8.0.30)
├── ib_logfile1
├── ibdata1                            <- InnoDB tablespace système
├── ibtmp1                             <- InnoDB temp tablespace
├── mysql.ibd                          <- Tablespace mysql (≥8.0)
├── undo_001                           <- InnoDB undo tablespace
├── undo_002
├── #ib_16384_0.dblwr                  <- InnoDB doublewrite buffer (≥8.0.20)
├── #ib_16384_1.dblwr
├── #innodb_temp/                      <- Répertoire temporaire InnoDB
│   └── temp_1.ibt
├── #innodb_redo/                      <- InnoDB redo log (≥8.0.30)
│   ├── #ib_redo0
│   ├── #ib_redo1
│   └── #ib_redo2
├── mysql/                             <- Base système mysql
│   ├── user.ibd                       <- Table utilisateurs
│   ├── db.ibd                         <- Permissions databases
│   ├── tables_priv.ibd                <- Permissions tables
│   ├── columns_priv.ibd               <- Permissions colonnes
│   └── ...
├── performance_schema/                <- Base performance_schema
│   └── (tables mémoire uniquement)
├── sys/                               <- Base sys (vues monitoring)
│   └── (vues uniquement)
├── myapp/                             <- Base utilisateur
│   ├── users.ibd                      <- Table users
│   ├── orders.ibd                     <- Table orders
│   ├── products.ibd                   <- Table products
│   └── ...
├── mysql.sock                         <- Socket Unix (connexions locales)
└── mysqld.pid                         <- PID du processus mysqld

/etc/mysql/
├── my.cnf                             <- Configuration principale
├── conf.d/                            <- Configs additionnelles
│   └── mysql.cnf
└── mysql.conf.d/
    └── mysqld.cnf                     <- Config serveur

/var/log/mysql/
└── error.log                          <- Log des erreurs

/var/run/mysqld/
└── mysqld.sock                        <- Socket (peut être ici aussi)


DÉTAIL DE CHAQUE TYPE DE FICHIER :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. FICHIERS DE DONNÉES (.ibd) - INNODB FILE-PER-TABLE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Exemple : myapp/users.ibd

RÔLE :
-> Contient les données ET les index d'une table
-> Un fichier .ibd par table (avec innodb_file_per_table=ON, par défaut)

STRUCTURE INTERNE :
-> Pages de 16 KB (par défaut)
-> Organisées en B+Tree
-> Compression possible (ROW_FORMAT=COMPRESSED)

TAILLE TYPIQUE :
-> 10 000 lignes de 1 KB ≈ 10 MB
-> Avec index : + 20-30%

VISUALISER LA TAILLE :
SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'myapp';


2. ibdata1 - INNODB SYSTEM TABLESPACE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE CRITIQUE :
-> Data dictionary (≥8.0, avant c'était dans .frm)
-> Undo logs (historique des transactions)
-> Change buffer (optimisation insertions index)
-> Doublewrite buffer (protection corruption, <8.0.20)

TAILLE :
-> Démarre à 12 MB
-> Croît automatiquement (auto-extend)
-> NE SE RÉDUIT JAMAIS (par design !)

PROBLÈME FRÉQUENT :
-> ibdata1 peut grossir indéfiniment
-> Contient des données "zombie" de tables supprimées

SOLUTION :
-> Vider et recréer (export/import)
-> Ou utiliser innodb_file_per_table=ON (par défaut ≥5.6)


3. #innodb_redo/ - REDO LOG (WAL - Write-Ahead Log)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ANCIENS NOMS (≤8.0.29) : ib_logfile0, ib_logfile1
NOUVEAUX NOMS (≥8.0.30) : #innodb_redo/#ib_redo0, #ib_redo1, etc.

RÔLE CRUCIAL (Durabilité ACID) :
-> Enregistre TOUTES les modifications AVANT qu'elles soient écrites sur disque
-> Garantit la durabilité (le D dans ACID)
-> Permet la récupération après crash

FONCTIONNEMENT :
1. Transaction COMMIT
2. Écriture dans redo log (séquentielle, rapide)
3. Écriture dans buffer pool (mémoire)
4. Plus tard : flush sur disque (asynchrone)

EN CAS DE CRASH :
-> Au redémarrage : MySQL rejoue le redo log
-> Récupération de toutes les transactions validées
-> Rollback des transactions non validées (via undo log)

TAILLE PAR DÉFAUT : 48 MB × 2 = 96 MB

CONFIGURATION :
[mysqld]
innodb_redo_log_capacity = 512M  # MySQL ≥8.0.30

# Ou ancien paramètre
innodb_log_file_size = 256M       # MySQL <8.0.30
innodb_log_files_in_group = 2

IMPACT PERFORMANCE :
-> Trop petit : flush fréquents -> lent
-> Trop grand : récupération longue après crash


4. undo_001, undo_002 - UNDO TABLESPACE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE (MVCC - Multi-Version Concurrency Control) :
-> Stocke les ANCIENNES versions des lignes modifiées
-> Permet les lectures cohérentes sans bloquer les écritures
-> Rollback des transactions

EXEMPLE MVCC :

Transaction A commence (READ COMMITTED)
-> SELECT * FROM users WHERE id = 1;
-> Résultat : nom = "Jean", age = 30

Transaction B modifie (UPDATE)
-> UPDATE users SET age = 31 WHERE id = 1;
-> Ancienne version (age=30) sauvegardée dans undo log
-> Nouvelle version (age=31) écrite dans la table

Transaction A relit
-> SELECT * FROM users WHERE id = 1;
-> Résultat : nom = "Jean", age = 30 (ancienne version via undo)
-> Cohérence garantie !

Transaction B COMMIT
-> Ancienne version marquée pour nettoyage (purge)

PURGE :
-> Thread de purge nettoie les anciennes versions
-> Quand plus aucune transaction n'en a besoin

TAILLE :
-> 10 MB initialement
-> Croît automatiquement
-> Se réduit automatiquement (≥8.0.21)


5. binlog.000001, binlog.000002 - BINARY LOG
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE (Réplication et Point-in-Time Recovery) :
-> Enregistre TOUTES les modifications (INSERT, UPDATE, DELETE)
-> Utilisé pour la réplication master -> slave
-> Permet la restauration à un moment précis (PITR)

FORMAT :
-> STATEMENT : Enregistre les requêtes SQL
-> ROW : Enregistre les changements de lignes (par défaut, plus sûr)
-> MIXED : Hybride

ROTATION :
-> Fichiers numérotés : binlog.000001, binlog.000002, etc.
-> Rotation quand taille max atteinte (max_binlog_size=1G par défaut)
-> Ou sur commande : FLUSH BINARY LOGS;

TAILLE :
-> 1 GB par fichier par défaut
-> Peut grossir très vite (haut volume d'écritures)

PURGE AUTOMATIQUE :
[mysqld]
expire_logs_days = 7  # Ou binlog_expire_logs_seconds = 604800

DÉSACTIVER (si pas de réplication) :
[mysqld]
skip-log-bin

ATTENTION : Sans binlog, pas de réplication ni de PITR !


6. #ib_16384_0.dblwr, #ib_16384_1.dblwr - DOUBLEWRITE BUFFER
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE (Protection contre corruption) :
-> Résoudre le problème des "partial page writes"

PROBLÈME :
-> InnoDB écrit des pages de 16 KB
-> OS écrit généralement des secteurs de 512 bytes ou 4 KB
-> Si crash pendant écriture -> page corrompue !

SOLUTION DOUBLEWRITE :
1. InnoDB écrit d'abord dans doublewrite buffer (séquentiel, rapide)
2. Puis écrit dans les fichiers .ibd (positions réelles)
3. Si crash pendant (2) -> récupération depuis doublewrite buffer

LOCALISATION :
-> MySQL <8.0.20 : Dans ibdata1
-> MySQL ≥8.0.20 : Fichiers séparés (#ib_16384_*.dblwr)

DÉSACTIVATION (SSD avec protection) :
[mysqld]
innodb_doublewrite = 0

[ATTENTION] À ne faire que sur SSD avec protection matérielle !


7. mysql.sock - SOCKET UNIX
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE :
-> Communication locale (même machine)
-> Plus rapide que TCP/IP
-> Utilisé par défaut quand host=localhost

EXEMPLE :
mysql -u root -p
# Équivalent à :
mysql -u root -p -h localhost -S /var/lib/mysql/mysql.sock

POUR FORCER TCP/IP :
mysql -u root -p -h 127.0.0.1


8. mysqld.pid - PROCESS ID
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CONTENU :
-> PID (Process ID) du processus mysqld en cours

RÔLE :
-> Vérifier qu'un seul mysqld tourne
-> Scripts d'arrêt utilisent ce PID

EXEMPLE :
cat /var/lib/mysql/mysqld.pid
-> 12345

kill -SIGTERM 12345  # Arrêt gracieux


9. FICHIER DE CONFIGURATION : /etc/mysql/my.cnf
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Format : INI (sections avec [])

EXEMPLE COMPLET :

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysql]
# Configuration pour client mysql
no-auto-rehash
prompt = "\u@\h [\d]> "

[mysqld]
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# GÉNÉRAL
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
port = 3306
datadir = /var/lib/mysql
tmpdir = /tmp

# Bind à toutes les interfaces (ou seulement localhost pour sécurité)
bind-address = 0.0.0.0
# bind-address = 127.0.0.1  # Seulement local

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# CONNEXIONS
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
max_connections = 200           # Max connexions simultanées
max_connect_errors = 100000     # Éviter le blocage d'IP
wait_timeout = 28800            # 8 heures (secondes)
interactive_timeout = 28800

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# INNODB - MOTEUR DE STOCKAGE
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
default-storage-engine = InnoDB

# Buffer Pool (cache) - 70-80% de la RAM pour serveur dédié
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4    # 1 instance par GB

# Redo Log
innodb_redo_log_capacity = 512M     # MySQL ≥8.0.30
# innodb_log_file_size = 256M       # MySQL <8.0.30
# innodb_log_files_in_group = 2

# Doublewrite buffer
innodb_doublewrite = 1              # 1=ON, 0=OFF

# Flushing
innodb_flush_log_at_trx_commit = 1  # 1=ACID complet, 2=rapide mais risqué
innodb_flush_method = O_DIRECT      # Linux: évite double cache OS

# File per table
innodb_file_per_table = 1

# I/O
innodb_io_capacity = 200            # IOPS (HDD: 200, SSD: 2000+)
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Locking
innodb_lock_wait_timeout = 50       # Timeout verrou (secondes)

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# QUERY CACHE (DÉPRÉCIÉ MySQL ≥8.0)
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# query_cache_type = 0              # Désactivé par défaut ≥8.0
# query_cache_size = 0

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# BINARY LOG (Réplication & PITR)
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
log-bin = /var/log/mysql/binlog
binlog_format = ROW                 # STATEMENT, ROW, MIXED
max_binlog_size = 1G
binlog_expire_logs_seconds = 604800 # 7 jours
sync_binlog = 1                     # Durabilité max (lent)

# GTID (recommandé pour réplication)
gtid_mode = ON
enforce_gtid_consistency = ON

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# RÉPLICATION
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
server-id = 1                       # Unique par serveur
relay-log = /var/log/mysql/relay-log
relay_log_recovery = 1

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# LOGS
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
log-error = /var/log/mysql/error.log

# Slow query log (requêtes lentes)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2                 # Logguer si > 2 secondes

# General log (TOUTES les requêtes - DEBUG UNIQUEMENT)
# general_log = 0
# general_log_file = /var/log/mysql/general.log

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# SÉCURITÉ
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# SSL/TLS
# require_secure_transport = ON
# ssl-ca = /etc/mysql/certs/ca.pem
# ssl-cert = /etc/mysql/certs/server-cert.pem
# ssl-key = /etc/mysql/certs/server-key.pem

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# LIMITES
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
max_allowed_packet = 64M            # Taille max requête
max_heap_table_size = 64M
tmp_table_size = 64M

# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# PERFORMANCE SCHEMA (Monitoring)
# ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
performance_schema = ON
performance-schema-instrument = 'stage/%=ON'
performance-schema-consumer-events-stages-current = ON

[mysqldump]
quick
quote-names
max_allowed_packet = 64M

[mysql_safe]
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysqld/mysqld.pid


10. FICHIER DE LOG : /var/log/mysql/error.log
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CONTENU TYPIQUE :

2024-12-08T10:30:00.123456Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.35) starting as process 12345
2024-12-08T10:30:00.456789Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-12-08T10:30:01.234567Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-12-08T10:30:01.567890Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-12-08T10:30:01.890123Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.35'  socket: '/var/lib/mysql/mysql.sock'  port: 3306

2024-12-08T11:45:23.456789Z 123 [Note] [MY-010914] [Server] Aborted connection 123 to db: 'myapp' user: 'appuser' host: '192.168.1.100' (Got an error reading communication packets)

FORMAT :
-> Timestamp ISO 8601
-> Thread ID
-> Niveau : System, Note, Warning, Error
-> Code erreur : MY-XXXXX
-> Subsystème : Server, InnoDB, Repl, etc.
-> Message

ROTATION DES LOGS :
# Avec logrotate
/var/log/mysql/*.log {
    daily
    rotate 14
    missingok
    create 640 mysql mysql
    compress
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin && \
        /usr/bin/mysqladmin --defaults-file=/etc/mysql/my.cnf \
        flush-logs
    endscript
}


COMPARAISON AVEC PostgreSQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌──────────────────────┬─────────────────────┬─────────────────────┐
│     Composant        │     PostgreSQL      │       MySQL         │
├──────────────────────┼─────────────────────┼─────────────────────┤
│ Données tables       │ base/OID/           │ database/*.ibd      │
│ Données système      │ base/OID/           │ ibdata1, mysql.ibd  │
│ WAL/Redo Log         │ pg_wal/             │ #innodb_redo/       │
│ Undo Log             │ Dans tables         │ undo_001, undo_002  │
│ Réplication log      │ pg_wal/ (même)      │ binlog.000001       │
│ Configuration        │ postgresql.conf     │ my.cnf              │
│ Logs erreurs         │ pg_log/             │ error.log           │
│ Socket               │ .s.PGSQL.5432       │ mysql.sock          │
│ PID                  │ postmaster.pid      │ mysqld.pid          │
│ Catalogue système    │ pg_catalog          │ information_schema  │
│ Moteur stockage      │ Heap/TOAST          │ InnoDB/MyISAM       │
│ Doublewrite          │ Non (WAL suffit)    │ Oui (dblwr)         │
└──────────────────────┴─────────────────────┴─────────────────────┘
"""


# [OK] PARTIE 4 : ARCHITECTURE INTERNE DE MySQL

"""
┌────────────────────────────────────────────────────────────────────────┐
│                   ARCHITECTURE MySQL - VUE D'ENSEMBLE                  │
└────────────────────────────────────────────────────────────────────────┘

ARCHITECTURE EN COUCHES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

┌─────────────────────────────────────────────────────────────────────┐
│                       SERVEUR MySQL (mysqld)                        │
│                                                                     │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                   COUCHE CONNEXION                             │ │
│  │  - Authentification (caching_sha2_password)                    │ │
│  │  - Thread per connection                                       │ │
│  │  - Connection pooling                                          │ │
│  │  - SSL/TLS                                                     │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                   COUCHE SQL                                   │ │
│  │                                                                │ │
│  │  ┌──────────┐  ┌──────────┐  ┌───────────┐  ┌──────────┐    │ │
│  │  │  Parser  │-> │Optimizer │-> │ Executor  │-> │  Cache   │    │ │
│  │  │  (Lexer/ │  │  (Cost-  │  │ (Execute  │  │ (Query   │    │ │
│  │  │   Yacc)  │  │   Based) │  │   Plan)   │  │  Cache)  │    │ │
│  │  └──────────┘  └──────────┘  └───────────┘  └──────────┘    │ │
│  │                                                                │ │
│  │  ┌─────────────────────────────────────────────────────────┐ │ │
│  │  │  Privilege System (GRANT, REVOKE)                       │ │ │
│  │  └─────────────────────────────────────────────────────────┘ │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │              COUCHE MOTEUR DE STOCKAGE (Pluggable)            │ │
│  │                                                                │ │
│  │  ┌──────────────────┐  ┌──────────────────┐  ┌────────────┐ │ │
│  │  │     InnoDB       │  │     MyISAM       │  │   MEMORY   │ │ │
│  │  │  (Transactionnel)│  │ (Non-trans.)     │  │  (Heap)    │ │ │
│  │  │  - ACID          │  │  - Rapide lecture│  │  - RAM     │ │ │
│  │  │  - Row locking   │  │  - Table locking │  │  - Temp    │ │ │
│  │  │  - MVCC          │  │  - Full-text     │  │            │ │ │
│  │  │  - Foreign keys  │  │  - Compression   │  │            │ │ │
│  │  └──────────────────┘  └──────────────────┘  └────────────┘ │ │
│  │                                                                │ │
│  │  ┌──────────────────┐  ┌──────────────────┐  ┌────────────┐ │ │
│  │  │     CSV          │  │     ARCHIVE      │  │   FEDERATED│ │ │
│  │  │  (Fichiers CSV)  │  │  (Compression)   │  │  (Remote)  │ │ │
│  │  └──────────────────┘  └──────────────────┘  └────────────┘ │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                   FICHIERS SUR DISQUE                          │ │
│  │  - Tables (.ibd)                                               │ │
│  │  - Index (dans .ibd)                                           │ │
│  │  - Redo Log (#innodb_redo/)                                   │ │
│  │  - Undo Log (undo_001, undo_002)                              │ │
│  │  - Binary Log (binlog.000001)                                 │ │
│  └────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘


DÉTAIL DES COUCHES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. COUCHE CONNEXION (Connection Layer)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE :
-> Gérer les connexions clients
-> Authentification
-> Thread management
-> Gestion SSL/TLS

PROTOCOLE :
-> Protocole MySQL (propriétaire, binaire)
-> Port par défaut : 3306
-> Socket Unix pour connexions locales

CONNEXION TYPIQUE :
1. Client ouvre connexion TCP vers port 3306
2. Handshake MySQL (échange de versions, capabilities)
3. Authentification :
   - Ancienne méthode : mysql_native_password (hash SHA1)
   - Nouvelle (≥8.0) : caching_sha2_password (hash SHA2-256)
   - Support x.509 (certificats SSL)
   - Support LDAP, Kerberos (Enterprise)
4. Connexion établie

MODÈLE THREAD-PER-CONNECTION :
-> Chaque connexion = 1 thread dédié
-> Thread pool disponible (Enterprise/Percona)

EXEMPLE (mysql client) :
mysql -h localhost -u root -p
-> Connexion via socket Unix

mysql -h 192.168.1.100 -u appuser -p myapp
-> Connexion TCP/IP

VOIR LES CONNEXIONS :
SHOW PROCESSLIST;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  5 | root | localhost | NULL | Query   |    0 | init  | show processlist |
| 10 | app  | 10.0.0.1  | mydb | Sleep   |  120 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+

TUER UNE CONNEXION :
KILL 10;


2. COUCHE SQL (SQL Layer)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

C'est le CERVEAU de MySQL !

A) PARSER (Analyseur syntaxique)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE :
-> Analyser la requête SQL
-> Vérifier la syntaxe
-> Construire un arbre de syntaxe abstraite (AST)

ÉTAPES :
1. Lexical Analysis (Tokenisation)
   SELECT id, nom FROM users WHERE age > 18;
   ->
   [SELECT] [id] [,] [nom] [FROM] [users] [WHERE] [age] [>] [18] [;]

2. Syntax Analysis (Parsing)
   -> Vérifier que la grammaire SQL est respectée
   -> Construction de l'AST (Abstract Syntax Tree)

EXEMPLE AST (simplifié) :
SELECT
├── Columns
│   ├── id
│   └── nom
├── FROM
│   └── Table: users
└── WHERE
    └── Condition: age > 18

3. Vérification sémantique
   -> La table "users" existe-t-elle ?
   -> La colonne "age" existe-t-elle ?
   -> L'utilisateur a-t-il les droits SELECT sur cette table ?

EN CAS D'ERREUR :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '...' at line 1


B) OPTIMIZER (Optimiseur de requêtes)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE CRUCIAL :
-> Transformer la requête en PLAN D'EXÉCUTION optimal
-> Choisir les index à utiliser
-> Ordre des jointures
-> Algorithme de jointure

TYPES D'OPTIMISATIONS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Optimisation algébrique (rule-based)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Simplification de l'AST
-> Constant folding : WHERE 1+1 = 2 -> WHERE TRUE
-> Predicate pushdown : descendre les WHERE avant les JOIN

2. Optimisation basée sur les coûts (cost-based)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Estimer le coût de chaque plan possible
-> Choisir le plan avec le coût le plus faible

FACTEURS DE COÛT :
-> Nombre de lignes (cardinalité)
-> Sélectivité des index
-> Taille des tables
-> Coût I/O disque vs mémoire
-> Coût CPU

EXEMPLE :

Requête :
SELECT u.nom, c.montant
FROM users u
JOIN commandes c ON u.id = c.user_id
WHERE u.age > 25 AND c.status = 'completed';

PLANS POSSIBLES :

PLAN A : Table scan users + Table scan commandes + Join
-> Coût : 1000 (lecture 10k lignes users) + 5000 (50k commandes) + 500M (join)
-> Total : Très élevé [X]

PLAN B : Index scan users(age) + Index scan commandes(status) + Join
-> Coût : 100 (lecture 1k lignes via index) + 500 (5k commandes) + 5M (join)
-> Total : Moyen

PLAN C : Index scan users(age) + Nested loop join avec index commandes(user_id)
-> Coût : 100 + 1000 × log(50000) ≈ 16000
-> Total : Optimal [OK]

L'optimiseur choisit PLAN C !

STATISTIQUES :
-> MySQL collecte des statistiques sur les tables (ANALYZE TABLE)
-> Nombre de lignes, distribution des valeurs, cardinalité des index
-> Utilisé pour estimer les coûts

VOIR LE PLAN :
EXPLAIN SELECT u.nom, c.montant
FROM users u
JOIN commandes c ON u.id = c.user_id
WHERE u.age > 25;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | u     | ref  | age_idx       | age  | 4       | NULL | 1000 | Using where; Using index    |
|  1 | SIMPLE      | c     | ref  | user_id_idx   | user | 4       | u.id | 5    | Using where                 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

COLONNES IMPORTANTES :
-> type : ALL(scan complet), index, range, ref, eq_ref, const
-> possible_keys : Index candidats
-> key : Index réellement utilisé
-> rows : Estimation nombre de lignes examinées
-> Extra : Infos additionnelles (Using temporary, Using filesort, etc.)


C) EXECUTOR (Exécuteur)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE :
-> Exécuter le plan d'exécution
-> Appeler le moteur de stockage (InnoDB, MyISAM, etc.)
-> Gérer les verrous
-> Retourner les résultats au client

ÉTAPES :
1. Ouvrir les tables (via moteur de stockage)
2. Acquérir les verrous nécessaires
3. Lire les données (via API du moteur)
4. Appliquer les filtres WHERE
5. Effectuer les jointures
6. Trier si ORDER BY
7. Limiter si LIMIT
8. Retourner les résultats

ALGORITHMES DE JOINTURE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Nested Loop Join
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
FOR each row in table1:
    FOR each row in table2:
        IF join_condition:
            return row

Coût : O(n × m)
-> Bon si table2 a un index sur join_column

2. Block Nested Loop Join (BNL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Charger table1 en mémoire (join buffer)
-> Scanner table2 une seule fois
-> Comparer en mémoire

3. Hash Join (MySQL ≥8.0.18)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Construire une table de hachage de table1
-> Sonder table2 avec la table de hachage

Coût : O(n + m)
-> Très efficace pour grandes tables


D) QUERY CACHE (Cache de requêtes) - DÉPRÉCIÉ ≥8.0
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ANCIEN FONCTIONNEMENT (≤5.7) :
-> Cache les résultats complets des SELECT
-> Invalidé à chaque UPDATE/INSERT/DELETE sur les tables concernées

POURQUOI DÉPRÉCIÉ ?
[X] Verrou global (contention)
[X] Invalidation trop fréquente (inutile pour écritures fréquentes)
[X] Alternative : cache applicatif (Redis, Memcached)

DÉSACTIVÉ PAR DÉFAUT depuis MySQL 8.0 !


E) PRIVILEGE SYSTEM (Système de privilèges)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÔLE :
-> Vérifier les droits de l'utilisateur
-> À chaque opération (SELECT, INSERT, UPDATE, DELETE, etc.)

NIVEAUX DE PRIVILÈGES :
1. GLOBAL : Sur tout le serveur
2. DATABASE : Sur une base spécifique
3. TABLE : Sur une table spécifique
4. COLUMN : Sur une colonne spécifique
5. ROUTINE : Sur une procédure/fonction stockée

STOCKAGE :
-> Table mysql.user (privilèges globaux)
-> Table mysql.db (privilèges base)
-> Table mysql.tables_priv (privilèges table)
-> Table mysql.columns_priv (privilèges colonne)

EXEMPLE :
GRANT SELECT, INSERT ON myapp.users TO 'appuser'@'localhost';


3. COUCHE MOTEUR DE STOCKAGE (Storage Engine Layer)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ARCHITECTURE PLUGGABLE :
-> MySQL sépare la couche SQL de la couche stockage
-> Permet de changer de moteur sans modifier le code SQL

API UNIFORME :
-> Open table, Close table
-> Read row, Write row
-> Start transaction, Commit, Rollback
-> Create index, Drop index

VOIR LES MOTEURS DISPONIBLES :
SHOW ENGINES;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write disappears)       | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+


COMPARAISON InnoDB vs MyISAM :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌─────────────────────┬──────────────────────┬─────────────────────┐
│  Caractéristique    │       InnoDB         │      MyISAM         │
├─────────────────────┼──────────────────────┼─────────────────────┤
│ Transactions ACID   │ OUI [OK]               │ NON [X]              │
│ Verrous             │ Row-level (ligne)    │ Table-level (table) │
│ Clés étrangères     │ OUI [OK]               │ NON [X]              │
│ Crash recovery      │ OUI [OK]               │ NON [X]              │
│ MVCC                │ OUI [OK]               │ NON [X]              │
│ Full-text search    │ OUI (≥5.6)           │ OUI                 │
│ Compression         │ OUI (InnoDB)         │ OUI (MyISAM)        │
│ Geospatial          │ OUI                  │ OUI                 │
│ Performance lecture │ Très bon             │ Excellent           │
│ Performance écriture│ Très bon             │ Moyen (table lock)  │
│ Utilisation mémoire │ Plus (buffer pool)   │ Moins               │
│ Cas d'usage         │ Défaut (tout)        │ Logs, archives      │
│ Statut              │ Défaut depuis 5.5    │ Legacy (éviter)     │
└─────────────────────┴──────────────────────┴─────────────────────┘

RECOMMANDATION : TOUJOURS UTILISER InnoDB !
"""


# [OK] PARTIE 5 : VOYAGE COMPLET D'UNE REQUÊTE (Du Client au Disque)

"""
┌────────────────────────────────────────────────────────────────────────┐
│     SCÉNARIO : SELECT + INSERT DEPUIS DAKAR VERS SERVEUR LONDRES       │
└────────────────────────────────────────────────────────────────────────┘

CONTEXTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Tu es à DAKAR (Sénégal)
Serveur MySQL à LONDRES (AWS eu-west-2)
Distance : ~4 500 km
Latence réseau : ~150-200 ms (aller-retour)

Serveur MySQL (Londres) :
-> IP : 51.124.45.67:3306
-> Base : ecommerce
-> Table : products
-> Moteur : InnoDB


ÉTAPE 0 : CONFIGURATION ET CONNEXION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CODE DE CONNEXION (Python) :
import mysql.connector

config = {
    'host': '51.124.45.67',
    'port': 3306,
    'user': 'appuser',
    'password': 'SecurePassword123!',
    'database': 'ecommerce',
    'charset': 'utf8mb4',
    'use_unicode': True,
    'connection_timeout': 10,
    'autocommit': False
}


ÉTAPE 1 : ÉTABLISSEMENT DE LA CONNEXION (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

conn = mysql.connector.connect(**config)

1. RÉSOLUTION DNS (Dakar, local)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Résoudre "51.124.45.67" (déjà une IP, pas de DNS)
[TEMPS] Temps : 0 ms

Si c'était un nom de domaine :
-> Requête DNS vers serveur DNS local
-> Récursion éventuelle
[TEMPS] Temps : 20-50 ms

2. HANDSHAKE TCP (Dakar -> Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
a) SYN (Dakar -> Londres) : [TEMPS] +100 ms
b) SYN-ACK (Londres -> Dakar) : [TEMPS] +100 ms
c) ACK (Dakar -> Londres) : [TEMPS] +100 ms

Total TCP handshake : ~300 ms

3. HANDSHAKE MySQL (Protocole MySQL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Serveur envoie Initial Handshake Packet (Londres -> Dakar) :
   {
     protocol_version: 10,
     server_version: "8.0.35-MySQL",
     thread_id: 12345,
     auth_plugin_data_part_1: [random 8 bytes],  # Salt
     capabilities_flags: 0xF7FF,
     character_set: 255,  # utf8mb4
     status_flags: 2,
     auth_plugin_name: "caching_sha2_password",
     auth_plugin_data_part_2: [random 12 bytes]  # Suite du salt
   }
   [TEMPS] +100 ms

b) Client envoie Handshake Response (Dakar -> Londres) :
   {
     capabilities: 0xF7FF,
     max_packet_size: 16777216,  # 16 MB
     charset: 255,  # utf8mb4
     username: "appuser",
     auth_response: SHA2(password, salt),  # Hash du mot de passe
     database: "ecommerce",
     auth_plugin_name: "caching_sha2_password"
   }
   [TEMPS] +100 ms

c) Serveur vérifie les credentials (Londres, local)
   -> Hash stocké : SELECT authentication_string 
                   FROM mysql.user 
                   WHERE user='appuser' AND host='%';
   -> Comparaison : hash_client == hash_stocké ?
   [TEMPS] ~5 ms (requête interne + calcul)

d) Si AUTH OK : Serveur envoie OK_Packet (Londres -> Dakar)
   {
     header: 0x00,  # OK
     affected_rows: 0,
     last_insert_id: 0,
     status_flags: 0x0002,  # AUTOCOMMIT
     warnings: 0
   }
   [TEMPS] +100 ms

   Si AUTH KO : ERR_Packet
   {
     header: 0xFF,  # ERROR
     error_code: 1045,  # ER_ACCESS_DENIED_ERROR
     sql_state: "28000",
     error_message: "Access denied for user 'appuser'@'...' (using password: YES)"
   }

e) Connexion établie [OK]
   -> Thread MySQL assigné (thread_id: 12345)
   -> État initial : AUTOCOMMIT=1, TRANSACTION_ISOLATION=REPEATABLE-READ

TOTAL CONNEXION : ~700 ms


ÉTAPE 2 : INSERT D'UNE LIGNE (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CODE (Dakar) :
cursor = conn.cursor()

sql = """
INSERT INTO products (name, category, price, stock, description)
VALUES (%s, %s, %s, %s, %s)
"""

data = ('Djembé Artisanal', 'Instruments', 45000.00, 15, 
        'Djembé fait main en bois de Lenke')

cursor.execute(sql, data)
conn.commit()

PROCESSUS DÉTAILLÉ :

1. PRÉPARATION CLIENT (Dakar, local)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Construction de la requête finale (driver)
   -> Remplacement des placeholders %s
   -> Échappement des chaînes (prévention SQL injection)
   
   INSERT INTO products (name, category, price, stock, description)
   VALUES ('Djembé Artisanal', 'Instruments', 45000.00, 15, 
           'Djembé fait main en bois de Lenke')
   
   [TEMPS] <1 ms

b) Sérialisation du paquet MySQL
   -> COM_QUERY packet (type 0x03)
   -> Taille : ~200 bytes
   [TEMPS] <1 ms

2. ENVOI DU PAQUET (Dakar -> Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[TEMPS] +100 ms (latence réseau)

3. RÉCEPTION PAR LE SERVEUR (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Lecture du socket réseau
   -> Thread 12345 lit le paquet
   [TEMPS] <1 ms

b) Désérialisation
   -> Extraction de la commande : COM_QUERY
   -> Extraction du SQL : INSERT INTO ...
   [TEMPS] <1 ms

4. PARSING (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Lexical analysis (tokenisation)
   [INSERT] [INTO] [products] [(] [name] [,] [category] ... [)]
   [TEMPS] ~1 ms

b) Syntax analysis
   -> Construction de l'AST (Abstract Syntax Tree)
   
   INSERT
   ├── Table: products
   ├── Columns: [name, category, price, stock, description]
   └── Values: ['Djembé...', 'Instruments', 45000.00, 15, '...']
   
   [TEMPS] ~1 ms

c) Vérifications sémantiques
   -> Table "products" existe ? [OK]
   -> Colonnes existent ? [OK]
   -> Types compatibles ? [OK]
   -> Privilèges INSERT ? 
     SELECT Insert_priv FROM mysql.db WHERE user='appuser' AND db='ecommerce'
     -> OUI [OK]
   
   [TEMPS] ~2 ms

5. OPTIMISATION (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Pour un INSERT simple, pas d'optimisation complexe
-> Plan direct : Insertion dans la table

[TEMPS] ~1 ms

6. EXÉCUTION - Phase 1 : PRÉPARATION (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Ouverture de la table (InnoDB)
   -> Chargement de la définition depuis Data Dictionary
   -> Vérification du schéma
   [TEMPS] ~1 ms (si en cache)

b) Génération de l'AUTO_INCREMENT (si id est AUTO_INCREMENT)
   -> Incrémenter le compteur AUTO_INCREMENT
   -> Nouvelle valeur : 12345
   [TEMPS] <1 ms

c) Validation des contraintes (AVANT insertion)
   
   -> PRIMARY KEY : id = 12345 est unique ?
     -> Recherche dans l'index PRIMARY
     [TEMPS] ~1 ms (index en cache)
   
   -> UNIQUE constraints : Vérifier autres index uniques
     -> Ex: Si index unique sur (name, category)
     [TEMPS] ~1 ms par contrainte
   
   -> NOT NULL : Tous les champs NOT NULL sont remplis ? [OK]
   
   -> CHECK constraints : Vérifier conditions
     -> Ex: CHECK (price >= 0) [OK]
     -> Ex: CHECK (stock >= 0) [OK]
   
   -> FOREIGN KEY : Si references vers autres tables
     -> Ex: category_id REFERENCES categories(id)
     -> Vérifier que la clé existe
     [TEMPS] ~2 ms (requête vers table liée)
   
   Total validations : ~5-10 ms

d) Acquisition des verrous
   -> Row-level lock sur la ligne à insérer (intention)
   -> En mode AUTOCOMMIT : transaction implicite démarre
   [TEMPS] <1 ms

7. EXÉCUTION - Phase 2 : ÉCRITURE REDO LOG (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

AVANT d'écrire dans la table, écrire dans le REDO LOG (WAL) !

a) Génération de l'entrée redo log
   {
     LSN (Log Sequence Number): 123456789,
     space_id: 25,  # Tablespace products
     page_no: 1024,  # Page où insérer
     type: INSERT_ROW,
     data: [12345, 'Djembé Artisanal', 'Instruments', 45000.00, 15, ...]
   }
   [TEMPS] <1 ms

b) Écriture dans le redo log buffer (RAM)
   -> Buffer circulaire en mémoire
   [TEMPS] <1 ms

c) Flush du redo log sur disque (selon innodb_flush_log_at_trx_commit)
   
   innodb_flush_log_at_trx_commit = 1 (par défaut, ACID complet) :
   -> fsync() immédiat du redo log sur disque
   -> Garantit durabilité même si crash juste après
   [TEMPS] ~5-20 ms (selon disque : HDD=10-20ms, SSD=1-5ms)
   
   innodb_flush_log_at_trx_commit = 2 (moins sûr, plus rapide) :
   -> Écriture dans le cache OS, pas de fsync immédiat
   -> Flush toutes les secondes
   [TEMPS] <1 ms (mais perte possible si crash OS)
   
   Supposons SSD : [TEMPS] ~3 ms

8. EXÉCUTION - Phase 3 : ÉCRITURE DANS LE BUFFER POOL (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Localiser la page appropriée dans le buffer pool
   -> Recherche space_id=25, page_no=1024
   -> Si en cache : [OK]
   -> Si pas en cache : charger depuis disque (read I/O)
   
   Supposons en cache : [TEMPS] <1 ms
   Si pas en cache : [TEMPS] ~5-10 ms (lecture SSD)

b) Insertion de la ligne dans la page (B+Tree)
   -> Trouver la position appropriée (ordre par PRIMARY KEY)
   -> Insérer les données
   -> Marquer la page comme "dirty" (modifiée)
   [TEMPS] ~1 ms

c) Mise à jour des index secondaires
   
   Supposons 2 index secondaires :
   - INDEX idx_category (category)
   - INDEX idx_price (price)
   
   Pour chaque index :
   -> Localiser la page de l'index dans le buffer pool
   -> Insérer l'entrée [valeur_index -> PRIMARY_KEY]
     Ex: ['Instruments' -> 12345] dans idx_category
     Ex: [45000.00 -> 12345] dans idx_price
   -> Marquer les pages comme dirty
   
   [TEMPS] ~2 ms par index = 4 ms total

d) PAS d'écriture disque immédiate !
   -> Les pages dirty restent en mémoire (buffer pool)
   -> Seront écrites plus tard par le thread "page cleaner"
   -> Ou au prochain checkpoint

Total écriture buffer pool : ~6 ms

9. EXÉCUTION - Phase 4 : UNDO LOG (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Créer une entrée UNDO pour permettre le ROLLBACK

a) Génération de l'entrée undo
   {
     type: INSERT_UNDO,
     table: products,
     primary_key: 12345,
     operation: DELETE  # L'inverse d'INSERT = DELETE
   }
   [TEMPS] <1 ms

b) Écriture dans l'undo tablespace
   -> Écriture dans undo_001 ou undo_002
   -> En mémoire d'abord (buffer pool)
   [TEMPS] <1 ms

Total undo log : ~1 ms

10. EXÉCUTION - Phase 5 : BINARY LOG (Londres, si activé)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Pour la réplication Master -> Slave

a) Génération de l'événement binlog
   
   Format ROW (par défaut) :
   {
     timestamp: 1733654400,
     server_id: 1,
     event_type: WRITE_ROWS_EVENT,
     database: ecommerce,
     table: products,
     row_data: [12345, 'Djembé Artisanal', 'Instruments', 45000.00, 15, ...]
   }
   
   Format STATEMENT (ancien) :
   {
     timestamp: 1733654400,
     server_id: 1,
     event_type: QUERY_EVENT,
     database: ecommerce,
     query: "INSERT INTO products ..."
   }
   
   [TEMPS] ~1 ms

b) Écriture dans le binlog buffer (RAM)
   [TEMPS] <1 ms

c) Flush du binlog sur disque (selon sync_binlog)
   
   sync_binlog = 1 (ACID, par défaut ≥8.0) :
   -> fsync() après chaque transaction
   [TEMPS] ~3-5 ms (SSD)
   
   sync_binlog = 0 (rapide mais risqué) :
   -> OS gère le flush
   [TEMPS] <1 ms
   
   Supposons sync_binlog=1 : [TEMPS] ~4 ms

Total binlog : ~5 ms

11. COMMIT DE LA TRANSACTION (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

En mode AUTOCOMMIT, le COMMIT est automatique

a) Écriture finale du redo log (si pas déjà fait)
   [TEMPS] 0 ms (déjà fait en phase 7)

b) Marquage de la transaction comme validée
   -> LSN de commit enregistré
   [TEMPS] <1 ms

c) Libération des verrous
   -> Row locks relâchés
   [TEMPS] <1 ms

d) Nettoyage de l'undo log (pas immédiat)
   -> Thread "purge" s'en occupe en arrière-plan

Total commit : ~1 ms

12. RÉPONSE AU CLIENT (Londres -> Dakar)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Construction du paquet OK
   {
     header: 0x00,  # OK
     affected_rows: 1,
     last_insert_id: 12345,  # AUTO_INCREMENT généré
     status_flags: 0x0002,  # AUTOCOMMIT
     warnings: 0,
     info: ""
   }
   [TEMPS] <1 ms

b) Sérialisation et envoi (Londres -> Dakar)
   [TEMPS] +100 ms (latence réseau)

13. RÉCEPTION PAR LE CLIENT (Dakar)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Désérialisation du paquet OK
   [TEMPS] <1 ms

b) Mise à jour du driver
   -> cursor.lastrowid = 12345
   -> cursor.rowcount = 1
   [TEMPS] <1 ms


RÉCAPITULATIF TEMPS INSERT :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Préparation client :             2 ms
Envoi (Dakar -> Londres) :      100 ms
Parsing :                         4 ms
Optimisation :                    1 ms
Préparation exécution :          10 ms
Redo log (fsync) :                3 ms
Buffer pool (insertion) :         6 ms
Undo log :                        1 ms
Binary log (fsync) :              4 ms
Commit :                          1 ms
Réponse (Londres -> Dakar) :     100 ms
Réception client :                2 ms
─────────────────────────────────────
TOTAL :                         ~234 ms

SANS fsync (innodb_flush_log_at_trx_commit=2, sync_binlog=0) :
-> Retirer ~7 ms de fsync
-> Total : ~227 ms

MAIS : Risque de perte de données si crash ! [ATTENTION]


ÉTAPE 3 : SELECT D'UNE LIGNE (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CODE (Dakar) :
sql = "SELECT * FROM products WHERE id = %s"
cursor.execute(sql, (12345,))
row = cursor.fetchone()

PROCESSUS DÉTAILLÉ :

1. PRÉPARATION CLIENT (Dakar, local)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Construction requête : SELECT * FROM products WHERE id = 12345
-> Sérialisation paquet COM_QUERY
[TEMPS] ~1 ms

2. ENVOI (Dakar -> Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[TEMPS] +100 ms

3. PARSING (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

AST :
SELECT
├── Columns: * (toutes)
├── FROM: products
└── WHERE: id = 12345

[TEMPS] ~2 ms

4. OPTIMISATION (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Vérification du cache de plans
   -> Hash de la requête : SHA256("SELECT * FROM products WHERE id = ?")
   -> Plan en cache ? [OK] (requête fréquente)
   [TEMPS] <1 ms

b) Si plan pas en cache, génération :
   
   Plans possibles :
   
   PLAN A : Full table scan
   -> Scanner TOUTES les lignes de la table
   -> Coût : 10000 (si 10000 lignes)
   
   PLAN B : Index scan sur PRIMARY KEY (id)
   -> Recherche directe via B+Tree
   -> Coût : 3 (log2(10000) ≈ 13 comparaisons)
   
   Choix : PLAN B [OK]
   
   [TEMPS] ~3 ms (si pas en cache)

Total optimisation : ~1 ms (cache hit)

5. EXÉCUTION (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Ouverture de la table
   -> Chargement définition (si pas en cache)
   [TEMPS] <1 ms

b) Acquisition de verrous (lecture)
   -> Shared lock (S) sur la ligne id=12345
   -> En mode REPEATABLE READ : snapshot read (MVCC)
   -> Pas de blocage des écritures !
   [TEMPS] <1 ms

c) Recherche dans l'index PRIMARY KEY
   
   STRUCTURE B+TREE (simplifié) :
   
   Root Node (Page 3)
   ├─ [1 ... 5000] -> Page 1001
   └─ [5001 ... 10000] -> Page 1002
   
   Page 1002 (Non-leaf node)
   ├─ [5001 ... 7500] -> Page 2001
   └─ [7501 ... 10000] -> Page 2002
   
   Page 2002 (Non-leaf node)
   ├─ [7501 ... 10000] -> Page 3001
   └─ [10001 ... 12500] -> Page 3002
   
   Page 3002 (Leaf node - Données réelles)
   ├─ Row: id=10001, name="...", ...
   ├─ Row: id=10500, name="...", ...
   ├─ Row: id=12345, name="Djembé...", ... <- TROUVÉ !
   └─ Row: id=12500, name="...", ...
   
   Étapes :
   1. Lire Root Node (Page 3)
      -> 12345 est dans [5001 ... 10000] -> Aller à Page 1002
      [TEMPS] <1 ms (en cache)
   
   2. Lire Page 1002
      -> 12345 est dans [10001 ... 12500] -> Aller à Page 3002
      [TEMPS] <1 ms (en cache)
   
   3. Lire Page 3002 (Leaf)
      -> Recherche binaire dans la page : 12345 trouvé !
      [TEMPS] <1 ms
   
   Total recherche : ~3 ms (cache hit)
   
   Si cache miss (lecture disque) :
   -> 3 lectures × 5 ms (SSD) = 15 ms

d) Vérification MVCC (snapshot read)
   -> La ligne est-elle visible pour ma transaction ?
   -> Vérifier le trx_id (transaction ID) de la ligne
   -> Vérifier dans l'undo log si besoin de version antérieure
   
   Si ligne récente : [OK] Visible
   [TEMPS] <1 ms

e) Extraction des données
   -> Lire toutes les colonnes (SELECT *)
   -> Conversion en format réseau
   [TEMPS] ~1 ms

Total exécution : ~6 ms (cache hit)

6. CONSTRUCTION DE LA RÉPONSE (Londres)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Paquet Result Set Header
   {
     column_count: 6  # Nombre de colonnes
   }

b) Paquets Column Definition (un par colonne)
   {
     catalog: "def",
     schema: "ecommerce",
     table: "products",
     org_table: "products",
     name: "id",
     org_name: "id",
     charset: 63,  # binary
     column_length: 11,
     column_type: 3,  # LONG (INT)
     flags: 0x4003,  # NOT_NULL, PRI_KEY, AUTO_INCREMENT
     decimals: 0
   }
   ... × 6 colonnes

c) Paquet Row Data
   {
     id: 12345,
     name: "Djembé Artisanal",
     category: "Instruments",
     price: 45000.00,
     stock: 15,
     description: "Djembé fait main..."
   }

d) Paquet EOF (End Of File)
   {
     header: 0xFE,
     warnings: 0,
     status_flags: 0x0002
   }

Total : ~7 paquets, ~500 bytes
[TEMPS] ~2 ms

7. ENVOI (Londres -> Dakar)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[TEMPS] +100 ms

8. RÉCEPTION CLIENT (Dakar)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

a) Désérialisation des paquets
   -> Parsing Result Set
   -> Construction de l'objet Row
   [TEMPS] ~2 ms

b) Retour au programme Python
   row = (12345, 'Djembé Artisanal', 'Instruments', 45000.00, 15, '...')


RÉCAPITULATIF TEMPS SELECT :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Préparation client :            1 ms
Envoi (Dakar -> Londres) :     100 ms
Parsing :                       2 ms
Optimisation (cache hit) :      1 ms
Exécution (cache hit) :         6 ms
Construction réponse :          2 ms
Réponse (Londres -> Dakar) :   100 ms
Réception client :              2 ms
─────────────────────────────────────
TOTAL (cache hit) :           ~214 ms

TOTAL (cache miss - lecture disque) :
-> +12 ms (3 pages × 4 ms supplémentaires)
-> Total : ~226 ms


ÉTAPE 4 : SELECT COMPLEXE AVEC JOIN (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CODE (Dakar) :
sql = """
SELECT p.name, p.price, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price BETWEEN 10000 AND 50000
  AND p.stock > 0
ORDER BY p.price ASC
LIMIT 10
"""

cursor.execute(sql)
rows = cursor.fetchall()

SUPPOSONS :
-> Table products : 10 000 lignes
-> Table categories : 50 lignes
-> Index sur products(category_id)
-> Index sur products(price)
-> Index sur products(stock)

PROCESSUS D'OPTIMISATION (Londres) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PLANS POSSIBLES :

PLAN A : Full table scan + filter + join + sort
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Scanner products (10000 lignes)
2. Filtrer price BETWEEN et stock > 0 -> ~2000 lignes
3. Join avec categories (nested loop)
4. Trier par price
5. Limiter à 10

Coût estimé : 10000 + 2000×50 + 2000×log(2000) ≈ 135000

PLAN B : Index range scan sur price + filter + join + sort
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Scan index price [10000, 50000] -> ~2000 entrées
2. Fetch rows depuis table
3. Filtrer stock > 0 -> ~1500 lignes
4. Join avec categories
5. Trier par price (déjà trié par index !)
6. Limiter à 10

Coût estimé : 2000 + 2000 + 1500×50 ≈ 79000

PLAN C : Index range scan + filter stock + join + no sort needed
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
(Même que B mais en exploitant que l'index price est déjà trié)

Coût estimé : 2000 + 2000 + 1500×50 ≈ 79000
Mais : PAS de sort nécessaire car index déjà ordonné -> -10000

Coût ajusté : 69000 [OK] OPTIMAL

L'OPTIMISEUR CHOISIT PLAN C !

[TEMPS] Optimisation : ~10 ms

EXÉCUTION (Londres) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Index Range Scan sur products(price)
   -> Parcourir l'index B+Tree pour [10000, 50000]
   -> Retourne 2000 pointeurs vers lignes
   [TEMPS] ~50 ms (assume cache hit partiel)

2. Fetch des lignes depuis products
   -> Pour chaque pointeur, lire la ligne complète
   -> 2000 lectures
   [TEMPS] ~100 ms (si 50% en cache)

3. Filtrer stock > 0
   -> En mémoire
   -> 2000 -> 1500 lignes
   [TEMPS] ~5 ms

4. Join avec categories (Nested Loop + Index)
   -> Pour chaque produit (1500) :
     - Lire categories.id via index PRIMARY
     - Fetch category_name
   -> Avec index : O(1500 × log(50)) ≈ 8500
   [TEMPS] ~40 ms (categories probablement 100% en cache)

5. PAS de tri (index déjà ordonné par price)

6. LIMIT 10
   -> Prendre les 10 premiers
   [TEMPS] <1 ms

Total exécution : ~195 ms

TOTAL SELECT COMPLEXE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Envoi (Dakar -> Londres) :     100 ms
Parsing :                        5 ms
Optimisation :                  10 ms
Exécution :                    195 ms
Construction réponse :           5 ms
Réponse (Londres -> Dakar) :    100 ms
Réception :                      5 ms
─────────────────────────────────────
TOTAL :                        ~420 ms


IMPORTANCE DES INDEX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SANS INDEX (Plan A - Full table scan) :
-> Exécution : ~1500 ms
-> Total : ~1720 ms

AVEC INDEX (Plan C - Index range scan) :
-> Exécution : ~195 ms
-> Total : ~420 ms

GAIN : 4x plus rapide ! [RAPIDE]
"""


# [OK] PARTIE 6 : MOTEURS DE STOCKAGE - InnoDB EN PROFONDEUR

"""
┌────────────────────────────────────────────────────────────────────────┐
│              InnoDB : LE MOTEUR PAR DÉFAUT                             │
└────────────────────────────────────────────────────────────────────────┘

POURQUOI InnoDB ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Développé par Innobase Oy (Finlande) en 1995
-> Racheté par Oracle en 2005
-> Intégré à MySQL en 2001
-> Moteur par défaut depuis MySQL 5.5 (2010)

FONCTIONNALITÉS PRINCIPALES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] Transactions ACID complètes
[OK] Verrous au niveau ligne (row-level locking)
[OK] MVCC (Multi-Version Concurrency Control)
[OK] Clés étrangères (FOREIGN KEY)
[OK] Récupération après crash (crash recovery)
[OK] Buffer pool (cache intelligent)
[OK] Compression de données
[OK] Réplication fiable
[OK] Full-text search (≥5.6)
[OK] Index géospatiaux (≥5.7)


ARCHITECTURE InnoDB :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

┌─────────────────────────────────────────────────────────────────────┐
│                         InnoDB ENGINE                               │
│                                                                     │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                   BUFFER POOL (RAM)                            │ │
│  │                                                                │ │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐       │ │
│  │  │  Data Pages  │  │ Index Pages  │  │  Undo Pages  │       │ │
│  │  │  (16KB each) │  │  (16KB each) │  │  (16KB each) │       │ │
│  │  └──────────────┘  └──────────────┘  └──────────────┘       │ │
│  │                                                                │ │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐       │ │
│  │  │ Change Buffer│  │ Adaptive Hash│  │  Lock Info   │       │ │
│  │  │ (Insert buf.)│  │    Index     │  │              │       │ │
│  │  └──────────────┘  └──────────────┘  └──────────────┘       │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              ^v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                     LOG SUBSYSTEM                              │ │
│  │                                                                │ │
│  │  ┌──────────────────┐         ┌──────────────────┐           │ │
│  │  │   Redo Log       │         │    Undo Log      │           │ │
│  │  │ (#innodb_redo/)  │         │ (undo_001, 002)  │           │ │
│  │  │   (WAL)          │         │    (MVCC)        │           │ │
│  │  └──────────────────┘         └──────────────────┘           │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              ^v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                   STORAGE SUBSYSTEM                            │ │
│  │                                                                │ │
│  │  ┌──────────────────┐  ┌──────────────────┐                  │ │
│  │  │  Tablespaces     │  │  Doublewrite     │                  │ │
│  │  │  (*.ibd files)   │  │    Buffer        │                  │ │
│  │  └──────────────────┘  └──────────────────┘                  │ │
│  └────────────────────────────────────────────────────────────────┘ │
│                              ^v                                      │
│  ┌────────────────────────────────────────────────────────────────┐ │
│  │                      DISK (File System)                        │ │
│  └────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────┘


1. BUFFER POOL (Cache en RAM)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

LE CŒUR DE LA PERFORMANCE InnoDB !

RÔLE :
-> Cache des pages de données, d'index, d'undo
-> Réduire les I/O disque (lecture/écriture)
-> Améliorer drastiquement les performances

TAILLE PAR DÉFAUT :
-> 128 MB (trop petit pour production !)
-> Recommandation : 70-80% de la RAM serveur dédié
-> Exemple : Serveur 16 GB RAM -> buffer pool = 12 GB

CONFIGURATION :
[mysqld]
innodb_buffer_pool_size = 12G        # Taille totale
innodb_buffer_pool_instances = 12    # Instances (1 par GB, max 64)
                                      # Réduit contention

STRUCTURE :
-> Divisé en PAGES de 16 KB (par défaut)
-> Chaque page peut contenir :
  - Plusieurs lignes de données
  - Nœud d'index B+Tree
  - Undo records
  - Etc.

ALGORITHME DE REMPLACEMENT : LRU (Least Recently Used)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

InnoDB utilise un LRU MODIFIÉ (midpoint insertion)

Liste LRU divisée en 2 zones :
┌────────────────────────────────────────────────────────┐
│                     LRU LIST                           │
│                                                        │
│  ┌─────────────────────┬──────────────────────────┐  │
│  │   YOUNG (New)       │      OLD (Eviction)      │  │
│  │   63% (défaut)      │        37%               │  │
│  ├─────────────────────┼──────────────────────────┤  │
│  │ Pages récemment     │ Pages candidates pour    │  │
│  │ accédées            │ éviction                 │  │
│  └─────────────────────┴──────────────────────────┘  │
│           ^                                            │
│        Midpoint (innodb_old_blocks_pct = 37)          │
└────────────────────────────────────────────────────────┘

MÉCANISME :
1. Nouvelle page lue du disque -> Insérée au MIDPOINT (pas en tête!)
2. Si la page est réaccédée dans les 1s (innodb_old_blocks_time) :
   -> Promue en tête de la liste YOUNG
3. Si la page n'est pas réaccédée :
   -> Reste dans OLD -> Évincée en premier

POURQUOI CE MÉCANISME ?
-> Protège contre les "full table scans" qui polluent le cache
-> Évite d'évincer des pages "hot" fréquemment utilisées

MONITORING :
SHOW ENGINE INNODB STATUS\G

...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 13743595520  # ~13 GB
Dictionary memory allocated 1876889
Buffer pool size   13107200               # Pages (13107200 × 16KB ≈ 200GB)
Free buffers       12451963               # Pages libres
Database pages     652487                 # Pages utilisées
Old database pages 240719                 # Pages dans OLD
Modified db pages  15234                  # Pages dirty (modifiées)
...

PAGES "DIRTY" :
-> Pages modifiées en RAM mais pas encore écrites sur disque
-> Écrites par le thread "page cleaner" (background)
-> Ou lors d'un checkpoint

FLUSHING STRATEGY :
[mysqld]
innodb_max_dirty_pages_pct = 90     # % max de pages dirty avant flush forcé
innodb_max_dirty_pages_pct_lwm = 10 # Low water mark


2. CHANGE BUFFER (Buffer d'insertion)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PROBLÈME À RÉSOUDRE :
Lors d'un INSERT, InnoDB doit mettre à jour :
1. La table (données)
2. TOUS les index secondaires

Si un index n'est PAS en cache :
-> Lecture disque nécessaire
-> Lent ! [X]

SOLUTION : CHANGE BUFFER
-> Buffer temporaire pour les modifications d'index secondaires NON-UNIQUES
-> Modifications buffered en RAM
-> Mergées plus tard (en arrière-plan ou à la lecture)

EXEMPLE :
INSERT INTO products (name, category_id, price) 
VALUES ('Produit X', 5, 1000);

Sans change buffer :
1. Insérer dans table products : [OK] rapide
2. Mettre à jour index idx_category (category_id) :
   -> Page index pas en cache -> lecture disque -> lent [X]
3. Mettre à jour index idx_price (price) :
   -> Page index pas en cache -> lecture disque -> lent [X]

Avec change buffer :
1. Insérer dans table products : [OK]
2. Buffer modification idx_category dans change buffer : [OK] rapide (RAM)
3. Buffer modification idx_price dans change buffer : [OK] rapide (RAM)

Plus tard (background merge ou lecture) :
-> Charger page index depuis disque
-> Appliquer les modifications buffered
-> Écrire sur disque

AVANTAGES :
[OK] INSERT/UPDATE/DELETE plus rapides
[OK] Moins d'I/O random
[OK] Regroupement des modifications (batch)

LIMITATIONS :
-> Uniquement pour index secondaires NON-UNIQUES
-> Pas pour index uniques (vérification d'unicité nécessaire immédiatement)
-> Pas pour index PRIMARY

CONFIGURATION :
[mysqld]
innodb_change_buffering = all    # all, inserts, deletes, changes, none
innodb_change_buffer_max_size = 25  # % du buffer pool (max 50)


3. ADAPTIVE HASH INDEX (Index Hash Adaptatif)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

OPTIMISATION AUTOMATIQUE !

IDÉE :
-> InnoDB détecte les patterns d'accès fréquents
-> Construit automatiquement un index hash en RAM
-> Accès O(1) au lieu de O(log n) pour ces patterns

EXEMPLE :
Requête fréquente :
SELECT * FROM users WHERE email = 'jean@example.com';

Normalement :
-> Recherche B+Tree : O(log n) = ~13 comparaisons pour 10000 lignes

Avec adaptive hash :
-> InnoDB détecte que "email" est souvent recherché
-> Construit hash: hash('jean@example.com') -> pointeur vers ligne
-> Recherche : O(1) = 1 lookup !

ACTIVATION :
[mysqld]
innodb_adaptive_hash_index = ON  # ON par défaut

MONITORING :
SHOW ENGINE INNODB STATUS\G

...
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
...
2.47 hash searches/s, 148.32 non-hash searches/s

Ratio : 2.47 / (2.47 + 148.32) ≈ 1.6% de hit rate
-> Si ratio bas : adaptive hash peu utilisé (normal pour charges variées)


4. REDO LOG (Write-Ahead Log)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

LE GARANT DE LA DURABILITÉ (ACID) !

PRINCIPE WAL (Write-Ahead Logging) :
-> AVANT d'écrire sur disque, écrire dans le log
-> Log séquentiel = rapide
-> Données = écriture random = lent

FICHIERS :
-> MySQL ≥8.0.30 : #innodb_redo/#ib_redo*
-> MySQL <8.0.30 : ib_logfile0, ib_logfile1

STRUCTURE :
-> Fichiers circulaires (ring buffer)
-> Écrasés en boucle
-> Taille fixe configurée

CONTENU :
Chaque entrée redo log contient :
{
  LSN (Log Sequence Number): 123456789,  # Position globale
  space_id: 25,       # Tablespace
  page_no: 1024,      # Page modifiée
  type: UPDATE_ROW,   # Type de modification
  offset: 512,        # Offset dans la page
  data: [...]         # Nouvelle valeur
}

FLUSH STRATEGY (innodb_flush_log_at_trx_commit) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

innodb_flush_log_at_trx_commit = 1 (DÉFAUT, ACID COMPLET) :
-> Chaque COMMIT : fsync() du redo log sur disque
-> Durabilité garantie même si crash OS
-> Performance : ~1000-5000 TPS (HDD), ~10000-50000 TPS (SSD)

innodb_flush_log_at_trx_commit = 2 (COMPROMIS) :
-> Chaque COMMIT : écriture dans le cache OS, pas de fsync()
-> Flush sur disque toutes les secondes (background thread)
-> Perte possible si crash OS (mais pas si crash MySQL)
-> Performance : ~10x plus rapide

innodb_flush_log_at_trx_commit = 0 (DANGEREUX) :
-> Écriture dans le cache InnoDB, flush toutes les secondes
-> Perte possible si crash MySQL ou OS
-> Performance : ~15x plus rapide
-> Ne JAMAIS utiliser en production !

CHECKPOINT :
-> Processus qui écrit les pages dirty sur disque
-> Permet de "vider" le redo log (réutilisation)
-> Se produit quand :
  - Redo log à 75% plein
  - Toutes les 60 secondes
  - Arrêt propre du serveur

RÉCUPÉRATION APRÈS CRASH :
1. MySQL redémarre
2. InnoDB scanne le redo log depuis dernier checkpoint
3. Rejoue toutes les modifications validées (COMMITTED)
4. Rollback des transactions non validées (via undo log)
5. MySQL prêt [OK]

DURÉE RÉCUPÉRATION :
-> Dépend de la taille du redo log
-> 1 GB redo log ≈ 10-30 secondes de récupération
-> Trade-off : Grand redo log = moins de checkpoints = plus rapide
            Mais récupération plus longue

CONFIGURATION :
[mysqld]
innodb_redo_log_capacity = 2G    # MySQL ≥8.0.30
# Ou
# innodb_log_file_size = 1G       # MySQL <8.0.30
# innodb_log_files_in_group = 2


5. UNDO LOG (Journal d'annulation)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

DOUBLE RÔLE CRUCIAL !

A) ROLLBACK DE TRANSACTIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Exemple :
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;  # Ancienne valeur : 500
UPDATE users SET balance = balance + 100 WHERE id = 2;  # Ancienne valeur : 300
# CRASH ! Ou ROLLBACK explicite

UNDO LOG contient :
[
  { operation: UPDATE, table: users, id: 1, old_value: 500 },
  { operation: UPDATE, table: users, id: 2, old_value: 300 }
]

ROLLBACK :
-> Appliquer l'inverse : restaurer old_value
-> users(id=1).balance = 500
-> users(id=2).balance = 300

B) MVCC (Multi-Version Concurrency Control)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

LE SECRET DES LECTURES SANS BLOCAGE !

PROBLÈME CLASSIQUE :
Transaction A (lecture) et Transaction B (écriture) sur même ligne
-> Sans MVCC : A doit attendre B (lock) ou voir données incohérentes

SOLUTION MVCC :
-> Garder plusieurs VERSIONS de chaque ligne
-> Chaque transaction voit sa propre version cohérente

MÉCANISME InnoDB :

Chaque ligne contient des colonnes cachées :
┌───────────────────────────────────────────────────────┐
│ LIGNE DANS TABLE users                                │
├───────────────────────────────────────────────────────┤
│ id: 1                      <- Colonne visible         │
│ name: "Jean"               <- Colonne visible         │
│ balance: 500               <- Colonne visible         │
│ ─────────────────────────────────────────────────────│
│ DB_TRX_ID: 12345           <- Transaction ID (caché)  │
│ DB_ROLL_PTR: 0x7f8a...     <- Pointeur vers undo log  │
│ DB_ROW_ID: 1               <- Row ID (si pas de PK)   │
└───────────────────────────────────────────────────────┘

SCÉNARIO :

T0 : État initial
users(id=1) : { balance: 500, DB_TRX_ID: 100 }

T1 : Transaction A démarre (READ COMMITTED)
-> Prend un snapshot : trx_id = 200
-> SELECT * FROM users WHERE id = 1;
-> Lit : balance = 500 [OK]

T2 : Transaction B démarre
-> trx_id = 201
-> UPDATE users SET balance = 400 WHERE id = 1;

Que fait InnoDB ?
1. Créer une entrée undo log :
   { trx_id: 201, table: users, id: 1, old_balance: 500 }
2. Modifier la ligne :
   users(id=1) : { balance: 400, DB_TRX_ID: 201, DB_ROLL_PTR: -> undo }
3. Transaction B COMMIT

T3 : Transaction A relit
-> SELECT * FROM users WHERE id = 1;
-> InnoDB voit DB_TRX_ID = 201 (postérieure à 200)
-> Ligne pas visible pour Transaction A !
-> InnoDB suit DB_ROLL_PTR vers undo log
-> Reconstruit l'ancienne version : balance = 500
-> Retourne : balance = 500 [OK]

COHÉRENCE GARANTIE !

PURGE (Nettoyage) :
-> Thread "purge" nettoie les anciennes versions d'undo
-> Quand plus aucune transaction n'en a besoin

FICHIERS :
-> undo_001, undo_002 (tablespaces undo)
-> Taille auto-ajustable (≥8.0.21)

CONFIGURATION :
[mysqld]
innodb_undo_tablespaces = 2       # Nombre de tablespaces undo
innodb_max_undo_log_size = 1G     # Taille max avant truncate
innodb_undo_log_truncate = ON     # Auto-truncate


6. DOUBLEWRITE BUFFER
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PROTECTION CONTRE LA CORRUPTION !

PROBLÈME : Partial Page Write
-> InnoDB écrit des pages de 16 KB
-> Système de fichiers écrit par secteurs de 4 KB ou 512 bytes
-> Si crash pendant l'écriture -> page partiellement écrite -> CORRUPTION !

Exemple :
Page avant : [A][A][A][A]  (4 secteurs)
Écriture :   [B][B][B][B]
Crash après 2 secteurs : [B][B][A][A] <- CORRUPTED !

SOLUTION DOUBLEWRITE :
1. InnoDB écrit d'abord dans le doublewrite buffer (séquentiel)
2. Puis écrit dans le fichier .ibd (position réelle, random)
3. Si crash pendant (2) :
   -> Au redémarrage, vérifier checksum de la page
   -> Si corrompu : restaurer depuis doublewrite buffer [OK]

LOCALISATION :
-> MySQL <8.0.20 : Dans ibdata1
-> MySQL ≥8.0.20 : Fichiers séparés (#ib_16384_*.dblwr)

DÉSACTIVATION (sur SSD avec protection atomique) :
[mysqld]
innodb_doublewrite = 0

Ou fichier par fichier :
CREATE TABLE t1 (...) TABLESPACE = innodb_file_per_table 
                      COMPRESSION = 'zlib';


7. STRUCTURE SUR DISQUE : FORMAT DE PAGE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FICHIER .ibd :
-> Série de PAGES de 16 KB (configurable : 4K, 8K, 16K, 32K, 64K)

STRUCTURE D'UNE PAGE :
┌─────────────────────────────────────────────────┐
│                  PAGE (16 KB)                   │
├─────────────────────────────────────────────────┤
│ FIL Header (38 bytes)                           │
│  - Checksum (4 bytes)                           │
│  - Page number (4 bytes)                        │
│  - LSN (8 bytes) <- Dernière modification        │
│  - Page type (2 bytes) <- INDEX, UNDO, etc.      │
│  - Flush LSN (8 bytes)                          │
│  - Space ID (4 bytes)                           │
├─────────────────────────────────────────────────┤
│ Page Header (56 bytes)                          │
│  - Number of records                            │
│  - Heap top pointer                             │
│  - First free record                            │
│  - Garbage space                                │
│  - Last insert position                         │
│  - Direction (ASC/DESC)                         │
├─────────────────────────────────────────────────┤
│ Infimum Record (pseudo-record)                  │
├─────────────────────────────────────────────────┤
│ User Records (lignes de données)                │
│  ┌──────────────────────────────────────┐       │
│  │ Record 1                             │       │
│  │  - Extra bytes (6 bytes)             │       │
│  │  - Field 1, Field 2, ...             │       │
│  │  - NULL bitmap                       │       │
│  │  - DB_TRX_ID (6 bytes) <- Trx ID      │       │
│  │  - DB_ROLL_PTR (7 bytes) <- Undo ptr  │       │
│  └──────────────────────────────────────┘       │
│  ┌──────────────────────────────────────┐       │
│  │ Record 2                             │       │
│  └──────────────────────────────────────┘       │
│  ...                                            │
├─────────────────────────────────────────────────┤
│ Supremum Record (pseudo-record)                 │
├─────────────────────────────────────────────────┤
│ Page Directory (offsets vers records)           │
│  - Permet recherche binaire dans la page        │
├─────────────────────────────────────────────────┤
│ FIL Trailer (8 bytes)                           │
│  - Checksum (4 bytes) <- Vérifier intégrité      │
│  - LSN low 32 bits (4 bytes)                    │
└─────────────────────────────────────────────────┘

FORMAT DE LIGNE (ROW FORMAT) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ROW_FORMAT=DYNAMIC (défaut ≥5.7) :
-> Colonnes VARCHAR, TEXT, BLOB :
  - Si ≤ 40 bytes : inline dans la page
  - Si > 40 bytes : stockage externe (overflow pages), 20 bytes inline (prefix)
-> Compression possible

ROW_FORMAT=COMPACT :
-> Format compact, moins d'overhead
-> Colonnes TEXT/BLOB partiellement inline

ROW_FORMAT=COMPRESSED :
-> Compression de toute la page (zlib)
-> Économie 50-70% d'espace
-> Coût CPU pour compression/décompression

CONFIGURATION :
CREATE TABLE t1 (
  id INT PRIMARY KEY,
  data TEXT
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
"""


# [OK] PARTIE 7 : TYPES DE DONNÉES MySQL

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    TYPES DE DONNÉES MySQL                              │
└────────────────────────────────────────────────────────────────────────┘

CATÉGORIES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Numériques (INTEGER, DECIMAL, FLOAT, etc.)
2. Chaînes de caractères (CHAR, VARCHAR, TEXT)
3. Dates et heures (DATE, DATETIME, TIMESTAMP)
4. Binaires (BINARY, VARBINARY, BLOB)
5. Spatiaux (POINT, LINESTRING, POLYGON)
6. JSON (natif depuis 5.7)


1. TYPES NUMÉRIQUES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

A) ENTIERS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌──────────┬───────┬────────────────────┬────────────────────┐
│  Type    │ Bytes │   SIGNED Range     │  UNSIGNED Range    │
├──────────┼───────┼────────────────────┼────────────────────┤
│ TINYINT  │   1   │ -128 à 127         │ 0 à 255            │
│ SMALLINT │   2   │ -32768 à 32767     │ 0 à 65535          │
│ MEDIUMINT│   3   │ -8388608 à ...     │ 0 à 16777215       │
│ INT      │   4   │ -2147483648 à ...  │ 0 à 4294967295     │
│ BIGINT   │   8   │ -9223...808 à ...  │ 0 à 18446...615    │
└──────────┴───────┴────────────────────┴────────────────────┘

USAGE :
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
age TINYINT UNSIGNED  # 0-255 suffit pour un âge
year SMALLINT         # -32768 à 32767
user_id BIGINT UNSIGNED  # Pour très grandes bases

ZEROFILL (obsolète ≥8.0) :
id INT(5) ZEROFILL  # Affiche 00042 pour 42
-> Déprécié, à éviter !

B) DÉCIMAUX (Précision exacte)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

DECIMAL(M, D) ou NUMERIC(M, D)
-> M : Précision totale (max 65)
-> D : Décimales (max 30)

DECIMAL(10, 2) : 10 chiffres dont 2 décimales
-> Range : -99999999.99 à 99999999.99

USAGE :
price DECIMAL(10, 2)  # Prix : 99999999.99 max
salary DECIMAL(15, 2)  # Salaire

STOCKAGE :
-> Stocké en binaire (pas de virgule flottante)
-> Calculs EXACTS (pas d'arrondis comme FLOAT)

EXEMPLE :
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2) NOT NULL,
  CHECK (price >= 0)
);

INSERT INTO products VALUES (1, 19.99);
SELECT price * 1.2 FROM products;  # Résultat exact : 23.99

C) VIRGULE FLOTTANTE (Approximation)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FLOAT(M, D)  : 4 bytes, précision ~7 chiffres
DOUBLE(M, D) : 8 bytes, précision ~15 chiffres

[ATTENTION] ATTENTION : Imprécision due à la représentation binaire !

EXEMPLE PROBLÈME :
CREATE TABLE test (val FLOAT);
INSERT INTO test VALUES (0.1 + 0.2);
SELECT val FROM test;
-> Résultat : 0.300000011920929 (au lieu de 0.3)

USAGE :
latitude DOUBLE   # Coordonnées GPS
longitude DOUBLE
scientific_value DOUBLE  # Calculs scientifiques

RECOMMANDATION :
-> Utiliser DECIMAL pour l'argent !
-> Utiliser FLOAT/DOUBLE pour calculs approximatifs acceptables


2. TYPES CHAÎNES DE CARACTÈRES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

A) CHAR(N) - Longueur fixe
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Longueur FIXE (padding avec espaces)
-> Max 255 caractères
-> Rapide pour longueurs connues

CHAR(10) :
'abc'     -> Stocké comme 'abc       ' (7 espaces ajoutés)
'1234567890' -> Stocké tel quel

USAGE :
country_code CHAR(2)   # 'FR', 'US', 'SN'
status CHAR(1)         # 'A' (actif), 'I' (inactif)
md5_hash CHAR(32)      # Hash MD5

B) VARCHAR(N) - Longueur variable
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Longueur VARIABLE (1-2 bytes de préfixe pour longueur)
-> Max 65535 bytes (dépend de l'encoding)
-> Économise l'espace

VARCHAR(100) :
'abc' -> Stocké : [3][abc] (1 byte longueur + 3 bytes données)

USAGE :
name VARCHAR(100)
email VARCHAR(255)  # Standard email
url VARCHAR(2083)   # Max URL length (IE)

UTF8MB4 :
-> 1 caractère = 1-4 bytes
-> VARCHAR(255) utf8mb4 = max 255 caractères = max 1020 bytes

C) TEXT (Texte long)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌────────────┬────────────┬─────────────────┐
│   Type     │  Max Size  │     Usage       │
├────────────┼────────────┼─────────────────┤
│ TINYTEXT   │ 255 bytes  │ Petits textes   │
│ TEXT       │ 65 KB      │ Articles courts │
│ MEDIUMTEXT │ 16 MB      │ Articles longs  │
│ LONGTEXT   │ 4 GB       │ Livres, docs    │
└────────────┴────────────┴─────────────────┘

USAGE :
description TEXT
article_content MEDIUMTEXT
book_content LONGTEXT

[ATTENTION] LIMITATIONS :
-> Pas de DEFAULT value
-> Ne peut pas être utilisé comme clé primaire
-> Indexation limitée (prefix index uniquement)

CREATE INDEX idx_desc ON articles (description(100));
                                    # Index sur 100 premiers chars

D) ENUM - Énumération
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Liste fixe de valeurs
-> Stocké comme entier (1-2 bytes)
-> Économique en espace

status ENUM('pending', 'active', 'suspended', 'deleted')

Stockage :
'pending' -> 1
'active' -> 2
'suspended' -> 3
'deleted' -> 4

AVANTAGES :
[OK] Économie d'espace (2 bytes vs 20+ bytes string)
[OK] Validation automatique

INCONVÉNIENTS :
[X] Modification difficile (ALTER TABLE)
[X] Moins flexible qu'une table de référence

E) SET - Ensemble
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Plusieurs valeurs simultanées (bitmap)
-> Max 64 éléments

permissions SET('read', 'write', 'delete', 'admin')

Exemples :
'read,write' -> Bitmap : 0011 (bits 1 et 2)
'read,delete' -> Bitmap : 0101
'read,write,delete,admin' -> Bitmap : 1111

RECHERCHE :
SELECT * FROM users WHERE FIND_IN_SET('admin', permissions);


3. TYPES DATE ET HEURE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

┌───────────┬───────┬─────────────────────┬──────────────────────────┐
│   Type    │ Bytes │       Range         │        Format            │
├───────────┼───────┼─────────────────────┼──────────────────────────┤
│ DATE      │   3   │ 1000-01-01 à        │ YYYY-MM-DD               │
│           │       │ 9999-12-31          │                          │
├───────────┼───────┼─────────────────────┼──────────────────────────┤
│ TIME      │   3   │ -838:59:59 à        │ HH:MM:SS                 │
│           │       │ 838:59:59           │                          │
├───────────┼───────┼─────────────────────┼──────────────────────────┤
│ DATETIME  │   8   │ 1000-01-01 00:00:00 │ YYYY-MM-DD HH:MM:SS      │
│           │       │ à 9999-12-31 ...    │                          │
├───────────┼───────┼─────────────────────┼──────────────────────────┤
│ TIMESTAMP │   4   │ 1970-01-01 00:00:01 │ YYYY-MM-DD HH:MM:SS      │
│           │       │ à 2038-01-19 ...    │ (UTC + conversion)       │
├───────────┼───────┼─────────────────────┼──────────────────────────┤
│ YEAR      │   1   │ 1901 à 2155         │ YYYY                     │
└───────────┴───────┴─────────────────────┴──────────────────────────┘

DATETIME vs TIMESTAMP :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

DATETIME :
-> Stocke la date/heure "telle quelle"
-> Pas de conversion de timezone
-> Range plus large (1000-9999)
-> 8 bytes

TIMESTAMP :
-> Stocke en UTC (Unix timestamp)
-> Conversion automatique selon timezone session
-> Range limité (1970-2038 - problème Y2038!)
-> 4 bytes
-> AUTO UPDATE : ON UPDATE CURRENT_TIMESTAMP

USAGE :
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
published_at TIMESTAMP NULL DEFAULT NULL

FRACTIONAL SECONDS (≥5.6.4) :
DATETIME(6)  # 6 décimales : YYYY-MM-DD HH:MM:SS.microseconds
TIMESTAMP(3) # 3 décimales : millisecondes

FONCTIONS :
NOW()              # Date/heure actuelle
CURDATE()          # Date actuelle
CURTIME()          # Heure actuelle
DATE_ADD(date, INTERVAL 1 DAY)
DATE_SUB(date, INTERVAL 1 WEEK)
TIMESTAMPDIFF(HOUR, start, end)  # Différence en heures


4. TYPES BINAIRES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BINARY(N) : Équivalent binaire de CHAR (longueur fixe)
VARBINARY(N) : Équivalent binaire de VARCHAR
BLOB : Binary Large Object

┌────────────┬────────────┬─────────────────┐
│   Type     │  Max Size  │     Usage       │
├────────────┼────────────┼─────────────────┤
│ TINYBLOB   │ 255 bytes  │ Petits binaires │
│ BLOB       │ 65 KB      │ Images petites  │
│ MEDIUMBLOB │ 16 MB      │ Images moyennes │
│ LONGBLOB   │ 4 GB       │ Vidéos, gros    │
└────────────┴────────────┴─────────────────┘

USAGE :
password_hash BINARY(60)  # bcrypt hash
salt VARBINARY(32)
profile_picture BLOB
video MEDIUMBLOB

RECOMMANDATION :
-> Stocker les fichiers dans un système de fichiers (S3, etc.)
-> Stocker seulement le chemin/URL dans MySQL
photo_url VARCHAR(255)


5. TYPES SPATIAUX (Géospatiaux)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Support natif depuis MySQL 5.7 (amélioré en 8.0)

POINT : Un point (latitude, longitude)
LINESTRING : Une ligne (suite de points)
POLYGON : Un polygone (zone fermée)
MULTIPOINT, MULTILINESTRING, MULTIPOLYGON
GEOMETRYCOLLECTION

USAGE :
location POINT NOT NULL SRID 4326  # SRID 4326 = WGS84 (GPS)

INDEX SPATIAL :
CREATE SPATIAL INDEX idx_location ON restaurants(location);

INSERTION :
INSERT INTO restaurants (name, location)
VALUES ('Le Dakarois', ST_GeomFromText('POINT(-17.4441 14.6928)', 4326));

REQUÊTES :
# Restaurants dans un rayon de 5 km
SELECT name, ST_Distance_Sphere(
  location,
  ST_GeomFromText('POINT(-17.4500 14.7000)', 4326)
) AS distance
FROM restaurants
WHERE ST_Distance_Sphere(
  location,
  ST_GeomFromText('POINT(-17.4500 14.7000)', 4326)
) <= 5000
ORDER BY distance;


6. TYPE JSON (Natif depuis 5.7)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

STOCKAGE BINAIRE OPTIMISÉ !

attributes JSON

INSERT INTO products (name, attributes) VALUES 
('T-Shirt', '{"color": "red", "size": "L", "material": "cotton"}');

FONCTIONS JSON :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Extraction
SELECT name, attributes->'$.color' AS color FROM products;
SELECT name, attributes->>'$.color' AS color FROM products;  # Sans quotes

# Recherche
SELECT * FROM products WHERE attributes->'$.color' = 'red';

# Modification
UPDATE products 
SET attributes = JSON_SET(attributes, '$.stock', 10)
WHERE id = 1;

# Ajout
UPDATE products 
SET attributes = JSON_INSERT(attributes, '$.brand', 'Nike')
WHERE id = 1;

# Suppression
UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.color')
WHERE id = 1;

# INDEX (Generated Column + Index)
ALTER TABLE products 
ADD COLUMN color VARCHAR(50) AS (attributes->>'$.color') VIRTUAL;

CREATE INDEX idx_color ON products(color);

# Arrays JSON
INSERT INTO products VALUES (1, 'Product', '{"tags": ["sport", "outdoor"]}');

SELECT * FROM products 
WHERE JSON_CONTAINS(attributes->'$.tags', '"sport"');


CHOIX DU BON TYPE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÈGLES GÉNÉRALES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] Utiliser le plus petit type possible (économie espace + performance)
[OK] UNSIGNED pour valeurs positives uniquement
[OK] DECIMAL pour l'argent (jamais FLOAT/DOUBLE)
[OK] VARCHAR pour textes courts variables
[OK] TEXT pour textes longs
[OK] DATETIME pour dates (pas TIMESTAMP sauf besoin timezone)
[OK] JSON pour données semi-structurées
[OK] ENUM pour listes fermées et stables

[X] Éviter ENUM si les valeurs changent souvent
[X] Ne pas stocker de gros fichiers en BLOB
[X] Ne pas utiliser FLOAT/DOUBLE pour l'argent
"""


# [OK] PARTIE 8 : INDEX ET OPTIMISATION

"""
┌────────────────────────────────────────────────────────────────────────┐
│              INDEX MySQL : ACCÉLÉRER LES REQUÊTES                      │
└────────────────────────────────────────────────────────────────────────┘

QU'EST-CE QU'UN INDEX ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ANALOGIE : Index d'un livre
-> Sans index : lire TOUT le livre pour trouver un mot
-> Avec index : aller directement à la page

SANS INDEX :
SELECT * FROM users WHERE email = 'jean@example.com';
-> MySQL scanne TOUTES les lignes (FULL TABLE SCAN)
-> 1 million de lignes ? 1 million de lectures ! [LENT]

AVEC INDEX :
CREATE INDEX idx_email ON users(email);
-> MySQL utilise l'arbre B+Tree
-> Recherche en O(log n) : ~20 comparaisons pour 1 million de lignes [RAPIDE]


TYPES D'INDEX InnoDB :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. PRIMARY KEY (Clustered Index)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

LE PLUS IMPORTANT !

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

PARTICULARITÉ InnoDB : Clustered Index
-> Les DONNÉES de la table sont stockées DANS l'index PRIMARY KEY
-> L'index PRIMARY = la table elle-même !

STRUCTURE B+TREE (Clustered) :
┌───────────────────────────────────────────────────────────┐
│                     PRIMARY KEY INDEX                     │
│                    (Contient les DONNÉES)                 │
│                                                           │
│  Root Node (Page 3)                                       │
│  ├─ [1 ... 500] -> Page 10                                │
│  └─ [501 ... 1000] -> Page 11                             │
│                                                           │
│  Page 10 (Non-leaf)                                       │
│  ├─ [1 ... 250] -> Page 100                               │
│  └─ [251 ... 500] -> Page 101                             │
│                                                           │
│  Page 100 (Leaf - DONNÉES RÉELLES)                       │
│  ├─ id=1, name="Alice", email="alice@..."                │
│  ├─ id=2, name="Bob", email="bob@..."                    │
│  ├─ ...                                                   │
│  └─ id=250, name="Zoe", email="zoe@..."                  │
└───────────────────────────────────────────────────────────┘

CONSÉQUENCES :
[OK] Recherche par PRIMARY KEY = ultra rapide (accès direct)
[OK] Range queries efficaces : SELECT * WHERE id BETWEEN 10 AND 20
[OK] ORDER BY PRIMARY KEY = gratuit (déjà trié)

[X] INSERT désordonnés = lents (réorganisation de l'arbre)

RECOMMANDATION :
-> Toujours utiliser INT AUTO_INCREMENT comme PRIMARY KEY
-> Éviter UUID/GUID aléatoires comme PRIMARY (fragmenté)
-> Si besoin UUID : utiliser UUID_TO_BIN(UUID(), 1) (version 1, ordonné)

SI PAS DE PRIMARY KEY ?
-> InnoDB crée automatiquement un hidden clustered index (DB_ROW_ID)
-> Perte de performance ! Toujours définir un PRIMARY KEY !


2. SECONDARY INDEX (Index secondaire)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Index sur colonnes autres que PRIMARY KEY

CREATE INDEX idx_email ON users(email);

STRUCTURE B+TREE (Secondary) :
┌───────────────────────────────────────────────────────────┐
│                    SECONDARY INDEX                        │
│              (Contient email + PRIMARY KEY)               │
│                                                           │
│  Root Node                                                │
│  ├─ ['a@...' ... 'm@...'] -> Page 200                     │
│  └─ ['n@...' ... 'z@...'] -> Page 201                     │
│                                                           │
│  Page 200 (Leaf)                                          │
│  ├─ email='alice@...', PRIMARY_KEY=1                      │
│  ├─ email='bob@...', PRIMARY_KEY=2                        │
│  ├─ ...                                                   │
│  └─ email='marc@...', PRIMARY_KEY=250                     │
└───────────────────────────────────────────────────────────┘

PROCESSUS DE RECHERCHE :
SELECT * FROM users WHERE email = 'jean@example.com';

1. Recherche dans idx_email (B+Tree)
   -> Trouve : email='jean@...', PRIMARY_KEY=42
   [TEMPS] O(log n)

2. Lookup dans PRIMARY KEY (clustered index)
   -> Avec PRIMARY_KEY=42, récupérer les données complètes
   [TEMPS] O(log n)

Total : 2× O(log n) (double lookup)

COVERING INDEX (Index couvrant) :
Si la requête N'A BESOIN QUE des colonnes dans l'index :

SELECT email FROM users WHERE email = 'jean@example.com';

-> Pas besoin du lookup PRIMARY KEY !
-> 1× O(log n) uniquement [OK]

ENCORE MIEUX :
CREATE INDEX idx_email_name ON users(email, name);

SELECT email, name FROM users WHERE email = 'jean@example.com';
-> Covering index ! Pas de lookup PRIMARY KEY !


3. UNIQUE INDEX
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Garantit l'unicité des valeurs

CREATE UNIQUE INDEX idx_email_unique ON users(email);

Ou directement :
ALTER TABLE users ADD UNIQUE KEY (email);

EFFET :
INSERT INTO users (id, email) VALUES (1, 'jean@example.com');  # [OK]
INSERT INTO users (id, email) VALUES (2, 'jean@example.com');  # [X] Duplicate entry

PRIMARY KEY = UNIQUE INDEX automatique


4. COMPOSITE INDEX (Index composé)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Index sur PLUSIEURS colonnes

CREATE INDEX idx_lastname_firstname ON users(last_name, first_name);

STRUCTURE (conceptuel) :
┌──────────────┬──────────────┬─────────────┐
│  last_name   │  first_name  │ PRIMARY_KEY │
├──────────────┼──────────────┼─────────────┤
│ Dupont       │ Jean         │ 1           │
│ Dupont       │ Marie        │ 2           │
│ Dupont       │ Pierre       │ 3           │
│ Martin       │ Alice        │ 4           │
│ Martin       │ Bob          │ 5           │
└──────────────┴──────────────┴─────────────┘

ORDRE DES COLONNES = CRITIQUE !

REQUÊTES UTILISANT L'INDEX :
[OK] WHERE last_name = 'Dupont'
[OK] WHERE last_name = 'Dupont' AND first_name = 'Jean'
[OK] WHERE last_name = 'Dupont' AND first_name LIKE 'J%'

REQUÊTES N'UTILISANT PAS L'INDEX :
[X] WHERE first_name = 'Jean'  (première colonne manquante)
[X] WHERE first_name LIKE 'J%'

RÈGLE : Leftmost Prefix
-> L'index peut être utilisé si les colonnes de gauche sont présentes

Index (A, B, C) peut servir pour :
-> (A)
-> (A, B)
-> (A, B, C)
Mais PAS pour : (B), (C), (B, C)


5. FULL-TEXT INDEX
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Recherche textuelle avancée (mots-clés, pertinence)

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

RECHERCHE :
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST ('mysql performance' IN NATURAL LANGUAGE MODE);

MODES :
1. NATURAL LANGUAGE MODE (défaut)
   -> Recherche par pertinence
   -> Stopwords ignorés (le, la, de, etc.)

2. BOOLEAN MODE
   -> Opérateurs : +mot (obligatoire), -mot (exclusion), "phrase exacte"
   
   AGAINST ('+mysql -oracle "base de données"' IN BOOLEAN MODE)

3. QUERY EXPANSION MODE
   -> Élargit la recherche avec termes similaires

LIMITATIONS :
-> InnoDB : min 3 caractères par mot (ft_min_word_len=3)
-> MyISAM : plus de fonctionnalités


6. SPATIAL INDEX (Géospatial)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Pour données géographiques (POINT, POLYGON, etc.)

CREATE SPATIAL INDEX idx_location ON restaurants(location);

Requêtes optimisées pour :
-> Proximité
-> Intersection
-> Contenu

SELECT * FROM restaurants 
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(-17.44 14.69)')) <= 5000;


STRATÉGIES D'INDEXATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

RÈGLES D'OR :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] CRÉER DES INDEX POUR :
1. PRIMARY KEY (obligatoire !)
2. FOREIGN KEY (jointures)
3. Colonnes dans WHERE fréquent
4. Colonnes dans ORDER BY
5. Colonnes dans GROUP BY
6. Colonnes dans JOIN ON

[X] NE PAS INDEXER :
1. Colonnes rarement utilisées dans WHERE
2. Colonnes avec peu de valeurs distinctes (sexe: M/F)
3. Petites tables (< 1000 lignes)
4. Colonnes modifiées très fréquemment

COÛT D'UN INDEX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Espace disque : 10-30% de la taille de la table
-> RAM : Index chargés dans buffer pool
-> Ralentissement INSERT/UPDATE/DELETE (maintien de l'index)

CHAQUE INDEX = +10-20% de temps d'écriture

NOMBRE OPTIMAL :
-> 3-7 index par table (en moyenne)
-> Analyser avec EXPLAIN

EXEMPLE CONCRET :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Table : orders (10 millions de lignes)

Requête fréquente :
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND status = 'completed'
  AND order_date >= '2024-01-01'
ORDER BY order_date DESC
LIMIT 10;

INDEX À CRÉER :
CREATE INDEX idx_customer_status_date 
ON orders(customer_id, status, order_date DESC);

POURQUOI CET ORDRE ?
1. customer_id : égalité (= plus sélectif)
2. status : égalité
3. order_date : range + ORDER BY (DESC pour éviter filesort)

AVEC CET INDEX :
-> Recherche directe du customer_id
-> Filtrage du status
-> Scan des order_date déjà triés DESC
-> LIMIT 10 : s'arrête après 10 lignes
-> Temps : ~5 ms [RAPIDE]

SANS INDEX :
-> Full table scan : 10 millions de lignes
-> Filtrage en mémoire
-> Tri en mémoire (filesort)
-> Temps : ~30 secondes [LENT]

GAIN : 6000× plus rapide !


ANALYSE DE PERFORMANCE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

EXPLAIN :
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+-------------------------+----------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys           | key      | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-------------------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_customer_status_date| idx_...  | 4       | const | 50   | Using where |
+----+-------------+--------+------+-------------------------+----------+---------+-------+------+-------------+

COLONNES IMPORTANTES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

type : Type d'accès (du meilleur au pire)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
system  : Table avec 1 ligne (meilleur)
const   : Lookup PRIMARY KEY ou UNIQUE (très bon)
eq_ref  : JOIN avec PRIMARY KEY ou UNIQUE
ref     : Index non-unique
range   : Range scan (BETWEEN, >, <)
index   : Full index scan
ALL     : Full table scan (PIRE !)

possible_keys : Index candidats
key : Index réellement utilisé
rows : Estimation nombre de lignes examinées (plus bas = mieux)

Extra : Informations additionnelles
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Using index       : [OK] Covering index (optimal)
Using where       : [OK] Filtrage additionnel
Using filesort    : [X] Tri en mémoire (coûteux, créer index)
Using temporary   : [X] Table temporaire (GROUP BY sans index)
Using join buffer : [ATTENTION] JOIN sans index (ajouter index sur FK)


EXPLAIN ANALYZE (MySQL ≥8.0.18) :
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

-> Exécute réellement la requête
-> Temps réel, nombre de lignes réelles

SHOW PROFILE (profiling détaillé) :
SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 12345;
SHOW PROFILE;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000034 |
| init                 | 0.000052 |
| System lock          | 0.000012 |
| optimizing           | 0.000015 |
| statistics           | 0.000025 |
| preparing            | 0.000018 |
| executing            | 0.000008 |
| Sending data         | 0.002354 |  <- Temps principal
| end                  | 0.000013 |
| query end            | 0.000007 |
| closing tables       | 0.000010 |
| freeing items        | 0.000023 |
| cleaning up          | 0.000015 |
+----------------------+----------+

SLOW QUERY LOG :
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # Logguer si > 2 secondes

ANALYSER :
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-> Top 10 requêtes les plus lentes


MAINTENANCE DES INDEX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ANALYSER LES STATISTIQUES :
ANALYZE TABLE orders;
-> Recalcule les statistiques (cardinalité, distribution)
-> Améliore les plans d'exécution de l'optimiseur

OPTIMISER (Défragmenter) :
OPTIMIZE TABLE orders;
-> Reconstruit la table (récupère l'espace fragmenté)
-> Bloquant ! À faire pendant fenêtre de maintenance

VÉRIFIER L'UTILISATION :
SELECT 
  table_schema,
  table_name,
  index_name,
  cardinality
FROM information_schema.statistics
WHERE table_schema = 'ecommerce'
ORDER BY table_name, index_name;

INDEX INUTILISÉS :
SELECT 
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce';
-> Index jamais utilisés -> Supprimer pour libérer espace et améliorer INSERT
"""


# [OK] PARTIE 9 : TRANSACTIONS ACID ET ISOLATION

"""
┌────────────────────────────────────────────────────────────────────────┐
│                TRANSACTIONS ACID : GARANTIES FONDAMENTALES             │
└────────────────────────────────────────────────────────────────────────┘

QU'EST-CE QU'UNE TRANSACTION ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Une transaction = groupe d'opérations SQL qui doivent TOUTES réussir ou TOUTES échouer

EXEMPLE CLASSIQUE : Transfert bancaire
-> Débiter compte A : -100€
-> Créditer compte B : +100€

SANS TRANSACTION :
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  # [OK]
# CRASH ! [RAPIDE]
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  # [X] Jamais exécuté
-> Résultat : 100€ perdus ! [ARGENT]

AVEC TRANSACTION :
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  # Valide tout d'un coup

Si crash avant COMMIT :
-> ROLLBACK automatique [OK]
-> Compte A pas débité
-> Cohérence préservée !


PROPRIÉTÉS ACID :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

A - ATOMICITY (Atomicité)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
"Tout ou rien" : Transaction = unité indivisible

GARANTIE InnoDB :
-> UNDO LOG conserve les anciennes valeurs
-> En cas d'échec : ROLLBACK restaure l'état initial

EXEMPLE :
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 500);  # order_id = 100
INSERT INTO order_items (order_id, product_id, qty) VALUES (100, 5, 2);
INSERT INTO order_items (order_id, product_id, qty) VALUES (100, 8, 1);
COMMIT;

Si crash après 2ème INSERT :
-> Undo log rejoue l'inverse :
  - DELETE order_items WHERE order_id = 100
  - DELETE orders WHERE order_id = 100
-> État cohérent restauré [OK]

C - CONSISTENCY (Cohérence)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Les contraintes d'intégrité sont toujours respectées

CONTRAINTES :
-> PRIMARY KEY
-> UNIQUE
-> FOREIGN KEY
-> CHECK
-> NOT NULL

EXEMPLE :
CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL,
  CHECK (balance >= 0)  # Solde jamais négatif
);

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
# Si balance devient négative -> ERREUR, ROLLBACK automatique

-> Contrainte CHECK empêche état incohérent [OK]

I - ISOLATION (Isolation)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Transactions concurrentes ne s'interfèrent pas

Plusieurs utilisateurs modifient la DB en même temps :
-> Sans isolation : chaos, données corrompues
-> Avec isolation : chaque transaction voit une vue cohérente

DÉTAILS dans section "Niveaux d'isolation" ci-dessous

D - DURABILITY (Durabilité)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Une fois COMMIT fait, les données persistent même en cas de crash

GARANTIE InnoDB :
-> REDO LOG écrit sur disque AVANT le COMMIT
-> WAL (Write-Ahead Logging)

COMMIT retourne [OK] -> Garanti sur disque, même si :
- Coupure électrique juste après
- Crash MySQL
- Crash OS

Récupération au redémarrage : redo log rejoué [OK]


SYNTAXE TRANSACTIONS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

START TRANSACTION;
-- ou BEGIN;

-- Opérations SQL
UPDATE ...
INSERT ...
DELETE ...

COMMIT;  # Valider
-- ou ROLLBACK;  # Annuler

EXEMPLE COMPLET :
START TRANSACTION;

-- Créer une commande
INSERT INTO orders (user_id, total, status) 
VALUES (123, 0, 'pending');

SET @order_id = LAST_INSERT_ID();

-- Ajouter les articles
INSERT INTO order_items (order_id, product_id, qty, price) 
VALUES 
  (@order_id, 5, 2, 25.00),
  (@order_id, 8, 1, 50.00);

-- Calculer le total
UPDATE orders 
SET total = (SELECT SUM(qty * price) FROM order_items WHERE order_id = @order_id)
WHERE id = @order_id;

-- Déduire du stock
UPDATE products SET stock = stock - 2 WHERE id = 5;
UPDATE products SET stock = stock - 1 WHERE id = 8;

COMMIT;

AUTOCOMMIT :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Par défaut : AUTOCOMMIT = 1
-> Chaque requête SQL = transaction implicite

SELECT @@autocommit;  # 1

INSERT INTO users VALUES (...);  
# Équivaut à :
# START TRANSACTION;
# INSERT INTO users VALUES (...);
# COMMIT;

DÉSACTIVER :
SET autocommit = 0;

INSERT INTO users VALUES (...);  # Transaction ouverte
INSERT INTO users VALUES (...);  # Même transaction
COMMIT;  # Valider les 2 INSERT

RECOMMANDATION :
-> Laisser autocommit = 1
-> Utiliser START TRANSACTION explicite quand nécessaire


NIVEAUX D'ISOLATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

4 NIVEAUX standardisés SQL (du moins strict au plus strict) :

┌──────────────────────┬────────────┬─────────────┬───────────────┐
│ Niveau               │ Dirty Read │ Non-Repeat. │ Phantom Read  │
├──────────────────────┼────────────┼─────────────┼───────────────┤
│ READ UNCOMMITTED     │     [OK]     │      [OK]     │      [OK]       │
│ READ COMMITTED       │     [X]     │      [OK]     │      [OK]       │
│ REPEATABLE READ      │     [X]     │      [X]     │      [OK]       │
│ SERIALIZABLE         │     [X]     │      [X]     │      [X]       │
└──────────────────────┴────────────┴─────────────┴───────────────┘

InnoDB PAR DÉFAUT : REPEATABLE READ

CONFIGURATION :
# Session courante
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# Globale
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

# my.cnf
[mysqld]
transaction_isolation = READ-COMMITTED


1. READ UNCOMMITTED (Lecture non validée)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PROBLÈME : DIRTY READ (lecture sale)
-> Transaction A lit des données modifiées par Transaction B NON COMMIT

SCÉNARIO :

T1 : Transaction A lit balance
SELECT balance FROM accounts WHERE id = 1;
-> balance = 1000

T2 : Transaction B débite
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
# Pas encore de COMMIT

T3 : Transaction A relit
SELECT balance FROM accounts WHERE id = 1;
-> balance = 500  <- DIRTY READ ! (Transaction B pas commit)

T4 : Transaction B annule
ROLLBACK;  # Finalement, annule

-> Transaction A a vu 500 (valeur fantôme) !
-> Incohérent si A prend des décisions basées sur 500

USAGE :
-> JAMAIS en production !
-> Seulement pour rapports approximatifs où précision pas critique


2. READ COMMITTED (Lecture validée)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GARANTIE : Pas de dirty read
-> On lit seulement les données COMMIT

PROBLÈME : NON-REPEATABLE READ (lecture non reproductible)

SCÉNARIO :

T1 : Transaction A lit
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-> balance = 1000

T2 : Transaction B modifie et commit
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

T3 : Transaction A relit (même requête)
SELECT balance FROM accounts WHERE id = 1;
-> balance = 500  <- Changé pendant la transaction !

-> Problème pour calculs qui assument cohérence dans une transaction

USAGE :
-> PostgreSQL par défaut
-> Oracle par défaut
-> Acceptable pour beaucoup d'applications web
-> Plus de concurrence que REPEATABLE READ


3. REPEATABLE READ (Lecture reproductible)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

InnoDB PAR DÉFAUT [OK]

GARANTIE : Snapshot cohérent
-> La transaction voit un "snapshot" au moment de son démarrage
-> Modifications ultérieures par d'autres transactions invisibles

MÉCANISME : MVCC (Multi-Version Concurrency Control)
-> Via UNDO LOG (vu en Partie 6)

SCÉNARIO :

T1 : Transaction A démarre
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-> balance = 1000
-> Snapshot pris (trx_id = 100)

T2 : Transaction B modifie et commit
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;  # (trx_id = 101)
COMMIT;

T3 : Transaction A relit
SELECT balance FROM accounts WHERE id = 1;
-> InnoDB voit : DB_TRX_ID = 101 (postérieur à 100)
-> Suit DB_ROLL_PTR vers undo log
-> Reconstruit ancienne version : balance = 1000
-> Transaction A lit toujours : 1000 [OK]

COHÉRENCE GARANTIE dans la transaction !

PROBLÈME : PHANTOM READ (lecture fantôme)

SCÉNARIO :

T1 : Transaction A compte les lignes
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-> 10 lignes

T2 : Transaction B insère et commit
START TRANSACTION;
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

T3 : Transaction A recalcule
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-> 10 lignes (en InnoDB) [OK]

MAIS :

T4 : Transaction A veut modifier toutes les lignes
UPDATE orders SET priority = 'high' WHERE status = 'pending';
-> 11 lignes modifiées ! <- PHANTOM (la nouvelle ligne est modifiée)

InnoDB RÉSOUT PARTIELLEMENT avec "next-key locks"

USAGE :
-> Défaut InnoDB
-> Bon compromis : cohérence + performance
-> Recommandé pour la plupart des applications


4. SERIALIZABLE (Sérialisable)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GARANTIE MAXIMALE : Comme si transactions s'exécutaient en série (une après l'autre)

MÉCANISME InnoDB :
-> Tous les SELECT deviennent SELECT ... FOR SHARE (shared lock)
-> Bloque les écritures concurrentes

SCÉNARIO :

T1 : Transaction A sélectionne
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
-> Shared lock posé

T2 : Transaction B essaie de modifier
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
-> BLOQUÉ ! Attend que Transaction A finisse

T3 : Transaction A commit
COMMIT;
-> Lock relâché

T4 : Transaction B peut continuer
-> UPDATE s'exécute

AVANTAGES :
[OK] Aucune anomalie possible
[OK] Cohérence maximale

INCONVÉNIENTS :
[X] Performance très réduite
[X] Beaucoup de blocages (deadlocks possibles)
[X] Concurrence minimale

USAGE :
-> Transactions financières critiques
-> Où cohérence absolue prime sur performance


VERROUS (LOCKS) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

TYPES DE VERROUS InnoDB :

1. SHARED LOCK (S) - Verrou partagé
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Lecture
-> Plusieurs transactions peuvent avoir S lock simultanément
-> Bloque les X locks (exclusive)

SELECT ... FOR SHARE;  # Ou LOCK IN SHARE MODE (ancien)

USAGE :
START TRANSACTION;
SELECT stock FROM products WHERE id = 5 FOR SHARE;
-- Vérifier stock disponible
-- Autre transaction ne peut pas modifier le stock (bloquée)
INSERT INTO order_items (product_id, qty) VALUES (5, 2);
COMMIT;

2. EXCLUSIVE LOCK (X) - Verrou exclusif
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Écriture
-> Bloque tous autres locks (S et X)

SELECT ... FOR UPDATE;

UPDATE, DELETE, INSERT : X lock automatique

USAGE :
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Verrou exclusif : personne d'autre ne peut lire ou modifier
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

3. INTENTION LOCKS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> IS (Intention Shared) : intention de poser S lock sur lignes
-> IX (Intention Exclusive) : intention de poser X lock sur lignes
-> Posés au niveau TABLE
-> Performance : évite de scanner toutes les lignes pour vérifier locks

4. GAP LOCKS et NEXT-KEY LOCKS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> En REPEATABLE READ
-> Verrous sur les "gaps" (espaces) entre les enregistrements
-> Empêche phantom reads

EXEMPLE :
Table : id = [1, 5, 10]

SELECT * FROM t WHERE id > 3 FOR UPDATE;
-> Lock sur : 5, 10 (existants)
-> + Gap lock sur : ]3, 5[, ]5, 10[, ]10, ∞[
-> Empêche INSERT de 4, 7, 15, etc.


DEADLOCKS (Interblocages)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SCÉNARIO CLASSIQUE :

T1 : Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  # Lock row 1
-- Attendre un peu
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  # Veut lock row 2

T2 : Transaction B (en parallèle)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   # Lock row 2
-- Attendre un peu
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   # Veut lock row 1

RÉSULTAT :
-> Transaction A attend lock sur row 2 (détenu par B)
-> Transaction B attend lock sur row 1 (détenu par A)
-> DEADLOCK ! [SKULL_AND_CROSSBONES]

DÉTECTION InnoDB :
-> InnoDB détecte automatiquement (graphe de dépendances)
-> Choisit une "victime" (généralement la plus petite transaction)
-> ROLLBACK de la victime
-> L'autre continue

ERREUR RETOURNÉE :
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ÉVITER LES DEADLOCKS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] Accéder aux tables/lignes dans le MÊME ORDRE dans toutes transactions
[OK] Transactions courtes
[OK] Utiliser des index (réduire nombre de locks)
[OK] Retry logic dans l'application

CODE PYTHON avec retry :
import mysql.connector
import time

def transfer_money(conn, from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor = conn.cursor()
            cursor.execute("START TRANSACTION")
            
            # IMPORTANT : Ordre constant (toujours plus petit id en premier)
            if from_id < to_id:
                cursor.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                cursor.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
            else:
                cursor.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
                cursor.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
            
            conn.commit()
            return True
            
        except mysql.connector.Error as err:
            if err.errno == 1213:  # Deadlock
                conn.rollback()
                if attempt < max_retries - 1:
                    time.sleep(0.1 * (attempt + 1))  # Backoff exponentiel
                    continue
                else:
                    raise
            else:
                conn.rollback()
                raise
    
    return False
"""



# [OK] PARTIE 10-16 AJOUTÉES - Continuons avec le fichier complet...



# [OK] PARTIE 10 : JOINS ET RELATIONS

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    JOINS : RELIER LES TABLES                           │
└────────────────────────────────────────────────────────────────────────┘

MODÈLE RELATIONNEL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Au lieu d'une table géante avec duplication :

[X] MAUVAIS DESIGN (table dénormalisée) :
┌────────┬──────────┬──────────────┬──────────────┬───────────────┐
│ order_id│ customer │ customer_email│ product_name │ category_name │
├────────┼──────────┼──────────────┼──────────────┼───────────────┤
│ 1      │ Jean     │ jean@...      │ T-Shirt      │ Vêtements     │
│ 2      │ Jean     │ jean@...      │ Pantalon     │ Vêtements     │  <- Duplication
│ 3      │ Marie    │ marie@...     │ T-Shirt      │ Vêtements     │  <- Duplication
└────────┴──────────┴──────────────┴──────────────┴───────────────┘

Problèmes :
-> Duplication massive (espace disque)
-> Anomalies de mise à jour (changer email -> modifier toutes les lignes)
-> Incohérences possibles

[OK] BON DESIGN (normalisé avec relations) :

customers:
┌────┬──────┬──────────────┐
│ id │ name │ email        │
├────┼──────┼──────────────┤
│ 1  │ Jean │ jean@...     │
│ 2  │ Marie│ marie@...    │
└────┴──────┴──────────────┘

products:
┌────┬───────────┬─────────────┐
│ id │ name      │ category_id │
├────┼───────────┼─────────────┤
│ 5  │ T-Shirt   │ 10          │
│ 8  │ Pantalon  │ 10          │
└────┴───────────┴─────────────┘

categories:
┌────┬───────────┐
│ id │ name      │
├────┼───────────┤
│ 10 │ Vêtements │
└────┴───────────┘

orders:
┌────┬─────────────┬────────────┬──────┐
│ id │ customer_id │ product_id │ qty  │
├────┼─────────────┼────────────┼──────┤
│ 1  │ 1           │ 5          │ 2    │
│ 2  │ 1           │ 8          │ 1    │
│ 3  │ 2           │ 5          │ 1    │
└────┴─────────────┴────────────┴──────┘

Avantages :
[OK] Pas de duplication
[OK] Cohérence garantie
[OK] Espace optimisé
[OK] Modifications faciles


TYPES DE RELATIONS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. ONE-TO-MANY (1:N) - Un-à-plusieurs
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Un client -> plusieurs commandes
Une catégorie -> plusieurs produits

CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  category_id INT NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

2. MANY-TO-MANY (N:M) - Plusieurs-à-plusieurs
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Étudiants <-> Cours (un étudiant suit plusieurs cours, un cours a plusieurs étudiants)
Produits <-> Commandes

NÉCESSITE TABLE DE JONCTION :

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

CREATE TABLE courses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  grade DECIMAL(5,2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

3. ONE-TO-ONE (1:1) - Un-à-un
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Utilisateur <-> Profil détaillé (rarement utilisé, on peut tout mettre dans une table)

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE
);

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,  # PK = FK (garantit 1:1)
  bio TEXT,
  avatar_url VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);


FOREIGN KEY (Clés étrangères) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GARANTIT L'INTÉGRITÉ RÉFÉRENTIELLE !

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

ACTIONS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ON DELETE :
-> RESTRICT (défaut) : Empêche suppression si références existent
-> CASCADE : Supprime en cascade les lignes enfants
-> SET NULL : Met à NULL la FK
-> NO ACTION : Comme RESTRICT

ON UPDATE :
-> CASCADE (défaut) : Propage la modification
-> RESTRICT : Empêche modification
-> SET NULL : Met à NULL

EXEMPLES :

ON DELETE RESTRICT :
DELETE FROM customers WHERE id = 1;
-> ERREUR si orders avec customer_id = 1 existent

ON DELETE CASCADE :
DELETE FROM customers WHERE id = 1;
-> Supprime aussi tous orders avec customer_id = 1 [OK]

ON DELETE SET NULL :
DELETE FROM customers WHERE id = 1;
-> orders.customer_id -> NULL pour les orders concernés

USAGE :
-> RESTRICT : Protection par défaut
-> CASCADE : Pour relations fortes (user -> user_profile)
-> SET NULL : Pour relations optionnelles


TYPES DE JOINS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

DONNÉES DE TEST :

customers:
┌────┬────────┐
│ id │ name   │
├────┼────────┤
│ 1  │ Alice  │
│ 2  │ Bob    │
│ 3  │ Carol  │
└────┴────────┘

orders:
┌────┬─────────────┬────────┐
│ id │ customer_id │ total  │
├────┼─────────────┼────────┤
│ 1  │ 1           │ 100.00 │
│ 2  │ 1           │ 50.00  │
│ 3  │ 2           │ 200.00 │
│ 4  │ NULL        │ 75.00  │  <- Commande sans client (erreur de données)
└────┴─────────────┴────────┘


1. INNER JOIN (Jointure interne)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Retourne seulement les lignes avec correspondance dans LES DEUX tables

SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

RÉSULTAT :
┌────────┬──────────┬────────┐
│ name   │ order_id │ total  │
├────────┼──────────┼────────┤
│ Alice  │ 1        │ 100.00 │
│ Alice  │ 2        │ 50.00  │
│ Bob    │ 3        │ 200.00 │
└────────┴──────────┴────────┘

-> Carol (pas de commandes) : absente
-> Order 4 (sans client) : absente

2. LEFT JOIN (ou LEFT OUTER JOIN)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Retourne TOUTES les lignes de la table de GAUCHE + correspondances de droite

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

RÉSULTAT :
┌────────┬──────────┬────────┐
│ name   │ order_id │ total  │
├────────┼──────────┼────────┤
│ Alice  │ 1        │ 100.00 │
│ Alice  │ 2        │ 50.00  │
│ Bob    │ 3        │ 200.00 │
│ Carol  │ NULL     │ NULL   │  <- Pas de commandes -> NULL
└────────┴──────────┴────────┘

USAGE COURANT : Trouver clients SANS commandes
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
-> Carol


3. SELF JOIN (Auto-jointure)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Joindre une table avec ELLE-MÊME

EXEMPLE : Hiérarchie d'employés

employees:
┌────┬────────┬────────────┐
│ id │ name   │ manager_id │
├────┼────────┼────────────┤
│ 1  │ Alice  │ NULL       │  <- PDG
│ 2  │ Bob    │ 1          │  <- Manager de Alice
│ 3  │ Carol  │ 1          │
│ 4  │ Dave   │ 2          │  <- Subordonné de Bob
└────┴────────┴────────────┘

REQUÊTE : Lister employés avec leur manager

SELECT 
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

RÉSULTAT :
┌──────────┬─────────┐
│ employee │ manager │
├──────────┼─────────┤
│ Alice    │ NULL    │
│ Bob      │ Alice   │
│ Carol    │ Alice   │
│ Dave     │ Bob     │
└──────────┴─────────┘


JOINS MULTIPLES ET OPTIMISATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SELECT 
  o.id AS order_id,
  c.name AS customer,
  p.name AS product,
  cat.name AS category,
  oi.qty,
  oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
WHERE o.created_at >= '2024-01-01'
ORDER BY o.id, oi.id;

RÈGLES D'OPTIMISATION :
[OK] INDEX sur TOUTES les colonnes de JOIN (FK)
[OK] Filtrer tôt (WHERE avant JOIN si possible)
[OK] Utiliser EXPLAIN pour vérifier le plan
"""


# [OK] PARTIE 11 : RÉPLICATION ET HAUTE DISPONIBILITÉ

"""
┌────────────────────────────────────────────────────────────────────────┐
│           RÉPLICATION : DISPONIBILITÉ ET PERFORMANCE                   │
└────────────────────────────────────────────────────────────────────────┘

POURQUOI LA RÉPLICATION ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] HAUTE DISPONIBILITÉ (HA) :
-> Si serveur principal (master) tombe, un replica prend le relais

[OK] RÉPARTITION DE CHARGE (Load Balancing) :
-> Lectures sur replicas (slaves)
-> Écritures sur master
-> Ratio lecture/écriture souvent 80/20 ou 90/10

[OK] SAUVEGARDES :
-> Backup depuis replica (pas d'impact sur master)

[OK] ANALYSE/REPORTING :
-> Requêtes analytiques lourdes sur replica dédié
-> Pas d'impact sur production

[OK] RÉPARTITION GÉOGRAPHIQUE :
-> Replica proche des utilisateurs (latence réduite)
-> Ex: Master à Paris, Replica à Dakar


ARCHITECTURE RÉPLICATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

MASTER-SLAVE (Source-Replica) :

                    ┌──────────────────┐
                    │      MASTER      │
                    │   (Source)       │
                    │  ┌──────────┐    │
                    │  │  Writes  │    │
                    │  └──────────┘    │
                    │  ┌──────────┐    │
                    │  │  Binary  │    │
                    │  │   Log    │    │
                    │  └────┬─────┘    │
                    └───────┼──────────┘
                            │
            ┌───────────────┼───────────────┐
            │               │               │
            [BLACK_DOWN-POINTING_TRIANGLE]               [BLACK_DOWN-POINTING_TRIANGLE]               [BLACK_DOWN-POINTING_TRIANGLE]
    ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
    │   REPLICA 1  │ │   REPLICA 2  │ │   REPLICA 3  │
    │   (Slave)    │ │   (Slave)    │ │   (Slave)    │
    │ ┌──────────┐ │ │ ┌──────────┐ │ │ ┌──────────┐ │
    │ │  Reads   │ │ │ │  Reads   │ │ │ │  Reads   │ │
    │ └──────────┘ │ │ └──────────┘ │ │ └──────────┘ │
    └──────────────┘ └──────────────┘ └──────────────┘

FONCTIONNEMENT :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. Client écrit sur MASTER
   INSERT INTO users VALUES (...)

2. MASTER écrit dans BINARY LOG
   -> Événement: INSERT INTO users ...

3. REPLICA se connecte au MASTER (IO Thread)
   -> Lit les événements du binary log
   -> Écrit dans son RELAY LOG

4. REPLICA applique les événements (SQL Thread)
   -> Lit relay log
   -> Exécute INSERT INTO users ...

5. REPLICA à jour [OK]


CONFIGURATION RÉPLICATION (Étapes détaillées) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ÉTAPE 1 : CONFIGURATION MASTER

# /etc/mysql/my.cnf
[mysqld]
server-id = 1                    # Unique dans le cluster
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW              # ROW, STATEMENT, MIXED
binlog_expire_logs_seconds = 604800  # 7 jours

Redémarrer MySQL

ÉTAPE 2 : CRÉER UTILISATEUR RÉPLICATION

CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

ÉTAPE 3 : NOTER POSITION BINLOG

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      154 |              |                  |
+------------------+----------+--------------+------------------+

ÉTAPE 4 : CONFIGURATION REPLICA

# /etc/mysql/my.cnf
[mysqld]
server-id = 2                    # Différent du master !
read_only = 1                    # Empêche écritures (sauf super user)
relay_log = /var/log/mysql/relay-bin.log

ÉTAPE 5 : CONFIGURER RÉPLICATION

CHANGE MASTER TO
  MASTER_HOST='51.124.45.67',
  MASTER_USER='replicator',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=154;

START SLAVE;

ÉTAPE 6 : VÉRIFIER

SHOW SLAVE STATUS\G

*************************** 1. row ***************************
             Slave_IO_Running: Yes  <- IMPORTANT
            Slave_SQL_Running: Yes  <- IMPORTANT
          Seconds_Behind_Master: 0   <- Retard en secondes


GTID (Global Transaction Identifier) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

MySQL ≥5.6, SIMPLIFIE LA RÉPLICATION !

# Master et Replica
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
server-id = 1  # Unique

CHANGE MASTER TO
  MASTER_HOST='51.124.45.67',
  MASTER_USER='replicator',
  MASTER_PASSWORD='...',
  MASTER_AUTO_POSITION=1;  <- Utilise GTID

START SLAVE;
"""


# [OK] PARTIE 12 : PROCÉDURES STOCKÉES, TRIGGERS, VUES

"""
┌────────────────────────────────────────────────────────────────────────┐
│              PROCÉDURES STOCKÉES ET TRIGGERS                           │
└────────────────────────────────────────────────────────────────────────┘

PROCÉDURES STOCKÉES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Programme SQL stocké dans la base de données

AVANTAGES :
[OK] Logique métier centralisée
[OK] Performance (code pré-compilé)
[OK] Sécurité (GRANT EXECUTE)
[OK] Réduction trafic réseau

DÉLIMITEUR nécessaire car ; est utilisé dans la procédure :

DELIMITER $$

CREATE PROCEDURE transfer_money(
  IN from_account_id INT,
  IN to_account_id INT,
  IN amount DECIMAL(10,2)
)
BEGIN
  DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';
  DECLARE from_balance DECIMAL(10,2);
  
  START TRANSACTION;
  
  SELECT balance INTO from_balance
  FROM accounts
  WHERE id = from_account_id
  FOR UPDATE;
  
  IF from_balance < amount THEN
    SIGNAL insufficient_funds
      SET MESSAGE_TEXT = 'Solde insuffisant';
  END IF;
  
  UPDATE accounts SET balance = balance - amount WHERE id = from_account_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account_id;
  
  COMMIT;
END$$

DELIMITER ;

APPEL :
CALL transfer_money(1, 2, 100.00);


TRIGGERS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Code exécuté AUTOMATIQUEMENT lors d'événements

EXEMPLE : Audit log

CREATE TABLE audit_log (
  id INT PRIMARY KEY AUTO_INCREMENT,
  table_name VARCHAR(50),
  operation VARCHAR(10),
  user VARCHAR(100),
  timestamp DATETIME,
  old_data JSON,
  new_data JSON
);

DELIMITER $$

CREATE TRIGGER users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, operation, user, timestamp, old_data, new_data)
  VALUES (
    'users',
    'UPDATE',
    USER(),
    NOW(),
    JSON_OBJECT('id', OLD.id, 'email', OLD.email),
    JSON_OBJECT('id', NEW.id, 'email', NEW.email)
  );
END$$

DELIMITER ;


VUES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Requête SELECT stockée comme "table virtuelle"

CREATE VIEW customer_orders AS
SELECT 
  c.id AS customer_id,
  c.name AS customer_name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

USAGE :
SELECT * FROM customer_orders WHERE order_count > 5;
"""


# [OK] PARTIE 13 : SÉCURITÉ

"""
┌────────────────────────────────────────────────────────────────────────┐
│                        SÉCURITÉ MySQL                                  │
└────────────────────────────────────────────────────────────────────────┘

GESTION DES UTILISATEURS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CRÉER UN UTILISATEUR :
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecurePass123!';

# Depuis un réseau spécifique
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';

# Depuis n'importe où (attention !)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'SecurePass123!';

MODIFIER MOT DE PASSE :
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPassword456!';

SUPPRIMER :
DROP USER 'appuser'@'localhost';


PRIVILÈGES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

NIVEAUX :
1. Global : *.* (toutes les bases)
2. Database : dbname.*
3. Table : dbname.tablename
4. Column : colonnes spécifiques

PRIVILÈGES COURANTS :
-> SELECT : Lecture
-> INSERT : Insertion
-> UPDATE : Mise à jour
-> DELETE : Suppression
-> CREATE : Créer tables/bases
-> DROP : Supprimer tables/bases
-> INDEX : Créer/supprimer index
-> ALTER : Modifier structure
-> GRANT OPTION : Donner privilèges à d'autres
-> ALL PRIVILEGES : Tous les privilèges

EXEMPLES :

# Lecture seule sur une base
GRANT SELECT ON ecommerce.* TO 'readonly'@'%';

# Lecture/écriture sur tables spécifiques
GRANT SELECT, INSERT, UPDATE, DELETE 
ON ecommerce.products TO 'appuser'@'localhost';

# Admin complet sur une base
GRANT ALL PRIVILEGES ON ecommerce.* TO 'admin'@'localhost';

# Super admin (toutes bases, attention !)
GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost' WITH GRANT OPTION;

APPLIQUER :
FLUSH PRIVILEGES;

VOIR PRIVILÈGES :
SHOW GRANTS FOR 'appuser'@'localhost';

RÉVOQUER :
REVOKE INSERT, UPDATE ON ecommerce.* FROM 'appuser'@'localhost';


PRINCIPE DU MOINDRE PRIVILÈGE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] Application web :
-> SELECT, INSERT, UPDATE, DELETE uniquement
-> PAS de DROP, CREATE, ALTER

CREATE USER 'webapp'@'192.168.1.%' IDENTIFIED BY 'SecurePass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* TO 'webapp'@'192.168.1.%';

[OK] Service de reporting :
-> SELECT uniquement (lecture seule)

CREATE USER 'reports'@'10.0.0.%' IDENTIFIED BY 'ReportPass!';
GRANT SELECT ON ecommerce.* TO 'reports'@'10.0.0.%';

[OK] Admin/DBA :
-> Tous privilèges mais depuis IP spécifique

CREATE USER 'dba'@'203.0.113.50' IDENTIFIED BY 'DbaPass!';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'203.0.113.50' WITH GRANT OPTION;


SÉCURISER MYSQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. DÉSACTIVER UTILISATEUR ROOT DISTANT :
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

2. SUPPRIMER UTILISATEURS ANONYMES :
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

3. SUPPRIMER BASE DE TEST :
DROP DATABASE IF EXISTS test;

4. BIND ADDRESS (écouter sur interface spécifique) :
[mysqld]
bind-address = 192.168.1.10  # IP interne
# bind-address = 0.0.0.0  # Toutes interfaces (danger si pas firewall)

5. CHIFFREMENT SSL/TLS :

# Générer certificats
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

# my.cnf
[mysqld]
require_secure_transport = ON  # Force SSL

# Créer utilisateur SSL obligatoire
CREATE USER 'secureuser'@'%' 
IDENTIFIED BY 'Pass!' 
REQUIRE SSL;

6. MOT DE PASSE FORT :
[mysqld]
validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1

7. LIMITER TENTATIVES DE CONNEXION :
CREATE USER 'limited'@'%' 
IDENTIFIED BY 'Pass!'
WITH MAX_USER_CONNECTIONS 10
     MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 100
     MAX_CONNECTIONS_PER_HOUR 50;


AUDIT ET LOGGING :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

GENERAL LOG (toutes les requêtes - dev uniquement !) :
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

SLOW QUERY LOG :
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

ERROR LOG :
[mysqld]
log_error = /var/log/mysql/error.log

AUDIT PLUGIN (Enterprise) :
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = ALL;
SET GLOBAL audit_log_format = JSON;
"""


# [OK] PARTIE 14 : MONITORING ET MAINTENANCE

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    MONITORING ET MAINTENANCE                           │
└────────────────────────────────────────────────────────────────────────┘

COMMANDES SHOW ESSENTIELLES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# État général
SHOW STATUS;

# Variables de configuration
SHOW VARIABLES;
SHOW VARIABLES LIKE 'innodb%';

# Processus actifs
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

# État InnoDB
SHOW ENGINE INNODB STATUS\G

# Bases de données
SHOW DATABASES;

# Tables
SHOW TABLES;
SHOW TABLE STATUS;

# Index
SHOW INDEX FROM products;

# Utilisateurs
SELECT User, Host FROM mysql.user;


MÉTRIQUES IMPORTANTES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. CONNEXIONS :
SHOW STATUS LIKE 'Threads_connected';   # Connexions actuelles
SHOW STATUS LIKE 'Max_used_connections';  # Max atteint
SHOW VARIABLES LIKE 'max_connections';   # Limite

2. REQUÊTES :
SHOW STATUS LIKE 'Questions';           # Total requêtes
SHOW STATUS LIKE 'Queries';             # Incluant commandes internes
SHOW STATUS LIKE 'Com_select';          # SELECT
SHOW STATUS LIKE 'Com_insert';          # INSERT
SHOW STATUS LIKE 'Com_update';          # UPDATE
SHOW STATUS LIKE 'Com_delete';          # DELETE

3. BUFFER POOL :
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  # Lectures totales
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          # Lectures disque
# Hit ratio : (read_requests - reads) / read_requests * 100
# Objectif : > 99%

4. LOCKS :
SHOW STATUS LIKE 'Innodb_row_lock_waits';      # Attentes
SHOW STATUS LIKE 'Innodb_row_lock_time';       # Temps total (ms)
SHOW STATUS LIKE 'Innodb_row_lock_time_avg';   # Temps moyen (ms)

5. TABLES TEMPORAIRES :
SHOW STATUS LIKE 'Created_tmp_tables';         # Totales
SHOW STATUS LIKE 'Created_tmp_disk_tables';    # Sur disque (mauvais)


PERFORMANCE SCHEMA :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Activé par défaut ≥8.0

# Top requêtes par temps total
SELECT 
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT/1000000000000, 2) AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

# Locks en cours
SELECT * FROM performance_schema.data_locks;

# I/O par fichier
SELECT 
  FILE_NAME,
  EVENT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  SUM_NUMBER_OF_BYTES_READ,
  SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 10;


SYS SCHEMA (vues simplifiées) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Requêtes lentes
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

# Tables non utilisées
SELECT * FROM sys.schema_unused_indexes;

# Analyse I/O
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

# Utilisateurs par activité
SELECT * FROM sys.user_summary;


SAUVEGARDES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. MYSQLDUMP (logique) :

# Toutes les bases
mysqldump --all-databases --single-transaction \
  --master-data=2 --flush-logs \
  -u root -p > backup.sql

# Une base
mysqldump ecommerce --single-transaction -u root -p > ecommerce.sql

# Restaurer
mysql -u root -p < backup.sql

AVANTAGES :
[OK] Portable (fichier SQL texte)
[OK] Sélectif (tables spécifiques)

INCONVÉNIENTS :
[X] Lent pour grandes bases
[X] Verrouille brièvement les tables

2. MYSQLPUMP (parallèle, MySQL ≥5.7) :

mysqlpump --default-parallelism=4 \
  --all-databases > backup.sql

3. XTRABACKUP (physique, Percona) :

# Backup
xtrabackup --backup --target-dir=/backup/full

# Restaurer
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

AVANTAGES :
[OK] Très rapide
[OK] Backup à chaud (pas d'arrêt)
[OK] Incrémental possible

4. BINLOG PITR (Point-In-Time Recovery) :

# Sauvegarder binlogs
mysqlbinlog mysql-bin.000001 > binlog_000001.sql

# Restaurer jusqu'à un moment précis
mysqlbinlog --stop-datetime='2024-01-16 10:30:00' \
  mysql-bin.000001 | mysql -u root -p


MAINTENANCE RÉGULIÈRE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Analyser tables (mettre à jour statistiques)
ANALYZE TABLE products, orders;

# Optimiser (défragmenter)
OPTIMIZE TABLE products;

# Vérifier erreurs
CHECK TABLE products;

# Réparer
REPAIR TABLE products;

# Purger binlogs anciens
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';


SCRIPT MONITORING PYTHON :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

import mysql.connector
import time

def monitor_mysql(host, user, password):
    conn = mysql.connector.connect(
        host=host, user=user, password=password
    )
    cursor = conn.cursor(dictionary=True)
    
    # Connexions
    cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
    connections = cursor.fetchone()['Value']
    
    cursor.execute("SHOW VARIABLES LIKE 'max_connections'")
    max_conn = cursor.fetchone()['Value']
    
    conn_usage = (int(connections) / int(max_conn)) * 100
    
    # Buffer pool hit ratio
    cursor.execute("SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests'")
    read_requests = int(cursor.fetchone()['Value'])
    
    cursor.execute("SHOW STATUS LIKE 'Innodb_buffer_pool_reads'")
    disk_reads = int(cursor.fetchone()['Value'])
    
    if read_requests > 0:
        hit_ratio = ((read_requests - disk_reads) / read_requests) * 100
    else:
        hit_ratio = 0
    
    # Slow queries
    cursor.execute("SHOW STATUS LIKE 'Slow_queries'")
    slow_queries = cursor.fetchone()['Value']
    
    print(f"Connexions: {connections}/{max_conn} ({conn_usage:.1f}%)")
    print(f"Buffer Pool Hit Ratio: {hit_ratio:.2f}%")
    print(f"Slow Queries: {slow_queries}")
    
    # Alertes
    if conn_usage > 80:
        print("[ATTENTION] ALERTE: Usage connexions > 80%")
    
    if hit_ratio < 95:
        print("[ATTENTION] ALERTE: Buffer pool hit ratio < 95%")
    
    cursor.close()
    conn.close()

# Exécuter
monitor_mysql('localhost', 'monitor', 'password')
"""


# [OK] PARTIE 15 : PREMIERS PAS PRATIQUES

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    PREMIERS PAS AVEC MySQL                             │
└────────────────────────────────────────────────────────────────────────┘

CONNEXION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

# Ligne de commande
mysql -u root -p
mysql -h 192.168.1.10 -u appuser -p ecommerce

CRÉER UNE BASE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CREATE DATABASE ecommerce 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE ecommerce;


CRÉER DES TABLES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  category_id INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_category (category_id)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  INDEX idx_customer (customer_id),
  INDEX idx_status (status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;


CRUD OPERATIONS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- CREATE (Insert)
INSERT INTO customers (name, email) VALUES ('Jean Dupont', 'jean@example.com');

INSERT INTO products (name, price, stock) VALUES 
  ('T-Shirt', 25.00, 100),
  ('Pantalon', 45.00, 50),
  ('Chaussures', 80.00, 30);

-- READ (Select)
SELECT * FROM customers;
SELECT name, price FROM products WHERE price < 50;
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

-- UPDATE
UPDATE products SET stock = stock - 5 WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 10;

-- DELETE
DELETE FROM customers WHERE id = 5;


REQUÊTES COURANTES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- Pagination
SELECT * FROM products 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- Page 3 (10 par page)

-- Recherche
SELECT * FROM products 
WHERE name LIKE '%shirt%' 
  OR name LIKE '%pantalon%';

-- Agrégation
SELECT 
  COUNT(*) AS total_orders,
  SUM(total) AS total_revenue,
  AVG(total) AS avg_order_value,
  MAX(total) AS max_order,
  MIN(total) AS min_order
FROM orders
WHERE status = 'paid';

-- Group By
SELECT 
  status,
  COUNT(*) AS count,
  SUM(total) AS total
FROM orders
GROUP BY status;

-- Having (filtre après GROUP BY)
SELECT 
  customer_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING order_count > 5;

-- Sous-requête
SELECT name, email
FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id 
  FROM orders 
  WHERE total > 1000
);
"""


# [OK] PARTIE 16 : CAS D'USAGE RÉELS ET EXEMPLES COMPLETS

"""
┌────────────────────────────────────────────────────────────────────────┐
│                  CAS D'USAGE RÉELS ET INTÉGRATIONS                     │
└────────────────────────────────────────────────────────────────────────┘

EXEMPLE 1 : APPLICATION NODE.JS / EXPRESS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Installation :
npm install mysql2 express dotenv

Configuration (.env) :
DB_HOST=localhost
DB_USER=appuser
DB_PASSWORD=SecurePass123!
DB_NAME=ecommerce
DB_PORT=3306

Code (app.js) :
const express = require('express');
const mysql = require('mysql2/promise');
require('dotenv').config();

const app = express();
app.use(express.json());

// Pool de connexions
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Routes API

// GET /api/products
app.get('/api/products', async (req, res) => {
  try {
    const [rows] = await pool.query(
      'SELECT id, name, price, stock FROM products WHERE stock > 0'
    );
    res.json(rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Database error' });
  }
});

// GET /api/products/:id
app.get('/api/products/:id', async (req, res) => {
  try {
    const [rows] = await pool.query(
      'SELECT * FROM products WHERE id = ?',
      [req.params.id]
    );
    
    if (rows.length === 0) {
      return res.status(404).json({ error: 'Product not found' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Database error' });
  }
});

// POST /api/orders
app.post('/api/orders', async (req, res) => {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const { customer_id, items } = req.body;
    
    // Créer la commande
    const [result] = await connection.query(
      'INSERT INTO orders (customer_id, total, status) VALUES (?, 0, "pending")',
      [customer_id]
    );
    
    const order_id = result.insertId;
    let total = 0;
    
    // Ajouter les articles
    for (const item of items) {
      const [products] = await connection.query(
        'SELECT price, stock FROM products WHERE id = ? FOR UPDATE',
        [item.product_id]
      );
      
      if (products.length === 0) {
        throw new Error(`Product ${item.product_id} not found`);
      }
      
      const product = products[0];
      
      if (product.stock < item.qty) {
        throw new Error(`Insufficient stock for product ${item.product_id}`);
      }
      
      // Insérer ligne de commande
      await connection.query(
        'INSERT INTO order_items (order_id, product_id, qty, price) VALUES (?, ?, ?, ?)',
        [order_id, item.product_id, item.qty, product.price]
      );
      
      // Déduire du stock
      await connection.query(
        'UPDATE products SET stock = stock - ? WHERE id = ?',
        [item.qty, item.product_id]
      );
      
      total += product.price * item.qty;
    }
    
    // Mettre à jour le total
    await connection.query(
      'UPDATE orders SET total = ? WHERE id = ?',
      [total, order_id]
    );
    
    await connection.commit();
    
    res.status(201).json({ order_id, total });
    
  } catch (error) {
    await connection.rollback();
    console.error(error);
    res.status(400).json({ error: error.message });
  } finally {
    connection.release();
  }
});

app.listen(3000, () => {
  console.log('API running on port 3000');
});


EXEMPLE 2 : APPLICATION PYTHON / FLASK
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Installation :
pip install flask mysql-connector-python python-dotenv

Configuration (.env) :
DB_HOST=localhost
DB_USER=appuser
DB_PASSWORD=SecurePass123!
DB_NAME=ecommerce

Code (app.py) :
from flask import Flask, jsonify, request
import mysql.connector
from mysql.connector import pooling
import os
from dotenv import load_dotenv

load_dotenv()

app = Flask(__name__)

# Pool de connexions
dbconfig = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME")
}

connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    **dbconfig
)

@app.route('/api/products', methods=['GET'])
def get_products():
    conn = connection_pool.get_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("SELECT * FROM products WHERE stock > 0")
        products = cursor.fetchall()
        return jsonify(products)
    finally:
        cursor.close()
        conn.close()

@app.route('/api/customers', methods=['POST'])
def create_customer():
    data = request.get_json()
    
    conn = connection_pool.get_connection()
    cursor = conn.cursor()
    
    try:
        cursor.execute(
            "INSERT INTO customers (name, email) VALUES (%s, %s)",
            (data['name'], data['email'])
        )
        conn.commit()
        
        return jsonify({
            'id': cursor.lastrowid,
            'name': data['name'],
            'email': data['email']
        }), 201
        
    except mysql.connector.IntegrityError as e:
        conn.rollback()
        return jsonify({'error': 'Email already exists'}), 400
    finally:
        cursor.close()
        conn.close()

@app.route('/api/stats/revenue', methods=['GET'])
def get_revenue_stats():
    conn = connection_pool.get_connection()
    cursor = conn.cursor(dictionary=True)
    
    try:
        cursor.execute("""
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as orders,
                SUM(total) as revenue
            FROM orders
            WHERE status = 'paid'
              AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            GROUP BY DATE(created_at)
            ORDER BY date DESC
        """)
        
        stats = cursor.fetchall()
        return jsonify(stats)
    finally:
        cursor.close()
        conn.close()

if __name__ == '__main__':
    app.run(debug=True)


EXEMPLE 3 : SCRIPT D'ANALYSE DE DONNÉES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Script d'analyse avec pandas :

import pandas as pd
import mysql.connector
import matplotlib.pyplot as plt

# Connexion
conn = mysql.connector.connect(
    host='localhost',
    user='reports',
    password='password',
    database='ecommerce'
)

# Requête 1 : Chiffre d'affaires mensuel
query1 = """
SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    SUM(total) as revenue,
    COUNT(*) as orders
FROM orders
WHERE status = 'paid'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month
"""

df_revenue = pd.read_sql(query1, conn)

# Graphique
plt.figure(figsize=(12, 6))
plt.plot(df_revenue['month'], df_revenue['revenue'], marker='o')
plt.title('Évolution du Chiffre d\'Affaires')
plt.xlabel('Mois')
plt.ylabel('Chiffre d\'Affaires (€)')
plt.xticks(rotation=45)
plt.grid(True)
plt.tight_layout()
plt.savefig('revenue.png')

# Requête 2 : Top 10 clients
query2 = """
SELECT 
    c.name,
    c.email,
    COUNT(o.id) as orders,
    SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 10
"""

df_top_customers = pd.read_sql(query2, conn)
print("\nTop 10 Clients:")
print(df_top_customers.to_string(index=False))

# Requête 3 : Produits populaires
query3 = """
SELECT 
    p.name,
    SUM(oi.qty) as units_sold,
    SUM(oi.qty * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY p.id, p.name
ORDER BY units_sold DESC
LIMIT 10
"""

df_products = pd.read_sql(query3, conn)
print("\nProduits les plus vendus:")
print(df_products.to_string(index=False))

conn.close()


CAS D'USAGE E-COMMERCE COMPLET :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SCHÉMA COMPLET :

CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;

-- Tables principales
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL,
  role ENUM('customer', 'admin') DEFAULT 'customer',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
);

CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  parent_id INT DEFAULT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(200) UNIQUE NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  category_id INT,
  image_url VARCHAR(500),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX idx_category (category_id),
  INDEX idx_slug (slug),
  FULLTEXT INDEX idx_search (name, description)
);

CREATE TABLE carts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id)
);

CREATE TABLE cart_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  cart_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT UNSIGNED NOT NULL DEFAULT 1,
  FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  UNIQUE KEY unique_cart_product (cart_id, product_id)
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  payment_method VARCHAR(50),
  shipping_address TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_user (user_id),
  INDEX idx_status (status),
  INDEX idx_created (created_at)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  qty INT UNSIGNED NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  rating TINYINT CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY unique_user_product (user_id, product_id),
  INDEX idx_product (product_id)
);


REQUÊTES BUSINESS COURANTES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- Recherche produits avec pertinence
SELECT 
  p.*,
  MATCH(p.name, p.description) AGAINST('smartphone' IN NATURAL LANGUAGE MODE) as relevance
FROM products p
WHERE MATCH(p.name, p.description) AGAINST('smartphone' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Produits avec moyenne des avis
SELECT 
  p.id,
  p.name,
  p.price,
  AVG(r.rating) as avg_rating,
  COUNT(r.id) as review_count
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.price
HAVING avg_rating >= 4
ORDER BY avg_rating DESC, review_count DESC;

-- Dashboard vendeur
SELECT 
  DATE(o.created_at) as date,
  COUNT(DISTINCT o.id) as orders,
  COUNT(DISTINCT o.user_id) as customers,
  SUM(o.total) as revenue,
  AVG(o.total) as avg_order_value
FROM orders o
WHERE o.status IN ('paid', 'processing', 'shipped', 'delivered')
  AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(o.created_at)
ORDER BY date DESC;

-- Produits en rupture de stock
SELECT 
  p.id,
  p.name,
  p.stock,
  COUNT(oi.id) as orders_last_30_days
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id 
  AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE p.stock < 10
GROUP BY p.id, p.name, p.stock
ORDER BY orders_last_30_days DESC;


FÉLICITATIONS ! [BRAVO]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Tu as maintenant un guide COMPLET et ULTRA-DÉTAILLÉ de MySQL couvrant :

[OK] Partie 1-4  : Fondamentaux, histoire, installation, architecture
[OK] Partie 5    : Voyage d'une requête (Dakar -> Londres avec timings)
[OK] Partie 6    : InnoDB en profondeur (buffer pool, logs, MVCC)
[OK] Partie 7    : Types de données (numériques, chaînes, dates, JSON)
[OK] Partie 8    : Index et optimisation (B+Tree, stratégies, EXPLAIN)
[OK] Partie 9    : Transactions ACID (isolation, locks, deadlocks)
[OK] Partie 10   : Joins et relations (INNER, LEFT, SELF, FK)
[OK] Partie 11   : Réplication et HA (master-slave, GTID)
[OK] Partie 12   : Procédures stockées, triggers, vues
[OK] Partie 13   : Sécurité (users, privileges, SSL)
[OK] Partie 14   : Monitoring et maintenance (SHOW, Performance Schema)
[OK] Partie 15   : Premiers pas pratiques (CRUD, requêtes courantes)
[OK] Partie 16   : Cas d'usage réels (Node.js, Python, analytics)

Ce guide couvre TOUT ce qu'un débutant doit savoir pour devenir compétent
en MySQL, avec des explications détaillées du POURQUOI, COMMENT et QUAND !
"""