[MySQL] 옵티마이저의 데이터 처리

목차

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

2. 병렬 처리

3. ORDER BY 처리

4. GROUP BY 처리(Using filesort)

5. DISTINCT 처리

 

 

옵티마이저

MySQL에서는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조해 최적의 실행 계획을 수립하는 작업이 필요한데 옵티마이저가 이 작업을 수행한다.

 

옵티마이저는 쿼리 파서의 파싱 작업을 거쳐 만들어진 SQL 파스 트리를 참조하며 다음과 같은 과정을 거쳐 실행 계획을 만든다.

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

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

 

 

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

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

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
  (일반적으로 테이블이 페이지 1개로 구성된 경우)
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
  (인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)

풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘기고 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 상당히 빨리 처리된다.

 

(리드 어헤드)

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

 

innodb_read_ahead_threshold 시스템 변수에 설정된 개수만큼의 포그라운드 스레드에 의해 연속된 데이터 페이지가 읽히면 InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그다음 페이지들을 읽어서 버퍼 풀로 적재한다.

 

 

2. 병렬 처리

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

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

SET SESSION innodb_parallel_read_threads = 4;
SELECT COUNT(*) FROM salaries;

병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.

 

 

 

3. ORDER BY 처리(Using filesort)

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

	장점	단점
인덱스 이용	(INSERT, UPDATE, DELETE 쿼리가 실행될 때) 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.	INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수로 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort 이용	인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.	정렬 작업이 쿼리 실행시 처리되므로 레코드 대상 건수가 많아질수로 쿼리의 응답 속도가 느리다.

인덱스를 이용한 정렬이 빠르지만 다음과 같은 경우 때문에 모든 정렬을 인덱스를 이용하도록 튜닝하긴 불가능하다.

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

 

1) 소트 버퍼

MySQL은 정렬이 필요한 경우에만 소트 버퍼라는 별도의 메모리 공간을 할당받아 정렬에 사용하고 쿼리의 실행이 완료되면 즉시 시스템으로 반납한다.

 

(소트 버퍼 메모리 크기)

버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가

최대 사용 가능한 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정

 

(1) 멀티 머지

정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 큰 경우 MySQL은 레코드를 여러 조각으로 나눠서 처리한다.

버퍼 크기만큼 정렬된 레코드를 병합하면서 정렬을 수행한다. 이 작업들은 모두 디스크 I/O를 유발한다.

1. 메모리의 소트 버퍼에서 정렬을 수행
2. 결과를 임시로 디스크에 기록
3. 다음 레코드를 가져와 반복 수행

(2) 세션 메모리 영역

소트 버퍼는 세션 메모리 영역을 사용하는데, 커넥션이 많을수록, 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커지게 된다. 소트 버퍼의 크기를 10MB 이상으로 설정하면 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시에 실행되면서 운영체제는 메모리 부족 현상을 겪을 수도 있다.

 

 

테스트하는 서버에서 메모리나 디스크의 특성에 따라 다를 수 있지만 일반적인 트랜잭션 처리용 서버에서 소트 버퍼 크기는 56KB ~ 1MB가 적절하다.

 

 

2) 정렬 알고리즘

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

 

(1) 싱글 패스(Single-pass)

소트 버퍼에 Select 대상이 되는 컬럼을 전부 담아서 정렬을 수행

• <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드의 컬럼들은 고정 사이즈로 메모리 저장
• <sort_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드의 컬럼들은 가변 사이즈로 메모리 저장

 

(2) 투 패스(Two-pass)

정렬 대상 컬럼과 PK 값만 소트 버퍼에 담아서 정렬을 수행

정렬된 순서대로 다시 PK로 테이블을 읽어서 SELECT 할 컬럼을 가져오는 방식

• <sort_key, rowid>: 정렬 키와 레코드의 Row ID만 가져와서 정렬하는 방식

 

최신 버전에서는 일반적으로 싱글 패스 방식을 주로 사용하고, 다음의 경우 투 패스 방식 사용

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

 

3) 정렬 처리 방법

쿼리에 ORDER BY가 사용되면 다음 3가지 방법중 하나로 정렬이 처리된다.

인덱스를 사용할 수 있는지 검토하고 사용할 수 없으면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 Filesort 한다.

	실행 계획의 Extra 컬럼 내용	우선 순위	처리 속도
