首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

【mysql性能优化】sql解析顺序

作者：fuvogsda99363818 | 来源：互联网 | 2023-10-11 05:56
文章目录MySQL架构总览：2查询执行流程2.1连接2.2处理2.3结果3SQL解析顺序MySQL架构总览：架构最好看图，再配上必要的说

文章目录

  • MySQL架构总览&＃xff1a;
  • 2 查询执行流程
    • 2.1 连接
    • 2.2 处理
    • 2.3 结果
  • 3 SQL解析顺序


MySQL架构总览&＃xff1a;

架构最好看图&＃xff0c;再配上必要的说明文字。
下图根据参考书籍中一图为原本&＃xff0c;再在其上添加上了自己的理解。
在这里插入图片描述
从上图中我们可以看到&＃xff0c;整个架构分为两层&＃xff0c;上层是MySQLD的被称为的‘SQL Layer’&＃xff0c;下层是各种各样对上提供接口的存储引擎&＃xff0c;被称为‘Storage Engine Layer’。其它各个模块和组件&＃xff0c;从名字上就可以简单了解到它们的作用&＃xff0c;这里就不再累述了。

2 查询执行流程

下面再向前走一些&＃xff0c;容我根据自己的认识说一下查询执行的流程是怎样的&＃xff1a;

2.1 连接


  1. 客户端发起一条Query请求&＃xff0c;监听客户端的‘连接管理模块’接收请求
  2. 将请求转发到‘连接进/线程模块’
  3. 调用‘用户模块’来进行授权检查
  4. 通过检查后&＃xff0c;‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接&＃xff0c;如果失败则创建一个新的连接请求

2.2 处理


  1. 先查询缓存&＃xff0c;检查Query语句是否完全匹配&＃xff0c;接着再检查是否具有权限&＃xff0c;都成功则直接取数据返回
    2)上一步有失败则转交给‘命令解析器’&＃xff0c;经过词法分析&＃xff0c;语法分析后生成解析树
  2. 接下来是预处理阶段&＃xff0c;处理解析器无法解决的语义&＃xff0c;检查权限等&＃xff0c;生成新的解析树
  3. 再转交给对应的模块处理
  4. 如果是SELECT查询还会经由‘查询优化器’做大量的优化&＃xff0c;生成执行计划
  5. 模块收到请求后&＃xff0c;通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
  6. 有则调用‘表管理模块’&＃xff0c;先是查看table cache中是否存在&＃xff0c;有则直接对应的表和获取锁&＃xff0c;否则重新打开表文件
  7. 根据表的meta数据&＃xff0c;获取表的存储引擎类型等信息&＃xff0c;通过接口调用对应的存储引擎处理
    9)上述过程中产生数据变化的时候&＃xff0c;若打开日志功能&＃xff0c;则会记录到相应二进制日志文件中

2.3 结果


  1. Query请求完成后&＃xff0c;将结果集返回给‘连接进/线程模块’
  2. 返回的也可以是相应的状态标识&＃xff0c;如成功或失败等
  3. 连接进/线程模块’进行后续的清理工作&＃xff0c;并继续等待请求或断开与客户端的连接

一图小总结
在这里插入图片描述

3 SQL解析顺序

接下来再走一步&＃xff0c;让我们看看一条SQL语句的前世今生。
首先看一下示例语句

SELECT DISTINCT< select_list >
FROM< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE< where_condition >
GROUP BY< group_by_list >
HAVING< having_condition >
ORDER BY< order_by_condition >
LIMIT < limit_number >

然而它的执行顺序是这样的

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

虽然自己没想到是这样的&＃xff0c;不过一看还是很自然和谐的&＃xff0c;从哪里获取&＃xff0c;不断的过滤条件&＃xff0c;要选择一样或不一样的&＃xff0c;排好序&＃xff0c;那才知道要取前几条呢。既然如此了&＃xff0c;那就让我们一步步来看看其中的细节吧。

