배경 🐈
책 'Real MySQL 8.0'과 'MySQL을 더 빠르게, 성능 최적화 선택과 집중' 이라는 책을 가지고 현업 개발자 2분과 스터디를 진행하고 있습니다.
오늘은 'Real MySQL 8.0'의 '실행 계획'을 읽고 진행했던 스터디의 핵심 내용과 스터디를 하며 고민했던 부분 위주로 정리하고자 합니다.
📍 실행 계획
옵티마이저는 왠만하면 최적의 실행 계획을 세우지만 그렇지 않은 경우도 있다. 이를 보완하기 위해서 개발자는 EXPLAIN 명령어로 옵티마이저가 수립한 실행 계획을 확인할 수 있다. 우리는 이 실행 계획을 보고 쿼리가 어떻게 처리되는지 이해할 필요가 있다.
📌 통계 정보
MySQL 5.7 버전까지는 옵티마이저는 단순히 테이블과 인덱스에 대한 정보만 가지고 실행 계획을 수립했다. 실제 데이터 분포에 대한 정보가 부족해 비효율적인 방식으로 실행 계획이 수립될 수 있었다. 이를 해결하기 위해 옵티마이저는 데이터의 일부 값을 랜덤으로 가져와 참조하는 방식을 추가하였다. 따라서 옵티마이저는 테이블 및 인덱스 정보 뿐만 아니라 데이터 분포도를 참조할 수 있는 히스토그램 정보를 가지고 실행 계획을 수립한다.
물론 히스토그램이 실제 데이터 분포를 100% 반영할 순 없지만, 기존에 부족했던 정보에 비해 많이 보완되었다.
📌 실행 계획 분석
실행 계획에는 id 칼럼과 select_type, table, partitions, type, possible_keys , key 칼럼 등 많은 칼럼이 존재하지만, 이 중에서 가장 중점적으로 봐야 할 칼럼은 select_type, type, Extra 칼럼이다. 다른 칼럼과 칼럼별 자세한 정보는 공식 문서를 확인하자.
select_type 칼럼
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다. 대부분의 경우 조회 시 쿼리 성능 최적화가 목표일 것이기 때문에 select 쿼리별 종류를 확인할 수 있다는 점에서 유용하다.
그리고 select_type이 DERIVED인 경우 최적화가 가능한지 확인이 필요하다. DERIVED는 실행 결과를 메모리나 디스크에 임시 테이블을 생성해 저장하는 방식으로 처리하는데 상대적으로 조인에 비해 성능이 좋지 않기 때문에 해당 서브 쿼리를 조인으로 해결이 가능한지 확인이 필요하다.
type 칼럼
테이블의 레코드를 읽는 방식을 나타내는 칼럼이다. 풀 테이블 스캔(ALL)인지, 풀 인덱스 스캔(index)인지, 레인지 인덱스 스캔(range)인지 확인이 가능한 부분이 이 부분이다.
비효율적인 데이터 읽기 방식은 성능을 떨어뜨리는 주범이기 때문에 실행 계획에서 type 칼럼을 확인하는 것은 매우 중요하다‼️ type 칼럼이 index나 ALL 같이 성능이 안나오는 실행 계획이라면 나는 쿼리를 수정하던가 인덱스를 추가하던가 하는 방식으로 쿼리 최적화 방법을 찾을 것이다. 물론 인덱스를 생성 시에는 해당 인덱스의 필요성에 대해 깊이 고민하고 추가해야만 한다.
Extra 칼럼
성능과 관련된 중요한 정보들이 주로 표시된다. 가장 직관적으로 쿼리 성능의 특이점을 전달해주기 때문에 쿼리 최적화 시 가장 첫번째로 봐야 할 정보라고 생각한다.
그 중에서 중요한 Extra 칼럼 종류 몇 가지만 소개하자면,
Using index(커버링 인덱스)는 실제 데이터가 있는 디스크에서 데이터를 읽을 필요 없이 인덱스의 정보만을 이용하여 쿼리 처리가 가능할 경우 표시된다. 디스크 I/O가 한번도 없기 때문에 매우 성능이 좋은 방식이다.
Using index condition은 인덱스 컨디션 푸시 다운 방식을 사용한 경우 표시된다. 인덱스 컨디션 푸시 다운은 데이터 조회 시, 쿼리 조건절 부분을 인덱스를 이용해 최대한 필터링하여 반드시 필요한 데이터만 디스크에서 읽어오는 것이다. 무분별한 디스크 I/O로 인한 성능 저하를 방지할 수 있는 성능 최적화 방식이다.
Using where는 InnoDB 스토리지 엔진(디스크)에서 반환한 데이터를 MySQL 엔진에서 필터링을 거치는 경우를 의미한다. innoDB 스토리지 엔진에서 한번 거른 데이터를 MySQL 엔진에서 한번 더 걸렀다는 뜻이기 때문에 MySQL 엔진에서 걸러진 데이터의 양만큼 불필요한 디스크 I/O가 있었다는 의미이므로 성능 최적화가 필요하다. Using where은 주로 Using index condition의 성능 최적화 이전 결과로 표시된다.
아래 영상은 커버링 인덱스와 인덱스 컨디션 푸쉬 다운 방식을 공부할 때, 도움을 받았던 영상이다. 성능 최적화를 하고 변경된 실행 계획 결과를 직관적으로 보여주기 때문에 실행 계획에 대한 감도 잡힌다.
[10분 테코톡] 매트, 토르의 MySQL 성능 최적화
실행 계획 챕터는 내가 스터디 준비를 했는데, 실행 계획 결과을 설명해주는 챕터였다. 실행계획 챕터는 실행 계획의 종류가 많았기 때문에 어떤 방식으로 스터디를 진행하면 좋을지 고민했다. 그러다가 직접 실행 계획을 돌려보면 좋을 것 같았고, 퀴즈 형식으로 진행하면 스터디원들의 몰입도가 좋지 않을까 생각했다.
그래서 로컬에서 쿼리를 직접 돌려볼 수 있는 환경을 구성하고, 주요하게 보면 좋을 칼럼들 위주로 거기에 맞는 예제 쿼리를 준비하여 쿼리만 보고 실행계획이 어떻게 나올지 예측하고, 실제 실행계획과 비교하는 방식으로 스터디를 준비했다. 그리고 성능이 좋지 않은 경우, 쿼리 최적화를 진행해 바뀐 실행계획까지 확인했다. 실행 결과를 예측하고 예측이 맞았는지 틀렸다면 왜 틀렸는지 까지 확인할 수 있어서 스터디원들의 반응이 좋았다.
[참고 자료]
Real MySQL 8.0 (1권)
