[Java知识库]mybatis for循环遍历查询数据 以及 IN 查询数据 的耗时对比，数据量万级别以上

xml层：

    <select id="getInfoByIds" parameterType="string" resultType="com.micro.pojo.Product">
        SELECT
        *
        FROM productorder T
        WHERE T.productorder_code IN
            <foreach collection="ids" index="index" open="(" close=")" item="item" separator=",">
                <if test="(index % 1000) == 999"> NULL) OR T.productorder_code IN (</if>#{item}
            </foreach>
    </select>

    <select id="getInfoById" parameterType="java.lang.String" resultType="com.micro.pojo.Product">
        SELECT
        *
        FROM productorder T
        WHERE T.productorder_code = #{productId}
    </select>

mapper层：

    List<Object> getInfoByIds(@Param("ids") List<String> ids);

    Object getInfoById(@Param("productId") String productId);

service层：

    @Override
    public List<Object> getInfoByIds(List<String> ids) {
        return productMapper.getInfoByIds(ids);
    }

    @Override
    public Object getInfoById(String productId) {
        return productMapper.getInfoById(productId);
    }

controller层：

    @RequestMapping(value = "/getDiff", method = RequestMethod.GET)
    public Map<String, Object> getDiff() {
        Map<String, Object> result = new HashMap<>();
        long startTime = System.currentTimeMillis();
        for (int i = 1; i < 50000; i++) {
            productService.getInfoById("20180501234" + i);
        }
        long endTime = System.currentTimeMillis();
        result.put("costTime1", (endTime - startTime) + "ms");
        return result;
    }

    @RequestMapping(value = "/getDiff2", method = RequestMethod.GET)
    public Map<String, Object> getDiff2() {
        Map<String, Object> result = new HashMap<>();
        long startTime = System.currentTimeMillis();
        List<String> ids = new ArrayList<>();
        for (int i = 1; i < 50000; i++) {
            ids.add("20180501234" + i);
        }
        productService.getInfoByIds(ids);
        long endTime = System.currentTimeMillis();
        result.put("costTime2", (endTime - startTime) + "ms");
        return result;
    }

耗时对比：

?结论：显然 IN 查询 比 for循环多次查询要快很多！

引申：高效的找出两个List中的不同元素，建议把大的list转成map，然后遍历小list，通过key区大的map中获取；如：

    public static List<String> getDiff4(List<String> listA,List<String> listB){
        long startTime = System.currentTimeMillis();
        List<String> diff = new ArrayList<String>();
        List<String> maxList = listA;
        List<String> minList = listB;
        if (listB.size() > listA.size()) {
            maxList = listB;
            minList = listA;
        }
        Map<String, Integer> map = new HashMap<String, Integer>(maxList.size());
        for (String string : maxList) {
            map.put(string, 1);
        }
        for (String string : minList) {
            // 说明相同
            if (map.get(string) != null) {
                map.put(string, 2);
                continue;
            }
            // 说明不相同
            diff.add(string);
        }
        long endTime = System.currentTimeMillis();
        System.out.println(String.format("getDiff4消耗时间：%s",(endTime - startTime)));
        return diff;
    }