EXPLAIN EXTENDED 로 쿼리 최적화 결과 확인

MySQL에서 쿼리의 실행 계획을 확인하는 방법은 EXPLAIN 명령을 이용해서 확인할 수 있다.
하지만 쿼리 실행계획에 나오는 결과만으로는 부족할 때가 가끔 있는데,
대표적인 경우가 MySQL 옵티마이져가 최종적으로 변환한 쿼리의 형태가 어떤 형태인지 확인하고자 할 경우가 대표적이다.
이런 경우에 EXPLAIN EXTENDED 명령을 이용해서 MySQL Optimizer에 의해서 최종적으로 어떻게 쿼리가 변환되었는지를 알 수 있다.
아래 테스트 결과는 MySQL 5.1 버전대에서 보여진 결과이며, MySQL 4.1 또는 MySQL 5.0에서 확인하는 방법은 조금 다른데, 이 경우 확인 방법은 최 하단에 추가하겠다.
우선, 간단한 단일 테이블 쿼리와 조인 쿼리 몇개를 실행해서 변환된 쿼리의 내용을 확인해보자.
(참고로, 출력 결과는 아래와 같이 포맷팅되어 있지 않다. 단지 보기 편하도록 직접 손으로 포맷팅만 한 것이다.)

이 예제는 단일 테이블에 대해서 여러 조건들을 가진 쿼리가 어떻게 변환되는지 확인해보았다.
root@localhost:sb_innodb 23:02:09> EXPLAIN extended SELECT * FROM sbtest WHERE id>5 AND id>6 AND c='a' AND pad=c; 
+----+-------------+--------+-------+..+---------+---------+..+---------+----------+-------------+
| id | select_type | table  | type  |..| key     | key_len |..| rows    | filtered | Extra       |
+----+-------------+--------+-------+..+---------+---------+..+---------+----------+-------------+
|  1 | SIMPLE      | sbtest | range |..| PRIMARY | 4       |..| 5323572 |   100.00 | Using where |
+----+-------------+--------+-------+..+---------+---------+..+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
Note (Code 1003): select
  `sb_innodb`.`sbtest`.`id` AS `id`,
  `sb_innodb`.`sbtest`.`k` AS `k`,
  `sb_innodb`.`sbtest`.`c` AS `c`,
  `sb_innodb`.`sbtest`.`pad` AS `pad`
from `sb_innodb`.`sbtest`
where
  ((`sb_innodb`.`sbtest`.`id` > 5)
     and (`sb_innodb`.`sbtest`.`id` > 6)
     and (`sb_innodb`.`sbtest`.`c` = 'a')
     and (`sb_innodb`.`sbtest`.`pad` = 'a')
  )
실행 계획이 출력되고, 그 밑에 Note 항목으로 출력되는 조금 이상한 형태의 쿼리가 Optimizer에 의해서 변환된 쿼리 문장이다.
이 예제에서 보면, "*" 마크가 모든 컬럼명으로 대체되었으며, [c='a' AND pad=c] 조건이 [(`sb_innodb`.`sbtest`.`c` = 'a') and (`sb_innodb`.`sbtest`.`pad` = 'a')]로 변환된 것을 확인할 수 있다.
하지만 안타깝게도 MySQL Optimizer가 [id>5 AND id>6] 조건을 하나로 병합하지 못하고 [(`sb_innodb`.`sbtest`.`id` > 5) and (`sb_innodb`.`sbtest`.`id` > 6)] 이렇게 그대로 유지된 것도 확인할 수 있다.
조금은 한심스럽지만, 내부적으로 어떤 처리를 더 하는지는 모르니깐, 넘어가자. ㅠㅠ

다음으로 간단한 조인을 실행하는 쿼리의 예제를 살펴보자.
root@localhost:sb_innodb 23:02:25>EXPLAIN extended SELECT t1.id,t2.pad FROM sbtest t2, sbtest t1 WHERE t1.id=5 AND t2.k=t1.k;
+----+-------------+-------+-------+..+---------+---------+..+---------+----------+-------+
| id | select_type | table | type  |..| key     | key_len |..| rows    | filtered | Extra |
+----+-------------+-------+-------+..+---------+---------+..+---------+----------+-------+
|  1 | SIMPLE      | t1    | const |..| PRIMARY | 4       |..|       1 |   100.00 |       |
|  1 | SIMPLE      | t2    | ref   |..| k       | 4       |..| 5323572 |   100.00 |       |
+----+-------------+-------+-------+..+---------+---------+..+---------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
Note (Code 1003): select
  '5' AS `id`,
  `sb_innodb`.`t2`.`pad` AS `pad`
