罗罗编程学习记录
本页内容 展开查看

05.2.1.2 InnoDB 内存结构与缓冲池

所属:全栈开发面试题库 / 05 数据库与存储 / 05.2 MySQL / 05.2.1 架构、InnoDB 与事务并发

覆盖范围

  • Buffer Pool 组成、LRU 两段改造与命中率
  • 脏页、Change Buffer、自适应哈希索引
  • 内存参数估算、预读、临时表空间与 NUMA 影响
  • 与 PostgreSQL shared_buffers 对照

高频题速览

编号 题目 难度 频率
05.2.1.2-001 InnoDB 内存结构组成 高级 ★★★★☆
05.2.1.2-002 Buffer Pool LRU 两段改造 高级 ★★★★★
05.2.1.2-005 Change Buffer 作用与场景 进阶 ★★★★☆
05.2.1.2-008 buffer_pool_size 怎么定 进阶 ★★★★★
05.2.1.2-016 与 PG shared_buffers 对照 进阶 ★★★★☆

内存结构主线

InnoDB 内存
  ├─ Buffer Pool:数据页 / 索引页缓存(LRU young + old)
  ├─ Change Buffer:二级索引页变更暂存
  ├─ Log Buffer:redo 日志缓冲
  ├─ Adaptive Hash Index:热点页哈希加速
  └─ 锁信息、字典、缓冲位图等内部结构

05.2.1.2-001 InnoDB 的内存结构包含哪些部分?

频率 ★★★★☆ | 难度 高级

一句话结论

InnoDB 内存主要由 Buffer Pool(数据/索引页)、Change Buffer(二级索引变更缓冲)、Log Buffer(redo 缓冲)、自适应哈希索引及锁与字典等内部结构组成;Buffer Pool 占绝大多数。

概念解释

  • Buffer Pool:缓存磁盘页,是读写热点数据的第一站,减少随机 IO。
  • Change Buffer:对不在缓冲池的二级索引页做变更暂存,稍后合并回磁盘。
  • Log Buffer:redo 记录的内存缓冲,按策略刷入 redo 文件。
  • AHI:对频繁访问的页内键值建立哈希,加速等值查找。

原理流程

  1. 读请求先查 Buffer Pool,命中直接返回;未命中读磁盘页入池。
  2. 写请求先改缓冲池页(脏页),变更记录进 redo;二级索引未命中页先进 Change Buffer。
  3. 后台线程按策略把脏页与 Change Buffer 合并写回磁盘。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW ENGINE INNODB STATUS\G   -- 看 Buffer Pool hit rate 与 change buffer 状态

常见误区

  • 误区:Log Buffer 用于缓存数据行。正解:它只缓冲 redo 日志记录,数据页在 Buffer Pool。
  • 误区:Change Buffer 缓存的是查询结果。正解:它缓冲二级索引页的写变更,不是结果缓存。

追问

  • 问:哪块内存对性能影响最大?答:Buffer Pool 命中率,容量不足会产生大量磁盘随机读。
  • 问:Change Buffer 只作用于哪种索引?答:二级索引;聚簇索引页通常直接定位主键,不经过 Change Buffer。

05.2.1.2-002 Buffer Pool 的 LRU 为什么改造成 young/old 两段?

频率 ★★★★★ | 难度 高级

一句话结论

朴素 LRU 容易被一次性大范围扫描(全表导出、批量统计)把热点页挤出缓冲池;InnoDB 把 LRU 分 young(头部,热点)与 old(尾部新入区)两段,新页先入 old 区,驻留足够时间并被再次访问才晋升 young,避免扫描污染。

概念解释

  • young 段:LRU 列表前部,长期热点页所在。
  • old 段:新读入页先放入的区域,比例由参数控制。
  • midpoint:young 与 old 的分界位置,新页插在分界处。
  • 晋升条件:old 区页在驻留时间阈值内被再次访问,才移动到 young 头部。

原理流程

  1. 未命中磁盘读入的页插入 LRU 中点(old 区头部)。
  2. 若短时间内被再次访问,晋升 young;否则留在 old 区逐步淘汰。
  3. 大扫描大量新页只占 old 区,不会冲掉 young 段的热点。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
