MySQL架构体系[亲测有效]

MySQL基本架构(一条SQL语句的执行过程)

MySQL主要可以分为Server层和存储引擎层。
Server层包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB(5.5后成为默认存储引擎)、MyISAM、Memory等多个存储引擎。

连接器:负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。

查询缓存:客户端与服务端建立连接后，MySQL在执行查询语句时会先查询缓存，校验这条SQL是不是在之前执行过。之前执行过的语句及其结果会以key-value对的形式被直接缓存在内存中。(不建议使用查询缓存，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。在MySQL8.0中已删除该功能)

分析器:分析SQL语句的作用,主要分为以下两步
1.词法分析，提取关键字
2.语法分析，判断SQL语句是否正确

优化器:对SQL语句进行自动优化，对语法分析树的形态进行修改，把语法分析树变为查询树，确定执行方案。

执行器:执行语句。首先校验用户是否有执行查询的权限，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

存储引擎InnoDB、MyISAM

常用的存储引擎有以下几种:
InnoDB引擎:Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎:不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎:所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB的区别

关于全文索引:MySQL全文索引在5.7之前只支持英文，5.7之后内置了ngram全文检索插件，用来支持中文分词，对MyISAM和InnoDB引擎都有效。
该处具体到底如何笔者也仍然存在疑问，难以给出准确回答，但是在更多情况下，全文索引我们更倾向于使用elasticSearch等工具实现全文检索。

InnoDB、MyISAM下索引实现的差异

MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
MyISAM会按照数据插入的顺序分配行号，从0开始，然后按照数据插入的顺序存储在磁盘上。因为行是定长的，所以可以从表的开头跳过相应的字节找到需要的行。
MyISAM的索引文件仅仅保存数据记录的行号，然后通过此行号回表查询需要的数据

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求Key是唯一的，而辅助索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式索引和数据存放是分开的，非聚集的。

InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。
InnoDB的二级索引的叶子节点存储的不是行号（行指针），而是主键列。这种策略的缺点是二级索引需要两次索引查找，第一次在二级索引中查找主键，第二次在聚簇索引中通过主键查找需要的数据行。

综上，两者都包含非聚簇索引的实现，但是InnoDB引擎支持了聚簇索引。

为什么InnoDB及MyISAM引擎都使用了B+Tree作为索引

B树与B+树的比较:

1. B树只适合随机检索，而B+树同时支持随机检索和顺序检索
2. B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。
   一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。
   B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
3. B+树的查询效率更加稳定
   B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。
   而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
4. B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。
   B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历，而B树不支持这样的操作。
5. 增删文件（节点）时，B+树效率更高。
   因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

B+树与hash索引的比较:
B+树底层实现是多路平衡查找树，hash索引底层是hash表。

1. hash索引进行等值查询更快，但是却无法进行范围查询，且等值查询不稳定，性能不可预测
   当某个键值存在且大量重复的时候，发生hash碰撞，此次hash索引效率可能极差
   b+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低
2. hash索引不支持使用索引进行排序
3. hash索引不支持模糊查询以及多列索引的最左前缀匹配
4. hash索引任何时候都避免不了回表查询，而B+树在符合某些条件的时候可以只通过索引完成查询

B+树对比二叉树:
二叉树当数据库的数据量特别大时，其层数也特别大。磁盘IO的次数会由树的高度决定，不使用二叉树是为了压缩树的高度，减少磁盘IO的次数。

索引的基本原理

索引的原理很简单，就是把无序的数据变成有序的查询
把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

InnoDB逻辑存储结构

从InnoDB的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）

表空间又由段（segment）、区（extent）、页（page）组成
页在一些文档中有时也称为块（block）
段、区均为逻辑概念，并且段管理由innodb存储引擎内部完成。

表空间:表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中
默认情况下InnoDB有一个共享表空间ibdata1，即所有数据都存放在这个表空间内

