[Real MySQL] 6. 실행 계획

실행 계획?

최적의 방법을 찾아내기 위해

수많은 데이터에서 내가 원하는 데이터를 뽑아내기 위한 방법은 정말 다양할 수 있다.
그렇기에 우리는 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다.

여행 계획을 세부적으로 따져가면서 세우듯이, DBMS에서도 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하고, 최적의 실행계획을 수립한다.
옵티마이저가 이런 작업을 담당한다.

MySQL에서는 EXPLAIN이라는 키워드로 실행 계획을 확인할 수 있다.

따라서 통계 정보는 실행 계획에서 상당히 중요한데, 레코드 건수가 많지 않으면 통계 정보가 부정확하여 엉뚱한 실행 계획이 도출될 수 있음을 늘 염두에 두고 있어야 한다.
필요에 따라 ANALYZE 명령어로 통계정보를 강제적으로 갱신해야 할 수도 있다.

---

실행 계획 분석

EXPLAIN 명령어로 내가 실행할 쿼리의 실행계획을 보면, 테이블 형태로 메타 데이터들이 조회되는 것을 볼 수 있다.
따라서 각각의 칼럼들의 의미를 아는 것이 중요한데, 책에서는 거의 모든 내용을 소개하지만 이 글에서는 필요한 몇 가지만 짚으려 한다.

id 칼럼

실행계획의 id 칼럼은 각 SELECT 쿼리의 식별자 값이다.
만약 하나의 SELECT 쿼리에서 여러 개의 테이블이 조인된다면 모두 같은 id값이 부여된다.

select_type 칼럼

각 SELECT 쿼리가 어떤 타입인지를 보여준다.

• SIMPLE : UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리다. 쿼리가 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE 인 쿼리는 단 하나만 존재한다.
• PRIMARY : UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽의 단위 쿼리를 뜻한다. SIMPLE과 마찬가지로 단 하나만 존재한다.
• UNION : UNION 결합 시 첫 번째를 제외한 두 번째 이후의 단위 SELECT 쿼리다.
• DEPENDENT UNION : UNION 시 내부 쿼리가 외부의 값을 참조해서 처리될 때 표시된다.
• SUBQUERY : FROM절 이외에서 사용되는 서브 쿼리만을 의미한다.
• DEPENDENT SUBQUERY : 외부 쿼리에 의존적인 서브 쿼리를 의미한다.
  • select_type에 DEPENDENT 키워드가 있으면 서브 쿼리가 외부 쿼리에 의존적이므로 비효율적인 경우가 많다.
• DERIVED : 서브 쿼리가 FROM절에 사용된 경우다. 이는 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
  • 따라서 성능 상 비효율적일 가능성이 높다. 서브 쿼리 대신 조인으로 해결할 수 있다면 조인으로 해결해야 한다.
• UNCACHEABLE SUBQUERY : 원래의 서브 쿼리는 여러 번 읽힐 수 있기 때문에 내부적으로 캐싱이 된다. 하지만 여러가지 이유로 인해 캐싱될 수 없는 상황을 말한다.

type 칼럼

각 테이블에 접근하는 방식이다. 쿼리 튜닝 시 인덱스 사용 여부 등을 체크해야 하므로 필수적으로 확인해야 할 중요한 칼럼들 중 하나이다.

• const : PK나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있고, 이름에서 알 수 있듯이 반드시 1건을 반환하는 쿼리다.
• eq_ref : 여러 테이블 조인 시, 조인에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽을 테이블의 PK나 유니크 키 칼럼의 검색 조건에 사용하는 경우다.
• ref : 동등 조건(Equal)으로 검색하는 경우이다.
  • 위 세 가지 접근 방법 모두 쿼리 튜닝 시 크게 신경쓰지 않아도 된다.
• fulltext : MySQL의 전문 검색 인덱스를 사용해 레코드를 읽는 방법이다.
• unique_subquery : WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리이다. unique라는 이름에서 알 수 있듯이 IN절 안의 서브 쿼리에서 중복되지 않은 결과를 반환할 때 쓰인다.
• index_subquery : IN (subquery)의 서브 쿼리에서 중복된 값이 나올 수 있지만 인덱스로 중복을 제거할 수 있을 때 사용된다.
• range : 인덱스 레인지 스캔 형태의 방법이다.
  • 통상적으로 인덱스 레인지 스캔은 const, ref, range 이 세 가지 접근 방법을 의미한다.
