InnoDB Buffer Pool 与索引

• 经典 LRU 的缺陷（全表扫描污染缓存、顺序读冲刷热点页）
• midpoint insertion 与 young/old 区（shorten the list 双向链表）
• innodb_old_blocks_time 与 innodb_old_blocks_pct 参数的作用

InnoDB 的 Buffer Pool 没有使用朴素的 LRU，而是采用改进的 LRU（Modified LRU）来避免全表扫描/大范围顺序读把真正的热点页挤出缓存。它将整个 LRU 链表按位置分为 young 区（链表头部，约 63%）和 old 区（链表尾部，约 37%，由 innodb_old_blocks_pct 控制）。新读入的页通过 midpoint insertion 插入到 young 与 old 的分界点（midpoint），而不是插入到链表头部。这样冷数据（如全表扫描的页）不会立刻占据头部，而是先进入 old 区；只有当页面在 old 区停留超过 innodb_old_blocks_time 毫秒后再次被访问，才会被提升（promote）到 young 区（链表头部）。当 new 页从 old 区提升到 young 区时，同时会从链表尾部淘汰一个最旧的页。这套机制保证了被频繁访问的真正热点页停留在头部，而被一次性扫描的页快速从 old 区被淘汰。

该设计是"分代 LRU"思想，用时间窗口（old 区停留时间）来区分"短暂访问"与"持续热点"，从而避免顺序扫描造成的缓存污染。理解 midpoint 位置、提升条件（old 区停留超时被再次访问）与淘汰策略（old 区尾部淘汰）是回答重点。

• Buffer Pool 缓存的数据页、索引页、undo 页、插入缓冲、锁信息等
• 页的读入（从磁盘加载到内存）与写回（flush 脏页）流程
• 通过缓冲池大幅减少磁盘 IO、提升命中率

Buffer Pool 是 InnoDB 在内存中的一块缓冲区域，用于缓存数据页（data pages）、索引页（index pages）、undo 页、Change Buffer、自适应哈希索引、锁信息（lock info）等。因为 InnoDB 以页（page，默认 16KB）为最小读写单位，B+ 树索引的节点和行数据都以页存储。当查询需要访问某页时，InnoDB 先到 Buffer Pool 中查找，若不在则从磁盘读取该页放入缓冲池（Read），并缓存在 LRU 链上；对页的修改只更新内存中页的副本并标记为脏页（dirty page），同时写 redo log 保证持久性，脏页在合适时机（通过强制刷盘、LRU 淘汰、后台线程、checkpoint 等）异步写回磁盘（Flush）。因此 Buffer Pool 的大小直接决定内存可容纳多少页，从而决定减少了多少磁盘 IO。

核心是"内存缓存 + 异步回写 + redo 保证持久性"。页的读写在内存中完成，磁盘写被延迟批量进行，这种机制既提高性能又保证崩溃安全。回答应强调"页"为最小单位、脏页与 redo 的配合。

• Redo Log 是 InnoDB 存储引擎层日志，Binlog 是 Server 层归档日志
• 两阶段提交的 prepare 与 commit 阶段
• 崩溃时依据 redo 中记录的 XID 与 binlog 中 XID 判断事务是否提交

Redo Log 是 InnoDB 引擎层的物理日志，记录对页的物理修改，用于崩溃恢复（保证已提交事务不丢失、未提交事务回滚）；Binlog 是 MySQL Server 层的逻辑日志，记录逻辑操作，用于主从复制与 PITR 时间点恢复。由于两者记录时机不同，若在提交过程中崩溃可能导致"redo 已记录但 binlog 未记录"或反之，从而造成引擎与复制数据不一致。因此 MySQL 采用两阶段提交：第一阶段 prepare，InnoDB 把 redo 刷到磁盘并标记为 prepare 状态；第二阶段 commit，先写 binlog（并 fsync），再提交 redo 事务（标记为 commit 状态）。崩溃恢复时，InnoDB 扫描 redo 中 prepare 状态的事务，若发现 binlog 中已有对应的 XID（即 binlog 已写入），则提交该事务；否则回滚。这样保证"binlog 与 redo 里的提交状态一致"，从而崩溃后主从与数据一致。

两阶段提交的本质是用 binlog 的 XID 与 redo 的 prepare/commit 状态做交叉校验，决定崩溃恢复时是提交还是回滚。顺序是"先 redo prepare → 再写 binlog → 再 redo commit"。这是面试中极其高频的考点，需讲清两阶段提交的顺序。

• Redo Log 是物理日志，记录页的物理修改，用于崩溃恢复
• WAL（Write-Ahead Logging）先写日志再写数据页
• 写入路径：用户事务 → log buffer → 刷盘（fsync）→ redo log 文件

