INDEX를 사용한 데이터 검색은 항상 빠른가?

INDEX를 활용한 데이터 검색은 조회할 데이터 양에 따라 FULL SCAN(TABLE FULL SCAN)보다 성능이 더 나빠 질 수 있다.

아래는, PostgreSQL과 ORACLE, MySQL에서 조회할 데이터 양에 따른 INDEX RANGE SCAN과 FULL SCAN의 성능을 비교한 차트다.

어느 DBMS든 상관 없이 INDEX를 사용한 데이터 검색은 조회할 데이터가 많아질 수록 성능이 가파르게 나빠지는 것을 알 수 있다.

위 차트를 만들기 위해 테스트한 SQL은 아래와 같다. (인덱스는 ord_dtm에 대한 인덱스만 구성되어 있다.)

-- PostgreSQL

    EXPLAIN (ANALYZE,COSTS OFF,BUFFERS)
    /*+ SeqScan(t1) */
    SELECT  t1.member_id ,COUNT(*) cnt
    FROM    tr_ord_big t1
    WHERE   t1.ord_dtm >= TO_DATE('20190101','YYYYMMDD')
    AND     t1.ord_dtm <  TO_DATE('20220801','YYYYMMDD')
    GROUP BY t1.member_id
    ORDER BY 2 DESC;


    EXPLAIN (ANALYZE,COSTS OFF,BUFFERS)
    /*+ IndexScan(t1) */
    SELECT  t1.member_id ,COUNT(*) cnt
    FROM    tr_ord_big t1
    WHERE   t1.ord_dtm >= TO_DATE('20190101','YYYYMMDD')
    AND     t1.ord_dtm <  TO_DATE('20220801','YYYYMMDD')
    GROUP BY t1.member_id
    ORDER BY 2 DESC;
    
-- ORACLE
    SELECT  /*+ FULL(T1) */
            T1.MEMBER_ID ,COUNT(*) CNT
    FROM    STARTDBORA.TR_ORD_BIG T1
    WHERE   T1.ORD_DTM >= TO_DATE('20190101','YYYYMMDD')
    AND     T1.ORD_DTM <  TO_DATE('20220801','YYYYMMDD')
    GROUP BY T1.MEMBER_ID
    ORDER BY 2 DESC;

    SELECT  /*+ INDEX(T1) */
            T1.MEMBER_ID ,COUNT(*) CNT
    FROM    STARTDBORA.TR_ORD_BIG T1
    WHERE   T1.ORD_DTM >= TO_DATE('20190101','YYYYMMDD')
    AND     T1.ORD_DTM <  TO_DATE('20220801','YYYYMMDD')
    GROUP BY T1.MEMBER_ID
    ORDER BY 2 DESC;

-- MYSQL
    SELECT  /*+ NO_INDEX(T1) */
            t1.member_id ,COUNT(*) cnt
    FROM    startdbmy.tr_ord_big t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220801','%Y%m%d')
    GROUP BY t1.member_id
    ORDER BY 2 DESC;

    SELECT  /*+ INDEX(T1) */
            t1.member_id ,COUNT(*) cnt
    FROM    startdbmy.tr_ord_big t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220801','%Y%m%d')
    GROUP BY t1.member_id
    ORDER BY 2 DESC;
SQL
복사

동일한 테이블에 동일한 데이터 양을 사용해 테스트했다. SQL은 ord_dtm에 대한 조건과 member_id를 활용한 GROUP BY 가 포함되어 있다. 그러므로 위 테스트 결과는 순수 데이터 검색 뿐만 아니라 데이터를 집계 처리하는 과정의 부하도 포함되어 있다. 재미있는 점은, MySQL의 경우 temp를 이용한 GROUP BY 처리를 하면서 다른 DBMS에 비해 성능이 매우 안 좋다는 점이다. 또한 각 DBMS 별로 할당된 메모리 공간이 달라 성능에 차이가 있기도 하다. 여기서 중요한 점은 DBMS 별 성능 차이가 아닌, INDEX 를 활용한 데이터 검색은 데이터 양에 따라 가파르게 성능이 나빠진다는 점이다.

노련한 튜너들이 배치 튜닝을 위해 FULL SCAN을 종종 사용하는 이유 중에 하나다.