#|详解MySQL架构设计及SQL执行流程 mysql|sql|数据库|MySQL

MySQL的发展历史和版本分支:

时间	里程碑
1996 年	MySQL 1.0发布。它的历史可以追溯到1979年，作者Monty用BASIC设计的一个报表工具。
1996 年10月	3.11.1发布:MySQL没有2.x版本:
2000 年	ISAM升级成MylSAM引擎。MySQL开源。
2003 年	MySQL 4.0发布,集成InnoDB存储引擎。
2005 年	MySQL 5.。版本发布，提供了视图、存储过程等功能。
2008 年	MySQL AB公司被Sun公司收购，进入Sun MySQL时代。
2009 年	Oracle收购Sun公司，进入Oracle MySQL时代。
2010 年	MySQL 5.5发布，InnoDB成为默认的存储引擎。
2016 年	MySQL发布8.0.0版本。为什么没有6、7? 5.6可以当成6.x, 5.7可以当成 7.Xo

因为MySQL是开源的（也有商业版本），所以在MySQL稳定版本的基础上也发展基础上也发展出来了很多的分支，就像 Linux—样，有 Ubuntu、RedHat、CentOSs Fedora、Debian。
我们操作数据库有各种各样的方式，比如Linux系统中的命令行，比如数据库工具 Navicat,比如程序，例如Java语言的JDBC API或者ORM框架。
那么大家有没有思考过，当我们的工具或者程序连接到数据库之后，实际上发生了什么事情？它的内部是怎么工作的？
接下来我们便以一条査询语句为例，我们来看下MySQL的工作流程是什么样的。
1. 一条查询SQL语句是如何执行的?

文章图片

我们的程序或者工具要操作数据库，第一步要做什么事情呢？
没错，首先是跟数据库建立连接。
1.1 连接 MySQL服务监听的端口默认是3306,客户端连接服务端的方式有很多。
可以是同步的也可以是异步的，可以是长连接也可以是短连接，可以是TCP也可以是Unix Socket, MySQL有专门处理连接的模块，连接的时候需要验证权限。
我们怎么查看MySQL当前有多少个连接？
可以用show status命令，模糊匹配Thread:

show global status like 'Thread%';

文章图片

字段	含义
Threads_cached	缓存中的线程连接数。.
Threads_connected	当前打开的连接数。
Threads_created	为处理连接创建的线程数。
Threads_running	非睡眠状态的连接数，通常指并发连接数:

那么为什么连接数是查看线程？客户端的连接和服务端的线程有什么关系?
客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是Kill线程。
既然是分配线程的话，保持连接肯定会消耗服务端的资源。MySQL会把那些长时间不活动的（SLEEP）连接自动断开。
有两个参数:

show global variables like 'wait_timeout'; —非交互式超时时间，如 JDBC 程序show global variables like 'interactive_timeout'; "交互式超时时间，如数据库工具

默认都是28800秒，8小时。
既然连接消耗资源，MySQL服务允许的最大连接数（也就是并发数）默认是多少呢？
在5.7版本中默认是151个，最大可以设置成100000。

show variables like 'max_connections';

文章图片

参数级别说明：
MySQL中的参数（变量）分为session和global级别，分别是在当前会话中生效和全局生效,但是并不是每个参数都有两个级别，比如max_connections就只有全局级别。
当没有带参数的时候，默认是session级别，包括查询和修改。
比如修改了一个参数以后，在本窗口査询已经生效，但是其他窗口不生效：

文章图片

所以，如果只是临时修改，建议修改session级别。如果需要在其他会话中生效，必须显式地加上global参数。
那么执行一条查询语句，客户端跟服务端建立连接之后呢？下一步要做什么？
1.2 查询缓存 MySQL内部自带了一个缓存模块。
有一张500万行数据的表，没有索引，如果我两次执行一模一样的SQL语句，第二次会不会变得很快？

SELECT * FROM `user` where name='Jack'

缓存没有生效，为什么？ MySQL的缓存默认是关闭的。

show variables like 'query_cache%';

文章图片

