因为做的是类似SAAS的系统,关于同一个业务没会有不同的视角,有管理员,有类别分类的,有特别逻辑处理的。总而言之涉及到很多方面,再加上历史遗留问题导致导致的数据问题,这SQL写起来真的酸爽。
除了简单的关联,还要考虑到一个效率问题,最近就因为CASE WHEN导致了一个线上查询缓慢,差一点就要超过客户端设置的超时请求设置的一个问题。
问题详情:
现在有一个检查盘点资产的要求,之前只是要求在数据库有拿过资产的员工去发检查清单,
检查单表 check
检查详情(针对涉及到的资产每一个生成对应数据)check_detail
货物资产 asset 员工表employee
员工返回检查结果(针对的涉及到的员工每一个生成数据) employee_check
问题来了之前的逻辑,实际上员工领用资产了那他的check_detail和employee_check是对应的上的
但是没有的话就对应不上了,之前只检查有资产的员工,现在还要检查没有领用资产的(万一有惊喜呢)
Mybaitis写的SQL一部分
<if test="maps.employeeId != null ">
AND CASE WHEN aio.no_use_asset_employee_join = 1 THEN ( aius.employee_id = #{maps.employeeId} )
ELSE ( aiod.employee_id = #{maps.employeeId} && aius.employee_id = #{maps.employeeId} ) END
</if>
就是有1的时候所用人都参与,0的时候要满足两个条件,在范围的员工还要有资产
但是一上生产就完了,一张表只有几万条数据巨卡,两三秒才返回差一点就超时的那种
检查SQL 用Explain分析发现该走的索引也都走了,就是很慢,而且随便从日志找一条SQL发现实际涉及到的行数也不多,大概是三个解析,最多的也才两百多行,为什么会这么慢?
实际上就是结果集else的时候他既要匹配aiod.employee_id = #{maps.employeeId} 又要 aius.employee_id = #{maps.employeeId}
类似于SQL函数,没走索引,效率很慢。
那优化怎样,就是尽量都走优化,既然都用到这个aiod.employee_id = #{maps.employeeId} ,就把他提出来,然后改造成这个样 ,1=1永远为true就是不再做筛选
<if test="maps.employeeId != null ">
AND aius.employee_id = #{maps.employeeId}
</if>
<if test="maps.employeeId != null ">
AND CASE WHEN aio.no_use_asset_employee_join = 1 THEN ( 1 = 1)
ELSE ( aiod.employee_id = #{maps.employeeId} ) END
</if>
思考:
就是不要把太多条件去放进CASE WHEN里面,这样会导致不走索引,只能单纯去比较就会很慢。尽量拆分出公共部分,尽可能去走索引查询。