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가 좋은 도구인듯 하다.
(알면 알수록 더 답답해지는 부분도 많이 있긴 하지만..)

MyISAM 테이블과 InnoDB 테이블의 통계 정보

MySQL은 각 테이블들에 대해서 INFORMATION_SCHEMA 데이터베이스의 STATISTICS라는 테이블에 통계 정보를 관리하고 있다.

이러한 통계 정보는 쿼리의 실행 시에 최적의 실행 방법을 찾아내기 위한 기초 데이터로 사용된다.

대표적으로 MySQL 옵티마이저가 인덱스를 사용해서 데이터를 조회할 지 또는 테이블 전체를 스캔할 지 등의 결정을 하는 데 사용된다.

MySQL의 통계 정보는 Oracle과 같은 다른 DBMS와는 달리 히스토그램 정보는 없으며 단순히 Cardinality만 관리한다.

여기서 Cardinality라 함은 해당 인덱스 값의 Unique 값의 수를 의미한다.

또한, MySQL은 Oracle과 달리 통계 정보가 상당히 동적이다. 동적이라 함은 여러 가지 Event에 의해서 상당히 자주

업데이트됨을 의미한다. 그래서 MySQL에서는 Oracle과 같이 통계 정보를 마이그레이션한다거나 정확한 통계 정보 수집을

위해서 ANALYZE를 해둔다는 것은 의미가 없다 (라고 자주 이야기되어진다).

MySQL의 통계 정보는 다음과 같은 Event가 발생할 경우, 자동적으로 재 수집을 하게 된다.

1. MySQL 서버에 의해서 테이블이 처음 Open되는 시점
2. ANALYZE TABLE TBL_NAME; 명령 실행 시
3. SHOW TABLE STATUS LIKE ‘TBL_NAME’; 명령 실행 시
4. SHOW INDEX FROM TBL_NAME; 명령 실행 시
5. Meta Query (테이블에 대한 DDL 문장) 실행 시 (InnoDB Plug-in에서는 innodb_stats_on_metadata 설정 옵션에 의존)
6. 지정된 량 이상의 데이터가 변경 될 경우
7. 기타 등등...

위와 같은 Event에 의해서 사용자가 눈치채지 못하는 사이, 자동으로 테이블의 통계 정보가 변경되기 때문에, Oracle과 같은 DBMS와는 달리 MySQL에서는 테이블의 통계 정보가 그다지 관심의 대상이 될 수 없는 것이다. (별달리 DBA가 해줄 수 있는 것도 별로 없다. ㅠㅠ)

MyISAM 테이블과 InnoDB 테이블의 통계 정보 또한 수집 방법이 조금씩 다른데,

MyISAM 테이블

1. 인덱스 전체를 읽어서 정확한 Cardinality를 구하게 된다.
2. 작은 테이블이라 하더라도 어느 정도의 시간이 걸리며
3. 통계 정보를 수집하는 동안은 Lock을 걸어서 데이터의 변경이 금지된다.
4. InnoDB도 마찬가지지만, MyISAM 테이블의 경우에는 특히나 서비스 중에 통계 정보를 수집하는 것은 피하는 것이 좋을 듯 하다.
5. 대신, MyISAM 테이블의 통계 정보는 한번 수집되면 상당히 정확한 Cardinality를 가지게 된다.
6. MyISAM 테이블의 통계 정보는 InnoDB 보다는 고정된 형태이며, “ANALYZE TABLE TBL_NAME;” 명령을 명시적으로 실행하는 경우에만 통계 정보가 수집됨

InnoDB 테이블

1. 랜덤하게 데이터 페이지 몇 개를 샘플링해서 분석한 뒤 예측된 Cardinality를 구하게 된다.
2. MyISAM과 동일하게 통계 정보를 수집하는 동안은 Lock으로 변경이 금지된다.
3. InnoDB Plug-in 이전의 Built-in 버전까지는 무조건 랜덤하게 8개의 인덱스 페이지를 샘플링해서 분석했었지만, 
   Plug-in 버전부터는 랜덤하게 샘플링할 페이지의 개수를 설정 옵션으로 지정할 수 있다.
4. 그래서, MyISAM 테이블의 통계 정보와는 달리 InnoDB 테이블의 통계 정보는 예측치이며, 실제 데이터와 상당한 차이를 보이게 된다.
5. MyISAM 보다는 상당히 동적이며, 위에 언급된 대부분의 경우에 통계 정보가 수집됨

