On supposera pour la suite que les articles de Frédéric Bouchery SQL 1 et SQL 2 n'ont aucun secret pour vous.

• rester le plus simple possible,
• ne pas représenter plusieurs fois la même chose,
• ne pas mélanger des concepts différents dans une même entité.

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

• pour un auto-increment, utiliser MEDIUMINT UNSIGNED qui permet d'identifier 16 777 215 enregistrements en ne consommant que 3 octets.
• pour les devises, l'usage de DECIMAL est encouragé pour une bonne gestion de la précision.
• pour les textes d'une table où les modifications sont très fréquentes, utiliser un CHAR. Si une taille de 255 est insuffisante, il y a la possibilité de compresser le contenu et de le décompresser à la volée grâce, par exemple, aux fonctions string gzinflate ( string data [, int length]) et string gzdeflate ( string data [, int level]). On peut augmenter sans problème le niveau de compression au maximum, c'est à dire 9, les résultats sont très satisfaisants.
• pour les dates, le type TIMESTAMP est déconseillé, lourd à gérer lors des mises à jours, il faut lui préférer DATETIME.
• un mot tout de même sur l'unique différence entre un champ de type BLOB et un champ de type TEXT : BLOB est sensible à la casse alors que TEXT est insensible à la casse.
• PHP ne faisant pas de différence entre 0 et NULL, autant déclarer tous les champs NOT NULL, on gagne ainsi 1 bit par champ et par enregistrement.

MySQL stocke les informations relatives à une table dans trois types de fichiers,

• .MYD pour les données,
• .MYI pour les index,
• .frm pour la description de la table.

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.

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

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

• index unique : une adresse pointe vers un enregistrement (ex : clé primaire),
• index : une adresse pointe vers plusieurs enregistrements,
• index multiple : une adresse est composée de plusieurs colonnes. Un index multiple peut être unique ou non,
• fulltext : c'est un type particulier d'index dont on reparle plus loin.

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 :

• le premier niveau contient un seul bloc référençant la totalité des données.
• le dernier niveau contient les feuilles de l'arbre, les adresses des données dans le fichier MYD.
• les niveaux intermédiaires sont chaînés, de manière à conserver le classement lexicographique sur l'ensemble des enregistrements.

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.

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

Table

Nom de la table utilisée.

Type

Mode d'accès aux enregistrements, du plus efficace au moins performant :

• système : cas rare où l'on attaque une table système, le contenu de la table est assimilé à une constante.
• const : la requête ne considère qu'un seul enregistrement de la table, celui-ci sera lu en premier et assimilé à une constante pour la suite des opérations.
• eq_ref : parcours d'un index unique. C'est le meilleur type de jointure possible.
• ref : parcours d'un index non unique. Cette technique est convenable pour un faible nombre d'enregistrements à rapporter.
• range : recherche par intervalle avec un index.
• index : parcours séquentiel de l'index.
• ALL : parcours séquentiel de la table.

Possible_keys

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.

key

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

key_len

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.

ref

Colonnes ou constantes utilisées avec la clé pour atteindre le bon enregistrement.

rows

Estimation du nombre d'enregistrements à parcourir (pour chaque combinaison) avant de trouver le bon.

Extra

Informations complémentaires :

• distinct : l'évaluation de cette table s'arrête au premier résultat trouvé (efficace).
• not exists : l'évaluation de la table nécessite un passage unique (efficace).
• range checked for each record (key map: x) : le choix de l'index est réévalué pour chaque combinaison. Cette méthode est quand même plus efficace que de ne pas utiliser d'index du tout...
• Using Filesort : MySQL fera un passage supplémentaire de façon à ordonner les résultats avant la suite des opérations.
• Using Index : La table (le fichier MYD) n'est pas ouverte, tout est dans l'index (très très efficace).
• Using temporary : MySQL crée une table temporaire (à votre insu) de manière à exécuter la requête. C'est le cas typique où la clause ORDER BY ne repose pas sur les mêmes colonnes que la clause GROUP BY.
• where used : filtrage complémentaire réalisé avant la suite des opérations.

Pour un gain de performance, les cas Using Filesort et Using temporary sont à étudier de près car très consommateurs de ressources.

Exemple :

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

Résultat

Explication

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.

Conclusion

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.

• dans une clause where (dans les deux parties d'un AND ou d'un OR),
• pour réaliser une jointure,
• dans un MAX() ou un MIN(),
• dans un ORDER BY ou un GROUP BY
• dans un select qui éviterait de lire la table.

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.

Exemples de bonne utilisation d'index :

... 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 */

Exemples de mauvaise utilisation d'index :

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

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 :

• SQL_SMALL_RESULT
• SQL_BIG_RESULT
• HIGH_PRIORITY

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.

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

• ne supportent pas les TEXT et BLOB,
• doivent être déclarée avec un MAX_ROWS pour ne pas accidentellement utiliser toute la mémoire,
• la recherche par sous-clé ne fonctionne pas,
• l'auto-increment n'est pas supporté,
• impossible de créer un INDEX contenant des valeurs à NULL,
• on ne peut pas utiliser la clause ORDER BY sur un index,
• comme tout réside en mémoire, une table temporaire sera détruite en cas de crash du serveur et doit être reconstruite au redémarrage.
• Dans la version 3.x de MySQL, on ne peut utiliser qu'une table temporaire par requête.
• RENAME ne fonctionne pas non plus.

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.

MySQL 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 ...     |
+----+-------------------+---------------------------------------------+

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

Restrictions :

• Les index FULLTEXT ne peuvent être qu'une combinaison de champs TEXT et / ou VARCHAR (sauf dans le mode IN BOOLEAN).
• les paramètres de MATCH doivent être contenus dans le même index fulltext (sauf dans le mode IN BOOLEAN).
• L'argument de AGAINST() doit être une chaîne de caractères (constante).
• Si un mot recherché est présent dans plus de la moitié des enregistrements, sa valeur sémantique sera assimilée à zéro, aucun résultat ne sera remonté.

Nommer tous les champs

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.

Eviter les produits cartésiens

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 :

1. mysql> SELECT a.nom, f.date from ARTICLE a, FACTURE f where a.ID = f.ID and f.DATE = '20010101'
2. 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.

Libérer les ressources

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.