谈一谈mysql innoDB的体系结构与执行细节

mysql架构

Mysql服务器可以分为server层和存储引擎层，其中server层包含连接器、查询缓存、分析器、优化器、执行器。而存储引擎最大的特点就是基于表的和插件式的，可以根据不同的应用建立不同的存储引擎表，mysql默认使用innoDB。

上面这张图是mysql的架构图。一个连接进程和Mysql数据库实例通信本质上就是两个进程在通信，数据库只能算作一组文件的集合，而正在与底层数据库打交道的是数据库实例这个程序。

其中连接层负责将数据库实例与客户端程序建立TCP连接，并且完成一些认证、校验工作，这一层在大多基于C/S的服务都有类似架构。
而服务层完成了大多数核心服务，包括sql的解析、分析、优化、缓存以及所有的内置函数。所有跨存储引擎的功能也都在这一层实现：存储过程、触发器、视图等。
而存储引擎才是正在负责mysql中数据的存储和提取，存储引擎是基于表的，而且是以插件的形式存在，可以根据不同的表更换不同的存储引擎。（存储引擎就是如何管理操作数据的一种方法，数据最终需要存储在磁盘（文件系统）上）
存储层主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互，这才是数据真正落地的地方，需要操作系统（如文件系统）和硬件（如磁盘）的支持

每个客户端连接（服务器为之维护客户端套接字）都会在服务器进程中拥有一个线程，每个查询都会在单独的线程中执行，对应一个CPU核心中执行。服务器会负责线程的分配和撤销（线程池），因此不需要为每一个新建的连接创建或者销毁线程

mysql执行流程

mysql客户端（例如命令行打开的mysql client）与mysql服务器建立连接后，服务器需要对客户端进行认证，一旦认证成功，服务器还会继续验证该客户端是否具有执行某个特定查询的权限。

Mysql会解析查询（sql语句），并创建内部数据结构（语法解析树），然后对其进行各种优化（如选择合适的索引、重写查询等）。用户可以通过特殊的关键字提示（hint）优化器，影响他的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以得知服务器是如何进行优化决策的，提供一个参考基准，便于用户重构sql和schema、修改相关配置

一个查询请求在mysql的基本处理步骤：
【1】MySQL客户端通过连接器与MySQL服务器建立TCP连接，然后服务器对客户端进行一些权限认证操作
【2】对于select语句，在解析sql之前，服务器会先检查查询缓存（query cache），如果能够在其中找到对应的sql，服务器就会直接返回查询缓存中的结果集。（该部分已经在MySQL8被移除，因为查询缓存失效十分频繁，一旦进行了任何一个更新操作，整个缓存都会被清空）
【3】分析器对查询语句进行扫描、词法分析及语法分析。如果sql语法有误，就会收到you hava an error in your SQL syntax的错误提示。

之后还会对合法的查询语句进行语义检查。根据数据字典中有关的模式定义检查语句中的数据库对象如关系名、属性名是否存在。如果是视图则通过视图消解方法将对视图的操作转换为对基本表的操作。还会根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查。
检查通过后便将SQL查询语句转换为等价的关系代数表达式。
这个过程把数据库对象的外部名称转换为内部表示（查询树或语法分析树）

【4】优化器会生成执行计划，并且选择合适的索引，为字段选择合适的查询位置，为多表确定正确的连接顺序等。
【5】根据优化器生成的执行计划，由代码生成器生成执行这个查询计划的代码加以执行并回送查询结果。执行器根据表的引擎定义，去调用相应引擎提供的读取接口，最终得到接口返回的结果。

innoDB体系架构

存储引擎和文件系统打交道，负责将用户对数据库实例的访问，同步到数据库系统（作为应用层一部分的数据库系统接收操作系统的文件系统或磁盘系统的服务）。数据库实例是由后台线程以及一个共享内存区组成，是正在与数据库文件集合交互的。而数据库系统是物理操作系统文件或其他形式文件类型的集合

内存池

