SQL及索引优化

一、问题SQL筛选步骤

1、检查慢日志是否开启：

show variables like 'slow_query_log';

2、检查慢日志路径：

#检查慢日志路径：
show variables like '%slow_query_log%';

3、开始慢查询日志：

set GLOBAL slow_query_log = on;

4、慢日志判断标准

show variables like '%long_query_time%';

5、修改慢日志判断标准

由于实际项目中1秒左右的查询旧可以定义为慢查询，所以这边将慢查询时间设置为1秒，再进行观察

set GLOBAL long_query_time = 1;

6、慢查询日志的存储格式解析

其中 rows_send是用户真实需要的数据，rows_examined相当于中间结果集，两者相差太大，说明SQL查询的效率有问题

二、SQL优化工具

SQL优化工具Tosska SQL Tuning Expert for Oracle，帮助SQL开发人员解决SQL性能问题。 

  本工具主要创始人Richard To, 资深ITPUB元老，从1996年开始设计SQL优化工具，拥有超过20年的SQL优化经验。

  工具已经从最初的1.0， 1.1，1.2，逐步演化到了3.0.  最新版本主要包含3个功能：

  1. 为单条SQL产生等价SQL, 并找出最快的等价SQL;

  2. 为单条SQL建议最佳索引；

  3. SQL性能报表；

  官网下载 https://www.tosska.cn/tosska-sql-tuning-expert-tse-oracle-free-download-zh/

  产品介绍 https://www.tosska.cn/tosska-sql-tuning-expert-tse-oracle-zh/

 

三、使用工具优化SQL

1、用法大致介绍

主要又如下功能，操作简单，大家可以自行安装查看

2、未优化之前的SQL

 

select
        count(1)
        from
        cms_workflow wf
        where
        wf.org_id = '149300c4126dc809ee3f7839f4ec03c1'
         
        AND EXISTS (
        select 1
        from cms_workflow a left join
        cms_workflow_auditor b
        on a.apply_id = b.apply_id
        where b.auditor_id = '940b22e174d04eef8b4500e5ddc6350c'
        and a.apply_id=wf.apply_id
        union all
        select 1
        from cms_workflow a left join
        cms_workflow_notice b
        on a.apply_id = b.apply_id
        where b.notice_user_id = '940b22e174d04eef8b4500e5ddc6350c'
        and a.apply_id=wf.apply_id
        union all
        select 1
        from cms_workflow a left join
        cms_workflow_todo b
        on a.apply_id = b.apply_id
        where b.user_id = '940b22e174d04eef8b4500e5ddc6350c'
        AND a.apply_id = wf.apply_id
        );

  查询时间：

3、查看慢查询的执行计划

由上表可以看出，产生的中间表记录为2万多条

4、优化SQL

select /*+ QB_NAME(QB1) BKA(`wf`@QB1) */ count(1)
from   cms_workflow wf
where  wf.org_id = '149300c4126dc809ee3f7839f4ec03c1'
       and wf.apply_id in (select apply_id
                           from   (select a.apply_id
                                   from   cms_workflow a
                                          left join cms_workflow_auditor b
                                          on a.apply_id = b.apply_id
                                   where  b.auditor_id = '940b22e174d04eef8b4500e5ddc6350c'
                                   union all
                                   select a.apply_id
                                   from   cms_workflow a
                                          left join cms_workflow_notice b
                                          on a.apply_id = b.apply_id
                                   where  b.notice_user_id = '940b22e174d04eef8b4500e5ddc6350c'
                                   union all
                                   select a.apply_id
                                   from   cms_workflow a
                                          left join cms_workflow_todo b
                                          on a.apply_id = b.apply_id
                                   where  b.user_id = '940b22e174d04eef8b4500e5ddc6350c') DT1)
       and wf.id >= ''

5、查看最优SQL执行计划

6、最终对比

由上述比较可以看出，在索引优化基本相同的情况下，主要看SQL的写法产生中间表的量级，对于索引优化相当的，产生中间表的数量越小查询越快