MySQL EXPLAIN EXTENDED can tell you all kinds of interesting things

MySQL EXPLAIN EXTENDED can tell you all kinds of interesting things

 

Date	Ver	Etc.

 

 

* 이 글은 MySQL Performance Blog 의 기사를 번역한 내용이다.

* EXPLAIN EXTENDED can tell you all kinds of interesting things

* http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-tell-you-all-kinds-of-interesting-things/

 

EXPLAIN EXTENDED can tell you all kinds of interesting things

 

While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1

 

많은 사람이 MySQL EXPLAIN 명령을 사용하는 와중에, 일부 사람들은 MySQL 4.1 에서 추가된 “extended explain” 을 친숙히 사용하고 있다.

 

EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process is called query rewriting, and it is a part of any good SQL optimizer. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including the rewritten query.

 

EXPLAIN EXTENDED 는 MySQL optimizer 가 query 에 어떤일을 하는지 볼 수 있다. 이를 반드시 알 필요는 없지만, MySQL 은 실제 수행 전에 당신의 쿼리를 생각치도 못할 정도로 바꿀 수 있다. 이 과정을 query rewriting 이라 하며 이는 좋은 SQL optimizer 가 갖춰야 할 부분이다. EXPLAIN EXTENDED 는 EXPLAIN 에 warning message 를 더한 것으로 rewritten query 를 포함한 부수적인 정보를 표현한다.

 

To take a look at EXPLAIN EXTENDED, I’ll start with three empty tables. It is important to note that the tables are empty because the MySQL optimizer treats empty tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a bit.

 

EXPLAIN EXTENDED 를 보자, 세개의 빈 테이블을 가지고 설명하겠다. 이 과정은 MySQL optimizer 가 빈 테이블을 두개 이상의 rows 를 가진 테이블과 어떤식으로 다르게 다루는지 보기 위해 중요하다. (부수적으로, 테이블이 하나의 row 를 가지는 경우 포함)

 

mysql> create table j1 (c1 int); Query OK, 0 rows affected (0.16 sec)   create table j2 (c1 int); Query OK, 0 rows affected (0.11 sec)   mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec)

 

