一、问题 sql

最近有个查询页面特别慢，这个页面有 12 个输入框，但都是可选的，默认是没有输入值的。

下面是这个搜索的原始 SQL 语句，写在 MyBatis 的 XML 文件里的，通过 if 语句在相应的搜索参数不为空时拼接对应的过滤条件。

在最悲观输入的情况下，SQL 等价于下面的简化版：

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select C.*
                  from (select distinct AI.SerialNo as aiSerialNo,
                                        AI.inputDate,
                                        FD.phaseName,
                                        getItemName('BusinessStatus',
                                                    AI.ApplyStatus) as applyStatus,
                                        getBusinessName(AI.BusinessType) as businessType,
                                        getItemName('LType', AI.LOANTYPE) as lType,
                                        getSellerName(AI.OperateUserID) as opUname,
                                        getusername(AI.Interviewerid) as iName,
                                        getorgname(AI.OperateOrgID) as opOrgName,
                                        bi.Serialno,
                                        substr(ai.properties, 9, 1) as a2
                          FROM t_ai AI
                          LEFT JOIN t_ci CI
                            ON AI.SERIALNO = CI.APPLYSERIALNO
                           and CI.PUTOUTTYPE in ('010', '030')
                          LEFT JOIN t_bi BI
                            ON BI.CONTRACTSERIALNO = CI.SERIALNO
                          LEFT JOIN t_tj tj
                            on tj.loanno = BI.serialno, t_fd FD, t_pc PC, t_pw pw
                         WHERE AI.SERIALNO = FD.OBJECTNO
                           AND AI.SERIALNO = PC.OBJECTNO
                           and AI.SERIALNO = PW.OBJECTNO
                           and (AI.applystatus in ('103', '104') and
                               FD.endtime is not null and
                               fd.serialno =
                               (select max(t.serialno)
                                   from t_fd t
                                  where t.objectno = ai.serialno) or
                               (AI.applystatus not in ('103', '104') and
                               FD.endtime is null))
                           and AI.OPERATEORGID in
                               (SELECT t_b_org.BELONGORGID
                                  FROM t_b_org t_b_org
                                 WHERE t_b_org.ORGID IN
                                       (SELECT ORGID
                                          FROM t_org
                                         WHERE SALESUPER = :3))
                        ) C
                 order by C.inputDate, C.aiSerialNo) A
         WHERE ROWNUM < :1)
 WHERE RN >= :2

这个 SQL 的执行计划总的 cost 值为 13902 。大表都是全表扫描。

二、优化过程

1. 检查表的关联

把 sql 语句拷贝到 notepad++，双击表的别名，会用绿色高亮显示。

• 发现 tj 这个表既没有用于 where 子句进行过滤，也不在 select 子句里。 结合 select distinct, 可以确定 left join 这个表对最终结果集是没有任何影响的。可以安全地移除对这个表的关联。

• 发现 pc、pw 这两个表都与 ai 表关联，且与 ai 是一对一的关系。但它们并没有出现在 select 子句，在 where 子句也只是在相应的参数出现时才拼接过滤语句。也就是：如果过滤条件出现，那么关联这个表是能过滤数据的，如果过滤条件不出现，关联不关联这个表的结果都是一样的。既然这样，就可以利用 MyBatis 的 if 条件性地关联这个表。如下：

<if test="mobilePhone != null and mobilePhone != ''.toString() or familyTel != null and familyTel != ''.toString()">
    join t_pc PC on AI.SERIALNO = PC.OBJECTNO
    <if test="mobilePhone != null and mobilePhone != ''.toString()">
      and (PC.MOBILETELEPHONE = #{mobilePhone , jdbcType=VARCHAR} or PC.MOBILETELEPHONE2 = #{mobilePhone , jdbcType=VARCHAR})
    </if>
    <if test="familyTel != null and familyTel != ''.toString()">
      and PC.FAMILYTEL = #{familyTel , jdbcType=VARCHAR}
    </if>
</if>

继续阅读 →