Question

SQL子查询中IN太多，该如何优化让查询速度更快？

SELECT LIFT_INFO_ID FROM lc_lift_association
<where>
<if test="param.platforms != null">
AND PLATFORM_ID IN
<foreach item="id" index="index" collection="param.platforms"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
<if test="param.userUnits != null">
AND USER_UNIT_ID IN
<foreach item="id" index="index" collection="param.userUnits"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
<if test="param.smantanceUnits != null">
AND SMANTA_UNIT_ID IN
<foreach item="id" index="index" collection="param.smantanceUnits"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
<if test="param.manufacturingUnits != null">
AND MANUFA_UNIT_ID IN
<foreach item="id" index="index" collection="param.manufacturingUnits"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
<if test="param.inspecAgencys != null">
AND INSPEC_ANGECY_ID IN
<foreach item="id" index="index" collection="param.inspecAgencys"
open="(" separator="," close=")">
#{id}
</foreach>

</if>
<if test="param.qualitSuperBureaus != null">
AND QUALIT_SUPER_BUREAU IN
<foreach item="id" index="index" collection="param.qualitSuperBureaus"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
</where>
) assoc ON d.ID = assoc.LIFT_INFO_ID

Answer 1

in可以用exists代替，原则上集合数少用IN比较快，集合数据大用exists比较好

追问

• 请问具体该如何替换啊？我试过好像不行！！！