# Optimisation des performances

### Introduction

Lorsqu'une base de données grandit, les temps de réponse peuvent se dégrader significativement. Une requête qui s'exécute en quelques millisecondes sur 1 000 lignes peut prendre plusieurs secondes sur 5 millions de lignes.

L'optimisation des performances repose sur **trois leviers** :

- **Les index** : accélérer les recherches
- **La configuration serveur** : adapter les ressources
- **Les requêtes** : écrire des requêtes efficaces

### Les index

#### Principe

Un **index** fonctionne comme l'index d'un livre : plutôt que de parcourir toutes les pages pour trouver un mot, on consulte l'index qui indique directement la bonne page.

Sans index, le SGBD effectue un **full table scan** : il parcourt *toutes* les lignes de la table pour trouver celles qui correspondent à la condition.

<p class="callout warning">Les index ont un **coût** : ils occupent de l'espace disque et ralentissent les opérations d'écriture (INSERT, UPDATE, DELETE) car l'index doit être mis à jour à chaque modification.</p>

#### Index simple

Un index simple porte sur **une seule colonne**.

```sql
-- Créer un index sur la colonne planete_origine
CREATE INDEX idx_planete ON transmissions(planete_origine);

-- Supprimer un index
DROP INDEX idx_planete ON transmissions;
```

Créez un index simple sur les colonnes fréquemment utilisées dans :

- Les clauses `WHERE`
- Les clauses `JOIN`
- Les clauses `ORDER BY`

#### Index composé

Un index composé porte sur **plusieurs colonnes**. L'ordre des colonnes est crucial.

```sql
-- Index sur deux colonnes
CREATE INDEX idx_planete_priorite ON transmissions(planete_origine, priorite);
```

<p class="callout info">Placez en premier dans l'index les colonnes les plus **sélectives** (celles qui filtrent le plus de lignes).</p>

#### Index unique

Un index unique garantit que les valeurs de la colonne sont **uniques** dans toute la table, tout en offrant les mêmes gains de performance qu'un index classique.

```sql
-- Garantir l'unicité du code de transmission
CREATE UNIQUE INDEX idx_code_unique ON transmissions(code_transmission);
```

**Différence avec PRIMARY KEY** :

- `PRIMARY KEY` : une seule par table, n'accepte pas les NULL
- `UNIQUE INDEX` : plusieurs possibles par table, accepte les NULL

#### Analyser une requête avec EXPLAIN

La commande `EXPLAIN` permet de voir comment MariaDB exécute une requête.

```sql
EXPLAIN SELECT * FROM transmissions WHERE planete_origine = 'Tatooine';
```

Résultat simplifié :

<table id="bkmrk-type-possible_keys-k"><thead><tr><th>type</th><th>possible\_keys</th><th>key</th><th>rows</th></tr></thead><tbody><tr><td>ALL</td><td>NULL</td><td>NULL</td><td>5000000</td></tr></tbody></table>

Les colonnes importantes :

- **type** : le type de scan 
    - `ALL` = full table scan (mauvais)
    - `ref` = utilisation d'un index (bon)
    - `range` = scan partiel d'index (bon)
    - `const` = résultat unique via PRIMARY KEY (excellent)
- **key** : l'index utilisé (NULL = aucun index)
- **rows** : estimation du nombre de lignes parcourues

<p class="callout warning">Si vous voyez `type = ALL` sur une grande table, c'est un signal d'alarme : un index est probablement nécessaire.</p>

### Exemple pratique : Registre galactique des communications

Mettons en pratique ces concepts avec une table de **5 millions de lignes**.

#### Contexte

L'Empire intercepte et enregistre toutes les communications de la galaxie. Chaque transmission est stockée avec sa date, son origine, sa priorité et son contenu.

#### Création de la table

```sql
CREATE TABLE transmissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code_transmission VARCHAR(20) NOT NULL,
    date_emission DATETIME NOT NULL,
    emetteur VARCHAR(100) NOT NULL,
    recepteur VARCHAR(100) NOT NULL,
    planete_origine VARCHAR(50) NOT NULL,
    priorite ENUM('basse', 'normale', 'haute', 'urgente') NOT NULL,
    contenu TEXT
) ENGINE=InnoDB;
```

#### Génération de 5 millions de lignes

Cette procédure génère des données aléatoires :

```sql
DELIMITER //

CREATE PROCEDURE generer_transmissions(IN nb_lignes INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE planetes VARCHAR(255) DEFAULT 'Tatooine,Coruscant,Naboo,Hoth,Endor,Dagobah,Mustafar,Alderaan,Bespin,Jakku';
    DECLARE priorites VARCHAR(255) DEFAULT 'basse,normale,haute,urgente';

    -- Désactiver les checks pour accélérer l'insertion
    SET autocommit = 0;
    SET unique_checks = 0;
    SET foreign_key_checks = 0;

    WHILE i < nb_lignes DO
        INSERT INTO transmissions (code_transmission, date_emission, emetteur, recepteur, planete_origine, priorite, contenu)
        VALUES (
            CONCAT('TR-', LPAD(i, 10, '0')),
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND,
            CONCAT('Agent-', FLOOR(RAND() * 10000)),
            CONCAT('Base-', FLOOR(RAND() * 1000)),
            ELT(FLOOR(1 + RAND() * 10), 'Tatooine', 'Coruscant', 'Naboo', 'Hoth', 'Endor', 'Dagobah', 'Mustafar', 'Alderaan', 'Bespin', 'Jakku'),
            ELT(FLOOR(1 + RAND() * 4), 'basse', 'normale', 'haute', 'urgente'),
            CONCAT('Message intercepté numéro ', i)
        );

        SET i = i + 1;

        -- Commit toutes les 10000 lignes
        IF i % 10000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;

    COMMIT;

    -- Réactiver les checks
    SET unique_checks = 1;
    SET foreign_key_checks = 1;
    SET autocommit = 1;
END //

DELIMITER ;

-- Exécution (peut prendre plusieurs minutes)
CALL generer_transmissions(5000000);
```

