Real MySQL 8.0 Ch.9 — 옵티마이저와 기본 데이터 처리

같은 결과를 내는 쿼리라도 내부적으로 그 결과를 만드는 방법은 매우 다양하다. 그중 어떤 방법이 최소의 비용으로 처리되는지 결정하는 것이 옵티마이저다. 옵티마이저는 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조해 최적의 실행 계획을 수립한다.

옵티마이저는 어느 DBMS에서든 가장 복잡한 부분이다. 그래도 실행 계획을 이해할 수 있어야 불합리한 부분을 찾아내고, 더 나은 실행 계획을 수립하도록 유도할 수 있다.

쿼리 실행 절차

파싱: 요청된 SQL 문장을 잘게 쪼개 MySQL 서버가 이해할 수 있는 수준으로 분리한다. SQL 파서가 처리하며, 문법 오류는 이 단계에서 걸러지고 결과로 SQL 파스 트리가 만들어진다.
최적화 및 실행 계획 수립: 파스 트리를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 사용할지 선택한다. 옵티마이저가 처리한다.
실행: 결정된 테이블 읽기 순서와 인덱스를 이용해 스토리지 엔진에서 데이터를 가져온다. MySQL 엔진과 스토리지 엔진이 함께 처리한다.

두 번째 단계에서 옵티마이저가 하는 일은 다음과 같다.

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

옵티마이저의 종류

비용 기반 최적화(Cost-based optimizer, CBO): 쿼리를 처리하는 여러 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보로 실행 계획별 비용을 산출한다. 그중 비용이 최소인 방식을 선택한다.
규칙 기반 최적화(Rule-based optimizer, RBO): 테이블의 레코드 건수나 선택도를 고려하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다. 통계 정보를 조사하지 않으므로 같은 쿼리에는 항상 같은 실행 방법을 만든다.

규칙 기반 최적화는 통계 정보가 거의 없고 CPU가 느려 비용 계산이 부담스럽던 시절의 방식이다. 데이터의 분포도가 매우 다양한 현재는 거의 사용하지 않는다.

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

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽는 방식이다. 옵티마이저는 다음과 같은 경우에 선택한다.

레코드 건수가 너무 적어 인덱스를 읽는 것보다 풀 테이블 스캔이 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
인덱스 레인지 스캔을 쓸 수 있더라도 조건에 일치하는 레코드가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)

리드 어헤드

풀 테이블 스캔은 디스크 읽기가 많이 필요하다. InnoDB에는 한꺼번에 몇 페이지씩 읽어 올지 설정하는 시스템 변수가 없지만, 그렇다고 한 페이지씩 읽는 것은 아니다.

특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead)가 자동으로 시작된다. 앞으로 필요해질 영역을 예측해 미리 읽어 버퍼 풀에 가져다 두는 것이다.

처음 몇 개의 페이지는 포그라운드 스레드가 읽는다.
특정 시점부터는 백그라운드 스레드가 읽는다. 한 번에 4개 또는 8개씩, 최대 64개까지 페이지를 읽어 온다.

COUNT(*)와 풀 인덱스 스캔

SELECT COUNT(*) FROM employees;

이 쿼리는 풀 테이블 스캔을 할 것 같지만 풀 인덱스 스캔을 할 가능성이 높다. 레코드 건수만 필요하다면 용량이 작은 인덱스를 읽는 쪽이 I/O가 적기 때문이다.

병렬 처리

여기서 병렬 처리는 하나의 쿼리를 여러 스레드가 나누어 처리하는 것을 말한다. 여러 스레드가 각자의 쿼리를 동시에 처리하는 것은 처음부터 가능했다.

MySQL 8.0에서는 innodb_parallel_read_threads 시스템 변수로 하나의 쿼리를 최대 몇 개의 스레드로 처리할지 설정한다. 스레드 개수가 CPU 코어 개수를 넘으면 컨텍스트 스위칭 때문에 오히려 성능이 떨어질 수 있다.

ORDER BY 처리

정렬은 인덱스를 이용하거나 Filesort로 처리한다.

	인덱스 이용	Filesort 이용
장점	쓰기 시점에 이미 정렬돼 있어 순서대로 읽기만 하면 되므로 매우 빠르다.	인덱스를 만들지 않아도 된다. 정렬할 레코드가 많지 않으면 메모리에서 처리되어 충분히 빠르다.
단점	INSERT, UPDATE, DELETE 시 인덱스 추가·삭제 작업이 필요해 느리다. 디스크 공간과 버퍼 풀 메모리가 더 필요하다.	정렬이 쿼리 실행 시점에 처리되므로 레코드가 많을수록 응답이 느리다.

