一篇文章带你读懂MySQL的InnoDB存储引擎Buffer Pool原理

最新推荐文章于 2022-12-29 17:47:12 发布

cyd_0619

最新推荐文章于 2022-12-29 17:47:12 发布

阅读量606

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/cyd_0619/article/details/118422674

版权

mysql 专栏收录该内容

7 篇文章 4 订阅

订阅专栏

一个不变的原则：网络连接必须让线程来处理
在这里插入图片描述
一条SQL语句的执行

MySQL内部的工作线程从一个网络连接中取出来一个SQL语句，然后交给SQL接口去执行SQL语句，SQL接口将SQL语句交给查询解析器去解析SQL，解析完SQL，查询解析器再将SQL语句交给查询优化器，去获取一个最优的执行计划，最后交由执行器，执行器根据这个执行计划去调用存储引擎的接口，完成SQL语句的执行。

一、InnoDB存储引擎

1.1 基于InnoDB存储引擎完成一条更新语句的执行

更新语句：update users set name = ‘lisi’ where id = 10
在这里插入图片描述

第一步：InnoDB存储引擎中有一个非常重要的组件缓存池（Buffer Pool），我们执行一条更新语句的时候，首先会去 Buffer Pool 中看下是否有id=10这条数据，如果没有的话，就会从磁盘文件中读取数据到 Buffer Pool 中，而且接着还会对这行数据加独占锁。
第二步：假设 id=10 这行数据 name 原先是 “zhangsan”，现在我们要更新为 “lisi”，name 此时我们得先把要更新的原来的值 “zhangsan” 和 id=10 这些信息，写入到undo日志文件中去，以便于后续事务回滚后，我们可以将 id=10 这条数据回滚回去。
第三步：更新 Buffer Pool 中 id=10 的数据，将 name 更新为 “lisi”，这时候 Buffer Pool 中的这个数据就是脏数据了，因为这个时候磁盘上 id=10 这行数据的 name 字段还是 “zhangsan”，但是内存里这行数据已经被修改了，所以这会叫它脏数据。
第四步：如果此时万一MySQL所在的机器宕机了，必然会导致内存里修改过的数据丢失，所以我们需要对内存里所做的修改写入到一个Redo Log Buffer中去，它也是内存里的一个缓冲区，用来存放 redo 日志（redo日志： 就是记录下来你对数据做了什么修改，redo日志其实是用来MySQL宕机后，重启的时候用来恢复更新过的数据）
第五步：提交事务后，此时会根据一定的策略把 redo 日志从Redo Log Buffer中刷入到磁盘文件中。
这个策略是通过innodb_flush_log_at_commit来配置的
innodb_flush_log_at_commit = 0，提交事务时，不会把Redo Log Buffer刷入到磁盘文件中，这种会存储MySQL宕机后内存数据丢失；
innodb_flush_log_at_commit = 1，提交事务时，必须把Redo Log Buffer刷入到磁盘文件中，数据绝对不会丢失；
innodb_flush_log_at_commit = 1，提交事务时，把Redo Log Buffer里 redo 日志写入 os cache 缓存里去，可能1秒后才会把 os cache 里的数据刷入到磁盘文件里去；
第六步：提交事务的时候，同时也会写binlog日志文件，binlog 日志的刷盘由sync_binlog参数控制。
syn_binlog = 0，此时会把binlog 写入os cache中，不是进入磁盘文件；
syn_binlog = 1，此时会强制在事务提交的时候，把 binlog 日志直接写入到磁盘文件里去；
最后一步：当我们把 binlog 写入磁盘文件之后，接着就会完成最终的事务提交，此时会把本次更新对应的 binlog 文件名称和这次更新的 binlog 日志在文件里的位置，都写入到 redo 日志文件里去，同时在 redo 日志文件里写入一个 commit 标记。
对于 Buffer Pool 中的脏数据，MySQL后台有个IO线程会随机的将内存中更新后的脏数据刷回磁盘。

（1）redo log 是InnoDB存储引擎特有的，而binlog是MySQL Server自己的日志文件。

（2）redo日志的是那种刷盘策略到底选择哪一种？

其实对于redo日志的三种刷盘策略，我们通常建议是设置为1，也就是说，提交事务的时候，redo日志必须是刷入磁盘文件里的，这样严格保证事务提价后数据不会丢失，因为有redo日志在磁盘文件里，可以恢复你做的所有修改。

如果选择0的话，可能你提交事务之后，MySQL宕机，那么此时redo日志没有刷盘，导致内存里的redo日志丢失，你提交的事务更新的数据就丢失了。

（3）最后一步redo日志写入commit标记的意义是什么？

说白了，它其实是用来保持redo log与binlog日志一致的。

我们举个例子，假设我们在提交事务的时候，一共有图中的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 完全是一致的。

（4）执行更新操作的时候，为什么不能直接修改磁盘上的数据？

因为直接修改磁盘上的数据，相当于是对磁盘进行随机读写，那速度是相当的慢，随便一个大磁盘文件的随机读写性能，可能都要几百毫秒，如果是这么搞的划，可能我们数据库每秒也就只能处理几百个请求了。

（5）一句话总结Buffer Pool

在这里我们简单对Buffer Pool这个东西做一下总结：它其实是数据库中我们第一个必须要搞清楚的核心组件，因为增删改查操作首先就是针对这个内存中的Buffer Pool里的数据执行的，同时配合了后续的undo log、redo log、刷盘等机制和操作。

所以Buffer Pool就是数据库的一个内存组件，里面缓存了磁盘上的真实数据，然后我们的Java系统对数据库执行的增删改查操作，其实主要就是对这个Buffer Pool中的缓存数据执行的。

1.2 Buffer Pool

1.2.1 Buffer Pool 内存数据结构到底长什么样？

Buffer Pool其实本质就是一大块内存数据结构，由一大堆的缓存页和描述数据块组成的，然后加上了各种链表（free、flush、lru）来辅助它的运行。
在这里插入图片描述

