[MySQL] MySQL의 주요 처리 방식

MySQL 정렬의 처리 방식

---

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

정렬 처리 방법	실행 계획의 Extra 코멘트
인덱스 사용한 정렬	별도의 내용 표기 없음
드라이빙 테이블만 정렬  (조인이 없는 경우 포함)	"Using filesort"가 표시됨
조인 결과를 임시 테이블로 저장한 후,  임시 테이블에서 정렬	"Using temporary; Using filesort"가 같이 표시됨

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

1. 드라이빙 테이블만 정렬한 다음 조인을 수행

2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

일반적으로 조인이 수행되면서 레코드 건수는 거의 배수로 늘어나기 때문에 가능하면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적입니다. 그래서 두 번째 방법보다는 첫 번째 방법이 더 효율적으로 처리됩니다. 정렬을 처리하기 위해서는 인덱스를 이용하는 방법보다는 쿼리가 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있습니다.

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

물론 레코드를 정렬하기 위해 항상 "Filesort"라는 정렬 작업을 거쳐야 하는 것은 아닙니다. 이미 인덱스를 이용한 정렬은 이전 장에서 한번 살펴봤습니다. 하지만 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능합니다.

정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우

GROUP BY의 결과 또는 DISTINCT와 같은 처리의 결과를 정렬해야 하는 경우

UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우

랜덤하게 결과 레코드를 가져와야 하는 경우

MySQL이 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 "Using filesort"라는 코멘트가 표시되는지로 판단할 수 있습니다.  MySQL의 3가지 정렬 방법에 대해 살펴보겠습니다.

1) 인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 합니다. 또한 WHERE 절에 첫 번째 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 합니다. 그리고 B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없습니다. 예외적으로 R-Tree도 B-Tree 계열이지만 특성상 이 방식을 사용할 수 없습니다. 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 방식의 조인에서만 이 방식을 사용할 수 있습니다.

인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 됩니다. 실제로 MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지는 않습니다. 다음 예제처럼 ORDER BY가 있건 없건 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력되는 것을 확인할 수 있습니다. ORDER BY 절이 없어도 정렬이 되는 이유는 employees 테이블의 프라이머리 키를 읽고, 그 다음으로 salaries 테이블을 조인했기 때문입니다.

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

-- // emp_no 칼럼으로 정렬이 필요한데, 인덱스를 사용하면서 자동 정렬이 된다고
-- // 일부러 ORDER BY emp_no를 제거하는 것은 좋지 않은 선택이다.
SELECT * FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020;

ORDER BY 절을 넣지 않아도 자동으로 정렬된다고 해서 ORDER BY 절 자체를 쿼리에서 완전히 빼 버리고 쿼리를 작성하기도 합니다. 혹시나 ORDER BY 절을 포함하면 MySQL 서버가 별도로 정렬 작업을 한 번 더 할까봐 걱정스러워서입니다. 하지만 MySQL 서버는 정렬을 인덱스로 처리할 수 있다면 부가적으로 불필요한 정렬 작업을 수행하지 않습니다. 그래서 인덱스로 정렬이 처리될 때는 ORDER BY가 쿼리에 명시된다고 해서 작업량이 더 늘지는 않습니다.

위에서도 언급했듯이 인덱스를 사용한 정렬이 가능한 이유는 B-Tree 인덱스가 키값으로 정렬돼 있기 때문입니다. 또한 조인이 네스티드-루프 방식으로 실행되기 때문에 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다는 것입니다. 하지만 조인이 사용된 쿼리의 실행 계획에 조인 버퍼(Join buffer)가 사용되면 순서가 흐트러질 수 있기 때문에 주의해야 합니다.

2) 드라이빙 테이블만 정렬

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

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;

우선 WHERE 절의 조건이 다음 두 가지 조건을 갖추고 있기 때문에 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것입니다.

