【IDEA与sql执行计划】

当我页面调用发现超时熔断时，就应该意识到去查看sql的执行时间和效率了...

SQL执行计划简介

先拿个SQL当例子：

SELECT 'xxxx表'           AS ERP_TABLENAME,
       CTEAA.OU_NAME       AS OU_NAME,
       CTEAA.COMPANY_CODE  AS COMPANY_CODE,
       CTEAA.COMPANY_NAME,
       CTEAA.SEGMENT3      AS ACCOUNT_CODE,
       CTEAA.SEGMENT3_DESC AS ACCOUNT_NAME,
       CTEAA.VENDOR_NUM AS VENDOR_NUMBER,
       CTEAA.VENDOR_NAME,
       CTEAA.AFFILIATED_PARTY,
       CTEAA.PROJECT_NAME  AS project_Num,
       CTEAA.EMPLYEE,
       CTEAA.EMPLYEE_NUM,
       CTEAA.REV_DATE,
       (case
            when exists(select t.unit_code from hpfm_unit t where t.unit_name = CTEAA.RECEIVE_DEPT and rownum = 1)
                then (select t.unit_code from hpfm_unit t where t.unit_name = CTEAA.RECEIVE_DEPT and rownum = 1)
            else
                CTEAA.RECEIVE_DEPT
           end)                                                       AS RECEIVE_DEPT_CODE,
       (case
            when exists(select he.EMPLOYEE_NUM from HPFM_EMPLOYEE he where he.NAME = replace(CTEAA.RECEIVE_EMPLOYEE,',','') and rownum = 1)
                then (select he.EMPLOYEE_NUM from HPFM_EMPLOYEE he where he.NAME = replace(CTEAA.RECEIVE_EMPLOYEE,',','') and rownum = 1)
            else
                replace(CTEAA.RECEIVE_EMPLOYEE,',','')
           end)                                                       AS RECEIVE_EMPLOYEE_NUM,
​
       replace(CTEAA.RECEIVE_EMPLOYEE,',','')  AS RECEIVE_EMPLOYEE,
       CTEAA.RECEIVE_DEPT  ,
       CTEAA.CREATOR       AS CREATOR_NUM,
       CTEAA.CREATOR_DEPT  AS CREATOR_DEPT_CODE,
       ACCOUNT_RECEIVABLE_AGE,
       info.REFERENCE3     AS PO_PROJECT,
       info.PURCHARSE_TYPE AS PO_TYPE,
       0                   AS TENANT_ID,
       CTEAA.LAST_UPDATE_DATE,
       CTEAA.PERIOD
FROM CUX_TRA_ESTIMATE_AP_AGE CTEAA
         LEFT JOIN CUX_PROV_ORDER_INFO info ON info.ORDER_CODE = CTEAA.ORDER_NUM
WHERE CTEAA.PERIOD = '2022-03'
  --AND CTEAA.SEGMENT3 = #{accountCode}
ORDER BY CTEAA.SEGMENT3 ASC

IDEA右键 可以查看Explain Plan Raw:

• 表的加载顺序:ID：执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断

• Operation： 当前操作的内容:

• Name：操作对象

• Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集行数。

• Bytes：表示执行该步骤后返回的字节数。

• Cost（CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。

• Time：Oracle 估计当前操作的时间。

  执行计划中，表的访问方式有两种：

  第一种：全表扫描

  第二种：索引扫描：

  index unique scan  --索引唯一扫描
  ​
  index range scan  --索引局部扫描
  ​
  index full scan  --索引全局扫描
  ​
  index fast full scan  --索引快速全局扫描，不带order by情况下常发生
  ​
  index skip scan  --索引跳跃扫描，where条件列是非索引的前导列情况下常发生

  运算符：

  ​
  1.sort    --排序，很消耗资源
  ​
  (1)order by clauses (2)group by (3)sort merge join –-这三个会产生排序运算
  ​
  2.filter    --过滤，如not in、min函数等容易产生
  ​
  3.view    --视图，大都由内联视图产生(可能深入到视图基表)
  ​
  4.partition view     --分区视图
  ​

  COUNT STOPKEY : 表示代码中用到rownum，并且扫描到指定行数就终止了

