当前时区为 UTC + 8 小时



发表新帖 回复这个主题  [ 1 篇帖子 ] 
作者 内容
1 楼 
 文章标题 : mysql explain 初探
帖子发表于 : 2010-05-06 21:44 

注册: 2009-05-16 14:22
帖子: 198
送出感谢: 0 次
接收感谢: 0 次
来源于官网[url]:http://dev.mysql.com/doc/refman/5.1/en/using-explain.html[/url]:
我大体看了一下不是很难,就没有翻译,然后安按照自己的理解做了几个实验:
When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order:

With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in the SELECT statement, begin the statement with SELECT STRAIGHT_JOIN rather than just SELECT.

EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.
上面是整体的叙述,下面给出具体的输出结果:
# 查询id
id
The SELECT identifier. This is the sequential number of the SELECT within the query.
查询的类型:
select_type

The type of SELECT, which can be any of those shown in the following table.
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
注:DEPENDENT typically signifies the use of a correlated subquery
下面我们开始测试:我所考虑的例子都很简单
root:@localhost[nov]>explain select version() \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE


root:@localhost[nov]>explain select 1 union select 2 \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
*************************** 2. row ***************************
id: 2
select_type: UNION
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
为了考虑自查询问题,我们自己构建了两个表,很简单的,结构如下:
root:@localhost[mytest]>explain test1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s1 | int(11) | NO | PRI | 0 | |
+-------+---------+------+-----+---------+-------+
root:@localhost[mytest]>explain emulator;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| userid | int(10) unsigned | NO | | NULL | |
| movieid | int(10) unsigned | NO | | NULL | |
| stamp | bigint(20) | NO | | NULL | |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
然后我们执行一个语句,然后观察查询类型的变化:
explain select * from emulator where userid in (select * from test1) \G
********************* 1. row ***************************
id: 1
select_type: PRIMARY
table: emulator
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
从上面我们看出我们的查询中,id 2需要第一步的查询作为输入,以便进行下面的查询。
root:@localhost[mytest]>explain select * from me where s1 in (select 5 union select userid from emulator )\G
id: 1
select_type: PRIMARY
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
id: 3
select_type: DEPENDENT UNION
id: NULL
select_type: UNION RESULT
这个我只是给出了例子,具体的我也不是很理解,先标记
root:@localhost[mytest]>explain select * from (select * from me) as me \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
*************************** 2. row ***************************
id: 2
select_type: DERIVED
后面的两种,等有时间补上,呵呵,不好理解阿。


_________________
missing is i missing you...


页首
 用户资料  
 
显示帖子 :  排序  
发表新帖 回复这个主题  [ 1 篇帖子 ] 

当前时区为 UTC + 8 小时


在线用户

正在浏览此版面的用户:没有注册用户 和 2 位游客


不能 在这个版面发表主题
不能 在这个版面回复主题
不能 在这个版面编辑帖子
不能 在这个版面删除帖子
不能 在这个版面提交附件

前往 :  
本站点为公益性站点,用于推广开源自由软件,由 DiaHosting VPSBudgetVM VPS 提供服务。
我们认为:软件应可免费取得,软件工具在各种语言环境下皆可使用,且不会有任何功能上的差异;
人们应有定制和修改软件的自由,且方式不受限制,只要他们自认为合适。

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
简体中文语系由 王笑宇 翻译