缓冲池技术避免每次都访问磁盘，而是查询内存上的缓存数据，以加速对数据的访问。缓冲池以页（16K）为单位
Mysql缓冲池用于弥补CPU处理速度和磁盘IO速度的瓶颈（不能老让CPU等着I/O行为的完成）
缓冲池可以起到加速读和加速写的作用。数据库实例的读操作，会将磁盘的页存放到缓冲池中，下次读取相同的页时，首先从池中读，未命中才读取磁盘。数据库实例的写操作，首先修改缓冲池中的页，脏页被后台线程以一定频率刷新到磁盘上。（索引修改操作也可以经过change buffer进行优化）

buffer pool

innoDB存储引擎是基于磁盘存储的，并将其中的数据按照记录页的方式进行管理，对数据页的修改是基于缓冲池中的页的，并按照一定频率异步刷新到磁盘上。

缓冲池上通过多个链表管理脏页、空闲页和正常页。并且基于LRU算法进行管理
缓冲池对LRU列表进行了分代优化，默认以5:3分为了新生代和老年代，预读页优先放入老年代，当页被正在访问时才进入新生代，以防止热页被预读入的新页淘汰。同时在老年代设置了存留阈值，只有在老年代停留时间超过该阈值才存入新生代，以防止缓冲池污染问题

脏页通过检查点机制刷回磁盘，脏页由缓冲池中的flush链表管理，检查点技术可以将部分脏页刷回磁盘，减少数据库恢复的时间。当缓冲池不够用（LRU换出）时，将脏页刷会内存，当redo 日志不可用时（循环写入时，将被覆盖的部分），刷新脏页。

当数据库实例发出写请求，首先查询缓冲池，如果存在：
【1】直接修改缓冲池中的页（一次内存操作）
【2】写redo buffer（一次内存操作）（这里的写是广义上的写，具体行为看参数设置）
否则会先执行一次IO操作，将数据页读入缓冲池然后执行上述操作。

脏页刷盘时机：
【1】mysql正常关闭时
【2】后台线程（主线程或脏页刷新线程page cleaner thread）空闲时
【3】缓冲池换出脏页时（空闲链表没有页面可用，需要进行页面换出操作）
【4】重做日志满了，需要覆盖写，则缓冲池对应脏页应被刷新（如果redo满了，则用户对内存redo log将被暂停，检查点将向前推进，直到具有足够的空间）

redo logo buffer

上面提到的缓存池主要是对数据页、索引页、change buffer等数据的缓存，innoDB的内存区还有重做日志缓冲，innoDB首先将重做日志信息放入该缓冲区，然后按照一定频率将其刷新到日志文件中（磁盘还是文件系统的内核缓冲区，需要看具体设置的参数）
重做日志缓存的大小由配置参数innoDB_log_buffer_size控制

Redo log记录着磁盘数据的变更日志，以物理存储的最小单位“页”进行记录，如对某个页面的某个偏移量做了某个更新操作，记录的是物理信息，是磁盘数据级别的。

重做日志的刷新时机
【1】主线程每秒负责将一部分重做日志缓存的内容刷新到日志文件
【2】（默认）每个事务提交时，会进行一次重做日志缓存的fsync调用
【3】当重做日志缓冲池剩余空间大小小于1/2时，进行刷新操作

其中主线程每秒刷新，主要是为了优化事务提交的速度。

当用户执行修改操作后，首先会写内存中的数据页，其次写内存中的redo log，当用户提交事务时，默认情况下，redo log刷入磁盘，此时内存中的数据页则一般不会刷入磁盘，但是只要要保证redo log刷入磁盘，就可以保证持久性了

此设计保证了：
【1】用户如果能够从内存中读取到页面，那么页面一定是最新值
【2】如果用户无法从内存中读取页面，那么从磁盘读取的也一定是最新值

其实除此以外还有一部分额外的内存池——对数据结构本身进行内存分配（缓冲池帧、重做日志缓存帧。各种变量等系统内存的维护）

后台线程

innoDB是多线程的模型，后台具有多个不同的后台线程。
【1】主线程
主线程负责将缓冲池中的数据异步刷新到磁盘（通过fsyn调用将内存中的数据同步到磁盘）（这里的数据包括刷新redo log、数据页、索引页等，包括主动和被动行为），保证数据的一致性，同时还负责脏页的刷新，对change buffer（延迟写操作）进行合并、undo页回收等。