准备工作
1.创建测试数据库

create database testQuery

2.创建测试表

CREATE TABLE table1
(uid VARCHAR(10) NOT NULL,name VARCHAR(10) NOT NULL,PRIMARY KEY(uid)
)ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;UTF8;CREATE TABLE table2
(oid INT NOT NULL auto_increment,uid VARCHAR(10),PRIMARY KEY(oid)
)ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;UTF8;

3.插入数据

INSERT INTO table1(uid,name) VALUES(&＃39;aaa&＃39;,&＃39;mike&＃39;),(&＃39;bbb&＃39;,&＃39;jack&＃39;),(&＃39;ccc&＃39;,&＃39;mike&＃39;),(&＃39;ddd&＃39;,&＃39;mike&＃39;);
INSERT INTO table2(uid) VALUES(&＃39;aaa&＃39;),(&＃39;aaa&＃39;),(&＃39;bbb&＃39;),(&＃39;bbb&＃39;),(&＃39;bbb&＃39;),(&＃39;ccc&＃39;),(NULL);

4.最后想要的结果

SELECTa.uid,count(b.oid) AS total
FROMtable1 AS a
LEFT JOIN table2 AS b ON a.uid &＃61; b.uid
WHEREa. NAME &＃61; &＃39;mike&＃39;
GROUP BYa.uid
HAVINGcount(b.oid) < 2
ORDER BYtotal DESC
LIMIT 1;

&＃xff01;现在开始SQL解析之旅吧&＃xff01;

  1. FROM
    当涉及多个表的时候&＃xff0c;左边表的输出会作为右边表的输入&＃xff0c;之后会生成一个虚拟表VT1。
    (1-J1)笛卡尔积
    计算两个相关联表的笛卡尔积(CROSS JOIN) &＃xff0c;生成虚拟表VT1-J1。

mysql> select * from table1,table2;
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
| aaa | mike | 1 | aaa |
| bbb | jack | 1 | aaa |
| ccc | mike | 1 | aaa |
| ddd | mike | 1 | aaa |
| aaa | mike | 2 | aaa |
| bbb | jack | 2 | aaa |
| ccc | mike | 2 | aaa |
| ddd | mike | 2 | aaa |
| aaa | mike | 3 | bbb |
| bbb | jack | 3 | bbb |
| ccc | mike | 3 | bbb |
| ddd | mike | 3 | bbb |
| aaa | mike | 4 | bbb |
| bbb | jack | 4 | bbb |
| ccc | mike | 4 | bbb |
| ddd | mike | 4 | bbb |
| aaa | mike | 5 | bbb |
| bbb | jack | 5 | bbb |
| ccc | mike | 5 | bbb |
| ddd | mike | 5 | bbb |
| aaa | mike | 6 | ccc |
| bbb | jack | 6 | ccc |
| ccc | mike | 6 | ccc |
| ddd | mike | 6 | ccc |
| aaa | mike | 7 | NULL |
| bbb | jack | 7 | NULL |
| ccc | mike | 7 | NULL |
| ddd | mike | 7 | NULL |
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
rows in set (0.00 sec)

(1-J2)ON过滤
基于虚拟表VT1-J1这一个虚拟表进行过滤&＃xff0c;过滤出所有满足ON 谓词条件的列&＃xff0c;生成虚拟表VT1-J2。
注意&＃xff1a;这里因为语法限制&＃xff0c;使用了’WHERE’代替&＃xff0c;从中读者也可以感受到两者之间微妙的关系&＃xff1b;

mysql> SELECT-> *-> FROM-> table1,-> table2-> WHERE-> table1.uid &＃61; table2.uid-> ;
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
| aaa | mike | 1 | aaa |
| aaa | mike | 2 | aaa |
| bbb | jack | 3 | bbb |
| bbb | jack | 4 | bbb |
| bbb | jack | 5 | bbb |
| ccc | mike | 6 | ccc |
&＃43;-----&＃43;------&＃43;-----&＃43;------&＃43;
rows in set (0.00 sec)

