MYSQL基础架构

php基础就讲到这里，下面讲一下

mysql的基础架构

mysql> select * from T where ID=10；
首先我们看到这么一条简单的查询语句，
我们看到的只是输入一条语句，返回一个结果，但是大家可能有点忘却了这条语句在 MySQL 内部的执行过程。
下面我就和大家一起温习一下这条语句的具体执行过程。
请看这张图

下面我给出的是 MySQL 的基本架构示意图，从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

从图中不难看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。大家可以先对每个组件的名字有个印象，接下来我会结合开头提到的那条 SQL 语句，带大家走一遍整个执行流程，依次看下每个组件的作用。

连接器

mysql -h$ip -P$port -u$user -p

输完命令之后，你就需要在交互对话里面输入密码。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

查询缓存

连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

分析器

首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒.

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等，没关系，我会在后面的文章中单独展开说明优化器的内容。

执行器

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。

02 窗口函数

1窗口函数的使用场景

环比如何计算？怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？

对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的。

要解决此类问题，最方便的就是使用窗口函数。

2MySQL窗口函数简介

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。 窗口函数和普通聚合函数也很容易混淆，二者区别如下：

聚合函数是将多条记录聚合为一条；
而窗口函数是每条记录都会执行，有几条记录执行完还是几条。

今天主要给大家讲一下排序的窗口函数

row_number()

select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
from tb_score; 

-- 用途：显示分区中的当前行号 由结果可以看出，分数相同时按照学号顺序进行排名

DENSE_RANK()

select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn
from tb_score; 

-- 为了让分数相同时排名也相同

RANK()

select stu_no,course,score, RANK()over(partition by course order by score desc ) rn
from tb_score; 

-- 如果2个并列的第1之后，下一个我想是第3名，则可以使用RANK函数实现

NTILE()

select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group from tb_score;

-- NTILE函数的作用是对每个分组排名后，再将对应分组分成N个小组

3窗口函数小结