MySQL :: MySQL 5.5 Reference Manual :: 7.8.2 EXPLAIN Output Format 페이지를 번역한 문서

7.8.2. EXPLAIN Output Format

EXPLAIN 명령은 SELECT 명령에 대한 실행 계획을 보여준다.

EXPLAIN 명령의 결과는 SELECT 명령에서 사용된 테이블에 대한 정보를 MySQL이 실제로 접근할 테이블의 순서대로 한 테이블당 한 행씩 보여준다. MySQL은 모든 조인을 NESTED-LOOP 조인으로 실행하는데 MySQL이 조인을 수행할 때 첫 번째 테이블의 한 행을 읽고 여기에 매칭되는 두 번째 테이블, 또 세 번째 테이블을 읽는 식으로 진행된다는 뜻이다. 모든 테이블이 처리되면 MySQL은 매칭된 컬럼들을 출력하고 더 이상 매칭되는 행이 없을 때 까지 테이블들을 역으로 검색한다. 작업이 완료되면 다음 행을 읽어들이고 위의 과정을 반복한다.

EXTENDED 키워드가 사용되면 EXPLAIN 명령 후 SHOW WARNINGS 명령을 실행해야 볼 수 있는 추가적인 정보를 함께 보여준다. 이 정보를 통해 SELECT 명령에서 옵티마이저가 테이블과 컬럼을 어떻게 다루는지, 쿼리문 재작성이나 최적화 규칙 적용 후에 어떤 모습인지를 알 수 있으며 최적화 과정에 관련된 부가적인 정보를 볼 수 있고 EXPLAIN 결과에 filtered 컬럼을 추가적으로 표시해준다.

하나의 EXPLAIN 명령에서 EXTENDED, PARTITIONS 키워드를 동시에 사용할 수는 없다.

EXPLAIN 실행결과에서 각 컬럼의 의미

여기에선 EXPLAIN 실행 후 나오는 컬럼들의 의미에 대해 설명한다. type, Extras 컬럼에 대한 자세한 내용은 뒤에서 다시 설명한다.

EXPLAIN 결과는 한 테이블당 한 행씩 출력되는데 각 행에서 컬럼들의 의미는 아래 표에 간단히 요약되어있고 보다 자세한 내용은 그 뒤에 이어서 설명되어있다.

컬럼의미
idSELECT 문의 고유한 ID
select_typeSELECT 종류
table이 행에서 사용된 테이블 이름
type조인 타입
possible_keys사용 가능한 인덱스 목록
key실제로 사용될 인덱스
key_len사용될 인덱스의 길이
ref인덱스와 비교될 컬럼
rows쿼리 실행을 위해 읽어야할 행의 수
Extra

추가적인 정보

id

SELECT 문의 고유한 ID. 쿼리에 사용된 SELECT 문에 순서대로 부여된 숫자다.

select_type

SELECT 문의 종류.

의미
SIMPLEUNION이나 서브쿼리를 사용하지 않는 단순한 형태의 SELECT
PRIMARY두 개 이상의 SELECT 문으로 이루어진 쿼리일때 가장 바깥쪽의 SELECT
UNIONUNION을 사용했을 때 첫 번째가 아닌 SELECT
DEPENDENT UNION바깥쪽 SELECT 문에 의존하는 UNION을 사용했을 때 첫 번째가 아닌 SELECT
UNION RESULTUNION의 결과
SUBQUERY서브쿼리의 첫 번째 SELECT
DEPENDENT SUBQUERY바깥쪽 SELECT 문에 의존하는 서브쿼리의 첫 번째 SELECT
DERIVEDFROM절 뒤에 서브쿼리를 사용해 얻어진 결과로부터의 SELECT
UNCACHEABLE SUBQUERY캐시할 수 없고 항상 바깥쪽 쿼리에 의해 항상 다시 계산되는 서브쿼리
UNCACHEABLE UNIONUNCACHEABLE SUBQUERY에 속하고 UNION을 사용했을 때 첫 번째가 아닌 SELECT

일반적으로 DEPENDENT는 상호 관련 서브쿼리의 사용을 의미한다. (참고: Section 12.2.10.7, "Correlated Subqueries")

