逻辑查询步骤
(7) SELECT (8) DISTINCT
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) HAVING
(9) ORDER BY
(10) LIMIT
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) HAVING
(9) ORDER BY
(10) LIMIT
1
)
FROM
:对
FROM
子句中的左表和右表执行笛卡儿积(
Cartesian product
),产生虚拟表
VT1
。
2 ) ON :对虚拟表 VT1 应用 ON 筛选,只有那些符合的行才**入虚拟表 VT2 中。
3 ) JOIN :如果指定了 OUTER JOIN (如 LEFT OUTERJOIN 、 RIGHT OUTER JOIN ),那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中,产生虚拟表 VT3 。如果 FROM 子句包含两个以上表,则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1 )~步骤 3 ),直到处理完所有的表为止。
4 ) WHERE :对虚拟表 VT3 应用 WHERE 过滤条件,只有符合的记录才**入虚拟表 VT4 中。此时数据还没有分组,所以不能在 where 中出现对统计的过滤
5 ) GROUP BY :根据 GROUP BY 子句中的列,对 VT4 中的记录进行分组操作,产生 VT5 。在 GROUP BY 阶段,数据库认为两个 NULL 值是相等的,因此会将 NULL 值分到同一个分组中。
6 ) CUBE|ROLLUP :对表 VT5 进行 CUBE 或 ROLLUP 操作,产生表 VT6 。
7 ) HAVING :对虚拟表 VT6 应用 HAVING 过滤器,只有符合的记录才**入虚拟表 VT7 中。 count(expr) 会返回 expr 不为 NULL 的行数, count(1) 、 count(*) 会返回包括 NULL 值在内的所有数量
8 ) SELECT :第二次执行 SELECT 操作,选择指定的列,插入到虚拟表 VT8 中。
9 ) DISTINCT :去除重复数据,产生虚拟表 VT9 。
10 ) ORDER BY :将虚拟表 VT9 中的记录按照进行排序操作,产生虚拟表 VT10 。如果不指定排序,数据并非总是按照主键顺序进行排序的。 NULL 被视为最小值
11 ) LIMIT :取出指定行的记录,产生虚拟表 VT11 ,并返回给查询用户。 LIMIT n, m 的效率是十分低的 , 一般可以通过在 where 条件中指定范围来优化 where id> ? limit 10
2 ) ON :对虚拟表 VT1 应用 ON 筛选,只有那些符合的行才**入虚拟表 VT2 中。
3 ) JOIN :如果指定了 OUTER JOIN (如 LEFT OUTERJOIN 、 RIGHT OUTER JOIN ),那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中,产生虚拟表 VT3 。如果 FROM 子句包含两个以上表,则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1 )~步骤 3 ),直到处理完所有的表为止。
4 ) WHERE :对虚拟表 VT3 应用 WHERE 过滤条件,只有符合的记录才**入虚拟表 VT4 中。此时数据还没有分组,所以不能在 where 中出现对统计的过滤
5 ) GROUP BY :根据 GROUP BY 子句中的列,对 VT4 中的记录进行分组操作,产生 VT5 。在 GROUP BY 阶段,数据库认为两个 NULL 值是相等的,因此会将 NULL 值分到同一个分组中。
6 ) CUBE|ROLLUP :对表 VT5 进行 CUBE 或 ROLLUP 操作,产生表 VT6 。
7 ) HAVING :对虚拟表 VT6 应用 HAVING 过滤器,只有符合的记录才**入虚拟表 VT7 中。 count(expr) 会返回 expr 不为 NULL 的行数, count(1) 、 count(*) 会返回包括 NULL 值在内的所有数量
8 ) SELECT :第二次执行 SELECT 操作,选择指定的列,插入到虚拟表 VT8 中。
9 ) DISTINCT :去除重复数据,产生虚拟表 VT9 。
10 ) ORDER BY :将虚拟表 VT9 中的记录按照进行排序操作,产生虚拟表 VT10 。如果不指定排序,数据并非总是按照主键顺序进行排序的。 NULL 被视为最小值
11 ) LIMIT :取出指定行的记录,产生虚拟表 VT11 ,并返回给查询用户。 LIMIT n, m 的效率是十分低的 , 一般可以通过在 where 条件中指定范围来优化 where id> ? limit 10
物理查询
数据库也许并不会完全按照逻辑查询处理的方式来进行查询
,MySQL
数据库层有
Parser
和
Optimizer
两个组件。
Parser
的工作就是分析
SQL
语句
,
而
Op-timizer
的工作就是对这个
SQL
语句进行优化,选择一条最优的路径来选取数据,但是必须保证物理查询处理的最终结果和逻辑查询处理是相等的。
Explain
语句简介
explain
命令是查看查询优化器(
Optimizer
)是如何执行查询语句的。
explain
输出字段
id
id 值越大优先级越高,越先被执行。 id 相同时,执行顺序由上至下。
id 值越大优先级越高,越先被执行。 id 相同时,执行顺序由上至下。
select_type
**: 意味着查询不包括子查询和 union
**: 意味着查询不包括子查询和 union
PRIMARY:
查询中若包含任何复杂的子部分
,
最外层的
select
被标记为
PRIMARY
SUBQUERY: 包含在 SELECT 列表中的子查询中的 SELECT( 不在 From 字句中 )
DERIVED: 包含在 FROM 子句的子查询中的 SELECT , MySQL 会递归执行并将结果放到一个临时表中。
UNION: UNION 中的第二个或后面的 SELECT 语句 ,UNOIN 中的第一个 SELECT 显示为 PRIMARY 。
UNION RESULT: 用来从 UNION 的匿名临时表检索结果的 SELECT 被标记为 UNION SELECT 。
SUBQUERY: 包含在 SELECT 列表中的子查询中的 SELECT( 不在 From 字句中 )
DERIVED: 包含在 FROM 子句的子查询中的 SELECT , MySQL 会递归执行并将结果放到一个临时表中。
UNION: UNION 中的第二个或后面的 SELECT 语句 ,UNOIN 中的第一个 SELECT 显示为 PRIMARY 。
UNION RESULT: 用来从 UNION 的匿名临时表检索结果的 SELECT 被标记为 UNION SELECT 。
table
显示这一行数据正在访问哪张表,若在查询中为 select 起了别名,则显示别名,如果为 (x 是个数字 , 可以理解为第几步执行的结果 )
显示这一行数据正在访问哪张表,若在查询中为 select 起了别名,则显示别名,如果为 (x 是个数字 , 可以理解为第几步执行的结果 )
type
ALL: 全表扫描 ,MySQL 将遍历全表以找到匹配的行。
index: 跟 ALL 一样,只是扫描全表时按索引次序进行,与 ALL 相比主要优点是避免了排序。
range: 有限制的索引扫描 , 带有 BETWEEN 或在 WHERE 字句中带有 > 、 < 的查询。
ref: 索引查找,查找索引匹配某个单值的所有行 ( 当索引为非唯一性索引时 ) 。
eq_ref: 类似 ref ,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用 primary key 或者 unique key 作为关联条件。
const 、 system: const 用于用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分时 ,system 是 const 类型的特例,当查询的表只有一行的情况下,使用 system 。
NULL: MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
ALL: 全表扫描 ,MySQL 将遍历全表以找到匹配的行。
index: 跟 ALL 一样,只是扫描全表时按索引次序进行,与 ALL 相比主要优点是避免了排序。
range: 有限制的索引扫描 , 带有 BETWEEN 或在 WHERE 字句中带有 > 、 < 的查询。
ref: 索引查找,查找索引匹配某个单值的所有行 ( 当索引为非唯一性索引时 ) 。
eq_ref: 类似 ref ,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用 primary key 或者 unique key 作为关联条件。
const 、 system: const 用于用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分时 ,system 是 const 类型的特例,当查询的表只有一行的情况下,使用 system 。
NULL: MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys
查询可以使用哪些索引。
查询可以使用哪些索引。
keys
MySQL 实际采用哪个索引。
MySQL 实际采用哪个索引。
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度( key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的)
不损失精确性的情况下,长度越短越好
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度( key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的)
不损失精确性的情况下,长度越短越好
ref
使用哪个列或常数与 key 一起从表中选择行。
使用哪个列或常数与 key 一起从表中选择行。
rows
MySQL 估计为了找到所需的行而要读取的行数。
MySQL 估计为了找到所需的行而要读取的行数。
Extra
Using index: 从索引中就可以查询到最终需要的信息,不需要再读取表。
Using temporary: 表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort: MySQL 中无法利用索引完成的排序操作称为 ” 文件排序 ”
Using index: 从索引中就可以查询到最终需要的信息,不需要再读取表。
Using temporary: 表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort: MySQL 中无法利用索引完成的排序操作称为 ” 文件排序 ”
原文来自:
shanks’s blog