Redo Log 是 InnoDB 的物理重做日志，记录了对数据页的物理修改（如"在页 X 的偏移 Y 写入某值"），其核心作用是保证崩溃恢复：即使脏页尚未刷盘，只要 redo 已持久化，崩溃重启后就能重放 redo 把已提交事务的修改恢复到数据页中，实现"已提交不丢失"。它遵循 WAL（Write-Ahead Logging）原则，即先写 redo log 再写数据页。写入路径为：事务修改数据页时，先把对应的 redo 记录写入内存中的 log buffer；当满足条件（如 innodb_flush_log_at_trx_commit=1 时事务提交即触发 fsync）时，将 log buffer 中的记录刷入磁盘上的 redo log 文件（一般是 ib_logfile 系列）。redo log 文件是固定大小、循环使用的，通过 LSN（Log Sequence Number）来标记写入位置，并通过 checkpoint 记录已刷入数据页的 LSN，从而允许回收（覆盖）旧日志。当 redo 文件快满而脏页尚未刷盘时，会触发强制刷脏（checkpoint）以推进 checkpoint，避免日志被覆盖导致数据丢失。

回答要抓住"WAL + 崩溃恢复 + 循环写 + LSN/checkpoint"四个要点。redo 允许"先改内存、后刷盘"，从而把频繁的随机磁盘写转成顺序的日志写，既保证性能又保证持久性。innodb_flush_log_at_trx_commit 决定提交时是否立即 fsync。

• Undo Log 记录数据变更前的旧镜像，用于回滚与 MVCC 版本链
• roll pointer 串起 undo 版本链，供 Read View 读取历史版本
• purge 线程清理不再被任何事务引用的 undo 记录与已删除行

Undo Log（回滚日志）保存了数据被修改前的旧版本（旧镜像），主要有两个作用：其一，事务回滚时，根据 undo 记录把数据恢复到修改前的状态，实现原子性；其二，支撑 MVCC，每条记录通过 roll pointer 指向其 undo 记录，形成版本链，配合 Read View 在读取时按 trx_id 判断可见性，从而读取到符合隔离级别要求的旧版本数据，实现快照读。undo 记录分为 insert undo（插入时生成，仅事务自身需要回滚插入，删除时可直接清理）和 update undo（更新/删除时生成，可能被其他并发事务的快照读引用）。当某个旧版本不再被任何活跃事务的 Read View 需要时，后台 purge 线程负责清理这些 undo 记录，并释放被删除行占用的空间、回收 undo 表空间。purge 是异步进行的，避免 undo 无限膨胀。

Undo 是 MVCC 的物理基础，版本链 + Read View 是逻辑实现，purge 是清理机制。回答需区分 insert undo 与 update undo 的清理差异，并说明 purge 协调"历史版本保留"与"空间回收"的矛盾。

• Change Buffer 缓存对非唯一二级索引的插入/更新/删除操作，待页读入时合并
• 减少索引页的随机读写，提升写性能
• 唯一索引需实时检查唯一性约束，无法延迟合并

Change Buffer（插入缓冲，旧称 Insert Buffer）是 InnoDB 用来优化二级索引写操作的特殊数据结构。当对非唯一二级索引执行插入、更新、删除时，如果目标索引页不在 Buffer Pool 中，InnoDB 不会立即把数据页读入内存进行修改，而是把这些变更（change）先缓存在 Change Buffer（位于内存的系统表空间，并可通过后台线程持久化到磁盘）中，记为"待合并的变更"。当该索引页后续被读入 Buffer Pool 时，再将这些缓存的变更合并（merge）到页上，从而把多次随机写合并为一次顺序写，显著减少随机磁盘 IO。唯一索引（包括主键）无法使用 Change Buffer，因为唯一索引在写入时必须立即检查唯一性约束——若延迟合并，就无法在插入时发现重复键冲突，因此唯一索引的写入必须实时定位到目标页并校验，无法享受延迟合并的优化。

核心是"以空间换时间、以延迟换随机 IO 减少"。非唯一索引没有唯一性约束这个实时性要求，所以可以延迟合并；唯一索引的约束检查要求实时性，故被排除。回答需指出 Change Buffer 只适用于"非唯一"二级索引。

• AHI 是 InnoDB 自动为高频访问的索引页建立的哈希索引，加速等值/前缀查找
• AHI 位于 Buffer Pool 内，由 InnoDB 根据访问频率自动维护
• 高并发写入时 AHI 的哈希 latch 竞争会成为瓶颈，反而降低性能

