MySQL/Optimisation
Optimiser le serveur MySQL.
Avant de démarrer l'optimisation.
Quand la base est anormalement lente, vérifier les points suivants : 
Des outils pour unixeries existent pour trouver les goulots d'étranglement :
On peut ensuite classer les applications en trois groupes par leurs nécessités :
Variables de statut et serveur.
MySQL peut être monitoré et personnalisé en surveillant les variables de statut, et définissant les variables de serveur qui peuvent être globales ou par session.
Les variables de statut peuvent être monitorées par "SHOW [GLOBAL|SESSION] STATUS [LIKE '%foo%']" ou "mysqladmin [extended-]status".
Les variables de serveur peuvent être définies dans "/etc/mysql/my.cnf" ou via "SET [GLOBAL|SESSION] VARIABLE foo := bar", et affichées avec "mysqladmin variables" ou "SHOW [GLOBAL|SESSION] VARIABLES [LIKE '%foo%']".
Généralement, les variables de statut commencent par une majuscule, et pas les variables de serveur.
Pour gérer les quotas de ces variables, il faut les multiplier par la valeur de "max_connections" pour avoir une estimation de la mémoire maximum utilisée. Cela permettra d'éviter des crashs lors des pics de connexions. Exemple :
 global_buffers:
 key_buffer
 innodb_buffer_pool
 innodb_log_buffer
 innodb_additional_mem_pool
 net_buffer
 thread_buffers:
 sort_buffer
 myisam_sort_buffer
 read_buffer
 join_buffer
 read_rnd_buffer
Expérience.
Lançons le programme Perl suivant :
Simule une entrée de données en quantité industrielle à intervalle régulier.
 mysql> CREATE DATABASE industrial
 $ perl generate_huge_db.pl | mysql industrial
 real 6m21.042s
 user 0m37.282s
 sys 0m51.467s
Pour vérifier le nombre d'éléments :
 mysql> SELECT COUNT(*) FROM weightin;
 | count(*) |
 | 2000000 | 
 1 row in set (0.00 sec)
La taille doit être importante :
 $ perl generate_huge_db.pl > import.sql
 $ ls -lh import.sql
 -rw-r--r-- 1 root root 189M jun 15 22:08 import.sql
 $ ls -lh /var/lib/mysql/industrial/weightin.MYD
 -rw-rw---- 1 mysql mysql 35M jun 15 22:17 /var/lib/mysql/industrial/weightin.MYD
 $ time mysqldump industrial > dump.sql 
 real 0m9.599s
 user 0m3.792s
 sys 0m0.616s
 $ ls -lh dump.sql
 -rw-r--r-- 1 root root 79M jun 15 22:18 dump.sql
 $ time mysqldump industrial | gzip > dump.sql.gz 
 real 0m17.339s
 user 0m11.897s
 sys 0m0.488s
 $ ls -lh dump.sql.gz 
 -rw-r--r-- 1 root root 22M jun 15 22:19 dump.sql.gz
Incidemment, restaurer d'un dump est plus rapide car il y a moins d'insertions :
 # time zcat dump.sql.gz | mysql industrial
 real 0m31.772s
 user 0m3.436s
 sys 0m0.580s
La commande SQL scanne tous les enregistrements pour obtenir une somme :
 mysql> SELECT SUM(*) FROM weightin;
Par exemple, pour compter le matériel depuis le premier janvier 2008 :
 mysql> SELECT COUNT(*), SUM(poids) FROM pesee WHERE date >= '2008-01-01' AND date < '2008-01-02';
MySQL a besoin de lire toute la base même pour un petit nombre d'enregistrement, car rien ne garantit qu'ils soient classés. Pour améliorer ceci, on peut faire de la date un index. MySQL va donc créer une nouvelle table cachée avec les dates classées dans l'ordre, et stocker leur position dans la table 'weightin' afin de pouvoir faire le lien avec. Comme l'index est ordonné, MySQL peut plus rapidement localiser un enregistrement (ex : par dichotomie) plutôt que lors d'une lecture séquentielle.
Ajout de l'index :
 ALTER TABLE weightin ADD INDEX (date);
On remarque que le fichier .MYD a grossi :
 $ ls -lh /var/lib/mysql/industrial/
 -rw-rw---- 1 mysql mysql 49M jun 15 22:36 weightin.MYI
C'est parce qu'il est utilisé pour stocker les index, par défaut toutes les clés primaires.
On constate aussi que l'ordre naturel des résultats change après ajout de l'index. Dans cet exemple, codice_6 renverra les valeurs de "date" les plus élevées à la fin, puis utilisera les ID comme clé de tri secondaire.
Autre exemple.
Tentons d'optimiser la requête :
 mysql> SELECT DISTINCT line FROM weightin;
