[한끼족보] 인덱스를 활용한 조회 속도 개선기

문제 상황

한끼족보에서는 사용자가 마음에 드는 식당을 ‘좋아요’로 저장하고, 이후 좋아요한 식당 목록을 조회할 수 있다. 기존에는 전체 목록을 한 번에 내려주는 방식이었지만, 커서 기반 페이지네이션을 도입해 응답 시간을 개선했고 그 과정은 이전 글에 정리했다. 이번 글에서는 페이지네이션을 넘어 더 빠른 응답을 얻기 위해 실행 계획을 분석하고, 그 결과를 바탕으로 쿼리를 튜닝한 과정을 공유하고자 한다.

실행 계획을 분석해보자.

개발 환경 데이터베이스에서 식당 정보 10만개를 삽입하고 각 유저가 적어도 25개의 식당에 대해 좋아요를 눌렀다고 가정하고 테스트 환경을 구성한 뒤, 쿼리 실행계획을 확인해보았을 때, 다음과 같은 실행계획을 확인할 수 있었다.

SELECT s.*
FROM heart h
JOIN store s ON h.store_id = s.store_id
WHERE h.user_id = 10
ORDER BY h.heart_id DESC;

 

참고로, heart 테이블의 DDL은 이어서 첨부된 사진과 같다.

EXPLAIN 

EXPLAIN 명령어를 통해 상세한 실행계획을 확인해보았을 때, 도출된 계획은 아래와 같았다.

+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                      | key                 | key_len | ref               | rows | filtered | Extra                                    |
+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------------------+
|  1 | SIMPLE      | h     | NULL       | ref    | uk_heart_user_store,fk_heart_store | uk_heart_user_store | 9       | const             |   30 |   100.00 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY                            | PRIMARY             | 8       | hankki.h.store_id |    1 |   100.00 | NULL                                     |
+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+------------------------------------------+

최적화를 해보자

FileSort를 없애보자!

좋아요한 식당 목록을 가져올 때, 기획 상 최신에 좋아요를 누른 좋아요 식당을 먼저 보여주어야 했기 때문에 Order By절을 사용하여 기본키인 id값을 기준으로 내림차순 정렬하여 반환하고 있다. 이때, 현재의 실행계획을 확인해보면 Using filesort 라는 키워드를 확인할 수 있는데 정렬 시에 인덱스를 활용하지 못했음을 의미한다. 때문에 옵티마이저는 where 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리한 것이다. 물론 현재 상황처럼 정렬해야 할 레코드가 많지 않으면 메모리에서 FileSort가 처리되므로 충분히 빠르다. 하지만, 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느려지게 된다. 그렇다면, 왜 현재의 쿼리는 인덱스를 활용하지 못하고 있는 것일까?

현재의 인덱스

DDL을 확인해보면, 본래의 유니크 인덱스를 확인해보았을 때, (user_id, store_id) 순으로 생성되어 있는 것을 확인할 수 있다. 또한, 외래키 제약 조건으로 인해 Store_id와 User_id에 대해서는 인덱스가 생성되어 있다. 때문에 where 조건 절에서는 해당 인덱스들을 활용하여 효율적으로 처리되었다. 반면, 정렬 작업의 경우는 어떨까? 먼저 user_id로 효율적으로 필터링 된 레코드들을 반환한다. 하지만 이는 유니크 인덱스인 (user_id, store_id)를 활용하여 처리되었기 때문에 heart_id가 뒤섞여져있다. 복합 인덱스의 경우에는, 칼럼 정렬이 인덱스가 생성된 칼럼 순서대로 정렬되기 때문이다. 즉, 인덱스의 두번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있기 때문에 두번 째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다. 때문에 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치가 상당히 중요하다. 그렇다면 어떻게 heart_id도 인덱스를 활용할 수 있도록 할 수 있을까?

수정된 인덱스

답은 간단하다. user_id로 필터링 된 후, 반환된 레코드들이 Heart_id로 정렬되어 있을 수 있도록 복합 인덱스를 생성하면 된다. 아래 명령어를 통해 유니크 인덱스를 수정해보자.

-- 기존 인덱스 삭제 후 재생성
DROP INDEX uk_heart_user_store ON heart;
CREATE UNIQUE INDEX uk_heart_user_store ON heart (user_id, heart_id, store_id);

 

이렇게 변경한 후, 실행계획을 확인해보면 아래와 같다.

+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                      | key                 | key_len | ref               | rows | filtered | Extra                                         |
+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | h     | NULL       | ref    | uk_heart_user_store,fk_heart_store | uk_heart_user_store | 9       | const             |   30 |   100.00 | Using where; Backward index scan; Using index |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY                            | PRIMARY             | 8       | hankki.h.store_id |    1 |   100.00 | NULL                                          |
+----+-------------+-------+------------+--------+------------------------------------+---------------------+---------+-------------------+------+----------+-----------------------------------------------+

와! FileSort가 사라졌다. 해당 복합 인덱스를 새롭게 정의하면서 user_id = 10 조건으로 구간을 찾을 때 이미 heart_id가 정렬되어 있기 때문에 정렬 작업이 불필요해진 것이다. 또한, 해당 유니크 인덱스는 heart 테이블의 모든 칼럼을 포함하고 있기 때문에 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있어, "Using Index"가 표시되었다. 이렇게 인덱스만으로 처리되는 것을 커버링 인덱스라고 한다.

Backward index scan

하지만 추가적으로 궁금한 점이 생겼다. Backward index sacn이 뭘까? 이는 인덱스의 정렬 및 스캔 방향과 관련이 깊다. 인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이나 내림차순으로 정렬되어 저장된다. 하지만 어떤 인덱스가 오름차순으로 생성되었다고 해서 그 인덱스를 오름차순으로만 읽을 수 있다는 뜻은 아니다. 그 인덱스를 거꾸로 끝에서부터 읽는다면 내림차순으로 정렬된 인덱스로도 사용될 수 있다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