自适应哈希索引（Adaptive Hash Index，AHI）是 InnoDB 的一个优化特性：当二级索引或聚簇索引的某些页被频繁访问（通常是等值或固定前缀查找）时，InnoDB 会自动监测访问模式，为这些热点页建立哈希索引（把索引键值映射到页地址），从而把 B+ 树的多层查找降为 O(1) 的哈希查找，加速等值查询。AHI 是"自适应"的，不需要人工创建，由 InnoDB 根据访问频率自动构建和更新，且存放在 Buffer Pool 内、只对内存中的页生效。然而在高并发写入场景下，AHI 的哈希表需要使用全局的 latch（锁）来保护，插入和删除操作频繁更新 AHI 会造成锁竞争（latch contention），当竞争超过收益时反而拖慢性能。因此对于高并发写入、热点页频繁变化的系统，常建议通过设置 innodb_adaptive_hash_index=OFF 关闭 AHI，以消除哈希锁竞争。

AHI 是"读加速、写增加开销"的折中。回答应说明其自动构建机制、位于内存、只服务等值查找，并解释高并发写入时哈希 latch 竞争成为瓶颈，从而解释为何建议关闭。

• 脏页刷新策略：根据 redo 生成速率与 checkpoint 年龄动态调整刷盘力度
• 避免 redo log 写满导致的用户事务阻塞
• innodb_io_capacity 定义刷盘 IO 上限，innodb_max_dirty_pages_pct 定义脏页比例上限

InnoDB 采用自适应刷脏（adaptive flushing）策略：后台刷脏线程会根据 redo log 的生成速度（写入速率）和 checkpoint 年龄（当前 LSN 与已刷盘 checkpoint LSN 的差距）动态调整脏页刷新的频率与力度。当 redo 生成快、checkpoint 年龄大（即 redo 即将被写满而脏页尚未刷盘）时，InnoDB 会主动提高刷盘速率，把脏页尽快刷盘以推进 checkpoint，从而避免 redo log 文件被写满。如果 redo 写满，用户事务将被迫阻塞等待刷盘，这是性能灾难。innodb_io_capacity 定义了 InnoDB 在刷盘时能够使用的磁盘 IO 容量上限（每秒最大 IO 次数），是自适应刷脏的"天花板"；innodb_max_dirty_pages_pct 定义 Buffer Pool 中脏页允许占用的最大比例（默认 75%），当脏页比例超过该阈值时，会触发强制刷脏。两者配合自适应刷脏策略，共同控制刷盘节奏。

刷脏的核心矛盾是"刷太慢 redo 写满阻塞，刷太快浪费 IO"。自适应刷脏以 redo 生成速率和 checkpoint 年龄为信号动态调节，参数 innodb_io_capacity 是 IO 上限、innodb_max_dirty_pages_pct 是脏页比例阈值。回答需讲清信号与参数的关系。

• 聚簇索引即主键索引，叶子节点存整行数据
• 数据物理上按主键逻辑顺序排列（B+ 树叶子节点链表）
• 主键查询快，但插入主键乱序时可能页分裂

InnoDB 采用聚簇索引（Clustered Index）组织数据，即表的主键索引就是聚簇索引，其 B+ 树叶子节点直接存储整行数据（聚簇索引的叶子节点就是真正的数据行）。也就是说，数据行物理上按照主键的逻辑顺序（B+ 树叶子节点通过指针双向/单向链成有序链表）排列存储。因此：按主键等值或范围查询非常高效（一次 B+ 树查找即可拿到整行，无需回表）；每张表只能有一个聚簇索引（因为数据只能按一种顺序存储）。如果表没有主键，InnoDB 会选择一个非空唯一索引作为聚簇索引；若都没有，则隐式生成一个 6 字节的 rowid 作为聚簇索引。由于数据按主键顺序存储，当插入的主键不是递增的（乱序插入）时，会频繁触发页分裂（page split）和页重排，导致额外的 IO 和空间碎片，因此通常建议使用自增主键。

聚簇索引是"InnoDB 表即索引"的体现，叶子节点即数据。回答需强调"叶子节点存整行"、"一表一聚簇"、"主键顺序决定物理顺序"以及乱序主键插入的页分裂代价。

• 二级索引叶子节点存的是索引列值 + 主键值
• 查找过程：先查二级索引得主键，再回聚簇索引取整行（回表）
• 回表产生额外 IO，覆盖索引可避免回表

二级索引（Secondary Index，非主键索引）的叶子节点存储的是索引列的值和对应行的主键值（而不是整行数据）。当通过二级索引查询时，查找过程分两步：第一步，在二级索引的 B+ 树中按索引列值定位，找到符合条件的叶子节点，得到主键值；第二步，用这个主键值再到聚簇索引完成的 B+ 树中查找，取出整行数据，这个过程称为"回表"（bookmark lookup / table lookup）。因为二级索引本身不包含所有列，只有回到聚簇索引才能拿到完整行。回表会带来额外的随机 IO（两次索引树查找）。如果查询所需的列全部包含在二级索引中（覆盖索引），则无需回表，直接从二级索引即可返回结果，从而避免回表开销。