(1-J3)添加外部列
如果使用了外连接(LEFT,RIGHT,FULL)&＃xff0c;主表&＃xff08;保留表&＃xff09;中的不符合ON条件的列也会被加入到VT1-J2中&＃xff0c;作为外部行&＃xff0c;生成虚拟表VT1-J3。

mysql> SELECT-> *-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid;
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| aaa | mike | 1 | aaa |
| aaa | mike | 2 | aaa |
| bbb | jack | 3 | bbb |
| bbb | jack | 4 | bbb |
| bbb | jack | 5 | bbb |
| ccc | mike | 6 | ccc |
| ddd | mike | NULL | NULL |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
rows in set (0.00 sec)

下面从网上找到一张很形象的关于‘SQL JOINS’的解释图&＃xff0c;如若侵犯了你的权益&＃xff0c;请劳烦告知删除&＃xff0c;谢谢。
在这里插入图片描述
2. WHERE
对VT1过程中生成的临时表进行过滤&＃xff0c;满足WHERE子句的列被插入到VT2表中。
注意&＃xff1a;
此时因为分组&＃xff0c;不能使用聚合运算&＃xff1b;也不能使用SELECT中创建的别名&＃xff1b;
与ON的区别&＃xff1a;
如果有外部列&＃xff0c;ON针对过滤的是关联表&＃xff0c;主表&＃xff08;保留表&＃xff09;会返回所有的列&＃xff1b;
如果没有添加外部列&＃xff0c;两者的效果是一样的&＃xff1b;
应用&＃xff1a;
对主表的过滤应该放在WHERE&＃xff1b;
对于关联表&＃xff0c;先条件查询后连接则用ON&＃xff0c;先连接后条件查询则用WHERE&＃xff1b;

mysql> SELECT-> *-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;;
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| aaa | mike | 1 | aaa |
| aaa | mike | 2 | aaa |
| ccc | mike | 6 | ccc |
| ddd | mike | NULL | NULL |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
rows in set (0.00 sec)

  1. GROUP BY
    这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
    注意&＃xff1a;
    其后处理过程的语句&＃xff0c;如SELECT,HAVING&＃xff0c;所用到的列必须包含在GROUP BY中&＃xff0c;对于没有出现的&＃xff0c;得用聚合函数&＃xff1b;
    原因&＃xff1a;
    GROUP BY改变了对表的引用&＃xff0c;将其转换为新的引用方式&＃xff0c;能够对其进行下一级逻辑操作的列会减少&＃xff1b;
    我的理解是&＃xff1a;
    根据分组字段&＃xff0c;将具有相同分组字段的记录归并成一条记录&＃xff0c;因为每一个分组只能返回一条记录&＃xff0c;除非是被过滤掉了&＃xff0c;而不在分组字段里面的字段可能会有多个值&＃xff0c;多个值是无法放进一条记录的&＃xff0c;所以必须通过聚合函数将这些具有多值的列转换成单值&＃xff1b;

mysql> SELECT-> *-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;-> GROUP BY-> a.uid;
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| aaa | mike | 1 | aaa |
| ccc | mike | 6 | ccc |
| ddd | mike | NULL | NULL |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
rows in set (0.00 sec)

  1. HAVING
    这个子句对VT3表中的不同的组进行过滤&＃xff0c;只作用于分组后的数据&＃xff0c;满足HAVING条件的子句被加入到VT4表中。