默认关闭的意思就是不推荐使用，为什么MySQL不推荐使用它自带的缓存呢？主要是因为MySQL自带的缓存的应用场景有限，第一个是它要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。
第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。
所以缓存这一块，我们还是交给0RM框架(比如MyBatis默认开启了一级缓存)，或者独立的缓存服务，比如Redis来处理更合适。
在MySQL 8.0中，查询缓存已经被移除了。
1.3 语法解析和预处理(Parser & Preprocessor) 没有使用缓存的话，就会跳过缓存的模块，下一步要做什么呢？
没错，接下来就是识别解析我们的SQL语句了，那为什么一条SQL语句能够被识别呢？假如随便执行一个字符串,服务器就会报了一个1064的错：

文章图片

它是怎么知道我输入的内容是错误的呢？
这个就是MySQL的Parser解析器和Preprocessor预处理模块。
这步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。
1.3.1 词法解析
词法分析就是把一个完整的SQL语句打碎成一个个的单词。
比如一个简单的SQL语句：

select name from user where id = 1;

它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。
1.3.2 语法解析
第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合, 然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树(select lex)。

文章图片

词法语法分析是一个非常基础的功能，Java的编译器、百度搜索引擎如果要识别语句，必须也要有词法语法分析功能。
任何数据库的中间件，要解析SQL完成路由功能，也必须要有词法和语法分析功能, 比如Mycat, Sharding-JDBC （用到了 Druid Parser）。在市面上也有很多的开源的词法解析的工具（比如LEX, Yacc）。
1.3.3 预处理器
如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？
比如：

select * from Jack;

似乎解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？
但是实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。
它会检査生成的解析树，解决解析器无法解析的语义。比如，它会检査表和列名是否存在，检査名字和别名，保证没有歧义。
预处理之后会得到一个新的解析树。
1.4 查询优化(Query Optimizer)与查询执行计划 1.4.1 什么是优化器？
得到解析树之后，是不是执行SQL语句了呢？
这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的SQL?
这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？
这个就是MySQL的査询优化器的模块(Optimizer)。
査询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划，MySQL里面使用的是基于开销(cost)的优化器，那种执行计划开销最小，就用哪种。
可以使用这个命令査看査询的开销：

show status like 'Last_query_cost';

1.4.2 优化器可以做什么？
MySQL的优化器能处理哪些优化类型呢?
举两个简单的例子:
1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。
2、有多个索引可以使用的时候，选择哪个索引。
实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。
如果对于优化器的细节感兴趣，可以看看《数据库査询优化器的艺术-原理解析与SQL性能优化》。
但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写SQL语句的时候还是要注意。
优化完之后，得到一个什么东西呢？优化器最终会把解析树变成一个査询执行计划,查询执行计划是一个数据结构。
我们怎么査看MySQL的执行计划呢？比如多张表关联查询，先査询哪张表？在执行査询的时候可能用到哪些索引，实际上用到了什么索引?
MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

EXPLAIN select name from user where id = 1

如果要得到详细的信息，还可以用FORMAT=JSON,或者开启optimizer traceo

EXPLAIN FORMAT=JSON select name from user where id=l;

1.5 存储引擎得到执行计划以后，SQL语句是不是终于可以执行了?
问题又来了:
1、从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面?
2、执行计划在哪里执行？是谁去执行?
1.5.1 存储引擎基本介绍
比如这三张表，都是二维的存储结构，表面上看起来是一模一样的。

文章图片

但是他们的表类型是不一样的。
表面上一样，底层肯定是有差异的。我们先找到数据库存放数据的路径:

show variables like 'datadir'

文章图片

默认情况下，每个数据库有一个自己文件夹，以mysql-demo数据库为例。
任何一个存储引擎都有一个frm文件，这个是表结构定义文件。

文章图片

不同的存储引擎存放数据的方式不一样，产生的文件也不一样。
这里我们有几个问题：
1、表类型是怎么选择的？可以修改吗？
2、MySQL为什么支持这么多存储引擎呢？一种还不够用吗？
3、这些不同的存储引擎，到底有什么区别？
1.5.2 存储引擎选择
一张表的存储引擎，是在创建表的时候指定的，使用ENGINE关键字。

CREATE TABLE user_innodb' ( id int( 11) NOT NULL AUTOJNCREMENT, name varchar(255) DEFAULT NULL, gender tinyint( 1) DEF AULT NULL, phone' varchar(l 1) DEFAULT NULL, PRIMARY KEY ('id'), KEY 'comidx_name_phone' ( name ,'phone') )ENGINE=InnoDB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8mb4;

