{"id":1413,"date":"2013-04-23T14:12:20","date_gmt":"2013-04-23T13:12:20","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1413"},"modified":"2017-09-05T15:42:27","modified_gmt":"2017-09-05T14:42:27","slug":"utiliser-une-sous-requete-cest-mal","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2013\/04\/23\/utiliser-une-sous-requete-cest-mal\/","title":{"rendered":"Utiliser une sous-requ\u00eate c’est mal ?"},"content":{"rendered":"

Jusqu\u2019en MySQL 5.5 inclus, l\u2019utilisation de sous-requ\u00eates peut, dans certain cas, \u00eatre la cause de probl\u00e8mes de performances (l\u2019optimiseur est bien meilleur en MySQL 5.6<\/a>, MariaDB 5.5 et MariaDB 10<\/a>).<\/p>\n

R\u00e9cemment j\u2019ai eu un souci en prod, apr\u00e8s une MEP<\/a>, avec une requ\u00eate qui durait en moyenne plus de 1000 secondes<\/strong>…<\/p>\n

Inutile de pr\u00e9ciser que dans un environnement OLTP, application web plus pr\u00e9cis\u00e9ment, ce n\u2019est pas jouable, elle a donc \u00e9t\u00e9 vir\u00e9e tr\u00e8s rapidement (mais pas le d\u00e9veloppeur qui l\u2019a cr\u00e9\u00e9e…)<\/p>\n

 <\/p>\n

Alors passons sur le fait que ce genre de probl\u00e8me devrait \u00eatre identifi\u00e9 avant d\u2019arriver sur la prod, et voyons \u00e0 quoi ressemble une requ\u00eate qui peut prendre 45 minutes:<\/p>\n

SELECT DISTINCT OtherID\r\nFROM MyTable\r\nWHERE Status = 1\r\n    AND Stage = 4\r\n    AND Invisible = 0\r\n    AND MainID IN\r\n        (\r\n          SELECT MainID\r\n          FROM MyTable\r\n          WHERE OtherID = 2779262\r\n              AND Stage = 0\r\n              AND Invisible = 0\r\n         )<\/pre>\n
 <\/p>\n
7 rows in set (44 min 56.66 sec)<\/strong><\/span><\/p>\n
 <\/p>\n
On a donc une belle sous-requ\u00eate dans la clause IN de la requ\u00eate externe. La table est en InnoDB<\/strong>, elle contient 310 millions<\/strong> de lignes pour une taille de 62Go<\/strong>.<\/p>\n
 <\/p>\n
Qu\u2019en dit le QEP ?<\/h3>\n
 <\/p>\n
*************************** 1. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/address>\n
\u00a0select_type: PRIMARY<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/strong><\/address>\n
possible_keys: StageMainIDOtherID<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: StageMainIDOtherID<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 1<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: const<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 155316386<\/strong><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using temporary<\/address>\n
*************************** 2. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 2<\/address>\n
\u00a0select_type: DEPENDENT SUBQUERY<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: index_subquery<\/strong><\/address>\n
possible_keys: MainIDOtherIDStatus, autre index…<\/em><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: MainIDOtherIDStatus<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 8<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: func,const<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 1<\/strong><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where<\/address>\n
 <\/p>\n
Malgr\u00e9 la pr\u00e9sence d\u2019index (pertinent ?), l\u2019optimiseur analyse la moiti\u00e9 des lignes de la table (155316386), ce qui sur une grosse table peut prendre pas mal de temps, vous en conviendrez.<\/p>\n
Niveau status variables<\/h3>\n
 <\/p>\n
| Created_tmp_disk_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_files \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 44383976 \u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 151576920 \u00a0\u00a0\u00a0 |\r\n| Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 7 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
Cette vision un peu plus bas niveau montre un nombre \u00e9lev\u00e9 de lecture lors du parcours transverse de l\u2019index (Handler_read_next<\/em>), en clair ce n\u2019est pas vraiment bon pour les performances.<\/p>\n
A noter que la clause DISTINCT g\u00e9n\u00e8re la cr\u00e9ation d\u2019une table temporaire, son remplissage et \u00e9videmment sa lecture (Created_tmp_tables, Handler_write, Handler_read_rnd_next<\/em>) .<\/p>\n
Temps d\u2019ex\u00e9cution unitaire (w\/ mysqlslap)<\/h3>\n
 <\/p>\n
En mode unitaire, sur un serveur en configuration prod mais sans charge, mysqlslap nous donne:<\/p>\n
\u00a0\u00a0\u00a0 Minimum number of seconds to run all queries: 478.936 seconds<\/strong><\/span>.<\/p>\n
Comment faire pour rendre cette requ\u00eate utilisable ?<\/h3>\n
Plusieurs choix, le plus simple est de la r\u00e9\u00e9crire (pas de modification de la table ou des index). Une sous-requ\u00eate de ce type se converti facilement en jointure:<\/p>\n
 <\/p>\n
SELECT DISTINCT r1.OtherID\r\nFROM MyTable r1 INNER JOIN MyTable r2 USING (MainID)\r\nWHERE r1.Status = 1\r\n    AND r1.Stage = 4\r\n    AND r1.Invisible = 0\r\n    AND r2.OtherID = 2779262\r\n    AND r2.Stage = 0\r\n    AND r2.Invisible = 0<\/pre>\n
Qu\u2019en dit le QEP ?<\/h3>\n
\u00a0<\/address>\n
*************************** 1. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/address>\n
\u00a0select_type: SIMPLE<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: r2<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/strong><\/address>\n
possible_keys: OtherIDStageStatusInvisibleMainID, autre index…<\/em><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: OtherIDStageStatusInvisibleMainID<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 5<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: const,const<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 234<\/strong><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using index;<\/strong> Using temporary<\/address>\n
*************************** 2. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/address>\n
\u00a0select_type: SIMPLE<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: r1<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/strong><\/address>\n
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: MainIDOtherIDStatus<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 4<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: MyTable.r2.MainID<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 1<\/strong><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where<\/address>\n
 <\/p>\n
234 lignes \u00e0 analyser au lieu de 155316386. \u00c7a \u00e0 l\u2019air meilleur \ud83d\ude42<\/p>\n
Un index couvrant en prime (Extra: Using index<\/em>), c\u2019est toujours bon \u00e0 prendre.<\/p>\n
Niveau status variables<\/h3>\n
 <\/p>\n
| Created_tmp_disk_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_files \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 236 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 705 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 7 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n<\/pre>\n
Avec beaucoup moins de ligne analys\u00e9es, le nombre de Handler_read_next<\/em> est extr\u00eamement bas par rapport \u00e0 la la requ\u00eate pr\u00e9c\u00e9dente: 705 au lieu de 151576920. On a donc un faible nombre de lecture lors parcours transverse de l\u2019arbre d\u2019index. En clair, c\u2019est plut\u00f4t une bonne chose.<\/p>\n
 <\/p>\n
Le temps d\u2019ex\u00e9cution de la requ\u00eate \u00e0 froid est: 7 rows in set (1.17 sec)<\/strong><\/span><\/p>\n
A mettre en perspective avec les 45 minutes de la premi\u00e8re version…<\/p>\n
Temps d\u2019ex\u00e9cution unitaire (w\/ mysqlslap)<\/h3>\n
 <\/p>\n
\u00a0\u00a0\u00a0 Minimum number of seconds to run all queries: 0.001 seconds<\/strong><\/span><\/p>\n
 <\/p>\n
Le faisceau d\u2019indice valide bien un \u201cmeilleur\u201d (c\u2019est un euph\u00e9misme) temps d\u2019ex\u00e9cution minimal unitaire (0.001 s vs 478.936 s)<\/p>\n
 <\/p>\n
Je pense que l\u2019on va garder cette version de la requ\u00eate pour la prod :).<\/p>\n
 <\/p>\n
