MySQL面试核心知识点总结与原理详细解析

一、 mysql 架构与基础

1. 核心架构层级

• 连接层：负责接收客户端连接、授权认证。
• 服务层：涵盖 mysql 的大多数核心服务功能（如 sql 解析、分析、优化、缓存以及所有的内置函数），跨存储引擎的功能都在这里实现（如触发器、视图）。
• 存储引擎层：负责数据的存储和提取。采用插件式架构，支持 innodb、myisam 等。

2. innodb vs myisam

💻 演示：指定存储引擎

-- 创建一张使用 innodb 的表（mysql 5.5 后默认）
create table user_innodb (
    id int primary key auto_increment,
    name varchar(50)
) engine=innodb default charset=utf8mb4;

-- 创建一张使用 myisam 的表
create table user_myisam (
    id int primary key auto_increment,
    name varchar(50)
) engine=myisam default charset=utf8mb4;

二、 索引 (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 字段加一个二级索引
create index idx_name on user(name);

-- ❌ 发生回表：
-- 引擎先在 idx_name 树上找到 '张三' 对应的主键 id=1。
-- 但是你要查 age，idx_name 树上没有 age，只好拿 id=1 回到主键树（聚簇索引）再查一次。
select id, name, age from user where name = '张三';

-- ✅ 覆盖索引 (extra: using index)：
-- 你要查的 id 和 name，在 idx_name 这棵树的叶子节点上都已经有了！
-- 直接返回数据，不需要再去主键树查，性能极高。
select id, name from user where name = '张三';

-- 💡 进阶：如何让第一条 sql 也变成覆盖索引？建立联合索引！
create index idx_name_age on user(name, age);
-- 此时再去查 name 和 age，就全是覆盖索引了。

4. 最左前缀原则

• 原理解释：联合索引 (a, b, c) 的 b+ 树是先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。
• 如果你跳过 a 直接查 b，在树里 b 完全是无序的（比如 a=1时b=2，a=2时b=1），就只能全表扫描。因此必须从最左边连续匹配，遇到范围查询（>, <, like）会使得后面的字段无法利用索引的有序性。

💻 演示：最左前缀原则的生效与失效

-- 创建联合索引 (a, b, c)
create index idx_a_b_c on test_table(a, b, c);

-- ✅ 完美走索引：从左到右连续匹配
select * from test_table where a = 1 and b = 2 and c = 3;
select * from test_table where a = 1 and b = 2;
select * from test_table where a = 1;

-- ❌ 完全不走索引：跳过了最左边的 a
select * from test_table where b = 2 and c = 3;

-- ⚠️ 部分走索引：中间断开或者遇到范围查询
select * from test_table where a = 1 and c = 3;       -- a 走索引，c 断层不走索引
select * from test_table where a = 1 and b > 10 and c = 3; -- a 走索引，b 走索引(找范围)，c 不走索引(范围之后失效)

三、 事务与 mvcc

1. 事务 acid 特性及实现原理

• a (原子性)：靠 undo log。执行前先记录旧数据，出错就顺着 undo log 把数据改回去。
• d (持久性)：靠 redo log。数据先在内存改，同时记录 redo log，后续哪怕宕机，也能通过 redo log 恢复。
• i (隔离性)：靠 锁 + mvcc 保证并发时不互相干扰。
• c (一致性)：是业务结果的最终目的，由应用程序的逻辑、以及数据库的 a、i、d 特性共同来保障。

💻 演示：脏读、不可重复读、幻读究竟长啥样？

-- 查看当前隔离级别
select @@transaction_isolation;
-- 设置隔离级别
set session transaction isolation level read committed;

-- 👻 场景：脏读 (发生在 读未提交 ru 级别)
-- 事务 a:
begin;
update account set balance = balance - 100 where id = 1; -- 扣钱，但还没提交！
-- 事务 b:
select balance from account where id = 1; -- 查到了扣完的钱。如果a马上回滚，b查到的就是脏数据！

-- 👻 场景：不可重复读 (发生在 读已提交 rc 级别)
-- 事务 a: 
begin;
select balance from account where id = 1; -- 第一次读：1000
-- 事务 b:
begin; update account set balance = 500 where id = 1; commit; -- 别人改了并提交
-- 事务 a:
select balance from account where id = 1; -- 第二次读：500！(同一个事务内，越读越少，见鬼了)

-- 👻 场景：幻读 (发生在 可重复读 rr 级别)
-- 事务 a:
begin;
select count(*) from user where age > 20; -- 查出 5 个人
-- 事务 b:
begin; insert into user(age) values (25); commit; -- 别人悄悄塞了一个人进去
-- 事务 a:
update user set status = 1 where age > 20; -- ！！发现竟然更新了 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 * from user where id = 1 lock in share mode;

-- 加排他锁 (x锁，写锁)：我在看/改的时候，别人连加s锁都不行，属于唯我独尊
select * from user where id = 1 for update;

update user set age = 20 where id = 1; -- 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 * from user where id > 1 and id < 10 for update;
-- 此时不仅仅 id=5 这行被锁住了。
-- (1, 5) 和 (5, 10) 这两个间隙也被锁死了！

-- 事务 b：
insert into user (id) values (3); -- ⛔ 被阻塞！插入不进去，从而防止了事务 a 发生幻读！

💻 演示：灾难级的面试真题 —— 行锁变表锁！

-- ⚠️ 假设 name 字段没有建索引！
begin;
update user set age = 30 where name = '张三'; 
-- 灾难发生：因为 name 没索引，mysql 无法定位具体哪行，只能在主键树上全表扫描。
-- 扫描的过程中，会把所有途径的记录和间隙全锁上！
-- 导致其他任何事务连 update 李四、甚至 insert 王五 都会被阻塞挂起！等同于表锁！

五、 mysql 三大日志系统深度融合

核心面试大题：为什么需要两阶段提交 (2pc)？

• 原理解释：因为这是两个互相独立的日志（redo属于引擎层面，binlog属于server层面）。如果事务提交时，先写完 a，还没写 b 就宕机了，必然导致不一致：
  • 如果先写 redo log 且提交了，再写 binlog 失败：主库有这条数据，但 binlog 没这条记录，从库同步不到，主备不一致。
  • 如果先写 binlog ，redo log 还没写就宕机了：主库丢了数据，但从库存有这条数据，主备不一致。
• 2pc 流程：
  1. 执行器把数据改完，写入内存。
  2. 引擎写 redo log，并标记为 prepare 状态。
  3. 服务器写 binlog。
  4. 引擎把刚才那个 redo log 的状态改成 commit 状态。
• 崩溃恢复逻辑验证：如果发现 redo log 只是 prepare，但是 binlog 已经写完整了，说明数据已经“公告”出去了，mysql 恢复时会继续把事务提交（保证一致）；如果 binlog 没写完，说明没人知道，则彻底回滚。

六、 sql 优化与 explain 分析

1. explain 深层理解

• type：全表扫描 (all) 的原理是从头到尾读完所有的聚簇索引叶子节点。索引范围扫描 (range) 的原理是定位到树上的两个边界端点，由于叶子节点有链表，只需顺着链表拿数据（底层io大减）。
• using filesort：说明 mysql 无法利用索引自带的有序性，只能把数据捞到内存的 sort buffer 里，用快排或归并亲自排序，极度消耗 cpu。

💻 演示：查看执行计划

explain select u.name, o.order_no 
from user u 
join orders o on u.id = o.user_id 
where u.age = 20;

重点盯防：

• type：最好是 ref 或 range，严禁 all（全表扫描）。
• extra：看到 using temporary (用到临时表) 或者 using filesort (说明排序没走索引，在内存里生排) 必须想办法加索引。

2. 索引失效背后的原理解析

• 对索引列套函数（如 where year(date) = 2024）：b+ 树是以 date 的原始值进行排序的。你套了函数之后，原始值全变了格式，破坏了原本 b+ 树的有序性，引擎只能蒙着头全表扫描。
• 隐式类型转换（字符串列传入数字 138xxx）：mysql 等价于偷偷在你的列上套了个 cast(phone as signed int)，同上，函数破坏了索引树结构，宣告失效。
• like 左模糊 %三：b+ 树的比较原则是从第一个字符开始比较（类似查字典）。你上来第一个字就不确定（%），字典无从查起，失效。如果是右模糊 张%，先锁定所有“张”开头的，后面再判断，可以走索引。

💻 演示：明明建了索引，为什么不用？

-- 假设我们在 create_time, phone, name 上都建了单列索引

-- ❌ 对列用函数：b+ 树原本按日期字符串排的，被 year 一搞全乱了
select * from table where year(create_time) = 2024; 
-- ✅ 正确写法：
select * from table where create_time >= '2024-01-01' and create_time < '2025-01-01';

-- ❌ 隐式类型转换：phone 是 varchar 字符串类型，你传了数字！mysql 底层偷偷给你套了个 cast 函数，索引直接失效
select * from table where phone = 13800138000;
-- ✅ 正确写法：加上单引号
select * from table where phone = '13800138000';

-- ❌ like 左模糊：第一字符都不确定，字典没法查
select * from table where name like '%三';
-- ✅ 右模糊可以走索引：先锁定一部分开头的人再去慢慢查
select * from table where name like '张%';

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面试核心知识点总结与原理详细解析的文章就介绍到这了,更多相关mysql面试核心知识点内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！