制服MySQL诱惑

MYSQL

1、Java工程师眼中的数据库是什么东西？

据我所知，目前行业里大部分的Java工程师对MySQL的了解和掌握程度，大致就停留在这么一个阶段：对MySQL可以建库建表建索 引，然后就是执行增删改查去更新和查询里面的数据！ 所以我们看下面的图，很多Java工程师眼中的数据库大致就是下面这样子。

2、MySQL驱动到底是什么东西？

我们先来看下面的一段maven配置，这段maven配置中就引入了一个MySQL驱动。这里的mysql-connector-java就是面向Java语言的 MySQL驱动。

大家都知道，如果我们要访问数据库，必须得跟数据库建立一个网络连接，那么这个连接由谁来建立呢？ 其实答案就是这个MySQL驱动，他会在底层跟数据库建立网络连接，有网络连接，接着才能去发送请求给数据库服务器！我们看下 图。

然后当我们跟数据库之间有了网络连接之后，我们的Java代码才能基于这个连接去执行各种各样的增删改查SQL语句

所以对于Java语言开发的系统，MySQL会提供Java版本的MySQL驱动，对于PHP、Perl、.NET、Python、Ruby等各种常见的编程语 言，MySQL都会提供对应语言的MySQL驱动，让各种语言编写的系统通过MySQL驱动去访问数据库。

3、数据库连接池到底是用来干什么的？

一般我们必须要使用一个数据库连接池，也就是说在一个池子里维持多个数据库连接，让多个线程使用里面的不同的数据库连接去 执行SQL语句，然后执行完SQL语句之后，不要销毁这个数据库连接，而是把连接放回池子里，后续还可以继续使用。 基于这样的一个数据库连接池的机制，就可以解决多个线程并发的使用多个数据库连接去执行SQL语句的问题，而且还避免了数据库连 接使用完之后就销毁的问题，我们看下图的说明。

4、MySQL数据库的连接池是用来干什么的？

当我们把目光转移到MySQL的时候，我们要来思考一个问题，那就是肯定会有很多系统要与MySQL数据库建立很多个连接，那么 MySQL也必然要维护与系统之间的多个连接，所以MySQL架构体系中的第一个环节，就是连接池。 我们看下面的图，实际上MySQL中的连接池就是维护了与系统之间的多个数据库连接。除此之外，你的系统每次跟MySQL建立连接的 时候，还会根据你传递过来的账号和密码，进行账号密码的验证，库表权限的验证。

5、为了执行SQL语句，你知道MySQL用了什么样的架构设计吗？

• **线程：**网络连接必须得分配给一个线程去进行处理，由一个线程来监听请求以及读取请求数据，比如从网络连接中读取和解析出来一 条我们的系统发送过去的SQL语句

• **SQL接口：**MySQL内部提供了一个组件，就是SQL接口（SQL Interface），他是一套执行SQL语句的接口，专门用于执行我们 发送给MySQL的那些增删改查的SQL语句（SQL接口只是一个中间层，它负责将用户的SQL语句传递给MySQL服务器，并将执行结果返回给用户。具体的SQL语句执行是由MySQL服务器完成的，包括数据的读取、写入、索引的使用等。SQL接口的作用是提供一个方便的方式让用户与数据库进行交互，隐藏了底层的实现细节。）

• **查询解析器：**比如我们来举一个例子，现在我们有这么一个SQL语句： select id,name,age from users where id=1 这个SQL语句，我们用人脑是直接就可以处理一下，只要懂SQL语法的人，立马大家就知道他是什么意思，但是MySQL自己本 身也是一个系统，是一个数据库管理系统，他没法直接理解这些SQL语句！ 所以此时有一个关键的组件要出场了，那就是查询解析器 这个查询解析器（Parser）就是负责对SQL语句进行解析的，比如对上面那个SQL语句进行一下拆解，拆解成以下几个部分： 我们现在要从“users”表里查询数据 查询“id”字段的值等于1的那行数据 对查出来的那行数据要提取里面的“id,name,age”三个字段。

• **查询优化器：**当我们通过解析器理解了SQL语句要干什么之后，接着会找查询优化器（Optimizer）来选择一个最优的查询路径。

