[10분 테코톡] 매트, 토르의 MySQL 성능 최적화 정리

전제 조건
1. MySQL 8과 MySQL 8의 기본 스토리지 엔진인 InnoDB를 기준으로 삼는다.
2. 쿼리 최적화 중에서도 인덱스를 활용한 쿼리 최적화에 집중
 
사용할 테이블

crew	study_log
id INT nickname VARCHAR(20) track VARCHAR(20) age INT	id INT crew_id INT title VARCHAR(50) content TEXT type VARCHAR(20) created_at DATETIME updated_at DATETIME

 
track : 백엔드와 프론트 두 가지 경우가 있다.
 
인덱스를 왜 쓸까?
 
DB에서 성능 최적화는 디스크 I/O와 관련이 많다.
요즘 SSD가 많이 보편화되었지만 여전히 메모리 I/O에 비해서는 많이 느리다.
성능개선을 한다고 하는 것은 디스크 I/O를 줄이는 것이 핵심이다.

또한 인덱스를 쓰면 조회는 빨라지지만 데이터 수정, 삭제, 생성은 느려진다는 말이 있다.
이것은 맞는 이야기이다.
 
그럼에도 불구하고 인덱스를 왜 생성해서 써야 할까?
일반적으로 웹 서비스의 경우 CRUD에서 R(Read)과 CUD(Create, Update, Delete)의 비율이 8:2에서 9:1이라고 한다. GET 요청이 압도적으로 많기 때문에 조회에서 성능 최적화를 하고 데이터 수정, 삭제는 조금 손해를 보더라도 전체적으로 이득을 보자는 취지이다.
 
 
ORDER BY, GROUP BY에서 이점
1) ORDER BY

SELECT *

FROM crew

WHERE nickname >= "매트" AND nickname <= "토르"

ORDER BY nickname;

위 쿼리를 보면 nickname에 의해서 ORDER BY, 즉 정렬을 하고 있다. 만약에 인덱스가 없다면 데이터를 다 읽어 와서
DB에서 직접 정렬했어야 할 것이다. 하지만 인덱스는 이미 정렬되어 있기 때문에 인덱스 순서대로 파일을 읽기만 하면 된다.
 
2) GROUP BY

각 track에서 nickname이 가장 빠른 사람들을 가져오는 쿼리를 보낼때 인덱스가 걸려 있는 경우에는 프론트엔드에서 nickname이 가장 빠른 '꼬재'를 읽고 나머지 데이터는 읽지 않고 바로 백엔드 track으로 넘어가서 '매트'만 읽으면 된다.
중간에 있는 데이터를 읽지 않았기 때문에 디스크 I/O를 많이 줄일 수 있다.

 

인덱스 실행 계획

1) ALL

- 테이블 전체를 스캔할 때

ALL은 Full Table Scan을 의미하며 데이터를 하나하나 다 읽는 것을 말한다. 디스크 I/O는 시간이 많이 걸리는 작업이기 때문에 Full Table Scan을 하면 성능이 좋지 않다.

Full Table Scan 일어나는 두 가지 경우가 있다. 인덱스가 없어서 Full Table Scan 하는 경우와 인덱스가 있는데도 Full Table Scan 하는 경우가 있다. 인덱스가 있는데도 Full Table Scan 하는 경우는 데이터 전체의 개수가 그렇게 많지 않거나 읽고자 하는 데이터가 전체 데이터의 25%를 넘어가면 인덱스가 있다 하더라도 Full Table Scan이 일어난다.
 
 
2) Range Scan
- 인덱스를 이용하여 범위 검색을 할 때

이상적으로 인덱스를 잘 걸었을 때 발생하는 실행 계획이다. id가 19 이상이고 27 이하인 데이터를 가지고 오라했을 때
Root에서부터 타고 내려가서 19는 17보다 크니까 오른쪽으로 가게 될 것이고 24보다는 작으니 왼쪽으로 가게 될 것이다.
이처럼 필요한 부분만 데이터를 읽게 된다. 즉, 탐색할 데이터가 줄기 때문에 디스크 I/O를 줄일 수 있다.
 

3) index
- 인덱스 전체를 스캔할 때

index라고 하면 Index Full Scan이기 때문에 전체 인덱스를 다 읽게 된다. 앞서 보았던 Full Table Scan 보다는 물론 성능이 좋다.(전체 데이터의 25%이하를 조회할 때) 왜냐하면 인덱스는 데이터 파일보다는 크기가 작기 때문이다. 하지만 Index Range Scan보다는 성능이 좋지 않다.

 

(사례)

기본 칼럼에 인덱스 적용

위와 같이 id, nickname, track, age가 있다. id의 경우에는 InnoDB니까 클러스터 인덱스(실제 데이터가 인덱스 정렬 순서로 정렬)가 미리 걸려있을 것이다. track, age에 인덱스를 걸기 전에 서비스의 특성상 무엇에 대한 조회가 많이 일어나는지를 우선 파악해야 한다.

