我有以下查询，它们都返回相同的结果和行数:

select * from (
               select UNIX_TIMESTAMP(network_time) * 1000 as epoch_network_datetime, 
                      hbrl.business_rule_id, 
                      display_advertiser_id, 
                      hbrl.campaign_id, 
                      truncate(sum(coalesce(hbrl.ad_spend_network, 0))/100000.0, 2) as demand_ad_spend_network, 
                      sum(coalesce(hbrl.ad_view, 0)) as demand_ad_view, 
                      sum(coalesce(hbrl.ad_click, 0)) as demand_ad_click, 
                      truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else 100*sum(hbrl.ad_click)/sum(hbrl.ad_view) end, 0), 2) as ctr_percent, 
                      truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else sum(hbrl.ad_spend_network)/100.0/sum(hbrl.ad_view) end, 0), 2) as ecpm,
                      truncate(coalesce(case when sum(hbrl.ad_click) = 0 then 0 else sum(hbrl.ad_spend_network)/100000.0/sum(hbrl.ad_click) end, 0), 2) as ecpc 
               from hourly_business_rule_level hbrl
               where (publisher_network_id = 31534) 
               and network_time between str_to_date('2017-08-13 17:00:00.000000', '%Y-%m-%d %H:%i:%S.%f') and str_to_date('2017-08-14 16:59:59.999000', '%Y-%m-%d %H:%i:%S.%f') 
               and (network_time IS NOT NULL and display_advertiser_id > 0)
               group by network_time, hbrl.campaign_id, hbrl.business_rule_id
               having demand_ad_spend_network > 0
               OR demand_ad_view > 0
               OR demand_ad_click > 0
               OR ctr_percent > 0
               OR ecpm > 0
               OR ecpc > 0
               order by epoch_network_datetime) as atb
       left join dim_demand demand on atb.display_advertiser_id = demand.advertiser_dsp_id 
       and atb.campaign_id = demand.campaign_id 
       and atb.business_rule_id = demand.business_rule_id 

运行 explain extended，结果如下:

+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+
| id | select_type | table                      | type | possible_keys                                                                 | key     | key_len | ref             | rows    | filtered | Extra                                        |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>                 | ALL  | NULL                                                                          | NULL    | NULL    | NULL            | 1451739 |   100.00 | NULL                                         |
|  1 | PRIMARY     | demand                     | ref  | PRIMARY,join_index                                                            | PRIMARY | 4       | atb.campaign_id |       1 |   100.00 | Using where                                  |
|  2 | DERIVED     | hourly_business_rule_level | ALL  | _hourly_business_rule_level_supply_idx,_hourly_business_rule_level_demand_idx | NULL    | NULL    | NULL            | 1494447 |    97.14 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+

另一个是:

select UNIX_TIMESTAMP(network_time) * 1000 as epoch_network_datetime, 
       hbrl.business_rule_id, 
       display_advertiser_id, 
       hbrl.campaign_id, 
       truncate(sum(coalesce(hbrl.ad_spend_network, 0))/100000.0, 2) as demand_ad_spend_network, 
       sum(coalesce(hbrl.ad_view, 0)) as demand_ad_view, 
       sum(coalesce(hbrl.ad_click, 0)) as demand_ad_click, 
       truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else 100*sum(hbrl.ad_click)/sum(hbrl.ad_view) end, 0), 2) as ctr_percent, 
       truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else sum(hbrl.ad_spend_network)/100.0/sum(hbrl.ad_view) end, 0), 2) as ecpm, 
       truncate(coalesce(case when sum(hbrl.ad_click) = 0 then 0 else sum(hbrl.ad_spend_network)/100000.0/sum(hbrl.ad_click) end, 0), 2) as ecpc 
from hourly_business_rule_level hbrl
join dim_demand demand on hbrl.display_advertiser_id = demand.advertiser_dsp_id 
and hbrl.campaign_id = demand.campaign_id 
and hbrl.business_rule_id = demand.business_rule_id 
where (publisher_network_id = 31534) 
and network_time between str_to_date('2017-08-13 17:00:00.000000', '%Y-%m-%d %H:%i:%S.%f') and str_to_date('2017-08-14 16:59:59.999000', '%Y-%m-%d %H:%i:%S.%f') 
and (network_time IS NOT NULL and display_advertiser_id > 0)
group by network_time, hbrl.campaign_id, hbrl.business_rule_id
having demand_ad_spend_network > 0
OR demand_ad_view > 0
OR demand_ad_click > 0 
OR ctr_percent > 0
OR ecpm > 0
OR ecpc > 0
order by epoch_network_datetime;

这是第二个查询的结果:

+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+
| id | select_type | table                      | type | possible_keys                                                                 | key     | key_len | ref                                                           | rows    | filtered | Extra                                        |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | hourly_business_rule_level | ALL  | _hourly_business_rule_level_supply_idx,_hourly_business_rule_level_demand_idx | NULL    | NULL    | NULL                                                          | 1494447 |    97.14 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | demand                     | ref  | PRIMARY,join_index                                                            | PRIMARY | 4       | my6sense_datawarehouse.hourly_business_rule_level.campaign_id |       1 |   100.00 | Using where; Using index                     |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+

第一个大约需要 2 秒，而第二个需要 2 多分钟!

为什么第二个查询用了这么长时间？ 我在这里错过了什么？

谢谢。

最佳答案

只要子查询显着减少之前的行数，就使用子查询 - ANY JOIN - 始终加强 Rick James Plan B。 加强您已经记录的 Rick & Paul 的回答。 Rick 和 Paul 的回答值得接受。

关于Mysql子查询比join快多了，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45692447/