段:表空间是由多个段组成的，常见的段有数据段、索引段、回滚段等
InnoDB是索引组织的，因此数据即索引，索引即数据
数据段就是B+树的叶子节点
索引段即为B+树的非叶子节点

区:区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区，即一个区中有64个连续的页，

行(页):InnoDB存储磁盘管理的最小单位，页的大小默认是16KB，每个页存放的记录也是硬性定义的，最多允许存放16KB/2-200行记录，即7992行记录

InnoDB崩溃恢复原理

参考资料: https://www.cnblogs.com/coderyuhui/p/7191413.html 下文只做笔者个人的一些总结，具体的实现原理参考原文。

基础知识

lsn：从数据库创建以来产生的redo日志量，这个值越大，说明数据库的更新越多，也可以理解为更新的时刻。此外，每个数据页上也有一个lsn，表示最后被修改时的lsn，值越大表示越晚被修改。
比如，数据页A的lsn为100，数据页B的lsn为200，checkpoint lsn为150，系统lsn为300，表示当前系统已经更新到300，小于150的数据页已经被刷到磁盘上，因此数据页A的最新数据一定在磁盘上，而数据页B则不一定，有可能还在内存中。

redo日志：现代数据库都需要写redo日志，例如修改一条数据，首先写redo日志，然后再写数据。在写完redo日志后 ，就直接给客户端返回成功。
这样虽然看过去多写一次盘，但是由于把对磁盘的随机写入（写数据）转换成了顺序的写入（写redo日志），性能有很大幅度的提高。
当数据库挂了之后，通过扫描redo日志，就能找出那些没有刷盘的数据页（在崩溃之前可能数据页仅仅在内存中修改了，但是还没来得及写盘），保证数据不丢。

undo日志：数据库还提供类似撤销的功能，当你发现修改错一些数据时，可以使用rollback指令回滚之前的操作。这个功能需要undo日志来支持。
此外，现代的关系型数据库为了提高并发（同一条记录，不同线程的读取不冲突，读写和写读不冲突，只有同时写才冲突），都实现了类似MVCC的机制，在InnoDB中，这个也依赖undo日志。
为了实现统一的管理，与redo日志不同，undo日志在BufferPool中有对应的数据页，与普通的数据页一起管理，依据LRU规则也会被淘汰出内存，后续再从磁盘读取。与普通的数据页一样，对undo页的修改，也需要先写redo日志。

检查点checkPoint：数据库为了提高性能，数据页在内存修改后并不是每次都会刷到磁盘上。
checkpoint之前的数据页保证一定落盘了，这样之前的日志就没有用了(由于InnoDB redolog日志循环使用，这时这部分日志就可以被覆盖)，checkpoint之后的数据页有可能落盘，也有可能没有落盘，
所以checkpoint之后的日志在崩溃恢复的时候还是需要被使用的。InnoDB会依据脏页的刷新情况，定期推进checkpoint，从而减少数据库崩溃恢复的时间。检查点的信息在第一个日志文件的头部。

崩溃恢复：用户修改了数据，并且收到了成功的消息，然而对数据库来说，可能这个时候修改后的数据还没有落盘，如果这时候数据库挂了，重启后，数据库需要从日志中把这些修改后的数据给捞出来，重新写入磁盘，保证用户的数据不丢。
这个从日志中捞数据的过程就是崩溃恢复的主要任务，也可以称为为数据库前滚。
当然，在崩溃恢复中还需要回滚没有提交的事务，提交没有提交成功的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo前滚，然后做undo回滚。

InnoDB崩溃恢复过程

1. redo日志前滚数据库
   前滚数据库，主要分为两阶段，首先是日志扫描阶段，扫描阶段按照数据页的space_id和page_no分发redo日志到hash_table中，保证同一个数据页的日志被分发到同一个哈希桶中，且按照lsn大小从小到大排序。
   扫描完后，再遍历整个哈希表，依次应用每个数据页的日志，应用完后，在数据页的状态上至少恢复到了崩溃之前的状态。

2. redo日志解析

