N+1 문제 해결 지난 n+1 문제 해결후 조회 성능이 절망적이었기 때문에 인덱스을 적용해보려한다.

문제

지난 n+1 개선 후 발생한 문제상황이다.

쿼리수는 32개에서 4개로 줄어들었지만 artist와 album을 조인한 후 조회, 정렬하는 복잡한 쿼리가 SQL TIME의 대부분을 차지하고 있다. (653ms 중 649ms)

정확한 쿼리는 아래와 같다.

1
2
3
4
5
SELECT * FROM album a1
JOIN artist a2 ON a2.id = a1.artist_id AND a2.is_deleted = false
WHERE a1.is_deleted = false
ORDER BY a1.created_at DESC
LIMIT 10;

Explain을 통해 해당 쿼리를 확인한 결과 album Table을 full scan하고 있고, 정렬을 메모리로 수행하고 있다는 정보를 확인할 수 있었다.

이게 무슨 뜻이냐면……

옵티마이저

어떤 점이 문제인지 이제부터 알아보자. 아래 내용은 서적 Real MySQL 8.0를 참고했다.

풀 테이블 스캔

• 인덱스를 사용할 수 있는 적절한 조건이 없는 경우
• 테이블의 레코드 건수가 작아서 인덱스를 사용하는 것보다 풀 테이블 스켄을 하는 편이 더 빠른경우
• 인덱스 레인지 스캔을 사용할 수 있더라도 조건 일치 레코드 건수가 너무 많은 경우

위 3가지 경우에 옵티마이저는 풀 테이블 스캔을 한다.

풀 테이블 스캔이란, 말 그대로 테이블에 존재하는 전체 데이터를 처음부터 끝까지 읽으며 요청을 처리하는 작업이다.

나의 경우 단순히 테이블에 사용할 수 있는 인덱스가 존재하지 않았기 때문에 풀 테이블 스캔이 실행되었으며, album 테이블에 존재하는 19852개의 Row를 읽었다.

그러나 where절에 해당하는 인덱스를 사용하고 있다면 테이블의 전체 데이터를 읽을 필요가 없다.

인덱스 테이블의 데이터들은 B-tree 형태로 저장되어있기 때문에, where절에 해당하는 노드만 탐색하는 것으로 탐색 범위를 줄일 수 있다.

Filesort

Order By가 사용되는 경우 옵티마이저는 정렬을 처리하기 위해 다음 3가지 방법 중 하나를 사용한다.

아래로 갈수록 처리속도가 떨어진다. filesort는 임시 테이블을 이용한 정렬보다는 빠르나, 인덱스를 사용한 정렬보다는 느리다.

• filesort: Where 절 검색조건에 사용한 테이블(=드라이빙 테이블)이 정렬조건으로 사용된 경우, Join 전에 정렬이 가능
• temporary: Where 절 검색조건에 사용한 테이블과 정렬 조건으로 사용되는 테이블이 달라 Join 후에 정렬을 해야하는 경우 (filesort에 비해 정렬해야하는 레코드의 수가 많다.)

그러나 Order By에 인덱스가 사용되는 경우 사실 정렬 작업은 따로 필요 없다.

인덱스란 정렬해놓은 컬럼의 사본이다. 따라서 인덱스 테이블에 저장된 데이터는 이미 정렬된 상태이며, 인덱스를 순서대로 읽기만 하면 된다.

실제로 쿼리에 Order By를 사용하지 않아도 조회는 데이터가 정렬된 것을 확인할 수 있다. (그래도 OrderBy는 쓰도록 하자.)

Limit

일반적으로 Limit은 처리 결과의 일부만 가져오기에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 한다.

그러나 인덱스를 사용하지 않은 경우 Limit는 성능 측면에서 큰 의미가 없다.

즉, 페이지네이션이 DB 성능상 큰 장점이 되지 않는다.

서버가 쿼리를 처리하는 방식은 아래 두가지로 구분할 수 있다.

• 스트리밍 방식: 조건에 일치하는 레코드가 검색되면 바로바로 클라이언트로 전송(마지막 레코드가 조회되기 전이라도 조회된 레코드들 바로 바로 전송)
• 버퍼링 방식: 모든 레코드를 가져온 다음에 처리 후 전공

Order By나 Group By와 같은 처리가 사용되는 경우 쿼리는 ‘버퍼링 방식’을 통해 처리된다. 모든 데이터가 도착한 후에야 정렬과 그루핑 처리를 할 수 있기 때문이다.

