Go back

MySQL 面试回答——事务 MVCC、索引、锁、日志与主从

MySQL 面试回答

本文覆盖 MySQL 面试高频的 8 个子主题：① 事务与 MVCC ② 存储引擎 ③ 缓存 BufferPool ④ 索引 ⑤ 锁机制 ⑥ 日志 ⑦ SQL 优化 ⑧ 主从架构。每个子主题按「一句话结论（30s）/ 核心原理（2min）/ 底层深入（5-10min）」三版本作答，末尾附 10 个追问点。

① 事务与 MVCC

一句话结论

事务的 ACID 各由一个机制兜底——原子性靠 undo log、隔离性靠锁 + MVCC、持久性靠 redo log、一致性由前三者共同保证；MVCC 的本质是「读不加锁」，因为每次读都是通过 ReadView + undo 版本链看到事务开始时的快照，而不是最新数据。

核心原理（2min）

ACID 怎么落地： 先自己分辨一下：四个特性里，有三个是「手段」、有一个是「结果」——哪个是结果？想通了这一点，后面每个机制归位到哪就清楚了。

MVCC 主流程： 每行有隐藏字段 DB_TRX_ID（最后修改这行的事务 ID）和 DB_ROLL_PTR（指向 undo log 中上一版本的指针），把一行所有历史版本串成链。读事务开始时生成一个 ReadView（快照），遍历版本链，用 ReadView 里的 min_trx_id / max_trx_id / m_ids 判断每个版本是否可见，取第一个可见版本返回。这里先想清楚「为什么要给每行塞两个隐藏字段」——因为不加锁读，就得有别的办法知道「这个版本是谁、上一个版本在哪」，否则回滚和快照读都无从谈起；这两个字段就是为「版本链」铺路。

ReadView 四字段： creator_trx_id（创建者）、m_ids（生成时所有活跃未提交事务集合）、min_trx_id（m_ids 最小值）、max_trx_id（下一个待分配事务 ID）。可见性判断：版本 trx_id 是自己改的→可见；< min_trx_id→已提交→可见；>= max_trx_id 或在 m_ids 中→未提交→不可见，沿链回溯上一版本。

底层深入（5-10min）

RC 与 RR 的唯一区别是 ReadView 生命周期，MVCC 实现完全相同： RC 每次 SELECT 都重新生成 ReadView（能读到别的事务新提交的数据）；RR 只在事务第一次 SELECT 时生成、之后复用（保证可重复读）。所以「RR 通过 MVCC 解决可重复读」本质就是「同一个 ReadView 用到底」。这一点值得玩味：两个隔离级别底层代码几乎一模一样，只改了一个「什么时候生成快照」的时机——可见「可重复读」的本质不是魔法，而是「把快照钉住不换」。

真实源码印证（include/read0types.h）： ReadView 的可见性判断就落在 changes_visible() 这一个函数里，判断顺序与上面讲的分支完全对应。

[[nodiscard]] bool changes_visible(trx_id_t id) const override {
  ut_ad(id > 0);

  if (id < m_up_limit_id || id == m_creator_trx_id) {
    return true;
  }
  if (id >= m_low_limit_id) {
    return false;
  }
  if (m_ids.empty()) {
    return true;
  }

  const ids_t::value_type *p = m_ids.data();

  return !std::binary_search(p, p + m_ids.size(), id);
}

代码里 m_up_limit_id 就是正文说的 min_trx_id（低水位，小于它的一定已提交、可见），m_low_limit_id 是 max_trx_id（高水位，大于等于它的一定不可见），夹在中间的再查 m_ids 活跃事务集合——注意 std::binary_search 说明 m_ids 本身就是排好序的数组，可见性判断最后退化成一次 O(log n) 二分。四个字段的定义如下，源码命名恰好和面试常说的「min/max」相反，别背串：

  /** The read should not see any transaction with trx id >= this
  value. In other words, this is the "high water mark". */
  trx_id_t m_low_limit_id;

  /** The read should see all trx ids which are strictly
  smaller (<) than this value.  In other words, this is the
  low water mark". */
  trx_id_t m_up_limit_id;

  /** If the view is open, then this is a trx->id of the transaction which has
  created this view, used to let this view see the changes of this transaction.
  Note that a transaction might have no trx->id assigned in which case this
  will be 0. A transaction may also get trx->id assigned after it has already
  created a read view, in which case it should call set_view_creator_trx_id to
  update this field.
  It is 0 for read views cloned by clone_oldest_view.
  Otherwise its value doesn't matter. */
  trx_id_t m_creator_trx_id;

  /** Set of RW transactions that was active when this snapshot
  was taken */
  ids_t m_ids;

  /** The view does not need to see the undo logs for transactions
  whose transaction number is strictly smaller (<) than this value:
  they can be removed in purge if not needed by other views */
  trx_id_t m_low_limit_no;

m_creator_trx_id 就是那句「自己改的自己看得到」的来源（id == m_creator_trx_id 直接返回 true）；而 m_low_limit_no 是给 purge 线程用的——只有所有活跃 ReadView 都越过某个事务号，它对应的 undo 才能被清，这正是下面「长事务导致 undo 膨胀」的底层依据。

但 RR 不能完全避免幻读——它只解决了「快照读」的幻读： 普通 SELECT 走 MVCC 快照读，总看到同一快照；但 SELECT ... FOR UPDATE / UPDATE / DELETE 是「当前读」，走最新已提交版本并加锁，不走 ReadView。当前读的幻读必须靠间隙锁/临键锁来防。所以 RR 下「快照读靠 MVCC、当前读靠间隙锁」才是完整答案。为什么 FOR UPDATE 不能也走快照读？——因为它要锁住真实存在的最新行，读一个「过期的快照」去加锁，锁的根本就不是当前数据，其他事务会基于错误的前提继续写。

长事务导致 undo 膨胀的底层链路： 一个 ReadView 持有对 m_ids 中所有事务的引用，purge 线程不能清理任何「可能被活跃 ReadView 需要」的 undo 记录。一个事务挂 8 小时，这期间所有 UPDATE/DELETE 产生的旧版本都不能删，undo 表空间持续膨胀，版本链越来越长、每次一致性读回溯成本也越来越高。查长事务用 information_schema.innodb_trx 的 trx_started / trx_rows_modified。

