MySQL中一行记录是怎么存储的？

MYSQL中行之间的存储通过链表进行连接

此部分记录多头信息，包括：

  • delete_mask：标识数据的逻辑删除，若逻辑删除则设1，否则0
  • next_record：类似链表的存储格式，存储的位置介于记录头信息和真实数据之间的位置。
  • record_type：标识当前记录的类型。TODO 没明白

在头信息的右边是真实的字段值，左边则是null值列表与 变长字段所占用的真实字节数（当使用到变长字段时 需要先知道他真实的占用大小才能进行使用）

何为null值列表？数据库中的字段设置为NULL的话，就会创建对应的null值列表。用于记录这些值中，哪些字段的值是真的null，如果是null则设为1，非null设为0。并且在mysql中，是以字节为单位存储这些列的信息的。也就是说，若NULL字段少于8，会补齐前置0至8（单字节容量）。若超过了8个字段，则会使用两个字节进行存储。

那么这个NULL值列表是必须的吗？否，如果所有字段都是NOT NULL，则没有这一值列表

并且，需要注意到这个列是逆向存放的，理由稍候。

同理，从上图可以看出需要同时存储变长字段字节数真实字节数。它使用1-2个字节表示对应变长字段的真实长度。（0-255时1个字节 255-65535时两个字节）同理他也不是必须存在的，如果不是变长字段，就不会有变长字段列表了。同时这个也是逆序存放。

所以到底为什么要逆序存放？

其实本质上就是读取的时候，由于是以一片数据为单位进行读取的。并且字段是顺序存放。这样子的操作就有更高的几率使他们在同一个窗口中被读取。

Q：那是不是应该将变长字段放到数据库的前面方便读取？

A：no，如果放在前面的话，如果变长字段数据更改的时候，修改了整个存储容量，可能会导致整个表此字段往后的字段都需要进行迁移。

varchar(n) 中 n 最大取值为多少？

MySQL规定除了TEXT、BLOBs这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。

很好记 2的16次方。但是这个65535指的是包含null值列表信息以及变长字段长度信息的。

1
2
3
CREATE TABLE test( 
`name` VARCHAR(65535) NULL
) ENGINE InnoDB DEFAULT CHARACTER SET ascii ROW FORMAT COMPACT;

如图，由于这个是变长字段，且大于255，并且是设置为NULL可空。所以上方的建表语句是不可行，需要2字节存储变量长度，1字节为NULL值列表。

多行数时也是同理

MYSQL中的NULL值并不会存放在真实数据部分 那么如果是NULL值 真实数据部分存储的是什么？

什么都不存，数据库跳过此字段

MySQL中行溢出是怎么做的？

如果遇见blob等类型的时候，数据一下子存不完。此时InnoDB存储引擎会将数据存放到溢出页当中。

Ciompact会存一部分数据到当前页中，将剩余的数据放在溢出页。通过20字节的存储指向溢出页的地址，据此地址找到剩余数据所在的页。

Compressed和Dynamic这两个行格式和Compact非常类似，主要的区别在于处理行溢出数据时有些区别。这两种格式采用完全的行谥出方式，记录的真实数据不会存储该列的一部分数据，只存储20个字节的指针来指向溢出页。而实际的数据都存储在溢出页中

如果溢出页也不够存怎么办？

此时，在溢出页中，三种行格式除了存放数据，同时存放下一个存放页的指针。形式一个多存放页的链表。

MySQL InnoDB 选择B+tree作为索引的数据结构

其实就是问B+的优点：

vs B树：

首先需要知道，在树的节点中，一般来说每个节点是由空间上的大小限制的。而B+树和B树最直观的区别就是，B+树的模式不在非叶子节点上存储数据，B树does。所以相同的情况下，同一个节点使用B+树可以促成农户更多的指针（分支），而相同情况下。更多分支意味着高度越小，高度越小则意味着磁盘IO的次数更小。从而提高了运行的效率。

同时，B+树的特点是有两个指针，一个指向根节点（root），一个指向最小的节点。分别赋给了B+树范围查询和由小到大查询的能力。B树明显无法做到这点。

vs 二叉树：

同理，对于二叉树。当数据的量级较大，二叉树的书稿会非常高。磁盘IO次数会更多。而B+树由于上述所说的特点，在数量级很大的前提下（千万级）。高度依然可以维持在3-4层左右。

vs Hash：