SHOW VARIABLES LIKE 'innodb_old_blocks_time';
-- 状态变量观察 young/old 命中
SHOW STATUS LIKE 'Innodb_buffer_pool%';

常见误区

  • 误区:读入缓冲池的页直接放到 LRU 最前面。正解:先入 old 区,满足再次访问与时间条件才晋升。
  • 误区:两段 LRU 能彻底消除大查询影响。正解:它缓解污染,超大扫描仍会占满 old 区并产生大量 IO,需从查询本身控制。

追问

  • 问:old 区比例调大会怎样?答:更多新页停留,但热点保留空间被压缩;调小则新页更容易被淘汰。
  • 问:什么参数控制 old 区驻留时间?答:innodb_old_blocks_time,阈值内二次访问才晋升。

05.2.1.2-003 Buffer Pool 实例与命中率如何监控?

频率 ★★★★☆ | 难度 进阶

一句话结论

Buffer Pool 可拆为多个实例降低内部锁争用;命中率用逻辑读与物理读比值衡量,持续偏低说明缓冲池容量不足或存在扫描污染,需结合命中率、脏页比例与磁盘 IO 判断。

概念解释

  • 多实例:大缓冲池划分为多个实例,各有独立 LRU 与锁,减少单实例争用。
  • 命中率:1 - 物理读 / 逻辑读 的近似比值,反映内存承载热点的程度。
  • 物理读:缓冲池未命中引发的磁盘 IO 次数。

原理流程

  1. 状态变量累计逻辑读与物理读,定期计算命中率。
  2. 命中率长期偏低时先区分是容量不足还是一次性大扫描造成。
  3. 结合磁盘 IO 利用率与慢查询分布定位原因。

代码 / 命令

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- 逻辑读
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';          -- 物理读
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

常见误区

  • 误区:命中率 100% 才健康。正解:偶发未命中正常,持续高物理读与 IO 压力才是问题。
  • 误区:多实例数量越多越好。正解:实例数随缓冲池容量增长,过小内存拆多实例反而增加开销。

追问

  • 问:命中率低有哪两类根因?答:容量装不下热点;或大扫描把热点挤出缓冲池。
  • 问:如何看缓冲池里缓存了哪些表?答:可通过缓冲池页与表空间映射相关视图/状态辅助分析,具体视图名随版本以手册为准。

05.2.1.2-004 脏页是如何产生与刷盘的?

频率 ★★★★☆ | 难度 进阶

一句话结论

修改先在缓冲池完成,页标记为脏页(内存与磁盘不一致);脏页不立即刷盘,由后台线程按 checkpoint、redo LSN 推进与脏页比例策略异步写回,崩溃时靠 redo 重做已提交修改。

概念解释

  • 脏页(dirty page):内存已修改、磁盘尚未更新的数据页。
  • 刷盘线程:后台把脏页写回表空间文件。
  • checkpoint:推进可回收 redo 的点,保证崩溃恢复从最近 checkpoint 重做。

原理流程

  1. 事务修改缓冲池页并写 redo,页变为脏页。
  2. 后台根据 redo 产生速度、脏页比例与系统 IO 能力调度刷脏。
  3. 刷盘过快会挤占业务 IO,过慢会导致 redo 不可回收与恢复时间拉长。

代码 / 命令

SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';
SHOW VARIABLES LIKE 'innodb_flush_neighbors';

常见误区

  • 误区:提交事务时同步把脏页刷盘。正解:提交只保证 redo 落盘策略满足持久性,数据页刷盘异步进行。
  • 误区:脏页越多越危险。正解:少量脏页是正常缓冲行为,关键是刷盘速度能跟上 redo 产生速度。

追问

  • 问:为什么 redo 先于脏页刷盘?答:先保证日志可重做,数据页可异步刷;崩溃后用 redo 恢复。
  • 问:刷脏跟不上会出现什么?答:redo 空间不可回收、恢复时间变长,甚至提交被刷盘策略拖住。

05.2.1.2-005 Change Buffer 解决什么问题?适用哪些场景?

频率 ★★★★☆ | 难度 进阶

一句话结论

Change Buffer 缓存对不在缓冲池的二级索引页的插入/更新/删除变更,避免每次写都先把冷门二级索引页从磁盘读入;等该页被读访问时再合并变更,适合写多读少、二级索引更新分散的场景。

