est un outil de base de données très puissant, bien souvent sous-utilisé voire même mal utilisé. Découvrez des commandes SQL un peu “exotiques” comme FOR UPDATE ou les PROCEDURE stockées, et tirez le meilleur parti de MySQL pour votre jeu web!

Cet article se base énormément sur la documentation de MySQL (8.0), à laquelle je vous invite à vous référer régulièrement. Pensez aussi à suivre les nouveautés à chaque nouvelle version de MySQL.

Les colonnes

JSON

Ce type de colonne permet de stocker un objet au format JSON, de manière optimisée: MySQL 8 sera en effet capable d’indexer cette colonne pour accélérer les requêtes portant dessus.

Le format JSON est adapté si et seulement si deux lignes d’une même table ont des objets de structure différente. Le reste du temps (99% du temps donc), le format JSOn est inadapté (vous êtes en train de déguiser une sérialisation en BDD, et c’est un mauvais modèle).

DATETIME, TIMESTAMP, ou INT UNSIGNED?

Si vous stockez des dates, utilisez un champ DATE ou DATETIME. En effet, DATETIME vous permettra de définir des valeurs par défaut comme CURRENT_TIMESTAMP, et même, de demander à MySQL de mettre ce champ à jour à chaque modification de la ligne de la table de données (via ON UPDATE CURRENT_TIMESTAMP).

MySQL n’enregistre pas la timezone d’un DATETIME: mettez toujours votre serveur MySQL et vos clients en UTC (SET time_zone = '+00:00';), pour éviter les soucis de fuseaux horaires/changements d’heure.

Les “generated columns”

MySQL 5.7 permet de créer des colonnes “virtuelles” (“generated columns”) dont la valeur sera calculée à la volée, soit lors de la sélection dans la table (VIRTUAL) soit à l’insertion/mise à jour dans la table (STORED). La première méthode évite de stockage la valeur virtuelle sur le disque, mais elle est lente car elle requière de recalculer cette valeur à chaque SELECT.

Les indexes

Cardinalité

Un index efficace a une forte cardinalité, c’est à dire que le moins de lignes possibles de la table doivent partager la même valeur pour l’index. Ainsi, un index sur une colonne BIT(1) sera peu efficace alors qu’un index unique INT UNSIGNED, comme celui utilisé pour les IDs, sera très efficace (car unique). Attention aussi à la répartition des valeurs au sein d’un index: si 90% des valeurs d’un TINYINT UNSIGNED sont égales à 0 et que vous avez besoin de connaître les lignes où cette valeur est 0, alors l’index sera peu efficace.

Index multi-colonne et ordre des colonnes

Un INDEX peut porter sur plusieurs colonnes, mais si vous souhaitez que MySQL l’utilise pour optimiser une requête, alors il faudra que la requête porte d’abord sur la 1ere colonne de l’index, puis sur la seconde, puis sur la troisième, etc. Par exemple, l’index (x, y, z) sera utilisé pour une requête ayant une clause WHERE x = ... AND y = ... AND z = ..., ou encore pour WHERE x BETWEEN ... AND ..., mais pas pour WHERE y = ... AND z = ....

Le serveur MySQL

Les TRIGGER

Ils vous permettent d’effectuer une action à chaque fois qu’une ligne est insérée, supprimée ou modifiée dans une table. Depuis MySQL 5.7, vous pouvez même avoir plusieurs TRIGGER d’un même type sur une même table (ie: deux triggers qui se lancent avant l’insertion de toute nouvelle ligne dans la table). Les triggers sont lents par rapport à une table sans aucun trigger, mais ils sont indispensables pour assurer qu’aucune donnée erronée n’est insérée dans la table, ou pour mettre à jour d’autres tables (dans le cadre d’un système d’historisation par exemple).

L’optimiseur de MySQL 5.7

Soyez prudent avec MySQL 5.7, car son optimiseur a parfois tendance à trop simplifier les requêtes imbriquées. En effet, le 5.6 avait tendance à utiliser des TEMPTABLE dans ces requêtes imbriquées, alors que MySQL 5.7 privilégie les tables dématérialisées (voire ma question sur StackOverflow). Ainsi, si une requête du 5.6 ne marche plus correctement sur MySQL 5.7, envisagez de forcer la matérialisation de votre sous-requête grâce à DISTINCT, qui force MySQL 5.7 à utiliser une TEMPTABLE (plus lent, mais parfois indispensable).

