MySQL - 실행 계획 분석

학습할 것

• Mysql Explain
• 사용방법
• 주요 실행계획 분석 항목
  • select_type
  • type
  • extra

---

MySQL Explain

MySQL 튜닝에서 가장 중요한 것은 쿼리와 스키마 최적화이다. 스키마 설계는 한번 진행되면 그 테이블을 사용하는 모든 쿼리에 영향을 주기 때문에 좀처럼 변경하기 힘들지만 쿼리는 해당 쿼리만 수정하면 되므로 변경하기 쉽다. Slow Query를 없애는 것은 성능을 향상 시키기 위한 매우 중요한 수단이므로 어플리케이션의 성능을 향상 시키고 싶다면 우선적으로 쿼리의 튜닝을 검토해봐야 한다.

성능 진단의 가장 첫걸음은 실행한 SQL이 DB에서 어떻게 처리되는 지를 파악하는 것이다. 사람이 이해할 수 있는 언어로 구성된 SQL을 실행하도록 DB에 명령을 내리면 DB는 내부적으로 SQL 파싱 (문법 체크 및 DB에서 실행하기 위한 형태로 변환하는 단계)을 하고 옵티마이징 (데이터를 찾는 가장 빠른 방법을 찾아내는 단계)을 거친 후 실제로 데이터를 찾는다. 사용자가 느끼기에는 SQL만으로 모든 조작이 이루어지는 것처럼 보이나 실제로는 조금이라도 빠르게 데이터를 찾아내기 위해 DB 내부적으로는 의외로 많은 단계를 거친다.

MySQL Explain 이란 DB가 테이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것이다. MySQL Explain을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.

사용 방법

EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...

MySQL Explain 결과의 각 항목 별 의미

구분	설명
id	select 아이디로 SELECT를 구분하는 번호
table	참조하는 테이블
select_type	select에 대한 타입
type	조인 혹은 조회 타입
possible_keys	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
key	실제로 사용할 인덱스
key_len	실제로 사용할 인덱스의 길이
ref	Key 안의 인덱스와 비교하는 컬럼(상수)
rows	쿼리 실행 시 조사하는 행 수립
extra	추가 정보

id

행이 어떤 SELECT 구문을 나타내는 지를 알려주는 것으로 구문에 서브 쿼리나 UNION이 없다면 SELECT는 하나밖에 없기 때문에 모든 행에 대해 1이란 값이 부여되지만 이외의 경우에는 원 구문에서 순서에 따라 각 SELECT 구문들에 순차적으로 번호가 부여된다.

table

행이 어떤 테이블에 접근하는 지를 보여주는 것으로 대부분의 경우 테이블 이름이나 SQL에서 지정된 별명 같은 값을 나타낸다.

select_type

각 단위 select 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

구분	설명
SIMPLE	단순 SELECT (Union 이나 Sub Query 가 없는 SELECT 문)
PRIMARY	Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리
UNION	UNION 쿼리에서 Primary를 제외한 나머지 SELECT
DEPENDENT_UNION	UNION 과 동일하나, 외부쿼리에 의존적임 (값을 공급 받음)
UNION_RESULT	UNION 쿼리의 결과물
SUBQUERY	Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문 서브 쿼리는 사용되는 위치에 따라 각각 다른 이름을 지니고 있다. - 중첩된 쿼리(Nested Query) : Select 되는 칼럼에서 사용된 경우 - 서브 쿼리(Sub Query) : Where절에서 사용된 경우 - 파생 테이블(Derived) : FROM절에 사용된 서브 쿼리. 파생 테이블이라고 하며, RDBMS 전체적으로 인라인 뷰(Inline View) 또는 서브 셀렉트라고 부르기도 한다.
DEPENDENT_SUBQUERY	Sub Query 와 동일하나, 외곽쿼리에 의존적임 (값을 공급 받음)
DERIVED	SELECT로 추출된 테이블 (FROM 절 에서의 서브쿼리 또는 Inline View)
UNCACHEABLE SUBQUERY	Sub Query와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동이라더라도 Cache된 결과를 사용할 수 없음
UNCACHEABLE UNION	UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리

select_type 분석 포인트

DEPENDENT_SUBQUERY 예제

• DEPENDENT_UNION와 DEPENDENT_SUBQUERY는 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브 쿼리)가 실행되야 하므로 (DEPENDENT 키워드가 없는) 일반 서브 쿼리보다는 처리 속도가 느릴 때가 많다.
• DERIVED는 select 쿼리의 실행 결과를 메모리나 디스크 임시 테이블을 생성하는 것. 안타깝게도 MySQL은 FROM 절에 사용된 서브 쿼리를 제대로 최적화하지 못할 때가 대부분이다. 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
  • MySQL 6.0 이상 버전부터는 FROM 절의 서브 쿼리에 대한 최적화 부분이 많이 개선될 것으로 알려졌으며 다들 많이 기대하는 상태다.
