一、概述

深刻学习 MySQL，从概览 MySQL 逻辑架构开始。

首先来看一下 MySQL 的逻辑架构图：

MySQL 最重要、最不同凡响的个性就是它的 存储引擎架构 ，这种架构将： 查询处理、其余零碎工作、数据的存储与提取 三局部拆散。所以，带来的益处就是能够在应用时 依据性能、个性，以及其余需要来抉择数据存储形式。

存储引擎架构分为三层，自上而下，分为第一层：连贯层 ；第二层： 服务层 ；第三层： 引擎层。

第一层：连贯层：

MySQL 的最上层是连贯服务，引入了线程池的概念，容许多台客户端连贯。次要工作是：连贯解决、受权认证、平安防护等。

连贯层为通过平安认证的接入用户提供线程，同样，在该层上能够实现基于 SSL 的平安连贯。

第二层：服务层：

服务层用于解决外围服务，如规范的 SQL 接口、查问解析、SQL 优化和统计、全局的和引擎依赖的缓存与缓冲器等等。所有的与存储引擎无关的工作，如过程、函数等，都会在这一层来解决。在该层上，服务器会解析查问并创立相应的外部解析树，并对其实现优化，如确定查问表的程序，是否利用索引等，最初生成相干的执行操作。如果是 SELECT 语句，服务器还会查问外部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中可能很好的晋升零碎的性能。

第三层：引擎层：

存储引擎层，存储引擎负责理论的 MySQL 数据的 存储与提取，服务器通过 API 与 存储引擎进行通信。不同的存储引擎性能和个性有所不同，这样能够依据理论须要有针对性的应用不同的存储引擎。

二、连接器

当客户端（利用）连贯到 MySQL 服务器时，服务器须要对其进行认证。认证基于用户名、原始主机信息和明码。

除了根本认证之外，连接器还会进行一些线程的解决。

每个客户端连贯都会在服务器过程中领有一个线程，这个连贯的查问只会在这个独自的线程中执行，该线程只能轮流在某个 CPU 外围或者 CPU 中运行。服务器会负责缓存线程，因而不须要为每一个新建的连贯创立或者销毁线程。

连接器：负责跟客户端 建设连贯 | 获取权限 | 维持和治理连贯。连贯命令个别如下模式:

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

通过如下语句能够查看以后连贯的信息:

mysql> show processlist;
+----+------+--------------------+-------+---------+------+----------+------------------+
| Id | User | Host               | db    | Command | Time | State    | Info             |
+----+------+--------------------+-------+---------+------+----------+------------------+
|  5 | root | 192.168.33.1:57721 | mysql | Sleep   |   29 |          | NULL             |
|  6 | root | localhost          | NULL  | Query   |    0 | starting | show processlist |
+----+------+--------------------+-------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

其中 ”Sleep” 代表当初零碎外面有一个闲暇连贯.

客户端如果太长时间不流动, 连接器就会主动断开. 这个是由期待超时工夫 wait_timeout 管制的, 默认值是 8 小时.

数据库长连贯: 指连贯胜利后, 如果客户端继续有申请, 则始终应用同一个连贯.

数据库短连贯: 指每次执行完很少的几次查问后就断开连接, 下次查问从新再建设一个.

两种连贯实际计划:

1. 定期断开长连贯. 应用一段时间, 或者程序外面判断执行过一个占用内存的大查问后, 断开连接, 之后要查问再重连.
2. 如果应用的 MySQL 版本是 5.7 或更新版本, 能够在执行一个比拟大的操作后, 通过执行 mysql_reset_connection 来从新初始化连贯资源. 这个过程不须要重连和从新做权限验证, 然而会将连贯复原到刚刚创立完时的状态。

三、查问缓存

对于 SELECT 语句，在解析查问之前，服务器会先查看查问缓存（Query Cache），如果可能在其中找到对应的查问，服务器就不用再执行查问解析、优化和执行的整个过程，而是间接返回查问缓存中的后果集。

但不举荐应用查问缓存 ，即大多数状况下不倡议应用查问缓存。为 什么呢？

起因：查问缓存的生效十分频繁，只有有对一个表的更新，这个表上所有的查问缓存都会被清空。对于更新压力大的数据库来说，查问缓存的命中率会非常低。除非你的业务就是有一张动态表，很长时间才会更新一次。比方，一个系统配置表，那这张表上的查问才适宜应用查问缓存。

好在 MySQL 也提供了这种“按需应用”的形式。能够将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不应用查问缓存。而对于确定要应用查问缓存的语句，能够用 SQL_CACHE 显式指定，如下：

mysql> select SQL_CACHE * from T where ID=10；

四、解析器

• 词法剖析
• 语法分析

