Mysql-架构和SQL执行顺序

引言

最近开始捡mysql了，主要从Mysql架构，索引，锁，事务，性能分析和优化几个方面写几篇博客，这篇从mysql架构开始

逻辑架构

这是mysql的逻辑架构图：

主要可分为四大块，从底层开始，分别为：

• 文件系统：
  所有的数据最终必须还是得存放在磁盘上
• 可插拔存储引擎
  数据的读写操作都是存储引擎完成的，可以理解成操作磁盘数据的组件，常用的有InnoDB,MyISAM等
• mysql服务器
  mysql server，用于处理客户端的请求，语句解析，查询优化，调用存储引擎返回结果集
• mysql连接器
  主要用于与服务器产生连接，如mysql client, JDBC等等

---

当然重点在存储引擎和mysql服务器这块，我们先介绍一下常用的存储引擎，然后分析mysql服务器的执行流程

存储引擎

作用：为MySql服务器提供接口操作文件系统
MySQL的存储引擎是针对表进行指定的。（engine=InnoDB\myisam），常见的存储引擎有InnoDB和MyISAM。
InnoDB
支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
MyISAM
插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。
两者对比如下：

MySql服务器

MySql服务器包括：
连接池：连接管理，权限管理
SQL接口：用于接受SQL：DML DDL
解析器：用于词法分析和语法分析：将SQL转化成语法树，然后验证SQL语法是否正确
查询优化器：用于优化SQL：生成执行计划，选择索引
查询缓存：把SQL之后的hash和执行后的结果集保存，同一个SQL走缓存，不用再查询一次（这个在MySql8后取消了）

---

一条SQL在服务器中的流程如下：

比如SQL：select a.name ,b.course_name from student a left join course b on a.course_id = b.id
在解析器和查询优化器优化处理后，真实的执行顺序就是：

1 – FROM <left_table>
2 – ON <join_condition>
3 – <join_type> JOIN <right_table> 第二步和第三步会循环执行
4 – WHERE <where_condition> 第四步会循环执行，多条件执行顺序从左往右
5 – GROUP BY <group_by_list>
6 – HAVING <having_condition>
7 – SELECT 分组之后才会执行SELECT
8 – DISTINCT <select_list>
9 – ORDER BY <order_by_condition>
10 LIMIT <limit_number> 前9步都是SQL92标准语法 limit是MySQL的独有的

物理结构

Mysql的所有数据，索引最终都是存储在文件系统上的（SHOW VARIABLES LIKE ‘%datadir%’; 可以查看文件存储目录），如图：

首先进入/var/lib/mysql目录，里面存放的是
ibdata文件（使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件，重启一次生成一个）
以及各个database，然后进入test这个目录：
.frm：主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件

InnoDB存储引擎架构

InnoDB内存结构

Buffer Pool缓冲
看图，上面一部分就是InnoDB的内存结构，其中有一块叫Buffer Pool缓冲池，其中又包含数据页，索引页等，mysql的读写操作都是针对这部分的。

意思就是当我们做增删改查操作时，都是先到Buffer Pool缓冲池中操作数据，这时还不是直接操作磁盘中的数据。那么操作之后Buffer Pool缓冲池中的数据和磁盘中的数据还不是一致的，那么该数据页为脏页

---

redo log buffer重做日志缓冲
另外有一块叫redo log buffer重做日志缓冲（内存结构图中最后一个）
先解释重做日志（redo log）: redo log如果要存储数据则先存储数据的日志 ， 一旦内存崩了 则可以从日志找。即当事务提交时，先写重做日志，然后再择时将脏页写入磁盘。如果发生宕机导致数据丢失，就通过重做日志进行数据恢复。

这里的重做日志缓冲就是将重做日志先缓存起来，然后将多次IO操作缓冲为一次IO操作，提升性能。
这是 redo log 落盘机制， 给个图对应着看：

InnoDB磁盘文件

InnoDB的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。二进制文件(binlog)等文件是MySQL Server层维护的文件，所以未列入InnoDB的磁盘文件中
系统表空间
在MySql服务器物理结构的图中有一个ibdata1文件这个就是系统表空间它是被多个表共享的，包含

• 数据字典(data dictionary)：记录数据库相关信息
• doublewrite write buffer：解决部分写失败（页断裂）
• insert buffer：内存insert buffer数据，周期写入共享表空间，防止意外宕机
• 回滚段(rollback segments)
• undo空间：undo页

undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undolog（也看成数据库数据）的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。如上图所示，表空间中有回滚段和叶节点段和非叶节点段，而三者都有对应的页结构

用户表空间
在MySql服务器物理结构的图中还有.ibd文件，这是用户表空间，用户表空间的命名规则为：表名.ibd；其中frm文件是表结构定义文件，记录每个表的表结构定义；每个表的数据和索引都会存在自已的表空间中

系统表空间和用户表空间关系如图所示：

redo日志文件和归档文件
在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，这就是InnoDB的重做日志文件(redo log file)，它记录了对于InnoDB存储引擎的事务日志