奇怪了，很多时候我们自己写的建表语句是没有指定存储引擎的，对吧？
当我们没有指定的时候，数据库就会使用默认的存储引擎，5.5.5之前，默认的存储引擎是MylSAM, 5.5.5之后，默认的存储引擎是InnoDB。
这么多的存储引擎，区别到底在哪里?
试想一下: 如果我有一张表，需要很高的访问速度，而不需要考虑持久化的问题，是不是要把数据放在内存?
如果一张表，是用来做历史数据存档的，不需要修改，也不需要索引，那它是不是要支持数据的压缩?
如果一张表用在读写并发很多的业务中，是不是要支持读写不干扰，而且要保证比较高的数据一致性呢?
说到这里大家应该明白了，为什么要支持这么多的存储引擎，就是因为我们有不同的业务需求，一种存储引擎不能提供所有的特性。
1.5.3 存储引擎比较
常见存储引擎
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

MylSAM （3个文件）

文章图片

应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。
特点：
- 支持表级别的锁（插入和更新会锁表）。不支持事务。
- 拥有较高的插入（insert）和查询（select）速度。
- 存储了表的行数（count速度更快）。
适合：只读之类的数据分析的项目。
InnoDB （2个文件）

文章图片

mysql 5.7中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性， InnoDB还支持外键引用完整性约束。
特点：
- 支持事务，支持外键，因此数据的完整性、一致性更高。
- 支持行级别的锁和表级别的锁。
- 支持读写并发，写不阻塞读（MVCC）。
- 特殊的索引存放方式，可以减少I/O，提升査询效率。
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
InnoDB本来是InnobaseOy公司开发的，它和MySQL AB公司合作开源了 InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。
后来08年Sun公司（开发Java语言的Sun）收购了 MySQL AB, 09年Sun公司又被Oracle收购了，所以MySQL, InnoDB又是一家了。所以有人觉得MySQL越来越像Oracle,其实也是这个原因。
Memory （1个文件）

文章图片

将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbduster为大型分布式数据集提供了快速的键值查找。
特点：
- 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。
- 将表中的数据存储到内存中。
CSV （3个文件）

文章图片

它的表实际上是带有逗号分隔值的文本文件。csv表允许以CSV格式导入或转储数据, 以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入或导出阶段使用csv表。
特点：不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。
Archive （2 个文件）

文章图片

这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。
特点：不支持索引，不支持update delete。

这是MySQL里面常见的一些存储引擎，我们看到了，不同的存储引擎提供的特性都不一样，它们有不同的存储机制、索引方式、锁定水平等功能。
我们在不同的业务场景中对数据操作的要求不同，就可以选择不同的存储引擎来满足我们的需求，这个就是MySQL支持这么多存储引擎的原因。
1.5.4 如何选择存储引擎？
如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择My ISAM。
如果需要一个用于查询的临时表，可以选择Memory。
如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎：
https://dev.mysql.com/doc/internals/en/custom-engine.html
按照这个开发规范，实现相应的接口，给执行器操作。
这也能说明为什么能支持这么多存储引擎，还能自定义存储引擎，表的存储引擎改了对Server访问没有任何影响，就是因为大家都遵循了一定了规范，提供了相同的操作接口。
每个存储引擎都有自己的服务。

show engine innodb status;

这些存储引擎用不同的方式管理数据文件，提供不同的特性，但是为上层提供相同的接口。
1.6 执行引擎(Query Execution Engine),返回结果 OK,存储引擎分析完了，它是我们存储数据的形式，继续续第二个问题，是谁使用执行计划去操作存储引擎呢？
这就是我们的执行引擎，它利用存储引擎提供的相应的API来完成操作。
为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的API是相同的。
最后把数据返回给客户端。
2. MySQL体系结构总结基于上面分析的流程，我们一起来梳理一下MySQL的内部模块。
总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服务层**，**和跟硬件打交道的存储引擎层。

文章图片

