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: viaduc.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
), mais là encore je n’ai pas trouvé mieux que de passer des entretiens. Ça donne une bonne idée de ce que le marché peut payer pour un poste précis. Et puis si vous n’êtes pas satisfait par votre salaire (ou autre) rien ne vous empêche d’aller voir ailleurs par la suite.
