[MySQL] 쿼리 실행 계획 (feat. 옵티마이저): 실행 절차, 실행 계획의 종류, Read Ahead, 병렬 처리 등등

 

안녕하세요. 우아한테크코스 5기 깃짱이라고 합니다. 

 

💋 인트로

DBMS를 사용하는 이유는, 데이터를 안전하게 저장, 관리하고, 빠르게 조회하기 위해서이다.

옵티마이저가 쿼리를 최적으로 처리될 수 있도록 실행 계획을 수립하지만, 항상 좋은 실행 계획을 만드는 것은 아니기 때문에, 사용자가 실행 계획을 보완할 수 있도록 EXPLAIN 명령어로 실행 계획을 확인할 수 있도록 해준다.

백엔드 개발자라면, 이 실행 계획을 이해하고, 최적화할 수 있어야 한다.

 

먼저, 대략적인 쿼리의 실행 절차에 대해 살펴보자.

💋 쿼리 실행

✔️ 쿼리 실행 절차

• SQL 파싱
• 사용자로부터 온 SQL문을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리함. (파스트리)
• 최적화 및 실행 계획 수립
• 파스트리를 확인하면서, 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을 지 선택
  • 불필요한 조건 제거
  • 복잡한 연산 단순화
  • 테이블의 조인이 필요한 경우, 어떤 순서로 읽을지 결정
• 옵티마이저에서 처리함.
• 실행 계획대로 수행
  • 스토리지 엔진으로부터 데이터를 가져옴
  • MySQL 엔진과 스토리지 엔진이 함께 참여함

 

✔️ 쿼리의 최적화 규칙

• 규칙 기반 최적화
• 현재 거의 사용되지 않음.
• 비용 기반 최적화
• 쿼리를 처리하기 위한 여러 가지 방법을 만들고, 각 작업의 비용 정보와 대상 테이블의 통계 정보를 이용해서 실행 계획별 비용을 산출하고, 비용이 최소인 처리 방식을 선택해 쿼리를 실행함.
• 현재 MySQL 8.0에서는 비용 기반 최적화를 규칙으로 하여 쿼리 실행 계획을 수립하고 있음.

 

다음으로는, 실행 계획에 따라 데이터를 읽어오는 방법에 대해서 알아보자.

 

💋 실행 계획의 종류

✔️ 풀 테이블 스캔

인덱스를 사용하지 않고, 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 것

 

언제 사용할까?

• 레코드 건수가 너무 적어서, 인덱스를 통해 읽는게 더 느린 경우 → 일반적으로 테이블이 페이지 1개
• SQL문에서 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 조건에 일치하는 레코드 건수가 너무 많은 경우

 

✔️ 풀 인덱스 스캔

인덱스를 처음부터 끝까지 스캔하는 것

 

이외에도 정말 많은 종류들이 존재한다.

✔️ 유니크 인덱스 스캔

• 테이블의 각 행에 대해 중복되지 않는 값을 가지는 인덱스
• 테이블을 스캔하는 대신 유니크 인덱스를 효율적으로 이용하여 데이터를 읽어옴.

 

✔️ 커버링 인덱스 스캔

• 테이블의 필요한 컬럼들을 모두 포함하는 인덱스
• 인덱스만을 읽어와 필요한 데이터를 모두 얻을 수 있으므로 테이블 접근을 최소화하여 성능을 향상

 

✔️ 인덱스 레인지 스캔

• 인덱스의 범위를 지정하여 해당 범위 내의 데이터만 읽어옴.
• 예를 들어, 날짜 범위나 값의 범위 등을 지정하여 원하는 데이터만 추출할 수 있음.

 

✔️ 인덱스 스캔과 테이블 스캔의 조합

• 인덱스로 필요한 데이터의 위치를 찾은 후 해당 위치의 데이터를 테이블에서 읽어오는 방식

 

✔️ 비트맵 인덱스 스캔

• 테이블의 여러 컬럼들에 대한 인덱스를 이진 형태로 저장하는 방식
• 여러 개의 인덱스들을 조합하여 원하는 데이터를 찾아옴.

 

✔️ 클러스터링 인덱스 스캔