mysql> explain extended select j1.c1 from j1, j2, j3 where j1.c1 = j2.c1 and j3.c1 = j1.c1; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set, 1 warning (0.04 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select '0' AS `c1` from `test`.`j1` join `test`.`j2` join `test`.`j3` where 0 | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

You might notice a few odd things about this EXPLAIN. First, there are no tables listed. Taking a look at the Extra column we see that MySQL mentions ‘const’ tables. A ‘const’ table is a table that contains 0 or 1 rows, or a table on which all parts of a primary key or unique key lookup are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then MySQL can immediately return an empty set because it infers that there is no way that rows could be returned. MySQL does this by adding the WHERE clause in the query with ‘where 0′.

 

여기서 EXPLAIN 의 평범하지 않은 부분을 주목해야한다. 먼저, 아무런 테이블도 표현되지 않았다. Extra 컬럼을 보면 ‘const’ tables 라는 부분을 볼 수 있다. ‘const’ 테이블은 0 혹은 1 을 포함하거나 primary key 나 unique key lookup 을 만족하는 where 절의 모든 부분을 의미한다. ‘const’ 테이블이 아무 rows 를 가지지 않으면, 이는 OUTER JOIN 에서 사용되지 않으며, MySQL 은 빈 집합 결과를 바로 리턴한다. 그 이유는 rows 를 리턴할 방법이 없다고 추론하기 때문이다. MySQL 은 이를 where 절을 이용해 구현한다. (‘where 0’)

 

Let’s now look at what happens after the value (1) is inserted into each of the tables. Each table contains only a single row, and the value in each table is 1.

 

이제 각 테이블에 value 1 을 입력했을 때 어떤 일이 일어나는지 확인하자. 각 테이블은 한 row 만을 가지고 있으며, value 는 1이다.

 

mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; Query OK, 1 row affected (0.00 sec)   mysql> explain extended select j1.c1 from j1, j2, j3 where j1.c1 = j2.c1 and j3.c1 = j1.c1; +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra | +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ |  1 | SIMPLE      | j1    | system | NULL          | NULL | NULL    | NULL |    1 |       | |  1 | SIMPLE      | j2    | system | NULL          | NULL | NULL    | NULL |    1 |       | |  1 | SIMPLE      | j3    | system | NULL          | NULL | NULL    | NULL |    1 |       | +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ 3 rows in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select '1' AS `c1` from `test`.`j1` join `test`.`j2` join `test`.`j3` where 1 | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

You should now notice that the tables are actually listed in the EXPLAIN output, but also notice that the type field is set to ‘system’. A ‘system’ table is a special case of ‘const’ table which is used when a table contains only one row. The contents of these tables are read before the query begins executing. Because of this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause with ‘where 1′ because it knows that all the const tables contain equal values. If they did not, the above plan with the ‘where 0′ would be generated.

 

주목해야할 것은 EXPLAIN 결과에 TABLE 이 실제로 나열되었다는 것이다. 하지만 type 필드가 ‘system’ 으로 되어 있다. ‘system’ 테이블은 ‘const’ 테이블의 특별한 케이스로 테이블이 하나의 row 를 가질 때 사용된다. 이 테이블의 내용은 쿼리가 실행을 시작하기 전에 읽혀진다. 이 때문에, MySQL 은 실값의 비교를 실행플랜을 완전히 세우기 전에 할 수 있다. 당신은 MySQL 이 where 절을 ‘where 1’ 로 변경한 부분을 볼 것이다. 이 이유는 모든 const table 이 같은 값을 가지고 있음을 알기 때문이다. 만약 그렇지 않다면 위의 플랜은 ‘where 0’ 을 사용했을 것이다.

 

Finally, lets insert a few more rows and test the plan:

 

마지막으로 row 를 더 입력해 플랜을 확인해 보자.

 

mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2;   mysql> explain extended select j1.c1 from j1, j2, j3 where j1.c1 = j2.c1 and j3.c1 = j1.c1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | j1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             | |  1 | SIMPLE      | j2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where | |  1 | SIMPLE      | j3    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 3 rows in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message                                                                                                                                                                    | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note  | 1003 | select `test`.`j1`.`c1` AS `c1` from `test`.`j1` join `test`.`j2` join `test`.`j3` where ((`test`.`j2`.`c1` = `test`.`j1`.`c1`) and (`test`.`j3`.`c1` = `test`.`j1`.`c1`)) | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

The type field has now changed to ALL, which means that the entire table will be read. This is because these tables contain no indexes.

 

type filed 가 ALL 로 변경되었다, 이것이 의미하는 바는 모든 테이블이 읽힐 것이라는 것이다. 그 이유는 이 테이블들이 index 가 없기 때문이다.

 

There is another interesting thing, and I probably should have mentioned it before. You will notice that the query that I explained used the comma syntax, but when MySQL rewrote the query it switched it to use the JOIN keyword. This should put to rest any debate as to any perceived performance difference between comma join and ANSI JOIN syntax. They are intrinsically the same.

 

또다른 재미있는 부분이 있다, 이 부분은 앞서 이야기 해야 했을지도 모른다. 내가 comma 를 사용한 문법에 주목할 수 있다, 하지만 MySQL 이 Query 를 재작성하여 JOIN 키워드로 바뀌었다. 이는 향후 comma join 과 ANSI JOIN 의 성능차이에 대해 확인되어야 할 것이다.

이는 본질적으로 동일하다.

Last, EXPLAIN EXTENDED can show you information about the query rewrites that MySQL makes when accessing views which use the MERGE algorithm.

 

마지막으로 EXPLAIN EXTENDED 는 MySQL 이 MERGE 알고리즘을 사용한 view 를 엑세스 할 때 Query rewrites 정보를 보여준다.

 

For example:

예를 들면

 

mysql> create view v1 as select * from j1; Query OK, 0 rows affected (0.10 sec)   mysql> explain extended select * from v1 where c1=1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | j1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+-------------------------------------------------------------------------------+ | Level | Code | Message                                                                       | +-------+------+-------------------------------------------------------------------------------+ | Note  | 1003 | select `test`.`j1`.`c1` AS `c1` from `test`.`j1` where (`test`.`j1`.`c1` = 1) | +-------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

The most important thing to notice is the WHERE clause. You will see that the SELECT statement used in the view has been modified to include the WHERE clause that I used when accessing the view.

 

위의 내용중 가장 중요한 부분은 WHERE 절이다. 당신은 view 를 엑세스하기 위해 변경된 WHERE 절을 SELECT statement 를 에서 볼 것이다.