1. WHERE 절의 검색 조건("emp_no BETWEEN 100001 AND 100010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있습니다.

2. 드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 있습니다.

검색은 인덱스 레인지 스캔으로 처리할 수 있지만 ORDER BY 절에 명시된 칼럼은 employees 테이블의 프라이머리 키와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능합니다. 그런데 ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블(employees)에 포함된 칼럼임을 알 수 있습니다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한 것입니다.

A. 인덱스를 이용해 "emp_no BETWEEN 100001 AND 100010" 조건을 만족하는 9건을 검색

B. 검색 결과를 last_name 칼럼으로 정렬을 수행(Filesort)

C. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해서 86건의 최종 결과를 가져옴

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

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않습니다. 하지만 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있습니다. 위에서 살펴본 "드라이빙 테이블만 정렬"은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않습니다. 하지만 그 밖의 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거칩니다. 이 방법은 정렬의 3가지 방법 가운데 정렬해야 할 레코드 건수가 가장 많아지기 때문에 가장 느린 정렬 방법입니다. 다음 쿼리는 "드라이빙 테이블만 정렬"에서 살펴본 예제와 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 테이블을 읽어야 하므로 이 쿼리는 반드시 조인된 데이터를 가지고 정렬할 수밖에 없습니다.

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	e	range	PRIMARY	4		9	Using where;  Using temporary;  Using filesort;
1	SIMPLE	s	ref	PRIMARY	4	e.emp_no	4

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

정렬 방식의 성능 비교

주로 웹 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT가 거의 필수적으로 사용되는 경향이 있습니다. 일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 합니다. 그런데 ORDER BY나 GROUP BY와 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼만 가져와서는 처리될 수 없습니다. 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그룹핑 작업을 실행해야만 비로소 LIMIT로 건수 제한을 할 수 있습니다. WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생합니다.

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그룹핑 작업이 왜 느리게 작동할 수밖에 없는지 한번 살펴보겠습니다. 이를 위해 쿼리가 처리되는 방법을 "스트리밍 처리"와 "버퍼링 처리"라는 2가지 방식으로 구분해보겠습니다.

1) 스트리밍(Streaming) 방식

서버 쪽에서 처리해야 할 데이터가 얼마나 될지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미합니다. 이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받을 것입니다. 물론 가장 마지막의 레코드는 언제 받을지 알 수 없지만, 이는 그다지 중요하지 않습니다. 쿼리가 스트리밍 방식으로 처리될 수 있다면 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있습니다. 웹 서비스와 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요합니다. 스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해 줍니다.

또한 스트리밍 방식으로 처리되는 쿼리에서 LIMIT와 같이 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있습니다. 매우 큰 테이블을 아무런 조건 없이 SELECT만 해 보면 첫 번째 레코드는 아주 빨리 가져온다는 사실을 알 수 있습니다. 물론 서버에서는 쿼리가 아직 실행되고 있는 도중이라도 말입니다. 이것은 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍되기 때문입니다. 이 쿼리에 LIMIT 조건을 추가하면 전체적으로 가져오는 레코드 건수가 줄어들기 때문에 마지막 레코드를 가져오기까지의 시간을 상당히 줄일 수 있습니다.

스트리밍 처리는 어떤 클라이언트 도구나 API를 사용하느냐에 따라 그 방식에 차이가 있을 수도 있습니다. 대표적으로 JDBC 라이브러리를 이용해 "SELECT * FROM tb_bigtable"와 같은 쿼리를 실행하면 MySQL 서버는 레코드를 읽자마자 클라이언트로 그 결과를 전달할 것입니다. 하지만 JDBC는 MySQL 서버로부터 받는 레코드를 일단 자체적인 버퍼에 모두 담아둡니다. 그리고 마지막 레코드가 전달될 때까지 기다렸다가 모든 결과를 전달받으면 그때서야 비로소 클라이언트의 애플리케이션에 반환합니다. 즉, MySQL 서버는 스트리밍 방식으로 처리해서 반환하지만 클라이너트의 JDBC 라이브러리가 버퍼링을 하는 것입니다. 하지만 JDBC를 사용하지 않는 SQL 클라이언트 도구는 이러한 버퍼링을 하지 않기 때문에 아무리 큰 테이블이라 하더라도 첫 번째 레코드는 매우 빨리 가져옵니다.

JDBC 라이브러리가 자체적으로 레코드를 버퍼링하는 이유는 이 방식이 전체 처리량(Throughput)에서 뛰어나기 때문입니다. 이 방식은 JDBC 라이브러리와 MySQL 서버가 대화형으로 데이터를 주고받는 것이 아니라 MySQL 서버는 데이터의 크기에 관계없이 무조건 보내고, JDBC MySQL 서버로부터 전송되는 데이터를 받아서 저장만 하므로 불필요한 네트워크 요청이 최소화되기 때문에 전체 처리량이 뛰어난 것입니다.

