mysql学习（一）架构与执行流程

一条SQL的执行流程

• 首先我们要使用工具操作数据库的话，第一步首先就是要跟数据库建立连接
  

通信协议

1. 首先，MySQL 必须要运行一个服务，监听默认的3306 端口。
2. 当要跟第三方对接的时候需要注意两件事情
   • 第一个就是通信协议，比如我们是用HTTP 还是WebService 还是TCP？
   • 第二个是消息格式，比如我们用XML 格式，还是JSON 格式，还是定长格式？报文头长度多少，包含什么内容，每个字段的详细含义。
3. 而mysql支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。

通信类型：同步或者异步

• 同步通信的特点：
  • 同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回。
  • 一般只能做到一对一，很难做到一对多的通信。
• 异步通信的特点：
  • 异步可以避免应用阻塞等待，但是不能节省SQL 执行的时间。
  • 如果异步存在并发，每一个SQL 的执行都要单独建立一个连接，避免数据混乱。

连接方式：长连接或者短连接

• 短连接就是操作完毕以后，马上close 掉。
• 长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。
• show global status like 'Thread%';可以查看MySQL 当前有多少个连接
  • Threads_cached：缓存中的线程连接数。
  • Threads_connected：当前打开的连接数。
  • Threads_created：为处理连接创建的线程数。
  • Threads_running：非睡眠状态的连接数，通常指并发连接数。

通信协议

mysql支持的通信协议有哪些？

1. 第一种是Unix Socket
2. 第二种是TCP/IP 协议，我们的编程语言的连接模块都是用TCP 协议连接到MySQL 服务器的， 比如
   mysql-connector-java-x.x.xx.jar。

通信方式

• 通信方式一般有三种：
  • 单工：在两台计算机通信的时候，数据的传输是单向的。
  • 半双工：在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发的话，也必须等我发给你完了之后才能给我发。
  • 全双工：数据的传输是双向的，并且可以同时传输。
• 而mysql使用的就是半双工方式，要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。所以客户端发送SQL 语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的SQL 语句有多大，都是一次性发送。

查询缓存

• 当客户端与服务端建立了连接，下一步就需要从缓存里面找，看有没有之前查询过的记录
  

• MySQL 内部自带了一个缓存模块。缓存的作用我们应该很清楚了，把数据以KV 的形式放到内存里面，可以加快数据的读取速度，也可以减少服务器处理的时间。但是mysql默认把缓存关闭，但是这是为什么呢，为什么mysql不愿意开启缓存呢？

  • 主要是因为MySQL 自带的缓存的应用场景有限，第一个是它要求SQL 语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。
  • 第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

• 所以在mysql8.0中已经被移除了

语法解析和预处理

• 我们没有缓存的话，就会跳过缓存，下一步就会对sql语句进行语法解析和预处理
  
• 而进行这两步是通过是MySQL 的Parser 解析器和Preprocessor 预处理模块完成的，主要做的事情是对语句基于SQL 语法进行词法和语法分析和语义的解析

词法解析

• 词法分析就是把一个完整的SQL 语句打碎成一个个的单词。

语法解析

• 语法分析，语法分析会对SQL 做一些语法检查，比如单引号有没有闭合，然后根据MySQL 定义的语法规则，根据SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。
  

预处理器

• 上面流程执行结束之后，有个问题，如果我写了一个词法和语法都正确的SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？
• 所以还是在解析的时候报错，解析SQL 的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

查询优化（Query Optimizer）与查询执行计划

• 得到解析树之后，是不是执行SQL 语句了呢？这里我们有一个问题，一条SQL 语句是不是只有一种执行方式？或者说数据库最终执行的SQL 是不是就是我们发送的SQL？

• 这个答案是否定的。一条SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

• 这个就是MySQL 的查询优化器的模块（Optimizer）。
  

• 查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

• 优化器优化之后，最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。
  

• 得到执行计划以后，SQL 语句是不是终于可以执行了？

• 但是还有问题，从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面？执行计划在哪里执行？是谁去执行？

• 这个时候就需要存储引擎

存储引擎

• 在关系数据库里面，数据存放在表中，我们可以把这个表理解成Excel 电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。
• 而在mysql中支持多个存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。

常见存储引擎

• MyISAM 和InnoDB 是我们用得最多的两个存储引擎，在MySQL 5.5 版本之前，默认的存储引擎是MyISAM，它是MySQL 自带的。我们创建表的时候不指定存储引擎，它就会使用MyISAM 作为存储引擎。5.5 版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL 开发的。最主要的原因还是InnoDB 支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合。
• 我们可以使用show engines ;查看数据库对存储引擎的支持情况

MyISAM存储引擎

• 应用范围比较小。表级锁定限制了读/写的性能，因此在Web 和数据仓库配置中，它通常用于只读或以读为主的工作。
• 特点：
  • 支持表级别的锁（插入和更新会锁表）。不支持事务。
  • 拥有较高的插入（insert）和查询（select）速度。
  • 存储了表的行数（count 速度更快）。

InnoDB存储引擎

• InnoDB 是一个事务安全（与ACID 兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。
• 特点：
  • 支持事务，支持外键，因此数据的完整性、一致性更高。
  • 支持行级别的锁和表级别的锁。
  • 支持读写并发，写不阻塞读（MVCC）。
  • 特殊的索引存放方式，可以减少IO，提升查询效率。

Memory引擎

• 将所有数据存储在RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbcluster 为大型分布式数据集提供了快速的键值查找。
• 特点：把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。

执行引擎（Query Execution Engine）返回结果

• 执行引擎使用执行计划去操作存储引擎，最后把数据返回给客户端，即使没有结果也要返回。

• 以上就是一条sql的执行过程

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. Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等；
8. Pluggable Storage Engines：插件式存储引擎，它提供API 给服务层使用，跟具体的文件打交道。

• 总体上，我们可以把MySQL 分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层（参考MyBatis：接口、核心、基础）。

更新一条sql是如何执行的（还在理解）

• 我们说的update 操作其实包括了更新、插入和删除，更新流程和查询流程的基本流程是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作。

缓冲池Buffer Pool

• 首先，InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫Buffer Pool。
• 下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。
• 修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

InnoDB 内存结构和磁盘结构

• 主要分为3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。

Buffer Pool

• Buffer Pool 缓存的是页面信息，包括数据页、索引页。
• Buffer Pool 默认大小是128M（134217728 字节），可以调整。
• 内存的缓冲池写满了怎么办？
  • InnoDB 用LRU算法来管理缓冲池（链表实现，不是传统的LRU，分成了young 和old），经过淘汰的数据就是热点数据。
• 当需要更新一个数据页时，如果数据页在Buffer Pool 中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘IO，有没有优化的方式呢？

Binlog