Récupérer une ligne au hasard
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';
).
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
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…
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
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:
-
SELECT FOR UPDATE
etSELECT LOCK IN SHARE MODE
-
REPLACE
-
WHERE x < ANY (…)
,WHERE x IN (…)
etWHERE x < SOME (…)
-
SELECT … WHERE (col1, col2) = (SELECT x, y FROM …);
ouWHERE (col1, col2) IN (SELECT x, y FROM t2)
-
CREATE TABLESPACE
(grouper des tables en 1 fichier) -
GET … DIAGNOSTICS
dans lesHANLDER
des procédures -
les options
PASSWORD|ENCRYPTION|ENGINE|INSERT_METHOD|MIN_ROWS|MAX_ROWS
etc des tables sql -
INSERT … SET
-
SAVEPOINT|ROLLBACK|RELEASE|COMMIT|SET TRANSACTION
etc - Les XA Transactions (cross-serveurs, ie MySQL+SMTP)
-
CURSOR
-
NOWAIT|SKIP LOCKED
-
WITH
et les "Common Table Expressions" récursives ou non -
<=>
- les stored objects (procédures, fonctions, trigger, views, etc)
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
voireEXPLAIN 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 typeWHERE 1 = 1
n'impacte pas les performances -
Privilégiez l'utilisation de la clause
WHERE
plutôt queHAVING
, même si dans le cas où aucune agrégation n'est utilisée (cf la doc sur l'optimisation desWHERE
) -
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
ouBTREE
suivant si lesWHERE
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 unFULL 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 unORDER 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 typeBTREE
si vous faites desMIN/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 auLIMIT
-
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 …
ouSELECT * 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.