Buffer Pool 的大小：默认是128M，有一点偏小了，我们实际生产环境下完全可以对Buffer Pool进行整改。比如设置为2GB，在MySQL配置文件 my.cnf 中修改 innodb_buffer_pool_size = 2147483648。
缓存页 ：我们都知道数据库的核心数据模型就是 表+行+字段 的概念，那么我们的数据是一行一行的放在Buffer Pool中吗？
不是的，实际上MySQL对数据抽象出来了一个数据页的概念，它把很多行数据放在了一个数据页里，也就是说我们的磁盘文件中就是会有很多的数据页，每一页里面放了很多行数据。默认情况下，磁盘中存放的一个数据页的大小是16KB，而Buffer Pool中存放的叫缓存页，一个缓存页和磁盘上的数据页是一一对应的，缓存页的大小也是16KB。
描述数据块：对于每个缓存页，它实际上都会有一个描述信息，这个描述信息大体可以认为是用来描述这个缓存页的，比如包含如下的一些东西：这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址等。这个描述信息本身也是一块数据，在Buffer Pool中，每个缓存页的描述信息放在最前面，然后各个缓存页放在后面。
而且在这里我们要注意一点，Buffer Pool中的描述数据块大概相当于缓存页大小的5%左右，也就是每个描述数据块大概800个字节左右的大小，然后假设设置你的 Buffer Pool 大小是 128M，实际上 Buffer Pool 真正的最终大小会超出一些，可能有个130多MB的样子，因为它里面还要存放每个缓存页的描述数据。
free链表：存放空闲的缓存页
flush链表：存放脏的缓存页，也就是脏页
lru链表，最近最少使用链表，通过它可以淘汰那些很少被访问的缓存页

1.2.2 数据库启动的时候，是如何初始化Buffer Pool的？

其实这个也很简单，数据库只要一启动，就会按照你设置的Buffer Pool大小，稍微再加大一点，去找操作系统申请一块内存区域，作为Buffer Pool的内存区域，然后当内存区域申请完毕后，数据库就会按照默认的缓存页的16KB的大小以及对应的800字节左右的描述数据块的大小，在Buffer Pool中划出来一个一个的缓存页和一个一个的它们对应的描述数据。只不过这个时候，Buffer Pool中的一个一个缓存页都是空的，里面什么都没有，要等数据库运行起来，当我们对数据执行增删改查的操作的时候，才会把数据对应的数据页从磁盘文件里读取出来，放到Buffer Pool中的缓存页里去。

1.2.3 free链表

（1）我们怎么知道哪些缓存页是空闲的呢？

我们在执行增删改查的时候，此时就需要不停的从磁盘上读取一个一个数据页放到Buffer Pool中对应的缓存页里去，把数据缓存起来，那么以后就可以对这个数据在内存里执行增删改查了。但是此时在从磁盘上读取数据页放入Buffer Pool中的缓存页的时候，必然涉及到一个问题，那就是哪些缓存页是空闲的？

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

刚开始启动数据库的时候，可能所有的缓存页都是空的，因为此时可能是一个空的数据库，一条数据都没有，所以此时所有缓存页的描述数据块，都会被加入free链表中。

每个free链表是一个双向链表，并且这个free链表有一个基础节点，它会引用链表的头节点和尾节点，里面还存储了链表中有多少个描述数据块的节点，也就是有多少个空闲的缓存页。

（2）free链表占用多少内存空间？

可能有的人会以为这个描述数据块，在Buffer Pool里有一份，在free链表里也有一份，好像在内存里有两个一模一样的描述数据块，是这样的吗？其实这么想就大错特错了。

这里要给大家讲明白一点，这个free链表，它本身其实就是由Buffer Pool里的描述数据块组成的，你可以认为每个描述数据块里都有两个指针，一个是free_pre，一个是free_next，分别指向自己的上一个free链表节点，以及下一个free链表的节点。

通过Buffer Pool中的描述数据块的 free_pre 和 free_next 两个指针，就可以把所有的描述数据块串成一个free链表，上面为了画图需要，所以把描述数据块单独画了一份出来，表示它们之间的指针引用关系。

对于free链表而言，只有一个基础节点是不属于 Buffer Pool 的，它是40个字节大小的节点，里面就存放了free链表的头结点的地址、尾节点的地址，还有free链表里当前有多少个节点。

（3）如何将磁盘上的数据读取到Buffer Pool的缓存页中去？

首先，我们需要从free链表里获取一个描述数据块，然后就可以对应的获取到这个描述数据块对应的空闲缓存页了。

接着，我们就可以把磁盘上的数据页读取到对应的缓存页里去，同时把相关的一些描述数据写入缓存页的描述数据块里去，比如这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址等，最后把那个描述数据块从free链表里去除就可以了。

（4）这个描述数据块是怎么从free链表里移除的呢？

简单，我给你一段伪代码演示一下。

假设有一个描述数据块02，它的上一个节点是描述数据块01，下一个节点是描述数据块03，那么它在内存中的数据结构如下。

// 描述数据块
DescriptionDataBlock {
	// 这个块自己就是block02
	block_id = block02
	// 在free链表中的上一个节点是block01
	free_pre = block01;
	// 在free链表中的下一个节点是block03
	free_next = block03;
}

现在假设block03被使用了，要从free链表中移除，那么此时直接就可以把block02节点的free_next设置为null就可以了，block03就从free链表里失去引用关系了，如下所示：

// 描述数据块
DescriptionDataBlock {
	// 这个块自己就是block02
	block_id = block02
	// 在free链表中的上一个节点是block01
	free_pre = block01;
	// 在free链表中的下一个节点是空的
	free_next = null;
}

想必看到这里，大家就完全明白，磁盘中的数据页是如何读取到Buffer Pool中的缓存页里去的了，而且这个过程中free链表是用来干什么的。

（5）我们怎么知道数据页有没有被缓存？

我们在执行增删改查的时候，肯定是先看看这个数据页有没有被缓存，如果没被缓存就走上面的逻辑，从free链表中找到一个空闲的缓存页，从磁盘上读取数据页写入缓存页，写入描述数据块，从free链表中移除这个描述数据块。但是如果数据页已经被缓存了，那么就会直接使用。

为了快速知道数据页有没有被缓存，MySQL设计了一个哈希表数据结构，它会用表空间号+数据页号，作为一个key，然后缓存页的地址作为value。当你要使用一个数据页的时候，通过 “表空间号+数据页号” 作为key去这个哈希表里查一下，如果没有就读取数据页了，如果已经有了，就说明数据页已经被缓存了。

