MySQL - Devblog de Xenos

Récupérer une ligne au hasard

SELECT *
FROM `table` AS t
	INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM table)) AS mini) AS t2
WHERE t.id >= t2.mini
ORDER BY t.id ASC
LIMIT 1

Evitez ORDER BY RAND() LIMIT 1 (très lourd)

Cette approche n'est pas tout à fait equiprobable si vous avez des "trous" dans les ids.

La source de tout: la doc!

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 .

Types de colonnes

Les types de colonnes (en fait, les types de données que MySQL sait manipuler) sont listés dans le chapitre 11 de la doc MySQL . Les plus intéressants (exotiques) sont BIT BOOL TIMESTAMP ENUM SET BLOB GEOMETRY POINT LINESTRING POLYGON JSON et les variantes BINARY|CHARACTER SET|ASCII|UNICODE pour les types "string" etUNSIGNED|ZEROFILL pour les nombres. Je vous laisse lire le détail de ce chapitre pour plus d'infos.

Dates et timestamps

MySQL n'enregistre pas la timezone d'un DATETIME: ce type de colonne stocke un concept de date (Noël) et non un point de la ligne du temps (utilisez TIMESTAMP pour ça).
Attention aussi à la timzone de votre client (PHP) et de la connexion (SET time_zone = '00:00';).

La connection utilise la même timezone (heure hiver/été incluse) à l'insertion et à la selection, Date et Timestamp sont consistants — Comparatif entre Date (timezone-dependant) et Timestamp (timezone-agnostic)

Les timezones à l'insertion et à la selection diffèrent: on a une incohérence avec Date mais pas avec Timestamp — Comparatif entre Date (timezone-dependant) et Timestamp (timezone-agnostic)

Dans cet exemple, on insère une même date UTC (time_zone à 00:00) dans une colonne Date et une colonne Timestamp, qu'on récupère en UTC: pas de soucis pour aucune des deux colonnes: les timezone à l'insertion à la lecture sont les mêmes.
Ensuite, on change la timezone (03:00) puis on insère à nouveau une même date dans les deux colonnes: là, problème pour le type Date, car la première ligne indique toujours 9h alors que la seconde indique 12h.

En pratique, MySQL a sauvé le texte 2019-10-18 09:28:02 issu de la timezone au moment de l'insertion dans la première colonne, et renverra toujours ce texte quelle que soit la timezone de la connection (on le voit dans la 3e screen, à nouveau en UTC): si elles ne sont pas identiques, on a une incohérence.
Avec une colonne Timestamp, Mysql convertit la date actuelle en Timestamp (sans timezone) au moment de l'insertion (en prenant donc en compte la timezone de la connection à ce moment-là) et sauve cette valeur (entier) dans la colonne. A la lecture, cette valeur est convertie de nouveau dans la timezone de la connection et la date est consistante: si on ajoute 3h à la timezone de la connection (en passant de 00:00 à 03:00) alors la date affichée est décalée aussi de 3h, correspondant donc au même moment sur la "ligne du temps réel".

Vous aurez presque toujours besoin d'un TIMESTAMP, quasiment jamais d'un DATETIME.

Concepts utiles

Je ne paraphraserai pas la doc, mais les concepts (avancés) suivants vous seront sûrement utiles.
CHECK : valide une contrainte sur une colonne/une table entière pour chaque insertion, modification, etc
VIEW : une forme de "query stockée" ou de "table virtuelle"
CREATE PROCEDURE/CREATE FUNCTION : du code stocké dans le serveur SQL, pour éviter les allers-retours avec PHP/Java/etc. Permettent aussi d'utiliser les boucles (while), les conditions (if), les "exceptions" ( signal / handler ) etc
Generated columns (virtual columns) : des colonnes dont la valeur est calculée à la volée, ou à chaque insertion/modification de la table. Très utile pour stockée des "max id" d'un historique, ou un booléen indiquant si une ligne est celle d'id ou de date la plus élevée, etc
JSON_TABLE : convertit du JSON en une table SQL; pensez aussi à lire les autres fonctions JSON disponibles .
ENGINE=FEDERATED : permet de stocker les données d'une table sur un autre serveur (= accéder aux tables d'un serveur Y depuis un serveur X).
Extension "GEOMETRY" : permet de stocker, indexer, et manipuler des données géométriques en 2D (idéal pour une carte de jeu web!)
Window Functions : calculs sur des groupes de lignes dans un resultset

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;

