【重难点】【MySQL 02】SQL语句执行过程、COUNT(常量)、COUNT(*) 与 COUNT(列名) 的对比、多表查询的连接概念、将查询结果分组并排序

【重难点】【MySQL 02】SQL语句执行过程、COUNT(常量)、COUNT(*) 与 COUNT(列名) 的对比、多表查询的连接概念、将查询结果分组并排序

一、SQL语句执行过程

当向 MySQL 发送一个请求的时候，MySQL 中都发生了什么：

• 连接器：身份认证和权限相关（登录 MySQL 的时候）
• 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 后移除）
• 分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 要做什么，再检查语法
• 优化器：按照 MySQL 认为最优的方案去执行
• 执行器：执行语句，然后从存储引擎返回数据

1.MySQL 基本组件介绍

连接器

连接器主要和身份认证和权限相关的功能有关，就好比一个级别很高的门卫

主要负责用户登录数据库时，进行用户的身份认知，包括校验账户密码和权限等操作，如果账号密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的

查询缓存

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过。这个执行记录是以 Key-Value 的形式缓存在内存中，Key 是查询语句，Value 是结果集。如果缓存 Key 被命中，就会直接返回给客户端，如果没有命中，则会执行后续的操作，完成后也会把结果集缓存起来，方便下一次调用。当然，在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件

MySQL 查询不建议使用缓存，因为缓存失效在实际业务场景中可能会非常频繁，加入你对一个表更新的话，这个表上的所有查询缓存都会被清空。不过对于不经常更新的数据来说，使用缓存还是可以的

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了

分析器

没有命中缓存，那么就会进入分析器，分析器主要是用来分析这条 SQL 语句是做什么的，分析器也会分为几步：

1. 提取，一条 SQL 语句有多个字符串组成，首先要提取关键字（比如 select）、提取查询的表、提取字段名、提取查询条件等等。做完这些操作后，就会进入第二步
2. 语法分析，主要就是判断你输入的 SQL 是否正确，是否符合 MySQL 的语法

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了

优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来了

执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

2.语句分析

我们了解了 MySQL 中那么多组件之后，那么究竟一条 SQL 语句是如何执行的呢？我们的 SQL 分为两种，一种是查询，另一种是更新（增删改）

查询语句

select * from tb_student A where A.age='18' and A.name='张三';

对于查询语句，会先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL 8.0 版本以前，会先查询缓存，以这条 SQL 语句为 Key 在内存中查询是否有结果，如果有直接返回缓存结果集，如果没有，执行下一步

通过分析器进行词法分析，首先提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没有问题就执行下一步

接下来就是优化器来确定执行方案，上面的 SQL 语句，可以有两种执行方案：

1. 先查询学生表中姓名为 “张三” 的学生，然后判断是否年龄为 18
2. 先找出学生中年龄为 18 的学生，然后再查询姓名为 “张三” 的学生

那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（有时候实际上不一定是最好的）。那么确定了执行计划后就准备开始执行了

再进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果

更新语句

update te_student A set A.age='19' where A.name='张三';

更新语句基本上也会沿着查询语句的流程走，只是执行更新操作的时候需要记录日志。MySQL 自带的日志模块是 binlog，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了 redo log，我们就以 InnoDB 为例探讨这个语句的执行流程

先查询到张三这条数据，如果有缓存，会直接使用缓存结果

然后拿到查询的语句，把 age 改为 19，然后调用引擎接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交

执行器收到通知后记录 binlog，然后调用引擎接口 提交 redo log 为提交状态

最后更新完成

二、COUNT(常量)、COUNT(*) 与 COUNT(列名) 的对比

1.语义区别

• 常量是一个固定值，肯定不为 NULL
• * 可以理解为查询整行，所以肯定也不为 NULL
• 列名的查询可能为 NULL

因此，COUNT(常量) 和 COUNT(*) 表示的是直接查询符合条件的数据库表的行数，而 COUNT(列名) 表示的是查询符合条件的列的值不为 NULL 的行数

虽然它们都可以查询行数，但是万事万物一定会有一个标准，COUNT(*) 就是 MySQL 定义的标准统计行数的语法，MySQL 也对 COUNT(*) 进行过很多的优化

2.COUNT(*) 的优化

这里要区分不同的执行引擎，MySQL 中比较常用的执行引擎是 MyISAM 和 InnoDB.MyISAM 和 InnoDB 有很多区别，其中有一个关键区别和我们接下来要介绍的 COUNT(*) 有关，那就是 MyISAM 不支持事务，MyISAM 中的锁是表级锁；而 InnoDB 支持事务，并且支持行级锁