1.2.4 flush链表

（1）什么是flush链表？flush链表有什么用？

我们知道，内存中更新了数据的脏页，最终都是要刷回磁盘的，但是那些没有修改的缓存页是不需要刷会磁盘的，所以数据库在这里引入了另一个跟free链表类似的flush链表，这个flush链表本质也是通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表。

凡是被修改过的缓存页，都会把它的描述数据块加入到flush链表中去，flush的意思就是这些都是脏页，后续都是要flush刷新到磁盘上去的。

（2）flush链表构造的伪代码演示

我们用一些伪代码来给大家展示一下这个flush链表的构造过程，比如现在缓存页01被修改了数据，那么它就是脏页了，此时就必须把它加入到flush链表中去。

此时缓存页01的描述数据块假如如下所示

// 描述数据块
DescriptionDataBlock {
	// 这是缓存页01的数据块
	block_id = block01;
	// 在free链表中的上一个节点和下一个节点，因为这个缓存页已经被更新过了，肯定不在free链表里了，所以它在free链表中的两个指针都是null
	free_pre = null;
	free_next = null;
	// 在flush链表中的上一个节点和下一个节点，现在因为flush链表就它一个节点，所以也都是null
	flush_pre = null;
	flush_next = null;
}

// flush链表的基础节点
FlushLinkListBaseNode {
    // 基础节点指向链表起始节点和结束节点的指针
    // flush链表中目前就一个缓存页01，所以指向它的描述数据块
    start = block01;
    end = block01;
    // flush链表中有几个节点
    count = 1;
}

好了，我们可以看到，现在flush链表的基础节点就指向了一个block01的节点，接着比如缓存页02被更新了，它也是脏页了，此时它的描述数据块也要被加入到flush链表中去。

// 描述数据块
DescriptionDataBlock {
	// 这是缓存页01的数据块
	block_id = block01;
	// 在free链表中的上一个节点和下一个节点，因为这个缓存页已经被更新过了，肯定不在free链表里了，所以它在free链表中的两个指针都是null
	free_pre = null;
	free_next = null;
	// 在flush链表中的上一个节点和下一个节点，现在因为flush链表中它是起始节点，所以它的flush_pre指针是null
	flush_pre = null;
	// 然后flush链表中它的下一个节点是block02，所以flush_next指向block02
	flush_next = block02;
}
// 描述数据块
DescriptionDataBlock {
	// 这是缓存页02的数据块
	block_id = block02；
	// 在free链表中的上一个节点和下一个节点，因为这个缓存页已经被更新过了，肯定不在free链表里了，所以它在free链表中的两个指针都是null
	free_pre = null;
	free_next = null;
	// 在flush链表中的上一个节点和下一个节点，现在因为flush链表中它是尾节点，它的上一个节点是block01，下一个节点就是null
	flush_pre = block01;
	// 然后flush链表中它的下一个节点是block02，所以flush_next指向block02
	flush_next = null;
}

// flush链表的基础节点
FlushLinkListBaseNode {
    // 基础节点指向链表起始节点和结束节点的指针
    // flush链表中目前有缓存页01和缓存页02，所以指向它的描述数据块
    start = block01; // 起始节点是block01
    end = block02;   // 尾巴节点是block02
    // flush链表中有几个节点
    count = 2;
}

大家可以看到，当你更新缓存页的时候，通过变换缓存页中的描述数据块的flush链表的指针，就可以把脏页的描述数据块组成一个双向链表，也就是flush链表，而且flush链表的基础节点会指向起始节点和尾巴节点。

通过这个flush链表，就可以记录下来哪些缓存页是脏页了。

1.2.5 lru链表

（1）如果Buffer Pool中的缓存页不够了怎么办？

通过前面的学习我们知道，随着你不停的把磁盘上的数据页加载到空闲的缓存页里去，free链表中不停的移除空闲缓存页，free链表中的空闲缓存页就会越来越少，迟早有那么一瞬间，你会发现free链表中已经没有空闲缓存页了。

这个时候，我们就需要淘汰掉一些缓存页，顾名思义，你必须把一个缓存页里被修改过的数据，给它刷到磁盘上的数据页里去，然后这个缓存页就可以清空了，让它变成一个空闲的缓存页，加入到free链表中。

（2）要淘汰哪些缓存页？

我们应该淘汰那些缓存命中率比较低的缓存页，即淘汰哪些很少被访问的缓存页。

那么我们怎么知道哪些缓存页经常被访问，哪些缓存页很少被访问？此时就要引入一个新的LRU链表，这个所谓的LRU就是Least Recently Used，最近最少使用的意思。

通过这个LRU链表，我们可以知道哪些缓存页是最近最少被使用的，那么当你缓存页需要腾出来一个刷入磁盘的时候，不就可以选择那个LRU链表中最近最少使用的缓存页了么？

（3）LRU链表的工作原理？

首先，当我们从磁盘加载一个数据页到缓存页的时候，就把这个缓存页的描述数据块加入到LRU链表头部去，那么只要有数据的缓存页，它都会在LRU链表里了，而且最近被加载数据的缓存页，都会放到LRU链表的头部去。

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

那么这样的话，当你没有空闲缓存页的时候，就把LRU链表尾部的缓存页刷入磁盘，因为它一定是最近最少被访问的那个缓存页。

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

MySQL预读机制和全表描述一下子把大量未来可能不怎么访问的数据页加载到缓存页里去，然后LRU链表的前面全部是这些未来可能不怎么会被访问的缓存页，而之前一直被频繁访问的缓存页被挤到LRU链表的尾部，如果此时此刻，需要把一些缓存页刷入磁盘，腾出空间来加载新的数据页，那么此时就只能把LRU链表尾部那些之前一直频繁被访问的缓存页给刷入磁盘了。