概念解释

  • 作用对象:非唯一二级索引页;唯一索引需立即校验唯一性,通常直接读页。
  • 记录内容:对索引项的插入、删除标记与更新操作。
  • 合并(merge):页被加载进缓冲池时,Change Buffer 记录合并到页上。

原理流程

  1. 写二级索引时目标页不在缓冲池,变更记入 Change Buffer。
  2. 页后续被读或后台合并任务加载,回放变更形成最新页。
  3. Change Buffer 占用 Buffer Pool 一部分空间。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_change_buffering';
SHOW ENGINE INNODB STATUS\G   -- Insert buffer 段看合并与待合并量

常见误区

  • 误区:Change Buffer 加速主键写入。正解:聚簇索引按主键顺序定位,不走 Change Buffer。
  • 误区:Change Buffer 里的变更对外不可见。正解:数据一致性由事务与页读取时合并保证,读路径会拿到合并后结果。

追问

  • 问:唯一二级索引为什么少用 Change Buffer?答:唯一性检查必须读现存索引项,无法先记变更后读页。
  • 问:什么场景应考虑关闭?答:以二级索引读为主、写入频繁命中页面时,Change Buffer 收益有限,具体开关取值以手册为准。

05.2.1.2-006 自适应哈希索引(AHI)是什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

AHI 由 InnoDB 对频繁被等值访问的索引前缀自动建立哈希索引,在 B+ 树查找之上缩短定位路径;它由引擎自动判断热点,无需手工创建,高并发等值点查场景收益明显。

概念解释

  • 自动构建:引擎监测页的访问模式,对热点前缀建立哈希条目。
  • 作用层级:在 B+ 树页内/前缀层面加速等值查找,不替代 B+ 树范围扫描。
  • 可控开关:可通过参数关闭,具体默认与行为随版本以手册为准。

原理流程

  1. 某索引前缀被反复等值查询,引擎评估后为其构建哈希。
  2. 后续等值查找先查 AHI,命中直接定位记录,减少 B+ 树层级遍历。
  3. 范围查询、排序仍走 B+ 树。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW ENGINE INNODB STATUS\G   -- 观察 AHI 命中率与等待

常见误区

  • 误区:AHI 就是手工创建的哈希索引。正解:它是引擎自动维护的内部结构,用户不能显式建。
  • 误区:AHI 加速范围查询。正解:哈希只支持等值,范围扫描仍走 B+ 树。

追问

  • 问:AHI 可能带来什么争用?答:热点前缀上哈希槽竞争与自旋等待,极端场景可评估关闭。
  • 问:AHI 与二级索引什么关系?答:建立在已有 B+ 树索引访问模式之上,不新增独立索引对象。

05.2.1.2-007 缓冲池内部还维护哪些哈希与位图结构?

频率 ★★☆☆☆ | 难度 高级

一句话结论

除 AHI 外,缓冲池还维护页哈希(按空间/页号快速定位缓冲帧)、空闲帧链表、脏页链表与 LRU 链表等内部结构,用于快速找到页、管理淘汰与刷盘。

概念解释

  • 页哈希:space + page number → 缓冲帧的映射,判断页是否在池内。
  • 空闲链表:记录可分配的空闲帧。
  • 脏页链表:记录已修改待刷盘的帧。

原理流程

  1. 读页先经页哈希查缓冲帧;命中返回,未命中从空闲链表取帧。
  2. 修改后帧进入脏页链表;LRU 链表管理冷热淘汰。
  3. 后台线程按脏页链表与 LRU 推进刷盘与回收。

代码 / 命令

-- 缓冲池页状态计数
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

常见误区

  • 误区:LRU 链表本身就能判断页是否在池内。正解:定位帧靠页哈希,LRU 只管冷热顺序。
  • 误区:脏页链表按 LRU 顺序刷盘。正解:刷盘顺序综合 redo LSN、脏页比例与 IO 策略。

追问

  • 问:空闲帧不足时怎么办?答:从 LRU 尾部淘汰冷页,脏页需先刷盘再释放。
  • 问:这些内部结构谁维护?答:InnoDB 后台与前台访问路径共同维护,用户不直接操作。

05.2.1.2-008 innodb_buffer_pool_size 怎么定?

频率 ★★★★★ | 难度 进阶