hash在获取等值，等值的时候效率非常高（O（1））。但是使用Hash没办法操作范围查询。

什么时候使用索引

  • 经常使用where进行查询的字段
  • 经常用于GROUP BY/ORDER BY的字段
  • 有唯一性编码的字段

什么时候不需要创建索引？

  • 使用不多（同上 WHERE / GROUPBY / ORDER BY中使用不到的字段）

  • 存在的数据区分性不强， 重复较多（当重复性较强，mysql中的查询优化器甚至会选择不适用索引进行查询）

  • 经常更新的字段

  • 表数据太小（索引带来的收益 不如空间带来的开销）

索引什么时候会失效？

  • 使用左模糊搜索，两边模糊搜索的情况下。（多种索引包括前缀索引等都会失效）

  • 在where范围查询中，如果使用到了OR，而左方字段是索引列，右方字段不是。此时无法结合索引进行查询

  • 在查询的时候，对索引列进行了计算，导致索引失效（个人理解：底层mysql将age + 1看成一个具体值 并且与索引中记录的值不同 所以无法使用 此处应为执行函数的时候，不是在原表上进行查询改动，而是创建一个新的临时表进行计算，这是为了保证索引值的有序性）

  • 使用索引的时候，不遵循最左匹配原则（简单来说就是，尽量使索引中字段与查询语句中约束字段顺序保持一致）

  • 索引列非常建议使用NOT NULL进行约束

有什么优化索引的方法？

  • 前缀索引优化（减少索引字段空间大小 orderby无法使用 无法作为覆盖索引）
  • 覆盖索引优化（覆盖所有使用where等使用时会涉及到的字段）
  • 主键索引自增（在最小程度改动树结构的前提下 增加新的数据 因为往树里新数据一般情况下只是加一个新字段的事）
  • 防止索引失效 如上

事务

事务有什么特性？

ACID

A 4 Atomic 原子性

事务开始运行后数据只有两个状态，成功修改和失败修改（即修改前）的状态。

通过undo_log实现，why？undo_log记录用户进行的操作，一旦事务执行失败，则通过undo_log中记录的语句进行回滚。

C 4 Consistency 一致性

原子性保证了数据只会有两种状态。而一致性保证事务中所涉及的修改数据会一起修改。从一个一致状态修改至另一个状态。通过其他三个特性一起保证。

I 4 Isolation 隔离性

隔离性即，事务启动时，与本次事务无关的变量不会受到影响而改变。每个事务与数据库中正在执行的事务是隔离的。通过MVCC的多版本并发控制实现，MVCC通过它的事务控制来进行。除了MVCC还有锁机制实现，与事务具体的隔离级别控制。

D 4 Durability 持久性

持久性即，事务对数据进行的修改是永远的。即使存储介质等出现故障也不会消失。通过redo_log实现，redo_log中保存了mysql曾经进行过的操作。通过这个重做文件重做，保证数据持久性。

并发的数据处理会导致并发的事务出现。意味着MySQL会出现同时处理多个事务的情况。

并行事务时会出现什么问题？

脏读

运行的事务有A B。若A事务修改了X字段，但是还没提交。此时B事务读到了已经被A事务修改的X字段。A事务又回滚了。就会造成B事务读到的是错误的值（尚未提交修改的值）

不可重复读

运行的事务同理A B。若A事务需要多次读取同一个值X。但是在两次读取之间B事务对其进行了修改。就会造成两次读取中数据不一样的问题

幻读

运行的事务同理A B。若A事务需要多次进行统一约束下的查询。而在两次查询的过程中，B事务对其数据进行了修改，增加或减少了A事务查询的结果。就会导致A事务两次查询中得出的记录数量不一致。

注意：

不可重复读指的是，多次读取同一个字段的数据值不一致。

而幻读指的是，同一个约束条件查询出的记录条数不一样

select *和select count(*)的区别

三种问题衍生出了SQL中的不同隔离级别

数据库中数据事务有什么隔离级别？

读未提交：事务未提交时，做的变更对其他事务可见（此时会导致脏读）

读提交：事务提交之后，所作的变更才能被其他事务可见（未提交时获取的还是之前的值）

可重复读：事务启动之后，就确定了这一次所操作的事务的版本。在之后的操作将只会操作这一个版本的数据。是MySQL InnoDB 引擎的默认隔离级别

