MySQL中SQL语句是如何执行的MySQL性能优化（二）

MySQL 性能优化（一）SQL 语句在 MySQL 中是如何执行的

MySQL 性能优化（二）InnoDB 日志文件

文章目录

1.审查缓冲池

下面我们来看看更新SQL的流程图。不管过程多么复杂，有一件事是肯定的，最终的数据会落到磁盘文件上。但是我们在对数据库进行增删改查的时候，是不可能直接更新磁盘上的数据的，因为如果对磁盘进行随机读写操作，速度是相当慢的，而且随机读写对任何大磁盘文件的操作。 ，也许是几百毫秒。如果这样做，您的数据库可能每秒只能处理数百个请求！

其实我们在对数据库进行增删改查操作的时候，其实主要是针对内存中Buffer Pool中的数据进行的，也就是说你其实主要是增加了内存中的数据结构数据库。删除。同时配合后续的redo log、刷盘等机制和操作。所以Buffer Pool是数据库的一个内存组件，它把真实的数据缓存在磁盘上，然后我们Java系统对数据库进行的增删改查操作，其实主要是对这块内存中缓存的数据进行的数据结构。

2.配置缓冲池大小

因为Buffer Pool的本质其实是数据库的一个内存组件，可以理解为它是一个内存数据结构，所以这个内存数据结构必须有一定的大小，不能无限大。这个 Buffer Pool 默认是 128MB，还是有点小。我们可以在实际生产环境中完全调整Buffer Pool

比如我们的数据库是16核32G的机器，那么可以给Buffer Pool分配2GB的内存，可以使用如下配置。

nnodb_buffer_pool_size = 2147483648

我们可以先查看我们的innodb_buffer_pool_size的大小，执行如下SQL，可以发现是134217728

134217728 /1024 / 1024 =128M

show global variables like 'innodb_buffer_pool_size';

设置大小

SET GLOBAL innodb_buffer_pool_size= 32423423

3.缓冲池：数据结构3.1.磁盘数据结构：数据页

现在我们知道数据库中肯定有一块内存区域就是Buffer Pool，那么我们的数据是如何放到Buffer Pool中的呢？

我们都知道数据库的核心数据模型是表+字段+行的概念，也就是说我们都知道数据库里有表，一张表有很多字段，然后一张表有很多行数据。行数据有自己的字段值。那你觉得我们的数据是一行一行的放到Buffer Pool中吗？

显然不是这样。事实上，MySQL 从数据中抽象出数据页的概念。它将很多行数据放在一个数据页中，这意味着我们的磁盘文件中会有很多数据。数据页，每页数据中放置多行数据，如下图所示。

所以实际上，假设我们要更新一行数据。这时数据库会找到该行数据所在的数据页，然后直接从磁盘文件中将该行数据所在的数据页加载到Buffer Pool中。也就是说，Buffer Pool是一页一页的存储数据页，如下图所示。

3.2.缓冲池数据结构：数据页（缓存页）

实际上，默认情况下，存储在磁盘中的数据页大小为16KB，也就是说，一页数据包含16KB的内容。

Buffer Pool中存储的数据页通常被称为缓存页，因为Buffer Pool毕竟是一个缓冲池，里面的数据是从磁盘缓存到内存中的。在Buffer Pool中，默认情况下缓存页的大小和磁盘上数据页的大小是一一对应的，都是16KB。

3.3.缓存页面对应的描述信息

对于每一个缓存页面mysql数据库设计报告，他其实都会有一个描述信息，大致可以认为是用来描述缓存页面的。例如，它包括以下内容：数据页所属的表空间，数据页的编号，缓存页在缓冲池中的地址，以及其他杂项。每个缓存页对应一个描述，描述本身也是一条数据。在Buffer Pool中，每个缓存页的描述数据放在最前面，然后每个缓存页放在后面。

Buffer Pool中的描述数据大致相当于缓存页大小的5%左右，也就是每个描述数据大小约为800字节，再假设你设置的缓冲池大小为128MB，在事实上 Buffer 池的实际最终大小会超过一点，看起来可能超过 130 MB，因为它还存储了每个缓存页的描述数据。

4.缓冲池：初始化