二级索引是"索引列 + 主键"的复合索引结构，查找必然要回表（除非覆盖）。回答需讲清两步查找、回表的原因（叶子不含整行）以及覆盖索引如何消除回表。

• 覆盖索引指二级索引包含查询所需的所有列，无需回表
• InnoDB 二级索引叶子节点含主键，可用覆盖索引进行"索引覆盖扫描"
• 好处：减少 IO、避免回表随机读

覆盖索引（Covering Index）是指一个二级索引包含（覆盖）了查询所需的所有列，使得查询只需在二级索引的 B+ 树上就能完成，无需回表到聚簇索引。在 InnoDB 中，二级索引的叶子节点不仅包含索引列，还包含主键列，因此当查询选择的列是索引列或主键列时，就可以利用覆盖索引直接返回结果。例如建了复合索引 (a, b)，查询 SELECT a, b FROM t WHERE a=1 时，a、b 都在索引中，InnoDB 直接从二级索引扫描命中即可，避免了回表去聚簇索引读取整行，从而减少随机 IO、提升查询性能。优化器也会优先选择覆盖索引（Extra 列显示 Using index）。需要注意的是，覆盖索引需要额外占用空间，需在查询性能与存储成本之间权衡。

覆盖索引的本质是"让二级索引独立满足查询"，避免二次查找。回答应强调 InnoDB 二级索引含主键这一特性使得主键列可被覆盖，并通过 Using index 判断优化器是否走覆盖索引。

• 原子 DDL 使 DDL 操作要么全部成功要么全部回滚，不再出现部分完成
• 通过 redo log 与 undo log 记录 DDL 元数据与操作
• 影响的 DDL 类型：表 DDL、索引 DDL、表空间 DDL 等

MySQL 8.0 引入了原子 DDL（Atomic DDL）特性，使得 DDL 操作（如 CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX、DROP DATABASE 等）具备原子性：整个 DDL 要么完整成功，要么在失败时回滚到初始状态，不再出现"表结构改了但部分数据丢失"或"索引创建了一半"的中间状态。其实现原理是：InnoDB 在 MySQL 8.0 中为 DDL 的元数据（数据字典表）与操作持久化使用 redo log 和 undo log，将 DDL 操作作为一个整体事务来记录，崩溃时能依据这些日志进行回滚或重放。同时，MySQL 8.0 把数据字典重构为 InnoDB 表存储，使 DDL 变更可以像普通事务一样记录 undo 以便回滚。原子 DDL 还解决了以往 DDL 崩溃后遗留孤儿表/孤儿索引、以及 binlog 与数据字典不一致等问题。注意，原子 DDL 只针对 InnoDB 表，不支持所有语句（如某些非 InnoDB 操作）。

原子 DDL 的核心是"把 DDL 当事务处理"，借助 redo/undo 与数据字典的 InnoDB 化实现崩溃回滚。回答应说明它解决了"半完成 DDL"与"binlog 与字典不一致"的问题。

• 并行复制（MTS）的并行度依据：LOGICAL_CLOCK（逻辑时钟）与 WRITESET（写集合）
• LOGICAL_CLOCK：基于事务提交时间的前后依赖关系
• WRITESET：基于事务修改的行集合是否冲突判断可并行

MySQL 并行复制（Multi-Threaded Slave，MTS）旨在让从库并行执行事务以追赶主库，其并行度的判定主要有两种方式：LOGICAL_CLOCK 和 WRITESET。LOGICAL_CLOCK（逻辑时钟）模式下，从库认为主库中"提交时间接近"的事务之间没有依赖冲突，可以并行执行——它依据事务在 binlog 中的提交序列（commit sequence number）来划分并行组，允许同一批次内的事务并行重放，但两个事务若存在"后来的事务读取了先前事务写入数据"的依赖则不能并行。WRITESET（写集合）模式更进一步，它利用事务修改的行集合（writeset）来判断是否真正存在冲突：若两个事务写入的行集合没有交集，则即使它们逻辑上没有先后依赖，也可以并行执行，从而大幅提升并行度（尤其对写热点分散的负载）。WRITESET 需要 binlog_format=ROW，且与 GTID 配合使用最有效。两者都通过 slave_parallel_type 参数（DATABASE/LOGICAL_CLOCK）与 slave_parallel_workers 配置。

并行复制的核心是"在保证事务执行结果一致的前提下，尽量提高并行度"。LOGICAL_CLOCK 以提交时间近似推断无依赖，WRITESET 以写集合交集精确判断无冲突，后者并行度更高。回答需对比两者的依据与适用前提。

• GTID 由 server_uuid 与事务序号组成，全局唯一
• 通过 GTID 自动同步复制位置，无需手动指定 binlog 文件与位置
• 简化主从切换、failover 与搭建从库