• 存储引擎接口: 我们已经知道一个SQL语句要如何执行了，但是我们现在怎么知道哪些数据在内存里？哪些数据在磁盘 里？我们执行的时候是更新内存的数据？还是更新磁盘的数据？我们如果更新磁盘的数据，是先查询哪个磁盘文件，再更新哪 个磁盘文件？ 是不是感觉一头雾水 所以这个时候就需要存储引擎了，存储引擎其实就是执行SQL语句的，他会按照一定的步骤去查询内存缓存数据，更新磁盘数 据，查询磁盘数据，等等，执行诸如此类的一系列的操作

• 执行器: 执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL 语句的执行计划，大致就是不停的更新或者提取一些数据出来

  具体地，当用户通过SQL接口执行CRUD（Create、Retrieve、Update、Delete）等SQL语句时，SQL接口会按照以下步骤进行处理：

1. 解析：SQL接口首先会对用户输入的SQL语句进行解析，将其分解为不同的语法单元，如关键字、表名、列名、操作符等。

2. 语法检查：接下来，SQL接口会对解析后的语法单元进行语法检查，确保SQL语句符合MySQL的语法规范。如果发现语法错误，SQL接口会返回相应的错误信息给用户。

3. 查询优化：对于查询语句（如SELECT），SQL接口会将其交给查询优化器进行优化。查询优化器会根据表的索引情况、统计信息等，选择最优的查询执行计划。

4. 执行：一旦查询优化完成，SQL接口会将优化后的查询执行计划发送给MySQL服务器。MySQL服务器会按照计划执行查询操作，并返回执行结果。

5. 结果返回：MySQL服务器将执行结果返回给SQL接口，然后SQL接口将结果返回给用户。用户可以根据需要对结果进行处理和展示。

6、InnoDB存储引擎的架构设计是什么样？

InnoDB存储引擎中有一个非常重要的放在内存里的组件，就是缓冲池（Buffer Pool），这里面会缓存很多的数据， 以便于以后在查询的时候，万一你要是内存缓冲池里有数据，就可以不用去查磁盘了，我们看下图。

引擎要执行更新语句的时候 ，比如对某一行数据，他其实会先将这一行数据看看是否在缓冲池里，如果不在的 话，那么会直接从磁盘里加载到缓冲池里来，而且接着还会对这行记录加独占锁。

因为我们想一下，在我们更新“这一行数据的时候，肯定是不允许别人同时更新的，所以必须要对这行记录加 独占锁

6、如何让你更新的数据可以回滚？

其实稍微对数据库 有一点了解的同学都应该知道，如果我们执行一个更新语句，要是他是在一个事务里的话，那么事 务提交之前我们都是可以对数据进行回滚的，也就是把你更新为“xxx”的值回滚到之前的“zhangsan”去。 所以为了考虑到未来可能要回滚数据的需要，这里会把你更新前的值写入undo日志文件，我们看下图。

7、万一系统宕机，如何避免数据丢失？

当我们把要更新的那行记录从磁盘文件加载到缓冲池，同时对他加锁之后，而且还把更新前的旧值写入undo日志文件 之后，我们就可以正式开始更新这行记录了，更新的时候，先是会更新缓冲池中的记录，此时这个数据就是脏数据 了。为这个时候磁盘上“id=10”这行数据的name字段还是“zhangsan”，但是内存里这行数据已经被修改了，所以 就会叫他是脏数据。 我们看下图，我同时把几个步骤的序号标记出来了。

按照上图的说明，现在已经把内存里的数据进行了修改，但是磁盘上的数据还没修改 那么此时万一MySQL所在的机器宕机了，必然会导致内存里修改过的数据丢失，这可怎么办呢？ 这个时候，就必须要把对内存所做的修改写入到一个Redo Log Buffer里去，这也是内存里的一个缓冲区，是用来存 放redo日志的 所谓的redo日志，就是记录下来你对数据做了什么修改，比如对“id=10这行记录修改了name字段的值为xxx”，这 就是一个日志。 我们先看下图的示意

8、如果还没提交事务，MySQL宕机了怎么办？

所以我们都知道，其实在数据库中，哪怕执行一条SQL语句，其实也可以是一个独立的事务，只有当你提交事务之 后，SQL语句才算执行结束。 所以这里我们都知道，到目前为止，其实还没有提交事务，那么此时如果MySQL崩溃，必然导致内存里Buffer Pool中 的修改过的数据都丢失，同时你写入Redo Log Buffer中的redo日志也会丢失 我们看下图