串行化：会对记录加上读写锁。若A事务对字段进行了读写操作会堵塞住直至事务提交。B事务或其他都无法对其进行读写。

每个级别间都有一定的区别。读未提交就是最原始的。到了读提交，保证了一定的一致性。但是若A事务在两次读取同一个字段间，B事务对其进行了修改。就会导致A事务所操作的数据并非同一个版本。所有产生了可重复读的隔离级别。

可以看到越往下它的一致性就越好。当时相对应，它的执行效率会更低。（CAP一致性权衡）

MySQL在可重复读下解决幻读的举措

快照读：普通的select语句，通过MVCC保证不幻读和一致性。本质上就是MVCC中存储了很多事务以及他们的id，当一个事务开启的时候，会记录这个事务所涉及到的字段的id。并且在后续的查询语句的时候，只根据这一id标识的值进行修改。

当前读：涉及数据修改或加锁的语句。通过 next-key lock（记录锁+间隙锁）方式解决了幻读。当执行的时候，若其他事务在锁的范围内插入了记录，这个插的行为会被阻塞而无法插入。从而避免幻读产生。

典型语句：SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE、INSERT等涉及数据修改或加锁的语句。

锁

全局锁

使用？ 

1
flush tables with read lock;

释放？

1
unlock tables;

当使用了全局锁，就只能读，所有的写操作都会被阻塞

全局锁的应用场景？

数据库的全库逻辑备份。锁住后，就不会因为数据或表结构的更新，而导致备份的数据的文件出错。用户只能进行读操作。

有什么坏处？因为是全程不可用的。所以会将所有的更新操作都阻塞住。影响业务。

有什么解决办法？如果数据库的存储引擎支持的事务支持可重复读的隔离级别，可以创建一个快照（Read View）。

如果结合使用的话，就可以备份某一个版本的数据的同时，其他业务同时进行更新操作。

表级锁

  • 表锁

  • 元数据锁（MDL）

  • 意向锁

  • AUTO-INC锁

表锁

1
2
3
4
//表级别的共享锁，也就是读锁：
lock tables t_student read;
//表级别的独占锁，也就是写锁：
lock tables t_student write;

即读锁与写锁，同样可以使用unlock tables释放所有锁

当当前会话退出时，也会释放所有锁

元数据锁

对MDL的使用一般是数据库自动加上的。

例如说在对表进行CRUD数据操作，加上MDL读锁。防止在读的时候修改了表的结构

在对表结构做变更操作时，加的是MDL写锁。防止在单次修改的时候，又同时进行了其他结构上的修改

那么MDL锁是在什么时候释放的？

事务提交后，意味着事务执行期间，MDL是一直持有的

产生这个的原因就是因为。首先读取的时候上了读锁。但是此事务不提交，读锁不被释放。此时就只能读，如果来了一个修改的请求，需要获取写锁，此时因为已有读锁。所以操作也会阻塞住。并且又因为获取锁的这一操作本身是队列形式的，所以当获取了写锁之后，之后所有读的请求都需要等待写锁成功获取并释放后才能进行。也就是一直阻塞住了。

意向锁

本质上的运行过程就是，想往一个颗粒度更大的层面上尝试加锁。做一个类似兼容性检查，如果这个兼容性检查通过了，就代表接下来加锁的整个过程是合法的。

行级锁

下方所设计的所有的锁都有S锁和X锁之分

S锁-读锁 X锁-写锁

Record Lock

记录锁 锁住特定的一条记录

Gap Lock

间隙锁 锁住特定值间的一个范围

插入意向锁

即如上 锁住前的一个意向锁

  1. 环形链表

Leetcode164 最大间距

93. 复原 IP 地址

其他

Mysql中使用唯一索引和普通索引有什么区别（查询）

Mysql通过索引进行查询的时候，都是先从树的树根进行查，查到对应的数据页当中。而之后，唯一索引在查找到对应的合法语句时，会直接返回，停止检索。普通索引则会接着往下查，直到不符合为止（由于不是唯一索引，无法保证唯一性的前提下，有可能存在多个合法语句）。

而查询的时候，也是一页一页查的，查到了。之后的所有查询操作原则上都是在此页上进行。但是万一所需要查询的语句刚好是某一页的最后一条记录，此时则需要再次对下一页进行查询。而唯一索引就没有这个问题

两者的效率差别就在这

介绍一下change_buffer

