MySql学习笔记(五):explain-数据读取操作的操作类型
explain命令如下:
创新互联坚持“要么做到,要么别承诺”的工作理念,服务领域包括:成都网站制作、做网站、企业官网、英文网站、手机端网站、网站推广等服务,满足客户于互联网时代的肥城网站设计、移动媒体设计的需求,帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴!
MySQL> explain select * from t_blog; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_blog | ALL | NULL | NULL | NULL | NULL | 7 | | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set
其中select_type列指明该条SQL的读取操作的操作类型。
select_type共有六种类型:simple、primmy、subQuery、derived、union、union result。
1、simple
表示该条sql是简单的select,不包含任何子查询和union,例:
mysql> explain select * from t_blog; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_blog | ALL | NULL | NULL | NULL | NULL | 7 | | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set
2、primmy
查询中如果包含了任何一个子查询,最外层的查询就会被标记为primmy,例:
mysql> explain select * from t_blog where id = (select id from t_type where name = "JAVA"); +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | t_blog | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 2 | SUBQUERY | t_type | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set
这条sql一共读取了两张表,t_type作为子查询被加载,t_blog作为最外部的读取操作,被标记为 PRIMMY。
3、subquery
查询中,在select或where自居中包含了子查询,该子查询就会被标记为subquery,如上例的t_type
4、derived
在from里列表中包含了子查询,该子查询会被标记为derived(衍生),例:
mysql> explain select * from t_blog inner join (select id from t_type) a on t_blog.typeId = a.id; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY || ALL | NULL | NULL | NULL | NULL | 4 | | | 1 | PRIMARY | t_blog | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 2 | DERIVED | t_type | index | NULL | PRIMARY | 4 | NULL | 4 | Using index | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 3 rows in set
t_type表读取时,出现在from语句中,因此被标记为derived。mysql会先将from语句的子查询的查询结果放到一张临时表中,然后再将最终的结果返回,所以,是很耗费内存的一种操作。值得注意的是,在第一行的table列出现了
5、union
若sql中包含了union,第二个select会被标记为union
mysql> explain select * from t_blog b left join t_type t on b.typeId = t.id union select * from t_blog b1 right join t_type t1 on b1.typeId = t1.id; +------+--------------+------------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 7 | | | 1 | PRIMARY | t | eq_ref | PRIMARY | PRIMARY | 4 | blog.b.typeId | 1 | | | 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 4 | | | 2 | UNION | b1 | ALL | NULL | NULL | NULL | NULL | 7 | | | NULL | UNION RESULT || ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+------------+--------+---------------+---------+---------+---------------+------+-------+ 5 rows in set
t1表和t2表都出现在union后的select,因此都被标及为union
6、union result
表示该查询是从union表中获取结果的select,是union的结果集,如上例,上条select就是获取的union的结果集
分享文章:MySql学习笔记(五):explain-数据读取操作的操作类型
转载来源:http://scyanting.com/article/iphisi.html