DEPENDENT SUBQUERY와 UNCACHEABLE SUBQUERY는 값을 평가하는 방법에 있어 서로 다르다. DEPENDENT SUBQUERY의 경우는 외부 컨텍스트와 다른 값을 가진 변수들에 대해서 단 한번만 평가되지만 UNCACHEABLE SUBQUERY의 경우는 외부 컨텍스트의 각 행에 대해 모두 평가를 하게 된다. 서브쿼리의 캐시 가능 여부는 Section 7.9.3.1, "How the Query Cache Operates" 페이지에서 설명하는 제약사항에 따라 결정된다. 예를 들어 서브쿼리가 사용자 변수를 참조하는 경우에는 캐시될 수 없다.

table

결과를 얻기 위해 접근하는 테이블.

type

조인 방법. 자세한 설명은 아래 EXPLAIN 조인 방법들 참고.

possible_keys

쿼리 실행을 위해 사용 가능한 모든 인덱스를 보여준다. EXPLAIN 결과에서 나오는 테이블 순서와는 무관하다. 즉, 실제로 테이블 접근 순서를 정할 때는 possible_keys에 나열된 인덱스 중 어느 것도 사용하지 못할 수 있다.

이 컬럼이 NULL 값으로 나타나면 사용 가능한 인덱스가 아무 것도 없다는 뜻이다. 이런 경우 쿼리 실행 속도 개선을 위해 WHERE 절을 확인해서 이 쿼리가 인덱스 하기 적당한 컬럼을 사용하고 있는지 확인해볼 수 있다. 가능하다면 적절한 인덱스를 생성한 후 EXPLAIN 명령으로 다시 확인해보자. (참고: Section 12.1.7, "ALTER TABLE Syntax")

테이블에 어떤 인덱스가 존재하는지 확인하려면 'SHOW INDEX FROM 테이블명' 명령을 사용하면 된다.

key

실제로 사용될 인덱스를 나타낸다. MySQL이 possible_keys에 나타난 인덱스 중 하나를 사용하기로 결정하면 그 인덱스가 이 컬럼의 값으로 보여진다.

어떤 경우에는 possible_keys에 나타나지 않은 인덱스가 여기에 보여질 수도 있다. possible_keys에 나타난 인덱스들 중에서 쓸만한 인덱스가 하나도 없고 SELECT 하려는 컬럼이 특정한 인덱스가 가지고 있는 컬럼과 같을 경우 이렇게 될 수 있다. 이 경우에 실제로 데이터를 검색하기 위해 인덱스를 사용하는건 아니지만 데이터 블록을 읽는 대신 인덱스 블록을 읽는게 훨씬 효율적이기 때문에 인덱스를 사용한다.

InnoDB의 경우 PRIMARY KEY를 구성하고 있는 컬럼중 하나 또는 전체를 SELECT 할 경우 일반 인덱스가 사용될 수 있다. 왜냐하면 InnoDB의 인덱스 구조상 일반 인덱스는 PRIMARY KEY 정보를 가리키도록 만들어져 있기 때문에 그 자체에서 PRIMARY KEY 값을 읽을 수 있기 때문이다.

이 컬럼이 NULL 값이면 MySQL이 쿼리를 효율적으로 실행할 수 없는 상태임을 의미한다.

강제로 특정한 인덱스를 사용하거나 무시해야 하는 경우 FORCE INDEX, USE INDEX, IGNORE INDEX 문을 사용할 수 있다. (참고: Section 12.2.9.3, "Index Hint Syntax")

MyISAM, NDB 테이블의 경우 ANALYZE TABLE 명령을 실행해 옵티마이저가 더 좋은 인덱스를 선택할 수 있도록 도와줄 수 있다. NDB 테이블의 경우 이 명령은 분산된 pushed-down 조인의 성능도 향상시킨다. MyISAM 테이블의 경우 myisamchk --analyze 명령은 ANALYZE TABLE 명령과 같은 일을 한다. (참고: Section 6.6, "MyISAM Table Maintenance and Crash Recovery")

key_len

