关于mysql:MySQL优化学习手札三

诚实说这部分相干的知识点早就曾经筹备好了，只是苦于不晓得该组织这些内容而已，昨晚想到了该如何组织这部分内容。

本系列的文章不加阐明，个别都是在 InnoDB 数据引擎探讨。

在开始看本篇文章之前，倡议先看:

• SQL 查问模型和查疑补漏
• LeetCode 刷题四部曲之 SQL 篇(一)
• MySQL 优化学习手札(一)
• MySQL 优化学习笔记手札(二)

buffer pool 缓存池 的引入

简介

咱们在那里曾经唠叨过一条 SQL 由客户端发送给 MySQL 的服务端会产生些什么了，对此没有理解的，倡议翻一下《MySQL 优化学习手札(一)》

到目前为止，从宏观上来看，SQL 被发送给 MySQL 服务端之后，MySQL 的存储引擎依据 SQL 从磁盘中提取进去对应的数据，但你晓得磁盘的速度绝对于的内存的速度是很慢的，即便是固态硬盘。如果每次提取数据都从磁盘中提取数据，那未免有点太慢了吧。对于 InnoDB 作为存储引擎的表, MySQL 的开发者设计了缓存，来防止每次提取数据都从缓存池外面提取数据。InnoDB 存储引擎在解决客户端的申请时，当须要拜访一个页的一条记录时, 就会把残缺的页的数据加载到内存中，也就是说即便咱们只须要拜访一个页的一条记录，那也须要把整个页的数据加载到内存中。将整个页加载到内存中后就能够进行读写访问了, 在进行读写访问之后并不焦急把该页对应的内存空间开释掉，而是将其缓存起来，这样未来有请申请再次拜访该页面时，就能够防止间接从磁盘中提取数据了。

为了缓存磁盘中的页，在 MySQL 服务器启动的时候就向操作系统申请了一片间断的内存，这片内存也就是 buffer pool，通过

SHOW ENGINE INNODB STATUS;

能够看到缓存池的根本状态:

Buffer Pool 中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是 16KB。每个缓存页都会有一些对应的管制信息，包含页号、缓存页在 Buffer Pool 中的地址等，每个缓存页对应的管制信息占用内存大小是雷同，咱们称管制信息所占用的 内存为管制块，管制块和缓存页是一一对应的，都位于缓存池中，管制块位于缓存页之前。像上面这样:

这个碎片是啥? 申请的内存空间在调配了管制块和缓存页，不够一个管制块和缓存页所需的内存空间呗，又假如管制块和缓存页所占用的内存一样，咱们极其假如一下缓存池只有 57KB，只够凑一个管制块和缓存页，剩下 25KB，这 25KB 就是内存碎片。

每个管制块大概占用缓存页大小的 5%，在 MySQL 5.7.21 这个版本占用的大小是 808 字节。而咱们设置的 innodb_buffer_pool_size 并不蕴含这部分管制块占用的内存大小，也就是说 InnoDB 在 Buffer Pool 向操作系统申请间断的内存空间时，这片间断的内存空间个别会比 innodb_buffer_pool_size 的值大 5% 左右。Buffer Pool 的大小能够通过 MySQL 的配置文件 my.ini 来指定，当初咱们来看一下我 MySQL 上面 Buffer Pool 的配置:

InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to
access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it
too large, though, because competition of the physical memory may cause paging in the operating system. Note that on 32bit systems you might be limited to 2-3.5G of user level memory per process, so do not set it too high.

InnoDB 存储引擎，应用缓存池缓存索引和行数据，缓存池越大磁盘 IO 越少，在专门的数据库服务器上你能够将这个参数调整为机器物理内存的百分之八十以上。不要设置的太高，否则过程间竞争物理内存可能会导致操作系统的分页(对于操作系统治理内存相干的常识，我曾经忘的差不多了，往年打算重修，对这块理解的能够在评论区留言)。留神在 32 位操作系统上，每个过程可能会被限度只能应用 2 -3.5G 的内存。