GTID（Global Transaction Identifier，全局事务标识）是 MySQL 为每一个在源端（master）提交的事务分配的全局唯一标识。它的格式为 server_uuid：transaction_id，例如 3E11FA47-71CA-11E1-9E33-C80AA9429562:23，其中 server_uuid 是实例的唯一标识，transaction_id 是该实例上按顺序递增的事务序号。GTID 在事务提交时生成并写入 binlog，随 binlog 复制到从库。由于每个事务有全局唯一标识，从库可以据此识别哪些事务已经执行过、哪些需要执行，从而在复制中自动确定复制位置（由 GTID 集合 gtid_executed 记录），不再需要像传统方式那样手动指定 binlog 文件名与 position。这极大简化了主从切换、故障转移（failover）和从库搭建：切换时只需让新主库的 GTID 集合与从库对齐即可，无需逐点核对位置，也避免了复制中断或重复执行问题。

GTID 的本质是"用事务级全局标识替代文件级位置"。回答需给出 GTID 格式、生成与传播机制、以及它如何简化复制位置管理、failover 与从库搭建。注意 GTID 模式下事务必须完整复制（不能原子地跳过部分事务）。

• MGR 是 MySQL 的组复制插件，基于 Paxos 共识协议实现多主/单主强一致
• 写事务需在组内多数派成员投票确认后才提交，保证强一致
• 推出原因：相比传统异步/半同步复制提供更高一致性保证

Group Replication（组复制，MGR）是 MySQL 5.7.17 引入的复制插件，基于 Paxos 共识协议（MySQL 内部实现了一套分布式一致性协议）实现多个节点之间的强同步复制。它把一组 MySQL 节点组织成一个复制组，通过 Paxos 保证组内成员对事务提交顺序达成一致；写事务必须经过组内大多数成员（多数派）确认并接受后，才能在整个组内提交，从而保证任何已提交事务都不会因单点故障而丢失，实现数据强一致。MGR 支持单主模式（single-primary，一个节点可写，其他只读）和多主模式（multi-primary，多个节点可写，需处理写冲突）。相比传统异步复制（可能丢数据）和半同步复制，MGR 在一致性上更接近强一致，特别适合需要高可用且数据一致性要求高的场景。但 MGR 也有局限：对网络延迟敏感、需要稳定的网络环境、事务冲突率较高时性能下降、不支持某些存储引擎（仅 InnoDB）、延展性有限（一般建议 5-9 节点以内）。

MGR 的核心是"用 Paxos 共识替代主从单向复制"，多数派确认后才提交。回答需讲清 Paxos 共识、多数派提交、单主/多主模式以及强一致的代价与限制。

• 依据工作集大小、命中率指标规划 buffer pool 大小
• 多实例分片（innodb_buffer_pool_instances）减少并发访问锁竞争
• 命中率计算与缓存膨胀的权衡

Buffer Pool 大小规划需要综合考虑数据量、热点集（working set）与命中率。一般而言，若数据全部能装入内存（Buffer Pool 大于全部数据），命中率最高，但内存有限；实际中应让 Buffer Pool 至少覆盖"热点集"（被高频访问的那部分数据），可通过监控命中率（逻辑读命中比例=(read_requests-reads)/read_requests）来评估。若命中率过低（如物理读多），说明 Buffer Pool 过小，可考虑增大；但过大会挤压操作系统内存和连接缓存，需权衡。常见原则是设置为物理内存的 50%-80%。多实例（instance）分片：当 innodb_buffer_pool_size 较大（如 >1GB）时，InnoDB 会把 Buffer Pool 分成多个独立的缓冲池实例（instance，由 innodb_buffer_pool_instances 控制，默认 8），每个实例独立的 LRU 链表、mutex 和统计，从而分散并发访问时对全局锁的竞争，提升高并发下的吞吐量。Buffer Pool 大小最好为每个实例分配的内存之和，且实例数在大小较小时会自动限制。

规划是"以热点集为核心、以命中率为反馈"的迭代过程；实例分片是"以锁竞争换并行度"的并发优化。回答需区分"大小规划"与"实例分片"两个维度，并说明命中率与实例数的关系。

• STATEMENT：记录 SQL 语句，日志小但可能结果不一致
• ROW：记录行级变更，精确但日志大
• MIXED：默认语句，必要时切换为行级

MySQL Binlog 有三种格式：STATEMENT、ROW、MIXED。STATEMENT 格式记录的是执行的 SQL 语句本身，日志量小、占用空间少，但存在不确定性：某些函数（如 NOW()、UUID()、RAND()）或非确定性操作在从库重放时结果可能不同，且对锁行为、唯一性校验等可能在主从产生不一致。ROW 格式记录的是每一行的实际变更（before/after 镜像），数据精确、可保证主从一致，且支持命令 mysqlbinlog 精确回放与闪回，但日志量大、占用空间多，尤其是大事务。MIXED 混合格式默认使用 STATEMENT，但在检测到可能产生不确定结果的语句时自动切换为 ROW，兼顾日志体积与一致性。在实际生产中，为保证主从严格一致并支持闪回工具，通常推荐使用 ROW 格式（binlog_format=ROW），配合 binlog_row_image=FULL。

