mysql小结果集驱动大结果集_sql优化之以小结果集驱动大结果集

对于关联查询，我们必须遵循一条规则，以小结果集驱动大结果集

来一条语句：

SELECT DISTINCT

bbl.lead_Id

FROM

`buyer$buying_lead` bbl,

`supplier$product` sp,

`supplier$package_record` spr,

`supplier$user` su

WHERE

sp.`supplier_id` = su.supplier_id

AND su.company_id = spr.`company_id`

AND bbl.`category_id` = sp.`category_id`

AND bbl.source <> '002'

AND bbl.`status` = 9

AND spr.`package_id` > 1

AND spr.`expire_time` > now()

AND spr.`current_package` = 1

AND TIMESTAMPDIFF(hour,bbl.modify_time,NOW())<=24

ORDER BY bbl.modify_time desc;

这条语句执行时间竟然时间长达40s,分解下语句

selectcount(*) from `buyer$buying_lead` bbl where bbl.`status` = 9

AND bbl.source <> '002'   AND

TIMESTAMPDIFF(hour,bbl.modify_time,NOW())<=24---结果集竟然只有10多行

select bbl.lead_Id from `buyer$buying_lead` bbl where bbl.`status` = 9 AND bbl.source <> '002'   AND TIMESTAMPDIFF(hour,bbl.modify_time,NOW())<=24 ----执行速度很快

SELECT count(*) from `supplier$package_record` spr where  spr.`package_id` > 1  AND spr.`current_package` = 1   AND spr.`expire_time` > now()---也只有1000行

SELECT    bbl.lead_Id  FROM   `buyer$buying_lead` bbl,     `supplier$product` sp,     `supplier$package_record` spr,    `supplier$user` su             WHERE   sp.`supplier_id` = su.supplier_id     AND su.company_id = spr.`company_id`   AND bbl.`category_id` = sp.`category_id`    AND bbl.source <> '002'      AND bbl.`status` = 9      AND spr.`package_id` > 1     AND spr.`expire_time` > now()    AND spr.`current_package` = 1      AND TIMESTAMPDIFF(hour,bbl.modify_time,NOW())<=24;----这语句需要40s，说明排序不是影响sql执行缓慢的主要原因，于是怀疑关联的顺序是否有问题

explain sql语句

优化器竟然选择了sp作为驱动表，那么我们就强行指定关联顺序straight_join

SELECT    bbl.lead_Id  FROM   `buyer$buying_lead` bbl straight_join     `supplier$product` sp,     `supplier$package_record` spr,    `supplier$user` su             WHERE   sp.`supplier_id` = su.supplier_id     AND su.company_id = spr.`company_id`   AND bbl.`category_id` = sp.`category_id`    AND bbl.source <> '002'      AND bbl.`status` = 9      AND spr.`package_id` > 1     AND spr.`expire_time` > now()    AND spr.`current_package` = 1      AND TIMESTAMPDIFF(hour,bbl.modify_time,NOW())<=24  ORDER BY bbl.modify_time desc;

执行竟然只有0.31s，跟前没优化相比，不是一个数量级的

附上执行计划：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30221425/viewspace-1799967/，如需转载，请注明出处，否则将追究法律责任。