[MySQL]MySQL 벼락치기(8)

[MySQL]MySQL 벼락치기(8) - 실행계획(2)

강씨아저씨 2018. 9. 22. 12:02

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

MySQL 주요 처리 방식

이후 설명하는 내용중에 풀 테이블 스캔을 제외한 나머지는 모두 스토리지 엔진이 아니라 MySQL 엔진에서 처리되는 내용이다. 또한 MySQL 엔진에서 부가적으로 처리하는 작업은 대부분 성능에 미치는 영향력이 큰데, 안타깝게도 모두 쿼리의 성능에 저하시키는 데 한 몫하는 작업이다. 스토리지 엔진에서 읽은 레코드를 MySQL 엔진이 아무런 가공 작업도 하지 않고 사용자에게 반환한다면 최상의 성능을 보장하는 쿼리가 되겠지만, 우리가 필요로 하는 대부분의 쿼리는 그렇지 않다. MySQL 엔진에서 처리하는데 시간이 오래 걸리는 작업의 원리를 알아둔다면 쿼리를 튜닝하는데 상당히 많은 도움이 될 것이다.

풀 테이블 스캔

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미한다. MySQL 옵티마이저는 다음과 같은 조건이 일치할때 주로 풀 테이블 스캔을 선택한다.

테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른경우
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는경우
인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

ORDER BY처리

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다. MySQL 은 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 컬럼에 "Using filesort" 라는 코멘트가 표시되는지로 판단할 수 있다.

소트버퍼(Sort buffer)
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당하며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.
정렬 알고리즘
레코드를 정렬할 때, 레코드 전체를 소트버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 2가지 정렬 알고리즘으로 나눠볼 수 있다.

싱글 패스 알고리즘
소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 되는 컬럼 전부를 담아서 정렬을 수행하는 방법이다. 정렬 대상 레코드의 크기나 건수가 상당히 작은경우 빠른 성능을 보인다.
투 패스 알고리즘
정렬 대상 컬럼과 프라이머리 키값만을 소트버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 컬럼을 가져오는 알고리즘이다. 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우에 효율적이다.

정렬의 처리 방식

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방식중 하나로 정렬이 처리된다. 일반적으로 밑쪽에 있는 정렬 방법으로 갈수록 처리가 느려진다.

먼저 옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토할 것이다. 만약 인덱스를 이용 할 수 있다면 별도의 "filesort" 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다. 하지만 인덱스를 사용할 수 없다면 WHERER 조건에 일치하는 레코드를 검색해 정렬 버퍼에저장하면서 정렬을 처리 할 것이다. 이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 두가지 방법중 하나를 선택한다.

드라이빙 테이블만 정렬한 다음 조인을 수행
조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

일반적으로 조인이 수행되면서 레코드 건수는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음조인을 수행하는 방법이 효율적이다. 그래서 두번째 방법보다는 첫번째 방법이 더 효율적으로 처리된다. 이제부터 3가지 방법에 대해 차례대로 알아보자.

인덱스를 이용한 정렬
인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY 에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고 ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE 절에 첫번째 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY 는 같은 인덱스를 사용할 수 있어야 한다.

드라이빙 테이블만 정렬
일반적으로 조인이 수행되면 결과 레코드의 건수가 몇배로 불어난다. 그래서 조인을 실행하기 전에 첫번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다. 이 방법은 조인에서 첫 번째 읽히는 테이블의 컬럼만으로 ORDER BY 절이 작성돼야 한다.

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

우선 WHERE 절의 조건이 다음 두가지 조건을 갖추고 있기 때문에 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것이다.
1. WHERE 절의 검색조건(emp_no BETWEEN 100001 AND 100010) 은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
2. 드리븐 테이블(salaries) 의 조인컬럼인 emp_no 컬럼에 인덱스가 있다.
검색은 인덱스 레인지 스캔으로 처리할 수 있지만 ORDER BY 절에 명시된 컬럼은 employees 테이블의 프라이머리 키와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하다. 그런데 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블에 포함된 컬럼임을 알 수 있다. 그래서 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행한후 그 결과와 salaries 테이블을 조인한 것이다.

임시 테이블을 이용한 정렬
다음 쿼리는 "드라이빙 테이블만 정렬"에서 살펴본 예제와 ORDER BY 절의 컬럼만 제외하고 같은 쿼리다. 이 쿼리도 "드라이빙 테이블만 정렬"과 같은 이유로 employees 테이블이 드라이빙 테이블로 사용되며, salaries 테이블이 드리븐 테이블로 사용될 것이다.

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;