2.1 连接层我们的客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。
2.2 服务层连接层会把SQL语句交给服务层，这里面又包含一系列的流程：
比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。
然后就是优化器，MySQL底层会根据一定的规则对我们的SQL语句进行优化，最后再交给执行器去执行。
2.3 存储引擎存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储引擎。
存储引擎再往下就是内存或者磁盘。
3. 一条更新SQL是如何执行的？讲完了查询流程，我们是不是再讲讲更新流程、插入流程和删除流程？
在数据库里面，我们说的update操作其实包括了更新、插入和删除。如果大家有看过MyBatis的源码，应该知道Executor里面也只有doQuery()和doUpdate()的方法，没有 doDelete()和 dolnsert()。
更新流程和查询流程有什么不同呢？
基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。
区别就在于拿到符合条件的数据之后的操作。
3.1 缓冲池 Buffer Pool 首先，对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据, 必须先把磁盘里面的数据加载到内存里面才可以操作。
这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到内存呢？比如我要读6个字节。
磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的I/O操作。
所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。那么这样，我们干脆每次多读取一点，而不是用多少读多少。
InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。操作系统的页大小一般是4K,而在InnoDB里面，这个最小的单位默认是16KB大小。如果要修改这个值的大小，需要清空数据重新初始化服务。
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size
我们要操作的数据就在这样的页里面，数据所在的页叫数据页。

文章图片

这里有一个问题，操作数据的时候，每次都要从磁盘读取到内存（再返回给Server）,有没有什么办法可以提高效率?
这里还是缓存的思想。我们把把读取过的数据页缓存起来。
InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。
这个内存区域有个专属的名字，叫Buffer PooL：

文章图片

修改数据的时候，也是先写入到buffer pool,而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。那脏页什么时候才同步到磁盘呢？
InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
总结一下：Buffer Pool的作用是为了提高读写的效率。
3.2 Redo log 思考一个问题：因为刷脏不是实时的,如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。
【#|详解MySQL架构设计及SQL执行流程】那怎么办呢？所以内存的数据必须要有一个持久化的措施。
为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。
如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现crash-safe）。我们说的事务的ACID里面D （持久性），就是用它来实现的。

文章图片

这个日志文件就是磁盘的redo log （叫做重做日志）。
这个时候大家应该有这样的问题：同样是写磁盘，为什么不直接写到db file里面去？为什么先写日志再写磁盘？
写日志文件和和写到数据文件有什么区别？
我们先说一下磁盘寻址的过程。下图是磁盘的构造。磁盘的盘片不停地旋转，磁头会在磁盘表面画出一个圆形轨迹，这个就叫磁道。从内到位半径不同有很多磁道。然后又用半径线，把磁道分割成了扇区（两根射线之内的扇区组成扇面）。如果要读写数据，必须找到数据对应的扇区，这个过程就叫寻址。

文章图片

如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机10,读取数据速度较慢。
假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序10。
刷盘是随机I/O,而记录日志是顺序I/O （连续写的），顺序I/O效率更高，本质上是数据集中存储和分散存储的区别。因此先把修改写入日志文件，在保证了内存数据的安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。
redo log 位于/var/lib/mysql/目录下的 ib_logfile0 和 ib_logfile1, 默认 2 个文件,每个48M。

show variables like 'innodb_log%';

参数	含义
innodb _log _file_size	指定每个文件的大小，默认48M
innodb _log_files_in_group	指定文件的数量，默认为2
innodb_log_group_home_dir	指定文件所在路径，相对或绝对。如果不指定，则为 datadir 路径。

这个redo log有什么特点？

redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。
redo log不是记录数据页更新之后的状态，而是记录的是"在某个数据页上做了什么修改”,属于物理日志。
redo log的大小是固定的，前面的内容会被覆盖,一旦写满，就会触发buffer pool 到磁盘的同步，以便腾出空间记录后面的修改。

除了 redo log之外,还有一个跟修改有关的日志，叫做undo logo。redo log和undo log与事务密切相关，统称为事务日志。
3.3 Undo log undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态，分为insert undo log和update undo logo。如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。
可以理解为undo log记录的是反向的操作，比如insert会记录delete, update 会记录update原来的值，跟redolog记录在哪个物理页面做了什么操作不同，所以叫做逻辑格式的日志。

show global variables like '%undo%‘;

参数	含义
innodb_ undo_directory	undo文件的路径
innodb_undo_log_truncate	设置为1，即开启在线回收（收缩) undo log 志文件
innodb_max_undo_log_size	如果innodb_undo_log_truncate设置为1,超过这个大小的时候会触发 truncate回收（收缩）动作，如果page大小是16KB, truncate后空间缩小到10M。默认1073741824字节=1G。
innodb_undo_logs	回滚段的数量，默认128,这个参数已经过时。
innodb_undo_tablespaces	设置undo独立表空间个数，范围为0-95,默认为0,0表示表示不开启独立undo表空间且 un do日志存储在ibdata文件中。这个参数已经过时。

