MariaDB Engine, requêtes SQL devenues lentes

Notes d'optimisation

Introduction
1- Analyse d'une table
2- Création d'index composite
3- Cas datetime
4- Ré-écriture de requête
5- À garder en tête


Introduction

Lors d’une montée en version de MariaDB, mon application a rencontré des problèmes de lenteur.
Notes des ajustements qui m'ont permis de diviser par 10 le temps d'exécution de mes requêtes sur une table de 500 000 enregistrements.


1- Analyse et optimisation des tables

Etat des lieux du système :
Sur une grosse table, le simple fait de lancer un OPTIMIZE a apporté un gain notable. Optimisation à mensualiser.

Code SQL :
SHOW TABLE STATUS LIKE 'BDDphysore';
-- Data_free : l’espace libre alloué mais non utilisé — s’il est important, cela signifie que la table est fragmentée.
ANALYZE TABLE BDDphysore;
-- Analyse des tables (moins bien que OPTIMIZE)
OPTIMIZE TABLE BDDphysore;
-- Optimisation des tables (à privilégier mais en à faire hors des rush d'activité)
SHOW INDEX FROM BDDphysore;
-- (Optionnel) Vérification des index
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'BDDphysore';
-- (Optionnel) Un coup de STATISTICS

🔹 ANALYZE TABLE

Rôle : Met à jour les statistiques utilisées par l’optimiseur de requêtes.
Concrètement : Elle vérifie la distribution des valeurs des index et garde à jour les données utilisées par le planificateur de requête.
Utilité : Avoir de meilleures performances sur les jointures et regroupements (de JOIN ou de GROUP BY).

✅ N’altère pas les données, met simplement quelques locks en lecture pendant l’opération.

🔹 OPTIMIZE TABLE

Rôle : Réorganise physiquement la table et récupère l’espace perdu.
Concrètement, cette opération va :
     - Défragmenter le tableau.
     - Mettre à jour l’arbre d’index.
     - Récupérer l’espace disk non-utilisé lorsque de nombreux enregistrements ont été supprimés.
Utilité : Cette opération est particulièrement efficace lorsque la table a eu de nombreux delete ou update, laissant des « trous », ou que les données sont devenues fragmentées, ce qui ralentit l'accès.

✅ Peut nécessiter un lock plus important et prend parfois plus de temps.
✅ Peut conduire à un gain de performances sur certaines opérations de lecture et d’écriture.


2- Création d'index composite

Ensuite j’ai identifié les requêtes SQL les plus coûteuses de l’application,
afin d’ajuster la structure de la table ou de les reformuler pour améliorer leurs performances.

Analyse des champs utilisées dans les WHERE afin de construire un index composite pertinent, tout en vérifiant le type et la taille de ces champs pour limiter l’encombrement.

L'ordre que l'on applique dans l'index composite peut influencer l'efficacité de l'accès aux données.

Dans un index composite, l'ordre des colonnes va de la plus restrictive à la moins restrictive, et on garde le champ de regroupement en dernier.
Peu importe l'ordre des conditions dans la requête, c'est l'optimiseur qui va choisir la meilleure représentation.

1️⃣ La clé d’égalité ou d’inclusion : programme_id. → Elle va réduire le nombre de ligne examinées.
2️⃣ La condition d’inclusion ou d’inégalité : presultat3. → Un peu moins restrictive, par exemple presultat3)
3️⃣ La condition de plage ou de range : DATE_VISITE. → Filtrage sur une plage de valeurs.
4️⃣ Le regroupement : Utilisée par le GROUP BY.

Code SQL : 

🔹 Théorie - Ordre des colonnes dans l'index composite 🔹

1️⃣ programme_id — (ÉGALITÉ)
→ Filtrage rapide de toutes les données d'un programme particulier.

2️⃣ presultat3 — (ÉGALITÉ ou IN)
→ Filtrage sur le résultat (à inclure ou non).

