架构

MySQl是经典的 C/S架构 , 服务器端程序使用的mysqld。
不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是: 客户端进程向服务器进程发送一段文本(sql语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)。
那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为 例展示：
image.png
更具体的:
image.png
此图适用于5.7及之前的架构 8.0后对架构进行了优化 其中Caches&Buffers被删除了
image.png

Connectors

Connectors,指的是不同语言中与SQL的交互。MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用MySQL,我们可以编写代码，跟MySQL Server建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK,比如Native C API、.JDBC、PHP等各语言MySQL Connector,或者通过ODBC。但通过SDK来访问MySQL,本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。
接下来的MySQL Server结构可以分为如下的三层：

连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

  • 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
  • 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

接着我们来思考一个问题
一个系统只会和MySQL服务器建立一个连接吗？只能有一个系统和MySQL服务器建立连接吗？
当然不是，多个系统都可以和MySQL服务器建立连接，每个系统建立的连接肯定不止一个。所以，为了解决TCP
无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数，
采用长连接模式复用TCP连接，来解决上述问题。
image.png

TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后 面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
这些内容我们都归纳到MySQL的连接管理组件中。
所以连接管理的职责是负责认证、管理连接、获取权限信息。

服务层

  • SQL Interface: SQL接口
    • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
    • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定 义函数等多种SQL语言接口
  • Parser: 解析器
    • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定 义函数等多种SQL语言接口
    • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ，并根据数据字 典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还 会对SQl查询进行语法上的优化，进行查询重写。
  • Optimizer: 查询优化器
    • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划 。
    • 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连 接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将 查询结果返回给用户
    • 它使用“ 选取-投影-连接 ”策略进行查询。
    • SELECT id,name FROM student WHERE gender = '女';
    • 例如： 这个SELECT查询先根据WHERE语句进行 选取 ，而不是将表全部查询出来以后再进行gender过 滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过 滤，将这两个查询条件 连接 起来生成最终查询结果。
  • Caches & Buffers： 查询缓存组件
    • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结 果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过 程了，直接将结果反馈给客户端。
    • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
    • 这个查询缓存可以在 不同客户端之间共享 。
    • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

      小故事： 如果我问你9+8×16-3×2×17的值是多少，你可能会用计算器去算一下，最终结果35。如果再问你一遍9+8×16- 3×2×17的值是多少，你还用再傻呵呵的再算一遍吗？我们刚刚已经算过了，直接说答案就好了。

引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别 维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样 我们可以根据自己的实际需要进行选取。 MySQL 8.0.25默认支持的存储引擎如下：
image.png

存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存 在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设 备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。

小结

MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便，我们可以简化如下： image.png
简化为三层结构：

  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
  2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

    执行流程

    MySQL 的 sql执行流程

    image.png

    查询执行流程

    1. 查询缓存

    Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没 有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃 了这个功能。
    MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key,那么这个vlue就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
    大多数情况查询缓存就是个鸡肋，为什么呢？
    查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大降 低 ，只有 相同的查询操作才会命中查询缓存 。两个查询请求在任何字符上的不同（例如：空格、注释、 大小写），都会导致缓存不会命中。因此 MySQL 的 查询缓存命中率不高 。
    例如: SELECT employee_id,last_name FROM employees WHERE employee_id = 101; 和
    SELECT employee_id,last_name FROM employees WHERE employee_id = 101;
    就不是一个key
    同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数 举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW() ，每次调用都会产生最新的当前 时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次 查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询 的结果就是错误的！
    此外，既然是缓存，那就有它 缓存失效的时候 。MySQL的缓存系统会监测涉及到的每张表，只要该表的 结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高 速缓存中删除！对于 更新压力大的数据库 来说，查询缓存的命中率会非常低。
    总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。
    一般建议大家在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种“按需使用”的方式。你可以将my.cnf参数query._cache_type设置成DEMAND,代表当sql语句中有SQL_CACHE关键词时才缓存。比如：
    1. # query_cacher_type有3个值 0代表关闭了换成OFF ， 1代表开启NO ， 2代表DEMAND按需
    2. query_cacher_type=2
    这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：
    1. SELSEC SQL_CACHE * FROM emp WHERE id = 5; # 指明了先取缓存中查询
    2. SELSEC SQL_NO_CACHE * FROM emp WHERE id = 5; # 指明了不使用缓存
    image.png
    若使用了查询缓存可以使用命令查看sql命中率
    SHOW status LIKE '%Qcache%'
    image.png
    Qcachefree_blocks:表示查询缓存中还有多少剩余的blocks,如果该值显示较大，则说明查询缓存中的内存碎
    片过多了，可能在一定的时间进行整理。
    Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，
    是多了，还是不够用，DBA可以根据实际情况做出调整。
    Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存
    效果越理想。
    Qcache_inserts:表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理
    执行查询处理后把结果isrt到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不
    理想。当然系统刚启动后，查询缓存是空的，这很正常。
    Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适
    当的调整缓存大小。
    Qcache_not_cached:表示因为query    .cache._type的设置而没有被缓存的查询数量。
    Qcache._queries_in_cache:当前缓存中缓存的查询数量。
    Qcache_total_blocks:当前缓存的block数量。

    2. 解析器

    在解析器中对 SQL 语句进行语法分析、语义分析。
    image.png
    分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面 的字符串分别是什么，代表什么。 MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语 句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
    接着，要做“ 语法分析 ”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输 入的这个 SQL 语句是否 满足 MySQL 语法 。 select department_id,job_id,avg(salary) from employees group by department_id;
    如果SQL语句错误将报错 You have an error in your SQL syntax
    如果SQL语句正确，则会生成一个这样的语法树：
    image.png
    执行顺序:
    image.png

    3. 优化器

    在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等。
    经过了解析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。一条查询可以有很多
    种执行方式，最后都返回相同的结果。
    优化器的作用就是找到这其中最好的执行计划。
    image.png
    比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(Gjoi)的时候
    决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等 例如: 
    1. SELECT * FROM test1 
    2. JOIN test2 USING(ID) 
    3. WHERE test1.name='zhangwei' AND test2.name='aaa';
    4. # 即
    5. SELECT * FROM test1 
    6. JOIN test2 ON test1.ID = test2.ID
    7. WHERE test1.name='zhangwei' AND test2.name='aaa';
    方案1：可以先从表test1里面取出name=’zhangwei’的记录的ID值，再根据ID值关联到表test2,再判断test2
    里面name的值是否等于’mysql高级课程’。
    方案2：可以先从表test2里面取出name=’mysql高级课程’的记录的ID值，再根据ID值关联到test1,再判断
    test1里面name的值是否等于zhangwei.
    这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
    如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

4. 执行器

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。
image.png
在执行之前需要判断该用户是否 具备权限 。如果没有，就会返回权限错误。如果具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
select * from test where id=1;
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行的
读写。存储引擎API只是抽象接口，面还有个存储引擎层，具体实现还是要看表选择的存储引擎。
image.png
image.png
比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
image.png