MySQL是使用最广泛的数据库，只有理解了其底层机制，才能更好的写出高性能的SQL查询，所谓知其然，也要知其所以然。本篇Chat为接下来的深入理解MySQL数据原理 精华版本，重点知识，如果某个知识点不理解，可以再深入的看本专栏中的其它Blog内容介绍。

1. MySQL架构及查询语句执行流程，一条SQL语句的执行流程，MySQL数据库的整体结构
2. MySQL数据库存储引擎，InnoDB和MyISAM的区别，InnoDB的优势
3. MySQL日志机制，MySQL的两种日志，日志的作用是什么
4. MySQL数据库索引，索引的底层结构，B+树的构造，索引的优化策略
5. MySQL数据库事务及锁机制，事务的ACID，隔离级别，MVCC机制，锁的种类和使用

适合人群：不了解MySQL的新手，对MySQL的实现机制感兴趣的技术人员

本文的全部内容来自我个人在深入理解【MySQL数据库原理】学习过程中整理的博客，是该博客专栏的精华部分。在书写过程中过滤了流程性的上下文，例如部署环境、配置文件、代码示例等，而致力于向读者讲述其中的核心部分，如果读者有意对过程性内容深入探究，可以移步专栏中的其它Blog

MySQL架构及查询语句执行流程

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL的整体架构，分为哪几个部分，使用上需要注意什么

一条查询语句的执行流程是什么样的

接下来我们看这部分的内容。

MySQL的架构

客户端依据通信协议请求服务端，而MySQL这个服务器执行SQL语句命令并给出反馈，整体架构如下：

可以粗略的把MySQL服务器分为两层，上面的为Server层，主要包括连接器、查询缓存、分析器【分析器+预处理器】、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)，我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理，实际上也就是通信，数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据（某条给定的查询语句在第一次执行时，服务器会缓存这条查询语句和他返回的结果。）

• 如果存在，那么在返回查询结果之前，MySQL会检查一次用户权限。如果权限没有问题，key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被则直接从缓存中拿到结果返回给客户端。

• 查询不会被解析，不用生成执行计划，不会被执行。
• 判断是否命中缓存是将此查询语句和缓存中的查询语句进行比对，如果完全相同，那就认为它们是相同的，就认为命中缓存了。

• 如果不存在，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中

• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，Mysql通过将SQL语句进行解析，并生成一棵对应的解析树。MySQL解析器将使用MySQL语法分析(语法规则验证)和解析查询，如将验证是否使用错误的关键字，或者关键字的顺序是否正确
• 预处理器：预处理器根据一些MySQL规则进一步检查解析树是否合法，如数据表和数据列是否存在，解析列名和别名，是否有歧义。接下来预处理器会验证用户权限(precheck)。查看用户是否有相应的操作权限
• 优化器： 按照 MySQL 认为最优的方案去执行。例如表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，将SQL语句转化成执行计划，一条查询可以有很多种执行方式，最后都返回相同的结果，最后找到其中最好的执行计划（Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个）
• 执行器: Mysql根据执行计划给出的指令逐步执行。开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有就会返回没有权限的错误。在此过程中，有大量的操作需要通过调用存储引擎实现的接口完成，这些接口即为“handler API”接口。查询中的每一个表由一个handler的实例表示。（实际上，在优化阶段Mysql就为每一个表创建了一个handelr实例，优化器可以根据这些实例的接口获取表的相关信息，如表的所有列名、索引统计信息等）

下面的一层为存储引擎，以及真实存储的数据，存储引擎又分为很多中，在【MySQL数据库基础 五】数据库存储引擎这篇文章里详细的分析过MySQL的存储引擎选择。以上的分析器和预处理器也可以简化为一个分析器：

总体而言MySQL的架构分为两层

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

这两层共同构建了MySQL，以上各个环节中包含如下三个注意事项：

1. 连接的权限时效，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置
2. 长连接如何使用呢？因为创建连接比较复杂，所以建议使用长连接，但是长连接容易OOM

• 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
• 如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

3. 不建议使用查询缓存，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，8.0已经将该功能彻底移除了

总结而言，连接设置修改只影响后续连接，长连接虽好，但还是定期断开，否则容易OOM，不建议使用查询缓存。

一条查询语句的执行流程

了解了MySQL的架构之后呢，正式理解下一条语句如何进行执行和优化。，以如下的查询语句举例分析，从人员库的人员信息表里拿出一条人员数据：

use User
go
select * from  UserInfo   where  name='tml' and age='26' and sex='男';

分析下这个语句的执行流程：

1. 连接器： 连接数据库User，并通过输入账号密码通过连接认证【数据库权限check】
2. 查询缓存: 先执行查询缓存，如果命中数据，在返回之前先判断是否有权限【查询缓存check】，如果有则返回，没有则继续向下
3. 分析器: 若没有命中缓存，进行语法分析，提取关键字：use 、go、select 、from 、where 、and ，判断关键字是否满足MySQL的语法
4. 预处理器：进一步获取UserInfo表名、列名：name、age、sex，判断这些元素是否都存在，如果都存在则验证权限【权限precheck】，如果权限存在继续向下
5. 优化器： 判断先获取哪一列，产生各种方案【name->age->sex、name->sex->age、age->sex->name等】，最终会选取最优、成本最小的方案去执行
6. 执行器: 执行前先判断是否有权限执行语句【表权限check】， 调用handler查询相关接口，从InnoDB存储引擎中获取数据，