版本链回溯的真实代码（row/row0vers.cc）： 一致性读找不到可见版本时，就靠 trx_undo_prev_version_build 沿 DB_ROLL_PTR 一步步往上一版本走，直到版本链走完。

  while (version_trx_id == trx_id) {
    mem_heap_t *old_heap = heap;
    const dtuple_t *clust_vrow = nullptr;
    rec_t *prev_version = nullptr;

    /* We keep the semaphore in mtr on the clust_rec page, so
    that no other transaction can update it and get an
    implicit x-lock on rec until mtr_commit(mtr). */

    heap = mem_heap_create(1024, UT_LOCATION_HERE);

    trx_undo_prev_version_build(
        clust_rec, mtr, version, clust_index, clust_offsets, heap,
        &prev_version, nullptr,
        dict_index_has_virtual(sec_index) ? &clust_vrow : nullptr, 0, nullptr);

    /* The oldest visible clustered index version must not be
    delete-marked, because we never start a transaction by
    inserting a delete-marked record. */
    ut_ad(prev_version || !rec_get_deleted_flag(version, comp) ||
          !trx_rw_is_active(trx_id, false));

    /* Free version and clust_offsets. */
    mem_heap_free(old_heap);

    version = prev_version;

这个 while 循环就是「沿版本链往回找」的物理实现：每一轮拿当前 version 调 trx_undo_prev_version_build 构造出 prev_version，再把 version 更新成它继续回溯。所以「版本链」不是抽象概念，而是 DB_ROLL_PTR 这条真实指针一步步串出来的链。

Undo 的两类与生命周期： Insert Undo 只记主键/行号，事务提交即可删（新行对其他快照不可见）；Update Undo 记旧值 + roll_pointer，提交后必须等 purge 线程确认「无活跃 ReadView 需要它」才能清理——这是膨胀的主要来源。回滚段 innodb_rollback_segments=128，每段最多 1024 个 slot，事务按 trx_id % segments 轮询分配。

② 存储引擎（InnoDB / MyISAM）

一句话结论

InnoDB 赢在「支持事务 + 行级锁 + 聚簇索引 + MVCC + 崩溃恢复」这一整套组合；MyISAM 只保留了「表锁 + 计数器存行数」这种简单设计，所以早被淘汰，现在默认引擎就是 InnoDB。

核心原理（2min）

维度InnoDBMyISAM
事务支持（ACID 完整）不支持
锁粒度行锁 + 间隙锁 + 临键锁仅表锁，写并发差
索引聚簇索引，主键即数据，二级索引存主键回表非聚簇，索引和数据分离，叶子存行地址
MVCC支持（ReadView + undo）不支持
崩溃恢复redo log 自动恢复无 redo，崩溃需 repair table
count(*)遍历 B+ 树叶子页，慢有计数器，O(1)
外键/全文外键支持；全文 5.6 后也支持全文索引是传统强项

为什么默认 InnoDB？ 因为现代业务强依赖事务一致性、并发写入和崩溃不丢数据——这三点 MyISAM 全都做不到；MyISAM 唯一「快」的场景（count(*)）也因维护计数器导致写时全表锁，得不偿失。可以反向推一下 MyISAM 为什么会设计成「表锁 + 计数器」：它为了 count(*) 快，专门维护一个计数器，但每次写都要改这个计数器，于是写只能串行——「快」是用「写并发彻底牺牲」换来的。所以选引擎不是比谁功能多，是比「你的业务到底要拿什么换什么」。

底层深入（5-10min）

InnoDB 存储是五层模型：表空间 → 段 → 区 → 页 → 行。 五层不是拍脑袋堆出来的，每一层都在回答一个具体问题——「磁盘 IO 按多大粒度做」「空间按多大块分配」「索引数据怎么组织」。

页内 Page Directory 的真实定义（include/page0page.h）： 上面说的「槽」在源码里就是一个 2 字节的 page_dir_slot_t，每个槽最多/最少拥有点记录数被硬编码成了 8 和 4。

typedef byte page_dir_slot_t;
typedef page_dir_slot_t page_dir_t;

/* Offset of the directory start down from the page end. We call the
slot with the highest file address directory start, as it points to
the first record in the list of records. */
constexpr uint32_t PAGE_DIR = FIL_PAGE_DATA_END;

/* We define a slot in the page directory as two bytes */
constexpr uint32_t PAGE_DIR_SLOT_SIZE = 2;

/* The offset of the physically lower end of the directory, counted from
page end, when the page is empty */
constexpr uint32_t PAGE_EMPTY_DIR_START = PAGE_DIR + 2 * PAGE_DIR_SLOT_SIZE;

/* The maximum and minimum number of records owned by a directory slot. The
number may drop below the minimum in the first and the last slot in the
directory. */
constexpr uint32_t PAGE_DIR_SLOT_MAX_N_OWNED = 8;
constexpr uint32_t PAGE_DIR_SLOT_MIN_N_OWNED = 4;

PAGE_DIR_SLOT_MAX_N_OWNED = 8、PAGE_DIR_SLOT_MIN_N_OWNED = 4 就是正文「每个槽指向约 4-8 条记录」的出处；PAGE_DIR_SLOT_SIZE = 2 说明槽本身只存一个 2 字节的页内偏移，目录区从页尾倒着往页内长（PAGE_DIR 是距页尾的偏移）。这也解释了为什么页内是「先二分槽、再组内线性扫最多 7 条」——槽只能给到「组」这个粒度，组内记录变长，只能顺序找。

顺带解释一个经典现象：为什么 InnoDB 的 SELECT COUNT(*) 慢？ 因为 InnoDB 不在表级别维护行数，必须遍历聚簇索引的叶子页统计，行数没有汇总到页/区/段层；而 MyISAM 直接维护一个计数器。这里自然会追问：那 InnoDB 为什么不也维护一个计数器呢？——因为事务和 MVCC 下，「总行数」对每个事务是不一样的（别的事务未提交的插入对你可能不可见），一个全局计数器给不出「你这个事务视角的行数」，只能现扫。MyISAM 没有事务，才敢放心用一个全局数字。

③ 缓存（BufferPool）

一句话结论

BufferPool 是 InnoDB 的内存心脏，所有读写先经过它；它的两个关键设计是「分区 LRU 防冷数据污染热数据」和「Double Write 防页断裂」，本质都是「用内存 + 少量顺序写，换掉昂贵的随机 IO」。

核心原理（2min）

修改不直接落盘： 更新先改 BufferPool 里的页并标记为脏页，由后台线程异步刷盘，这就是「脏页」概念。脏页按 oldest_modification 挂在 Flush 链表上，刷盘线程按「最老优先」顺序刷，保证 checkpoint 稳定推进、redo log 能循环复用。为什么不改一次就落一次盘？——因为磁盘随机写是毫秒级、内存写是纳秒级，每次都落盘会把性能拖到磁盘；改成「内存先改、后台批量刷」，把多次随机写攒成批量顺序写，这才是 WAL 思路在页层面的体现。

分区 LRU： 传统 LRU 被一次全表扫描就能「冲走」所有热数据（BufferPool 污染）。为什么全表扫描会污染？——LRU 的规则是「被访问就挪到最前面」，全表扫描会把海量冷页挨个访问一遍、统统顶到头部，把真正热的数据挤出去。InnoDB 把 LRU 链表分成 young 区（前 63%）和 old 区（后 37%）：新页一律插到 old 区头部，在 old 区停留超过 innodb_old_blocks_time（默认 1000ms）再次被访问才晋升 young 区。全表扫描读一页间隔远小于 1 秒，冷页在 old 区待不到 1 秒就被淘汰，全程碰不到 young 区的热点数据。这个设计的妙处在于：它不判断「这次扫描是不是全表扫描」，而是用「两次访问的时间间隔」这个客观指标，把「真热点」和「一次性扫过」区分开。

底层深入（5-10min）

分区 LRU 的落地代码（buf/buf0lru.cc + include/buf0buf.ic）： 「新页进 old 区头部」不是一句空话——buf_LRU_add_block_low 里 old 为真时，把页插到 LRU_old 指针之后（old 区头部），而不是链表最前面。

static inline void buf_LRU_add_block_low(buf_page_t *bpage, bool old) {
  buf_pool_t *buf_pool = buf_pool_from_bpage(bpage);

  ut_ad(mutex_own(&buf_pool->LRU_list_mutex));

  ut_a(buf_page_in_file(bpage));
  ut_ad(!bpage->in_LRU_list);

  if (!old || (UT_LIST_GET_LEN(buf_pool->LRU) < BUF_LRU_OLD_MIN_LEN)) {
    UT_LIST_ADD_FIRST(buf_pool->LRU, bpage);

    bpage->freed_page_clock = buf_pool->freed_page_clock;
  } else {
#ifdef UNIV_LRU_DEBUG
    /* buf_pool->LRU_old must be the first item in the LRU list
    whose "old" flag is set. */
    ut_a(buf_pool->LRU_old->old);
    ut_a(!UT_LIST_GET_PREV(LRU, buf_pool->LRU_old) ||
         !UT_LIST_GET_PREV(LRU, buf_pool->LRU_old)->old);
    ut_a(!UT_LIST_GET_NEXT(LRU, buf_pool->LRU_old) ||
         UT_LIST_GET_NEXT(LRU, buf_pool->LRU_old)->old);
#endif /* UNIV_LRU_DEBUG */
    UT_LIST_INSERT_AFTER(buf_pool->LRU, buf_pool->LRU_old, bpage);

    buf_pool->LRU_old_len++;
  }

「在 old 区待满 innodb_old_blocks_time 再被访问才晋升 young」的判断在 buf_page_peek_if_too_old 里——它读页的 access_time，两次访问间隔超过阈值（默认 1000ms）才返回 true 触发挪到链表头。

static inline bool buf_page_peek_if_too_old(const buf_page_t *bpage) {
  buf_pool_t *buf_pool = buf_pool_from_bpage(bpage);

  if (buf_pool->freed_page_clock == 0) {
    /* If eviction has not started yet, do not update the
    statistics or move blocks in the LRU list.  This is
    either the warm-up phase or an in-memory workload. */
    return false;
  } else if (get_buf_LRU_old_threshold() != std::chrono::seconds::zero() &&
             bpage->old) {
    const auto access_time = buf_page_is_accessed(bpage);

    if (access_time != std::chrono::steady_clock::time_point{} &&
        (std::chrono::steady_clock::now() - access_time) >=
            get_buf_LRU_old_threshold()) {
      return true;
    }

    buf_pool->stat.n_pages_not_made_young++;
    return false;
  } else {
    return (!buf_page_peek_if_young(bpage));
  }
}

两段拼起来就是正文「新页插 old 区、待满 1 秒才晋升」的完整实现：UT_LIST_INSERT_AFTER(LRU, LRU_old, ...) 决定新页落在 old 区头部，now() - access_time >= threshold 决定它能不能翻进 young 区。全表扫描读一页的间隔远小于阈值，于是持续走 n_pages_not_made_young++ 分支，冷页根本到不了 young 区。

Double Write 解决「页断裂（Torn Page）」： InnoDB 页 16KB，但 OS/磁盘原子写单位是 4KB。脏页刷盘刷到 4KB 时宕机，这页前 4KB 新、后 12KB 旧，checksum 校验失败、页损坏。这里最容易的误判是「有 redo 不就完了吗」——redo log 救不了它，因为 redo 是「在 page_no=100 offset=64 处写 4 字节」这种依赖目标页完整有效才能重放的物理日志，页本身损坏就失去了重放基础。想通这点，就知道 Double Write 的本质是「给每一页准备一份完整副本当救命稻草」，先写副本再写正页，正页坏了用副本顶。Double Write 流程：刷盘前先把脏页顺序写一份到 dblwr 区（128 页一次 fsync，顺序 IO 很快），再离散写回各 .ibd；崩溃恢复时发现页 checksum 失败，就用 dblwr 区完整副本覆盖再重放 redo。代价是额外 5%~10% 写开销，金融场景必选。

Change Buffer：非唯一二级索引的写入加速器。 先想一个问题：插入一条数据，除了主键索引，为什么二级索引的维护最痛？——二级索引维护是随机 IO（新 key 要插到 B+ 树中间某页）。Change Buffer 把二级索引的变更先缓存在内存（占 BufferPool 一部分，默认 25%），批量 merge 时把多次随机 IO 合并成一次顺序写。为什么只限非唯一索引？ 因为唯一索引插入前必须读索引页判断是否重复——这个检查只能在真实索引页做，屏蔽了 Change Buffer 路径。这条边界反过来也是内化点：判断一个优化「能不能用」，先看它有没有依赖「必须当场知道结果」的信息——唯一性校验就是这种依赖。三种 merge 时机：查询触发（被动）、后台 Master Thread 每秒（主动）、drop/缩表（被动）。因此「写入后立即按二级索引查询」是反模式——缓存了等于没缓存还多一次 merge 开销；日志/流水这类「写多读极少」的表才是最佳场景。

AHI 自适应哈希索引： BufferPool 内自动构建的内存哈希表，把高频等值查询的索引键直接映射到叶子页地址（是映射到「页」不是「行」，跳过 B+ 树 2-3 层遍历）。为什么映射到「页」而不是「行」？——映射到行会随着每次插入/删除频繁失效，哈希表维护成本爆炸；映射到页则粒度粗、稳定得多，查到页后再做一次页内定位，用极小代价换掉 B+ 树那几次内存寻址。只有等值查询触发，范围/LIKE 不触发；分区锁分离（默认 8 分区）降竞争；高并发写入场景每次页修改都要维护 AHI 键，可能得不偿失，命中率低（hash searches/s 占比 < 10%）应关闭。

④ 索引（B+ 树 / 失效场景）

一句话结论

数据库索引用 B+ 树是因为它「矮胖 + 叶子有序链表 + 数据都在叶子」，能用最少的磁盘 IO 定位数据、还能顺带支持范围查询和排序；而「索引失效」的本质是查询破坏了 B+ 树的有序性，或导致回表代价超过全表扫描，优化器放弃走索引。

核心原理（2min）

B+ 树 vs B 树 vs Hash： 对比之前先立一个尺子：数据库索引的痛点是「磁盘 IO 贵」，所以评价任何索引结构就看它「用最少的 IO 能不能覆盖等值 + 范围 + 排序三种查询」。

聚簇索引与回表： InnoDB 主键就是聚簇索引，叶子存整行数据；二级索引叶子存「索引键 + 主键」，查到主键后还要回聚簇索引取整行，这就是「回表」。覆盖索引（查询列全在索引里）能消除回表，Extra 显示 Using index。

联合索引最左前缀是物理结果，不是人为限制： idx(a,b,c) 在 B+ 树里先按 a 排、a 同按 b 排、b 同按 c 排。所以跳过 a 直接 WHERE b=10，b 在全局无序，走不了索引。范围查询后失效的本质：WHERE a=1 AND b>10 AND c=5，在 b>10 的范围内跨 b 值后 c 不再全局有序，c=5 无法用索引定位，只能逐行判断。想彻底记住这条，就别去背「最左前缀」四个字，而去想「B+ 树是一颗按字典序排好的树」——哪一列在全局有序，哪一列就能用索引定位；一旦某列被跳过或被范围打断，后面的列就失去了全局有序性。

底层深入（5-10min）

B+ 树搜索的真实代码（btr/btr0cur.cc + page/page0cur.cc）： 一条定位操作从根页一路下到叶子页，靠的是 btr_cur_search_to_nth_level；每到一个非目标层，就取当前页里搜到的 node_ptr 指向下一层子页，height-- 后继续往下走。

  if (level != height) {
    const rec_t *node_ptr;
    ut_ad(height > 0);

    height--;

    node_ptr = page_cur_get_rec(page_cursor);

    offsets = rec_get_offsets(node_ptr, index, offsets, ULINT_UNDEFINED,
                              UT_LOCATION_HERE, &heap);

落到某个具体页之后，页内定位就是前面说的「先二分槽、再组内线性扫」，下面是 page_cur_search_with_match 里的核心二分循环。

  /* Perform binary search. First the search is done through the page
  directory, after that as a linear search in the list of records
  owned by the upper limit directory slot. */

  low = 0;
  up = page_dir_get_n_slots(page) - 1;

  /* Perform binary search until the lower and upper limit directory
  slots come to the distance 1 of each other */

  while (up - low > 1) {
    mid = (low + up) / 2;
    slot = page_dir_get_nth_slot(page, mid);
    mid_rec = page_dir_slot_get_rec(slot);

    cur_matched_fields = std::min(low_matched_fields, up_matched_fields);

    auto offsets = get_mid_rec_offsets();

    cmp = tuple->compare(mid_rec, index, offsets, &cur_matched_fields);

    if (cmp > 0) {
    low_slot_match:
      low = mid;
      low_matched_fields = cur_matched_fields;

    } else if (cmp) {
#ifdef PAGE_CUR_LE_OR_EXTENDS
      if (mode == PAGE_CUR_LE_OR_EXTENDS &&
          page_cur_rec_field_extends(tuple, mid_rec, offsets,
                                     cur_matched_fields, index)) {
        goto low_slot_match;
      }
#endif /* PAGE_CUR_LE_OR_EXTENDS */
    up_slot_match:
      up = mid;
      up_matched_fields = cur_matched_fields;

    } else if (mode == PAGE_CUR_G || mode == PAGE_CUR_LE
#ifdef PAGE_CUR_LE_OR_EXTENDS
               || mode == PAGE_CUR_LE_OR_EXTENDS
#endif /* PAGE_CUR_LE_OR_EXTENDS */
    ) {
      goto low_slot_match;
    } else {
      goto up_slot_match;
    }
  }

二分只作用在「槽」这一层：tuple->compare(mid_rec, ...) 把搜索键和目标记录比较，比较结果决定 low/up 两个槽往哪边收敛，直到相邻。这正是「有序的 B+ 树页」能 O(log n_slots) 定位的原因，而后面失效场景「破坏有序性就破坏这种二分」也由这里直接导出。

五种经典索引失效场景（对应追问 #6）：

  1. WHERE func(col) = 1——函数破坏了索引有序性；
  2. WHERE col = 123（col 是 varchar）——隐式类型转换本质是函数，MySQL 内部会转成 CAST(col AS ...)；
  3. WHERE col LIKE '%abc'——前导通配符无法利用有序性；
  4. WHERE a=1 OR b=2（b 无索引）——OR 的非索引分支逼着全表扫；
  5. WHERE a > 10 ORDER BY b——范围后列无序，排序走不了索引，Extra 出现 Using filesort。

ICP 索引下推： 把「索引列上能判断的条件」下推到存储引擎层，在扫描索引时（回表前）先过滤，减少回表次数。典型场景 idx_user_status=(user_id, status)，WHERE user_id>100 AND status=1——status 在索引里但用不上（user_id 是范围），被下推后引擎层先判断 status=1 再回表。注意：ICP 只减少回表、不减少索引扫描行数；覆盖索引自动跳过 ICP（本来就没回表）。EXPLAIN Extra 显示 Using index condition。想一下为什么它「只减回表、不减扫描」？——因为 user_id>100 这个范围决定了一共要扫哪些索引记录，这个量变不了；ICP 只是让那些「扫到了但不满足 status=1」的行别再白白回表。所以它的收益上限就是「回表次数」，而不是「扫描行数」。

MRR（Multi-Range Read）常与 ICP 协同： ICP 先减少回表行数，MRR 再把待回表的主键排序、批量顺序回表，把随机 IO 转顺序 IO，Using index condition; Using MRR 是 1+1>2 的最优组合。

⑤ 锁机制（间隙锁 / 临键锁）

一句话结论

InnoDB 行锁有三件套：记录锁锁一行、间隙锁锁两行之间的空隙（防插入）、临键锁 = 记录锁 + 前一个间隙锁（前开后闭区间），是 RR 隔离级别下防「当前读幻读」的主力；而间隙锁只在 RR 隔离级别生效，RC 下会退化成只锁命中的行。

核心原理（2min）

记录锁 (Record Lock)    锁住索引中的具体一行
间隙锁 (Gap Lock)       锁住两行之间的间隙（不含行本身），阻止 INSERT
临键锁 (Next-Key Lock)  记录锁 + 左侧间隙锁，区间 (prev, cur]
                        默认锁，RR 下防幻读的主力

临键锁的退化规则（等值查询 + 唯一索引是唯二能触发退化的条件）： 先问为什么偏偏「等值 + 唯一索引」才能退化——因为只有「等值 + 唯一」能保证「最多命中一行」，锁的范围可以精确收敛到这一行；换成范围查询或非唯一索引，命中是「一片」，就锁不住「点」了。

退化机制的意义：已经精确找到目标行就不锁间隙，释放不必要的锁，提升并发插入吞吐。

底层深入（5-10min）

锁在源码里的真实结构（include/lock0types.h + include/lock0lock.h + include/lock0priv.h）： 记录锁、间隙锁、插入意向锁不是一个 enum 里并列的三种值，而是「模式 + 类型位」拼出来的。模式只有 5 种。

/* Basic lock modes */
enum lock_mode {
  LOCK_IS = 0,          /* intention shared */
  LOCK_IX,              /* intention exclusive */
  LOCK_S,               /* shared */
  LOCK_X,               /* exclusive */
  LOCK_AUTO_INC,        /* locks the auto-inc counter of a table
                        in an exclusive mode */
  LOCK_NONE,            /* this is used elsewhere to note consistent read */
  LOCK_NUM = LOCK_NONE, /* number of lock modes */
  LOCK_NONE_UNSET = 255
};

「间隙锁」「插入意向锁」这些是叠加在记录锁上的类型位（LOCK_GAP、LOCK_REC_NOT_GAP、LOCK_INSERT_INTENTION），和模式做按位 OR 存在同一个 type_mode 字段里。

/* Precise modes */
/** this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or
 LOCK_REC_NOT_GAP */
constexpr uint32_t LOCK_ORDINARY = 0;
/** when this bit is set, it means that the lock holds only on the gap before
  the record; for instance, an x-lock on the gap does not give permission to
  modify the record on which the bit is set; locks of this type are created
  when records are removed from the index chain of records */
constexpr uint32_t LOCK_GAP = 512;
/** this bit means that the lock is only on the index record and does NOT
   block inserts to the gap before the index record; this is used in the case
   when we retrieve a record with a unique key, and is also used in locking
   plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ
   COMMITTED isolation level */
constexpr uint32_t LOCK_REC_NOT_GAP = 1024;
/** this bit is set when we place a waiting gap type record lock request in
   order to let an insert of an index record to wait until there are no
   conflicting locks by other transactions on the gap; note that this flag
   remains set when the waiting lock is granted, or if the lock is inherited to
   a neighboring record */
constexpr uint32_t LOCK_INSERT_INTENTION = 2048;

「临键锁 = 记录锁 + 间隙锁」因此就是 type_mode 里既不设 LOCK_GAP 也不设 LOCK_REC_NOT_GAP 的状态（LOCK_ORDINARY = 0）——所以正文说的「前开后闭区间」不是运行时对象，而是这几个位组合出来的语义。注意 LOCK_REC_NOT_GAP 的注释直接点明了「RC 隔离级别下检索唯一键时用的就是它」，这正是「RC 退化成只锁命中行」的源码出处。锁之间能否共存，最终查一张写死的 5×5 兼容矩阵。

static const byte lock_compatibility_matrix[5][5] = {
    /**         IS     IX       S     X       AI */
    /* IS */ {true, true, true, false, true},
    /* IX */ {true, true, false, false, true},
    /* S  */ {true, false, true, false, false},
    /* X  */ {false, false, false, false, false},
    /* AI */ {true, true, false, false, false}};

矩阵里 X 行全是 false——排他锁跟任何锁都不兼容，这是「写写互斥」的硬编码来源；S 与 S 是 true，读读共享。两个事务加锁前先取 type_mode & LOCK_MODE_MASK 得到模式，再查这张表判断能否共存，lock_mode_compatible 最终就是一次数组下标访问。

插入意向锁（Insert Intention Lock）是间隙锁的并发优化变体： 事务 INSERT 时在目标间隙上取得插入意向锁，同一间隙内不同位置的插入意向锁互相兼容（锁的是各自待插入的「点」不是整个间隙），所以同一间隙能并发插入；但插入意向锁会被真正的间隙锁阻塞——SELECT ... FOR UPDATE 持有整个间隙锁时，别的 INSERT 进不来。这个设计既保住 RR 防幻读语义，又最大化并发插入吞吐。可以这样理解它的聪明之处：它把「我要插入」这件事声明成一个「意向」，让「多个想插不同点的意向」互相放行，但「一个要锁整段间隙的」能拦下所有意向——既没破坏防幻读，又把并发插入的损失压到最低。

为什么间隙锁只在 RR 生效： 因为 RC 的隔离语义不要求「可重复读」，幻读是可接受的，间隙锁这种大范围锁反而拖累并发；而 RR 要防当前读的幻读（快照读已经靠 MVCC 防了），必须用间隙锁/临键锁堵住「范围查询区间内插入新行」。间隙锁不互斥——两个事务锁同一间隙不会死锁，冲突只发生在「一个锁间隙、一个想插入」之间。

死锁的典型成因： 两个事务各自持有一把记录锁、又都想拿对方的间隙锁，或加锁顺序不一致。定位用 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段 + information_schema.innodb_lock_waits。

⑥ 日志（binlog / redo / undo）

一句话结论

三种日志各管一件事：redo log（InnoDB 层，物理日志）保持久性、undo log（InnoDB 层，记录旧值）保原子性和 MVCC、binlog（Server 层，逻辑日志）用于主从复制和数据恢复；一条 update 的写入顺序是「redo(prepare) → binlog → redo(commit)」，靠两阶段提交保证两个日志永远一致。

核心原理（2min）

日志层级类型作用写方式
redo logInnoDB 引擎层物理日志（对某页某偏移的修改）崩溃恢复，保证持久性循环写，固定大小
undo logInnoDB 引擎层逻辑日志（旧值 + 版本指针）事务回滚 + MVCC 版本链随事务生成，purge 回收
binlogServer 层逻辑日志（SQL 或行变更）主从复制、数据恢复、审计顺序追加写

一条 UPDATE 的执行与写入顺序： ① 先改 BufferPool 中的页（WAL：先写 redo log 记录这次修改，redo 标为 PREPARE 状态并 fsync）→ ② Server 层写 binlog 并 fsync → ③ 写 redo 的 COMMIT 标记，事务正式提交 → ④ purge undo、释放锁。看到这个顺序先别急着背，问一句「为什么 redo 要分成 PREPARE 和 COMMIT 两笔、中间还夹个 binlog」——答案是 redo 和 binlog 是两个独立日志，谁先谁后都可能不一致，必须用「两阶段」把它们绑成一步，这正是下一节要讲的。

底层深入（5-10min）

redo 是怎么被写出来的（mtr/mtr0mtr.cc）： 前面说「WAL 先写 redo 再落盘」，这个「写 redo」在 InnoDB 里的入口是 mini-transaction 的提交——mtr_t::commit() 判断这次修改有没有产生日志记录，有就调 cmd.execute() 把 redo 写进日志缓冲区。

void mtr_t::commit() {
  ut_ad(is_active());
  ut_ad(!is_inside_ibuf());
  ut_ad(m_impl.m_magic_n == MTR_MAGIC_N);
  m_impl.m_state = MTR_STATE_COMMITTING;

  DBUG_EXECUTE_IF("mtr_commit_crash", DBUG_SUICIDE(););

  Command cmd(this);

  if (has_any_log_record() ||
      (has_modifications() && m_impl.m_log_mode == MTR_LOG_NO_REDO)) {
    ut_ad(!srv_read_only_mode || m_impl.m_log_mode == MTR_LOG_NO_REDO);

    cmd.execute();
  } else {
    cmd.release_all();
    cmd.release_resources();
  }
#ifndef UNIV_HOTBACKUP
  check_nolog_and_unmark();
#endif /* UNIV_HOTBACKUP */

  ut_d(remove_from_debug_list());
}

has_any_log_record() 为真（这次修改生成了 redo 记录）才走 cmd.execute() 落日志，否则直接释放资源——「只读事务不产生 redo」在代码层面就是这个分支。每条 redo 记录要么是单条（打 MLOG_SINGLE_REC_FLAG 标记），要么多条拼一起、末尾追加 MLOG_MULTI_REC_END 分隔符。

bool mtr_t::Command::prepare_write() {
  switch (m_impl->m_log_mode) {
    case MTR_LOG_SHORT_INSERTS:
      ut_d(ut_error);
      /* fall through (write no redo log) */
      [[fallthrough]];
    case MTR_LOG_NO_REDO:
    case MTR_LOG_NONE:
      ut_ad(m_impl->m_log.size() == 0);
      return false;
    case MTR_LOG_ALL:
      break;
    default:
      ut_d(ut_error);
      ut_o(return false);
  }

  const auto n_recs = m_impl->m_n_log_recs;
  ut_a(0 < n_recs);
  ut_ad(0 < m_impl->m_log.size());
  ut_ad(ib::redo::handler != nullptr);

  if (n_recs <= 1) {
    /* Flag the single log record as the
    only record in this mini-transaction. */

    *m_impl->m_log.front()->begin() |= MLOG_SINGLE_REC_FLAG;

  } else {
    /* Because this mini-transaction comprises
    multiple log records, append MLOG_MULTI_REC_END
    at the end. */

    mlog_catenate_ulint(&m_impl->m_log, MLOG_MULTI_REC_END, MLOG_1BYTE);
  }

  return true;
}

MTR_LOG_NO_REDO 分支直接返回 false 不写日志，对应临时表、只读操作这类明确不需要 redo 的场景；普通数据页修改走 MTR_LOG_ALL，把这条 mini-transaction 的日志记录序列化进 m_log 缓冲区。这就是「物理日志」在源码里的真实形态——一组「页号 + 偏移 + 新值」的字节流。

两阶段提交解决「双日志一致性」： 先看清问题再理解解法：如果 redo 和 binlog 一个写成功一个写失败，主库和从库（通过 binlog 同步）就会不一致——主库说提交了、从库说没有，两边从此分叉。所以 InnoDB 把提交拆成 Prepare（写 redo PREPARE，内含 XID）和 Commit（写 binlog，再写 redo COMMIT）两个阶段，崩溃恢复时以 binlog 为准：

为什么先 redo 后 binlog 而不是反过来？ 若先 binlog 后 redo，在「binlog 写完、redo 还没写」之间崩溃，binlog 说事务存在、redo 说不存在，两种日志互相矛盾，没有裁决依据；先 redo 后 binlog 则让 binlog 成为「提交决定书」——redo PREPARE 只是候选，binlog 完整才是决定。这里值得记的是一个通用原则：两个东西要达成一致，必须让「最后一个落笔的」成为裁决者，并且「前面的都是候选、可撤回」——先 redo 后 binlog 就符合这个原则，反着来就没有裁决者了。

XID 贯穿两端： XID = server_uuid:transaction_seq，崩溃恢复时用 XID 把 redo 的 PREPARE 和 binlog 记录对起来。

组提交（Group Commit）优化： 高并发下每个事务单独 fsync 是磁盘 IOPS 瓶颈，组提交把多个事务的 binlog 合并成一次 fsync，吞吐大幅提升。参数 binlog_group_commit_sync_delay（等待时间）+ binlog_group_commit_sync_no_delay_count（凑够多少立即刷）。注意：MySQL 的 2PC 是「同进程内双日志协调」，不是 XA 分布式事务，没有网络分区和协调者挂起问题。

⑦ SQL 优化

一句话结论

判断一条 SQL 该不该优化，就看 EXPLAIN 的三个信号——type 是否出现 ALL 全表扫描、key 是不是 NULL、Extra 有没有 Using filesort / Using temporary；优化的主旋律是「让扫描和过滤在索引层完成、减少回表、让排序走索引」。

核心原理（2min）

EXPLAIN 关键字段：

通用优化三板斧： ① 建联合索引让 WHERE + ORDER BY 都走索引，消除 filesort；② 用覆盖索引消除回表；③ 大表 LIMIT 深分页用延迟关联或游标分页。这三板斧背后其实是同一个目标：干掉「随机 IO」和「排序」这两个最贵的操作——filesort 是排序没走索引，回表是随机 IO，深分页是两者兼有。遇到慢 SQL，先问「随机 IO 多不多、排序走没走索引」，方向就不会错。

底层深入（5-10min）

深分页 LIMIT 100000,10 为什么越来越慢： 先定位慢的根源在哪——是「扫了 10 万行」这件事，还是「回表」这件事？LIMIT 语义是「取前 offset+limit 条、丢弃前 offset 条」，LIMIT 100000,10 实际扫了 100010 条，且走二级索引时每条都要回表（100010 次随机 IO）。扫 10 万行是不可避免的，但回表这 10 万次随机 IO 是可以省的——两个解法都奔着「只对最后 10 条回表」去的。两种解法：

ICP 的定量收益： 一张 100 万行的表，user_id>=100 命中 60 万行、其中 age>50 的 20 万行，有 ICP 时回表从 60 万降到 20 万，回表次数减少 66.7%。但 ICP 不减少索引扫描行数，实际收益取决于 BufferPool 命中率。

ICP 在 InnoDB 里的真实入口（row/row0sel.cc）： 前面说的「下推条件到存储引擎层」就是下面这个函数——扫描索引记录时，先判断 prebuilt->idx_cond 是否有下推条件，有就只转换条件所需的列、再调 innobase_index_cond 求值。

  if (!prebuilt->idx_cond) {
    return (ICP_MATCH);
  }

  MONITOR_INC(MONITOR_ICP_ATTEMPTS);

  /* Convert to MySQL format those fields that are needed for
  evaluating the index condition. */

  if (prebuilt->blob_heap != nullptr) {
    mem_heap_empty(prebuilt->blob_heap);
  }

  for (i = 0; i < prebuilt->idx_cond_n_cols; i++) {
    const mysql_row_templ_t *templ = &prebuilt->mysql_template[i];

    /* Skip virtual columns */
    if (templ->is_virtual) {
      continue;
    }

    if (!row_sel_store_mysql_field(
            mysql_rec, prebuilt, rec, prebuilt->index, prebuilt->index, offsets,
            templ->icp_rec_field_no, templ, ULINT_UNDEFINED, nullptr,
            prebuilt->blob_heap)) {
      return (ICP_NO_MATCH);
    }
  }

  /* We assume that the index conditions on
  case-insensitive columns are case-insensitive. The
  case of such columns may be wrong in a secondary
  index, if the case of the column has been updated in
  the past, or a record has been deleted and a record
  inserted in a different case. */
  result = innobase_index_cond(prebuilt->m_mysql_handler);

if (!prebuilt->idx_cond) return (ICP_MATCH); 是「没有下推条件就直接放行」的快速通道，也解释了覆盖索引为何自动跳过 ICP（它本就没回表）。循环里的 row_sel_store_mysql_field 只把条件判断需要的列转成 MySQL 格式，转不出来直接返回 ICP_NO_MATCH——这就是「回表前先过滤」的落点。

优化器选错索引怎么办： FORCE INDEX(idx) 强制、ANALYZE TABLE 更新统计信息、OPTIMIZER_TRACE 看完整代价估算过程。

⑧ 主从架构

一句话结论

主从复制的本质是「主库把 binlog 发给从库、从库重放」，瓶颈和丢数据风险都出在这个链路——用半同步复制防丢数据、GTID 做自动续接、MTS 并行回放压延迟，共同构成一套可读从、可切换的高可用架构。

核心原理（2min）

复制模式三选一（数据丢失 vs 性能的权衡）： 三种模式其实是同一条刻度轴上的三个点——「主库提交前，要等几个从库确认」。先问自己：既然全同步最安全，为什么不全用全同步？——因为每多等一个从库、每多等一次网络往返，写入延迟就高一截，而「零丢失」往往不是所有业务都要的。选哪种，取决于你更怕「丢数据」还是更怕「写变慢」。

GTID 替代 binlog position： 传统 MASTER_LOG_FILE + MASTER_LOG_POS 是物理偏移，主从切换后全变了、要人工算位置；GTID = source_uuid:transaction_id 是逻辑标识，同一事务在任何机器上 GTID 相同，从库连新主时发自己的 gtid_executed 集合，主库自动求差集推送缺失事务——切换零配置、不重不丢。

底层深入（5-10min）

主从延迟的根因与解法： 根因是「主库多线程并发写，从库单线程串行回放」，一个大事务（批量 UPDATE）阻塞后续所有事务。那为什么不干脆让从库也并发回放？——难点在事务之间有依赖，乱序回放会破坏一致性；所以核心问题从「要不要并发」变成了「怎么判断哪些事务能安全并发」。解法是 MTS（Multi-Threaded Slave）多线程并行回放：

MTS 依赖判定的真实代码（sql/rpl_trx_tracking.cc）： last_committed 和 sequence_number 不是从库回放时瞎猜的，而是主库在提交阶段由 Commit_order_trx_dependency_tracker::get_dependency 算好、写进 binlog 随事务一起下发的（这段在 Server 层，因为复制是 binlog 机制、不属于 InnoDB 引擎层）。

void Commit_order_trx_dependency_tracker::get_dependency(
    THD *thd, bool parallelization_barrier, int64 &sequence_number,
    int64 &commit_parent) {
  Transaction_ctx *trn_ctx = thd->get_transaction();

  assert(trn_ctx->sequence_number > m_max_committed_transaction.get_offset());

  sequence_number =
      trn_ctx->sequence_number - m_max_committed_transaction.get_offset();

  if (trn_ctx->last_committed <= m_max_committed_transaction.get_offset())
    commit_parent = SEQ_UNINIT;
  else
    commit_parent =
        std::max(trn_ctx->last_committed, m_last_blocking_transaction) -
        m_max_committed_transaction.get_offset();

  if (is_trx_unsafe_for_parallel_slave(thd) || parallelization_barrier)
    m_last_blocking_transaction = trn_ctx->sequence_number;
}

这里 sequence_number 是事务提交的全局递增序号，commit_parent 就是 binlog 里记下的 last_committed——正文「Tj.last_committed >= Ti.sequence_number 即可并行」的两个字段就是它俩。is_trx_unsafe_for_parallel_slave 判出「不确定能否安全并行」的事务会被强制做成 barrier（更新 m_last_blocking_transaction），后续事务的 last_committed 都排到它之后——这正是从库「既能并发、又不破坏一致性」的判定依据。

高可用（MHA）与脑裂防护： MHA 是「主从 + 外部管理器」，故障切换五阶段：确认主真死（secondary_check_script 从另一网络路径再探测，区分「网络分区」和「真宕机」）→ 选同步延迟最小的从库 → 补差异日志 → 切换 → VIP 漂移。防脑裂靠 STONITH（Shoot The Other Node In The Head，物理断电/API 强制关机旧主），因为 VIP 飘走不代表旧主不再收写，只有杀掉旧主进程才彻底杜绝双主。若零数据丢失是硬约束（金融），用 MGR（组复制 + Paxos，RPO=0）替代 MHA。

追问清单

1. 事务的 ACID 分别怎么实现？undolog 和 redolog 各负责什么？

一句话结论：原子性靠 undo、隔离性靠锁 + MVCC、持久性靠 redo、一致性是前三者共同结果。

展开：undo log 记录修改前旧值，回滚时沿版本链恢复，负责原子性和 MVCC 读视图；redo log 记录「对某页的物理修改」，WAL 先写 redo 再落盘、崩溃后重放，负责持久性。隔离性分两层——写写冲突靠行锁/间隙锁，读写冲突靠 MVCC 快照读不加锁。一致性不是独立机制，靠原子 + 隔离 + 持久 + 约束（唯一索引、外键、NOT NULL）共同保证。

2. MVCC 核心原理？ReadView 在 RR 和 RC 下区别？

一句话结论：MVCC = 每行隐藏列 DB_TRX_ID + DB_ROLL_PTR 串成版本链，读事务用 ReadView 四字段判断每个版本可见性，读不加锁；RR 和 RC 的唯一区别是 ReadView 生成时机。

展开：ReadView 含 m_ids（活跃事务集合）、min_trx_id、max_trx_id。RC 每次 SELECT 重新生成 ReadView，能读到已提交的新数据；RR 只在第一次 SELECT 生成、后续复用，保证可重复读。注意 RR 只防了快照读的幻读，FOR UPDATE 等当前读仍会看到新插入的行，靠间隙锁防。

3. InnoDB 和 MyISAM 核心区别？为什么默认 InnoDB？

一句话结论：InnoDB 支持事务 + 行锁 + 聚簇索引 + MVCC + 崩溃恢复，MyISAM 只有表锁、不支持事务、无 redo，所以默认 InnoDB。

展开：MyISAM 索引数据分离（叶子存行地址）、count(*) 有计数器 O(1) 快，但写时全表锁、崩溃后要 repair。InnoDB 主键即聚簇索引、二级索引回表，写并发高、崩溃靠 redo 自动恢复。现代业务强依赖事务和并发写，这是 MyISAM 做不到的。

4. Buffer Pool 的 LRU 怎么优化？为什么分 young 和 old 区？

一句话结论：分区 LRU 把链表分成 young（前 63%）和 old（后 37%），新页进 old 区，待满 1 秒再被访问才晋升 young，防止全表扫描污染热数据。

展开：传统 LRU 一次全表扫描就把热页挤出缓存（BufferPool 污染）。分区后，全表扫描读一页间隔远小于 innodb_old_blocks_time=1000ms，冷页在 old 区一闪而过被淘汰，碰不到 young 区的热点页。分 old 区的目的就是给「冷页」一个隔离的入口和观察期。

5. B+ 树为什么适合做数据库索引？和 B 树、Hash 比？

一句话结论：B+ 树非叶子不存数据、树更矮，磁盘 IO 更少，且叶子用双向链表串联，天然支持范围查询和排序。

展开：vs B 树——B+ 树数据全在叶子，查询次数稳定、范围查询方便；vs Hash——Hash 等值 O(1) 但不支持范围/排序、冲突会退化，所以只做内存 AHI 加速。B+ 树的核心价值是「一页放更多键 → 树矮 → IO 少」加「叶子有序链表 → 范围友好」。

6. 什么情况索引失效？举 5 个 SQL 例子。

一句话结论：索引失效本质是「破坏了 B+ 树有序性」或「回表代价超过全表扫」，优化器放弃走索引。

展开：① WHERE func(col)=1 函数破坏有序性；② WHERE col=123（col 是 varchar）隐式转换=函数；③ WHERE col LIKE '%abc' 前导通配符；④ WHERE a=1 OR b=2（b 无索引）OR 分支逼全表扫；⑤ WHERE a>10 ORDER BY b 范围后列无序，排序走 filesort。此外还有联合索引不满足最左前缀、!=/NOT IN、IS NULL 判断优化器评估后认为全表更快等情况。

7. 间隙锁解决什么问题？什么隔离级别下生效？

一句话结论：间隙锁锁住两个索引记录之间的空隙、阻止 INSERT，解决 RR 级别下「当前读」的幻读；只在 RR 隔离级别下生效，RC 下退化成只锁命中的行。

展开：快照读的幻读已经靠 MVCC 解决，但 SELECT ... FOR UPDATE 是当前读，读最新版本，必须靠「锁住范围间隙」防止别的事务在区间内插入新行。RC 不要求可重复读、幻读可接受，所以不用间隙锁（代价是并发下降）。

8. binlog / redolog / undolog 分别做什么？update 写入顺序？

一句话结论：redo 物理日志保持久性、undo 记录旧值保原子性和 MVCC、binlog 逻辑日志用于主从复制；update 顺序是 redo(prepare) → binlog → redo(commit)。

展开：先改 BufferPool 页 + 写 redo PREPARE 并 fsync，再写 binlog 并 fsync，最后写 redo COMMIT 标记。这套两阶段提交保证双日志一致：崩溃恢复以 binlog 为准——binlog 有该事务就提交、没有就回滚，主从永远一致。

9. 怎么判断一条 SQL 需不需要优化？Explain 的 key 列看到什么放心？

一句话结论：看 EXPLAIN 三处——type 是否为 ALL、key 是否为 NULL、Extra 有没有 filesort/temporary；key 非 NULL 且 type 为 const/eq_ref/ref/range 就基本放心。

展开：key 列有值说明实际用上了索引，再结合 type（ref/range 说明等值或范围命中索引）和 Extra（Using index 覆盖索引最优，Using index condition ICP 次之）判断质量。出现 Using filesort + 大数据量就是性能杀手，要检查 ORDER BY 列是否在联合索引中。

10. 主从延迟怎么产生？怎么处理？

一句话结论：根因是主库多线程并发写、从库单线程串行回放，大事务阻塞后续事务；处理用 MTS 并行回放 + 半同步复制 + 读写分离兜底。

展开：开启 slave_parallel_workers=N + slave_parallel_type=LOGICAL_CLOCK（按事务提交时间戳判依赖，单库场景也能并行，DATABASE 模式单库会退化）；把大事务拆小；对实时性要求高的读强制走主库，从库只承接容忍延迟的读。监控用 Seconds_Behind_Master 和 P99 延迟告警。

Share this post on:
Share this post via WhatsAppShare this post on FacebookShare this post on XShare this post via TelegramShare this post on PinterestShare this post via email
Previous Post
Redis 面试回答——数据结构、持久化、高可用与缓存设计
Next Post
Java 基础面试回答——IO 模型、HashMap、ArrayList、Java8 与虚拟线程