하지만 이번 쿼리에서는 ORDER BY 절의 정렬기준 컬럼이 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 컬럼이다. 즉 정렬이 수행되기 전에 반드시 salaries 테이블을 읽어야 하므로 이 쿼리는 반드시 조인된 데이터를 가지고 정렬할 수 밖에 없다.

쿼리 실행계획을 보면 Extra 컬럼에 "Using temporary; Using filesort" 라는 코멘트가 표시된다 이는 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리 했음을 의미한다.

GROUP BY 처리

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

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
조인의 드라이빙 테이블에 속한 컬럼만 이용해 그룹핑을 할때 GROUP BY 컬럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리한다.
루스 인덱스 스캔을 이용하는 GROUP BY
```
EXPLAIN
SELECT emp_no
FROM salaries
WHERE from_date= '1985-03-01'
GROUP BY emp_no;
```
salaries테이블의 인덱스는(emp_no + from_date)로 생성돼 있으므로 위의 쿼리 문장에서 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리다. 하지만 이 쿼리 실행계획은 다음과 같이 인덱스 레인지 스캔을 이용했으며 Extra 컬럼의 메시지를 보면 GROUP BY 처리까지 인덱스를 사용했다는 것을 알 수 있다.
MySQL 서버가 이 쿼리를 어떻게 실행했는지 순서대로 알아보자.

(emp_no + from_date) 인덱스를 차례대로 스캔하면서, emp_no의 첫번째 유일한 값(그룹 키) "10001"을 찾아낸다.
(emp_no + from_date) 인덱스에서 emp_no가 '10001' 인 것 중에서 from_date 값이 '1985-03-01' 인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에 사용된 'from_date = 1985-03-01' 조건을 합쳐서 'emp_no=10001 AND from_date = '1985-03-01' 조건으로 (emp_no + from_date) 인덱스를 검색하는 것과 거의 흡사하다.
(emp_no + from_date) 인덱스에서 emp_no의 그 다음 유니크한(그룹 키) 값을 가져온다.
3번 단계에서 결과가 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.

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

DISTINCT 처리

특정 컬럼의 유니크한 값만을 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. DISTINCT는 MIN( ), MAX( ) 또는 COUNT( ) 와 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우 두가지로 구분해서 살펴보자. 이렇게 구분한 이유는 DISTINCT 키워드가 영향을 미치는 범위가 달라지기 때문이다. 그리고 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행계획에서 DISTINCT 처리가 인덱스를 사용하지 못할때는 항상 임시 테이블이 필요하다.

SELECT DISTINCT
단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 뭐리 문장을 사용한다. 이 경우에는 GROUP BY와 거의 같은 방식으로 처리된다. 단지 차이는 SELECT DISTINCT의 경우에는 정렬이 보장되지 않는다는 것 뿐이다.
DISTINCT 를 사용할 때 자주 실수하는 것이 있다 DISTINCT 는 SELECT 하는 레코드를 유니크하게 SELECT 하는 것이지 컬럼을 유니크하게 조회하는 것이 아니다. 즉 다음 쿼리에서 SELECT 하는 결과는 first_name 만 유니크한 것을 가져오는 것이 아니라 (first_name + last_name) 전체가 유니크한 레코드를 가져오는 것이다.
```
SELECT DISTINCT first_name, last_name FROM employees;
```
SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 컬럼에 영향을 미친다. 절대로 SELECT 하는 여러 컬럼중에서 일부 컬럼만 유니크하게 조회하는 방법은 없다. 단, 이어서 설명할 DISTINCT가 집합 함수 내에 사용된 경우는 조금 다르다.
집합함수와 함께 사용된 DISTINCT
COUNT( ), MIN( ), MAX( ) 와 같은 집합함수내에서 DISTINCT 키워드가 사용될 수 있는데, 이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다. 집합 함수가 없는 SELECT 쿼리에서 DISTINCT는조회하는 모든 컬럼의 조합이 유니크한 것들만 가져온다. 하지만 집합함수 내에서 사용된 DISTINCT는 그 집합함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져온다.
```
SELECT COUNT(DISTINCT first_name, last_name) FROM employees;
```

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~