Il suffit de faire de 'line' un index, afin qu'il puisse éviter les doublons regroupés ensemble, au lieu de rescanner toute la table pour les localiser :
 ALTER TABLE weightin ADD INDEX (line);
Taille du fichier :
 -rw-rw---- 1 mysql mysql 65M jun 15 22:38 weightin.MYI
On constate que l'ordre naturel des résultats change après ajout de l'index. Dans cet exemple, codice_6 renverra les valeurs de "line" les plus élevées à la fin, puis utilisera les ID comme clé de tri secondaire.
Considérations générales.
La première question pour optimiser les sélections est toujours de savoir si les index sont configurés, et si oui s'ils sont utilisés.
Les requêtes individuelles peuvent être détaillées par codice_8. Pour tout le serveur les variables "Sort_%" peuvent être surveillés pour indiquer combien de fois MySQL doit aller les chercher dans le fichier de données en l'absence d'index.
Garder les index en mémoire vive augmente les performances de lecture. Le quotient des clés lues sur les requêtes de lecture de clés reflète les réels accès de MySQL au fichier d'index sur le disque quand il nécessitait une clé. 
Idem avec les clés écrites, utiliser codice_3 pour faire le calcul. Si le pourcentage est trop haut, key_buffer_size pour MyISAM et innodb_buffer_pool_size pour InnoDB sont les variables à régler.
Les variables Key_blocks_% peuvent être utilisées pour voir combien les clés tampons sont réellement utilisées. Une unité correspond à 1 ko, sauf si key_cache_block_size a été modifié. Comme MySQL utilise les blocs internes, key_blocks_unused doit être vérifié. Pour estimer la taille du tampon à définir, celle des fichiers .MYI doit être vérifiée. Pour InnoDB il y a innodb_buffer_pool_size qui concerne tous les types de données en tampon (pas seulement les index).
sort_buffer_size (par thread) est la mémoire utilisée pour codice_10 et codice_11. Il est déconseillé par contre d'utiliser myisam_sort_buffer_size.
read_buffer_size (par thread) est la taille de mémoire allouée pour les scans complets de table (comme les tables volumineuses ne tiennent pas complètement en mémoire).
Query cache.
La principale raison de ne pas rétrograder vers des versions antérieures à MySQL 4.0.1, est la faculté de stocker les requêtes codice_12 jusqu'à ce que les tables soient modifiées.
Le "Query Cache" peut être configuré au travers des variables query_cache_%. La plus importante est la globale query_cache_size et query_cache_limit qui préviennent les requêtes uniques à résultats anormalement plus larges que la taille du cache.
Les blocs Query Cache ont une taille variable dont le minimum est défini par query_cache_min_res_unit, donc après reset du cache le nombre de bloc libre doit être idéalement de un. Une large valeur de Qcache_free_blocks engendrerait de la fragmentation.
Voir aussi les variables :
Exemple d'un cache vide :
 mysql> SHOW VARIABLES LIKE 'query_cache_type';
 | Variable_name | Value |
 | query_cache_type | ON |
 1 row in set (0.00 sec)
 mysql> SHOW VARIABLES LIKE 'query_cache_size';
 | Variable_name | Value |
 | query_cache_size | 0 |
 1 row in set (0.00 sec)
 mysql> SHOW STATUS LIKE 'Qcache%';
 | Variable_name | Value |
 | Qcache_free_blocks | 0 |
 | Qcache_free_memory | 0 |
 | Qcache_hits | 0 |
 | Qcache_inserts | 0 |
 | Qcache_lowmem_prunes | 0 |
 | Qcache_not_cached | 0 |
 | Qcache_queries_in_cache | 0 |
 | Qcache_total_blocks | 0 |
 8 rows in set (0.00 sec)
Cache utilisé (savannah.gnu.org) :
 mysql> SHOW VARIABLES LIKE "query_cache_size";
 | Variable_name | Value |
 | query_cache_size | 33554432 |
 1 row in set (0.00 sec)
 mysql> SHOW STATUS LIKE "Qcache%";
 | Variable_name | Value |
 | Qcache_free_blocks | 1409 |
 | Qcache_free_memory | 27629552 |
 | Qcache_hits | 7925191 |
 | Qcache_inserts | 3400435 |
 | Qcache_lowmem_prunes | 2946778 |
 | Qcache_not_cached | 71255 |
 | Qcache_queries_in_cache | 4546 |
 | Qcache_total_blocks | 10575 |
 8 rows in set (0.00 sec)
Le paramètre de codice_14 correspondant est :
 query_cache_size = 32M
Pour nettoyer le cache afin d'améliorer les performances :
 mysql> RESET QUERY CACHE;
 Query OK, 0 rows affected (0.00 sec)
Pour conserver les requêtes en cache en les défragmentant :
 FLUSH QUERY CACHE
