记一次解决oracle sql性能瓶颈的问题

叁歲伎倆 2022-05-28 22:38 224阅读 0赞

先上sql：

SELECT
            (SELECT M.ALBUM_ID FROM ALBUM_R_MUSIC AM,ALBUM M WHERE AM.MUSIC_ID = M.MUSIC_ID AND AM.ALBUM_ID = M.ALBUM_ID  AND ROWNUM = 1) ALBUM_ID,
            (SELECT M.ALBUM_NAME FROM ALBUM_R_MUSIC AM,ALBUM M WHERE AM.MUSIC_ID = M.MUSIC_ID AND AM.ALBUM_ID = M.ALBUM_ID AND ROWNUM = 1) ALBUM_NAME,
            (SELECT TO_CHAR(WM_CONCAT(T.ARTIST_ID || ':' ||
            T.ARTIST_NAME))
            FROM MUSIC_R_ARTIST MR, ARTIST T
            WHERE M.MUSIC_ID = MR.MUSIC_ID(+)
            AND MR.ARTIST_ID = T.ARTIST_ID
            AND MR.ARTIST_TYPE = 1) ARTIST_ID,
            M.MUSIC_ID,
            M.LENGTH,
            M.MUSIC_NAME,
            M.SINGER_NAME,
            M.RANK_NUM,
            M.PUBLISH_YEAR,
            M.STATUS
            FROM
            <if test="tagId != null">
                TAG TA,
                OBJECT_R_TAG O,
            </if>
            MUSIC_R_ARTIST T,
            ARTIST_R_TYPE Y,
            MUSIC M
            <where>
            T.ARTIST_ID = Y.ARTIST_ID
            AND Y.TYPE_ID = '1'
            AND T.MUSIC_ID = M.MUSIC_ID
            AND M.STATUS = '1'
            AND T.ARTIST_TYPE = 1
            <if test="tagId != null">
                AND M.MUSIC_ID = O.OBJECT_ID(+)
                AND TA.TAG_ID = O.TAG_ID
                AND TA.TAG_ID = #{tagId}
                AND O.OBJECT_TYPE = 1
            </if>
            </where>
    </php>

这个sql有九百多万条数据，分页采用的mybatis框架的RowBounds类。会把整个结果集扫描一次，很耗时间，大概分一次页要5S左右，到后面几页可能需要几分钟。。。  
![这里写图片描述][70]  
解决办法是将要关联的表写成存储过程，将MUSIC\_R\_ARTIST T,ARTIST\_R\_TYPE Y,MUSIC M这三张需要关联的表融合成一张大的表，并且将rownum作为一列，加上索引。存储过程会提前把需要的数据跑出来，这样我们在查询的时候去操作musicianas，如下：

CREATE OR REPLACE procedure migu_musician_songs AS begin execute immediate 'drop table temp_musicianas';
    execute   immediate 'create table temp_musicianas As SELECT m.*,rownum rn from MUSIC M where exists (select 1 from (select music_id from (select artist_id from ARTIST_R_TYPE where type_id = 1) Y, MUSIC_R_ARTIST T where y.artist_id = t.artist_id) t where t.music_id = m.music_id and m.status = 1)'; execute immediate 'rename musicianas to temp_musicianas1'; execute immediate 'rename temp_musicianas to musicianas'; execute immediate 'create index IND_musicianas_rn on musicianas(rn)'; EXCEPTION WHEN OTHERS THEN ROLLBACK; END;

这样我们可以通过行号，也就是rownum去做分页，取得从startIndex到endIndex的数据，就是我们需要的这一页的数据。sql如下：

SELECT
            (SELECT M.ALBUM_ID FROM ALBUM_R_MUSIC AM,ALBUM M WHERE AM.MUSIC_ID = M.MUSIC_ID AND AM.ALBUM_ID = M.ALBUM_ID  AND ROWNUM = 1) ALBUM_ID,
            (SELECT M.ALBUM_NAME FROM ALBUM_R_MUSIC AM,ALBUM M WHERE AM.MUSIC_ID = M.MUSIC_ID AND AM.ALBUM_ID = M.ALBUM_ID AND ROWNUM = 1) ALBUM_NAME,
            (SELECT TO_CHAR(WM_CONCAT(T.ARTIST_ID || ':' ||
            T.ARTIST_NAME))
            FROM MUSIC_R_ARTIST MR, ARTIST T
            WHERE M.MUSIC_ID = MR.MUSIC_ID(+)
            AND MR.ARTIST_ID = T.ARTIST_ID
            AND MR.ARTIST_TYPE = 1) ARTIST_ID,
            M.MUSIC_ID,
            M.LENGTH,
            M.MUSIC_NAME,
            M.SINGER_NAME,
            M.RANK_NUM,
            M.PUBLISH_YEAR,
            M.STATUS
            FROM
            <if test="tagId != null">
                OBJECT_R_TAG O,
            </if>
            musicianas M
            <where> 1= 1
            <if test="tagId != null">
                AND M.MUSIC_ID = O.OBJECT_ID(+)
                AND o.TAG_ID = #{tagId}
                AND O.OBJECT_TYPE = 1
            </if>
                and m.rn between ${startIndex} and ${endIndex}
            </where>

极大提升了效率：  
![这里写图片描述][70 1]

[70]: /images/20220529/2ff01dde3d8c491ba7e24b7d4ead9790.png
[70 1]: /images/20220529/d90d5b59bd7d4eb79dcce30bb957681b.png