[MySQL] MySQL 실행 계획 분석을 통해 페이징 쿼리 성능 개선

안녕하세요. 이번에는 MySQL 실행 계획을 통해 페이징 쿼리의 성능을 개선하는 과정을 작성해 보았는데요,

 

UI가 무한 스크롤 방식이 아닌 오프셋 페이지 방식(<< 1 | 2 | 3 >>)이기 때문에 커서 기반 페이지네이션 쿼리 방식을 사용하지 않고, 오프셋 기반 페이지네이션 쿼리 방식을 커버링 인덱스 방식의 쿼리를 사용하여 페이징 쿼리의 성능을 개선하였습니다. 

 

• 커서 기반 페이지네이션 : 클라이언트가 가져간 마지막 row의 순서상 다음 row들을 n개 요청, 응답하게 구현
• 오프셋 기반 페이지네이션 : DB의 offset 쿼리를 사용하여 페이지 단위로 요청/ 응답하게 구현

커버링 인덱스란?

쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스(Covering Index) 라고합니다. 인덱스는 데이터를 효율적으로 찾는 방법이지만, MySQL의 경우 인덱스안에 포함된 데이터를 사용할 수 있으므로 이를 잘 활용한다면 디스크까지 접근할 필요가 전혀 없습니다. 따라서 커버링 인덱스를 활용하면 쿼리의 성능을 비약적으로 향상시킬 수 있습니다.

 

아래 내용들은 제가 MySQL 실행 계획 분석을 통해, 커버링 인덱스를 사용하지 않았을 때와 사용했을 때의 성능적 차이를 비교해본 것과 커버링 인덱스를 적용해 쿼리의 성능을 향상시킨 과정을 담은 내용들입니다.

 

pageNo = 10000000, pageSize = 10으로 가정하고 getPosts 메서드의 속도를 측정해서 성능척 차이를 비교해보았습니다.

🔍 커버링 인덱스를 사용하지 않았을 때

우선 커버링 인덱스를 적용하지 않는 쿼리가 작성된 getPosts 메서드입니다. 프라이머리 키는 post_id, post 테이블의 인덱스인 post_idx에는 email이 존재합니다.

    @Select(
        """
        SELECT title, content 
        FROM post where email = #{email} 
        ORDER BY post_id DESC LIMIT #{pageSize} OFFSET #{pageNo};
        """
    )
    fun getPosts(email: String, pageNo: Int, pageSize: Int): List<Post>

 

쿼리의 실행 계획을 분석한 결과입니다.

1. type -> ref

ref 접근 방법은 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키등의 제약 조건도 없습니다. 인덱스의 종류와 관계없이 동등조건으로 검색할 떄는 ref 접근 방법이 사용됩니다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지는 않지만 ref 또한 인덱스 레인지 스캔이기 때문에 매우 빠른 속도로 레코드들을 조회하므로 효율적인 방식으로 실행되고 있다고 생각했습니다.

 

2. Extra -> Using where

Using where는 rows와 실제 결괏값의 차이가 많이 날 때 MySQL 엔진은 스토리지 엔진(InnoDB)으로 부터 받은 데이터를 가공하는 작업을 거치게됩니다. 즉 1차적으로 스토리지 엔진에서 불필요하게  많은 데이터를 access 했다는 의미입니다. 하지만 Using Where 즉,  대상이 되는 row(page = 10)의 나머지 칼럼 값을 읽는 현상은 오프셋 기반 페이지네이션 특성상 발생은 피할 수 없다고 생각합니다.

 

위와 같은 문제를 피할 수 없지만, 커버링 인덱스를 사용하여 쿼리의 속도를 더 빠르게 쿼리로 튜닝하여 위 문제를 보완할 수 있을 것이라고 생각하였습니다.

 

현재 위의 쿼리는 select에서 사용된 title, content가 인덱스 (post_idx(email))에 포함되지 않기 때문에 커버링 인덱스가 될 수가 없습니다. 따라서, 커버링 인덱스를 태우지 않은 일반적인 조회 쿼리는 order by, offset ~ limit 을 수행할때도 아래 그림처럼 데이터 블록으로 접근을 하게 됩니다. 

커버링 인덱스가 적용되지 않았을 때