Alors que faut il en tirer comme conclusion ?<\/h3>\n
 <\/p>\n
Il est l\u00e9gitime de se poser la question de proscrire ou pas les sous-requ\u00eates si on n\u2019est pas en MySQL 5.6+ ou MariaDB 5.5+<\/p>\n
Je conseillerai surtout de toujours tester ses requ\u00eates avant de les pousser en prod… notamment en la passant au r\u00e9v\u00e9lateur EXPLAIN<\/strong><\/a>.<\/p>\n
En effet dans le cas de la 1\u00e8re requ\u00eate, rows: 155316386, est un bon indicateur de \u201c\u00e7a risque de partir en couille !<\/em>\u201d.<\/p>\n
La requ\u00eate peut \u00e9galement \u00eatre r\u00e9\u00e9crite de la mani\u00e8re suivante:<\/p>\n
 <\/p>\n
SELECT DISTINCT OtherID\r\nFROM\r\n(\r\n    SELECT MainID\r\n    FROM MyTable\r\n    WHERE Invisible = 0\r\n        AND Stage = 0\r\n        AND OtherID = 2779262\r\n) dt\r\nINNER JOIN MyTable r\r\nWHERE r.MainID = dt.MainID\r\n    AND r.Invisible = 0\r\n    AND r.Stage = 4\r\n    AND r.Status = 1<\/pre>\n
oh la belle sous requ\u00eate ! (ou table d\u00e9riv\u00e9e pour les puristes)<\/p>\n
Qu\u2019en dit le QEP ?<\/h3>\n
 <\/p>\n
