MySQL语句的执行顺序-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySQL语句的执行顺序

简介: from->on->join->where->group by->sum、count、max、avg->having->select->distinct->order by->limit

1. 书写顺序

select->distinct->from->join->on->where->group by->having->order by->limit

必须字段:select、from

可选字段:distinct、join、on、where、group by、having、sum、count、max、avg、order by、limit

2. 执行顺序

from->on->join->where->group by->sum、count、max、avg->having->select->distinct->order by->limit

# 用左连接的方式查询 bumen 表和 yuangong 表。使用 LEFT JOINON 来实现左连接。

select bumen.d_id,d_name,function,bumen.address,id,name,birthday,sex,salary,yuangong.address

from bumen 

left join yuangong 

on yuangong.d_id=bumen.d_id;

bumen表与yuangong表进行笛卡尔积运算,根据yuangong.d_id=bumen.d_id筛选出符合条件的行,再根据这是左连接, left join一般以左表为驱动表,然后用此表的记录去关联其他表,即使其他表对应元组为空,最后执行select操作,选择出指定的列

FORM: 对FROM的左表和右表计算笛卡尔积。产生虚表VT1

ON: 对虚表VT1进行ON筛选,只有那些符合的行才会被记录在虚表VT2中。

JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, 如果 from 子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。

WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.

CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6

HAVING: 对虚拟表VT6,即已经分组的数据进行过滤的条件,只有符合的记录才会被 插入到虚拟表VT7中

SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中

DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9

ORDER BY: 将虚拟表VT9中的记录按照进行排序操作,产生虚拟表VT10

LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。

3. 关键词含义

  • from:需要从哪个数据表检索数据
  • join:对需要关联查询的表进行关联关联查询时,数据库会选择一个驱动表,然后用此表的记录去关联其他表 left join一般以左表为驱动表(right join一般为右表),inner join一般以结果集少的表为驱动表,left join某些情况下会被查询优化器优化为inner join
    • 驱动表选择原则:在对最终结果集没影响的前提下,优先选择结果集最少的那张表作为驱动表
    • 使用索引关联的情况下,有Index Nested-Loop joinBatched Key Access join两种算法
    • 未使用索引关联的情况下,有Simple Nested-Loop joinBlock Nested-Loop join两种算法
    • Simple Nested-Loop join(SNLJ,简单嵌套循环连接)算法:根据on条件,从驱动表取一条数据,然后全表扫面被驱动表,将符合条件的记录放入最终结果集中。这样驱动表的每条记录都伴随着被驱动表的一次全表扫描
      • 匹配次数:外层表行数*内层表行数
    • Index Nested-Loop Join(INLJ,索引嵌套循环连接)算法:索引嵌套循环连接是基于索引进行连接的算法,索引是基于内层表的,通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录进行比较, 从而利用索引的查询减少了对内层表的匹配次数
      • 匹配次数:外层表行数*内层表索引高度
    • Block Nested-Loop Join(BNLJ,缓存块嵌套循环连接)算法:缓存块嵌套循环连接通过一次性缓存多条数据,把参与查询的列缓存到Join Buffer 里,然后拿join buffer里的数据批量与内层表的数据进行匹配,从而减少了内层循环的次数(遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据)。当不使用Index Nested-Loop Join的时候,默认使用Block Nested-Loop Join
    • Batched Key Access join(BKAJ)算法:和SNLJ算法类似,但用于被join表上有索引可以利用,那么在行提交给被join的表之前,对这些行按照索引字段进行排序,因此减少了随机IO,排序这才是两者最大的区别,但是如果被join的表没用索引呢?那就使用BNLJ了
    • 什么是Join Buffer?
      • Join Buffer会缓存所有参与查询的列而不是只有Join的列。
      • 可以通过调整join_buffer_size缓存大小
      • join_buffer_size的默认值是256K,join_buffer_size的最大值在MySQL 5.1.22版本前是4G,而之后的版本才能在64位操作系统下申请大于4GJoin Buffer空间。
      • 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loopon,默认为开启。
    • 在选择Join算法时,会有优先级,理论上会优先判断能否使用INLJ、BNLJ:Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
    • 注:可以使用explain查找驱动表,结果的第一张表即为驱动表,但执行计划在真正执行时可能发生改变
  • on:关联条件
  • where:过滤表中数据的条件
    • 执行顺序:自下而上、从右到左
    • 注:对数据库记录生效,无法对聚合结果生效,可以过滤掉最大数量记录的条件必须写在where子句末尾,不能使用聚合函数(sum、count、max、avg)
  • group by:如何将上面过滤出的数据分组
    • 执行顺序:从左往右
    • 注:尽量在group by之前使用where过滤,避免之后使用having过滤
  • avg:求平均值
  • having:对上面已经分组的数据进行过滤的条件
    • 注:对聚合结果过滤,因此很耗资源,可以使用聚合函数
    • 例:筛选统计人口数量大于100W的地区
      select region, sum(population), sum(area) from bbc group by region having sum(population)>1000000,不能用where筛选超过100W的地区,因为不存在这样的一条记录
  • select:查看结果集中的哪个列或列的计算结果
  • distinct:对结果集重复值去重
  • order by:按照什么样的顺序来查看返回的数据
    • 执行顺序:从左到右
    • 注:很耗资源
  • limit:截取出目标页数据

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章