查询执行流程->SQL解析顺序

小鱼儿 2024-02-18 21:18 63阅读 0赞

**一、MySQL架构总览：**

架构最好看图，再配上必要的说明文字。

下图根据参考书籍中一图为原本，再在其上添加上了自己的理解。

![70][]

或者

![70 1][]

1、客户端/服务端通信协议  
MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，C->S，S->C，两个动作不能同时发生。  
C->S，查询语句很长时，设置max\_allowed\_packet参数。注意： 查询实在是太大，服务端会拒绝接收更多数据并抛出异常。  
S->C，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果。因而在实际开发中，尽量保持查询简单且只返回必需的数据，这也是查询中尽量避免使用SELECT \*以及加上LIMIT限制的原因之一。  
2、查询缓存  
在解析查询语句前：查询语句是否在查询缓存中。在检查一次用户权限后直接返回缓存中的结果。  
注：查询不会被解析，也不会生成执行计划，更不会执行。  
MySQL将缓存存放在一个引用表（不是table，类似于HashMap的数据结构），通过一个哈希值索引，哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。  
查询中包含任何用户自定义（函数、存储函数、用户变量、临时表、MySQL库中的系统表）  
查询结果都不会被缓存。比如函数NOW()或者CURRENT\_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT\_USER或者CONNECION\_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。  
缓存失效：MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。  
写操作失效： 任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：  
任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存  
如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗  
不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：  
用多个小表代替一个大表，注意不要过度设计  
批量插入代替循环单条插入  
合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适  
可以通过SQL\_CACHE和SQL\_NO\_CACHE来控制某个查询语句是否需要进行缓存  
注：不建议打开打开查询缓存  
最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query\_cache\_type设置为DEMAND，这时只有加入SQL\_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。  
3、语法解析和预处理  
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。解析器主要通过语法规则来验证和解析。  
4、查询优化  
SQL解析和预处理是正确的才由优化器将其转化成查询计划。  
优化器的作用：找到最好的执行计划。  
MySQL使用基于成本的优化器，一个查询使用某种执行计划时的成本，选择成本最小的一个。在MySQL可以通过查询当前会话的last\_query\_cost的值来得到其计算当前查询的成本。  
mysql> select \* from t\_message limit 10;  
...省略结果集  
  
mysql> show status like 'last\_query\_cost';  
\+-----------------+-------------+  
| Variable\_name | Value |  
\+-----------------+-------------+  
| Last\_query\_cost | 6391.799000 |  
\+-----------------+-------------+  
示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。  
有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。  
MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：  
重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）  
优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）  
提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）  
优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）  
5）查询执行引擎  
在完成SQL语法解析和查询优化器以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。  
6）返回结果给客户端  
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。  
如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。  
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

**二、查询执行流程**

下面再向前走一些，容我根据自己的认识说一下查询执行的流程是怎样的：

**1.连接**

1.1客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求

1.2将请求转发到‘连接进/线程模块’

1.3调用‘用户模块’来进行授权检查

1.4通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求

**2.处理**

2.1先查询缓存，检查Query语句是否完全匹配，接着再检查是否具有权限，都成功则直接取数据返回

2.2上一步有失败则转交给‘命令解析器’，经过词法分析，语法分析后生成解析树

2.3接下来是预处理阶段，处理解析器无法解决的语义，检查权限等，生成新的解析树

2.4再转交给对应的模块处理

2.5如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划

2.6模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限

2.7有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件

2.8根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理

2.9上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中

**3.结果**

3.1Query请求完成后，将结果集返回给‘连接进/线程模块’

3.2返回的也可以是相应的状态标识，如成功或失败等

3.3‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接

**![70 2][]**

**三、SQL解析顺序**

**1）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 >

2）**mysql解析器执行的顺序**

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

`(8)` `SELECT` `(9)` `DISTINCT` `(11)<` `Top` `Num> <` `select` `list>`

`(1)` `FROM` `[left_table]`

`(3)<join_type>` `JOIN` `<right_table>`

`(2)` `ON` `<join_condition>`

`(4)` `WHERE` `<where_condition>`

`(5)` `GROUP` `BY` `<group_by_list>`

`(6)` `WITH` `<` `CUBE` `|` `RollUP` `> （avg\sum ..... ）`

`(7)` `HAVING` `<having_condition>`

`(10)` `ORDER` `BY` `<order_by_list>`

`（12）LIMIT <limit_number>`

**一、sql执行顺序**

(1)from

**from 子句--执行顺序为从后往前、从右到左**  
表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后）

解析器按照从右到左的顺序处理，FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，即最后的表为驱动表，在FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指被其他表所引用的表

(2) on

(3) join

