[MySQL] 옵티마이저와 힌트 2

옵티마이저와 힌트 1

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 "싱글 패스(Single-pass)"와 "투 패스(Tow-pass)" 두 가지 정렬 모드로 나눌 수 있다.

최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용하지만, 아래의 경우에는 싱글 패스 정렬 방식을 사용하지 못하고, 투 패스 정렬 방식을 사용한다.

• 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함할 때

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준(ORDER BY) 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 방식이다.

mysql> SELECT emp_no, first_name, last_name
              FROM employees
              ORDER BY first_name;

위 그림에서 알 수 있듯이, 처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한 뒤, 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨주는 과정을 볼 수 있다.

투 패스 정렬 방식

정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT한 칼럼을 가져오는 정렬 방식으로, 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식이다.

mysql> SELECT emp_no, first_name, last_name
              FROM employees
              ORDER BY first_name;

위 그림을 살펴보면, 투 패스 정렬 방식은 처음 employees 테이블을 읽을 때는 정렬에 필요한 first_name 칼럼과 프라이머리 키인 emp_no만 읽어서 정렬을 수행하고, employees 테이블을 한 번 더 읽어서 last_name을 가져온 뒤, 최종적으로 그 결과를 클라이언트 쪽으로 넘기는 과정을 확인할 수 있다.

정렬 처리 방법

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

옵티마이저가 인덱스를 이용할 수 있다면 별도의 "Filesort" 과정 없이 인덱스를 순서대로 읽어서 결과를 반환하지만, 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)한다.

이때 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 아래 2가지 방법 중 하나를 선택한다.

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

보통 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다. 그래서 두 번째 방법보다는 첫 번째 방법이 더 효율적으로 처리된다.

인덱스를 이용한 정렬

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

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

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;

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

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

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

이 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 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 절의 검색 조건("emp_no BETWEEN 100002 AND 100010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
• 드리븐 테이블(salaries)의 조건 칼럼인 emp_no 칼럼에 인덱스가 있다.

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

1. 인덱스를 이용해 "emp_no BETWEEN 100002 AND 100010" 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 칼럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 최종 결과를 가져옴

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 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 절의 정렬 기준 칼럼은 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 칼럼이다. 때문에 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수 밖에 없다.

요약

• 정렬 알고리즘
  • 싱글 패스 정렬 방식
    • 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 방식
    • 최신에 주로 사용됨
  • 투 패스 정렬 방식
    • 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT한 칼럼을 가져오는 정렬방식
    • 테이블을 두 번 읽어야 하기 때문에 주로 사용되지 않는다.
• 정렬 처리 방법
  • 인덱스를 이용한 방법
    • 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
    • WHERE 절에 첫 번째로 읽히는 테이블의 칼럼에 대한 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
  • 조인의 드라이빙 테이블만 정렬 (조인 전 정렬)
    • 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 한다.(그렇지 않으면 임시 테이블을 이용한 정렬을 사용한다.)
    • ORDER BY 절의 컬럼이 드라이빙 테이블에 포함된 칼럼인 경우, 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 드리븐 테이블을 조인한다.
  • 임시 테이블을 이용한 방법 (조인 후 정렬)
    • ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블의 칼럼인 경우 사용된다.
    • 조인의 드라이빙 테이블만 정령 방식과는 다르게, 정렬이 수행되기 전에 반드시 조인 테이블을 읽어야 한다.
    • 정렬해야 할 레코드 건수가 가장 많이 때문에 가장 느리다.

Reference
백은빈, 이성욱. 『Real MySQL 8.0』. 위키북스, 2022