인덱스를 사용한 정렬		1	1
조인에서 드라이빙 테이블만 정렬	"Using filesort"	2	2
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort"	3	3

 

(1) 인덱스를 이용한 정렬

인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다.

SELECT * FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 1000002 AND 100020
ORDER BY e.emp_no;

 

 

인덱스를 이용한 정렬을 위해 필요한 조건

• ORDER BY에 명시된 컬럼이 드라이빙 테이블에 속해야 한다.
• ORDER BY 순서대로 생성된 인덱스가 있어야 한다.
• WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.

 

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

드라이빙 테이블의 레코드를 먼저 정렬한 다음 조인을 실행한다.

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;

1. 인덱스를 이용해 "emp_no BETWEEN 100001 AND 100010" 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴

 

드라이빙 테이블만 정렬을 위해 필요한 조건

• 드라이빙 테이블의 컬럼만으로 ORDER BY 절을 작성해야 한다.

(검색은 인덱스 레인지 스캔으로 처리할 수 있지만,

ORDER BY 절에 명시된 컬럼은 PK와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하다.)

 

 

(3) 임시 테이블을 이용한 정렬

"드라이빙 테이블만 정렬" 방식 외의 패턴에서는 조인된 데이터를 임시 테이블에 저장하고 정렬을 수행한다.

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

 

(4) 정렬 처리 방법의 성능

• 스트리밍 방식 : 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
• 버퍼링 방식 : 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 전송하는 방식

인덱스를 사용한 정렬 방식은 스트리밍 형태의 처리이며, 나머지 2가지 방법은 버퍼링 방식으로 처리된다. 따라서 가능하다면 인덱스를 사용한 정렬로 유도하도록 튜닝하는 것이 좋다.

 

(JDBC 라이브러리의 버퍼링)

MySQL 서버는 스트리밍 방식으로 처리해서 레코드를 반환하더라도 JDBC 라이브러리는 MySQL 서버로부터 받는 레코드를 일단 내부 버퍼에 모두 담아두고 마지막 레코드까지 전달받으면 클라이언트의 애플리케이션에 반환하기 때문에 버퍼링 방식으로 동작한다.

 

 

4) 정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다.

FULUSH STATUS;
SHOW STATUS LIKE 'Sort%';

Sort_merge_passes	멀티 머지 처리 횟수
Sort_range	인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_scan	풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
Sort_rows	정렬한 전체 레코드 건수

 

4. GROUP BY 처리

GROUB BY 작업은 인덱스 스캔, 루스 인덱스 스캔, 인덱스를 사용하지 못하고 임시 테이블을 사용하는 방법으로 나뉜다.

 

1) 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때, GROUP BY 컬럼으로 인덱스가 있다면 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. 그룹 함수등의 그룹값을 처리해야 하는 경우에는 임시 테이블이 필요할 때도 있다.

 

실행 계획의 Extra : 없음

 

2) 루스 인덱스 스캔을 이용하는 GROUP BY

단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다.

(루스 인덱스 스캔 참고)

 

실행 계획의 Extra : "Using index for group-by"

 

 

3) 임시 테이블을 사용하는 GROUP BY

GROUP BY의 기준 컬럼이 드라이빙, 드리븐 테이블에 있는지와 관계없이 인덱스를 사용하지 못할 때 처리되는 방식이다.

MySQL 8.0 에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

 

실행 계획의 Extra : "Using temporary"

 

 

5. DISTINCT 처리

1) SELECT DISTINCT

집합 함수를 사용하지 않고 DISTINCT 만 사용한 경우 GROUP BY와 동일한 방식으로 처리된다.

SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

 

2) 집합 함수와 함께 사용된 DISTINCT

COUNT(), MIN(), MAX() 같은 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들을 가져오고 단순히 DISTINCT 만 사용한 경우와 다르게 동작한다.

EXPLAIN
SELECT COUNT(DISTINCT s.salary)
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 컬럼의 값만 저장하기 위해 임시 테이블을 사용하지만 실행 계획에서 "Using temporary"를 표시하지 않는다.

 

인덱스된 컬럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있다.

 

 

 

 

 

 

참고

Real MySQL 8.0 (1권)