2) 버퍼링(Buffering) 방식

ORDER BY나 GROUP BY와 같은 처리는 쿼리의 결과와 스트리밍되는 것을 불가능하게 합니다. 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그룹핑을 해서 차례대로 보내야 하기 때문입니다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려지는 것입니다. 이 방식을 스트리밍의 반대 표현으로 버퍼링(Buffering)이라고 표현해 본 것입니다. 

버퍼링 방식은 먼저 쿼리의 결과를 모아 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 합니다. 그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않습니다. 네트워크로 전송되는 레코드의 건수를 줄일 수는 있지만 MySQL 서버가 해야하는 작업량에는 그다지 변화가 없기 때문입니다.

"정렬의 처리 방식"에서 언급한 ORDER BY의 3가지 처리 방식 가운데 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후에 정렬됩니다. 즉 인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있습니다. 하지만 인덱스를 사용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT로 제한된 건수만큼 잘라서 클라이언트로 전송해줄 수 있음을 의미합니다.

조인과 함께 ORDER BY 절과 LIMIT 절이 함께 사용될 경우, 정렬의 각 처리 방식별로 어떤 차이가 있는지 좀 더 자세히 살펴보겠습니다.

SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE tb1.col1 = t2.col1
ORDER BY t1.col2
LIMIT 10;

tb_test1 테이블의 레코드가 100건이고, tb_test2 테이블의 레코드가 1,000건(tb_test1의 레코드 1건당 tb_test2의 레코드가 10건씩 존재한다고 가정)이며, 두 테이블의 조인 결과는 전체 1,000건이라고 가정하고 정렬의 처리 방식별로 읽어야 하는 레코드 건수와 정렬을 수행해야 하는 레코드 건수를 비교해 보겠습니다.

tb_test1이 드라이빙되는 경우