3️⃣ presultat9 — (ÉGALITÉ ou "<> ''")
→ Filtrage sur le champ non vide.

4️⃣ DATE_VISITE — (PLAGE)
→ Filtrage par plage de dates (avec LIKE) ou par range.

5️⃣ presultat1 — (REGROUPEMENT)
→ Utilisé par le GROUP BY.

🔹 En pratique - Choix final de MariaDB parmis toutes les combinaisons disponibles 🔹

SELECT *
FROM `BDDphysore` 
WHERE 
id IN (
	SELECT 
	max(id) AS id
	FROM `BDDphysore` 
	WHERE 
	`DATE_VISITE` LIKE '2025%' 
	AND `programme_id` = '126' 
	AND `presultat3` IN ('Relevé_Fin','Relevé_Mise_En_Pause') 
	GROUP BY `presultat1`
	)


CREATE INDEX idx_piegegage_req01
ON BDDphysore (programme_id, presultat3, DATE_VISITE, presultat1);



SELECT *
FROM `BDDphysore` 
WHERE 
id IN (
	SELECT 
	max(id) AS id
	FROM `BDDphysore` 
	WHERE 
	`DATE_VISITE` LIKE '2025%' 
	AND `programme_id` = '126' 
	AND `presultat7` != ''
	AND `presultat3` != 'Détermination'
	GROUP BY `presultat1`
	)


CREATE INDEX idx_piegegage_req02
ON BDDphysore (programme_id, presultat7, presultat1, DATE_VISITE, presultat3);



SELECT *
FROM `BDDphysore` 
WHERE 
id IN (
	SELECT 
	max(id) AS id
	FROM `BDDphysore` 
	WHERE 
	`DATE_VISITE` LIKE '2025%' 
	AND `programme_id` = '126' 
	AND `presultat9` != ''
	AND `presultat3` != 'Détermination'
	GROUP BY `presultat1`
	)


CREATE INDEX idx_piegegage_req03
ON BDDphysore (programme_id, presultat9, presultat1, DATE_VISITE, presultat3);

Code SQL : 

SELECT id, presultat1, LENGTH(presultat1) AS longueur
FROM BDDphysore
ORDER BY longueur DESC

SELECT presultat1, CHAR_LENGTH(presultat1) AS length
FROM BDDphysore
WHERE CHAR_LENGTH(presultat1) > 255
ORDER BY length DESC
LIMIT 10;



ALTER TABLE BDDphysore
MODIFY presultat1 VARCHAR(255),
MODIFY presultat3 VARCHAR(255),
MODIFY presultat7 VARCHAR(255),
MODIFY presultat9 VARCHAR(255);


3- Cas datetime

Lors de la mise à jour du moteur MariaDB mes tables sont passées de
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
à
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Au passage $mysqli->set_charset("utf8")) est devenu un alias de utf8mb3 (utf8mb4 pour les émojis, caractères spéciaux..., utilise 4 octets au lieu de 3)

Ma colonne DATE_VISITE est un champ DATETIME, donc on ne peut pas changer sa collation directement (car la collation concerne les chaînes).

J'ai une petite alerte lors de l'utilisation de EXPLAIN pour lire le plan d'exécution de la requête,
sur une requête qui utilise un index composite avec DATETIME
afin de savoir quel index composite est utilisé

WHERE DATE_VISITE LIKE '2025%'

correction pour passer l'index sans alerte :

WHERE DATE_VISITE >= '2025-01-01 00:00:00' AND DATE_VISITE <= '2025-12-31 23:59:59'
ou
WHERE DATE_VISITE COLLATE utf8mb3_general_ci LIKE '2025%'

pourtant
WHERE DATE_VISITE LIKE '2025%'
reste bon en temps d'exécution !!??


Code BASH : 