再拿一个sql来做例子吧：

SELECT ctab.ACCOUNT_CODE,
       ctab.ACCOUNT_NAME,
       ctab.SUB_ACCOUNT_CODE,
       ctab.SUB_ACCOUNT_NAME,
       ctab.PERIOD_NAME
FROM CUX_TRANS_ACC_BALANCE ctab
         LEFT JOIN CUX_TRA_AP_INV_DETAILS CTAID ON ctab.ACCOUNT_CODE = CTAID.SEGMENT3
         LEFT JOIN CUX_TRA_AP_AR_RELATION CTAAR ON CTAAR.APPLIED_TRX_NUM = CTAID.INVOICE_NUM
         LEFT JOIN CUX_TRA_PAYMENT ctp ON ctp.CHECK_NUMBER = CTAID.INVOICE_NUM
         JOIN CUX_TRA_COA_MAPPING CTCM on CTCM.COA_AC_CODE = CTAB.ACCOUNT_CODE
    and CTCM.COA_SAC_CODE = CTAB.SUB_ACCOUNT_CODE and CTCM.TYPE = 'ACC_AC'
WHERE ctab.CURRENT_PERIOD = '2022-04'
GROUP BY ctab.ACCOUNT_CODE,
    ctab.ACCOUNT_NAME,
    ctab.SUB_ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_NAME,
    ctab.PERIOD_NAME
ORDER BY ctab.ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_CODE
    
    -- 耗时1s 802ms

我们可以看到 执行计划如下：

单次执行就耗时1s 802ms，而且在定时调度时间上就可以明显感觉到很慢，然后我通过执行计划发现这里面关联表几乎都是全表扫描，但是查询出的字段又涉及两个表，再次确认需求后，我去掉了不必要的关联语句，如下：

SELECT ctab.ACCOUNT_CODE,
       ctab.ACCOUNT_NAME,
       ctab.SUB_ACCOUNT_CODE,
       ctab.SUB_ACCOUNT_NAME,
       ctab.PERIOD_NAME
FROM CUX_TRANS_ACC_BALANCE ctab
        LEFT  JOIN CUX_TRA_COA_MAPPING CTCM on CTCM.COA_AC_CODE = CTAB.ACCOUNT_CODE
    and CTCM.COA_SAC_CODE = CTAB.SUB_ACCOUNT_CODE and CTCM.TYPE = 'ACC_AC'
WHERE ctab.CURRENT_PERIOD = '2022-04'
GROUP BY ctab.ACCOUNT_CODE,
    ctab.ACCOUNT_NAME,
    ctab.SUB_ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_NAME,
    ctab.PERIOD_NAME
ORDER BY ctab.ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_CODE
    -- 耗时1s 261ms
   

耗时1s 261ms 感觉快了一些，不过关联关系还可以更精准，比如如果确认要取A表中有B表存在的数据，那么应该把左连接改为 内连接：

SELECT ctab.ACCOUNT_CODE,
       ctab.ACCOUNT_NAME,
       ctab.SUB_ACCOUNT_CODE,
       ctab.SUB_ACCOUNT_NAME,
       ctab.PERIOD_NAME
FROM CUX_TRANS_ACC_BALANCE ctab
          JOIN CUX_TRA_COA_MAPPING CTCM on CTCM.COA_AC_CODE = CTAB.ACCOUNT_CODE
    and CTCM.COA_SAC_CODE = CTAB.SUB_ACCOUNT_CODE and CTCM.TYPE = 'ACC_AC'
WHERE ctab.CURRENT_PERIOD = '2022-04'
GROUP BY ctab.ACCOUNT_CODE,
    ctab.ACCOUNT_NAME,
    ctab.SUB_ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_NAME,
    ctab.PERIOD_NAME
ORDER BY ctab.ACCOUNT_CODE,
    ctab.SUB_ACCOUNT_CODE
