09. 옵티마이저와 힌트

옵티마이저

MySQL 서버로 요청된 쿼리의 결과는 동일하지만 내부적으로 그 결과를 만드는 방법은 매우 다양하다. 그 역할을 옵티마이저가 담당하며 다양한 방법 중에서 최적의 방법과 최소의 비용이 소모될지 결정하게 된다.

MySQL에서는 EXPLAIN 명령을 통해 쿼리의 실행 계획을 확인할 수 있지만, 상당히 많은 정보가 출력되기 때문에 옵티마이저가 실행하는 최적화에 대해 어느정도 지식이 필요하다.

실행 계획을 이해할 수 있어야만 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

쿼리 실행 절차

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

1번 단계

1번 단계를 "SQL 파싱(Parsing)" 이라고 하며, "SQL 파서" 라는 모듈로 처리한다. 이 때, SQL 문이 문법적으로 잘못됐다면 이 단계에서 걸러지는 것이다. 또한, 이 단계에서 MySQL 서버가 이해할 수 있는 "SQL 파스 트리"가 만들어진다.

SQL 파스 트리를 기반으로 MySQL 서버 내에서 쿼리문을 실행하는 것이다.

2번 단계

2번 단계는 1번 단계에서 만들어진 SQL 파스 트리를 기반으로 다음과 같은 내용을 수행한다.

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

이 밖에도 더 많은 작업을 하지만, 대표적인 작업은 위와 같다.

2번 단계는 "최적화 및 실행 계획 수립" 단계로 MySQL 서버의 "옵티마이저"에서 처리한다. 또한 이 과정이 완료되면 "실행 계획"이 만들어진다.

3번 단계

3번 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

1번, 2번 단계는 거의 MySQL 엔진에서 처리하며, 3번 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여한다.

옵티마이저 종류

옵티마이저는 데이터베이스 서버에서 두뇌 같은 역할을 담당한다.

비용 기반 최적화 (Cost-based optimizer, CBO)
규칙 기반 최적화 (Rule-based optimizer, RBO)

대부분의 DBMS는 비용 기반 최적화 방법을 채택하고 있으며, MySQL 역시 마찬가지다.

비용 기반 최적화 (Cost-based optimizer, CBO)

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

규칙 기반 최적화 (Rule-based optimizer, RBO)

규칙 기반 최적화(RBO)는 각 테이블이나 인덱스의 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되던 최적화 방법이다.

단순 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
통계 정보(테이블의 레코드 건수나 컬럼 값의 분포도)를 조사하지 않음
- 따라서, 같은 쿼리에 대해서 거의 동일한 실행 방법을 만들어 낸다.
사용자의 데이터 분포도가 매우 다양하기 때문에 규칙 기반 최적화는 현재 많이 사용되지 않고 있음.

기본 데이터 처리

모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등 기본 데이터 가공 기능을 가지고 있다. 하지만 결과는 동일하더라도 RDBMS별로 그 결과를 만들어내는 과정은 천차만별이다. MySQL 서버는 어떤 알고리즘을 사용하여 가공하는지 알아보자.

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

MySQL 옵티마이저가 풀 테이블 스캔을 선택하는 조건

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

DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. 하지만, MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 페이지를 읽어올지 설정하는 시스템 변수는 없다.

이 내용은 InnoDB 에서는 다른 상황인데, InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다.

리드 어헤드(Read ahead)란 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것을 말한다.
즉, 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 백그라운드 스레드가 담당한다.

MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 통해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있다. 이 값은 기본 설정으로 충분하지만, 데이터 웨어하우스용으로 MySQL을 사용한다면 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋은 방법이다.

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

병렬 처리

MySQL 8.0 버전부터는 용도가 한정되어 있긴 하지만 처음으로 MySQL 서버에서도 쿼리의 병렬 처리가 가능해졌다.

MySQL 8.0에서는 innodb_parallel_read_threads 시스템 변수를 이용하여 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있다.

병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 줄어들지만, 서버에 장착된 CPU의 코어 개수는 넘어설 경우 오히려 성능이 떨어질 수 있다는 점을 주의하지만.

ORDER BY 처리 (Using filesort)

대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다. 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 "Filesort" 라는 별도의 처리를 이용하는 방법이 존재한다.

장점

단점

인덱스 이용

조회 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어 순서대로 읽기만 하면 되므로 매우 빠르다.

INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 필요하다. 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다.

Filesort 이용

