应用explain的12个字段阐明
0. 前情提要: 用到的几个表阐明:
0.1. goods表和goods2两个表构造和数据雷同(复制的表)-test库
mysql> show create table goods;CREATE TABLE `goods` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `price` double DEFAULT NULL, UNIQUE KEY `id` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=150001 DEFAULT CHARSET=utf8mysql> show create table goods2;CREATE TABLE `goods2` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL, `price` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4mysql> select count(*) from goods;+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.02 sec)mysql> select count(*) from goods2;+----------+| count(*) |+----------+| 150000 |+----------+1 row in set (0.05 sec)0.2. t2表是goods表中的前10条-test库
mysql> select * from t2;+----+---------+--------+| id | name | price |+----+---------+--------+| 1 | 商品1 | 200.17 || 2 | 商品2 | 200.87 || 3 | 商品3 | 200.81 || 4 | 商品4 | 200.43 || 5 | 商品5 | 200.73 || 6 | 商品6 | 200.36 || 7 | 商品7 | 200.61 || 8 | 商品8 | 200.98 || 9 | 商品9 | 200.06 || 10 | 商品0 | 200.38 |+----+---------+--------+10 rows in set (0.00 sec)mysql> show create table t2;CREATE TABLE `t2` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL, `price` double DEFAULT NULL, UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb40.3. sakila库的film表:
mysql> show create table film;CREATE TABLE `film` ( `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `description` text, `release_year` year(4) DEFAULT NULL, `language_id` tinyint(3) unsigned NOT NULL, `original_language_id` tinyint(3) unsigned DEFAULT NULL, `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3', `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99', `length` smallint(5) unsigned DEFAULT NULL, `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99', `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G', `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_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`) ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;1 row in set (0.00 sec)mysql> select count(*) from film;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)0.4. sakila库的film_category表
mysql> show create table film_actor;CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_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=InnoDB DEFAULT CHARSET=utf8mb4;mysql> select count(*)from film_actor;+----------+| count(*) |+----------+| 5462 |+----------+1 row in set (0.00 sec)0.5. sakila库的film_category表
mysql> show create table film_category;CREATE TABLE `film_category` ( `film_id` smallint(5) unsigned NOT NULL, `category_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`,`category_id`), KEY `fk_film_category_category` (`category_id`), CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 mysql> select count(*) from film_category;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)0.6. sakila库的actor表
mysql> show create table actor;CREATE TABLE `actor` ( `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`)) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 mysql> select count(*) from actor;+----------+| count(*) |+----------+| 200 |+----------+1 row in set (0.00 sec)explain一共有12个字段, 上面具体介绍:
1. id=步骤编号: 示意以后执行打算的第几步
执行打算可能分很多步, 每一行示意一个步骤, id=1示意是第1步;如果编号id雷同, 执行程序就是从上到下;
id越大越先执行
id:1
1.1 id越大的越先执行
mysql> explain select (select 1 from actor limit 1) from film;+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+| 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index || 2 | SUBQUERY | actor | NULL | index | NULL | idx_actor_last_name | 182 | NULL | 200 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)上面的 2.row id=2 最大, 第二步就先执行;
第二步:
- 是子查问;
- 连贯类型是 index;
- 用到的索引名是 idx_actor_last_name;
- 扫描的索引长度182;
- 可能会检测的行数200;
- 扩大信息: 应用了索引;
1.2 id雷同的后面的先执行
2. select_type=查问类型: 示意是简略查问还是简单查问
select_type=primary 简单查问=蕴含union查问或者蕴含子查问select_type=simple 简略查问=不蕴含union, 也不蕴含子查问;
select_type=union
select_type=union result
select_type=dependent union
select_type=subquery
select_type=derived
select_type=materializaton
2.1. select_type=simple: 简略查问
示意不须要 union 操作, 或者不蕴含子查问有连贯查问时, 外层的查问为simple, 且只有一个;
2.2. select_type=primary: 简单查问(有union操作或有子查问)
简单查问: 有union操作; 或者有子查问;
2.2.1 union查问实例:
mysql> explain select film_id from film union all select film_id from film_actor;+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+| 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index || 2 | UNION | film_actor | NULL | index | NULL | idx_fk_film_id | 2 | NULL | 5462 | 100.00 | Using index |+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)有union查问的步骤, 位于最外层的查问的select_type即为 primary, 且只有一个;
2.3 select_type=union 连贯查问
union查问=连贯查问, 连贯的是2个select查问:
第一个查问是: derived派生的表; 除了一个之外, 第二个当前的表select_type都是union;
2.4 select_type=union result 此类型id为空(mysql5.7.30当前都没有了)
2.5 select_type=dependent union
此查问跟union 一样, 呈现在union或union all 语句中, 然而这个查问要受内部查问的影响;
mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+| 1 | PRIMARY | film_category | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | 100.00 | Using index || 3 | DEPENDENT UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | func | 5 | 100.00 | Using index |+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec)2.6 select_type=subquery 子查问
除了from子句中蕴含的子查问外, 其余中央呈现的子查问都可能是 subquery;
mysql> explain select (select 1 from actor limit 1) from film;+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+| 1 | PRIMARY | film | NULL | index | NULL | idx_fk_language_id | 1 | NULL | 1000 | 100.00 | Using index || 2 | SUBQUERY | actor | NULL | index | NULL | idx_actor_last_name | 182 | NULL | 200 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)2.7 select_type=derived from子句中呈现的子查问也叫派生表
然而在5.7当前的版本中没有 derived这个类型了, 做了优化;
5.6还是有的, 看: 5.7.30:
mysql> explain select (select 1 from actor where film_id=1) from (select * from film where film_id=1) der;+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+| 1 | PRIMARY | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index || 2 | DEPENDENT SUBQUERY | actor | NULL | index | NULL | idx_actor_last_name | 182 | NULL | 200 | 100.00 | Using where; Using index |+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+2 rows in set, 2 warnings (0.00 sec)2.8 select_type=materializaton 物化(具体化): 将子查问后果作为一个长期表来放慢执行速度
失常来讲是常驻内存, 下次查问会再次援用长期表;
如果是一个大表, 大表作为一个子查问, 就会在第一次查问时生成一个长期表, 前面每次援用;
mysql> explain select * from (select * from goods) s where id in (select id from goods2);+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+| 1 | SIMPLE | goods | NULL | ALL | id | NULL | NULL | NULL | 149548 | 100.00 | NULL || 1 | SIMPLE | <subquery3> | NULL | eq_ref | <auto_key> | <auto_key> | 8 | func | 1 | 100.00 | Using where || 3 | MATERIALIZED | goods2 | NULL | ALL | NULL | NULL | NULL | NULL | 149692 | 100.00 | NULL |+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+3 rows in set, 1 warning (0.00 sec)subquery3的子查问依赖3的物化的, 物化先执行, 而后是goods, 最初在是子查问;
物化前提是: 表记录比拟多
3. table=表名: 示意以后这一步波及的表都有哪些
- table示意查问应用的表名, 如果查问应用了别名, 显示的就是别名;
- 如果并不波及数据表的操作, table=null;
- 如果显示为<derived N>就示意这是个长期表, N就是步骤的id;
- 如果显示为<union M, N> 也是个长期表, 示意, 是M, N两个步骤id的后果集;
table:film
4. partitions=分区情况
partitions:NULL
5. type=连贯类型: 用的全表扫描/有没有走索引
type的效率由高到低排序:
type=system 最高效;type=const 用到索引: 且是惟一索引或主键且+ where返回了只有1行;type=eq_ref 用到索引: 连贯查问中前表每个后果, 都只匹配后表一行后果. 且比拟是惟一索引, 查问效率较高. type=ref 用到索引: 多表的 join 查问, 非惟一或非主键索引, 或者是应用了最左前缀 规定索引的查问.type=fulltext 用到全文索引type=ref_or_null 用到索引, 相当于 ref+ xxx is null的条件type=unique_subquery 用于在in模式查问,子查问返回不反复的惟一值(略)type=index_subquery 用于in模式子查问用到辅助索引或者in常数列表,子查问可能返回反复值 (略)type=range 字段上有索引的范畴扫描: 常见在索引字段应用 >, <, is null, between, in, like等运算符的查问中;type=index_merge 应用了2个以上的索引, 常见: and, or的多个列都有索引, 依据索引查出来进行合并;type=index 索引从头到尾扫一遍; 不必查表了; select name from t2; name上有索引, 然而不指定where条件;type=all 性能最差: 全表扫描数据, 而后在server层返回数据过滤返回合乎的数据; 5.1 type=system 最高效
type=system 表只有1行数据 或是空表; 且只用于 myisam和memory表; innodb的type列通常为all或index;
5.2 type=const 惟一索引或主键+返回一行记录时
应用惟一索引或主键, 返回后果肯定是1行记录的等值where条件时, type=const通常
先来一个一般索引:
mysql> alter table t2 add index(id);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t2 where id=2;+----+---------+--------+| id | name | price |+----+---------+--------+| 2 | 商品2 | 200.87 |+----+---------+--------+1 row in set (0.00 sec)mysql> explain select * from t2 where id=2;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | t2 | NULL | ref | id | id | 8 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)看到type=试试ref; 不是const;
改为惟一索引试试, 先删掉一般索引
mysql> show index from t2;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t2 | 1 | id | 1 | id | A | 10 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)mysql> drop index id on t2;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t2;Empty set (0.00 sec)新建惟一索引, 再试试:
mysql> alter table t2 add unique(id);Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from t2 where id>3;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t2 | NULL | range | id | id | 8 | NULL | 7 | 100.00 | Using where |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from t2 where id=2;+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | t2 | NULL | const | id | id | 8 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)id>3是 type=range; id=2时, 惟一索引, type=const
5.3 type=eq_ref 多表join查问时, 对于前表的每一行数据, 后表中只返回一行匹配
阐明: t2和goods表中数据是一样的, 只是goods中有近15万条数据, t2中只有10条且t2表id有unique索引;
mysql> explain select * from t2 left join goods on t2.id=goods.id;+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL || 1 | SIMPLE | goods | NULL | eq_ref | id | id | 8 | test.t2.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from t2 right join goods on t2.id=goods.id;+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+| 1 | SIMPLE | goods | NULL | ALL | NULL | NULL | NULL | NULL | 149548 | 100.00 | NULL || 1 | SIMPLE | t2 | NULL | eq_ref | id | id | 8 | test.goods.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> 5.4 type=ref 多表的 join 查问, 非惟一或非主键索引, 或者是应用了最左前缀规定索引的查问.
mysql> explain select * from film where title = 'film2';+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+| 1 | SIMPLE | film | NULL | ref | idx_title | idx_title | 514 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql>title列不是惟一索引, 也不是主键列; 然而它也是索引列:
KEYidx_title(title),
5.5 type=fulltext 用到全文索引
全文索引的优先级很高, 如果全文索引和一般索引同时存在 ,mysql 不论性能代价, 会优先应用全文索引;
5.6 type=ref_or_nul 理论用的少: 相似ref, 但减少了null值的比拟
mysql> alter table t2 add index(name);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from t2 where name='商品1' or name is null;+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | t2 | NULL | ref_or_null | name | name | 33 | const | 2 | 100.00 | Using index condition |+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)如果没有 null判断, 就是ref了;
5.7 type=unique_subquery 有索引:在where中in,子查问返回不反复的惟一值
5.8 type=index_subquery 用于in模式子查问用到辅助索引或者in常数列表,子查问可能返回反复值
5.9 type=range 有索引的范畴扫描: 常见在索引字段应用 >, <, is null, between, in, like等运算符的查问中;
5.10 type=index_merge 应用了2个以上的索引, 常见: and, or的多个列都有索引, 依据索引查出来进行合并;
5.11 type=index 索引从头到尾扫一遍; 不查表; select name from t2; name上有索引, 然而不指定where条件;
5.12 type=all 性能最差: 全表扫描数据, 而后在server层返回数据过滤返回合乎的数据;
小结: 好的查问个别至多达到 range级别, 最好达到ref;
放大下范畴: 常见的有:
system/const/eq_ref/ref/range/index/all
6. possible_keys=可能用到的索引:以后这一步有可能用到的索引有哪些,都列出来
possible_keys:NULL
7. key=索引: 确定用了的索引
key:NULL
8. key_len=索引长度: 越小越好(越短越好)
key_len:NULL
9. ref=索引具体在哪一列上
ref:NULL
9.1 如果应用的常数等值查问, 会显示const;
9.2 如果是连贯查问, 被驱动表的执行打算此处会显示 驱动表的关联字段;
mysql> explain select * from t2 left join goods on t2.id=goods.id;+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL || 1 | SIMPLE | goods | NULL | eq_ref | id | id | 8 | test.t2.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)9.3 如果是条件应用了表达式或函数, 或条件列产生了外部隐式转换, 此处显示为func;
mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+| 1 | PRIMARY | film_category | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | 100.00 | Using index || 3 | DEPENDENT UNION | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | func | 5 | 100.00 | Using index |+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+10. rows=以后这一步可能会检测的行数
rows:1000
估算行, 非准确值;
11. filtered=过滤后返回数据的百分比: 通过server过滤后理论返回客户端的百分比
filtered:100.00
12. Extra=扩大信息: 有没有排序/有没有用长期表, 很多种类型
Extra:NULL
12.1 no table used
mysql> explain select 1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.00 sec)12.2 using index 应用到了索引
mysql> explain select name from t2 ;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t2 | NULL | index | NULL | name | 33 | NULL | 10 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)12.3 NULL: 查问到的列有未被索引笼罩到的(就是查了几个列, 其中有的没索引)
mysql> explain select name, price from t2 ;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)不是纯正用索引, 然而也用到了索引;
或者, 就是未被索引笼罩到; 查了没用索引的列, 也都是 NULL;
12.4 using where 查问的where条件是没有索引的
mysql> explain select price from t2 where price=2;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select price from t2 where name='s' and price=2;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| 1 | SIMPLE | t2 | NULL | ref | name | name | 33 | const | 1 | 10.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)where 语句中有的列没有索引;
12.5 using where using index: 查问的合乎索引,然而不是第一个列, 用不到索引
mysql> alter table t2 add index(name, price);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from t2 where price=2;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | t2 | NULL | index | NULL | name_2 | 42 | NULL | 10 | 10.00 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)相当于又找到索引, 又用了where, 为什么呢? 有索引, 然而用不到, 最初还是用了where扫表;