SQL，查询条件中IN的内容过多效率低进行优化

前提：

        业务实施过程中，查询分页数据很慢，后定位到是in的数据过多，导致查询很慢。

优化思路：

        使用临时表解决，将数据插入到一临时表中，再将临时表与查询主表进行join。

其中要注意的是，因为业务部署使用的是分布式部署，有多个节点，要保证每个临时表在多个节点下也是唯一的。这里采用的方式是使用节点ip+port+IdUtil.objectId()【也可以使用雪花参数，但还是要加上IP与端口才能保持唯一】

示例：

        原SQL（当数组ids的数值过多时，查询效率很慢）

        select
        count(*)
        from t_test_info info
        <where>
            info.status = 0 
            <if test="ids!= null and ids.size > 0 ">
                AND info.id in
                <foreach collection="ids" separator="," open="(" close=")" item="id">
                    #{id}
                </foreach>
            </if>
        </where>

      优化SQL

1、新建临时表xml, 包含创建临时表、临时表新增数据、drop临时表

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.TemporaryTableMapper">

    <update id="createPtIdTempTable">
        drop temporary table if exists ${tableName};
        create temporary table ${tableName}
        (
            pt_id bigint not null
        )
    </update>

    <insert id="batchInsertPtIdTempTableData">
        insert into ${tableName} (id)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item})
        </foreach>
    </insert>

    <delete id="dropTempTable">
        drop temporary table if exists ${tableName};
    </delete>
</mapper>

2、代码优化

    //新建临时表
 String tempTableName = "t_temp_" +HttpUtil.getIpPort()+ IdUtil.objectId();
        temporaryTableMapper.createPtIdTempTable(tempTableName);
        temporaryTableMapper.batchInsertPtIdTempTableData(queryDto.getPtIds(), tempTableName);
    //将临时表名称传入sql

对应sql优化

        select
        count(*)
        from t_test_info info
         join ${tempTableName} temp on info.id = temp.id
        <where>
            info.status = 0 
        </where>

然后再去对列表测试，可以发现查询效率大幅度提升。