【MySQL】SQL底层执行原理详解
MySQL的内部组件结构
图解MySQL架构
大体来说，MySQL 可以分为 Server层和存储引擎层（支持 “插拔”）两部分。
Server层
主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
Store层（引擎层）
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定表的存储引擎类型,默认会给你设置存储引擎为InnoDB。
创建表指定存储引擎的DDL如下：
CREATE TABLE TEST (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;关于存储引擎的问题在上一篇文章中已经提到过了！这一章节只要是讲解Server层的各种组件。
连接器
MySQL是开源的，他有非常多种类的客户端，例如：navicat、shell、jdbc、SQLyog等非常丰富的客户端，这些客户端要向MySQL发起通信都必须先跟Server端建立通信连接，而建立连接的工作就是有连接器完成的。
我们现在通过命令行窗口的方式来连接MySQL，并阐述其底层的运行原理。
查看 | 启动MySQL服务
我们可以通过windows的cmd命令来启动MySQL的服务
# 查看windows启动了那些服务
net start# 启动mysql服务
net start mysql80
# 关闭mysql服务
net stop mysql80
说明MySQL服务已经启动了（一般在Windows中，默认安装mysql后，其服务都是开机自启的）
注：如果启动的时候，报拒绝访问，那就是权限的问题，搜索框输入cmd，然后右键 “以管理员身份运行” 即可。
建立MySQL连接
# 标准语法
# mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306
mysql -u root -p连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。
在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。
1、如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
2、如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限（只要连接不断，权限怎么改都没有用！）
修改完成后，只有再新建的连接才会使用新的权限设置。
用户的权限表在系统表空间的 mysql库中的user表 中。
创建新用户
CREATE USER 'Harmony'@'%' IDENTIFIED BY '888888';赋权限，%表示所有(host)
grant all on *.* to 'Harmony'@'%';刷新数据库
flush privileges;修改密码
ALTER USER 'Harmony'@'%' IDENTIFIED BY '123456';查看权限
show grants for Harmony@"%";连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，在 show processlist 命令中看到它。
其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接，可以使用 kill 命令（Id号）来关闭连接。
客户端如果长时间不发送command到Server端，连接器就会自动将它断开。
这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
show global variables like "wait_timeout";
# 设置全局服务器关闭非交互连接之前等待活动的秒数
set global wait_timeout=28800; 如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：
Lost connection to MySQL server during query
这时候如果你要继续，就需要重连，然后再执行请求了。
在数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
开发当中我们大多数时候用的都是长连接，把连接放在Pool内进行管理，但是长连接有些时候会导致 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。
所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉，从现象看就是 MySQL 异常重启了。
怎么解决这类问题呢？
1、定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection() 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
关于mysql_reset_connection()可以看这里
这个是C语言的API不是mysql的shell命令~~~
当然这个长连接的问题可以用数据库连接池来解决！！！
查询缓存（鸡肋）
这个功能很鸡肋，mysql8.0已经移除了查询缓存功能。
常用的一些操作
show databases; # 显示所有数据库
use dbname; # 打开数据库：
show tables; # 显示数据库mysql中所有的表；
describe user; # 显示表mysql数据库中user表的列信息）；连接建立完成后，你就可以执行 select 语句了。
MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。
key 是查询的SQL语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
但是大多数情况查询缓存就是个鸡肋！
因为查询缓存往往弊大于利。查询缓存的失效非常频繁。只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
所以，一般建议大家在静态表里使用查询缓存。
什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。
MySQL 也提供了这种“按需使用”的方式。
你可以将 my.cnf 参数 query_cache_type 设置成 DEMAND
query_cache_type 有3个值 。0 代表关闭查询缓存OFF，1 代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存。
修改 my.cnf文件 里的 query_cache_type 值
query_cache_type=2这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
select SQL_CACHE * from test where ID=1；查看当前MySQL实例是否开启缓存机制
show global variables like "%query_cache_type%";监控查询缓存的命中率
show status like'%Qcache%'; # 查看运行的缓存信息由于我的MySQL是8.0版本的，是看不到结果的，因为MySQL缓存在8.0的版本已经被废除了~~~
这里找了一张网图~~~
- Qcache_free_blocks：表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
- Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
- Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
- Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
- Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
- Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
- Qcache_queries_in_cache：当前缓存中缓存的查询数量。
- Qcache_total_blocks：当前缓存的block数量。
分析器
如果没有命中查询缓存，就要开始真正执行语句了。
MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。
例如：MySQL 从你输入的 "select" 这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”
做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法
如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒
如下述，这个语句 from 写成了 "fro"。
词法分析器原理
词法分析器分成6个主要步骤完成对sql语句的分析
1、词法分析
2、语法分析
3、语义分析
4、构造执行树
5、生成执行计划
6、计划的执行
词法分析器执行步骤如下：
SQL语句的分析分为词法分析与语法分析。
MySQL的词法分析由 MySQLLex 完成，语法分析由 Bison 生成。
那么除了Bison外，Java当中也有开源的词法结构分析工具例如Antlr4，ANTLR从语法生成一个解析器，可以构建和遍历解析树。
select id,name,age from test where id = 1;
经过bison语法分析之后，会生成一个这样的语法树
优化器
经过了分析器，MySQL 就知道你要做什么了。
在开始执行之前，还要经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
例如下面的这一条SQL：
select * from test1 join test2 using(ID)
where test1.name = yangguo and test2.name = xiaolongnv;注意：里面有用到一个比较陌生的东西——using()
using等价于join操作中的on
但是想要使用 using 必须满足两个条件：
1. 查询必须是等值连接。
2. 等值连接中的列必须具有相同的名称和数据类型。
上面定这一条SQL语句，执行逻辑可能有如下两种情况：
（1）既可以先从表 test1 里面取出 name=yangguo的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 yangguo
（2）也可以先从表 test2 里面取出 name=xiaolongnv 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name 的值是否等于 yangguo
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。
当然我们知道 “小表关联大表的效率高！”
执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行操作（例如：修改、查询）的权限。
select * from test where id=1;如果没有，就会返回没有权限的错误。
如果有权限，就打开表继续执行。
打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
比如我们这个例子中的表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：
（1）调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
（2）调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
（3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