mysql> SELECT-> *-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;-> GROUP BY-> a.uid-> HAVING-> count(b.oid) < 2;
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| uid | name | oid | uid |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
| ccc | mike | 6 | ccc |
| ddd | mike | NULL | NULL |
&＃43;-----&＃43;------&＃43;------&＃43;------&＃43;
rows in set (0.00 sec)

  1. SELECT
    这个子句对SELECT子句中的元素进行处理&＃xff0c;生成VT5表。
    (5-J1)计算表达式 计算SELECT 子句中的表达式&＃xff0c;生成VT5-J1
    (5-J2)DISTINCT
    寻找VT5-1中的重复列&＃xff0c;并删掉&＃xff0c;生成VT5-J2
    如果在查询中指定了DISTINCT子句&＃xff0c;则会创建一张内存临时表&＃xff08;如果内存放不下&＃xff0c;就需要存放在硬盘了&＃xff09;。这张临时表的表结构和上一步产生的虚拟表VT5是一样的&＃xff0c;不同的是对进行DISTINCT操作的列增加了一个唯一索引&＃xff0c;以此来除重复数据。

mysql> SELECT-> a.uid,-> count(b.oid) AS total-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;-> GROUP BY-> a.uid-> HAVING-> count(b.oid) < 2;
&＃43;-----&＃43;-------&＃43;
| uid | total |
&＃43;-----&＃43;-------&＃43;
| ccc | 1 |
| ddd | 0 |
&＃43;-----&＃43;-------&＃43;
rows in set (0.00 sec)

  1. ORDER BY
    从VT5-J2中的表中&＃xff0c;根据ORDER BY 子句的条件对结果进行排序&＃xff0c;生成VT6表。
    注意&＃xff1a;
    唯一可使用SELECT中别名的地方&＃xff1b;

mysql> SELECT-> a.uid,-> count(b.oid) AS total-> FROM-> table1 AS a-> LEFT OUTER JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;-> GROUP BY-> a.uid-> HAVING-> count(b.oid) < 2-> ORDER BY-> total DESC;
&＃43;-----&＃43;-------&＃43;
| uid | total |
&＃43;-----&＃43;-------&＃43;
| ccc | 1 |
| ddd | 0 |
&＃43;-----&＃43;-------&＃43;
rows in set (0.00 sec)

  1. LIMIT
    LIMIT子句从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。
    注意&＃xff1a;
    offset和rows的正负带来的影响&＃xff1b;
    当偏移量很大时效率是很低的&＃xff0c;可以这么做&＃xff1a;
    采用子查询的方式优化&＃xff0c;在子查询里先从索引获取到最大id&＃xff0c;然后倒序排&＃xff0c;再取N行结果集
    采用INNER JOIN优化&＃xff0c;JOIN子句里也优先从索引获取ID列表&＃xff0c;然后直接关联查询获得最终结果

mysql> SELECT-> a.uid,-> count(b.oid) AS total-> FROM-> table1 AS a-> LEFT JOIN table2 AS b ON a.uid &＃61; b.uid-> WHERE-> a. NAME &＃61; &＃39;mike&＃39;-> GROUP BY-> a.uid-> HAVING-> count(b.oid) < 2-> ORDER BY-> total DESC-> LIMIT 1;
&＃43;-----&＃43;-------&＃43;
| uid | total |
&＃43;-----&＃43;-------&＃43;
| ccc | 1 |
&＃43;-----&＃43;-------&＃43;
row in set (0.00 sec)

至此SQL的解析之旅就结束了&＃xff0c;上图总结一下&＃xff1a;
在这里插入图片描述