预读机制： 所谓的预读机制，说的就是当你从磁盘上加载一个数据页的时候，它可能会连带着把这个数据页相邻的其它数据页也加载到缓存里去，放到LRU链表头部。这样会导致，实际上只有一个缓存页是被访问了，另外几个是通过预读机制加载到链表头部，其实并没有人访问。此时链表尾部的那几个缓存页之前是一直被人访问的，只不过在这一瞬间，被加载进来的缓存页给占据了LRU链表前面的位置。如果此时刚好没有空闲缓存页了，那LRU链表那几个之前被频繁访问的缓存页就会被删除，这是不合理的。

全表扫描： 所谓的全表扫描，意思就是类似如下的SQL语句：select * from users; 此时它没有加任何一个where条件，会导致它直接一下子把这个表里所有的数据页，都从磁盘加载到Buffer Pool中的缓存页里去。此时可能LRU链表中排在前面的一大串缓存页都是全表扫描加载进来的缓存页可能这些缓存页后续几乎不会用到。此时LRU链表的尾部，可能全部都是之前一直被频繁访问的那些缓存页，然后你要淘汰掉一些缓存页腾出空间的时候，就会把LRU链表尾部一直被频繁访问的缓存页给淘汰掉了，而留下了之前全表扫描加载进来的大量的不经常访问的缓存页，这是不合理的。

（5）哪些情况下会触发MySQL的预读机制？

有一个参数是innodb_read_ahead_threshold，它的默认值是56，意思就是如果顺序的访问了一个数据区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻数据区中的所有数据页都加载到缓存里去。
如果 Buffer Pool 里缓存了一个数据区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个数据区里的其它的数据页都加载到缓存里去，这个机制通过参数innodb_random_read_ahead来控制，默认是OFF，也就是这个机制是关闭的。

（6）为什么MySQL要设计预读这个机制呢？为什么要把一些相邻的数据页也加载到缓存里去呢？这是为了应对什么样的场景？

道理很简单，说白了还不是为了提升性能么。就比如在一个区里，你顺序读取了数据页01~ 数据页56，那么MySQL会判断，你可能接下来会继续顺序读取后面的数据页，那么此时它干脆提前把后续的一大堆数据页（比如数据页57 ~ 数据页72）都读取到 Buffer Pool 里去，那么后续你在读取数据页60的时候，是不是就可以直接从 Buffer Pool 里拿到数据了吗？

（7）基于冷热数据分离的思想设计LRU链表

所以为了解决普通LRU算法带来的问题，真正MySQL在设计LRU链表的时候，实际上采取的是冷热数据分离的思想，MySQL真正的LRU链表，会被拆分为两个部分，一部分是热数据，一部分是冷数据，这个冷热数据的比例是由innodb_old_blocks_pct参数控制的，默认是37，也就是说冷数据占比37%。这个时候，LRU链表实际上看起来是下面这样子的：
在这里插入图片描述
（8）运行期间，冷热两个区域是如何使用的？

首先数据页第一次被加载到缓存的时候，这个时候缓存页会被放到冷数据区域的LRU链表头部。
接着在1s之后（由innodb_old_blocks_time参数控制，默认值是1000毫秒），你访问这个缓存页，它就会被挪到热数据区域的链表头部。因为假设你加载了一个数据页到缓存里去，然后过了1s之后你还访问了这个缓存页，说明你后续可能会经常访问它，所以就把这个缓存页放到热数据区域的链表头部去。

（9）LRU链表的热数据区域是如何优化的？

我们知道，在热数据区域，如果我们访问了一个缓存页，是不是应该要把它立马移动到热数据区域的链表头部去？

但是你要知道，热数据区域里的缓存页可能是经常被访问的，所以这么频繁的进行移动是不是性能也不太好？也没这个必要。

所以说，LRU链表的热数据区域的访问规则被优化了一下，即只有在热数据区域的后3/4部分的缓存页被访问了，才会把它移动到热数据区域链表头部，如果是热数据区域的前面1/4的缓存页被访问了，它是不会移动到链表头部的。

（10）什么时候把LRU链表的冷数据区域的缓存页刷入磁盘呢？

定时把LRU尾部的部分缓存页刷入磁盘
首先你第一个时机，并不是在缓存页满的时候，才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘，而是有一个后台线程，它会运行一个定时任务，这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页，刷入磁盘里去，清空这几个缓存页，把它加入回free链表中去。
把flush链表中一些缓存页定时刷入磁盘
在LRU链表的热数据区域里的很多缓存页可能被频繁的修改，难道它们永远都不刷入磁盘中的吗？
所以这个后台线程同时也会在MySQL不怎么繁忙的时候，找个时间把flush链表中的缓存页都刷入磁盘中，这样被你修改过的数据，迟早都会刷入磁盘的。
只要 flush链表中的一波缓存页被刷入了磁盘，那么这些缓存页也会从flush链表和lru链表中移除，然后加入free链表中。
实在没有空闲缓存页的时候，就从LRU链表的冷数据区域的尾部找到一个缓存页刷入磁盘
在所有free链表都被使用，然后flush链表中有一大堆被修改过的缓存页，lru链表中有一大堆的缓存页，实在没有空闲缓存页的时候，就从LRU链表的冷数据区域的尾部找到一个缓存页，它一定是最不经常使用的缓存页，然后把它刷入磁盘和清空，然后把数据页加入到这个腾出来的空闲缓存页里去。

1.2.6 redo log

（1）redo log是干什么的？

现在我们都知道，在更新完buffer pool中的缓存页之后，必须要写一条redo log，这样才能记录下来我们对数据库做的修改。redo log可以保证我们事务提交之后，如果事务中的增删改SQL语句更新的缓存页还没刷到磁盘上去，此时MySQL宕机了，那么MySQL重启之后，就可以把redo log重做一遍，恢复出来事务当时更新的缓存页，然后再把缓存页刷到磁盘就可以了。

redo log本质是保证事务提交之后，修改的数据绝对不会丢失的。

（2）redo log 日志文件长什么样？里面包含什么东西？

redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值，具体修改的值是什么，它里面需要记录的就是表空间号 + 数据页号 + 偏移量 + 修改几个字节的值 + 具体的值。

一条redo log看起来大致的结构如下所示：

日志类型，表空间ID，数据页号，数据页中的偏移量，修改数据长度，具体修改的数据