(4) where

**where子句--执行顺序为自下而上、从右到左**

ORACLE 采用自下而上从右到左的顺序解析Where 子句,根据这个原理,表之间的连接必须写在其他Where 条件之前, 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾。

(5)group by(开始使用select中的别名，后面的语句中都可以使用)

**group by--执行顺序从左往右分组**

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。即在GROUP BY前使用WHERE来过虑，而尽量避免GROUP BY后再HAVING过滤。

(6) avg,sum....

(7)having

**having 子句----很耗资源，尽量少用**

避免使用HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.

如果能通过Where 子句在GROUP BY前限制记录的数目,那就能减少这方面的开销.  
(非oracle 中)on、where、having 这三个都可以加条件的子句中，on 是最先执行，where 次之，having 最后，因为on 是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，

where 也应该比having 快点的，因为它过滤数据后才进行sum，在两个表联接时才用on 的，所以在一个表的时候，就剩下where 跟having比较了。

在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where 可以使用rushmore 技术，而having 就不能，在速度上后者要慢。  
如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，where 的作用时间是在计算之前就完成的，而having 就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

在多表联接查询时，on 比where 更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where 进行过滤，然后再计算，计算完后再由having 进行过滤。

由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

(8) select

**select子句--少用\*号，尽量取字段名称**。

在解析的过程中, 会将依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 使用列名意味着将减少消耗时间。

sql 语句用大写的；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行

(9) distinct

(10) order by **order by子句--执行顺序为从左到右排序,很耗资源**

从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。

第一步：首先对from子句中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1（选择相对小的表做基础表）;

FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

第二步：接下来便是应用on筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足on逻辑表达式的行，生成虚拟表 vt2 ;

ON: 对vt1表应用ON筛选器只有满足 join\_condition 为真的行才被插入vt2

第三步：如果是outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤的添加进来，如果是right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 ;

OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。

第四步：如果 from 子句中的表数目多余两个表，那么就将vt3和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复1-3的步骤，最终得到一个新的虚拟表 vt3。;

第五步：应用where筛选器，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4；

WHERE：对vt3应用 WHERE 筛选器只有使 where\_condition 为true的行才被插入vt4

第六步：group by 子句将中的唯一的值组合成为一组，得到虚拟表vt5。如果应用了group by，那么后面的所有步骤都只能得到的vt5的列或者是聚合函数（count、sum、avg等）。原因在于最终的结果集中只为每个组包含一行。这一点请牢记。

GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5

第七步：应用cube或者rollup选项，为vt5生成超组，生成vt6.

CUBE|ROLLUP：把超组(supergroups)插入vt6，生成vt6

第八步：应用having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器。

HAVING：对vt6应用HAVING筛选器只有使 having\_condition 为true的组才插入vt7

第九步：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8.

SELECT：处理select列表产生vt8

第十步：应用distinct子句，vt8中移除相同的行，生成vt9。事实上如果应用了group by子句那么distinct是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。

DISTINCT：将重复的行从vt8中去除产生vt9

第十一步：应用order by子句。按照order\_by\_condition排序vt9，此时返回的一个游标，而不是虚拟表。sql是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。对表进行排序的查询可以返回一个对象，这个对象包含特定的物理顺序的逻辑组织。这个对象就叫游标。正因为返回值是游标，那么使用order by 子句查询不能应用于表表达式。排序是很需要成本的，除非你必须要排序，否则最好不要指定order by，最后，在这一步中是第一个也是唯一一个可以使用select列表中别名的步骤。

ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

第十二步：应用top选项。此时才返回结果给请求者即用户。

TOP：从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者

![70 3][]

继续做以下的前期准备工作：  
新建一个测试数据库TestDB；  
create database TestDB;  
创建测试表table1和table2；  
CREATE TABLE table1  
(  
customer\_id VARCHAR(10) NOT NULL,  
city VARCHAR(10) NOT NULL,  
PRIMARY KEY(customer\_id)  
)ENGINE=INNODB DEFAULT CHARSET=UTF8;  
  
CREATE TABLE table2  
(  
order\_id INT NOT NULL auto\_increment,  
customer\_id VARCHAR(10),  
PRIMARY KEY(order\_id)  
)ENGINE=INNODB DEFAULT CHARSET=UTF8;  
  