모든 정렬을 인덱스로 처리하도록 튜닝하는 것은 거의 불가능하다.

정렬 기준이 너무 많아 요건별로 인덱스를 모두 생성할 수 없는 경우
GROUP BY나 DISTINCT의 결과를 정렬해야 하는 경우
UNION의 결과처럼 임시 테이블의 결과를 다시 정렬해야 하는 경우
랜덤하게 결과 레코드를 가져와야 하는 경우

Filesort를 사용하면 실행 계획의 Extra 컬럼에 Using filesort가 표시된다.

소트 버퍼

정렬을 수행하기 위해 할당받는 별도의 메모리 공간이다.

정렬이 필요할 때만 할당되며, 정렬할 레코드의 크기에 따라 가변적으로 증가한다.
최대 크기는 sort_buffer_size 시스템 변수로 설정한다.
정렬할 레코드가 소트 버퍼보다 크면 나누어 정렬한 결과를 임시로 디스크에 기록하고, 다시 읽어 병합(멀티 머지)한다. 그만큼 I/O가 많이 발생한다.

책의 테스트에서는 소트 버퍼 크기가 256KB~8MB 사이일 때 최적의 성능을 보였고, 그 이상에서는 효과가 없었다.

소트 버퍼를 너무 크게 설정하면 서버 메모리가 부족해져 MySQL 서버가 종료될 수 있다. 대량 정렬이 필요하면 해당 세션의 소트 버퍼만 일시적으로 늘려서 실행하고 다시 줄이는 방법이 좋다.

싱글 패스와 투 패스

소트 버퍼에 무엇을 담아 정렬하느냐에 따라 두 가지 방식이 있다.

싱글 패스는 정렬 기준 컬럼을 포함해 SELECT 대상 컬럼 전부를 소트 버퍼에 담아 정렬한다.

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;

정렬에 필요 없는 last_name까지 소트 버퍼에 담아 정렬한 뒤 결과를 바로 반환한다.

투 패스는 정렬 대상 컬럼과 PK 값만 소트 버퍼에 담아 정렬하고, 정렬된 순서대로 다시 PK로 테이블을 읽어 SELECT할 컬럼을 가져온다. 테이블을 두 번 읽어야 하는, 싱글 패스보다 오래된 방식이다.

최신 버전은 기본적으로 싱글 패스를 쓰지만 다음 경우에는 투 패스를 사용한다.

레코드의 크기가 max_length_for_sort_data 시스템 변수 값보다 클 때
BLOB이나 TEXT 타입 컬럼이 SELECT 대상에 포함될 때

SELECT *로 모든 컬럼을 가져오면 소트 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 수 있다. 꼭 필요한 컬럼만 조회해야 하는 이유다.

인덱스를 이용한 정렬

인덱스로 정렬을 처리하려면 다음 조건을 만족해야 한다.

ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이라면 드라이빙 테이블)에 속해야 한다.
ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
해시 인덱스나 전문 검색 인덱스는 정렬에 사용할 수 없다.

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

인덱스를 쓸 수 없다면, 조인 전에 첫 번째 테이블의 레코드를 먼저 정렬하고 조인하는 것이 차선책이다. 이를 위해서는 드라이빙 테이블의 컬럼만으로 ORDER BY 절을 작성해야 한다.

SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
  AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY e.last_name;

WHERE 조건은 employees의 PK로 검색하면 작업량을 줄일 수 있다.
드리븐 테이블(salaries)의 조인 컬럼 emp_no에 인덱스가 있다.

last_name은 employees의 PK와 무관해 인덱스를 이용한 정렬은 불가능하다. 하지만 정렬 기준 컬럼이 드라이빙 테이블에 있으므로, 옵티마이저는 드라이빙 테이블만 검색해 먼저 정렬한 뒤 그 결과를 salaries와 조인한다.

반대로 드리븐 테이블의 컬럼으로 ORDER BY를 하면, 조인이 끝난 전체 결과를 정렬할 수밖에 없다.

정렬과 LIMIT, 스트리밍과 버퍼링