• 테이블을 정렬된 상태로 디스크에 저장하는 인덱스
• (참고) InnoDB의 경우 primary key를 기준으로 데이터의 저장 위치가 결정되는 클러스터링 인덱스임.
• 인덱스에 따라서 디스크에 저장되는 물리적인 위치까지 결정되기 때문에, 테이블의 물리적인 순서에 따라 데이터를 읽어오므로 데이터의 접근을 최소화하여 성능을 향상

 

✔️ 히트맵 인덱스 스캔

• 데이터의 히트맵 정보를 인덱스로 저장하는 방식
• 데이터의 액세스 패턴을 분석하여 가장 빈번하게 액세스되는 데이터를 먼저 읽어오는 것이 목표

 

✔️ 예상 인덱스 스캔

• 통계 정보를 이용하여 인덱스 스캔의 비용을 추정하고, 가장 빠른 방법으로 데이터를 읽어올 수 있는 인덱스를 선택

 

✔️ 인덱스 스캔 힌트

• 데이터베이스 옵티마이저에게 원하는 방식으로 인덱스를 스캔하도록 지시하는 기능
• 쿼리에서 인덱스 스캔 힌트를 사용하면 옵티마이저는 해당 힌트를 확인하고, 지정된 인덱스 스캔 방식을 사용하여 데이터를 읽어옴.
• 옵티마이저가 자동으로 선택하는 인덱스 스캔 방식을 무시하고, 사용자가 명시한 방식으로 데이터를 읽어올 수 있기 때문에,특정 인덱스 스캔 방식이나 액세스 경로를 선호하는 경우에 원하는 방식으로 데이터를 가져올 수 있음.
• 그러나 인덱스 스캔 힌트를 남용하면 옵티마이저의 최적화 능력을 제한할 수 있으므로, 신중하게 사용해야 함.

 

 

💋 심화 주제

✔️ 풀 테이블 스캔 시에, MySQL은 디스크로부터 페이지를 하나씩 읽어올까? (InnoDB의 Read Ahead)

• MyISAM 스토리지 엔진
• 디스크로부터 페이지를 하나씩 읽어옴
• InnoDB 스토리지 엔진
  • 미리 데이터를 디스크로부터 읽어서 InnoDB의 버퍼풀에 가져다 두는 것
  • 처음 몇 개의 페이지는 Foreground Thread가 페이지 읽기를 실행함.
  • 연속 페이지 수 설정값인 innodb_read_ahead_threshold만큼의 페이지를 연속해서 읽고 난 시점부터는 읽기 작업을 Background Thread로 넘기고(최대 64개 페이지) Foreground Thread는 버퍼 풀에 올라온 데이터를 가져다 사용하기만 하면 됨.
• 특정 테이블에서 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해 Read Ahead 작업이 자동으로 실행됨.

 

✔️ 하나의 쿼리에서의 MySQL의 병렬처리 (MySQL 8.0 ~)

• 하나의 쿼리를 여러 쓰레드가 나누어 동시에 처리하는 것
• MySQL 8.0부터 도입되었으나, 아직까지는 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있음.

 

✔️ 병렬 처리를 못하던 MySQL 8.0 이전에는 Read Ahead를 어떻게 처리했을까?

• MySQL 8.0 이전 버전에서는 Read Ahead는 단일 스레드로 처리되었습니다.
• 단일 스레드로 처리되기 때문에 읽을 데이터를 예측하고 미리 읽어오는 작업과 동시에 다른 작업을 수행할 수 없습니다. 따라서 I/O 대기 시간 동안에는 다른 작업을 처리할 수 없어 전체적인 성능 저하를 가져왔습니다.
• 또한 데이터를 읽을 때마다 디스크에서 데이터를 읽어오기 때문에 I/O 대기 시간이 발생하고, 디스크 액세스의 부하가 증가합니다. 이로 인해 데이터를 읽어오는데 더 많은 시간이 소요될 수 있습니다.

 

💋 참고자료

• Real MySQL 8.0 옵티마이저와 힌트
• Real MySQL 8.0 실행계획

 

도움이 되었다면, 공감/댓글을 달아주면 깃짱에게 큰 힘이 됩니다!🌟