인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점이 된다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.

정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답속도가 느리다.

항상 Filesort 정렬 작업을 거쳐야 하는 것은 아니다. 하지만, 모든 정렬을 인덱스를 이용하여 튜닝하기란 거의 불가능하다.

정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
GROUP BY 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
UNION 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
랜덤하게 결과 레코드를 가져와야 하는 경우

Filesort 정렬 작업이 일어났을 경우 실행 계획(EXPLAIN)의 Extra 컬럼에 "Using filesort" 메시지가 표시된다. MySQL 정렬 특성을 이해하면 쿼리를 튜닝할 때 조금 더 빠른 쿼리가 될지 쉽게 판단할 수 있을 것이다.

소트 버퍼 (Sort Buffer)

소트 버퍼란 MySQL이 정렬을 수행할 때 별도의 메모리 공간을 할당받아서 처리하는 영역을 말한다.

정렬이 필요한 경우에만 할당
레코드 크기에 따라 가변적으로 증가
최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size 시스템 변수로 설정

소트 버퍼는 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

여기서 문제가 되는 것은 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 큰 경우이다. MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.

메모리의 소트 버퍼에서 정렬을 수행하고
그 결과를 임시 디스크로 기록해 둔다.
다음 레코드를 가져와 다시 정렬해서 반복적으로 디스크에 임시 저장한다.

이러한 방식을 멀티 머지(Multi-merge)라고 하며 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행한다. 수행된 멀티 머지 횟수는 Sort_merge_passes 라는 상태 변수에 누적해서 집계된다.

이 작업들은 모두 디스크의 쓰기와 읽기를 유발하며, 반복 작업의 횟수가 많아진다.

소트 버퍼 크기는 56KB ~ 1MB 미만이 적절해 보인다. 세션 메모리 영역에 해당하므로, 커넥션이 많으면 많아질수록, 정렬 작업이 많으면 많아질수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미한다.

이는 더 이상 메모리에 여유 공간이 없을 때 운영체제 OOM-Killer가 프로세스를 강제로 종료할 것이다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스 정렬 방식, 투 패스 정렬 방식으로 나눌 수 있다.

좀 더 정확히는 MySQL 서버의 정렬 방식은 다음과 같이 3가지가 존재한다.

<sort_key, rowid>: 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식
<sort_key, additional_fileds>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드의 컬럼들은 고정 사이즈로 메모리에 저장
<srot_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 컬럼들은 가변 사이즈로 메모리에 저장

첫 번째 방식을 "투 패스" 정렬 방식이라 명명하고, 두 번째와 세 번째 방식을 "싱글 패스" 정렬 방식으로 명명한다.

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼을 전부 담아서 정렬을 수행하는 방법
투 패스 정렬 방식에 비해 더 많은 소트 버퍼 공간이 필요
최신 버전에서 일반적으로 사용하는 방식

투 패스 정렬 방식

정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽는 방법
테이블을 두 번 읽어야 하기 때문에 불합리할 수 있음
MySQL 서버에서 아래와 같은 조건일 경우 투 패스 정렬 방식 사용
- 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
- BLOB이나 TEXT 타입 컬럼이 SELECT 대상에 포함될 때

정렬 처리 방법

인덱스를 이용한 정렬

ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 순서대로 생성된 인덱스가 필요
여러 테이블이 조인되는 경우 네스티드-루프(Nested-loop) 방식의 조인에서만 사용 가능
B-Tree 인덱스가 키 값으로 정렬되어 있어 순서대로 읽기만 하면 된다.
- 하지만, 조인이 사용된 실행 계획에 조인 버퍼(Join buffer)가 사용되어 순서가 흐트러질 수 있음!

조인의 드라이빙 테이블만 정렬

조인에서 첫 번째로 읽히는 테이블을 드라이빙 테이블이라고 한다.

조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인하는 방식
드라이빙 테이블의 컬럼만으로 ORDER BY를 작성해야 함.

임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 그 결과를 정렬해야 할 경우 임시 테이블이 필요하다.
2번의 방법은 임시 테이블을 사용하지 않지만, 그 외의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고 다시 정렬한다.
- 이 과정으로 인해 정렬해야 할 레코드가 가장 많기 때문에 가장 느린 정렬 방법이다.

정렬 처리 방법의 성능 비교