数据库一启动，会根据你设置的Buffer Pool大小稍大一些，去操作系统申请一块内存区域作为Buffer Pool的内存区域。然后当内存区申请完成后，数据库会按照默认的缓存页大小16KB和对应的描述数据大小约800字节，将缓存页一一划分在Buffer Pool中。对应的描述数据。那么当数据库划分Buffer Pool的时候，就和我们之前看到的图片一样，如下图

只是此时，Buffer Pool中的缓存页是空的，里面什么都没有。数据库运行后，当我们要对数据进行增删改查等操作时，从磁盘文件中读取数据对应的页面，放入Buffer Pool中的缓存页面。

5.缓冲池：空闲链表

当你的数据库在运行时，你肯定会不断地进行增删改查等操作。这时候就需要不断地从磁盘上一一读取数据页，放入Buffer Pool中对应的缓存中。进入页面，缓存数据，以后可以对内存中的数据进行增删改查。

但是这时候，当从磁盘中读取数据页并放入Buffer Pool中的缓存页时，必然会涉及到一个问题，即哪些缓存页是空闲的？因为默认情况下，磁盘上的数据页和缓存页是一一对应的，都是16KB，一个数据页对应一个缓存页。所以我们必须知道Buffer Pool中有哪些缓存页是空闲的。

空闲链表，它是一个双向链表数据结构，在这个空闲链表中，每个节点都是描述数据块的空闲缓存页的地址，也就是说，只要你的一个缓存页是空闲的，那么他的描述数据块就会被放入这个空闲链表中。

数据库启动的时候，可能所有的缓存页都是空闲的，因为此时可能是一个没有数据的空数据库，所以此时所有缓存页的描述数据块都会放到这个空闲的链表。

一个空闲链表包含每个缓存页的描述数据块。只要缓存页是空闲的，它们对应的描述数据块就会被加入到空闲链表中，每个节点都会双向链接自己的前后节点。形成一个双向链表。

空闲链表本身实际上是由缓冲池中的描述数据块组成的。可以认为每个描述数据块中有两个指针，一个是free_pre，一个是free_next，分别指向自己的空闲链表的A节点。

对于空闲链表，只有一个基本节点不属于缓冲池。是一个40字节的节点，里面存储了空闲链表头节点的地址，尾节点的地址，空闲链表中当前有多少节点。

那么磁盘数据页是如何读取到缓冲池数据页的呢？

首先，我们需要从空闲链表中获取一个描述数据块，然后我们可以相应地获取该描述数据块对应的空闲缓存页，如下图：

然后我们可以将磁盘上的数据页读入对应的缓存页，同时将一些相关的描述数据写入缓存页的描述数据块，比如数据页所属的表空间类信息，最后从空闲链表中取出描述数据块，如下图：

我们在执行CRUD的时候，首先要检查数据页是否被缓存。如果没有缓存，按照上面的逻辑，从空闲链表中找到一个空闲的缓存页，从磁盘中读取。取数据页写入缓存页，写入描述数据，从空闲链表中取出描述数据块。但是如果数据页已经被缓存了，就直接使用了。

所以其实数据库也会有一个**哈希表数据结构，会以表空间号+数据页号为key，然后以缓存页的地址为value。 **读取一个数据页到缓存后，会写入一个键值对到哈希表中，键是表空间号+数据页号，值是缓存页的地址，那么如果你下次再用这个数据页可以直接从哈希表中读取，已经放入缓存页。

6.缓冲池：刷新链表

flush链表的本质是利用缓存页的描述数据块中的两个指针，使修改后的缓存页的描述数据块形成一个双向链表。对于所有被修改的缓存页面，它们的描述数据块将被添加到刷新链表中。 flush的意思就是这些都是脏页，后面会刷到磁盘上。因此，flush链表的结构如下。如图，和自由链表差不多。

7.缓冲池：停用缓存页面

随着你不断地将磁盘上的数据页加载到空闲缓存页中，空闲链表中的空闲缓存页会越来越少吗？因为只要将一个数据页加载到一个空闲缓存页中，空闲链表就会减少一个空闲缓存页。迟早，空闲链表中不会有空闲缓存页。和redis一样，内存中存储的数据已经满了，如何消除数据？

