EXPLAIN 문은
단순하게 사용하면
DESCRIBE tbl_name이나
SHOW COLUMNS FROM tbl_name과 같은 의미의 동작을 하지만,
그러나
EXPLAIN SELECT select_options 문처럼 사용하면,
1) 어떻게 SELECT가 진행되었는지
2) 어떤 테이블이 어떤 순서로 JOIN되었는지 등에 관한 정보를 출력한다.
【사용법】
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
【예제】
mysql> show tables;
+-------------------+
| Tables_in_jijoeDB |
+-------------------+
| employees |
| test2 |
+-------------------+
2 rows in set (0.00 sec)
mysql> explain employees;
+----------+--------------+-------------------+------+-----+---------+----------------+
| Field | Type | Collation | Null | Key | Default | Extra |
+----------+--------------+-------------------+------+-----+---------+----------------+
| id | tinyint(4) | binary | | PRI | NULL | auto_increment |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| tel | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| address | varchar(225) | latin1_swedish_ci | YES | | NULL | |
| position | varchar(50) | latin1_swedish_ci | YES | | NULL | |
+----------+--------------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> show columns from employees;
+----------+--------------+-------------------+------+-----+---------+----------------+
| Field | Type | Collation | Null | Key | Default | Extra |
+----------+--------------+-------------------+------+-----+---------+----------------+
| id | tinyint(4) | binary | | PRI | NULL | auto_increment |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| tel | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| address | varchar(225) | latin1_swedish_ci | YES | | NULL | |
| position | varchar(50) | latin1_swedish_ci | YES | | NULL | |
+----------+--------------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc employees;
+----------+--------------+-------------------+------+-----+---------+----------------+
| Field | Type | Collation | Null | Key | Default | Extra |
+----------+--------------+-------------------+------+-----+---------+----------------+
| id | tinyint(4) | binary | | PRI | NULL | auto_increment |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| tel | varchar(20) | latin1_swedish_ci | YES | | NULL | |
| address | varchar(225) | latin1_swedish_ci | YES | | NULL | |
| position | varchar(50) | latin1_swedish_ci | YES | | NULL | |
+----------+--------------+-------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> explain select * from employees;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql>
EXPLAIN SELECT에 의해서 출력되는 각 필드는 다음과 같다.