실제 사용될 인덱스의 길이를 나타낸다. key 컬럼의 값이 NULL이면 이 컬럼의 값도 NULL이다. 이 값을 통해 다중 인덱스에서 MySQL이 실제로 어떤 부분을 사용하는지 알 수 있다.

ref

key 컬럼에서 표시된 인덱스가 어떤 컬럼 또는 어떤 상수 값과 비교되는지 보여준다.

rows

쿼리 실행을 위해 검사해야 하는 행의 수를 보여준다.

InnoDB의 경우 이 값은 대략적인 값이고 그 외의 DB에서는 정확한 값이다.

filtered

쿼리의 조건절에 의해 필터링 되는 행의 비율을 보여준다. 즉, rows 컬럼은 검사 대상이 되는 행의 수이고 이전 테이블과 조인될 행의 수는 rows × filtered / 100 으로 계산할 수 있다. 이 컬럼은 EXPLAIN EXTENDED 명령으로 볼 수 있다.

Extra

MySQL이 쿼리를 해석하는 과정에 대한 추가적인 정보를 담고 있다. 자세한 내용은 아래 EXPLAIN 추가 정보에서 설명한다

EXPLAIN 조인 방법들

EXPLAIN 결과에서 type 컬럼은 테이블이 어떻게 조인되는지를 설명해준다. 아래 설명된 조인 방법의 순서는 성능이 좋은 것 부터 나쁜 것의 순서로 나열되어 있다.

system

테이블에 행이 하나만 존재할 수 있는 경우(예를 들어 시스템 테이블)일때 보여진다. const 타입의 특수한 경우다.

const

매칭되는 행이 하나만 존재하고 쿼리가 시작되고 바로 읽혀졌을 때 보여진다. 행이 하나 뿐이기 때문에 옵티마이저에서 상수로 취급되고 단 한 번만 읽히기 때문에 매우 빠르다.

const 타입은 WHERE 절의 비교 구문이 모두 PRIMARY KEY의 값이거나 UNIQUE 인덱스의 값일 때 사용된다. 아래의 쿼리에서 tbl_nameconst 타입으로 취급된다.

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

다른 테이블과 조인하기 위해 이 테이블의 행이 단 하나만 읽혀질 때 보여진다. system, const 타입을 제외하고는 가장 좋은 조인 방법이다. PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스의 모든 컬럼이 사용될때 이 타입이 사용된다.

eq_ref 타입은 인덱스의 컬럼이 = 연산자로 비교될 때 사용되고 비교 값은 상수나 이전에 조인된 테이블의 컬럼을 사용하는 표현식이 될 수 있다. 아래 예제에서 MySQL은 ref_table을 처리하기 위해 eq_ref 타입을 사용한다.

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref

다른 테이블과 조인하기 위해 인덱스 값과 매칭되는 모든 행을 읽어올 때 보여진다. ref 타입은 조인이 인덱스의 앞쪽만 사용하거나 사용되는 인덱스가 PRIMARY KEY, UNIQUE 인덱스가 아닐 경우 사용된다(즉, 키 값으로 하나의 행만 SELECT 할 수 없는 경우). 사용되는 인덱스를 통해 매칭되는 행의 수가 많지 않다면 좋은 조인 방법이라고 할 수 있다.

ref 타입은 인덱스의 컬럼이 = 또는 <=> 연산자로 비교될 때 사용된다. 아래의 예제에서 MySQL은 ref_table을 처리하기 위해 ref 타입을 사용한다.

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext

FULLTEXT 인덱스를 사용해서 조인될 때 보여진다.

ref_or_null

ref 타입과 비슷하지만 행이 NULL 값을 가지고 있는지까지 확인해야 할 경우 보여진다. 이 조인 타입은 서브쿼리를 처리할 때 자주 사용된다. 아래의 예제에서 MySQL은 ref_table을 처리하기 위해 ref_or_null 타입을 사용한다.

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

(참고: Section 7.13.4, "IS NULL Optimization")

index_merge

Index Merge 최적화를 사용할 때 보여진다. 이때 EXPLAIN 결과의 key 컬럼에는 사용되는 인덱스가 표시되고 key_len 컬럼에는 사용되는 인덱스 중 가장 길이가 긴 인덱스의 길이가 표시된다. (참고: Section 7.13.2, "Index Merge Optimization")