SET @idMax := (SELECT MAX(id) FROM so_place);
SELECT * FROM (
    SELECT DISTINCT
        FLOOR(RAND()*@idMax+1) AS id_place, id, name
    FROM so_object
) AS t
INNER JOIN so_place AS p ON p.id = t.id_place;

Le moteur FEDERATED

Le moteur MySQL FEDERATED (au même titre que MyISAM ou InnoDB) vous permet de faire une référence à une table d’un autre serveur. Par exemple, une table InnoDB T se trouve sur un serveur A. Une table FEDERATED F se trouve sur un autre serveur B, et est déclarée comme faisant référence à la table T de A. Les données de la table F sont donc stockées sur un autre serveur! Cela peut vous servir pour créer un serveur MySQL d’archive par exemple.

L’extension “Geometry”

Cette extension de MySQL, installée par défaut sur les mutualisés d’OVH, permet de manipuler des données géométriques (comme son nom l’indique), en 2D. Cela sera très utile pour stocker les cases de la carte du jeu par exemple, ou la position (vecteur (x, y)) des unités sur cette carte. Depuis MySQL 5.7, les colonnes GEOMETRY d’une table InnoDB peuvent être indexées, rendant les requêtes rapides (exemple: pour savoir quelles cases de la carte se trouve dans la zone de vue du joueur, ou quelle case est voisine de quelle autre case). Référez-vous aux fonctions géométriques de MySQL.

Si votre carte est régulière (losanges ou hexagones pour chaque case), alors mieux vaut utiliser deux colonnes x INT UNSIGNED|DOUBLE, y INT UNSIGNED|DOUBLE sur lesquelles vous poserez un UNIQUE INDEX: l’efficacité sera meilleure qu’un index SPATIAL, et vous gagnerez en simplicité.

Les emojis

Sur Iamanoc, l’insertion des emojis dans un message plantait. En effet, certains caractères (dont les emojis), sont encodés sur 4 octets. Or, MySQL n’utilise que 3 octets dans l’encodage UTF8 (comme dans utf8_general_ci|utf8_bin). Pour éviter cela, il faut basculer les tables (ou plus précisément les colonnes *CHAR|*TEXT) en utf8mb4_general_ci|utf8mb4_bin), et il vous faut définir cet encodage comme étant celui utilisé par votre connexion PDO via $pdo->query("SET NAMES utf8mb4"); avant vos requêtes.

Pour ma part, j’exécute cette query SQL pour l’utf8mb4 dans la méthode qui crée l’objet PDO, en même temps que la définition de la timezone à UTC.

Les de requête SQL

Sélectionner les N plus grands

Supposons que, dans votre jeu web, chaque joueur ait des points et une alliance. Vous voulez afficher un classement avec les 3 premiers joueurs de chaque alliance.

-- MySQL 8
SELECT ROW_NUMBER() OVER (PARTITION BY joueur.id_alliance ORDER BY joueur.points DESC) AS rk, * FROM joueur ORDER BY joueur.id_alliance, joueur.points DESC HAVING rk <= 3
-- MySQL 5
SELECT t.row_number AS rk, j.* FROM (
    SELECT
        id,
        @rk := if(@lastAlliance IS NOT NULL AND @lastAlliance = id_alliance, @rk + 1, 1) as row_number,
        @lastAlliance := id_alliance as dummy
    FROM joueur ORDER by id_alliance, points DESC
) AS t
INNER JOIN joueur AS j ON j.id = t.id
WHERE t.row_number <= 3
Deux exemples, le 1er en MySQL 8 (Window Function) et le second en MySQL 5

Soyez prudent en utilisant des variables mutables dans une requête SQL: le plan d’exécution de la requête peut totalement changer son résultat si elle n’est pas correctement écrite!

Récupérer une ligne au hasard

