mysql explain

最新推荐文章于 2024-08-17 11:26:03 发布
最新推荐文章于 2024-08-17 11:26:03 发布
阅读量987 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 索引
原文链接：http://blog.chinaunix.net/uid-540802-id-3419311.html
版权

文章目录

引言

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
先看一个例子：

mysql> explain select * from t_order; 
explain select sql_no_cache  * from user; 不走cache
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderALLNULLNULLNULLNULL100453

1 row in set (0.03 sec)
加上extended后之后：
mysql> explain extended select * from t_order;

idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEt_orderALLNULLNULLNULLNULL100453100.00

1 row in set, 1 warning (0.00 sec)

解释

列名解释
idSELECT识别符。这是SELECT的查询序列号,id越大优先级越高，先执行,id号的每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好
select_typeSELECT类型,可以为以下任何一种:
SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENTUNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个
SELECTDEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询DERIVED:导出表的SELECT(FROM子句的子查询)
table输出的行所引用的表
partitions代表分区表的命中情况，非分区表，该项为null
type联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery: 该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)range:只检索给定范围的行,使用一个索引来选择行。
index:出现idex是sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。（select 后面用了索引，但是where后面没有用到，需要优化，使得where后面用到索引）。该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:**一定要避免全表扫描，**对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys指出MySQL能使用哪个索引在该表中找到行
key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_lenwhere后面的字段命中索引的长度，表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。值越小越好。key_len决定了索引项在存储空间占用的大小，越小意味着一个磁盘扇区能够放置的索引项越多（除了叶），从而可以降低B+树的高度，高度低就意味着查找时所搜索的路径越少，比如一个三层B+树，从根到叶节点只需2步，而四层就需要3步了，而搜索的路径少就意味着磁盘IO读取次数少（如果没有全放内存的话），自然就提高查询的效率了。
ref显示使用哪个列或常数与key一起从表中选择行。
rows显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。越小越好。
filtered显示了通过条件过滤出的行数的百分比估计值。
Extra该列包含MySQL解决查询的详细信息
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:order by 没有用上索引需要建索引优化，MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列息。
Using temporary:group by没有用上索引，需要优化(包含Using filesort，先order by 后group by)，为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
Using join bufbuffer：join 没有用上索引，需要优化。

key_len 长度计算

where 后面用到的索引计算，不包含order by等。

  1. 先看索引上的字段类型+长度。比如int = 4;varchar(20)=20
  2. 如果是varchar或者char 这种字符串字段，看字符集，比如utf-8要乘以3，GBK要乘以2,utf8mb4一个字符占用4个字节。
  3. varchar这种动态字符串要加2个字节
  4. 允许为空的字段要加一个字节

select_type

UNION

当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。
mysql> explain select * from t_order where order_id=100 union select * from t_order where order_id=200;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYt_orderconstPRIMARYPRIMARY4const1
2UNIONt_orderconstPRIMARYPRIMARY4const1
NULLUNION RESULT<union1,2>ALLNULLNULLNULLNULLNULL

3 rows in set (0.34 sec)

DEPENDENT UNION与DEPENDENT SUBQUERY

当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。
第一个子查询的select_type则是DEPENDENT SUBQUERY。
mysql> explain select * from t_order where order_id in (select order_id from t_order where order_id=100 union select order_id from t_order where order_id=200);

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYt_orderALLNULLNULLNULLNULL100453Using where
2DEPENDENT SUBQUERYt_orderconstPRIMARYPRIMARY4const1Using index
3DEPENDENT UNIONt_orderconstPRIMARYPRIMARY4const1Using index
NULLUNION RESULT<union2,3>ALLNULLNULLNULLNULLNULL

4 rows in set (0.03 sec)

SUBQUERY

子查询中的第一个select其select_type为SUBQUERY。
mysql> explain select * from t_order where order_id=(select order_id from t_order where order_id=100);

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYt_orderconstPRIMARYPRIMARY4const1
2SUBQUERYt_orderconstPRIMARYPRIMARY41Using index

2 rows in set (0.03 sec)

DERIVED:

当子查询是from子句时，其select_type为DERIVED。
mysql> explain select * from (select order_id from t_order where order_id=100) a;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYsystemNULLNULLNULLNULL1
2DERIVEDt_orderconstPRIMARYPRIMARY41Using index

2 rows in set (0.03 sec)

type

system,const

见上面4.DERIVED的例子。其中第一行的type就是为system，第二行是const，这两种联接类型是最快的。

eq_ref

在t_order表中的order_id是主键，t_order_ext表中的order_id也是主键，该表可以认为是订单表的补充信息表，他们的关系是1对1，在下面的例子中可以看到b表的连接类型是eq_ref，这是极快的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEbALLorder_idNULLNULLNULL1
1SIMPLEaeq_refPRIMARYPRIMARY4test.b.order_id1Using where

2 rows in set (0.00 sec)

ref