那么此时数据丢失要紧吗？ 其实是不要紧的，因为你一条更新语句，没提交事务，就代表他没执行成功，此时MySQL宕机虽然导致内存里的数据 都丢失了，但是你会发现，磁盘上的数据依然还停留在原样子。 也就是说，“id=1”的那行数据的name字段的值还是老的值，“zhangsan”，所以此时你的这个事务就是执行失败 了，没能成功完成更新，你会收到一个数据库的异常。然后当mysql重启之后，你会发现你的数据并没有任何变化。 所以此时如果mysql宕机，不会有任何的问题。

接着我们想要提交一个事务了，此时就会根据一定的策略把redo日志从redo log buffer里刷入到磁盘文件里去。 此时这个策略是通过innodb_flush_log_at_trx_commit来配置的，他有几个选项。 当这个参数的值为0的时候，那么你提交事务的时候，不会把redo log buffer里的数据刷入磁盘文件的，此时可能你都 提交事务了，结果mysql宕机了，然后此时内存里的数据全部丢失。 相当于你提交事务成功了，但是由于MySQL突然宕机，导致内存中的数据和redo日志都丢失了，我们看下图：

当这个参数的值为1的时候，你提交事务的时候，就必须把redo log从内存刷入到磁盘文件里去，只要事务提交成功，那么redo log就 必然在磁盘里了，我们看下图：

最后来看看，如果innodb_flush_log_at_trx_commit参数的值是2呢？ 他的意思就是，提交事务的时候，把redo日志写入磁盘文件对应的os cache缓存里去，而不是直接进入磁盘文件，可 能1秒后才会把os cache里的数据写入到磁盘文件里去。 这种模式下，你提交事务之后，redo log可能仅仅停留在os cache内存缓存里，没实际进入磁盘文件，万一此时你要 是机器宕机了，那么os cache里的redo log就会丢失，同样会让你感觉提交事务了，结果数据丢了，看下图。

9、MySQL binlog到底是什么东西？

实际上我们之前说的redo log，他是一种偏向物理性质的重做日志，因为他里面记录的是类似这样的东西，“对哪个 数据页中的什么记录，做了个什么修改”。 而且redo log本身是属于InnoDB存储引擎特有的一个东西。 而binlog叫做归档日志，他里面记录的是偏向于逻辑性的日志，类似于“对users表中的id=10的一行数据做了更新操 作，更新以后的值是什么” binlog不是InnoDB存储引擎特有的日志文件，是属于mysql server自己的日志文件。

所以其实我们上一讲讲到，在我们提交事务的时候，会把redo log日志写入磁盘文件中去。然后其实在提交事务的时 候，我们同时还会把这次更新对应的binlog日志写入到磁盘文件中去，如下图所示。

对于binlog日志，其实也有不同的刷盘策略，有一个sync_binlog参数可以控制binlog的刷盘策略，他的默认值是0， 此时你把binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存缓存。 所以跟之前分析的一样，如果此时机器宕机，那么你在os cache里的binlog日志是会丢失的，我们看下图的示意

如果要是把sync_binlog参数设置为1的话，那么此时会强制在提交事务的时候，把binlog直接写入到磁盘文件里去， 那么这样提交事务之后，哪怕机器宕机，磁盘上的binlog是不会丢失的，如下图所示

当我们把binlog写入磁盘文件之后，接着就会完成最终的事务提交，此时会把本次更新对应的binlog文件名称和这次 更新的binlog日志在文件里的位置，都写入到redo log日志文件里去，同时在redo log日志文件里写入一个commit标 记。 在完成这个事情之后，才算最终完成了事务的提交，我们看下图的示意。

最后在redo日志中写入commit标记有什么意义呢？

我们来举个例子，假设我们在提交事务的时候，一共有上图中的5、6、7三个步骤，必须是三个步骤都执行完毕，才算 是提交了事务。那么在我们刚完成步骤5的时候，也就是redo log刚刷入磁盘文件的时候，mysql宕机了，此时怎么 办？ 这个时候因为没有最终的事务commit标记在redo日志里，所以此次事务可以判定为不成功。不会说redo日志文件里 有这次更新的日志，但是binlog日志文件里没有这次更新的日志，不会出现数据不一致的问题。 如果要是完成步骤6的时候，也就是binlog写入磁盘了，此时mysql宕机了，怎么办？ 同理，因为没有redo log中的最终commit标记，因此此时事务提交也是失败的。 必须是在redo log中写入最终的事务commit标记了，然后此时事务提交成功，而且redo log里有本次更新对应的日 志，binlog里也有本次更新对应的日志 ，redo log和binlog完全是一致的。