서비스에서 nickname에 대한 조회가 많다고 가정한다. 그 다음에는 Cardinality(특정 집합의 유일한 값의 개수)가 높은 컬럼에 인덱스를 생성해야 한다. 닉네임의 경우 중복이 없고, track의 경우는 중복이 많아 Cardinality가 낮은 칼럼이다. 그러므로 nickname에 인덱스를 적용한다.
 

처음에 실행 계획이 ALL, 즉 Full Table Scan이었다가 인덱스를 걸고나니 range Index Range Scan으로 바뀐 것을 알 수 있다.
 

실제 성능도 조회 시간이 2.55초 걸리던 것이 0.72초로 많이 줄었다.

 

 

복합 인덱스

- 두 개 이상의 칼럼을 합쳐서 인덱스를 만드는 것
- 하나의 칼럼으로 인덱스를 만들었을 때 보다 더 적은 데이터 분포를 보여 탐색할 데이터 수가 줄어듬
- 결합 인덱스, 다중 컴럼 인덱스, Composite Index라고도 불림 

먼저 위와 같이 나이(age)순, 다음에는 nickname 순으로 정렬되어 있는 테이블이 있다. 예를 들어서 같은 26살이라 하더라도 가나다순에 의해서 동키콩, 매트, 토르 이런 식으로 정리되어있다. 
 

나이 순으로 정렬되어있기 때문에 26 이상의 사람들을 가져오려고 하면 위와 같이 빨간 네모 부분의 데이터를 탐색하면 된다. 그래서 이 정렬 기준을 이용해서 데이터 탐색 범위를 줄일 수 있습니다
 

나이가 26 이상이고 nickname이 '토르' 보다 뒤에 나오는 사람들을 가져 오려고 한다면 마찬가지로 나이(age) 순으로 정렬되어 있고 nickname 순으로 정렬되어있기 때문에 위와같이 빨간 네모부분인 '토르' 이후인 사람들을 가져오면 된다. 이번에도 탐색 범위가 줄어들었다.
 

만약 nickname을 기준으로 탐색하고자 한다면 위와 같이 원하는 만큼 데이터 탐색 범위를 줄일 수 없다. 따라서 Full Table Scan을 읽게 된다.
 
그러므로 이런 것들을 잘 고려해서 복합 인덱스를 사용해야 한다.

 

 

커버링 인덱스

인덱스를 사용하여 처리하는 쿼리 중에 가장 큰 부하를 차지하는 부분이 어디일까?
인덱스 검색에서 일치하는 키 값의 레코드를 읽는 것이다.

인덱스 검색에서 일치하는 키 값의 데이터를 읽기 위해서  위와같이 추가적인 디스크 I/O가 발생하게 된다.

N개의 인덱스를 검색할 때 최악의 경우 N번의 디스크 I/O가 발생할 수 있으며 쿼리 최적화의 가장 큰 목적은 이런 디스크 I/O를 줄이는 것이다.

crew 테이블에서 a 와 d 사이의 nickname을 가진 BACKEND 크루를 조회한다고 가정하고 원활한 조회를 위해서 nickname과 track으로 복합 인덱스를 추가해주었다.

실행 계획을 살펴보면 옵티마이저가 전체 데이터에서 20~25% 이상을 조회하는 경우
인덱스를 통해 조회하는 것보다 데이터 파일을 바로 읽는 것(Full Table Scan)이 효율적이라 판단해서 Full Table Scan이 발생한 것을 알 수 있다. 이를 커버링 인덱스를 통해 개선할 수 있다.

 

커버링 인덱스란?

인덱스로 설정한 컬럼만 읽어 쿼리를 모두 처리할 수 있는 인덱스이며 불필요한 디스크 I/O를 줄여서 조회 시간을 단축시킬 수 있다.

모든 컬럼을 조회하던 쿼리에서 nickname과 track으로 생성한 복합 인덱스에 대한 쿼리 조회로 개선하였다.

 

이것을 활용해 조회하면 추가적인 데이터 파일을 읽지 않고 인덱스만 읽기 때문에 불필요한 디스크 I/O 시간을 단축시킬 수 있다.

 

다시 한번 실행 계획을 살펴보면 type에 Index Range Scan이 발생한 것을 알 수 있고
커버링 인덱스를 타게 되면 Extra 컬럼에 'Using index'가 표시되는 것을 확인할 수 있다.

커버링 인덱스를 타지 않은 쿼리의 조회 속도와 커버링 인덱스를 탔을 때 조회 속도를 비교해보면 100만 건을 기준으로 대략 3초에서 0.5초로 줄어든 것을 확인할 수 있다.

 

인덱스에는 한가지 비밀이 숨겨져 있는데 PK(Primary Key)인 id를 함께 조회하는 쿼리는 어떤 인덱스를 탈까?

 