插入测试数据；  
INSERT INTO table1(customer\_id,city) VALUES('163','hangzhou');  
INSERT INTO table1(customer\_id,city) VALUES('9you','shanghai');  
INSERT INTO table1(customer\_id,city) VALUES('tx','hangzhou');  
INSERT INTO table1(customer\_id,city) VALUES('baidu','hangzhou');  
INSERT INTO table2(customer\_id) VALUES('163');  
INSERT INTO table2(customer\_id) VALUES('163');  
INSERT INTO table2(customer\_id) VALUES('9you');  
INSERT INTO table2(customer\_id) VALUES('9you');  
INSERT INTO table2(customer\_id) VALUES('9you');  
INSERT INTO table2(customer\_id) VALUES('tx');  
INSERT INTO table2(customer\_id) VALUES(NULL);  
  
准备工作做完以后，table1和table2看起来应该像下面这样：  
mysql> select \* from table1;

mysql> select \* from table2;

准备SQL逻辑查询测试语句  
SELECT a.customer\_id, COUNT(b.order\_id) as total\_orders  
FROM table1 AS a  
LEFT JOIN table2 AS b  
ON a.customer\_id = b.customer\_id  
WHERE a.city = 'hangzhou'  
GROUP BY a.customer\_id  
HAVING count(b.order\_id) < 2

ORDER BY total\_orders DESC;

并且订单数少于2的客户。  
SQL逻辑查询语句执行顺序  
还记得上面给出的那一长串的SQL逻辑查询规则么？那么，到底哪个先执行，哪个后执行呢？现在，我先给出一个查询语句的执行顺序：  
(7) SELECT  
(8) DISTINCT <select\_list>  
(1) FROM <left\_table>  
(3) <join\_type> JOIN <right\_table>  
(2) ON <join\_condition>  
(4) WHERE <where\_condition>  
(5) GROUP BY <group\_by\_list>  
(6) HAVING <having\_condition>  
(9) ORDER BY <order\_by\_condition>  
(10) LIMIT <limit\_number>  
  
SQL解析过程如下:

1、执行FROM语句

FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1；

注:**from 子句--执行顺序为从后往前、从右到左**

表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后）

![70 4][]

笛卡尔积 （table1的记录条数 \* table2的记录条数）条记录。这就是VT1的结果，接下来的操作就在VT1的基础上进行。

select * from table1,table2;

2）执行ON过滤

ON: 对vt1表应用ON筛选器只有满足 join\_condition 为真的行才被插入vt2；=》过滤出所有满足ON 谓词条件的列，生成虚拟表vt2

执行完笛卡尔积以后，接着就进行ON a.customer\_id = b.customer\_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到VT2表，内容如下：  
mysql> select \* from table1,table2 WHERE table1.customer\_id = table2.customer\_id;  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
\+-------------+----------+----------+-------------+  
6 rows in set (0.00 sec)  
  
mysql> select \* from table1 join table2  
\-> on table1.customer\_id = table2.customer\_id;  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
\+-------------+----------+----------+-------------+  
6 rows in set (0.00 sec)

3）添加外部行t3

连接类型为OUTER JOIN时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候，会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。

![70 5][]

LEFT OUTER JOIN把左表记为保留表，得到的结果为：  
select \* from table1 left join table2  
on table1.customer\_id = table2.customer\_id;  
  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
| baidu | hangzhou | NULL | NULL |  
\+-------------+----------+----------+-------------+  
  
RIGHT OUTER JOIN把右表记为保留表，得到的结果为：  
  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
| NULL | NULL | 7 | NULL |  
\+-------------+----------+----------+-------------+  
  
FULL OUTER JOIN把左右表都作为保留表，得到的结果为：  
  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
| baidu | hangzhou | NULL | NULL |  
| NULL | NULL | 7 | NULL |  
\+-------------+----------+----------+-------------+  
  
添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。  
  
由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN，过滤掉了以下这条数据：  
  
| baidu | hangzhou | NULL | NULL |  
  
现在就把这条数据添加到VT2表中，得到的VT3表如下：  
  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| 9you | shanghai | 3 | 9you |  
| 9you | shanghai | 4 | 9you |  
| 9you | shanghai | 5 | 9you |  
| tx | hangzhou | 6 | tx |  
| baidu | hangzhou | NULL | NULL |  
\+-------------+----------+----------+-------------+  
  
接下来的操作都会在该VT3表上进行。

4）执行WHERE过滤

注:对VT1过程中生成的临时表进行过滤，满足WHERE子句的列被插入到VT2表中。

WHERE：对vt3应用 WHERE 筛选器只有使 where\_condition 为true的行才被插入vt4  
对添加外部行得到的VT3进行WHERE过滤，只有符合<where\_condition>的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'的时候，就会得到以下内容，并存在虚拟表VT4中：  
SELECT \* FROM table1 AS a  
LEFT OUTER JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou';  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| 163 | hangzhou | 2 | 163 |  
| tx | hangzhou | 6 | tx |  
| baidu | hangzhou | NULL | NULL |  
\+-------------+----------+----------+-------------+  
但是在使用WHERE子句时，需要注意以下两点：  
由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where\_condition=MIN(col)这类对分组统计的过滤；  
由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。

