Utiliser une sous-requête c’est mal ?
Jusqu’en MySQL 5.5 inclus, l’utilisation de sous-requêtes peut, dans certain cas, être la cause de problèmes de performances (l’optimiseur est bien meilleur en MySQL 5.6, MariaDB 5.5 et MariaDB 10).
Récemment j’ai eu un souci en prod, après une MEP, avec une requête qui durait en moyenne plus de 1000 secondes…
Inutile de préciser que dans un environnement OLTP, application web plus précisément, ce n’est pas jouable, elle a donc été virée très rapidement (mais pas le développeur qui l’a créée…)
Alors passons sur le fait que ce genre de problème devrait être identifié avant d’arriver sur la prod, et voyons à quoi ressemble une requête qui peut prendre 45 minutes:
SELECT DISTINCT OtherID
FROM MyTable
WHERE Status = 1
AND Stage = 4
AND Invisible = 0
AND MainID IN
(
SELECT MainID
FROM MyTable
WHERE OtherID = 2779262
AND Stage = 0
AND Invisible = 0
)
7 rows in set (44 min 56.66 sec)
On a donc une belle sous-requête dans la clause IN de la requête externe. La table est en InnoDB, elle contient 310 millions de lignes pour une taille de 62Go.
Qu’en dit le QEP ?
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: MyTable type: ref possible_keys: StageMainIDOtherID key: StageMainIDOtherID key_len: 1 ref: const rows: 155316386 Extra: Using where; Using temporary *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: MyTable type: index_subquery possible_keys: MainIDOtherIDStatus, autre index… key: MainIDOtherIDStatus key_len: 8 ref: func,const rows: 1 Extra: Using where
Malgré la présence d’index (pertinent ?), l’optimiseur analyse la moitié des lignes de la table (155316386), ce qui sur une grosse table peut prendre pas mal de temps, vous en conviendrez.
Niveau status variables
| Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | | Handler_read_first | 0 | | Handler_read_key | 44383976 | | Handler_read_last | 0 | | Handler_read_next | 151576920 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 8 | | Handler_write | 7 |
Cette vision un peu plus bas niveau montre un nombre élevé de lecture lors du parcours transverse de l’index (Handler_read_next), en clair ce n’est pas vraiment bon pour les performances.
A noter que la clause DISTINCT génère la création d’une table temporaire, son remplissage et évidemment sa lecture (Created_tmp_tables, Handler_write, Handler_read_rnd_next) .
Temps d’exécution unitaire (w/ mysqlslap)
En mode unitaire, sur un serveur en configuration prod mais sans charge, mysqlslap nous donne:
Minimum number of seconds to run all queries: 478.936 seconds.
Comment faire pour rendre cette requête utilisable ?
Plusieurs choix, le plus simple est de la réécrire (pas de modification de la table ou des index). Une sous-requête de ce type se converti facilement en jointure:
SELECT DISTINCT r1.OtherID
FROM MyTable r1 INNER JOIN MyTable r2 USING (MainID)
WHERE r1.Status = 1
AND r1.Stage = 4
AND r1.Invisible = 0
AND r2.OtherID = 2779262
AND r2.Stage = 0
AND r2.Invisible = 0
Qu’en dit le QEP ?
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: r2 type: ref possible_keys: OtherIDStageStatusInvisibleMainID, autre index… key: OtherIDStageStatusInvisibleMainID key_len: 5 ref: const,const rows: 234 Extra: Using where; Using index; Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: r1 type: ref possible_keys: MainIDOtherIDStatus,StageMainIDOtherID key: MainIDOtherIDStatus key_len: 4 ref: MyTable.r2.MainID rows: 1 Extra: Using where
234 lignes à analyser au lieu de 155316386. Ça à l’air meilleur 🙂
Un index couvrant en prime (Extra: Using index), c’est toujours bon à prendre.
Niveau status variables
| Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | | Handler_read_first | 0 | | Handler_read_key | 236 | | Handler_read_last | 0 | | Handler_read_next | 705 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 8 | | Handler_write | 7 |
Avec beaucoup moins de ligne analysées, le nombre de Handler_read_next est extrêmement bas par rapport à la la requête précédente: 705 au lieu de 151576920. On a donc un faible nombre de lecture lors parcours transverse de l’arbre d’index. En clair, c’est plutôt une bonne chose.
Le temps d’exécution de la requête à froid est: 7 rows in set (1.17 sec)
A mettre en perspective avec les 45 minutes de la première version…
Temps d’exécution unitaire (w/ mysqlslap)
Minimum number of seconds to run all queries: 0.001 seconds
Le faisceau d’indice valide bien un “meilleur” (c’est un euphémisme) temps d’exécution minimal unitaire (0.001 s vs 478.936 s)
Je pense que l’on va garder cette version de la requête pour la prod :).
Alors que faut il en tirer comme conclusion ?
Il est légitime de se poser la question de proscrire ou pas les sous-requêtes si on n’est pas en MySQL 5.6+ ou MariaDB 5.5+
Je conseillerai surtout de toujours tester ses requêtes avant de les pousser en prod… notamment en la passant au révélateur EXPLAIN.
En effet dans le cas de la 1ère requête, rows: 155316386, est un bon indicateur de “ça risque de partir en couille !”.
La requête peut également être réécrite de la manière suivante:
SELECT DISTINCT OtherID
FROM
(
SELECT MainID
FROM MyTable
WHERE Invisible = 0
AND Stage = 0
AND OtherID = 2779262
) dt
INNER JOIN MyTable r
WHERE r.MainID = dt.MainID
AND r.Invisible = 0
AND r.Stage = 4
AND r.Status = 1
oh la belle sous requête ! (ou table dérivée pour les puristes)
Qu’en dit le QEP ?
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 235 Extra: Using temporary *************************** 2. row *************************** id: 1 select_type: PRIMARY table: r type: ref possible_keys: MainIDOtherIDStatus,StageMainIDOtherID key: MainIDOtherIDStatus key_len: 4 ref: dt.MainID rows: 1 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DERIVED table: MyTable type: ref possible_keys: OtherIDStageStatusInvisibleMainID, autre index… key: OtherIDStageStatusInvisibleMainID key_len: 5 ref: rows: 234 Extra: Using where; Using index
54990 (235 x 1 x 234) lignes à analyser + index couvrant. Ça n’a pas l’air trop mal tout ça !
Cette requête combine sous-requête + jointure et jusqu’ici… même pas peur.
Niveau status variables
| Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 2 | | Handler_read_first | 0 | | Handler_read_key | 236 | | Handler_read_last | 0 | | Handler_read_next | 705 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 244 | | Handler_write | 242 |
Les indications bas niveau fournit par les handlers sont assez proche de celles fournit par l’exécution de la version jointure seule, de la requête.
Seules les informations (Created_tmp_tables, Handler_read_rnd_next, Handler_write) liées aux 2 tables temporaires créées (1 pour la table dérivée + 1 pour le DISTINCT) sont différentes.
En(un peu plus) clair: Handler_write = 235 lignes écrites dans la table dérivée + 7 lignes du résultat final après dédoublonnage (DISTINCT) = 242
Ces 2 tables étant suffisamment petite pour tenir entièrement en mémoire (Created_tmp_disk_tables=0) le surcoût devrait être négligeable par rapport à la la version jointure seule de la requête.
Le temps d’exécution de cette requête à froid est: 7 rows in set (1.23 sec)
Il est équivalent à la jointure.
Temps d’exécution unitaire (w/ mysqlslap)
Minimum number of seconds to run all queries: 0.001 seconds
On retrouve bien des temps d’exécution équivalent au millième près. CQFD
Il y a souvent plusieurs façon d’écrire une requête, mais elles sont rarement équivalentes en matière de temps d’exécution. La commande EXPLAIN fait partie du package minimal que tout développeur doit connaître. Ajouter à cela un environnement de test et un zeste de bon sens et il devient alors possible d’éliminer une partie des soucis de prod et d’éviter de perdre du temps inutilement.
Divers
-------------- mysql Ver 14.14 Distrib 5.5.24, for Linux (x86_64) using readline 5.1 Server version: 5.5.24-55-log Percona Server (GPL), Release 26.0 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 --------------
CREATE TABLE MyTable ( MyTableID int(11) NOT NULL AUTO_INCREMENT, MainID int(11) NOT NULL DEFAULT '0', OtherID int(11) NOT NULL DEFAULT '0', Stage tinyint(4) NOT NULL DEFAULT '0', Status int(11) DEFAULT '0', Invisible tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (MyTableID), KEY OtherIDStageStatusInvisible (OtherID,Stage,Status,Invisible), KEY OtherIDStatusInvisible (OtherID,Status,Invisible), KEY MainIDOtherIDStatus (MainID,OtherID,Status), KEY OtherIDStageStatusInvisibleMainID (OtherID,Stage,Status,Invisible,MainID), KEY StageMainIDOtherID (Stage,MainID,OtherID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
*************************** 1. row *************************** Name: MyTable Engine: InnoDB Version: 10 Row_format: Compact Rows: 310632909 Avg_row_length: 52 Data_length: 16334700544 Index_length: 49877909504 Data_free: 7340032 Collation: utf8_swedish_ci
Architecte Solution Cloud chez Oracle
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
Avec la clause DISTINCT dans la sous-requête (si valeurs redondantes), ça va toujours beaucoup mieux !
On peut aussi envisager un ORDER BY dans la sous-requête, non ?
Salut Denis,
effectivement avec la clause DISTINCT on peut réduire le result set de la sous-requête. La contrepartie est la création d’une table temporaire qui, avec un gros volume de données, peut coûter des I/O disques.
Par contre d’un point de vu perf, je ne comprend pas très bien l’intérêt de l’ORDER BY dans la sous-requête ?
Bonjour
avez vous fait le test en limitant les données de la sous-requête par rapport aus données de la requête principale
ex :
SELECT DISTINCT OtherID
FROM MyTable
WHERE Status = 1
AND Stage = 4
AND Invisible = 0
AND MainID IN
(
SELECT SubQueryMyTable.MainID
FROM MyTable as SubQueryMyTable
WHERE OtherID = 2779262
AND Stage = 0
AND Invisible = 0
and SubQueryMyTable.MainID = MyTable.MainID
)
Sur certain sqgbd cela améliore beaucoup les performances
Hello,
c’est une autre manière de faire.
On pourrait partir aussi sur
SELECT DISTINCT OtherID
FROM MyTable
WHERE Status = 1
AND Stage = 4
AND Invisible = 0
AND EXISTS
(
SELECT SubQueryMyTable.MainID
FROM MyTable as SubQueryMyTable
WHERE OtherID = 2779262
AND Stage = 0
AND Invisible = 0
and SubQueryMyTable.MainID = MyTable.MainID
)
Le problème est alors quand l’on manipule des sous-requêtes corrélées. Et sur de grosses tables ça donne de mauvaises performances.
En d’autres termes, les temps d’exécutions des ces 2 requêtes et celui de la requête problématique originale sont équivalents.
Excellent post Daz:)
Les sous requêtes dans MariaDB c’est depuis la version 5.3 l’équivalent du code base MySQL 5.1 avec les performances de MySQL 5.5 et le backport des algo de MySQL 6.0 (sous requêtes et block based joins), depuis MariaBD 5.5 encore plus de cas sont couverts comme les sous requêtes dans les updates ou encore avec des index couvrants contenants des valeurs null, en MariADB 10, les histogrammes permettent de qualifier le coût de la sous requête meme sur les colones non indéxées https://kb.askmonty.org/en/engine-independent-table-statistics/ , et enfin dynamic predicates permette de changer la stratégie d’optimisation pendant l’execution de la requête ( Q20 TPC-H) https://mariadb.atlassian.net/browse/MDEV-83
Avec un index sur la requête principale portant sur les valeurs renvoyées par la sous-requête !
Je suis curieux de savoir ce que donnerait le meme plan optimisé en join_level 6 ou 8 ? Histoire de savoir si le BKA JOIN produit de bons résultats en mémoire en comparaison du adaptive hash index de InnoDB, j’ai vu des perfs *4 sur des gros ranges, ici avec 250 records sur la première passe je pense que le coût d’allocation du join buffer ne permet pas d’être compensé par la performance du MRR
Que de bonnes questions Steph 🙂
Du coup je vais faire un autre article en réponse, rien que pour toi 😉
++
[…] Comme promit, voici la suite de l’article Utiliser une sous-requête c’est mal ? […]