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.
Le plus important pour obtenir un système rapide est bien sûr le schéma utilisé. Il faut également savoir le genre de choses que fera le système et où seront les goulots d'étranglement.
Les ralentissements les plus courants sont :
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 section 7 Types de tables MySQL.
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:
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.
NULL
La raison des règles précitées est que nous ne pouvons pas vérifier les conditions avant que la requête ne commence à être exécutée. Si nous rencontrons un problème après avoir mis à jour plusieurs lignes, nous ne pouvons plus revenir en arrière puisque la table ne le supporte pas. Nous ne pouvons pas nous arrêter car le travail de mise à jour ne serait fait qu'à moitié, ce qui est sans doute le pire qui puisse arriver. Dans ce cas, il vaut mieux faire du mieux qu'on peut et continuer comme si rien ne s'était passé.
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
http://www.mysql.com/information/crash-me.php 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.
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'acquitera 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)
| Lecture de 2000000 lignes indexées | Secondes | Secondes |
| mysql | 367 | 249 |
| mysql_odbc | 464 | |
| db2_odbc | 1206 | |
| informix_odbc | 121126 | |
| ms-sql_odbc | 1634 | |
| oracle_odbc | 20800 | |
| solid_odbc | 877 | |
| sybase_odbc | 17614 |
| Insertion de lignes (350768 | Secondes | Secondes |
| mysql | 381 | 206 |
| mysql_odbc | 619 | |
| db2_odbc | 3460 | |
| informix_odbc | 2692 | |
| ms-sql_odbc | 4012 | |
| oracle_odbc | 11291 | |
| solid_odbc | 1801 | |
| sybase_odbc | 4802 |
Le test ci-dessus a été exécuté avec un index de cache de 8Mo.
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 qu cette politique va biaiser fortement les tests en faveur de Oracle, car les tests ci-dessus sont supposé montrer ce qu'une installation simple peut faire pour un client simple.
Pour exécuter la suite de tests, vous devez télécharger la distribution source de MySQL, installer les pilotes perl DBI et perl DBD pour les bases qui vous intéressent.
cd sql-bench perl run-all-tests --server=#
où # est un des serveurs supportés. Vous pouvez obtenir une liste de
toutes les options et des serveurs supportés avec l'option de ligne de
commande run-all-tests --help.
crash-me essaie de déterminer quelles fonctionnalités un serveur
supporte, et quelles sont ses limitations. Par exemple, le test détermine :
VARCHAR
Vous pouvez retrouver les résultats de crash-me sur de nombreuses bases de données
à http://www.mysql.com/information/crash-me.php.
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 5.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.
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 librairies. 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.
SELECTs et autres requêtesPremiè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.
Si votre problème est du à des fonctions natives de MySQL, vous pouvez toujours effectuer les tests suivants avec le client MySQL :
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.
EXPLAIN (Obtenir des informations sur les SELECT)
EXPLAIN nom_de_table
ou EXPLAIN SELECT select_options
EXPLAIN nom_de_table est un synonyme de DESCRIBE nom_de_table ou
SHOW COLUMNS FROM nom_de_table.
Lorsque vous ajoutez au début d'une commande SELECT le mot
clé EXPLAIN, MySQL va expliquer le processus qu'il va suivre
pour exécuter la commande SELECT, et donner des détails
sur la façon avec laquelle il va joindre les tables, et dans quel
ordre.
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'optimisateur. See section 4.5.2 Syntaxe de ANALYZE TABLE.
Vous pouvez aussi voir si l'optimisateur fait les jointures dans un
ordre vraiment optimal. Pour forcer l'optimisateur à 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
SELECT, le numéro séquentiel de cette commande SELECT
dans la requête.
select_type
SELECT, qui peut être :
SIMPLE
SELECT (sans UNIONs ou sous-requêtes).
PRIMARY
SELECT extérieur.
UNION
UNION SELECTs.
DEPENDENT UNION
UNION SELECTSs, dépend de la commande
extérieure.
SUBSELECT
SELECT de la sous-requête.
DEPENDENT SUBSELECT
SELECT, dépendant de la requête extérieure.
DERIVED
SELECT.
table
type
system
const.
const
const sont très rapides, car elles ne sont lues qu'une fois.
eq_ref
UNIQUE ou PRIMARY KEY.
ref
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.
range
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.
index
ALL, sauf que seul l'arbre d'index sera lu et scanné.
C'est généralement plus rapide que ALL, car le fichier d'index est généralement
plus petit que le fichier de données.
ALL
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
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 ad hoc, et examinez le résultat avec
la commande EXPLAIN. See section 6.5.4 Syntaxe de ALTER TABLE.
Pour connaître tous les index d'une table, utilisez le code SHOW INDEX FROM nom_de_table.
key
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 6.4.1 Syntaxe de SELECT.
De plus, exécuter myisamchk --analyze (see section 4.4.6.1 Syntaxe de l'utilitaire myisamchk) ou
ANALYZE TABLE (see section 4.5.2 Syntaxe de ANALYZE TABLE) sur la table va aider l'optimiseur
à choisir les index.
key_len
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
ref indique quelle colonne ou quelles constantes sont utilisées
avec la clé key, pour sélectionner les lignes de la table.
rows
rows indique le nombre de ligne que MySQL estime devoir
examiner pour exécuter la requête.
Extra
Distinct
Not exists
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: #)Using filesort
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
Using temporary
ORDER BY sur une colonne différente de celles qui font partie
de GROUP BY.
Using where
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).
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 5.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 :
| Table | Colonne | Type de colonne |
tt | ActualPC | CHAR(10)
|
tt | AssignedPC | CHAR(10)
|
tt | ClientID | CHAR(10)
|
et | EMPLOYID | CHAR(15)
|
do | CUSTNMBR | CHAR(15)
|
| Table | Index |
tt | ActualPC
|
tt | AssignedPC
|
tt | ClientID
|
et | EMPLOYID (primary key)
|
do | CUSTNMBR (primary key)
|
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,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)
Comme le type type vaut ALL pour chaque table, le résultat
indique que MySQL fait un scan complet 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,ClientID,ActualPC NULL NULL NULL 3872 Using where
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 where used
ClientID,
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 support 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 :
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
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 where used
ClientID,
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'optimisateur 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.2Mo, (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 log N ! Tant que toute 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 grosses 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 N). Pour éviter cela, augmentez le cache d'index au fur et à mesure que votre index grossit. See section 5.5.2 Réglage des paramètres du serveur.
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. See section 5.4.3 Comment MySQL utilise les 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.
See section 5.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT).
Quelques conseils généraux :
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.
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 !
WHERE
Les optimisation de la clause WHERE sont présentées avec la commande
SELECT car elles sont généralemnt 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 :
((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)
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
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.
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()...).
WHERE simplifiée est construite pour
accélérer l'évaluation de WHERE pour chaque sous-joinutre, et aussi essayer
d'ignorer les lignes le plus tôt possible.
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.
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
-> WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
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.
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.
SQL_SMALL_RESULT, MySQL va utiliser une table temporaire
en mémoire.
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,... ;
DISTINCT
DISTINCT est converti en GROUP BY sur toutes les colonnes,
DISTINCT combiné avec un ORDER BY aura dans la plupart des cas
recours à une table temporaire.
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 permière ligne de t2 est trouvée.
LEFT JOIN et RIGHT JOIN
A LEFT JOIN B est implémenté dans MySQL comme suit :
B est censée être dépendante de la
table A et de toutes les tables dont dépend A.
A est censée être dépendante de
toutes les tables (à part B) qui sont utilisées
dans la condition du LEFT JOIN.
LEFT JOIN sont transmises à la clause WHERE.
WHERE sont effectuées.
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.
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 (d.key=a.key)
WHERE b.key=d.key
MySQL fera une recherche complète sur b puisque le LEFT JOIN forcera
sa lecture avant celle de d. Un palliatif est de changer la requête en :
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
WHERE b.key=d.key
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 correpond 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) :
ORDER BY sur des clefs différentes :
SELECT * FROM t1 ORDER BY key1,key2
ORDER BY en utilisant des parties de clef non consécutives.
SELECT * FROM t1 WHERE key2=constante ORDER BY partie_clef2
ASC et DESC.
SELECT * FROM t1 ORDER BY partie_clef1 DESC,partie_clef2 ASC
ORDER BY :
SELECT * FROM t1 WHERE key2=constant ORDER BY key1
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).
ORDER BY et GROUP BY.
HASH dans les tables HEAP).
NULL et l'une d'elles
utilise ORDER BY ... DESC. Cela vient du fait que en SQL, les valeurs
NULL sont toujours triées avant les valeurs normales, que vous utilisiez
DESC ou non.
Dans les cas où MySQL doit trier les résultats, il utilisera l'algorithme suivant :
WHERE sont évitées.
sort_buffer).
MERGEBUFF (7) regions to one block in
another temporary file. Repeat until all blocks from the first file
are in the second file.
MERGEBUFF2 (15)
blocks à traiter.
record_rnd_buffer) .
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 5.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 :
sort_buffer.
record_rnd_buffer.
tmpdir pour qu'il pointe vers un disque dédié avec beaucoup d'espaces libres.
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 :
LIMIT, MySQL
va utiliser les index dans certains cas, où il aurait préféré utiliser
un scan de table complet.
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.
LIMIT # avec DISTINCT, MySQL va s'arrêter
dès qu'il a trouvé # lignes distinctes.
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.
# 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.
LIMIT # est utilisée pour calculer l'espace nécessaire.
INSERTLe 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 :
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 4.5.6.4 Syntaxe de SHOW VARIABLES.
INSERT DELAYED.
See section 6.4.3 Syntaxe de INSERT.
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.
LOAD DATA INFILE. Elle est généralement 20 fois plus rapide que l'équivalent
en commandes INSERT.
See section 6.4.9 Syntaxe de LOAD DATA INFILE.
LOAD DATA INFILE. Utilisez la procédure standard :
CREATE TABLE. Par exemple, en utilisant
mysql ou Perl-DBI.
FLUSH TABLES ou la commande en ligne shell
mysqladmin flush-tables.
myisamchk --keys-used=0 -rq /path/to/db/tbl_name. Cela va
supprimer l'utilisation des index dans la table.
LOAD DATA INFILE. Les index ne
seront pas modifiés, et donc, très rapides.
myisampack
pour la réduire de taille. See section 7.1.2.3 Caractéristiques des tables compressées.
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.
FLUSH TABLES ou utilisez la commande en ligne shell
mysqladmin flush-tables.
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.
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 insertionsSi 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 5.5.2 Réglage des paramètres du serveur.
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 4.5.1 Syntaxe de OPTIMIZE TABLE.
DELETE
Si vous voulez effacer toutes les lignes d'une table, vous devez utiliser
TRUNCATE TABLE nom_de_table. See section 6.4.7 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 5.5.2 Réglage des paramètres du serveur.
Quelques conseils en vrac pour accélérer le serveur :
thread_cache_size. See section 5.5.2 Réglage des paramètres du serveur.
EXPLAIN. See section 5.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT).
SELECT complexes sur les tables MyISAM qui
sont souvent modifiées. Cela évitera des problèmes de verrouillage.
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.
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.
SELECT * FROM table_name WHERE hash=MD5(CONCAT(col1,col2))
AND col_1='constant' AND col_2='constant'
VARCHAR et BLOB. Vous obtiendrez des lignes à
format dynamique si vous utilisez ne serait-ce qu'une seule colonne
VARCHAR ou BLOB. See section 7 Types de tables MySQL.
UPDATE table set count=count+1 where index_column=constant
est très rapide!
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.
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.
INSERT /*! LOW_PRIORITY */ lorsque vous voulez que vos sélections
soient prioritaires.
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.
INSERT multiple pour insérer plusieurs lignes
en une seule commande SQL (plusieurs serveurs SQL le supporte).
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.
AUTO_INCREMENT pour avoir des valeurs uniques.
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 4.5.1 Syntaxe de OPTIMIZE TABLE.
HEAP pour accélérer les traitements au maximum.
See section 7 Types de tables MySQL.
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.
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éan 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.
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 sont toujours
regénérables, vous ne perdrez pas de données avec DELAY_KEY_WRITE.
Vous pouvez trouver une discussion sur les différentes méthodes de verrouillage dans l'annexe. See section D.4 Méthodes de verrouillage.
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.
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;
Vous pouvez aussi utiliser les options LOW_PRIORITY de INSERT,
UPDATE ou DELETE ou HIGH_PRIORITY avec
SELECT si vous voulez donner d'autres priorités aux actions. Vous pouvez
aussi démarrer mysqld avec l'option --low-priority-updates
pour obtenir ce type de comportement.
Utiliser l'option SQL_BUFFER_RESULT peut aussi réduire la durée des
verrous de tables. See section 6.4.1 Syntaxe de SELECT.
Vous pouvez aussi changer le code de verrouillage dans le fichier `mysys/thr_lock.c', pour utiliser une queue simple. Dans ce cas, les verrous en écriture et en lecture auront la même priorité, ce qui peut aider certaines applications.
Le système de verrou de MySQL est exempt de blocage.
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 grandes
tables, le verrouillage de table est bien plus rapide que le verrouillage
de page, mais il y a aussi des inconvénients.
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.
Depuis MySQL version 3.23.7, vous pouvez insérer des lignes dans
les tables MyISAM, en même temps que d'autres threads y lisent.
Notez que, actuellement, cela ne fonctionne que si il n'y a pas
de trous après les lignes effacées dans la table, au moment de l'insertion.
Lorsque tous les trous ont été bouchés avec de nouvelles données, les insertions
simultanées seront automatiquement réactivées.
Le verrouillage de table 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 :
SELECT qui prend très longtemps.
UPDATE sur la table. Ce client va devoir
attendre que la commande SELECT soit finie.
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.
full disk, auquel cas tous les threads
qui veulent accéder à la table seront en attente de plus d'espace sur le disque.
Des solutions aux problèmes sont :
SELECT. Vous pourriez
passer par une table de sommaire pour cela.
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.
INSERT, UPDATE ou
DELETE, une priorité plus basse avec l'attribut LOW_PRIORITY.
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.
SET LOW_PRIORITY_UPDATES=1.
See section 5.5.6 Syntaxe de SET.
SELECT est très importante,
en utilisant l'attribut HIGH_PRIORITY. See section 6.4.1 Syntaxe de SELECT.
INSERT combinés avec des SELECT,
utilisez les tables MyISAM car elle supportent les commandes
SELECTs et INSERT simultanées.
INSERT et SELECT,
utilisez l'attribut DELAYED de la commande INSERT pour résoudre
ce problème.
See section 6.4.3 Syntaxe de INSERT.
SELECT et DELETE, l'option
LIMIT de DELETE peut aider. See section 6.4.6 Syntaxe de DELETE.
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 et al). 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épendamment 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 section 7 Types de tables MySQL.
Vous pouvez obtenir des gains de performances sur les tables et minimiser l'espace disque en utilisant les techniques ci-dessous :
MEDIUMINT est souvent préférable à INT.
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.
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 7.1.2 Formats de table MyISAM.
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-trees. Les chaînes sont automatiquement
préfixée et leurs espaces terminaux sont supprimés. See section 6.5.7 Syntaxe de CREATE INDEX.
Les index sont utilisés pour :
WHERE.
MAX() et MIN() pour une colonne indexée.
C'est une opération qui est optimisée par le préprocesseur, 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
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 5.2.7 Comment MySQL optimise les clauses ORDER BY.
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éfix 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 si il en reste de disponible. Voici certains de ces cas :
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.
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 section 7 Types de tables MySQL. 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:
KEY nom_de_l_index (nom_de_la_colonne(longueur))
L'exemple suivant crée un index sur les 10 premiers caractères de la colonne nom :
mysql> CREATE TABLE test (
-> nom CHAR(200) NOT NULL,
-> KEY index_du_nom (nom(10)));
Pour les colonnes BLOB et TEXT, vous devez indexer le début de la colonne.
Vous ne pouvez pas indexer une colonne entière.
Dans les versions 3.23.23 et supérieures de MySQL, vous pouvez aussi créer les index spéciaux
FULLTEXT. Ils sont utilisé pour les recherches sur les textes en entier. Seules les
tables de type MyISAM supportent les index FULLTEXT. Il peuvent être
créés depuis des colonnes CHAR, VARCHAR et TEXT. L'indexation se fait
toujours par rapport à la colonne en entier, et l'indexation partielle n'est pas supportée.
Voir section 6.8 Recherche en Texte-entier (Full-text) dans MySQL pour plus d'informations.
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.)
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, regardez section 5.4.3 Comment MySQL utilise les index.
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 5.4.7 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.16 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 :
table_cache lignes, et qu'aucun
thread n'utilise cette table.
mysqladmin refresh ou
mysqladmin flush-tables.
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 :
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 6.4.2 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 4.5.6.3 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. Toute fois, 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 plate-formes Linux 32 bits limitent par défaut la taille des fichiers à 2 Go. Heureusement, cela sera bientot réparé avec l'arrivée des nouveaux systèmes de fichier (XFS/Reiserfs). Si vous souhaitez désespéremment utiliser des fichiers de plus de 2 Go sur Linux-intel 32bits, 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 plate-formes, nous vous conseillons de tester votre plate-forme avant de la choisir définitivement.
--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 à arreter le serveur (ou bien de verrouiller
les parties pertinantes) 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 plate-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 servers (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.
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 va