需要帮助来优化此sql查询吗

p1iqtdky  于 2021-06-24  发布在  Hive
关注(0)|答案(3)|浏览(340)

我需要一些帮助来优化这个sql查询。这工作完全正常。我只想减少这个查询的运行时间

select distinct 
o.usrp_order_number,t.* 
from ms_bvoip_order_extension oe
 inner join ms_order o on oe.ms_order_id = o.ms_order_id
 inner join ms_sub_order so on so.ms_order_id = o.ms_order_id
 inner join ms_job j on j.entity_id = so.ms_sub_order_id
  left join mstask t ON t.wf_job_id = j.wf_job_id
  where
  o.order_type = 900
  and o.entered_date between date_sub(current_date(),53) and
 date_sub(current_date(),3)
  and j.entity_type = 5 and t.name RLIKE 'Error|Correct|Create AOTS Ticket' and t.wf_job_id is not null
  order by
  o.usrp_order_number
8aqjt8rx

8aqjt8rx1#

您需要为筛选所依据的列添加索引。
我们不知道你们每个table上有多少张唱片,但是 t.name RLIKE 标准应作为最后一项进行评估。我将根据以下想法重写您的查询:

select ...
from
(
    select ...
    inner join ...
    inner join ...
    inner join ...
    left join ...
    where ...
) temporary
where temporary.somename RLIKE 'Error|Correct|Create AOTS Ticket'
o.usrp_order_number

如果查询不是很动态,那么您甚至可以将结果缓存一段时间。

yqkkidmi

yqkkidmi2#

在hive中连接之后执行where条件(尽管cbo和ppd可能会更改此行为),最好研究这两个查询的解释输出。您可以这样移动条件: o.order_type = 900 到join on子句以减少join上的行。在配置单元的join on子句中只允许包含两个表列的非equi条件。表t也是左连接的,但是 where : t.name RLIKE 'Error|Correct|Create AOTS Ticket' and t.wf_job_id is null and t.ORIGINAL_START_DATE is not null 将左连接变换为内连接。检查您需要内部连接还是左侧连接

select distinct 
o.usrp_order_number,t.* 
from ms_bvoip_order_extension oe
 inner join ms_order o 
    on oe.ms_order_id = o.ms_order_id
       and o.order_type = 900
       and and o.entered_date between date_sub(current_date(),53) and date_sub(current_date(),3)                 
 inner join ms_sub_order so on so.ms_order_id = o.ms_order_id
 inner join ms_job j on j.entity_id = so.ms_sub_order_id 
                    and j.entity_type = 5
 left join mstask t on t.wf_job_id = j.wf_job_id 
                    and t.name RLIKE 'Error|Correct|Create AOTS Ticket' 
                    and t.wf_job_id is null
                    and t.ORIGINAL_START_DATE is not null 
order by o.usrp_order_number

请阅读以下关于配置设置的回答:https://stackoverflow.com/a/48487306/2700344

y0u0uwnf

y0u0uwnf3#

确保你有适当的索引
表ms\ order输入\日期、订单\类型、ms\ order\ id列的复合索引
表ms\u job实体类型、实体id列的复合索引
表mstask wf\u job\u id、original\u start\u date列的复合索引
表ms\u sub\u order列ms\u order\u id的索引
表ms\u bvoip\u order\u扩展和ms\u order\u id列的索引

相关问题