【mysql】-- Mysql-InnoDB的内存和磁盘架构详解

一、mysql的逻辑架构

1、连接器

连接器负责跟客户端连接、获取权限、维持和管理连接。
客户端先和连接器建立连接，经过ICP握手后，连接器开始认证客户端身份，这些都成功完成，表示连接成功。
有参数“wait_timeout”控制连接时长，默认8小时。
长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接：指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

一直使用长连接问题：
MySQL 占用内存涨得特别快【MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启】。

解决方案：
定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2、查询缓存

一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

问题：
查询缓存失效很频繁，使用查询缓存是得不偿失的。
设置参数“query_chache_type”为DEMAND，默认sql不使用查询缓存。当某个语句需要使用时可以SQL_CACHE显示指定，例如：
select SQL_CACHE * from T where ID=10；
备注：mysql 8.0版本已经删除这个模块。

3、分析器

识别sql语句，并分析sql是否满足mysql语法。【sql做什么】

4、优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。【sql怎么做】

5、执行器

有权限打开表时，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

二、innoDB的内存和磁盘结构

三、buffer pool缓冲池

1、buffer pool介绍

Buffer Pool是InnoDB存储引擎层的缓冲池，不属于MySQL的Server层。
缓冲池(buffer pool)是一种常见的降低磁盘访问的机制。
缓冲池通常以页(page)为单位缓存数据。
缓冲池的常见管理算法是LRU,mysql采用的是新老生代改进LRU算法，主要是解决“预读失效”和“缓存池污染问题”。
缓冲池大小默认128M，独立的MySQL服务器推荐设置缓冲池大小为总内存的80%。可以使用下面的语句进行查询：show variables like '%innodb_buffer_pool_size%';
作用：内存中以页（page）为单位缓存磁盘数据【数据页和索引页数据】，减少磁盘IO，提升访问速度。

2、SQL的读写操作原理

无论读/写操作，都是先去buffer pool中LRU列表查找是否有该数据页，有的话会直接命中，没有就需要从磁盘中读取。
从磁盘读取一个数据页【此时说明这个页必然不在Lru列表中】，首先先在free list找一个空闲缓存页的描述信息。
A：如果能找到空闲缓存页，那么将新数据页放入缓存页，同时从free list中删除该缓存页描述信息。
B：如果free list中没有空闲缓存页，需要去LRU list中寻找冷数据缓存页，将数据替换覆盖，在替换前其实需要将脏页刷回磁盘。
接着在buffer pool中对这个数据页进行DML操作。一旦对LRU链表中缓存页做了修改【脏页】，需要将脏页刷新回磁盘。

3、LRU List、Free List、Flush List

mysql启动后，BufferPool被初始化，在没有任何查询操作前，BufferPool缓存页是一块块空的内存。那么，**从磁盘读取到数据页是如何放入缓存页？**这里就需要下面三个list概念。

Free List(空闲页列表)
基于缓存页描述信息组织起来的双向链表，每个节点对应缓存页的一个描述信息。当缓存页中没存储数据，对应的描述信息维护在Free List；当有数据页放入缓存页中，从Free List找一个节点，放入成功后，从Free List移除。

LRU List
LRU列表管理的是“已经存放了读取到的数据页”的缓存页【查询和预读得到的数据页】。利用Lru算法管理，频繁使用的页在前端，最小使用的在尾端，当不能存放新读取到的页时，优先淘汰Lru列表中尾端页。

-------如何判断数据页有没有在内存缓存中(即缓存页)？
依托数据结构：Hashtable，key=表空间号+数据页号， value=缓存页地址
判断数据页在hash中，优先使用Buffer Pool中缓存页【原因：免去了磁盘的随机IO，其次缓存页中的数据可能是已经被修改了的脏数据】。

Flush List
在lru列表中数据页被修改后【该页叫脏页】，数据库会通过checkPoint机制将脏页刷新回磁盘。因此，flush list是按脏页修改先后顺序排列的链表，且按照顺序持久化到磁盘，被多次更新的页面不会重复插入到flush list，只是更新一些属性值。

4、新老生代改进LRU算法(最近最少使用算法)

最频繁使用的页在LRU列表的前端，最少使用的页在LRU列表的尾端。当缓冲不能存放新读取到的页时，首先释放LRU列表尾端的页。

4.1、传统LRU算法

使用双向链表+Map来存储，双向链表是为了更快移动调整数据，Map是了更快找到对应数据。
基本思路：
添加节点：首先查是否有key，有的话，替换value，且移动这个节点；没有key, 如果容量满了，删除last，在first处添加新的；容量没满，first位置添加即可。
查询节点：查询到节点数据，将这个节点移到first位置。
删除节点：类似双向链表一样。