日志类型： MySQL根据你修改了数据页里的几个字节的值，redo log就划分为了不同的类型，MLOG_1BYTE类型的日志指的就是修改了1个字节的值，MLOG_2BYTE类型的日志指的就是修改了2个字节的值，以此类推，还有修改了4个字节的值的日志类型，修改了8个字节的值的日志类型。

当然，如果你要是一下子修改了一大串的值，类型就是MLOG_WRITE_STRING，就是代表你一下子在那个数据页的某个偏移量的位置插入或者修改了一大串的值。

当你日志类型是MLOG_WRITE_STRING的时候，才会有日志结构中的修改数据长度，否则不需要。

（3）redo log 是一条一条的直接就往磁盘文件里写入吗？

可能有些人会认为就是如此简单粗暴的往磁盘文件里写，但其实并没有那么简单！

其实MySLQ内有另外一个数据结构，叫做redo log block，大概你可以理解为，平时我们的数据不是存放在数据页了么，用一页一页的数据页来存放数据。那么对于redo log页并不是单行单行的写入日志文件的，它是用一个redo log block来存放多个单行日志的。

一个redo log block是512个字节，这个redo log block的512个字节分为了3个部分，一个是12字节的header块头，一个是496字节的body块体，一个是4个字节的trailer块尾，如下图所示：
在这里插入图片描述
在这里面，12字节的header头又分为了4个部分。

包括4个字节的block no，就是块唯一编号；
2个字节的data length，就是block里写入了多少字节数据；
2个字节的first record group。这个是说每个事务都会有多个redo log，是一个redo log group，即一组redo log。那么在这个block里的第一组redo log的偏移量，就是这2个字节存储的。
4个字节的checkpoint on；

其实对于我们redo log而言，它确实是不停的追加写入到redo log磁盘文件里去的，但是其实每一个redo log都是写入到文件里的一个redo log block里去的，一个block最多放496字节的redo log 日志。

那么有人会有疑问了，到底一个一个redo log block在日志文件里是怎么存在的？那么一条一条的redo log又是如何写入日志文件里的redo log block里去的呢？估计很多人都很奇怪这个问题。

其实我们要写一个redo log，是先在内存里把这个redo log给弄到一个redo log block数据结构里去，然后等内存里的一个redo log block的512个字节都满了，再一次性把这个redo log block写入磁盘文件。redo log磁盘文件里面放的就是一个一个的redo log block。

（4）redo log buffer

这个redo log buffer其实就是MySQL在启动的时候，就跟操作系统申请的一块连续内存空间，大概可以认为相当于是buffer pool吧。那个buffer pool是申请之后划分了N多个空的缓存页和一些链表结构，让你把磁盘上的数据页加载到内存里来的。redo log buffer也是类似的，它是申请出来的一片连续内存，然后里面划分为了N多个空的redo log block。
在这里插入图片描述
可以通过设置MySQL的innodb_log_buffer_size来指定这个redo log buffer的大小，默认的值就是16MB，其实已经够大了，毕竟一个redo log block才512个字节而已，每一条redo log其实也就是几个字节到几十个字节罢了。

这里我们看到了redo log buffer的结构，就很清晰了的知道，当你要写入一条redo log的时候，就会先从第一个redo log block开启写入，如下图：
在这里插入图片描述
写满了一个redo log block，就会继续写下一个redo log block，以此类推，直到所有的redo log block都写满了。

那么此时肯定有人会问了，万一要是redo log buffer里所有的redo log block都写满了呢？

那此时必然会强制把redo log block刷入到磁盘中去的！在磁盘文件里不停的追加一个又一个的redo log block，如下图：
在这里插入图片描述
另外还要给大家讲一点的是，其实在我们平时执行一个事务的过程中，每个事务会有多个增删改操作，那么就会有多个redo log，这多个redo log就是一组redo log，其实每次一组redo log都是先在别的地方暂存，然后都执行完了，再把一组redo log给写入到redo log buffer的block里去的。

如果一组redo log实在是太多了，那么就可能会存放在两个redo log block中，反之，如果说一个redo log group比较小，那么也可能多个redo log group是在一个redo log block里去。

（5）redo log buffer里的redo log block是哪些时候会刷入到磁盘文件里去的？

如果写入redo log buffer的日志已经占据了redo log buffer总容量的一半了，也就是超过了8MB的redo log在缓冲里了，此时就会把它们刷入到磁盘文件里去。
一个事务提交的时候，必须把它的那些redo log所在的redo log block都刷入到磁盘文件里去。（事务提交的时候要不要刷redo log到磁盘是有个刷盘策略的，上面有讲过了）
后台线程定时刷新，有一个后台线程每隔1秒就会把redo log buffer里的redo log block刷到磁盘文件里去。
MySQL关闭的时候，redo log block都会刷入到磁盘里去。

（6）redo log磁盘文件

默认情况下，redo log都会写入一个目录的文件里，这个目录可以通过show variables like 'datadir'来查看，可以通过innodb_log_group_home_dir参数来设置这个目录的。

然后redo log是有多个的，写满了一个就会写下一个redo log，通过innodb_log_file_size可以指定每个redo log文件的大小，默认是48MB，通过innodb_log_files_in_group可以指定日志文件的数量，默认就2个。

所以默认情况下，目录里就两个日志文件，分别为ib_logfile0和ib_logfile1，每个48MB，最多就这2个日志文件，就是先写第一个，写满了再写第二个。那么如果第二个也写满了，就继续写第一个，覆盖第一个日志文件里原来得redo log就可以了。（这个覆盖写之前要先触发一下缓存页刷盘，否则会导致覆盖的那些redo log对应的数据丢失）

1.2.7 undo log

（1）什么是undo log？

假设现在我们一个事务里要执行一些增删改的操作，那么必然要先把对应的数据页从磁盘加载出来放到buffer pool的缓存页里，然后在缓存页里执行一通增删改，同时记录redo log日志。

但是现在有个问题，万一要是一个事务里的一通增删改操作执行到了一半，结果就回滚事务了，比如一个事务里有4个增删改操作，结果目前为止已经执行了2个增删改SQL了，已经更新了一些buffer pool里的数据了，但是还有2个增删改SQL的逻辑还没执行，此时事务要回滚了怎么办？