retire cache page，顾名思义，就是要把一个cache page中修改过的数据flush到磁盘上的数据页，然后才能清空这个cache page，重新释放缓存页。

7.1.缓存命中率

假设有两个缓存页，一个缓存页的数据被频繁修改和查询。例如，在 100 个请求中，有 30 次是在查询和修改此缓存页中的数据。那么我们可以说，在这种情况下，缓存命中率非常高。

另一个缓存页的数据刚从磁盘加载到缓存页后修改查询一次，之后的100次请求都不是修改查询这个缓存页的数据，那么此时我们说缓存命中率有点低，因为大部分请求可能还需要去磁查询数据，而他们要操作的数据不在缓存中。

因此，命中率是我们做缓存页面驱逐时考虑的一个原则。

7.2.LRU 列表

LRU 最近最少使用，表示最近最少使用。通过这个LRU列表，我们可以知道哪些缓存页是最近最少使用的，那么当需要腾出一个缓存页刷新到磁盘时，就不能在LRU列表中选择最近最少使用的缓存页吗？ ?

假设当我们从磁盘加载一个数据页到缓存页时，我们把缓存页的描述数据块放到LRU链表的头部，那么只要有一个缓存页有数据，它将在 LRU 中，并且将最近加载数据的缓存页面放在 LRU 链表的头部。

只要我们从磁盘加载一个数据页到缓存页，把缓存页的描述数据块放到LRU链表的头部，那么只要有一个缓存页有数据，它将在 LRU 中，并且将最近加载数据的缓存页面放在 LRU 链表的头部。因此，在消除缓存页的时候，直接在LRU链表末尾找到一个缓存页，然后你把LRU链表末尾的缓存页刷到磁盘，然后再加载你需要的磁盘数据页到自由空间。缓存页面可以是

结束了！

7.3.预读机制/全扫描

所谓的预读机制是指当你从磁盘加载一个数据页时，它也可能将与该数据页相邻的其他数据页加载到缓存中！如下：

这时候如果把LRU链表末尾的缓存页flush到磁盘，那是绝对不合理的。最合理的做法是通过预读机制加载上图中LRU列表的第二个。缓存页被刷新到磁盘并清空，毕竟几乎没有人会访问它！

哪些条件会触发 MySQL 的预读机制？

innodb_read_ahead_threshold

一个参数是innodb_read_ahead_threshold，默认值为56，表示如果一个区域的多个数据页被顺序访问，并且访问的数据页数超过这个阈值，就会触发预读。将下一个相邻区段中的所有数据页加载到缓存中的机制。

innodb_random_read_ahead

如果一个区域的13个连续数据页缓存在Buffer Pool中，并且这些数据页被频繁访问，则直接触发预读机制，直接触发该区域的其他数据页。所有数据页都加载到缓存中。该机制由参数innodb_random_read_ahead控制，默认为OFF，即关闭此规则。

全表扫描

这是一个类似于以下的 SQL 语句：SELECT * FROM USERS。这时候他没有添加任何where条件，这会导致他直接将这个表中的所有数据页从磁盘加载到Buffer Pool中。 7.4.冷热分离LRU

上面提到的问题是由于所有缓存的页面都混合在一个LRU链表中造成的。 MySQL在设计LRU链表的时候，其实是采用了冷热数据分离的思路。

真正的LRU链表会分成两部分，一是热数据，二是冷数据。冷热数据的比例由innodb_old_blocks_pct参数控制，默认为37，即冷数据占比37%。首次加载数据的缓存页会一直移动到冷数据区的链表头部。

MySQL已经定了一个规则，他设计了一个innodb_old_blocks_time参数，默认值是1000，也就是1000毫秒。假设你将一个数据页加载到缓存中，然后在 1s 后访问缓存页，说明你以后很可能会频繁访问。时间限制是 1s，所以你只能在 1s 之后访问缓存。页面，他会给你缓存页面到热数据区的链表头部。

综上所述，我们知道在淘汰缓存的时候，必须先淘汰冷数据区很少访问的缓存页

7.5. 热数据 LRU 移动