Sans "DISTINCT", MySQL ≥5.7 ré-utilisera la même valeur de RAND au lieu de générer une valeur par ligne

UTF-8

Utilisez toujours le CHARACTER SET 'utf8mb4' pour toutes vos colonnes stockant des textes issus des utilisateurs. Appliquez aussi la requête SET NAMES 'utf8mb4' dès votre connexion à la BDD.

En effet, si vous ne respectez pas ces deux consignes, alors certains textes (emojis, caractères chinois spécifiques, etc) ne seront pas stockables en BDD et génèreront une erreur.

Sélectionner les 3 plus grands…

-- 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

Sélectionner les infos des 3 plus forts joueurs de chaque alliance

N'utilisez pas de variable (@xyz) dans les SELECT: la documentation l'a déprécié, et déconseille l'assignement en dehors d'un SET!

Compter les lignes répondant à une condition

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 = ?

Compte le nombre de cases terrestres, maritimes, occupées et vides du territoire d'une carte de jeu

Historiser une table

Pour historiser une table à chaque modification, utilisez un TRIGGER qui va se charger de copier la ligne existante de la table à historiser vers une table d'archive, ou qui marquera la ligne comme étant une ligne d'historique.

Cette historisation sauve toutes les infos d'un objet. Il est souvent plus intéressant de créer une table dédiée aux actions faites, et d'enregistrer ces actions quand elles ont lieu (log).

UPDATE et ordre des colonnes

Dans un UPDATE, l'ordre des colonnes est important car MySQL utilisera la nouvelle valeur de chaque colonne précédente pour mettre à jour les colonnes suivantes. 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. Elle vaudra donc 2.

Les commandes SQL (queries)

La doc MySQL contient de nombreuses petites variantes aux syntaxes de base, dont les plus exotiques sont:

Optimisations, intégrité

Je ne ferai ici qu'un tour rapide des pistes possibles pour optimiser la partie DB de votre jeu web. Consultez le chapitre 8 de la doc pour des détails plus poussés.

Utilisez EXPLAIN voire EXPLAIN FORMAT=JSON pour comprendre pourquoi vos queries sont lentes
Vérifiez la cardinalité de vos index, l'ordre de leurs colonnes, et leur unicité ou non.
Ne sélectionnez que les colonnes utiles, et le nombre de lignes strictement nécessaire.
Utilisez les types de données les plus petits possibles (tinyint au lieu de bigint par exemple)
Utilisez toujours des transactions , éventuellement avec le niveau de verrouillage le moins restrictif possible
N'appelez pas de fonction utilisant la valeur des lignes dans vos clauses WHERE, car les index ne seront plus utilisables (privilégiez une stored generated column indexée)
L'ordre des conditions dans un WHERE n'importe pas, la présence de conditions inutiles type WHERE 1 = 1 n'impacte pas les performances
Privilégiez l'utilisation de la clause WHERE plutôt que HAVING, même si dans le cas où aucune agrégation n'est utilisée (cf la doc sur l'optimisation des WHERE )
Un sub-select SELECT … WHERE {unique_column} = … est optimisé par mysql et n'est donc pas problématiquement lent
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 doit porter sur un et un seul index (qui peut avoir plusieurs colonnes) pour être performant de type BTREE si vous faites des MIN/MAX/SUM
LIMIT n'améliore pas les performances: il dit juste à MySQL de ne transférer qu'une partie du resultset
SQL_FOUND_ROWS est lent car il oblige mysql à compter le nombre total de lignes plutôt que de s'arrêter au LIMIT
SELECT * FROM t1 WHERE (column1,column2) = (1,1) est efficace si on a posé un index sur toutes les colonnes du "row constructor" (c1, c2) mais il bloque MySQL dans le cas contraire: c1 = 1 AND c2 = 2 est souvent plus sûr
Dans certains cas, il peut e^tre utile d'indiquer à mysql quels indexes utiliser dans une query , voire utiliser des optimize hints
La fonction BENCHMARK texte les performances d'une expression, fonction, query etc
LEFT JOIN / JOIN sur des tables + sous-jointures existent: t1 LEFT JOIN (t2 LEFT JOIN t3) ON … ou SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

L'optimiseur de MySQL >=5.7

L'optimiseur de MySQL ≥5.7 a parfois tendance à trop simplifier les requêtes imbriquées, cf ma question sur StackOverflow ). Une query peut donc renvoyer des résultats différents entre Mysql 5.6 et MySQL 5.7.

Si besoin, matérialisez les subqueries via DISTINCT par exemple.

⇇ Astuces de codeur