#### Test sans index

```sql
-- Vérifier qu'il n'y a pas d'index (à part la PRIMARY KEY)
SHOW INDEX FROM transmissions;

-- Analyser la requête
EXPLAIN SELECT id, date_emission, emetteur
FROM transmissions
WHERE planete_origine = 'Tatooine' AND priorite = 'urgente';

-- Exécuter et mesurer le temps
SELECT id, date_emission, emetteur
FROM transmissions
WHERE planete_origine = 'Tatooine' AND priorite = 'urgente';
```

Résultat EXPLAIN (sans index) :

<table id="bkmrk-type-key-rows-all-nu"><thead><tr><th>type</th><th>key</th><th>rows</th></tr></thead><tbody><tr><td>ALL</td><td>NULL</td><td>5000000</td></tr></tbody></table>

<p class="callout warning">Le SGBD parcourt les **5 millions de lignes** pour trouver les résultats. Temps estimé : plusieurs secondes.</p>

#### Création d'un index composé

```sql
-- Créer un index sur les deux colonnes utilisées dans le WHERE
CREATE INDEX idx_planete_priorite ON transmissions(planete_origine, priorite);

-- Vérifier que l'index existe
SHOW INDEX FROM transmissions;
```

#### Test avec index

```sql
-- Analyser la même requête
EXPLAIN SELECT id, date_emission, emetteur
FROM transmissions
WHERE planete_origine = 'Tatooine' AND priorite = 'urgente';

-- Exécuter et comparer le temps
SELECT id, date_emission, emetteur
FROM transmissions
WHERE planete_origine = 'Tatooine' AND priorite = 'urgente';
```

Résultat EXPLAIN (avec index) :

<table id="bkmrk-type-key-rows-ref-id"><thead><tr><th>type</th><th>key</th><th>rows</th></tr></thead><tbody><tr><td>ref</td><td>idx\_planete\_priorite</td><td>~125000</td></tr></tbody></table>

<p class="callout info">Le SGBD utilise l'index et ne parcourt qu'une fraction des lignes. Temps estimé : quelques millisecondes.</p>

### Configuration serveur

La configuration de MariaDB influence directement les performances.

Les paramètres se trouvent dans `/etc/mysql/mariadb.conf.d/50-server.cnf` (voir documentation pour une installation dockerisée).

#### innodb\_buffer\_pool\_size

C'est le paramètre **le plus important** pour les performances. Il définit la quantité de mémoire allouée au cache des données et des index InnoDB.

```ini
# Règle : 70-80% de la RAM disponible sur un serveur dédié
# Exemple pour un serveur avec 8 Go de RAM
innodb_buffer_pool_size = 6G
```

<p class="callout info">Plus ce buffer est grand, plus les données fréquemment accédées restent en mémoire, évitant les lectures disque coûteuses.</p>

#### max\_connections

Nombre maximum de connexions simultanées autorisées.

```ini
# Valeur par défaut : 151
# Augmenter si vous avez beaucoup de clients simultanés
max_connections = 200
```

<p class="callout warning">Chaque connexion consomme de la mémoire. Augmenter cette valeur sans augmenter la RAM peut causer des problèmes.</p>

### Optimisation des requêtes

Avant de chercher des solutions complexes, il faut s'assurer que les requêtes elles-mêmes sont bien écrites.

#### Éviter SELECT \*

Récupérer toutes les colonnes consomme plus de ressources (mémoire, réseau) que nécessaire.

```sql
-- À éviter
SELECT * FROM transmissions WHERE planete_origine = 'Tatooine';

-- Préférer
SELECT id, date_emission, emetteur FROM transmissions WHERE planete_origine = 'Tatooine';
```

#### Limiter les résultats

Si vous n'avez besoin que des premiers résultats, utilisez `LIMIT`.

```sql
-- Récupérer les 100 dernières transmissions
SELECT id, date_emission, emetteur
FROM transmissions
ORDER BY date_emission DESC
LIMIT 100;
```

#### Éviter les fonctions sur les colonnes dans WHERE

Appliquer une fonction sur une colonne empêche l'utilisation des index.

```sql
-- À éviter : l'index sur date_emission ne sera PAS utilisé
SELECT * FROM transmissions WHERE YEAR(date_emission) = 2024;

-- Préférer : l'index peut être utilisé
SELECT * FROM transmissions WHERE date_emission BETWEEN '2024-01-01' AND '2024-12-31';
```

#### Préférer EXISTS à IN pour les sous-requêtes

Sur de gros volumes, `EXISTS` est généralement plus performant que `IN`.

```sql
-- Moins performant
SELECT * FROM personnages WHERE id IN (SELECT emetteur_id FROM transmissions WHERE priorite = 'urgente');

-- Plus performant
SELECT * FROM personnages p WHERE EXISTS (SELECT 1 FROM transmissions t WHERE t.emetteur_id = p.id AND t.priorite = 'urgente');
```


### Bonnes pratiques - Résumé

- **Requêtes** : éviter SELECT \*, utiliser LIMIT, ne pas appliquer de fonctions sur les colonnes filtrées
- **Index** : indexer les colonnes utilisées dans WHERE, JOIN et ORDER BY
- **Ne pas sur-indexer** : chaque index ralentit les écritures
- **Analyser** : utiliser EXPLAIN régulièrement pour vérifier l'utilisation des index
- **Configurer** : adapter innodb\_buffer\_pool\_size à la mémoire disponible