Data Processing(데이터 처리)

MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등 기본 데이터 가공 기능을 가지고 있으며, 엔진별로 데이터 처리 방식은 모두 다르다.

쿼리 실행 절차

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

1. SQL Parsing: 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(Parse Tree)

2. 최적화 및 실행 계획 수립: SQL 파싱 정보(Parse Tree)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 결정(Execution Plan)

   • 불필요한 조건 제거 및 복잡한 연산 단순화

   • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정

   • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정

   • 가져온 레코드들을 임시 테이블에 넣고 재가공이 필요한지 결정

3. 데이터 읽음: 위에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 읽어옴

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

쿼리 처리 방법

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

• 스트리밍 방식: 서버 쪽에서 처리할 데이터가 얼마인지 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송하는 방식

  • 첫 번째 레코드를 처리하자마자 전달할 수 있으므로 많은 데이터를 조회하는 것과 상관 없이 빠른 응답을 받을 수 있음

• 버퍼링 방식: 모든 결과를 스토리지 엔진으로부터 가져와서 서버 쪽에 저장한 뒤 클라이언트로 전송하는 방식

  • ORDER BY / GROUP BY 같은 처리는 모든 레코드를 읽어서 처리해야 하므로 버퍼링 방식으로 처리

리드 어헤드(Read ahead)

어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해 요청이 오기 전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것

보통 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 풀 테이블 스캔은 상당히 많은 디스크 읽기가 필요한데, 이를 향상시키기 위해 리드 어헤드 기능을 사용한다. InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동 시작된다. 테이블 풀 스캔 시 작업은 아래와 같이 진행된다.

1. 풀 스캔이 실행되면 포그라운드 스레드(Foreground Thread)에 의해 처음 몇 개의 데이터 페이지가 읽힘

2. 특정 시점부터 백그라운드 스레드가 읽기 작업을 시작

3. 백그라운드 스레드는 한 번에 읽는 페이지 수를 늘려가며 읽기 작업을 진행

4. 읽은 페이지를 버퍼 풀에 저장

5. 포그라운드 스레드는 백그라운드 스레드가 읽은 페이지(버퍼 풀)에서 가져다가 사용

풀 테이블 스캔과 풀 인덱스 스캔은 아래와 같은 경우 실행되며 리드 어헤드(Read ahead) 기능을 사용하게 된다.

• 풀 테이블 스캔(Full Table Scan): 아래의 조건을 만족하는 경우 풀 테이블 스캔을 수행한다.

  • 테이블의 레코드 건수가 너무 작아 인덱스를 사용하는 것보다 테이블을 읽는 것이 빠른 경우(보통 테이블이 페지이 1개로 구성된 경우)

  • WHERE 절 혹은 ON 절에 이용할 수 있는 적절한 조건이 없는 경우

  • 인덱스 레인지 스캔을 사용할 수 있지만 옵티마이저 판단한 조건 일치 레코드 건수가 너무 많은 경우

• 풀 인덱스 스캔(Full Index Scan): 모든 레코드를 읽어야 하는 것이 아닌 레코드 건수만 조회하는 경우 풀 인덱스 스캔으로 처리

  • SELECT COUNT(*) FROM table_name 같은 경우 가능

  • 용량이 적어 디스크 읽기 횟수가 줄어 훨씬 빠른 속도로 처리

병렬 처리

하나의 쿼리를 여러 스레드가 작업을 나누어 처리하는 것

MySQL 8.0에서 병렬 처리 기능이 추가되었으나, WHERE 조건 없이 단순이 테이블 전체 건수를 가져오는 경우에만 병렬 처리가 가능하다.

SET SESSION innodb_parallel_read_threads = 4;

SELECT COUNT(*)
FROM TABLE_NAME;

스레드 수를 늘려 서버에 장착된 코어 개수를 넘어가게 되는 경우 오히려 성능이 떨어질 수 있다.

ORDER BY 처리

정렬을 처리할 때 인덱스를 이용하는 방법과 쿼리가 실행 될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나뉘며, 장단점은 아래와 같다.

이용 방법	장점	단점
인덱스	INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠름	INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느림, 인덱스 개수가 늘러날수록 메모리가 많이 필요
Filesort	인덱스를 생성하지 않아도 되므로 위의 인덱스의 단점이 발생하지 않는 장점이 존재	정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 느려짐

