Data Processing

쿼리 실행 절차

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

1. SQL Parsing: 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(Parse Tree)
2. 최적화 및 실행 계획 수립: SQL 파싱 정보(Parse Tree)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 결정(Execution Plan)
3. 데이터 읽음: 위에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 읽어옴

자세한 내용은 쿼리 처리(Query Processing) 문서 참고

쿼리 처리 방법

MySQL에서 쿼리 처리 방법은 스트리밍 처리와 버퍼링 처리 두 가지로 나눌 수 있다.

• 스트리밍 방식: 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송하는 방식
  • 서버 쪽에서 처리할 데이터가 얼마인지 관계 없이 바로 전송
  • 첫 번째 레코드를 처리하자마자 전달할 수 있으므로 데이터 양과 상관 없이 빠른 응답을 받을 수 있음
  • 단순 SELECT 쿼리는 대부분 스트리밍 방식으로 처리
• 버퍼링 방식: 모든 결과를 스토리지 엔진으로부터 가져와서 서버 쪽에 저장한 뒤 클라이언트로 전송하는 방식
  • ORDER BY, GROUP BY,DISTINCT 와 같이 전체 결과 집합을 대상으로 해야 하는 작업은 반드시 버퍼링이 필요

리드 어헤드(Read ahead)

어떤 영역의 데이터가 필요할 것을 예측해 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 처리 방식이다.

• 포그라운드 스레드의 요청이 오기 전에 백그라운드 스레드가 미리 디스크에서 페이지를 읽어 버퍼 풀에 적재하는 방식
• 풀 테이블 스캔이나 풀 인덱스 스캔과 같이 대량의 데이터를 순차적으로 읽어야 할 때 사용
• I/O 대기 시간을 줄여 대용량 순차 읽기 작업의 성능을 향상

테이블 풀 스캔 시 리드 어헤드는 다음과 같은 순서로 진행하게 된다.

1. 풀 스캔이 실행되면 포그라운드 스레드(Foreground Thread)에 의해 처음 몇 개의 데이터 페이지가 읽힘
2. 특정 시점부터 백그라운드 스레드가 읽기 작업을 시작
3. 백그라운드 스레드는 한 번에 읽는 페이지 수를 늘려가며 읽기 작업을 진행
4. 읽은 페이지를 버퍼 풀에 저장
5. 포그라운드 스레드는 백그라운드 스레드가 읽은 페이지(버퍼 풀)에서 가져다가 사용

병렬 처리(Parallel Processing)

하나의 쿼리를 여러 스레드가 작업을 나누어 처리하는 방식이다.

• MySQL 8.0에서 병렬 처리 기능이 추가
• WHERE 조건 없이 단순이 테이블 전체 건수를 가져오는 경우에만 제한적으로 사용

SET SESSION innodb_parallel_read_threads = 4;

SELECT COUNT(*)
FROM TABLE_NAME;

innodb_parallel_read_threads 시스템 변수로 병렬 처리 스레드 수를 조절할 수 있으며, 과도하게 늘리면 오히려 성능이 떨어질 수 있다.

ORDER BY 처리

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

인덱스 사용 정렬

인덱스를 이용해 정렬하는 방법은 가장 효율적인 방법으로, 인덱스가 이미 정렬된 상태이기 때문에 인덱스를 순서대로 읽기만 하면 되기 때문에 별도의 정렬 작업이 필요 없다.

Filesort

Filesort는 인덱스를 이용하지 못하는 경우에 사용하는 정렬 방법으로, 실행 계획의 Extra 컬럼에 Using filesort로 표시된다.

소트 버퍼(Sort Buffer)

Filesort를 이용해 정렬을 처리할 때, 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받는 공간을 소트 버퍼(Sort Buffer)라고 하며 아래와 같은 특징을 가진다.

• 정렬이 필요한 경우에만 메모리 공간을 할당
• 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가
  • 최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size라는 시스템 변수로 설정 가능
