작년에 책을 통해 읽은 내용인데, 시간 지나가면 까먹고 책을 다시 펼쳐볼 때가 많아서 블로그 글도 작성할 겸 & 핵심 내용 정리 할 겸을 목표로 작성한다.
"아는 만큼 보인다"라는 말이 지식을 쌓을수록 얻는 재미 중 하나인 것 같다.
쿼리 실행 계획 절차
MySQL 서버에서는 쿼리가 실행되는 과정을 크게 3가지로 나눌 수 있음
- 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)함
- SQL의 파싱 정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택함
- 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴
첫 번째 단계를 SQL 파싱이라고 하며, MySQL 서버의 "SQL 파서"라는 모듈로 처리 SQL문장이 잘못됐다면, 이 단계에서 걸러지며, 해당 단계에서 "SQL 파스 트리"가 만들어짐. MySQL은 SQL 문장 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행함
두 번째 단계는 "최적화 및 실행 계획 수립" 단계이며, MySQL 서버의 "옵티마이저"에서 처리함. 또한 두 번째 단계가 완료되면 쿼리의 "실행 계획"이 만들어짐.
- 구체적으로는 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있는 경우, 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
합니다.
세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코들을 조인하거나 정렬하는 작업을 수행. 첫 번째 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하고, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여하여 처리함.
옵티마이저 종류
옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 함.
- 비용 기반 최적화
- 규칙 기반 최적화
규칙 기반 최적화는 기본적으로 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식임 -> 이미 오래전부터 많은 DBMS에서 사용하지 않음
비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출함.
풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔이란?
인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미함. MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택함
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
일반적으로, 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요함
대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있음
하지만, MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇개씩 페이지를 읽어올지 설정하는 시스템 변수는 없음
나도 단순하게 생각하고 있던 것은 디스크로부터 페이지를 하나씩 읽어오는 것이라고 생각했는데, 잘못된 생각이었다.
MySQL에서 InnoDB 스토리지 엔진은 특정 테이블의 연속된 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read Ahead) 작업이 자동으로 시작된다. 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미함.
즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Forground Thread, 클라이언트 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
ORDER BY 처리
레코드를 가져올 때, 정렬은 필수적으로 많이 사용된다. 정렬을 위해 사용되는 여러 메커니즘들이 있는데 정확히 이해하고 옵티마이저가 어떻게 수행하는지 아는 것 또한 중요하다고 할 수 있다.
정렬을 처리하는 방법에는 인덱스를 이용하는 방법과 Filesort라는 별도의 처리 방식이 있습니다.
- MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 "Using Filesort" 메시지가 표시되는지 여부로 판단할 수 있음.
Sort Buffer
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 Sort Buffer(소트 버퍼)라고 함. 소트 버퍼는 정렬이 필요한 경우에만 할당됨 -> 인덱스를 활용한 정렬은 굳이 Sort Buffer가 필요 없다는 뜻. 왜냐하면, 이미 인덱스를 통해 정렬되어 있기 때문
따라서, 메모리의 Sort Buffer에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둔다. 그리고 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장함. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 함. 이 병합 작업을 멀티 머지(Multi-Merge)라고 표현한다
다음 포스트는 정렬 처리 방법에서 조인에서 드라이빙 테이블만 정렬과 조인에서 조인 결과를 임시 테이블로 저장 후 정렬할 때, 일어나는 과정을 다루어보겠습니다.
Reference
- Real MySQL 8.0(백은빈, 이성욱 지음)