그런데,

InnoDB 테이블의 경우만 간단한 테이블을 생성 후, 레코드를 몇 건 입력하고 SHOW INDEX를 실행해 보았다.

mysql> show index from stat_test;  

+-----------+------------+----------+--------------+-------------+..-------------+..

| Table     | Non_unique | Key_name | Seq_in_index | Column_name |.. Cardinality |..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test |          0 | PRIMARY  |            1 | fdpk        |..          14 |..

| stat_test |          1 | ix_test  |            1 | fd1         |..          14 |..

| stat_test |          1 | ix_test1 |            1 | fd1         |..          14 |..

| stat_test |          1 | ix_test1 |            2 | fd2         |..          14 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

Ix_test1 이라는 인덱스는 2개의 컬럼으로 구성되어 있기 때문에 2개의 레코드로 표현되었으며, 현재 Cardinality가 모두 14 인것으로 표시되었다. 사실 여기 14는 입력된 레코드의 건수이다. (현재 테스트 테이블의 레코드가 전부 한 페이지에 저장될 정도이기 때문에 정확한 레코드 건수가 수집될 수 있었을 것으로 보인다. 하지만 일반적인 서비스 환경에서는 그렇지 않을 것이다.)

분명히, SHOW INDEX 명령에 의해서 한번 통계 정보가 수집되었을 것으로 보이는데, 상당히 부정확하다.

실제 테이블의 데이터를 한번 조회 해보면, 통계 정보와 상당히 다르다는 것을 알 수 있다.

mysql> select count(distinct fd1) as cardinality from stat_test;    

+-------------+

| cardinality |

+-------------+

|           2 |

+-------------+

mysql> select count(distinct fd1, fd2) as cardinality from stat_test;

+-------------+

| cardinality |

+-------------+

|          11 |

+-------------+

이 결과로 보아, 정확한 통계 정보는 아래와 같이 표시되었어야 할 것으로 보인다.

정확히 이와 같진 않더라도, 이와 비슷한 값이 나왔어야 할 것으로 보이는데...

+-----------+------------+----------+--------------+-------------+..-------------+..

| Table     | Non_unique | Key_name | Seq_in_index | Column_name |.. Cardinality |..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test |          0 | PRIMARY  |            1 | fdpk        |..          14 |..

| stat_test |          1 | ix_test  |            1 | fd1         |..           2 |..

| stat_test |          1 | ix_test1 |            1 | fd1         |..           2 |..

| stat_test |          1 | ix_test1 |            2 | fd2         |..          11 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

여기에서 다시 ANALYZE TABLE 명령을 실행 후, 통계 정보를 다시 확인해 보았다.

mysql> analyze table stat_test;

+----------------+---------+----------+----------+

| Table          | Op      | Msg_type | Msg_text |

+----------------+---------+----------+----------+

| test.stat_test | analyze | status   | OK       |

+----------------+---------+----------+----------+

mysql> show index from stat_test;

+-----------+------------+----------+--------------+-------------+..+-------------+..

| Table     | Non_unique | Key_name | Seq_in_index | Column_name |..| Cardinality |..

+-----------+------------+----------+--------------+-------------+..+-------------+..

| stat_test |          0 | PRIMARY  |            1 | fdpk        |..|          14 |..

| stat_test |          1 | ix_test  |            1 | fd1         |..|           4 |..

| stat_test |          1 | ix_test1 |            1 | fd1         |..|           4 |..

| stat_test |          1 | ix_test1 |            2 | fd2         |..|          14 |..

+-----------+------------+----------+--------------+-------------+..+-------------+..

이 결과는 상당히 현실적으로 보인다. 하지만, 데이터가 많아져 페이지 수가 많아지면 이 예측은 상당히 어긋날 가능성도 높아질 것이다.

내부적인 처리는 소스를 확인하지 않는 이상 모르겠지만, 각 Event 별로 수집되는 통계 정보의 수준이 다른 것이 아닌가 생각이 된다.

결론적으로 InnoDB 테이블도, 생성 및 초기 Open된 시점이 오래된 테이블의 경우에는 명시적인 ANALYZE TABLE ...;

또는 ALTER TABLE ... ENGINE=INNODB; 등의 명령으로 통계 정보를 업데이트해 줄 필요는 있어 보인다.