explain可以分析某条select语句会查询多少条记录、以怎样的方式查询,以及复杂select的执行顺序,借此可以了解到select语句的性能和查询是如何执行的

如: select子句和from子句,先执行from子句

ps: 我们的服务器上mysql版本是5.1.73,mysql 5.6 explain能对update、insert等进行解释


第一步:先插入大量数据,因为explain的结果和数据库实际的数据有关系

delimiter $$                   
drop procedure if exists addoplist;
create procedure addoplist(in mpoint int, in mproductid int, in mnum int)
begin
declare id int;
declare maid int;
declare msid int;
declare mpid int;
set id=0;
while id<mnum do
select aid, sid, rid into maid, msid, mpid from tbl_roles where aid>=((select max(aid) from tbl_roles) - (select min(aid) from tbl_roles))*rand() + (select min(aid) from tbl_roles) limit 1;
insert table_oplist(optype, aid, sid, pid, optime, rid, point, freeze, productid, device) values(1, maid, msid, mpid, UNIX_TIMESTAMP(), 0, mpoint, 0, mproductid, concat("qwerwqrqwrwdxcvzxvdfge", round(rand()*1000)));
set id=id+1;
end while;
end$$
delimiter ;
call addoplist(30, 1010, 300000);


第二步:expalin解释了哪些有用信息 例:explain select count(*) from table_oplist where device="qwerwqrqwrwdxcvzxvdfge52";

+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | tbl_oplist | ref  | oplist_device | oplist_device | 131     | const |  307 | Using where; Using index |

+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+

1> rows列是mysql估计为了找到所需的行而要读取的行数; 这个估算可能不精确,也反映不出limit的作用; 结果有多行时,所有rows列的值相乘来粗略估算整个查询要读的行数;

rows和数据库的实际总行数有关;

2> type列我认为是最重要的一列

ALL     :按行全表扫描;当查询中使用了limit时,并不是全表,而是找到limit的几个就不再扫描了;或者在Extra列显示“Using distinct/not exists”(暂时没见过);

index   :按索引次序全表扫描,而不是按行;当Extra列显示"Using index",说明使用的是覆盖索引,只扫描索引的数据,而不是按索引全表扫描;

range  : 相对于index来说,它是一个范围的索引扫描,不一定但通常出现在select语句中带有between或者where子句里带有>等比较符; 出现在in ()子句或or列表的情况(目前不太清楚)

ref       : 索引访问,索引跟参考值相比较,返回所有匹配行;它可能找到多个符合条件的行;查找+扫描;只有当使用非唯一索引或唯一索引的非唯一性前缀时才发生;ref_or_null也属于这类(在初次查找的结果里进行第二次查找以找出NULL条目)

eq_ref  :最多返回一条符合条件的记录,在使用主键或者唯一性索引查找时可看到;

const, system :mysql能对查询的某部分进行优化并转换成一个常量时,就会使用这个类型;例如:将主键放在where子句来选这行的主键时,就会被转换成常量

NULL   :mysql在优化阶段分解查询语句,在执行阶段甚至用不着再访问表或索引;

ref图

mysql> create index oplist_device on table_oplist(device);

mysql> explain select * from table_oplist where  device="";
+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl_oplist | ref  | oplist_device | oplist_device | 131     | const |    4 | Using where |

+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+

没有索引时:

mysql> explain select distinct aid from table_oplist where  device="";
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | tbl_oplist | ALL  | NULL          | NULL | NULL    | NULL | 5180 | Using where; Using temporary |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+

const图

mysql> explain select * from table_account where account="test02222";
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_account | const | PRIMARY       | PRIMARY | 66      | const |    1 |       |

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+


3> select_type

simple     : 简单select,不包括子查询和union

primary   : 查询中若包含任何复杂的子部分,最外层的select被标记为primary

derived    : 对应的table列是<derivedN>

当explain输出 select_type为derived时,表示一个嵌套范围的开始,如果后面的id较小,代表嵌套已结束;

subquery  : select 子句

union       : union中的第二个或后面的select语句

union result: union的结果

4> table

表示对应行正在访问的表;当from子句中有子查询或有union时,table列会变得复杂;

当from子句中有子查询时,table列是<derivedN>,N是explain输出中后面一行的id

5> key

这一列显示的是优化采用的哪一个索引可以最小化查询成本;不一定出现在 possible_keys中

6> ref

这一列显示了在key列记录的索引中查找值所用的列或常量,值为null时仅表示啥都没使用;