Préambule |
On supposera pour la suite que les articles de Frédéric Bouchery SQL 1 et SQL 2 n'ont aucun secret pour vous.
Création d'une base |
Je vous conseille un petit coup de Google pour trouver un article à base de Merise pour vous aider à concevoir ou à améliorer votre base.
De façon à faciliter la vie à MySQL, l'usage des champs à longueur variable tels que VARCHAR, TEXT et BLOB est largement déconseillé. Nous reviendrons plus loin sur l'utilité de créer une table avec des enregistrements de taille 'fixe'.
Nous n'allons pas illustrer ici une méthode de modélisation mais évoquer rapidement les types de colonnes MySQL. (M décrit la taille maximale d'affichage du champ, D est mis pour décimal, UNSIGNED permet d'optimiser le stockage de chiffres positifs, ZEROFILL
remplit les chiffres de zéros).
Type | Nb Octets | Commentaires |
TINYINT[(M)] [UNSIGNED] [ZEROFILL] | 1 | Très petit entier. Va de -128 à 127, de 0 à 255 si non signé |
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] | 2 | Petit entier. Va de -32 768 à 32 767, de 0 à 65 535 si non signé |
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | 3 | Entier moyen. Va de -8 388 608 à 8 388 607, de 0 à 16 777 215 si non signé |
INT[(M)] [UNSIGNED] [ZEROFILL] | 4 | Va de -2 147 483 648 à 2 147 483 647, de 0 à 4 294 967 295 si non signé |
INTEGER[(M)] [UNSIGNED] [ZEROFILL] | 4 | Synonyme de INT |
BIGINT[(M)] [UNSIGNED] [ZEROFILL] | 8 | Grand entier. Va de -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807, de 0 à 18 446 744 073 709 551 615 si non signé |
FLOAT(precision) [ZEROFILL] | 4 si precision <= 24 ou 8 si 25 <= precision <= 53 | Flottant |
FLOAT[(M,D)] [ZEROFILL] | 4 | Flottant à precision simple. Va de -1.175494351E-38 à 3.402823466E+38 |
DOUBLE[(M,D)] [ZEROFILL] | 8 | Flottant à double precision. Va de -2.2250738585072014E-308 à 1.7976931348623157E+308 |
DOUBLE PRECISION[(M,D)] [ZEROFILL] | 8 | Synonyme de DOUBLE |
REAL[(M,D)] [ZEROFILL] | 8 | Synonyme de DOUBLE |
DECIMAL[(M[,D])] [ZEROFILL] | M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D) | Flottant stocké comme une chaîne de caractères |
NUMERIC(M,D) [ZEROFILL] | M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D) | Synonyme de DECIMAL |
DATE | 3 | Date 'YYYY-MM-DD' allant de '1000-01-01' à '9999-12-31' |
DATETIME | 8 | Date et heure 'YYYY-MM-DD HH:MM:SS' allant de '1000-01-01 00:00:00' à '9999-12-31 23:59:59' |
TIMESTAMP[(M)] | 4 | Date allant de '1970-01-01 00:00:00' à quelque part en 2037. L'affichage dépend de M : YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, ou YYMMDD pour, respectivement : 14, 12, 8, ou 6 |
TIME | 3 | Heure 'HH:MM:SS', allant de '-838:59:59' à '838:59:59' |
YEAR[(2|4)] | 1 | Année à 2 ou 4 chiffres allant de 1901 à 2155 pour 4 chiffres et de 1970-2069 pour 2 chiffres |
[NATIONAL] CHAR(M) [BINARY] | M octets, 1 <= M <= 255 | Chaîne de charactères (jusq'à 255) remplie d'espaces à la fin. Ces espace sont enlevés lors d'une requête. BINARY entraine une recherche sensible à la casse |
BIT | 1 | Synonyme de CHAR(1) |
BOOL | 1 | Synonyme de CHAR(1) |
CHAR | 1 | Synonyme de CHAR(1) |
[NATIONAL] VARCHAR(M) [BINARY] | L+1 octets, où L <= M et 1 <= M <= 255 | Chaîne de charactères (jusqu'à 255). BINARY entraine une recherche sensible à la casse |
TINYBLOB | L+1 octets, où L < 2^8 | Chaîne de charactères (jusqu'à 255), sensible à la casse |
TINYTEXT | L+1 octets, où L < 2^8 | Chaîne de charactères (jusqu'à 255), insensible à la casse |
BLOB | L+2 octets, où L < 2^16 | Chaîne de charactères (jusqu'à 65 535), sensible à la casse |
TEXT | L+2 octets, où L < 2^16 | Chaîne de charactères (jusqu'à 65 535), insensible à la casse |
MEDIUMBLOB | L+3 octets, où L < 2^24 | Chaîne de charactères (jusqu'à 16 777 215), sensible à la casse |
MEDIUMTEXT | L+3 octets, où L < 2^24 | Chaîne de charactères (jusqu'à 16 777 215), insensible à la casse |
LONGBLOB | L+4 octets, où L < 2^32 | Chaîne de charactères (jusqu'à 4 294 967 295), sensible à la casse |
LONGTEXT | L+4 octets, où L < 2^32 | Chaîne de charactères (jusqu'à 4 294 967 295), insensible à la casse |
ENUM('value1','value2',...) | 1 ou 2 octets, selon le nombre d'énumérations (65 535 valeurs maximum) | Enumération de 65 535 chaînes différentes |
SET('value1','value2',...) | 1, 2, 3, 4 ou 8 octets, selon de nombre de membres de l'ensemble | Ensemble qui peut contenir de 0 à plusieurs valeurs choisies dans une liste de valeurs |
Configuration physique de MySQL |
MySQL stocke les informations relatives à une table dans trois types de fichiers,
Cette architecture est à prendre en compte pour une utilisation courante : un accès aux données d'une table via un index peut nécessiter la lecture des fichiers MYD et MYI. Par contre, une optimisation extrême consiste à récupérer l'information sans ouvrir le fichier MYD.
Structure du fichier MYDLe fichier MYD contient l'intégralité des données de la table.
Il existe deux types de tables, selon la nature fixe ou dynamique de leurs champs. Dans les deux cas, les enregistrements sont ajoutés dans le fichier sans aucun classement. Dans le cas d'enregistrements à taille variable, toute modification (insert / update / replace / delete) peut entraîner la création de vides dans le fichier, et de fragmentation d'un enregistrement. Cette situation n'est pas souhaitable mais peut être 'soignée' grâce à l'utilisation de la commande OPTIMIZE TABLE. Pour éviter cette situation, il suffit d'éviter les champs VARCHAR, TEXT et BLOB.
Pour le cas de l'utilisation d'une table sujette à de nombreuses modifications, (telle qu'une table stockant des informations de session), l'utilisation d'une table temporaire est recommandée.
Structure du fichier MYILe fichier MYI contient les adresses des enregistrements dans le fichier MYD.
Cela permet d'aller lire directement le bon enregistrement sans avoir à parser toute la table, c'est un annuaire. Cette adresse peut correspondre à une colonne de la table, au début d'une colonne (dans le cas de colonne contenant du texte), à plusieurs colonnes en même temps et enfin à une combinaison de ces 3 points.
Il existe plusieurs types d'index :
Les index de MySQL, comme pour tout SGBDR, sont organisés en B-tree, c'est à dire que la distance de toute feuille à la racine est identique (Balanced en anglais).
Un fichier index est divisé en blocs, de taille identique. Chaque bloc contient les clés et les adresses correspondantes aux enregistrements contenus dans la table. A la différence des fichiers MYD, les données dans les bloc sont classées par ordre lexicographique. Ces blocs sont organisés en niveaux :
Il faut garder à l'esprit que plus une clé est petite, plus un bloc contient
de valeurs, et donc plus l'indexation est efficace...
Exemple avec un bloc de 1024 octets et une clé de 20 octets : on référence 50
enregistrements dans la racine. Avec 3 niveaux, on référence 50^3 = 125 000
enregistrements. Avec 4 niveaux, 6 250 000 enregistrements... On voit bien l'impact
d'un index sur les performances. On notera aussi que tout est fonction de la
taille de la clé. Avec une clé de 100 octets, sur 4 niveaux, nous n'aurions
plus que 10 000 enregistrements indexés... Dans le même ordre d'idée, un fichier
MYI court aura plus de chance d'être conservé dans le cache du serveur MySQL,
augmentant ainsi les performances.
Auditer sa base grâce à EXPLAIN |
Un select de MySQL fonctionne grâce à la méthode Single Sweep Multi Join. De cette façon, les résultats d'une requête sont envoyés directement au client, ligne à ligne, sans retours en arrière. Un avantage de cette méthode est que le choix de l'optimiseur est accessible a priori, grâce à l'emploi de la clause EXPLAIN. Voici les différentes valeurs obtenues par un EXPLAIN SELECT ...
Nom de la table utilisée.
Mode d'accès aux enregistrements, du plus efficace au moins performant :
Index disponibles pour la lecture de la table. Si cette valeur est à NULL, il faut très sérieusement étudier la possibilité de créer un index sur cette table.
Index finalement utilisé par MySQL pour exécuter la requête. On peut forcer MySQL à ignorer un index (IGNORE INDEX) ou bien le forcer à en utiliser un autre (USE INDEX).
Longueur de la clé utilisée. C'est ici qu'il faut concentrer ses efforts de façon à réduire le plus possible ce chiffre : en utilisant un plus petit INT, en utilisant un préfixe de colonne. Plus la clé est courte, plus l'enregistrement est localisé rapidement. Dans le cas d'une clé multiple (portant sur plusieurs colonnes), c'est ici que l'on peut vérifier combien de sous clés sont effectivement utilisées.
Colonnes ou constantes utilisées avec la clé pour atteindre le bon enregistrement.
Estimation du nombre d'enregistrements à parcourir (pour chaque combinaison) avant de trouver le bon.
Informations complémentaires :
Pour un gain de performance, les cas Using Filesort et Using temporary sont à étudier de près car très consommateurs de ressources.
Un développeur constate qu'une requête met 15 minutes
à s'exécuter. Le premier reflexe à avoir est de réaliser un explain :
mysql> EXPLAIN
-> SELECT VARIETE.CODE, COMMANDE.CODE_T,
-> Sum(COMMANDE.QUANT-LIVRAISON.QUANT) AS QCOM
-> FROM VARIETE
-> INNER JOIN COMMANDE ON VARIETE.CODE = COMMANDE.CODE
-> INNER JOIN COM_BASE ON COMMANDE.NCOMMANDE = COM_BASE.NCOMMANDE
-> LEFT JOIN LIVRAISON ON (COMMANDE.CODE = LIVRAISON.CODE) AND
-> (COMMANDE.NCOMMANDE = LIVRAISON.NCOMMANDE)
-> WHERE (((COM_BASE.CONF)='O'))
-> GROUP BY VARIETE.CODE, COMMANDE.CODE_T
table | type | possible_keys | key | key_len | ref | rows | Extra |
COMMANDE | ALL | PRIMARY | 17068 | Using temporary | |||
VARIETE | eq_ref | PRIMARY | PRIMARY | 4 | COMMANDE.CODE | 1 | where used; Using index |
COM_BASE | ref | PRIMARY | PRIMARY | 3 | COMMANDE.NCOMMANDE | 1 | where used |
LIVRAISON | ALL | 13855 |
La table COMMANDE est parsée dans son intégralité, les jointures sur les tables VARIETE et COM_BASE sont parfaites, la table VARIETE n'est même pas ouverte, l'index seul suffit. Par contre les 13855 enregistrements de la table LIVRAISON seront parcourus 17068 fois... voilà la cause de la lenteur de l'exécution.
Ajoutons un index sur les champs réalisant la jointure:
mysql> ALTER TABLE `LIVRAISON` ADD INDEX `key` (`CODE`,`NCOMMANDE`)
Nouveau temps d'exécution : 0.66s.
On peut encore améliorer : vu le Using temporary sur la table COMMANDE ? Le temps d'exécution étant devenu acceptable, on laisse les choses dans l'état.
Attention : EXPLAIN select ... WHERE ...
sur une table contenant
moins de deux enregistrements conduit à l'obtention d'un champ Comment comme
ceci :
Impossible WHERE noticed after reading const tables
L'optimiseur ne peut effectuer de tri sur une table assimilée à une constante
... effectivement :
Explain ne fonctionne qu'en fonction des données contenues dans les tables.
Quels champs indexer ? |
Il faut également penser à la création d'une clé sur plusieurs colonnes, dans le cas où il faudrait créer deux index sur une même table.
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5 /* optimisé comme "index_part1='hello'"
*/
... WHERE index1=1 and index2=2 or index1=3 and index3=3; /* index utilisé sur
index1 mais pas sur index2 ni index 3 */
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 n'est pas utilisé
*/
... WHERE index=1 OR A=10 /* l'index n'est pas utilisé des deux côtés du OR
*/
... WHERE index_part1=1 OR index_part2=10 /* Aucun index ne couvre tous les
enregistrements*/
Pour le cas de select sur des champs alphanumériques, il faut penser à la création d'index sur une sous partie de la chaîne, cela peut contribuer à améliorer les performances. Il faut penser également aux index FULLTEXT.
On considère qu'un index est inutile s'il ne peut éliminer plus de 70% des enregistrements de la table lors d'un select.
Divers trucs d'optimisation |
Il est possible de donner un coup de main à l'optimiseur MySQL en lui indiquant qu'un select ne va rapporter qu'un faible volume de valeurs ou, à l'inverse, un important resultset. Si on est vraiment pressé, on peut également sauter la queue d'accès au niveau du serveur... On peut alors ajouter les clauses :
Une autre option utile est STRAIGHT_JOIN. Elle permet de forcer l'optimiseur à évaluer les jointures dans l'ordre d'apparition des tables dans le FROM.
La version 4.0.1 de MySQL permet de cacher les select au niveau du serveur.
Restrictions sur les tables temporairesCes tables n'ont pas de problèmes de gestion d'espace, et les temps de réponses sont excellents mais il y a un prix :
Au niveau des inserts, il y a un moyen efficace de gagner du temps, c'est de faire des insert multiples et / ou de verrouiller la table en écriture. Sans le LOCK TABLES, autant d'index sur le buffer seraient créés. Ici, le buffer ne sera flushé qu'une fois :
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;
De la même façon, on peut vider le buffer d'un seul coup en utilisant l'option insert DELAYED. La table restera alors ouverte jusqu'au remplissage du buffer.
Index FULLTEXTMySQL possède une fonction très intéressante de recherche : FULLTEXT. Cette façon de procéder est à retenir pour implémenter un moteur de recherche sur votre site. En reprenant l'exemple de la documentation de MySQL, on peut créer une table contenant un tel indice et insérer quelques valeurs :
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
mysql> INSERT INTO articles VALUES
-> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'),
-> (0,'How To Use MySQL Efficiently', 'After you went through a ...'),
-> (0,'Optimising MySQL','In this tutorial we will show how to ...'),
-> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalise ...'),
-> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'),
-> (0,'MySQL Security', 'When configured properly, MySQL could be ...');
Lançons quelques recherches fulltext :
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+---------------------------------------------+
| id | title | body |
+----+-------------------+---------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison we ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase Management ... |
+----+-------------------+---------------------------------------------+
-> deux lignes sont évaluées par la fonction MATCH et retournées classées selon
la pertinence du résultat. Voyons dans le détail comment cela fonctionne :
mysql> SELECT id,MATCH title,body AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+ | id | MATCH (title,body) AGAINST ('Tutorial') | +----+-----------------------------------------+ | 1 | 0.64840710366884 | | 2 | 0 | | 3 | 0.66266459031789 | | 4 | 0 | | 5 | 0 | | 6 | 0 | +----+-----------------------------------------+mysql> SELECT id, body, MATCH title,body AGAINST ( -> 'Security implications of running MySQL as root') AS score -> FROM articles WHERE MATCH (title,body) AGAINST -> ('Security implications of running MySQL as root');
+----+-----------------------------------------------+-----------------+ | id | body | score | +----+-----------------------------------------------+-----------------+ | 4 | 1. Never run mysqld as root. 2. Normalise ... | 1.5055546709332 | | 6 | When configured properly, MySQL could be ... | 1.31140957288 | +----+-----------------------------------------------+-----------------+ Depuis la version 4.0.1 de MySQL, on peut executer des recherches booléennes :mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ( -> '+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-----------------------------------------------+ | id | title | body | +----+------------------------------+-----------------------------------------------+ | 1 | MySQL Tutorial | DBMS stands for DataBase Management ... | | 2 | How To Use MySQL Efficiently | After you went through a ... | | 3 | Optimising MySQL | In this tutorial we will show how to ... | | 4 | 1001 MySQL Trick | 1. Never run mysqld as root. 2. Normalise ... | | 6 | MySQL Security | When configured properly, MySQL could be ... | +----+------------------------------+-----------------------------------------------+
Il convient d'éviter de faire un select *. De la même façon, les fonctions utilisées dans un select doivent avoir un alias.
Il faut bien penser à créer une jointure entre toutes les tables d'un même select. Il y a plusieurs syntaxes possibles, c'est à chacun de choisir :
mysql> SELECT a.nom, f.date from ARTICLE a, FACTURE f where a.ID
= f.ID and f.DATE = '20010101'
mysql> SELECT a.nom, f.date from ARTICLE a INNER JOIN FACTURE f
on a.ID = f.ID where f.DATE = '20010101'
La première solution est la plus répandue. La deuxième (ANSI SQL-92) est plus correcte car plus lisible et plus propre : la jointure est réalisée grâce à JOIN et la restriction est réalisée dans la clause WHERE. De plus, on peut plus facilement passer à une jointure ouverte lorsque l'on a l'habitude de la deuxième syntaxe.
Il est de bon ton de faire un mysql_free_result à l'issue du traitement du select, de façon à ne pas encombrer la mémoire vive avec des données inutiles.