innodb_buffer_pool_size=8M

总共缓存池的大小是 8M

innodb_buffer_pool_instances=8

这个不代表有 8 个缓存池

innodb_buffer_pool_size/innodb_buffer_pool_instances 是每个缓存池实例的大小，当 innodb_buffer_pool_size 的值小于 1G，innodb_buffer_pool_instances 这个参数设置就是有效的。MySQL 官网举荐在 innodb_buffer_pool_size 大于 1G 的状况下，设置多个 buffer pool 实例。

free 链表(闲暇链表)

MySQL 启动的时候，就会实现对 Buffer Pool 的初始化过程，就是先向操作系统申请 Buffer Pool 的内存空间，而后把它划分成若干对管制块和缓存页，但目前缓存池中所有的管制块和缓存页还没有存储信息，随着 MySQL 开始接管查问申请，InnoDB 开始从磁盘页中提取数据，缓存池的管制块和缓存页开始存储信息，为了确定从磁盘上提取的页该放到哪个缓存页，辨别哪些缓存页是闲暇的，哪些缓存页是曾经被应用的，缓存页对应的管制块就派上了用场，MySQL 的开发者将所有闲暇的缓存页对应的管制块做为一个结点放到一个链表中，这个链表咱们称之为 free 链表。刚刚初始化实现的缓存池所有的缓存页都是闲暇的，所以每一个缓存页对应的管制块都会被退出到 free 链表中。

黄色的结点是链表的头结点，记录链表首结点和尾结点，以及以后链表中结点的数量等信息。链表的头结点占用的内存空间并不大，在 MySQL5.7.21 这个版本，每个头结点只占用 40 字节大小。有了这个 free 链表之后事就好办了，每当须要从磁盘中加载一个页到缓存池中时，就从闲暇链表中取一个闲暇的缓存页，并且把该缓存页对应的管制块的信息填上(就是该页对应的页号之类的信息)，并且把该缓存页对应的 free 链表结点从链表中移除，示意该缓存页曾经被应用了。

LRU 链表

Buffer Pool 实质上是 InnoDB 向操作系统申请的一块间断的内存空间, 是无限的, 咱们不可能将所有的磁盘页都加载进入内存，那咱们该如何缓存数据页呢？或者说咱们该如何制订缓存策略呢？现实的情景是拜访某个数据页的时候，这个数据页曾经在缓存池外面了。这也就是 LRU，LRU 全称: Least Recently Used, 依照最近起码应用的，也就是说当 Buffer Pool 中不再有闲暇的缓存页时，就须要淘汰局部最近很少应用的缓存页，那么咱们该如何晓得哪些缓存页最近频繁应用，哪些最近很少应用呢？咱们能够借助于链表，采取 LRU 策略:

当咱们须要拜访某个页时:

• 如果该页不在缓存池里，，就把该页从磁盘加载到缓存池中的缓存页时，就把该缓存页对应的管制块作为链表的头结点。
• 如果该页曾经缓存在缓存池里，则间接把该页对应的管制块挪动到链表头部。

那么该链表的尾部就是最近起码应用的缓存页喽，当 Buffer Pool 中的闲暇缓存页应用完时，到 LRU 链表的尾部找些缓存页淘汰就能够了。

这个问题还没有被齐全解决, 尽管从宏观上来看 LRU 策略没什么问题, 但咱们依然须要依据理论状况对 LRU 策略做些小补丁, 专门应答一些非凡的情况:

• 须要扫描全表的查问语句(没有用到索引, 没有根 WHERE 子句的查问)，扫描全表意味着须要拜访该表所有的数据页, 假如这个表中记录十分多，又假如这张表数据比拟大，那么就意味着缓存池中的所有页都被换了一次血，其余查问语句在执行时又得执行一次从磁盘加载到缓存池的操作。这种全表扫描的语句执行频率也不高，然而每次执行都会把缓存池中的缓存页换一次血，重大影响其余查问对缓存池的应用，从而大大降低了缓存的命中率(缓存的命中率 = 缓存页的拜访次数 除以 缓存页在缓存的次数)。

