[Real MySQL] 6장(1). 실행 계획

실행 계획이란?

DBMS의 쿼리 실행에서 같은 결과를 만들어 내는 데 한 가지 방법만 있는 것이 아니라, 아주 많은 방법이 있지만 그중에서 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다.

DBMS에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업을 옵티마이저가 담당한다.

 

EXPLAIN

MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다.

 

 

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 SQL 파스 트리를 만든다. (SQL 파싱이라고 하며, SQL 파서라는 모듈로 처리한다)
2. SQL의 파스 트리를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 이 단계가 완료되면 쿼리의 실행 계획이 만들어진다.
3. (최적화 및 실행 계획 단계이며, 옵티마이저에서 처리한다)
4. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
5. (수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어올 수 있도록 요청하고 (핸들러 요청), MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

 

옵티마이저의 종류

• 비용 기반 최적화 방법 (현재 대부분의 DBMS가 선택)
  • 쿼리를 처리하기 위한 여러 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출해, 최소 비용이 소요되는 처리 방식을 선택해 쿼리 실행.
• 규칙 기반 최적화 방법
  • 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식.

 

통계 정보

비용 기반 최적화에서 가장 중요한 것은 통계 정보이다.

MySQL 또한 다른 DBMS와 같이 비용 기반의 최적화를 사용하지만 다른 DBMS보다 통계 정보는 그리 다양하지 않다.

또한 MySQL에서 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다.

 

 

ANALYZE

하지만 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때 도 많다.

단, ANALYZE를 실행하는 동안 InnoDB 테이블은 읽기와 쓰기 모두 불가능하므로 서비스 도중이 ANALYZE을 실행하지 않는 것이 좋다.

 

실행 계획 분석

id 칼럼

• 단위 SELECT 쿼리별로 부여되는 식별자 값.
• SELECT 문장은 하나인데 여러 개의 테이블이 조인되는 경우에는 id 값이 증가하지 않고 같은 id가 부여된다.

 

select_type 칼럼

SIMPLE

• UNION 이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리

 

PRIMARY

• UNION 이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 쿼리

 

UNION

• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리

 

SUBQUERY

• FROM 절 이외에서 사용되는 서브 쿼리

 

DEPENDENT SUBQUERY

• 서브 쿼리가 바깥족 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우

 

DERIVED

• 서브 쿼리가 FROM 절에 사용된 경우
• DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성. (파생 테이블)
• MySQL은 FROM 절에 사용된 서브 쿼리를 제대로 최적화하지 못할 때가 대부분임 => 조인으로 해결하는 것이 좋음

 

table 칼럼

• 테이블 기준으로 표시되며, 별칭이 부여된 경우에는 별칭이 표시.
• <> 테이블은 임시 테이블을 의미.

 

type 칼럼

• MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미.
• 인덱스를 사용해 레코드를 읽었는지, 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 읽었는지 등을 의미.
• 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야 할 중요 정보.

 

const

• 쿼리가 PK나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식
• 다중 칼럼으로 구성된 PK나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 대는 const 타입의 접근 방법을 사용할 수 없다. (ref로 표시됨)

 

eq_ref

• 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됨.
• 조인에서 처음 읽은 테이블의 칼럼 값을, 그다음 읽어야 할 테이블의 PK나 유니크 키 칼럼의 검색 조건에 사용할 때

 

ref

• 인덱스의 종류와 관계없이 동등 조건으로 검색할 때
• ref 타입은 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. (하지만 동등 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나임)

 

const, eq_ref, ref 정리

• 세 가지 접근 방식은 모두 WHERE 조건절에 사용되는 비교 연산자는 동등 비교 연산자이어야 한다는 점.
• 세 가지 모두 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방법

 

unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방식.
• 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 접근 방법을 사용.

 

index_subquery

• IN에서 subquery가 중복된 값을 반환할 수는 있지만 중복된 값을 인덱스를 이용해 제거할 수 있을 때 index_suvquery 접근 방식이 사용됨.

 

range

• 인덱스 레인지 스캔 형태의 접근 방법
• 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미.
• <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

cf) 보통 인덱스 레인지 스캔이라고 하면 const, ref, range라는 세 가지 접근 방법을 모두 묶어서 지칭한다.

 

index_merge

• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식.