정렬 방식	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	tb_test1 : 1건  tb_test2 : 10건	1번	0건
드라이빙 테이블만  정렬	tb_test1 : 100건  tb_test2 : 10건	10번	100건  (tb_test1 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후  정렬	tb_test1 : 100건  tb_test2 : 1000건	100번  (tb_test1 테이블의 레코드 건수만큼 조인 발생)	1,000건  (조인된 결과 레코드 건수를 전부 정렬해야 함)

tb_test2가 드라이빙되는 경우

정렬 방식	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	tb_test1 : 10건  tb_test2 : 10건	10번	0건
드라이빙 테이블만  정렬	tb_test1 : 1000건  tb_test2 : 10건	10번	1,000건  (tb_test2 테이블의 레코드 건수만큼 정렬 필요)
임시 테이블 사용 후  정렬	tb_test1 : 1000건  tb_test2 : 100건	1,000번  (tb_test2 테이블의 레코드 건수만큼 조인 발생)	1,000건  (조인된 결과 레코드 건수를 전부 정렬해야 함)

어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만듭니다. 가능하다면 인덱스를 사용한 정렬로 유도하고 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있습니다.

인덱스를 사용하지 못하고 별도로 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT 조건이 아무런 도움이 되지 못하는 것은 아닙니다. 정렬해야 할 대상 레코드가 1,000건인 쿼리에 LIMIT 10이라는 조건이 있다면 MySQL 서버는 1,000건의 레코드를 모두 정렬하는 것이 아니라 필요한 순서(ASC 또는 DESC)대로 정렬해서 상위 10건만 정렬이 채워지면 정렬을 멈추고 결과를 반환합니다. 하지만 MySQL 서버는 정렬을 위해 퀵 소트 알고리즘을 사용합니다. 이는 LIMIT 10을 만족하는 상위 10건을 정렬하기 위해 더 많은 작업이 필요할 수도 있음을 의미합니다. 퀵 소트 알고리즘은 인터넷에서 쉽게 확인해볼 수 있으므로 한 번쯤 살펴보는 것이 좋습니다.

결론적으로, 인덱스를 사용하지 못하는 쿼리를 페이징 처리에 사용하는 경우 LIMIT로 5~10건만 조회한다고 하더라도 쿼리가 기대만큼 아주 빨라지지는 않습니다.

정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장하고 있습니다. 정렬과 관련해서도 지금까지 몇 건의 레코드나 정렬 처리를 수행했는지, 소트 버퍼 간의 병합 작업(멀티 머지)은 몇 번이나 발생했는지 등을 다음과 같은 명령으로 확인해 볼 수 있습니다.

mysql> SHOW SESSION STATUS LIKE 'Sort%';

mysql> SELECT first_name, last_name

FROM employees

GROUP BY first_name, last_name;

mysql> SELECT SESSION STATUS LIKE 'Sort%';

각 상태 값은 다음과 같은 의미가 있으며, 이 값들을 이용해 지금까지 MySQL 서버가 처리한 정렬 작업의 내용을 어느 정도 이해할 수 있습니다.

Sort_merge_passes는 멀티 머지 처리 횟수를 의미합니다.

Sort_range는 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수입니다.

Sort_scan은 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수입니다. Sort_scan과 Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값입니다.

Sort_rows는 지금까지 정렬한 전체 레코드 건수를 의미합니다.

이 예제의 결과를 해석해보면 대략 다음과 같은 내용을 알아낼 수 있습니다.

풀 테이블 스캔의 결과를 1번(2 - 1 = 1) 정렬

단위 정렬 작업의 결과를 56번(112 - 56 = 56) 병합 처리

전체 정렬된 레코드 건수는 279,408건(558,816 - 279,408 = 279,408)

GROUP BY 처리

GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 요소 중 하나입니다. GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행합니다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없습니다.

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

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

ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리합니다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹 함수(Aggregation function) 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있습니다. GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 "GROUP BY 처리"에서 언급한 추가적인 정렬 작업은 필요하지 않습니다. 이런 그룹핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 칼럼에 별도로 GROUP BY 관련 코멘트(Using index for group-by)나 임시 테이블이나 정렬 관련 코멘트(Using temporary, Using filesort)가 표시되지 않습니다.

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

루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미하는데, 실행 계획의 "Using index for group-by" 코멘트를 설명하면서 한번 언급한 적이 있습니다. 루스 인덱스 스캔을 사용하는 다음 예제를 한번 살펴보겠습니다.

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

salaries 테이블의 인덱스는 (emp_no + from_date)로 생성돼 있으므로 위의 쿼리 문장에서 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리입니다. 하지만 이 쿼리의 실행 계획은 다음과 같이 인덱스 레인지 스캔(range 타입)을 이용했으며, Extra 칼럼의 메시지를 보면 GROUP BY 처리까지 인덱스를 사용했다는 것을 알 수 있습니다.

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	salaries	range	PRIMARY	7		568914	Using where;  Using index for group-by

MySQL 서버가 이 쿼리를 어떻게 실행했는지, 순서대로 하나씩 살펴보겠습니다.

1. (emp_no + from_date) 인덱스를 차례대로 스캔하면서, emp_no의 첫 번째 유일한 값(그룹 키) "10001"을 찾아냅니다.

2. (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) 인덱스를 검색하는 것과 거의 흡사합니다.

3. (emp_no + from_date) 인덱스에서 emp_no의 그 다음 유니크한(그룹 키) 값을 가져옵니다.

4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행합니다.

MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있습니다. 또한 프리픽스 인덱스(Prefix index, 칼럼값의 앞쪽 일부만으로 생성된 인덱스)는 루스 인덱스 스캔을 사용할 수 없습니다. 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상됩니다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어냅니다. 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도이 임시 테이블이 필요하지 않습니다.

루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 WHERE 절의 조건이나 ORDER BY 절이 인덱스를 사용할 수 있을지 없을지 판단하는 것보다는 어렵습니다. 여기서는 여러 패턴의 쿼리를 살펴보고, 루스 인덱스 스캔을 사용할 수 있는지 없는지 판별하는 연습을 해보겠습니다. 우선, (col1+col2+col3) 칼럼으로 생성된 tb_test 테이블을 가정해보겠습니다. 다음의 쿼리들은 루스 인덱스 스캔을 사용할 수 있는 쿼리입니다. 쿼리의 패턴을 보고, 어떻게 사용 가능한 것인지를 생각해보겠습니다.

SELECT col1, col2 FROM tb_test GROUP BY col1, col2;
SELECT DISTINCT col1, col2 FROM tb_test;
SELECT col1, MIN(col2) FROM tb_test GROUP BY col1;
SELECT col1, col2 FROM tb_test WHERE col1 < const GROUP BY col1, col2;
SELECT MAX(col3), MIN(col3), col1, col2 FROM tb_test WHERE col2 > const GROUP BY col1, col2;
SELECT col2 FROM tb_test WHERE col1 < const GROUP BY col1, col2;
SELECT col1, col2 FROM tb_test WHERE col3 = const GROUP BY col1, col2;

다음의 쿼리는 루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴입니다.

-- // MIN()과 MAX() 이외의 집합 함수가 사용됐기 때문에 루스 인덱스 스캔은 사용 불가
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1;

-- // GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않기 때문에 사용 불가
SELECT col1, col2 FROM tb_test GROUP BY col2, col3;

-- // SELECT 절의 칼럼이 GROUP BY와 일치하지 않기 때문에 사용 불가
SELECT col1, col3 FROM tb_test GROUP BY col1, col2;

일반적으로 B-Tree 인덱스는 인덱스를 구성하는 칼럼이 왼쪽부터 일치하는 형태로 사용될 때만 사용할 수 있습니다. 하지만 루스 인덱스 스캔은 인덱스의 첫 번째 칼럼이 WHERE 조건이나 GROUP BY에 사용되지 않아도 B-Tree 인덱스를 사용할 수 있는 방식이기도 합니다. 오라클과 같은 DBMS에서는 옵티마이저가 인덱스의 첫 번째 칼럼에 대한 조건을 마음대로 만들어서 추가하는 형태로 이런 기능이 구현돼 있습니다. 하지만 지금 출시되는 MySQL 5.0, 5.1, 그리고 5.5에서는 아직 루스 인덱스 스캔이 이렇게 인덱스를 사용할 수 있는 방법은 지원되지 않습니다. MySQL의 루스 인덱스 스캔의 최적화는 아직 초기 수준이라고 할 수 있습니다.

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

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

EXPLAIN
SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
GROUP BY e.last_name;

이 쿼리의 실행 계획에서는 Extra 칼럼에 "Using temporary"와 "Using filesort" 메시지가 표시됐습니다. 이 실행 계획에서 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL)하기 때문이 아니라 인덱스를 전혀 사용할 수 없는 GROUP BY이기 때문입니다.

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	e	ALL				300584	Using temporary;  Using filesort;
1	SIMPLE	s	ref	PRIMARY	4	e.emp_no	4

