使用Explain分析select语句

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时仅表示啥都没使用；