后台线程中的purge线程负责回收不被需要的undo页面，page cleaner用于刷新脏页。他们都一定程度上分担了主线程的工作量。

【2】IO线程
innoDB使用了大量异步请求来处理写I/O请求，而当I/O完成都会触发回调函数，I/O线程就是负责处理I/O请求的回调逻辑的
【3】undo页回收线程
【4】脏页刷新线程

innoDB重要特性

【1】change buffer，对非唯一普通索引进行修改后，如果页面不再内存中，则不需要立即读出索引页并进行修改，而是延迟这个动作的执行，等到之后页面被读出，再执行合并操作并恢复到内存中的缓冲池。后台线程也会时不时地执行合并操作（内存不够用、mysql正常退出等情况也会执行合并操作）。
changeBuffer的目的就是降低对磁盘的随机读次数（不需要专门地从磁盘中读出到内存），同时不用专门将页面调入缓冲池，节省了内存空间。

changeBuffer和数据页一样，是物理页的一个组成部分，底层是一颗B+树，负责对索引表的辅助索引进行changeBuffer，存放于共享表空间。changeBuffer在内存中有缓存，同时也可以持久化到磁盘中。

如果要修改的字段是唯一索引或者主键，还需要加载数据页（从磁盘读出来），判断是否违反唯一性约束，从而导致changeBuffer没有了意义。

【2】double write
主要是防止页本身损坏导致的重做失效，使用重做日志进行恢复之前，用户需要一个页面的副本，当发送写失效时，先通过页的副本对损坏页进行恢复，之后再进行重做。

对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer写入物理磁盘，之后doublewrite buffer中的各个页面会写入各个表空间文件中。
如果操作系统将页面写入磁盘的过程中发生了崩溃，恢复过程中，innoDB存储引擎可以从共享表空间中的doublewrite 中找到一个该页的副本，并复制到表空间文件中重新执行日志重做。

doublewrite由内存中的doublewrite buffer和共享表空间中的两个连续的区（2M）组成。doublewrite buffer缓存buffer pool的脏页（备份），防止写磁盘过程中崩溃导致页损坏而使得无法恢复，可以通过innoDB缓存找到页面的备份去重新执行崩溃恢复操作

【3】自适应哈希索引
自适应哈希索引是根据**缓冲池（内存）**中的B+树页面进行构造的，建立速度很快，不需要对整张表构建索引。innoDB引擎会自动根据访问的频率与模式自动的为某些热点页面建立哈希索引。（占用一部分buffer pool的内存空间）
建立条件比较严苛：访问模式一样（查询条件一样）。如果以where a = 1 被查询了100次。而且哈希索引只能用于等值查询。

【4】刷新临界页
当刷新一个脏页的时候，innoDB存储引擎会检查该页所在区的所有页，如果存在脏页则一起刷新。基于该特点，多个异步I/O可以合并为一个同步I/O操作。对于传统机械硬盘建议开启该特性，而对于固态硬盘则没有必要。（可以通过设置innoDB flush neighbors = 0 关闭该特性）

【5】异步I/O
用户可以在发出一个I/O请求后立即再次发送另一个I/O请求，这些I/O请求将会调用相应的回调函数，innoDB的后台I/O线程会处理这些异步请求的回调。

AIO的一个优势是可以将多个I/O合并为一个I/O操作。

innoDB与myisam对比

innoDB的存储引擎是mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。相对于MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引，因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。
innoDB的设计目标主要面向在线事务处理（OLTP）的应用（偏I/O密集型）。它的特点就是行锁设计。支持外键，支持类似于oracle的非锁定读（默认读取不会加锁），通过MVCC来获得高并发性。
对于表中数据的存储，innoDB存储引擎采用聚集的方式，因此每张表的存储都是按主键的顺序进行存放。

支持外键的存储引擎只有InnoDB.在创建外键时，要求父表必须有对应索引，子表在创建外键的时候，也会自动的创建对应的索引。