• 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 바꿔주는 것이 좋다.
  • 쿼리 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 한다. 다른 방법이 없어서 서브 쿼리를 사용하는 것은 피할 수 없다. 하지만 조인으로 해결할 수 있는 경우라면 서브 쿼리보다는 조인을 사용할 것을 강력히 권장한다.
  • 실제 현업에서는 단계적으로 기능을 추가하는 방식으로 개발이 진행되기 때문에 서브 쿼리 형태로 작성되는 것이 많다. 이런 방식은 쿼리 성능상 좋지 않다. 반드시 마지막에는 서브 쿼리를 조인으로 풀어서 고쳐 쓰는 습관을 들이자.

 

type

인덱스를 사용해 레코드를 읽었는지 아니면 테이블 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다.

• 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type칼럼은 반드시 체크해야할 중요한 정보다.
• 아래 순서는 접근방식이 빠른 순서대로 나열된 것(MySQL에서 부여한 우선순위임) 이다.

구분	설명
system	테이블에 단 한개의 데이터만 있는 경우
const	SELECT에서 Primary Key 혹은 Unique Key를 살수로 조회하는 경우 반드시 1건의 레코드만 반환
eq_ref	조인을 할 때 Primary Key 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시 조인에서 처음 읽은 테이블의 칼럼 값을, 그다음 읽어야 할 테이블의 프리이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때 eq_ref라고 한다
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우
fulltext	MySQL의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미 "MATCH... AGAINST ..." 구문 사용해서 실행
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우
unique_subquery	다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01); 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음
index_subquery	unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02); 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
index_merge	두 개의 인덱스가 병합되어 검색이 이루어지는 경우
index	인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함
all	테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함

type 분석 포인트

• ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등한 조건으로만 비교되므로 매우 빠른 조회 방법의 하나다.
• const, eq_ref, ref 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상 문제를 일으키지 않는 접근 방법이다. 쿼리를 튜닝할 때도 이 세 가지 접근 방법에 대해서는 크게 신경 쓰지 않고 넘어가도 무방하다.
• 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 빨리 처리되는 경우가 더 많았다. 전문 검색 쿼리를 사용할 때는 각 조건별로 성능을 확인해 보는 편이 좋다.
• range가 접근 방법 순서상으로 우선순위가 낮아보이지만, 이 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법마 사용해도 어느 정도 성능이 보장된다고 볼 수 있다.
• 인덱스 레인지 스캔이라고 하면 const, ref, range 라는 세가지 접근 방법을 모두 묶어서 지칭하는 것. 
• "인덱스를 효율적으로 사용한다" 또는 "범위 제한 조건으로 인덱스를 사용한다"는 표현 모두 이 세가 가지 접근 방법을 의미한다.
• index_merge 는 이름만큼 그렇게 효율적으로 작동하는 것 같지 않았다. 이유는 아래와 같다.
  • 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.
  • AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.
  • index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 필요하다.
• index 접근 방식은 range 접근 방식과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것을 의미하는 것이 아니라는 접을 잊지 말자.
• 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것이 아니라는 점을 기억하자.
  • InnoDB도 풀 테이블 스캔, 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 기능을 제공한다. 이 기능을 "리드 어헤드(Read Ahead)" 라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있다. DW나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하도록 튜닝된 쿼리)보다 더 나은 접은 방법이 되기도 한다.
  • 일반적으로 index와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내줘야 하는 웹 서비스 등과 같은 OLTP환경에서는 적합하지 않다.

possible_keys

쿼리에서 접근하는 컬럼 들과 사용된 비교 연산자들을 바탕으로 어떤 인덱스를 사용할 수 있는 지를 표시해준다.

 

key

테이블에 접근하는 방법을 최적화 하기 위해 어떤 인덱스를 사용하기로 결정했는 지를 나타낸다.

• 쿼리를 튜닝할 때는 Key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.

 

key_len

MySQL이 인덱스에 얼마나 많은 바이트를 사용하고 있는 지를 보여준다. MySQL에서 인덱스에 있는 컬럼들 중 일부만 사용한다면 이 값을 통해 어떤 컬럼들이 사용되는 지를 계산할 수 있다.

• 사용자가 쉽게 무시하는 정보지만 사실은 매우 중요한 정보 중 하나다.
• 쿼리를 처리 하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 우리에게 알려 준다.

Key_len 값 표시 로직

ref

키 컬럼에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 사용되었는 지를 나타낸다.

rows

원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할 지에 대한 예측값을 의미한다.

extra

쿼리의 실행 계획에서 성능에 관련된 중요한 내용으로 일반적으로 2~3개씩 같이 표시된다.

종류가 다양하므로 성능에 영향이 있는 주요한 타입만 나열한다.

 

