Chapitre 7 Optimisation de MySQL

Table des matières

7.1 Présentation de l'optimisation
7.1.1 Limitations et inconvénients des choix conceptuels de MySQL
7.1.2 Portabilité
7.1.3 Pour quoi avons nous utilisé MySQL ?
7.1.4 La suite de tests MySQL
7.1.5 Utiliser vos propres tests de performance
7.2 Optimisation des commandes SELECT et autres requêtes
7.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT)
7.2.2 Mesurer les performances d'une requête
7.2.3 Vitesse des requêtes SELECT
7.2.4 Comment MySQL optimise les clauses WHERE
7.2.5 Optimisation d'intervalle
7.2.6 Optimisation de combinaison d'index
7.2.7 Comment MySQL optimise IS NULL
7.2.8 Comment MySQL optimise DISTINCT
7.2.9 Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN
7.2.10 Comment MySQL optimise ORDER BY
7.2.11 Comment MySQL optimise les clauses GROUP BY
7.2.12 Comment MySQL optimise LIMIT
7.2.13 Comment éviter les analyses de tables
7.2.14 Vitesse des requêtes INSERT
7.2.15 Vitesses des commandes UPDATE
7.2.16 Rapidité des requêtes DELETE
7.2.17 Autres conseils d'optimisation
7.3 Verrouillage de tables
7.3.1 Méthodes de verrouillage
7.3.2 Problème de verrouillage de tables
7.4 Optimiser la structure de la base de données
7.4.1 Conception
7.4.2 Rendre vos tables aussi compactes que possible
7.4.3 Index de colonnes
7.4.4 Index sur plusieurs colonnes
7.4.5 Comment MySQL utilise les index
7.4.6 Le cache de clé des tables MyISAM
7.4.7 Comment MySQL compte les tables ouvertes
7.4.8 Quand MySQL ouvre et ferme les tables
7.4.9 Inconvénients de la création d'un grand nombre de tables dans la même base de données
7.5 Optimiser le serveur MySQL
7.5.1 Réglage du système, au moment de la compilation, et paramètres du démarrage
7.5.2 Réglage des paramètres du serveur
7.5.3 Contrôle des performances de l'optimisateur de requêtes
7.5.4 Influences de la compilation et des liaisons sur la vitesse de MySQL
7.5.5 Comment MySQL gère la mémoire
7.5.6 Comment MySQL utilise le DNS
7.6 Problèmes avec les disques
7.6.1 Utiliser des liens symboliques

L'optimisation est une tâche complexe car elle nécessite une parfaite compréhension du système en entier. Alors qu'il serait possible de faire quelques optimisations localement avec une faible connaissance de votre système ou de votre application, plus vous voulez un système optimal, plus il est nécessaire de le connaître.

Ce chapitre va tenter d'expliquer et de donner des exemples de différentes manières d'optimiser MySQL. Souvenez-vous, malgré tout, qu'il existe toujours d'autres moyens (de plus en plus difficiles) de rendre le système plus véloce.

7.1 Présentation de l'optimisation

Le facteur le plus important pour optimiser un système est la conception de base. Vous devez aussi savoir quel type de ralentissement votre système peut rencontrer, et ce qu'il doit faire.

Les ralentissements les plus fréquents sont :

  • Recherches sur le disque Il faut du temps pour trouver une donnée sur un disque. Avec les disques modernes, le temps moyen d'accès est de 10ms, ce qui donne environs 100 recherches par seconde. Ce temps s'améliore lentement avec les nouveaux disques, et il est très difficile d'optimiser cette valeur pour une table unique. Pour optimiser les accès disques, il faut distribuer les données sur plusieurs disques.

  • Lectures et écritures sur le disque Lorsque le disque a atteint la bonne position, nous devons y lire des données. Les disques modernes délivrent environs 10 à 20 Mo de données par seconde. Cela est facile à optimiser, car vous pouvez lire en paralelle sur plusieurs disques.

  • Cycles processeurs Lorsque les données sont en mémoire centrales (ou si elles y étaient déjà), nous devons traiter les données pour obtenir le résultat. La taille des tables par rapport à la mémoire disponible est le principal facteur limitant. Avec des tables de petites taille, ce n'est jamais un problème.

  • Accès mémoire Lorsque le processeur doit traiter plus de données que ce qui peut être contenu dans les caches du processeur, alors la vitesse de transfert avec la mémoire devient limitante. C'est un facteur assez extraordinaire, mais il faut en être conscient.

7.1.1 Limitations et inconvénients des choix conceptuels de MySQL

Avec les tables de type MyISAM, MySQL utilise un verrouillage extrêmement rapide (plusieurs lectures / une seule écriture). Le plus gros problème avec ce type de table survient quand vous avez un mélange de flux de modifications et des sélections lentes sur la même table. Si c'est une problème sur plusieurs tables, vous pouvez utiliser un autre type de table pour celles ci. See Chapitre 14, Moteurs de tables MySQL et types de table.

