Utilisation de la fonction EXPLAIN de MySQL pour optimiser des requêtes SQL
Quand les requêtes en base de donnée deviennent lentes, une bonne approche est d'utiliser la fonction Explain pour avoir le plan d’exécution de la requête. Mais le résultat n'est pas toujours évident. Dans cet article je vous montre comment j'ai utilisé Explain pour résoudre des problèmes de performance.
Comme souvent, une fonctionnalité est développée et testée avec peu de ligne dans la bdd sans se soucier du potentiel de la volumétrie. Après quelques temps en production, la table fait 2,5 millions de lignes et pèse 3,6Go sur le disque. Il y a un minimum de 5 requêtes SQL sur la page (il pourrait y en avoir plus à cause de requête dans des boucles) et le code PHP tombe en timeout car les requêtes sont trop longues coté base de données.
Travailler sur le jeu de donnée complet serait compliqué, ne serait ce que charger un dump sur mon laptop et du coup chaque essai d'optimisation prendrait beaucoup de temps. J'ai donc fait une extraction de la table à partir d'un point dans le temps pour obtenir environ 1 million de ligne. C'est largement suffisant pour avoir les problèmes de performance qui existent en production.
Mon point de départ est: 1 table, 1M de lignes, environ 1Go sur le disque, 5 requêtes SQL qui nécessitent 1950 ms au total. Ok 2s pour 5 requêtes c'est pas tant que ça, mais je n'ai pas le jeu de donnée complet et je suis le seul à solliciter le serveur SQL sur mon PC. En production les temps sont bien plus élevés.
Voici les requêtes.
SELECT DISTINCT (l0_.id_job_execution) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.id_job_execution ASC; SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution = 81169 AND l0_.source_guid = 'SHIFT' AND (l0_.geo_status = 'NULL' OR l0_.geo_status IS NULL OR l0_.geo_status = 'pending') AND l0_.status <> 'Deleted'; SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution IN ('81169') AND l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.shift_id ASC, l0_.id_job_execution ASC; SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (partition by pro_guid ORDER BY id_job_execution DESC) AS rang FROM lib_events WHERE id_job_execution IN ('81169') AND id_job_execution > 81168 AND source_guid = 'SHIFT' ) temp WHERE rang = 1; SELECT * FROM lib_events l0_ WHERE l0_.id IN (?) LIMIT 1000;
J'ai simplifié la dernière requête, * est une liste de 34 colonnes et la clause IN contient le résultat de la 4ème requête soit bien plus que 1000 identifiants.
La première requête est simple mais nécessite 1000ms pour s’exécuter. La lancer avec Explain donne ce résultat.
> EXPLAIN SELECT DISTINCT (l0_.id_job_execution) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.id_job_execution ASC \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: liberal_event_id_job key: liberal_event_id_job key_len: 5 ref: NULL rows: 182228 Extra: Using index condition; Using where
Le 2ème requête prend 31 ms.
> EXPLAIN SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution = 81169 AND l0_.source_guid = 'SHIFT' AND (l0_.geo_status = 'NULL' OR l0_.geo_status IS NULL OR l0_.geo_status = 'pending') AND l0_.status <> 'Deleted' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: liberal_event_id_job,liberal_event_status,liberal_event_geo_status key: liberal_event_geo_status key_len: 303 ref: NULL rows: 46502 Extra: Using index condition; Using where
La 3ème requête prend 140 ms.
> EXPLAIN SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution IN ('81169') AND l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.shift_id ASC, l0_.id_job_execution ASC \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: ref possible_keys: liberal_event_id_job key: liberal_event_id_job key_len: 5 ref: const rows: 175692 Extra: Using index condition; Using where
La 4ème requête prend 217 ms.
> EXPLAIN SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (partition by pro_guid ORDER BY id_job_execution DESC) AS rang FROM lib_events WHERE id_job_execution IN ('81169') AND id_job_execution > 81168 AND source_guid = 'SHIFT' ) temp WHERE rang = 1 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 175692 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DERIVED table: lib_events type: ref possible_keys: liberal_event_id_job key: liberal_event_id_job key_len: 5 ref: const rows: 175692 Extra: Using index condition; Using where; Using temporary
La dernière requête prend 212 ms.
EXPLAIN SELECT * FROM lib_events l0_ WHERE l0_.id IN (?) LIMIT 1000 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 175692 Extra: Using where
On peut observer que toutes les requêtes utilisent un index. C'est indiqué dans la ligne "key" (la 6ème). Certaines ont plusieurs choix d'index, c'est indiqué dans la ligne "possibile_key" (la 5ème).
La première requête est vraiment simple, mais prend 1000 ms, c'est beaucoup de temps pour une requête si simple. On ne peut rien faire pour la clause ORDER BY mais on peut améliorer la clause WHERE. La création d'un nouvel index contenant les colonnes id_job_execution et source_guid devrait couvrir l'intégralité de la clause WHERE.
> CREATE INDEX IF NOT EXISTS test_source_job ON `lib_events` (`source_guid`, `id_job_execution`); Query OK, 0 rows affected (2.973 sec) Records: 0 Duplicates: 0 Warnings: 0 > EXPLAIN SELECT DISTINCT (l0_.id_job_execution) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.id_job_execution ASC \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: liberal_event_id_job,test_source_job key: test_source_job key_len: 53 ref: NULL rows: 179312 Extra: Using where; Using index
Le nouveau plan d’exécution fourni par Explain montre que le nouvel index est utilisé (ligne "key"). On voit également que MySQL travaille avec moins de lignes (ligne "rows") 179 312 au lieu de 182 228. Enfin la ligne "extra" indique "Using index" et non "Using index condition" ce qui signifie que la clause WHERE est entièrement couverte par notre index ce qui était l'objectif. Et coté performance, la requête ne nécessite plus que 32 ms de temps d’exécution. C'est un gain énorme.
La deuxième requête ne bénéficie pas de ce nouvel index. Exécuter un explain montre que le nouvel index est dans la liste des "possible_keys" mais le plan d'exécution montre que la requête utilisé toujours le même index. Essayons d'ajouter un nouvel index pour couvrir l'intégralité de la clause WHERE comme on a fait pour la requête précédente.
> CREATE INDEX IF NOT EXISTS test_source_job_geo_status ON `lib_events` (`source_guid`, `id_job_execution`, `geo_status`, `status`); Query OK, 0 rows affected (3.793 sec) Records: 0 Duplicates: 0 Warnings: 0 > EXPLAIN SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution = 81169 AND l0_.source_guid = 'SHIFT' AND (l0_.geo_status = 'NULL' OR l0_.geo_status IS NULL OR l0_.geo_status = 'pending') AND l0_.status <> 'Deleted' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: liberal_event_id_job,liberal_event_status,liberal_event_geo_status,test_source_job,test_source_job_geo_status key: test_source_job_geo_status key_len: 659 ref: NULL rows: 6 Extra: Using where; Using index
Explain montre la même évolution que pour la première requête et le temps d'exécution passe de 31 à 4 ms.
La 3ème requête devrait bénéficier du premier index créé. Utilisons Expain pour le controller.
> EXPLAIN SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution IN ('81169') AND l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' ORDER BY l0_.shift_id ASC, l0_.id_job_execution ASC \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: index_merge possible_keys: liberal_event_id_job,test_source_job,test_source_job_geo_status key: liberal_event_id_job,test_source_job key_len: 5,53 ref: NULL rows: 87846 Extra: Using intersect(liberal_event_id_job,test_source_job); Using where
On vois que le nouvel index est utilisé en combinaison avec l'index liberal_event_id. C'est étrange car cet index contient le champs id_job_execution qui est également présent dans le nouvel index et le temps d'exécuion est le même. Donc aucun gain. Cette requête devrait être plus rapide.
En regardant la requête avec plus d'attention, on se rend compte qu'il y a un ORDER BY (connu pour être lent) alors que la clause SELECT ne fait qu'un COUNT(). Cet ORDER BY est totalement inutile et apporte beaucoup de travail coté serveur.
Testons de nouveau la requête sans la clause ORDER BY.
> EXPLAIN SELECT COUNT(l0_.id) AS sclr_0 FROM lib_events l0_ WHERE l0_.id_job_execution IN ('81169') AND l0_.id_job_execution > 81168 AND l0_.source_guid = 'SHIFT' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: ref possible_keys: liberal_event_id_job,test_source_job,test_source_job_geo_status key: test_source_job key_len: 53 ref: const,const rows: 179312 Extra: Using where; Using index
Comme attendu, cette fois seul le nouvel index est utilisé et le temps d'exécution passe de 140 à 23 ms.
Les 2 dernières requêtes consomment chacune 200 ms et fonctionnent ensemble. La liste d'identifiant utilisée dans la cinquième requête est le résultat de la 4ème requête.
La 5ème requête est très simple, elle ne devrait pas prendre autant de temps.En regardant l'Explain on se rend compte que le serveur utilise la clé primaire comme index et travaille avec 179 312 lignes. Il n'y a rien de plus à faire sur cette requête sauf qu'elle contient une clause LIMIT pour ne retourner que 1000 lignes. Que se passe-t-il si l'on déplace la clause LIMIT sur la quatrième requête?
> EXPLAIN SELECT * FROM lib_events l0_ WHERE l0_.id IN (?) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: l0_ type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 1000 Extra: Using where
Comme prévu, le plan d'exécution donnée par Explain montre que le serveur travaille avec 1000 lignes et le temps d'exécution passe de 200 à 10 ms. C'est un gros gain, surtout que la donnée transite par du code PHP et la réduction du nombre d'id retourné par la 4ème requête réduis la consommation mémoire du script.
Ajouter la clause LIMIT à la 4ème requête ne change rien, elle est ni plus lente ni plus rapide.
J'ai tenté plusieurs approche pour optimiser cette requête comme l'utilisation d'une CTE qui est censé être plus optimisé, mais il n'y a aucun impact bénéfique sur la performance de cette requête. Finalement, après une bonne nuit de sommeil, je me suis demandé si le champs dans la clause PARTITION BY ne devrait pas faire parti de l'index.
Tentons donc de créer un nouvel index en incluant ce champs dedans en plus des champs présent dans la clause WHERE.
> CREATE INDEX IF NOT EXISTS test_source_job_guid ON `lib_events` (`source_guid`, `id_job_execution`, `pro_guid`); Query OK, 0 rows affected (4.839 sec) Records: 0 Duplicates: 0 Warnings: 0 > EXPLAIN SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (partition by pro_guid ORDER BY id_job_execution DESC) AS rang FROM lib_events WHERE id_job_execution IN ('81169') AND source_guid = 'SHIFT' ) temp WHERE rang = 1 LIMIT 0, 1000 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 199362 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DERIVED table: lib_events type: ref possible_keys: liberal_event_id_job,test_source_job,test_source_job_geo_status,test_source_job_guid key: test_source_job_guid key_len: 53 ref: const,const rows: 199362 Extra: Using where; Using index; Using temporary
Comme pour les autres, on peut maintenant voir sur la ligne "extra" le terme "using index" et plus "condition on index". Et le temps d'exécution passe de 200 à 90 ms. La clause PARTITION BY bénéficie donc des index.
Les index prennent du temps à être mis à jour quand les données sont écrites dans la table et il ne faut pas non plus oublier leur impact sur l'espace disque. Il ne faut pas mettre d'index inutile et éviter les duplications. Dans notre cas le premier index sur les champs source_guid et id_job_execution est un doublon du dernier index qui contient en plus le champs prod_guid. MySQL sait utiliser un index que partiellement et ignorer les champs en plus, on peut donc supprimer le premier index et controller avec Explain que le dernier index est bien pris en compte et que les performances restent les mêmes.
J'espère que cet article vous aidera à comprendre l'utilisation de la fonction Explain qui est un outil très puissant et utile mais malheureusement souvent oublié ou incompris. Des fois le problème ne vient pas d'un index manquant comme ce fut le cas pour la 3ème requête et la lecture attentive du résultat d'Explain peut mettre en évidence qu'il y a un problème avec la façon dont la requête a été écrite.
Ajouter un commentaire