하지만 커버링 인덱스 방식을 이용하면, where, order by, offset ~ limit 을 인덱스 검색으로 빠르게 처리하고, 아래 그림처럼 이미 다 걸러진 10개의 row에 대해서만 데이터 블록에 접근하기 때문에 성능의 이점을 얻게 됩니다.

커버링 인덱스가 적용됬을 때

마지막으로, 커버링 인덱스를 사용하지 않았을 때의 getPosts 메서드의 시간을 측정한 결과입니다.

pageNo = 1000000, 즉, 10만 쪽에 있는 데이터를 보려고 할 때, 실행시간을 보면 거의 4초 가깝게 걸린 것을 확인할 수 있었습니다.

🔍 커버링 인덱스를 사용했을 때

커버링 인덱스를 적용한 쿼리가 작성된 getPosts 메서드입니다. 프라이머리 키는 post_id, post 테이블의 인덱스인 post_idx에는 email이 존재합니다.

    @Select(
        """
        SELECT title, content
        FROM post as i
                 JOIN (SELECT post_id
                       FROM post
                       WHERE email = #{email}
                       ORDER BY post_id DESC LIMIT #{pageSize}
                       OFFSET #{pageNo}) as temp on temp.post_id = i.post_id 
        """
    )
    fun getPosts(email: String, pageNo: Int, pageSize: Int): List<Post>

서브쿼리(SELECT)에서 select절을 비롯해 order by, where 등 쿼리 내 모든 항목이 인덱스 컬럼으로만 이루어지게 하여 인덱스 내부에서 쿼리가 완성될 수 있도록 하였습니다. 이렇게 커버링 인덱스로 빠르게 걸러낸 row의 post_id를 통해 실제 select 절의 항목들을 빠르게 조회해올수 있도록 쿼리를 튜닝하였습니다.

 

프라이머리 키인 post_id는 모든 인덱스에 자동 포함됩니다.

 

쿼리의 실행 계획을 분석한 결과입니다.

먼저, 커버링 인덱스가 적용되면 EXPLAIN 결과 (실행 계획) 의 Extra 필드에 "Using index" 가 표기되었습니다.

 

위의 결과와 다르게 Extra 항목에 Using index 가 등장합니다. 이 쿼리는 인덱스에 포함된 컬럼 (post_id) 만으로 쿼리가 생성 가능하니 커버링 인덱스가 사용 된 것입니다. 이렇게 Extra 항목에 Using index 가 나온다면 이 쿼리는 커버링 인덱스가 사용된 것으로 보시면 됩니다.

 

하지만, Extra 항목에 Using index가 있어야만 인덱스를 사용한 것이 아닙니다. 인덱스 사용 유무는 key 항목에 선택된 인덱스가 있냐 없냐의 차이입니다. Extra 항목에 Using index가 있는 경우는 쿼리 전체가 인덱스 컬럼값으로 다 채워진 경우에만 발생합니다.

 

그리고 첫 번째 라인의 table 컬럼이 <derived2>인데, 단위 SELECT 쿼리의 아이디가 2번인 실행 계획으로부터 만들어진 임시 테이블을 가리킵니다. 단위 SELECT 쿼리의 id 2번은 post 테이블로부터 SELECT된 결과가 저장된 임시 테이블입니다. 

 

첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있는 것으로 봐서 2개 테이블(첫 번째 라인의 <derived2>와 두 번째 라인의 i 테이블)이 조인되는 쿼리라는 것을 알 수 있습니다. <derived2> 테이블이 i 테이블보다 먼저 표시되었기 때문에 드라이빙 테이블이 되고 i 테이블은 드리븐 테이블이 됩니다. 즉 <derived2> 테이블을 먼저 읽어서 i 테이블로 조인을 실행했다는 것을 알 수 있습니다.

 

마지막으로, 커버링 인덱스를 사용하지 않았을 때의 getPosts 메서드의 시간을 측정한 결과입니다.

약 0.6초 정도의 시간이 걸리는 걸 확인할 수 있었습니다. 커버링 인덱스가 적용되지 않은 쿼리에 비해 훨씬 빠른 속도가 보장되는 것을 확인하였습니다. 

 

.. 추가 작성 예정