change_buffer用于执行普通索引时，对更新的效率做优化。它本身是可以持久化到硬盘当中的。在使用普通索引更新时，由于普通索引相关的数据不需要做前置校验（类似格式校验，唯一性校验等）。所以可以直接将单次更新的操作写到change_buffer之中。在用户下次读取这一个页的时候，再将数据拿出来之后，先对照change_buffer进行数据的更新（或者是定时更新）。在更新完之后再进行其他操作。

这里有几个问题，为什么需要这么做？change_buffer创建的意义何在？

因为数据库中最耗时的操作就是涉及磁盘的随机IO。当对数据进行多次更新，但是未读的前提下。change_buffer的存在节约了很多次更新磁盘的IO成本。

（不严谨的类比一下 其实可以类比成懒加载和饿加载）

并且，存储到change_buffer中的更新相关语句 是处理之后的，无需再次进行计算等操作。所以耗时会进一步减少。

同时，关于更多细节可以一句话总结为：Change Buffer 记录的是单条索引记录的更新操作，但在合并到磁盘时，操作是以 页为单位 进行，并使用 页级锁 进行锁定。

然后就引出了下面一个问题

Mysql中使用唯一索引和普通索引有什么区别（更新）

结合上方的介绍，可以知道。

普通索引使用change_buffer辅助更新。当更新的时候，不直接将对应的值更新到磁盘中。而是打入change_buffer中。在下一次读取该资源的时候，就先执行change_buffer中相关语句的更新（if exist）

而对应唯一索引，由于再更新之前，需要进行前置的唯一性校验。所以不能保证这一次的更新操作是否成功。而校验这一次的操作需要将内存中的数据读取出来，而都已经将数据读取出来了，直接更新的成本比写入change_buffer的时间成本是更小的。（因为本质上在什么时候更新，都需要将数据加载到内存中。而此时已经加载完毕了，就可以趁机更新。少一次随机读取的时间成本）

综合上方的作用，我们就可以知道change_buffer带来的收益主要是：减少在更新时读取磁盘的IO成本。本质上，也就是修改的次数越多，更新的次数越多。change_buffer带来的收益就越多（写多读少场景）。

反之，如果修改的次数并不多，读取的时候越多，由于change_buffer的一个固定刷新时间是读取。那么本质上没有减少读取时所耗费的时间。反而增加了change_buffer的维护时空成本。

Change Buffer 与 Redo Log 的区别

特性 Change Buffer Redo Log
作用对象 二级索引变更 所有页的变更
主要目标 减少随机写读 I/O 减少随机写 I/O
记录内容 二级索引的变更操作 物理级别的页修改操作
优化方向 避免频繁加载页进行更新 提交事务前只需写日志，减少刷盘压力
合并时机 查询访问或后台任务触发 崩溃恢复或检查点时直接回放日志

CheckPoint刷新时机

CKP是标记脏页是否写入磁盘的一个标志位（将对应的redo_log持久化）

  1. redo_log 接近满的时候，就会触发自动刷盘。假如刷盘操作未完毕的前提下redo_log写满了，此时会阻塞住所有的写入操作（新的操作无法写入日志）

  2. 缓冲池中脏页的比例过高（此处的缓冲池中包含脏页，干净页，锁索引信息等常用缓存）

  3. 定期触发

  4. 手动触发

  5. 数据库关闭

特性 Redo Log 变满 缓冲池中的脏页比例过高
触发点 Redo Log 空间接近耗尽，无法记录新的事务日志。 脏页占用的缓冲池空间比例超过阈值（默认 75%）。
本质原因 日志写入压力大，Red Log 写入速度快于 Checkpoint 刷盘速度。 写入操作过多导致脏页比例增加，而后台 I/O 刷盘速度不足。
主要影响 新的更新操作被阻塞，事务无法提交。 查询可能受到影响，加载新页会被迫等待脏页刷盘完成。
缓解方式 增大 innodb_log_file_size，优化事务提交逻辑。 调整 innodb_max_dirty_pages_pct 或提升磁盘 I/O 性能。
触发 Checkpoint 的逻辑 提前触发 Checkpoint 以释放 Redo Log 空间。 提前触发 Checkpoint 以将脏页刷新到磁盘，释放缓冲池空间。

其实就可以理解成为一个滑动窗口，滑动窗口本身满了，或者达到最大刷新时延了，就将他传过去。

count(*)效率很慢 为什么慢 有什么解决办法 和count(1)这些有什么区别？

