책너두 (Real MySQL 8.0 1권) 28일차 (~304p)

요약

• 이번 장은 개인적으로 가장 궁금한 점을 해소할 수 있었다.
  • 실행 계획을 보면서 최대한 쿼리 최적화하는 실습을 한 적이 있었는데, 특히 드라이빙 테이블이 선정되는 조건을 제대로 이해하지 못했었는데, 책을 읽으면서 정리가 되어 좋았다.
• 정렬 처리 방법을 3가지 방식으로 옵티마이저가 처리함.
  • 인덱스를 이용한 정렬
  • 조인의 드라이빙 테이블만 정렬
  • 조인 결과를 임시 테이블로 저장 후 정렬
    • 조건 절에 쓰인 칼럼과 ORDER BY 에 쓰인 칼럼이 어떤 테이블 (드라이빙 or 드리븐)에 속해있는지에 따라 드라이빙 테이블이 결정되고, 위 정렬 방식중 한가지가 선택됨.
• 정렬 처리 방법에 대한 성능에 대한 비교를 생각해 볼 수 있었음.
  • 쿼리에 있는 LIMIT 은 스트리밍 방식이냐, 버퍼링 방식이냐에 따라 우리가 원하는 대로 동작하지 않을 수 있음.

메모

정렬 처리 방법

• 쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리됨.
• 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어짐.
  • 인덱스를 사용한 정렬 → 별도 메시지 표기 없음.
  • 조인에서 드라이빙 테이블만 정렬 → “Using filesort” 메시지 표시됨
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 → “Using templorary; Using filesort” 메시지 표시됨.
• 먼저, 옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토함
  • 인덱스를 이용할 수 있다면 별도의 “Filesort” 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다.
  • 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리한다.
  • 이때, MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택함.
    • 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
    • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
      • 일반적으로, 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음, 조인을 수행하는 방법이 효율적임.

인덱스를 이용한 정렬

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

mysql> 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;

// 인덱스를 사용하면 자동으로 정렬이 된다고 해서 일부로 ORDER BY emp_no를 제거하는 것은 좋지 않은 선택임.
mysql> SELECT * 
                FROM employees e, salaries s
                WHERE s.emp_no=e.emp_no
                    AND e.emp_no BETWEEN 100002 AND 100020

• 실제 위 쿼리는 동일한 결과를 반환함.

• 위 그림과 같이 인덱스(프라이머리 키)를 이용하여 데이터를 읽고, 그 다음에 salaries 테이블을 조인했기 때문에 ORDER BY 를 하지 않아도 동일한 결과를 반환함.

ORDER BY 절을 포함한다고 MySQL 서버가 별도로 정렬 작업을 불필요하게 더 수행하지 않음. 또, 어떤 이유로 쿼리의 실행 계획이 조금 변경된다면 ORDER BY가 명시되지 않은 쿼리는 결과를 기대했던 순서로 가져오지 못해서 애플리케이션의 버그로 연결될 수 있음. 하지만 ORDER BY 절을 명시해두면 성능상의 손해가 없음은 물론이고 이런 예외 상황에서도 버그로 연결되지 않음.

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

조인의 드라이빙 테이블만 정렬

• 일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고 레코드 하나하나의 크기도 늘어남.
• 그래서 조인이 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 됨.
  • 이 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 함.

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

• 위 그림은 위 쿼리의 과정을 보여줌
  1. 인덱스를 이용해 “emp_no BETWEEN 100001 AND 100010” 조건을 만족하는 9 건을 검색함.
  2. 검색 결과를 last_name 칼럼으로 정렬을 수행한다. (Filesort)
  3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행하여 86건의 최종 결과를 가져온다.

임시 테이블을 이용한 정렬

• 쿼리가 여러 테이블을 조인하지 않고 ,하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않음.
• 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면, 임시 테이블이 필요할 수도 있음.
• 위의 “조인의 드라이빙 테이블만 정렬” 의 경우, 2개 이상의 테이블이 조인되면서 정렬이 실행되지만, 임시 테이블을 사용하지는 않음.
  • 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거침.
  • 이 방법은 정렬의 3가지 방법 가운데 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법임.

mysql> 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 절의 칼럼만 제외하고 같은 쿼리임.
• 이 쿼리도 “드라이빙 테이블만 정렬” 과 같은 이유로 employees 테이블이 드라이빙 테이블로 사용됨
• 하지만 이번 쿼리는 ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 칼럼임.
  • 즉, 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로, 이 쿼리는 조인된 데이터를 가지고 정렬할 수밖에 없음.

• 위 그림은 위 쿼리의 처리 절차를 보여줌

정렬 처리 방법의 성능 비교

• 주로 웹 서비스용 쿼리에서는 ORDER BY와 함께, LIMIT이 거의 필수로 사용되는 경향이 있음.
• 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 함.
• 그런데 ORDER BY, GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없음.
  • 우선, 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 비로소 LIMIT으로 건수를 제한할 수 있음.
  • WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도, 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생함.
• 쿼리에서 인덱스를 사용하지 못하는 정렬이나, 그루핑 작업이 왜 느리게 작동할 수밖에 없는지 한번 살펴보자.
  • 이를 위해, 쿼리가 처리되는 방법을 “스트리밍 처리”와 “버퍼링 처리” 2가지 방식으로 구분해 보자.

스트리밍 방식

• 그림과 같이 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식이다.
• 이 방식으로 쿼리를 처리할 경우, 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.
  • 물론, 가장 미자믹 레코드는 언제 받을지 알 수 없지만, 이는 별로 안중요함.
• 위와 같이 쿼리가 스트리밍 방식으로 처리될 수 있다면 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있음.
• 웹 서비스 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번쨰 레코드를 전달받게 되기까지의 응답 시간이 중요함.
• 스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해줌.
• 또한, 스트리밍 방식으로 처리되는 쿼리는 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있음.
• 매우 큰 테이블을 아무 조건 없이 SELECT만 해보면 첫 번쨰 레코드는 아주 빨리 가져온다는 것을 알 수 있음.
  • 이는 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍 되기 떄문임.
  • 이 쿼리에 LIMIT 조건을 추가하면 전체적으로 가져오는 레코드 건수가 줄어들기 때문에 마지막 레코드를 가져오기까지의 시간을 상당히 줄일 수 있음.

버퍼링 방식

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

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

스트리밍 처리는 어떤 클라이언트 도구나 API를 사용하느냐에 따라 그 방식에 차이가 있을 수 있음.
ex) JDBC 라이브러리는 MySQL 서버로부터 받는 레코드를 일단 내부 버퍼에 모두 담아둠. 여기서, MySQL 서버는 스트리밍 방식으로 처리해서 반환하지만 클라이언트의 JDBC 라이브러리가 버퍼링 하는 것임. → 그 이유는 JDBC 라이브러리가 전체 처리(Throughput) 시간이 짧고, MySQL 서버와의 통신 횟수가 적어서 자원 소모가 줄어들기 때문임.
아주 대량의 데이터를 가져와야 한다면 MySQL 서버와 JDBC 간의 전송방식을 스트리밍 방식으로 변경할 수 있음.

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

정렬 관련 상태 변수

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

mysql> SHOW STATUS LIKE 'Sort%'

• Sort_merge_passes : 멀티 머지 처리 횟수
• Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
• Sort_rows : 지금까지 정렬한 전체 레코드 건수
• Sort_scan : 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수.