现在我们假设已经提交事务了，此时一次更新“update users set name='xxx' where id=10”，他已经把内存里的 buffer pool中的缓存数据更新了，同时磁盘里有redo日志和binlog日志，都记录了把我们指定的“id=10”这行数据 修改了“name='xxx'”。 此时我们会思考一个问题了，但是这个时候磁盘上的数据文件里的“id=10”这行数据的name字段还是等于 zhangsan这个旧的值啊！

所以MySQL有一个后台的IO线程，会在之后某个时间里，随机的把内存buffer pool中的修改后的脏数据给刷回到磁 盘上的数据文件里去，我们看下图：

10、执行更新操作的时候，为什么不能执行修改磁盘上的数据？

在MySQL中，执行更新操作时，不能直接修改磁盘上的数据，而是需要通过内存中的缓存来进行操作。这是因为MySQL使用了缓存机制，将磁盘上的数据缓存在内存中，以提高数据访问速度。缓存分为查询缓存和InnoDB缓存，其中查询缓存用于缓存查询结果，而InnoDB缓存则用于缓存表数据和索引数据。

当执行更新操作时，如果直接修改磁盘上的数据，会导致缓存中的数据与磁盘上的数据不一致，从而可能导致数据的丢失或者不一致。因此，MySQL采用了一种称为“写后日志”的机制来保证数据的一致性。

写后日志（Write-Ahead Logging，WAL）是一种常见的数据库技术，它的基本思想是在修改数据之前，先将修改操作记录到一个日志中，然后再将修改操作应用到内存中的缓存和磁盘上的数据中。这样，在发生故障时，可以通过日志来恢复数据，保证数据的一致性。

具体地，当执行更新操作时，MySQL会先将修改操作记录到InnoDB缓存中的写入日志（Write-Ahead Log，WAL）中，然后再将修改操作应用到内存中的缓存和磁盘上的数据中。如果在修改过程中发生故障，可以通过WAL日志来恢复数据，保证数据的一致性。

总之，执行更新操作时，不能直接修改磁盘上的数据，而是需要通过内存中的缓存和写后日志机制来进行操作，以保证数据的一致性和安全性。

11、Buffer Pool这个内存数据结构到底长个什么样子？

• 数据页：MySQL中抽象出来的数据单位

• 缓存页：和数据页对应起来

• 描述信息:对于每个缓存页，他实际上都会有一个描述信息，这个描述信息大体可以认为是用来描述这个缓 存页的 比如包含如下的一些东西：这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址以及别的一些(描述页和缓存页都是以16KB为单位进行管理，但它们在内存中的存储方式和使用方式是不同的。描述页是磁盘上的数据在内存中的表示，而缓存页是描述页的缓存副本，用于提高数据的访问速度。)

12、 从磁盘读取数据页到Buffer Pool的时候，free链表有什么用？

但是此时在从磁盘上读取数据页放入Buffer Pool中的缓存页的时候，必然涉及到一个问题，那就是哪些缓存页是空闲的？

所以数据库会为Buffer Pool设计一个free链表，他是一个双向链表数据结构，这个free链表里，每个节点就是一个空闲的缓存 页的描述数据块的地址，也就是说，只要你一个缓存页是空闲的，那么他的描述数据块就会被放入这个free链表中。

接着我们来看下一个问题：你怎么知道一个数据页有没有被缓存呢？

所以其实数据库还会有一个哈希表数据结构，他会用表空间号+数据页号，作为一个key，然后缓存页的地址作为value。

也就是说，每次你读取一个数据页到缓存之后，都会在这个哈希表中写入一个key-value对，key就是表空间号+数据页号， value就是缓存页的地址，那么下次如果你再使用这个数据页，就可以从哈希表里直接读取出来他已经被放入一个缓存页了。

13、当我们更新Buffer Pool中的数据时，flush链表有什么用？

数据库在这里引入了另外一个跟free链表类似的flush链表，这个flush链表本质也是通过缓存页的描述数据块中的两个指 针，让被修改过的缓存页的描述数据块，组成一个双向链表。

凡是被修改过的缓存页，都会把他的描述数据块加入到flush链表中去，flush的意思就是这些都是脏页，后续都是要flush刷新 到磁盘上去的 所以flush链表的结构如下图所示，跟free链表几乎是一样的。