• index_merge : 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 병합하는 방식이다. 그닥 효율적이지는 않다.
• index : 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
  • 절대 인덱스를 효율적으로 사용하는 방식이 아님에 주의해야 한다.
• all : 풀 테이블 스캔을 의미한다. 가장 비효율적인 방식이다.
  • 하지만 InnoDB 스토리지 엔진에서는 대량의 디스크 I/O 유발을 막기 위해 리드 어헤드라는 기능으로 한꺼번에 많은 페이지를 읽어들일 수 있다. 잘못 튜닝된 쿼리보다 이 방법이 나을 수도 있다.
  • 보통의 빠른 응답을 사용자에게 돌려줘야 하는 OLTP 웹 서비스 환경에서는 index와 ALL 방법은 적합하지 않다.

possible_keys 칼럼

사용될 법했던 인덱스의 목록이다. 말 그대로 후보였던 것들이다. 사용한 인덱스가 아님에 주의하자.

key 칼럼

최종 선택된 실행 계획에서 사용하는 인덱스이다. 인덱스를 전혀 사용하지 못하면 NULL로 표기된다.

key_len 칼럼

쿼리를 처리하기 위해 다중 칼럼 인덱스에서 몇 개 칼럼까지 사용했는지 바이트 단위로 알려주는 값이다.
예를 들어 CHAR(4) 칼럼과 INTEGER 칼럼의 복합 인덱스에서 앞쪽 CHAR(4) 칼럼만 유효하게 사용했다면, 총 인덱스 키는 16바이트지만 key_len에는 12바이트가 표시된다. (utf8 기준 문자 1개 고정값 3바이트)

Extra 칼럼

• Distinct : 두 테이블 조인 시 중복된 값을 제거했음을 알려준다.
• impossible HAVING : 쿼리에서 HAVING절의 조건을 만족하는 레코드가 없는 경우이다.
• impossible WHERE : 쿼리에서 WHERE절 조건이 항상 false인 경우이다.
• Not exists : 아우터 조인을 이용해 안티 조인을 처리하는 경우다.
  • NOT IN (subquery) 나 NOT EXISTS를 사용하는 조인을 안티 조인이라 한다.
  • A 테이블에는 존재하지만 B 테이블에 없는 값을 조회할 경우에 안티 조인을 사용하는데, 레코드 건수가 많을 때는 안티 조인을 아우터 조인으로 처리하면 빠른 성능을 낼 수 있다.
• Using filesort : ORDER BY 처리가 인덱스를 사용하지 못할 때이다.
  • 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행한다.
  • 많은 부하를 일으키므로 쿼리 튜닝이 필요하다.
• Using index(커버링 인덱스) : 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우이다.
• Using index for group-by : GROUP BY 처리가 인덱스를 이용하면 정렬된 인덱스 칼럼을 읽으면서 그룹핑 작업만 수행한다.
  • 루스 인덱스 스캔 방식이다.
• Using join buffer : 조인 수행 시 드리븐 테이블에 인덱스가 없다면, 드라이빙 테이블에서 읽은 레코드를 임시로 버퍼 공간에 보관하여 조인을 수행한다.
  • join_buffer_size라는 시스템 변수로 버퍼 크기를 설정 가능하다. 보통 1MB 정도면 충분하다.
• Using temporary : 중간 결과를 담아 두기 위해 임시 테이블을 사용했다. 이 테이블이 메모리에 생성됐었는지 디스크에 생성됐었는지는 실행 계획으로는 알 수가 없다.
• Using where : MySQL 엔진이 스토리지 엔진에서 받은 데이터에 별도의 가공을 한 경우 표시된다.
  • 작업 범위 제한 조건은 스토리지 엔진에서 처리되지만 체크 조건은 MySQL 엔진에서 처리된다.
    • 하지만 MySQL 5.1 InnoDB 버전 부터는 체크 조건도 스토리지 엔진으로 전달되는데, 이를 Condition push down이라 한다.
• Using where with pushed condition : 바로 위에서 나온 Condition push down이 적용됐음을 의미한다.

EXPLAIN EXTENDED (Filtered 칼럼)