如果缓存没有命中的话，MySQL 会对查问语句进行解析。简略说解析的作用将咱们人能看懂的 SQL 解析成 MySQ 能辨认的语言。

解析器先会做“词法解析”。输出的是由多个字符串和空格组成的一条 SQL 语句，MySQL 须要辨认出外面的字符串别离是什么，代表什么。

MySQL 从输出的 ”select” 这个关键字辨认进去，这是一个查问语句。它也要把字符串“T”辨认成“表名 T”，把字符串“ID”辨认成“列 ID”。

做完了这些辨认当前，就要做“语法解析”。依据词法解析的后果，语法解析器会依据语法规定，判断输出的这个 SQL 语句是否满足 MySQL 语法。

五、优化器

• 抉择适合的索引
• 决定各个表的连贯程序

通过了解析器，MySQL 晓得咱们要干什么。

接下来并不是间接执行，而是会在优化器这一层进行优化，优化器是个非常复杂的部件，它会帮咱们去应用他本人认为的最好的形式去优化这条 SQL 语句，并生成一条条的执行打算。

例如在表外面有多个索引的时候，决定应用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连贯程序。比方你执行上面这样的语句，这个语句是执行两个表的 join：

mysql> 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。

这两种执行办法的逻辑后果是一样的，然而执行的效率会有不同，而优化器的作用就是决定抉择应用哪一个计划。

优化器阶段实现后，这个语句的执行计划就确定下来了，而后进入执行器阶段。如果你还有一些疑难，比方优化器是怎么抉择索引的，有没有可能抉择错等等，没关系，我会在前面的文章中独自开展阐明优化器的内容。

六、执行器

1. 判断对这个表有没有查问权限
2. 有权限, 则继续执行.
3. 调用存储引擎接口进行执行查问或其余操作.
4. 最终将查问后果集返回给客户端, 语句即执行实现.

MySQL 通过解析器晓得了你要做什么，通过优化器晓得了该怎么做，于是就进入了执行器阶段，执行器会依据一系列的执行打算去调用存储引擎的接口去实现 SQL 的执行。

开始执行的时候，要先判断一下你对这个表 T 有没有执行查问的权限，如果没有，就会返回没有权限的谬误，如下所示(在工程实现上，如果命中查问缓存，会在查问缓存放回后果的时候，做权限验证。查问也会在优化器之前调用 precheck 验证权限)。

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就关上表继续执行。关上表的时候，执行器就会依据表的引擎定义，去应用这个引擎提供的接口。

比方咱们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

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

至此，这个语句就执行实现了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中曾经定义好的。

七、问题 1：MySQL 的工作流程

最上层：客户端连贯

1、连贯解决：客户端同数据库服务层建设 TCP 连贯，连贯治理模块会建设连贯，并申请一个连接线程。如果连接池中有闲暇的连接线程，则调配给这个连贯，如果没有，在没有超过最大连接数的状况下，创立新的连接线程负责这个客户端。

2、受权认证：在真正的操作之前，还须要调用用户模块进行受权查看，来验证用户是否有权限。通过后，刚才提供服务，连接线程开始接管并解决来自客户端的 SQL 语句。

第二层：外围服务

1、连接线程接管到 SQL 语句之后，将语句交给 SQL 语句解析模块进行语法分析和语义剖析。

2、如果是一个查问语句，则能够先看查问缓存中是否有后果，如果有后果能够间接返回给客户端。

3、如果查问缓存中没有后果，就须要真的查询数据库引擎层了，于是发给 SQL 优化器，进行查问的优化。如果是表变更，则别离交给 insert、update、delete、create、alter 解决模块进行解决。

第三层：数据库引擎层

1、关上表，如果需要的话获取相应的锁。

2、先查问缓存页中有没有相应的数据，如果有则能够间接返回，如果没有就要从磁盘下来读取。

3、当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得前面的查问更加高效，因为内存无限，多采纳变通的 LRU 表来治理缓存页，保障缓存的都是常常拜访的数据。

最初，获取数据后返回给客户端，敞开连贯，开释连接线程。

八、问题 2

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那必定是会报“不存在这个列”的谬误：“Unknown column‘k’in‘where clause’”。你感觉这个谬误是在哪个阶段报进去的呢？

答案：分析器.

MySQL 会解析查问, 并创立外部数据结构(解析树). 分析器会查看: 数据表和数据列是否存在, 别名是否有歧义等. 所以此为分析器阶段. —– 参考自 <MySQL 实战 45 讲 > 01 节课后探讨 @圈圈圆圆的答复.

参考资料

[1] MYSQL 晋升笔记（1）：MySQL 逻辑架构

[2] MySQL 高级 -MySQL 逻辑架构图简介

[3] <MySQL 实战 45 讲 >

欢送关注我的微信公众号：正午 Coder