接下来我们看一下LRU链表热数据区的一个性能优化点，即热数据区，如果你访问一个缓存页，你是不是应该马上把它移到热点数据区？到链表的头部？如果是这样，那么频繁的移动不是很好吗？

并发量大时，会因为需要加锁而产生锁竞争，每次移动效率都会明显下降。因此，MySQL 对这一点进行了优化。如果一个缓存页在热数据区，并且在热数据区的前1/4区域（注意是热数据区的1/4，而不是整个链接的1/4）list @>，那么在访问缓存页时，不需要将其移到热数据区的头部；如果缓存页在热数据的最后3/4区域，那么当缓存页面被访问，会被移动到热数据区的头部。

例如假设热数据区的链表中有100个缓存页，那么前25个缓存页即使被访问也不会移动到链表的头部。但是对于接下来的 75 个缓存页，只要被访问，就会被移动到链表的头部。这样他就可以尽可能的减少链表中节点的移动

7.6.冷数据LRU闪烁

首先，在第一次，不是缓存页满的时候，会选择LRU冷数据区末尾的几个缓存页刷到磁盘，但是有后台线程，会运行一个定时任务，这个定时任务会定时刷新LRU链表冷数据区末尾的一些缓存页到磁盘，清空这些缓存页，再添加回免费名单！所以实际上，当缓存的页面没有用完时，可能会清空一些缓存的页面。让我们看看下面的图标

8.多个缓冲池优化并发

MySQL同时接收到多个请求，他自然会使用多个线程来处理这些多个请求，每个线程会负责处理一个请求。

现在多个线程同时访问缓冲池。这时候他们都在访问内存中的一些共享数据结构，比如缓存页、各种链表等等，这时候是不是不可避免呢？想要锁定？是的，多线程并发访问一个Buffer Pool必须加锁，然后让一个线程先完成一系列操作，比如将数据页加载到缓存页，更新空闲链表，更新lru链表，然后释放锁，然后下一个线程执行一系列操作。

在大多数情况下，每个线程都会查询或更新缓存页面中的数据。这个操作发生在内存中，基本在微秒级，非常快，包括更新free、flush、Lru这些链表，因为都是基于链表进行一些指针操作，性能也是极高的。

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

一般来说，MySQL 的默认规则是，如果你分配少于 1GB 的内存给一个 Buffer Poolmysql数据库设计报告，它最多只会给你一个 BufferPool。

但是如果你的机器内存很大，那么你必须给Buffer Pool分配更大的内存，比如给他8G内存，那么你可以同时设置多个Buffer Pool，比如下面的MySQL服务器——侧面配置。

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

我们为缓冲池设置了8GB的总内存，然后设置他应该有4个缓冲池。此时，每个缓冲池的大小为 2GB。此时，MySQL 将有 4 个 Buffer Pools！每个 Buffer Pool 负责管理一部分缓存页面和描述数据块，并拥有自己独立的 free、flush、lru 等链表。

8.1.chunk机制支持动态调整缓冲池

缓冲池由许多块组成。它的大小由 innodb_buffer_pool_chunk_size 参数控制。默认值为 128MB。每个块是一系列描述数据块和缓存页。在这种情况下，缓冲池按照chunk被分成了一系列的小数据块，但是每个缓冲池共享一组free、flush、lru的链表

给大家解释一下这个chunk机制，并不是让你在数据库运行的时候动态调整缓冲池的大小。事实上，这不是重点。重点是你需要了解数据库缓冲池的真实数据结构。它由缓冲池组成，每个缓冲池由多个chunk组成，然后你只需要知道它可以支持运行时动态调整大小。

8.2.缓冲池总内存应该设置为多少？

建议设置一个合理健康的比例，也就是设置你机器内存的50%~60%左右用于缓冲池。比如你有一台32GB的机器，那么设置一个20GB的内存给buffer，剩下的留给使用OS等比较合理。假设你的机器有 128GB 内存，缓冲池可以设置为 80GB 左右，大概就是这样的规律。

8.3.应该设置多少个缓冲池？

确定了缓冲池的总大小后，还要考虑设置多少个缓冲池以及chunk的大小

此时，记住有一个很关键的公式：

缓冲池总大小 = 块大小 x 块数 x 缓冲池数