EXPLAIN 키워드를 통해 실행계획을 보면 칼럼-값 쌍을 확인할 수 있다. 각 칼럼이 어떤 의미를 나타내는지 정리했다.
선정 이유
내가 실행한 쿼리의 실행 계획을 보려면 각 칼럼이 어떤 의미를 갖는지 알아야한다.
해당 텍스트
id 칼럼
각 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)으로 검색하는 경우를 나타낸다.
위 세가지 타입은 쿼리 튜닝시 크게 신경쓰지 않아도 된다.
range : 인덱스 레인지 스캔 형태의 쿼리 타입을 의미항다.
index_merge : 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 병합하는 방식이다.
index : 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. 인덱스를 효율적으로 사용하는 방법이 아님을 주의.
all : 풀 테이블 스캔을 의미한다. 가장 비효율적인 방식이다.
보통의 빠른 응답을 사용자에게 돌려줘야 하는 OLTP 웹 서비스 환경에서는 index와 ALL 방법은 적합하지 않다.
possible_keys
사용될 수 있었던 인덱스의 목록. 말 그대로 후보였던 것들이며 사용한 인덱스를 의미하는 것이 아니다.
key
최종 선택된 실행 계획에서 사용하는 인덱스. 인덱스를 전혀 사용하지 못하면 NULL로 표기된다.
key_len
쿼리를 처리하기 위해 다중 칼럼 인덱스에서 몇 개 칼럼까지 사용했는지 바이트 단위로 알려주는 값. 예를 들어 CHAR(4) 칼럼과 INTEGER 칼럼의 복합 인덱스에서 앞쪽 CHAR(4) 칼럼만 유효하게 사용했다면, 총 인덱스 키는 16바이트지만 key_len에는 12바이트가 표시된다. (utf8 기준 문자 1개 고정값 3바이트)
Extra
주로 내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 보여준다. 이름과 달리 성능 튜닝에서 중요하게 참고되는 칼럼이다.
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 엔진이 스토리지 엔진에서 받은 데이터에 별도의 가공을 한 경우 표시된다.
Distinct : 두 테이블 조인 시 중복된 값을 제거했음을 알려준다.
impossible HAVING : 쿼리에서 HAVING절의 조건을 만족하는 레코드가 없는 경우를 나타낸다.
impossible WHERE : 쿼리에서 WHERE절 조건이 항상 false인 경우를 나타낸다.
Not exists : 아우터 조인을 이용해 안티 조인을 처리하는 경우.
NOT IN (subquery) 나 NOT EXISTS를 사용하는 조인을 안티 조인이라 한다.
A 테이블에는 존재하지만 B 테이블에 없는 값을 조회할 경우에 안티 조인을 사용하는데, 레코드 건수가 많을 때는 안티 조인을 아우터 조인으로 처리하면 빠른 성능을 낼 수 있다.
주제
EXPLAIN키워드를 통해 실행계획을 보면 칼럼-값 쌍을 확인할 수 있다. 각 칼럼이 어떤 의미를 나타내는지 정리했다.선정 이유
내가 실행한 쿼리의 실행 계획을 보려면 각 칼럼이 어떤 의미를 갖는지 알아야한다.
해당 텍스트
id 칼럼
각 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: 외부 쿼리에 의존적인 서브 쿼리를 의미한다.DERIVED: 서브 쿼리가 FROM절에 사용된 경우다. 이는 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다. 성능 상 비효율적일 가능성이 높다. 서브 쿼리 대신 조인으로 해결할 수 있다면 조인으로 해결해야 한다.UNCACHEABLE SUBQUERY: 원래의 서브 쿼리는 여러 번 읽힐 수 있기 때문에 내부적으로 캐싱이 된다. 하지만 여러가지 이유로 인해 캐싱될 수 없는 상황을 말한다.type
각 테이블에 접근하는 방식을 나타낸다. 인덱스 사용 여부 등을 여기서 확인할 수 있기 때문에 성능 튜닝시 꼭 확인해야하는 칼럼 중에 하나이다.
const: PK나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있고 반드시 1건의 레코드를 반환하는 쿼리 타입을 의미한다.eq_ref: 여러 테이블 조인 시, 조인에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽을 테이블의 PK나 유니크 키 칼럼의 검색 조건에 사용하는 경우를 나타낸다.ref: 동등 조건(Equal)으로 검색하는 경우를 나타낸다.위 세가지 타입은 쿼리 튜닝시 크게 신경쓰지 않아도 된다.
range: 인덱스 레인지 스캔 형태의 쿼리 타입을 의미항다.index_merge: 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 병합하는 방식이다.index: 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. 인덱스를 효율적으로 사용하는 방법이 아님을 주의.all: 풀 테이블 스캔을 의미한다. 가장 비효율적인 방식이다.보통의 빠른 응답을 사용자에게 돌려줘야 하는 OLTP 웹 서비스 환경에서는 index와 ALL 방법은 적합하지 않다.
possible_keys
사용될 수 있었던 인덱스의 목록. 말 그대로 후보였던 것들이며 사용한 인덱스를 의미하는 것이 아니다.
key
최종 선택된 실행 계획에서 사용하는 인덱스. 인덱스를 전혀 사용하지 못하면 NULL로 표기된다.
key_len
쿼리를 처리하기 위해 다중 칼럼 인덱스에서 몇 개 칼럼까지 사용했는지 바이트 단위로 알려주는 값. 예를 들어 CHAR(4) 칼럼과 INTEGER 칼럼의 복합 인덱스에서 앞쪽 CHAR(4) 칼럼만 유효하게 사용했다면, 총 인덱스 키는 16바이트지만 key_len에는 12바이트가 표시된다. (utf8 기준 문자 1개 고정값 3바이트)
Extra
주로 내부적인 처리 알고리즘에 대해 조금 더 깊은 내용을 보여준다. 이름과 달리 성능 튜닝에서 중요하게 참고되는 칼럼이다.
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 엔진이 스토리지 엔진에서 받은 데이터에 별도의 가공을 한 경우 표시된다.Distinct: 두 테이블 조인 시 중복된 값을 제거했음을 알려준다.impossible HAVING: 쿼리에서 HAVING절의 조건을 만족하는 레코드가 없는 경우를 나타낸다.impossible WHERE: 쿼리에서 WHERE절 조건이 항상 false인 경우를 나타낸다.Not exists: 아우터 조인을 이용해 안티 조인을 처리하는 경우.관련 페이지
p.343 ~