使用Explain分析select语句
explain可以分析某条select语句会查询多少条记录、以怎样的方式查询,以及复杂select的执行顺序,借此可以了解到select语句的性能和查询是如何执行的
10年积累的做网站、成都网站建设经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先网站制作后付款的网站建设流程,更有元江县免费网站建设让你可以放心的选择与我们合作。
如: 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=((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列是
当explain输出 select_type为derived时,表示一个嵌套范围的开始,如果后面的id较小,代表嵌套已结束;
subquery : select 子句
union : union中的第二个或后面的select语句
union result: union的结果
4> table
表示对应行正在访问的表;当from子句中有子查询或有union时,table列会变得复杂;
当from子句中有子查询时,table列是
5> key
这一列显示的是优化采用的哪一个索引可以最小化查询成本;不一定出现在 possible_keys中
6> ref
这一列显示了在key列记录的索引中查找值所用的列或常量,值为null时仅表示啥都没使用;
网站栏目:使用Explain分析select语句
文章路径:http://myzitong.com/article/jchjch.html