推荐阅读
  • default

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）

    eclipse学习（第三章：ssh中的Hibernate）——11.Hibernate的缓存（2级缓存，get和load）
    本文介绍了eclipse学习中的第三章内容，主要讲解了ssh中的Hibernate的缓存，包括2级缓存和get方法、load方法的区别。文章还涉及了项目实践和相关知识点的讲解。 ... [详细]
  • jsp

    高质量SQL书写的30条建议

    高质量SQL书写的30条建议
    本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]
  • regex

    MySQL多表数据库操作方法及子查询详解

    MySQL多表数据库操作方法及子查询详解
    本文详细介绍了MySQL数据库的多表操作方法，包括增删改和单表查询，同时还解释了子查询的概念和用法。文章通过示例和步骤说明了如何进行数据的插入、删除和更新操作，以及如何执行单表查询和使用聚合函数进行统计。对于需要对MySQL数据库进行操作的读者来说，本文是一个非常实用的参考资料。 ... [详细]
  • default

    Android 新闻App的本地服务器搭建教程

    本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • import

    使用 Ubuntu 中的 Python 获取浏览器历史记录

    使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]
  • copy

    图解redis的持久化存储机制RDB和AOF的原理和优缺点

    图解redis的持久化存储机制RDB和AOF的原理和优缺点
    本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件，恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘，实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点，帮助读者更好地理解redis的持久化存储策略。 ... [详细]
  • default

    用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

    ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]
  • case

    Oracle存储过程写法小例子及已命名的异常

    本文介绍了Oracle存储过程的基本语法和写法示例，同时还介绍了已命名的系统异常的产生原因。 ... [详细]
  • case

    关系型数据库和NoSQL数据库的介绍及应用场景

    本文介绍了关系型数据库和NoSQL数据库的概念和特点，列举了主流的关系型数据库和NoSQL数据库，同时描述了它们在新闻、电商抢购信息和微博热点信息等场景中的应用。此外，还提供了MySQL配置文件的相关内容。 ... [详细]
  • join

    MyBatis多表查询与动态SQL使用

    MyBatis多表查询与动态SQL使用
    本文介绍了MyBatis多表查询与动态SQL的使用方法，包括一对一查询和一对多查询。同时还介绍了动态SQL的使用，包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]
  • byte

    Python自动提取文本中的时间（包含中文日期）及特殊时间识别方法

    本文介绍了在处理不规则数据时如何使用Python自动提取文本中的时间日期，包括使用dateutil.parser模块统一日期字符串格式和使用datefinder模块提取日期。同时，还介绍了一段使用正则表达式的代码，可以支持中文日期和一些特殊的时间识别，例如'2012年12月12日'、'3小时前'、'在2012/12/13哈哈'等。 ... [详细]
  • string

    模板引擎StringTemplate的使用方法和特点

    模板引擎StringTemplate的使用方法和特点
    本文介绍了模板引擎StringTemplate的使用方法和特点，包括强制Model和View的分离、Lazy-Evaluation、Recursive enable等。同时，还介绍了StringTemplate语法中的属性和普通字符的使用方法，并提供了向模板填充属性的示例代码。 ... [详细]
  • string

    HashMap的相关问题及其底层数据结构和操作流程

    HashMap的相关问题及其底层数据结构和操作流程
    本文介绍了关于HashMap的相关问题，包括其底层数据结构、JDK1.7和JDK1.8的差异、红黑树的使用、扩容和树化的条件、退化为链表的情况、索引的计算方法、hashcode和hash()方法的作用、数组容量的选择、Put方法的流程以及并发问题下的操作。文章还提到了扩容死链和数据错乱的问题，并探讨了key的设计要求。对于对Java面试中的HashMap问题感兴趣的读者，本文将为您提供一些有用的技术和经验。 ... [详细]
  • byte

    超级简单加解密工具的方案和功能

    超级简单加解密工具的方案和功能
    本文介绍了一个超级简单的加解密工具的方案和功能。该工具可以读取文件头，并根据特定长度进行加密，加密后将加密部分写入源文件。同时，该工具也支持解密操作。加密和解密过程是可逆的。本文还提到了一些相关的功能和使用方法，并给出了Python代码示例。 ... [详细]
  • string

    H5游戏性能优化，调试技巧

    本文介绍了H5游戏性能优化和调试技巧，包括从问题表象出发进行优化、排除外部问题导致的卡顿、帧率设定、减少drawcall的方法、UI优化和图集渲染等八个理念。对于游戏程序员来说，解决游戏性能问题是一个关键的任务，本文提供了一些有用的参考价值。摘要长度为183字。 ... [详细]
author-avatar
fuvogsda99363818
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有