取舍的本质是"日志体积 vs 一致性精度"。STATEMENT 省空间但不确定，ROW 精确但体积大，MIXED 折中。生产环境出于一致性与可恢复性考虑多用 ROW。

• 异步/半同步/组复制的数据一致性保证差异
• 主从延迟导致读写不一致（读从库读到旧数据）
• 检测方法（Seconds_Behind_Master、延迟判断）与缓解措施（读写分离策略、强制走主库）

三种复制方式在数据一致性上差异明显：异步复制（Async）中，主库提交事务后立即返回，不等待从库确认，从库可能落后甚至丢失数据，一致性最弱；半同步复制（Semi-Sync）中，主库提交后需等待至少一个从库确认收到 binlog 才返回（或按配置），降低了丢失窗口，但仍是最终一致，从库可能短暂落后；组复制（Group Replication，MGR）基于 Paxos，写事务需多数派确认后才提交，提供强一致保证。主从延迟会导致读写不一致：应用从从库读可能读到旧数据（复制延迟）。检测方法：通过 SHOW SLAVE STATUS 的 Seconds_Behind_Master（反映 SQL 线程与 IO 线程的时差，但重连/多线程时可能不准确）以及对比主从 GTID 集合、延迟监控工具（如 pt-heartbeat）来判断。缓解措施：配置读写分离时对一致性要求高的读强制走主库（如按关键字路由、ProxySQL 的 fat 连接绑定）、结合半同步/组复制减少延迟、增大从库并行度、优化从库 SQL 执行等。

回答分两层：先对比三种复制的一致性保证，再讲读写不一致的检测与缓解。核心是"异步最弱、半同步折中、MGR 最强"，以及"读写分离时对强一致读走主库"。

• sys schema 是基于 Performance Schema 数据封装的便捷视图/存储过程
• sys.diagnostics 一键收集诊断信息
• sys.io_global_by_file_by_latency 按文件统计 IO 延迟

sys schema 是 MySQL 内置的一组基于 Performance Schema 数据的便捷视图、存储函数和存储过程，用于简化性能监控与诊断。其数据源是 Performance Schema 的 instrumentation 与 event 表，但封装成更易读、更聚合的形式。常见应用包括：sys.diagnostics 存储过程可以一次性收集系统状态、InnoDB 状态、复制状态、Performance Schema 汇总等诊断信息，便于排障；sys.io_global_by_file_by_latency 视图按文件统计全局 IO 延迟（总延迟、平均延迟、读写次数等），用于定位磁盘 IO 热点文件；此外还有 sys.schema_table_statistics（表统计）、sys.statements_with_*（慢 SQL 相关）、sys.innodb_lock_waits（锁等待）等。sys schema 帮助 DBA 快速定位性能瓶颈，而不必手动写复杂的 Performance Schema 查询。

sys schema 是"PS 数据的简化视图"，回答需说明其数据来源（PS）与典型用途（诊断、IO 延迟、锁等待、慢 SQL 等）。

• 预读是顺序 IO 优化，按区（extent）提前读取相邻页
• 线性预读（linear read-ahead）与随机预读（random read-ahead）
• 参数 innodb_read_ahead_threshold 控制触发条件

预读（Read-Ahead）是 InnoDB 的一种 IO 优化机制：当检测到某个数据区（extent）中的页面被顺序访问时，InnoDB 会预测后续相邻页面很快也会被访问，于是提前把这些尚未被请求的相邻页从磁盘批量读入 Buffer Pool，从而把多次随机小 IO 合并为一次顺序大 IO，降低磁盘 IO 次数、提升顺序扫描性能。预读分为两种：线性预读（linear read-ahead），当顺序访问的页数达到阈值（由 innodb_read_ahead_threshold 控制，默认 56）时触发，把整个区读入；随机预读（random read-ahead），当检测到某个区内有多个页被随机访问时触发，一次读入该区剩余页（注意：随机预读由 innodb_random_read_ahead 控制且默认关闭，实际生产中主要使用线性预读）。预读通过 innodb_io_capacity 等参数控制 IO 上限，避免过度预读浪费 IO。预读能显著提升全表扫描、大范围范围查询等顺序读场景的性能。

预读是"顺序读优化"，用空间预测换取 IO 合并。回答需说明触发条件（顺序访问页数达到阈值）、线性/随机两种方式及 IO 上限控制。

• ALGORITHM=INPLACE 与 COPY 的区别（是否重建表、是否需复制数据）
• LOCK 级别（NONE/SHARED/EXCLUSIVE）控制加锁范围
• Online DDL 期间 DML 与 DDL 并发执行

