MySQL探秘(一)MySQL 基础架构分析
1.1MySQL 基本架构概览
下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。
先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在 1.2 节中会详细介绍到这些组件的作用
架构示意图，可以理解出SQL语句在MySQL的各个功能模块中的执行过程。
简单来说 MySQL 主要分为 Server 层和存储引擎层：
Server 层：
主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数（如日期、时间、数学和加密函数等）等，还有一个通用的日志模块 binglog 日志模块。（不同的存储引擎共用一个Server层）
连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。
存储引擎：
1.主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。
2.执行create table建表的时候，如果不指定引擎类型，默认使用的就是InnoDB)。
3.在create table语句中使用engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同。
1.2 基本组件介绍
当向MySQL发送一个请求的时候，MySQL到底做了什么：
- 客户端连接到Server层并发送一条查询语句。
- Server层先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
- Server层进行语法解析、预处理，再由优化器生成选择对应的执行计划。
- Server层根据优化器选择的执行计划，再调用存储引擎的接口来执行查询。
- 将接口执行结果返回给客户端。
下面来详细介绍在一条语句执行到过程中参与到组件
1) 连接器
在查询 SQL 语句前，肯定要先建立与 MySQL 的连接，这就是由连接器来完成的。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令为：
Mysql -h$ip -p$port -u$user -p
输入密码，验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。结果如下
+—-+——+—————–+——+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+—————–+——+———+——+———-+——————+
| 2 | root | localhost:59550 | NULL | Sleep | 27 | | NULL |
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
+—-+——+—————–+——+———+——+———-+——————+
2 rows in set (0.00 sec)
客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数 wait_timeout 控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。
MySQL 使用了半双工的通信方式
要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能 同时发生。所以客户端发送 SQL 语句给服务端的时候，(在一次连接里面)数据是不能 分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送。
比如我们用 MyBatis 动态 SQL 生成了一个批量插入的语句，插入 10 万条数据，values 后面跟了一长串的内容，或者 where 条件 in 里面的值太多，会出现问题。
这个时候我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值(默认 是 4M)，把它调大，否则就会报错。
另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想 要的数据就中断操作，这个时候会对网络和内存产生大量消耗。
所以，我们一定要在程序里面避免不带 limit 的这种操作，比如一次把所有满足条件 的数据全部查出来，一定要先 count 一下。如果数据量的话，可以分批查询。
长连接和短连接
- 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
- 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断
开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
怎么解决这个问题呢？可以考虑以下两种方案：
- 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
- MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
执行一条查询语句，客户端跟服务端建立连接之后呢?下一步要做什么?
2) 查询缓存(MySQL 8.0 版本后移除)
在建立连接后，就开始执行 select 语句了，执行前首先会查询缓存。
1.MySQL 拿到查询请求后，会先查询缓存，看是不是执行过这条语句。执行过的语句及其结果会以 key-value 对的形式保存在一定的内存区域中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个
value 就会被直接返回给客户端。
2.如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，会提升效率。
3.查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
4.MySQL提供了这种“按需使用”的方式。可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：
Mysql> select SQL_CACHE * from user where id = 1;
5.注意，大多数情况下我会建议你不要使用查询缓存，为什么呢？
主要是因为 MySQL 自带的缓存的应用场景有限，第一个是它要求 SQL 语句必须一 模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL。
第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对 于有大量数据更新的应用，也不适合。
所以缓存这一块，我们还是交给 ORM 框架(比如 MyBatis 默认开启了一级缓存)， 或者独立的缓存服务，比如 Redis 来处理更合适。
(MySQL 8.0版本不在提供查询缓存这个功能)
3) 分析器
MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
1.词法解析
一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。
比如一个简单的 SQL 语句:
select name from user where id = 1;
它会打碎成 8 个符号，每个符号是什么类型，从哪里开始到哪里结束。
2.语法解析
语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合， 然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树(select_lex)。
如果你 SQL 语句不对，就会收到 You have an error in your SQL syntax 的错误提醒，比如下面这个语句 from 写成了 form。
mysql> select * form user where id = 1; - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form user_info where id = 1' at line 1
一般语法错误会提示第一个出现错误的位置，所以要关注的是紧接 use near 的内容。
3.预处理器
预处理器则根据一些MySQL规则进行进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。 预处理之后得到一个新的解析树。
如果我写了一个词法和语法都正确的 SQL，但是表名或者字段不存在比如下面这个语句 user1 不存在:
mysql> select * from user1 where id = 1; ERROR 1146 (42S02): Table 'test.user1' doesn't exist
完成这 3步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
4) 优化器
查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划。
MySQL使用基于成本的查询优化器(Cost-Based Optimizer，CBO)。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。 优化器会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。
可以使用这个命令查询上次查询的开销:
show status like 'Last_query_cost';
如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。
MySQL 的优化器能处理哪些优化类型呢? 举两个简单的例子:
1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。
2、有多个索引可以使用的时候，选择哪个索引。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（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。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。
优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
5) 执行器
MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
- 开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示(在工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。
mysql> select * from user where ID=1; ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'
- 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
- 所以该语句的执行流程则是这样：
- 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
- 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
- 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
- 至此，这个语句就执行完成了。