因为 MyISAM 的锁是表级锁，所以同一张表上面的操作需要串行进行，所以，MyISAM 做了一个简单的优化，那就是它可以把表的总行数单独记录下来，在一张表中使用 COUNT(*) 进行查询的时候，可以直接返回这个记录下来的数值，前提是没有 WHERE 条件

MyISAM 可以进行这种操作是因为 MyISAM 只有表级锁，不允许并发的数据库行数修改，所以查询得到的行数是准确的。但是对于 InnoDB 来说，InnoDB 是支持事务的，其中大部分操作都是使用行级锁，所以表的行数可能被并发修改，那么缓存记录下来的总行数就很可能不准确

不过 InnoDB 还是针对 COUNT(*) 做了一些其他优化的。我们知道，COUNT(*) 的目的只是为了统计总行数，所以，如果它能够在扫描表时，选择一个成本较低的索引进行统计的话，就可以大大节省时间。InnoDB 中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。所以，相比之下，非聚簇索引要比聚簇索引小很多。因此 MySQL 会优先选择最小的非聚簇索引来统计行数

最后需要注意，MySQL 对于 COUNT(*) 做的这些优化的前提都是查询语句中不包含 WHERE 和 GROUP BY 条件

3.COUNT(1) 和 COUNT(列名)

COUNT(1) 不需要做过多解释，它其实和 COUNT(*) 的优化是完全一样的

至于 COUNT(列名)，它的查询比较简单粗暴，就是进行全表扫描，然后判断指定字段的值是不是为 NULL，不为 NULL 就统计。相比 COUNT(*)，COUNT(列名) 多了一个判断步骤，因此性能不如 COUNT(*)

三、多表查询的连接概念

多表查询中有 5 种方式将两张表连接起来，分别是笛卡尔积、INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN

1.笛卡尔积

#把两个表的数据拼接起来
select * from dept,emp;

上面这种查询两个表的方式称为笛卡尔积（Cartesian product），又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤

2.INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name

3.LEFT JOIN

LEFT JOIN 关键字从左表返回所有的行，如果右表中的没有匹配到相关记录，则没匹配到的字段的值为 NULL。比如左表为学生表，右表为成绩表，查询学生名和学生成绩，以学号相等为条件，如果左表中有该学生，右表中没有该学生的成绩，那么结果集中还是会有该学生，只是其学生成绩的值为 NULL

SELECT columu_name(s)
FROM table1
LEFT JOIN table2
ON table.column_name=table2.column_name;

4.RIGHT JOIN

LEFT JOIN 关键字从左表返回所有的行，如果右表中的没有匹配到相关记录，则没匹配到的字段的值为 NULL

SELECT coulumn_name(s)
FROM table1
RIGHT JOIN table2
ON table.column_name=table2.column_name;

5.FULL OUTER JOIN（MySQL 不支持）

FULL OUTER JOIN 关键字只要左表和右表的其中一个表存在匹配，则返回行
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table.column_name=table2.colum_name;

四、将查询结果分组并排序

1.GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果进行分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

2.ORDER BY

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name DESC;

3.HAVING

在 SQL 中增加 HAVING 子句的原因是 WHERE 关键字无法与聚合函数一起使用
HAVING 子句可以让我们筛选分组后的各组数据

SELECT column_name,aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

4.分组并排序

如果我们要将查询结果分组并排序，那么在 SQL 语句中是先 GROUP BY 还是先 ORDER BY 呢？

GROUP BY、ORDER BY、WHERE、HAVING 这些关键字是按照如下顺序执行的：WHERE、GROUP BY、HAVING、ORDER BY

首先 WHERE 将原始记录中不满足条件的记录删除

然后通过 GROUP BY 关键字后面指定的分组条件将筛选到的视图进行分组

接着系统会根据 HAVING 关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉

最后按照 ORDER BY 语句对视图进行排序，这样最终的结果就生成了

在这四个关键字中，只有在 ORDER BY 语句中才可以使用最终视图的列名，如：

SELECT FruitName,ProductPlace,Price,ID AS IDE,Discount
FROM T_TEST_FRUITINFO
WHERE(ProductPlace = N'china')
ORDER BY IDE

在这条 SQL 中，只有 ORDER BY 语句中可以使用 IDE，其它条件语句中如果需要引用列名，只能使用 ID