具体代码实现见“https://blog.csdn.net/xunmengyou1990/article/details/116198942”

4.2、新老生代改进LRU算法

对于InnoDB的改进LRU算法，基本新老生代LRU算法主体思路列表拆分为新生代new和老生代old【新生代（new sublist）和老生代（old sublist）两个区域默认比例为63:37】，磁盘读取或预读的数据进入老生代头部，数据再次被读取时才会进入新生代头部，如果数据没被读取，这个数据会被新生代的“热数据页”更早地被淘汰出缓冲池。
对于上面的思路，在实际中考虑到“预读失效”和“缓存池污染”两大问题【后面具体介绍】，那么“完整的改进LRU算法思路”：
A：LRU列表分为new子列表【5/8缓冲池大小】和old子列表【3/8缓冲池大小】；
B：预读的数据页或从磁盘读取到的数据页被加入到old子列表的头部；
C：这些【预读或磁盘读取】数据页有“老生代停留时间窗口”T时间设置，只有在“被访问”且“在老生代停留时间”>T，才会进入新生代头部；

备注说明：

老年代所占LRU比例：
show variables like '%innodb_old_blocks_pct%';
老年代停留时间：
show variables like '%innodb_old_blocks_time%';

5、预读失效问题及解决策略

预读：磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。

预读有效原因：数据访问遵循“集中读写”原则。使用一些数据，大概率会使用附近的数据-----------------“局部性原理”。【因此，将下次可能用到的数据和索引加载到Buffer Pool，从磁盘加载数据时不是一行，而是一页。顺序访问N个页后触发预读，会将下一个页数据从磁盘加载到内存中，Mysql5.4版本默认开启，默认值是56。】

预读策略：线性预读、 随机预读【已废弃】。
线性预读认为如果前面的请求顺序访问当前区（extent）的页，那么接下来的若干请求也会顺序访问下一个区的页，并将下一个区加载到Buffer Pool。
Mysql5.4版本默认开启，默认值是56。表示顺序访问N个页后触发预读，会将下一个页数据从磁盘加载到内存中【一个区1M，最多64个页，一个页是16K】。
查看线性预读变量：show variables like 'innodb_read_ahead_threshold';

预读失效(存在问题)：提前主动把页放入了缓冲池，但最终MySQL并没有从页中读取数据。【会更快的把其他页挤出】
优化预读失效的思路：
(1)让预读失败的页，停留在缓冲池LRU里的时间尽可能短；
(2)让真正被读取的页，才挪到缓冲池LRU的头部；
保证真正被读取的热数据留在缓冲池里的时间尽可能长。
解决预读失效的方法：使用新老代改进版LRU算法【重点是 “B：预读的数据页或从磁盘读取到的数据页被加入到old子列表的头部；”】。

6、缓存池污染问题及解决

缓冲池污染原因：
当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降。

例如：select * from t1 where city like "%nanjing%";

这个语句虽然查询的结果集很少，但会全表扫描，会访问大量的页，按照“新老代改进版LRU算法”思想，读取到的大量数据进入新生代头部，而这些数据只访问一次，那些真正热数据会被替换。

解决缓冲池污染：“老生代停留时间窗口”机制。
假设一个T=老生代停留时间窗口；
批量扫描的数据进入老生代头部的页，即使是立刻被访问，也不会放入新生代头部；
只有满足“被访问”且“在老生代停留时间”>T，才会进入新生代头部。

innodb_buffer_pool_size：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。
innodb_old_blocks_pct：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。【如果参数设置为100，那么就是普通LRU】
innodb_old_blocks_time：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。
innodb_old_blocks_pct和innodb_old_blocks_time两个参数解决类似全表扫描操作，保证热点数据不被刷出。

7、脏页落盘机制

7.1、问题1:LRU列表中哪些数据页刷新回磁盘？

flush list是按脏页修改先后顺序排列的链表，且按照顺序持久化到磁盘，被多次更新的页面不会重复插入到flush list【只保存被修改缓存页的描述信息】，只是更新一些属性值。
对LRU列表的缓存页做了修改，这个缓存页对应的描述信息被添加到Flush list，等待被刷新回磁盘。如果buffer pool缓存页不够，也是优先将flush list的脏页【脏页就是LRU链表中被修改了的缓存页】刷新回磁盘。

7.2、问题2:脏页刷回磁盘时机？