unique_subquery

아래의 예제처럼 ref 타입에서 IN 서브쿼리를 사용하는 경우 보여진다.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery는 효율을 위해 서브쿼리를 완전히 대체하는 인덱스 룩업 함수로 볼 수도 있다.

index_subquery

unique_subquery와 비슷하게 IN 서브쿼리를 대체하지만 아래의 예제처럼 유니크하지 않은 인덱스를 사용하는 서브쿼리일 경우 보여진다.

value IN (SELECT key_column FROM single_table WHERE some_expr) 

range

인덱스를 사용해 일정한 범위의 행을 선택할 수 있을 때 보여진다. EXPLAIN 결과 중 key 컬럼에는 사용되는 인덱스가 표시되고 key_len 컬럼에는 사용되는 인덱스 중 가장 길이가 긴 인덱스의 길이가 표시되며 ref 컬럼은 NULL로 표시된다.

range 타입은 인덱스의 컬럼이 =, <>, >, >=, <, <=, IS_NULL, <=>, BETWEEN, IN() 연산자로 비교될 때 사용된다.

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

이 타입은 인덱스 페이지를 스캔한다는 점을 빼고는 ALL 타입과 같다. 대부분 데이터 블록의 크기 보다는 인덱스 블록의 크기가 작기 때문에 ALL 타입보다는 빠르다.

쿼리가 한 인덱스를 구성하는 컬럼 중 일부를 사용할 때 보여진다.

ALL

다른 테이블과 조인하기 위해 테이블 전체를 스캔할 때 보여진다. EXPLAIN에서 const로 표시되지 않은 첫 번째 테이블이라면 좋지 않은 정도의 타입이며 일반적으로는 가장 나쁜 조인 타입이다.

EXPLAIN 추가 정보

EXPLAIN 결과 중 Extra 컬럼은 쿼리가 어떻게 실행되는지에 대한 추가적인 정보를 보여준다. 쿼리를 빠르게 실행하고 싶다면 이 컬럼에서 Using filesort, Using temporary가 발생하지 않는지를 눈여겨 보아야 한다.

Child of 'table' pushed join@1

NDB 커널로 pushed-down 가능한 조인의 자식으로 table 테이블이 참조되는 경우 보여진다. MySQL Cluster NDB 7.2 이후 버전에서 pushed-down 조인이 활성화 되어있을 때만 적용된다. ndb_join_pushdown 페이지의 서버 시스템 변수 섹션에서 더 많은 정보와 예제를 볼 수 있다.

const row not found

비어있는 테이블에서 SELECT ... FROM tbl_name 같은 쿼리를 실행할 때 보여진다.

Distinct

유니크한 값을 찾을 때 보여지며 매칭되는 첫 번째 값이 발견되면 더 이상 검색을 하지 않는다.

Full scan on NULL key

서브쿼리를 처리할 때 옵티마이저가 인덱스를 사용할 수 없어서 대체 전략을 사용하는 경우 보여진다.

Impossible HAVING

HAVING 절이 항상 거짓이라 아무런 행도 가져올 수 없을 때 보여진다.

Impossible WHERE

WHERE 절이 항상 거짓이라 아무런 행도 가져올 수 없을 때 보여진다.

Impossible WHERE noticed after reading const tables

constsystem 조인 타입일 때 모든 행을 다 읽은 후 WHERE 절이 항상 거짓이 되는걸로 판단될 때 보여진다.

No matching min/max row

SELECT MIN(...) FROM ... WHERE condition 같은 쿼리에서 조건을 만족하는 행을 찾을 수 없을 때 보여진다.

no matching row in const table

조인을 할 때 테이블이 비어있거나 UNIQUE 인덱스 조건으로 검색했으나 조건을 만족하는 행이 하나도 없을 경우 보여진다.

No tables used

쿼리에 FROM 절이 없거나 FROM DUAL을 사용할 때 보여진다.

Not exists