Online DDL 允许在执行表结构变更（ALTER TABLE）的同时，其他并发的事务仍可对表进行 DML（增删改查），从而避免长时间锁表。其核心是 ALGORITHM 与 LOCK 两个选项。ALGORITHM 指定执行算法：INPLACE（原地）表示不重建整个表、直接在原表结构上修改，多数 InnoDB 操作（如加/删索引、加列、优化表的某些操作）支持 INPLACE；COPY（复制）表示创建新表并拷贝数据，加锁时间长、开销大。LOCK 指定 DDL 期间允许的并发级别：NONE（允许并发读写，最宽松）、SHARED（允许读、禁止写）、EXCLUSIVE（禁止读写）。当使用 LOCK=NONE 且 ALGORITHM=INPLACE 时，DDL 期间表可正常读写，后台通过增量日志（row log）捕获 DDL 期间并发产生的 DML 变更，在 DDL 结束时合并应用，从而做到在线变更。不是所有 DDL 都支持 INPLACE+NONE，不支持的会降级为 COPY 并加锁。

理解 Online DDL 的关键是"INPLACE 算法 + 并发 DML 的增量捕获（row log）"。回答需说明 ALGORITHM 决定是否重建表、LOCK 决定并发级别，并注意并非所有操作都支持最宽松组合。

• 准备阶段（prepare）：初始化、校验、获取锁
• 执行阶段（execute）：执行变更（INPLACE or COPY）
• 提交阶段（commit）：更新数据字典、提交变更

Online DDL 的执行过程分为三个阶段：准备阶段（prepare）、执行阶段（execute）、提交阶段（commit）。准备阶段：创建新表结构（如索引）、初始化并校验 DDL，获取所需元数据锁（如 SHARED 或 EXCLUSIVE），把 DDL 所需的资源准备好，此阶段通常很快。执行阶段：真正执行结构变更，若为 INPLACE 算法则直接修改原表对应的索引/结构，若为 COPY 则新建表并拷贝数据；此阶段是最耗时的，期间通过 row log 记录并发的 DML 变更，若所需锁为 NONE 则 DML 可并发执行。提交阶段：将 DDL 变更（如新的索引、表结构）写入数据字典，应用 row log 中捕获的并发 DML 变更，并提交整个 DDL，释放锁。提交阶段通常短暂，但需要独占锁（EXCLUSIVE）完成元数据更新。理解三个阶段有助于判断 DDL 期间哪些时刻可能阻塞 DML。

Online DDL 的三阶段是"准备-执行-提交"的流水线，其中执行阶段耗时最长、提交阶段需短暂独占锁。回答需结合 INPLACE/COPY 与 row log 说明各阶段行为。

• 原生 Online DDL 的优势与限制
• pt-osc 的原理（镜像表 + 增量拷贝 + 表名切换）
• 选择依据：版本、操作类型、锁、主从负载

原生 Online DDL（MySQL 自带）与 pt-online-schema-change（pt-osc，Percona 工具）都是用于在线修改表结构的方法，各有取舍。原生 Online DDL 的优势：直接由 MySQL 执行，操作类型多样（有的操作无需拷贝数据），无需额外工具，能利用引擎的内部优化；其限制是需要 MySQL 版本支持（5.6+ 才有完善的 INPLACE），某些操作（如修改某些列类型、改变主键）仍需 COPY 或加锁，且大表 COPY 时可能消耗大量磁盘与 IO。pt-osc 的原理：通过创建一张与原表结构相同的镜像表，先拷贝全量数据，再通过触发器（trigger）或 binlog 增量同步 DDL 期间的新增变更，最后在表名上原子切换，整个过程对原表锁影响很小。取舍建议：若操作支持 INPLACE+NONE 且版本较新，优先用原生 Online DDL（更简单可靠）；若操作会触发 COPY、或需在低版本 MySQL、或需精细控制并以从库压力为代价，可选用 pt-osc。无论哪种，都应先评估大表、主从复制压力与磁盘空间。

取舍的核心是"原生引擎能力 vs 外部工具可控性"。原生适合新版本、支持 INPLACE 的操作；pt-osc 适合 COPY 类操作、低版本或需要精细控制的场景。回答需对比两者的机制与适用场景。

• Performance Schema 采集性能数据本身有开销
• 通过 instrument 与 consumer 的开关控制采样范围
• 设置采样率、限制历史表记录数