• MySQL 的预判, 像是咱们玩英雄联盟会预判对手的操作一样，MySQL 也有对查问申请的预判，咱们称之为预读, 所谓的预读，就是 InnoDB 不仅仅会加载查问申请所对应的数据页, 还会额定加载一些数据页, 依据触发形式不同，预读又能够细分为下边两种:

  • 线性预读

  • 随机预读

    介绍这两种预读, 须要懂一些 MySQL 如何组织数据，咱们这里来简略介绍一下，到目前为止咱们晓得 InnoDB 以页为根本单位治理数据，每个索引都对应着一棵 B + 树，该 B + 树的每个结点都是一个数据页，数据页之间不是位于间断的内存空间，因为数据页之间有双向链表来保护着这些页的程序。InnoDB 的聚簇索引的叶子结点存储了残缺的用户记录，也就是所谓的索引即数据，数据即索引。

为了更好的治理数据页，MySQL 在数据页的根底上设计了表空间 (table space 有的也称 file space) 这个概念, 这个表空间是一个形象的概念，它能够对应文件系统上一个或多个实在文件(不同的表空间对应的文件数量可能不同)。每一个表空间能够被划分为许多个页，咱们的表数据就寄存在表空间的数据页里。

InnoDB 表空间又分为多种类型:

• 零碎表空间: 须要留神的一点是，在 MySQL 服务器中，零碎表空间只有一份。从 MySQL 5.5.7 到 MySQL5.6.6 之间的各版本，咱们表中的数据会被默认存储在这个零碎表空间中。
• 独立表空间

那这些数据存储在磁盘上的哪里呢？咱们能够通过:

SHOW VARIABLES LIKE 'datadir';

命令来查看数据目录：

咱们看下这个数据目录下有什么:

数了数一共六个文件夹刚好对应六个数据库, 咱们进 studydatabase 这个文件夹看一下，

studydatabase 一共有三张表: score、student、student_info, 一张表对应两个文件, 表名.frm 存储表的构造信息，ibd 存储表的数据信息。

对于表空间来说，MySQL 设立了区的概念来治理页(英文名: extent)，对于 16KB 的页来说，间断 64 个页就是一个区。有了区这个概念咱们回头接着来介绍 MySQL 的预判:

• 线性预读

如果程序拜访某个区的页面超过这个 innodb_read_ahead_threshold 这个零碎变量的值，就会触发一次下一个区中全副的页面到 Buffer Pool 的申请。

• 随机预读

如果 Buffer Pool 中曾经缓存了某个区的 13 个间断的页面，不管这些页面是不是程序读取的，MySQL 都会异步将这些页面所在区的所有页面加载到缓存池中。咱们能够通过 innodb_random_read_ahead 来敞开开启随机预读。默认为敞开状态。

预判原本是个好事件, 然而如果预判出错, 这些预读的页都放到 LRU 链表的头部，碰巧咱们的缓存池也不大，这就会导致 LRU 链表会被淘汰掉，大大降低缓存命中率。

针对这两种状况，MySQL 将 LRU 链表依照肯定比例分成两段。别离是:

• 一部分存储应用频率十分高的缓存页，这部分链表咱们称之为热数据或者 young 区域
• 另一部分存储应用频率不是很高的缓存页，所以这一部分链表也叫冷数据，或者 old 区域。

热区域的数据和冷区域的数据是不固定的，冷区域的数据也可能被转化为热区域的数据。咱们能够通过:

 SHOW VARIABLES LIKE 'innodb_old_blocks_pct';

来查看热区域和冷区域的比例,

默认状况下 old 区域占 37%，有了这个划分之后，下面两种状况的补丁就好打了:

• 针对预读这种状况，首次加载数据页到缓存池中，会被放在 old 区域的头部，这样预读的数据页却不常常被拜访到的，就会缓缓从链表的尾部淘汰。