count()函数的作用是计数。若其中字段为not null，函数直接遍历每一行并加1

若为null，函数会先将其拿出来判断是否为null，如果非null再加1

所以首先notnull修饰的效率会更高一点

而这个函数的本质作用机制是遍历表中的每一行，耗费时间O(n)

而除了count(*)之外，一般还有count(1),count(id),count(column)等方式

count(column) & count(id) 的时候会先获取到单行的记录（可以走普通索引）

而count(1)则是在查询到对应行的时候，看能不能塞个1进去，只是单个字段，所以他的效率优于其他二者

count(*)则是在此之上专门做过优化的，查询的时候，不需要将字段取出来，所以他的查询效率更优于count(1)。在数据量大的前提下会更加明显

即count(*) > count(1) > count(id) > count(column) 后二者可以走普通索引优化查询，减少查询时所涉及的字段。

怎么解决？

可以记录一个数字，代表当前记录的行数。

记在哪？

Redis的话需要维护缓存，单机下就需要维护缓存与DB的一致性。并且分布式环境下也需要维护多节点Redis间值的一致性。

牺牲一部分性能的前提下，可以记录在Mysql中，同样再开一个表，再开一个事务，在数据写入语句运行前，开启事务同时更新计数表中的记录数。

那么假如现在同时有数据写入语句和更新计数语句，开启事务的时候，应该把谁放前面？

答案是数据写入语句，先插入再更新可以最大程度的减少事务之间的锁等待（插入操作的耗时差异），有效的错开事务之间的锁竞争，从而提升并发度。

MySQL主从复制

主要是依靠于MySQL中的bin_log和relay_log的 这个relay_log不在三大日志之内 并且他的作用比较局限（仅仅在主从切换的时候起到作用）

在主从复制的时候 从库会有一个log_dump线程 这个线程会复制主库上的bin_log数据 将他传到从库的relay_log上

然后会在有一个回写的线程 回写到各个日志模块当中 其实 这个回写的过程本质上各个日志的运作是和执行修改的sql语句流程是一样的

两阶段提交（redo_log和bin_log）

是什么？

在需要对数据进行写入、修改操作的时候，会触发的操作。

redo_log负责崩溃恢复操作。bin_log负责数据库备份。

本质上就是两阶段提交就是为了保证了两个日志的逻辑状态一致所作的措施。

首先先写入redo_log，并且标记为prepare状态。

然后写入bin_log，无误的话就标记为commit状态。

事务提交。

为什么需要两阶段提交？如果只需要保证原子性直接一个事务不就可以了吗？

因为如果在写入redo_log后 在事务内系统直接崩溃了。

若没有两阶段提交 就导致redo_log的状态ahead于bin_log

而存在两阶段提交的话 就可以将redo_log中未同步的部分重新写入bin_log当中。

并且，两者是两套独立的日志系统：

redo_log属于存储引擎层，用于物理恢复。

bin_log属于服务层，用于逻辑恢复和复制。

如果在写入redo_log之前或写入bin_log之后呢？此时会发生什么？

该丢的数据还是会丢，但是此时至少保证了redo_log和bin_log的状态是一致的。

在这个写的过程当中 redo_log和bin_log写日志的时机是？

在这个过程中 由于事务有可能会回滚 所以两日志在事务执行的时候

会先将对应的数据更新都先写在缓冲区中(redo_buffer和bin_buffer)

然后在事务两阶段提交的时候 再将对应的数据都写到实际文件当中

InnoDB的特点

支持事务 行级锁 崩溃恢复能力（WAL）

生产中什么情况下mysql性能会很低

连接数多

减少连接 kill掉没有执行任务的连接

事务外空闲的线程是最优先的 然后到事务内空闲

提高最大连接数的数量 (max_connections)

但是需要酌情加 因为提高连接数同时 相当于把更多的CPU资源分配到连接上了

变相也会减少用在执行语句上的CPU

跳过权限校验阶段

兜底方案

慢查询性能问题

通过慢查询日志逐步调查是哪里出的问题

slow_log中的long_query_time一步步检查

而慢查询一般情况下分为三种