14、当Buffer Pool中的缓存页不够的时候，如何基于LRU算法淘汰部分缓 存？

你必须把一个缓存页里被修改过的数据，给他刷到磁盘上的数据页里去，然后这个缓存页就可以清空了， 让他重新变成一个空闲的缓存页。 接着你再把磁盘上你需要的新的数据页加载到这个腾出来的空闲缓存页中去，如下图。

这个LRU链表大致是怎么个工作原理呢？

简单来说，我们看下图，假设我们从磁盘加载一个数据页到缓存页的时候，就把这个缓存页的描述数据块放到LRU链 表头部去，那么只要有数据的缓存页，他都会在LRU里了，而且最近被加载数据的缓存页，都会放到LRU链表的头部 去。

然后假设某个缓存页的描述数据块本来在LRU链表的尾部，后续你只要查询或者修改了这个缓存页的数据，也要把这 个缓存页挪动到LRU链表的头部去，也就是说最近被访问过的缓存页，一定在LRU链表的头部，如下图。

15、简单的LRU链表在Buffer Pool实际运行中，可能导致哪些问题？

MySQL的预读机制，这个所谓预读机制，说的就是当你从磁盘上加载一个数据页的时候，他可 能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去！ 举个例子，假设现在有两个空闲缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓 存里去了，正好每个数据页放入一个空闲缓存页！

所以我们来看看，到底哪些情况下会触发MySQL的预读机制呢？

（1）有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果顺序的访问了一个区里的多个 数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓 存里去

（2）如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会 直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去（如果我们有大量的记录，那么表空间中页的数量就会非常的多，为了更好的管理这些页，设计者又提出了区的概念。 对于16KB的页来说，连续的64个页（连续的）就是一个区，也就是说一个区的大小为1MB。）

这个机制是通过参数innodb_random_read_ahead来控制的，他默认是OFF，也就是这个规则是关闭的

接着我们讲另外一种可能导致频繁被访问的缓存页被淘汰的场景，那就是全表扫描

当你要淘汰掉一些缓存页腾出空间的时候，就会把LRU链表尾部一直被频繁访问的缓存页给淘汰掉了，而留下了 之前全表扫描加载进来的大量的不经常访问的缓存页！

16、MySQL是如何基于冷热数据分离的方案，来优化LRU算法的？

第一次被加载了数据的缓存页，都会不停的移动到冷数据区域的链表头部，如上图所示 那么你要知道，冷数据区域的缓存页肯定是会被使用的，那么冷数据区域的缓存页什么时候会放到热数据区域呢？ 实际上肯定很多人会想，只要对冷数据区域的缓存页进行了一次访问，就立马把这个缓存页放到热数据区域的头部行 不行呢？如下图所示。

其实这也是不合理的，如果你刚加载了一个数据页到那个缓存页，他是在冷数据区域的链表头部，然后立马（在1ms 以内）就访问了一下这个缓存页，之后就再也不访问他了呢？难道这种情况你也要把那个缓存页放到热数据区域的头 部吗？ 所以MySQL设定了一个规则，他设计了一个innodb_old_blocks_time参数，默认值1000，也就是1000毫秒 也就是说，必须是一个数据页被加载到缓存页之后，在1s之后，你访问这个缓存页，他才会被挪动到热数据区域的链 表头部去。 因为假设你加载了一个数据页到缓存去，然后过了1s之后你还访问了这个缓存页，说明你后续很可能会经常要访问 它，这个时间限制就是1s，因此只有1s后你访问了这个缓存页，他才会给你把缓存页放到热数据区域的链表头部去。

在这样的一套缓存页分冷热数据的加载方案，以及冷数据转化为热数据的时间限制方案，还有就是淘汰缓存页的时候 优先淘汰冷数据区域的方案，基于这套方案，大家会发现，之前发现的问题，完美的被解决了。 因为那种预读机制以及全表扫描机制加载进来的数据页，大部分都会在1s之内访问一下，之后可能就再也不访问了， 所以这种缓存页基本上都会留在冷数据区域里。然后频繁访问的缓存页还是会留在热数据区域里。 当你要淘汰缓存的时候，优先就是会选择冷数据区域的尾部的缓存页，这就是非常合理的了！他不会让刚加载进来的 缓存页占据LRU链表的头部，频繁访问的缓存页在LRU链表的尾部，淘汰的时候淘汰尾部的频繁访问的缓存页了！ 问题完美的被解决了。 这就是LRU链表冷热数据分离的一套机制。