• 调用 InnoDB 引擎接口取这个表的第一行，判断是否满足条件name='tml' and age='26' and sex='男'如果不是则跳过，如果是则将这行存在结果集中；
• 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。以上就是查询的执行流程

MySQL数据库存储引擎

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

MySQL的存储库引擎有哪几种，分别有什么区别

接下来我们看这部分的内容。

数据库引擎分类介绍

在MySQL中，存储引擎是以插件的形式运行的。支持的引擎有十几种之多，但我们实战常用到的，只有InnoDB、MyISAM和Memory ，MySQL的默认存储引擎为InnoDB。

MyISAM

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。

• 体积小，质量大。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。同时能加载更多索引，建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件：MyISAM索引数据分离，体积小，内存利用率高

• tb_demo.frm，存储表定义； tb_demo.MYD，存储数据；tb_demo.MYI，存储索引

• MyISAM无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。
  
• DELETE FROM table时，MyISAM会先将表结构备份到一张虚拟表中，然后执行drop，最后根据备份重建该表。
  
• innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
  

MyISAM存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表，MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表，MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。不支持事务，自然就不支持行级锁
• 如果和 MyISAM 比 Insert 写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，MyISAM会慢于InnoDB，MyISAM的insert快于Innodb，update慢于Innodb
• select count(*) 和 order by 大概是使用最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的还是会锁全表的，MyISAM表的select count(*) 是非常快的；在 MyISAM 存储引擎中，把表的总行数（row）存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。同样，当 count(*) 语句包含 where条件时，两种表的操作是一样的MyISAM的select count(*) 和 order by的速率快
• 定期提供某些表的数据时，MyISAM的话很方便，只要发给他们对应那表的（frm.MYD,MYI）的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

所以综合而言MyISAM更加适合select多的，事务无要求的场景

InnoDB

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。

• InnoDB还引入了行级锁定和外键约束，
• Innodb的索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 数据文件体积庞大很多
• InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
• InnoDB 表的select count(*) 比 MyISAM 慢很多；当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量，需要注意的是，当count(*) 语句包含 where 条件时，两种表的操作是一样的，当count(*)语句包含where条件时MyISAM也需要扫描整个表
• DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除

在以下场合下，使用InnoDB是最理想的选择：

• 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
• 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。默认的事务隔离级别为可重复读（REPEATABLE-READ），通过MVCC（并发版本控制）来实现，使用的锁粒度默认为行级锁，可以支持更高的并发；当然，也支持表锁，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了
• 支持行级锁，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表
• 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束。MySQL支持外键的存储引擎只有InnoDB。
• 支持自动增加列AUTO_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。大数据量下用innodb，因为支持事务，行级锁。对于InnoDB来说，最大的优势在于支持事务，当然这是以牺牲效率为代价的

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。

• 虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
• 要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用
• 只支持表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低

基于以上的MEMORY的特性，适用场景如下：

• 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
• 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
• 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在**=和<>**的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

三种存储引擎的对比

三种存储引擎的详细对比用如下的这个表格来展示下吧：

MySQL日志机制

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

一条更新语句的执行流程

RedoLog指什么，BinLog指什么，二者的区别和作用场合分别是什么

WAL机制、crash-safe能力和主从备份同步

两阶段提交机制

组提交策略和延迟提交策略

接下来我们看这部分的内容。

更新语句的执行流程

DML数据操作语句（更新、删除、插入）这些在执行的时候肯定要记录日志，MySQL 自带的日志模块 binlog（归档日志） ，所有的存储引擎都可以使用，常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），假如我们要更新ID为2的这条数据当前值自增1，其中ID为主键，加了索引：

use User
go
update T set c=c+1 where ID=2;

我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

1. 连接器： 连接数据库User，并通过输入账号密码通过连接认证， 查询缓存不执行，因为在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空
2. 分析器: 进行语法分析，提取关键字：use 、go、update 、set 、where ，判断关键字是否满足MySQL的语法， 预处理器：进一步获取UserInfo表名、列名：name、age，判断这些元素是否都存在，如果都存在则验证权限，如果权限存在继续向下
3. 优化器： 定位到要更新的数据，查询tml这一条数据，然后把age改为18，生成一个执行计划
4. 执行器: 调用handler查询相关接口写入这一行数据

• 执行器先找存储引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
• 执行器拿到引擎给的行数据，把这个值设置为18，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务
• 执行器收到通知后记录 binlog，并把 binlog 写入磁盘
• 执行器调用引擎接口，提交 redo log 为提交状态。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

以上就是在更新数据时的一些操作，实际上只比查询多后半段。接下来我们从为什么需要使用开始讲起一直到日志是如何满足MySQL的安全可靠的。为了方便把前面的知识串联起来，我画了一个流程图：

循着这个路线正序的提取下日志部分的知识精华。