Si vous avez besoin de récupérer une ligne au hasard dans un jeu de résultat, alors posez-vous d’abord la question: voulez-vous vraiment une ligne au hasard, ou simplement une ligne quelconque? Dans le seconde cas, il vous suffit de rajouter un LIMIT 1 à votre requête SQL (MySQL prend le premier résultat de la table qu’il trouvera). C’est souvent largement suffisant. Sinon, si vous avez véritablement besoin d’une ligne au hasard (pour des besoins de gameplay par exemple), alors bannissez ORDER BY RAND() LIMIT 1 et privilégiez WHERE id >= (FLOOR(RAND()*(SELECT MAX(id) FROM table))) ORDER BY id ASC LIMIT 1 (voir l’article anglais sur ORDER BY RAND()).

Compter les lignes répondant à une condition

SELECT SUM(condition) AS total, ... FROM ... permet de compter facilement le nombre de lignes d’un résultat qui répondent à condition. On peut faire plusieurs de ces SUM dans une même requête, au lieu de faire plusieurs requêtes avec COUNT(*).

SELECT
    SUM(1) AS casesTotal,
    SUM(mc.type = 'TERRE') AS casesTerrestres,
    SUM(mc.type = 'MER') AS casesMaritimes,
    SUM(b.id IS NOT NULL) AS casesOccupees,
    SUM(b.id IS NULL) AS casesVides
FROM map_cases AS mc
LEFT JOIN batiments AS b ON b.id = mc.id_batiment
WHERE mc.id_pays = ?
Exemple d’utilisation des SUM(condition) pour compter les cases terrestres, maritimes, occupées et vides du territoire d’une carte de jeu

Historiser une table

Pour historiser une table à chaque modification (ie: conserver la ligne existante avant chaque UPDATE), je vous recommande d’utiliser un TRIGGER qui va se charger de copier la ligne existante de la table à historiser T vers la table d’archive A lors de chaque UPDATE de la table T. N’allez pas croire que “un TRIGGER, c’est mal!” car l’historisation est l’un des cas où ils seront plus fiables et puissants qu’un bricolage manuel. Enfin, stockez votre historique dans une table dédiée: cela vous permettra de bien le séparer des données actuelles, et d’éviter des baisses de performances avec le temps: tout mettre dans la même table obligerait MySQL à faire le tri entre historique et données actuelles à chaque requête, sans compter que ces deux tables n’ont pas forcément exactement les mêmes colonnes (la table d’historisation stockera aussi la date d’historisation et le nom de l’utilisateur ayant fait la modification).

$data = $dao->getData($id);
$daoHistoric->saveData($user, $data);
...
$dao->saveData($data);
DELIMITER $$
CREATE TRIGGER `historize_my_table`
    BEFORE UPDATE ON `my_table`
    FOR EACH ROW
