好的我们继续按照原大纲的结构对数据库★★★★★部分进行详细补充。这部分以 MySQL 为核心涵盖存储引擎、索引、事务隔离、日志体系、锁机制以及 SQL 优化的完整方法论。三、数据库核心能力 20%数据库是绝大多数业务系统的最终一致性保障。高级工程师不仅要能写出复杂的 SQL更要理解数据库内核的工作机制能够在高并发场景下设计出高性能、高可用的数据存储方案。3.1 MySQL 体系架构概览MySQL 的整体架构分为三层层级组件职责客户端层ConnectorsJDBC、ODBC、Python 等建立连接、认证、权限校验Server 层连接池、SQL 接口、解析器、优化器、缓存8.0 已移除SQL 解析、优化、执行计划生成存储引擎层InnoDB、MyISAM、Memory 等数据的实际存储和读取、事务支持InnoDB vs MyISAM 核心对比★★★★★ 必问特性InnoDBMyISAM事务支持✅ ACID❌行级锁✅❌表级锁外键约束✅❌MVCC✅❌聚簇索引✅❌全文索引✅5.6✅崩溃恢复✅Redo Log❌适用场景OLTP 高并发读写读多写少、数据仓库3.2 索引核心原理★★★★★3.2.1 聚簇索引Clustered Index与二级索引Secondary Index聚簇索引又称主键索引InnoDB 中表数据本身就是索引数据行存储在 BTree 的叶子节点中。如果定义了主键则主键作为聚簇索引如果没有主键则使用第一个唯一非空索引如果还没有则 InnoDB 隐式生成 6 字节的ROW_ID作为聚簇索引。特性叶子节点存储整行数据完整记录。二级索引非聚簇索引、辅助索引叶子节点存储的是索引列值 主键值。查询过程先扫描二级索引获得主键值再回表到聚簇索引查询完整行数据。如果查询列全部在二级索引中则无需回表称为覆盖索引Covering Index。3.2.2 BTree 索引结构深度剖析MySQL 使用 BTree 而非 B-Tree 的原因★★★★★查询稳定性所有数据都在叶子节点每次查询的 IO 次数固定树高度。范围查询高效叶子节点通过链表连接范围查询只需遍历链表。更高的扇出Fan-out非叶子节点只存索引值不存数据页大小固定可存储更多索引键 → 树更矮 → IO 更少。BTree 层数与数据量估算假设 BTree 页大小 16KB主键为 bigint8字节 指针6字节 14字节一个页可存约 1170 个索引条目。高度为 3 时根节点 1 个页第二层 1170 个页叶子层 1170 × 1170 约 136 万个页。每个叶子页可存约 16 行数据按行大小 1KB 估算→ 总数据量 ≈ 136 万 × 16 ≈2176 万行。结论3 层 BTree 足以支撑千万级数据索引查询仅需 2~3 次磁盘 IO。3.2.3 索引失效场景★★★★★ 高频 SQL 优化考点场景原因示例对索引列使用函数计算破坏索引有序性WHERE DATE(create_time) 2024-01-01→ 应改为create_time BETWEEN ... AND ...隐式类型转换字符串列与数字比较WHERE phone 13800001111phone 为 varchar→ 需要加引号使用!或不等值无法利用索引有序性可能导致全表扫描使用LIKE %xxx通配符在开头无法匹配前缀LIKE xxx%可以走索引OR 条件中左右列索引不一致MySQL 难以选择最优执行计划可将 OR 拆分为 UNION联合索引违反最左前缀原则索引多列有序性依赖左列(a, b, c)索引查询条件只有b或c不走索引范围查询后的列范围查询列后的索引列失效WHERE a 1 AND b 2只能用到 a 的索引3.2.4 联合索引Composite Index最左前缀原则核心规则联合索引(a, b, c)实际上等价于三个索引(a)、(a, b)、(a, b, c)。查询条件必须包含最左列a索引才能生效。可走索引的查询条件WHERE a 1✅WHERE a 1 AND b 2✅WHERE a 1 AND b 2 AND c 3✅WHERE a 1 AND c 3✅仅 a 列走索引c 列无法利用WHERE a 1 AND b 2✅a 走范围b 失效不走索引的条件WHERE b 2❌WHERE c 3❌WHERE b 2 AND c 3❌索引下推ICPIndex Condition PushdownMySQL 5.6 引入。在没有 ICP 之前即使索引部分生效也要回表取完整行再判断其他条件。启用 ICP 后可以在索引层面直接过滤掉不满足条件的记录减少回表次数。例如WHERE a 1 AND b 2使用 ICP 时在遍历索引a时直接判断 b2不满足则跳过无需回表。3.2.5 MRRMulti-Range Read优化针对范围查询 回表的场景。传统做法按索引顺序扫描每次回表随机读取磁盘随机 IO。MRR 优化将查到的行主键先排序放入 read_rnd_buffer再按主键顺序回表读取将随机 IO 转为顺序 IO。生效条件mrronmrr_cost_basedoff强制开启。3.3 事务隔离级别与 MVCC★★★★★3.3.1 SQL 标准定义的四种隔离级别隔离级别脏读Dirty Read不可重复读Non-Repeatable Read幻读Phantom ReadREAD UNCOMMITTED✅ 可能✅ 可能✅ 可能READ COMMITTED❌✅ 可能✅ 可能REPEATABLE READMySQL 默认❌❌MVCC 保证❌MVCC 间隙锁保证SERIALIZABLE❌❌❌读加锁3.3.2 MVCCMulti-Version Concurrency Control—— 多版本并发控制MVCC 是 InnoDB 实现 RC 和 RR 隔离级别的核心技术读写不互斥极大提升了并发性能。三个核心组件隐藏列Hidden ColumnsDB_TRX_ID创建或最后一次修改该行的事务 ID。DB_ROLL_PTR回滚指针指向 Undo Log 中该行的旧版本记录。DB_ROW_ID当表没有主键时InnoDB 用该列作为聚簇索引。Undo Log回滚日志记录数据行的历史版本类似于 Git 的提交历史。通过DB_ROLL_PTR串联成版本链。Read View读视图是什么当前事务可见的数据快照。包含四个重要属性creator_trx_id当前事务 ID。up_limit_id当前活跃的最小事务 ID即低水位。low_limit_id当前未分配的最小事务 ID即高水位大于等于它的事务都不可见。trx_ids当前活跃事务 ID 列表。可见性规则RC vs RR 的核心差异RC 级别每次执行 SELECT 语句时重新生成Read View。RR 级别事务内第一次执行 SELECT 时生成Read View整个事务期间复用不会更新。3.3.3 RR 级别下如何避免幻读快照读Snapshot ReadSELECT ...查询走 MVCC 版本链无论其他事务插入了多少新行当前事务看到的仍然是创建 Read View 时的数据快照因此幻读被 MVCC 天然屏蔽。当前读Current ReadSELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、INSERT、UPDATE、DELETE等操作读取的是最新版本。针对当前读的幻读问题InnoDB 引入Next-Key Lock行锁 间隙锁的组合来锁定范围。3.3.4 为什么 RR 可以避免幻读总结MVCC快照读解决了快照读的幻读Next-Key Lock当前读解决了当前读的幻读。两者结合使得 MySQL InnoDB 在 RR 级别下完美避免了幻读。3.4 InnoDB 事务日志体系★★★★★3.4.1 Redo Log重做日志—— 保证持久性Durability作用数据库崩溃恢复时重放 Redo Log确保已提交事务的修改不丢失WAL 技术——Write-Ahead Logging先写日志后写磁盘。存储物理日志循环写入固定大小的ib_logfile0、ib_logfile1文件两个文件轮换使用大小由innodb_log_file_size控制。刷盘策略innodb_flush_log_at_trx_commit参数控制0每秒刷盘最高性能可能丢失 1 秒数据1默认每次事务提交刷盘最安全性能最低2每次提交写入 OS Cache每秒刷盘性能和安全的折中3.4.2 Undo Log回滚日志—— 保证原子性Atomicity作用事务回滚时将数据恢复到修改前的状态。存储逻辑日志存储在 Undo Tablespaceundo_001、undo_002记录的是如何逆操作INSERT → DELETEDELETE → INSERTUPDATE → 旧值。清理Purge 线程异步清理不再需要的 Undo Log当没有事务需要访问旧版本数据时。3.4.3 Binlog归档日志—— 保证主从复制与数据恢复作用记录所有逻辑修改操作DDL DML用于主从复制、数据备份恢复、数据审计。格式STATEMENT记录 SQL 原文、ROW记录行级别变化推荐、MIXED混合模式。与 Redo Log 的区别特性Redo LogBinlog存储引擎InnoDB 特有MySQL Server 层通用日志内容物理日志页级别修改逻辑日志SQL 或行变化写入时机事务执行过程中不断写入事务提交时写入空间管理循环覆盖固定大小追加写入可设置过期时间主要用途崩溃恢复Crash-safe主从复制、数据恢复3.4.4 两阶段提交2PC—— 保证 Redo Log 和 Binlog 一致性问题MySQL 中 Redo Log 和 Binlog 是两套独立的日志系统若无协调机制在数据库崩溃时可能出现“Redo Log 有数据但 Binlog 没记录”或相反的情况导致主从数据不一致。解决方案两阶段提交2PC由XA RECOVER协调Prepare 阶段写 Redo Log状态为PREPARE刷盘。Commit 阶段写 Binlog刷盘。Binlog 写入成功后将 Redo Log 状态改为COMMIT事务正式完成。崩溃恢复规则如果 Redo Log 处于PREPARE状态且 Binlog 完整已写入则提交该事务。如果 Redo Log 处于PREPARE状态但 Binlog 未写入则回滚该事务。如果 Redo Log 已COMMIT则直接提交。3.5 InnoDB Buffer Pool —— 缓冲池作用缓存磁盘数据页到内存减少磁盘 IO是 MySQL 性能优化的核心之一。结构LRU 链表 Flush 链表 Free 链表。LRU 优化采用冷热分离的 LRUold区占 3/8young区占 5/8。新数据页先放入old区头部。如果数据在old区停留超过innodb_old_blocks_time默认 1000ms再次访问时才提升到young区。目的防止全表扫描将热点数据挤出 LRU预读 全表扫描污染问题。预读机制Read-Ahead线性预读顺序读取一个区的多个页后异步预读后续页。随机预读不连续页访问时预读5.5 后默认关闭。3.6 Change Buffer写缓冲区原 Insert Buffer作用缓存二级索引的修改操作INSERT、UPDATE、DELETE减少随机磁盘 IO。MySQL 5.5 中仅支持 INSERT5.6 扩展为支持 UPDATE/DELETE因此改名为 Change Buffer。工作原理当修改二级索引页时如果该页不在 Buffer Pool 中不立即读取磁盘页而是将修改记录在 Change Buffer 中。后续该页被读入 Buffer Pool 时再合并Merge这些修改。适用条件非唯一二级索引唯一索引需要立即检查唯一性约束无法延迟。3.7 Double Write双写—— 解决页断裂Partial Write问题InnoDB 页大小 16KB操作系统每次写 4KB若写入 4KB 时系统崩溃会导致数据页损坏Partial Write。解决方案先将脏页复制到内存中的Double Write Buffer2MB。将 Double Write Buffer 顺序写入磁盘的共享表空间ibdata1中双写区域。然后再将脏页写入实际数据文件。恢复如果数据页损坏从双写区域拷贝备份进行修复。注意Double Write 对性能有约 5%-10% 的影响但为了保证数据完整性建议保持开启5.6 默认开启不能关闭。3.8 InnoDB 锁机制★★★★★3.8.1 锁粒度锁类型说明全局锁Global LockFLUSH TABLES WITH READ LOCK整个库只读用于备份表级锁Table LockLOCK TABLES t READ/WRITEMyISAM 默认InnoDB 也可用行级锁Row LockInnoDB 默认锁定单行记录并发性能最高间隙锁Gap Lock锁定索引记录之间的间隙防止幻读RR 级别默认Next-Key Lock行锁 间隙锁的组合锁定记录本身及其前后的间隙意向锁Intention Lock表级锁表示事务想要在行上加共享锁/排他锁用于避免 DDL 与行锁冲突3.8.2 共享锁S vs 排他锁X共享锁SSELECT ... LOCK IN SHARE MODE允许其他事务读取但不能修改。排他锁XSELECT ... FOR UPDATE、INSERT、UPDATE、DELETE自动加 X 锁其他事务不能读不能写。兼容矩阵XSX❌ 冲突❌ 冲突S❌ 冲突✅ 兼容3.8.3 Next-Key Lock 与 Gap Lock 深入分析Gap Lock 出现时机RR 级别 当前读查询条件命中范围或未命中记录时Gap Lock 锁定不存在记录的间隙。示例表有 id 5, 10, 15。SELECT * FROM t WHERE id 6 FOR UPDATE会在 (5, 10)、(10, 15)、(15, ∞) 三个区间加 Gap Lock阻止其他事务插入 id 7、9、12、20 等记录。Next-Key Lock 的锁定区间对于id 10查询Next-Key Lock 锁定区间为 (5, 10] (10, 15] 即 (5, 15)。RC 级别不使用 Gap Lock这是 RC 级别无法避免幻读的根本原因。3.8.4 死锁Deadlock死锁的四个必要条件与 OS 一致互斥条件Mutual Exclusion持有并等待Hold and Wait不可抢占No Preemption循环等待Circular WaitMySQL 死锁的典型场景并发加锁顺序不一致事务 A 先锁 table1 再锁 table2事务 B 先锁 table2 再锁 table1。唯一键冲突两个并发插入相同唯一键先到的持有行锁后到的检测到重复键尝试加 S 锁但 X 锁已被持有 → 死锁。批量更新范围重叠两个事务使用不同的条件更新同一范围的数据。线上死锁排查流程★★★★★ 实战SHOW ENGINE INNODB STATUS查看最新的死锁信息LATEST DETECTED DEADLOCK部分。开启死锁日志innodb_print_all_deadlocksON将死锁记录到 MySQL error log。分析死锁图中的事务 SQL 和持有的锁类型X/S 锁 间隙锁。定位代码找到对应的 SQL 语句分析加锁顺序。解决方案统一加锁顺序所有事务按相同顺序访问表/索引。将 RR 隔离级别降为 RCRC 无 Gap Lock死锁概率大幅降低但需确认业务可接受。使用INSERT ... ON DUPLICATE KEY UPDATE减少唯一键冲突死锁。缩短事务减少锁持有时间拆分大事务。对热点行加队列排队机制避免并发冲突。3.9 SQL 优化与执行计划分析3.9.1 Explain 输出解读★★★★★ 必须熟练以 MySQL 8.0 的 Explain 输出为例重点关注的列列名核心含义重点关注值id执行顺序越大越先执行子查询 外层select_type查询类型SIMPLE最好、PRIMARY、SUBQUERY、DERIVED派生表、UNIONtable表名或别名-type最重要访问类型性能从优到差systemconsteq_refrefrangeindexALL目标至少达到 range 级别最好是 ref 或 eq_ref。ALL全表扫描通常需要优化possible_keys可能用到的索引-key实际使用的索引如果为 NULL说明未走索引key_len使用的索引字节长度可用于判断联合索引使用了多少列ref索引列与哪个值进行比较常量const或关联列rows预估扫描行数越小越好filtered存储引擎层过滤后剩余百分比越高越好100% 最优Extra关键信息额外信息重点关注Using index覆盖索引好、Using index conditionICP好、Using whereServer 层过滤、Using filesort文件排序性能差、Using temporary临时表性能差、Using join buffer没走索引用了 Join Buffer3.9.2 索引优化策略实战假设有一张 5000 万行的订单表order查询 SQLSELECTorder_id,user_id,amount,status,create_timeFROMorderWHEREuser_id12345ANDstatus1ANDcreate_timeBETWEEN2024-01-01AND2024-12-31ORDERBYcreate_timeDESCLIMIT20;执行 30 秒需要优化。优化思路分析执行计划Explain如果typeALL说明需要加索引。如果ExtraUsing filesort说明排序未走索引。创建联合索引遵循最左前缀原则条件中user_id是等值查询status是等值查询create_time是范围查询 排序字段。推荐索引(user_id, status, create_time)索引排序原因创建时间同时满足范围过滤和 ORDER BY 排序可以避免filesort。覆盖索引如果可能进一步优化当前查询 SELECT 包含了order_id主键、user_id、amount、status、create_time。如果amount不在索引中则回表取amount。如果业务允许可以把amount也加入索引但索引列太多会导致写入性能下降。使用覆盖索引 延迟关联技巧SELECTo.order_id,o.user_id,o.amount,o.status,o.create_timeFROM(SELECTorder_idFROMorderWHEREuser_id12345ANDstatus1ANDcreate_timeBETWEEN2024-01-01AND2024-12-31ORDERBYcreate_timeDESCLIMIT20)tmpJOINorderoONtmp.order_ido.order_id;子查询中只查主键可完全用覆盖索引完成二级索引包含主键。外层用主键回表取全部列但只取 20 行回表成本可控。索引下推ICP确保开启确保optimizer_switchindex_condition_pushdownon3.9.3 Join 优化 —— Join Buffer 与 Hash JoinNested Loop Join嵌套循环连接驱动表每条记录遍历被驱动表。如果被驱动表没有索引复杂度 O(N × M)性能极差。Join Buffer连接缓冲区当被驱动表无法走索引时MySQL 将驱动表的 join 列放入内存Join Buffer减少被驱动表的扫描次数。大小由join_buffer_size控制。Block Nested LoopBNL使用 Join Buffer 分批加载驱动表批量匹配被驱动表。Hash JoinMySQL 8.0.18 引入将驱动表构建为哈希表在内存中然后遍历被驱动表用哈希探测匹配。适合等值连接且大表无索引的场景。比 BNL 更高效时间复杂度 O(NM)。优化原则Join 查询中始终让小表作为驱动表被 EXPLAIN 中的第一张表且确保被驱动表的 join 列上有索引。如果无法加索引尝试升级到 MySQL 8.0 使用 Hash Join。3.9.4 分页查询优化★★★★★ 百万级分页问题 SQLSELECT*FROMordersORDERBYidLIMIT1000000,20;MySQL 需要扫描 1000020 行丢弃前 1000000 行性能极差。三种优化方案延迟关联覆盖索引 回表SELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tmpONo.idtmp.id;子查询走覆盖索引二级索引避免回表扫描 100 万行。游标分页记住最后一条 IDSELECT*FROMordersWHEREid{last_id}ORDERBYidLIMIT20;适用于顺序翻页场景如 App 无限滚动不走大 Offset性能恒定。业务场景妥协如果不可避免需要精确跳转可考虑将数据导入 Elasticsearch 或使用搜索引擎解决。本篇内容覆盖了原大纲中数据库MySQL的全量核心知识点包括存储引擎、索引结构、事务隔离与 MVCC、日志体系、Buffer Pool、锁机制以及 SQL 优化方法论。下一篇我们将继续补充 Redis 和 MQ消息队列部分。
【Java面试】——MySQL
发布时间：2026/7/26 6:36:23
好的我们继续按照原大纲的结构对数据库★★★★★部分进行详细补充。这部分以 MySQL 为核心涵盖存储引擎、索引、事务隔离、日志体系、锁机制以及 SQL 优化的完整方法论。三、数据库核心能力 20%数据库是绝大多数业务系统的最终一致性保障。高级工程师不仅要能写出复杂的 SQL更要理解数据库内核的工作机制能够在高并发场景下设计出高性能、高可用的数据存储方案。3.1 MySQL 体系架构概览MySQL 的整体架构分为三层层级组件职责客户端层ConnectorsJDBC、ODBC、Python 等建立连接、认证、权限校验Server 层连接池、SQL 接口、解析器、优化器、缓存8.0 已移除SQL 解析、优化、执行计划生成存储引擎层InnoDB、MyISAM、Memory 等数据的实际存储和读取、事务支持InnoDB vs MyISAM 核心对比★★★★★ 必问特性InnoDBMyISAM事务支持✅ ACID❌行级锁✅❌表级锁外键约束✅❌MVCC✅❌聚簇索引✅❌全文索引✅5.6✅崩溃恢复✅Redo Log❌适用场景OLTP 高并发读写读多写少、数据仓库3.2 索引核心原理★★★★★3.2.1 聚簇索引Clustered Index与二级索引Secondary Index聚簇索引又称主键索引InnoDB 中表数据本身就是索引数据行存储在 BTree 的叶子节点中。如果定义了主键则主键作为聚簇索引如果没有主键则使用第一个唯一非空索引如果还没有则 InnoDB 隐式生成 6 字节的ROW_ID作为聚簇索引。特性叶子节点存储整行数据完整记录。二级索引非聚簇索引、辅助索引叶子节点存储的是索引列值 主键值。查询过程先扫描二级索引获得主键值再回表到聚簇索引查询完整行数据。如果查询列全部在二级索引中则无需回表称为覆盖索引Covering Index。3.2.2 BTree 索引结构深度剖析MySQL 使用 BTree 而非 B-Tree 的原因★★★★★查询稳定性所有数据都在叶子节点每次查询的 IO 次数固定树高度。范围查询高效叶子节点通过链表连接范围查询只需遍历链表。更高的扇出Fan-out非叶子节点只存索引值不存数据页大小固定可存储更多索引键 → 树更矮 → IO 更少。BTree 层数与数据量估算假设 BTree 页大小 16KB主键为 bigint8字节 指针6字节 14字节一个页可存约 1170 个索引条目。高度为 3 时根节点 1 个页第二层 1170 个页叶子层 1170 × 1170 约 136 万个页。每个叶子页可存约 16 行数据按行大小 1KB 估算→ 总数据量 ≈ 136 万 × 16 ≈2176 万行。结论3 层 BTree 足以支撑千万级数据索引查询仅需 2~3 次磁盘 IO。3.2.3 索引失效场景★★★★★ 高频 SQL 优化考点场景原因示例对索引列使用函数计算破坏索引有序性WHERE DATE(create_time) 2024-01-01→ 应改为create_time BETWEEN ... AND ...隐式类型转换字符串列与数字比较WHERE phone 13800001111phone 为 varchar→ 需要加引号使用!或不等值无法利用索引有序性可能导致全表扫描使用LIKE %xxx通配符在开头无法匹配前缀LIKE xxx%可以走索引OR 条件中左右列索引不一致MySQL 难以选择最优执行计划可将 OR 拆分为 UNION联合索引违反最左前缀原则索引多列有序性依赖左列(a, b, c)索引查询条件只有b或c不走索引范围查询后的列范围查询列后的索引列失效WHERE a 1 AND b 2只能用到 a 的索引3.2.4 联合索引Composite Index最左前缀原则核心规则联合索引(a, b, c)实际上等价于三个索引(a)、(a, b)、(a, b, c)。查询条件必须包含最左列a索引才能生效。可走索引的查询条件WHERE a 1✅WHERE a 1 AND b 2✅WHERE a 1 AND b 2 AND c 3✅WHERE a 1 AND c 3✅仅 a 列走索引c 列无法利用WHERE a 1 AND b 2✅a 走范围b 失效不走索引的条件WHERE b 2❌WHERE c 3❌WHERE b 2 AND c 3❌索引下推ICPIndex Condition PushdownMySQL 5.6 引入。在没有 ICP 之前即使索引部分生效也要回表取完整行再判断其他条件。启用 ICP 后可以在索引层面直接过滤掉不满足条件的记录减少回表次数。例如WHERE a 1 AND b 2使用 ICP 时在遍历索引a时直接判断 b2不满足则跳过无需回表。3.2.5 MRRMulti-Range Read优化针对范围查询 回表的场景。传统做法按索引顺序扫描每次回表随机读取磁盘随机 IO。MRR 优化将查到的行主键先排序放入 read_rnd_buffer再按主键顺序回表读取将随机 IO 转为顺序 IO。生效条件mrronmrr_cost_basedoff强制开启。3.3 事务隔离级别与 MVCC★★★★★3.3.1 SQL 标准定义的四种隔离级别隔离级别脏读Dirty Read不可重复读Non-Repeatable Read幻读Phantom ReadREAD UNCOMMITTED✅ 可能✅ 可能✅ 可能READ COMMITTED❌✅ 可能✅ 可能REPEATABLE READMySQL 默认❌❌MVCC 保证❌MVCC 间隙锁保证SERIALIZABLE❌❌❌读加锁3.3.2 MVCCMulti-Version Concurrency Control—— 多版本并发控制MVCC 是 InnoDB 实现 RC 和 RR 隔离级别的核心技术读写不互斥极大提升了并发性能。三个核心组件隐藏列Hidden ColumnsDB_TRX_ID创建或最后一次修改该行的事务 ID。DB_ROLL_PTR回滚指针指向 Undo Log 中该行的旧版本记录。DB_ROW_ID当表没有主键时InnoDB 用该列作为聚簇索引。Undo Log回滚日志记录数据行的历史版本类似于 Git 的提交历史。通过DB_ROLL_PTR串联成版本链。Read View读视图是什么当前事务可见的数据快照。包含四个重要属性creator_trx_id当前事务 ID。up_limit_id当前活跃的最小事务 ID即低水位。low_limit_id当前未分配的最小事务 ID即高水位大于等于它的事务都不可见。trx_ids当前活跃事务 ID 列表。可见性规则RC vs RR 的核心差异RC 级别每次执行 SELECT 语句时重新生成Read View。RR 级别事务内第一次执行 SELECT 时生成Read View整个事务期间复用不会更新。3.3.3 RR 级别下如何避免幻读快照读Snapshot ReadSELECT ...查询走 MVCC 版本链无论其他事务插入了多少新行当前事务看到的仍然是创建 Read View 时的数据快照因此幻读被 MVCC 天然屏蔽。当前读Current ReadSELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、INSERT、UPDATE、DELETE等操作读取的是最新版本。针对当前读的幻读问题InnoDB 引入Next-Key Lock行锁 间隙锁的组合来锁定范围。3.3.4 为什么 RR 可以避免幻读总结MVCC快照读解决了快照读的幻读Next-Key Lock当前读解决了当前读的幻读。两者结合使得 MySQL InnoDB 在 RR 级别下完美避免了幻读。3.4 InnoDB 事务日志体系★★★★★3.4.1 Redo Log重做日志—— 保证持久性Durability作用数据库崩溃恢复时重放 Redo Log确保已提交事务的修改不丢失WAL 技术——Write-Ahead Logging先写日志后写磁盘。存储物理日志循环写入固定大小的ib_logfile0、ib_logfile1文件两个文件轮换使用大小由innodb_log_file_size控制。刷盘策略innodb_flush_log_at_trx_commit参数控制0每秒刷盘最高性能可能丢失 1 秒数据1默认每次事务提交刷盘最安全性能最低2每次提交写入 OS Cache每秒刷盘性能和安全的折中3.4.2 Undo Log回滚日志—— 保证原子性Atomicity作用事务回滚时将数据恢复到修改前的状态。存储逻辑日志存储在 Undo Tablespaceundo_001、undo_002记录的是如何逆操作INSERT → DELETEDELETE → INSERTUPDATE → 旧值。清理Purge 线程异步清理不再需要的 Undo Log当没有事务需要访问旧版本数据时。3.4.3 Binlog归档日志—— 保证主从复制与数据恢复作用记录所有逻辑修改操作DDL DML用于主从复制、数据备份恢复、数据审计。格式STATEMENT记录 SQL 原文、ROW记录行级别变化推荐、MIXED混合模式。与 Redo Log 的区别特性Redo LogBinlog存储引擎InnoDB 特有MySQL Server 层通用日志内容物理日志页级别修改逻辑日志SQL 或行变化写入时机事务执行过程中不断写入事务提交时写入空间管理循环覆盖固定大小追加写入可设置过期时间主要用途崩溃恢复Crash-safe主从复制、数据恢复3.4.4 两阶段提交2PC—— 保证 Redo Log 和 Binlog 一致性问题MySQL 中 Redo Log 和 Binlog 是两套独立的日志系统若无协调机制在数据库崩溃时可能出现“Redo Log 有数据但 Binlog 没记录”或相反的情况导致主从数据不一致。解决方案两阶段提交2PC由XA RECOVER协调Prepare 阶段写 Redo Log状态为PREPARE刷盘。Commit 阶段写 Binlog刷盘。Binlog 写入成功后将 Redo Log 状态改为COMMIT事务正式完成。崩溃恢复规则如果 Redo Log 处于PREPARE状态且 Binlog 完整已写入则提交该事务。如果 Redo Log 处于PREPARE状态但 Binlog 未写入则回滚该事务。如果 Redo Log 已COMMIT则直接提交。3.5 InnoDB Buffer Pool —— 缓冲池作用缓存磁盘数据页到内存减少磁盘 IO是 MySQL 性能优化的核心之一。结构LRU 链表 Flush 链表 Free 链表。LRU 优化采用冷热分离的 LRUold区占 3/8young区占 5/8。新数据页先放入old区头部。如果数据在old区停留超过innodb_old_blocks_time默认 1000ms再次访问时才提升到young区。目的防止全表扫描将热点数据挤出 LRU预读 全表扫描污染问题。预读机制Read-Ahead线性预读顺序读取一个区的多个页后异步预读后续页。随机预读不连续页访问时预读5.5 后默认关闭。3.6 Change Buffer写缓冲区原 Insert Buffer作用缓存二级索引的修改操作INSERT、UPDATE、DELETE减少随机磁盘 IO。MySQL 5.5 中仅支持 INSERT5.6 扩展为支持 UPDATE/DELETE因此改名为 Change Buffer。工作原理当修改二级索引页时如果该页不在 Buffer Pool 中不立即读取磁盘页而是将修改记录在 Change Buffer 中。后续该页被读入 Buffer Pool 时再合并Merge这些修改。适用条件非唯一二级索引唯一索引需要立即检查唯一性约束无法延迟。3.7 Double Write双写—— 解决页断裂Partial Write问题InnoDB 页大小 16KB操作系统每次写 4KB若写入 4KB 时系统崩溃会导致数据页损坏Partial Write。解决方案先将脏页复制到内存中的Double Write Buffer2MB。将 Double Write Buffer 顺序写入磁盘的共享表空间ibdata1中双写区域。然后再将脏页写入实际数据文件。恢复如果数据页损坏从双写区域拷贝备份进行修复。注意Double Write 对性能有约 5%-10% 的影响但为了保证数据完整性建议保持开启5.6 默认开启不能关闭。3.8 InnoDB 锁机制★★★★★3.8.1 锁粒度锁类型说明全局锁Global LockFLUSH TABLES WITH READ LOCK整个库只读用于备份表级锁Table LockLOCK TABLES t READ/WRITEMyISAM 默认InnoDB 也可用行级锁Row LockInnoDB 默认锁定单行记录并发性能最高间隙锁Gap Lock锁定索引记录之间的间隙防止幻读RR 级别默认Next-Key Lock行锁 间隙锁的组合锁定记录本身及其前后的间隙意向锁Intention Lock表级锁表示事务想要在行上加共享锁/排他锁用于避免 DDL 与行锁冲突3.8.2 共享锁S vs 排他锁X共享锁SSELECT ... LOCK IN SHARE MODE允许其他事务读取但不能修改。排他锁XSELECT ... FOR UPDATE、INSERT、UPDATE、DELETE自动加 X 锁其他事务不能读不能写。兼容矩阵XSX❌ 冲突❌ 冲突S❌ 冲突✅ 兼容3.8.3 Next-Key Lock 与 Gap Lock 深入分析Gap Lock 出现时机RR 级别 当前读查询条件命中范围或未命中记录时Gap Lock 锁定不存在记录的间隙。示例表有 id 5, 10, 15。SELECT * FROM t WHERE id 6 FOR UPDATE会在 (5, 10)、(10, 15)、(15, ∞) 三个区间加 Gap Lock阻止其他事务插入 id 7、9、12、20 等记录。Next-Key Lock 的锁定区间对于id 10查询Next-Key Lock 锁定区间为 (5, 10] (10, 15] 即 (5, 15)。RC 级别不使用 Gap Lock这是 RC 级别无法避免幻读的根本原因。3.8.4 死锁Deadlock死锁的四个必要条件与 OS 一致互斥条件Mutual Exclusion持有并等待Hold and Wait不可抢占No Preemption循环等待Circular WaitMySQL 死锁的典型场景并发加锁顺序不一致事务 A 先锁 table1 再锁 table2事务 B 先锁 table2 再锁 table1。唯一键冲突两个并发插入相同唯一键先到的持有行锁后到的检测到重复键尝试加 S 锁但 X 锁已被持有 → 死锁。批量更新范围重叠两个事务使用不同的条件更新同一范围的数据。线上死锁排查流程★★★★★ 实战SHOW ENGINE INNODB STATUS查看最新的死锁信息LATEST DETECTED DEADLOCK部分。开启死锁日志innodb_print_all_deadlocksON将死锁记录到 MySQL error log。分析死锁图中的事务 SQL 和持有的锁类型X/S 锁 间隙锁。定位代码找到对应的 SQL 语句分析加锁顺序。解决方案统一加锁顺序所有事务按相同顺序访问表/索引。将 RR 隔离级别降为 RCRC 无 Gap Lock死锁概率大幅降低但需确认业务可接受。使用INSERT ... ON DUPLICATE KEY UPDATE减少唯一键冲突死锁。缩短事务减少锁持有时间拆分大事务。对热点行加队列排队机制避免并发冲突。3.9 SQL 优化与执行计划分析3.9.1 Explain 输出解读★★★★★ 必须熟练以 MySQL 8.0 的 Explain 输出为例重点关注的列列名核心含义重点关注值id执行顺序越大越先执行子查询 外层select_type查询类型SIMPLE最好、PRIMARY、SUBQUERY、DERIVED派生表、UNIONtable表名或别名-type最重要访问类型性能从优到差systemconsteq_refrefrangeindexALL目标至少达到 range 级别最好是 ref 或 eq_ref。ALL全表扫描通常需要优化possible_keys可能用到的索引-key实际使用的索引如果为 NULL说明未走索引key_len使用的索引字节长度可用于判断联合索引使用了多少列ref索引列与哪个值进行比较常量const或关联列rows预估扫描行数越小越好filtered存储引擎层过滤后剩余百分比越高越好100% 最优Extra关键信息额外信息重点关注Using index覆盖索引好、Using index conditionICP好、Using whereServer 层过滤、Using filesort文件排序性能差、Using temporary临时表性能差、Using join buffer没走索引用了 Join Buffer3.9.2 索引优化策略实战假设有一张 5000 万行的订单表order查询 SQLSELECTorder_id,user_id,amount,status,create_timeFROMorderWHEREuser_id12345ANDstatus1ANDcreate_timeBETWEEN2024-01-01AND2024-12-31ORDERBYcreate_timeDESCLIMIT20;执行 30 秒需要优化。优化思路分析执行计划Explain如果typeALL说明需要加索引。如果ExtraUsing filesort说明排序未走索引。创建联合索引遵循最左前缀原则条件中user_id是等值查询status是等值查询create_time是范围查询 排序字段。推荐索引(user_id, status, create_time)索引排序原因创建时间同时满足范围过滤和 ORDER BY 排序可以避免filesort。覆盖索引如果可能进一步优化当前查询 SELECT 包含了order_id主键、user_id、amount、status、create_time。如果amount不在索引中则回表取amount。如果业务允许可以把amount也加入索引但索引列太多会导致写入性能下降。使用覆盖索引 延迟关联技巧SELECTo.order_id,o.user_id,o.amount,o.status,o.create_timeFROM(SELECTorder_idFROMorderWHEREuser_id12345ANDstatus1ANDcreate_timeBETWEEN2024-01-01AND2024-12-31ORDERBYcreate_timeDESCLIMIT20)tmpJOINorderoONtmp.order_ido.order_id;子查询中只查主键可完全用覆盖索引完成二级索引包含主键。外层用主键回表取全部列但只取 20 行回表成本可控。索引下推ICP确保开启确保optimizer_switchindex_condition_pushdownon3.9.3 Join 优化 —— Join Buffer 与 Hash JoinNested Loop Join嵌套循环连接驱动表每条记录遍历被驱动表。如果被驱动表没有索引复杂度 O(N × M)性能极差。Join Buffer连接缓冲区当被驱动表无法走索引时MySQL 将驱动表的 join 列放入内存Join Buffer减少被驱动表的扫描次数。大小由join_buffer_size控制。Block Nested LoopBNL使用 Join Buffer 分批加载驱动表批量匹配被驱动表。Hash JoinMySQL 8.0.18 引入将驱动表构建为哈希表在内存中然后遍历被驱动表用哈希探测匹配。适合等值连接且大表无索引的场景。比 BNL 更高效时间复杂度 O(NM)。优化原则Join 查询中始终让小表作为驱动表被 EXPLAIN 中的第一张表且确保被驱动表的 join 列上有索引。如果无法加索引尝试升级到 MySQL 8.0 使用 Hash Join。3.9.4 分页查询优化★★★★★ 百万级分页问题 SQLSELECT*FROMordersORDERBYidLIMIT1000000,20;MySQL 需要扫描 1000020 行丢弃前 1000000 行性能极差。三种优化方案延迟关联覆盖索引 回表SELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tmpONo.idtmp.id;子查询走覆盖索引二级索引避免回表扫描 100 万行。游标分页记住最后一条 IDSELECT*FROMordersWHEREid{last_id}ORDERBYidLIMIT20;适用于顺序翻页场景如 App 无限滚动不走大 Offset性能恒定。业务场景妥协如果不可避免需要精确跳转可考虑将数据导入 Elasticsearch 或使用搜索引擎解决。本篇内容覆盖了原大纲中数据库MySQL的全量核心知识点包括存储引擎、索引结构、事务隔离与 MVCC、日志体系、Buffer Pool、锁机制以及 SQL 优化方法论。下一篇我们将继续补充 Redis 和 MQ消息队列部分。