다음과 같은 이유로 그다지 효율적이지 않음.

• 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.
• AND, OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많음.
• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않음.
• index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에, 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

 

index

• 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔.
• 풀 테이블 스캔 방식과 비교했을때 비교하는 레코드 건수는 같지만, 인덱스는 일반적으로 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효율적이므로 풀 테이블 스캔보다는 빠르게 처리된다.

 

사용되는 경우

• 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (커버링 인덱스)의 경우 사용되는 방식
• 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우

개선 사항

• 인덱스 풀 스캔에서 LIMIT 조건을 사용하면 상당히 효율적인 쿼리를 만들 수 있다. (LIMIT 조건이 없거나 가져와야 할 레코드 건수가 많아지면 상당히 느려지는 방식)

 

ALL

• 풀 테이블 스캔 접근 방식으로, 가장 비효율적인 방법.

리드 어헤드

• 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공함. (한 번에 여러 페이지를 읽어서 처리할 수 있음)
• MySQL에서는 연속적으로 인접한 페이지가 연속해서 몇 번 읽히게 되면 백그라운드로 작동하는 읽기 스레드가 최대 한 번에 64개의 페이지씩 한꺼번에 디스크로부터 읽어 들이기 때문에 한 번에 페이지 하나씩 읽어 들이는 작업보다는 상당히 빠르게 레코드를 읽을 수 있다.
• 데이터웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서 잘못 튜닝된 쿼리보다 더 나은 접근 방법이 되기도 한다. (쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니다!!)

일반적으로 index, ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내 줘야 하는 웹 서비스 등과 같은 OLTP 환경에는 적합하지 않다.

 

possbile_keys

• 사용될 법했던 인덱스의 목록

 

keys

• 최종 선택된 실행 계획에서 사용하는 인덱스.
• 쿼리를 튜닝할 때는 Key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.

 

key_len

• 매우 중요한 정보 중 하나로, 쿼리를 처리하기 위해 다중 칼럼으로 만들어진 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줌.
• 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값.

 

ref

• 접근 방법이 ref이면 참조 조건 (Equal 비교 조건)으로 어떤 값이 제공됐는지 보여 준다

 

주의해서 봐야할 경우는 다음과 같다.

• ref 칼럼의 값이 func라고 표시되는 경우 참조용으로 사용되는 값을 그대로 사용한 것이 아니라, 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미.

EXPLAIN SELECT * FROM employees e, dept_emp de WHERE e.emp_no=(de.emp_no-1);

 

다음과 같은 상황에 ref 칼럼에 func가 출력된다

• 문자 집합이 일치하지 않는 두 문자열 칼럼을 조인할 때
• 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인할 때

 

따라서 되도록 조인 칼럼의 타입은 일치시키는 편이 좋다.

 

 

rows

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줌.
• 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미.

 

Extra

• 칼럼의 이름과는 달리, 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시됨.

 

const row not found

• 쿼리의 실행 계획에서 const 접근 방식으로 테이블을 읽었지만, 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 칼럼에 이 내용이 표시됨.

 

Distinct

• 쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인.

 

Impossible HAVING

• 쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때

EXPLAIN
SELECT e.emp_no, COUNT(*) AS cnt
FROM employees e
WHERE e.emp_no=10001
GROUP BY e.emp_no
HAVING e.emp_no IS NULL;

 

Impossible WHERE

• WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우
  

  EXPLAIN
  SELECT * FROM employess WHERE emp_no IS NULL;

 

Impossible WHERE noticed after reading const tables

• 쿼리의 WHERE 조건을 만족하는 레코드가 한 건도 없는 경우.

EXPLAIN
SELECT * FROM employess WHERE emp_no=0;

 

No matching row in const table

• MIN(), MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 경우.

  EXPLAIN
  SELECT MIN(dept_no), MAX(dept_no)
  FROM dept_emp WHERE dept_no='';

 

Not tables used

• FROM 절이 없는 쿼리 문장이나, FROM DUAL 형태의 쿼리 실행 계획.

  EXPLAIN SELECT 1;
  EXPLAIN SELECT 1 FROM dual;

 

Not exists