下面的例子在上面的例子上略作了修改，加上了条件。此时b表的联接类型变成了ref。因为所有与a表中order_id=100的匹配记录都将会从b表获取。这是比较常见的联接类型。
mysql> explain select * from t_order a,t_order_ext b where a.order_id=b.order_id and a.order_id=100;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEaconstPRIMARYPRIMARY4const1
1SIMPLEbreforder_idorder_id4const1

2 rows in set (0.00 sec)

ref_or_null

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

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderref_or_nulluser_iduser_id5const50325Using where

1 row in set (0.00 sec)

index_merge

经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起，如下例:
mysql> explain select * from t_order where order_id=100 or user_id=10;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderindex_mergePRIMARY,user_idPRIMARY,user_id4,5NULL2Using union(PRIMARY,user_id); Using where

1 row in set (0.09 sec)

unique_subquery

该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。
mysql> explain select * from t_order where order_id in (select order_id from t_order where user_id=10);

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYt_orderALLNULLNULLNULLNULL100649Using where
2DEPENDENT SUBQUERYt_orderunique_subqueryPRIMARY,user_idPRIMARY4func1Using where

2 rows in set (0.00 sec)

index_subquery

该联接类型与上面的太像了，唯一的差别就是子查询查的不是主键而是非唯一索引。
mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10);

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYt_orderALLNULLNULLNULLNULL100649Using where
2DEPENDENT SUBQUERYt_orderindex_subqueryPRIMARY,user_iduser_id5func50324Using index; Using where

2 rows in set (0.00 sec)

range

按指定的范围进行检索，很常见。
mysql> explain select * from t_order where user_id in (100,200,300);

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderrangeuser_iduser_id5NULL3Using where

1 row in set (0.00 sec)

index

在进行统计时非常常见，此联接类型实际上会扫描索引树，仅比ALL快些。
mysql> explain select count(*) from t_order;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderindexNULLuser_id5NULL100649Using index

1 row in set (0.00 sec)

ALL

完整的扫描全表，最慢的联接类型，尽可能的避免。
mysql> explain select * from t_order;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderALLNULLNULLNULLNULL100649

1 row in set (0.00 sec)

extra

Distinct

MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子，求指点。

Not exists

因为b表中的order_id是主键，不可能为NULL，所以mysql在用a表的order_id扫描t_order表，并查找b表的行时，如果在b表发现一个匹配的行就不再继续扫描b了，因为b表中的order_id字段不可能为NULL。这样避免了对b表的多次扫描。
mysql> explain select count(1) from t_order a left join t_order_ext b on a.order_id=b.order_id where b.order_id is null;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEaindexNULLexpress_type1NULL100395Using index
1SIMPLEbreforder_idorder_id4test.a.order_id1Using where; Using index; Not exists

2 rows in set (0.01 sec)

Range checked for each record

这种情况是mysql没有发现好的索引可用，速度比没有索引要快得多。
mysql> explain select * from t_order t, t_order_ext s where s.order_id>=t.order_id and s.order_id<=t.order_id and t.express_type>5;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtrangePRIMARY,express_typeexpress_type1NULL1Using where
1SIMPLEsALLorder_idNULLNULLNULL1Range checked for each record (index map: 0x1)

2 rows in set (0.00 sec)

Using filesort

需要优化，在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。
mysql> explain select * from t_order order by express_type;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderALLNULLNULLNULLNULL100395Using filesort

1 row in set (0.00 sec)

如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序。

Using index

这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。上面的例子中有很多这样的例子，不再多举例了。

Using temporary

发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。
mysql> explain select * from t_order a left join t_order_ext b on a.order_id=b.order_id group by b.order_id;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEaALLNULLNULLNULLNULL100395Using temporary; Using filesort
1SIMPLEbreforder_idorder_id4test.a.order_id1

2 rows in set (0.00 sec)

Using where

表示优化器需要通过索引回表查询数据；

Using index

覆盖索引。没有索引时候，select * 改成select 列会走覆盖索引。表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；

Using index condition

在5.6版本后加入的新特性（Index Condition Pushdown）;
Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

Using sort_union(…)/Using union(…)/Using intersect(…)

下面的例子中user_id是一个检索范围，此时mysql会使用sort_union函数来进行索引的合并。而当user_id是一个固定值时，请参看上面type说明5.index_merge的例子，此时会使用union函数进行索引合并。
mysql> explain select * from t_order where order_id=100 or user_id>10;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderindex_mergePRIMARY,user_iduser_id,PRIMARY5,4NULL2Using sort_union(user_id,PRIMARY); Using where

1 row in set (0.00 sec)
对于Using intersect的例子可以参看下例，user_id与express_type发生了索引交叉合并。
mysql> explain select * from t_order where express_type=1 and user_id=100;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderindex_mergeuser_id,express_typeuser_id,express_type5,1NULL1Using intersect(user_id,express_type); Using where

1 row in set (0.00 sec)

Using index for group-by

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。
mysql> explain select user_id from t_order group by user_id;

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt_orderrangeNULLuser_id5NULL3Using index for group-by

