5.2 Optimisation des SELECT s et autres requêtes
5 Optimisation de MySQL
Manuel de Référence MySQL 4.1 : Version Française
. Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT ) . Mesurer les performances d'une requête . Vitesse des requêtes SELECT . Comment MySQL optimise les clauses WHERE . Comment MySQL optimise la clause DISTINCT . Comment MySQL optimise LEFT JOIN et RIGHT JOIN . Comment MySQL optimise les clauses ORDER BY . Comment MySQL optimise la clause LIMIT . Vitesse des requêtes INSERT . Vitesses des commandes UPDATE . Rapidité des requêtes DELETE ->Autres conseils d'optimisation
|
5.2.12 Autres conseils d'optimisation
Quelques conseils en vrac pour accélérer le serveur :
-
Utilisez les connexions persistantes à la base, pour éviter les coûts récurents
de connexion. Si vous ne pouvez pas utiliser de connexions persistantes, et que
vous faites de nombreuses connexions à la base, essayez de modifier la valeur
de la variable
thread_cache_size
. Choix des paramètres du serveur .
-
Vérifiez toujours que vos requêtes utilisent vraiment les index que vous
avez créé dans les tables. Avec MySQL, vous pouvez utiliser la commande
EXPLAIN
. Syntaxe de
EXPLAIN
(Obtenir des informations
SELECT
) .
-
Essayez d'éviter les requêtes
SELECT
complexes sur les tables
MyISAM
qui
sont souvent modifiées. Cela évitera des problèmes de verrouillage.
-
Les nouvelles tables
MyISAM
peuvent insérer des lignes sans en effacer
d'autre, tout en lisant dans cette table. Si c'est important pour vous, vous
pouvez considérer d'autres méthodes où vous n'avez pas à effacer de lignes,
ou bien utilisez
OPTIMIZE TABLE
après avoir effacé beaucoup de lignes.
-
Utilisez
ALTER TABLE ... ORDER BY expr1,expr2...
si vous lisez les colonnes
dans l'ordre
expr1,expr2...
. Avec cette option, après de grosses modifications
dans la table, vous pourriez obtenir de meilleures performances.
-
Dans certains cas, cela vaut la peine d'ajouter une colonne qui est une
combinaison ('hashed') des informations des autres colonnes. Si cette colonne
est courte, et plutôt exemptes de doublons, elle peut se révéler plus rapide
qu'un gros index sur plusieurs colonnes. Avec MySQL, il est très facile d'utiliser
une telle colonne :
SELECT * FROM table_name WHERE hash=MD5(CONCAT(col1,col2))
AND col_1='constant' AND col_2='constant'
-
Pour les tables qui sont souvent modifiées, vous devriez essayer d'éviter
les colonnes
VARCHAR
et
BLOB
. Vous obtiendrez des lignes à
format dynamique si vous utilisez ne serait-ce qu'une seule colonne
VARCHAR
ou
BLOB
. Types de tables MySQL .
-
Normalement, cela ne sert à rien de séparer une table en différentes tables
plus petites, juste parce que vos lignes deviennent 'grosses'. Pour accéder
à une ligne, le plus long est le temps d'accès au premier octets de la ligne.
Après cela, les disques modernes vont lire très rapidement la ligne, et suffisament
pour la plus par des applications. Le seul cas où cela peut être important
est si vous êtes capables de dégager une table à format de ligne fixe
(voir ci-dessus), ou si vous avez besoin de scanner régulièrement la table,
mais que vous n'avez pas besoin de toutes les colonnes.
Types de tables MySQL .
-
Si vous avez besoin de calculer souvent des expressions en fonction des
informations placées dans de nombreuses lignes (comme compter des lignes),
il est probablement plus efficace d'introduire une nouvelle table qui va
mettre à jour ce compteur en temps réel. Une modification du type
type
UPDATE table set count=count+1 where index_column=constant
est très rapide!C'est très important lorque vous utilisez les types de tables MyISAM et
ISAM, qui ne dispose que d'un verrouillage de table (plusieurs lecteurs,
un seul qui écrit). Cela va aussi améliorer les performances avec la plus
par des bases, car le gestionnaire de verrouillage de ligne aura moins
de tâches à faire.
-
Si vous devez rassembler des statistiques issues de grosses tables de log,
utiliser les tables de sommaires plutôt que la table complète. Entretenir un
sommaire est bien plus rapide que de regénérer des tables à partir des logs
à chaque modification (suivant la criticité de vos informations), plutôt que
de modifier l'application qui fonctionne.
-
Si possible, essayez de marquer les rapports comme 'direct' ou 'statistique',
où les données nécessaires pour les rapports statistiques ne sont générées
qu'à partir de tables de sommaires, calculées depuis les données réelles.
-
Utilisez les valeurs par défaut des colonnes. N'insérez des valeurs explicitement
que lorsque la valeur diffère de la valeur par défaut. Cela réduit le temps
d'analyse de MySQL, et améliore les insertions.
-
Dans certains cas, il est pratique de compacter et stocker les données dans
un blob. Dans ce cas, vous devez ajouter du code supplémentaire pour
compacter et décompacter les données dans le blob, mais cela pourra vous
faire économiser de nombreux accès. C'est pratique lorsque vous avez des données
qui ne peuvent s'adapter facilement à une structure de base de données.
-
Normalement, vous devriez essayer de garder vos données non redondantes
(ce qui s'appelle la troisième forme normale dans les théories de bases
de données), mais ne vous empêchez pas de duplique des données ou de créer
des tables de sommaire, pour gagner de la vitesse.
-
Les procédures stockées ou UDF (fonctions utilisateur) peuvent être une bonne
façon de gagner en performance. Dans ce cas, vous devriez avoir une méthode
pour appliquer les mêmes fonctions d'une autre manière, si votre base
ne supporte les Les procédures stockées.
-
Vous pouvez aussi gagner de la vitesse en utilisant des caches de requêtes
dans vos applications, et en essayant de rassembler les nombreuses insertions ou
modifications. Si votre base de données supporte le verrouillage de
table (comme MySQL et Oracle), cela vous aidera à vous assurer que le cache
d'index est vidé après chaque modifications.
-
Utilisez
INSERT /*! DELAYED */
lorsque vous n'avez pas besoin d'être
assuré que vos données sont écrites. Cela accélère les insertions, car
de nombreuses lignes seront écrites en une seule fois.
-
Utilisez
INSERT /*! LOW_PRIORITY */
lorsque vous voulez que vos sélections
soient prioritaires.
-
Utilisez
SELECT /*! HIGH_PRIORITY */
pour rendre les sélections
prioritaires. C'est à dire, les sélections seront désormais faites même
si un autre programme attend pour écrire.
-
Utilise la commande
INSERT
multiple pour insérer plusieurs lignes
en une seule commande SQL (plusieurs serveurs SQL le supporte).
-
Utilisez
LOAD DATA INFILE
pour charger de grande quantité de données dans
une table. C'est généralement plus rapide que des insertions, et sera même
encore plus rapide une fois que
myisamchk
sera intégré dans
mysqld
.
-
Utilisez les colonnes
AUTO_INCREMENT
pour avoir des valeurs uniques.
-
Utilisez
OPTIMIZE TABLE
une fois de temps en temps, pour éviter la fragmentation
lors de l'utilisation de tables avec un format de ligne dynamique.
Syntaxe de
OPTIMIZE TABLE
.
-
Utilisez la tables de type
HEAP
pour accélérer les traitements au maximum.
Types de tables MySQL .
-
Avec un serveur web normal, les images doivent être stockées dans des fichiers.
C'est à dire, ne stockez qu'une référence au fichier d'image dans la base.
La raison principale à cela est qu'un serveur web est bien meilleur
pour mettre en cache des fichiers que le contenu d'une base de données.
Il est donc plus rapide si vous utilisez des fichiers.
-
Utilisez des tables en mémoire pour les données non critiques, qui ont
besoin d'être lues souvent (comme des informations sur la dernière bannière
affichée pour les utilisateurs sans cookies).
-
Les colonnes contenant des informations identiques dans différentes
tables doivent être déclarées identiquement lors de la création des tables,
et porter des noms identiques. Avant la version 3.23, vous pouviez
ralentir les jointures.Essayez de garder des noms simples (utilisez
nom
au lieu de
nom_du_client
dans la table de clients). Pour rendre vos noms de colonnes
portables vers les autres serveurs SQL, vous devriez essayer de les garder
plus petits que 18 caractères.
-
Si vous avez vraiment besoin de très haute vitesse, vous devriez considérer
les interfaces de bas niveau pour le stockage des données que les différents
serveurs SQL supportent. Par exemple, en accédant directement aux tables
MySQL
MyISAM
, vous pourriez obtenir un gain de vitesse de l'ordre de
2 à 5 fois, en comparaison avec l'interface SQL. Pour cela, les données doivent
être sur le même serveur que l'application, et généralement, elles ne doivent
être manipulées que par un seul programme à la fois (car le verrouillage
externe de fichiers est très lent). Vous pouvez éliminer ces problèmes en
cré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.
-
Dans de nombreux cas, il est plus rapide d'accéder aux données depuis une
base (en utilisant une connexion ouverte) que d'accéder à un fichier texte,
car la base de données est plus compacte que le fichier texte (si vous utilisez
des données numériques), et cela entraine moins d'accès disques. Vous allez
aussi économiser du code, car vous n'aurez pas à analyser le fichier texte
pour repérer les limites de lignes.
-
Vous pouvez aussi utiliser la réplication pour accélérer le serveur.
Réplication avec MySQL .
-
Déclarer une table avec
DELAY_KEY_WRITE=1
va accélérer la mise à jour
des index, car ils ne seront pas écrit sur le disque jusqu'à ce que le fichier
de données soit refermé. L'inconvénient est que vous devez exécuter l'utilitaire
myisamchk
sur ces tables avant de lancer
mysqld
pour vous assurer
que les index sont bien à jour, au cas où le processus aurait été interrompu
avant d'enregistrer les données. Comme les informations d'index sont toujours
regénérables, vous ne perdrez pas de données avec
DELAY_KEY_WRITE
.
|