MySQL 실행 계획 : MySQL의 주요 처리 방식(2)

de_sj_awa·2021년 10월 1일
mysql
0

MySQL 실행 계획 : MySQL의 주요 처리 방식(2)

2. ORDER BY 처리(Using filesort)

정렬의 처리 방식

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

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

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

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

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

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 반드시 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 1000002 AND 100020
ORDER BY e.emp_no;

-- // emp_no 칼럼으로 정렬이 필요한데, 인덱스를 사용하면서 자동 정렬이 된다고
-- // 일부러 ORDER BY emp_no를 제거하는 것은 좋지 않은 선택이다.
SELELCT * 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가 쿼리에 명시된다고 해서 작업량이 더 늘지는 않는다.
또한, 어떤 이유 때문에 쿼리의 실행 계획이 조금 변경된다면 ORDER BY가 명시되지 않은 쿼리는 결과가 기대했던 순서대로 가져오지 못해서 애플리케이션의 버그로 연결될 수도 있다. 하지만 ORDER BY 절을 명시해두면 성능상의 손해가 없음은 물론이고 이런 예외 상황에서도 버그로 연결되지 않을 것이다.

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

드라이빙 테이블만 정렬

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

SELECT * FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
  AND e.emp_no BETWEEN 100002 ABD 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건의 최종 결과를 가져옴(아래 그림의 오른쪽에 번호는 레코드가 조인되어 출력되는 순서를 의미).

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 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 Extra
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가지 방식으로 구분해보자.

  • 스트리밍(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 서버로부터 전송되는 데이터를 받아서 저장한 하므로 불필요한 네트워크 요청이 최소화되기 때문에 전체 처리량이 뛰어난 것이다.
하지만 JDBC의 버퍼링 처리 방식은 기본 작동 방식이며, 아주 대량의 데이터를 가져와야 할 때는 MySQL 서버와 JDBC 간의 전송 방식을 스트리밍 방식으로 변경할 수 있다.

  • 버퍼링 방식(Buffering) 방식
    ORDER BY나 GROUP BY와 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다. 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그룹핑을 해서 차례대로 보내야 하기 때문이다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려지는 것이다. 이 방식을 스트리밍의 반대 표현으로 버퍼링(Buffering)이라고 표현해 본 것이다.
    아래 그림에서 보는 바와 같이 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다. 그래서 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다. 네트워크로 전송되는 레코드의 건수를 줄일 수는 있지만 MySQL 서버가 해야 하는 작업량에는 그다지 변화가 없기 때문이다.

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

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

SELECT *
FROM tb_test t1, tb_test2 t2
WHERE t1.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_test2 : 10건
tb_test1 : 10건		 10번 0건
드라이빙 테이블만
정렬		 tb_test2 : 1000건
tb_test1 : 10건		 10번 1,000건
(tb_test2 테이블의 레코드
건수만큼 정렬 필요)
임시 테이블을 사용한
후 정렬		 tb_test2 : 1000건
tb_test1 : 100건		 1,000번
(tb_test2 테이블의 레코드
건수만큼 조인 발생)		 1000건
(조인된 결과 레코드 건수
를 전부 정렬해야 함)

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

인덱스를 사용하지 못하고 별도로 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%';
+---------------------+---------+
| Variable name       | Value   |
+---------------------+---------+
| Sort_merge_passes   | 56      |
| Sort_range          | 0       |
| Sort_rows           | 279408  |
| Sort_scan           | 1       |
+---------------------+---------+

mysql > SELECT first_name, last_name
        FROM employees
        GROUP BY first_name, last_name;

mysql > SELECT first_name, last_name
        FROM employees
        GROUP BY first_name, last_name;

mysql > SHOW SESSION STATUS LIKE 'Sort%';
+----------------------+------------+
| Variable name        | Value      |
+----------------------+------------+
| Sort_merge_passes    | 112        |
| Sort_range           | 0          |
| Sort_rows            | 558816     |
| Sort_scan            | 2          |
+----------------------+------------+

각 상태 값은 다음과 같은 의미가 있으며, 이 값들을 이용해 지금까지 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)

참고

  • Real MySQL
profile
de_sj_awa
이것저것 관심많은 개발자.
이전 포스트

MySQL 실행 계획 : MySQL의 주요 처리 방식(1)

다음 포스트

MySQL 실행 계획 : MySQL의 주요 처리 방식(3)

0개의 댓글