1 row in set (0.00 sec)
除了上面的三个说明，还需要注意rows的数值，多行之间的数值是乘积的关系，可以估算大概要处理的行数，如果乘积很大，那就很有优化的必要了。

参考：
http://blog.chinaunix.net/uid-540802-id-3419311.html
http://blog.csdn.net/zhuxineli/article/details/14455029
http://www.cnblogs.com/xiaoboluo768/p/5400990.html
https://www.cnblogs.com/gomysql/p/4004244.html (key_len)

春天的早晨
关注
专栏目录
Mysql Explain
02-28
Mysql Explain
Mysql Explain 详细介绍
09-09
MySQL的EXPLAIN命令是数据库管理员和开发者用于优化SQL查询性能的重要工具。它提供了一种查看MySQL如何执行SELECT语句的方法，帮助我们理解查询计划、索引使用情况以及潜在的性能瓶颈。下面是对MySQL EXPLAIN的详细...
Mysql Explain 详解 [原创]
cixin1802的博客
08-06 804
一.语法 explain < table_name > 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +----+---------...
MySQL中的EXPLAIN的详解
最新发布
SHI940637335的博客
08-17 4942
是一种优化技术，允许 MySQL 在存储引擎层面应用部分。
关于mysql中explain的那些事儿
东南风的专栏
12-29 628
有两种用法： 1.EXPLAIN tbl_name 2.EXPLAIN [EXTENDED] SELECT select_options 为了更好的说明它，我们需要建两张表，下面的语句用于创建一张测试用的订单表： CREATE TABLE `t_order` (  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMME
[转]MySQL Explain详解
weixin_34417200的博客
10-09 1006
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略...
mysql的sql执行计划详解（非常有用）
热门推荐
Gavin's Blog
05-11 2万+
引言： 实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有 sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运 行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优
mysqlexplain.ppt
09-13
MySQL的EXPLAIN命令是数据库管理员和开发者用来分析SQL查询执行计划的重要工具。它能帮助我们理解MySQL如何处理查询，从而优化查询性能。以下是对EXPLAIN命令及其相关知识点的详细解释。 1. **EXPLAIN调用方式**： ...
关于MySQL EXPLAIN key_len的计算
12-14
关于EXPLAIN的key_len 我们先看一个表的设计问题 可以看到上图中id、loginName、name、password这几个字段都设计成225长度的varchar字符串。其中id还是主键字段。 根据阿里巴巴的java代码规约，这里的字段命名有...
Mysql explain
regrethh的博客
04-11 794
用explain来获取关于查询执行计划的信息。比如mysql中有慢查询语句，可以通过explian来返回其执行计划中的信息，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。 explain命令 EXPLAIN select * from address; explain 的列 执行结果 id select_type ta...
mysql-explain
peng0107的博客
10-22 656
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如：explain select surname,first_name form a,b where a.id=b.id 下面解释下explain执行计划qn包含信息： id:select查询的一个序列号，包含一组...
MySql explain
咸鱼的博客
08-24 186
mysql&amp;gt; explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | s...
MySQL Explain详解
s1070的专栏
05-20 366
列名 描述 说明 相关链接 id 若没有子查询和联合查询，id则都是1。 Mysql会按照id从大到小的顺序执行query，在id相同的情况下，则从上到下执行。   select_type select类型。     table
Mysql explain解释
Ysh-uestc
10-05 188
https://blog.csdn.net/wuseyukui/article/details/71512793 https://segmentfault.com/a/1190000008131735 https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html
Mysql关键字Explain 性能优化神器
weixin_31351409的博客
08-26 129
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。在select语句之前增加explaion关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行SQL。 Explaion分析示例 -- actor建表语句： CREATE TABLE `actor` ( `id` int(11) NOT NULL, `...
mysql之explain
王者之峰
05-15 2277
explain是提供一些信息关于增删查改(mysql 5.6之前只支持查)是如何执行的命令。 执行结果列下面说明下。 下面例子测试的表 CREATE TABLE `test_people` ( `people_id` bigint(20) NOT NULL AUTO_INCREMENT, `last_name` varchar(50) NOT NULL, `fist_name...
MySQL EXPLAIN详解
poorCoder_的博客
05-17 1507
MySQL Explain 是什么使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 分析功能 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 使用EXPALIN + SQL 语句 执行所包含的信息: id | select_typ
MySQL：explain
zjh的博客
08-16 152
一、Explain 用法 explain模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 语法：Explain + SQL 语句;如：Explain select * from user; 会生成如下 SQL 分析结果，下面详细对每个字段进行详解 二、id 是一组数字，代表多个表之间的查询顺序，或者包含子句查询语句中的顺序，id 总共分为三种情况，依次详解 id 相同，执行顺序由上至下 id 不同，如果是子查询，id
MySQL explain
08-17
MySQL Explain是一个SQL命令前缀，用于分析查询计划，它可以帮助开发者理解数据库如何执行一条SQL语句。当你在SELECT语句前面加上EXPLAIN，MySQL会返回一个表格，列出了服务器将如何解析、优化和执行这个查询，包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值