Chapitre 7 Optimisation 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:

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.

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

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.

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)

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

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.

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.

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 :

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 :

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.

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

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 :

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 :

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

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.

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

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');

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'

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

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 :

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 :

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

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;

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

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;

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;

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.

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.

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

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.

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

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

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 :

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;

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.

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;

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 :

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 :

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

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

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 :

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

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

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

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 :

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

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 :

Inconvénients du verrouillage de ligne :

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

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.

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 :

Des solutions aux problèmes sont :

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

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 :

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 :

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.

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.

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 :

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 :

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.

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.

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.

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

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

mysql> SET GLOBAL keycache1.key_buffer_size=0;

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

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

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

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 de table se remplit, le serveur utilise la procédure suivante pour identifier une entrée du cache, pour la supprimer :

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

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.

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:

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 :

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

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

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.

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 :

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.

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.

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.

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.

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

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

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

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

if (1)

[mysqld]
skip-symbolic-links

[mysqld]
symbolic-links