[MySQL] EXPLAIN 사용하여 쿼리 최적화

EXPLAIN 명령문은 MySQL이 SELECT 명령문을 수행하는 방법에 대해 정보를 얻기 위한 수단이다.

EXPLAIN 테이블명 은 DESCRIBE 테이블명 또는 SHOW COLUMS FROM 테이블명 과 같은 의미이다.

EXPLAIN은 SELECT 명령문에서 사용된 각 테이블 정보를 리턴하는데 MySQL이 쿼리를 처리하는 동안 읽을 수 있도록 결과물 안에 테이블들이 목록으로 정리 된다.

EXTENDED 키워드가 사용되면, EXPLAIN은 EXPLAIN 명령문 다음에 SHOW WARNINGS 명령문을 입력해서 볼 수 있는 기타 정보를 리턴한다. 이 정보는 옵티마이저가 SELECT 명령문에 있는 컬럼 이름과 테이블을 얼마나 많이 검증을 하였는지를 보여주며, SELECT는 최적화 과정에 관한 어플리케이션 재 작성과 최적화 규칙, 그리고 다른 가능한 노트(notes)를 보여준다.

사용 예제 및 결과

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table_name	ref	idx_table_sri, idx_table_list_order, idx_table_up...	idx_table_status	8	const	34

옵션

옵션	설명
table	결과 열이 참조하는 테이블
type	조인(join) 타입. 서로 다른 타입의 조인이 아래에 있는데, 가장 좋은것부터 가장 나쁜 것의 순서로 되어 있다.
	system : 테이블이 하나의 레코드만 가지는 경우 const : 테이블에 조건을 만족하는 레코드가 하나일 때. 상수취금 eq_ref : 인덱스가 UNIQUE이거나 PRIMARY KEY인 경우의 조인으로 const를 제외한 조인 중 가장 좋은 형태 ref : eq_ref와 다른 점은 UNIQUE가 아닐 경우 사용한다는 것. range : 조건에 레코드의 범위가 주어진 조인. index : all  형태와 비슷하며, 인덱스를 사용한다. all : 모든 레코드를 스캔한다.
possible_keys	컬럼은 이 테이블에서 열을 찾기 위해 MySQL이 선택한 인덱스를 가리킨다. 이 컬럼은 EXPLAIN 결과에서 나타나는 테이블 순서와는 전적으로 별개의 순서가 된다. 이것은 possible_keys에 있는 키 중에 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을 수도 있음을 의미하는 것이다. 만일 이 컬럼 값이 NULL이라면, 연관된 인덱스가 존재하지 않게 된다. 이런 경우 쿼리 제작자는 WHERE 구문을 검사하여 이 구문이 인덱스 하기에 적당한 컬럼을 참조하고 있는지 여부를 알아 봄으로써 쿼리 속도를 개선 시킬 수가 있게 된다. 그러한 경우라면, 적절한 인덱스를 하나 생성한 후에, EXPLAIN을 다시 사용해서 쿼리를 검사한다. 테이블이 어떤 인덱스를 가지고 있는지를 보기 위해선, SHOW INDEX FROM [테이블명] 을 사용한다.
key	possible_keys에 나온 키 중에서 실제로 사용한 키로 MySQL이 실제로 사용할 예정인 키(인덱스)를 가리킨다. 만일 아무런 인덱스도 선택되지 않았다면, 그 키는 NULL이 된다. MySQL로 하여금 possible_keys 컬럼에 있는 인덱스를 사용하거나 또는 무시하도록 만들기 위해서, FORCE INDEX, USE INDEX 또는 IGNORE INDEX를 쿼리에서 사용하도록 한다. MyISAM 및 BDB 테이블의 경우에는 ANALYZE TABLE을 구동시키면 옵티마이저가 보다 좋은 인덱스를 선택하도록 도움을 줄 수가 있다. MyISAM 테이블의 경우에는 myisamchk --analyze 가 동일한 역할을 한다.
key_len	MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 만일 key 컬럼이 NULL이라면, 이 값도 NULL이 된다. key_len 값은 다중-부분(multiple-part) 키 중에 얼마나 많은 부분을 MySQL이 실제로 사용하는지를 알 수 있도록 한다.
ref	테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 보여준다.
rows	MySQL이 쿼리를 실행하기 위해 조사해야하는 열의 숫자를 가리킨다.
Extra	MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보를 제공한다.
	distinct : 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음. not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않음. range checked for each record : 최적의 인덱스가 없기 때문에 차선의 인덱스를 사용함. using filesort : MySQL이 정렬을 빠르게 하기 위해 부가적인 일을 함. using index : SELECT할 때 인덱스 파일만 사용. using temporary : 임시 테이블을 사용한다. order by 나 group by 할 때 주로 사용. where used : 조건을 사용함.