(关于explain使用:比较好的例子)
重点是第二种用法,需要深入的了解。
先看一个例子:
- mysql> explain select * from t_order;
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100453 | |
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- 1 row in set (0.03 sec)
加上extended后之后:
- mysql> explain extended select * from t_order;
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100453 | 100.00 | |
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
- 1 row in set, 1 warning (0.00 sec)
有必要解释一下这个长长的表格里每一列的含义:
id | SELECT识别符。这是SELECT的查询序列号 |
select_type |
SELECT类型,可以为以下任何一种:
|
table |
输出的行所引用的表 |
type |
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
possible_keys |
指出MySQL能使用哪个索引在该表中找到行 |
key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
ref | 显示使用哪个列或常数与key一起从表中选择行。 |
rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
Extra |
该列包含MySQL解决查询的详细信息
|
一.select_type的说明
1.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;
- +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
- | 1 | PRIMARY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
- | 2 | UNION | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
- | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
- +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
- 3 rows in set (0.34 sec)
2.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);
- +----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
- | 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100453 | Using where |
- | 2 | DEPENDENT SUBQUERY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
- | 3 | DEPENDENT UNION | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
- | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
- +----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+
- 4 rows in set (0.03 sec)
3.SUBQUERY:
子查询中的第一个select其select_type为SUBQUERY。
- mysql> explain select * from t_order where order_id=(select order_id from t_order where order_id=100);
- +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
- | 1 | PRIMARY | t_order | const | PRIMARY | PRIMARY | 4 | const | 1 | |
- | 2 | SUBQUERY | t_order | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
- +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
- 2 rows in set (0.03 sec)
4.DERIVED:
当子查询是from子句时,其select_type为DERIVED。
- mysql> explain select * from (select order_id from t_order where order_id=100) a;
- +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
- | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
- | 2 | DERIVED | t_order | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
- +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
- 2 rows in set (0.03 sec)
二.type的说明
1.system,const
见上面4.DERIVED的例子。其中第一行的type就是为system,第二行是const,这两种联接类型是最快的。
2.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;
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
- | 1 | SIMPLE | b | ALL | order_id | NULL | NULL | NULL | 1 | |
- | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.order_id | 1 | Using where |
- +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+
- 2 rows in set (0.00 sec)
3.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;
- +----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
- | 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
- | 1 | SIMPLE | b | ref | order_id | order_id | 4 | const | 1 | |
- +----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+
- 2 rows in set (0.00 sec)
4.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;
- +----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
- | 1 | SIMPLE | t_order | ref_or_null | user_id | user_id | 5 | const | 50325 | Using where |
- +----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+
- 1 row in set (0.00 sec)
5.index_merge
经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起,如下例:
- mysql> explain select * from t_order where order_id=100 or user_id=10;
- +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
- | 1 | SIMPLE | t_order | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5 | NULL | 2 | Using union(PRIMARY,user_id); Using where |
- +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
- 1 row in set (0.09 sec)
6.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);
- +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
- | 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
- | 2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4 | func | 1 | Using where |
- +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+
- 2 rows in set (0.00 sec)
7.index_subquery
该联接类型与上面的太像了,唯一的差别就是子查询查的不是主键而是非唯一索引。
- mysql> explain select * from t_order where user_id in (select user_id from t_order where order_id>10);
- +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
- | 1 | PRIMARY | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | Using where |
- | 2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5 | func | 50324 | Using index; Using where |
- +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+
- 2 rows in set (0.00 sec)
8.range
按指定的范围进行检索,很常见。
- mysql> explain select * from t_order where user_id in (100,200,300);
- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
- | 1 | SIMPLE | t_order | range | user_id | user_id | 5 | NULL | 3 | Using where |
- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
9.index
在进行统计时非常常见,此联接类型实际上会扫描索引树,仅比ALL快些。
- mysql> explain select count(*) from t_order;
- +----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
- | 1 | SIMPLE | t_order | index | NULL | user_id | 5 | NULL | 100649 | Using index |
- +----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
- 1 row in set (0.00 sec)
10.ALL
完整的扫描全表,最慢的联接类型,尽可能的避免。
- mysql> explain select * from t_order;
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100649 | |
- +----+-------------+---------+------+---------------+------+---------+------+--------+-------+
- 1 row in set (0.00 sec)
三.extra的说明
1.Distinct
MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子,求指点。
2.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;
- +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
- | 1 | SIMPLE | a | index | NULL | express_type | 1 | NULL | 100395 | Using index |
- | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | Using where; Using index; Not exists |
- +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+
- 2 rows in set (0.01 sec)
3.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;
- +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
- | 1 | SIMPLE | t | range | PRIMARY,express_type | express_type | 1 | NULL | 1 | Using where |
- | 1 | SIMPLE | s | ALL | order_id | NULL | NULL | NULL | 1 | Range checked for each record (index map: 0x1) |
- +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+
- 2 rows in set (0.00 sec)
4.Using filesort
在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行。
- mysql> explain select * from t_order order by express_type;
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 100395 | Using filesort |
- +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+
- 1 row in set (0.00 sec)
5.Using index
这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时,就会出现。上面的例子中有很多这样的例子,不再多举例了。
6.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;
- +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
- | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 100395 | Using temporary; Using filesort |
- | 1 | SIMPLE | b | ref | order_id | order_id | 4 | test.a.order_id | 1 | |
- +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+
- 2 rows in set (0.00 sec)
7.Using where
当有where子句时,extra都会有说明。
8.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;
- +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+
- | 1 | SIMPLE | t_order | index_merge | PRIMARY,user_id | user_id,PRIMARY | 5,4 | NULL | 2 | Using 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;
- +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
- | 1 | SIMPLE | t_order | index_merge | user_id,express_type | user_id,express_type | 5,1 | NULL | 1 | Using intersect(user_id,express_type); Using where |
- +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+
- 1 row in set (0.00 sec)
9.Using index for group-by
表明可以在索引中找到分组所需的所有数据,不需要查询实际的表。
- mysql> explain select user_id from t_order group by user_id;
- +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
- | 1 | SIMPLE | t_order | range | NULL | user_id | 5 | NULL | 3 | Using index for group-by |
- +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
- 1 row in set (0.00 sec)
除了上面的三个说明,还需要注意rows的数值,多行之间的数值是乘积的关系,可以估算大概要处理的行数,如果乘积很大,那就很有优化的必要了。
相关推荐
Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。
EXPLAIN用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出
使用方法,在select语句前加上explain就可以了: explain select count(DISTINCT uc_userid) as user_login from user_char_daily_gameapp_11 where uc_date >= "2017-09-04" and uc_date<="2017-09-08" AND
mysql explain可以帮助你检查索引和更好的优化查询语句,今天特地学习了下mysql explain的用法,需要的朋友可以参考下
mysql、其他数据库explain的用法
使用方法,在select语句前加上explain就可以了: 如: mysql> explain select * from kt_course order by create_time desc; +----+-------------+-----------+------+---------------+------+---------+------+---...
mysql explain命令用于显示mysql如何使用索引来处理select语句...explain 的使用方法,在select语句前加上explain就可以了, 如: explain select * form codetc; 将得到一个如下的结果: 下面我们对 explain 结果列
主要给大家介绍了关于mysql学习之引擎、Explain和权限的相关资料,文中通过示例代码将引擎、Explain和权限介绍的非常详细,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习...
使用方法,在select语句前加上explain就可以了,如: explain select * from statuses_status where id=11; explain列的解释 table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型...
explain命令是查看查询优化器如何决定执行查询的主要方法。 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的。 1、什么是MySQL执行计划 ...
本文实例讲述了MYSQL 性能分析器 EXPLAIN 用法。分享给大家供大家参考,具体如下: 使用方法: EXPLAIN SELECT * FROM user; 环境和数据准备 -- 查看 MySQL 版本 SELECT VERSION(); -- MySQL 提供什么存储引擎 ...
突出显示有问题MySQL解释结果。 受启发。 安装 将此行添加到您的应用程序的Gemfile中: gem 'mysql_casual_explain' 然后执行: $ bundle install 或将其自己安装为: $ gem install mysql_casual_explain ...
1.5.5. MaxDB和MySQL之间的特性差异 1.5.6. MaxDB和MySQL之间的协同性 1.5.7. 与MaxDB有关的链接 1.6. MySQL发展大事记 1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统...
1.5.5. MaxDB和MySQL之间的特性差异 1.5.6. MaxDB和MySQL之间的协同性 1.5.7. 与MaxDB有关的链接 1.6. MySQL发展大事记 1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 1.7.1. MySQL邮件列表 1.7.2. IRC(在线聊天系统...
前言 1. 一般信息 1.1. 关于本手册 1.2. 本手册采用的惯例 1.3. MySQL AB概述 1.4. MySQL数据库管理系统概述 1.4.1. MySQL的历史 1.4.2. MySQL的的主要特性 ...10.2. MySQL中的字符集和校对
使用方法,在select语句前加上explain就可以了: 如:explain select * from test1 EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型...
目录 前言 1. 一般信息 1.1. 关于本手册 ...1.5.5. MaxDB和MySQL之间的特性差异 1.5.6. MaxDB和MySQL之间的协同性 1.5.7. 与MaxDB有关的链接 1.6. MySQL发展大事记 1.6.1. MySQL 5.1的新特性 1.7. MySQL信息源 ...