17、 MySQL是如何将LRU链表的使用性能优化到极致的？

我们来看看LRU链表的热数据区域的一个性能优化的点，就是说，在热数据区域中，如果你访问了一个缓存页， 是不是应该要把他立马移动到热数据区域的链表头部去？ 我们看下面的图示。

但是你要知道，热数据区域里的缓存页可能是经常被访问的，所以这么频繁的进行移动是不是性能也并不是太好？也 没这个必要。 所以说，LRU链表的热数据区域的访问规则被优化了一下，即你只有在热数据区域的后3/4部分的缓存页被访问了，才 会给你移动到链表头部去。 如果你是热数据区域的前面1/4的缓存页被访问，他是不会移动到链表头部去的。 举个例子，假设热数据区域的链表里有100个缓存页，那么排在前面的25个缓存页，他即使被访问了，也不会移动到 链表头部去的。但是对于排在后面的75个缓存页，他只要被访问，就会移动到链表头部去。 这样的话，他就可以尽可能的减少链表中的节点移动了。

18、对于LRU链表中尾部的缓存页，是如何淘汰他们刷入磁盘的？

定时把LRU尾部的部分缓存页刷入磁盘 首先第一个时机，并不是在缓存页满的时候，才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘，而是有一个后台 线程，他会运行一个定时任务，这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页，刷入 磁盘里去，清空这几个缓存页，把他们加入回free链表去！ 所以实际上在缓存页没用完的时候，可能就会清空一些缓存页了，我们看下面的图示。

只要有缓存页被刷人磁盘，大家可以想象一下，那么这个缓存页必然会加入到free链表中，从flush链表中移除，从lru 链表中移除。

如果仅仅是把LRU链表中的冷数据区域的缓存页刷入磁盘，大家觉得够吗？ 明显不够啊，因为在lru链表的热数据区域里的很多缓存页可能也会被频繁的修改，难道他们永远都不刷入磁盘中了 吗？ 所以这个后台线程同时也会在MySQL不怎么繁忙的时候，找个时间把flush链表中的缓存页都刷入磁盘中，这样被你修 改过的数据，迟早都会刷入磁盘的！ 只要flush链表中的一波缓存页被刷入了磁盘，那么这些缓存页也会从flush链表和lru链表中移除，然后加入到free链表 中去！ 所以你可以理解为，你一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停 的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。 另外一边，你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存 页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。 这就是一个动态运行起来的效果！

19、如何通过多个Buffer Pool来优化数据库的并发性能？

我们看下图，就是一个多线程并发访问Buffer Pool的示意图。

多线程并发访问一个Buffer Pool，必然是要加锁的，然后让一个线程先完成一系列的操作，比如说加载数据页到 缓存页，更新free链表，更新lru链表，然后释放锁，接着下一个线程再执行一系列的操作。

一般来说，MySQL默认的规则是，如果你给Buffer Pool分配的内存小于1GB，那么最多就只会给你一个Buffer Pool。 但是如果你的机器内存很大，那么你必然会给Buffer Pool分配较大的内存，比如给他个8G内存，那么此时你是同时可 以设置多个Buffer Pool的，比如说下面的MySQL服务器端的配置。

 [server]

 innodb_buffer_pool_size = 8589934592

 innodb_buffer_pool_instances = 4

我们给buffer pool设置了8GB的总内存，然后设置了他应该有4个Buffer Pool，此时就是说，每个buffer pool的大小 就是2GB

这个时候，假设多个线程并发过来访问，那么不就可以把压力分散开来了吗？有的线程访问这个buffer pool，有的线 程访问那个buffer pool。

20、如何通过chunk来支持数据库运行期间的Buffer Pool动态调 整？

buffer pool在运行期间是不能动态的调整自己的大小的，因为动态调整buffer pool大小，比如buffer pool本来是8G，运行期间你给调整为16G了，此时是怎么实 现的呢？ 就是需要这个时候向操作系统申请一块新的16GB的连续内存，然后把现在的buffer pool中的所有缓存页、描述数据 块、各种链表，都拷贝到新的16GB的内存中去。这个过程是极为耗时的，性能很低下，是不可以接受的！

