一次SQL优化经历

ゝ一世哀愁。 2022-12-27 08:43 217阅读 0赞

**需求**  
目的是为了查询每一个科室（`department_code`）的数据总数和每一个科室的`flag_antimicrobial`为1,3,4的数据总数。  
由于查询数据总数不需要加条件，而查询`flag_antimicrobial`为1,3,4的需要加条件，故最开始相处如下SQL

**原SQL**

select m.deptName,
           m.deptCode,
           IFNULL(m.totalUsage, '0')                            totalUsage,
           IFNULL(m.antiUsage, '0')                             antiUsage,
           concat(round(m.antiUsage / m.totalUsage, 2), '%') as usageRatio
    from (select t1.department_name                             as deptName,
                 t1.department_code                             as deptCode,
                 count(distinct t1.visit_num)                   as totalUsage,
                 (select count(distinct t.visit_num)
                  from recipe_item_dispatch t
                  where t.flag_antimicrobial in ('1', '3', '4')
                    and t.department_code = t1.department_code) as antiUsage
          from recipe_item_dispatch t1
          group by t1.department_code) m
    LIMIT 0,10;

由于含有`t.department_code = t1.department_code`这个条件，子查询和外部查询是一个笛卡尔积，所以这条SQL奇慢无比。后来优化为以下SQL

**优化后**

select deptName, deptCode, totalUsage, antiUsage, concat(round(antiUsage / totalUsage, 2), '%') as usageRatio
    from (
             select t.department_name as                                                                   deptName,
                    t.department_code as                                                                   deptCode,
                    count(distinct t.visit_num)                                                            totalUsage,
                    count(distinct case when t.flag_antimicrobial in ('1', '3', '4') then t.visit_num end) antiUsage
             from recipe_item_dispatch t
             group by t.department_code) as m;