MyISAM不支持事务和外键和行级锁,，支持全文索引，主要面向在线分析处理（OLAP）的应用（偏CPU密集型），优势是访问的速度快。对事务的完整性没有要求或者以SELECT/INSERT为主的应用基本上都可以这个引擎来创建表。有一个缺陷——崩溃后无法安全恢复。
事务是在引擎层实现的，myisam不支持事务是被innoDB取代的重要原因之一。

Myisam索引文件和数据文件是分开存放的。主键索引是非聚簇索引。MyISAM的存储引擎表由MYD和MYI组成，MYD用于存储数据文件，MYI用于存储索引文件。

两个存储引擎的对比
Myslam的每张表中，数据、索引、元信息分开存储，而innoDB所有的表默认存储在一个表空间，或者若干个表空间文件。其中数据和索引存放在一个文件中。（Myslam的缓冲池仅缓存索引文件，数据文件交给操作系统的内核缓存来维护，因此访问数据部分时需要涉及一次系统调用）
Myslam索引表的叶子节点存储的是“行号”，这种分布方式很容易创建索引，且辅助索引和主键索引的存储方式相同。（而innoDB的辅助索引和主键索引采用了不同的存储方式，这种策略减少了当出现行移动或者数据页分裂时，辅助索引的维护工作）
Mylsam的记录按照插入顺序保存，而innoDB的记录按照主键顺序存储。
innoDB支持行锁、外键和事务。Mylsam仅仅支持表锁。
Mylsam是堆表，innoDB是索引组织表。

Mylsam比innoDB查询快，因为innoDB查询时维护的东西多：
【1】innoDB需要缓存数据块（页）和索引块，而mylsam只需要缓存索引块
【2】innoDB寻址要映射到块，再到行。而mylsam记录直接是文件的偏移量。
【3】innoDB要维护MVCC相关的数据结构

关键字实现细节

limit

Limit语法由偏移量offset和取值数量size组成，其中偏移量默认从0开始，其中limit 10000，10代表从第10001条数据开始取，取10条记录，当offset特别大的时候，效率非常低。

以下是实验数据：
select * from user limit 0,100 ---------耗时0.03s
select * from user limit 10000,100 ---------耗时0.05s
select * from user limit 100000,100 ---------耗时0.13s
select * from user limit 500000,100 ---------耗时0.23s

查询id只是一种优化方式，相当于覆盖索引，但是仍然需要扫描m+n的数据（只不过生成的结果集占用内存小一些，快一些而已）

一种比较常见的limit优化方式：先拿到目标主键，外表关联数据时会走主键索引。而且内部查询主键性能比查询全字段好一些

Select * from Student 
Join (Select id  from Student limit 1000,10 ) as stu
On student.id = stu.id

经过实验发现：explain select id from emp limit 10000,10走了覆盖索引，而且是直接扫描叶子节点（type值为index），而explain select * from emp limit 100,10执行的是全表扫描（type类型为all）

order by

如果order by没有走索引，那么使用explain得到的优化器解释一定会在extra选项中出现using filesort一项。
一般mysql会为每个线程分配一块内存用于排序——排序缓冲区sort buffer。
如果内存够用，mysql会优先使用全字段排序——将select中的字段和排序字段全部载入排序缓冲区，这样的好处是可以直接在内存区中构建结果集，避免了回表。
如果mysql认为无法为排序分配足够多的内存区域，则只会取核心字段（主键和排序字段），在排序内存区使用排序字段进行排序后，需要根据主键进行回表取数据行，回表操作增加了随机I/O的次数，是时间换空间的操作。

如果sort buffer不够用，那么每次只能取sort_buffer容量大小的数据进行不完整排序，从而进行了多次I/O和排序。通常可以修改相关参数：【1】增大sort_buffer_size参数【2】增大max_length_for_sort_data参数
也可以考虑减少不必要的select字段。

如果内存不够使用，会基于临时文件排序，排序效率低下。

临时文件排序基于归并算法，将需要排序的数据拆分到多个临时文件中，进行并行排序，然后最终返回一个合并后的结果集，因为是磁盘文件中进行，因此性能下降很多