• 쿼리 실행이 완료되면 바로 시스템으로 반납
• 세션 메모리 영역에 해당(클라이언트가 공유해서 사용되는 영역이 아님)

여기서 정렬해야 할 레코드가 메모리에 할당된 소트 버퍼만으로 처리되면 인메모리 정렬로 수행되어 빠르게 처리할 수 있다.

멀티 머지(Multi Merge)

하지만 소트 버퍼를 초과하는 경우엔 레코드를 여러 조각으로 나눠서 처리하며 임시 저장을 위해 디스크를 사용하게 되는데 과정은 아래와 같다.

1. 메모리의 소트 버퍼에서 정렬 수행
2. 그 결과를 임시로 디스크에 기록
3. 다음 레코드를 가져와서 다시 정렬하여 반복적으로 디스크에 임시 저장
4. 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬

디스크 I/O가 발생하기 때문에 레코드 건수가 많을 수록 위 작업 횟수도 많아지므로 성능이 크게 저하된다.

정렬 처리 방법

ORDER BY가 사용되면 아래 세 가지 방법 중 하나를 선택해서 처리하며, 아래쪽으로 내려갈수록 성능이 떨어진다.

정렬 처리 방법	실행 계획의 Extra 컬럼 내용
인덱스 사용 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	Using filesort
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	Using temporary; Using filesort

여기서 드라이빙 테이블은 조인에서 먼저 읽는 테이블을 의미하며 옵티마이저에서 아래의 조건을 만족하는 테이블을 드라이빙 테이블로 선정한다.

• 크기: 레코드 건 수가 가장 많은 테이블
• 인덱스: WHERE 절의 검색 조건이나 조인 조건에 사용된 컬럼에 인덱스 존재

1. 인덱스 사용 정렬

인덱스를 이용한 정렬을 사용하기 위해선 아래의 조건을 만족해야 한다.

• ORDER BY 절에 사용된 컬럼이 제일 먼저 읽는 테이블에 속함
• ORDER BY 순서대로 생성된 인덱스 사용
• WHERE절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건(존재 한다면)과 ORDER BY는 같은 인덱스를 사용해야 함

인덱스를 이용해 정렬 처리되는 경우 실제 인덱스 값으로 정렬돼 있기 때문에 그대로 순서대로 읽기만 하면 되기 때문에, 정렬 처리를 위한 별도의 작업 없이 스트리밍 형태로 처리할 수 있다.

2. 조인에서 드라이빙 테이블만 정렬

조인을 하게 되면 레코드 건수가 증가하게 되고, 하나하나의 레코드 크기도 늘어나기 때문에 레코드를 먼저 정렬한 뒤 조인을 수행하여 처리하게 된다.

-- 조건: 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 가능
SELECT *
FROM employees e,
     salaries s
WHERE e.emp_no = s.emp_no
  AND e.emp_no BETWEEN 10001 AND 10010
ORDER BY e.last_name;

1. 인덱스를 이용해 emp_no BETWEEN 10001 AND 10010 조건을 만족하는 레코드 검색
2. employees 테이블의 last_name 컬럼 정렬 수행
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인

3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬

위의 경우를 제외하고 2개 이상의 테이블이 조인되는 경우 임시 테이블에 저장하고 그 결과를 다시 정렬하는 방식을 사용한다.

-- 정렬이 수행 되기 위해선 `salaries` 테이블의 salary 컬럼 필요
SELECT *
FROM employees e,
     salaries s
WHERE e.emp_no = s.emp_no
  AND e.emp_no BETWEEN 10001 AND 10010
ORDER BY s.salary;

GROUP BY 처리

그룹핑 역시 인덱스를 사용하거나, 인덱스를 사용하지 못할 경우 임시 테이블을 사용한다.

타이트 인덱스 스캔(Tight Index Scan)

인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리하며, 추가적인 정렬 작업이나 내부 임시 테이블을 사용하지 않는다.