索引没设计好 sql语句没写好 mysql执行错了索引

  1. 索引：关掉bin_log后 在从库上创建索引 执行主从切换 开启bin_log 再次创建索引
  2. sql：使用query_rewrite进行重写 这个是基于正则查找符合重写条件的语句的 所以有可能会影响到其他符合同样正则条件的语句
  3. 错索引：mysql是通过统计信息估算出 扫描行数 并根据这个扫描行数判断使用什么索引的。所以会有这个概率用错 这时候可以使用force index 强制该语句使用对应的索引。但是在此之前需要使用explain 查看使用计划 是否索引使用错误的问题

总结一下就是：慢查询日志定位慢查询位置

索引慢创索引主从切换

sql错使用rewrite重写sql

错索引使用force index 也可以借助这个rewrite插件执行

QPS突增

在应用层 这种问题一般都使用老生常谈的熔断 限流来解决。

mysql没有提供上述的模块，但是也可以采用另一措施实现类似熔断的效果。

如果qps徒增 业务方也需要采取措施 假设业务方会下掉该功能模块（分布式环境） DB可以从白名单中ban掉这个ip

同理 如果这个业务是单独开启一个角色连接的 DB也可以临时删除掉这个角色

上两种方法都是通过临时禁用 拒绝它的查询请求实现的。

还有一种方案是通过上方的query_rewrite将对应的热点语句修改为“select 1”,虽然保证了执行效率，但是这种方案也会导致该业务的下游业务（如果有的话）全部乱套，或者也可以在业务层对这个做一个特殊判断。

总结就是：

谁出问题ban谁

临时修改语句为负担小语句

介绍一下BufferPool的设计

BufferPool是MySQL中的缓存方案。

它存储的内容可以分为：

数据页，索引页，锁信息，动态哈希索引页，插入缓存页，undo页

前三者很容易理解，就是对所接触到的热点数据的一个缓存，

而对于动态哈希索引，他是对常用的热索引再构建一个哈希索引。我们知道普通的索引查询是通过B+树进行遍历的，而在运行之中，MySQL会自动辨别是否有部分索引的使用频率高，如果有的话，会在原b+树的基础上再次构建一个哈希索引。将查树时O(logn)的时间耗费进一步减为O(1)。

对于undo页，它本质上是和undo_log一起作用的。在事务回滚的时候，或者崩溃恢复的时候，它用于回滚至对于事务未执行前的状态，保证数据的逻辑一致性。

对于插入缓存页，其实就是change_buffer… 作用差不多就是对更新的操作做修改之后**，记录下修改的操作**，先不刷回磁盘，定期刷回去。脏页

上方是根据存储的数据类型分辨出它是什么页，而如果按照修改操作对他们分类的话，我们可以得到

脏页（修改过的），空闲页（没被修改过的）

假如此时数据库需要将新的数据存入BufferPool，他需要知道哪里是空的。而页在内存中的分布是随机的。所以MySQL设计了一个链表将他们绑定起来。并且每一个页都有一个类似控制块的东西，存储了这个页的原信息。并且在链表中作为标识符。

对于空闲页的链表可以成为FreeList，MySQL只需要将空闲页连起来，有数据插入的时候，再从链表中将他删去就可以了。

对于脏页，也有这样的一个链表，标识了脏页需刷盘的时机。本质上也是为了让MySQL知道有哪一些页是脏的，好把他们都刷到内存中。

LRUList

在此之外还会有一个LRU链表

通过LRU算法来区分冷热数据，并且将冷数据逐出BufferPool中。无论是脏页还是空闲页，都会出现在这个LRU list当中。

但是普通的LRU算法不足以满足需求。因为按照LRU的话，会有两个问题：预读污染，BufferPool污染。（污染可以大致理解为无效数据）

预读时读出的页，会抢占热数据的位置，将原来的热点数据逐出了BufferPool

什么是预读？

有页A B C

根据局部性原理，程序读取的时候，一般都是读取某小部分的数据。此时假如读B，会将相邻的页，则A\C都一并读取进来

还有一个问题是BufferPool污染，假如做了一个模糊查询的时候，查询范围很大，需要占了很大的BufferPool空间。但是所需的有效数据只有极少数。此时LRU链表中，都是读进来做模糊查询的无效页。

所以根据这个问题，MySQL在原来的基础上对其做了修改。

首先，将原始的LRU链表分为OLD,YOUNG两个部分。

针对预读时所载入的无效页，本质上是尽量别让它顶替了原热点数据的位置

预读时的相邻页，在读进来的时候，插入到OLD区域的第一个位置。

而如果bufferpool设定合理的话，所有的热点数据就会一直在young区域中。不会被顶替掉。