所以，这个时候就需要引入undo log回滚日志了，这个回滚日志，它记录的东西其实非常简单，比如，你要是在缓存页里执行了一个insert语句，那么此时你在undo log日志里，对这个操作记录的回滚日志就必须是有一个主键和一个对应的delete操作，要能让你把这次insert操作给回退了。那么比如说你要是执行的是delete语句，那么起码你要把你删除的那条数据记录下来，如果要回滚，就应该执行一个insert操作把那条数据插回去。如果你要是执行的是update语句，那么起码你要把你更新之前的那个值记录下来，回滚的时候重新update一下，把你更新前的旧值给它更新回去。

说白了，就是你执行事务的时候，里面有很多insert、update和delete语句都在更新缓存页里的数据，但是万一事务回滚了，你必须有每条SQL语句对应的undo log回滚日志，根据回滚日志去恢复缓存页里被更新的数据。

（2）undo log 长什么样？

insert语句的undo log的类型是TRX_UNDO_INSERT_REC，这个undo log里包含了以下一些东西

这条日志的开始位置：一条undo log必须得有自己的一个开始位置
主键的各列长度和值：意思就是你插入的这条数据的主键的每个列，它的长度是多少，具体的值是多少。
表id：记录下来是在哪个表里插入的数据
undo log日志编号：每个undo log日志都是有自己的编号的，编号从0开始，然后依次递增。
undo log日志类型：insert语句的日志类型就是TRX_UNDO_INSERT_REC
这条日志的结束位置：告诉你undo log日志结束的位置

1.2.8 生产经验

（1）MySQL为什么要使用多个Buffer Pool？

假设MySQL同时接收到了多个请求，它自然会用多个线程来处理这多个请求，每个线程会处理一个请求，然后这多个线程是不是应该会同时去访问 Buffer Pool呢？就是同时去操作里面的缓存页，同时操作一个free链表、flush链表、lru链表。

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

其实就算是只有一个Buffer Pool，即使多个线程会加锁串行着排队执行，其实性能也差不到哪里去。因为大部分情况下，每个线程都是查询或者更新缓存里的数据，这个操作是发生在内存里的，基本都是微妙级别的，很快很快，包括更新free、flush、lru这些链表，因为它们都是基于链表进行一些指针操作，性能也是极高的。

但是不管怎么样，毕竟也是每个线程加锁然后排队一个一个操作，这也不是特别的好，特别是有的时候你的线程拿到锁之后，它可能要从磁盘里读取数据页加载到缓存页里去，这还发生了一次磁盘IO呢？ 所以它要是进行磁盘IO的话，也许就会耗时多一些，那么后面排队等它的线程自然就会多等一会儿了！

（2）如何通过多个Buffer Pool来优化数据库的并发能力？

我们可以为MySQL设置多个Buffer Pool 来优化它的并发能力。

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

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

我们给Buffer Pool设置了8GB总内存，然后设置了它应该有4个Buffer Pool，此时就是说，每个Buffer Pool的大小就是2GB。这个时候，MySQL在运行的时候就会有4个Buffer Pool了！每个Buffer Pool负责管理一部分的缓存页和描述数据块，有自己独立的free、flush、lru等链表。

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

这个在实际生产环境中，设置多个Buffer Pool来优化并发访问能力，是MySQL一个很重要的优化技巧。

（3）什么是chunk机制？

为了解决动态调整Buffer Pool，MySQL设计了一个chunk机制，也就是说Buffer Pool是由很多chunk组成的，它的大小是innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB。

所以实际上我们可以来做一个假设，比如现在我们给Buffer Pool设置一个总大小是8GB，然后有4个Buffer Pool，就是2GB，此时每个Buffer Pool是由一系列的128MB的chunk组成的，也就是说每个Buffer Pool会有16个chunk。然后每个Buffer Pool里的每个chunk里就是一系列的描述数据块和缓存页，每个Buffer Pool里的多个chunk共享一套free、flush、lru这些链表，此时的话看起来可能大致如下图所示：
在这里插入图片描述

（4）数据库运行期间，通过chunk机制来动态调整Buffer Pool？

有了chunk机制，我们就可以支持动态调整Buffer Pool大小了。比如我们Buffer Pool现在总大小是8GB，现在要动态调整加到16GB，那么此时只要申请一系列的128MB大小的chunk就可以了，只要每个chunk是连续的128MB内存就行了。然后把这些申请到的chunk内存分配给Buffer Pool就行了。

（5）生产环境中，应该给Buffer Pool设置多少内存？

首先考虑第一个问题，我们现在数据库部署在一台机器上，这台机器可能有个8G、16G、32G、64G、128G的内存大小，那么此时Buffer Pool应该设置多大呢？

有的人可能会想，假设我有32G内存，那么给Buffer Pool设置个30G得了，这样的话，MySQL大量的CRUD操作都是基于内存来执行的，性能绝对很高！

但是这么想就大错特错了，你要知道，虽然你的机器有32GB的内存，但是你的操作系统内核就要用掉起码几个GB的内存！

然后你的机器上可能还有别的东西在运行，是不是也要内存？然后你的数据库里除了Buffer Pool是不是还有别的内存数据结构，是不是也要内存？所以上面的那种想法是绝对不可取的。

如果你胡乱设置一个特别大的内存给Buffer Pool，会导致你的MySQL启动失败，它启动的时候就发现操作系统的内存根本不够用了！

所以通常来说，我们建议一个比较合理的、健康的比例，是给Buffer Pool设置你的机器内存的50% ~ 60%左右。比如你有32GB的机器，那么给Buffer Pool设置个20GB的内存，剩下的留给OS和其它人来用，这样比较合理一些。

（6）Buffer Pool总大小 = （chunk大小 * buffer pool数量）的2倍数

接着确定了Buffer Pool的总大小之后，就得考虑一下设置多少个Buffer Pool，比较chunk的大小了。

此时要记住，有一个很关键的公式就是：buffer pool总大小 = （chunk大小 * buffer pool数量）的2倍数。

比如默认的chunk大小是128MB，那么此时如果你的机器的内存是32GB，你打算给buffer pool总大小在20GB左右，那么你得算一下，此时你的buffer pool的数量应该是多少个呢?

