-
id列
select的编号从1开始
子查询则编号增加 -
select_type列
simple/primary
subquerymysql> explain select (select * from t2) from t1; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | t1 | index | NULL | name | 3 | NULL | 2 | Using index | | 2 | SUBQUERY | t2 | index | NULL | name | 4 | NULL | 2 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+derived
mysql> explain select 1 from (select 1)as a; +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+union
union result
-
table列
实际的表名
表的别名
派生表名，如derived,from型子查询
null，直接计算的结果不用走表mysql> explain select 1; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ -
type列
指查询方式，是分析“查询数据过程”的重要依据
执行效率 system > const > eq_ref > ref > range > index > ALL名称 含义 system 系统表，少量数据，往往不需要进行磁盘IO const 常量连接 eq_ref 主键索引（primary key）或者非空唯一索引（unique not null）等值扫描 ref 非主键非唯一索引等值扫描 range 范围扫描 index 索引树扫描 ALL 全表扫描（full table scan） All
全表数据扫描
意味着从表的第一行，往后逐行做全表扫描.可能扫描到最后一行mysql> explain select * from gold_log \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: gold_log type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 63118 Extra: NULLindex
全索引扫描
通俗来说，all扫描所有数据行data_all,而index扫描所有索引节点，相当于index_allmysql> explain select id from gold_log order by id \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: gold_log type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 63118 Extra: Using indexrange
索引范围扫描mysql> explain select * from gold_log where id>5 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: gold_log type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 31559 Extra: Using whereref
通过索引列，直接引用到某些数据行mysql> explain select * from gold_log where num=5 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: gold_log type: ref possible_keys: num,num2 key: num key_len: 5 ref: const rows: 1 Extra: NULLeq_ref
通过索引列，直接引用某一行数据mysql> explain select a.id,b.room_id from gold_log a inner join gold_room_log b on a.id=b.log_id where a.id >0 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 31559 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.a.id rows: 1 Extra: NULLconst,system,null
这三个分别值，查询优化到了常量级别，甚至不需要查询时间
一般按照主键查询时，易出现const,system
或者直接查找某个表达式不经过表时，出现nullmysql> explain select * from t1 where id =1; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+mysql> explain select * from (select * from t1 where id=1) a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+mysql> explain select * from t1 where id =3; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ -
possible_keys列
可能用到的索引
系统估计可能用的几个索引，最终只用一个 -
keys列
实际用到的索引 -
key_len列
使用的索引最大长度 -
ref列
连接查询时，前表与后表的引用关系 -
rows列
估计扫描多少行 -
extra列
using index
using where
using temporary，使用临时表
using filesort，使用文件排序
range checked for each record