1. Employees 테이블을 풀 테이블 스캔 방식으로 읽습니다.

2. 1번 단계에서 읽은 employees 테이블의 emp_no 값을 이용해 salaries 테이블을 검색 합니다.

3. 2번 단계에서 얻은 조인 결과 레코드를 임시 테이블에 저장합니다. 이 단계에서 사용되는 임시 테이블은 원본 쿼리에서 GROUP BY 절에 사용된 칼럼과 SELECT하는 칼럼만 저장합니다. 이 임시 테이블에서 중요한 것은 GROUP BY 절에 사용된 칼럼으로 유니크 키를 생성한다는 점입니다. 즉, GROUP BY가 임시 테이블로 처리되는 경우 사용되는 임시 테이블은 항상 유니크 키를 가집니다.

4. 1번 단계부터 3번 단계를 조인이 완료될 때까지 반복합니다. 조인이 완료되면 임시 테이블의 유니크 키 순선대로 읽어서 클라이언트로 전송됩니다. 만약, 쿼리의 ORDER BY 절에 명시된 칼럼과 GROUP BY 절에 명시된 칼럼이 같으면 별도의 정렬 작업을 수행하지 않습니다. ORDER BY 절과 GROUP BY 절에 명시된 칼럼이 다르다면 Filesort 과정을 거치면서 다시 한번 정렬 작업을 수행합니다.

 

DISTINCT 처리

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

 

 

SELECT DISTINCT ...