MySQL이 LEFT JOIN 최적화를 할 수 있었고 LEFT JOIN 조건에 의해 단 하나의 행만이 매칭될 경우 더 이상 검색을 하지 않는다. 이런 방법으로 최적화가 되는 쿼리는 아래와 같은 형태이다.

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;

t2.id 컬럼이 NOT NULL 타입으로 지정되었다고 가정하자. 먼저 t1.id 값으로 t2 테이블에서 같은 값을 찾게 되는데 이 때 매칭되는 값이 발견될 경우 t2.id 값이 NULL이 될 수 없음을 알게되고 그 다음부터는 같은 id 값을 가지고 검색을 해야 하는 경우 검색을 하지 않고 넘어가게 된다. 요약하면 t1의 모든 행에 대해 t2 테이블에서 매칭되는 값이 실제로 얼마나 되는지에 상관 없이 t2 테이블에서의 검색은 단 한 번만 실행된다는 것이다.

Range checked for each record (index map: N)

쿼리를 실행하기에 적당한 인덱스를 발견하지는 못했지만 선행 테이블의 컬럼 값들로부터 사용 가능한 인덱스들이 발견된 경우, 선행 테이블의 모든 행의 조합에 대해 range 또는 index_merge 접근 방법을 사용할 수 있는지 검사하게 된다. 이 방법은 빠르진 않지만 인덱스를 전혀 사용하지 않고 조인하는 것 보다는 낫다. 이 방법이 적용되는 기준은 Section 7.13.1, "Range Optimization", Section 7.13.2, "Index Merge Optimization" 페이지에 설명되어 있는데 선행 테이블의 모든 컬럼 값을 알고 있고 상수로 간주될 때만 해당되는 방법이다.

테이블의 인덱스들은 SHOW INDEX 명령을 실행했을 때 나오는 순서대로 1부터 번호가 매겨진다. 이 메시지에서 N에 해당하는 값은 사용 가능한 인덱스들을 나타내는 비트마스크 값인데, 예를 들어 N 값이 0x19(2진수로 11001)라면 1, 4, 5번째의 인덱스가 사용 가능하다는 뜻이다.

Scanned N databases

Section 7.2.4, "Optimizing INFORMATION_SCHEMA Queries" 페이지에 설명된 것 처럼 INFORMATION_SCHEMA 테이블에 대한 쿼리가 실행될 때 서버가 얼마나 많은 디렉토리를 스캔하는지 보여준다. 이 값은 0, 1, all 중에 하나로 보여진다.

Select tables optimized away

MyISAM 테이블에서 인덱스를 사용하는 집계 함수(MIN(), MAX())와 COUNT(*) 함수만 사용되었고 GROUP BY 절이 없어서 옵티마이저가 하나의 행만을 리턴해야 한다고 판단했을 때 보여진다.

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table

Section 7.2.4, "Optimizing INFORMATION_SCHEMA Queries" 페이지에 설명된 것 처럼 INFORMATION_SCHEMA 테이블에 대한 쿼리가 실행될 때 파일을 오픈하는 방법에 대한 최적화를 보여준다.

  • Skip_open_table: 테이블 파일을 오픈할 필요가 없을 때. 이미 데이터베이스 디렉토리를 검색하여 쿼리 내에서 사용 가능할 때.
  • Open_frm_only: 테이블의 .frm 파일만 오픈해야 할 때.
  • Open_trigger_only: 테이블의 .TRG 파일만 오픈해야 할 때.
  • Open_full_table: 최적화 되지 않은 검색으로 인해 .frm, .MYD, .MYI 파일을 오픈해야 할 때.

unique row not found

UNIQUE 인덱스나 PRIMARY KEY 조건을 만족하는 행이 없는 테이블에서 SELECT ... FROM tbl_name 같은 쿼리를 실행했을 때 보여진다.

Using filesort

정렬된 결과를 보여주기 위해 별도의 작업을 수행해야 하는 경우 보여진다. 정렬 작업은 WHERE 절에 매칭되는 모든 행의 키와 포인터를 조인 방법에 따라 저장하는 과정을 거치고 정렬된 순서대로 각 행에 접근해 결과를 가져오게 된다. (참고: Section 7.13.9, "ORDER BY Optimization")

