请求优化指定SQL脚本以提升性能：附待优化代码

嘿，我来帮你梳理下这个SQL脚本的优化思路，先提个小细节——你的脚本里有两处笔误：mo.session_id应该是mp.session_id，do.log_time应该是fo.log_time吧？先修正这个再谈优化，不然脚本执行会报错哦~修正后的脚本如下：

With accounts As (
 select account_id, creation_date from account where program_distributor = 'brinks' and channel = 'online' and creation_year = 2017
),
Form_opens as (
 select session_id, log_time from web_action_log where web_action = 'open_dd_form'
),
Mapping as (
 select session_id, account_id from web_link
)
Select trunc (acc.creation_date), count(distinct acc.account_id), count (distinct mp.account_id)
from accounts acc
left outer join mapping mp on acc.account_id = mp.account_id
Left outer join form_opens fo on mp.session_id = fo.session_id and acc.creation_date > fo.log_time
Group by trunc(acc.creation_date)
Order by 1;

接下来是具体的优化建议，都是实际工作中常用的手段：

1. 给核心表加针对性索引 #

索引是提升查询性能的重中之重，针对你的过滤条件和关联逻辑，建议创建这些索引：

• 给account表建复合索引，覆盖过滤条件和需要返回的字段：

  -- Oracle用INCLUDE，MySQL直接把后面的字段加到索引列里
  CREATE INDEX idx_account_brinks_online_2017 ON account (program_distributor, channel, creation_year) INCLUDE (account_id, creation_date);
  

• 给web_action_log表建索引，快速筛选出表单打开的日志：

  CREATE INDEX idx_webaction_openform ON web_action_log (web_action) INCLUDE (session_id, log_time);
  

• 给web_link表建索引，方便通过账号ID关联会话ID：

  CREATE INDEX idx_weblink_account_session ON web_link (account_id, session_id);
  

2. 优化CTE的使用（视数据库而定） #

不同数据库对CTE（公用表表达式）的优化能力不一样，比如MySQL 8.0+虽然支持CTE，但优化效果不如内联子查询；而Oracle对CTE的优化相对较好。如果你的查询数据量很大，可以试试：

• 把CTE改成内联子查询，或者
• 对数据量较大的CTE创建物化视图（比如Oracle的MATERIALIZED VIEW），提前计算好结果。

3. 简化COUNT(DISTINCT)操作 #

COUNT(DISTINCT)是比较耗时的，尤其是数据量大的时候，能简化就尽量简化：

• 第一个count(distinct acc.account_id)其实可以直接改成count(acc.account_id)——毕竟account表的account_id应该是主键或者唯一值吧？不需要额外去重，这样能省不少计算量。
• 第二个count(distinct mp.account_id)，如果web_link里一个账号对应多个会话ID，那去重是必要的，但可以提前在Mapping CTE里就去重：

  Mapping as (
   select distinct session_id, account_id from web_link
  )
  

  这样后续关联的时候数据量会小很多。

4. 提前过滤数据，减少关联量 #

把过滤条件尽可能往前面放，能有效减少后续关联的数据量：

• 比如acc.creation_date > fo.log_time这个条件，因为accounts里的都是2017年创建的账号，那fo.log_time肯定小于2018年1月1日，可以在Form_opens里先加上这个过滤：

  Form_opens as (
   select session_id, log_time 
   from web_action_log 
   where web_action = 'open_dd_form' and log_time < '2018-01-01'
  )
  

• 另外，保持accounts作为主表在最前面是对的，左连接的顺序尽量让小表驱动大表，提升关联效率。

5. 减少不必要的函数调用 #

如果creation_date本身就是不带时分秒的日期类型，那trunc(acc.creation_date)完全可以直接用acc.creation_date，省掉函数调用的开销。如果必须截断时间部分，可以在accounts CTE里提前处理好：

accounts As (
 select account_id, trunc(creation_date) as creation_date_trunc 
 from account where program_distributor = 'brinks' and channel = 'online' and creation_year = 2017
)

然后后续查询和分组都用creation_date_trunc，避免分组的时候重复做截断计算。

内容的提问来源于stack exchange，提问作者user6384832