MySQL之SQL逻辑查询顺序

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 联动贴:http://blog.itpub.net/29510932/viewspace-1777673/ left join on之中出现的一些有偏差的理解, 也是由于对SQL逻辑查询顺序的不了解导致的--------------------------------...
联动贴:http://blog.itpub.net/29510932/viewspace-1777673/
left join on之中出现的一些有偏差的理解, 也是由于对SQL逻辑查询顺序的不了解导致的
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
问题出现于生产环境,为了方便表述,在测试环境构造了类似的场景,同时部分验证和测试例子隐去了一些生产环境的信息
有开发人员对生产环境中出现的一些现象提出了疑问,然后仔细查阅资料,特意明确了这一个知识点,顺便又挖了一个坑......_(:з」∠)_
------------------------------------------------------------------------------------------------知识点--------------------------------------------------------------------------------------------------------------
SQL伪代码

点击(此处)折叠或打开

  1. SELECT DISTINCT <select_list>
  2. FROM <left_table>
  3. <join_type> JOIN <right_table>
  4. ON <join_condition>
  5. WHERE <where_condition>
  6. GROUP BY <group_by_list>
  7. HAVING <having_condition>
  8. ORDER BY <order_by_condition>
  9. LIMIT <limit_number>

SQL伪代码的执行顺序

点击(此处)折叠或打开

  1. (8) SELECT (9) DISTINCT <select_list>
  2. (1) FROM <left_table>
  3. (3) <join_type> JOIN <right_table>
  4. (2)     ON <join_condition>
  5. (4) WHERE <where_condition>
  6. (5) GROUP BY <group_by_list>
  7. (6) WITH {CUBE | ROLLUP}
  8. (7) HAVING <having_condition>
  9. (10) ORDER BY <order_by_list>
  10. (11) LIMIT <limit_number>

大前提:在MySQL中,忽略ICP(Index condition Pushdown),忽略数据库对order by,group by, limit等语法的优化
需要注意的是,SQL语句在执行各个步骤之后,都会把结果临时存储起来,姑且记为TM*
1.取出left_table 和right_table 然后对两个表的数据做笛卡尔积,得到临时结果TM1
2. 根据ON的<join_conditionjoin_condition过滤,留下符合条件的结果得到临时结果TM2;
3.检查join_type如果是left或者right,那么则会把left_table<left_table或者<right_tableright_table的行数补齐,得到临时结果TM3.1;
(3.1)如果存在更多的表要进行join,则读取下一张需要join的表的数据,重复1-3的步骤,直到得到最终的临时结果TM3;
4.对TM3的数据,依据where_condition进行过滤,得到临时结果TM4;
5.对TM4的数据,依据group_by_list<group_by_list进行分组操作,得到临时结果TM5;
6.对TM5的数据,进行CUBE或者ROLLUP操作,得到临时结果TM6;
7.
对TM6的数据,依据having_condition<having_condition进行过滤,得到临时结果TM7;
8.对TM7的数据,执行投影操作(和聚集函数计算?),得到临时结果TM8;
9.对TM8的数据,执行去重操作,得到临时结果TM9;
10.对TM9的数据,执行排序操作,得到临时结果TM10;
11.对TM10的数据,执行排序操作,得到临时结果TM11;
-------------------------------------------------------------------------------------------联动帖的情况-----------------------------------------------------------------------------------------------------------
那么回顾联动帖里面的问题,在left join on的条件中,除了常见的列关联,还存在col>1之类的选择条件把整个语句代入到上面的执行逻辑里面,
可以发现,在第2步里面确实是把不符合col>1的列过滤掉了,但是在第3步,依据left join的特性,又把左表中的列补全了,且不符合条件的全部使用null进行填充,
所以才会出现实验中的结果;

同样的,换成inner join以后,得到的结果也完全可以解释清楚,因此联动帖中的现象可以根据这个处理逻辑来理解和判断~
-------------------------------------------------------------------------------------------知识点的应用------------------------------------------------------------------------------------------------------------
背景:分页查询
于生产环境下截图,SQL语句稍加改动,截图隐去部分信息
问题:根据SQL逻辑查询顺序的描述来看,分页查询的两个语句应该消耗差不多的时间
实际上分页查询的count(*)比分页查询查内容的时候,要慢了800多倍;
问题语句的复现:

点击(此处)折叠或打开

  1. select count(*)
  2.   from  A
  3.   left  B on A.order_id= B.order_id
  4.   left  C on A.apply_id= C.apply_id
  5.   WHERE
  6.    a.`shop_id` IN (3 1,2,3,4,5,6,7,8,9,10)
  7. and a.`create_time` >= '2015-09-06 00:00:00'
  8. ------------------------------------------------------------
  9. select A.col1, B.col1, C.col1
  10.   from  A
  11.   left  B on A.col1= B.col1
  12.   left  C on A.col1= C.col1
  13.   WHERE
  14.    a.`shop_id` IN (1,2,3,4,5,6,7,8,9,10)
  15. and a.`create_time` >= '2015-09-06 00:00:00'
  16. limit 0,100

完全按照SQL逻辑执行顺序来重新分析这两个语句,这两个语句确实是差不太多的,都会执行步骤 1-4和6,下面一个语句还会在最后再执行一个limit,选出从0开始的100行数据;
那么实际看看执行结果:
count(*)

分页查询

去掉limit的分页查询()

其实有对比就很明显了, 这肯定是 limit 0,100 导致这个时间上的差距 ,那么是MySQL做了什么额外的操作使得limit的速度变得那么快了?
看看explain:
分页查询

去掉limit的分页查询

单纯在这个 分页查询里面,多出来了一个index condition的信息,这是5.6新加的特性 ICP(Index condition Pushdown)(又挖一个坑..._(:з」∠)_...),
在这个特性的帮助下,在步骤1就会减少读取进来的数据量(rows也发生了变化 ,所以极大的降低了分页查询的执行时间;

去掉limit之后,count(*)和实际查询的时间虽然还有差距, 不过也是在同一个级别上了,这里面应该还是有一些MySQL自己做的优化处理在里面,这其中的东西,就不太了解了。
-------------------------------------------------------------------------------------------完结的分割线-----------------------------------------------------------------------------------------------------------
PS:实际上在这个过程中,还是遇到了一些其他的“奇怪”的现象,继续摸索ing;每一个问题的背后,知识点经常是一个扣着一个,学无止境_(:з」∠)_...

</having_condition<></group_by_list<></right_table<></left_table<></join_condition<>
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
25天前
|
SQL 缓存 监控
MySQL缓存机制:查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系,包括已弃用的查询缓存和核心的InnoDB缓冲池,帮助理解缓存优化原理。通过合理配置,可显著提升数据库性能,甚至达到10倍以上的效果。
|
7天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
91 14
|
9天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
53 15
|
5天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
5天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
26天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
25天前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
165 11
|
10天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
9月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
236 0

热门文章

最新文章

推荐镜像

更多