(A)、当mysql关闭时，会将所有的脏数据页刷新回磁盘。由参数：innodb_fast_shutdown=0控制，默认让InnoDB在关闭前将脏页刷回磁盘，以及清理掉undo log。
(B)、后台线程Master Thread会按照每秒或者每十秒的速度，异步的将Buffer Pool中一定比例的页面刷新回磁盘中。
©、MySQL5.7中，Buffer Pool的刷新由page cleaner threads完成。
(D)、脏数据页太多时，也会触发将脏数据页刷新回磁盘。由参数innodb_nax_dirty_pages_pct控制，比如将其设置为75，表示当Buffer Pool中的脏数据页达到整体缓存的75%时，触发刷新的动作。现实情况是默认值为0，以此来禁用Buffer Pool早期的刷新行为。
(E)、redo log日志满了时，也会强制脏页列表中的脏页刷新回磁盘。【write ahead log策略和redo log是保证数据恢复作用，那么必须要确保redo log对应的脏页刷回磁盘，才可以覆盖】此时系统不接受其他事务的更新操作。
(F)、检测到系统空闲时也会刷脏页。

7.3、问题3:刷新临接数据页？

MySQL将某脏页刷新回磁盘时，是否也以相同的态度将该脏页邻接的脏页一并刷新回磁盘。由参数innodb_flush_neighbors控制。
设置为0时表示，禁用刷新邻接的功能【高版本默认为0】。
设置为1时表示，以相同的态度刷新其邻接的脏页。
设置为2时表示，以相同的程度刷新脏页。

四、buffer pool-- change buffer写缓冲

Mysql5.5之前版本，叫insert buffer，只对insert操作做优化；目前版本，对insert、delete、update都有效，叫change buffer写缓冲。

如果不考虑change buffer，只有buffer pool，那么“写操作–DML”是怎样情景：

按照上面的思路：“修改的页不在buffer pool缓冲池”，每次需要IO读磁盘操作，那么在“写多读少”的场景，上面频繁的IO操作，有没有优化空间？
答案是：有，利用change buffer写缓冲。 只有对于非聚簇索引的非唯一索引且数据页不在缓冲池中，对页进行DML操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer change)，等未来数据读取时，才会将数据合并恢复到buffer pool缓冲池。

考虑change buffer，那么“写操作–DML”是怎样情景：

1、什么条件下使用change buffer？

对于聚簇索引和唯一索引，如果进行DML操作，需要去磁盘加载数据页到buffer pool内存池，来判断这个操作是否违反唯一性约束。而对于非聚簇索引的非唯一索引是没要求。

五、buffer pool-- adaptive Hash index自适应哈希索引

InnoDB引擎会监控对索引页的查询，发现某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这种称之为自适应哈希索引。根据某个检索条件，直接查询到对应的数据页，跳过从B+树从上到下逐层定位的步骤。
本质是：从某个检索条件到某个数据页的哈希表。为经常使用的索引和经常使用的数据页建立缓存。

经过下面三个条件才能为数据页建立AHI：
条件1：索引使用次数大于17次
条件2：对使用次数大于17次的索引建立hash info，hash info是用来描述一次检索的条件与索引匹配程度。当hash info使用次数>100代表经常使用的hash info。
hash info结构:检索条件与索引匹配的列数，第一个不匹配的列中，两者匹配的字节数，是否从左匹配。
条件3：经常使用的hash info命中页的次数>页记录数的1/16。

六、buffer pool-- double write双写缓冲区

double write默认开启，参数skip_innodb_doublewrite虽然可以禁止使用double write功能，但还是强烈建议大家使用double write。

1、partial page write(部分页失效)问题

Innodb刷脏页是页数据页来刷回磁盘，一个数据页是16KB。对于一个page操作，大部分情况不是原子性，那么如果服务器宕机，一个page的数据会部分写入成功，这种就是“部分页失效问题”。
解决上述问题，InnoDB使用double write buffer来保证数据页的可靠性。
在写数据页之前，先把这个数据页写到一块独立的物理文件位置（ibdata），然后再写到数据页。当有宕机重启时，如果出现数据页损坏，那么在应用redo log之前，需要通过该页的副本来还原该页，然后再进行redo log重做，这就是double write。

问题1：为什么redo log日志不能数据恢复？
按照redo log原理，当脏页刷入磁盘(离开buffer pool即开始)，对应的checkpoint_lsn会变化且旧的redo log会被覆盖。那么对于数据页本身损坏，redo log是无法恢复的。

2、double write过程和数据页恢复过程

Double write工作过程
通过memcpy函数将脏页先复制到内存中的double write buffer，之后通过double write buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上【double write】；然后马上调用fsync函数，同步脏页进磁盘上【离散写入】。

数据恢复过程
若写double write buffer失败，此时数据是不会写入磁盘，通过redo log恢复数据到buffer pool，重新开始刷脏。
若将页写入磁盘的过程中发送了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的副本，将其复制到表空间文件，再应用redo log。