mysql学习笔记(1)之结构与执行流程

内容来源为六星教育，这里仅作为学习笔记

mysql大体结构

结构介绍

简化版mysql结构

MySQL逻辑架构整体分为三层 :

客户端 : 并非MySQL所独有，诸如 : 连接处理、授权认证、安全等功能均在这一层处理
核心服务 : 包括查询解析、分析、优化、缓存、内置函数(比如 : 时间、数学、加密等函数)，所有的跨存储引擎的功能也在这一层实现 : 存储过程、触发器、视图等
存储引擎 : 负责 MySQL 中的数据存储和提取，和 Linux 下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过 API 与存储引擎通信，这些 API接口 屏蔽不同存储引擎间的差异

体系结构详细

mysql的体系结构可以分为两层，mysql server层和存储引擎层，在mysql server层中又包括连接层与sql层

1.连接层

客户端或者应用程序通过接口(如：ODBC，JDBC)来连接MySQL，最先连接处理的就是连接层，连接层包括通信协议，线程处理，用户名密码认证三个部分，通信协议负责检测客户端版本是否与服务端兼容，线程处理是指每一个连接请求都会分配一个对应独立的线程，用户名密码认证创建的账号和密码，以及host主机授权是否可以连接到mysql服务器
1）连接之后的用户密码的效验
2) 对于效验之后的连接进行线程分配管理
3）对用户效验-》对于数据库表的操作权限的一个效验

2.sql层

sql层包含权限判断，查询缓存，解析器，预处理，查询优化器，缓存和执行计划；

1. 权限判断可以通过审核用户有没有访问某个库，某个表，或者表里某行的权限。
2. 查询缓存通过query cache进行操作，如果数据在query cache中，则直接返回结果给客户端
3. 查询解析器针对sql语句进行解析，判断语法是否正确，并生成解析器
4. 预处理器解决解析器无法解析的语义
5. 优化器对sql语句进行改写和相应的优化,例如：对连接表重排序，对外连接转内连接，代数等价法则，计算和减少常量表达式，自查询优化，早期终结，相等传递等，并生成最优的执行计划，然后就可以调用程序的API接口，通过存储引擎层访问数据；

3.存储引擎层
MySQL数据库的核心就在与存储引擎；
mysql数据库是开源的，所以用户可以根据mysql预定义的存储引擎接口编写自己的存储引擎，如果对现有存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性。

存储引擎可以分为mysql官网存储引擎和第三方存储引擎，innodb存储引擎早期就是第三方存储引擎，后来被oracle收购，同时也是mysql数据量OLTP在线事务处理应用最广泛的存储引擎。

3.1.Innodb存储引擎

1. Innodb存储引擎支持事务，其设计目标主要面向在线事务处理的应用，其特点是行锁设计，支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁，从mysql数据库5.5版本开始，Innodb存储引擎是默认的存储引擎。
2. Innodb存储引擎将数据放在一个逻辑表空间中，这个表空间就行黑盒一样由Innodb存储引擎自身进行管理，从mysql4.1版本开始，他可以将每个Innodb存储引擎的表单独存放到一个独立的ibd文件中，此外，Innodb存储引擎支持用裸设备来建立其表空间。
3. Innodb存储引擎通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了sql标准的4中隔离级别，默认为RR级别，同时，使用一种被称为next-key locking的策略来避免幻读现象的产生，除此之外，Innodb存储引擎还提供了插入缓存，二次写入，自适应hash索引，预读等高性能和高可用的功能。对于表中的数据存储，Innodb存储引擎采用聚集的方式，因此每张表的存储都是按主键的顺序进行存放，如果没有显示在表定义时指定主键，Innodb存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

3.2 myisam存储引擎

1. myisam存储引擎不支持事务。支持全文索引，主要面向一些OLAP数据应用，mysql5.5版本之前myisam存储引擎是默认的存储引擎，数据库系统与文件系统很大的一个不同之处在于对事务的支持，然而myisam存储引擎是不支持事务的，这个也不是很难理解，试想一下用户是否在所有的应用中都需要事务呢？在数据仓库中，如果没有ETL这些操作，只是简单的报表查询是否还需要事务的支持呢？此外，Myisam存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不是缓冲数据文件，这一点和大多数的数据库都非常不同。
2. Myisam存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件，可以通过使用myisampack工具进一步压缩数据文件，因为myisampack工具使用赫夫曼编码静态算法来压缩数据，因此使用myisampack工具压缩后的表是只读的，也可以通过myisampack来解压数据文件
3. 在mysql5.0版本之前，myisam默认支持表大小为4GB，如果需要支持大于4GB的myisam表时，则需要指定MAX_WORS和AVG_ROW_LENGTH属性，从mysql5.0版本开始，myisam默认支持256GB的表数据，这足够满足一般的需求

sql执行流程

MySQL 整个查询执行过程，总的来说分为 5 个步骤 :

1. 客户端向 MySQL 服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3. 服务器进行 SQL解析、预处理、再由优化器生成对应的执行计划
4. MySQL 根据执行计划，调用存储引擎的 API来执行查询
5. 将结果返回给客户端，同时缓存查询结果

例如 这一句sql select * from user where id > 10 and ( age > 21 or sex = 0);
sql的sql语句接口：接收到由连接层传递的SQL语句

1. 先判断下sql语句的类型
   (query(select)，dml(insert,update,delete)，ddl(alter)，status(show status)等)

2. 经过sql解析器，语法解析器
   假设是query （8.0之前：先判断查询缓存是否开启，如果开启查询查询缓存看是否命中，如果命中，那么直接返回结果，反之，则继续执行）
   解析器图解
   

3. 再经过优化器

   1. 获取表结构信息（字段信息，字段的类型，存储的位置，索引信息）获取的信息是查询的表的信息，如果是join那就是获取两张表的信息。
   2. 根据解析树进行条件过滤=》主要是一些没有意义的查询 例如：1=1
   3. 索引信息 来确定/判断执行计划
   4. 执行这个计划 在索引以及条件等来过滤

4. 调用存储引擎API来执行计划

笔记图