MySQL 面试核心知识点总结与原理解析 (强化版)一、 MySQL 架构与基础1. 核心架构层级连接层负责接收客户端连接、授权认证。服务层涵盖 MySQL 的大多数核心服务功能如 SQL 解析、分析、优化、缓存以及所有的内置函数跨存储引擎的功能都在这里实现如触发器、视图。存储引擎层负责数据的存储和提取。采用插件式架构支持 InnoDB、MyISAM 等。2. InnoDB vs MyISAM维度InnoDB (默认)MyISAM原理解释 (面试官追问为什么这样设计)事务✅ 支持❌ 不支持InnoDB 内部实现了 undo log 和 redo log 来保障 ACID 特性MyISAM 设计之初只为了查询快做成了轻量级。锁粒度行级锁表级锁InnoDB 在索引加锁Record Lock等并发更新互不影响MyISAM 直接锁整张表并发写性能极差。外键✅ 支持❌ 不支持维护数据完整性但现代互联网高并发架构一般不用外键由业务代码保证避免数据库层面的级联更新和锁死。恢复✅ 支持❌ 差InnoDB 靠 redo log 实现 Crash-safe宕机恢复MyISAM 宕机后极易损坏数据文件。结构聚簇索引非聚簇索引InnoDB 的主键 B 树叶子节点存放真实数据行MyISAM 的 B 树叶子节点存放数据文件的指针内存地址。 演示指定存储引擎-- 创建一张使用 InnoDB 的表MySQL 5.5 后默认CREATETABLEuser_innodb(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 创建一张使用 MyISAM 的表CREATETABLEuser_myisam(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50))ENGINEMyISAMDEFAULTCHARSETutf8mb4;二、 索引 (Index)1. 为什么使用 B 树底层原理解释非叶子节点不存数据只存主键/边界值原理操作系统按“页通常16KB”读取磁盘。如果节点里不存庞大的真实数据一页就能塞下几千个指针。这样一棵高度为 3 的 B 树就能存满上千万条数据结论树的高度大大降低矮胖磁盘 IO 次数极少通常只有2-3次。叶子节点带双向链表且数据全在叶子节点原理所有的叶子节点形成了一个完整的有序链表。结论当我们需要执行age 20的范围查询时只需找到 20 那个节点然后顺着链表一直往后拿数据即可不用再退回到父节点去遍历分支极大地提高了范围查询的效率。2. 聚簇索引 vs 非聚簇索引二级索引聚簇索引主键索引。数据和主键紧紧挨在一起聚簇。非聚簇索引比如你给name字段建的索引。它的叶子节点存放的是主键 ID。原理解释为什么二级索引不存完整数据主要是为了节省存储空间和保证数据一致性。如果每个索引都存一份完整数据不仅硬盘爆炸每次UPDATE还得同时更新所有索引里的数据存主键 ID数据只需在聚簇索引里改一份即可。3. 回表与覆盖索引回表在二级索引查到了主键 ID然后再拿着 ID 去主键树聚簇索引里走一遍 B 树查完整数据的过程。覆盖索引SELECT name, age FROM t WHERE name张三前提有联合索引(name, age)。因为要查的name和age都在这棵二级索引树上了避免了回表的磁盘 IO性能非常高。 演示回表与覆盖索引核心必考-- 假设我们有表 user主键是 id。我们给 name 字段加一个二级索引CREATEINDEXidx_nameONuser(name);-- ❌ 发生回表-- 引擎先在 idx_name 树上找到 张三 对应的主键 id1。-- 但是你要查 ageidx_name 树上没有 age只好拿 id1 回到主键树聚簇索引再查一次。SELECTid,name,ageFROMuserWHEREname张三;-- ✅ 覆盖索引 (Extra: Using index)-- 你要查的 id 和 name在 idx_name 这棵树的叶子节点上都已经有了-- 直接返回数据不需要再去主键树查性能极高。SELECTid,nameFROMuserWHEREname张三;-- 进阶如何让第一条 SQL 也变成覆盖索引建立联合索引CREATEINDEXidx_name_ageONuser(name,age);-- 此时再去查 name 和 age就全是覆盖索引了。4. 最左前缀原则原理解释联合索引(a, b, c)的 B 树是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。如果你跳过 a 直接查 b在树里 b 完全是无序的比如 a1时b2a2时b1就只能全表扫描。因此必须从最左边连续匹配遇到范围查询,,like会使得后面的字段无法利用索引的有序性。 演示最左前缀原则的生效与失效-- 创建联合索引 (a, b, c)CREATEINDEXidx_a_b_cONtest_table(a,b,c);-- ✅ 完美走索引从左到右连续匹配SELECT*FROMtest_tableWHEREa1ANDb2ANDc3;SELECT*FROMtest_tableWHEREa1ANDb2;SELECT*FROMtest_tableWHEREa1;-- ❌ 完全不走索引跳过了最左边的 aSELECT*FROMtest_tableWHEREb2ANDc3;-- ⚠️ 部分走索引中间断开或者遇到范围查询SELECT*FROMtest_tableWHEREa1ANDc3;-- a 走索引c 断层不走索引SELECT*FROMtest_tableWHEREa1ANDb10ANDc3;-- a 走索引b 走索引(找范围)c 不走索引(范围之后失效)三、 事务与 MVCC1. 事务 ACID 特性及实现原理A (原子性)靠undo log。执行前先记录旧数据出错就顺着 undo log 把数据改回去。D (持久性)靠redo log。数据先在内存改同时记录redo log后续哪怕宕机也能通过redo log恢复。I (隔离性)靠锁 MVCC保证并发时不互相干扰。C (一致性)是业务结果的最终目的由应用程序的逻辑、以及数据库的 A、I、D 特性共同来保障。 演示脏读、不可重复读、幻读究竟长啥样-- 查看当前隔离级别SELECTtransaction_isolation;-- 设置隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;-- 场景脏读 (发生在 读未提交 RU 级别)-- 事务 A:BEGIN;UPDATEaccountSETbalancebalance-100WHEREid1;-- 扣钱但还没提交-- 事务 B:SELECTbalanceFROMaccountWHEREid1;-- 查到了扣完的钱。如果A马上回滚B查到的就是脏数据-- 场景不可重复读 (发生在 读已提交 RC 级别)-- 事务 A:BEGIN;SELECTbalanceFROMaccountWHEREid1;-- 第一次读1000-- 事务 B:BEGIN;UPDATEaccountSETbalance500WHEREid1;COMMIT;-- 别人改了并提交-- 事务 A:SELECTbalanceFROMaccountWHEREid1;-- 第二次读500(同一个事务内越读越少见鬼了)-- 场景幻读 (发生在 可重复读 RR 级别)-- 事务 A:BEGIN;SELECTCOUNT(*)FROMuserWHEREage20;-- 查出 5 个人-- 事务 B:BEGIN;INSERTINTOuser(age)VALUES(25);COMMIT;-- 别人悄悄塞了一个人进去-- 事务 A:UPDATEuserSETstatus1WHEREage20;-- 发现竟然更新了 6 条数据 (见鬼了多出来个幻象)2. MVCC (多版本并发控制) 底层原理[!IMPORTANT]原理解释如果单纯用锁来保证隔离性读也加锁写也加锁并发效率就太低了。MVCC 的核心目的就是**“读写不冲突”。当别人在写数据时我不去阻塞他而是去读这条数据的历史版本快照**。版本链如何形成InnoDB 每行数据都有两个隐藏列trx_id最后修改它的事务ID和roll_pointer指向上一个版本。每次修改都会在undo log里生成一条包含旧值的记录roll_pointer就像一条锁链把连串的修改连接成了“版本链”。Read View (读视图)当事务执行SELECT时会拍一张“快照”记录当前有哪些事务还没提交比如m_ids [10, 15]。拿着读取到的行的trx_id去对比如果这个trx_id是未来产生的或者在活跃列表[10, 15]里说明还没提交那就不能看顺着roll_pointer找上一个版本直到找到一个已经提交的、自己应该看到的版本为止。RC 与 RR 的区别重点RC (读已提交)每次SELECT都重新拍快照。如果事务 10 刚刚提交新的快照里就不包含 10 了所以能读到事务 10 修改的数据出现不可重复读。RR (可重复读)只在事务第一次SELECT时拍一次快照以后一直用这个老快照。所以不管别人怎么修改、怎么提交我看到的永远是最初的数据解决不可重复读。四、 锁机制 (Lock)1. InnoDB 的行锁原理解析Record Lock (记录锁)锁某一条具体的记录。Gap Lock (间隙锁)锁住两条记录之间的空隙不锁记录本身。原理解释RR 级别下为了防止“幻读”。如果不锁间隙别人INSERT一条并在我之前提交我同样条件的查询就会多出一条记录幻读。锁住间隙就没人能插进来了。Next-Key Lock (临键锁) Record Lock Gap Lock。锁住记录本身及其左边的间隙左开右闭区间。InnoDB 的普通查询默认不加锁走MVCC但是在执行UPDATE / DELETE / SELECT ... FOR UPDATE时默认就是加临键锁。 演示如何手动加锁BEGIN;-- 加共享锁 (S锁读锁)我读的时候别人也能读(加S锁)但别人不能改(加X锁)SELECT*FROMuserWHEREid1LOCKINSHAREMODE;-- 加排他锁 (X锁写锁)我在看/改的时候别人连加S锁都不行属于唯我独尊SELECT*FROMuserWHEREid1FORUPDATE;UPDATEuserSETage20WHEREid1;-- UPDATE 语句隐式自动加排他锁(X锁)COMMIT;2. 表锁降级陷阱及其原理原理解释InnoDB 的行锁其实锁的是索引而不是真正的数据行当你执行UPDATE user SET age 30 WHERE name Tom时如果name没有索引MySQL 无法在索引树上精准定位这行只能迫不得已扫描整张表全表扫描这个过程中会把扫描过的所有行全都锁上效果等同于大瘫痪的表锁。3. 间隙锁防幻读 与 表锁降级大坑 演示Next-Key Lock 锁间隙防幻读-- 表里只有 id 1, 5, 10-- 事务 A 在 RR 隔离级别下BEGIN;SELECT*FROMuserWHEREid1ANDid10FORUPDATE;-- 此时不仅仅 id5 这行被锁住了。-- (1, 5) 和 (5, 10) 这两个间隙也被锁死了-- 事务 BINSERTINTOuser(id)VALUES(3);-- ⛔ 被阻塞插入不进去从而防止了事务 A 发生幻读 演示灾难级的面试真题 —— 行锁变表锁-- ⚠️ 假设 name 字段没有建索引BEGIN;UPDATEuserSETage30WHEREname张三;-- 灾难发生因为 name 没索引MySQL 无法定位具体哪行只能在主键树上全表扫描。-- 扫描的过程中会把所有途径的记录和间隙全锁上-- 导致其他任何事务连 UPDATE 李四、甚至 INSERT 王五 都会被阻塞挂起等同于表锁五、 MySQL 三大日志系统深度融合日志谁写的记录内容 (原理解释)核心作用特性redo logInnoDB物理日志在第 xx 个数据页偏移量 yy 的位置把值改成了张三。它记录的是底层的“手术动作”。崩溃恢复 (Crash-safe)。有了它修改内存就算没刷盘宕机了重启照样能靠重做恢复。空间固定循环写(旧日志刷盘后会被覆盖)。采用WAL 先写日志后写磁盘技术提升IO。undo logInnoDB逻辑日志你执行INSERT它记DELETE你执行UPDATE a2它记UPDATE a1。1.事务回滚(保证原子性)2. 形成MVCC的历史版本链。事务产生修改时写入随版本链回收。binlogServer层逻辑日志执行了UPDATE ... WHERE id 1这样的SQL。1.主从同步(从库解析SQL照做)2.数据恢复(重放整个建库历史)不限空间追加写(不覆盖旧日志)。核心面试大题为什么需要两阶段提交 (2PC)原理解释因为这是两个互相独立的日志redo属于引擎层面binlog属于Server层面。如果事务提交时先写完 A还没写 B 就宕机了必然导致不一致如果先写 redo log 且提交了再写 binlog 失败主库有这条数据但 binlog 没这条记录从库同步不到主备不一致。如果先写 binlog redo log 还没写就宕机了主库丢了数据但从库存有这条数据主备不一致。2PC 流程执行器把数据改完写入内存。引擎写redo log并标记为prepare状态。服务器写binlog。引擎把刚才那个redo log的状态改成commit状态。崩溃恢复逻辑验证如果发现 redo log 只是prepare但是 binlog 已经写完整了说明数据已经“公告”出去了MySQL 恢复时会继续把事务提交保证一致如果 binlog 没写完说明没人知道则彻底回滚。六、 SQL 优化与 EXPLAIN 分析1. EXPLAIN 深层理解type全表扫描 (ALL)的原理是从头到尾读完所有的聚簇索引叶子节点。索引范围扫描 (range)的原理是定位到树上的两个边界端点由于叶子节点有链表只需顺着链表拿数据底层IO大减。Using filesort说明 MySQL 无法利用索引自带的有序性只能把数据捞到内存的 Sort Buffer 里用快排或归并亲自排序极度消耗 CPU。 演示查看执行计划EXPLAINSELECTu.name,o.order_noFROMuseruJOINorders oONu.ido.user_idWHEREu.age20;重点盯防type最好是ref或range严禁ALL全表扫描。Extra看到Using temporary(用到临时表) 或者Using filesort(说明排序没走索引在内存里生排) 必须想办法加索引。2. 索引失效背后的原理解析对索引列套函数如WHERE YEAR(date) 2024B 树是以date的原始值进行排序的。你套了函数之后原始值全变了格式破坏了原本 B 树的有序性引擎只能蒙着头全表扫描。隐式类型转换字符串列传入数字138xxxMySQL 等价于偷偷在你的列上套了个CAST(phone AS signed int)同上函数破坏了索引树结构宣告失效。LIKE 左模糊%三B 树的比较原则是从第一个字符开始比较类似查字典。你上来第一个字就不确定%字典无从查起失效。如果是右模糊张%先锁定所有“张”开头的后面再判断可以走索引。 演示明明建了索引为什么不用-- 假设我们在 create_time, phone, name 上都建了单列索引-- ❌ 对列用函数B 树原本按日期字符串排的被 YEAR 一搞全乱了SELECT*FROMtableWHEREYEAR(create_time)2024;-- ✅ 正确写法SELECT*FROMtableWHEREcreate_time2024-01-01ANDcreate_time2025-01-01;-- ❌ 隐式类型转换phone 是 VARCHAR 字符串类型你传了数字MySQL 底层偷偷给你套了个 CAST 函数索引直接失效SELECT*FROMtableWHEREphone13800138000;-- ✅ 正确写法加上单引号SELECT*FROMtableWHEREphone13800138000;-- ❌ LIKE 左模糊第一字符都不确定字典没法查SELECT*FROMtableWHEREnameLIKE%三;-- ✅ 右模糊可以走索引先锁定一部分开头的人再去慢慢查SELECT*FROMtableWHEREnameLIKE张%;3. 高级 SQL 优化原理解析深分页问题 (LIMIT 1000000, 10)原理MySQL 的 LIMIT 是先查出来 1,000,010 条记录如果需要回表那就是 100万次回表然后把前 100万条全扔掉只留最后 10 条。回表代价极大。优化思路 (延迟关联)先写一个非常纯粹的子查询SELECT id FROM table LIMIT 1000000, 10这个查询只取 ID可以完美触发覆盖索引不用回表拿到这 10 个 ID 后再去拿完整的大表去 INNER JOIN这样仅产生了 10 次准确的回表查询速度提升千倍。JOIN 的小表驱动大表原理 (Index Nested-Loop Join)A JOIN B。MySQL 是以表 A 的每一行去通过网络/磁盘找表 B。A 是 100 行B 是 10万行。如果是 A 驱动 B就是循环 100 次每次在 B 树里精准二分查找如果是 B 驱动 A要循环 10万次这正是为什么被驱动表的关联字段绝对必须加索引的原因否则嵌套循环就是 100次 * 10万次全表扫描数据库直接宕机。
MySQL 面试核心知识点总结与原理解析
发布时间：2026/7/27 21:30:04
MySQL 面试核心知识点总结与原理解析 (强化版)一、 MySQL 架构与基础1. 核心架构层级连接层负责接收客户端连接、授权认证。服务层涵盖 MySQL 的大多数核心服务功能如 SQL 解析、分析、优化、缓存以及所有的内置函数跨存储引擎的功能都在这里实现如触发器、视图。存储引擎层负责数据的存储和提取。采用插件式架构支持 InnoDB、MyISAM 等。2. InnoDB vs MyISAM维度InnoDB (默认)MyISAM原理解释 (面试官追问为什么这样设计)事务✅ 支持❌ 不支持InnoDB 内部实现了 undo log 和 redo log 来保障 ACID 特性MyISAM 设计之初只为了查询快做成了轻量级。锁粒度行级锁表级锁InnoDB 在索引加锁Record Lock等并发更新互不影响MyISAM 直接锁整张表并发写性能极差。外键✅ 支持❌ 不支持维护数据完整性但现代互联网高并发架构一般不用外键由业务代码保证避免数据库层面的级联更新和锁死。恢复✅ 支持❌ 差InnoDB 靠 redo log 实现 Crash-safe宕机恢复MyISAM 宕机后极易损坏数据文件。结构聚簇索引非聚簇索引InnoDB 的主键 B 树叶子节点存放真实数据行MyISAM 的 B 树叶子节点存放数据文件的指针内存地址。 演示指定存储引擎-- 创建一张使用 InnoDB 的表MySQL 5.5 后默认CREATETABLEuser_innodb(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 创建一张使用 MyISAM 的表CREATETABLEuser_myisam(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50))ENGINEMyISAMDEFAULTCHARSETutf8mb4;二、 索引 (Index)1. 为什么使用 B 树底层原理解释非叶子节点不存数据只存主键/边界值原理操作系统按“页通常16KB”读取磁盘。如果节点里不存庞大的真实数据一页就能塞下几千个指针。这样一棵高度为 3 的 B 树就能存满上千万条数据结论树的高度大大降低矮胖磁盘 IO 次数极少通常只有2-3次。叶子节点带双向链表且数据全在叶子节点原理所有的叶子节点形成了一个完整的有序链表。结论当我们需要执行age 20的范围查询时只需找到 20 那个节点然后顺着链表一直往后拿数据即可不用再退回到父节点去遍历分支极大地提高了范围查询的效率。2. 聚簇索引 vs 非聚簇索引二级索引聚簇索引主键索引。数据和主键紧紧挨在一起聚簇。非聚簇索引比如你给name字段建的索引。它的叶子节点存放的是主键 ID。原理解释为什么二级索引不存完整数据主要是为了节省存储空间和保证数据一致性。如果每个索引都存一份完整数据不仅硬盘爆炸每次UPDATE还得同时更新所有索引里的数据存主键 ID数据只需在聚簇索引里改一份即可。3. 回表与覆盖索引回表在二级索引查到了主键 ID然后再拿着 ID 去主键树聚簇索引里走一遍 B 树查完整数据的过程。覆盖索引SELECT name, age FROM t WHERE name张三前提有联合索引(name, age)。因为要查的name和age都在这棵二级索引树上了避免了回表的磁盘 IO性能非常高。 演示回表与覆盖索引核心必考-- 假设我们有表 user主键是 id。我们给 name 字段加一个二级索引CREATEINDEXidx_nameONuser(name);-- ❌ 发生回表-- 引擎先在 idx_name 树上找到 张三 对应的主键 id1。-- 但是你要查 ageidx_name 树上没有 age只好拿 id1 回到主键树聚簇索引再查一次。SELECTid,name,ageFROMuserWHEREname张三;-- ✅ 覆盖索引 (Extra: Using index)-- 你要查的 id 和 name在 idx_name 这棵树的叶子节点上都已经有了-- 直接返回数据不需要再去主键树查性能极高。SELECTid,nameFROMuserWHEREname张三;-- 进阶如何让第一条 SQL 也变成覆盖索引建立联合索引CREATEINDEXidx_name_ageONuser(name,age);-- 此时再去查 name 和 age就全是覆盖索引了。4. 最左前缀原则原理解释联合索引(a, b, c)的 B 树是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。如果你跳过 a 直接查 b在树里 b 完全是无序的比如 a1时b2a2时b1就只能全表扫描。因此必须从最左边连续匹配遇到范围查询,,like会使得后面的字段无法利用索引的有序性。 演示最左前缀原则的生效与失效-- 创建联合索引 (a, b, c)CREATEINDEXidx_a_b_cONtest_table(a,b,c);-- ✅ 完美走索引从左到右连续匹配SELECT*FROMtest_tableWHEREa1ANDb2ANDc3;SELECT*FROMtest_tableWHEREa1ANDb2;SELECT*FROMtest_tableWHEREa1;-- ❌ 完全不走索引跳过了最左边的 aSELECT*FROMtest_tableWHEREb2ANDc3;-- ⚠️ 部分走索引中间断开或者遇到范围查询SELECT*FROMtest_tableWHEREa1ANDc3;-- a 走索引c 断层不走索引SELECT*FROMtest_tableWHEREa1ANDb10ANDc3;-- a 走索引b 走索引(找范围)c 不走索引(范围之后失效)三、 事务与 MVCC1. 事务 ACID 特性及实现原理A (原子性)靠undo log。执行前先记录旧数据出错就顺着 undo log 把数据改回去。D (持久性)靠redo log。数据先在内存改同时记录redo log后续哪怕宕机也能通过redo log恢复。I (隔离性)靠锁 MVCC保证并发时不互相干扰。C (一致性)是业务结果的最终目的由应用程序的逻辑、以及数据库的 A、I、D 特性共同来保障。 演示脏读、不可重复读、幻读究竟长啥样-- 查看当前隔离级别SELECTtransaction_isolation;-- 设置隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;-- 场景脏读 (发生在 读未提交 RU 级别)-- 事务 A:BEGIN;UPDATEaccountSETbalancebalance-100WHEREid1;-- 扣钱但还没提交-- 事务 B:SELECTbalanceFROMaccountWHEREid1;-- 查到了扣完的钱。如果A马上回滚B查到的就是脏数据-- 场景不可重复读 (发生在 读已提交 RC 级别)-- 事务 A:BEGIN;SELECTbalanceFROMaccountWHEREid1;-- 第一次读1000-- 事务 B:BEGIN;UPDATEaccountSETbalance500WHEREid1;COMMIT;-- 别人改了并提交-- 事务 A:SELECTbalanceFROMaccountWHEREid1;-- 第二次读500(同一个事务内越读越少见鬼了)-- 场景幻读 (发生在 可重复读 RR 级别)-- 事务 A:BEGIN;SELECTCOUNT(*)FROMuserWHEREage20;-- 查出 5 个人-- 事务 B:BEGIN;INSERTINTOuser(age)VALUES(25);COMMIT;-- 别人悄悄塞了一个人进去-- 事务 A:UPDATEuserSETstatus1WHEREage20;-- 发现竟然更新了 6 条数据 (见鬼了多出来个幻象)2. MVCC (多版本并发控制) 底层原理[!IMPORTANT]原理解释如果单纯用锁来保证隔离性读也加锁写也加锁并发效率就太低了。MVCC 的核心目的就是**“读写不冲突”。当别人在写数据时我不去阻塞他而是去读这条数据的历史版本快照**。版本链如何形成InnoDB 每行数据都有两个隐藏列trx_id最后修改它的事务ID和roll_pointer指向上一个版本。每次修改都会在undo log里生成一条包含旧值的记录roll_pointer就像一条锁链把连串的修改连接成了“版本链”。Read View (读视图)当事务执行SELECT时会拍一张“快照”记录当前有哪些事务还没提交比如m_ids [10, 15]。拿着读取到的行的trx_id去对比如果这个trx_id是未来产生的或者在活跃列表[10, 15]里说明还没提交那就不能看顺着roll_pointer找上一个版本直到找到一个已经提交的、自己应该看到的版本为止。RC 与 RR 的区别重点RC (读已提交)每次SELECT都重新拍快照。如果事务 10 刚刚提交新的快照里就不包含 10 了所以能读到事务 10 修改的数据出现不可重复读。RR (可重复读)只在事务第一次SELECT时拍一次快照以后一直用这个老快照。所以不管别人怎么修改、怎么提交我看到的永远是最初的数据解决不可重复读。四、 锁机制 (Lock)1. InnoDB 的行锁原理解析Record Lock (记录锁)锁某一条具体的记录。Gap Lock (间隙锁)锁住两条记录之间的空隙不锁记录本身。原理解释RR 级别下为了防止“幻读”。如果不锁间隙别人INSERT一条并在我之前提交我同样条件的查询就会多出一条记录幻读。锁住间隙就没人能插进来了。Next-Key Lock (临键锁) Record Lock Gap Lock。锁住记录本身及其左边的间隙左开右闭区间。InnoDB 的普通查询默认不加锁走MVCC但是在执行UPDATE / DELETE / SELECT ... FOR UPDATE时默认就是加临键锁。 演示如何手动加锁BEGIN;-- 加共享锁 (S锁读锁)我读的时候别人也能读(加S锁)但别人不能改(加X锁)SELECT*FROMuserWHEREid1LOCKINSHAREMODE;-- 加排他锁 (X锁写锁)我在看/改的时候别人连加S锁都不行属于唯我独尊SELECT*FROMuserWHEREid1FORUPDATE;UPDATEuserSETage20WHEREid1;-- UPDATE 语句隐式自动加排他锁(X锁)COMMIT;2. 表锁降级陷阱及其原理原理解释InnoDB 的行锁其实锁的是索引而不是真正的数据行当你执行UPDATE user SET age 30 WHERE name Tom时如果name没有索引MySQL 无法在索引树上精准定位这行只能迫不得已扫描整张表全表扫描这个过程中会把扫描过的所有行全都锁上效果等同于大瘫痪的表锁。3. 间隙锁防幻读 与 表锁降级大坑 演示Next-Key Lock 锁间隙防幻读-- 表里只有 id 1, 5, 10-- 事务 A 在 RR 隔离级别下BEGIN;SELECT*FROMuserWHEREid1ANDid10FORUPDATE;-- 此时不仅仅 id5 这行被锁住了。-- (1, 5) 和 (5, 10) 这两个间隙也被锁死了-- 事务 BINSERTINTOuser(id)VALUES(3);-- ⛔ 被阻塞插入不进去从而防止了事务 A 发生幻读 演示灾难级的面试真题 —— 行锁变表锁-- ⚠️ 假设 name 字段没有建索引BEGIN;UPDATEuserSETage30WHEREname张三;-- 灾难发生因为 name 没索引MySQL 无法定位具体哪行只能在主键树上全表扫描。-- 扫描的过程中会把所有途径的记录和间隙全锁上-- 导致其他任何事务连 UPDATE 李四、甚至 INSERT 王五 都会被阻塞挂起等同于表锁五、 MySQL 三大日志系统深度融合日志谁写的记录内容 (原理解释)核心作用特性redo logInnoDB物理日志在第 xx 个数据页偏移量 yy 的位置把值改成了张三。它记录的是底层的“手术动作”。崩溃恢复 (Crash-safe)。有了它修改内存就算没刷盘宕机了重启照样能靠重做恢复。空间固定循环写(旧日志刷盘后会被覆盖)。采用WAL 先写日志后写磁盘技术提升IO。undo logInnoDB逻辑日志你执行INSERT它记DELETE你执行UPDATE a2它记UPDATE a1。1.事务回滚(保证原子性)2. 形成MVCC的历史版本链。事务产生修改时写入随版本链回收。binlogServer层逻辑日志执行了UPDATE ... WHERE id 1这样的SQL。1.主从同步(从库解析SQL照做)2.数据恢复(重放整个建库历史)不限空间追加写(不覆盖旧日志)。核心面试大题为什么需要两阶段提交 (2PC)原理解释因为这是两个互相独立的日志redo属于引擎层面binlog属于Server层面。如果事务提交时先写完 A还没写 B 就宕机了必然导致不一致如果先写 redo log 且提交了再写 binlog 失败主库有这条数据但 binlog 没这条记录从库同步不到主备不一致。如果先写 binlog redo log 还没写就宕机了主库丢了数据但从库存有这条数据主备不一致。2PC 流程执行器把数据改完写入内存。引擎写redo log并标记为prepare状态。服务器写binlog。引擎把刚才那个redo log的状态改成commit状态。崩溃恢复逻辑验证如果发现 redo log 只是prepare但是 binlog 已经写完整了说明数据已经“公告”出去了MySQL 恢复时会继续把事务提交保证一致如果 binlog 没写完说明没人知道则彻底回滚。六、 SQL 优化与 EXPLAIN 分析1. EXPLAIN 深层理解type全表扫描 (ALL)的原理是从头到尾读完所有的聚簇索引叶子节点。索引范围扫描 (range)的原理是定位到树上的两个边界端点由于叶子节点有链表只需顺着链表拿数据底层IO大减。Using filesort说明 MySQL 无法利用索引自带的有序性只能把数据捞到内存的 Sort Buffer 里用快排或归并亲自排序极度消耗 CPU。 演示查看执行计划EXPLAINSELECTu.name,o.order_noFROMuseruJOINorders oONu.ido.user_idWHEREu.age20;重点盯防type最好是ref或range严禁ALL全表扫描。Extra看到Using temporary(用到临时表) 或者Using filesort(说明排序没走索引在内存里生排) 必须想办法加索引。2. 索引失效背后的原理解析对索引列套函数如WHERE YEAR(date) 2024B 树是以date的原始值进行排序的。你套了函数之后原始值全变了格式破坏了原本 B 树的有序性引擎只能蒙着头全表扫描。隐式类型转换字符串列传入数字138xxxMySQL 等价于偷偷在你的列上套了个CAST(phone AS signed int)同上函数破坏了索引树结构宣告失效。LIKE 左模糊%三B 树的比较原则是从第一个字符开始比较类似查字典。你上来第一个字就不确定%字典无从查起失效。如果是右模糊张%先锁定所有“张”开头的后面再判断可以走索引。 演示明明建了索引为什么不用-- 假设我们在 create_time, phone, name 上都建了单列索引-- ❌ 对列用函数B 树原本按日期字符串排的被 YEAR 一搞全乱了SELECT*FROMtableWHEREYEAR(create_time)2024;-- ✅ 正确写法SELECT*FROMtableWHEREcreate_time2024-01-01ANDcreate_time2025-01-01;-- ❌ 隐式类型转换phone 是 VARCHAR 字符串类型你传了数字MySQL 底层偷偷给你套了个 CAST 函数索引直接失效SELECT*FROMtableWHEREphone13800138000;-- ✅ 正确写法加上单引号SELECT*FROMtableWHEREphone13800138000;-- ❌ LIKE 左模糊第一字符都不确定字典没法查SELECT*FROMtableWHEREnameLIKE%三;-- ✅ 右模糊可以走索引先锁定一部分开头的人再去慢慢查SELECT*FROMtableWHEREnameLIKE张%;3. 高级 SQL 优化原理解析深分页问题 (LIMIT 1000000, 10)原理MySQL 的 LIMIT 是先查出来 1,000,010 条记录如果需要回表那就是 100万次回表然后把前 100万条全扔掉只留最后 10 条。回表代价极大。优化思路 (延迟关联)先写一个非常纯粹的子查询SELECT id FROM table LIMIT 1000000, 10这个查询只取 ID可以完美触发覆盖索引不用回表拿到这 10 个 ID 后再去拿完整的大表去 INNER JOIN这样仅产生了 10 次准确的回表查询速度提升千倍。JOIN 的小表驱动大表原理 (Index Nested-Loop Join)A JOIN B。MySQL 是以表 A 的每一行去通过网络/磁盘找表 B。A 是 100 行B 是 10万行。如果是 A 驱动 B就是循环 100 次每次在 B 树里精准二分查找如果是 B 驱动 A要循环 10万次这正是为什么被驱动表的关联字段绝对必须加索引的原因否则嵌套循环就是 100次 * 10万次全表扫描数据库直接宕机。
