9.1 개요 ~ 9.2 기본 데이터 처리

1. 개요

(1) 쿼리 실행 절차

단계는 크게 세 단계로 나눌 수 있다.

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

2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 어떤 인덱스를 이용해 테이블을 읽을지 선택

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

첫 번째 단계를 SQL 파싱 이라고 하며, MySQL 서버의 SQL 파서 라는 모듈로 처리한다. SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다. 또한 이 단계에서 SQL 파스 트리 가 만들어진다.

두 번째 단계는 최적화 및 실행 계획 수립 단계이며 MySQL 서버의 옵티마이저 에서 처리한다. 이 단계에서 실행 계획 이 만들어지며, 만들어진 SQL 파스 트리 를 참조하면서 대표적으로 다음과 같은 내용을 처리한다.

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

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

(2) 옵티마이저의 종류

현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(CBO) 방법과 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화(RBO)로 크게 나눌 수 있다.

규칙 기반 최적화는 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다. 이 경우 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 나타낸다. -> 사용자의 데이터는 분포도가 매우 다양하기 떄문에 RBO는 적합하지 않아서 이제 거의 사용되지 않는다.

비용 기반 최적화는 쿼리 처리를 위한 여러 가지의 가능한 방법을 만들고, 각 단위 작업의 "비용 정보"와 대상 테이블의 예측된 "통계 정보"를 이용해 실행 계획별 비용을 산출한다. 그리고 이 비용이 최소로 소용되는 처리 방식을 선택한다.

2. 기본 데이터 처리

(1) 풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 작업을 처리하는 방식이다.

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

• 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우

• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우

• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드건수가 너무 많은 경우

대부분의 DBMS는 풀 테이블 스캔을 할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어온다. 하지만 MySQl은 한 번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.

InnoDB에서는 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 Read ahead 작업이 자동으로 시작된다.

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

즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기작업을 백그라운드 스레드로 넘긴다.

Read Ahead는 풀 인덱스 스캔에서도 동일하게 사용된다. (레코드에만 있는 칼럼이 필요하지 않은 쿼리는 풀 인덱스 스캔을 주로 사용하게 된다.)

(2) 병렬 처리

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

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

(3) ORDER BY 처리

MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 Using filesort 메세지 표시 여부로 판단할 수 있다.

<1> 소트 버퍼

소트 버퍼란 MySQL이 정렬을 수행하기 위해 별도로 할당받은 메모리 공간이다. 공간의 크기는 시스템 변수로 설정할 수 있고, 정렬이 필요한 쿼리 실행이 완료되면 즉시 시스템으로 반납된다.

이때 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 어떻게 될까? MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.

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

각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야하게 되는데 이 병합 작업을 멀티 머지라고 표현한다.

이 작업들이 모두 디스크의 읽기와 쓰기를 유발한다.

소트 버퍼의 크기 자체는 소요 시간에 큰 혁신을 가져오진 않는다.

<2> 정렬 알고리즘

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

더 정확히는 MySQL 서버의 정렬 방식은 다음과 같이 세가지가 있다.

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

첫 번째 방식을 "투 패스" 정렬방식이라 명명하고
나머지 두 개를 "싱글 패스" 정렬 방식이라 명명한다.

{싱글 패스 정렬 방식}
소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식이다.

{투 패스 정렬 방식}
정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식이다.

투 패스 방식은 테이블을 두 번 읽어야해서 불합리하지만, 싱글 패스는 그렇지 않다.
하지만 싱글 패스는 더 많은 소트 버퍼 공간이 필요하다.

<3> 정렬 처리 방법

ORDER BY는 아래 세 가지 방법 중 하나로 처리된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.

{인덱스를 이용한 정렬}

이를 위해서는 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
또한 WEHRE 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. 그리고 B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없다.

인덱스 값이 정렬되어 있으므로 인덱스 순서대로 읽기만 하면 된다.

{조인의 드라이빙 테이블만 정렬}
조인을 실행하기 전 첫 번째 테이블의 레코드를 먼저 정렬하고 다음 조인을 실행하려면 조인에서 첫 번째 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야한다.

{임시 테이블을 이용한 정렬}

쿼리가 여러 테이블을 조인하지 않고 하나의 테이블로부터 SELECT 하여 정렬하는 경우라면 임시 테이블이 부필요하다. 하지만 2개 이상의 테ㅣ블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다.
이 방법은 세 가지 방법 가운데 가장 느린 정렬 방법이다.

{정렬 처리 방법의 성능 비교}

ORDER BY 나 GROUP BY는 LIMIT 건수가 처리해야할 작업량을 줄일 수 없다.
우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실앻애햐만 비로소 LIMIT으로 건수를 제한할 수 있다. WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수밖에 없는지 살펴보자.

이를 위해 쿼리가 처리되는 방법을 "스트리밍 처리"와 "버퍼링 처리"라는 두 가지 방식으로 구분해보자.

스트리밍 방식
조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트 쪽으로 전송해주는 방식.
웹 서비스 같은 OLTP 환경에선 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요하다. 이 방식은 빠른 응답 시간을 보장해주기 때문에 유용하다.

버퍼링 방식
ORDER BY, GROUP BY는 쿼리 결과가 스트리밍 되는 것을 불가능하게 한다. 이 경우엔 모든 레코드 검색하고 정렬 하는 동안 클라이언트는 아무것도 하지 않고 기다려야하는 버퍼링이 발생한다.

(4) GROUP BY 처리

인덱스를 사용하는 경우와 그렇지 못한 경우로 나눠볼 수 있다.

인덱스를 이용할 때는 인덱스를 차례대로 읽는 인덱스 스캔 방법과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔이라는 방법으로 나뉜다. 그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.

<1> 인덱스 스캔을 이용하는 GROUP BY

조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

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

루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다. 또한 프리픽스 인덱스는 루스 인덱스 스캔을 사용할 수 없다. 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어 낸다.

<3> 임시 테이블을 사용하는 GROPU BY

GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.

(5) DISTINCT 처리

<1> SELECT DISTINCT...

단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 한다면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 동일한 방식으로 처리된다.

<2> 집합 함수와 함께 사용된 DISTINCT

집합 함수가 없는 SELECT 쿼리에서 DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다. 하지만 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값이 유니크한 것들을 가져온다.

(6) 내부 임시 테이블 사용

스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑 할때는 내부적인 임시 테이블을 사용한다.
일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다. 물론 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 한다.
내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능하다.

<1> 메모리 임시 테이블과 디스크 임시 테이블

MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할때는 MEMORY 스토리지 엔진을 사용하며 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.
하지만 MySQL 8.0 버전부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하고 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됐다.

<2> 임시 테이블이 필요한 쿼리

아래와 같은 패턴의 쿼리는 MySQL엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스이다.

1. ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
2. ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
3. DISTINCT와 ORDER BY가동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
4. UNION이나 UNION DISTINCT가 사용된 쿼리 (select_type 칼럼이 UNION RESULT인 경우)
5. 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

<3> 임시 테이블이 디스크에 생성되는 경우

• UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 temp_table_size 또는 max_heap_table_size 시스템 변수보다 크거ㅏ나 temptable_max_ram 시스템 변수 값보다 큰 경우