Parfois, il est nécessaire d’itérer des traitements sur une table entière de la BDD. Quels sont les moyens pour y parvenir, et quelles en sont les performances?

La nécessité d’itérer

Quand je parle “d’itérer sur une table”, je parle d’une traitement à appliquer sur plusieurs lignes d’une même table de la BDD (et on prendra MySQL pour l’exemple). Ce cas n’arrive pas souvent, mais est régulièrement nécessaire dans un jeu: simulation d’un combat, déplacement de troupes, tour de jeu, etc sont des exemples nécessitant ce genre d’itération.

Exemple

Dans cet article, 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

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’aggrégat SUM(). Le résultat (10000100000) est instantané.

SELECT SUM(x) FROM int_test;
Aggrégation SQL: 15ms

Testez d’abord les perfs d’une seule requête (aggrégat si nécessaire) avant de vous lancer dans un traitement à plusieurs requêtes.

Sortir les données

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);
Comptage côté “client” (PHP): 35ms

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);
Traitement client ligne à ligne: 17s (!)

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, évitent 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;
Procédure et @userVariable: 2s

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;
Local variable: 1.5s

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 e avez déjà entendu parlé, 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;
Curseur: 350ms
Quand vous avez un traitement de données à faire, privilégiez, dans l’ordre (les perfs relatives sont entre parenthèses):
  1. Les requêtes d’agrégat, et de manière générale, une seule requête de traitement global (x1)
  2. Les curseurs dans les procédures (x20)
  3. 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.