mysql中explain

1.select_type:

/* select_type 使用 SIMPLE */
explain select * from tb_shop_order where id='20160329257032899';

/* select_type 使用 PRIMARY --最外面的select
select_type 使用 SUBQUERY --子查询第一个select
*/
explain select gorder_id,order_time from tb_shop_order where order_time = (select max(order_time) from tb_shop_order);

/*select_type 使用UNION 和 UNION RESULT*/
explain select * from tb_shop_order where order_time='2015-04-27 11:41:46' union select * from tb_shop_order where order_time='2015-04-27 11:41:47' ;

/*select_type 使用 DEPENDENT SUBQUERY 表示子查询中的第一个SELECT,取决于外面的查询 */
explain select * from tb_shop_order a where a.id in (select b.id from tb_shop_order b where b.gorder_id='785948');

2.type
/*type 使用ref和 eq_ref */
explain select * from tb_shop_order a left join tb_shop_gorder b on a.`gorder_id`= b.id where a. product_type='0701';

/* type使用system --效率最好，表示表只有一行记录*/
explain select * from tb_shop_gorder_sequence where stub='a';

/* type 使用const */
explain select * from tb_shop_order where id='20160329257032899';

/* type 使用 index */
explain select gorder_id from tb_shop_order;
explain select * from tb_shop_order order by id desc;
explain select merchant_id,avg(pay_amount) from tb_shop_order group by merchant_id order by avg(pay_amount) desc;

/* type 使用range，检索给定范围的行，需要使用一个索引的字段来筛选 */
explain select * from tb_shop_order where order_time between '2015-01-01 00:00:00' and '2015-05-01 00:00:00';
explain select * from elong_ihotel_activity.`tb_ihotel_activity_detail` t where t.`coupon_code` in ('TEST1234','NB123456');

/*type 使用DERIVED == 当子查询是from子句时 */
explain select * from ( select merchant_id,merchant_name,avg(pay_amount) from tb_shop_order group by merchant_id,merchant_name order by avg(pay_amount) desc) as table1 limit 2;

/*type 使用ref_or_null merchant_order_id 是一个空字段，并对该字段创建了一个索引，在下面的查询中可以看到联接类型为ref_or_null，
这是mysql为含有null的字段专门做的处理。在我们的表设计中应当尽量避免索引字段为NULL，因为这会额外的耗费mysql的处理时间来做优化。 */
explain select * from tb_shop_order where merchant_order_id ='2016666666666666' or merchant_order_id is null;

/*type 使用index_merge 表示使用了索引合并 */
explain select * from tb_shop_order where merchant_order_id ='2016666666666666' or gorder_id ='784736';

/*type 使用unique_subquery
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) */
explain select * from tb_shop_order a where a.id in (select b.id from tb_shop_order b where b.gorder_id='785948');

/*type 使用index_subquery
该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:
value IN (SELECT key_column FROM single_table WHERE some_expr)该类型替换了下面形式的IN子查询的ref: value IN */
explain select * from tb_shop_order a where a.gorder_id in (select b.gorder_id from tb_shop_order b where b.`buy_account_id`='190000032101145811');

3.extra

/*extra 使用 Select tables optimized away */
explain select max(gorder_id) from tb_shop_order;

/*extra 使用 Select tables optimized away */
explain select max(id) from tb_shop_order;

/*extra 使用 Using index */
explain select count(*) from tb_shop_order;
explain select gorder_id from tb_shop_order;

/*extra 使用 Using union(ix_order_moid,ix_order_gorder_id); Using where */
explain select * from tb_shop_order where merchant_order_id ='2016666666666666' or gorder_id ='784736';

/*extra 使用Using temporary; --为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using filesort --MySQL需要额外的一次传递,以找出如何按排序顺序检索行。*/
explain select merchant_id,avg(pay_amount) from tb_shop_order group by merchant_id order by avg(pay_amount) desc;

/*Using filesort*/
explain select * from tb_shop_order order by order_time desc;

/*extra Using index for group-by :表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。*/
explain select buy_account_id from tb_shop_order t group by t.`buy_account_id`;

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain-extra-information --官方文档

http://ustb80.blog.51cto.com/6139482/1064261 --执行计划参考

巴特西

mysql中explain

最新文章

热门文章