使用了索引的排序
index(a,b) :where a=1 order by b
先从索引中定位第一个满足添加a=1的元素数据页，回表查出select字段，拼装结果集。然后去辅助索引表查询下一个。（如果一个页已经被在载入内存缓冲池，就不用IO了）
注意，此处没有进行单独的排序，因为是按顺序从索引树中叶子节点中取的页面，而叶子节点是默认升序的。此过程既不需要临时表，也不需要额外排序操作

如果select的内容是id就不用回表了（即索引上的信息足够满足查询条件，可以进行覆盖索引优化，就不需要再到主键索引表上取数据了）

group by

语句执行的过程中会创建内存临时表，其中存放了group by字段和select字段，扫描指定表，向临时表插入记录。最终按照group by字段排序。如果不需要排序则使用order by null.
如果过程中发现内存临时表放不下数据，则会转换为磁盘临时表/临时文件。

临时表的作用是用于存储中间结果集，以便于排序。如果对group by 字段建立索引，那么就不用额外排序了，从左到右顺序扫描并且依次累加即可

union

A union B .这个过程会创建用于存放临时数据的临时表。执行第一个查询后存入该表，然后第二个查询尝试插入临时表。最后从临时表中按行取出数据，填入结果集，并且删除临时表。
Union具有去重的语义，这是通过临时表主键id唯一性约束实现的。

如果使用union All则失去去重语义，执行的时候会附带执行一次子查询，并将得到的结果作为结果集的一部分发给客户端，同时也不会使用临时表。

count

Count(主键id)，innoDB会遍历整张表，将每个数据行id取出，并返回给server层，server层将其累加。
Count（字段），如果字段被定义为非空，按行累加。如果允许为null，还需要额外读取数据行中的值，判断不是null才累加
Count(*)不会取出字段，而是直接按行累加。
Count(1)不取值，每遍历一行就对结果集填入一个1，server按行累加

效率对比:count(非空字段)最低，其次是count(主键id)，因为它们都涉及取值操作。而按行累加的count(*)最佳，count(1)次之

join

不经过任何优化的Join其实就是一个双循环语句、对两张表进行笛卡尔积。那么总共需要匹配的次数就等于：外层表行数 * 内层表行数，如果我们对“内循环中的表”或者说“被驱动表”建立索引，那么就可以将次数优化为：外层表的行数 * 内层表索引的高度
因此一个常用的优化就是，选择小表作为驱动表，被驱动表连接字段建立索引。

mysql还会为连接分配缓存块join buffer，一次性将多条驱动表的字段去和被驱动表进行匹配。
通过一次性缓存外层表多条记录，来减少内层表的扫表次数。如果无法使用索引嵌套循环，数据库默认使用该算法

select语句的执行顺序

select的机读流程：

每一个步骤都会生成一个虚表，并且下一步生成的虚表都是在某一个虚表的基础上生成的，这些中间生成的表对用户不可用，只有最后一步生成的表才会返回给调用者。
【1】from：对两个表执行笛卡尔积（类比双循环），生成虚表A
【2】对虚表A进行ON筛选，只有满足条件的行才会被连接并插入虚表B
【3】外连接（外部行连接）：保留主表，找到未匹配的、主表一侧的行添加到B，生成C，如果from子句中多于两个表则重复以上步骤，直到生成唯一的虚拟表。其中从表中与主表没有对应的字段（主表有值，从表没有值）将被赋值为NULL
【4】对C进行where筛选，符合条件的行插入虚表D
【5】按照group by字段对D进行分组（先通过临时表排序再分组），生成虚表E
【6】having子句对E进行筛选，将符合条件的语句插入F
【7】select从F中选取目标列，生成G
【8】distinct语句去除重复的行，生成H

如果指定了distinct子句，则会创建一张内存临时表（内部不够，使用文件排序），这张临时表的表结构和G表一样，但是distinct列被增加了唯一索引

【9】将H中的数据行按照order by字段进行排序，并生成一个游标。注意，此时select执行完毕，因此可以使用select指定的别名。

这一步返回的是游标。由于SQL是基于集合的，因此不存在预先排序，对表进行排序的查询可以返回一个对象，包含按特定物理顺序组织的行，这种对象就是游标。