一句话结论

通用经验是专用数据库实例把 Buffer Pool 设为物理内存的 50%~75%,再按热点数据集大小、内存占用与操作系统/其他进程预留压测调整;不是越大越好,过大可能引发换页与内存压力。

概念解释

  • 数据集热点:缓冲池目标是常驻活跃数据与索引,而非全量冷数据。
  • 系统预留:需为操作系统、连接内存、临时表、线程与其他进程留内存。
  • 实例数:一台机器多实例时,各实例池大小之和不能超过可分配内存。

原理流程

  1. 先统计活跃数据集(热表索引+数据)规模与机器总内存。
  2. 按比例初设池大小,观察命中率、物理读与内存余量。
  3. 压测峰值负载,必要时拆多实例或迁移数据,避免触发 swap。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
-- 操作系统层另看 free / 内存占用与 swap

常见误区

  • 误区:把 90% 以上内存都给 Buffer Pool。正解:需预留操作系统与会话/临时结构内存,过度分配会触发换页。
  • 误区:缓冲池等于全量数据文件大小。正解:池缓存热点即可,冷数据可留磁盘。

追问

  • 问:运行时能否调整池大小?答:8.0 支持在线调整(受控方式),过程与限制以官方手册为准。
  • 问:池大小和实例数怎么配合?答:大池拆多实例降低争用,小池单实例即可,实例数随容量设定。

05.2.1.2-009 缓冲池预热与实例重启恢复怎么做?

频率 ★★★☆☆ | 难度 进阶

一句话结论

重启后缓冲池为空,冷启动阶段物理读高、性能下降;可通过保存/恢复缓冲池页列表、预热热点表或分批引导流量缩短冷启动窗口,具体参数与版本能力以手册为准。

概念解释

  • 冷启动:实例重启后热点页不在内存,请求先读磁盘入池。
  • 预热:重启后主动访问热点表/索引把页载入缓冲池。
  • 页列表持久化:部分版本支持记录 shutdown 时池内页集合,启动后按其恢复。

原理流程

  1. 正常关闭可记录缓冲池页集合;启动后按列表回载热点页。
  2. 或手动对核心热表执行扫描/统计类语句预热。
  3. 预热期间限流引导流量,避免冷池被突发打满 IO。

代码 / 命令

-- 待确认:缓冲池持久化相关参数名与默认值以目标版本官方手册为准
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump%';
SHOW VARIABLES LIKE 'innodb_buffer_pool_load%';

常见误区

  • 误区:重启后性能立刻恢复到稳态。正解:冷启动需重新积累热点页,存在性能爬坡期。
  • 误区:预热全库最稳妥。正解:全库扫描把冷页也带入池内,应只预热核心热点对象。

追问

  • 问:预热为什么要分批?答:一次性大量读盘会打满 IO,影响正常业务访问。
  • 问:高可用切换后冷启动怎么缓解?答:备库长期持有热点页副本,切换后池已有缓存,优于冷实例启动。

05.2.1.2-010 大页内存与 NUMA 对 Buffer Pool 有什么影响?

频率 ★★★☆☆ | 难度 高级

一句话结论

大页(HugePage)可降低页表翻译开销、提升大块内存访问效率;NUMA 架构下若缓冲池跨节点访问远端内存,延迟会上升,需绑定节点或调整内存策略,具体配置随操作系统与版本核对。

概念解释

  • 大页:用更大内存页减少 TLB miss,适合大缓冲池。
  • NUMA 亲和:CPU 与内存按节点划分,跨节点访问远端内存代价更高。
  • Swap 风险:缓冲池被换出到磁盘后访问性能急剧下降,应避免。

原理流程

  1. 操作系统为实例分配内存,NUMA 调度决定内存落在本地还是远端节点。
  2. 大页预分配后供实例锁定使用,减少动态分配与 TLB 压力。
  3. 监控 NUMA 远端访问比例与 swap 使用,必要时绑定进程节点。

代码 / 命令

-- 数据库内查看池大小与状态;NUMA/大页在操作系统层查看
SHOW VARIABLES LIKE 'innodb_large_prefix';  -- 注意:前缀索引长度参数,非大页
SHOW STATUS LIKE 'Innodb_buffer_pool_bytes_data';