-- 比较
-- 左连接：返回数1056   耗时1s 261ms
-- 内连接：返回数110    耗时1s 108ms

两种关联方式对应的执行计划如下：

解释：

1、执行顺序根据缩进来判断

2、ID是每一步的标识符，如果数字前带有星号*，则表示将在随后提供这行包含的谓词信息，其实就是类似于注脚（谓词信息：主要是经常会看到两个谓词filter和access，如下虚线处：

简单说，执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径（表还是索引），而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用），至于到底用那个，通常是数据库的查询编译优化器根据自身的代价公式选择出的最优解，没有索引当然只会是filter，但加了索引也不一定会显示access，因为数据量等因素影响，可能会导致优化器没有选择走索引。

3、在id为2 的执行环节出现了HASH JOIN RIGHT SEMI（哈希右半连接），很奇怪，虽然说在连接时候的方式以及连接时候内表外表的选择，都由数据库决定，半连接一般是 in,exists等子查询才会出现，不过也有例外：当 两个表/结果集做JOIN，但是只返回某一个表/结果集中的数据 时也会产生半连接

4、关于分组/去重：

首先不要用 distinct ，是因为他除了去重自己还会排序，导致耗时太长，而且去重是去掉完全相同的行，雀氏省事但有时候并不符合我们的要求：

 

可以考虑用group by 或者 partition by

5、简单总结select 语句的执行顺序：

• FROM

• ON

• JOIN

• WHERE

• GROUP BY

• HAVING

• SELECT

• DISTINCT

• ORDER BY

6、关于Order by 在执行计划中的使用

order by 对于10G以后的版本 如果有group by则会进行组合显示 SORT GROUP BY 否则就是 SORT ORDER BY。

10G以前（Oracle数据库版本）GROUP BY子句可以返回排序的结果集，即使没有ORDER BY子句，因为默认使用SORT GROUP BY，自动排序分组字段。

从10G开始以后引入了HASH GROUP BY，新的内部排序算法会导致GROUP BY 子句不保证输出会按分组的列排序，也不保证结果集的顺序，所以需要我们在SQL中手动添加Order by。

我们可以进行测试，看是否添加order by 对于执行计划的影响：

PS：查看Oracle的版本：select * from v$version;

7、关于索引扫描：

• 关于index full scan【索引全局扫描】和index fast full scan【索引快速全局扫描】：后者比前者通常执行效率高，因为index fast full scan使用多块读的方式读取索引块，产生db file scattered reads 事件，读取时高效，但为无序读取，（index full scan使用单块读方式有序读取索引块，产生db file sequential reads事件，当采用该方式读取大量索引全扫描，效率低下）

  所以如果对应索引字段出现order by中时 多半就会走 索引全局扫描， 而且这两个扫描方式还有一些前提：

  当select和where中出现的列都存在索引是发生index full scan与index fast full scan的前提

  查询返回的数据行总数占据整个索引10%以上的比率

• 索引范围扫描(index range scan)

  出现条件：

  ① 当索引是组合索引时，而且select 语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。 ② 当扫描对象是唯一性索引时，此时目标sql的where条件一定是范围查询(如between..and...、>、<、<>、>=、<=等); ③ 当扫描对象是非唯一性索引时，此时目标sql的where条件没有限制(可以是等值，也可以是范围查询)

8、关于执行计划不走索引走全表扫描（TABLE ACCESS FULL)：

 

不走索引的情况：

• where中使用了OR

• 组合索引无效，因为不走最左匹配

• 出现左模糊匹配

• 出现不等于，not in ，is null 等

• 发生隐式转换，比如Oracle将字符型隐式转为数值型（如果有，执行计划上会展示to_number这样）

• 返回数据量占表比例过大（这个返回我觉得是分组去重前的行数）

最后的结论是我查询数据量占全表比例太大，优化器自动裁定不走索引，具体测试就是添加查询指定ID范围后就会走索引扫描。

 

强制sql走索引方法：

/*+index(ctab table_name_INDEX) */