Real MySQL 8.0 3 : 옵티마이저와 힌트 그리고 실행계획

09 옵티마이저와 힌트

---

Order by 처리(Using filesort)

정렬 처리 방법

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

정렬 처리 방법	실행 계획의 Extra 컬럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort" 메세지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort" 메세지가 표시됨

 먼저 옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토할 것이다. 인덱스를 이용할 수 있다면 별도의 "Filesort" 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다. 하지만 인덱스를 사용할 수 없다면 Where 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)할 것이다. 이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 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 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가 쿼리에 명시된다고 해서 작업량이 더 늘지는 않는다.
또한, 어떤 이유로 쿼리의 실행 계획이 조금 변경된다면 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 AND 100010
ORDER BY e.last_name;

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

• WHERE 절의 검색 조건("emp_no BETWEEN 100001 AND 10010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
• 드리븐 테이블(salaries)의 조인 컬럼인 emp_no 컬럼에 인덱스가 있다.

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

 아래 그림은 이 과정을 보여준다.

1. 인덱스를 이용해 "emp_no BETWEEN 100001 AND 10010" 조건을 만족하는 9건을 검색
2. 검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으면서 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 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수 밖에 없다.

DISTINCT 처리 

 단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DINTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 동일한 방식으로 처리된다. 특히 MySQL 8.0 버전부터는 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 다음의 두 쿼리는 내부적으로 같은 작업을 수행한다.

SELECT DISTINCT emp_no FROM 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 first_name, last_name FROM employees;

 SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 컬럼에 영향을 미친다. 절대로 SELECT하는 여러 컬럼 중에서 일부 컬럼만 유니크하게 조회하는 것은 아니다.

 

10 실행 계획

---

실행 계획 분석

type 컬럼

 쿼리이 실행 계획에서 type 이후의 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다. 여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지, 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다. 일반적으로 쿼리를 튜닝핳ㄹ 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 컬럼은 반드시 체크해야 할 중요한 정보다.

MySQL의 매뉴얼에서는 type 컬럼을 "조인 타입"으로 소개한다. 또한 MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리한다. 그래서 SELECT 쿼리의 테이블 개수에 관계없이 실행 계획의 type 컬럼을 "조인 타입"이라고 명시하고 있다. 하지만 type 컬럼의 값은 조인과 직접 연관 지어 생각하지 말고, 각 테이블의 접근 방법(Access type)으로 해석하면 된다.

 실행 계획의 type 컬럼에 표시될 수 있는 값은 현재 많이 사용되는 대부분의 버전에서 거의 차이 없이 다음과 같이 표시된다.

• system
• const
• eq_ref
• ref
• fulltext
• ref_or_null
• unique_subquery
• index_subquery
• ragne
• index_merge
• index
• ALL

 위의 12개 접근 방법 중에서 하단의 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다. ALL은 인덱스를사용하지 않고, 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방법을 의미한다. 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있다. 또한 index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용한다. 그러므로 실행 계획의 각 라인에 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.

 위에 표시된 각 접근 방법은 성능이 빠른 순서대로 나열된 것이며, MySQL 옵티마이저는 이러한 접근 방법과 비용을 함께 계산해서 최소의 비용이 필요한 접근 방법을 선택해 쿼리를 처리한다.

system

 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이브을 참조하는 형태의 접근방법이다. InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다.

const

 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식이다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.

 프라이머리 키의 일부만 조건으로 사용할 때는 const가 아닌 ref라고 표시되고, 프라이머리 키나 유니크 인덱스의 모든 컬럼을 동등 조건으로 WHERE 절에 명시하면 const 접근 방법을 사용한다.

실행 계획의 type 컬럼이 const인 실행 계획은 MySQL의 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다. 그래서 실행 계획의 type 컬럼 값이 "상수(const)"로 표시되는 것이다. 다음 예제 쿼리를 한 번 살펴보자.

SELECT COUNT(*)
FROM employees e1
WHERE first_name=(SELECT first_name FROM employees e2 WHERE emp_no=100001);​

실제 이 쿼리는 옵티마이저에 의해 최적화되는 시점에 다음과 같은 쿼리로 변환된다. 즉, 옵티마이저에 의해 상수화된 다음 쿼리 실행기로 전달되기 때문에 접근 방법이 const인 것이다.

SELECT COUNT(*)
FROM employees e1
WHERE first_name='Jasmink'; -- Jasminko는 사번이 100001인 사원의 first_name 값

eq_ref

 이 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 한다. 이때 두 번째 이후에 읽는 테이블의 type 컬럼에 eq_ref가 표시된다. 또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT_NULL이어야 하며, 다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 컬럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다. 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

ref

 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다. 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나다.

range

 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE"등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법인데, MySQL 서버가 가지고 있는 접근 방법 중에서 상당히 우선순위가 낮다. 얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있겠지만 range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.

index

 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방법과 같이 효율적을 인덱스의 필요한 부분만 읽는 것을 의미하는 것이 아니다.

 index 접근 방법은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔 시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이라 할 수 있다. index 접근 방법은 다음 조건 가운데(첫 번째+두 번째) 조건을 충족하거나(첫 번째+세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

• range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
• 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우)
• 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)

ALL

 우리가 흔히 알고 있는 풀 테이블 스캔을 의미하는 접근 방법이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다. 풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법이다.

 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다. InnoDB에서는 이 기능을 "리드 어헤드(Read Ahead)"라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있다. 데이터 웨어하우스(Data Warehouse)나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하게 튜닝된 쿼리)보다 더 나은 접근 방법이기도 하다. 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀스캔을 사용하지 못하게 하는 것은 아니라는 점을 기억하자.

 일반적으로 index와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다. 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.