*************************** 1. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/address>\n
\u00a0select_type: PRIMARY<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: <derived2><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ALL<\/address>\n
possible_keys: NULL<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: NULL<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: NULL<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: NULL<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 235<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using temporary<\/address>\n
*************************** 2. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/address>\n
\u00a0select_type: PRIMARY<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: r<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/address>\n
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: MainIDOtherIDStatus<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 4<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: dt.MainID<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 1<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where<\/address>\n
*************************** 3. row ***************************<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 2<\/address>\n
\u00a0select_type: DERIVED<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/address>\n
possible_keys: OtherIDStageStatusInvisibleMainID, autre index…<\/em><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: OtherIDStageStatusInvisibleMainID<\/address>\n
\u00a0\u00a0\u00a0\u00a0 key_len: 5<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref:<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 234<\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using index<\/address>\n
\u00a0<\/address>\n
\u00a0<\/address>\n
54990 (235 x 1 x 234) lignes \u00e0 analyser + index couvrant. \u00c7a n\u2019a pas l\u2019air trop mal tout \u00e7a !<\/p>\n
Cette requ\u00eate combine sous-requ\u00eate + jointure et jusqu\u2019ici… m\u00eame pas peur.<\/p>\n
Niveau status variables<\/h3>\n
 <\/p>\n
| Created_tmp_disk_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_files \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Created_tmp_tables \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 236 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 705 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_read_rnd_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 244 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 242 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
Les indications bas niveau fournit par les handlers sont assez proche de celles fournit par l\u2019ex\u00e9cution de la version jointure seule, de la requ\u00eate.<\/p>\n
Seules les informations (Created_tmp_tables, Handler_read_rnd_next, \u00a0Handler_write<\/em>) li\u00e9es aux 2 tables temporaires cr\u00e9\u00e9es (1 pour la table d\u00e9riv\u00e9e + 1 pour le DISTINCT) sont diff\u00e9rentes.<\/p>\n
 <\/p>\n
En(un peu plus) clair: Handler_write<\/em> = 235 lignes \u00e9crites dans la table d\u00e9riv\u00e9e + 7 lignes du r\u00e9sultat final apr\u00e8s d\u00e9doublonnage (DISTINCT) = 242<\/p>\n
 <\/p>\n
Ces 2 tables \u00e9tant suffisamment petite pour tenir enti\u00e8rement en m\u00e9moire (Created_tmp_disk_tables<\/em>=0) le surco\u00fbt devrait \u00eatre n\u00e9gligeable par rapport \u00e0 la la version jointure seule de la requ\u00eate.<\/p>\n
 <\/p>\n
Le temps d\u2019ex\u00e9cution de cette requ\u00eate \u00e0 froid est: 7 rows in set (1.23 sec)<\/strong><\/span><\/p>\n
Il est \u00e9quivalent \u00e0 la jointure.<\/p>\n
 <\/p>\n
Temps d\u2019ex\u00e9cution unitaire (w\/ mysqlslap)<\/h3>\n
 <\/p>\n
\u00a0\u00a0\u00a0 Minimum number of seconds to run all queries: 0.001 seconds<\/strong><\/span><\/p>\n
 <\/p>\n