이전과 동일하게 커버링 인덱스를 활용하고 있다.
PK를 복합 인덱스로 설정하지도 않았는데 왜 이와같은 결과가 나오는 걸까?

 

이에 대한 해답은 InnoDB Secondary 인덱스의 특수한 구조 덕분이다.  
Leaf(리프)노드에는 실제 레코드 주소가 아닌 Clustered 인덱스가 걸린 PK를 주소로 가지고 있기 때문이다. 그렇기 때문에 nickname, track, id 모두 커버링 인덱스로 활용가능하다.

 

 

인덱스 컨디션 푸시다운

한 명의 크루(crew)는 N개의 학습 로그(study_log)를 작성할 수 있다. 학습 로그(study_log) 테이블에 type이라는 컬럼이 존재하는데 type은 share, question 등 학습 로그의 목적을 명시하는 컬럼이다. 비즈니스 로직상 type을 기준으로 조회가 많이 일어난다고 가정하고 type 기준으로 조회를 위해서 인덱스를 생성해주었다.

QUESTION이라는 type을 가진 10월 7일부터 10월 13일 사이의 학습로그를 조회하는 쿼리이다.

실행 계획을 살펴보면 생성했던 인덱스가 key 값으로 잘 활용돼서 인덱스를 탄 것을 확인할 수 있다. 언뜻 보면 인덱스가 잘 적용된 것처럼 보인다.

여기는 한 가지 함정이 숨겨져 있는데 바로 Extra 컬럼의 'Using where'이다.

Extra 칼럼에는 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 표시된다. 또한 내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 Extra 컬럼에서 나타내고 있다. Extra 컬럼의 'Using where'는 InnoDB 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤, MySQL 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소한 것을 말한다.

방금 생성했던 type을 기반으로 생성된 인덱스를 통해서 InnoDB 스토리지 엔진이 조건을 활용해서 인덱스 필터링을 거치고 디스크 파일에서 500612개의 데이터를 MySQL 엔진으로 전달한다. MySQL 엔진은 인덱스로 걸지 않은 생성일(created_at)을 기반으로 체크 조건을 통해서 9061개 데이터를 필터링해서 사용자에게 전달해준다. 결국 InnoDB 스토리지 엔진은 불필요하게 너무 많은 데이터를 디스크에서 읽어버린 셈이 되어버렸다. 이것을 복합 인덱스를 통해서 개선이 가능하다.

 

WHERE 조건에서 사용하고 있는 type과 생성일(created_at)을 기반으로 복합 인덱스를 생성해주었다.

 

다시 실행 계획을 살펴보면 방금 생성했던 복합 인덱스를 key로 잘 활용해서 Index Range Scan이 발동한 것을 알 수 있다. 추가적으로 Extra 컬럼에 'Using index condition'이 표시된 것을 확인 할 수 있다.

Extra 칼럼의 'Using index condition'은 인덱스 컨디션 푸시다운으로 인해 표시되는데 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown)은 MySQL이 인덱스를 사용하여 테이블에서의 행을 검색하는 경우의 최적화를 의미한다. ICP를 활성화하고 인덱스의 칼럼만 사용하여 WHERE 조건의 일부를 평가할 수 있는 경우에는
MySQL 엔진은 WHERE 조건 부분을 스토리지 엔진으로 푸시한다. ICP는 최신 버전의 MySQL을 사용하고 있으면 활성화되어 있는 옵션이다.

다시 한번 그림을 살펴보면 InnoDB 스토리지 엔진이 복합 인덱스로 설정된 type과 생성일(created_at)을 모두 인덱스 조건으로 활용해 디스크에서 오직 9061개 데이터만 읽어서 MySQL 엔진으로 전달한다. MySQL 엔진은 추가적인 필터링 과정 없이 그대로 9061개 데이터를 사용자에게 전달해준다. 불필요한 디스크 I/O를 줄여서 조회 시간을 단축할 수 있게되었다.

 

이것은 인덱스 컨디션 푸시다운을 타지 않은 쿼리와 인텍스 컨디션 푸시다운을 탔을 때 쿼리를 비교한 것인데 100만 건을 기준으로 6.37초에서 0.42초로 개선된 것을 확인할 수 있다. 그래서 실행 계획을 판단할 때 단순히 type만 보고 이 실행 계획이 인덱스를 탔다 안 탔다 판단 하는 것보다 Extra 컬럼까지 함께 고려해서 인덱스가 적절히 탔는지 함께 고려해줘야 한다.

 

다양한 인덱스
인덱스 스킵 스캔
루스 인덱스 스캔
유니크 인덱스
전문 검색 인덱스
옵티마이저
... 

---

참고
https://www.youtube.com/watch?v=nvnl9YgnON8&ab_channel=%EC%9A%B0%EC%95%84%ED%95%9C%ED%85%8C%ED%81%AC