假设你的buffer pool的数量是16个，这是没问题的，那么此时 chunk大小 * buffer pool的数量 = 16 * 128MB = 2048MB，然后buffer pool总大小如果是20GB，此时buffer pool总大小就是2048MB的10倍，这就符合规则了。

当然，此时你可以设置多一些buffer pool数量，比如设置32个buffer pool，那么此时buffer pool总大小（20GB）就是（chunk大小128MB * 32个buffer pool）的5倍，也是可以的。

那么此时你的buffer pool大小就是20GB，然后buffer pool数量是32个，每个buffer pool的大小是640MB，然后每个buffer pool包含5个128MB的chunk，算下来就是这么一个结果。

（7）SHOW ENGINE INNODB STATUS

当你的数据库启动之后，你随时可以通过上述命令，去查看当前innodb里的一些具体情况，执行SHOW ENGINE INNODB STATUS就可以了。

二、MySQL物理数据模型

2.1 数据页

（1）MySQL为什么要引入数据页这个概念？

我们知道，当我们更新数据的时候，并不是直接去更像磁盘文件的，而是把磁盘上的一些数据加载到内存里来，然后对内存里的数据进行更新。但是这里就有一个问题了，难道我们每次都是把磁盘里的一条数据加载到内存里去进行更新，然后下次要更新别的数据的时候，再从磁盘里加载另外一条数据到内存里去吗？很明显，这样每次都是一条数据一条数据的加载到内存里去更新的方式效率很低。

所以InnoDB存储引擎在这里引入了一个数据页的概念，也就是把数据组织成一页一页的概念，每一页有16kb，每一页里面有很多条数据，然后每次加载磁盘的数据到内存的时候，是至少加载一页数据进去，甚至是多页数据进去。脏数据在刷回磁盘的时候，也是至少一个数据页刷回去。

（2）用于存放磁盘上的多行数据的数据页到底长什么样子？

每个数据页，实际上默认是有16KB大小的，那么这16KB的大小就只是存放大量的数据行吗？

明显不是的，其实一个数据页拆分成了很多个部分，大体上来说包含了文件头、数据页头、最小记录和最大记录、多个数据行、空闲区域、数据页目录、文件尾部。
在这里插入图片描述
数据插入数据页的过程：

刚开始大家都知道，刚开始一个数据页可能是空的，没有一行数据，此时这个数据页实际上是没有数据行那个区域的。
从磁盘加载一个空的数据页到 buffer pool 缓存里，此时在缓存里就叫做缓存页了，缓存页跟数据页是一一对应的。
在缓存页里插入一条数据，实际上就是在数据行那个区域里插入一行数据，然后空闲区域的空间会减少一些。
接着你就可以不停的插入数据到这个缓存页里去，直到它的空闲区域都耗尽了，就是这个页满了，此时数据行区域内可能有很多行数据，如下图所示，空闲区域就没了。

（3）一行数据在磁盘上是如何存储的？

接下里我们可以思考一下，对数据页中的每一行数据，它在磁盘上是怎么存储的？
其实这里涉及到一个概念，就是行格式。我们可以对一个表指定它的行存储的格式是什么样的，比如我们这里用一个COMPACT格式。

// 建表的时候指定行存储格式
CREATE TABLE table_name (
	......
) ROW_FORMAT = COMPACT

// 建完表后，修改行存储的格式
ALTER TABLE table_name ROW_FORMAT = COMPACT

在COMPACT这种格式下，每一行数据它实际存储的时候，大概格式类似下面这样：

变长字段的长度列表  null值列表  数据头  column01的值  column02的值  column03的值

举个例子，假设你有一张表，它的建表语句如下所示：

CREATE TABLE customer (
	name VARCHAR(10) NOT NULL,
	address VARCHAR(20),
	gender CHAR(1),
	job VARCHAR(30),
	school VARCHAR(50)
) ROW_FORMAT = COMPACT;

其中有一行数据是 “Jack NULL m NULL xx_school”，那么它真实存储大致如下所示：

0x09 0x04 00000101 000000000000000000001000000000000001101 jack m xx_school

刚开始先是它的变长字段的长度（逆序存放变长字段长度），用十六进制来存储，然后是NULL值列表（二进制bit位存储），指出了谁是NULL，接着是40个bit位的数据头，然后是真实的数据值，就放在后面。

但是等等，大家觉得真正在磁盘上存储的时候，我们那些字符串就是直接这么存储在磁盘上的吗？显然不是的！实际上字符串这些东西都是根据我们数据库指定的字符集编码，进行编码之后再存储的，所以大致看起来一行数据是如下所示的：

0x09 0x04 00000101 000000000000000000001000000000000001101 616161 636320 6262626262

但是，其实在实际存储一行数据的时候，会在它的真实数据部分，加入一些隐藏字段。

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

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

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

所以如果加上这几个隐藏字段之后，实际一行数据可能看起来如下所示：

0x09 0x04 00000101 000000000000000000001000000000000001101 00000000094C（DB_ROW_ID） 00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

（4）行溢出是什么？

实际上我们每一行数据都是放在一个数据页里的，这个数据页默认的大小是16KB，一旦一行数据的大小超过了数据页的大小了，那么就会出现行溢出的问题。

比如，你有一个表的字段类型是 varchar(65532) ，意思就是最大可以包含65532个字符，那也就是65532个字节，这就远大于16KB的大小了，也就是说这一行数据的这个字段都远超一个数据页的大小了！

这个时候，实际上会在那一页里存储你这行数据，然后在那个字段中，仅仅包含它一部分数据，同时包含一个20个字节的指针，指向了其它的一些数据页，那些数据页用链表串联起来，存放这个varchar(65532)超大字段里的数据。

2.2 表空间以及划分多个数据页的数据区，又是什么概念？

（1）什么是表空间、数据区？

简单来说，就是我们平时创建的那些表，其实都是有一个表空间的概念，在磁盘上都会对应着 “表名.ibd” 这样的一个磁盘数据文件。所以其实在物理层面，表空间就是对应一些磁盘上的数据文件。

有的表空间，比如系统表空间可能对应的是多个磁盘文件，有的我们自己创建的表对应的表空间可能就是对应一个“表名.ibd”数据文件。

