一、问题 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>
2. select 子句优化
从最内层的 select 子句可以看到，有 6 个自定义函数的调用。这些自定义函数的实现都是一个 sql 查询，虽然查询的目标表都很小。
但是这个内层查询会生成约 1w 条记录，然后在外层进行排序、分页，最终只取 10 条记录返回给页面展示。
这 1w 条记录就会有 6w 次很小的查询，而这些查询基本是白费的，因为最终其实只需 60 次就好。所以把这些函数调用都放到最终结果生成之后才进行。
3. 从业务上优化
上述的优化做了后，最差情况下的执行计划的 cost 是 12360，仍然太大了。
注意到 bi 表只在 select 子句中使用， ci 表只是用来关联起 ai 和 bi 表的。bi、ci 都是全表扫描，如果放到最外层进行关联，只有 10 条记录，完全可以走索引。但是业务上不允许，因为 ai 与 ci 是一对多的关系，ci 与 bi 是一对一的关系。
这是领导发现从 bi 表查询出来的字段对于这个搜索结果是没必要的，用户应该在其他功能去查看这些字段，跟业务沟通后决定把这些字段去掉。这样就不需要关联 ci、bi 表了，执行计划的 cost 降到了 10130。
4 从数据特性发现优化点
4.1 利用数据特点简化关联
现在剩下的开销主要是 ai 表和 fd 表关联引起的全表扫描。根据数据的特性，ai 与 fd 是一对多的关系，ai 的记录在 fd 里至少有一条记录。
fd 出现在 where 子句的两个地方：
<if test="phaseNo != null and phaseNo != ''.toString()">
and FD.PHASENO = #{phaseNo , jdbcType=VARCHAR}
</if>
和
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))
前面那个是根据用户的输入决定的过滤条件，对分页有影响。后面那个是取 fd 里关联的最新的那条记录，只取一条，对分页没影响，完全可以放到分页后再关联。
假定用户没有输入 phaseNo 搜索参数，我们就不需要在最内层关联 fd 表，这样，执行计划的开销可以降到 2094 。不过这个要实施的话，得写成两个查询，根据 phaseNo 参数是否有值来分别调用，复杂了点。
4.2 利用数据不均衡分布特性
ai 表的 applystatus 字段表示记录的状态，取值有：申请中、通过、否决，还有几个其他的状态，申请中 和 其他的中间状态占了只占了总量的很小一部分。
该字段上建了有索引。但是对于 通过、否决 这种占了大部分的，Oracle 是不会走索引，但是对于 申请中 这种只占极少数的是会走索引的。
可以把这种占极少数的状态组合作为搜索页面的一个下拉选项，默认选中；通过、否决 作为另外的选项。
5. 利用组合索引
先不管 4.2 用户不输入 phaseNo 情况下的优化，继续专注最差情况下的，毕竟我们期望 应用在所有输入下都有良好的性能。
业务最近反馈需要按天查看指定日期的所有 ai 记录，然后搜索页面增加了起始日期、结束日期两个输入框。
ai 里的记录从录入到进入最终状态只需要 N 天，所以，如果我们默认展示最近 N 天的记录，其实是可以满足绝大多数用户的需求，如果用户需要查看更早之前的记录，也可以手工选择日期。
所以我们可以建立一个组合索引(applystatus, inputDate) 替代 applystatus 上的单列索引，执行计划里的 cost 可以降到 1082 左右。
之所以把
applystatus放在组合索引的前面，是因为以前的查询一般只指定了applystatus没有指定inputDate；二是在只指定了inputDate，没有applystatus也可以进行索引跳跃扫描。
此时的执行计划是对 ai 表现通过上面的组合索引进行扫描，对 fd 表是嵌套循环，也是走了 objectno 跟 objectType 组合索引，但是总体开销还是很高。。。
6. 利用索引过滤减少回表
在 fd 表上创建一个新的组合索引 (objectno, phaseno)，然后 cost 降到 362，为啥？ 都是组合索引，列不同，效果也很不同。
这是因为 where 子句的 and fd.phaseno = #{phaseNo} 可以通过索引里的数据进行过滤，满足条件的才回表查询数据，进行进一步过滤；对于 (objectno, objectType) 组合索引，每一条记录都必须回表，自然更差些。
7. 如何达到最优
结合上面的优化过程，为了达到最优的效果，必须区分是否输入了 phaseNo 查询条件：
- 如果没有输入，则不在内存的主查询里关联 fd 表，在分页后的最终结果出来后才关联，cost 在 100 以下。
- 在有输入的情况下，通过新建的组合索引
(objectno, phaseno)减少回表，cost 为 362 左右。
三、小结
总结下上面用到的优化技术：
1. 减少不必要的表关联，如果可以，只对最终数据进行关联。
2. 减少 select 子句不必要的子查询或函数调用，只对最终数据进行处理。
3. 利用索引减少查询范围，利用索引过滤减少回表操作。
4. 认真研究所查询数据分布的特点。
5. 研究 inner join / left jion 对结果集的影响。
6. 重视从业务角度进行优化，要分析那些数据、操作对用户是否是必须的，还是可选的。
7. 优化必须考虑在最悲观的情况下的性能，这样可以保证在所有情况下都有可接受的性能。
欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。