단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용합니다. 이 경우에는 GROUP BY와 거의 같은 방식으로 처리됩니다. 단지 차이는 SELECT DISTINCT의 경우에는 정렬이 보장되지 않는다는 것뿐입니다. 다음의 두 쿼리는 정렬 관련 부분만 빼면 내부적으로 같은 작업을 수행합니다. 그런데 사실 이 두 개의 쿼리는 모두 인덱스를 이용하기때문에 부가적인 정렬 작업이 필요하지 않으며 완전히 같은 쿼리입니다. 하지만 인덱스를 이용하지 못하는 DISTINCT는 정렬을 보장하지 않습니다.

 

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

DISTINCT를 사용할 때 자주 실수하는 것이 있습니다. DISTINCT는 SELECT하는 레코드(튜플)를 유니크하게 SELECT하는 것이지 칼럼을 유니크하게 조회하는 것이 아닙니다. 즉, 다음 쿼리에서 SELECT하는 결과는 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name+last_name) 전체가 유니크한 레코드를 가져오는 것입니다.

 

SELECT DISTINCT first_name, last_name FROM employees;

가끔 DISTINCT를 다음과 같이 사용할 때도 있습니다.

 

SELECT DISTINCT(first_name), last_name FROM employees;

위의 쿼리는 얼핏 보면, first_name만 유니크하게 조회하고 last_name은 그냥 DISTINCT가 없을 때와 동일하게 조회하는 쿼리처럼 보입니다. 그리고 실제로 상당히 그럴듯하게 아무런 에러 없이 실행되기 때문에 쉽게 실수할 수 있는 부분입니다. 하지만 MySQL 서버는 DISTINCT 뒤의 괄호를 그냥 의미없이 사용된 괄호로 해석하고 제거해 버립니다. DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것입니다.

 

SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미칩니다. 절대로 SELECT하는 여러 칼럼 중에서 일부 칼럼만 유니크하게 조회하는 방법은 없습니다. 단, 이어서 설명할 DISTINCT가 집합 함수 내에 사용된 경우는 조금 다릅니다.

 

집합 함수와 함께 사용된 DISTINCT

COUNT() 또는 MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데, 이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석됩니다. 집합 함수가 없는 SELECT 쿼리에서 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)"를 처리하기 위해 임시 테이블을 사용합니다. 하지만 이 쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않습니다. 이는 버그처럼 보이지만 MySQL 5.0 5.1 그리고 5.5 모두 실행 계획의 Extra 칼럼에 "Using temporary"가 표시되지 않습니다.

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	e	range	PRIMARY	4		100	Using where;  Using index;
1	SIMPLE	s	ref	PRIMARY	4	e.emp_no	4

 

위의 쿼리의 경우에는 employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용합니다. 이때 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리입니다.

 

만약 위의 쿼리에 COUNT(DISTINCT ...)를 하나 더 추가해서 다음과 같이 변경해보겠습니다. COUNT() 함수가 두 번 사용된 다음 쿼리의 실행 계획은 위의 쿼리와 똑같이 표시됩니다. 하지만 다음 쿼리를 처리하려면 s.salary 칼럼의 값을 저장하는 임시 테이블과 e.last_name 칼럼의 값을 저장하는 또 다른 임시 테이블이 필요하므로 전체적으로 2개의 임시 테이블을 사용합니다.

 

SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

위의 쿼리는 DISTINCT 처리를 위해 인덱스를 이용할 수 없어서 임시 테이블이 필요했습니다. 하지만 다음 쿼리와 같이 인덱스된 칼럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 쿼리를 수행할 수 있습니다.

 

SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	dept_emp	range	PRIMARY	16		334242	Using index;

DISTINCT가 집합 함수 없이 사용된 경우와 집합 함수 내에서 사용된 경우 쿼리의 결과가 조금씩 달라지기 때문에 그 차이를 정확하게 이해해야 합니다. 다음 3개 쿼리의 차이를 잘 기억해둡시다.

 

SELECT DISTINCT first_name, last_name
FROM emplyees
WHERE emp_no BETWEEN 10001 AND 10200;

SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name)
FROM employees
WHERE emp_no BETWEEN 10001 AND 10200;

SELECT COUNT(DISTINCT first_name, last_name)
FROM employees
WHERE emp_no BETWEEN 10001 AND 10200;

 

