MySQL Reference Manual :: 7.2.17 Autres conseils d'optimisation

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

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

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

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

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

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

SELECT * FROM tbl_name
    WHERE hash_col=MD5(CONCAT(col1,col2))
    AND col1='constant' AND col2='constant';

Pour les tables qui sont souvent modifiées, vous devriez essayer d'éviter les colonnes VARCHAR et BLOB. Vous obtiendrez des lignes à format dynamique si vous utilisez ne serait-ce qu'une seule colonne VARCHAR ou BLOB. See Chapitre 15, 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 suffisamment pour la plus par des applications. Le seul cas où cela peut être important est si vous êtes capables de dégager une table à format de ligne fixe (voir ci-dessus), ou si vous avez besoin de scanner régulièrement la table, mais que vous n'avez pas besoin de toutes les colonnes. See Chapitre 15, 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 présenté ci-dessous est très rapide!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Utilisez les colonnes AUTO_INCREMENT pour avoir des valeurs uniques.

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

Utilisez la tables de type HEAP pour accélérer les traitements au maximum. See Chapitre 15, 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éant des commandes MyISAM de bas niveau dans le serveur MySQL (cela peut se faire facilement pour améliorer les performances). Soyez très prudent dans la conception de votre interface, mais il est très facile de supporter ce type d'optimisation.

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

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

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