Performance Schema（PS）通过采集事件（event）数据来监控性能，但采集本身会带来 CPU、内存和锁的开销，因此需要控制其开销。控制手段主要有：其一，通过 instrument（instrument 是埋点在代码中的采集点）的开关控制采集哪些事件，例如可以关闭不关心的 instrument（如某些语句、锁、等待事件），减少开销；其二，通过 consumer（PS 的消费者，决定采集到的事件写入哪些历史表/汇总表）的开关控制是否消费数据，例如关闭某些 consumer 避免写入过多历史记录；其三，控制采样率——对某些高频率事件（如语句、锁）可设置采样或仅在特定条件下采集，避免把所有事件都记录；其四，限制历史表（如 events_statements_history_long 等）的保留行数，避免内存无限增长。通过精细化开关，可在监控覆盖与性能开销之间取得平衡，让 PS 只采集真正需要的数据。

PS 开销控制的核心是"instrument（采集点）与 consumer（消费/存储）的双层开关 + 采样率 + 历史表容量限制"。回答需说明哪里有开销以及如何按需裁剪。

• instrument：代码中的埋点采集器
• consumer：决定采集数据是否被消费/存储
• table：保存采集结果的汇总表/历史表

Performance Schema 的架构由三层协作构成：instrument、consumer、table。instrument 是嵌入在 MySQL 代码中的采集点（埋点），用于在特定事件发生时（如执行语句、等待锁、磁盘 IO）产生事件数据；每个 instrument 都有名称（如 statement/sql/xxx、wait/io/xxx）和 ENABLED/TIMED 状态，决定是否启用采集及是否计时。consumer 是事件的"消费者"，决定采集到的事件是否被进一步处理并写入相应的汇总表或历史表；只有 enable 了对应的 consumer，事件才会被保存下来供查询。table 是保存采集结果的各类表，包括：当前事件表（events_statements_current 等）、历史表（events_statements_history 等）、以及大量汇总表（statements_summary_by_、file_summary_by_ 等），还有配置表（setup_instruments、setup_consumers）。三者配合：instrument 采集 → consumer 决定是否消费 → 写入 table 供查询。sys schema 就是基于这些表做了聚合封装。

架构是"埋点采集（instrument）→ 消费决策（consumer）→ 结果存储（table）"的流水线。回答需讲清三者的职责与关系，这是理解 PS 的入门框架。

• InnoDB 页（默认 16KB）与行格式（COMPACT、DYNAMIC、REDUNDANT）
• DYNAMIC 对长字段（TEXT/BLOB）的溢出存储处理
• 行格式对存储空间、页内行数、索引的影响

InnoDB 以页（page，默认 16KB）为最小存储单位，行格式决定了行数据在页内的存储方式。主要行格式有 REDUNDANT、COMPACT、DYNAMIC、COMPRESSED。COMPACT 格式：把行数据紧凑存储，长的可变长度字段（如 VARCHAR、TEXT、BLOB）如果超过一定长度，会把数据存到溢出页（overflow page），而在行内只保存 20 字节的前缀（prefix）和指向溢出页的指针，从而减少行占用的页内空间，使单页容纳更多行。DYNAMIC 格式（5.7+ 默认）：与 COMPACT 类似，但更进一步，长字段（TEXT/BLOB）完全溢出存储，行内只保留 20 字节指针，不保留前缀，适合大字段表，可减少页内行数膨胀、提升页利用率。行格式影响：存储空间（溢出页数量）、单页可容纳的行数（影响索引查找的页访问次数）、以及长字段的随机 IO（访问溢出页）。选择行格式时小字段可用 COMPACT，含大量 TEXT/BLOB 的表适合 DYNAMIC。

行格式影响的是"页内如何组织行 + 长字段如何溢出"。DYNAMIC 对长字段全量溢出只留指针，减少页内膨胀。回答需说明页、行格式、溢出页的关系及对索引与存储的影响。

• 命中率定义与计算
• 容量：Buffer Pool 不足、数据量大
• SQL 模式与预热：全表扫描、冷启动、缓存未命中

Buffer Pool 命中率下降表明读取的页面有较多来自磁盘（物理读），应从容量、SQL 模式与预热三个角度排查。容量角度：Buffer Pool 过小、无法容纳热点集，或数据量增长导致热点集超出内存，会降低命中率，可结合逻辑读（read_requests）与物理读（reads）计算命中率，必要时调大 innodb_buffer_pool_size。SQL 模式角度：全表扫描、大范围查询、无索引的字段过滤会一次性读入大量冷页，冲刷热点页，导致命中率下降，应优化 SQL、加索引、避免大范围扫描；此外缓存污染（大量冷数据涌入）也会冲击 LRU。预热角度：实例重启后 Buffer Pool 为空，需要逐步预热（warm-up）才能恢复命中率，冷启动阶段命中率必然低；可通过预热机制（如 5.7 的 buffer pool 导出/导入、8.0 的 dump/load）在重启后快速恢复缓存。综合排查就是"容量够不够、SQL 是否产生大量冷读、是否处于冷启动未预热"。

排查命中率下降是"容量不足、SQL 冷读、冷启动预热"三个维度的综合判断。回答需给出命中率公式和三个角度的具体措施。