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 :
SELECT count(*) FROM customer
WHERE id_cutomer IN
(
SELECT id_cutomer FROM customer
WHERE phone like '06%'
);
Requête 2 (requête 1 réécrite avec une jointure) :
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. row ***************************
id: 1
select_type: PRIMARY
table: customer
type: index
possible_keys: NULL
key: idx_lname
key_len: 50
ref: NULL
rows: 700021
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: customer
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
Requête 2 :
*************************** 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. 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. 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
CREATE TABLE `customer` (
`id_cutomer` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
`lname` varchar(16) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
`street_1` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL,
`city` varchar(20) COLLATE utf8_swedish_ci DEFAULT NULL,
`state` char(2) COLLATE utf8_swedish_ci DEFAULT NULL,
`zip` varchar(9) COLLATE utf8_swedish_ci DEFAULT NULL,
`phone` varchar(16) COLLATE utf8_swedish_ci DEFAULT NULL,
`since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`divers` text COLLATE utf8_swedish_ci,
PRIMARY KEY (`id_cutomer`),
KEY `idx_lname` (`lname`),
KEY `idx_city` (`city`),
KEY `idx_fname` (`fname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=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
—–