[MySQL] 옵티마이저와 힌트 1

MySQL에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다. MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당한다.

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌수 있다.

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

첫 번째 단계를 "SQL 파싱(Parsing)"이라고 하며, MySQL 서버의 "SQL파서"라는 모듈로 처리한다. SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러지며, 정상이라면 "SQL 파스 트리"가 만들어지고 이 SQL 파스 트리를 이용해 쿼리를 실행한다.

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 아래와 같은 내용을 처리한다.

• 불필요한 조건 제거 및 복잡한 연산의 단순화
• 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
• 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
• 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

두 번째 단계가 완료되면 쿼리의 "실행 계획"이 만들어진다. 세 번째 단계에서는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다. 첫 번째 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

옵티마이저의 종류

규칙 기반 최적화는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이지만, 현재는 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있다.

규칙 기반 최적화

• 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
• 통계 정보(테이블의 레코드 건수나 컬럼값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법이 만들어진다.
• 사용자의 데이터는 분포도가 매우 다양하므로 규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않음

비용 기반 최적화

• 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출
• 산출된 실행 방법대로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행

기본 데이터 처리

---

MySQL 서버에서 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 위해 사용하는 알고리즘을 간단히 살펴보자.

풀 테이블 스캔과 풀 인덱스 스캔

MySQL 옵티마이저는 아래와 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다. 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.

즉, 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 포그라운드 스레드(클라이언트 스레드)가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한 번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시킨다. 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리되는 것이다.

리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용된다.

mysql> SELECT COUNT(*) FROM employess;

단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문에 위 쿼리의 실행 계획은 풀 테이블 스캔보다는 풀 인덱스 스캔을 하게 될 가능성이 높다. 일반적으로 인덱스는 테이블의 2~3개 칼럼만으로 구성되기 때문에 테이블 자체보다는 용량이 작아서 훨씬 빠른 처리가 가능하다.

병렬 처리

MySQL 8.0에서는 innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있지만, 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없다.

아래와 같이 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

mysql> SET SESSION innodb_parallel_read_threads=1;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.32 sec)

mysql> SET SESSION innodb_parallel_read_threads=2;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.20 sec)

mysql> SET SESSION innodb_parallel_read_threads=3;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.18 sec)

mysql> SET SESSION innodb_parallel_read_threads=4;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.13 sec)

위 실행 결과를 보면 병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 줄어드는 것을 확인할 수 있다. 하지만 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있다.

ORDER BY 처리(Using filesort)

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나뉜다.

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort buffer)라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다. 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

sort_buffer_size 시스템 변수의 설정값이 무조건 크면 메모리에서 모두 처리되니 빨라질 것으로 예상하지만, 실제 결과는 그렇지 않다. 또한 리눅스 계열의 운영체제에서는 너무 큰 sort_buffer_size를 사용하는 경우, 큰 메모리 공간 할당때문에 성능이 훨씬 떨어질 수도 있다.

MySQL은 글로벌 메모리 영역과 세션(로컬) 메모리 영역으로 나눠서 생각할 수 있는데, 정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역에 해당한다. 즉, 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아니다. 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미한다.

요약

• 옵티마이저는 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립한다.
• 쿼리 실행 절차
  • 첫 번째 단계는 SQL 파싱(SQL 문장의 문법을 분리하여 파스 트리가 만들어짐)이 이루어진다.
  • 두 번째 단계는 SQL 파스 트리를 참조하여 실행 계획은 만들어낸다.
  • 세 번째 단계는 실행 계획대로 레코드를 읽어 와서 조인하거나 정렬하는 작업을 수행한다.
• 풀테이블 스캔
  • 풀 테이블 스캔이 실행되면 처음 몇개의 데이터 페이지는 포그라운드 스레드가 읽는다.
  • 포그라운드 스레드는 특정 시점부터 읽기 작업을 백그라운드 스레드로 넘기고, 백그라운드 스레드는 한 번에 4개 또는 8개씩 페이지를 읽으며 그 수를 증가시킨다.
  • 백그라운드 스레드가 한 번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해두기 때문에 포그라운드 스레드는 버퍼 풀에서 데이터를 가져다 사용하기만 하면 되므로 퀴리가 빨리 처리된다.
• 풀 인덱스 스캔
  • 단순 레코드의 건수가 필요한 쿼리의 경우, 테이블 자체보다 용량이 작은 풀 인덱스 스캔 방식을 선택할 가능성이 높다.
• 병렬 처리
  • MySQL 8.0에서는 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있지만, 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없다
• ORDER BY 처리
  • 정렬 처리 방법으로는 인덱스를 이용하는 방법과 쿼리가 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나뉜다.
• 소트 버퍼
  • MySQL이 정렬을 수행하기 위해 사용하는 별도의 메모리 공간
  • sort_buffer_size라는 시스템 변수를 이용해 최대 사용 가능한 소트 버퍼의 공간을 설정할 수 있지만, 무조건 크게 설정한다고 빨라지는 것은 아니다.
  • 소트 버퍼는 세션 메모리 영역에 해당하므로, 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커진다.

Reference
백은빈, 이성욱. 『Real MySQL 8.0』. 위키북스, 2022