BEGIN
    INSERT INTO `my_table_historic` VALUES (SELECT *, NOW() FROM `my_table` WHERE `my_table`.`id`=NEW.`id`;
END$$
Un mauvais exemple délégant à PHP la charge d’historiser les choses et un meilleur exemple utilisant un TRIGGER côté MySQL

Un appel de fonction dans une clause WHERE

Si une fonction MySQL (native ou custom) doit être appelée dans une clause WHERE, alors mieux vaut que cette fonction soit indépendante des lignes du resultset. MySQL pourra alors optimiser au maximum la requête, en appelant cette fonction une seule fois au lieu de l’évaluer à chaque ligne. Si les paramètres de la fonction dépendent des données de la ligne, alors mieux vaut que la fonction soit DETERMINISTIC et que le nombre de lignes soit très réduit car MySQL risque de devoir recalculer cette fonction pour chaque ligne de la table, ce qui rendra la requête très lente. Et si la fonction est NON DETERMINISTIC, alors MySQL recalculera nécessairement sa valeur à chaque ligne, ce qui sera encore plus lent.

UPDATE et ordre des colonnes

Dans une requête UPDATE, l’ordre des colonnes est important car MySQL utilisera la nouvelle valeur des colonnes précédente pour mettre à jour la colonne suivante. Par exemple, dans UPDATE ... SET c = 1, c = c+1, MySQL va d’abord mettre la valeur de la colonne c à 1, puis à c+1; la colonne c vaudra donc 2 pour toutes les lignes que le UPDATE modifiera.

Variables dans un UPDATE

Notez qu’au lieu de vous reposer sur l’ordre des colonnes, vous pouvez aussi vous servir de variables utilisateur (@myVar) dans un UPDATE. Toutefois, la meilleure façon de s’en servir reste d’utiliser ces variables dans un SELECT, et de faire un INNER JOIN dessus dans votre UPDATE car MySQL a parfois un comportement aléatoire avec les variables dans un UPDATE.

UPDATE spaceship AS s
INNER JOIN (
    SELECT
        s.id,
        @currentTimeUnits := s.extraction_time_units + (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(s.extraction_date_last))*1,
        @extractedQuantity := LEAST(@currentTimeUnits*2*bm.`count`, @remainingExtraction),
        @remainingTimeUnits := @currentTimeUnits - @extractedQuantity/2/bm.`count` AS remainingTimeUnits,
        @remainingExtraction := @remainingExtraction - @extractedQuantity
    FROM spaceship AS s
    INNER JOIN blueprint_module AS bm ON bm.id_blueprint = s.id_blueprint AND bm.`count` > 0
    INNER JOIN `module` AS m ON m.id = bm.id_module AND m.`name` = 'EXTRACTION'
    WHERE s.id_oc = idOc AND s.id_player = idPlayer
) AS t ON t.id = s.id
SET
    s.extraction_date_last = NOW(),
    s.extraction_time_units = t.remainingTimeUnits
;
Faire d’abord un SELECT, utilisant éventuellement des @variables, et faire un INNER JOIN dans le UPDATE sera fiable et véloce

Les boucles de requêtes SQL

Les boucles de requête SQL (while (...) { $pdo->query('...'); }) sont à bannir dans le client, car le temps réseau (le temps que PHP contacte le MySQL, envoie la requête, que MySQL la reçoive, la parse, et retourne son résultat) peut faire exploser la durée d’exécution de votre script (télécharger la démo). En revanche, dans une PROCEDURE stockée, ce temps réseau est nul (puisque la procédure stockée est exécutée par le serveur MySQL): une boucle de requête devient alors tolérable, même si la ré-écrire avec des jointures ou un curseur sera probablement bien plus véloce.

Optimisation

L’optimisation d’un serveur MySQL et des requêtes associées peut accélérer votre jeu web. Voici des pistes pour la réaliser. Ce sont des généralités palliatives (et non préventives): elles ne sont applicables que si votre requête est déjà lente.

  • Evitez de retourner des données inutiles ou non-finales: retourner la liste des cases du pays du joueur, puis la liste des unités sur ces cases est lent si vous voulez juste donner le nombre total d’unités qu’il possède.
  • Utilisez le bon type de colonnes, les bons indexes, et le bon row_format. Recourrez à EXPLAIN si besoin.
  • Les LOCK doivent-être bien posés: ne lockez que ce qu’il faut, ni plus, ni moins.
  • Evitez les fonctions dans les WHERE, y compris celles DETERMINIST: remplacez-les par le code in-line de la fonction avant d’exécuter la requête (si possible).
  • Inutile de vouloir bricoler l’ordre des conditions d’un WHERE: MySQL s’en charge déjà, donc, faites juste lisible (et non véloce).
  • HAVING est directement mergé à WHERE s’il n’y a pas de GROUP BY
  • Vous pouvez faire une table de constantes de jeu, et faire un sous-SELECT dessus: MySQL fait l’extraction des constantes en premier, et considère la ligne de donnée comme une constante (c’est véloce)
  • MySQL réordonne les jointures si nécessaire, donc concentrez-vous sur la lisibilité
  • L’index doit être HASH ou BTREE suivant si les WHERE sont des égalités ou des comparaisons
  • “Nested loop join” est très mauvais car il itère toutes les combinaisons de rows d’un join; “block nested” est un peu meilleur car c’est un nested avec un cache MySQL, par “block”. FULL TABLE SCAN le pire si la table contient plusieurs dizaines ou centaines de lignes: MySQL fait parfois un FULL TABLE SCAN pour les petites tables, car c’est plus rapide que d’ouvrir l’index, filtrer les lignes, puis ouvrir la table pour lire les données.
  • Toute expression dans un ORDER BY, y compris un “ORDER BY -x” empêche MySQL d’utiliser les indexes
  • GROUP BY devrait porter sur un et un seul index (qui peut avoir plusieurs colonnes); pour faire des MIN/MAX/SUM rapidement, il faut que l’index soit BTREE
  • LIMIT améliore les perfs, sauf si un SQL_FOUND_ROWS est présent (logique), et LIMIT 0 permet de retourner rapidement un resultset vide, pour checker qu’une query est valide
  • SELECT * FROM t1 WHERE (column1,column2) = (1,1) peut être bien si on a posé l’index sur toutes les colonnes du “row constructor” (c1, c2) mais bloque MySQL dans le cas contraire: c1 = 1 AND c2 = 2 est souvent plus sûr
  • Dans les cas tricky et si on est bon (ie: le plus souvent, il ne faut pas le faire) alors l’optimizer peut s’ajuster pour une query spécifique (cf les optimizer hints)
  • La fonction BENCHMARK peut aider à savoir si une query traine des pieds ou pas
  • Les LEFT JOIN (et autres JOIN) peuvent être faits sur des tables + sous-jointures, par exemple t1 LEFT JOIN (t2 LEFT JOIN t3) ON ... ou SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a (cf Nested Join Optimization)

Les commandes SQL (queries)

  • SELECT FOR UPDATE et SELECT LOCK IN SHARE MODE permettent de verrouiller les lignes pour éviter qu’elles ne soient lues/altérées par d’autres transactions: vous pouvez faire un SELECT FOR UPDATE puis faire des calculs sur les données récupérées avant de faire un UPDATE pour les mettre à jour, sans craindre qu’un accès concurrent ait lu la ligne de la table entre temps.
  • REPLACE remplace les lignes d’une table (delete+insert) en cas de doublon
  • WHERE x < ANY (...), WHERE x IN (...) et WHERE x < SOME (...) permettent de comparer x à un ensemble d’autres lignes
  • Row subqueries: SELECT ... WHERE (col1, col2) = (SELECT x, y FROM ...); ou WHERE (col1, col2) IN (SELECT x, y FROM t2)
  • CREATE TABLESPACE pour grouper des tables (par exemple, de nombreuses petites tables groupées en un fichier; mais je ne suis pas certain que ce soit cela…)
  • GET ... DIAGNOSTICS: utilisé dans les HANLDER des PROCEDURE pour récupérer des informations sur l’erreur/l’exception (SIGNAL) traité par ce HANDLER
  • Manipuler du JSON est facile sur MySQL 5.7 (et le futur MySQL 8)
  • ALTER TABLE (et CREATE TABLE)) offrent énormément d’options type PASSWORD|ENCRYPTION, ENGINE, INSERT_METHOD, MIN_ROWS|MAX_ROWS, etc
  • La syntaxe SET ... = ... similaire à celle de UPDATE est aussi utilisable pour INSERT
  • SAVEPOINT|ROLLBACK|RELEASE|COMMIT<:a> permettent de créer des “transactions imbriquées”
  • SET TRANSACTION définit le niveau d’isolation d’une transaction, pratique pour demander des “consistent reads” (ou non)
  • Les XA Transactions sont des transactions impliquant plusieurs serveurs (de BDD ou non, cela pourrait permettre de faire des transactions MySQL+SMTP pour les envois de mail)
  • PREPARE|EXECUTE|DEALLOCATE permettent de faire des requête préparées côté serveur SQL, mais c’est très limité comme syntaxes
  • Les CURSOR permettent d’itérer sur les lignes d’un resultset (un genre de “for each line in resultset { ... })
  • NOWAIT|SKIP LOCKED permettent de zapper des lignes verrouillées, pour éviter qu’une transaction en bloque une autre
  • WITH permet de faire des “Common Table Expressions”, c’est à dire “embarquer” une ou plusieurs vues dans une requête, et la/les réutiliser dans cette même requêtes. Les CTE récursives permettent le parcours d’arbre sans nécessiter de boucle SQL.
  • <=> (ou IS NOT DISTINCT FROM en SQL standard) est le “NULL Safe Equal Operator”, mais il ne retourne jamais NULL: il retourne 1 si les deux membres sont NULL et 0 si l’un des deux seulement est NULL (l’opérateur = retourne NULL dans ces deux cas).
  • STRCMP(expr1, expr2) retourne 0|1|-1 si expr1 est égal|avant|après expr2 dans l’ordre alphabétique (ou “current sort order”, donc cela dépend du “character set” et du typage des expressions)

Les éléments de la documentation