MySQL/Optimisation

MySQL

1. Introduction
2. PhpMyAdmin
3. Parcourir les bases de données
4. Spécifier les noms
5. LDD, LMD et LCD
6. Variables utilisateurs
7. Alias
8. Types de données
9. Manipulation de base
   • mysqldump
10. Manipulation de table
11. Manipulation de données
12. Requêtes
13. NULL
14. Opérateurs
15. Fonctions
16. Procédures stockées
17. Regex
18. Bases de données spatiales
19. Importer et exporter
20. Réplication
21. Optimisation
22. API
23. Sécurité
24. Mots réservés
25. Débogage

Optimiser le serveur MySQL

Avant de démarrer l'optimisation

Quand la base est anormalement lente, vérifier les points suivants :

1. Trouver les goulots d'étranglements (processeur, RAM, I/O, requêtes)
2. Chercher à complexifier les requêtes gourmandes. Généralement on effectue les opérations suivantes dans cet ordre pour éliminer un maximum de données inutiles des résultats rapidement : projection, sélection, jointure.
3. Optimiser l'application en retirant les requêtes ou cache PHP des pages web.
4. Optimiser les requêtes (ajouter des index, des tables temporaires ou changer de jointure).
5. Optimiser la base du serveur (taille du cache, etc.).
6. Optimiser le système (les différents systèmes de fichier, le swap (mémoire virtuelle) et les versions du noyau).
7. Optimiser le hardware.

Des outils pour unixeries existent pour trouver les goulots d'étranglement :

vmstat

monitore les utilisations du processeur, de la RAM et des I/O en les classant.

mytop

trouve les requêtes lourdes.

mysqlreport

checklist pas à pas (nécessite Perl et son module DBD-MySQL installés).

MySQL Workbench

anciennement mysql admin(istrator), monitore et personnalise MySQL de façon convenable.

On peut ensuite classer les applications en trois groupes par leurs nécessités :

• I/O et lecture (blogs, news).
• I/O et écriture (traqueur de connexion web, collection de données de compte).
• CPU (CMS, logiciel de business).

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 :

min_memory_needed = global_buffers + (thread_buffers * max_connections)

   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

 Lors des tests, désactiver le cache (query_cache_type=0 dans my.cnf) pour forcer un recalcul systématique des requêtes.

Lançons le programme Perl suivant :

#!/usr/bin/perl

use strict;

print "DROP TABLE IF EXISTS weightin;\n";
print "CREATE TABLE weightin (
	id INT PRIMARY KEY auto_increment,
	line TINYINT,
	date DATETIME,
	weight FLOAT(8,3)
);\n";

# 2 millions records, interval = 100s
for (my $timestamp = 1000000000; $timestamp < 1200000000; $timestamp += 100) {
    my $date = int($timestamp + rand(1000) - 500);
    my $weight = rand(1000);
    my $line = int(rand(3)) + 1;
    print "INSERT INTO weightin (date, line, weight) VALUES (FROM_UNIXTIME($date), $line, $weight);\n";
}

Rôle

Simule une entrée de données en quantité industrielle à intervalle régulier.

Utilisation

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, SELECT * FROM weightin 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, SELECT * FROM weightin renverra les valeurs de "line" les plus élevées à la fin, puis utilisera les ID comme clé de tri secondaire.

 il existe aussi la forme CREATE INDEX my_index ON weightin (line);, qui a l'avantage de baptiser l'index pour pouvoir le supprimer par son nom ultérieurement.

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.

1. Vérifier si les index sont utilisés

Les requêtes individuelles peuvent être détaillées par EXPLAIN. 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.

2. Est-ce que les index sont stockés dans un tampon

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 mysqlreport 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).

3. Configuration avancée

sort_buffer_size (par thread) est la mémoire utilisée pour ORDER BY et GROUP BY. 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 SELECT 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 :