常见误区

  • 误区:开了大页一定更快。正解:大页需系统层预分配且容量匹配,配置不当反而启动失败或浪费。
  • 误区:NUMA 只影响多机部署。正解:单台多路 CPU 机器上 NUMA 节点调度直接影响实例内存访问延迟。

追问

  • 问:如何判断存在 NUMA 远端访问?答:操作系统层查看节点内存命中与远端访问统计,数据库侧表现为随机读延迟抖动。
  • 问:为什么禁止数据库 swap?答:缓冲池页换出后再命中需磁盘换入,延迟远高于内存访问。

05.2.1.2-011 读请求在缓冲池未命中时的完整流程是什么?

频率 ★★★★☆ | 难度 进阶

一句话结论

未命中时引擎先从空闲链表或淘汰帧取缓冲帧,从磁盘把目标页读入缓冲池,更新 LRU 与页哈希,再在页内定位记录;若页是被淘汰的脏页,需先刷盘再复用帧。

概念解释

  • 缓冲帧分配:空闲帧优先,不足则淘汰 LRU 冷帧。
  • 页读取:按表空间+页号从表空间文件读取整页。
  • 页入池:建立页哈希映射,按 old/young 规则插入 LRU。

原理流程

  1. 页哈希未命中 → 找空闲帧或淘汰冷帧。
  2. 脏淘汰帧先刷盘,再从磁盘读新页覆盖。
  3. 页入池后执行页内记录查找,返回行给执行器。

代码 / 命令

-- 物理读计数反映未命中频率
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

常见误区

  • 误区:未命中只读需要的那一行。正解:IO 单位是整页,一次读入整页供页内多行复用。
  • 误区:淘汰干净帧即可直接复用。正解:脏帧必须先刷盘保证磁盘有最新版本,才能覆盖。

追问

  • 问:页入池先进哪一段?答:old 段,满足晋升条件才进 young 段,见 05.2.1.2-002。
  • 问:连续读多页为什么有预读效果?答:磁盘顺序读相邻页成本低,引擎可触发预读把后续页提前入池。

05.2.1.2-012 预读机制:线性预读与随机预读是怎么回事?

频率 ★★☆☆☆ | 难度 高级

一句话结论

预读是引擎预判后续会访问相邻页而提前批量读入缓冲池:线性预读按一段连续页被访问的比例触发,随机预读按同一区中被读页数量触发;用于把随机读转成顺序批量读,具体阈值与版本行为待确认以手册为准。

概念解释

  • 线性预读(read-ahead):检测到某区间连续页被较多访问,提前读后续页。
  • 随机预读:同一区中已有较多页入池,把该区剩余页批量读入。
  • 目的:减少多次小块随机 IO,利用磁盘顺序读优势。

原理流程

  1. 引擎统计页访问模式,判断是否命中预读条件。
  2. 满足条件时一次性读入相邻/同区多页。
  3. 预读页进入 old 段,未被再次访问会被较快淘汰。

代码 / 命令

SHOW VARIABLES LIKE 'innodb_read_ahead%';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_ahead%';

常见误区

  • 误区:预读总是有益。正解:误判会把无用页读入占用池空间,预读命中率低时反而增加 IO。
  • 误区:预读由应用层控制。正解:预读是引擎内部行为,应用只能通过查询模式间接影响。

追问

  • 问:顺序扫描为什么常伴随预读?答:连续访问模式容易命中线性预读条件,批量读相邻页。
  • 问:如何判断预读是否有效?答:对比预读页数与实际再次访问比例,低命中说明预读过度。

05.2.1.2-013 页压缩与透明压缩概览是怎样的?

频率 ★★☆☆☆ | 难度 高级

一句话结论

InnoDB 提供页/表压缩手段减少磁盘占用与 IO 读取量,代价是压缩解压 CPU 开销;适用冷热分明、磁盘紧张的场景,具体压缩格式、参数与版本支持范围以官方手册为准,标待确认处需在目标版本实测。

概念解释

  • 压缩表/页:存储时压缩页数据,读取时解压。
  • 收益:降低磁盘与缓冲池占用,单位页可承载更多行。
  • 代价:CPU 解压开销,压缩参数不当可能影响写性能。