임시 테이블(Using temporary)

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시 테이블을 사용합니다. "내부적"이라는 단어가 포함된 것은 여기서 이야기하는 임시 테이블은 "CREATE TEMPORARY TABLE"로 만든 임시 테이블과는 다르기 때문입니다. 일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨집니다. 물론 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 합니다. 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용합니다.

 

MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능합니다. 사용자가 생성한 임시 테이블(CREATE TEMPORARY TABLE)과는 달리 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제됩니다.

 

 

임시 테이블이 필요한 쿼리

다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스입니다. 물론 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많습니다.

 

ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리

ORDER BY와 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리

DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리

UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)

UNION ALL이 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)

쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 칼럼에 "Using temporary"라는 키워드가 표시되는지 확인하면 됩니다. 하지만 "Using temporary"가 표시되지 않을 때도 임시 테이블을 사용할 수 있는데, 위의 예에서 마지막 3개 패턴이 그런 예입니다. 첫번째부터 네번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블이 만들어집니다. 그리고 다섯 번째와 여섯 번째 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성됩니다. 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 상당히 처리 성능이 느립니다.

 

 

임시 테이블이 디스크에 생성되는 경우(MyISAM 스토리지 엔진을 사용)
내부 임시 테이블은 기본적으로는 메모리상에 만들어지지만 다음과 같은 조건을 만족하면 메모리에 임시 테이블을 생성할 수 없으므로 디스크상에 MyISAM 테이블로 만들어집니다.

 

임시 테이블에 저장해야 하는 내용 중 BLOB(Binary Large Object)나 TEXT와 같은 대용량 칼럼이 있는 경우

임시 테이블에 저장해야 하는 레코드의 전체 크기나 UNION이나 UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상인 크기의 칼럼이 있는 경우

GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우

임시 테이블에 저장할 데이터의 전체 크기(데이터의 바이트 크기)가 tmp_table_size 또는 max_heap_table_size 시스템 설정 값보다 큰 경우

 

첫 번째부터 세 번째까지는 처음부터 디스크에 MyISAM 스토리지 엔진을 사용해서 내부 임시 테이블이 만들어집니다. 하지만 네 번째는 처음에는 MEMORY 스토리지 엔진을 이용해 메모리에 내부 임시 테이블이 생성되지만 테이블의 크기가 시스템 설정 값을 넘어서는 순간 디스크의 MyISAM 테이블로 변환됩니다

 

 

임시 테이블 관련 상태 변수

실행 계획상에서 "Using temporary"가 표시되면 임시 테이블을 사용했다는 사실을 알 수 있습니다. 하지만 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지는 알 수 없으며, 몇 개의 임시 테이블이 사용됐는지도 알 수 없습니다. "Using temporary"가 한번 표시됐다고 해서 임시 테이블을 하나만 사용했다는 것을 의미하지는 않습니다. 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 파악하려면 MySQL 서버의 상태 변수(SHOW SESSION STATUS LIKE 'Created_tmp%')를 확인해 보면 됩니다.

 

mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';

 

mysql> SELECT first_name, last_name

FROM employees

GROUP BY first_name, last_name;

 

mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';

 

위의 내용을 보면 쿼리를 실행하기 전에 "SHOW SESSION STATUS LIKE 'Created_tmp%';" 명령으로 임시 테이블의 사용 현황을 먼저 확인해 둡니다. 그리고 SELECT 쿼리를 실행한 후, 다시 상태 조회 명령을 실행해 보면 됩니다. 예제의 두 상태 변수가 누적하고 있는 값의 의미는 다음과 같습니다.

 

Created_tmp_tables

쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태 값. 이 값은 내부 임시 테이블이 메모리에 만들어졌는지 디스크에 만들어졌는지를 구분하지 않고 모두 누적한다.

 

Created_tmp_disk_tables

디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값.

 

내부 임시 테이블의 사용 현황을 한 예로 들면, 임시 테이블이 1개(3-2=1)가 생성됐는데, "Created_tmp_disk_tables" 상태 변수 값의 변화를 보면 해당 임시 테이블이 디스크에 만들어졌었음을 알 수 있습니다.

 

 

임시 테이블 관련 주의사항

