1.MySQL是什么
MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。本篇文章主要会对MySQL的执行流程进行解析，所以你需要MySQL的使用经验。
2.MySQL的执行流程是怎样的
MySQL的执行流程可以理解为，一条sql语句从程序到数据库再把查询到的数据返回给用户，这一过程中发生了什么。
3.一条sql语句的完整执行流程
4.首先客户端需要连接上MySQL服务器，
MySQL是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。 这里我们拆分来看。
第一个是通信类型，MySQL的通信类型分为同步和异步。
同步通信的特点：
1、同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库， 线程会阻塞，等待数据库的返回。
2、一般只能做到一对一，很难做到一对多的通信。
异步通信的特点：
1、异步可以避免应用阻塞等待，但是不能节省SQL执行的时间。
2、如果异步存在并发，每一个SQL的执行都要单独建立一个连接，避免数据混乱。 但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量CPU资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。
所以说大部分都是用同步连接
第二个是连接方式：长连接或者短连接
MySQL既支持短连接，也支持长连接。短连接就是操作完毕以后，马上close 掉。 长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可 以使用这个连接。一般我们会在连接池中使用长连接。
保持长连接会消耗内存。长时间不活动的连接，MySQL服务器会断开。 默认最大长连接时间是8小时。
在5.7版本中MySQL服务默认允许151连接，最大可以设置成16384（2^14）。
第三个是通信方式：单工、半双工和全双工
单工：在两台计算机通信的时候，数据的传输是单向的。生活中的类比：遥控器。
半双工： 在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发 的话，也必须等我发给你完了之后才能给我发。生活中的类比：对讲机。
全双工： 数据的传输是双向的，并且可以同时传输。生活中的类比：打电话。
MySQL使用了半双工的通信方式
要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能 同时发生。所以客户端发送SQL语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的SQL语句有多大，都是一次性发送。
比如我们用MyBatis动态SQL生成了一个批量插入的语句， 插入10万条数据， values 后面跟了一长串的内容，或者where条件in里面的值太多，会出现问题。 这个时候我们必须要调整MySQL服务器配置max_allowed_packet参数的值（默认 是4M），把它调大，否则就会报错。
另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗。 所以，我们一定要在程序里面避免不带limit 的这种操作，比如一次把所有满足条件 的数据全部查出来，一定要先count一下。如果数据量的话，可以分批查询
5.建立连接后，接下来就是查询缓存
MySQL中自带了一个缓存模块，缓存的作用我们应该很清楚了，把数据以KV的形式放到内存里面，可以加快数据的读取速度，也可以减少服务器处理的时间。
key = sql语句
value = 数据
但是MySQL中的缓存是默认不开启的，因为他的缓存必须要求sql语句完全一致，多了一个空格都查不出来，并且只要这张表的数据发生改变，该表的所有缓存都会失效，所以该缓存的作用微乎其微。
所以缓存这一块，我们还是交给ORM框架（比如MyBatis默认开启了一级缓存），或者独立的缓存服务，比如Redis来处理更合适。在MySQL 8.0中，查询缓存已经被移除了
6.语法解析和预处理
这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。
1.词法解析
词法分析就是把一个完整的SQL语句打碎成一个个的单词。 比如一个简单的SQL语句：
它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。
2.语法解析
语法分析会对SQL做一些语法检查，比如单引号有没有闭合， 然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我 们把它叫做解析树（select_lex）。
任何数据库的中间件，比如Mycat，Sharding-JDBC（用到了Druid Parser），都 必须要有词法和语法分析功能，在市面上也有很多的开源的词法解析的工具（比如LEX 、Yacc）。
3.预处理器
它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。 预处理之后得到一个新的解析树。
7.查询优化（Query Optimizer）与查询执行计划
得到解析树之后，并不是直接执行sql语句。
1.什么是查询优化器
一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，就需要用到查询优化器，
查询优化器的目的就是根据解析树生成不同的执行计划（ExecutionPlan），然后选择一种最优的执行计划，MySQL里面使用的是基于开销（cost）的优化器，那种执行计 划开销最小，就用哪种。
2.查询优化器功能
MySQL的优化器能处理哪些优化类型呢？
举两个简单的例子：
1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表
2、有多个索引可以使用的时候，选择哪个索引。
实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的 算法实现尽可能优化查询效率的目标。
但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划。
3.优化器是怎么得到执行计划的？
expanded_query是优化后的SQL语句。
considered_execution_plans里面列出了所有的执行计划。
4.优化器得到的结果
优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。
当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为MySQL也有可能覆盖不到所有的执行计划。
我们怎么查看MySQL的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？
MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息。
8.存储引擎
1.存储引擎做什么
首先在关系型数据库中，数据是放在一个表里面的
我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时， 还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可 以把存储引擎叫做表类型。
在MySQL里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库 只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。
默认情况下，每个数据库有一个自己文件夹，任何一个存储引擎都有一个frm文件，这个是表结构定义文件。
2.常见存储引擎
MyISAM 和InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前， 默认的存储引擎是MyISAM，它是MySQL自带的。我们创建表的时候不指定存储引擎， 它就会使用MyISAM作为存储引擎。
5.5版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。 为什么要改呢？最主要的原因还是InnoDB 支持事务，支持行级别的锁，对于业务一致 性要求高的场景来说更适合
3.MyISAM
应用范围比较小。表级锁定限制了读/写的性能，因此在Web 和数据仓库配置中， 它通常用于只读或以读为主的工作。
特点：
支持表级别的锁（插入和更新会锁表）。不支持事务。
拥有较高的插入（insert）和查询（select）速度。
存储了表的行数（count速度更快）
适合：只读之类的数据分析的项目。
4.InnoDB
mysql5.7中的默认存储引擎。 InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级 为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束
特点：
支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）。
特殊的索引存放方式，可以减少IO，提升查询效率。
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
5.如何选择存储引擎
如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM
如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部 手册用C语言开发一个存储引擎
9.执行引擎
为什么我们修改了表的存储引擎，操作方式不需要做任何改变？
因为不同功能的存 储引擎实现的API是相同的。 最后把数据返回给客户端，即使没有结果也要返回。
10.MySQL 体系结构总结
1、 Connector：用来支持各种语言和SQL的交互，比如PHP，Python，Java的 JDBC；
2、 Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、 MySQL复制、集群等等；
3、 Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等
4、 SQL Interface：用来接收用户的SQL命令，返回用户需要的查询结果
5、 Parser：用来解析SQL语句
6、 Optimizer：查询优化器；
7、 CacheandBuffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key缓 存，权限缓存等等
8、 Pluggable Storage Engines：插件式存储引擎，它提供API给服务层使用， 跟具体的文件打交道
11. 架构分层
总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服 务层，和跟硬件打交道的存储引擎层（参考MyBatis：接口、核心、基础）。
12.接下来重点分析InnoDB
1.缓冲池 Buffer Pool
首先，InnnoDB的数据都是放在磁盘上的，InnoDB操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。 InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一 块内存区域里面。这个内存区域就叫Buffer Pool。
下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。
修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候， 我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘， 每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
2. InnoDB 内存结构和磁盘结构
Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。
1、Buffer Pool：
Buffer Pool缓存的是页面信息，包括数据页、索引页。
Buffer Pool默认大小是128M（134217728字节），可以调整。
如果页面写满了，InnoDB 用 LRU 算法来管理缓冲池（链表实现，不是传统的LRU，分成了young和old），经过淘汰的 数据就是热点数据。
2.change buffer
如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索 引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲 池中，从而提升更新语句（Insert、Delete、Update）的执行速度
最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge？ 有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、 redo log写满时触发。
3.（redo）Log Buffer
如果BufferPool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。
为了避免这个问题， InnoDB把所有对页面的修改操作专门写入一个日志文件，并且 在数据库启动时从这个文件进行恢复操作（实现crash-safe）——用它来实现事务的持 久性。
当然redo log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域 （Log Buffer）专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省 磁盘IO。
什么时候会把log pool的数据写到磁盘中
最后一点，进行redo log操作时候，要进行IO对数据直接写道磁盘也需要IO，为什么还要多此一举？
因为IO的方式不同，顺序IO消耗的时间短，而随机IO消耗的时间长。
13.磁盘结构
1.系统表空间 system tablespace
InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，ChangeBuffer和Undo Logs
数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）。
双写缓冲区：保存数据库一个页的副本，如果出现了 写入失效，就用页的副本来还原这个页，然后再应用redolog。DoubleWrite也是又两部分组成，内存和磁盘。
Undo Logs：undolog（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括select） 。 如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。
redo Log和undo Log与事务密切相关，统称为事务日志。
请记住那些表、区、页和行仅仅是逻辑结果他们都属于general tablespaces
2. Binlog
binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是 数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。 在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操 作重放一遍，来实现数据的恢复。
binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍
3.独占表空间 file-per-table tablespaces
我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置，默 认开启。
4.通用表空间 general tablespaces
通用表空间也是一种共享的表空间，跟ibdata1类似。 可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义
