Quand l'optimiseur ne veut pas d'un index

Comment se fait-il que cette insertion prenne si longtemps? La requête est simple, la colonne indexée et les tables sont à jour. Tout tient en mémoire... Mais il faut jusqu'à une heure et demi pour exécuter ceci :

mysql> select info from table where date < '2007-09-01' limit 250000;

Je dispose d'une table dont je doit traiter les données. Comme la table principale est plutôt grande (relativement au serveur qui l'héberge :) ), j'ai ajouté une table de cache intermédiaire, pour pouvoir préparer des lots de données quand le serveur est moins chargé.

A ma surprise, cette requête peut facilement prendre une heure et plus en début de mois, mais accélère progressivement et s'exécute beaucoup plus rapidement en fin de mois (note amusante : je suis débordé en début de mois, mais plus relax en fin de mois...). Il semble donc qu'il y ait un impact du nombre de lignes à lire sur la requête. C'est notre premier indice.

Mon outil de prédilection est la commande EXPLAIN.

mysql> explain select info from table where date < '2007-09-01' limit 250000;
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
|  1 | SIMPLE      | table  | ALL  | date          | NULL | NULL    | NULL | 27204920 | Using where | 
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
1 row in set (0.08 sec)
 

La clause LIMIT ne sert pas du tout à l'optimiseur : ce dernier doit toujours faire les mêmes calculs mais décide simplement d'en ignorer une partie une fois rendu à la partie d'affichage. En fait, il reste surtout à optimiser la clause.

Un index couvrant pourrait être une bonne idée, mais je ne veux pas surcharger la table avec un index supplémentaire juste pour cette requête. Comme la table est sollicitée en lecture et écriture, je préfère ne pas avoir trop d'index, ce qui ralentirait les écritures.

En lisant le résultat de EXPLAIN, une question de pose : pourquoi utiliser 'Using where' alors que l'index est là? Je me souviens alors de David Axmark, qui me disait : "Si MySQL voit qu'il y a trop de lignes à analyser dans une table, il va préférer lire directement toutes les données plutôt que passer par l'index. La limite est autour de 30%" (citation de mémoire : cela fait deux ans que David m'a dit cela). Cette explication a le mérite de s'appliquer à la situation (plus lent en début de mois, plus rapide en fin de mois), et donne une piste d'amélioration.

Solution à étudier : il faut réduire le nombre de lignes lues, mais s'assurer que l'on continue de couvrir tout le mois d'août correctement. J'essaie donc d'ajouter des limitateurs, certes un peu artificiels, afin de réduire le nombre de lignes à manipuler. :

mysql> explain select info from table where hour(date) = hour(now()) limit 250000;
 
mysql> explain select url from table where  mod(id, 24) = hour(now()) and date(date) < '2007-09-01' limit 250000;
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
|  1 | SIMPLE      | table  | ALL  | NULL          | NULL | NULL    | NULL | 27208386 | Using where | 
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 

En fait, je retombe sur le même problème : l'optimiseur ne comprend pas ce que je veux faire et il préfère revenir à sa méthode initiale. Il faut être plus intelligent que cela. Je me concentre sur l'index de date qui existe déjà. Cette fois-ci, j'obtiens ce que je cherche :

mysql> explain select info from table where date(date) = '2007-08-05' limit 250000;
 
 
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
|  1 | SIMPLE      | table  | range | date          | date | 4       | NULL | 3424317 | Using where | 
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
1 row in set (0.09 sec)
 

En sélectionnant une seule journée, j'ai incité l'optimiseur à n'utiliser qu'une petite partie des données (1 / 30eme environs), et cette fois-ci, il décide d'utiliser l'index. Voyons ce que cela donne si j'augmente la taille de la fenêtre de sélection. Je ne peux plus utiliser la fonction date(), mais un intervalle de dates fera très bien l'affaire.

mysql> explain select info from table where date between '2007-08-01' and '2007-08-12' ;
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
|  1 | SIMPLE      | table  | range | date          | date | 4       | NULL | 4950989 | Using where | 
 
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
 
1 row in set (0.12 sec)
 
 
 
mysql> explain select info from table where date between '2007-08-01' and '2007-08-13' ;
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
|  1 | SIMPLE      | table  | ALL  | date          | NULL | NULL    | NULL | 27204920 | Using where | 
 
+----+-------------+--------+------+---------------+------+---------+------+----------+-------------+
 
1 row in set (0.17 sec)
 

Entre le 12 et le 13 août, le moteur décide de ne plus utiliser l'index. 4,9 millions sur 27,2 millions au total, cela représente 18% de la masse de données. La limite d'utilisation de l'index doit être autour de 20% environs.

Et effectivement, l'exécution se passe bien plus rapidement, et de manière beaucoup plus prédictible :

mysql> insert into cache select info from table where date between '2007-08-01' and '2007-08-13' limit 250000;
 
Query OK, 0 rows affected (3 min 37.86 sec)
 
Records: 25000  Duplicates: 25000  Warnings: 0
 

A noter : j'ai l'habitude d'avoir un modèle de conception disponible dans une base annexe, pour pouvoir exécuter des commandes EXPLAIN sur les tables et analyser les requêtes sans travailler directement sur le site de production. Ici, cela n'a servi à rien, car c'est le volume de données qui posait problème. Les EXPLAIN dans la base annexe ne révélait rien. Cela confirme mon intuition habituelle : pour tester un modèle, il est bon d'y mettre 10 fois plus de données que prévu. Les problèmes sautent alors aux yeux.

Conclusion

• Essayez toujours de réduire la quantité de données qui est manipulée par MySQL. Dans cette optique, LIMIT ne sert à rien. Vraiment rien du tout, en fait. Il vaut mieux travailler avec les index, et s'assurer qu'ils sont utilisés.
• Passez régulièrement EXPLAIN sur vos requêtes, même en production. Peut-être qu'il faudrait simplement noter les EXPLAIN des requêtes qui sont exécutées, et intervenir quand l'optimiseur change de technique. Au moins, en utilisant une sélection représentative des opérations sur la base MySQL, vous pourrez savoir quand l'optimiseur abandonne toutes les avenues qu'on croyait lui avoir ouvert.
• La quantité de données à un impact sur les performances du serveur. Notez bien que la quantité de données qui fera plier votre machine est sûrement différente de celle de cet exemple : je travaille là sur une vieille machine, sans envergure. Par contre, l'optimiseur appliquera les mêmes recettes sur votre machine que sur ce MySQL 5.0.37. Bref, pensez à tester votre modèle de conception avec beaucoup de données : un ordre de magnitude au moins (c'est à dire, 10 fois plus).