EXPLAIN 명령 뒤에 EXTENED를 붙이면 filtered라는 새로운 칼럼이 표시되는데, MySQL 엔진에 의해 필터링된 후 남은 레코드의 비율이 표시된다.
정확한 값은 아니고 통계 정보를 기반으로 계산된 값이다.

EXPLAIN PARTITIONS (Partitions 칼럼)

EXPLAIN 명령 뒤에 PARTITIONS를 붙이면 partitions라는 새로운 칼럼이 표시되는데, 파티셔닝이 되어 있는 테이블에서 어떤 파티션을 읽었는지를 알려준다.
파티션이 여러 개인 테이블에서 쿼리 수행에 꼭 필요한 파티션을 골라내는 과정을 파티션 프루닝이라고 한다.

---

MySQL의 주요 처리 방식

MySQL의 실행 계획을 보면서 동시에 MySQL이 주요하게 처리하는 단위 작업들을 알아두는 것이 중요하다.

아래의 풀 테이블 스캔을 제외하면 모두 스토리지 엔진이 아니라 MySQL 엔진에서 처리하는 방식들인데, 이는 모두 성능 저하를 일으키기에 잘 알아두어야 한다.
책에는 많은 방식들이 자세하게 설명되어 있지만 여기서는 ORDER BY, GROUP BY 위주로만 간단하게 남기려 한다.

풀 테이블 스캔

인덱스를 사용하지 않고 데이터를 처음부터 끝까지 읽는 것이다.
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 리드 어헤드 작업을 수행하는데, 미리 여러 페이지를 읽어 InnoDB 버퍼 풀에 가져다 놓는 것을 의미한다.

ORDER BY 처리 (Using filesort)

MySQL은 정렬을 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이를 소트 버퍼라고 한다.

레코드의 수가 적다면 상관 없겠지만, 수가 많다면 메모리의 소트 버퍼에서 그 크기만큼만 정렬을 수행하고 그 결과를 임시로 디스크에 저장한다.
이를 반복한 후에 마지막에는 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다. 이를 멀티 머지라고 표현한다.

멀티 머지는 방법에서 보듯이 다수의 디스크의 쓰기와 읽기 작업을 유발한다.
인덱스를 사용하지 않는 ORDER BY 처리가 느릴 수 밖에 없는 이유다.

ORDER BY는 다음의 세 가지 방식 중 하나로 처리된다. 보통 아래로 갈수록 처리가 느려진다.

• 인덱스 사용한 정렬 : 별도 표기 없음
  • 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY 순서대로 생성된 인덱스가 있어야 한다.
• 드라이빙 테이블만 정렬 : Using filesort
  • 조인에서 읽히는 첫 번째 테이블의 칼럼만으로 ORDER BY절이 작성되어야 한다.
• 조인 결과를 임시 테이블로 저장한 후, 임시 테이블에서 정렬 : Using temporary; Using filesort

쿼리가 처리되는 방식은 크게 두 가지가 있는데, 스트리밍 방식과 버퍼링 방식이다.
스트리밍 방식은 서버에서 처리할 데이터 수와 관계없이 조건에 일치하는 레코드를 바로바로 클라이언트로 전송해주는 방식이고, 버퍼링 방식은 ORDER BY나 GROUP BY 때문에 스트리밍할 수 없고, WHERE 조건에 맞는 결과를 가져온 후 가공하여 클라이언트로 돌려주는 방식이다.

인덱스를 사용한 정렬은 스트리밍 방식으로 처리할 수 있기 때문에 빠르며, 나머지 두 방법은 버퍼링 방식으로 처리해야 하기 때문에 다소 느리다.

GROUP BY 처리

GROUP BY도 위에서 이야기했듯이 스트리밍 처리를 할 수 없게 하는 요소 중 하나다.
GROUP BY에 사용된 조건은 인덱스를 사용해서 처리할 수 없으므로 HAVING절을 튜닝하려고 인덱스를 생성하거나 할 필요는 없다.

ORDER BY와 마찬가지로 드라이빙 테이블에 속한 칼럼만을 이용해 그룹핑할 때 해당 칼럼으로 인덱스가 있다면 그 인덱스를 차례로 읽으면서 그룹핑 작업을 수행하고 조인을 처리한다.
이는 인덱스 스캔을 이용하는 방법이기에 Extra 칼럼에 특별한 정보가 표시되지 않는다.

Using index for group-by 가 표시되는 경우는 루스 인덱스 스캔을 이용하는 방식이다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.