레코드 건수가 많지 않으면 내부 임시 테이블이 메모리에 생성되고 MySQL의 서버의 부하에 크게 영향을 미치지는 않습니다. 성능상의 이슈가 될만한 부분은 내부 임시 테이블이 MyISAM 테이블로 디스크에 생성되는 경우입니다.

 

SELECT * FROM employees GROUP BY last_name ORDER BY first_name;

이 쿼리는 GROUP BY와 ORDER BY 칼럼이 다르고, last_name 칼럼에 인덱스가 없기 대문에 임시 테이블과 정렬 작업까지 수행해야 하는 가장 골칫거리가 되는 쿼리 형태 입니다.

 

id	select_type	table	type	key	key_len	ref	rows	Extra1
1	SIMPLE	employees	ALL				300584	Using temporary;  Using filesort

 

Rows 칼럼의 값을 보면 이 쿼리는 대략 처리해야 하는 레코드 건수가 30만 건 정도라는 사실을 알 수 있습니다. 이 실행 계획의 내부적인 작업 과정을 살펴보면 다음과 같습니다.

 

1. Employees 테이블의 모든 칼럼을 포함한 임시 테이블을 생성(MEMORY 테이블)

2. Employees 테이블로부터 첫 번째 레코드를 InnoDB 스토리지 엔진으로부터 가져와서

3. 임시 테이블에 같은 last_name이 있는지 확인

4. 같은 last_name이 없으면 임시 테이블에 INSERT

5. 같은 last_name이 있으면 임시 테이블에 UPDATE 또는 무시

6. 임시 테이블의 크기가 특정 크기보다 커지면 임시 테이블은 MyISAM 테이블로 디스크로 이동

7. Employees 테이블에서 더 읽을 레코드가 없을 때까지 2~6번 과정 반복(이 쿼리에서는 30만 회 반복)

8. 최종 내부 임시 테이블에 저장된 결과에 대해 정렬 작업을 수행

9. 클라이언트에 결과 반환

 

SELECT 절에 포함된 칼럼의 특징에 따라 3번~5번 과정은 조금씩 차이가 있지만 임시 테이블이 일반적으로 이러한 과정을 거친다고 생각하면 됩니다. 여기서 중요한 것은 임시 테이블이 메모리에 있는 경우는 조금 다르겠지만 디스크에 임시 테이블이 저장된 경우라면 30만 건을 임시 테이블로 저장하려면 적지 않은 부하가 발생하리라는 것입니다. 가능하다면 인덱스를 이용해 처리하고, 처음부터 임시 테이블이 필요하지 않게 만드는 것이 가장 좋습니다. 만약 이렇게 하기가 어렵다면 내부 임시 테이블이 메모리에만 저장될 수 있게 가공 대상 레코드를 적게 만드는 것이 좋습니다. 하지만 가공해야 할 데이터를 줄일 수 없다고 해서 tmp_table_size 또는 max_heap_table_size 시스템 설정 변수를 무조건 크게 설정하면 MySQL 서버가 사용할 여유 메모리를 내부 임시 테이블이 모두 사용해버릴 수도 있으므로 주의해야 합니다.

 

임시 테이블이 MEMORY(HEAP) 테이블로 물리 메모리에 생성되는 경우에도 주의해야 할 사항이 있습니다. MEMORY(HEAP) 테이블의 모든 칼럼은 고정 크기 칼럼이라는 점입니다. 만약, 위의 예제 쿼리에서 first_name 칼럼이 VARCHAR(512)라고 가정해보겠습니다. 실제 메모리 테이블에서 first_name 칼럼이 차지하는 공간은 512*3(문자집합을 utf8로 가정) 바이트가 될 것입니다. 실제 first_name 칼럼의 값이 1글자이든 2글자이든 관계없이, 테이블에 정의된 크기만큼 메모리 테이블에서 공간을 차지한다는 것입니다. 이러한 임시 테이블의 저장 방식 때문에 SELECT하는 칼럼은 최소화하고(특히 불필요하면 BLOB 이나 TEXT 칼럼은 배제하는 것이 좋습니다.), 칼럼의 데이터 타입 선정도 가능한 한 작게 해주는 것이 좋습니다.

출처: https://12bme.tistory.com/161?category=682920 [길은 가면, 뒤에 있다.]