Using index

데이터 블록에 접근하지 않고 인덱스 트리 검색만으로 데이터를 가져올 수 있을 때 보여진다. 단일 인덱스의 일부분만을 SELECT 하는 경우에 이런 전략이 사용된다.

EXPLAIN 결과 중 Extra 컬럼의 값으로 Using where가 같이 나오는 경우는 인덱스가 데이터 검색을 위해 사용된다는 의미이고 Using where가 없이 Using index만 있는 경우는 인덱스를 데이터 검색에 사용하는게 아니라 데이터 블록 접근을 피하기 위해 인덱스 블록에서 데이터를 가져온다는 의미이다.

사용자가 임의로 지정한 클러스터드 인덱스를 가지고 있는 InnoDB의 경우 Extra 컬럼에 Using index라고 나오지 않더라도 이런식으로 인덱스가 사용될 수 있다. EXPLAIN 결과 중 type 컬럼의 값이 index이고 keyPRIMARY일 경우 이런 현상이 발생한다.

Using index for group-by

Using index 메시지의 경우처럼 데이터 블록에 접근하지 않고 GROUP BYDISTINCT 쿼리를 실행할 수 있을 때 보여진다. 이런 경우에 인덱스는 각 그룹에 대해 아주 적은 부분만 읽혀지므로 효율적으로 사용될 수 있다. (참고: Section 7.13.10, "GROUP BY Optimization")

Using join buffer

이전에 수행된 테이블 조인 결과 중 일부가 조인 버퍼에 담겨지고 그 버퍼의 내용이 현재 테이블 조인 작업을 위해 사용되는 경우 보여진다.

Using sort_union(...), Using union(...), Using intersect(...)

index_merge 조인 타입에서 인덱스를 읽어서 어떻게 병합하는지 보여준다. (참고: Section 7.13.2, "Index Merge Optimization")

Using temporary

쿼리 실행 중 결과를 담아둘 임시 테이블을 생성해야 할 경우 보여진다. 일반적으로 GROUP BYORDER BY 절이 서로 다른 컬럼들을 사용할 때 나타난다.

Using where

다음 테이블과 조인되거나 클라이언트로 보내질 결과들이 WHERE 절에 의해 필터링 되는 경우 보여진다. 테이블의 모든 행을 읽으려는 경우가 아님에도 Extra 컬럼에 Using where라는 메시지가 보여지지 않고 조인 타입이 ALL이나 index로 나온다면 쿼리에 문제가 있을 가능성이 있다.

Using where with pushed condition

이 메시지는 NDBCLUSTER 테이블을 사용할 때만 볼 수 있는데 MySQL 클러스터가 인덱싱 되지 않은 컬럼과 상수 값을 직접 비교할 때의 효율 향상을 위해 Condition-Pushdown 최적화를 사용한다는걸 의미한다. 이 경우에 조건문은 클러스터의 데이터 노드들로 "pushed down"되어 동시에 실행된다. 이 방법은 매칭되지 않는 데이터를 네트워크로 전송할 필요가 없게 해주기 때문에 Condition-Pushdown을 사용하지 않을 때에 비해 5-10배 정도 속도 향상을 가져올 수 있다. (참고: Section 7.13.3, "Engine Condition Pushdown Optimization")

EXPLAIN 출력 결과 분석

EXPLAIN 결과 중 rows 컬럼의 값들을 모두 곱한 값을 통해 조인이 얼마나 효율적인지 가늠해볼 수 있다. 이 값은 MySQL이 쿼리를 실행하기 위해 얼마나 많은 행을 조사해야 하는지 보여주는데 시스템 변수 max_join_size 값으로 쿼리를 제한하고 있다면 이 값으로 여러 테이블에 접근하는 SELECT 쿼리의 실행이 가능할지의 여부도 결정할 수 있다. (참고: Section 7.11.2, "Tuning Server Parameters")

아래의 예제는 여러 테이블에 접근하는 쿼리를 EXPLAIN 명령을 통해 조금씩 개선하는 과정을 보여준다.

우선 다음과 같은 SELECT 문을 EXPLAIN 명령을 통해 개선한다고 가정하자.

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR; 