原理流程

  1. 建表时指定压缩格式/参数。
  2. 写入页按格式压缩落盘;读入缓冲池后解压供访问。
  3. 评估压缩比、CPU 与读写延迟后再决定是否启用。

代码 / 命令

-- 待确认:目标版本支持的压缩子句与参数名以官方手册为准
-- CREATE TABLE t (...) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

常见误区

  • 误区:压缩一定提升性能。正解:主要收益是空间,CPU 紧张时解压开销可能抵消 IO 收益。
  • 误区:压缩后缓冲池存的是压缩数据。正解:入池页通常解压后使用,空间收益主要体现在磁盘与落盘传输。

追问

  • 问:哪些列适合压缩?答:重复度高、文本/冗余多的数据压缩比好,高熵随机数压缩有限。
  • 问:压缩表对 DDL 有什么额外注意?答:重建与碎片整理时需评估压缩重算开销。

05.2.1.2-014 8.0 数据字典与元信息在内存中如何组织?

频率 ★★★☆☆ | 难度 高级

一句话结论

8.0 起元数据集中存于 InnoDB 内部数据字典表,内存中缓存表结构、列、索引等字典信息;不再依赖旧版散落的 .frm 文件,结构变更纳入原子 DDL 管理。

概念解释

  • 数据字典表:系统内部表记录库、表、列、索引、统计等元数据。
  • 字典缓存:实例内存缓存常用对象定义,避免重复读系统表。
  • 原子 DDL:字典变更与数据变更在同一可恢复流程中提交。

原理流程

  1. 打开表时加载/缓存其字典定义。
  2. DDL 修改字典表并走原子提交。
  3. 实例启动从数据字典恢复全部元数据。

代码 / 命令

-- 系统字典相关信息可通过 information_schema 与系统视图查询
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'your_db' LIMIT 20;

常见误区

  • 误区:8.0 表结构还存在 .frm 文件里。正解:8.0 数据字典内化,.frm 历史文件不再是结构来源。
  • 误区:字典缓存陈旧会导致结构感知错误。正解:DDL 提交后失效相关缓存,会话重新打开表获取新定义。

追问

  • 问:原子 DDL 依赖什么保证?答:数据字典事务化,崩溃可回滚,详见 05.2.1.1-014。
  • 问:字典表用户能直接改吗?答:不能直接写内部字典表,结构变更走标准 DDL。

05.2.1.2-015 内部临时表落在哪个表空间?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

内存临时表使用内部内存引擎构建;超过 tmp_table_size/max_heap_table_size 后转为磁盘临时表,落在临时表空间中,会话结束自动释放。

概念解释

  • 内存临时表:会话私有,在内存中组织分组排序结果。
  • 磁盘临时表:超阈值后落盘,占用临时表空间文件。
  • 生命周期:随语句结束或会话结束释放,不残留用户数据。

原理流程

  1. 优化器判定需要临时表,先在内存构建。
  2. 估计/实际超阈值后转换为磁盘临时表。
  3. 语句结束清理临时表与占用空间。

代码 / 命令

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW STATUS LIKE 'Created_tmp%tables';

常见误区

  • 误区:磁盘临时表会写到用户业务表空间。正解:落在实例临时表空间,与业务表分离。
  • 误区:临时表占用的内存不计入缓冲池。正解:内存临时表使用会话私有内存,不占 Buffer Pool 帧。

追问

  • 问:如何减少磁盘临时表?答:缩小结果集、让分组排序走索引、调大临时表内存阈值(需评估总内存)。
  • 问:内部临时表与 CREATE TEMPORARY TABLE 一样吗?答:前者是引擎/Server 内部自动构建,后者是用户显式创建的会话临时表。

05.2.1.2-016 InnoDB 缓冲池与 PostgreSQL shared_buffers 怎么对照?

频率 ★★★★☆ | 难度 进阶

一句话结论

两者都是实例内的数据页共享缓存:MySQL 称 Buffer Pool,PG 称 shared_buffers;定位与作用高度对应,都缓存热点页、管理脏页刷盘,但内存组织、LRU 策略与连接模型不同,参数估算思路可互相参照。

概念解释

  • 对应关系:Buffer Pool ↔ shared_buffers,都是页缓存主体。
  • 差异:PG 另有 work_mem 服务排序/聚合内存,MySQL 临时表内存由 tmp_table_size 等控制。
  • 经验值:两者都建议占专用实例内存的较大比例,但需结合各自默认与官方建议压测。

