MySQL中BufferPool

Table of Contents generated with DocToc

MySQL InnoDB

前置文章

Buffer Pool

  • 索引页、数据页、锁、undo页、自适应哈希索引和插入缓冲页

  • Free List

  • Flush List

  • LRU List（预读失效、Buffer Pool污染）

  • master thread loop

  • write ahead log

主要线程和职能

  • master thread：核心的后台线程，负责将缓冲区中的数据异步刷新到磁盘， 刷新脏页、merge insert buffer和回收undo页等

  • io thread：InnoDB中存在大量AIO，io thread用于处理这些异步请求的回调逻辑

  • purge thread：事务被提交后，undo log不再需要，purge thread用于回收已经被分配并使用的undo log页

  • page cleaner thread：刷新脏页

关键特性

  • 插入缓冲区：即insert buffer/change buffer，InnoDB针对非唯一的辅助索引写入专门的优化，会将写入操作先写入到buffer中

    • https://blog.csdn.net/it_lihongmin/article/details/115315120

    • insert buffer底层是一颗B+树，所有表共用一颗

    • Merge Insert Buffer的时机：1、当辅助索引页被读取到缓冲区时 2、Insert Buffer BitMap检测到该页已无可用空间时 3、master thread定时merge

  • 两次写：double write，在将数据页写到表时，这时发生宕机，16KB的页可能只写了部分，这时就发生了部分写失效，此时页发生损坏，即使有重做日志，也没有意义 InnoDB通过两次写来解决这种部分写失效的问题，将需要写入的页copy到double write buffer和共享表磁盘空间，由于都是顺序写，开销并不大，后再将buffer中的页写入表空间文件中

  • 自适应哈希索引：InnoDB自动根据访问的频率和模式来为某些热点页建立哈希索引

    • where a = xx / where a = xx and b = xxx 若交替以上述模式（where条件）执行，不会建立索引

    • 以该模式访问了100次

    • 页通过该模式访问了N次，其中N=页中记录条数/16

  • 异步IO：即AIO，AIO的好处就是可以非常方便的合并多个IO操作，如分别读取多个相邻页时可以合并为一次IO，读取一次。

  • 刷新邻接页：刷新脏页时，检查是否有相邻的脏页，如果存在就合并刷新到磁盘

Double Write

Buffer Pool简单小结

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。

Innodb 通过三种链表来管理缓页：

Free List （空闲页链表），管理空闲页； Flush List （脏页链表），管理脏页； LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。； InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。 当**「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」**时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。 可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。

在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

参考博客：https://xiaolincoding.com/mysql/buffer_pool/buffer_pool.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E8%A6%81%E6%9C%89-buffer-pool

锁

  • S锁：读锁

  • X锁：写锁

  • IS锁：意向读锁

  • IX锁：意向写锁

  • Record Lock

  • Gap Lock

  • Next Key Lock

日志

  • redo log：是Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复。顺序写

    • 刷新：master thread每秒刷新、事务提交时、redo缓冲区可用空间小于一半时

  • undo log：是Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。随机读写

    • 当事务提交时：1、会将undo log页放入列表中，以供之后的purge操作 2、判断undo页是否可重用（可用空间是否大于1/4），若可以则分配给下一个事务使用

  • binlog：是 Server 层生成的日志，主要用于数据备份和主从复制。

索引

索引类型

  • 聚簇索引：每个表都会有一个聚簇索引，如果表存在主键就是用主键顺序，如果不存在主键会生成隐式主键。叶子节点中包含行数据。

  • 非聚簇索引（辅助索引/二级索引）： 需要通过非聚簇索引的B+树找到对应节点，节点中包含主键信息，根据主键信息再通过聚簇索引找到对应的行记录，这个过程叫做回表。当查询信息只包含主键或索引列时，这时不会回表，这叫做索引覆盖。

  • 联合索引

  • 覆盖索引

  • 自适应哈希索引：由InnoDB优化自行创建，无法手动创建

创建索引的原则

  • 列区分度较大

  • 经常会被使用到的列

  • 更新特别频繁的列不建议创建索引

  • 尽量控制索引个数，索引不是越多越好

  • 尽量使用短索引

索引失效的场景

Hash索引：

  • 只用于使用=或<=>操作符的等式比较，只适用于key-value查询

  • Hash索引不适用于范围查询，例如<、>、<=、>=这类操作

B-Tree索引：

  • 以%开头的LIKE查询不能利用B-Tree索引

  • 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，要将字符串用引号引起来索引才生效

  • 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则，是不会使用复合索引的，右边可以没有，左边和中间不能缺

  • 如果MySQL估计使用索引比全表扫描更慢，则不使用索引

  • 用or分割开的条件，每一列都要有索引才能使用索引

整理过程中问题

当行记录数据大小大于页大小（16KB）时，如何存储？

行记录在数据页中的存储结构

https://mp.weixin.qq.com/s/A5gNVXMNE-iIlY3oofXtLw

一个页中至少需要两行数据，溢出的数据存储在新的一个页里面，留出部分字节存储溢出页信息

select如何找到mvcc中具体版本的数据

通过read view和undo log，readview中记录了当前行记录已提交和未提交的事务，根据事务隔离级别，选择对应版本的undo log，生成具体版本的行记录 http://imodou.com.cn/article/MySQL%20MVCC%E5%BA%95%E5%B1%82%E5%8E%9F%E7%90%86%E8%AF%A6%E8%A7%A3.html#_3-%E7%89%88%E6%9C%AC%E9%93%BE

undo log存储在哪

存储在共享表空间， rollback segment，一个rollback segment对应1024个undo log segment，在每个undo log segment中进行undo页的申请

SSD和机械磁盘差异

select是否会触发隐式事务

insert/update/delete会触发隐式事务，select不会

direct io是mmap吗？和其他常规io差异是零拷贝吗？

mmap是把page cache地址空间映射到用户空间，像操作应用层内存一样写文件，省去了系统调用的开销

direct io是避开了page cache，数据直接写磁盘数据块

https://www.cnblogs.com/bandaoyu/p/16752377.html

mysql写时如果数据页没在缓冲区，会先将数据页加载到缓冲区再写，还是直接写?

上一页2023 04下一页2022 12

最后更新于

这有帮助吗？