구분	설명
using index	커버링 인덱스라고 하며 인덱스 자료 구조를 이용해서 데이터를 추출
using where	where 조건으로 데이터를 추출. type이 ALL 혹은 Indx 타입과 함께 표현되면 성능이 좋지 않다는 의미
using filesort	데이터 정렬이 필요한 경우로 메모리 혹은 디스크상에서의 정렬을 모두 포함. 결과 데이터가 많은 경우 성능에 직접적인 영향을 줌
using temporary	쿼리 처리 시 내부적으로 temporary table이 사용되는 경우를 의미함
Not exists	이 쿼리를 NOT EXISTS 형태의 쿼리로 변환해서 처리했음을 의미하는 것이 아니라 MySQL이 내부적으로 어떤 최적화를 했는데 그 최적화의 이름이 "Not exists"인 것이다. Extra 칼럼의 Not exists와 SQL의 NOT EXISTS 연산자를 혼동하지 않도록 주의하자.

 

extra 분석 포인트

• MySQL Explain 상 일반적으로 데이터가 많은 경우 Using Filesort 와 Using Temporary 상태는 좋지 않으며 쿼리 튜닝 후 모니터링이 필요하다.
• Not exists 활용방안
  • 개발하다 보면 A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리가 자주 사용된다. 이럴 때는 주로 NOT IN(subquery) 형태나 NOT EXISTS 연산자를 주로 사용한다. 이러한 형태의 조인을 안티-조인(Anti-JOIN)이라고 한다. 똑같은 처리를 아우터 조인(LEFT OUTER JOIN)으로 이용해도 구현할 수 있다.
  • 일반적으로 안티-조인으로 처리해야 하지만 레코드의 건수가 많을 때는 NOT IN(subquery) 형태나 NOT EXISTS 연산자보다는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.

Not exists 형태로 튜닝하는 사례

• Using filesort
  • Using filesort 는 ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한 번 정렬해야 한다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 칼럼에는 "Using filesort" 코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다.
  • Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
• Using index

인덱스 칼럼 아닌 칼럼 조회하는 쿼리

인덱스 커버링이 되지 않는 이유 : 각 레코드의 나머지 데이터를 가져오기 위해 최대 5번 디스크 읽기해야 함.

인덱스 칼럼만 조회하면서 Using Index가 되었다.

 

• 위와 같이 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우라면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있다.
• first_name만 인덱스를 만들어도 "커버링 인덱스"가 가능한 방법은?
  • select emp_no, first_name
    from employees where first_name between 'Babette' and 'Gad'
  • first_name말로도 emp_no을 가져와야 한다. 하지만 emp_no는 employees 테이블의 프라이머리 키이기 때문에 이미 인덱스에 포함돼 있어 데이터 파일을 읽지 않아도 된다. 즉, InnoDB의 보조 인덱스에는 데이터 레코드를 찾아가기 위한 주소로 사용하기 위해 프라이머리 키를 저장해두는 것이지만, 더불어 추가 칼럼 하나 더 가지는 인덱스의 효과를 동시에 얻을 수 있게 되는 것이다.
  • 그러나 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 더 위험한 상황을 초래될 수 있다. 너무 과도하게 인덱스의 칼럼이 많아지면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있기 때문이다. 너무 커버링 인덱스 위주로 인덱스를 생성하지는 않도록 주의하자.

emp_no이 프라이머리키, first_name이 인덱스 칼럼 

 

• Using temporary
  • 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용한다. 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다.
  • 임시테이블 종류
    • FROM절에 사용된 서브 쿼리 (Derived table)
    • COUNT(DISTINCT column1)를 포함하는 쿼리
    • UNION이나 UNION ALL이 사용된 쿼리 항상 임시 테이블 사용하여 병합된 결과
    • 인덱스를 사용하지 못하는 정렬 작업
• Using where
  • MySQL 5.1부터는 실행 계획에 Filtered 칼럼이 함께 표시되므로 쉽게 성능상 이슈가 있는지 없는지 알아낼 수 있다.
  • Filtered 칼럼
    • 조인과 같은 여러 가지 이유로 여전히 각 스토리지 엔진에서 읽어 온 레코드를 MySQL 엔진에서 필터링하는데, 이 과정에서 버려지는 레코드는 발생할 수밖에 없다. 이런 이유들 때문에 Filtered 칼럼이 필터링을 얼마나 효율적으로 실행됐는지를 사용자에게 알려주기 위해 추가되었다.(5.1.12 버전 이후)
    • 보여주는 숫자는 퍼센트(%)이다.
      • row가 100건이고 filtered가 20%이면 필터링되고 남은 레코드는 20건이라는 의미다. 그러나 이것도 단지 예측일 뿐이다.

출처

-개발자와 DBA를 위한 Real MySQL (도서)

-https://www.mysql.com

MySQL