查询导致内存溢出优化

最新推荐文章于 2025-03-19 17:40:02 发布

抓手

最新推荐文章于 2025-03-19 17:40:02 发布

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签：内存溢出查询 sql

本文链接：https://blog.csdn.net/Anenan/article/details/89846867

版权

MySQL 专栏收录该内容

50 篇文章

订阅专栏

报错信息

org.springframework.web.util.NestedServletException: 
Handler dispatch failed; nested exception is java.lang.OutOfMemoryError:Java heap space

出现原因

        //查询了大量的userId  
        List<Long> loanUserIds = bidMapper.findLoanUserIds();
        //将userId作为参数继续查询
        List<UserDistribute> loanUserDistributes = usersMapper.findUserDistribute(loanUserIds, loanUserIds.size());

    <select id="findUserDistribute" resultType="com.mmtvip.mainhomeapi.vo.datadisclosure.UserDistribute">
        SELECT
            tap.`NAME` AS `province`,
            t.cou/#{size} AS `ratio`
        FROM
            (
                SELECT
                    count(1) cou,
                    province_id province
                FROM
                    t_dict_ad_citys tdac,
                    (
                        SELECT city_id ci
                        FROM t_users tu
                        WHERE tu.id IN
                            <foreach collection="userIds" item="id" separator="," open="(" close=")" >
                                #{id}
                            </foreach>
                        AND tu.city_id != 0
                    ) tc
                WHERE tc.ci = tdac.id
                GROUP BY province_id
                ORDER BY cou DESC
            ) t
        LEFT JOIN t_dict_ad_provinces tap ON 	t.province = tap.id
        ORDER BY t.cou DESC LIMIT 10
    </select>

解决方案

//将结果在sql中直接查出
List<UserDistribute> loanUserDistributes = usersMapper.findUserDistribute();

    <select id="findUserDistribute" resultType="com.mmtvip.mainhomeapi.vo.datadisclosure.UserDistribute">
        SELECT
            tap.`NAME` AS `province`,
            t.cou/
                    (SELECT COUNT(DISTINCT user_id) FROM t_bids WHERE `STATUS` IN (4, 5, 14))
                    AS `ratio`
        FROM
            (
                SELECT
                    count(1) cou,
                    province_id province
                FROM
                    t_dict_ad_citys tdac,
                    (
                        SELECT city_id ci
                        FROM t_users tu
                        WHERE tu.id IN
                              (SELECT DISTINCT user_id FROM t_bids WHERE `STATUS` IN (4, 5, 14))
                        AND tu.city_id != 0
                    ) tc
                WHERE tc.ci = tdac.id
                GROUP BY province_id
                ORDER BY cou DESC
            ) t
        LEFT JOIN t_dict_ad_provinces tap ON t.province = tap.id
        ORDER BY t.cou DESC LIMIT 10
    </select>