소트 버퍼(Sort Buffer)

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

• 정렬이 필요한 경우에만 메모리 공간을 할당 받음

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

  • 최대 사용 가능한 소트 버퍼 공간은 sort_buffer_size라는 시스템 변수로 설정 가능

• 쿼리 실행이 완료되면 바로 시스템으로 반납

• 세션 메모리 영역에 해당(클라이언트가 공유해서 사용되는 영역이 아님)

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

멀티 머지(Multi Merge)

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

1. 메모리의 소트 버퍼에서 정렬 수행

2. 그 결과를 임시로 디스크에 기록

3. 다음 레코드를 가져와서 다시 정렬하여 반복적으로 디스크에 임시 저장

4. 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬

위 과정에서 알 수 있듯이 디스크 쓰기와 읽기를 유발하여 레코드 건수가 많을 수록 위 작업 횟수가 많아지므로 속도가 느려진다. 하지만 sort_buffer_size를 단순히 늘리더라도 빨라지는 것은 아니며, 메모리 사용량이 늘어나 문제가 발생할 수 있으므로 적절한 크기로 설정하는 것이 중요하다.

정렬 알고리즘

MySQL 서버의 정렬 방식은 아래와 같이 세 가지로 나뉜다.

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

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

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

<sort_key, rowid>

정렬 대상 컬럼과 PK 값만 소트 버퍼에 담아서 정렬하는 방식으로, 아래와 같은 순서로 처리된다.

1. 정렬에 필요한 컬럼과 프라이머리 키만 조회

2. 정렬 수행

3. 정렬된 프라이머리 키를 이용해 다시 테이블에서 필요한 컬럼을 조회

4. 정렬된 결과를 클라이언트에 반환

위 과정에서 테이블을 두 번 읽어야해서 성능 저하를 유발하기 때문에 모든 레코드를 가져와서 정렬하는 방식을 사용하는 것이 좋지만 아래 상황의 경우 어쩔 수 없이 사용하게 된다.

• 레코드의 크기가 max_length_for_sort_data보다 큰 경우

• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함된 경우

<sort_key, *additional_fields>

대상이 되는 컬럼을 전부 담아서 정렬을 수행하기 때문에 레코드를 한 번만 읽어서 정렬한 뒤 그대로 클라이언트에 반환할 수 있다.

정렬 처리 방법

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개 이상의 테이블이 조인되는 경우 임시 테이블에 저장하고 그 결과를 다시 정렬하는 방식을 사용해야 한다.

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;

위 쿼리에서 정렬이 수행 되기 위해선 salaries 테이블을 읽어야 하므로 조인한 데이터를 이용해 정렬을 해야한다. 때문에 임시 테이블을 만들어 조인 결과를 저장하고 그 결과를 정렬하는 방식을 사용하게 된다.

GROUP BY 처리

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가 표시된다. GROUP BY가 필요한 경우 내부적으로 임시 테이블을 만들어 중복 제거 및 집합 함수 연산을 수행하고 최종 결과를 반환한다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회할 때 DISTINCT를 사용하는데, DISTINCT 처리가 실행 계획에서 인덱스를 사용하지 못하는 경우 항상 임시 테이블이 사용된다. 해당 키워드가 미치는 범위는 아래 두 가지로 나뉘어지게 된다.

• 집합 함수가 없는 경우

• MIN(), MAX(), COUNT() 같은 집함 함수와 함께 사용하는 경우

SELECT DISTINCT

단순히 조회된 레코드 중에서 유니크한 레코드만 가져올 땐 SELECT DISTINCT를 사용할 수 있다. 이 경우 GROUP BY와 동일한 방식으로 처리되어 아래 두 쿼리는 내부적으로 같은 작업을 수행한다.

SELECT DISTINCT emp_no
FROM salaries;

SELECT emp_no
FROM salaries
GROUP BY emp_no;

DISTINCT에서 중요한 점은 특정 컬럼에 대해 유니크한 값을 조회하는 것이 아니라 조회된 레코드 전체에 대해 유니크한 값을 조회한다는 것이다.

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

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

집합 함수와 함께 사용된 DISTINCT

집합 함수 내에서 사용된 DISTINCT는 SELECT와는 다르게 집합 함수의 인자로 전달된 컬럼값에 유니크한 것들만 가져오게된다.

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권)