MYSQL设计了一个chunk机制，也就是说buffer pool是由很多chunk组 成的，他的大小是innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB。

21、我们写入数据库的一行数据，在磁盘上是怎么存储的？

变长字段的长度列表，null值列表，数据头，column01的值，column02的值，column0n的值......

现在我们来假设一下，现在有一行数据，他的几个字段的类型为VRACHAR(10)，CHAR(1)，CHAR(1)，那么 他第一个字段是VARCHAR(10)，这个长度是可能变化的，所以这一行数据可能就是类似于：hello a a，这样子，第一 个字段的值是“hello”，后面两个字段的值都是一个字符，就是一个a 然后另外一行数据，同样也是这几个字段，他的第一个字段的值可能是“hi”，后面两个字段也是“a”，所以这一行 数据可能是类似于：hi a a。一共三个字段，第一个字段的长度是是不固定的，后面两个字段的长度都是固定的1个字 符。

在存储每一行数据的时候，都保存一下他的变长字段的长度列表，这样才能解决一行数据的读取问题。 也就是说，你在存储“hello a a”这行数据的时候，要带上一些额外的附加信息，比如第一块就是他里面的变长字段 的长度列表 也就是说，这个hello是VARCHAR(10)类型的变长字段的值，那么这个“hello”字段值的长度到底是多少？ 我们看到“hello”的长度是5，十六进制就是0x05，所以此时会在“hello a a”前面补充一些额外信息，首先就是变 长字段的长度列表，你会看到这行数据在磁盘文件里存储的时候，其实是类似如下的格式：0x05 null值列表 数据头 hello a a。

如果说有多个变长字段，比如一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1)，一共5个字段，其中三个是变长字 段，此时假设一行数据是这样的：hello hi hao a a 此时在磁盘中存储的，必须在他开头的变长字段长度列表中存储几个变长字段的长度，一定要注意一点，他这里是逆 序存储的！现在hello hi hao三个字段的长度分别是0x05 0x02 0x03，但是实际存放在变长字段长度列表的时候，是逆序放的， 所以一行数据实际存储可能是下面这样的： 0x03 0x02 0x05 null值列表 头字段 hello hi hao a a

22、一行数据中的多个NULL字段值在磁盘上怎么存储？

实际在磁盘上存储数据的时候，一行数据里的NULL值是不会按照字符串的方式存放在磁盘上浪费空间 的。对所有的NULL值，不通过字符串在磁盘上存储，而是通过二进制的bit位来存储，一行数据里假设有多个字段 的值都是NULL，那么这多个字段的NULL，就会以bit位的形式存放在NULL值列表中。

变长字段长度列表 NULL值列表 头信息 column1=value1 column2=value2 ... columnN=valueN

NULL值列表，这个NULL值列表是这样存放的，你所有允许值为NULL的字段，注意，是允许值为NULL，不 是说一定值就是NULL了，只要是允许你为NULL的字段，在这里每个字段都有一个二进制bit位的值，如果bit值是1说 明是NULL，如果bit值是0说明不是NULL。如果这个变长字段的值是NULL，就不用在变长字段长度列表里 存放他的值长度了（NULL值逆序排放，是NULL该位为1，反之则是0，同样是逆序排放，一般是8个bit位的倍数）

23、磁盘上的一行数据到底如何读取出来的？

我们结合上面的磁盘上的数据存储格式来思考一下，一行数据到底是如何读取出来的呢？ 再看上面的磁盘数据存储格式： 0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN 首先他必然要把变长字段长度列表和NULL值列表读取出来，通过综合分析一下，就知道有几个变长字段，哪几个变长 字段是NULL，因为NULL值列表里谁是NULL谁不是NULL都一清二楚。 此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值长度，然后也知道哪几个字段是NULL的，此时 根据这些信息，就可以从实际的列值存储区域里，把你每个字段的值读取出来了。 如果是变长字段的值，就按照他的值长度来读取，如果是NULL，就知道他是个NULL，没有值存储，如果是定长字 段，就按照定长长度来读取，这样就可以完美的把你一行数据的值都读取出来了！

24、我们每一行的实际数据在磁盘上是如何存储的？

有一行数据是“jack NULL m NULL xx_school”，那么他真实存储大致如下所示： 0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school