Attendre les locks.
Les variables Table_locks_% affichent le nombre de requêtes en attente faute de pouvoir accéder aux tables actuellement verrouillées par d'autres requêtes. Ces situations peuvent être causées par la commande codice_15 ou encore par plusieurs accès en écriture simultanés sur la même table.
Cache des tables.
MySQL a besoin d'un certain temps pour ouvrir une table et lire ses métadonnées comme les noms de colonnes.
Si plusieurs threads tentent d'accéder à la même table, elle est ouverte plusieurs fois.
Pour accélérer ceci la métadonnée peut être stockée dans le table_cache (alias table_open_cache depuis MySQL 5.1.3). 
Une bonne valeur est le nombre max_connections multiplié par le nombre de tables moyen par sélection.
Utiliser mysqlreport ou regarder les Open_tables après que les Opened_tables ou le Uptime nombre de tables ouvertes par seconde peut être calculé (hors des heures de pointe comme la nuit).
Connexions et threads.
Pour chaque connexion de client, MySQL crée un thread séparé sous le processus principal mysqld. Pour les grands sites à plusieurs centaines de connexions par semaine, créer les threads eux-mêmes peut consommer un temps non négligeable. Pour l'accélérer, les threads en attente sont mis en cache après déconnexion de leur client. En règle générale, moins d'un thread par seconde peut être créé ensuite.
Les clients qui envoient plusieurs requêtes au serveur doit utiliser les connexions persistantes comme avec la fonction PHP codice_16.
Ce cache peut être configuré par thread_cache_size et monitoré avec les variables threads_%.
Pour éviter les surcharges MySQL bloque les nouvelles connexions si plus que max_connections sont utilisé à cet instant. Commencer par max_used_connections et surveiller le nombre de connexion rejetées (Aborted_clients) et celle "time out" (Aborted_connections).
Ne pas déconnecter les clients aux connexions persistantes peut rapidement provoquer un déni de service. Les connexions normales sont fermées après le wait_timeout d'inactivité en seconde.
Tables temporaires.
Il est parfaitement normal que MySQL crée des tables temporaires pendant les classements ou les résultats de regroupement. Ces tables sont soit en mémoire, soit trop larges et sont écrites sur le disque (plus lent).
Le nombre de tables sur le disque (variables Created_tmp_%) doit être négligeable ou la configuration de max_heap_table_size et tmp_table_size doit être reconsidérée.
Écritures différées.
Pour rédiger les logs d'accès au serveur web dans une base, avec de nombreux codice_17 subséquents dans la même table, les performances peuvent être améliorées en conseillant au serveur de mettre en cache les requêtes d'écriture un court moment, puis de tout envoyer comme batch sur le disque.
Attention au fait que toutes les méthodes mentionnées ne contreviennent pas à la recommandation car les insertions sont reconnus avec OK au client avant leur écriture définitive sur le disque, et donc des données pourraient être perdues en cas de crash. 
INSERT DELAYED fonctionne sur les principaux moteurs de stockage.
Optimiser les tables.
Index.
Il convient d'utiliser le commande suivante régulièrement pour réorganiser les enregistrements sur le disque dur, afin de réduire la taille de la table (sans rien effacer) et d'accélérer les lectures par index (grâce aux enregistrements contigus nécessitant moins de déplacement des têtes de lecture des disques durs) :
 OPTIMIZE TABLE MaTable1
De plus, au moment de leurs créations, les types les plus petits possibles sont souhaités. Par exemple :
Dupliquer une partie en lecture seule.
Quand les anciens enregistrements ne sont plus utiles mais qu'on ne veut pas les archiver, on peut créer une table temporaire (mais pas "temporary table") à partir d'une requête. Les performances de cette petite table seront alors bien meilleures.
Optimiser les requêtes.
Principes.
D'une manière générale, il est préférable d'effectuer par priorité décroissante dans le langage de requête : 
De même, si l'ordre des conditions dans le codice_23 ne modifie jamais le résultat obtenu, il peut en revanche avoir un impact important sur les performances. En effet, il est plus performant de :
kill.
Les requêtes en cours sont visibles avec :
 show full processlist;
Pour forcer l'arrêt de celles qui durent depuis plus d'une heure (3600 secondes), copier le résultat de la requête suivante pour l'exécuter dans un deuxième temps :
select concat('kill ', p.id, ';')
from information_schema.processlist p
where TIME > 3600
Si les requêtes restent en statut "Killed" au lieu de disparaitre de la liste des processus, c'est que le SGBD rollback. Si c'est trop long, on peut alors redémarrer le daemon du serveur.
Comparer les fonctions avec BENCHMARK.
BENCHMARK() permet de mesurer les rapidités des fonctions ou opérateurs MySQL :
 mysql> SELECT BENCHMARK(100000000, CONCAT('a','b'));
 | BENCHMARK(100000000, CONCAT('a','b')) |
 | 0 |
 1 row in set (21.30 sec)