MySQL peut utiliser à la fois des tables transactionnelles et des tables non-transactionnelle. Pour pouvoir travailler tranquillement avec des tables non-transactionnelles (qui n'ont pas la possibilité de revenir en arrière si quelque chose se passe mal) MySQL suit les règles suivantes:

  • Toutes les colonnes ont une valeur par défaut.

  • Si vous insérez une mauvaise valeur dans une colonne (par exemple NULL dans une colonne NOT NULL, ou encore une valeur numérique trop grande dans une colonne numérique), MySQL prendra en compte "la meilleure valeur possible" plutôt que de sortir une erreur. Pour les valeurs numériques, il s'agit de 0, de la valeur la plus petite possible, ou de la valeur la plus grande possible. Pour les chaînes, il s'agit soit d'une chaîne vide, soit de la chaîne la plus longue que peut contenir la colonne.

  • Toutes les expressions calculées retournent une valeur qui peut être utilisées à la place d'afficher un message d'erreur. Par exemple, 1/0 retourne NULL

Pour plus d'informations, voyez See Section 1.5.6, « Comment MySQL gère les contraintes ».

Ce qui précède signifie qu'il ne faut pas que le contrôle du contenu des champs soit fait au niveau de MySQL, mais au niveau de l'application.

7.1.2 Portabilité

Comme tous les serveurs SQL implémentent différemment le langage SQL, cela prend de solides connaissances pour écrire des applications SQL portables. Pour les insertions et sélections simples, c'est très simple, mais plus vos besoins se complexifient, plus c'est abscons. Si vous voulez une application qui fonctionne rapidement sur de nombreuses bases de données, c'est même encore plus difficile.

Pour rendre une application complexe portable, vous pouvez commencer par choisir une panoplie de serveurs SQL avec lesquels travailler.

Vous pouvez utiliser le programme/page web de MySQL appelé crash-me pour trouver les fonctions, types et limites que vous pouvez utiliser avec un panel de serveurs de bases de données. Les tests de crash-me ne vérifient pas tout, mais il est déjà très exhaustif avec plus de 450 points de tests.

Par exemple, vous ne devriez pas avoir de nom de colonne supérieur à 18 caractères, si vous voulez pouvoir utiliser Informix ou DB2.

Les programmes de tests crash-me et de performances de MySQL sont très indépendants du serveur. En regardant comment nous avons géré ces situations, vous pouvez comprendre comment rendre votre propre code indépendant du serveur. Les tests de performances sont situés dans le dossier sql-bench de la distribution source de MySQL. Ils sont écrits en Perl avec l'interface DBI, ce qui résout les problèmes de connexion.

Voyez http://www.mysql.com/information/benchmarks.html pour connaître les résultats de ces benchmarks.

Comme vous pouvez le voir avec ces résultats, toutes les bases de données ont leur point faible. En réalité, elles ont toutes une approche différente du même problème, et cela conduit à des comportements spécifiques.

Si vous avez besoin de l'indépendance au serveurs de bases de données, vous devez bien connaître les faiblesses de chaque serveur. MySQL est très rapide pour lire et modifier les données, mais peine lorsque les lectures et écritures sont lentes sur la même table. Oracle, d'un autre coté, a de gros problèmes lorsque vous essayez d'accéder aux données que vous avez modifié récemment (jusqu'à ce qu'elles soient écrites sur le disque). Les bases de données transactionnelles en général ne sont pas très douées pour générer des tables résumés à partir des tables de log, car dans ce cas, le verrouillage de ligne est inutile.

Pour rendre votre application reellement indépendante de la base de données, vous devez définir un classe très souple à travers laquelle vous allez vous interfacer pour manipuler vos données. Comme le langage C++ est disponible sur la plupart des systèmes, cela rend les classes C++ très pratiques pour cette tâche.

Si vous utilisez une fonctionnalité spécifique d'une base de données (comme la commande REPLACE de MySQL), il vous faut aussi coder la même commande pour les autres serveurs (qui sera alors plus lente). Avec MySQL, vous pouvez aussi utiliser la syntaxe /*! */ pour utiliser des mots clés spécifiques de MySQL dans une requête. Le code entre /* */ sera alors traité comme un commentaire et ignoré par la plupart des autres serveurs SQL.

Si les hautes performances sont plus importantes que l'exactitude, comme pour les applications web, il est possible de créer une couche application qui met en cache les résultats et vous donne de meilleures performances. En laissant les anciens résultats se périmer, vous pouvez garder un cache à jour. Cela vous donne une méthode pour gérer les grandes charges, durant lesquelles vous pouvez augmenter la taille du cache, et augmenter la durée de vie.

Dans ce cas, les informations de création de tables doivent contenir les informations de taille initiale du cache, et la fréquence de rafraîchissement des tables. See Section 5.11, « Cache de requêtes MySQL ».

7.1.3 Pour quoi avons nous utilisé MySQL ?

Pendant le développement initial de MySQL, les fonctions de MySQL ont été créées pour convenir à un maximum de clients. Celles ci supporte des entrepôts de données pour deux des plus gros revendeurs suédois.

Nous recevons chaque semaine le résumé de toutes les transactions par carte de toutes les boutiques, et nous sommes chargés de fournir des informations utiles aux gérants des boutiques pour les aider à comprendre comment leurs propres campagnes publicitaires touchent leurs clients.

Les données sont assez énormes (près de 7 millions de résumés de transactions par mois), et nous avec les données de 4-10 ans que nous présentons aux utilisateurs. Nous avons chaque semaine des requêtes des clients qui veulent un accès 'instantané' aux nouveaux rapports sur ces données.

Nous avons réussi en stockant toutes les informations dans des tables de 'transactions' compressées. Nous avons une série de macros (scripts) qui génère des tables de résumés groupés par différents critères (groupe de produits, identifiant de client, boutique ...). ces rapports sont des pages web générées dynamiquement par un petit script Perl qui parcours une page web, exécute les requêtes SQL, et insère les résultats. Nous aurions bien utilisé PHP ou mod_perl à la place, mais ils n'étaient pas disponibles à cette époque.

Nous avons écrit un outil en C pour la représentation graphique des données qui génère des GIFs à partir du résultat de requêtes SQL (avec quelques traitements sur le résultat). Ceci est également effectué dynamiquement par le script Perl qui parcourt les fichiers HTML.

Pour la plupart des cas, un nouveau rapport peut simplement être fait en copiant un script existant, et en modifiant la requête SQL qu'il exécute. Dans certains cas, nous aurons besoin d'ajouter des champs à une table de résumé existante ou d'en générer une nouvelle, mais c'est tout de même toujours assez simple, car nous gardons toutes les tables de transactions sur disque. (Actuellement, nous avons au moins 50 Go de tables de transactions et 200 Go d'autres données sur les clients.)

Nous donnons également accès aux tables de résumés à nos clients directement avec ODBC, de sorte que les utilisateurs avancés puissent traiter les données eux-mêmes .

Nous n'avons eu aucun problème à supporter tout cela avec une relativement modeste Sun Ultra SPARCStation (2x200 MHz). Nous avons récemment amélioré l'un de nos serveurs en un bi-CPU 400 MHz UltraSPARC, et nous projetons actuellement de supporter les transactions au niveau du produit, ce qui signifie un décuplement des données. Nous pensons pouvoir y arriver uniquement en ajoutant des disques supplémentaires à nos systèmes.

Nous expérimentons aussi Intel-Linux, pour pouvoir avoir plus de puissance CPU pour moins cher. Comme nous utilisons désormais le format binaire portable pour les bases de données (nouveauté de la version 3.23), nous utiliserons cela pour quelques parties de l'application.

Nous avons au départ le sentiment que Linux s'acquittera mieux des faibles et moyennes charges tandis que Solaris fonctionnera mieux sur les grosses charges à cause des I/O disques extrêmes, mais nous n'avons actuellement aucune conclusion à ce propos. Après quelques discussion avec un développeur du noyau Linux, un effet de bord de Linux pourrait tant de ressources aux travaux de traitement que les performances de l'interface interactive peut devenir vraiment lente. Cela fait apparaître la machine très lente et sans réponse lorsque de gros traitements sont en cours. Heureusement, cela sera mieux géré dans les futurs noyaux de Linux.

7.1.4 La suite de tests MySQL

Ceci devrait comprendre une description technique de la suite de tests de performances de MySQL (et crash-me), mais cette description n'est pas encore écrite. Actuellement, vous pouvez vous faire une idée des tests en regardant le code et les résultats dans le répertoire sql-bench dans toutes les distributions de sources de MySQL.

Cette suite de test est censée permettre à utilisateur de comparer ce qu'une implémentation SQL donnée réussi bien ou mal.

Sachez que ces tests de performances lancent en un seul thread, donc il mesure le temps minimum pour chaque opération. Nous projetons pour le futur d'ajouter de nombreux tests multi-thread à cette suite de tests.

Par exemple, (tous ont été lancés sur une même machine NT 4.0)

Lecture de 2000000 lignes indexéesSecondesSecondes
mysql367249
mysql_odbc464 
db2_odbc1206 
informix_odbc121126 
ms-sql_odbc1634 
oracle_odbc20800 
solid_odbc877 
sybase_odbc17614 
Insertion de lignes (350768SecondesSecondes
mysql381206
mysql_odbc619 
db2_odbc3460 
informix_odbc2692 
ms-sql_odbc4012 
oracle_odbc11291 
solid_odbc1801 
sybase_odbc4802 

Le test ci-dessus a été exécuté avec un index de cache de 8 Mo.

Nous avons rassemblé d'autres résultats de tests à http://www.mysql.com/information/benchmarks.html.

Notez que Oracle n'est pas inclus dans ces tests car ils ont demandé à être retirés. Tous les tests d'Oracle doivent être faits par Oracle! Nous croyons que cette politique va biaiser fortement les tests en faveur de Oracle, car les tests ci-dessus sont supposés montrer ce qu'une installation simple peut faire pour un client simple.

Pour utiliser la suite de tests, les prerequis suivants doivent être vérifiés :

  • La suite de tests est disponible dans la distribution source de MySQL, et vous devez avoir téléchargé cette distribution. Vous pouvez télécharger la version publiée sur le site de http://www.mysql.com/downloads/, ou utiliser celle du serveur de développement (see Section 2.4.3, « Installer à partir de l'arbre source de développement »).

  • Les scripts de tests ont été écrits en Perl, et utilisent le module Perl DBI pour accéder aux serveurs, donc DBI doit être installée. Vous aurez aussi besoin des pilotes spécifiques DBD de chaque serveur que vous voulez tester. Par exemple, pour tester MySQL, PostgreSQL et DB2, les modules DBD::mysql, DBD::Pg et DBD::DB2 doivent être installés. See Section 2.9, « Commentaires sur l'installation de Perl ».

La suite de tests est située dans le dossier sql-bench de la distribution source de MySQL. Pour exécuter la suite de tests, compilez MySQL, puis allez dans le dossier sql-bench et exécutez le script run-all-tests :

shell> cd sql-bench
shell> perl run-all-tests --server=server_name

server_name est un des serveurs supportés. Pour avoir la liste de toutes les options et serveurs supportés. utilisez cette commande :

shell> perl run-all-tests --help

Le script crash-me est aussi situé dans le dossier sql-bench. crash-me essaie de déterminer quelles fonctionnalités un serveur supporte, et quelles sont ses limitations. Par exemple, le test détermine :

  • Les types de colonnes supportés

  • Le nombre d'index supportés

  • Les fonctions supportées

  • La taille maximale d'une requête

  • La taille maximale d'une colonne VARCHAR

7.1.5 Utiliser vos propres tests de performance

Vous devriez vraiment penser à préparer des tests de performances pour votre application et base, afin d'identifier les opérations les plus lentes. En les corrigeant (ou en rempla¸ant ces opérations des "modules simples") vous pouvez facilement identifier les autres opérations lentes (et ainsi de suite...). Même si la performance générale de votre application est suffisante, vous devriez prévoir où seront les prochains freins, et décider d'anticiper leur résolution, avant que vous n'ayez vraiment besoin de ces performances.

Pour avoir un exemple de programme de tests portables, voyez la suite de tests MySQL. See Section 7.1.4, « La suite de tests MySQL ». Vous pouvez prendre n'importe quel programme de cette suite, le modifier pour l'adapter à vos besoins, et essayer différentes solutions à votre problème : il suffit de tester et d'identifier la solution la plus rapide pour vous.

Une autre suite de tests est la "Open Source Database Benchmark", disponible sur le site de http://osdb.sourceforge.net/.

Il est très fréquent que des problèmes surviennent lorsque le système subit une forte charge. Nous avons de nombreux clients qui nous contactent lorsqu'ils ont mis leur système en production, et rencontré des problèmes de charge. Pour chacun d'entre eux, les problèmes étaient des problèmes simples de conceptions (les scans de tables ne sont pas bons sous forte charge) ou des problèmes liés au système d'exploitation ou les bibliothèques. La plupart auraient été vraiment plus simples à tester si le système n'était pas déjà en production.

Pour éviter des problèmes comme ceux-là, vous devriez mettre quelques efforts dans les tests de votre application dans son ensemble, avant de la mettre dans les pires conditions. Vous pouvez utiliser le programme Super Smack pour cela, qui est disponible à http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. Comme son nom le suggère, il va mettre votre système à genoux si vous lui demandez, alors assurez vous de ne l'utiliser qu'avec votre système de développement.

7.2 Optimisation des commandes SELECT et autres requêtes

Premièrement, ce qui affecte toutes les requêtes : plus votre système de droits est compliqué, plus vous aurez des baisses de performances.

Si vous n'avez aucun GRANT effectué, MySQL optimisera les vérifications de droits. Donc, si vous avez un système volumineux, il serait bénéfique d'éviter les grants. Sinon les performances seront réduites. Par exemple, si vous n'avez pas de droits de niveau table ou colonne, le serveur n'a pas à vérifier le contenu des tables tables_priv et columns_priv. Similairement, si vous n'avez pas de limites de ressources, le serveur n'a pas de comptes de ressources à faire. Si vous avez un très haut niveau de requêtes, il peut se révéler bénéfique d'utiliser une structure de droits simplifiée, pour réduire le temps de vérification.

Si votre problème est spécifique à une expression MySQL ou une fonction, vous pouvez utiliser la fonction BENCHMARK() du client mysql pour effectuer un test de performances. La syntaxe est BENCHMARK(loop_count,expression). Par exemple :

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

Ce qui précède montre que MySQL peut exécuter 1 000 000 d'additions en 0.32 secondes sur un PentiumII 400MHz.

Toutes les fonctions MySQL sont sensé être optimisées, mais il peut y avoir quelques exceptions et la fonction BENCHMARK(nombre_de_fois,expression) est un très bon moyen de trouver ce qui cloche dans vos requêtes.

7.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT)

EXPLAIN tbl_name

Ou :

EXPLAIN SELECT select_options

EXPLAIN nom_de_table est un synonyme de DESCRIBE nom_de_table ou SHOW COLUMNS FROM nom_de_table.

  • La syntaxe EXPLAIN tbl_name est synonyme de DESCRIBE tbl_name ou SHOW COLUMNS FROM tbl_name.

  • Lorsque vous faites précéder une commande SELECT avec le mot clé EXPLAIN, MySQL vous explique comment il va traiter la commande SELECT, choisir les tables et index pour les jointures.

Cette section fournit des informations sur comment utiliser EXPLAIN.

Avec l'aide de EXPLAIN, vous pouvez identifier les index à ajouter pour accélérer les commandes SELECT.

Vous devriez souvent utiliser la commande ANALYZE TABLE pour mettre à jour les statistiques de cardinalité de vos tables, qui affectent les choix de l'optimiseur. See Section 13.5.2.1, « Syntaxe de ANALYZE TABLE ».

Vous pouvez aussi voir si l'optimiseur fait les jointures dans un ordre vraiment optimal. Pour forcer l'optimiseur à utiliser un ordre spécifique de jointure dans une commande SELECT, ajoutez l'attribut STRAIGHT_JOIN à la clause.

Pour les jointures complexes, EXPLAIN retourne une ligne d'information pour chaque table utilisée dans la commande SELECT. Les tables sont listées dans l'ordre dans lequel elles seront lues. MySQL résout toutes les jointures avec une seule passe multi-jointure. Cela signifie que MySQL lit une ligne dans la première table, puis recherche les lignes qui correspondent dans la seconde, puis dans la troisième, etc. Lorsque toutes les tables ont été traitées, MySQL affiche les colonnes demandées, et il remonte dans les tables jusqu'à la dernière qui avait encore des lignes à traiter. La prochaine ligne est alors traitée de la même fa¸on.

Avec MySQL version 4.1 l'affichage de EXPLAIN a été modifié pour mieux fonctionner avec les structures comme UNION, sous-requêtes, et tables dérivées. La plus importante évolution est l'addition de deux nouvelles colonnes : id et select_type.

Le résultat de la commande EXPLAIN est constitué des colonnes suivantes :

  • id

    identifiant de SELECT, le numéro séquentiel de cette commande SELECT dans la requête.

  • select_type

    Type de clause SELECT, qui peut être :

    • SIMPLE

      SELECT simple (sans utiliser de clause UNION ou de sous-requêtes).

    • PRIMARY

      SELECT extérieur.

    • UNION

      Second et autres UNION SELECTs.

    • DEPENDENT UNION

      Second et autres UNION SELECTSs, dépend de la commande extérieure.

    • SUBQUERY

      Premier SELECT de la sous-requête.

    • DEPENDENT SUBSELECT

      Premier SELECT, dépendant de la requête extérieure.

    • DERIVED

      Table dérivée SELECT.

  • table

    La table à laquelle la ligne fait référence.

  • type

    Le type de jointure. Les différents types de jointures sont les suivants, dans l'ordre du plus efficace au plus lent :

    • system

      La table a une seule ligne (c'est une table système). C'est un cas spécial du type de jointure const.

    • const

      La table a au plus une ligne correspondante, qui sera lue dès le début de la requête. Comme il n'y a qu'une seule ligne, les valeurs des colonnes de cette ligne peuvent être considérées comme des constantes pour le reste de l'optimiseur. Les tables const sont très rapides, car elles ne sont lues qu'une fois.

      const est utilisé lorsque vous comparez toutes les parties d'une clé PRIMARY/UNIQUE avec des constantes :

      SELECT * FROM const_table WHERE primary_key=1;
      
      SELECT * FROM const_table
      WHERE primary_key_part1=1 AND primary_key_part2=2;
      

    • eq_ref

      Une ligne de cette table sera lue pour chaque combinaison de ligne des tables précédentes. C'est le meilleur type de jointure possible, à l'exception des précédents. Il est utilisé lorsque toutes les parties d'un index sont utilisées par la jointure, et que l'index est UNIQUE ou PRIMARY KEY.

      eq_ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l'opérateur =. L'élément comparé doit être une constante ou une expression qui utiliser les colonnes de la table qui est avant cette table.

      Dans l'exemple suivant, ref_table sera capable d'utiliser eq_ref :

      SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
      
      SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;
      

    • ref

      Toutes les lignes avec des valeurs d'index correspondantes seront lues dans cette table, pour chaque combinaison des lignes précédentes. ref est utilisé si la jointure n'utilise que le préfixe de gauche de la clé, ou si la clé n'est pas UNIQUE ou PRIMARY KEY (en d'autres termes, si la jointure ne peut pas sélectionner qu'une seule ligne en fonction de la clé). Si la clé qui est utilisée n'identifie que quelques lignes à chaque fois, la jointure est bonne.

      ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l'opérateur =.

      Dans les exemples suivants, ref_table sera capable d'utiliser ref.

      SELECT * FROM ref_table WHERE key_column=expr;
      
      SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
      
      SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;
      

    • ref_or_null

      Comme ref, mais avec le coût supplémentaire pour les recherches couvrant les valeurs NULL. Ce type de jointure est nouveau en MySQL 4.1.1 est sert essentiellement à la résolution des sous-requêtes.

      Dans les exemples suivants, MySQL peut utiliser une jointure ref_or_null pour traiter ref_table :

      SELECT * FROM ref_table
      WHERE key_column=expr OR key_column IS NULL; 
      

      See Section 7.2.7, « Comment MySQL optimise IS NULL ».

    • index_merge

      Ce type de jointure indique que l'optimisation de type Index Merge est utilisée. Dans ce cas, la colonne key contient une liste d'index utilisés, et key_len contient la liste des plus longues parties de clés utilisées. Pour plus d'informations, voyez Section 7.2.6, « Optimisation de combinaison d'index ».

    • unique_subquery

      Ce type remplace le type ref dans certaines sous-requêtes IN de la forme suivante :

      value IN (SELECT primary_key FROM single_table WHERE some_expr) 
      

      unique_subquery est simplement une analyse d'index, qui remplace complètement la sous-requête pour une meilleure efficacité.

    • index_subquery

      Ce type de jointure est similaire à unique_subquery. Elle remplace des sous-requêtes IN, mais elle fonctionne pour les index non-uniques dans les sous-requêtes de la forme suivante :

      value IN (SELECT key_column FROM single_table WHERE some_expr) 
      

    • range

      Seules les lignes qui sont dans un intervalle donné seront lues, en utilisant l'index pour sélectionner les lignes. La colonne key indique quel est l'index utilisé. key_len contient la taille de la partie de la clé qui est utilisée. La colonne ref contiendra la valeur NULL pour ce type.

      range peut être utilisé lorsqu'une colonne indexée est comparée avec une constante comme =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN ou IN.

      SELECT * FROM tbl_name
      WHERE key_column = 10;
      
      SELECT * FROM tbl_name
      WHERE key_column BETWEEN 10 and 20;
      
      SELECT * FROM tbl_name
      WHERE key_column IN (10,20,30);
      
      SELECT * FROM tbl_name
      WHERE key_part1= 10 AND key_part2 IN (10,20,30);
      

    • index

      C'est la même chose que ALL, hormis le fait que seul l'arbre d'index est étudié. C'est généralement plus rapide que ALL, car le fichier d'index est plus petit que le fichier de données.

      Cette méthode peut être utilisée lorsque la requête utilise une colonne qui fait partie d'un index.

    • ALL

      Une analyse complète de la table sera faîte pour chaque combinaison de lignes issue des premières tables. Ce n'est pas bon si la première table n'est pas une jointure de type const et c'est très mauvais dans les autres cas. Normalement vous pouvez éviter ces situations de ALL en ajoutant des index basée sur des parties de colonnes.

  • possible_keys

    La colonne possible_keys indique quels index MySQL va pouvoir utiliser pour trouver les lignes dans cette table. Notez que cette colonne est totalement dépendante de l'ordre des tables. Cela signifie que certaines clés de la colonne possible_keys pourraient ne pas être utilisées dans d'autres cas d'ordre de tables.

    Si cette colonne est vide, il n'y a pas d'index pertinent. Dans ce cas, vous pourrez améliorer les performances en examinant votre clause WHERE pour voir si des colonnes sont susceptibles d'être indexée. Si c'est le cas, créez un index approprié, et examinez le résultat avec la commande EXPLAIN. See Section 13.2.2, « Syntaxe de ALTER TABLE ».

    Pour connaître tous les index d'une table, utilisez le code SHOW INDEX FROM nom_de_table.

  • key

    La colonne key indique l'index que MySQL va décider d'utiliser. Si la clé vaut NULL, aucun index n'a été choisi. Pour forcer MySQL à utiliser un index listé dans la colonne possible_keys, utilisez USE KEY/IGNORE KEY dans votre requête. See Section 13.1.7, « Syntaxe de SELECT ».

    Pour les tables MyISAM et BDB, la commande ANALYZE TABLE va aider l'optimiseur à choisir les meilleurs index. Pour les tables MyISAM, myisamchk --analyze fera la même chose. Voyez Section 13.5.2.1, « Syntaxe de ANALYZE TABLE » et Section 5.7.3, « Utilisation de myisamchk pour la maintenance des tables et leur recouvrement ».

  • key_len

    La colonne key_len indique la taille de la clé que MySQL a décidé d'utiliser. La taille est NULL si la colonne key vaut NULL. Notez que cela vous indique combien de partie d'une clé multiple MySQL va réellement utiliser.

  • ref

    La colonne ref indique quelle colonne ou quelles constantes sont utilisées avec la clé key, pour sélectionner les lignes de la table.

  • rows

    La colonne rows indique le nombre de ligne que MySQL estime devoir examiner pour exécuter la requête.

  • Extra

    Cette colonne contient des informations additionnelle sur comment MySQL va résoudre la requête. Voici une explication des différentes chaînes que vous pourriez trouver dans cette colonne :

    • Distinct

      MySQL ne va pas continuer à chercher d'autres lignes que la ligne courante, après en avoir trouvé une.

    • Not exists

      MySQL a été capable d'appliquer une optimisation de type LEFT JOIN sur la requête, et ne va pas examiner d'autres lignes de cette table pour la combinaison de lignes précédentes, une fois qu'il a trouvé une ligne qui satisfait le critère de LEFT JOIN.

      Voici un exemple de cela :

      SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
      WHERE t2.id IS NULL;
      

      Supposons que t2.id est défini comme NOT NULL. Dans ce cas, MySQL va scanner t1 et rechercher des lignes dans t2 via t1.id. Si MySQL trouve une ligne dans t2, il sait que t2.id ne peut pas être NULL, et il ne va pas scanner le reste des lignes de t2 qui ont le même id. En d'autres termes, pour chaque ligne de t1, MySQL n'a besoin que de faire une recherche dans t2, indépendamment du nombre de lignes qui sont trouvées dans t2.

    • range checked for each record (index map: #)

      MySQL n'a pas trouvé d'index satisfaisant à utiliser. Il va, à la place, pour chaque combinaison de lignes des tables précédentes, faire une vérification de quel index utiliser (si il en existe), et utiliser cet index pour continuer la recherche. Ce n'est pas très rapide, mais c'est plus rapide que de faire une recherche sans aucun index.

    • Using filesort

      MySQL va avoir besoin d'un autre passage pour lire les lignes dans l'ordre. Le tri est fait en passant en revue toutes les lignes, suivant le type de jointure est stocker la clé de tri et le pointeur de la ligne pour chaque ligne qui satisfont la clause WHERE. Alors, les clés sont triées. Finalement, les lignes sont triées dans l'ordre.

    • Using index

      Les informations de la colonne sont lues de la table, en utilisant uniquement les informations contenues dans l'index, sans avoir à faire d'autres lectures. Cela peut arriver lorsque toutes les colonnes utilisées dans une table font partie de l'index.

    • Using temporary

      Pour résoudre la requête, MySQL va avoir besoin de créer une table temporaire pour contenir le résultat. C'est typiquement ce qui arrive si vous utilisez une clause ORDER BY sur une colonne différente de celles qui font partie de GROUP BY.

    • Using where

      Une clause WHERE sera utilisée pour restreindre les lignes qui seront trouvées dans la table suivante, ou envoyée au client. Si vous n'avez pas cette information, et que la table est de type ALL ou index, vous avez un problème dans votre requête (si vous ne vous attendiez pas à tester toutes les lignes de la table).

    Si vous voulez rendre vos requêtes aussi rapide que possible, vous devriez examiner les lignes qui utilisent Using filesort et Using temporary.

Vous pouvez obtenir une bonne indication de la qualité de votre jointure en multipliant toutes les valeurs de la colonne rows dans la table de la commande EXPLAIN. Cela est une estimation du nombre de lignes que MySQL va examiner pour exécuter cette requête. C'est aussi ce nombre qui sera utilisé pour interrompre votre requête, grâce à la variable max_join_size. See Section 7.5.2, « Réglage des paramètres du serveur ».

L'exemple ci-dessous illustre comme une requête JOIN peut être optimisée avec les résultats de la commande EXPLAIN.

Supposons que vous avez la requête SELECT suivante, et que vous l'examinez avec EXPLAIN:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Pour cette exemple, nous supposons que :

  • Les colonnes utilisées sont déclarées comme ceci :

    TableColonneType de colonne
    ttActualPCCHAR(10)
    ttAssignedPCCHAR(10)
    ttClientIDCHAR(10)
    etEMPLOYIDCHAR(15)
    doCUSTNMBRCHAR(15)
  • Les tables ont les index suivants :

    TableIndex
    ttActualPC
    ttAssignedPC
    ttClientID
    etEMPLOYID (clé primaire)
    doCUSTNMBR (clé primaire)
  • Les valeurs de tt.ActualPC ne sont pas réparties également.

Initialement, avant toute optimisation, la commande EXPLAIN produit les informations suivantes :

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

Comme le type type vaut ALL pour chaque table, le résultat indique que MySQL fait une analyse complète de toutes les tables. Cela va prendre un très long temps de calcul, car le nombre de lignes à examiner de cette fa¸on est le produit du nombre de lignes de toutes les tables : dans notre cas, cela vaut 74 * 2135 * 74 * 3872 = 45,268,558,720 lignes. Si les tables étaient plus grandes, cela serait encore pire.

Le premier problème que vous avons ici, est que MySQL ne peut pas (encore) utiliser d'index sur les colonnes, si elles sont déclarées différemment. Dans ce contexte, les colonnes VARCHAR et CHAR sont les mêmes, mais elles ont été déclarée avec des tailles différentes. Comme tt.ActualPC est déclarée comme CHAR(10) et que et.EMPLOYID est déclaré comme CHAR(15), il y a un problème de taille.

Pour corriger cette disparité, utilisez la commande ALTER TABLE pour agrandir la colonne ActualPC de 10 caractères à 15 :

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Maintenant, tt.ActualPC et et.EMPLOYID sont tous les deux des colonnes de type VARCHAR(15). Exécuter la commande EXPLAIN produit maintenant le résultat suivant :

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

Ce n'est pas parfait, mais c'est bien mieux. Le produit de toutes les lignes a été divisé par 74). Cette version s'exécute en quelques secondes.

Une autre modification peut être faîte pour éliminer les problèmes de taille de colonne pour tt.AssignedPC = et_1.EMPLOYID et tt.ClientID = do.CUSTNMBR :

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

Maintenant, EXPLAIN produit le résultat suivant :

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

C'est presque aussi bon que cela pourrait l'être.

Le problème final est que, par défaut, MySQL supporte que les valeurs de la colonne tt.ActualPC sont uniformément répartie, et que ce n'est pas le cas pour la table tt. Mais il est facile de le dire à MySQL :

mysql> <userinput>ANALYZE TABLE tt;</userinput>

Maintenant, la jointure est parfaite, et la commande EXPLAIN produit ce résultat :

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

Notez que la colonne rows dans le résultat de EXPLAIN est une prédiction éclairée de l'optimiseur de jointure MySQL. Pour optimiser une requête, vous devriez vérifier si ces nombres sont proches de la réalité. Si ce n'est pas le cas, vous pourriez obtenir de meilleures performances avec l'attribut STRAIGHT_JOIN dans votre commande SELECT, et en choisissant vous même l'ordre de jointure des tables dans la clause FROM.

7.2.2 Mesurer les performances d'une requête

Dans la plupart des cas, vous pouvez mesurer la performance d'une requête en comptant le nombre d'accès disques. Pour les tables de petite taille, vous pouvez généralement obtenir une seule lecture (car l'index est probablement en cache). Pour les tables plus grandes, vous pouvez estimer que vous aurez besoin de (en utilisant les index B-tree) : log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 lectures pour trouver une ligne.

Pour MySQL, un bloc d'index vaut généralement 1024 octets, et le pointeur de données vaut 4 octets. Une table de 500,000 avec un index de taille 3 (entier moyen) vous donnera log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 lectures.

Comme l'index ci-dessus vous serait de taille 500 000 * 7 * 3/2 = 5.2 Mo, (en supposant que les index des tampons sont remplit aux 2/3, ce qui est typique), vous aurez probablement l'essentiel de l'index en mémoire, et vous n'aurez alors besoin que de 1 ou 2 lectures pour lire le reste des lignes.

Pour les écritures, toutefois, vous aurez besoin de 4 lectures (comme ci-dessus), pour trouver la place du nouvel index, et normalement, deux autres lectures pour modifier l'index et la ligne.

Notez que le raisonnement ci-dessus n'indique pas que votre application va dégénérer en fonction du logarithme népérien! Tant que tout est mis en cache par l'OS ou le serveur SQL, les performances ne vont se réduire que marginalement, même si la table grossit beaucoup. Une fois que les données seront trop importantes pour être en cache, votre application va ralentir car le serveur devra faire des lectures sur le disque (ce qui va accroître le log). Pour éviter cela, augmentez le cache d'index au fur et à mesure que votre index grossit. See Section 7.5.2, « Réglage des paramètres du serveur ».

7.2.3 Vitesse des requêtes SELECT

En général, lorsque vous voulez rendre un SELECT ... WHERE plus rapide, la première chose à faire est de voir si vous pouvez ajouter des index. Toutes les références entre les tables doivent normalement être faites avec des index. Vous pouvez utiliser la commande EXPLAIN pour déterminer les index utilisés pour le SELECT. Voyez aussi Section 7.4.5, « Comment MySQL utilise les index » et Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) ».

Quelques conseils généraux :

  • Pour aider MySQL à mieux optimiser les requêtes, exécutez myisamchk --analyze sur une table après l'avoir remplie avec quelques données consistantes. Cela met à jour une valeur pour chaque partie de l'index qui indique le nombre moyen de lignes qui ont la même valeur. (Pour les index uniques, c'est toujours 1, bien sûr.) MySQL utilisera cela pour décider quel index choisir pour connecter deux tables avec une "expression non-constante". Vous pouvez vérifier le retour de l'exécution d'analyze en faisant SHOW INDEX FROM nom_de_table et examiner la colonne Cardinality.

  • Pour trier un index et des données par rapport à un index, utilisez myisamchk --sort-index --sort-records=1 (si vous voulez trier selon le premier index). Si vous avec un index unique à partir duquel vous voulez lire toutes les lignes en prenant comme ordre cet index, c'est un bon moyen de rendre les traitements plus rapides. Notez, toutefois, que ce tri n'est pas le plus optimal et prendra beaucoup de temps pour une grosse table !

7.2.4 Comment MySQL optimise les clauses WHERE

Les optimisation de la clause WHERE sont présentées avec la commande SELECT car elles sont généralement utilisées avec la commande SELECT, mais les mêmes optimisations peuvent s'appliquer aux clauses WHERE des commandes DELETE et UPDATE.

Notez aussi que cette section est incomplète. MySQL fait de très nombreuses optimisations, et nous n'avons pas eu le temps de toutes les documenter.

Certaines des optimisations effectuées par MYSQL sont présentées ici :

  • Suppression des parenthèses inutiles :

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)
    

  • Remplacement des constantes :

       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5
    

  • Suppression des conditions constantes (nécessaires pour le remplacement des constantes) :

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6
    

  • Les expressions constantes utilisées par les index sont évaluées une fois.

  • COUNT(*) sur une table simple, sans clause WHERE est lu directement dans les informations de la table pour les tables MyISAM et HEAP. Cela peut aussi être fait avec les expressions NOT NULL lorsqu'elles sont utilisées sur une seule table. table.

  • Détection précoce est expressions constantes invalides. MySQL détecte rapidement les commandes SELECT qui sont impossibles, et ne retourne aucune ligne.

  • HAVING est combiné avec la clause WHERE si vous n'utilisez pas la clause GROUP BY ou les fonctions de groupe (COUNT(), MIN()...).

  • Pour chaque sous-jointure, une clause WHERE simplifiée est construite pour accélérer l'évaluation de WHERE pour chaque sous-jointure, et aussi essayer d'ignorer les lignes le plus tôt possible.

  • Toutes les tables constantes sont lues en premier, avant toute autre table de la requête. Une table constante est une table :

    • Une table vide ou une table d'une ligne.

    • Une table qui est utilisée avec la clause WHERE sur un index de type UNIQUE, ou avec une clé primaire PRIMARY KEY, dont toutes les parties sont des expressions constantes, et les parties de l'index sont identifiées comme NOT NULL.

    Toutes les tables suivantes sont considérées comme constantes :

    mysql> SELECT * FROM t WHERE primary_key=1;
    mysql> SELECT * FROM t1,t2
        ->          WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
    

  • La meilleure combinaison de jointure est obtenue en testant toutes les possibilités. Si toutes les colonnes des clauses ORDER BY et GROUP BY proviennent de la même table, cette table sera utilisée de préférence comme première table dans la jointure.

  • Si il y a une clause ORDER BY et une clause GROUP BY différente, ou si la clause ORDER BY ou GROUP BY contient des colonnes issues des tables autres que la première, une table temporaire est créée.

  • Si vous utilisez SQL_SMALL_RESULT, MySQL va utiliser une table temporaire en mémoire.

  • Chaque index de table est interrogé, et le meilleur index qui représente moins de 30% des lignes est utilisé. Si un tel index ne peut être identifié, un scan rapide de la table est fait.

  • Dans certains cas, MySQL peut lire des lignes depuis l'index sans même consulter le fichier de données. Si toutes les colonnes de l'index sont des nombres, alors seul l'arbre d'index sera utilisé pour résoudre la requête.

  • Avant chaque affichage de ligne, celles qui ne satisfont pas les critères de la clause HAVING sont ignorées.

Quelques exemples de requêtes très rapides :

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
    ->        WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
    ->        ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
    ->        ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

Les requêtes suivantes ne sont résolues qu'avec l'arbre d'index (en supposant que les colonnes sont numériques) :

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
    ->        WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

Les requêtes suivantes utilisent l'indexation pour lire les lignes dans un ordre donnés, dans faire de tri supplémentaire :

mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1,key_part2,... ;
mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1 DESC,key_part2 DESC,... ;

7.2.5 Optimisation d'intervalle

La méthode d'accès range utilise un seul index pour obtenir une sous-partie de table, dont les lignes font parties d'un intervalle de valeurs d'index. La description détaillée de l'extraction des index de la clause WHERE est présentée dans les sections suivantes.

7.2.5.1 Méthode d'accès par intervalle pour les index mono-colonnes

Pour un index à une colonne, les intervalles peuvent être représentés en pratique par les conditions correspondantes à la clause WHERE, et cela donne des ``conditions d'intervalle'' au lieu d'intervalle.

La définition d'une condition d'intervalle pour un index mono-colonne est la suivante :

  • Pour les index BTREE et HASH, la comparaison d'une partie de clé avec une valeur constante est une condition d'intervalle lorsqu'on l'utilise avec =, <=>, IN, IS NULL ou IS NOT NULL .

  • Pour les index BTREE, la comparaison d'une partie de clé avec une constante est une condition d'intervalle avec les opérateurs >, <, >=, <=, BETWEEN, != et <>, ou LIKE 'pattern' (où 'pattern' ne commence pas avec un joker).

  • Pour tous les types d'index, plusieurs conditions d'intervalles combinées avec des opérateurs OR ou AND forment une condition d'intervalle.

``Valeur constante'', dans les descriptions précédentes, signifie l'un des objets suivants :

  • Une constante dans une chaîne de requête

  • Une colonne dans une table const ou system dans une jointure.

  • Le résultat d'une sous-requête non-correllée

  • Une expression composée entièrement de sous-expression de l'un des types précédents.

Voici des exemples de requêtes avec des conditions d'intervalles dans la clause WHERE :

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 
'bar' AND 'foo';

Notez que certaines valeurs non-constantes sont converties en constantes durant la phase de propagation des constantes.

MySQL essaie d'extraire les conditions d'intervalle de la clause WHERE pour chaque index possible. Durant le processus d'extraction, les conditions qui ne peuvent pas être utilisées sont ignorées, les conditions qui produisent des intervalles qui se recoupent sont combinées ensembles, et les conditions qui produisent des intervalles vides sont supprimées.

Par exemple, observez la commande suivante, où key1 est une colonne indexée et nonkey n'est pas indexée :

SELECT * FROM t1 WHERE
   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
   (key1 < 'bar' AND nonkey = 4) OR
   (key1 < 'uux' AND key1 > 'z');

Le processus d'extraction de la clé key1 est la suivante :

  1. Début avec la clause WHERE originale :

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
    (key1 < 'bar' AND nonkey = 4) OR
    (key1 < 'uux' AND key1 > 'z')
    
  2. Suppression de nonkey = 4 et key1 LIKE '%b' car elles ne peuvent pas êtr utilisées pour des conditions d'intervalle. La bonne méthode pour les supprimer est de les remplacer avec une valeur TRUE, pour qu'elles n'ignorent aucune lignes lors de la recherche. Cela donne :

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
    (key1 < 'bar' AND TRUE) OR
    (key1 < 'uux' AND key1 > 'z')
    
  3. Suppression des conditions qui sont toujours vraies ou fausses :

    • (key1 LIKE 'abcde%' OR TRUE) est toujours vraie

    • (key1 < 'uux' AND key1 > 'z') est toujours fausse

    Remplacement de ces conditions avec des constantes, nous obtenons :

    (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
    

    Suppression des constantes TRUE et FALSE inutiles, nous obtenons :

    (key1 < 'abc') OR (key1 < 'bar')
    
  4. Combinaisons des intervalles communs conduit à une seule condition finale, à utiliser pour l'intervalle :

    (key1 < 'bar')
    

En général (et tel que démontré dans notre exemple), les conditions utilisées pour une condition d'intervalle sont moins restrictives que la clause WHERE. MySQL va compléter la recherche par des filtres appliqués aux lignes trouvées pour supprimer celles qui ne satisfont par les clauses WHERE.

L'algorithme d'extraction d'intervalle peut gérer des conditions AND/OR de profondeur arbitraire, et son résultat ne dépend pas de l'ordre des conditions dans la clause WHERE.

7.2.5.2 Méthode d'accès par intervalle pour les index multi-colonnes

Les conditions d'intervalle sur un index à plusieurs parties est une extension de la version pour index mono-colonne. Une condition d'intervalle pour un index multi-colonnes restreint les lignes à un ou plusieurs intervalles dans l'index. Les intervalles sont définis comme un jeu d'index, en utilisant l'ordre de l'index existant.

Par exemple, considérez l'index multi-colonnes suivant, défini par key1(key_part1, key_part2, key_part3) et leur ordre :

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

La condition key_part1 = 1 définit cet intervalle :

(1, -inf, -inf) <= (key_part1, key_part2, key_part3) < (1, +inf, +inf)

L'intervalle couvre les 4eme, 5eme et 6eme lignes dans la table précédente, et peut être utilisés par la méthode d'accès par intervalle.

Par contraste, la condition key_part3 = 'abc' ne définit aucun intervalle et ne peut pas être utilisée par la méthode d'accès par intervalle.

La description suivante montre comment les conditions d'intervalles fonctionnent avec un index multi-colonnes.

  • Pour les index HASH, chaque intervalle contiennent des valeurs identiques qui peuvent être utilisées. Cela signifie que l'intervalle peut produire des conditions d'intervalles uniquement pour les conditions suivantes :

        key_part1 cmp const1
    AND key_part2 cmp const2
    AND ...
    AND key_partN cmp constN;
    

    Ici, const1, const2, ... sont constantes, cmp est un des opérateurs de comparaison =, <=> ou IS NULL et les conditions couvrent toutes les parties de l'index. C'est à dire qu'il y a N conditions, une pour chaque partie de l'index.

    Voyez Section 7.2.5.1, « Méthode d'accès par intervalle pour les index mono-colonnes » pour avoir la définition d'une constante dans ce contexte.

    Par exemple, la condition suivante est une condition d'intervalle pour un index HASH :

    key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
    
  • Pour in index BTREE, un intervalle peut être utilisable pour des conditions AND combinées, où chaque condition compare une partie de la clée avec une valeur constante et un opérateur de comparaison =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN ou LIKE 'pattern' (où 'pattern' ne commence pas par un joker). Un intervalle peut être utilisé tant qu'il est possible de déterminer une ligne qui vérifie la condition, ou deux intervalles si <> ou != est utilisé. Par exemple :

    key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
    

    L'intervalle sera :

    ('foo', 10, 10)
       < (key_part1, key_part2, key_part3)
          < ('foo', +inf, +inf)
    

    Il est possible que l'intervalle créée contienne plus de lignes que la condition initialle. Par exemple, l'intervalle précédent inclut la valeur ('foo', 11, 0),, qui ne satisfait pas les conditions initiales.

  • Si les conditions qui génèrent les conditions d'intervalle sont combinées avec OR, elles forment une condition qui couvre un jeu de ligne contenu dans l'union des intervalles. Si les conditions sont combinées avec AND, elles forment une condition qui couvre un jeu de lignes contenu dans l'intersection des intervalles. Par exemple, pour cette condition bâtie sur un index à 2 colonnes :

    (key_part1 = 1 AND key_part2 < 2)
    OR (key_part1 > 5)
    

    Les intervalles seront :

    (1, -inf) < (key_part1, key_part2) < (1, 2)
    (5, -inf) < (key_part1, key_part2)
    

    Dans cet exemple, l'intervalle de la première ligne utilise une partie de la clé pour l'opérande de gauche, et deux parties de clé pour l'opérande de droite. La colonne key_len dans le résultat de EXPLAIN indique la taille maximale du préfixe de clé utilisé.

    Dans certains cas, key_len peut indiquer qu'une clé a été utilisée mais ce n'est pas ce que vous attendiez. Par exemple, supposez que key_part1 et key_part2 soient NULL. Alors, la colonne key_len va afficher deux clés de taille différentes pour les conditions suivantes :

    key_part1 >= 1 AND key_part2 < 2
    

    Mais en fait, les conditions seront converties en :

    key_part1 >= 1 AND key_part2 IS NOT NULL
    

Section 7.2.5.1, « Méthode d'accès par intervalle pour les index mono-colonnes » décrit comment les optimisations sont appliquées pour combiner ou éliminer les intervalles basés sur des index mono-cultures. Des étapes analogues sont effectuées pour les conditions sur des index multi-colonnes.

7.2.6 Optimisation de combinaison d'index

La méthode de combinaison d'index (Index Merge, index_merge) est utilisée pour lire des lignes avec plusieurs scans ref, ref_or_null et range et les combiner en un seul résultat. Cette méthode est employée lorsque les conditions sur la table sont un groupe de conditions disjointes pour lesquelles ref, ref_or_null, ou range peuvent être utilisées avec différentes clés.

Ce type d'optimisation ``join'' est nouveau en MySQL 5.0.0, et représente un changement significatif dans le comportement de MySQL avec les index, car l'ancienne règle était que le serveur n'utilisait qu'un seul index au plus pour chaque table référencée.

Dans le résultat de EXPLAIN, cette méthode apparait sous le nom de index_merge dans la colonne de type type. Dans ce cas, la colonne key contient la liste des index utilisés, et key_len contient la liste des tailles maximales de clé pour chaque index.

Exemples :

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

SELECT * FROM tbl_name
    WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;

SELECT * FROM t1, t2
    WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
    AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
    WHERE t1.key1=1
    AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

La méthode de combinaison d'index a différentes méthodes d'accès aux index, tels que présentées dans le champ Extra du résultat de la commande EXPLAIN :

  • intersection

  • union

  • sort-union

Les sections suivantes décrivent ces méthodes avec plus de détails :

Note : L'algorithme d'optimisation des combinaisons d'index a les limitations suivantes :

  • Si un scan d'intervalle est possible avec une clé, la combinaison d'index sera omise. Par exemple :

    SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
    

    Pour cette requête, deux solutions sont possibles :

    1. Une combinaison d'index avec la condition (goodkey1 < 10 OR goodkey2 < 20).

    2. Un scan d'intervalle avec la condition badkey < 30.

    Mais ici, l'optimisateur ne considèrera que la seconde méthode. Si ce n'est pas ce que vous souhaitez, vous pouvez forcer l'optimiseur à utiliser index_merge en utilisant les clauses IGNORE INDEX et FORCE INDEX. Les requêtes suivantes seront exécutées avec une combinaison d'index :

    SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
    WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
    
    SELECT * FROM t1 IGNORE INDEX(badkey)
    WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
    
  • Si votre requête a une clause WHERE complexe avec des conditions AND/OR imbriquées, et que MySQL n'a pas identifié la méthode optimale, essayez de répartir les conditions en utilisant les lois d'identitée :

    (x AND y) OR z = (x OR z) AND (y OR z)
    (x OR y) AND z = (x AND z) OR (y AND z)
    

Le choix entre les méthodes de index_merge est basée sur le calcul de coûts.

7.2.6.1 Algorithme d'accès aux intersections de combinaisons d'index

Cet algorithme peut être employé lorsque la clause WHERE a été convertie en plusieurs conditions d'intervelle sur différentes clés compbinées avec AND, et que chaque condition vérifie :

  • Sous cette forme, où l'index a exactement N parties (c'est à dire que toutes les parties de l'index sont couvertes) :

    key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
    
  • Toute condition d'intervalle sur une clé primaire de table InnoDB ou BDB.

Voci quelques exemples :

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

L'algorithme d'intersection effectue des scans simultanés sur tous les index utilisés, et produit la séquence de lignes qu'il re¸oit des analyses d'index combinés.

SI toutes les colonnes utilisées dans la requêtes sont couvertes par les index utilisés, toutes les lignes de la table ne seront pas lues : EXPLAIN indiquera Using index dans la colonne Extra. Voici un exemple de cette requête :

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

Si les index utilisés ne couvrent pas toutes les colonnes, les lignes complètes seront lues uniquement lorsque les conditions d'intervalles seront toutes satisfaites.

Si une des conditions est une condition sur une clé primaire d'une table InnoDB ou BDB, elle n'est pas utilisée pour lire les lignes, mais pour filtrer les lignes lues par les autres conditions.

7.2.6.2 Algorithme d'accès aux unions de combinaison d'index

Le critère applicable pour cet algorithme est similaire à ceux de la méthode des intersections de combinaison d'index. L'algorithme peut être employé lorsque la clause WHERE a été convertie en plusieurs conditions d'intervalle combinées avec l'opérateur OR, et que chaque condition est une des suivantes :

  • Sous cette forme, où l'index a exactement N parties, c'est-à-dire que toutes les parties de l'index sont couvertes :

    key_part1=const1 AND key_part2=const2 ... AND key_partN=constN
    
  • Toute condition d'intervalle sur une clé primaire d'une table InnoDB ou BDB.

  • Une condition pour laquelle l'algorithme d'intersection de combinaison d'index est applicable.

Voici quelques exemples :

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

7.2.6.3 Algorithme d'accès aux unions triées de combinaison d'index

Cet algorithme d'accès est employé lorsque la clause WHERE a été convertie en plusieurs conditions d'intervalle par l'opérateur OR, mais que l'algorithme d'union de combinaison d'index n'est pas utilisable.

Voici quelques exemples :

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
     WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

La différence entre l'algorithme d'union triée et l'algorithme d'union est que l'algorithme d'union triée doit commencer par lire les identifiants de toutes les lignes, et les trier avant de retourner un résultat.

7.2.7 Comment MySQL optimise IS NULL

MySQL peut exploiter certaines optimisation sur les conditions column IS NULL, comme il peut le faire avec les conditions column = constant_value. Par exemple, MySQL peut utiliser des index et des intervalles pour rechercher des valeurs NULL avecIS NULL.

SELECT * FROM table_name WHERE key_col IS NULL;

SELECT * FROM table_name WHERE key_col <=> NULL;

SELECT * FROM table_name WHERE key_col=# OR key_col=# OR key_col IS NULL

Si vous utilisez column_name IS NULL sur une colonne NOT NULL dans une clause WHERE, sur une table qui ne fait pas partie d'une jointure OUTER JOIN, l'expression sera optimisée immédiatement.

MySQL 4.1.1 peut aussi optimiser des combinaisons column = expr AND column IS NULL, une forme qui est fréquente avec les sous-requêtes. EXPLAIN vous indiquera ref_or_null lorsque cette optimisation est utilisée.

Cette optimisation peut gérer une condition IS NULL avec toute partie de clé.

Quelques exemples de requêtes qui sont optimisées (en supposant qu'il existe une clé sur t2 (a,b) :

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1,t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1,t2 WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1,t2 WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1,t2 WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null fonctionne en lisant la clé de référence, et après ¸a, fait une recherche différente pour les valeurs NULL.

Notez que l'optimisation ne peut gérer qu'un seul niveau de conditions IS NULL.

SELECT * FROM t1,t2 where (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);

Dans le cas ci-dessus, MySQL va uniquement utiliser une recherche de clé pour la partie (t1.a=t2.a AND t2.a IS NULL) et ne sera pas capable d'utiliser la clé pour b.

7.2.8 Comment MySQL optimise DISTINCT

DISTINCT combiné avec un ORDER BY aura dans la plupart des cas recours à une table temporaire.

Notez que comme DISTINCT peut utiliser GROUP BY, apprenez comment MySQL fonctionne avec les champs de ORDER BY et HAVING qui ne sont pas dans la liste des colonnes sélectionnées. See Section 12.9.3, « GROUP BY avec les champs cachés ».

Quand vous combinerez LIMIT # avec DISTINCT, MySQL stoppera dès qu'il trouvera # lignes uniques.

Si vous n'utilisez pas de colonnes de toutes les tables utilisées, MySQL arrête de scanner la table non-utilisée dès qu'il trouve la première correspondance.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

Dans ce cas, en supposant que t1 est utilisée avant t2 (vérifiez avec EXPLAIN), MySQL arrêtera de lire à partir de t2 (pour cette ligne particulière de t1) lorsque la première ligne de t2 est trouvée.

7.2.9 Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN

A LEFT JOIN B est implémenté dans MySQL comme suit :

  • La table B est censée être dépendante de la table A et de toutes les tables dont dépend A.

  • La table A est censée être dépendante de toutes les tables (à part B) qui sont utilisées dans la condition du LEFT JOIN.

  • Toutes les conditions du LEFT JOIN sont transmises à la clause WHERE.

  • Toutes les optimisations standards de jointures sont effectuées, à l'excepté qu'une table est toujours lue après celles dont elle dépend. S'il y a une dépendance circulaire, MySQL retournera une erreur.

  • Toutes les optimisations standards de WHERE sont effectuées.

  • S'il y a une ligne dans A qui répond à la clause WHERE, mais qu'il n'y avait aucune ligne dans B qui répondait à la condition du LEFT JOIN, alors une ligne supplémentaire de B est générée avec toutes les colonnes mises à NULL.

  • Si vous utilisez LEFT JOIN pour trouver les enregistrements qui n'existent pas dans d'autres tables et que vous effectuez le test suivant : nom_colonne IS NULL dans la partie WHERE, où nom_colonne est une colonne qui est déclarée en tant que NOT NULL, alors MySQL arrêtera de chercher d'autres lignes (pour une combinaison de clefs particulière) après avoir trouvé une ligne qui répond à la condition du LEFT JOIN.

RIGHT JOIN est implémenté de manière analogue à LEFT JOIN.

L'ordre de lecture de tables forcé par LEFT JOIN et STRAIGHT JOIN aidera l'optimiseur de jointures (qui calcule l'ordre dans lequel les tables doivent être jointes) à faire son travail plus rapidement, puisqu'il y aura moins de permutations de tables à vérifier.

Notez que ce qui précède signifie que si vous faites une requête de la sorte :

SELECT *
    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

Un palliatif est de changer la requête en :

SELECT *
    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

Depuis la version 4.0.14, MySQL effectue l'optimisation LEFT JOIN suivante : si la condition WHERE est toujours fausse pour la ligne NULL générée, la jointure LEFT JOIN est transformée en jointure normale.

Par exemple, dans la requête suivante, la clause WHERE sera fausse si t2.column est NULL : il est donc valide de convertir la jointure en une jointure normale.

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Par conséquent, il est possible de convertir la requête en jointure normale :

SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

Cela peut se faire plus rapidement, car MySQL peut maintenant utiliser la table t2 avant la table t1 si les relations sont plus favorables. Pour forcer l'utilisation spécifique d'un ordre de table, utilisez STRAIGHT JOIN.

7.2.10 Comment MySQL optimise ORDER BY

Dans certain cas, MySQL peut utiliser un index pour répondre à une requête ORDER BY ou GROUP BY sans faire aucun tri.

L'index peut être utilisé même si le ORDER BY ne correspond pas exactement à l'index, tant que toutes les parties inutilisée de l'index et les colonnes du ORDER BY sont constantes dans la clause WHERE. Les requêtes suivantes utilisent l'index pour répondre aux parties ORDER BY / GROUP BY :

SELECT * FROM t1 ORDER BY partie_clef1,partie_clef2,...
SELECT * FROM t1 WHERE partie_clef1=constante ORDER BY partie_clef2
SELECT * FROM t1 WHERE partie_clef1=constante GROUP BY partie_clef2
SELECT * FROM t1 ORDER BY partie_clef1 DESC,partie_clef2 DESC
SELECT * FROM t1 WHERE partie_clef1=1 ORDER BY partie_clef1 DESC,partie_clef2 DESC

Quelques cas où MySQL ne peut pas utiliser les index pour répondre à ORDER BY: (Notez que MySQL utilisera quand même les indexes pour trouver les lignes qui correspondent à la clause WHERE) :

  • Vous effectuez un ORDER BY sur des clefs différentes :

    SELECT * FROM t1 ORDER BY key1, key2;
    
  • Vous effectuez un ORDER BY en utilisant des parties de clef non consécutives.

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
    
  • Vous mélangez ASC et DESC.

    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
    
  • La clef utilisée pour extraire les résultats n'est pas la même que celle utilisée lors du groupement ORDER BY :

    SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
    
  • Vous faites une jointure entre plusieurs tables et les colonnes sur lesquelles vous faites un ORDER BY ne font pas toutes parties de la première table non-const qui est utilisée pour récupérer les lignes (C'est la première table dans l'affichage d'EXPLAIN qui n'utilise pas une méthode de récupération sur une ligne constante).

  • Vous avez plusieurs expressions ORDER BY et GROUP BY.

  • L'index de table utilisé est un type d'index qui n'enregistre pas les lignes dans l'ordre. (comme le type d'index HASH dans les tables HEAP).

Dans les cas où MySQL doit trier les résultats, il utilisera l'algorithme suivant :

  1. Lit toutes les lignes en fonction d'un index ou par scan de la table. Les lignes qui ne vérifient pas la condition WHERE sont ignorées.

  2. Stocke les valeurs des clés de tri dans un buffer. La taille du buffer est la valeur de la variable système sort_buffer_size.

  3. Lorsque le buffer se remplit, fait un tri rapide et stocke le résultat dans une fichier temporaire. Sauve le pointeur dans un bloc trié. Si toutes les lignes tiennent dans le buffer de tri, aucun fichier temporaire n'est créé.

  4. Répète les étapes précédentes jusqu'à ce que toutes les lignes aient été lues.

  5. Fait une combinaison multiple jusqu'à MERGEBUFF (7) régions en un bloc, dans un autre fichier temporaire. Répête l'opération jusqu'à ce que le premier fichier soit dans le second.

  6. Répête la suite jusqu'à ce qu'il y ait moins de MERGEBUFF2 (15) bloc libres.

  7. Dans la dernière combinaison multiple, seul le pointeur de ligne (la dernière partie de la clé de tri), est écrite dans le fichier de résultat.

  8. Lit les lignes triées en utilisant les pointeurs de lignes du fichier de résultat. Pour optimiser cela, on lit un gros bloc de pointeur, on les trie, et on les utilise pour lire les lignes en ordre dans un buffer. La taille du buffer est la valeur de la variable système read_rnd_buffer_size. Le code de cette étape est dans le fichier source sql/records.cc.

Vous pouvez vérifier avec EXPLAIN SELECT ... ORDER BY si MySQL peut utiliser des index pour répondre à cette requête. Si vous obtenez un Using filesort dans la colonne extra, c'est que MySQL ne peut utiliser d'index pour résoudre cet ORDER BY. See Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) ».

Si vous voulez plus de rapidité avec les ORDER BY, vous devez d'abord voir si vous pouvez faire en sorte que MySQL utilises des index au lieu de passer par des phases de tri en plus. Si cela se révèle impossible, vous pouvez :

  • Augmenter la taille de la variable sort_buffer.

  • Augmenter la taille de la variable record_rnd_buffer.

  • Changer tmpdir pour qu'il pointe vers un disque dédié avec beaucoup d'espace libre. Si vous utilisez MySQL version 4.1 ou plus récent, vous pouvez répartir la charge entre plusieurs disques physiques en donnant à l'option tmpdir une liste de chemin, séparés par des deux-points (‘:’) ou des points-virgules ‘;’ sous Windows). Ils seront utilisés circulairement. Note : ces chemins doivent aboutir à différents disques physiques, et non pas différentes partitions du même disque.

Par défaut, MySQL trie les requêtes GROUP BY x,y[,...] comme si vous aviez spécifié l'ordre ORDER BY x,y[,...]. Si vous ajoutez une clause ORDER BY explicite, MySQL l'optimise aussi sans perte de vitesse, même si un tri a lieu. Si la requête inclut une clause GROUP BY mais que vous voulez éviter le surcoût du tri, vous pouvez supprimer le tri en spécifiant ORDER BY NULL :

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

7.2.11 Comment MySQL optimise les clauses GROUP BY

La méthode la plus générale pour satisfaire une clause GROUP BY est de scanner toute la table et de créer une table temporaire où toutes les lignes de chaque groupe sont rangées consécutivement, puis d'utiliser cette table temporaire pour trouver les groupes, et leur appliquer les fonctions d'aggrégation s'il y en a. Dans certains cas, MySQL est capable de faire encore mieux, et d'éviter la création de la table temporaire grâce aux index.

La plus importante condition à l'utilisation des index pour GROUP BY est que toutes les colonnes du GROUP BY soient dns le même index, et que l'index stocke les clés dans le même ordre (par exemple, un B-Tree et non pas un HASH). L'utilisation de cette technique dépend aussi des parties de l'index qui sont utilisées dans la requête, les conditions posées sur ces index, et les différentes fonctions d'agrégation.

Il y a deux méthodes pour exécuter une requête GROUP BY via un accès aux index, tels que présenté dans les sections suivantes. Dans la première méthode, les opérations de rgroupmeent sont appliquées ensembles avec les prédicats d'intervalles. La seconde méthodes commence par faire une analyse d'intervalle, puis regroupe les lignes trouvées.

7.2.11.1 Scan restreint d'index

Le plus efficace est lorsque l'index sert à lire directement un groupe de champs. Avec cette méthode d'accès, MySQL exploite la propriété de certains types d'index comme les B-Tree, pour lesquels les clés sont triées. Cette propriété permet la recherche de groupsa dans un index en omettant d'autres clés pour satisfait toutes les conditions de la clause WHERE. Comme cette méthode d'accès ne prend en compte qu'une fraction de toutes les clés d'un index, elle est appelée ``scan restreint d'index'', ou loose index scan. Lorsque qu'il n'y a pas de clause WHERE, un scan restreint va lire autant de clé que de groupe, ce qui peut être un nombre inférieur au nombre de clés. Si la clause WHERE contient des prédicats d'intervalles (indiqués dans Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) », dans la colonne range), un scan restreint d'index analysera la première clé de chaque groupe qui satisfont les conditions d'intervalle, et lira ainsi le minimum possible de clé. Cela est rendu possible dans les conditions suivantes :

  • La requête utilise une seule table.

  • La clause GROUP BY inclut les premières parties consécutives de l'index, et si la requête utilise une clause DISTINCT à la place d'une clause GROUP BY, tous les attributs distincts se rapportent au début de l'index.

  • Les seules fonctions d'aggrégation utilisées sont MIN() et MAX(), et toutes font référence à la même colonne.

  • Toute les autres parties de l'index de GROUP BY doivent être des constantes (c'est à dire qu'elles doivent être référencées avec des constantes), hormis pour les arguments des fonctions MIN() et MAX().

Le résultat de EXPLAIN pour ces requêtes affiche la valeur Using index for group-by dans la colonne Extra.

Les requêtes suivantes sont autant d'exemple qui sont éligibles, en supposant qu'il existe un index idx(c1, c2, c3) sur la table t1(c1,c2,c3,c4):

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT(c1, c2) FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

Les requêtes suivantes ne peuvent pas être exécutées avec les méthodes de sélection rapide, pour les raisons citées :

  • Il y a d'autres fonctions d'agrégation que MIN() ou MAX() :

    SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
    
  • Les champs de GROUP BY ne font pas référence au début de l'index :

    SELECT c1,c2 FROM t1 GROUP BY c2, c3;
    
  • La requête fait référence à une partie de clé qui est placée après la partie GROUP BY, et pour lesquelles l'égalité ne se fait pas avec une constante :

    SELECT c1,c3 FROM t1 GROUP BY c1, c2;
    

7.2.11.2 Scan d'index systématique

Un scan d'index systématique peut être un scan d'index total, un scan d'intervalle, suivant les conditions.

Lorsque les conditions pour faire un scan d'index restreint ne sont pas la, il est toujours possible d'éviter la constitution de tables temporaires pour les requêtes GROUP BY. S'il y a des conditions d'intervalle dans la clause WHERE, cette méthode ne va lire que les clés qui satisfont les conditions. Sinon, elle appliquera un scan d'index. Comme cette méthode lit toutes les clés de chaque intervalle définit par WHERE, ou scanne tout l'index s'il n'y a pas de condition d'intervalles, nous l'appelons un ``scan d'index systématique''. Notez qu'avec un scan d'index systématique, les opérations de regroupement sont faites après la lecture des clés qui satisfont les conditions.

Pour que cette méthode fonctionne, il suffit que toutes les colonnes d'une requête qui fasse référence à une partie de clé avant ou entre les conditions de la clause GROUP BY, soient des conditions constantes. Ces constantes remplissent les ``trous'' dans les clés de recherche, pour qu'il soit possible de former des préfixes comlets d'index. Ensuite, ces préfixes seront utilisés pour les recherches. Si vous avez besoin de tri avec GROUP BY, et qu'il est possible de former des clés de recherche avec des préfixes d'index, MySQL pourra aussi éviter le tri, car la recherche avec préfixe dans un index ordonnés lit les clés dans l'ordre.

Les requêtes suivantes ne fonctionneront pas avec la première méthode, mais fonctionneront toujours avec la deuxième méthode d'accès aux index (en supposant que nous avons l'index idx sur la table table t1) :

  • Il y a un ``trou'' dans le GROUP BY, mais il est couvert par la condition (c2 = 'a').

    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
    
  • GROUP BY ne commence pas par la première clé, mais il y a une condition qui fournit une constante pour cette partie de clé :

    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
    

7.2.12 Comment MySQL optimise LIMIT

Dans certains cas, MySQL va gérer la requête différemment avec la clause LIMIT #, si la clause HAVING n'est pas utilisée :

  • Si vous ne sélectionnez que quelques lignes avec LIMIT, MySQL va utiliser les index dans certains cas, où il aurait préféré utiliser un scan de table complet.

  • Si vous utilisez LIMIT # avec la clause ORDER BY, MySQL va arrêter de trier dès qu'il a trouvé la première # au lieu de trier toute la table.

  • Lorsque vous combinez LIMIT # avec DISTINCT, MySQL va s'arrêter dès qu'il a trouvé # lignes distinctes.

  • Dans certains cas, la clause GROUP BY peut être appliquée en lisant les clés dans l'ordre (ou en faisant un tri sur la clé), puis en calculant un sommaire, jusqu'à ce que la clé soient modifiée. Dans ce cas, LIMIT # ne va pas appliquer les éléments non nécessaires de la clause GROUP BYs.

  • Aussitôt que MySQL a envoyé les premières # lignes au client, il annule le reste de la requête (si vous n'utilisez pas la fonction SQL_CALC_FOUND_ROWS).

  • LIMIT 0 va toujours retourner rapidement un résultat vide. C'est pratique pour vérifier une requête et lire les types de colonnes du résultat, sans exécuter réellement la requête.

  • Lorsque le serveur utilise des tables temporaire pour résoudre les requêtes, la clause LIMIT # est utilisée pour calculer l'espace nécessaire.

7.2.13 Comment éviter les analyses de tables

EXPLAIN affiche la valeur ALL dans la colonne type lorsque MySQL utilise n scan de table pour résoudre une requête. Cela arrive lorsque :

  • La table est si petite qu'il est plus rapide d'analyser la table que d'utiliser les index. C'est un cas courant pour les tables de moins de 10 lignes, et de taille de ligne faible.

  • Il n'y a pas de restriction exploitable sur les conditions ON et WHERE, avec les colonnes indexées.

  • Vous comparez des colonnes indexées avec des constantes, et MySQL a calculé, en se basant sur l'arbre d'index, que les constantes couvrent une trop grande partie de la table : un scan devrait être plus rapide. See Section 7.2.4, « Comment MySQL optimise les clauses WHERE ».

  • Vous utilisez une clé avec une cardinalité faible (c'est à dire, beaucoup de lignes sont trouvées). MySQL va alors supposer que l'utilisation de l'index va lui imposer beaucoup de recherches, et qu'un scan de table sera plus rapide.

Ce que vous pouvez faire pour éviter les scans de grosses tables :

7.2.14 Vitesse des requêtes INSERT

Le temps d'insertion d'une ligne est constitué comme ceci :

  • Connexion : (3)

  • Envoi au serveur : (2)

  • Analyse de la requête : (2)

  • Insertion de la ligne : (1 x taille de la ligne)

  • Insertion des index  : (1 x nombre d'index)

  • Fermeture : (1)

où les nombres représentent une partie proportionnelle du temps total. Le calcul ne prend pas en compte les coûts d'administration initiaux de l'ouverture des tables (qui est fait une fois pour chaque requête simultanée).

La taille de la table ralentit les opérations d'insertion des index par un facteur de log N (B-trees).

Quelques méthodes pour accélérer les insertions :

  • Si vous insérez plusieurs lignes depuis le même client, en même temps, utilisez les valeurs multiples de la commande INSERT. C'est bien plus rapide (et parfois beaucoup plus rapide) que d'utiliser des commandes INSERT distinctes. Si vous ajoutez des données dans une table non vide, vous pouvez ajuster la variable bulk_insert_buffer_size pour l'accélérer encore plus. See Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ».

  • Si vous insérez de nombreuses lignes depuis différents clients, vous pouvez accélérer les insertions en utilisant la commande INSERT DELAYED. See Section 13.1.4, « Syntaxe de INSERT ».

  • Avec les tables MyISAM, vous pouvez insérer des lignes en même temps que vous utilisez des commandes SELECT, du moment qu'il n'y a pas d'effacement de ligne dans la table.

  • Lorsque vous chargez une table depuis un fichier texte, utilisez la commande LOAD DATA INFILE. Elle est généralement 20 fois plus rapide que l'équivalent en commandes INSERT. See Section 13.1.5, « Syntaxe de LOAD DATA INFILE ».

  • Il est possible, avec un peu de travail supplémentaire, d'accélérer encore la vitesse des commandes LOAD DATA INFILE. Utilisez la procédure standard :

    1. Créez optionnellement une table avec CREATE TABLE. Par exemple, en utilisant mysql ou Perl DBI.

    2. Exécutez une commande FLUSH TABLES ou la commande en ligne shell mysqladmin flush-tables.

    3. Utilisez myisamchk --keys-used=0 -rq /path/to/db/tbl_name. Cela va supprimer l'utilisation des index dans la table.

    4. Insérez vos données dans la table, avec LOAD DATA INFILE. Les index ne seront pas modifiés, et donc, très rapides.

    5. Si vous allez uniquement lire la table dans le futur, utilisez myisampack pour la réduire de taille. See Section 14.1.3.3, « Caractéristiques des tables compressées ».

    6. Re-créez les index avec myisamchk -r -q /path/to/db/tbl_name. Cette commande va créer l'arbre d'index en mémoire, avant de l'écrire sur le disque, ce qui est bien plus rapide, car il n'y a que peu d'accès disques. L'arbre final sera aussi parfaitement équilibrés.

    7. Exécutez une commande FLUSH TABLES ou utilisez la commande en ligne shell mysqladmin flush-tables.

    Notez que la commande LOAD DATA INFILE fait aussi les optimisations ci-dessus, si vous faites les insertions dans une table vide. La différence principale avec la procédure ci-dessus est que vous pouvez laisser myisamchk allouer plus de mémoire temporaire pour la création d'index, que vous ne pourriez le faire pour chaque recréation.

    Depuis MySQL 4.0 vous pouvez aussi utiliser ALTER TABLE tbl_name DISABLE KEYS au lieu de myisamchk --keys-used=0 -rq /path/to/db/tbl_name et ALTER TABLE tbl_name ENABLE KEYS au lieu de myisamchk -r -q /path/to/db/tbl_name. De cette fa¸on, vous pouvez aussi éviter l'étape FLUSH TABLES.

  • Vous pouvez accélérer les insertions qui sont faites avec plusieurs requêtes en verrouillant vos tables :

    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;
    

    La principale différence de vitesse est que l'index de buffer est écrit sur le disque une fois, après toutes les insertions INSERT terminées. Normalement, il aurait du y avoir de nombreuses écritures, une pour chaque commande INSERT. Le verrouillage n'est pas nécessaire si vous pouvez insérer toutes les lignes d'une seule commande.

    Pour les tables transactionnelles, vous devriez utiliser BEGIN/COMMIT au lieu de LOCK TABLES pour accélérer les opérations.

    Le verrouillage va aussi réduire le nombre total de tests de connexions, mais le temps d'attente maximum de certains threads va augmenter (car il va y avoir la queue pour les verrous). Par exemple :

    thread 1 fait 1000 insertions
    thread 2, 3, et 4 font 1 insertion
    thread 5 fait 1000 insertions
    

    Si vous ne voulez pas utiliser le verrouillage, les threads 2, 3 et 4 auront fini avant les 1 et 5. Si vous utilisez le verrouillage, 2, 3 et 4 me finiront probablement pas avant 1 ou 5, mais la durée globale de l'opération sera 40% plus courte.

    Comme les commandes INSERT, UPDATE et DELETE sont très rapides avec MySQL, vous obtiendrez de meilleures performances générales en ajoutant des verrous autour de toutes vos opérations de 5 insertions o modifications simultanées. Si vous faîtes de très nombreux insertions dans une ligne, vous pouvez utiliser LOCK TABLES suivi de UNLOCK TABLES une fois de temps en temps (par exemple, toutes les 1000) pour permettre aux autres threads d'accéder à la table. Cela vous donnera quand même une bonne accélération.

    Bien sur, LOAD DATA INFILE reste bien plus rapide pour charger les données.

  • Pour accélérer LOAD DATA INFILE et INSERT, agrandissez le buffer de clé. See Section 7.5.2, « Réglage des paramètres du serveur ».

7.2.15 Vitesses des commandes UPDATE

Les requêtes de modification sont optimisées comme les requêtes de SELECT avec le coût supplémentaire de l'écriture. La vitesse d'écriture dépend de la taille des données qui sont modifiées, et du nombre d'index que cela va impacter. Les index ne sont pas modifiés tant que la ligne n'est pas écrite. Les index qui ne sont pas modifiés ne seront pas réécrit.

De plus, une autre méthode pour obtenir des accélérations avec les modifications est de retarder les modifications, et d'en faire plusieurs d'un coup. Faire plusieurs modifications d'un coup est bien plus rapide que d'en faire une à chaque fois.

Notez que, avec le format de ligne dynamique, la modification d'une ligne peut déboucher sur la fragmentation de la ligne. Si vous le faite souvent, il est très important d'appliquer OPTIMIZE TABLE sur ces tables, pour les optimiser. See Section 13.5.2.5, « Syntaxe de OPTIMIZE TABLE ».

7.2.16 Rapidité des requêtes DELETE

Si vous voulez effacer toutes les lignes d'une table, vous devez utiliser TRUNCATE TABLE nom_de_table. See Section 13.1.9, « Syntaxe de TRUNCATE ».

Le temps de suppression d'une ligne est exactement proportionnel au nombre d'index. Pour effacer les enregistrements plus rapidement, vous pouvez augmenter la taille du cache d'index. See Section 7.5.2, « Réglage des paramètres du serveur ».

7.2.17 Autres conseils d'optimisation

Quelques conseils en vrac pour accélérer le serveur :

  • Utilisez les connexions persistantes à la base, pour éviter les coûts récurrents de connexion. Si vous ne pouvez pas utiliser de connexions persistantes, et que vous faites de nombreuses connexions à la base, essayez de modifier la valeur de la variable thread_cache_size. See Section 7.5.2, « Réglage des paramètres du serveur ».

  • Vérifiez toujours que vos requêtes utilisent vraiment les index que vous avez créé dans les tables. Avec MySQL, vous pouvez utiliser la commande EXPLAIN. See Explain: (manual) Explain.

  • Essayez d'éviter les requêtes SELECT complexes sur les tables MyISAM qui sont souvent modifiées. Cela évitera des problèmes de verrouillage.

  • Les nouvelles tables MyISAM peuvent insérer des lignes sans en effacer d'autre, tout en lisant dans cette table. Si c'est important pour vous, vous pouvez considérer d'autres méthodes où vous n'avez pas à effacer de lignes, ou bien utilisez OPTIMIZE TABLE après avoir effacé beaucoup de lignes.

  • Utilisez ALTER TABLE ... ORDER BY expr1,expr2... si vous lisez les colonnes dans l'ordre expr1,expr2.... Avec cette option, après de grosses modifications dans la table, vous pourriez obtenir de meilleures performances.

  • Dans certains cas, cela vaut la peine d'ajouter une colonne qui est une combinaison ("hashed") des informations des autres colonnes. Si cette colonne est courte, et plutôt exemptes de doublons, elle peut se révéler plus rapide qu'un gros index sur plusieurs colonnes. Avec MySQL, il est très facile d'utiliser une telle colonne :

    SELECT * FROM tbl_name
        WHERE hash_col=MD5(CONCAT(col1,col2))
        AND col1='constant' AND col2='constant';
    
  • Pour les tables qui sont souvent modifiées, vous devriez essayer d'éviter les colonnes VARCHAR et BLOB. Vous obtiendrez des lignes à format dynamique si vous utilisez ne serait-ce qu'une seule colonne VARCHAR ou BLOB. See Chapitre 14, Moteurs de tables MySQL et types de table.

  • Normalement, cela ne sert à rien de séparer une table en différentes tables plus petites, juste parce que vos lignes deviennent grosses. Pour accéder à une ligne, le plus long est le temps d'accès au premier octets de la ligne. Après cela, les disques modernes vont lire très rapidement la ligne, et suffisamment pour la plus par des applications. Le seul cas où cela peut être important est si vous êtes capables de dégager une table à format de ligne fixe (voir ci-dessus), ou si vous avez besoin de scanner régulièrement la table, mais que vous n'avez pas besoin de toutes les colonnes. See Chapitre 14, Moteurs de tables MySQL et types de table.

  • Si vous avez besoin de calculer souvent des expressions en fonction des informations placées dans de nombreuses lignes (comme compter des lignes), il est probablement plus efficace d'introduire une nouvelle table qui va mettre à jour ce compteur en temps réel. Une modification du type présenté ci-dessous est très rapide!

    UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
    

    C'est très important lorsque vous utilisez les types de tables MyISAM et ISAM, qui ne dispose que d'un verrouillage de table (plusieurs lecteurs, un seul qui écrit). Cela va aussi améliorer les performances avec la plus par des bases, car le gestionnaire de verrouillage de ligne aura moins de tâches à faire.

  • Si vous devez rassembler des statistiques issues de grosses tables de log, utiliser les tables de sommaires plutôt que la table complète. Entretenir un sommaire est bien plus rapide que de regénérer des tables à partir des logs à chaque modification (suivant l'importance de vos informations), plutôt que de modifier l'application qui fonctionne.

  • Si possible, essayez de marquer les rapports comme "direct" ou "statistique", où les données nécessaires pour les rapports statistiques ne sont générées qu'à partir de tables de sommaires, calculées depuis les données réelles.

  • Utilisez les valeurs par défaut des colonnes. N'insérez des valeurs explicitement que lorsque la valeur diffère de la valeur par défaut. Cela réduit le temps d'analyse de MySQL, et améliore les insertions.

  • Dans certains cas, il est pratique de compacter et stocker les données dans un BLOB. Dans ce cas, vous devez ajouter du code supplémentaire pour compacter et décompacter les données dans le BLOB, mais cela pourra vous faire économiser de nombreux accès. C'est pratique lorsque vous avez des données qui ne peuvent s'adapter facilement à une structure de base de données.

  • Normalement, vous devriez essayer de garder vos données non redondantes (ce qui s'appelle la troisième forme normale dans les théories de bases de données), mais ne vous empêchez pas de duplique des données ou de créer des tables de sommaire, pour gagner de la vitesse.

  • Les procédures stockées ou UDF (fonctions utilisateur) peuvent être une bonne fa¸on de gagner en performance. Dans ce cas, vous devriez avoir une méthode pour appliquer les mêmes fonctions d'une autre manière, si votre base ne supporte les procédures stockées.

  • Vous pouvez aussi gagner de la vitesse en utilisant des caches de requêtes dans vos applications, et en essayant de rassembler les nombreuses insertions ou modifications. Si votre base de données supporte le verrouillage de table (comme MySQL et Oracle), cela vous aidera à vous assurer que le cache d'index est vidé après chaque modifications.

  • Utilisez INSERT /*! DELAYED */ lorsque vous n'avez pas besoin d'être assuré que vos données sont écrites. Cela accélère les insertions, car de nombreuses lignes seront écrites en une seule fois.

  • Utilisez INSERT /*! LOW_PRIORITY */ lorsque vous voulez que vos sélections soient prioritaires.

  • Utilisez SELECT /*! HIGH_PRIORITY */ pour rendre les sélections prioritaires. C'est à dire, les sélections seront désormais faites même si un autre programme attend pour écrire.

  • Utilise la commande INSERT multiple pour insérer plusieurs lignes en une seule commande SQL (plusieurs serveurs SQL le supporte).

  • Utilisez LOAD DATA INFILE pour charger de grande quantité de données dans une table. C'est généralement plus rapide que des insertions, et sera même encore plus rapide une fois que myisamchk sera intégré dans mysqld.

  • Utilisez les colonnes AUTO_INCREMENT pour avoir des valeurs uniques.

  • Utilisez OPTIMIZE TABLE une fois de temps en temps, pour éviter la fragmentation lors de l'utilisation de tables avec un format de ligne dynamique. See Section 13.5.2.5, « Syntaxe de OPTIMIZE TABLE ».

  • Utilisez la tables de type HEAP pour accélérer les traitements au maximum. See Chapitre 14, Moteurs de tables MySQL et types de table.

  • Avec un serveur web normal, les images doivent être stockées dans des fichiers. C'est à dire, ne stockez qu'une référence au fichier d'image dans la base. La raison principale à cela est qu'un serveur web est bien meilleur pour mettre en cache des fichiers que le contenu d'une base de données. Il est donc plus rapide si vous utilisez des fichiers.

  • Utilisez des tables en mémoire pour les données non critiques, qui ont besoin d'être lues souvent (comme des informations sur la dernière bannière affichée pour les utilisateurs sans cookies).

  • Les colonnes contenant des informations identiques dans différentes tables doivent être déclarées identiquement lors de la création des tables, et porter des noms identiques. Avant la version 3.23, vous pouviez ralentir les jointures.

    Essayez de garder des noms simples (utilisez nom au lieu de nom_du_client dans la table de clients). Pour rendre vos noms de colonnes portables vers les autres serveurs SQL, vous devriez essayer de les garder plus petits que 18 caractères.

  • Si vous avez vraiment besoin de très haute vitesse, vous devriez considérer les interfaces de bas niveau pour le stockage des données que les différents serveurs SQL supportent. Par exemple, en accédant directement aux tables MySQL MyISAM, vous pourriez obtenir un gain de vitesse de l'ordre de 2 à 5 fois, en comparaison avec l'interface SQL. Pour cela, les données doivent être sur le même serveur que l'application, et généralement, elles ne doivent être manipulées que par un seul programme à la fois (car le verrouillage externe de fichiers est très lent). Vous pouvez éliminer ces problèmes en créant des commandes MyISAM de bas niveau dans le serveur MySQL (cela peut se faire facilement pour améliorer les performances). Soyez très prudent dans la conception de votre interface, mais il est très facile de supporter ce type d'optimisation.

  • Dans de nombreux cas, il est plus rapide d'accéder aux données depuis une base (en utilisant une connexion ouverte) que d'accéder à un fichier texte, car la base de données est plus compacte que le fichier texte (si vous utilisez des données numériques), et cela entraîne moins d'accès disques. Vous allez aussi économiser du code, car vous n'aurez pas à analyser le fichier texte pour repérer les limites de lignes.

  • Vous pouvez aussi utiliser la réplication pour accélérer le serveur. See Chapitre 6, Réplication de MySQL.

  • Déclarer une table avec DELAY_KEY_WRITE=1 va accélérer la mise à jour des index, car ils ne seront pas écrit sur le disque jusqu'à ce que le fichier de données soit refermé. L'inconvénient est que vous devez exécuter l'utilitaire myisamchk sur ces tables avant de lancer mysqld pour vous assurer que les index sont bien à jour, au cas où le processus aurait été interrompu avant d'enregistrer les données. Comme les informations d'index peuvent toujours être regénérées, vous ne perdrez pas de données avec DELAY_KEY_WRITE.

7.3 Verrouillage de tables

7.3.1 Méthodes de verrouillage

Actuellement, MySQL ne supporte que le verrouillage de table pour les tables ISAM/MyISAM et MEMORY (HEAP), le verrouillage de page pour les tables BDB et le verrouillage de ligne pour InnoDB.

Dans de nombreux cas, vous pouvez faire prévoir le type de verrouillage qui sera le plus efficace pour une application, mais il est très difficile de savoir si un type de verrou est meilleur que l'autre. Tout dépend de l'application, et des différentes composants qui utilisent les verrous.

Pour décider si vous voulez utiliser un type de table avec verrouillage de ligne, vous devez commencer par étudier ce que votre application fait, et quel est le schéma d'utilisation des sélections et modifications. Par exemple, la plupart des applications Web font de nombreuses sélections, peu d'effacements, des modifications basées sur des clés, et des insertions dans des tables spécifiques. Le moteur de base MySQL MyISAM est très bien optimisé pour cette application.

Toutes les méthodes de verrouillage de MySQL sont exemptes de blocage, sauf pour les tables InnoDB et BDB. Ceci fonctionne en demandant tous les verrous d'un seul coup, au début de la requête, et en verrouillant les tables toujours dans le même ordre.

Les tables InnoDB obtiennent automatiquement leur verrou de ligne et les tables BDB leur verrou de page, durant le traitement de la requête SQL, et non pas au démarrage de la transaction.

La méthode de verrouillage des tables de MySQL en écriture (WRITE) fonctionne comme ceci :

  • Si il n'y a pas de verrou sur la table, pose un verrou en écriture dessus.

  • Sinon, soumet une requête de verrouillage dans la queue de verrous d'écriture.

La méthode de verrouillage des tables de MySQL en lecture (READ) fonctionne comme ceci :

  • Si il n'y a pas de verrou sur la table, pose un verrou en écriture dessus.

  • Sinon, soumet une requête de verrouillage dans la queue de verrou de lecture.

Lorsqu'un verrou est libéré, le verrou est donné aux threads de la queue de verrou en écriture, puis à ceux de la queue de verrou en lecture.

Cela signifie que si vous avez de nombreuses modifications dans une table, la commande SELECT va attendre qu'il n'y ait plus d'écriture avant de lire.

Depuis MySQL 3.23.33, vous pouvez analyser le comportement des verrous sur une table avec les variables de statut Table_locks_waited et Table_locks_immediate :

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Depuis MySQL 3.23.7 (3.23.25 pour Windows), vous pouvez librement mélanger des commandes INSERT et SELECT sur une table MyISAM sans verrous, si les commandes INSERT sont sans conflit. C'est à dire, vous pouvez insérer des lignes dans une table MyISAM en même temps que d'autres clients lisent la même table. Aucun conflit ne survient si la table ne contient aucun bloc libre dans les données, et que les lignes sont insérées à la fin de la table. Les trous sont des lignes qui ont été effacées. S'il y a des trouvés, les insertions concurrentes sont réactivées automatiquement, lorsque les trous sont bouchés par de nouvelles données.

Pour contourner ce problème dans les cas où vous voulez faire de nombreuses INSERT et SELECT sur la même table, vous pouvez insérer les lignes dans une table temporaire, et ne modifier la table réelle que de temps en temps, à partir de la table temporaire.

Ceci peut être fait comme ceci :

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDB utilise un verrouillage de ligne, et BDB utilise un verrouillage de page. Pour les moteurs InnoDB et BDB, un blocage de verrou est possible. Cela est dû au fait que InnoDB obtient automatiquement un verrou de ligne, et BDB pose le verrou de page durant le traitement SQL, et non pas au démarrage de la transaction.

Avantages du verrouillage de ligne :

  • Moins de conflits de lignes, lorsque les mêmes lignes sont utilisées par différents threads.

  • Moins de modifications pour les annulations (ROLLBACK)

  • Rend possible le verrouillage d'une ligne pour une longue durée.

Inconvénients du verrouillage de ligne :

  • Prend plus de mémoire que les verrous de page ou de table.

  • Est plus lent que les verrous de page ou de table, lorsqu'il est utilisé sur une grand partie de la table, car il faut alors poser plusieurs verrous.

  • Est vraiment bien pire que les autres verrous si vous utilisez souvent la requête GROUP BY sur la majeure partie des données, ou si vous avez à scanner toute la table.

  • Avec des verrous de plus haut niveau, vous pouvez aussi supporter des verrous d'autres types, pour optimiser l'application, car le coût de l'administration est moindre que pour le verrouillage de ligne.

Les verrous de tables sont supérieurs aux verrous de page ou de ligne dans les cas suivants :

  • Les lectures.

  • Les lectures et les modifications sur des clés strictes : c'est le cas si une modification ou un effacement de ligne peut être lu en une seule opération dans l'index.

    UPDATE table_name SET column=value WHERE unique_key#
    DELETE FROM table_name WHERE unique_key=#
    
  • SELECT combiné avec INSERT (et quelques UPDATE et DELETE rares).

  • De nombreux scans / GROUP BY sur toute la table, sans aucune écriture.

Autres possibilités alternatives au verrouillage de ligne ou de page :

Le versionnage (comme celui que nous utilisons pour les insertions simultanées avec MySQL), où vous pouvez avoir un thread qui écrit et de nombreux autres qui lisent. Cela signifie que les bases ou tables supportent différentes vues des données, suivants le moment d'accès aux données. D'autres noms pour cette techniques sont time travel, copy on write ou copy on demand.

La copy on demand (copie sur demande) est dans de nombreuses situations bien meilleure que le verrouillage de page ou de ligne. Le pire reste l'utilisation de mémoire, qui est bien plus forte qu'avec les verrous normaux.

Au lieu d'utiliser le verrouillage de ligne, vous pouvez utiliser des verrous au niveau de l'application (comme les get_lock/release_lock de MySQL). Cela ne fonctionne qu'avec les applications bien élevées.

7.3.2 Problème de verrouillage de tables

MySQL utilise le verrouillage de table (au lieu du verrouillage de ligne ou de colonne) sur tous les types de tables, sauf InnoDB et BDB, pour obtenir un système de verrou à très haute vitesse.

Pour les tables InnoDB et BDB, MySQL n'utilise le verrouillage de table que vous le demandez explicitement avec LOCK TABLES. Pour ces tables, nous vous recommandons de ne jamais utiliser la commande LOCK TABLES, car InnoDB utilise un verrouillage de ligne automatique, et BDB utilise un verrouillage de pages, pour assurer l'isolation des transactions.

Pour les grandes tables, le verrouillage de table est meilleur que le verrouillage de lignes, pour la plupart des applications, mais il recèle quelque pièges.

Le verrouillage de tables permet à de nombreux threads de lire dans la même table, mais si un thread désire écrire dans la table, il doit obtenir un verrou en écriture pour avoir un accès exclusif. Durant la modification, les autres threads qui voudront lire dans cette table, devront attendre.

Comme les modifications de tables sont considérées comme plus importantes que les lectures avec SELECT, toutes les commandes qui modifient la table ont priorités sur les lectures. Cela devrait vous assurer que les modifications ne sont pas retenues trop longtemps, à cause de nombreuses lectures sur une même table. Vous pouvez toutefois modifier cela avec l'option LOW_PRIORITY des commandes de modification, et l'option HIGH_PRIORITY de SELECT).

Depuis MySQL version 3.23.7, vous pouvez utiliser la variable max_write_lock_count pour forcer MySQL à laisser temporairement la place à toutes les commandes SELECT, après un certain nombre de modifications dans la table.

Le verrouillage de table est une mauvaise technique dans les situations suivantes :

  • Un client exécute une commande SELECT qui prend très longtemps.

  • Un autre client exécute une commande UPDATE sur la table. Ce client va devoir attendre que la commande SELECT soit finie.

  • Un autre client exécute une autre commande SELECT sur la même table. Comme UPDATE a la priorité sur SELECT, cette commande SELECT va attendre que UPDATE soit finit. Il va donc attendre que le premier SELECT soit fini.

Des solutions aux problèmes sont :

  • Essayez d'accélérer au maximum les commandes SELECT. Vous pourriez passer par une table de sommaire pour cela.

  • Démarrez mysqld avec l'option --low-priority-updates. Cela va donner aux commandes de modification une priorité plus faible que SELECT. Dans ce cas, c'est la commande SELECT du précédent scénario qui s'exécutera avant la commande INSERT.

  • Vous pouvez donner à une commande spécifique INSERT, UPDATE ou DELETE, une priorité plus basse avec l'attribut LOW_PRIORITY.

  • Démarrez mysqld avec une valeur faible pour max_write_lock_count afin de donner plus souvent la chance aux verrous READ la possibilité de lire des données, entre deux verrous WRITE.

  • Vous pouvez spécifier que toutes les modifications d'un thread spécifique doivent être faites avec un priorité basse, en utilisant la commande SQL : SET LOW_PRIORITY_UPDATES=1. See Section 13.5.2.8, « Syntaxe de SET ».

  • Vous pouvez spécifier qu'une requête particulière SELECT est très importante, en utilisant l'attribut HIGH_PRIORITY. See Section 13.1.7, « Syntaxe de SELECT ».

  • Si vous avez des problèmes avec des INSERT combinés avec des SELECT, utilisez les tables MyISAM car elle supportent les commandes SELECTs et INSERT simultanées.

  • Si vous voulez mélanger les commandes INSERT et SELECT, utilisez l'attribut DELAYED de la commande INSERT pour résoudre ce problème. See Section 13.1.4, « Syntaxe de INSERT ».

  • Si vous avez des problèmes avec des combinaisons de SELECT et DELETE, l'option LIMIT de DELETE peut aider. See Section 13.1.1, « Syntaxe de DELETE ».

  • Utiliser SQL_BUFFER_RESULT avec les commandes SELECT peut aider à réduire la durée des verrous. See Section 13.1.7, « Syntaxe de SELECT ».

  • Vous pouvez changer le code de verrouillage dans le fichier mysys/thr_lock.c pour n'utiliser qu'une queue unique. Dans ce cas, les lectures et écritures auront la même priorité, ce qui peut aider certaines applications.

Voici quelques conseils avec le système de verrouillage de MySQL :

  • Les accès concurents ne sont pas un problème si vous ne mélangez pas les sélections et les modifications de nombreuses lignes dans la même table.

  • Vous pouvez utiliser LOCK TABLES pour accélérer les opérations : de nombreuses modifications dans un même verrou seront plus rapides. Répartir le contenu de la table en plusieurs tables peut aussi aider.

  • Si vous rencontrez des problèmes de vitesse avec les verrous de tables, vous devez être capables d'améliorer les performances en convertissant certaines tables en InnoDB ou BDB. See Chapitre 15, Le moteur de tables InnoDB. See Section 14.4, « Tables BDB ou BerkeleyDB ».

7.4 Optimiser la structure de la base de données

7.4.1 Conception

MySQL conserve les données et les index dans deux fichiers séparés. De nombreux (et en fait presque toutes) les autres bases mélangent les données et les index dans le même fichier. Nous pensons que le choix de MySQL est bien meilleur pour un grand nombre de systèmes modernes.

Une autre méthode de stockage des données est de conserver les informations de chaque colonne dans une zone séparée (par exemple SDBM et Focus). Cela va réduire les performances qui accèdent à plus d'une colonne. Comme cela dégénère vite lorsque plus d'une colonne est utilisée, nous pensons que ce modèle n'est pas bon pour une base de données généraliste.

Les cas les plus courants sont que les index et les données sont stockées ensemble (comme Oracle/Sybase). Dans ce cas, vous aurez aussi les informations de lignes dans la page finale de l'index. L'intérêt d'une telle organisation est que, dans de nombreuses situations, dépendament du cache d'index, vous économisez des lectures disques. Les problèmes de cette organisation sont :

  • Le scan des tables est bien plus lent, car vous devez lire les index pour obtenir les données.

  • Vous ne pouvez pas utiliser uniquement l'index pour lire des données pour une requête.

  • Vous utilisez beaucoup d'espace, et vous devez dupliquer des index de noeuds (car vous ne pouvez pas simplement stocker des lignes dans les noeuds).

  • Les suppressions vont perturber la table (comme les index ne sont pas modifiés lors de l'effacement).

  • Il est plus difficile de ne mettre en cache que les données.

7.4.2 Rendre vos tables aussi compactes que possible

Une des optimisations simple est de réduire au maximum la taille de vos données et de vos index sur le disque et en mémoire. Cela peut donner des accélérations impressionnantes, car les lectures sur le disque sont plus rapides, et moins de mémoire centrale sera utilisée. L'indexation de colonnes de petites taille prend aussi moins de ressources.

MySQL supporte un grand nombre de type de tables et de format de ligne. Choisir ces types peut vous conduire à des améliorations de performances. See Chapitre 14, Moteurs de tables MySQL et types de table.

Vous pouvez obtenir des gains de performances sur les tables et minimiser l'espace disque en utilisant les techniques ci-dessous :

  • Utilisez les types les plus efficaces et les plus petits possibles. MySQL a différents types spécialisés qui épargnent de l'espace disque et de la mémoire.

  • Utilisez les types d'entiers les plus petits possible pour réduire les tables. Par exemple, MEDIUMINT est souvent préférable à INT.

  • Déclarez les colonnes pour qu'elle soient NOT NULL si possible. Cela accélère les traitements, et vous fait gagner un bit par colonne. Notez que si vous avez vraiment besoin d'une valeur NULL dans votre application, il est recommandé de l'utiliser. Evitez simplement de l'utiliser par défaut sur toutes les colonnes.

  • Si vous n'avez pas de colonne de taille variable (VARCHAR, TEXT ou BLOB), un format de ligne à taille fixe est utilisé. C'est plus rapide, mais cela prend plus d'espace sur le disque. See Section 14.1.3, « Formats de table MyISAM ».

  • La clé primaire doit être aussi courte que possible. Cela rend l'identification des lignes plus efficace.

  • Ne créez que des index dont vous avez besoin. Les index sont bons pour accélérer les lectures, mais sont plus lents lorsque vous écrivez des données. Si vous accéder essentiellement à votre table en lecture avec des combinaisons de colonnes, faites un index avec ces colonnes. Le premier index doit être la colonne la plus utilisée. Si vous utilisez constamment de nombreuses colonnes, vous devriez utiliser la colonne avec le plus de doublons en premier, pour obtenir une meilleure compression.

  • Si il est probable qu'une colonne a un préfixe unique avec les premiers caractères, il est mieux de n'indexer que ce préfixe. MySQL supporte les index sur une partie de colonne. Les index les plus courts sont les plus efficaces car ils prennent moins d'espace disque, et aussi, car ils absorbent plus de requêtes grâce au cache en mémoire. See Section 7.5.2, « Réglage des paramètres du serveur ».

  • Dans certaines circonstances, il peut être intéressant de séparer en deux une table qui est scannée très souvent. C'est particulièrement vrai pour les formats de tables dynamiques, et si possible, utilisez un format de table statique pour les colonnes les plus pertinentes.

7.4.3 Index de colonnes

Tous les types de colonnes ed MySQL peuvent être indexés. L'utilisation des index sur les colonnes pertinentes est la meilleur fa¸on d'améliorer les performances de opérations de SELECT.

Le nombre maximum de clefs et la longueur maximale des index sont définis pour chaque type de table. See Chapitre 14, Moteurs de tables MySQL et types de table. Vous pouvez avec tous les gestionnaires de tables avoir au moins 16 clefs et une taille totale d'index d'au moins 256 octets.

Pour les colonnes CHAR et VARCHAR, il est possible d'indexer un préfixe de la colonne. C'est plus rapide et plus économe en espace disque que l'indexation de la colonne entière. La syntaxe pour indexer le début d'une colonne au moment de la création de la table ressemble à cela: See Section 7.4.3, « Index de colonnes ».

Les moteurs de tables MyISAM et (depuis MySQL 4.0.14) InnoDB supportent aussi l'indexation des colonnes BLOB et TEXT. Lors de l'indexation d'une colonne BLOB ou TEXT, vous devez spécifier une taille pour l'index. Par exemple :

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Les préfixes peuvent atteindre 255 octets de longeur (ou 1000 octets pour les tables MyISAM et InnoDB depuis MySQL 4.1.2). Notez que les limites de préfixes sont mesurées en octets, alors que la limite de préfixe dans la commande CREATE TABLE est interprétée comme un nombre de caractères. Prenez le en compte lorsque vous spécifiez une taille de préfixe pour une colonne qui utilise un jeu de caractères multi-octets.

Depuis MySQL 3.23.23, vous pouvez aussi créer des index FULLTEXT. Ils sont utilisés pour les recherches en texte plein. Seules les tables MyISAM supportent les index FULLTEXT et uniquement pour les colonnes CHAR, VARCHAR, et TEXT. L'indexation se fait sur toute la largeur de la colonne. L'indexation par préfixe n'est pas possible. Voyez la section Section 12.6, « Recherche en texte intégral (Full-text) dans MySQL » pour plus de détails.

Depuis MySQL 4.1.0, vous pouvez créer des index spatiaux. Actuellement, le type de données spatial n'est supporté que par les tables MyISAM. Les données spatiales utilisent un R-tree.

Le moteur de tables MEMORY (HEAP) supporte les index hash. Depuis MySQL 4.1.0, ce moteur supporte aussi les index B-tree.

7.4.4 Index sur plusieurs colonnes

MySQL peut créer des index sur plusieurs colonnes. Un index peut comprendre jusqu'à 15 colonnes. (sur les colonnes de type CHAR ou VARCHAR, vous pouvez utiliser uniquement le début de la colonne pour l'indexation.) (see Section 7.4.3, « Index de colonnes »).

Un index sur plusieurs colonnes peut être compris comme un tableau trié contenant des valeurs créées par concaténation des valeurs des colonnes indexées.

MySQL utilise les index sur plusieurs colonnes de telle sorte que les requêtes sont accélérées quand on spécifie une quantité connue de la première colonne de l'index dans un clause WHERE, même si on ne spécifie pas la valeur des autres colonnes.

On suppose qu'une table est créée avec les paramètres suivant:

mysql> CREATE TABLE test (
    ->       id INT NOT NULL,
    ->       nom CHAR(30) NOT NULL,
    ->       prenom CHAR(30) NOT NULL,
    ->       PRIMARY KEY (id),
    ->       INDEX nom_index (nom,prenom));

Alors l'index nom_index est un index de nom et de prenom. Cela sera utile pour les requêtes qui spécifient des valeurs dans une gamme donnée de nom, ou pour à la fois nom et prenom. Ainsi l'index nom_index sera utilisé pour les requêtes suivantes:

mysql> SELECT * FROM test WHERE nom="Widenius";

mysql> SELECT * FROM test WHERE nom="Widenius"
    ->                    AND prenom="Michael";

mysql> SELECT * FROM test WHERE nom="Widenius"
    ->                    AND (prenom="Michael" OR prenom="Monty");

mysql> SELECT * FROM test WHERE nom="Widenius"
    ->                    AND prenom >="M" AND prenom < "N";

Cependant, l'index nom_index ne sera pas utilisé pour les requêtes suivantes :

mysql> SELECT * FROM test WHERE prenom="Michael";

mysql> SELECT * FROM test WHERE nom="Widenius"
    ->                    OR prenom="Michael";

Pour plus d'informations sur la méthode de MySQL pour utiliser les index dans le but d'améliorer les performance des requêtes, voyez la section suivante.

7.4.5 Comment MySQL utilise les index

Les index sont utilisés pour trouver des lignes de résultat avec une valeur spécifique, très rapidement. Sans index, MySQL doit lire successivement toutes les lignes, et à chaque fois, faire les comparaisons nécessaires pour extraire un résultat pertinent. Plus la table est grosse, plus c'est coûteux. Si la table dispose d'un index pour les colonnes utilisées, MySQL peut alors trouver rapidement les positions des lignes dans le fichier de données, sans avoir à fouiller toute la table. Si une table à 1000 lignes, l'opération sera alors 100 fois plus rapide qu'une lecture séquentielle. Notez que si vous devez lire la presque totalité des 1000 lignes, la lecture séquentielle se révélera alors plus rapide, malgré tout.

Tous les index de MySQL (PRIMARY, UNIQUE et INDEX) sont stockés sous la forme de B-tree. Les chaînes sont automatiquement préfixée et leurs espaces terminaux sont supprimés. See Section 13.2.4, « Syntaxe de CREATE INDEX ».

Les index sont utilisés pour :

  • Trouver rapidement des lignes qui satisfont une clause WHERE.

  • Ecarter rapidement des lignes. S'il y a un choix à faire entre plusieurs index, MySQL utilise généralement celui qui retourne le plus petit nombre de lignes.

  • Lire des lignes dans d'autres tables lors des jointures.

  • Trouver les valeurs MAX() et MIN() pour une colonne indexée. C'est une opération qui est optimisée par le preprocesseur, qui vérifie si vous utilisez la constante WHERE key_part_# = sur toute les parties de clés inférieures à < N. Dans ce cas, MySQL va faire une simple recherche de clé et remplacer l'expression par une constante. Si toutes les expressions sont remplacées par des constantes, la requête va alors être rapidement calculée :

    SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
    
  • Trier ou grouper des lignes dans une table, si le tri ou le regroupement est fait avec un préfixe à gauche utilisable (par exemple, ORDER BY key_part_1,key_part_2 ). La clé est lue en ordre inverse, si toutes les parties de clés sont suivies du mot clé DESC. See Section 7.2.10, « Comment MySQL optimise ORDER BY ».

  • Dans certains cas, la requête peut être optimisée pour lire des valeurs sans consulter le fichier de données. Si cette possibilité est utilisée avec des colonnes qui sont toutes numériques, et forme le préfixe de gauche d'une clé, les valeurs peuvent être lues depuis l'index, à grande vitesse :

    SELECT key_part3 FROM table_name WHERE key_part1=1
    

Supposez que vous utilisiez la commande SELECT suivante :

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

Si un index multi-colonne existe sur les colonnes col1 et col2, les lignes appropriées seront directement lues. Si des index séparés sur les colonnes col1 et col2 existent, l'optimiseur va essayer de trouver l'index le plus restrictif des deux, en décidant quel index débouche sur le moins de lignes possibles.

Si une table a un index multi-colonne, tout préfixe d'index peut être utilisé par l'optimiseur pour trouver des lignes. Par exemple, si vous avez un index à trois colonnes (col1,col2,col3), vous pouvez faire des recherches accélérées sur les combinaisons de colonnes (col1), (col1,col2) et (col1,col2,col3).

MySQL ne peut utiliser d'index partiel sir les colonnes ne forment pas un préfixe d'index. Supposez que vous avez la commande SELECT suivante :

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Si un index existe sur les colonnes (col1,col2,col3), seule la première requête pourra utiliser l'index ci-dessus. Les deux autres requêtes utilisent des colonnes indexées, mais les colonnes (col2) et (col2,col3) ne font pas partie du préfixe des colonnes (col1,col2,col3).

MySQL utilise aussi les index lors des comparaisons avec l'opérateur LIKE si l'argument de LIKE est une chaîne constante qui ne commence pas par un caractère joker. Par exemple, les requêtes SELECT suivantes utilisent des index :

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Dans le premier exemple, seules les lignes avec "Patrick" <= key_col < "Patricl" sont considérées. Dans le second exemple, "Pat" <= key_col < "Pau" sont considérées.

Les commandes SELECT suivantes n'utilisent pas d'index :

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

Dans la première requête, la valeur associée à LIKE commence avec un caractère joker. Dans le second exemple, la valeur associée à LIKE n'est pas une valeur constante.

MySQL 4.0 fait une autre optimisation avec l'opérateur LIKE. Si vous utilisez ... LIKE "%string%" et que string est plus grand que 3 caractères, MySQL va utiliser l'algorithme Turbo Boyer-Moore qui prend une valeur initiale pour résoudre le masque, et l'exploite pour accélérer la recherche.

Les recherches qui utilisent la fonction column_name IS NULL vont utiliser les index si column_name sont des index.

MySQL normalement utilise l'index qui génère le moins de lignes possible. Un index est utilisé avec les colonnes que vous spécifiez, et les opérateurs suivants : =, >, >=, <, <=, BETWEEN et l'opérateur LIKE sans préfixe joker, c'est à dire de la forme 'quelquechose%'.

Un index qui ne s'applique pas à tous les niveaux de AND dans une requête WHERE, ne sera pas utilisé pour optimiser la requête. En d'autres termes, pour être capable d'utiliser un index pour optimiser une requête, un préfixe de l'index doit être utilisé dans toutes les parties de la formule logique contenant AND.

Les clauses WHERE suivantes utilisent des 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é par "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
          /* peut utiliser un index sur index1 mais pas sur index2 ou index 3 */

Ces clauses WHERE n'utilisent pas d'index :

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 n'est pas utilisé */
... WHERE index=1 OR A=10                  /* Index n'est pas utilisé sur les deux parties du AND */
... WHERE index_part1=1 OR index_part2=10  /* Aucun index ne s'applique à toutes les colonnes */

Notez que dans certains cas, MySQL ne va pas utiliser un index, même s'il y en a un disponible. Si l'utilisation de l'index requiert que MySQL accède à plus de 30% des lignes de la table (dans ce cas, un scan de table est probablement plus rapide, et demandera moins d'accès disques). Notez que si une telle requête utilise la clause LIMIT pour ne lire qu'une partie des lignes, MySQL utilisera tout de même l'index, car il va trouver plus rapidement les quelques lignes de résultat.

Les index hash ont des caractéristiques différentes de celles présentées :

  • Elles sont utilisées uniquement pour les comparaisons avec les opérateurs = ou <=> (mais elles sont très rapides).

  • L'optimiseur ne peut pas utiliser un index hash pour accélérer une clause ORDER BY. Ce type d'index ne peut être utilisé que pour rechercher la prochaine ligne dans l'ordre.

  • MySQL ne peut déterminer approximativement le nombre de lignes qui sont présentes entre deux valeurs : cette valeur est utilisée par l'optimiseur d'intervalle pour décider quel index utiliser. Cela affecte certaines requêtes, si vous changez la table MyISAM en table MEMORY.

  • Seules les clés entières peuvent être recherchées, pour une ligne. Avec un index B-tree, un préfixe peut être utilisé pour trouver les lignes.

7.4.6 Le cache de clé des tables MyISAM

Pour réduire les accès aux disques, le moteur MyISAM emploie une stratégie utilisé par de nombreux systèmes de bases de données. Il utilise un cache qui garde en mémoire les blocs de tables les plus souvent utilisés.

  • Pour les blocs d'index, une structure spéciale appelée cache de clés (buffer de clés) est entretenue. La structure contient un certain nombre de bloc de mémoire, où les blocs d'index les plus souvent sollicités résident.

  • Pour les blocs de données, MySQL n'utilise pas de cache. Au lieu de cela, il exploite le cache natif du système de fichiers.

Cette section décrit les opérations basiques du cache de clés MyISAM. Puis, elle présente les modifications apportées en MySQL version 4.1 pour améliorer les performances du cache de clés, et vous donner un meilleur contrôle sur les opérations de cache.

  • Les accès aux caches de clés ne sont pas séquentiels entre les threads. Des accès simultanés sont désormais possibles.

  • Vous pouvez configurer plusieurs caches de clés, et assigner différents index de tables, spécifiquement.

Le mécanisme de cache de clés est aussi utilisé par les tables ISAM. Toutefois, ce n'est pas significatif. Les tables ISAM sont de moins en moins utilisée depuis l'introduction en MySQL 3.23 des tables MyISAM. MySQL 4.1 va plus loin : les tables ISAM sont désactivées par défaut.

Vous pouvez contrôler la taille du cache de clé avec la variable système key_buffer_size. Si cette variable vaut zéro, le cache ne sera pas utilisé. Le cache de clés est aussi désactivé si la valeur de key_buffer_size est trop petite pour allouer le nombre minimal de blocs de buffers (8).

Lorsque le cache de clés n'est pas opérationnel, les fichiers d'index sont lus avec le cache du système de fichiers, fourni par le système d'exploitation. En d'autres termes, les index sont lus avec la même technique que les blocs de données.

Un bloc d'index est une adresse unitaire pour le fichier d'index MyISAM. Généralement, la taille d'un bloc d'index est égal à la taille des noeuds de l'index B-tree. Les index sont représentés sur le disque en utilisant un arbre B-tree. Les noeuds terminaux sont appelés des feuilles. Les noeuds qui ne sont pas des feuilles sont dits non-terminaux.

Tous les blocs de buffer dans la structure de cache de clés ont la même taille. Cette taille peut être égale, supérieure ou inférieure à la taille de bloc d'index de la table. Généralement, un de ces deux valeurs est un multiple de l'autre.

Lorsque des données d'un bloc d'index de table doivent être lues, le serveur commence par vérifier si elles sont disponibles dans le cache de clés, plutôt que sur le disque. C'est à dire, qu'il va préférer écrire ou lire dans le cache de clés que sur le disque. Sinon, le serveur choisit un bloc de cache contenant un index d'une autre table, et remplace les données par celles de la table qu'il manipule. Dès que le bloc est dans le cache, les données d'index sont accessibles.

Si un des blocs sélectionnés pour être écrasé, a été modifié, le bloc est considéré comme ``sale.'' Dans ce cas, avant d'être remplacé, il est d'abord écrit dans le fichier d'index, sur le disque.

Généralement, le serveur suit une heuristique LRU (Least Recently Used : le moins utilisé) : lorsqu'il choisit un bloc pour être remplacé, il sélectionne le bloc qui a été accédé le moins souvent. Pour faciliter ce choix, le module de cache de clés entretient une queue (la chaîne LRU) de tous les blocs utilisés. Lorsqu'un bloc doit être remplacé, les blocs du début de la queue sont les moins souvent sélectionnés, et sont les candidats au remplacement.

7.4.6.1 Accès au cache de clé partagé

Avant MySQL 4.1, les accès au cache de clé étaient sérialisé : deux threads ne pouvaient y accéder simultanément. Les processus serveur demandent un bloc après avoir finit de traiter la requête précédente. En conséquence, une requête pour un bloc d'index qui n'est pas présente dans le cache de clés bloque l'accès aux autres threads lorsque le cache est complété avec le nouveau bloc.

Depuis la version 4.1.0, le serveur supporte un accès partagé au cache de clés :

  • Un buffer qui n'est pas modifié peut être lu par plusieurs threads.

  • Un buffer qui est modifié fait attendre les threads qui doivent l'utiliser jusqu'à la fin de la modification.

  • Plusieurs threads peuvent initier des requêtes qui engendreront des remplacement de cache, tant qu'ils n'interfèrent pas les uns avec les autres : c'est à dire tant qu'ils ont besoin de blocs différents, et qu'ils remplacent des blocs différents.

L'accès partagé au cache de clé permet au serveur d'améliorer considérablement la vitesse d'exécution.

7.4.6.2 Caches multiples de clés

Les accès partagés à un cache de clés améliorent grandement les performances, mais ne règlent pas les contentieux entre les threads. Ils sont toujours en compétition pour les structures de contrôle qui donnent l'accès aux caches de clés. Pour réduire ces frictions, MySQL 4.1.1 dispose de caches multiples de clés. Cela vous permet d'assigner différentes clés à différents caches.

Lorsqu'il peut y avoir un cache de clé multiple, le serveur doit savoir quel cache utiliser lors du traitement d'une requête, pour une table MyISAM. Par défaut, les index des tables MyISAM dans le cache par défaut. Pour assigner un index à un cache spécifique, utilisez la commande CACHE INDEX.

Par exemple, les deux commandes suivantes assignent les index des tables t1, t2 et t3 au cache de clé appelé hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

Note : si le serveur a été compilé en incluant le moteur de table ISAM, les tables ISAM utilise le mécanisme de cache de clés. Mais les index ISAM utilisent uniquement le cache de clés par défaut, et ils ne peuvent pas être assignés à un autre cache.

Le cache de clés indiqué dans la commande CACHE INDEX peut être créé en spécifiant sa taille avec le paramètre SET GLOBAL ou en utilisant les options de démarrage. Par exemple :

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

Pour détruire un cache de clé, donnez lui une taille de zéro :

mysql> SET GLOBAL keycache1.key_buffer_size=0;

Les variables de cache de clés sont des variables systèmes structurées, qui ont un nom et des composants. Pour keycache1.key_buffer_size, keycache1 est le nom de variable du cache, et key_buffer_size est le composant de cache. Voyez Section 9.4.1, « Variables système structurées » pour une description de la syntaxe utilisée pour faire référence aux variables système structurée.

Par défaut, les index de tables sont assignés dans le cache de clé principal, dit aussi cache par défaut. Ce cache est créé au démarrage. Lorsqu'un cache de clés est détruit, les index qui lui étaient assignés sont assignés au cache par défaut.

Pour un serveur en charge, nous recommandons la stratégie suivante pour les caches de clés :

  • Un cache de clés principal qui représente 20% de l'espace alloué pour tous les caches de clés. Il sera utilisé par les tables qui sont le plus sollicitées, mais qui ne sont pas modifiées.

  • Un cache de clés minoritaire qui représente 20% de l'espace alloué pour tous les caches de clés. Il sera utilisé pour les tables de taille intermédiaires, qui sont intensivement modifiées, comme des tables temporaires, par exemple.

  • Un cache de clés secondaire qui représente 60% de l'espace alloué pour tous les caches de clés. C'est le cache de clé par défaut, il sera utilisé pour toutes les autres tables.

Une raison pour utiliser les trois caches de clés est que l'accès à un des caches ne bloque pas l'accès aux autres. Les requêtes qui accèdent aux index d'un des caches ne sont pas en compétition avec les requêtes qui utilisent les index dans les autres cache. Les gains de performances sont aussi dûs à :

  • Le cache principal est utilisé pour les requêtes en lecture et son contenu est jamais modifié. Par conséquent, lorsqu'un bloc d'index doit être lu sur le disque, le contenu du bloc remplacé n'a pas besoin d'être sauvé.

  • Pour un index assigné au cache principal, s'il n'y a pas de requêtes qui font des scans d'index, il y a une haute probabilité que tous les blocs d'index qui ne sont pas terminaux resteront dans le cache.

  • Une opération de modification sur une table temporaire est effectuée plus rapidement lorsque le noeud à modifier est déjà dans le cache, et n'a pas besoin d'être lu dans le disque. Si la taille des index de la table temporaire est comparable à la taille du cache minoritaire, la probabilité est très haute que l'index soit déjà dans le cache.

7.4.6.3 Stratégie d'insertion au milieu

Par défaut, le système de gestion de cache de clé de MySQL 4.1 utilise la stratégie LRU pour choisir les blocs de cache qui doivent être remplacés, mais il accepte aussi une autre méthode plus sophistiquée, appelée "stratégie de l'insertion au milieu".

Lors de l'utilisation de la stratégie d'insertion au milieu, la chaîne LRU est divisée en deux parties : une sous-chaîne principale, et une sous-chaîne secondaire. Le point de division entre les deux parties n'est pas fixé, mais le système s'assure que la partie principale n'est pas ``trop petite'', et qu'elle contient au moins key_cache_division_limit % de bloc de cache de clés. key_cache_division_limit est un composant d'une variable structurée de cache de clé, et sa valeur peut être modifiée indépendamment pour chaque cache.

Lorsqu'un bloc d'index est lu dans une table, depuis le cache de clé, il est placé à la fin de la sous-chaîne secondaire. Après un certain nombre d'accès, il est promu dans la sous-chaîne principale. Actuellement, le nombre d'accès requis pour passer un bloc et le même pour tous les blocs d'index. Dans le futur, nous allons permettre au compteur d'accès d'utiliser le niveau de B-tree : moins d'accès seront nécessaires à un noeud s'il contient un noeud non-terminal d'un des niveaux supérieur de l'index B-tree.

Un bloc de la chaîne principale est placé à la fin de la chaîne. Le bloc circule alors dans la la sous-chaîne. Si le bloc reste à la fin de la sous-chaîne suffisamment longtemps, il est rétrogradé dans la chaîne secondaire. Ce temps est déterminé par la valeur du composant key_cache_age_threshold.

La valeur de seuil prescrit que, pour un cache de clé contenant N blocs, le bloc au début de la chaîne principale qui n'est pas accédé dans les derniers N*key_cache_age_threshold/100 accès doit être placé au début de la chaîne secondaire. Il devient le premier candidat à l'éviction, car les blocs de remplacement sont toujours pris au début de la chaîne secondaire.

La stratégie de l'insertion au milieu vous permet de garder les blocs les plus utilisés dans le cache. Si vous préférez utiliser la stratégie LRU classique, laissez la valeur de key_cache_division_limit à 100.

La stratégie d'insertion au milieu aide à améliorer les performances lorsque l'exécution d'une requête qui requiert un scan d'index place dans le cache toutes les valeurs de l'index. Pour éviter cela, vous devez utiliser la stratégie d'insertion au milieu, avec une valeur très inférieure à 100 pour key_cache_division_limit. Les blocs les plus utilisés seront conservés dans le cache durant un tel scan.

7.4.6.4 Pré-chargement des index

S'il y a suffisamment de blocs dans le cache de clé pour contenir tout un index, ou au moins les blocs correspondant aux blocs non-terminaux, alors cela vaut la peine de pré-charger l'index avant de commencer à l'utiliser. Le pré-chargement vous premet de mettre les blocs d'index dans un buffer de cache le plus efficacement : il lit les blocs séquentiellement sur le disque.

Sans le pré-chargement, les blocs seront placées dans le cache de clé, au fur et à mesure des besoins des requêtes. Même si les blocs resteront dans le cache, puisqu'il y a de la place pour tous, ils seront pris sur le disque dans un ordre aléatoire, et non séquentiellement.

Pour pré-charger un index dans un cache, utilisez la commande LOAD INDEX INTO CACHE. Par exemple, la commande suivante précharge les index des tables t1 et t2 :

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

L'option IGNORE LEAVES fait que les blocs non-terminaux seuls seront lus dans l'index. Par conséquent, la commande ci-dessus va charger tous les blocs de l'index de t1, mais uniquement les blocs non-terminaux de t2.

Si un index a été assigné à un cache de clé en utilisant la commande CACHE INDEX, le pré-chargement place les blocs dans ce cache. Sinon, l'index est chargé dans le cache par défaut.

7.4.6.5 Taille des blocs du cache de clé

MySQL 4.1 propose une nouvelle variable key_cache_block_size, pour chaque clé. Cette variable spécifie la taille du bloc de buffer pour le cache de clé. Il sert à optimiser les performances d'E/S pour les fichiers d'index.

Les meilleurs performances d'E/S sont obtenues lorsque la taille du buffer de lecture est égale à la taille des opérations natives d'E/S système. Mais en donnant une taille de bloc de clé égale à la taille du buffer d'E/S ne donne pas les meilleures performances. Lors de la lecture de grands blocs terminaux, le serveur charge beaucoup de données inutiles, ce qui empêche la lecture d'autres noeuds.

Actuellement, vous ne pouvez pas contrôler la taille des blocs d'index dans la table. Cette taille est fixée par le serveur lorsque le fichier d'index .MYI est créé, en fonction de la taille des index de la table. Dans la plupart des cas, il est choisi égal à la taille du buffer d'E/S. Dans le futur, cela sera changé, et la variable key_cache_block_size sera exploitée.

7.4.6.6 Restructurer le cache de clé

Un cache de clé peut être restructuré à tout moment, en modifiant les valeurs de ses paramètres. Par exemple :

mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;

Si vous assignez une nouvelle valeurs aux variables key_buffer_size ou key_cache_block_size, le serveur va détruire l'ancienne structure du cache, et en recréer un, en se basant sur les nouvelles valeurs. Si le cache contient des blocs sales, le serveur va les sauver sur le disque avant de détruire et recréer le cache. La restructuration n'intervient pas si vous modifiez un autre paramètre du cache.

Lors de la restructuration d'un cache de clé, le serveur commence par vider le contenu des buffers sales sur le disque. Après cela, le contenu du cache devient inaccessible. Cependant, la restructuration ne bloque pas les requêtes qui utilisent des index qui sont dans le cache. Au lieu de cela, le serveur accède directement à la table et utilise le cache du système de fichiers. Le cache du système de fichiers n'est pas aussi efficace que celui du cache de clés : si les requêtes s'exécutent toujours, elles seront un peu ralenties. Une fois que le cache a été restructuré, il redevient accessible, et les blocs peuvent être placés dedans. Le cache du système de fichiers n'est plus utilisé.

7.4.7 Comment MySQL compte les tables ouvertes

Quand vous utiliserez la commande mysqladmin status, vous verrez quelque chose de ce genre :

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

Cela vous laissera perplexe si vous n'avez que 6 tables.

MySQL est multi-threadé, il peut donc exécuter plusieurs requêtes sur la même table simultanément. Pour minimiser les interférences entre deux threads ayant différentes actions sur le même fichier, la table est ouverte indépendamment par chacun des threads. Cela nécessite un peu de mémoire, mais augmente les performances. Avec les tables au format ISAM et MyISAM, cela requière aussi un fichier additionnel de description du fichier des données. Avec ce type de tables, le fichier décrivant l'index est partagé entre tous les threads.

Vous pourrez lire plus sur le sujet à la section suivante : See Section 7.4.8, « Quand MySQL ouvre et ferme les tables ».

7.4.8 Quand MySQL ouvre et ferme les tables

table_cache, max_connections et max_tmp_tables affectent le nombre maximum de tables que le serveur garde ouvertes. Si vous augmentez l'une de ces valeurs, vous pourriez rencontrer une des limites de votre système d'exploitation. Cependant, vous pourrez augmenter ces limites sur de nombreux systèmes d'exploitation. Consultez votre documentation système pour voir comment faire cela, car la méthode pour modifier la limite est différente pour chaque système.

table_cache est lié au max_connections. Par exemple, pour 200 connexions simultanées, vous devriez avoir un cache de table d'environ 200 * n, où n est le nombre maximum de table dans une jointure. Vous devez aussi réserver des pointeurs de fichiers supplémentaires pour les tables temporaires et les fichiers.

Assurez vous que votre système d'exploitation peut gérer le nombre de pointeurs de fichiers demandé par l'option table_cache. Si table_cache est trop grand, MySQL peut être à court de pointeurs, et refuser des connexions, échouer à l'exécution de requêtes, ou être très instable. Vous devez aussi prendre en compte que les tables MyISAM peuvent avoir besoin de deux pointeurs de fichiers pour chaque table différente. Vous pouvez augmenter le nombre de pointeurs de fichiers disponibles pour MySQL avec l'option de démarrage --open-files-limit=#. See Section A.2.17, « Fichier non trouvé ».

Le cache de tables ouvertes reste au niveau de table_cache entrées (par défaut, 64; cela peut être modifié avec l'option -O table_cache=# de mysqld). Notez que MySQL peut ouvrir temporairement plus de tables, pour être capable d'exécuter des requêtes.

Une table qui n'est pas utilisée est refermée, et supprimée du cache de table, dans les circonstances suivantes :

  • Lorsque le cache est plein, et qu'un thread essaie d'ouvrir une table qui n'est pas dans le cache.

  • Lorsque le cache contient plus de table_cache lignes, et qu'aucun thread n'utilise cette table.

  • Lorsque quelqu'un utilise la commande mysqladmin refresh ou mysqladmin flush-tables.

  • Lorsque quelqu'un exécute la commande FLUSH TABLES.

Lorsque le cache de table se remplit, le serveur utilise la procédure suivante pour identifier une entrée du cache, pour la supprimer :

  • Les tables qui n'est pas en cours d'utilisation est libérée, en utilisant la table qui a été accédé depuis plus longtemps en premier.

  • Si le cache est plein, et qu'aucune table ne peut être libérée, mais qu'une nouvelle table doit être ouverte, le cache est temporairement étendu.

  • Si le cache est dans un état d'extension, et qu'une table passe de l'état d'utilisation à non utilisation, la table est immédiatement fermée et libérée du cache.

Une table est ouverte pour chaque accès simultané. Cela signifie que si vous avez deux threads qui accèdent à la même table, ou accèdent à la même table deux fois dans la requête (avec AS), la table devra être ouverte deux fois. La première ouverture d'une table prendre deux pointeurs de fichiers. Chaque utilisation supplémentaire de la même table ne prendra qu'un pointeur supplémentaire. Le pointeur de fichier supplémentaire de la première table est celui du fichier d'index. Ce pointeur est partagé entre les threads.

Si vous ouvrez une table avec HANDLER table_name OPEN, un objet de table dédié sera alloué pour le thread. Cet objet de table n'est pas partagé avec les autres threads, et il ne sera pas fermé avant que le thread n'appelle HANDLER table_name CLOSE, ou que le thread ne meurt. See Section 13.1.3, « Syntaxe de HANDLER ». Lorsque cela arrive, la table est placée dans le cache de table (si il n'est pas plein).

Vous pouvez vérifier si votre cache de table n'est pas trop petit en vérifiant la variable de mysqld appelée Opened_tables. Si cette valeur est grande, même si vous n'avez pas trop abusé de la commande FLUSH TABLES, vous devrez augmenter la taille du cache. See Section 13.5.3.15, « Syntaxe de SHOW STATUS ».

7.4.9 Inconvénients de la création d'un grand nombre de tables dans la même base de données

Si vous avez beaucoup de fichiers dans un dossier, les opérations d'ouverture, fermeture, et création seront ralenties. Si vous exécutez une requête SELECT sur plusieurs tables, il y aura une légère perte lorsque le cache de tables sera plein, car pour chaque table ouverte, une autre doit être fermée. Vous pouvez réduire cette table en augmentant la taille du cache de tables.

7.5 Optimiser le serveur MySQL

7.5.1 Réglage du système, au moment de la compilation, et paramètres du démarrage

Nous démarrons par le niveau du système, car certaines décisions à ce niveau doivent être prises très tôt. Dans d'autres cas, un regard rapide à cette partie doit suffire, car ce n'est pas tellement important pour les gros gains. Toutefois, il est toujours sympathique de sentir combien on peut gagner en changeant des choses à ce niveau.

Le choix du système d'exploitation est vraiment important! Pour utiliser au maximum les capacités de machines multi-processeurs, il vaut mieux choisir Solaris (car les threads marchent vraiment très bien) ou Linux (car le noyau 2.2 supporte très bien le SMP). Mais les plates-formes Linux 32 bits limitent par défaut la taille des fichiers à 2 Go. Heureusement, cela sera bientôt réparé avec l'arrivée des nouveaux systèmes de fichier (XFS/Reiserfs). Si vous souhaitez désespérement utiliser des fichiers de plus de 2 Go sur Linux-intel 32 bits, vous devriez utiliser le patch de LFS pour le système de fichier ext2

Comme nous n'avons pas utilisé MySQL en production sur énormément de plates-formes, nous vous conseillons de tester votre plate-forme avant de la choisir définitivement.

Autres astuces:

  • Si vous avez suffisamment de RAM, vous pouvez supprimer toutes les partitions d'échange (swap). Certains systèmes d'exploitation utilisent parfois la partition d'échange quand bien même il reste de la mémoire libre.

  • L'utilisation de l'option --skip-external-locking de MySQL empêche les verrous externes. Cela n'influencera pas les fonctionnalités de MySQL tant que vous n'utilisez qu'un seul serveur. Il faut cependant penser à arrêter le serveur (ou bien de verrouiller les parties pertinentes) avant d'utiliser myisamchk. Sur certains systèmes, cette option est inutile car les verrous externes ne fonctionnent pas du tout.

    L'option --skip-external-locking est activée par défaut quand on compile avec MIT-pthreads, car flock() n'est pas totalement supporté sur toutes les plates-formes par MIT-pthreads. Elle l'est également sur Linux, cat le verrouillage des fichiers de Linux n'est pas encore sûr.

    Les seuls cas où on ne peut pas utiliser --skip-external-locking sont si on utilise plusieurs serveurs (pas de clients) MySQL sur les mêmes données, ou si on lance myisamchk sur une table sans vider son tampon et sans la verrouiller au préalable.

    Il est toujours possible d'utiliser LOCK TABLES/UNLOCK TABLES même si vous utilisez --skip-external-locking.

7.5.2 Réglage des paramètres du serveur

Vous pouvez obtenir les tailles par défaut des tampons du serveur mysqld avec la commande:

shell> mysqld --help

Cette commande génère une liste de toutes les options de mysqld et des variables configurables. Cette sortie comprend les valeurs par défaut et ressemble à cela :

Possible variables for option --set-variable (-O) are:
back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
record_rnd_buffer_size   current value: 131072
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

Si un serveur mysqld est en cours d'exécution, vous pouvez voir les valeurs que les variables utilisent réellement en exécutant la commande :

mysql> SHOW VARIABLES;

Vous pouvez obtenir les statistiques et différents indicateurs de statut pour un serveur en fonctionnement avec cette commande :

mysql> SHOW STATUS;

Les variables de serveur et de statut sont aussi accessibles avec mysqladmin:

shell> mysqladmin variables
shell> mysqladmin extended-status

Vous pouvez trouver une description complète de toutes les variables système dans les sections Section 5.2.3, « Variables serveur système » et Section 5.2.4, « Variables de statut du serveur ».

MySQL utilise des algorithmes très extensibles, donc vous pouvez utiliser très peu de mémoire. Si malgré tout vous fournissez plus de mémoire à MySQL, vous obtiendrez également de meilleures performances.

Les deux variables les plus importantes au moment du réglage d'un serveur MySQL sont key_buffer_size et table_cache. Vous devriez vous assurer que celles sont sont bien paramétrées avant de modifier les autres variables.

Les exemples suivants indiquent quelques valeurs typiques pour différentes valeurs de configuration. Les exemples utilisent le script mysqld_safe et utilisent la syntaxe --name=value pour donner à la variable appelée name la valeur value. Cette syntaxe est disponible depuis MySQL 4.0. Pour les anciennes versions de MySQL, prenez en compte ces différences :

  • Utilisez safe_mysqld plutôt que mysqld_safe.

  • Utilisez la syntaxe de modification des variables --set-variable=nom=valeur ou -O name=value.

  • Pour les noms de variables qui finissent par _size, vous pouvez les spécifier sans le suffise _size. Par exemple, l'ancien nom de sort_buffer_size est sort_buffer. L'ancien nom de read_buffer_size est record_buffer. Pour voir quelles variables votre version du serveur reconnaît, utilisez mysqld --help.

Si vous avez beaucoup de mémoire (>=256 Mo) et beaucoup de tables, et que vous désirez des performances maximales avec un faible de nombre de clients, vous devriez essayer quelque chose cela:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O read_buffer_size=1M &

Si vous n'avez que 128 Mo et seulement quelques tables, mais que vous demandez beaucoup de classements, vous pouvez essayer cela :

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

Si vous avez peu de mémoire et beaucoup de connections, essayez cela:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O read_buffer_size=100k &

Ou encore:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O read_buffer_size=8k -O net_buffer_length=1K &

Si vous utilisez GROUP BY ou ORDER BY sur des fichiers de taille supérieure à la mémoire disponible, vous devriez augmenter la valeur de record_rnd_buffer pour accélérer la lecture des lignes après que le classement ait été fait.

A l'installation de MySQL, un répertoire support-files est créé, et contient plusieurs exemples de fichiers my.cnf: my-huge.cnf, my-large.cnf, my-medium.cnf et my-small.cnf. Vous pouvez les utiliser comme base pour optimiser votre système.

Si vous avez vraiment beaucoup de connections, des problèmes peuvent apparaître avec le fichier d'échange si mysqld n'a pas été configuré pour utiliser peu de mémoire pour chaque connexion. mysqld fonctionne mieux si vous avec suffisamment de mémoire pour toutes les connections, bien sûr !

Notez que si vous changez une option de mysqld, elle ne prendra effet qu'au prochain démarrage du serveur.

Pour voir les effets d'un changement de paramètre, essayez quelque chose comme ca:

shell> mysqld -O key_buffer=32m --help

Les valeurs des variables sont listées vers la fin du résultat. Assurez vous bien de la présence de l'option --help en fin de ligne; si ce n'est pas le cas, les options listées après dans la ligne de commande ne seront pas prises en compte à la sortie.

Pour plus d'information sur le paramétrage du moteur InnoDB, voyez la section Section 15.12, « Conseils pour l'amélioration des performances InnoDB ».

7.5.3 Contrôle des performances de l'optimisateur de requêtes

La tâche de l'optimisateur de requête est de trouver une méthode optimale pour exécuter une requête SQL. Comme la différence entre de ``bonnes'' et de ``mauvaises'' performances peut être de plusieurs grandeur d'ordre, la plupart des optimisateurs de requêtes, y compris celui de MySQL, fait une recherche plus ou moins exhaustive des méthodes possibles pour traiter une requête. Pour les jointures, le nombre de méthodes croit exponentiellement avec le nombre de tables. Pour les petits nombres de tables (jusqu'à 7 ou 10), ce n'est pas sensible. Mais dès que de grosses requêtes sont soumises, le temps passé à l'optimisation peut être source de ralentissement pour le serveur.

MySQL 5.0.1 propose une nouvelle méthode plus souple pour l'optimisation, qui permet à l'utilisateur de contrôler l'exhaustivité de la recherche de l'optimisateur dans sa quête pour la méthode la plus efficace pour traiter une requête. L'idée générale est que plus le nombre de méthodes étudiées est petit, moins l'optimisateur prendra de temps à compiler la requête. D'un autre coté, comme l'optimisateur a omis certaines méthodes, il peut avoir mis de coté la méthode optimale.

Le comportement de l'optimisateur peut être contrôlé grâce à deux variables système :

  • La variable optimizer_prune_level indique à l'optimisateur d'omettre des méthodes basées sur l'estimation du nombre de lignes utilisées dans les tables. Notre expérience montre que ce type de ``prévision'' échoue rarement, tout en réduisant considérablement le temps de compilation des requêtes. C'est pour cela que cette variable est active par défaut (optimizer_prune_level=1). Cependant, si vous pensez que l'optimisateur pourrait trouver mieux, alors cette option peut être désactivée (optimizer_prune_level=0), au risque de voir la compilation de la requête prendre beaucoup plus de temps. Notez que même si vous utilisez cette heuristique, l'optimisateur va étudier un nombre exponentiel de méthodes.

  • La variable optimizer_search_depth indique la ``profondeur'' d'analyse de l'optimisateur. Les valeurs les plus faibles de optimizer_search_depth peuvent conduire à de grandes différences dans le temps de compilation. Par exemple, une requête avec 12-13 ou plus peut facilement prendre des heures ou des jours à compiler si optimizer_search_depth a une valeur proche du nombre de tables à traiter. Mais, si optimizer_search_depth vaut 3 ou 4, le compilateur peut traiter cette requête en une minute environ. Si vous n'êtes pas sûrs de la valeur raisonnable de optimizer_search_depth, donnez lui la valeur de 0 pour que l'optimisateur puisse déterminer la valeur automatiquement.

7.5.4 Influences de la compilation et des liaisons sur la vitesse de MySQL

La plupart des tests suivants ont été réalisés sous Linux avec les outils comparatifs de MySQL, mais ils peuvent donner quelques indications pour d'autres systèmes d'exploitation et sur une charge de travail différente.

Les exécutables les plus rapides sont obtenus en liant avec -static.

Sur Linux, le code le plus rapide sera obtenu en compilant avec pgcc et -O3. Pour compiler sql_yacc.cc avec ces options, il faut environ 200 Mo de mémoire car gcc/pgcc demande beaucoup de mémoire pour créer toutes les fonctions d'une traite. Il est aussi possible d'utiliser CXX=gcc à la configuration de MySQL pour éviter l'inclusion de la bibliothèque libstdc++ (qui n'est pas nécessaire). Sachez que pour certaines versions de pgcc, le code résultant ne fonctionnera que sur de vrais processeurs Pentium, même si vous utilisez l'option du compilateur qui doit générer du code fonctionnant sur tour les types de processeurs x586 (comme AMD).

L'utilisation du meilleur compilateur et/ou de la meilleur option de compilation permet de gagner 10 à 30% de vitesse dans vos applications. C'est très important quand vous compilez le serveur SQL vous-même !

Nous avons compilé avec les compilateurs de Cygnus CodeFusion et de Fujitsu, mais aucun des deux n'était suffisamment exempt d'erreurs pour permettre la compilation de MySQL avec l'optimisation.

A la compilation de MySQL, vous devriez uniquement utiliser le support des caractères que vous allez utiliser. (Option --with-charset=xxx.) Les distributions binaires standards de MySQL sont compilées avec le support de toutes les gammes de caractères.

Voici une liste des mesures que nous avons effectués:

  • L'utilisation de pgcc et la compilation complète avec l'option -O6 donne un serveur mysqld 1% plus rapide qu'avec gcc 2.95.2.

  • Si vous utilisez la liaison dynamique (sans -static), le résultat est 13% plus lent sur Linux. Sachez que vous pouvez néanmoins utiliser la liaison dynamique pour les bibliothèques de MySQL. Seul le serveur a des performances critiques.

  • Si vous allégez votre binaire mysqld avec l'option strip libexec/mysqld, vous obtenez un binaire jusqu'à 4% plus rapide.

  • Si vous utilisez TCP/IP plutôt que les sockets Unix, le résultat est 7.5% plus lent sur le même ordinateur. (Si vous vous connectez sur localhost, MySQL utilisera les sockets par défaut.)

  • Si vous vous connectez en TCP/IP depuis un autre ordinateur avec un lien Ethernet 100 Mo/s, le résultat sera 8 à 11% plus lent.

  • L'utilisation de connections sécurisées ( toutes les données chiffrées par le support interne de SSL) pour nos tests comparatifs a provoqué une perte de vitesse de 55%.

  • Si vous compilez avec --with-debug=full, vous perdrez 20% de performances sur la plupart des requêtes, mais la perte peut être plus importante sur certaines requêtes (La suite de tests de MySQL tourne 35% plus lentement). Si vous utilisez --with-debug, vous ne perdrez que 15%. En démarrant une version de mysqld, compilée avec --with-debug=full, avec --skip-safemalloc, le résultat final devrait être proche d'une compilation avec --with-debug.

  • Sur un Sun UltraSPARC-IIe, Forte 5.0 est 4% plus rapide que gcc 3.2.

  • Sur un Sun UltraSPARC-IIe, Forte 5.0 est 4% plus rapide en mode 32 bit qu'en mode 64 bit.

  • La compilation avec gcc 2.95.2 sur UltraSPARC avec l'option -mcpu=v8 -Wa,-xarch=v8plusa améliore les performances de 4%.

  • Sur Solaris 2.5.1, MIT-pthreads est 8-12% plus lent que la gestion native des threads de Solaris sur mono-processeur. Avec plus de charge ou de CPU, la différente devrait être encore plus grande.

  • La compilation sur Linux-x86 avec gcc sans les pointeurs -fomit-frame-pointer ou -fomit-frame-pointer -ffixed-ebp rend mysqld 1 à 4% plus rapide.

Autrefois les distributions fournies par MySQL AB de MySQL-Linux étaient compilées avec pgcc, mais nous avons dû revenir au simple gcc à cause d'un bogue dans pgcc qui générait du code qui ne fonctionnait pas sur AMD. Nous continuerons à utiliser gcc tant que ce bogue ne sera pas corrigé. Néanmoins, si vous avez une machine non-AMD, vous pouvez obtenir des binaires plus rapides en compilant avec pgcc. Le binaire standard de MySQL pour Linux est lié statiquement pour être plus rapide et plus portable.

7.5.5 Comment MySQL gère la mémoire

La liste suivante indique certaines techniques utilisées par le serveur mysqld pour gérer la mémoire. Lorsque c'est possible, la variable serveur liée à la mémoire est indiquée :

  • Le buffer de clés (variable key_buffer_size) est partagé par tous les threads. Les autres buffers sont sont alloués par le serveur suivant les besoins. See Section 7.5.2, « Réglage des paramètres du serveur ».

  • Chaque connexion utilise un espace spécifique au thread :

    • une pile (par défaut, 64 ko, variable thread_stack),

    • un buffer de connexion (variable net_buffer_length),

    • un buffer de résultat (variable net_buffer_length).

    Le buffer de connexion et celui de résultat sont dynamiquement élargit jusqu'à max_allowed_packet suivant les besoins. Lorsque la requête s'exécute, une copie de la chaîne de requête est aussi allouée.

  • Tous les threads partagent la même mémoire de base.

  • Seules les tables compressées ISAM / MyISAM sont copiées en mémoire. Ceci est dû au fait que pour un espace de 32 bits, il n'y a pas de place pour les grosses tables en mémoire. Lorsque les systèmes de 64 bits seront plus répandus, nous pourrions généraliser le support pour la copie en mémoire.

  • Chaque requête qui effectue une analyse séquentielle d'une table, alloue un buffer de lecture (variable record_buffer).

  • Lors de la lecture de lignes en ordre 'aléatoire' (par exemple, après un tri), un buffer de lecture aléatoire est allouée pour éviter les accès disques (variable record_rnd_buffer).

  • Toutes les jointures sont faîtes en une seule passe, et la plupart des jointure sont faîtes sans utiliser de table temporaire. La plupart des table temporaires sont faîtes en mémoire (table HEAP). Les tables temporaires avec beaucoup de données (calculées comme la somme des tailles de toutes les colonnes) ou qui contiennent des colonnes de type BLOB sont sauvées sur le disque.

    Un problème avec les versions de MySQL antérieures à la version 3.23.2 est que si une table HEAP dépassait la taille maximale de tmp_table_size, vous obteniez une erreur The table tbl_name is full. Dans les nouvelles versions, ce problème est géré en passant automatiquement la table HEAP en une table MyISAM sur le disque. Pour contourner ce problème, vous pouvez augmenter la taille maximale des tables en mémoire en modifiant l'option tmp_table_size de mysqld, ou en modifiant l'option SQL BIG_TABLES dans le programme client. See Section 13.5.2.8, « Syntaxe de SET ». En MySQL version 3.20, la taille maximale de la table temporaire est record_buffer*16, ce qui fait que si vous utilisez cette version, vous aurez à augmenter la valeur de record_buffer. Vous pouvez aussi démarrer mysqld avec l'option --big-tables pour toujours stocker les tables temporaires sur le disque. Cependant, cela va affecter la vitesse de votre serveur pour les requêtes complexes.

  • La plupart des requêtes qui sont triées allouent un buffer de tri, et entre 0 et 2 fichiers temporaires, suivant la taille du résultat. See Section A.4.4, « Où MySQL stocke les fichiers temporaires ? ».

  • Toute l'analyse et les calculs sont faits en mémoire locale. Aucune mémoire supplémentaire n'est nécessaire pour les petits calculs, et les allocations et libérations de mémoire sont évités. La mémoire n'est allouée que pour les chaînes très grandes (ceci se fait via malloc() et free()).

  • Chaque fichier d'index est ouvert une fois, et le fichier de données est ouvert pour chaque thread concurrent. Pour chaque thread concurrent, une structure de table, une structure de colonne pour chaque colonne et un buffer de taille 3 * n est alloué (où n est la taille maximale de ligne, en dehors des colonnes de type BLOB). Une colonne de type BLOB utilise 5 à 8 octets de plus que la taille des données du BLOB. Les gestionnaires de table ISAM/MyISAM utilisent un buffer d'une ligne de plus pour leur utilisation interne.

  • Pour chaque table qui a une colonne BLOB, un buffer est dynamiquement agrandi pour lire les valeurs BLOB. Si vous analysez toute une table, un buffer aussi grand que la plus grande valeur de la colonne BLOB sera alloué.

  • Les gestionnaires de tables pour les tables en cours d'utilisation sont sauvées dans un cache, et géré comme une pile FIFO. Normalement, ce cache contient 64 lignes. Si une table doit être utilisée par deux threads concurrents simultanément, le cache contiendra deux entrées pour la table. See Section 7.4.8, « Quand MySQL ouvre et ferme les tables ».

  • La commande mysqladmin flush-tables ferme toute les tables qui ne sont pas utilisées, et marque toutes les tables en cours d'utilisation pour qu'elles soient fermées dès la fin du thread. Cela va libérer l'essentiel de la mémoire utilisée.

ps et d'autres commandes de statut système peuvent indiquer que mysqld utilise beaucoup de mémoire. Ceci est peut être dû a des erreurs de comptabilité. Par exemple, sous Solaris, ps compte la mémoire inutilisée entre les threads comme de la mémoire utilisée. Vous pouvez le vérifier en regardant l'état de la swap avec swap -s. Nous avons testé mysqld avec les détecteurs de fuite mémoire commerciaux, et il n'y a aucune fuite.

7.5.6 Comment MySQL utilise le DNS

Quand un nouveau thread se connecte à mysqld, mysqld crée nouveau thread pour traiter la requête. Ce thread contrôle d'abord si le nom de l'hôte est dans le cache des noms d'hôte. Si ce n'est pas le cas, le thread va appeler gethostbyaddr_r() et gethostbyname_r() pour résoudre le nom de l'hôte.

  • Si le système d'opération supporte les fonctions gethostbyaddr_r() et gethostbyname_r(), compatibles avec les threads, elles sont utilisées pour la résolution.

  • Si le système d'exploitation ne supporte pas les appels précédents, le thread va verrouiller un "mutex" et appeler gethostbyaddr() et gethostbyname() à la place. Sachez que dans ce cas, aucun autre thread ne peut résoudre de nom d'hôte qui n'est pas dans le cache tant que le premier thread n'a pas fini.

Il est possible de désactiver la recherche du nom par DNS en démarrant mysqld avec l'option --skip-name-resolve. Dans ce cas, il est toujours possible d'utiliser les adresses IP dans les tables de privilèges de MySQL.

Si votre service DNS est très lent et que vous avez beaucoup d'hôtes, vous pouvez améliorer les performances soit en désactivant le DNS avec --skip-name-resolve, soit en augmentant la taille de HOST_CACHE_SIZE (par défaut: 128) et en recompilant mysqld.

Il est possible de désactiver le cache de noms d'hôte avec --skip-host-cache. Il est possible de vider le cache des noms d'hôtes avec FLUSH HOSTS ou avec mysqladmin flush-hosts.

Si vous ne voulez pas autoriser les connections par TCP/IP, vous pouvez utiliser l'option --skip-networking au démarrage de mysqld.

7.6 Problèmes avec les disques

  • Comme mentionné plus tôt, les accès disques représentent une limitation. Ce problème devient de plus en plus apparent, au fur et à mesure que les données sont de plus en plus nombreuses, et que les techniques de cache deviennent impossibles. Pour les grandes bases de données, lorsque vous accédez aux données plus ou moins aléatoirement, vous pouvez être sûr que vous aurez besoin d'un accès disque pour lire, et de plusieurs autres pour écrire. Pour minimiser le problème, utilisez des disques avec des temps d'accès très faibles.

  • Augmentez le nombre de disques disponibles (et donc, réduisez le coût d' un accès), en pla¸ant des données sur d'autres fichiers via des liens symboliques.

    • Utiliser des liens symboliques

      Cela signifie que vous allez faire un lien symbolique sur le fichier d'index et/ou le fichier de données sur un autre disque. Cela améliore les lectures et écriture (surtout si ces disques ne sont alors utilisés qu'à ¸a). See Section 7.6.1, « Utiliser des liens symboliques ».

    • Parallèlisme

      Le parallélisme signifie que vous avez plusieurs disques matériel, et que vous écrivez le premier bloc de données sur le premier disque, puis le second bloc de données sur le second disque, et le n-ième bloc sur le n-ième disque, etc. Cela signifie que si la taille normale de vos données sont moins grand que le nombre de disque disponibles, vous obtiendrez alors des performances additionnées. Notez que le parallélisme est très dépendant du nombre de disque disponibles et du système d'exploitation. See Section 7.1.5, « Utiliser vos propres tests de performance ».

      Notez que la différence de performance avec le parallélisme est très dépendante des paramètres. Suivant la fa¸on avec laquelle vous avez configuré les disques en parallèle, et le nombre de disque que vous utilisez, le facteur d'amélioration peut être très variable. Notez que vous devez faire votre optimisation en lecture aléatoire ou séquentielle.

  • Pour plus de robustesse, vous pouvez utiliser des disques en RAID 0+1 (parallélisme et réplication), mais dans ce cas, vous aurez besoin de 2*N disques pour contenir vos données sur N disques. C'est probablement l'option la plus sûre, si vous avez le budget pour cela. Vous risquez aussi d'avoir à investir dans un système de gestion de gros volume de données pour gérer cela efficacement.

  • Une bonne option est de garder les données semi-importantes (qui peuvent être regénérées) sur un disque RAID 0 tandis que les données vraiment importantes (comme les informations d'hôtes et les log) sur un disque de type RAID 0+1 ou RAID N. RAID N peut être un problème si vous avez de nombreux accès en écrire, à cause du temps de modification des bits de parité.

  • Sous Linux, vous pouvez améliorer les performances (jusqu'à 100% en charge n'est pas difficile) en utilisant hdparm pour configurer votre interface disque. La commande suivante doit être une série de bonnes options de hdparm pour MySQL (et probablement d'autres applications) :

    hdparm -m 16 -d 1
    

    Notez que la performances et la robustesse des solutions ci-dessus dépendent de votre matériel, et nous vous conseillons vivement de tester votre système soigneusement après avoir utilisé hdparm! Consultez le manuel de hdparm pour plus de détails. Si hdparm n'est pas utilisé correctement, le système de fichiers peut être corrompu. Sauvegardez tout avant d'expérimenter.

  • Vous pouvez aussi modifier les paramètres suivants sur le système de fichiers que la base de données utilise :

    Si vous n'avez pas besoin de savoir quand un fichier a été accédé la dernière fois (ce qui n'est pas utile avec un serveur de base de données), vous pouvez monter votre système de fichier avec l'option -o noatime.

    Sur de nombreux systèmes d'exploitation, vous pouvez monter des disques avec l'option -o async pour que le système de fichiers soit modifié de manière asynchrone. Si votre serveur est raisonnablement stable, vous devriez obtenir de bonne performances sans sacrifier la stabilité (cette option est activée par défaut sur Linux).

7.6.1 Utiliser des liens symboliques

Vous pouvez déplacer les dossiers de bases de données et les placer dans un autre endroit, puis remplacer les dossiers eux-mêmes par des liens symboliques vers ces autres endroits. Vous pourriez vouloir faire cela pour mettre la base de données sur un système de fichier plus rapide, ou pour gagner de l'espace disque sur le système central, ou encore répartir vos tables sur différents disques.

Le mieux, pour cela, est de faire des liens symboliques des bases vers les différents disques, et de ne faire des liens symboliques sur les tables qu'en dernier ressort.

7.6.1.1 Utiliser les liens symboliques pour les bases

Pour créer des liens symboliques sur les bases de données, vous devez commencer par créer un dossier sur un disque de destination, puis faire un lien symbolique depuis le dossier de données vers votre dossier de destination.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

MySQL n'accepte pas que vous fassiez le lien depuis plusieurs bases sur le même dossier. Remplacer une base par un lien symbolique sera correct tant que vous n'essayez pas de faire des liens symboliques dans la même base. Supposez que vous la base db1 dans le dossier de données MySQL, puis que vous fassiez un lien symbolique db2 qui pointe sur db1 :

shell> cd /path/to/datadir
shell> ln -s db1 db2

Maintenant, pour toute table tbl_a de db1, il en apparaît aussi tbl_a dans db2. Si un thread modifie db1.tbl_a et un autre db2.tbl_a, il va y avoir un conflit.

Si vous avez vraiment besoin de cette fonctionnalité, vous devez changer le code suivant dans le fichier C mysys/mf_format.c:

if (!(MyFlags & MY_RESOLVE_LINK) || 
    (!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

Avant MySQL 4.0, recherchez cette instruction dans le fichier mysys/mf_format.c :

if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

Remplacez l'instruction par :

if (1)

Sous Windows, vous pouvez utiliser des liens internes symboliques pour relier des bases en compilant MySQL avec l'option -DUSE_SYMDIR. Cela vous permettra de placer vos bases de données sur différentes partitions. See Section 7.6.1.3, « Utiliser des liens symboliques pour les bases de données sous Windows ».

7.6.1.2 Utiliser les liens symboliques avec les tables sous Unix

Avant MySQL 4.0, vous ne devez pas utiliser les liens symboliques avec les tables, si vous n'êtes pas très prudents avec. Le problème est que si vous exécutez ALTER TABLE, REPAIR TABLE ou OPTIMIZE TABLE sur une table symbolique, le lien sera supprimé et remplacé par le fichier original. Cela arrive car les commandes ci-dessus fonctionnent en créant un fichier temporaire dans le dossier de base, et lorsque l'opération est faite, l'original est remplacé par la copie.

Vous ne devez pas utiliser des liens symboliques sur les tables, sur les systèmes qui ne supportent pas complètement la fonction realpath(). (Au moins Linux et Solaris supportent realpath())

En MySQL 4.0, les liens symboliques sont complètement supportés par les tables MyISAM. Les autres types de tables vous donneront des résultats étranges lorsque vous les utilisez comme indiqué ci-dessus.

La gestion des liens symboliques de MySQL 4.0 fonctionne comme ceci (uniquement pour les tables MyISAM) :

Dans le dossier de données, vous allez toujours trouver le fichier de définition de table, le fichier de structure et le fichier d'index.

  • Dans le dossier de données, vous devez toujours avoir le fichier de définition de table, le fichier de données et le fichier d'index. Les fichiers de données et d'index peuvent être déplacés ailleurs, et remplacés dans le dossier de données par des liens symboliques. Mais le fichier de définition ne le peut pas.

  • Vous pouvez utiliser un lien symbolique avec le fichier d'index et celui de données, pour placer ces fichiers dans d'autres dossiers.

  • Le lien symbolique peut être fait via le système d'exploitation (si mysqld ne fonctionne pas) ou avec la commande INDEX/DATA DIRECTORY="path-to-dir" dans CREATE TABLE. See Section 13.2.5, « Syntaxe de CREATE TABLE ».

  • myisamchk ne va pas remplacer un lien symbolique avec les données ou le fichier d'index, mais il va travailler directement sur le fichier vers lequel le lien pointe. Tous les fichiers temporaires seront créé dans le même dossier que le dossier qui contient les données ou le fichier d'index.

  • Lorsque vous détruisez une table qui utilise un lien symbolique, le fichier et le lien symbolique sont détruits. C'est une bonne raison pour ne pas exécuter mysqld en tant que root ou donner des droits d'écriture à d'autres personnes dans les dossiers de données de MySQL.

  • Si vous renommez une table avec ALTER TABLE RENAME vous n'avez pas à déplacer la table dans une autre base, le lien symbolique du dossier de base sera renommé avec le nouveau nom.

  • Si vous utilisez la commande ALTER TABLE RENAME pour déplacer la table dans une autre base, la table sera déplacée dans l'autre base, et l'ancien lien symbolique et le fichier vers lequel il pointait seront détruits (en d'autres termes, la nouvelle table ne sera pas un lien symbolique).

  • Si vous n'utilisez pas de lien symbolique, vous devriez utiliser l'option --skip-symlink de mysqld pour vous assurer que personne n'efface ou ne renomme un fichier en dehors du dossier de données de MySQL.

SHOW CREATE TABLE n'indique pas si une table a des liens symboliques, avant la version 4.0.15. C'est aussi vrai pour mysqldump, qui utilise SHOW CREATE TABLE pour générer les commandes CREATE TABLE.

Ce qui n'est pas encore supporté :

  • ALTER TABLE ignore toutes les options INDEX/DATA DIRECTORY="path".

  • BACKUP TABLE et RESTORE TABLE ne respectent pas les liens symboliques.

  • Le fichier .frm ne doit jamais être un lien symbolique (Comme indiqué précédemment, seul les fichiers d'index et de données peuvent être des liens symboliques. Si jamais vous le faites malgré tout, vous générerez des erreurs de cohérence. Supposez que vous une base db1 dans le dossier de données MySQL, et une table tbl1 dans cette base, et dans le dossier db1, vous faites un lien symbolique tbl2 qui pointe sur tbl1 :

    shell> cd /path/to/datadir/db1
    shell> ln -s tbl1.frm tbl2.frm
    shell> ln -s tbl1.MYD tbl2.MYD
    shell> ln -s tbl1.MYI tbl2.MYI
    

    Il va y avoir des problèmes si un thread lit db1.tbl1 et qu'un autre modifie db1.tbl2:

    • Le cache de requête sera induit en erreur (il va croire que tbl1 a été mis à jour, et retournera des résultats incohérents).

    • La commande ALTER de la table tbl2 va aussi échouer.

7.6.1.3 Utiliser des liens symboliques pour les bases de données sous Windows

Depuis MySQL 3.23.16, les serveurs mysqld-max et mysql-max-nt de la stribution MySQL sont compilé avec l'option -DUSE_SYMDIR. Cela vous permet de disposer d'un dossier de base de données sur un autre disque, en utilisant un lien symbolique vers ce dossier, même si la procédure à suivre pour configurer ce lien est différent.

Depuis MySQL 4.0, les liens symboliques sont activés par défaut. Si vous n'en avez pas besoin, vous pouvez les désactiver avec l'option skip-symbolic-links :

[mysqld]
skip-symbolic-links

Avant MySQL 4.0, les liens symboliques sont désactivés par défaut. Pour les activer, vous pouvez ajouter la ligne suivante dans votre fichier my.cnf ou my.ini :

[mysqld]
symbolic-links

Sous Windows, vous créez un lien symbolique vers une base de données MySQL en créant un fichier qui contient le nom du dossier de destination. Sauvez le fichier dans le dossier de données, en utilisant le nom db_name.sym, où db_name est le nom de la base.

Supposons que le dossier de données MySQL est C:\mysql\data et que votre base foo soit placée dans le dossier D:\data\foo, vous pouvez configurer les liens symboliques comme ceci :

  1. Assurez vous que le dossier D:\data\foo existe bien, en le créant si nécessaire. Si vous avez déjà un dossier appelé foo dans le dossier de données, vous devez le déplacer dans D:\data. Sinon, le lien symbolique sera inopérant. Pour éviter les problèmes, le serveur ne doit pas fonctionner lorsque vous déplacez le dossier.

  2. Créez le fichier C:\mysql\data\foo.sym qui contient le chemin D:\data\foo\.

Après cela, toutes les tables créées dans la base foo seront créées dans le dossier D:\data\foo. Notes que les liens symboliques ne seront pas utilisés si un dossier du même nom existe dans le dossier de données MySQL.