따라서 LIMIT과 같은 조건을 사용하더라도, LIMIT 조건과 관계없이 Where절과 일치하는 모든 데이터를 조회하고, 처리한 후에야 LIMIT을 사용하여 잘라내게 된다.

인덱스를 사용하면 Order By나 Group By를 사용하더라도 스트리밍 방식의 처리가 가능해진다.

이는 filesort에서 설명했다싶이 적절한 인덱스가 존재하는 경우, 데이터의 사본이 인덱스 테이블에 정렬된 순서로 저장되기 때문이다.

따라서 모든 데이터를 조회하지 않고 LIMIT 조건에 해당하는 데이터만을 읽어올 수 있다.

다만 MySQL에서 스트리밍을 사용하더라도, 클라이언트에서 JDBC를 사용하는 경우 JDBC 자체에서 데이터를 버퍼링한다. (모든 레코드가 전달될 때까지 기다렸다가 클라이언트에게 반환)

인덱스

적용 기준

위 문제를 해결하기 위해 인덱스를 적용해야한다는 결론에 도달했다. 그렇다면 이번엔 어떤 컬럼을 인덱스로 사용하는가에 대해 고민할 차례이다.

적용하자
- 그룹핑, 정렬 기준으로 자주 사용되는 컬럼 → 인덱스에서 정렬된 데이터 가져온다.
- WHERE, JOIN에 자주 사용되는 컬럼 →B-tree를 사용할 수 있어 스캔 시간 감소

적용하지 말자
- 자주 업데이트되는 컬럼 (ex. updated at) → 인덱스는 조회가 빨라지는 대신 수정,삽입 성능에 악영향을 준다.
- 값의 종류가 거의 없는 컬럼(ex. Boolean 타입의 status) → 비효율적이다.
- 테이블이 작은 경우 → 인덱스 사용보다 풀 테이블 스캔이 더 빠를 수 있다.

나의 경우 is_deleted와 created_at을 사용한 복합 인덱스를 사용하기로 결정했다.

조회 시 대부분의 경우 created_at을 사용하여 정렬 후 전송하며, 삭제 데이터를 필터링해주는 작업을 백엔드에서 처리하기 때문이다.

is_deleted는 boolean 타입이므로 단독 인덱스로 사용하기엔 비효율적이나 조회 시 created_at과 거의 함께 사용되기에 때문에 내린 결정이다.

인덱스 적용

1
2
3
4
5
6
7
8
9
10
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| album |          0 | PRIMARY                     |            1 | id          | A         |       19852 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| album |          0 | UKhklhy2353uyf7g5f7luipf8rs |            1 | uuid        | A         |       19314 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| album |          1 | FKmwc4fyyxb6tfi0qba26gcf8s1 |            1 | artist_id   | A         |       12623 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| album |          1 | created_deleted_album_index |            1 | created_at  | A         |           9 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| album |          1 | created_deleted_album_index |            2 | is_deleted  | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)

Unique 옵션을 지닌 컬럼과 Primary 키의 경우 자동적으로 인덱스가 생성되기 때문에 따로 인덱스를 생성 해줄 필요 없다.

CREATE INDEX created_deleted_album_index ON album (created_at, is_deleted);

명령을 통해 인덱스를 생성해주었다.

인덱스 생성 후 Explain을 통해 쿼리 실행 계획을 확인한 결과 full scan이 아닌 index를 사용하고 있음을 확인할 수 있다.

예측한 탐색 행 수 또한 19852개에서 99개로 줄어들었으며 filesort 또한 하지 않았다.

개선 후 성능 비교

동시 접속한 100명 사용자가 10개의 앨범을 조회하는 요청을 10회 반복하는 시나리오를 기준으로 테스트했다.

인덱스 적용 전과 비교하여 TPS는 15.4s → 158.1s로 향상했으며 평균 응답 시간또한 6.1s에서 0.5s초로 향상했다.

SQL time은 기존 653ms에서 39ms(최대값) 정도로 감소했다. 특히 과거 SQL time의 대부분을 차지하던 artist와 album을 조인한 후 select하는 쿼리의 실행 시간은 649ms에서 7ms정도로 급감했다.

이로써 인덱스를 통한 성능개선까지 마치게 되었다.

불편했던 TPS 성능이 확연하게 오른 것을 보니 기분이 좋다. 특히 학대당하던 CPU가 개선 후에는 사용률이 100%에 다다르지 않고도 다수의 사용자를 소화 가능해진 점도 좋았다. 이 점도 응답 시간 감소에 많은 영향을 준 듯