刚开始先是他的变长字段的长度，用十六进制来存储，然后是NULL值列表，指出了谁是NULL，接着是40个bit位的数据头， 然后是真实的数据值，就放在后面。 在读取这个数据的时候，他会根据变长字段的长度，先读取出来jack这个值，因为他的长度是4，就读取4个长度的数据，jack 就出来了； 然后发现第二个字段是NULL，就不用读取了； 第三个字段是定长字段，直接读取1个字符就可以了，就是m这个值； 第四个字段是NULL，不用读取了； 第五个字段是变长字段长度是9，读取出来xx_school就可以了。

实际上字符串这些东西都是根据我们数据库指定的字符集编码，进行编码之后再存储的，所以大致看起来一行数据是如下所示 的： 0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262。在实际存储一行 数据的时候，会在他的真实数据部分，加入一些隐藏字段

• 首先有一个DB_ROW_ID字段，这就是一个行的唯一标识，是他数据库内部给你搞的一个标识，不是你的主键ID字段。如果我 们没有指定主键和unique key唯一索引的时候，他就内部自动加一个ROW_ID作为主键。

• 接着是一个DB_TRX_ID字段，这是跟事务相关的，他是说这是哪个事务更新的数据，这是事务ID，

• 最后是DB_ROLL_PTR字段，这是回滚指针，是用来进行事务回滚的

所以如果你加上这几个隐藏字段之后，实际一行数据可能看起来如下所示： 0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID） 00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

25、行溢出怎么办？

比如有一个表的字段类型是VARCHAR(65532)，意思就是最大可以包含65532个字符，那也就是65532个字节，这就远大于 16kb的大小了，也就是说这一行数据的这个字段都远超一个数据页的大小了！ 这个时候实际上会在那一页里存储你这行数据，然后在那个字段中，仅仅包含他一部分数据，同时包含一个20个字节的指针， 指向了其他的一些数据页，那些数据页用链表串联起来，存放这个VARCHAR(65532)超大字段里的数据。

26、用于存放磁盘上的多行数据的数据页到底长个什么样子？

一个数据页拆分成了很多个部分，大体上来说包含了文件头、数据页头、最小记录和最大记录、多 个数据行、空闲空间、数据页目录、文件尾部。

我们现在要插入一行数据，此时数据库里可是一行数据都没有的，此时应该先 是从磁盘上加载一个空的数据页到缓存页里去

27、MySQL数据库的日志顺序读写以及数据文件随机读写的原理

简单来说，MySQL在工作的时候，尤其是执行增删改操作的时候，肯定会先从表空间的磁盘文件里读取数据页出来， 这个过程其实就是典型的磁盘随机读操作

我们先看下面的图，图里有一个磁盘文件的示意，里面有很多数据页，然后你可能需要在一个随机的位置读取一个数 据页到缓存，这就是磁盘随机读

因为你要读取的这个数据页可能在磁盘的任意一个位置，所以你在读取磁盘里的数据页的时候只能是用随机读的这种 方式。 磁盘随机读的性能是比较差的，所以不可能每次更新数据都进行磁盘随机读，必须是读取一个数据页之后放到Buffer Pool的缓存里去，下次要更新的时候直接更新Buffer Pool里的缓存页。

接着我们来看磁盘顺序读写，之前我们都知道，当你在Buffer Pool的缓存页里更新了数据之后，必须要写一条redo log日志，这个redo log日志，其实就是就是走的顺序写 所谓顺序写，就是说在一个磁盘日志文件里，一直在末尾追加日志，我们看下图

写redo log日志的时候，其实是不停的在一个日志文件末尾追加日志的，这就是磁盘顺序 写。

28、在Buffer Pool执行完增删改之后，写入日志文件的redo log长什么样？

是redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了 几个字节的值，具体修改的值是什么，他里面需要记录的就是表空间号+数据页号+偏移量+修改几个字节的值+具体 的值 itjc8.com搜集整理 所以根据你修改了数据页里的几个字节的值，redo log就划分为了不同的类型，MLOG_1BYTE类型的日志指的就是修 改了1个字节的值，MLOG_2BYTE类型的日志指的就是修改了2个字节的值，以此类推，还有修改了4个字节的值的日 志类型，修改了8个字节的值的日志类型。 当然，如果你要是一下子修改了一大串的值，类型就是MLOG_WRITE_STRING，就是代表你一下子在那个数据页的某 个偏移量的位置插入或者修改了一大串的值。 所以其实一条redo log看起来大致的结构如下所示： 日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据