• 针对全表扫描这种查问频率非常低的场景，对某个处在 old 区域的缓存页进行第一次拜访时在它对应的管制块中记录下这个拜访工夫，如果后序的拜访工夫与第一次拜访的工夫在某个工夫距离内，那么该页面就不会从 old 区域挪动到 young 区域的头部。这个间隔时间是由 innodb_old_blocks_time 管制的 , 通过 innoddb_old_blocks_time 来管制:

   SHOW VARIABLES LIKE 'innodb_old_blocks_time';

事实上仅仅这两个补丁还不够，还得持续打上来，但持续讲下去并不是本篇的主题。

flush 链表的治理

如果咱们批改的某条数据，先将该页加载进入到缓存页中，而后间接对缓存页进行批改，那么此时缓存页的数据就和磁盘页的数据不统一了。当然，咱们也能够批改完缓存页的时候马上同步到磁盘对应的页上，然而频繁的和内存进行交互相当影响性能，毕竟磁盘读写的速度相当慢。所以每次批改完缓存页的时候，MySQL 并不会立刻把批改同步到磁盘上，而是在某个工夫点进行同步。

但如果不立刻进行同步的话，那咱们该如何晓得 Buffer Pool 中的哪些页是脏页呢？链表同志，请再次出场，所有的脏页对应的管制块都会作为结点退出到一个链表中，因为这个链表对应的缓存页都是须要被刷新到磁盘上的，所以也叫 flush 链表。

InnoDB 还有其余模式的链表，比方 unzip LRU 链表用于治理解压页等等，咱们下面介绍 buffer pool 配置的时候，默认配置是 8 个，Buffer Pool 实质上来说 Buffer Pool 是 InnoDB 向操作系统申请的一块间断的内存空间，在多线程环境下，拜访缓存池的链表都须要加锁解决，在缓存池比拟大且高并发拜访下，单个 buffer pool 可能会影响处理速度，所以如果单个 buffer pool 特地大的时候，咱们能够将它们拆分为若干个小的 Buffer Pool。每个 Buffer Pool 都是独立的，多线程并发拜访并不会相互影响，从而进步解决并发的能力。

buffer pool 的一些注意事项

在 MySQL 5.7.5 之前，Buffer Pool 的大小只能在 MySQL 启动的时候指定，在 MySQL 5.7.5 之后的版本包含 MySQL5.7.5，MySQL 反对在运行时调整缓存池大小，MySQL 以 chunk 为单位向操作系统申请内存地址空间，也就是缓存池又能够看做是若干 chunk 组成的。一个 chunk 代表一个间断的内存地址空间。这个 chunk 的大小由配置文件中的 innodb_buffer_pool_chunk_size 来指定。

为了保障每一个 buffer pool 实例中蕴含的 chunk 数量雷同，innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances 的倍数。如果你设置的不是，MySQL 会主动调整，举个例子，innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances = 2G，你指定的是 9G，MySQL 会主动将其晋升为 10G。

如果服务启动的时候，innodb_buffer_pool_size 大于 innodb_buffer_pool_chunk_size 乘 innodb_buffer_pool_instances，那么 innodb_buffer_pool_chunk_size 的值会被设置为 innodb_buffer_pool_size / innodb_buffer_pool_instances. 举个例子, 假如你将 innodb_buffer_pool_size 设置为 2G，innodb_buffer_pool_chunk_size = 128M，innodb_buffer_pool_instances = 32，也就是 4G。那么 innodb_buffer_pool_chunk_size 会被调整为 64M。

总结一下

本文基本上的写作思路是，当初 MySQL 查问速度比拟迟缓，在无效的利用索引的状况下，还能怎么晋升 MySQL 的运行速度，带着疑难去浏览掘金小册《MySQL 是怎么运行的：从根儿上了解 MySQL》的 buffer pool 章节，最终的答案就是查看 buffer pool 的配置是否正当，少数的内容都从其摘录而来，用带着问题的形式又将其的内容又组合了一下。