• A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리.
• 이럴 때 주로 NOT IN (Subquery) 형태나 NOT EXISTS 연산자를 주로 사용.
• 이러한 형태의 조인을 안티-조인이라고 함. 똑같은 처리를 아우터 조인(LET OUTER JOIN)을 이용해도 구현할 수 있다. (레코드의 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.)
• 아우터 조인을 이용해 안티-조인을 수행하는 쿼리의 경우.

EXPLAIN
SELECT *
FROM dept_emp de
LEFT JOIN departments d ON de.dept_no=d.dept_no
WHERE d.dept_no IS NULL;

 

Range checked for each record

• EXPLAIN SELECT * FROM employees e1, employess e2 WHERE e2.emp_no >= e1.emp_no;

 

Unique row not found.

• 두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때.

 

Using filesort

• ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만, 적절히 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한번 정렬해야 함.
• 이때 Extra 칼럼에 표시되며, 실행 계획 extra 칼럼에 using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

 

Using index

• 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 표시.
• 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스를 검색해 일치하는 레코드의 나머지 칼럼 값을 가져오기 위해 데이터 파일을 찾아서 가져오는 작업임. (최악의 경우 인덱스를 통해 검색된 결과 레코드 한 건마다 디스크를 한 번씩 읽어야 할 수 있다.)
• 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우라면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있다.

 

Using index for group-by

• GROUP BY 처리가 인덱스를 이용할 때.
• GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 칼럼을 이용해 정렬 작업을 수행하고, 다시 정렬된 결과를 그룹핑하는 형태의 고부하 작업을 필요로 한다. 하지만 GROUP BY 처리가 인덱스를 이용하면 정렬된 인덱스 칼럼을 순서대로 읽으면서 그룹핑 작업만 수행해서 레코드가 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르고 처리됨.

 

Using join buffer

• 일반적으로 빠른 쿼리 실행을 위해 조인이 되는 칼럼은 인덱스를 생성한다. 실제로 조인이 필요한 인덱스는 조인되는 양쪽 테이블 칼럼 모두가 필요한 것이 아니라, 조인에서 뒤에 읽는 테이블의 칼럼에만 필요.
• 조인이 수행될 때 드리븐 테이블(두 개의 테이블이 조인될 때, 뒤에 읽히는 테이블)의 조인 칼럼에 적절한 인덱스가 없다면 드라이빙 테이블(두 개의 테이블이 조인될 때, 먼저 읽히는 테이블)로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔해야 할 것이다. 이때 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MySQL 서버는 드라이빙 테이블에서 읽은 레코드를 임시 공간에 보관해두고 필요할 때 재사용할 수 있게 해 줌.
• 이때 읽은 레코드를 임시로 보관해두는 메모리 공간을 조인 버퍼라고 함.

 

Using temporary

• MySQL이 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용한다. (임시 테이블은 메모리에 생성될 수도, 디스크상에 생성될 수도 있음)
• 인덱스를 사용하지 못하는 GROUP BY 쿼리는 실행 계획에서 Using temporary 메시지가 표시되는 가장 대표적인 형태의 쿼리다.

 

Using where

• MySQL 엔진 레이어(스토리지 엔진 레이어가 아닌)에서 별도의 가공을 해서 필터링 작업을 처리하는 경우 Using where 코멘트가 표시됨.

---

EXPLAIN EXTENDED (Filtered 칼럼)

스토리지 엔진에서 최종적으로 사용자에게 전달되는 레코드만 가져오는 것이 아니라, 조인과 같은 여러 가지 이유로 여전히 각 스토리지 엔진에서 읽어 온 레코드를 MySQL 엔진에서 필터링하는데, 이 과정에서 버려지는 레코드가 발생할 수밖에 없다.

 

EXPLAIN EXTENED ... 명령을 통해 실행계획에 Filtered 칼럼을 추가할 수 있다.

 

Filtered

• MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지의 비율이 표시된다.
• 예를 들어 20%의 경우, 전체 100건의 레코드를 읽었을 경우, MySQL 엔진에 의해 필터링되고 20건만 남았다는 것을 의미.

 

EXPLAIN PARTITIONS

EXPLAN PARTIIONS ... 명령으로 파티션 테이블의 실행 계획 정보를 자세히 확인할 수 있다.

---

실행계획(2) 에 대한 정리는 다음 글에서 이어집니다!

[Real MySQL 정리] 6장(2) 실행 계획 - MySQL의 주요 처리 방식