MySQL EXPLAIN SQL出力情報説明
17937 ワード
EXPLAIN SQL ， ， 。 ，
， ， SQL 。 EXPLAIN 。
一、EXPLAIN概要
EXPLAIN SQL ， SQL 。
EXPLAIN id ，select_type，table，type，possible_keys，key
MySQL 5.6.3 SELECT, DELETE, INSERT,REPLACE, and UPDATE.
EXPLAIN EXTENDED
EXPLAIN PARTITIONS
二、EXPLAIN出力列説明
-- EXPLAIN
(root@localhost) [sakila]> explain select sum(amount) from customer a,
-> payment b where 1=1 and a.customer_id=b.customer_id and
-> email='[email protected]'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 590
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 14
Extra: 1、各列が表す意味
Column Meaning
------ ------------------------------------
id The SELECT identifier
select_type The SELECT type
table The table for the output row
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
Extra Additional information2、各列の具体的な説明
id：
， select
id ， ， id ( ) ，
id ， ， ； ，id ， ，
select_type:
select （ OR ）
select_type Value Meaning
------------- -----------------------------------------------
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT select
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query( )
DERIVED Derived table SELECT (subquery in FROM clause)
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be reevaluated
for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable
subquery (see UNCACHEABLE SUBQUERY)
table：
( ) ， :
• : The row refers to the union of the rows with id values of M and N.
• : The row refers to the derived table result for the row with an id value of N.
A derived table may result, for example, from a subquery in the FROM clause.
• : The row refers to the result of a materialized subquery for the row with an id value of N.
partitions:
， EXPLAIN， PARTITIONS
type：
system
const ，
eq_ref ， system，const ，
=， join
ref ， ， = <=>，
fulltext
ref_or_null ref ， NULL
index_merge ( ， )， 。
， （ range ）
unique_subquery in ， value in (select...) “select unique_key_column” 。
PS： in ！
index_subquery ， ”select non_unique_key_column“
range
index a. ， （Extra Using Index）；
b. （ Using Index）；
c. Extra Using Index Using Where ， ；
d. ， ，
all
possible_keys：
MySQL 。
， 。
NULL， ，
key
MySQL ， ， NULL
key possible_keys ， possible_keys
TIPS： ， key
key_len
，
ref
，
rows
MySQL ，
InnoDB， ，
Extra
三、EXPLAIN EXTENDEDの使用例
(root@localhost) [sakila]> explain extended select * from city where country_id in
-> ( select country_id from country where country='China') and 1=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: country
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 109
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: city
type: ref
possible_keys: idx_fk_country_id
key: idx_fk_country_id
key_len: 2
ref: sakila.country.country_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
(root@localhost) [sakila]> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `city`.`city_id` AS `city_id`,`city`.`city` AS `city`,`city`.`country_id`
AS `country_id`,`city`.`last_update` AS `last_update` from `sakila`.`country` join `sakila`.`city` where
((`city`.`country_id` = `country`.`country_id`) and (`country`.`country` = 'China'))
1 row in set (0.00 sec)
-- extended ， filtered
-- SQL ， 1=1
-- SQL SQL 四、EXPLAIN PARTITIONS例
(root@localhost) [sakila]> CREATE TABLE `actor_part` (
-> `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
-> `first_name` varchar(45) NOT NULL,
-> `last_name` varchar(45) NOT NULL,
-> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`actor_id`),
-> KEY `idx_actor_last_name` (`last_name`)
-> ) partition by hash(actor_id) partitions 4;
Query OK, 0 rows affected (0.11 sec)
(root@localhost) [sakila]> insert into actor_part select * from actor;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0
(root@localhost) [sakila]> explain select * from actor_part where actor_id=10; -- partitions
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | actor_part | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
(root@localhost) [sakila]> explain partitions select * from actor_part where actor_id=10; -- partitions
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | actor_part | p2 | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
-- partitions 五、参考:
MySQL reference manual 5.6