인덱스 스캔 방향

인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. 오름차순으로 생성된 인덱스를 정순으로 읽으면 자동으로 오른차순으로 정렬된 결과가 되고, 역순으로 읽으면 그 결과는 내림차순으로 정렬된 상태가 되는 것이다.

현재의 우리 쿼리는 heart_id가 오름차순으로 인덱스가 생성되었지만 정렬 과정에서 내림차순으로 진행하고 있기 때문에 옵티마이저는 해당 인덱스를 역순으로 읽어 Backward index scan이라는 실행계획이 뜬 것이다.

내림차순 인덱스

MySQL 8.0버전 부터는 정렬 순서를 혼합한 인덱스도 생성할 수 있게 되었다.

인덱스를 역순으로 스캔하느냐, 정순으로 스캔하느냐에 따라서도 실행 속도에 차이가 발생한다. MySQL 서버의 InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지 간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만, 실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없기 때문이다.

 

InnoDB 페이지 내부에서 레코드들은 단방향(next) 포인터로만 연결되어 있다. 이 때문에 인덱스를 정순(ASC)으로 스캔할 때는 항상 동일한 방향으로 페이지 잠금을 획득할 수 있어 데드락 위험이 적고, 다음 페이지 접근이 예측 가능해 read-ahead(미리 읽기) 최적화도 활용할 수 있다.

반면 역순(DESC) 스캔은 페이지를 거꾸로 탐색해야 하지만, 역방향 포인터가 존재하지 않기 때문에 다시 탐색 과정이 필요하다. 이때, B+Tree 인덱스 스캔에서는 페이지 단위로 잠금를 걸고 데이터를 읽는데, 역순 스캔에서 여러 스레드가 동시에 역방향으로 잡으면, 서로 반대 방향에서 잠금를 잡으려다 데드락 발생 위험이 높아지게 된다. (반면, 정순 스캔에서 항상 잠금의순서가 일정하기 때문에여러 스레드가 동시에 같은 인덱스를 스캔해도 deadlock이 거의 없다.) 또한 역순 스캔은 앞으로 어떤 페이지를 읽을 지 미리 예측하기 어렵기 때문에 read-ahead 최적화가 거의 적용되지 않고 매번 I/O작업이 발생할 수 있어 성능이 떨어지게 된다.

 

 

현재 좋아요 목록을 가져오는 쿼리는 ORDER BY ... DESC 하는 쿼리가 빈번하게 실행되는 경우이기 때문에 내림차순 인덱스를 생성하여 인덱스를 역순으로 스캔하지 않도록 하여 최적화를 더욱 시킬 수 있다. 특히 해당 쿼리 외에는 heart_id를 정렬하는 쿼리가 존재하지 않기 때문에 더더욱 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상에도 완화가 될 것이라 판단하였다.

-- 기존 인덱스 삭제 후 재생성
DROP INDEX uk_heart_user_store ON heart;
CREATE UNIQUE INDEX uk_heart_user_store ON heart (user_id ASC, heart_id DESC, store_id ASC);

때문에 인덱스를 다시 heart_id를 내림차순으로 정렬된 인덱스로 수정하였고 이때 다시 실행계획을 확인해보니, 역순 스캔이 사라진 것을 확인할 수 있었다.

+----+-------------+-------+------------+--------+-----------------------------------------------------------------+---------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                   | key                 | key_len | ref               | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+-----------------------------------------------------------------+---------------------+---------+-------------------+------+----------+--------------------------+
|  1 | SIMPLE      | h     | NULL       | ref    | uk_heart_user_store,uk_heart_user_store_asc_test,fk_heart_store | uk_heart_user_store | 9       | const             |   30 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY                                                         | PRIMARY             | 8       | hankki.h.store_id |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+-----------------------------------------------------------------+---------------------+---------+-------------------+------+----------+--------------------------+

테스트 환경에서의 레코드의 수가 많지 않음에도 , EXPLAIN ANALYZE 명령어를 통해 확인했을 때 기존의 역순 스캔에 비해 정순 스캔이 52%정도(0.389ms -> 0.188ms) 더 빨라진 것을 확인할 수 있었다. 

// 역순 스캔 - 기존
-> Nested loop inner join  (cost=37.1 rows=30) (actual time=0.0992..0.389 rows=30 loops=1)
    -> Filter: (h.store_id is not null)  (cost=4.12 rows=30) (actual time=0.0626..0.0905 rows=30 loops=1)
        -> Covering index lookup on h using uk_heart_user_store_asc_test (user_id = 10) (reverse)  (cost=4.12 rows=30) (actual time=0.0609..0.0865 rows=30 loops=1)
    -> Single-row index lookup on s using PRIMARY (store_id = h.store_id)  (cost=1 rows=1) (actual time=0.0096..0.00964 rows=1 loops=30)
    

// 정순 스캔 - 수정 후
-> Nested loop inner join  (cost=37.1 rows=30) (actual time=0.0445..0.188 rows=30 loops=1)
    -> Filter: (h.store_id is not null)  (cost=4.12 rows=30) (actual time=0.031..0.0422 rows=30 loops=1)
        -> Covering index lookup on h using uk_heart_user_store (user_id = 10)  (cost=4.12 rows=30) (actual time=0.0303..0.0392 rows=30 loops=1)
    -> Single-row index lookup on s using PRIMARY (store_id = h.store_id)  (cost=1 rows=1) (actual time=0.00461..0.00463 rows=1 loops=30)