3.4 更新过程有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。
name 原值是 jack:

update user set name = 'penyuyan' where id=l;

事务开始，从内存（buffer pool）或磁盘（data file）取到包含这条数据的数据页，返回给Server的执行器；
Server的执行器修改数据页的这一行数据的值为penyuyan;
记录 name=jack 到 undo log;
记录 name=penyuyan 到 redo log;
调用存储引擎接口，记录数据页到Buffer Pool （修改name = penyuyan）;
事务提交。

了解了内存的Buffer Pool和磁盘的两个日志，我们也从总体上看下InnoDB的架构是什么样的。
4. InnoDB总体架构

文章图片

我们先看一下内存结构：
4.1 内存结构 Buffer Pool主要分为3个部分:Buffer Pool 、 Change Buffer、Adaptive Hash Index，另外还有一个 (redo) log buffer。
4.1.1 Buffer Pool
Buffer Pool缓存的是页面信息，包括数据页、索引页。
Buffer Pool默认大小是128M （134217728字节）,可以调整。
査看系统变量

SHOW VARIABLES like '%innodb_buffer_pool%';

查看服务器状态，里面有很多跟Buffer Pool相关的信息:

SHOW STATUS LIKE '%innodb_buffer_pool%'

这些参数都可以在官网查到详细的含义，用搜索功能。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

文章图片

内存的缓冲池写满了怎么办？（Redis设置的内存满了怎么办？）InnoDB用LRU 算法来管理缓冲池（链表实现，不是传统的LRU,分成了 young和old）,经过淘汰的数据就是热点数据。
4.1.2 LRU
传统LRU,可以用Map+链表实现。value存的是在链表中的地址。

文章图片

首先，InnoDB中确实使用了一个双向链表，LRU List 。但是这个LRU List 放的不是data page,而是指向缓存页的指针。
如果写buffer pool的时候发现没有空闲页了,就要从buffer pool中淘汰数据页了, 它要根据LRU链表的数据来操作。
这样是不是很和谐？
首先，InnoDB的数据页并不是都是在访问的时候才缓存到buffer pool的。
InnoDB有一个预读机制（read ahead）。也就是说，设计者认为访问某个page 的数据的时候，相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool中缓存起来。
这种预读的机制又分为两种类型，一种叫线性预读（异步的）（Linear read-ahead）。为了便于管理，InnoDB中把64个相邻的page叫做一个extent （区）。如果顺序地访问了一个extent的56个page,这个时候InnoDB就会把下一个extent （区）缓存到 buffer pool 中。
顺序访问多少个page才缓存下一个extent,由一个参数控制:

show variables like 'innodb_read_ahead_threshold'

第二种叫做随机预读（Random read-ahead）, 如果buffer pool已经缓存了同一个extent （区）的数据页的个数超过13时，就会把这个extent剩余的所有page全部缓存到buffer pool。
但是随机预读的功能模式是不开启的，由一个参数控制：

show variables like 'innodb_random_read_ahead'