然后在表空间的磁盘文件里，其实会有很多很多的数据页，因为大家都知道一个数据页不过就是16KB而已，总不可能一个数据页就是一个磁盘文件吧。

但是有一个问题，就是一个表空间里包含的数据页实在是太多了，不便于管理，所以在表空间里又引入了一个数据区的概念，英文就是extent。

表空间里面有很多组数据区，一组有256个数据区，每个数据区有着连续的64个数据页，每个数据页是16kb，所以一个数据区是1MB。
在这里插入图片描述

2.3 磁盘随机读写与顺序读写是什么？

剖析一下MySQL在实际工作时候的两种数据读写机制，一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写，一种是对redo log、undo log、binlog这种日志进行的磁盘顺序读写。

（1）随机读写

简单来说，MySQL在工作的时候，尤其是执行增删改操作的时候，肯定会从表空间的磁盘文件里读取数据页出来，这时候需要在一个随机的位置读取一个数据页到缓存，这就是磁盘随机读。

对于磁盘随机读来说，主要关注的性能指标是IOPS和响应延迟。

IOPS： 就是说底层的存储系统每秒可以执行多少次磁盘读写操作，比如你底层磁盘支持每秒执行1000个磁盘随机读写操作和每秒执行200个磁盘随机读写操作，对你的数据库的性能影响其实是非常大的。IOPS越高，你的数据库的并发能力越高。

响应延迟： 假设你的底层磁盘支持你每秒执行200个随机读写操作但是每个操作是耗费10ms完成呢，还是耗费1ms完成呢，这个其实也是有很大影响的。

随机读写性能是很差的，所以其实一般对于核心业务的数据库的生产环境机器规划，我们都是推荐用SSD固态硬盘的，而不是机械硬盘，因为SSD固态硬盘的随机读写并发能力和响应延迟要比机械硬盘好得多，可以大幅度提升数据库的QPS和性能。

（2）顺序读写

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

磁盘顺序写的性能其实是很高的，某种程度上来说，几乎可以跟内存随机读写的性能差不多，尤其是在数据库里其实也用了OS cache机制，就是redo log顺序写入磁盘之前，先是进入OS cache，就是操作系统管理的内存缓存里。

所以对于这个写磁盘日志文件而言，最核心关注的是磁盘每秒读写多少数据量和吞吐量指标 ，就是说每秒可以写入磁盘100MB数据和每秒可以写入磁盘200MB数据，对数据库的并发能力影响也是极大的。
因为数据库的每一次更新SQL语句，都必然涉及到多个磁盘随机读取数据页的操作，也会涉及到一条redo log日志文件顺序写的操作。所以磁盘读写的IOPS指标，就是每秒可以执行多少个随机读写操作，以及每秒可以读写磁盘的数据量的吞吐量指标，就是每秒可以写入多少redo log日志，整体决定了数据库的并发能力和性能。

包括你磁盘日志文件的顺序读写的响应延迟，也决定了数据库的性能，因为你写redo log日志文件越快，那么你的SQL语句性能就越高。

2.4 生产经验

这边给大家分析一个真实的大家都经常会碰到的数据库生产故障，就是数据库无法连接的问题。大家会看到的异常消息往往是 “ERROR 1040(HY000) ：Too manay connections”，这个时候就是说数据库的连接池里已经有太多的连接了，不能再跟你建立新的连接了。

曾经在我们的一个生产案例中，数据库部署在64G的大内存物理机上，机器配置各方面都很高，然后连接这台物理机的Java系统部署在2台机器上，Java系统设置的连接池的最大大小是200，也就是说每台机器上部署的Java系统，最多跟MySQL数据库建立200个连接，一共最多建立400个连接。

但是这个时候如果MySQL报异常说Too many connections，就说明目前MySQL甚至都无法建立400个网络连接？这也太少了吧！毕竟是高配置的数据库机器！

于是我们检查了一下MySQL的配置文件my.cnf，里面有一个关键的参数是max_connections，就是MySQL能建立的最大连接数，设置的是800.

那奇怪了，明明设置了MySQL最多可以建立800个连接，为什么居然两台机器要建立400个连接都不行呢？

这是时候我们可以用命令行或者一些管理工具登录到MySQL去，执行下面的命令看一下：

show variables like 'max_connections'

此时，我们看到的当前MySQL仅仅只是建立了214个连接而已！

所以我们此时就可以想到，是不是MySQL根本不管我们设置的那个max_connections，就是直接强行把最大连接数设置为214？于是我们可以去检查一下MySQL的启动日志，可以看到如下字样：

Could not increase number of max_open_files to more than mysqld(request:65535)
Changed limits:max_connections:214(requested:2000)
Changed limits:table_open_cache:400(requested:4096)

所以说，看看日志就很清楚了，MySQL发现自己无法设置max_connections为我们期望的800，只能强行限制为214了！

这是为什么呢？简单来说，就是因为底层的linux操作系统把进程可以打开的文件句柄数限制为1024了，导致MySQL最大连接数是214。

所以说，往往你在生产环境部署了一个系统，比如数据库系统、消息中间件系统、存储系统、缓存系统之后，都需要调整一下linux的一些内核参数，这个文件句柄的数量是一定要调整的，通常都得设置为65535。

执行命令如下：

ulimit -HSn 65535

然后用如下命令检查最大文件句柄数是否被修改了

cat /etc/security/limits.conf
cat /etc/rc.local

cyd_0619

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
一篇文章带你读懂MySQL的InnoDB存储引擎Buffer Pool原理

一个不变的原则：网络连接必须让线程来处理一条SQL语句的执行 MySQL内部的工作线程从一个网络连接中取出来一个SQL语句，然后交给SQL接口去执行SQL语句，SQL接口将SQL语句交给查询解析器去解析SQL，解析完SQL，查询解析器再将SQL语句交给查询优化器，去获取一个最优的执行计划，最后交由执行器，执行器根据这个执行计划去调用存储引擎的接口，完成SQL语句的执行。一、InnoDB存储引擎1.1 基于InnoDB存储引擎完成一条更新语句的执行更新语句：update users set na
复制链接

扫一扫