웹 서비스용 쿼리에서 ORDER BY 와 LIMIT 이 거의 필수로 사용되는 경향이 있다. 여기서 LIMIT 을 통해서 처리하는 양을 줄일 수 있다고 생각하지만, 데이터를 처리하는 방식에 따라 그렇지 못할 수 있다. 쿼리가 처리되는 방법을 살펴보자.

ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다.

스트리밍 방식

조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송하는 방식
이 방식으로 처리되는 쿼리에서 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄일 수 있다.

버퍼링 방식

ORDER BY, GROUP BY를 사용하는 상황에서 MySQL 서버는 모든 레코드를 검색하고 정렬한다. 이 과정에서 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.
결과를 모아서 일괄 가공해야 하므로 LIMIT 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.

ORDER BY 처리 방식 중 인덱스를 이용한 정렬 방식만 스트리밍 형식이고 나머지는 모두 버퍼링 방식이다.

GROUP BY 처리

GROUP BY는 ORDER BY처럼 스트리밍 처리를 할 수 없게 한다.

HAVING 절을 통해 GROUP BY 결과에 대해 필터링 역할을 수행한다.
GROUP BY 절에 사용된 컬럼이 인덱스에 포함되어 있고 순서가 맞으면 인덱스를 사용할 수 있지만, HAVING 절에 사용된 조건은 인덱스를 사용할 수 없다.

인덱스를 이용하는 방식

인덱스 스캔

인덱스 스캔을 사용하여 인덱스를 차례대로 읽으면서 그루핑 작업 수행하고 그 결과로 조인 처리
GRUOP BY가 인덱스를 사용해서 처리된다 하더라도 그룹값을 처리하기 위해 임시 테이블이 필요한 경우도 존재

루스 인덱스 스캔

인덱스의 레코드를 건너뛰면서 필요한 부분만 읽는 방식 ("Using index for group-by")
인덱스의 유니크한 값의 수가 적을수록 성능 향상 즉, 분포도가 좋지 않은 인덱스가 효율 good
임시 테이블을 사용하지 않는다.

인덱스를 사용하지 않는 방식

임시 테이블 사용

인덱스를 전혀 사용하지 못할 때 사용되는 방식 ("Using temporary")

DISTINCT 처리

MIN(), MAX(), COUNT() 같은 집합 함수와 함께 사용되는 경우와 그렇지 않은 경우로 나눌 수 있다.
집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요
- But, "Using temporary" 메시지가 출력되지 않음!

SELECT DISTINCT ...

DISTINCT는 SELECT 레코드를 유니크하게 SELECT 하는 것이지, 특정 컬럼만 유니크하게 조회하는 것이 아니다.

아래의 경우 GROUP BY와 동일한 방식으로 처리된다.

-- DISTINCT
SELECT DISTINCT emp_no 
FROM salaries;

-- GROUP BY
SELECT emp_no
FROM salaries
GROUP BY emp_no;

DISTINCT를 사용할 때 괄호를 사용하는 경우가 있는데, MySQL 서버는 괄호를 의미 없는 괄호로 해석하고 제거해버린다.

-- 둘은 같은 쿼리이다.
SELECT DISTINCT (first_name), last_name
FROM employees;

SELECT DISTINCT first_name, last_name
FROM employees;

집합 함수와 함께 사용된 DISTINCT

집합 함수 내에서 DISTINCT 키워드(함수가 아니다!)가 사용될 수 있는데, 이 경우에는 단일 컬럼의 유니크 값을 가져온다. 또한 내부적으로 임시 테이블을 사용하지만, 실행 계획에서는 임시 테이블을 사용한다는 메시지는 표시되지 않는다.

SELECT COUNT(DISTINCT s.salary) -- COUNT()
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
    AND e.emp_no BETWEEN 100001 AND 100100;

"COUNT(DISTINCT s.salary)" 를 처리하기 위해 임시 테이블이 생성되며, 임시 테이블의 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리다.

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로 받은 레코드를 정렬하거나 그루핑할 때 내부적인 임시 테이블을 사용한다. 이러한 내부적인(internal) 임시 테이블은 CREATE TEMPORARY TABLE 명령으로 만들어진 테이블과는 다르다.

처음에는 메모리에 생성되었다가 테이블의 크기가 커지면 디스크로 옮겨진다.
다른 세션이나 다른 쿼리에서 볼 수 없고, 쿼리가 처리되면 자동으로 삭제된다.

Previous08. 인덱스 Next기술에 매몰되지 말자.

Last updated 15 days ago

Was this helpful?