mysql查询语句执行全流程

我们在命令行输入sql语句或者借助框架查询接口在mysql执行查询，就可以得到与查询条件相匹配的数据，那mysql是用了什么样的架构和机制来实现的数据查询呢？下面就从架构和机制两方面来进行讲解。

架构

从图中可以看出，mysql框架分为两层：server层和存储引擎层。
server层包括mysql除数据存储和提取外的所有核心功能。

• 连接器，提供数据库连接，获取权限，维持和管理连接；
• 词法分析，输入的字符串做拆分和识别，解析出mysql的关键字等信息；
• 语法分析，将识别出来的字符根据语法规则做校验；
• 优化器，流转到优化器时已经确定了要在那些表上执行哪些数据操作，在该环节是确定使用那个索引以及多表关联场景下的语句执行顺序；
• 执行器，对语句中涉及的表进行鉴权，鉴权通过后使用表中指定的引擎接口执行语句操作；

存储引擎层提供数据的存储和提取。

机制

索引

索引，使用空间换时间的思想来加速查询。

以innodb存储引擎为例，非主键索引的叶节点存储的是主键，需要通过回表的方式查询主键索引获取到记录的详细信息。但是回表操作的开销较大，所以根据数据查询的实际场景做了索引覆盖，索引下沉等优化。
新建索引的指导思想是以最小的代价达到加速查询的目标，非聚簇索引叶节点存放的是主键键值，因此为了减少存储开销需要选择数据量较小的字段作为主键。主键索引叶子节点存放的是具体数据，而且innodb是以数据页的形式批量存储数据，结合操作系统顺序存储的方式需要设置递增主键来减少随机存储的开销。
普通索引和唯一索引在查询性能基本一致，但是唯一索引在写入时需要先读取数据判断是否有唯一键冲突，开销更大。因此非必要一般使用普通索引。

索引的选用，优化器在拿到待执行的sql后，会分析查询条件可以命中哪些索引，然后综合考虑扫描行数，回表开销，排序开销，索引区分度（基数）等开销来最终确定使用哪个索引，但是这种方式也会有反向优化的问题。

buffer

sortBuffer主要优化排序的执行效率，在sortBuffer大小不足以支撑排序时会使用排序文件来进行排序。具体到参与排序的字段，有两种选取形式：全字段排序和rowId排序。其中rowId排序需要额外的回表操作，会新增磁盘读取的消耗。所以存储引擎会优先使用sortBuffer+全字段排序。
在基于业务需求设计排序查询时，可以提前设计索引结构，借助索引字段的有序性避免存储引擎每次执行排序操作。

MVCC

MVCC（Multi Version Concurrency Control）多版本并发控制机制，与基于锁的并发控制相对应，减少了读多写少场景下读操作的加锁开销。MVCC是由读视图、undo log和行锁一同实现的读、更新分离控制。
一个事务创建时会向innodb事务管理系统申请新的事务id，每行数据又会对应多个版本，每行数据多版本就是借助事务id和undo log实现的。如下图所示行数据的多个版本按照链式串联起来，每个虚线箭头就是一个undo log。

每个事务开始后多会新建一个自己的读视图数组，也就是所谓的“快照”，数组分为三段：当前最小活跃事务id，最大活跃事务id+1，和中间区间活跃事务。
规则：

• 小于最小事务id的所有数据，均为当前事务创建前提交的数据可直接读取
• 大于最大事务id+1的所有数据，均为当前事务创建后产生的数据均不可读取
• 介于两者之间的数据，分两种情况
  • 事务id在数组中，则表明当前事务创建时事务已经创建但是未提交，所以不可读取
  • 事务id不在数组中，则表明当前事务创建时事务为已提交状态（非活跃），所以可以读取
    
    在了解了视图、undo log概念后一块看一下RR隔离界别下事务读取、更新数据的过程。

参考：
1.极客时间：MySQL实战45讲