• Qcache_free_blocks : si la valeur est haute, cela indique une forte fragmentation.
• Qcache_not_cached : si la valeur est haute, il y a soit plus de requête hors du cache (par exemple parce qu'ils utilisent des fonctions comme now()) soit la valeur de query_cache_limit est trop basse.
• Qcache_lowmem_prunes : montant des anciens résultats purgés car le cache était plein, et les tables modifiées. query_cache_size doit être augmenté pour abaisser cette variable.

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 my.cnf 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 LOCK TABLE 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 mysql_pconnect().

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 INSERT 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 ACID 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.

• Pour les tables MyISAM, les écritures différées peuvent être définies avec DELAY_KEY_WRITE dans un CREATE ou un ALTER TABLE. L'inconvénient est qu'après un crash la table est automatiquement marquée comme corrompue et doit être vérifiée voire réparée ce qui prend un certain temps.
• Pour InnoDB, c'est innodb_flush_log_at_trx_commit. En cas de crash seuls les index sont reconstruits.

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 :

• si un nombre est toujours positif, choisir un type unsigned afin de pouvoir en stocker deux fois plus dans le même nombre d'octets.
• pour stocker des dates contemporaines (de 1970 à 2038) mieux vaut prendre un timestamp sur quatre octets qu'un datetime sur 8.

Pour utiliser au mieux un index, la valeur de la requête ne doit pas être convertie (ex : si l'index porte une un type entier, ne pas chercher where champ = '2' mais where champ = 2).

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 :

• Les sélections, afin de réduire le plus grand nombre de données en mémoire. Dans la mesure du possible il faut éviter les wildcards (*) qui engendrent plus de transfert d'information en réseau (ex : ID ou dates de mises à jour inutiles).
• Les projections, toujours pour diminuer la taille des données.
• Les tris, pour accélérer les jointures.
• Les jointures. Les différents plans d'exécution examinés sont constitués des différents chemins d'accès (ex : accès aux index primaires et secondaires) et de la variété des techniques de jointure selon les hints :
  1. tri fusion (merge join)
  2. hashage (hash join)
  3. boucle imbriquée (nested loop join)
  4. produit (product join).

De même, si l'ordre des conditions dans le WHERE 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 :

• Placer les conditions qui filtrent le plus d'enregistrements avant les autres (cela nécessite en général de connaitre la taille courante des tables).
• Vérifier l'emploi du mot clé BETWEEN, qui peut consommer plus de ressources en allant chercher des octets fragmentés sur le disque, qu'un parcours séquentiel de table.
• S'assurer que les LIKE ne sont pas remplaçables par des =.
• S'assurer que les CURSOR (en) ne sont pas remplaçables.
• Déplacer des conditions du WHERE ou du GROUP BY dans le JOIN.
• Remplacer les SUM(ROUND()) par des ROUND(SUM()).

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 EXPLAIN devant SELECT, 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 WHERE 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 hints :

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 (INSERT, DELETE, UPDATE) sont prioritaires sur le SELECT. Mais grâce à HIGH_PRIORITY un SELECT peut être traité avec elles.

STRAIGHT_JOIN

Force MySQL à évaluer les tables d'un JOIN dans l'ordre où elles sont nommées (de gauche à droite).

SQL_SMALL_RESULT

Lors d'un DISTINCT ou d'un GROUP BY, ce hint prévient l'optimiseur que la requête va renvoyer un petit nombre de lignes.

SQL_BIG_RESULT

Lors d'un DISTINCT ou d'un GROUP BY, 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 LOCK rapidement.

SQL_CACHE

Force MySQL à copier le résultat dans le cache. Ne fonctionne que si la valeur de query_cache_type est DEMAND 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 LIMIT, ce hint dit au serveur de calculer combien de lignes auraient été retournées en cas d'absence de LIMIT. Pour récupérer le nombre il faut sélectionner FOUND_ROWS().

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

• USE INDEX : spécifie de rechercher des enregistrements de préférence en parcourant les index des tables.
• FORCE INDEX : idem en plus restrictif. Une table ne sera parcourant sans index que si l'optimiseur n'a pas le choix.
• IGNORE INDEX : demande de ne pas favoriser les index.

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 FOR JOIN, FOR ORDER BY ou FOR GROUP BY.

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

Trop d'index dans une table ralentit toutes les requêtes qui y sont faites. Il est donc recommandé de ne pas en créer plus de deux ou trois dans chacune.

Limites

Dans MariaDB 10 et MySQL 5, il existe une limite de 61 jointures par requête. Il peut donc être nécessaire de les remplacer, par exemple par :

• GROUP_CONCAT() : si on n'a pas besoin que les données soient dans des colonnes séparées.
• UNION : s'il y a beaucoup de tables différentes jointes
• Une sous-requête (SELECT direct ou via une procédure stockée)
• Une classe statique de mapping dans les applications qui utilisent la base (enum pour remplacer des SELECT vers des données fixes, telles que la civilité : Mr, Mme).

Liens externes

• (en) « MySQL Optimization »
• (en) « A guide to mysqlreport »
• (en) « High Performance MySQL » (livre)
• (en) « Tuning tips from the company EZ »
• (en) « MySysop » (démo d'un script PHP pour l'optimisation et le tuning MySQL)
• Voir aussi les newsgroups et mailing lists MySQL