Jointure vs sous-requête
MySQL est connu pour ne pas être très performant avec les sous-requêtes. Ce n’est pas faux, et d’ailleurs c’est encore le cas avec MySQL 5.5. Le contournement consiste en général à réécrire la requête, certaines sous-requêtes pouvant être aisément réécrite en jointure.
C’est le cas de
SELECT a FROM T1 WHERE col IN (SELECT col FROM T2…) qui se transforme en
SELECT distinct a FROM T1 INNER JOIN T2 ON TI.col=T2.col WHERE …
Avec un exemple concret, il est possible de faire les constatations suivantes.
Serveur : MySQL: 5.5.22 Community Server (GPL)
Requête 1 :
|
1 |
SELECT count(*) FROM customer |
|
1 |
WHERE id_cutomer IN |
|
1 |
( |
|
1 |
SELECT id_cutomer FROM customer |
|
1 |
WHERE phone like '06%' |
|
1 |
); |
Requête 2 (requête 1 réécrite avec une jointure) :
|
1 2 3 |
SELECT count(*) FROM customer c1 INNER JOIN customer c2 USING(id_cutomer) WHERE c2.phone like '06%'; |
P.S. Le critère de l’auto-jointure étant sur toutes les parties de l’index unique (la clé primaire), il n’ est donc pas nécessaire de déboublonner avec DISTINCT (ou GROUP BY).
Après benchs, l’écart entre ses 2 requêtes est d’environ 7 %. Dans la vraie vie, l’écart peut aller de quelques secondes à une requête qui ne rend jamais la main…
La lecture du plan d’exécution (QEP) donné par la commande EXPLAIN, n’explique pas de manière flagrante la différence de performance.
Requête 1 :
|
1 |
*************************** 1. row *************************** |
|
1 |
id: 1 |
|
1 |
select_type: PRIMARY |
|
1 |
table: customer |
|
1 |
<strong>type: index</strong> |
|
1 |
possible_keys: NULL |
|
1 |
<strong>key: idx_lname</strong> |
|
1 |
key_len: 50 |
|
1 |
ref: NULL |
|
1 |
<strong>rows: 700021</strong> |
|
1 |
<strong>Extra: Using where; Using index</strong> |
|
1 |
*************************** 2. row *************************** |
|
1 |
id: 2 |
|
1 |
select_type: <strong>DEPENDENT SUBQUERY</strong> |
table: customer
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
Requête 2 :
|
1 |
*************************** 1. row *************************** |
id: 1
select_type: SIMPLE
table: c1
type: index
possible_keys: PRIMARY
key: idx_lname
key_len: 50
ref: NULL
rows: 700021
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: customer.c1.id_cutomer
rows: 1
Extra: Using where
Qu’en est il de la version 5.6 de MySQL ?
Cette dernière mouture apporte une ribambelle d’amélioration, notamment en ce qui concerne l’optimisation des sous-requêtes…
Serveur : MySQL: 5.6.5-m8 MySQL Community Server (GPL)
Les QEPs respectifs sont
|
1 |
*************************** 1. row *************************** |
id: 1
select_type: SIMPLE
table: customer
type: index
possible_keys: PRIMARY
key: idx_lname
key_len: 50
ref: NULL
rows: 699628
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: customer.customer.id_cutomer
rows: 1
Extra: Using where
et
|
1 |
*************************** 1. row *************************** |
id: 1
select_type: SIMPLE
table: c1
type: index
possible_keys: PRIMARY
key: idx_lname
key_len: 50
ref: NULL
rows: 699628
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: customer.c1.id_cutomer
rows: 1
Extra: Using where
Oui, oui vous avez bien lu, ils sont bien (quasiment) identique !
En détaillant le QEP de la sous-requête (avec EXPLAIN EXTENDED), on constate que l’optimiseur à (enfin) transformé la sous-requête en jointure :
show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select count(0) AS count(*) from customer.customer join customer.customer where ((customer.customer.id_cutomer = customer.customer.id_cutomer) and (customer.customer.phone like ‘06%’))
C’est une bien bonne nouvelle !
Malheureusement MySQL 5.6 n’est pas encore en GA (fin d’année à priori selon mes sources). Mais tout n’est pas perdu ! (ça s’est du suspens :D).
Quelques chiffres
Un « quick bench », avec mysqlslap
Requête 1 :
mysqlslap –create-schema=test -q »SELECT count(*) FROM customer WHERE id_cutomer IN (SELECT id_cutomer FROM cust WHERE phone like ‘06%’); » -i200
Requête 2 :
mysqlslap –create-schema=test -q »SELECT count(*) FROM customer c1 INNER JOIN customer c2 USING(id_cutomer) WHERE c2.phone like ‘06%’; » -i200
MySQL 5.5
Requête 1 :
Minimum number of seconds to run all queries: 0.923 seconds
Requête 2 :
Minimum number of seconds to run all queries: 0.844 seconds
MySQL 5.6
Requête 1 :
Minimum number of seconds to run all queries: 0.974 seconds
Requête 2 :
Minimum number of seconds to run all queries: 0.979 seconds
Divers
|
1 |
CREATE TABLE `customer` ( |
|
1 |
`id_cutomer` int(11) NOT NULL AUTO_INCREMENT, |
|
1 |
`fname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '', |
|
1 |
`lname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '', |
|
1 |
`street_1` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL, |
|
1 |
`city` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL, |
|
1 |
`state` char(2) COLLATE utf8_swedish_ci DEFAULT NULL, |
|
1 |
`zip` varchar(9) COLLATE utf8_swedish_ci DEFAULT NULL, |
|
1 |
`phone` varchar(16) COLLATE utf8_swedish_ci DEFAULT NULL, |
|
1 |
`since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
|
1 |
`divers` text COLLATE utf8_swedish_ci, |
|
1 |
PRIMARY KEY (`id_cutomer`), |
|
1 |
KEY `idx_lname` (`lname`), |
|
1 |
KEY `idx_city` (`city`), |
|
1 |
KEY `idx_fname` (`fname`) |
|
1 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci |
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
—–