原理流程

  1. 读页先查共享缓存,未命中读磁盘入缓存。
  2. 写改缓存页为脏页,后台异步刷盘。
  3. 命中率与物理读都是两边核心监控指标。

代码 / 命令

-- MySQL
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- PostgreSQL
SHOW shared_buffers;

常见误区

  • 误区:两者参数可以按相同绝对值照搬。正解:实例用途、连接模型与其他内存区不同,需分别压测。
  • 误区:PG shared_buffers 越大越好,和 OS 缓存无关。正解:PG 依赖操作系统页缓存作为二级缓存,shared_buffers 并非一味调大。

追问

  • 问:PG 为什么还依赖 OS cache?答:shared_buffers 之外,文件系统页缓存仍承担部分读缓存。
  • 问:两边脏页刷盘机制差异在哪?答:PG 由 checkpoint 与后台 writer 推进,MySQL 由 InnoDB 刷脏线程与 checkpoint 管理,详见 05.3.1.5。

05.2.1.2-017 缓冲池命中率低应该怎么排查?

频率 ★★★★☆ | 难度 进阶

一句话结论

先算命中率与物理读量,再区分是容量不足(热点持续装不下)还是扫描污染(大查询批量读冷页);结合慢查询、脏页比例与磁盘 IO 定位,分别对应扩容、优化大查询或调整缓冲策略。

概念解释

  • 容量不足特征:热点表总量超过池容量,物理读持续偏高且覆盖核心热表。
  • 扫描污染特征:偶发大表扫描后命中率骤降,old 区被冷页填满。
  • 伴随信号:磁盘 IO 利用率高、慢查询增多、脏页刷盘压力大。

原理流程

  1. 统计逻辑读/物理读趋势,看命中率是持续低还是骤降型。
  2. 定位产生大量页访问的 SQL(慢日志、计划扫描范围)。
  3. 判断根因后:扩容缓冲池、改造大查询、限制扫描范围或调整预读/LRU 参数。

代码 / 命令

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- 结合慢日志找出扫描行数大的 SQL

常见误区

  • 误区:命中率低就立刻加大缓冲池。正解:先排除大扫描污染,否则扩容后仍会被冷页占满。
  • 误区:命中率是唯一性能指标。正解:需结合物理 IO、响应时间与锁等待综合判断。

追问

  • 问:如何识别某条 SQL 把冷页带入池中?答:看其计划扫描范围与执行前后池内页/物理读变化。
  • 问:扩容后命中率仍低说明什么?答:可能热点数据集继续增长,或存在持续大范围扫描未治理。

05.2.1.2-018 批量大查询把冷数据带入缓冲池有什么影响?

频率 ★★★☆☆ | 难度 进阶

一句话结论

大范围扫描读入的冷页占据 old 区并逐步挤占热点位置,导致热点页被淘汰、后续正常请求物理读上升;影响程度取决于扫描规模、LRU 保护与池容量,应避免在业务高峰跑全量扫描。

概念解释

  • 冷页污染:只被访问一次的页占用缓冲帧,淘汰真正热点。
  • old 区缓冲:两段 LRU 降低冷页进入 young 段的概率,但不能完全阻止占用。
  • 二次访问缺失:冷页不再被读,最终被淘汰,占用期间挤压其他页。

原理流程

  1. 批量扫描逐页读入,冷页进 old 区。
  2. 扫描持续大量读页,old 区饱和并推动 LRU 尾部淘汰。
  3. 热点页若在 old 区被挤出,后续访问转为物理读。

代码 / 命令

-- 定位扫描行数大的语句
EXPLAIN <批量查询>;
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

常见误区

  • 误区:只读不写的大查询不影响数据库性能。正解:它消耗 IO 与缓冲池空间,间接拖慢在线读写。
  • 误区:两段 LRU 可以完全隔离大扫描。正解:只延缓冷页晋升,old 区仍会被占满。

追问

  • 问:大表统计/导出应该何时跑?答:业务低峰,分批限速,必要时在只读副本上执行。
  • 问:如何降低扫描对缓冲池的冲击?答:缩小扫描范围、分批处理、避免 SELECT *,评估专用分析副本。