EXPLAIN字段详解&测试

       测试数据库来自mysql示例数据库(https://launchpad.net/test-db)。所用的表为sakila.film以及sakila.film_actor和sakila.language。表结构分别为：

       CREATE TABLE `film` (

  `film_id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,

  `title` varchar(255) NOT NULL,

  `description` text,

  `release_year` year(4) DEFAULT NULL,

  `language_id` tinyint(3) unsigned NOT NULL,

  `original_language_id` tinyint(3) unsignedDEFAULT NULL,

  `rental_duration` tinyint(3) unsigned NOT NULLDEFAULT '3',

  `rental_rate` decimal(4,2) NOT NULL DEFAULT'4.99',

  `length` smallint(5) unsigned DEFAULT NULL,

  `replacement_cost` decimal(5,2) NOT NULLDEFAULT '19.99',

  `rating` enum('G','PG','PG-13','R','NC-17')DEFAULT 'G',

  `special_features`set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULTNULL,

  `last_update` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`film_id`),

  KEY `idx_title` (`title`),

  KEY `idx_fk_language_id` (`language_id`),

  KEY `idx_fk_original_language_id`(`original_language_id`),

  CONSTRAINT `fk_film_language` FOREIGN KEY(`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,

  CONSTRAINT `fk_film_language_original`FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ONUPDATE CASCADE

) ENGINE=InnoDBAUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

 

CREATE TABLE`film_actor` (

  `actor_id` smallint(5) unsigned NOT NULL,

  `film_id` smallint(5) unsigned NOT NULL,

  `last_update` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`actor_id`,`film_id`),

  KEY `idx_fk_film_id` (`film_id`),

  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY(`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,

  CONSTRAINT `fk_film_actor_film` FOREIGN KEY(`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE

) ENGINE=InnoDBDEFAULT CHARSET=utf8

 

id

     *id可以认为是查询序列号，每一个id代表一个select，一组相同的id可以认为是一个查询里分开进行的几步（如关联查询），解析顺序在explain生成表中由上至下顺序解析，不同的id代表不同子查询，id越大优先级越高，越先被解析。根据mysql官方文档的解释，id是selectidentifier，即select标识符，解析顺序并不代表执行顺序，但是一般来讲相差不大。

  *id相同：explain selectfilm_actor.actor_id from film inner join film_actor using(film_id) order by film_actor.actor_idlimit 100;

       *id不同：explain select film_actor.actor_id from film innerjoin film_actor using(film_id)

union all select film_actor.actor_id fromfilm_actor where film_id = 1;

 

       这里由于使用了union all，所以union操作时不会使用默认选项distinct，在重复选项不影响时可以提高速度。

select_type

       *SIMPLE:查询不含union或者子查询（关联查询仍然是si