EXPLAIN EXTENDED
1先执行 EXPLAIN EXTENDED
2 show warnings;
EXPLAIN EXTENDED
SELECT*FROM
`receivable_application_relation` d
WHERE d.`application_id` IN
(SELECT
id
FROM
application
WHERE application_code IN (‘ZYHT20190305192721001‘,‘ZYHT20190309152839001‘,‘ZYHT20190309152904001‘));
SHOW WARNINGS;
查看优化后最终执行的sql
In 并不是走的子查询 而是 join ,这个是 从上面的画红线的地方copy 出来的
SELECT
`cf_receivables_financing`.`d`.`id` AS `id`,
`cf_receivables_financing`.`d`.`application_id` AS `application_id`,
`cf_receivables_financing`.`d`.`receivable_id` AS `receivable_id`,
`cf_receivables_financing`.`d`.`update_time` AS `update_time`,
`cf_receivables_financing`.`d`.`create_time` AS `create_time`,
`cf_receivables_financing`.`d`.`deleted` AS `deleted`
FROM
`cf_receivables_financing`.`application`
JOIN `cf_receivables_financing`.`receivable_application_relation` `d`
WHERE (
(
`cf_receivables_financing`.`d`.`application_id`=`cf_receivables_financing`.`application`.`id`
)
AND (
`cf_receivables_financing`.`application`.`application_code` IN (‘ZYHT20190305192721001‘,‘ZYHT20190309152839001‘,‘ZYHT20190309152904001‘)
)
)
EXPLAIN EXTENDED
SELECT
d.*FROM
`receivable_application_relation` d
INNER JOIN application a
ON d.`application_id`=a.id
WHERE a.application_code IN (‘ZYHT20190305192721001‘,‘ZYHT20190309152839001‘,‘ZYHT20190309152904001‘);
查看优化后的sql
SELECT
`cf_receivables_financing`.`d`.`id` AS `id`,
`cf_receivables_financing`.`d`.`application_id` AS `application_id`,
`cf_receivables_financing`.`d`.`receivable_id` AS `receivable_id`,
`cf_receivables_financing`.`d`.`update_time` AS `update_time`,
`cf_receivables_financing`.`d`.`create_time` AS `create_time`,
`cf_receivables_financing`.`d`.`deleted` AS `deleted`
FROM
`cf_receivables_financing`.`receivable_application_relation` `d`
JOIN `cf_receivables_financing`.`application` `a`
WHERE (
(
`cf_receivables_financing`.`d`.`application_id`=`cf_receivables_financing`.`a`.`id`
)
AND (
`cf_receivables_financing`.`a`.`application_code` IN (‘ZYHT20190305192721001‘,‘ZYHT20190309152839001‘,‘ZYHT20190309152904001‘)
)
);
原文:https://www.cnblogs.com/fanBlog/p/11943748.html