On retrouve bien des temps d\u2019ex\u00e9cution \u00e9quivalent au milli\u00e8me pr\u00e8s. CQFD<\/p>\n
Il y a souvent plusieurs fa\u00e7on d\u2019\u00e9crire une requ\u00eate, mais elles sont rarement \u00e9quivalentes en mati\u00e8re de temps d\u2019ex\u00e9cution. La commande EXPLAIN fait partie du package minimal que tout d\u00e9veloppeur doit conna\u00eetre. Ajouter \u00e0 cela un environnement de test et un zeste de bon sens et il devient alors possible d\u2019\u00e9liminer une partie des soucis de prod et d\u2019\u00e9viter de perdre du temps inutilement.<\/p>\n
\n
\n
 <\/p>\n
Divers<\/h3>\n
--------------\r\nmysql \u00a0Ver 14.14 Distrib 5.5.24, for Linux (x86_64) using readline 5.1\r\nServer version: \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a05.5.24-55-log Percona Server<\/strong> (GPL), Release 26.0\r\nProtocol version: \u00a0\u00a0\u00a010\r\nConnection: \u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0Localhost via UNIX socket\r\nServer characterset: \u00a0\u00a0\u00a0utf8\r\nDb \u00a0\u00a0\u00a0 characterset: \u00a0\u00a0\u00a0utf8\r\nClient characterset: \u00a0\u00a0\u00a0utf8\r\nConn. \u00a0characterset: \u00a0\u00a0\u00a0utf8\r\n--------------\r\n<\/pre>\n
CREATE TABLE MyTable (\r\n\u00a0MyTableID int(11) NOT NULL AUTO_INCREMENT,\r\n\u00a0MainID int(11) NOT NULL DEFAULT '0',\r\n\u00a0OtherID int(11) NOT NULL DEFAULT '0',\r\n\u00a0Stage tinyint(4) NOT NULL DEFAULT '0',\r\n\u00a0Status int(11) DEFAULT '0',\r\n\u00a0Invisible tinyint(1) NOT NULL DEFAULT '0',\r\n\u00a0PRIMARY KEY (MyTableID),\r\n\u00a0KEY OtherIDStageStatusInvisible (OtherID,Stage,Status,Invisible),\r\n\u00a0KEY OtherIDStatusInvisible (OtherID,Status,Invisible),\r\n\u00a0KEY MainIDOtherIDStatus (MainID,OtherID,Status),\r\n\u00a0KEY OtherIDStageStatusInvisibleMainID (OtherID,Stage,Status,Invisible,MainID),\r\n\u00a0KEY StageMainIDOtherID (Stage,MainID,OtherID)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci<\/pre>\n
 <\/p>\n
*************************** 1. row ***************************\r\nName: MyTable\r\nEngine: InnoDB\r\nVersion: 10\r\nRow_format: Compact\r\nRows: 310632909\r\nAvg_row_length: 52\r\nData_length: 16334700544\r\nIndex_length: 49877909504\r\nData_free: 7340032\r\nCollation: utf8_swedish_ci<\/pre>\n","protected":false},"excerpt":{"rendered":"
Jusqu\u2019en MySQL 5.5 inclus, l\u2019utilisation de sous-requ\u00eates peut, dans certain cas, \u00eatre la cause de probl\u00e8mes de performances (l\u2019optimiseur est bien meilleur en MySQL 5.6, MariaDB 5.5 et MariaDB 10).<\/p>\n
R\u00e9cemment j\u2019ai eu un souci en prod, apr\u00e8s une MEP, avec une requ\u00eate qui durait en moyenne plus de 1000 secondes…<\/p>\n
Inutile de pr\u00e9ciser que dans un environnement OLTP, application web plus pr\u00e9cis\u00e9ment, ce n\u2019est pas jouable, elle a donc \u00e9t\u00e9 vir\u00e9e tr\u00e8s rapidement (mais pas le d\u00e9veloppeur qui l\u2019a cr\u00e9\u00e9e…)<\/p>\n
Alors passons sur le fait que ce genre de probl\u00e8me devrait \u00eatre identifi\u00e9 avant d\u2019arriver sur la prod, et voyons \u00e0 quoi ressemble une requ\u00eate qui peut prendre 45 minutes:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8,34],"tags":[],"class_list":["post-1413","post","type-post","status-publish","format-standard","hentry","category-mysql","category-optimisation"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-mN","jetpack-related-posts":[{"id":1163,"url":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","url_meta":{"origin":1413,"position":0},"title":"Am\u00e9liorations de l’optimiseur dans MariaDB","author":"Olivier DASINI","date":"9 janvier 2012","format":false,"excerpt":"Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l'optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates. Voici un effet visuel de ces optimisations: Avec MySQL 5.5, l'utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d'un SELECT), donne le plan d'ex\u00e9cution suivant:","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1375,"url":"https:\/\/dasini.net\/blog\/2013\/02\/21\/mysql-5-6\/","url_meta":{"origin":1413,"position":1},"title":"MySQL 5.6","author":"Olivier DASINI","date":"21 f\u00e9vrier 2013","format":false,"excerpt":"Cela fait quelques jours maintenant que MySQL 5.6 est disponible pour la production. Un impressionnant travail a \u00e9t\u00e9 effectu\u00e9 par les \u00e9quipe d'Oracle, voici un petit r\u00e9sum\u00e9 des principales \u00e9volution vu par Peter Zaitsev. L'\u00e9v\u00e9nement dans l'\u00e9v\u00e9nement, c'est la \"pol\u00e9mique\" sur les performances de la 5.6, par rapport \u00e0 MySQL\u2026","rel":"","context":"Dans "MariaDB"","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1245,"url":"https:\/\/dasini.net\/blog\/2012\/04\/05\/mysql-5-6-rock-suite\/","url_meta":{"origin":1413,"position":2},"title":"MySQL 5.6 rock suite","author":"Olivier DASINI","date":"5 avril 2012","format":false,"excerpt":"Voici la suite du post MySQL 5.6 rock, dans lequel je test MySQL 5.5 & 5.6, MariaDB 5.3 & 5.5 et Percona server 5.5. Pour cet article, toujours un bench. Le contexte est assez proche, \u00e0 la diff\u00e9rence pr\u00e8s que cette fois les serveurs sont test\u00e9s en lecture (65%) et\u2026","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"dasini.net - 95 centile for R\/W i\/o bounds","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/dasini.net_bench_mysql_95percentil.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1227,"url":"https:\/\/dasini.net\/blog\/2012\/03\/30\/mysql-5-6-rocks\/","url_meta":{"origin":1413,"position":3},"title":"MySQL 5.6 rock !","author":"Olivier DASINI","date":"30 mars 2012","format":false,"excerpt":"Comme d'habitude, mon but n'est pas de conna\u00eetre les possibilit\u00e9s maximales du serveur (d'autres le font mieux que moi), mais plut\u00f4t d'avoir une id\u00e9e assez pr\u00e9cise de leurs comportements respectifs dans un environnement le plus proche possible de ma prod. pour ce test, les candidats sont, Percona 5.5, MariaDB 5.3\u2026","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"dasini.net - 95 centile","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-includes\/images\/percentil.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1487,"url":"https:\/\/dasini.net\/blog\/2014\/01\/07\/update-db-set-age-age-1-where-productmysql\/","url_meta":{"origin":1413,"position":4},"title":"UPDATE db SET age = age + 1 WHERE product=’MySQL’","author":"Olivier DASINI","date":"7 janvier 2014","format":false,"excerpt":"2013 \u00e0 \u00e9t\u00e9 une ann\u00e9e tr\u00e8s riche en ce qui concerne l\u2019\u00e9cosyst\u00e8me MySQL \/ MariaDB \/ Percona Server. Voici un petit r\u00e9cap technique (incomplet) pour les 3 acteurs majeurs: MySQL @Oracle MariaDB & SkySQL Percona","rel":"","context":"Dans "MariaDB"","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1425,"url":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","url_meta":{"origin":1413,"position":5},"title":"Utiliser une sous requ\u00eate c\u2019est mal ? (suite) part 1-3","author":"Olivier DASINI","date":"27 mai 2013","format":false,"excerpt":"Comme promit, voici la suite de l\u2019article Utiliser une sous-requ\u00eate c\u2019est mal ? L\u2019id\u00e9e ici est de r\u00e9pondre aux interrogations de svar et d\u2019en profiter pour explorer les nouvelles possibilit\u00e9s de la variante stable de MySQL qui poss\u00e8de l\u2019optimiseur le plus avanc\u00e9, c\u2019est \u00e0 dire MariaDB 5.5. Pr\u00e9ambule En pr\u00e9-requis,\u2026","rel":"","context":"Dans "MariaDB"","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA","width":350,"height":200,"srcset":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1x, https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1.5x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1413"}],"version-history":[{"count":12,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1413\/revisions"}],"predecessor-version":[{"id":2030,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1413\/revisions\/2030"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}