from
  `sb_innodb`.`sbtest` `t2`
     join `sb_innodb`.`sbtest` `t1` where ((`sb_innodb`.`t2`.`k` = '0'))
이 예제에서는, t1.id 값이 미리 Optimizer에 의해서 숫자값 5로 대체되고 [t1.id=5] 조건 자체가 제거되어버린 것을 확인할 수 있다.
또한, [t1.id=5] 인 레코드의 k 컬럼의 값이 0인 것을 Optimizer가 알아내었기 때문에 [t2.k=t1.k] 이 조건도 [(`sb_innodb`.`t2`.`k` = '0')]이런 상수 비교로 대체된 것을 확인할 수 있다.
이러한 형태의 최적화는 const 타입의 접근 형태에서 가능한 최적화 방법이다.
또한 JOIN이 포함된 쿼리의 변환 결과에서 조인 순서도 확인 가능하다고 메뉴얼상에는 명시되어 있었지만, 실제로는 그렇지 않고 처음 작성된 쿼리의 순서대로 나열되어 있다.
(메뉴얼 버그가 생각보다 심하다. ㅠㅠ)

다음 예제에서는 간단한 IN (Sub-Query) 형태의 쿼리를 살펴보자.
root@localhost:sb_innodb 23:02:45>EXPLAIN extended SELECT * FROM sbtest WHERE id IN (SELECT id FROM sbtest WHERE id BETWEEN 1 AND 10);
+----+--------------------+--------+-----------------+..+---------+..+----------+----------------+
| id | select_type        | table  | type            |..| key     |..| filtered | Extra          |
+----+--------------------+--------+-----------------+..+---------+..+----------+----------------+
|  1 | PRIMARY            | sbtest | ALL             |..| NULL    |..|   100.00 | Using where    |
|  2 | DEPENDENT SUBQUERY | sbtest | unique_subquery |..| PRIMARY |..|   100.00 | Using index; ..|
+----+--------------------+--------+-----------------+..+---------+..+----------+----------------+
2 rows in set, 1 warning (0.00 sec)
Note (Code 1003): select
  `sb_innodb`.`sbtest`.`id` AS `id`,
  `sb_innodb`.`sbtest`.`k` AS `k`,
  `sb_innodb`.`sbtest`.`c` AS `c`,
  `sb_innodb`.`sbtest`.`pad` AS `pad`
from `sb_innodb`.`sbtest`
where
  <in_optimizer>(`sb_innodb`.`sbtest`.`id`,<exists>(<primary_index_lookup>(<cache>(`sb_innodb`.`sbtest`.`id`)
    in sbtest on PRIMARY where ((`sb_innodb`.`sbtest`.`id` between 1 and 10)
  and (<cache>(`sb_innodb`.`sbtest`.`id`) = `sb_innodb`.`sbtest`.`id`)))))
IN (Sub-Query)가 변환된 것이 좀 읽고 해석하기는 힘들지만,
대략적으로 유추하면서 읽어보면, SubQuery를 위해서 sbtest 테이블을 Primary key 룩업을 통해서 id값을 캐시에 담고,
캐시에 담겨진 id 값과 Outer에 정의된 sbtest테이블의 id값을 비교해서 일치하는 건을 리턴하는 형태라는 것을 이해할 수 있다.
이 주제와는 관계가 없지만, 위의 실행 계획에서도 알 수 있듯이 MySQL에서는 [fd1 IN (Sub-Query)] 형태의 쿼리는 fd1 컬럼에 인덱스가 정의되어 있어도 사용하지 못한다.
(이는 MySQL의 알려진 버그 정도쯤 될 것 같다. 하지만 MySQL 5.1에서도 해결이 되지 않다니 ㅠㅠ)

MySQL 4.1이나 5.0에서는 EXPLAIN EXTENDED를 실행해도 변환된 쿼리는 보이지 않는다.
MySQL 5.1 미만의 버전에서는 Optimizer에 의해서 변환된 쿼리가 경고 메시지 형태로 Client에 전달되기 때문이며, 이때에는 "SHOW WARNINGS;" 명령을 실행하면 변환된 쿼리 문장을 확인할 수 있다.

혹시, 작성된 쿼리가 실행계획만으로는 어떻게 풀릴지 예측하기가 부족하다면 EXPLAIN EXTENDED 로 변환된 쿼리를 한번 확인해보는 것도 좋은 방법일듯 하다.
예를 들어서 [fd1 BETWEEN 100 AND 110] 조건과 [fd1>=100 AND fd1<=110] 이 MySQL Optimizer에 의해서 어떻게 변환되어서 실행되는지 궁금한 경우에도 EXPLAIN EXTENDED가 좋은 도구인듯 하다.
(알면 알수록 더 답답해지는 부분도 많이 있긴 하지만..)