很明显，线性预读或者异步预读，能够把可能即将用到的数据提前加载到buffer pool,肯定能提升I/O的性能，所以是一种非常有用的机制。
但是预读肯定也会带来一些副作用，就是导致占用的内存空间更多，剩余的空闲页更少。如果说buffer pool size不是很大，而预读的数据很多，很有可能那些真正的需要被缓存的热点数据被预读的数据挤出buffer pool，淘汰掉了。下次访问的时候又要先去磁盘。
所以问题就来了**，**怎么让这些真正的热点数据不受到预读的数据的影响呢？
随意可以干脆把LRU list分成两部分，靠近head的叫做new sublist, 用来放热数据（我们把它叫做热区）。靠近tail的叫做oId sublist,用来放冷数据 (我们把它叫做冷区）。中间的分割线叫做midpoint。也就是对buffer pool做一个冷热分离。

文章图片

所有新数据加入到buffer pool的时候，一律先放到冷数据区的head,不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在old sublist （冷区）直接被淘汰。
放到LRU List以后，如果再次被访问，都把它移动到热区的head。
如果热区的数据长时间没有被访问，会被先移动到冷区的head部，最后慢慢在tail被淘汰。

文章图片

在默认情况下，热区占了 5/8的大小，冷区占了 3/8,这个值由innodb_old_blocks_pct控制，它代表的是old区的大小,默认是37%也就是3/8。
innodb_old_blocks_pct的值可以调整，在5%到95%之间，这个值越大，new区越小，这个LRU算法就接近传统LRU。
如果这个值太小，old区没有被访问的速度淘汰会更快。
到这里预读的问题通过冷热分离解决了，还有没有其他的问题呢？
我们先把数据放到冷区，用来避免占用热数据的存储空间。但是如果刚加载到冷区的数据立即被访问了一次，按照原来的逻辑，这个时候我们会马上把它移动到热区。
假设这一次加载然后被立即访问的冷区数据量非常大，比如我们查询了一张几千万数据的大表，没有使用索引，做了一个全表扫描。或者，dump全表备份数据,这种查询属于短时间内访问，后面再也不会用到了。
如果短时间之内被访问了一次，导致它们全部被移动到热区的head,它会导致很多热点数据被移动到冷区甚至被淘汰，造成了缓冲池的污染。
这个问题我们又怎么解决呢？
那我们得想一个办法，对于加载到冷区然后被访问的数据，设置一个时间窗口, 只有超过这个时间之后被访问，我们才认为它是有效的访问。
InnoDB里面通过innodb_old_blocks_time这个参数来控制，默认是一秒钟。也就是说1秒钟之内被访问的，不算数，待在冷区不动。只有1秒钟以后被访问的, 才从冷区移动到热区的head。
这样就可以从很大程度上避免全表扫描或者预读的数据污染真正的热数据。
以上的方法似乎比较完美了。
这样的算法，还有没有可以优化的空间呢？
为了避免并发的问题，对于LRU链表的操作是要加锁的。也就是说每一次链表的移动，都会带来资源的竞争和等待。从这个角度来说，如果要进一步提升InnoDB LRU的效率，就要尽量地减少LRU链表的移动。
比如，把热区一个非常靠近head的page移动到head,有没有这个必要呢？所以InnoDB对于new区还有一个特殊的优化：
如果一个缓存页处于热数据区域，且在热数据区域的前1/4区域（注意是热数据区域的1/4,不是整个链表的1/4）,那么当访问这个缓存页的时候，就不用把它移动到热数据区域的头部；如果缓存页处于热区的后3/4区域，那么当访问这个缓存页的时候，会把它移动到热区的头部。
内存缓冲区对于提升读写性能有很大的作用。思考一个问题：
当需要更新一个数据页时,如果数据页在Buffer Pool中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘i/p,有没有优化的方式呢？
4.1.3 Change Buffer 写缓冲
Change Buffer 是 Buffer Pool 的一部分。
如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、 Delete、Update）的执行速度。
这一块区域就是Change Buffer。 5.5之前叫Insert Buffer插入缓冲，现在也能支持 delete 和 update。
最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge? 有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、 redo log写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用Change Buffer （写缓冲）。
可以通过调大这个值，来扩大Change的大小，以支持写多读少的业务场景。

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

代表 Change Buffer 占 Buffer Pool 的比例，默认 25%。
4.1.4 Adaptive Hash Index
索引应该是放在磁盘的，为什么要专门把一种哈希的索引放到内存？这部分内容太多，会在另外一篇文章中说明。
4.1.5 Redo Log Buffer
Redo log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域(Log Buffer)专门用来保存即将写入日志文件的数据，默认16M,它一样可以节省磁盘IO；

文章图片

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

需要注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件。
那么，Log Buffer什么时候写入log file?
在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。
log buffer写入磁盘的时机，由一个参数控制，默认是1。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

值	含义
0 （延迟写）	log buffer将每秒一次地写入log file中,并且log file的flush操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
1 （默认，实时写，实时刷）	每次事务提交时MySQL都会把log buffer的数据写入log file,并且刷到磁盘中去。
2 （实时写，延迟刷）	每次事务提交时MySQL都会把log buffer的数据写入log file。但是flush操作并不会同时进行。该模式下，MySQL会每秒执行一次flush操作。

刷盘越快，越安全，但是也会越消耗性能。
总结一下，这是MySQL的内存结构，分为：Buffer pool, change buffer. Adaptive Hash Index、 log buffer。
下面我们来看一下磁盘结构，磁盘结构里面主要是各种各样的表空间，叫做Table space。
4.2 磁盘结构表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB的表空间分为5大类：
4.2.1 系统表空间 system tablespace
在默认情况下InnoDB存储引擎有一个共享表空间（对应文件/var/lib/mysql/ ibdata1），也叫系统表空间。
InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，(Change Buffer和Undo Logs），如果没有指定file-per-table，也包含用户创建的表和索引数据。
1、 undo在后面介绍，因为也可以设置独立的表空间
2、数据字典：由内部系统表组成，存储表和索弓I的元数据（定义信息）。
3、双写缓冲（InnoDB的一大特性）:
InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K,操作系统页大小为4K, InnoDB的页写入到磁盘时，一个页需要分4次写。

文章图片

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效(partial page write)，可能会导致数据丢失。

show variables like 'innodb_doublewrite';

我们不是有redo log吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write, InnoDB的双写技术。通过它实现了数据页的可靠性。
跟redo log —样，double write由两部分组成，一部分是内存的double write，—个部分是磁盘上的double write。因为double write是顺序写入的，不会带来很大的开销。
在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。
4.2.2 独占表空间 file-per-tabletablespaces
我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置，默认开启。

SHOW VARIABLES LIKE 'innodb_file_per_table';

开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的ibd文件(例如/var/lib/mysql/mysql-demo/user_innodb.ibd),存放表的索引和数据。
但是其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。
4.2.3 通用表空间 general tablespaces
通用表空间也是一种共享的表空间，跟ibdata1类似。
可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。语法：

create tablespace ceshi add datafile '/var/lib/mysql/ceshi.ibd' file_block_size= 16K engine=innodb;

在创建表的时候可以指定表空间，用ALTER修改表空间可以转移表空间。

create table t2673(id integer) tablespace ceshi;

不同表空间的数据是可以移动的。
删除表空间需要先删除里面的所有表：
4.2.4 临时表空间 temporary tablespaces
存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录下的ibtmp1文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。
4.2.5 Redo log
磁盘结构里面的redo log,在前面已经介绍过了。
4.2.6 undo log tablespace
undo Log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。
5. 后台线程后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread, I/O thread, purge thread, page cleaner thread。

master thread负责刷新缓存数据到磁盘并协调调度其它后台进程。
IO thread 分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer，重做日志，读写请求的I/O回调。
purge thread 用来回收 undo 页。
page cleaner thread用来刷新脏页。

除了 InnoDB架构中的日志文件，MySQL的Server层也有一个日志文件，叫做 binlog，它可以被所有的存储引擎使用。
6. Bin log bin log以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。
在开启了 bin log功能的情况下，我们可以把bin log导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。
binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的bin log，然后执行一遍。
有了这两个日志之后，我们来看一下一条更新语句是怎么执行的（redo 不能一次写入了）:

文章图片

例如一条语句：update teacher set name=‘王力宏’ where id=1;
1、先查询到这条数据，如果有缓存，也会用到缓存。
2、把name改成王力宏，然后调用引擎的API接口，写入这一行数据到内存，同时记录redo logo这时redo log进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
3、执行器收到通知后记录binlog，然后调用存储引擎接口，设置redo log为 commit 状态。
4、更新完成。
这张图片的重点：
1、先记录到内存，再写日志文件。
2、记录redo log分为两个阶段。
3、存储引擎和Server记录不同的日志。
3、先记录redo,再记录bin log。
为什么需要两阶段提交？
举例：
如果我们执行的是把name改成王力宏，如果写完redo log,还没有写bin log的时候，MySQL重启了。
因为redo log可以在重启的时候用于恢复数据，所以写入磁盘的是王力宏。但是 binlog里面没有记录这个逻辑日志，所以这时候用binlog去恢复数据或者同步到从库, 就会出现数据不一致的情况。
所以在写两个日志的情况下，binlog就充当了一个事务的协调者。通知InnoDB来执行 prepare 或者 commit 或者 rollbacko。
如果第⑥步写入binlog失败，就不会提交。
简单地来说，这里有两个写日志的操作，类似于分布式事务，不用两阶段提交，就不能保证都成功或者都失败。
在崩溃恢复时，判断事务是否需要提交由如下几种情况：
1、binlog无记录，redolog无记录：在redo log 写之前 crash，恢复操作：回滚事务
2、binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务
3、binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务
4、binlog有记录，redolog状态commit：正常完成的事务，不需要恢复
所以主要还是看binlog中有无记录。