웹 서비스용 쿼리에는 ORDER BY와 LIMIT이 거의 필수로 함께 쓰인다. 그런데 ORDER BY나 GROUP BY는 조건에 맞는 레코드를 LIMIT 건수만큼만 가져와서는 처리할 수 없다. 조건을 만족하는 레코드를 모두 가져와 정렬하거나 그루핑한 뒤에야 LIMIT으로 건수를 제한할 수 있다.

그래서 WHERE 조건이 인덱스를 잘 타도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 생긴다.

스트리밍 방식

처리할 데이터가 얼마인지와 관계없이, 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송하는 방식이다.

클라이언트는 쿼리를 요청하고 곧바로 첫 번째 레코드를 받는다.
OLTP 환경에서는 첫 번째 레코드를 받기까지의 응답 시간이 중요한데, 스트리밍은 조회 건수와 상관없이 빠른 응답을 보장한다.
LIMIT처럼 결과 건수를 제한하는 조건이 전체 실행 시간을 크게 줄여 준다.

버퍼링 방식

모든 레코드를 검색하고 정렬하는 동안 클라이언트는 기다려야 하므로 응답이 느리다. LIMIT이 있어도 성능 향상에 큰 도움이 되지 않는다.

다만 Filesort를 거치는 쿼리에서 LIMIT이 전혀 쓸모없는 것은 아니다. LIMIT 10이 있으면 1000건을 모두 정렬하지 않고, 상위 10건이 채워지면 정렬을 멈추고 결과를 반환한다. 그렇다 해도 MySQL은 정렬에 퀵 소트와 힙 소트를 사용하므로, 상위 10건을 가려내기 위해 생각보다 많은 작업이 필요할 수 있다.

JDBC의 버퍼링

JDBC로 SELECT를 실행하면 MySQL 서버는 레코드를 읽자마자 클라이언트로 전달한다. 하지만 JDBC 라이브러리는 받은 레코드를 내부 버퍼에 모두 담아 두었다가, 마지막 레코드까지 받은 뒤에야 애플리케이션에 반환한다. 서버는 스트리밍으로 보내는데 JDBC가 버퍼링을 하는 것이다.

불필요한 네트워크 요청을 최소화해 전체 처리량을 높이기 위한 동작이다. 아주 대량의 데이터를 가져와야 할 때는 스트리밍 방식으로 변경할 수 있다.

GROUP BY 처리

GROUP BY도 ORDER BY처럼 스트리밍 처리를 불가능하게 만드는 요소다. GROUP BY 결과에 대한 HAVING 조건은 인덱스로 처리할 수 없으므로, HAVING을 튜닝하려고 인덱스를 고민할 필요는 없다.

GROUP BY는 인덱스를 사용할 수 있으면 인덱스 스캔 또는 루스 인덱스 스캔으로, 사용할 수 없으면 임시 테이블로 처리된다.

인덱스 스캔(타이트 인덱스 스캔)

조인의 드라이빙 테이블에 속한 컬럼만으로 그루핑하고 그 컬럼에 인덱스가 있다면, 인덱스를 차례대로 읽으며 그루핑하고 그 결과로 조인한다.

인덱스로 처리되더라도 그룹 함수 등의 그룹값을 처리하기 위해 임시 테이블이 필요할 때도 있다.

루스 인덱스 스캔

인덱스의 레코드를 건너뛰면서 필요한 부분만 읽는 방식이다. 실행 계획에 Using index for group-by가 표시된다.

프리픽스 인덱스에는 사용할 수 없다.
인덱스 레인지 스캔은 유니크한 값의 수가 많을수록 성능이 좋지만, 루스 인덱스 스캔은 유니크한 값의 수가 적을수록 성능이 좋다.

DISTINCT 처리

COUNT(DISTINCT ...)처럼 집합 함수와 함께 쓰인 DISTINCT가 인덱스를 사용하지 못하면 항상 임시 테이블이 필요하다. 그런데 실행 계획에는 Using temporary가 표시되지 않는다.

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

이 쿼리는 인덱스가 없는 두 컬럼 각각에 DISTINCT 처리가 필요하므로 임시 테이블이 2개 필요하다. 반면 인덱스가 있는 컬럼이라면 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 처리할 수 있다.

내부 임시 테이블

MySQL 엔진은 스토리지 엔진에서 받아 온 레코드를 정렬하거나 그루핑할 때 내부 임시 테이블을 사용한다. 처음에는 메모리에 생성됐다가 크기가 커지면 디스크로 옮겨지고, 쿼리 처리가 끝나면 자동으로 삭제된다.

참고

Real MySQL 8.0