• 首先通过block_no和lsn之间的关系以及日志checksum判断是否读到了日志最后，如果读到最后则返回。(之前说了，即使数据库是正常关闭的，也要走崩溃恢复逻辑，那么在这里就返回了，因为正常关闭的checkpoint值一定是指向日志最后)
  否则则把日志去头掐尾放到一个recv_sys->buf中，日志头里面存了一些控制信息和checksum值，只是用来校验和定位，在真正的应用中没有用。
• 从recv_sys->buf中解析日志。日志分两种：single_rec和multi_rec，前者表示只对一个数据页进行一种操作，后者表示对一个或者多个数据页进行多种操作。
• 解析出相应的日志后，按照space_id和page_no进行哈希(如果对应的表空间在内存中不存在，则表示表已经被删除了)，放到hash_table里面(日志真正存放的位置依然在buffer pool)即可，等待后续应用。

3. redo日志应用
   主要作用就是遍历hash_table，从磁盘读取对每个数据页，依次应用哈希桶中的日志。
   执行完了redo前滚数据库，数据库的所有数据页已经处于一致的状态，undo回滚数据库就可以安全的执行了。
   由于数据库崩溃的时候可能有一些没有提交的事务或者已经提交的事务，故在这个时候需要决定是否提交。
   主要分为三步，首先是扫描undo日志，重新建立起undo日志链表，接着是，依据上一步建立起的链表，重建崩溃前的事务，即恢复当时事务的状态。最后，就是依据事务的不同状态，进行回滚或者提交。

4. undo日志回滚数据库
   第一步: 遍历整个undo日志空间，如果发现某个undo segment非空，就进行segment初始化,如果发现某个undo slot非空，就进行slot 初始化。之后把不同类型的undo日志放到不同链表中。
   undo日志主要分为两种：TRX_UNDO_INSERT和TRX_UNDO_UPDATE。前者主要是提供给insert操作用的，后者是给update和delete操作使用。
   undo日志有两种作用，事务回滚时候用和MVCC快照读取时候用。
   由于TRX_UNDO_INSERT的数据不需要提供给其他线程用，所以只要事务提交，就可以删除TRX_UNDO_INSERT类型的undo日志。
   TRX_UNDO_UPDATE在事务提交后还不能删除，需要保证没有快照使用它的时候，才能通过后台的purge线程清理。
   第二步：第一步中，已经在内存中建立起了undo_insert_list和undo_update_list(链表每个undo segment独立)，所以这一步只需要遍历所有链表，重建起事务的状态。
   如果undo日志的状态是TRX_UNDO_ACTIVE，则事务的状态为TRX_ACTIVE，如果undo日志的状态是TRX_UNDO_PREPARED，则事务的状态为TRX_PREPARED。
   重建起事务后，按照事务id加入到trx_sys->trx_list链表中。
   第三步:统计所有需要回滚的事务(事务状态为TRX_ACTIVE)一共需要回滚多少行数据，输出到错误日志中。
   第三步的操作在两个地方被调用。一个是在recv_recovery_from_checkpoint_finish,另外一个是在recv_recovery_rollback_active中。
   前者主要是回滚对数据字典的操作，也就是回滚DDL语句的操作，后者是回滚DML语句。前者是在数据库可提供服务之前必须完成，后者则可以在数据库提供服务(也即是崩溃恢复结束)之后继续进行(通过新开一个后台线程trx_rollback_or_clean_all_recovered来处理)。
   因为InnoDB认为数据字典是最重要的，必须要回滚到一致的状态才行，而用户表的数据可以稍微慢一点，对外提供服务后，慢慢恢复即可。因此我们常常在会发现数据库已经启动起来了，然后错误日志中还在不断的打印回滚事务的信息。

MySQL的锁机制

MySQL锁按照锁的粒度划分可分为:行锁、表锁、页锁
行锁:
行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。
行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。
行级锁按照使用方式分为共享锁和排他锁。