EXPLAIN SELECT *
FROM `BDDphysore` 
WHERE 
id IN (
	SELECT 
	max(id) AS id
	FROM `BDDphysore` 
	WHERE 
	WHERE DATE_VISITE >= '2025-01-01 00:00:00' AND DATE_VISITE <= '2025-12-31 23:59:59'
	AND `programme_id` = '126' 
	AND `presultat7` != ''
	AND `presultat3` != 'Détermination'
	GROUP BY `presultat1`
	)

EXPLAIN SELECT *
FROM `BDDphysore` 
WHERE 
id IN (
	SELECT 
	max(id) AS id
	FROM `BDDphysore` 
	WHERE 
	DATE_VISITE COLLATE utf8mb3_general_ci LIKE '2025%'
	AND `programme_id` = '126' 
	AND `presultat7` != ''
	AND `presultat3` != 'Détermination'
	GROUP BY `presultat1`
	)


4- Ré-écriture de requête

J’ai tenté de réécrire la requête en remplaçant le IN par un JOIN,
mais le gain de performance ici s’est avéré négligeable après test.

FROM BDDphysore t1
INNER JOIN t2 ON t1.id = t2.id

INNER JOIN ou JOIN
Ne garde que les lignes qui ont des correspondances dans les deux tables.

LEFT JOIN (ou LEFT OUTER JOIN)
Garde toutes les lignes de la table de gauche (t1), même si aucune correspondance n’existe dans t2.
Quand il n’y a pas de correspondance, les colonnes de t2 sont remplies avec NULL.

LEFT JOIN
Garde toutes les lignes de la table de gauche (celle qui est avant le JOIN).
Met des NULL si aucune correspondance dans la table de droite.


Code BASH : 

SELECT 
    LEFT(t1.DATE_VISITE, 10) AS DATE_FIN, 
    t1.presultat1 AS NOMDUPIEGE, 
    t1.presultat3 AS Etat
FROM BDDphysore t1
JOIN (
    SELECT MAX(id) AS id, presultat1
    FROM BDDphysore
    WHERE DATE_VISITE LIKE '2025%'
      AND programme_id = '126'
      AND presultat3 IN ('Relevé_Fin', 'Relevé_Mise_En_Pause')
    GROUP BY presultat1
) t2 ON t1.id = t2.id;


SELECT 
    LEFT(b.`DATE_VISITE`,10) AS DATE_VISITE,
    b.`presultat3`,
    b.`presultat7`,
    b.`presultat9`
FROM `BDDphysore` b
INNER JOIN (
    SELECT 
        MAX(id) AS id,
        presultat1
    FROM `BDDphysore`
    WHERE 
        `DATE_VISITE` LIKE '2025%' 
        AND `programme_id` = '126'
        AND `presultat7` IS NOT NULL AND `presultat7` <> '' 
        AND `presultat3` IS NOT NULL AND `presultat3` <> 'Détermination'
    GROUP BY `presultat1`
) max_ids ON b.id = max_ids.id;


5- À garder en tête

@@sql_mode est une variable système de MariaDB (et MySQL) qui détermine le comportement et les contraintes que le serveur applique lorsqu'on exécute des instructions SQL.

Code BASH :
root@DEBIANdev:~#mariadb -u root -p -e "SELECT @@sql_mode" 
+-----------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------+
| IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------+
✅ IGNORE_SPACE : ignore les espaces dans certaines syntaxes de fonction.
✅ NO_ZERO_IN_DATE : interdit les dates avec mois ou jours égaux à zéro (jusqu'en MySQL 5.x).
✅ NO_ZERO_DATE : interdit le cas de '0000-00-00'.
✅ ERROR_FOR_DIVISION_BY_ZERO : lancer une erreur plutôt que rendre NULL lorsque tu divises par zéro.
✅ NO_AUTO_CREATE_USER : option obsolète depuis MySQL 5.7, garde le serveur de création d’utilisateur plus sécurisé.
✅ NO_ENGINE_SUBSTITUTION : interdit que MySQL remplace automatiquement le engine d’une table lorsque le engine demandé n’est pas installé.




Retour Home