루스 인덱스 스캔(Loose Index Scan)

GROUP BY 컬럼이 인덱스의 선행 컬럼일 때, 각 그룹의 첫 번째 레코드만 읽고 다음 그룹으로 바로 건너뛰어 불필요한 스캔을 최소화한다.

CREATE INDEX idx_empNo_fromDate ON salaries (emp_no, from_date);

EXPLAIN
SELECT emp_no
FROM salaries
WHERE from_date = '2000-01-01'
GROUP BY emp_no;

인덱스가 emp_no, from_date으로 생성되어 있으므로 WHERE 조건은 인덱스 레인지 스캔 방식으로 불가능하지만, 실행 계획과 실행 과정은 아래와 같이 진행된다.

실행 계획

id	table	type	key	extra
1	salaries	range	PRIMARY	Using where; Using index for group-by

실제 쿼리 실행 과정

1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) 탐색
2. 찾은 emp_no 중에서 from_date가 ‘2000-01-01’인 레코드가 있는지 확인
   • 해당 단계는 WHERE 조건으로 emp_no AND from_date 인덱스 탐색을 하는 것과 유사하게 동작
3. (emp_no, from_date) 인덱스에서 emp_no의 다음 유일한 값으로 이동
4. 2 - 3단계를 반복하며 emp_no의 유일한 값이 없을 때까지 반복

임시 테이블 사용

인덱스를 사용할 수 없는 경우, 내부적으로 임시 테이블을 생성하여 그룹핑을 처리한다

• 내부적으로 임시테이블을 생성(실행 계획에서 Using temporary로 표시)
• 그루핑할 컬럼으로 유니크 인덱스를 생성해, 모든 레코드를 삽입하면서 중복 제거 및 집계 함수 계산

DISTINCT 처리

DISTINCT 처리를 실행 계획에서 인덱스를 사용하지 못하는 경우 항상 임시 테이블이 사용된다.

집합 함수 없는 경우(SELECT DISTINCT)

단순히 조회된 레코드 중에서 유니크한 레코드만 가져올 땐 SELECT DISTINCT를 사용할 수 있다.

• GROUP BY와 유사한 방식으로 처리
• 특정 컬럼에 대해 유니크한 값을 조회하는 것이 아닌 조회된 레코드 전체에 대해 유니크한 값을 조회

-- fist_name, last_name 조합이 유니크한 레코드만 조회
SELECT DISTINCT fist_name, last_name
FROM employees;

-- first_name만 유니크한 레코드만 조회하는 것이 아닌, 위 쿼리와 동일하게 조회
SELECT DISTINCT(first_name), last_name
FROM employees;

집합 함수와 함께 사용된 DISTINCT

집합 함수 내에서 사용된 DISTINCT는 집합 함수의 인자로 전달된 컬럼값에 유니크한 값만을 대상으로 집계 함수를 수행한다.

EXPLAIN
SELECT COUNT(DISTINCT s.salary) -- s.salary 컬럼의 유니크한 값만 조회
FROM employees e,
     salaries s
WHERE e.emp_no = s.emp_no
  AND e.emp_no BETWEEN 100000 AND 100100;

• 내부적으로 COUNT(DISTINCT)를 처리하기 위해 salary 컬럼의 값만 저장하기 위한 임시 테이블 생성(실행 계획엔 표시되지 않음)
• 생성된 임시 테이블의 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아질 수록 성능 저하

인덱스를 사용하는 경우

만약 인덱스를 사용할수 있는 컬럼에 대해 DISTINCT를 사용하는 경우 임시 테이블 없이 레인지 스캔 혹은 인덱스 풀 스캔을 수행하여 최적화하여 처리할 수 있다.

SELECT COUNT(DISTINCT emp_no)
FROM employees;

SELECT COUNT(DISTINCT emp_no)
FROM employees
GROUP BY emp_no

참고자료

• Real MySQL 8.0 (1권)