共享锁允许一个事务读数据，不允许修改数据，如果其他事务要对该行加锁，只能加共享锁。即共享锁允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。
排他锁是修改数据时加的锁，可以读取和修改数据，一旦一个事务对该行数据加锁，其他事务将不能再对该数据加任务锁。即一个锁在某一时刻只能被一个线程占有，其他线程必须等待锁被释放之后才可能获取到锁。

表锁:
表级锁是MySQL锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。
被大部分MySQL引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认是行级锁。

页锁:
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。
所以取了折中的页级，一次锁定相邻的一组记录。BDB支持页级锁。

乐观锁&悲观锁
数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段
要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁
悲观并发控制(悲观锁)是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。
如果一个事务执行的操作对其行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要地狱回滚事务的成本的环境中。

悲观锁的具体流程:
在对任意记录进行修改前，先尝试为该记录加上排他锁。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
期间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或者直接抛出异常。

悲观锁的优点和不足：
悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证。
但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。
并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

乐观锁
乐观并发控制(乐观锁)是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。
在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

乐观锁相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本，为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次同时对版本标识进行更新。
当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

乐观锁的优点和不足：
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。
但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

InnoDB锁的特性

1. 在不通过索引条件查询的时候，InnoDB使用的确实是表锁。
2. 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁
4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引
   这种情况下InnoDB将使用表锁而不是行锁。

MVCC

MVCC全称是： Multiversion concurrency control，多版本并发控制，提供并发访问数据库时，对事务内读取的到的内存做处理，用来避免写操作堵塞读操作的并发问题。
一个支持MVCC的数据库，在更新某些数据时，并非使用新数据覆盖旧数据，而是标记旧数据是过时的，同时在其他地方新增一个数据版本。因此，同一份数据有多个版本存储，但只有一个是最新的。
MVCC提供了 时间一致性的 处理思路，在MVCC下读事务时，通常使用一个时间戳或者事务ID来确定访问哪个状态的数据库及哪些版本的数据。读事务跟写事务彼此是隔离开来的，彼此之间不会影响。假设同一份数据，既有读事务访问，又有写事务操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本，直到写事务提交，读事务才会访问到这个新的数据版本。
MVCC有两种实现方式，第一种实现方式是将数据记录的多个版本保存在数据库中，当这些不同版本数据不再需要时，垃圾收集器回收这些记录。这个方式被PostgreSQL和Firebird/Interbase采用，SQL Server使用的类似机制，所不同的是旧版本数据不是保存在数据库中，而保存在不同于主数据库的另外一个数据库tempdb中。第二种实现方式只在数据库保存最新版本的数据，但是会在使用undo时动态重构旧版本数据，这种方式被Oracle和MySQL/InnoDB使用。

InnoDB的MVCC实现机制

MVCC可以认为是行级锁的一个变种，它可以在很多情况下避免加锁操作，因此开销更低。MVCC的实现大都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB的MVCC实现，是通过保存数据在某个时间点的快照来实现的。一个事务，不管其执行多长时间，其内部看到的数据是一致的。也就是事务在执行的过程中不会相互影响。下面我们简述一下MVCC在InnoDB中的实现。

InnoDB的MVCC，通过在每行记录后面保存两个隐藏的列来实现：一个保存了行的创建时间，一个保存行的过期时间（删除时间），当然，这里的时间并不是时间戳，而是系统版本号，每开始一个新的事务，系统版本号就会递增。在RR隔离级别下，MVCC的操作如下：

select操作。InnoDB只查找版本早于（包含等于）当前事务版本的数据行。可以确保事务读取的行，要么是事务开始前就已存在，或者事务自身插入或修改的记录。行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取的行，在事务开始之前未删除。
insert操作。将新插入的行保存当前版本号为行版本号。
delete操作。将删除的行保存当前版本号为删除标识。
update操作。变为insert和delete操作的组合，insert的行保存当前版本号为行版本号，delete则保存当前版本号到原来的行作为删除标识。
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。