【10】从游标的开始处选择指定数量或者比例的行(例如进行limit操作)，生成结果集返回调用者。

Limit n,m 从第n条记录（0开始）选择m条记录，但是数据量很大的时候非常低效，因为limit每次从头开始扫描（移动游标对象扫描记录，不走索引，因为走到这个位置时索引的作用已经发挥完毕了，这里返回的游标就是一个迭代器的指针）。

使用了Order by子句的查询不能用作表 表达式（视图、子查询、派生表）

上面这种写法会保存，因为from子句的内容中不是一张表（带有排序作用的order by子句的查询），而是一个对象，其中的行按特定的顺序组织在一起，这种对象就是游标。

update语句的执行流程

更新语句update、insert等都是默认开启自动提交事务的，因此当一条记录被更新后，缓冲池中的页面必然会变脏。但是一般不会同步刷新页面，而是通过后台线程异步刷新页面。当事务提交时，默认情况下会将redo log的内容同步到磁盘，当完成这个操作的时候（redo log更新成功（这里仅考虑redo log）），整个更新操作就算完成了。因为redo log是实现原子性和持久性的关键元素，如果日志被成功更新，那么即使脏页发生丢失，也可以通过redo log进行恢复操作
核心就是WAL（write ahead log）——先同步日志，再将数据写入磁盘

innoDB的redo log是固定大小，如果满了就会覆盖开头，循环写入。

两个重要参数（其实就是双指针）：write pos（当前记录的位置）和checkpoint（当前要擦除的位置）
一边写一边后移，擦除记录前需要将记录更新到数据文件中，【w,c】之间都是可以使用的空间，如果w追上c则表示redo log满了，暂时不能执行新的更新。
有了redolog，innoDB可以保证即使数据库发送异常重启，之前提交的记录都不会丢失，被称为crash-safe崩溃安全。

Redo log是innoDB独有的日志。Server的日志叫binlog（归档日志/二进制日志），所有引擎都可以使用。Redo log是物理日志，记录“某个页面上做了什么修改”，而binlog是逻辑日志，记录的是语句级别的（statement）或者行的逻辑修改级别的（row）。而且Binlog是追加写入的，达到一定大小后会切换下一个文件，不会覆盖以前的日志。

Redolog不是记录数据页更新之后的状态，而是记录某个页面、某个偏移量做了什么改动，是物理数据层面的。
Binlog有两种模式，statement模式记录SQL语句，row模式记录行的内容（两条，更新前和更新后）

binLog记录了数据库执行更改的所有逻辑操作（如SQL语句），用于做数据规定、数据恢复和数据复制。不具备崩溃恢复功能。
两阶段提交

【1】执行器先调用引擎接口取得ID=2的这行记录，如果数据页存在于内存直接返回给执行器，否则先从磁盘读入内存，然后再返回。（简单概况：从磁盘或缓冲池中读取（拷贝）目标数据行，放入目标内存区域）
【2】记录undo log（事务开启前的数据版本）
【3】执行器得到记录，将c字段值加一，得到新的记录，再调用引擎接口写入新数据
【4】引擎将新数据更新到内存中（缓冲池中的页面变脏）
【5】同时将redo log写入内存（执行整个事务的过程中，redo log就不断被保存进内存），此时redo log处于prepare准备状态。（写入redo log缓冲区，表示预提交）
【6】执行器生成该操作的binlog，并将binlog写入磁盘（这里的写是广义概念，是否同步到磁盘看具体参数，默认是不同步磁盘，仅仅从应用程序的binlog cache写入操作系统缓存）
【7】执行器调用引擎的提交事务接口，（默认情况下）将redo log写入磁盘，此时redo log处于提交阶段。

两阶段提交是为了保证redo log和bin log的数据一致性。

commit阶段之前的崩溃，都是通过undo log进行回滚的。而如果是commit阶段崩溃，需要考虑binlog的状态：
【1】如果binlog的记录是完整的，那么使用redo log对页面数据进行重新更新
【2】如果binlog的记录是不完整的，那么使用undo log进行数据回滚。