더불어 아래와 같은 가정을 따른다.

  • WHERE 절에 사용된 컬럼들은 아래와 같이 선언되었다.

    테이블컬럼타입
    ttActualPCCHAR(10)
    ttAssignedPCCHAR(10)
    ttClientIdCHAR(10)
    etEMPLOYIDCHAR(15)
    doCUSTNMBRCHAR(15)
  • 이 테이블은 아래와 같은 인덱스를 가지고 있다.

    테이블인덱스
    ttActualPC
    ttAssignedPC
    ttClientId
    etEMPLOYID (primary key)
    doCUSTNMBR (primary key)
  • tt.ActualPC 값은 균일하게 분포되어 있지 않다.

쿼리 최적화를 시작하기 전에 EXPLAIN을 실행한 결과는 아래와 같다.

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

모든 테이블의 조인 타입이 ALL이므로 MySQL이 모든 행에 대한 조합인 카티션곱 결과를 생성할 것임을 알 수 있다. 이 과정은 각 테이블의 레코드 수를 곱한 만큼의 행을 조사해야 하기 때문에 아주 긴 시간이 소요되는 작업이다. 위의 경우에 수행되는 곱셉의 결과는 74 × 2135 × 74 × 3872 = 45,268,558,720 이다. 테이블이 더 컸다면 시간이 얼마나 걸릴지 모를 일이다.

한 가지 문제가 있는데 MySQL은 인덱스를 사용할 때 같은 타입과 길이로 선언되어야 가장 효율적으로 동작한다는 점이다. VARCHAR 타입과 CHAR 타입은 같은 길이로 선언됐을 경우 같은 형태의 인덱스로 취급된다. 지금 tt.ActualPC 컬럼은 CHAR(10) 타입으로 선언되었고 et.EMPLOYID 컬럼은 CHAR(15) 타입으로 선언되어서 서로 길이가 맞지 않는 상태다.

서로 다른 컬럼의 길이를 맞춰주기 위해 ALTER TABLE 명령을 사용하여 ActualPC 컬럼의 길이를 15로 늘려보자.

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

이제 tt.ActualPC 컬럼과 et.EMPLOYID 컬럼 모두 VARCHAR(15) 타입이 되었다. EXPLAIN 명령을 다시 한 번 실행해보면 아래와 같이 변한걸 볼 수 있다.

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

아직 완벽하지 않지만 rows 컬럼 값들의 곱이 74배나 줄어들어 훨씬 성능이 좋아졌다. 이 상태의 쿼리는 실행되는데 수 초정도 소요된다.

이제 tt.AssignedPC = et_1.EMPLOYID, tt.ClientID = do.CUSTNMBR 두 비교문의 컬럼 길이 불일치를 해결하기 위해 또 테이블을 수정하자.

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

수정 후 EXPLAIN을 실행하면 아래와 같은 결과를 볼 수 있다.

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

이제 최적화가 거의 다 완료되었지만 남아있는 문제가 있다. MySQL은 기본적으로 tt.ActualPC 컬럼의 값이 고르게 분포되어있다고 생각하지만 tt 테이블의 경우는 그렇지 않다. 다행히 MySQL에게 실제 데이터 분포를 알려줄 수 있는 방법이 있다.

mysql> ANALYZE TABLE tt;

다시 EXPLAIN 명령을 실행해보면 인덱스 정보가 추가되어 조인이 효율적으로 이루어지고 있음을 볼 수 있다.

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN 결과의 rows 컬럼 값은 조인 옵티마이저로부터 학습된 값이라는 점에 유의하자. rows 값들의 곱과 쿼리가 리턴하는 실제 행의 수가 얼마나 비슷한지 확인해야 한다. 두 수치가 많은 차이를 보인다면 SELECT 문에서 STRAIGHT_JOIN을 사용하거나 FROM 절의 테이블의 순서를 변경함으로써 성능의 개선을 기대할 수도 있다.

어떤 경우에는 EXPLAIN SELECT 명령이 서브쿼리와 사용될 때 DML이 실행되는 것도 가능하다. (참고: Section 12.2.10.8, "Subqueries in the FROM Clause")