5)执行GROUP BY分组

注1：这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。

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

GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer\_id，就会得到以下内容：  
SELECT \* FROM table1 AS a  
LEFT OUTER JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou'  
GROUP BY  
a.customer\_id;  
\+-------------+----------+----------+-------------+  
| customer\_id | city | order\_id | customer\_id |  
\+-------------+----------+----------+-------------+  
| 163 | hangzhou | 1 | 163 |  
| baidu | hangzhou | NULL | NULL |  
| tx | hangzhou | 6 | tx |  
\+-------------+----------+----------+-------------+

得到的内容会存入虚拟表VT5中，此时，我们就得到了一个VT5虚拟表，接下来的操作都会在该表上完成。

6）执行HAVING过滤

注：这个子句对VT3表中的不同的组进行过滤，只作用于分组后的数据，满足HAVING条件的子句被加入到VT4表中。

7）SELECT列表

现在才会执行到SELECT子句，不要以为SELECT子句被写在第一行，就是第一个被执行的。

注：这个子句对SELECT子句中的元素进行处理，生成VT5表。

计算表达式 计算SELECT 子句中的表达式，生成VT5-J1  
  
我们执行测试语句中的SELECT a.customer\_id, COUNT(b.order\_id) as total\_orders，从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容：  
SELECT  
a.customer\_id,  
count(b.order\_id) AS total  
FROM  
table1 AS a  
LEFT OUTER JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou'  
GROUP BY  
a.customer\_id  
HAVING  
count(b.order\_id) < 2;  
\+-------------+--------------+  
| customer\_id | total\_orders |  
\+-------------+--------------+  
| baidu | 0 |  
| tx | 1 |  
\+-------------+--------------+  
  
不，还没有完，这只是虚拟表VT7。

8）执行DISTINCT子句  
如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。  
  
由于我的测试SQL语句中并没有使用DISTINCT，所以，在该查询中，这一步不会生成一个虚拟表。  
执行ORDER BY子句  
  
对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的ORDER BY total\_orders DESC，就会得到以下内容：  
)DISTINCT  
寻找VT5-1中的重复列，并删掉，生成VT5-J2  
如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT5是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。  
 SELECT  
a.customer\_id,  
count(b.order\_id) AS total  
FROM  
table1 AS a  
LEFT OUTER JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou'  
GROUP BY  
a.customer\_id  
HAVING  
count(b.order\_id) < 2;  
  
\+-------------+--------------+  
| customer\_id | total\_orders |  
\+-------------+--------------+  
| tx | 1 |  
| baidu | 0 |  
\+-------------+--------------+  
  
可以看到这是对total\_orders列进行降序排列的。上述结果会存储在VT8中。

9）ORDER BY  
从VT5-J2中的表中，根据ORDER BY 子句的条件对结果进行排序，生成VT6表。  
注意：唯一可使用SELECT中别名的地方；  
  
SELECT  
a.customer\_id,  
count(b.order\_id) AS total  
FROM  
table1 AS a  
LEFT OUTER JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou'  
GROUP BY  
a.customer\_id  
HAVING  
count(b.order\_id) < 2  
ORDER BY  
total DESC;

10）执行LIMIT子句

LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。  
MySQL数据库的LIMIT支持如下形式的选择：  
LIMIT n, m  
表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。

LIMIT子句从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。  
注意：  
offset和rows的正负带来的影响；  
当偏移量很大时效率是很低的，可以这么做：  
采用子查询的方式优化，在子查询里先从索引获取到最大id，然后倒序排，再取N行结果集  
采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果  
  
SELECT  
a.customer\_id,  
count(b.order\_id) AS total  
FROM  
table1 AS a  
LEFT JOIN table2 AS b ON a.customer\_id = b.customer\_id  
WHERE  
a. city = 'hangzhou'  
GROUP BY  
a.customer\_id  
HAVING  
count(b.order\_id) < 2  
ORDER BY  
total DESC  
LIMIT 1;

![70 6][]

[70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fc8eec75debb4485af5ed71f2cf01ceb.png
[70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f7652b1a86134f71814ca2a7e0ea7767.png
[70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2e1f32cc68b449fe92fdc0a7f668b33e.png
[70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d6c8ef55f89a42a3b97bfa84b5ec4aad.png
[70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/056d728e7d6e4ec6a10cdda3c3132030.png
[70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2d5dbcacc8bb4481885755d5ca274e1a.png
[70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/76bf0cedbf164e98b7473155ae363445.png