实习经历之sql优化

来公司实习不久，被安排的工作是接口开发及联调，遇到了一个离职员工的一个遗留问题，关于多表查询的sql优化，关于多表联查的优化我为此写过一篇文章多表联查优化_猴子与打字机的博客-CSDN博客，提供以下几种处理方案

• 优化sql语句
• 索引优化
• 反范式设计
• 业务代码优化
• 使用缓存

 正题：

原sql

select e.cgi                       AS enci,
       e.lon,
       e.lat,
       x.rrc_userconnmean,
       x.traffic_actusermean,
       x.total_traffic,
       round(y.mr_rat::numeric, 4) as mr_rat,
       z.is_zero
from dim_cell_config e
         left join
     (select b.pm_id                                                               AS enci,
             b.rrc_userconnmean,
             b.traffic_actusermean,
             (b.pdcp_cpoctul + b.pdcp_sduoctul + b.pdcp_cpoctdl + b.pdcp_sduoctdl) AS total_traffic
      from dwd_lte_pm_pc b
      where b.pm_id in
            (select a.cgi
             from dim_cell_config a
             where a.county_id = '450110'
        and a.net_type = '4G') and b.start_time = '2022-07-01 00:00:00'
                and b.period_type = 2
                and b.region_type = 1) x
on e.cgi = x.enci
    left join
    (select d.enci, d.mr_rat
    from dwd_cover_region_kpi d
    where d.enci in
    (select a.enci
    from dim_cell_config a
    where a.county_id = '450110'
    and a.net_type = '4G')
    and d.start_time = '2022-07-01 00:00:00'
    and d.period_type = 2
    and d.region_type = 1) y on e.enci = y.enci
    left join
    (select f.oid, case when count(*) > 0 then true else false end is_zero
    from abs_zreo_business_4g f
    where f.p_date = to_char('2022-07-01 00:00:00'::date, 'YYYYMMDD')
    and f.ecgi in
    (select a.enci
    from dim_cell_config a
    where a.county_id = '450110'
    and a.net_type = '4G')
    group by f.oid
    ) z on e.cgi = z.oid
where e.county_id = '450110'
  and e.net_type = '4G';

explain 之后，看到查询时间800ms

 开始改进

第一步优化dim_cell_config

explain analyse
select a.cgi
from dim_cell_config a
where a.county_id = '450110'
  and a.net_type = '4G';

无索引：97ms

创建覆盖索引后：1ms

create index on dim_cell_config(county_id,net_type,cgi,lon,lat);

 第二步优化dwd_lte_pm_pc

explain analyse
select b.pm_id   AS enci,
       b.rrc_userconnmean,
       b.traffic_actusermean,
       (b.pdcp_cpoctul + b.pdcp_sduoctul + b.pdcp_cpoctdl + b.pdcp_sduoctdl) AS total_traffic
from dwd_lte_pm_pc b
where b.pm_id in
      (select a.cgi
       from dim_cell_config a
       where a.county_id = '450110'
         and a.net_type = '4G')
  and b.period_type = 2
  and b.region_type = 1
  and b.start_time = '2022-07-06 00:00:00';

子查询无索引：843ms 

子查询有索引，其他无索引390ms 

创建了覆盖索引44ms 

create index on
    dwd_lte_pm_pc_20220706 (start_time, pm_id, period_type,region_type, rrc_userconnmean,
                            traffic_actusermean, pdcp_cpoctul, pdcp_sduoctul, pdcp_cpoctdl, pdcp_sduoctdl);

优化in子查询为覆盖索引+left join连接查询38ms

create index on dwd_lte_pm_pc_20220706 (start_time, pm_id, period_type, region_type,rrc_userconnmean,traffic_actusermean,pdcp_cpoctul,pdcp_sduoctul,pdcp_cpoctdl,pdcp_sduoctdl);

第三步，改造dim_cell_config与dwd_cover_region_kpi联查部分

原始状态400ms

修改sql语句，in变为连接查询

添加覆盖索引48ms

 第四步，改造abs_zreo_business_4g

原状态117ms 

将in子查询改为覆盖索引+关联查询4ms 

最后

以上就是酷酷小土豆为你收集整理的实习经历之sql优化的全部内容，希望文章能够帮你解决实习经历之sql优化所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。