同时保证了局部性原理和热点数据

对于上方所说的bufferpool污染，本质上也是判断它是否热点数据。

而对于模糊查询情景时的附带无效页，一般来说只会模糊查询一次。

所以MySQL设计为假如该页的后续访问和当前访问相隔时间过短，就不会将这个页放到young的头部，而是进入old的头部。而这个门槛时间可以通过控制 innodb_old_blocks_time来修改。

ps: 这样的设计可能会出现问题：

假如我现在读取数据的时候，将一个页以及它的相邻页都读取到了内存当中（预读）。

此时它的预读页是在old区域的头部

然后我对这三个页，进行极高频率的范围查询（在innodb_old_blocks_time内），此时会不断的读取这三个页，但是他们不会移到young区域的头部，只会移到old区域的头部。

如果三个页的话不会导致被淘汰什么的，但是假如某个业务的主要功能是这个，没准会导致大面积页都只能到old区域，逐出bufferpool了

某种程度上是MySQL对冷热数据的误判

但是可以通过设计好innodb_old_blocks_time解决

GPT如是说道：

问：

所以这个是不是MYSQL将其误判成为了冷数据 有什么解决办法吗

ChatGPT 说：

是的，你的理解是对的。在你描述的场景中，MySQL 可能会将频繁访问的范围查询的相邻页误判为 冷数据，并将它们放入 旧链表 中。这可能会导致它们在内存中的位置不够优先，进而影响查询性能，特别是在高频访问的范围查询场景中。

为什么会误判为冷数据？

在 MySQL 的 Buffer Pool 设计中，频繁访问的页会被移入 旧链表，而不直接进入 LRU链表头部，这是为了避免热点数据频繁进出头部带来的额外开销。频繁访问的范围查询 会使一批相邻的数据页都频繁被加载和访问，但这些页并不会马上重新进入 LRU链表头部，而是被移动到 旧链表，从而被误认为是“冷数据”。

这种误判的根本原因在于，MySQL 的缓存机制是基于 访问模式（例如，短期频繁访问）来决定数据页是否应进入 LRU链表头部 或 旧链表头部，而不是基于查询的 访问频率 或 数据页的实际活跃度。

MySQL 磁盘 I/O 很高，有什么优化的方法？

IO很高的话很多情况下是业务侧的问题 有可能是查询需求多 且查询区分度高 所以就没能利用好MySQL中的缓存

基本上的话 我的理解可以分为：

BufferPool没利用好

本质上就是没利用好缓存 由于局部性定理 一般来说 缓存是可以减少很多磁盘IO的

而出现这种的情况 有可能是因为BufferPool开太小了 因为这个本质上是LRU的 开太小的话就什么都保存不了

三大日志刷盘

我们知道MySQL优秀的原因很大部分是由于其InnoDB所提供的几个特性，他们主要是通过三大日志和其他实现的。

而三大日志本质上就相当于是一个缓冲区和backup_plan

日志中的内容是随着数据的更新插入而修改的 在由于各种原因 日志会刷盘 就是将日志中的内容更新到磁盘上

此处以undo_log做一些额外的思考

其中保存的主要是数据的各种事务信息 方便在事务需要回滚的时候执行 又或者是支撑MVCC的实现

MVCC保证了可重复读下的数据逻辑一致性 对于长事务来说 有可能需要涉及到某个表中版本久远的数据

然后就会导致那一页在长事务完成前不会被回收

这里引出了另外一个问题：MySQL是如何判断对应的undo_log页能否被回收的呢？

这个本质上可以类比成垃圾回收进行理解，本质上就是判断undo_log中哪一部分是不可达的（可以回收的）

在这个场景下不存在循环引用这些问题 所以MySQL就为每一个页设定了一个计数器

记录当前页所涉及的并发事务的数量

在这个计数器为0时 代表可以回收 本质上就是将该页标记成为垃圾

继续类比垃圾回收的思路 具体的回收时机 本质上就是对应日志刷盘的时机

假如上述所说的长事务 同时牵扯到了很多版本久远的数据 提交的时候 没准就会同时牵扯到很多页同时变为垃圾

换句话来说 他未提交的时候 就会占有当前的很多资源 本质上 就会产生和垃圾回收中一样的问题（回收次数多 但是其实没回收到什么东西）

而这个的对策 两者是一样的 就只能在实践中进行性能调优了吧！