Préparation
Dans cet article, je vais vous montrer comment optimiser votre utilisation du serveur SQL (MySQL). Pour cela, je vais utiliser un exemple un peu bidon, mais simple: je veux faire un traitement itératif sur toutes les lignes d'une table d'entiers, pour faire une somme. Je vais donc d'abord créer ma structure de test, sous la forme d'une table avec deux colonnes et 100.000 lignes. Comptez 5 secondes environ pour l'exécution de ce script (même si on s'en fiche un peu de cette performance).
CREATE TABLE int_test (
id INT UNSIGNED NOT NULL PRIMARY KEY,
x INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
DELIMITER $$
CREATE PROCEDURE filltable()
BEGIN
DECLARE i INT UNSIGNED;
SET i = 100000;
WHILE (i > 0) DO
INSERT INTO int_test (id, x) VALUES (i, 2*i);
SET i = i - 1;
END WHILE;
END$$
DELIMITER ;
CALL filltable();
DROP PROCEDURE filltable;
-- DROP TABLE int_test;
On veut maintenant appliquer un traitement itératif sur ces 100k lignes, le plus rapidement possible, pour faire la somme des x.
Solutions
Eviter l'itératif: les agrégats
La première solution à laquelle penser est d'éviter le traitement itératif. Ainsi, dans notre exemple, il n'est pas nécessaire de boucler sur toutes les lignes de la table pour en faire la somme. Il suffit d'utiliser la fonction d'agrégat SUM() . Le résultat (10000100000) est instantané.
SELECT SUM(x) FROM int_test;
Testez d'abord les perfs d'une seule requête (agrégat si nécessaire) avant de vous lancer dans un traitement à plusieurs requêtes.
Sortir les données
Quand l'agrégat n'est pas possible, la deuxième solution souvent utilisée consiste à sortir les données de la BDD, et à faire le traitement dans un langage client (typiquement, PHP). Cette approche me pose problème en général, car elle répartit la logique métier entre deux éléments: le MySQL (qui modélise les données du jeu) et le PHP (qui traite les données du jeu). La frontière entre traitement et modélisation étant souvent floue, je n'aime pas cette approche; d'autant plus qu'elle verrouillera généralement les lignes (toutes les lignes!) pendant la durée du traitement, incluant les temps d'échange réseau entre la BDD et le client PHP (les temps réseaux sont les plus longs). Mais voyons ses résultats.
$t0 = microtime(true);
$pdo = new PDO('mysql:host=127.0.0.1;dbname=variispace', 'variispace', 'variispace');
$data = $pdo->query('SELECT x FROM int_test')->fetchAll(PDO::FETCH_ASSOC);
$sum = 0;
foreach ($data AS $d) {
$sum += $d['x'];
}
echo $sum . PHP_EOL;
echo (microtime(true) - $t0);
Evidemment, on peut procéder autrement pour faire son traitement côté PHP, mais si le traitement nécessite plusieurs requêtes, alors vous aurez souvent besoin d'itérer (foreach) et vous ne pourrez pas passer facilement par des fonctions natives de PHP (array_sum).
La répartition de la logique de traitement entre serveur (SQL) et client (PHP) fait également intervenir le réseau, rendant les benchmark plus aléatoires.
Sortir les données une à une
Suivant les cas, il se peut que les données soient à sortir une à une (SELECT d'une ligne, modifications en PHP, puis INSERT de la ligne modifiée). Ce cas est le plus lourd, donc voyons un peu ce qu'il donnerait:
// Ne rigolez pas: ce genre d'approche se voit souvent, et pas qu'en client/serveur SQL!
$t0 = microtime(true);
$pdo = new PDO('mysql:host=127.0.0.1;dbname=variispace', 'variispace', 'variispace');
$sum = 0;
for($i = 1;true;$i++) {
$st = $pdo->prepare('SELECT x FROM int_test WHERE id = ?');
$st->execute(array($i));
$n = ($st->fetchAll(PDO::FETCH_ASSOC))[0]['x'] ?? null;
if (!$n) {
break;
}
$sum += $n;
}
echo $sum . PHP_EOL;
echo (microtime(true) - $t0);
Les boucles de requêtes SQL faites depuis un client sont souvent très, mais alors trèèès lentes!
Procédure et user variable
Une approche plus rapide, évitant de sortir des données inutilement du SQL, consiste à créer une procédure stockée en charge du traitement. Ainsi, le code client (PHP) ne fera qu'un seul appel au SQL (un appel de procédure CALL), et la procédure (qui se trouve dans le serveur SQL) contiendra la logique métier du traitement (évitant donc de la scinder entre PHP et SQL). Pour comparer facilement avec l'approche PHP, je vous propose donc une première version de cette procédure fonctionnant sur le même principe.
DELIMITER $$
CREATE PROCEDURE tst()
BEGIN
DECLARE somme BIGINT UNSIGNED DEFAULT 0;
START TRANSACTION READ ONLY;
SET @i = 1;
WHILE (@x := (SELECT x FROM int_test WHERE id = @i)) DO
SET somme = somme + @x;
SET @i = @i + 1;
END WHILE;
COMMIT;
SELECT somme;
END$$
DELIMITER ;
CALL tst();
DROP PROCEDURE tst;
Ne pas sortir les données de la BDD permet d'économiser énormément de temps réseau.
Procédure et variable locale
Là, nous avons utilisé une variable @userVariable, mais il est aussi possible de passer par une variable locale (DECLARE). Est-ce qu'il y a un impact? Oui, car la variable @ est définie au niveau de la session, alors que la variable locale est restreinte à la procédure: mysql peut facilement l'optimiser à l'exécution.
DELIMITER $$
CREATE PROCEDURE tst()
BEGIN
DECLARE i INT UNSIGNED DEFAULT 1;
DECLARE somme BIGINT UNSIGNED DEFAULT 0;
START TRANSACTION READ ONLY;
WHILE (@x := (SELECT x FROM int_test WHERE id = i)) DO
SET somme = somme + @x;
SET i = i + 1;
END WHILE;
COMMIT;
SELECT somme;
END$$
DELIMITER ;
CALL tst();
DROP PROCEDURE tst;
La différence n'est pas flagrante ici, mais elle peut être de x10 dans certains cas, alors privilégiez toujours les LOCAL VARIABLES aux @USER VARIABLES.
Les curseurs
Enfin, que donne un curseur? Si vous en avez déjà entendu parler, on vous a sûrement rabâché que "c'est lent, c'est nul, ne les utilise jamais!". En pratique, il faut être plus mesuré que cela: s'ils sont effectivement plus lent qu'une query d'agrégat, ils sont plus rapides que les autres méthodes citées. Leur seul défaut est donc d'être très verbeux.
DELIMITER $$
CREATE PROCEDURE tst()
BEGIN
DECLARE varx INT UNSIGNED;
DECLARE somme BIGINT UNSIGNED DEFAULT 0;
DECLARE c CURSOR FOR SELECT x FROM int_test;
OPEN c;
r:LOOP
BEGIN
DECLARE done TINYINT UNSIGNED DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
FETCH c INTO varx;
IF (done) THEN
LEAVE r;
END IF;
END;
SET somme = somme + varx;
END LOOP;
CLOSE c;
SELECT somme;
END$$
DELIMITER ;
CALL tst();
DROP PROCEDURE tst;
- Les requêtes d'agrégat, et de manière générale, une seule requête de traitement global (x1)
- Les curseurs dans les procédures (x20)
- Les procédures itératives avec une variable locale (x100)
Evitez les procédures à user variables (x200), et évitez de sortir inutilement des données de votre BDD (x1000), car vous allez vous trainer beaucoup d'autres lourdeurs derrière: réseau, mémoire, DAO, locks, etc.