Toutefois, on ne peut pas comparer des requêtes avec :
 mysql> SELECT BENCHMARK(100, SELECT `id` FROM `lignes`);
 ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for
 the right syntax to use near 'SELECT `id` FROM `lignes`)' at line 1
En effet, sachant que MySQL doit parser la requête, on peut considérer que les benchmarks inférieurs à 10 s ne sont pas exploitables.
Analyse des fonctions avec EXPLAIN.
En ajoutant codice_8 devant codice_12, MySQL détaille les différentes opérations qu'il effectue dans le cadre de cette sélection (comment les tables sont jointes et dans quel ordre). Cela permet de placer d'éventuels hints en fonction.
Exemple.
Jointure de deux tables sans indice :
 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
 | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 3 | |
 2 rows in set (0.01 sec)
Maintenant on ajoute un index sur la seconde table, ce qui fait descendre ensuite lors de la même sélection, la colonne "rows" de la seconde ligne : MySQL a donc effectué une lecture de moins pour le même résultat.
 mysql> ALTER TABLE b ADD KEY(i);
 Query OK, 3 rows affected (0.01 sec)
 Records: 3 Duplicates: 0 Warnings: 0
 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
 | 1 | SIMPLE | b | ref | i | i | 5 | test.a.i | 2 | |
 2 rows in set (0.00 sec)
Enfin, en ajoutant un index sur la première table, la condition codice_23 est améliorée car MySQL sait qu'il ne lui faut qu'une ligne de la première table (au lieu de quatre) :
 mysql> ALTER TABLE a ADD KEY(i);
 Query OK, 4 rows affected (0.00 sec)
 Records: 4 Duplicates: 0 Warnings: 0
 mysql> explain SELECT * FROM a left join b using (i) WHERE a.i < 2;
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 | 1 | SIMPLE | a | range | i | i | 5 | NULL | 1 | Using where |
 | 1 | SIMPLE | b | ref | i | i | 5 | test.a.i | 2 | |
 2 rows in set (0.02 sec)
Hints d'optimisation.
L'ordre des mots réservés est important si on applique plusieurs :
 SELECT [ALL | DISTINCT | DISTINCTROW ]
 [HIGH_PRIORITY] [STRAIGHT_JOIN]
 [SQL_SMALL_RESULT | SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
 [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
HIGH_PRIORITY.
Généralement les commandes LMD (codice_33) sont prioritaires sur le codice_12. Mais grâce à codice_35 un codice_12 peut être traité avec elles.
STRAIGHT_JOIN.
Force MySQL à évaluer les tables d'un codice_37 dans l'ordre où elles sont nommées (de gauche à droite).
SQL_SMALL_RESULT.
Lors d'un codice_38 ou d'un codice_11, ce hint prévient l'optimiseur que la requête va renvoyer un petit nombre de lignes.
SQL_BIG_RESULT.
Lors d'un codice_38 ou d'un codice_11, dit à l'optimiseur que la requête renvoie un nombre élevé de résultats.
SQL_BUFFER_RESULT.
Force MySQL à copier le résultat dans une table temporaire. Cela peut s'avérer utile par exemple pour supprimer des codice_42 rapidement.
SQL_CACHE.
Force MySQL à copier le résultat dans le cache. Ne fonctionne que si la valeur de codice_43 est codice_44 ou 2.
SQL_NO_CACHE.
Demande à MySQL de ne pas mettre le résultat en cache. C'est utile quand la requête survient très rarement, ou que le résultat change très souvent.
SQL_CALC_FOUND_ROWS.
Si une requête contient codice_45, ce hint dit au serveur de calculer combien de lignes auraient été retournées en cas d'absence de codice_45. Pour récupérer le nombre il faut sélectionner codice_47.
 SELECT SQL_CALC_FOUND_ROWS * FROM `wiki1_page` LIMIT 2 OFFSET 100;
 SELECT FOUND_ROWS();
Hints sur les index.
Afin d'influer sur les déroulements vu précédemment, on peut utiliser les suivants :
Exemples :
-- Exemple 1
SELECT *
FROM table1 USE INDEX (date)
WHERE date BETWEEN '20150101' AND '20150131'
-- Exemple 2
SELECT *
FROM table1 IGNORE INDEX (date)
WHERE id BETWEEN 100 AND 200
Pour appliquer ces règles lors d'une jointure, d'un tri ou d'un regroupement, il suffit d'ajouter utiliser codice_51, codice_52 ou codice_53.
SELECT *
FROM table1 t1
JOIN table2 t2 FORCE INDEX ON JOIN (t1_id) ON t1.id = t2.t1_id
WHERE t1.id BETWEEN 100 AND 200