MySQL架构原理九魂环17问，面试官，你敢接招吗？
- 内容介绍
- 文章标签
- 相关推荐
前言：这该死的面试，到底谁在折磨谁？
归根结底。 说实话，最近这技术圈是真的卷，卷到让人怀疑人生。昨天有个粉丝私信我， 说去面试大厂，后来啊被面试官按在地上摩擦，问了一堆MySQL架构原理的问题，出来的时候腿都是软的。我就想问问，现在的面试官都是魔鬼吗？非要问什么“九魂环17问”，你是要当唐三啊？不过话说回来MySQL这东西，平时用着挺爽，一到面试就抓瞎，这确实是个大问题。今天咱们就来好好掰扯掰扯这个MySQL架构原理， 不管你是小白还是老鸟，这篇文章都值得你收藏起来没事拿出来看看，毕竟技多不压身嘛，对吧？
观感极佳。 咱们今天不整那些虚头巴脑的理论，直接上干货，虽然可能有点乱，但绝对都是真材实料。就像那个让人敬佩的白发程序员——MySQL/MariaDB之父Monty，人家在阿里交流会上也没说那么多废话啊。咱们也要务实搞技术嘛，实用至上。
一、MySQL架构原理：这到底是个什么玩意儿？
先说说你得知道MySQL不是一块铁板，它是有层次的。就像咱们公司组织架构一样，有老板，有经理，有干活的。MySQL主要分为两层，一个是服务层，一个是存储引擎层。别小看这个分层，这可是理解一切的基础，说白了...。
躺平... 当你的SQL语句像一条条指令发过来的时候，先说说得经过服务层。服务层里有啥？有SQL接口，也就是连接器，这玩意儿负责跟你客户端打交道，用的是TCP协议，建立的是长连接。默认8小时不说话就断开，挺绝情的吧？而且服务端默认最大连接数才151个，虽然能改到10万，但你真敢改吗？反正我是不敢。
连接器还要验证你的密码，不对就滚蛋。对了以前还有个查询缓存，MySQL 5.7默认关了8.0直接删了。为啥？主要原因是这玩意儿鸡肋啊， 这也行？ 稍微改点表数据缓存就失效了还不如用Redis来得实在。所以别再问我缓存的事了过时的东西就让它过去吧。
然后SQL就到了解析器。这哥们儿负责词法分析、语法分析，看看你关键字写错没，表名对不对。要是你连select都能拼错，那解析器绝对会给你一个大大的报错，让你怀疑人生。解析完了生成语法树，这树长得怎么样，决定了你SQL能不能跑，他破防了。。
接下来就是优化器了这可是个聪明人。它看着你的SQL，心里盘算着：“这哥们儿想查数据，我有好几种路可以走，走哪条最快呢？”它会选择一个最优的施行计划。比如你写了`where age = 20 and name = 'zhangsan'`， 但是你的索引是`name, age`，优化器会帮你调整顺序，让你能走索引。是不是很贴心？当然如果你写了`1=1`这种废话，它也会直接给你删掉，别想骗它，就这样吧...。
呵... 再说说才是存储引擎干活的时候。InnoDB、MyISAM这些家伙都在这一层。它们负责把数据从磁盘捞出来或者塞进去。这里面的门道可就多了咱们后面细说。
1.1 那些年我们用过的存储引擎
说到存储引擎， 大家最熟悉的肯定是InnoDB了毕竟现在是默认的。但是MySQL这家族大着呢， 盘它。 还有MyISAM、Memory、Archive等等。咱们来个表格对比一下免得大家晕头转向。
| 存储引擎 | 特点 | 适用场景 | 事务支持 | 锁粒度 |
|---|---|---|---|---|
| InnoDB | 行锁、 事务、MVCC、外键 | 高并发、事务核心业务 | 支持 | 行级锁 |
| MyISAM | 表锁、读取速度快、压缩率高 | 只读业务、全文检索 | 不支持 | 表级锁 |
| Memory | 数据存内存、速度快、宕机丢失 | 临时表、缓存数据 | 不支持 | 表级锁 |
| Archive | 只支持插入和查询、高压缩比 | 日志归档、历史数据存储 | 不支持 | 行级锁 |
这事儿我可太有发言权了。 看明白了吗？InnoDB是现在的当红炸子鸡，啥都好，就是占地方。MyISAM是老古董，读起来快，但是写起来就惨了主要原因是它是表锁，一个人写，全表都得等着。Memory引擎就是个暴发户，快是快，但是一断电啥都没了适合做临时中转。Archive引擎是个守财奴，特别省空间，适合存那些不动的老数据。
二、 Buffer Pool与日志：MySQL的“内功心法”
咱们都知道，磁盘IO慢，内存快。MySQL为了解决这个问题，搞了个Buffer Pool。这玩意儿就是InnoDB的一块内存区域，默认大小128MB。你查数据、改数据，其实都是先在这个内存里折腾，我是深有体会。。
整起来。 这里有个细节，MySQL加载数据不是一条条加的，是一页页加的。一页多大？16KB！为啥是16KB？主要原因是操作系统一页是4KB，16KB是4KB的整数倍，这样IO效率高。这就像你去超市买东西，不买一瓶水，而是买一箱，虽然一次拿得重，但是少跑几趟，划算。
那问题来了内存里的数据改了什么时候写到磁盘去？这就是个大学问了。如果你每次改完都立马写磁盘，那随机读写能把磁盘磨冒烟了。所以MySQL用了WAL技术，先写日志，再改内存，再说说慢慢刷盘，一言难尽。。
这时候就要提到日志三宝了：redoLog、undoLog、binLog，离了大谱。。
redoLog是InnoDB特有的，它记录的是“数据被改成了什么样”。它是循环写的，空间固定满了就覆盖。这玩意儿是为了保证持久性，万一机器挂了重启后照着redoLog重做一遍，数据就回来了。
undoLog也是InnoDB的，它记录的是“数据修改前的样子”。这玩意儿是为了回滚用的，事务失败了或者你要撤销操作，就靠它了。MVCC也是靠它来实现的可视化读取。
binLog是服务层的，逻辑日志。它记录的是SQL语句的原始逻辑。这玩意儿主要是用来做主从复制的，从库拿着binLog就能重放一遍主库的操作，原来如此。。
2.1 脏数据什么时候刷盘？这是个玄学
Buffer Pool里被改了还没写回磁盘的数据，叫脏页。啥时候刷盘？这得看心情，哦不看参数。
有个参数叫`innodb_max_dirty_pages_pct`，默认75%。意思是脏页占了Buffer Pool的75%了就得开始刷了不然内存不够用了。还有个`innodb_flush_log_at_trx_commit`参数， 这玩意儿特别重要，面试必问！
- 设为0：事务提交时不写磁盘，只留在内存里。MySQL挂了这一秒的事务就丢了。最快，最不平安。
- 设为1：事务提交时必须把redoLog写到磁盘。最平安，但是性能损耗大。
- 设为2：事务提交时写到操作系统的缓存，不直接落盘。只要操作系统不挂，MySQL挂了也没事。折中方案。
你要是问我怎么选？我一般选1，毕竟数据无价，服务器可以换，数据丢了饭碗就丢了。当然如果你是在做那种统计类的， 完善一下。 丢几条数据没所谓的，那你可以选0或者2，追求极致性能。
三、索引：B+树的魅力
说到索引，那真是让人又爱又恨。爱它是主要原因是查得快，恨它是主要原因是它占地方还影响写入速度。 你我共勉。 MySQL最常用的索引结构就是B+树。为啥是B+树？不是二叉树，不是B树，也不是哈希？
咱们先看看二叉树。如果你按顺序插入1、 2、3、4、5，二叉树就退化成链表了树高5层， 境界没到。 查个数据得读5次磁盘，这谁受得了？
开倒车。 平衡二叉树虽然解决了退化问题，但是每个节点只能存两个子节点，树还是太高了。几千万数据下来树高得吓死人。
他破防了。 B树呢，是多叉树，节点里能存很多数据和指针。但是B树的非叶子节点也存数据，这就导致一页存不了多少索引，树还是降不下来。
再说说是B+树，这可是集大成者。非叶子节点只存索引，不存数据，这样一页16KB能存成千上万个索引。而且叶子节点存了所有数据，并且用双向链表连起来了。这就意味着，树高只有3到4层，查几千万数据也就3、4次磁盘IO，简直爽翻天。而且范围查询特别快，顺着链表一扫就完事了，一针见血。。
3.1 聚集索引 vs 非聚集索引
躺平。 InnoDB的索引分两种：聚集索引和非聚集索引。
聚集索引就是主键索引。它的叶子节点存的是整行数据。一张表只能有一个聚集索引。如果你没设主键， MySQL会帮你找个唯一的非空字段； 踩雷了。 如果还没有，它就自己偷偷搞个隐藏字段叫row_id。反正必须得有一个。
非聚集索引的叶子节点存的是主键的值。这意味着啥？意味着你用辅助索引查数据，查到主键后还得去聚集索引里再查一遍，这叫“回表”。多一次操作，当然慢一点。
所以 咱们建索引的时候，尽量用主键查，或者搞个“覆盖索引”， 划水。 就是你要查的字段都在索引里不用回表，那效率就杠杠的。
3.2 索引失效的坑，你踩过几个？
有时候明明建了索引，MySQL就是不用，气人不？这通常是你写法有问题。
最常见的就是最左前缀原则。你建了个联合索引`name, age, city`， 你查的时候直接`where city = '北京'`，那肯定不走索引啊，索引树是按name排的，你直接跳到city，MySQL怎么找？
我们都... 还有那个`like`查询。`like '张%'`可以走索引， 但是`like '%张'`或者`like '%张%'`就不行了主要原因是不知道前面是啥，没法二分查找。
再说一个，在索引列上做运算、使用函数、或者类型转换， 说真的... 都会导致索引失效。这些坑，踩一次就记住了疼啊。
四、事务：ACID的坚守
MySQL的事务，那可是数据库的基石。ACID四个字母，面试官能跟你聊半天，佛系。。
A原子性：要么全做，要么全不做。这靠的是undoLog。 实锤。 失败了？没事，照着undoLog滚回去。
害... C一致性：前后数据得守恒，钱不能多也不能少。这个是数据库应用层面的目标，靠其他三个特性来保证。
I隔离性：你干你的，我干我的，互不干扰。这靠的是锁和MVCC。MySQL有四个隔离级别：读未提交、 尊嘟假嘟？ 读已提交、可重复读、串行化。级别越高，越平安，但是越慢。
D持久性：一旦提交，就永久保存。这靠的是redoLog。commit成功了就算服务器下一秒爆炸，数据也在，何苦呢？。
五、 那些乱七八糟的数据库产品
说了这么多MySQL，其实市面上数据库产品多如牛毛。咱们做技术的，眼光不能太窄，虽然咱们今天主聊MySQL，但也得知道别人在干啥。下面这个表格列了一些常见的数据库，没事看看，吹牛也能用得上，也是醉了...。
| 数据库名称 | 类型 | 核心特点 | 典型应用场景 |
|---|---|---|---|
| MySQL | 关系型 | 开源、 成本低、社区活跃 | Web业务、电商、博客 |
| Oracle | 关系型 | 强大、稳定、贵、功能全 | 银行、电信、大型国企核心系统 |
| PostgreSQL | 关系型 | 高级特性多、开源、标准支持好 | 地理信息系统、复杂查询场景 |
| MongoDB | 文档型 | Schema自由、高性能、水平 | 游戏、日志、社交数据存储 |
| Redis | 键值型 | 极快、内存存储、支持丰富数据结构 | 缓存、排行榜、消息队列 |
| ClickHouse | 列式存储 | 分析型数据库、查询极快 | 数据分析、报表、用户行为分析 |
六、别被面试官吓倒
我傻了。 说了这么多，其实MySQL架构原理这东西，看着吓人，其实也就那么回事。什么九魂环、十七问，只要你把基础打牢了什么问题都能接招。记住面试是双向选择，他问你问题，也是在考察他的问题水平，别太卑微。
再说说送大家一句话：此心光明，亦复何言？知行合一，以行践言，坚持做难而正确的事。今年生了一场病，目前康复中，这次伤病后最大体悟就是学会了自身内省，致良知。技术这条路，没有捷径，只有不断的学习和积累。2024好事接龙，祝愿所有有缘刷到的同学，好事发生，喜事连连。
对了 如果你觉得这篇文章对你有帮助，别光收藏啊，点个赞，转发一下让我也有动力继续写下去。毕竟写这玩意儿掉头发啊！下次咱们聊聊MySQL的锁机制，或者聊聊JVM调优，看你们想听啥。评论区告诉我！
前言：这该死的面试，到底谁在折磨谁？
归根结底。 说实话，最近这技术圈是真的卷，卷到让人怀疑人生。昨天有个粉丝私信我， 说去面试大厂，后来啊被面试官按在地上摩擦，问了一堆MySQL架构原理的问题，出来的时候腿都是软的。我就想问问，现在的面试官都是魔鬼吗？非要问什么“九魂环17问”，你是要当唐三啊？不过话说回来MySQL这东西，平时用着挺爽，一到面试就抓瞎，这确实是个大问题。今天咱们就来好好掰扯掰扯这个MySQL架构原理， 不管你是小白还是老鸟，这篇文章都值得你收藏起来没事拿出来看看，毕竟技多不压身嘛，对吧？
观感极佳。 咱们今天不整那些虚头巴脑的理论，直接上干货，虽然可能有点乱，但绝对都是真材实料。就像那个让人敬佩的白发程序员——MySQL/MariaDB之父Monty，人家在阿里交流会上也没说那么多废话啊。咱们也要务实搞技术嘛，实用至上。
一、MySQL架构原理：这到底是个什么玩意儿？
先说说你得知道MySQL不是一块铁板，它是有层次的。就像咱们公司组织架构一样，有老板，有经理，有干活的。MySQL主要分为两层，一个是服务层，一个是存储引擎层。别小看这个分层，这可是理解一切的基础，说白了...。
躺平... 当你的SQL语句像一条条指令发过来的时候，先说说得经过服务层。服务层里有啥？有SQL接口，也就是连接器，这玩意儿负责跟你客户端打交道，用的是TCP协议，建立的是长连接。默认8小时不说话就断开，挺绝情的吧？而且服务端默认最大连接数才151个，虽然能改到10万，但你真敢改吗？反正我是不敢。
连接器还要验证你的密码，不对就滚蛋。对了以前还有个查询缓存，MySQL 5.7默认关了8.0直接删了。为啥？主要原因是这玩意儿鸡肋啊， 这也行？ 稍微改点表数据缓存就失效了还不如用Redis来得实在。所以别再问我缓存的事了过时的东西就让它过去吧。
然后SQL就到了解析器。这哥们儿负责词法分析、语法分析，看看你关键字写错没，表名对不对。要是你连select都能拼错，那解析器绝对会给你一个大大的报错，让你怀疑人生。解析完了生成语法树，这树长得怎么样，决定了你SQL能不能跑，他破防了。。
接下来就是优化器了这可是个聪明人。它看着你的SQL，心里盘算着：“这哥们儿想查数据，我有好几种路可以走，走哪条最快呢？”它会选择一个最优的施行计划。比如你写了`where age = 20 and name = 'zhangsan'`， 但是你的索引是`name, age`，优化器会帮你调整顺序，让你能走索引。是不是很贴心？当然如果你写了`1=1`这种废话，它也会直接给你删掉，别想骗它，就这样吧...。
呵... 再说说才是存储引擎干活的时候。InnoDB、MyISAM这些家伙都在这一层。它们负责把数据从磁盘捞出来或者塞进去。这里面的门道可就多了咱们后面细说。
1.1 那些年我们用过的存储引擎
说到存储引擎， 大家最熟悉的肯定是InnoDB了毕竟现在是默认的。但是MySQL这家族大着呢， 盘它。 还有MyISAM、Memory、Archive等等。咱们来个表格对比一下免得大家晕头转向。
| 存储引擎 | 特点 | 适用场景 | 事务支持 | 锁粒度 |
|---|---|---|---|---|
| InnoDB | 行锁、 事务、MVCC、外键 | 高并发、事务核心业务 | 支持 | 行级锁 |
| MyISAM | 表锁、读取速度快、压缩率高 | 只读业务、全文检索 | 不支持 | 表级锁 |
| Memory | 数据存内存、速度快、宕机丢失 | 临时表、缓存数据 | 不支持 | 表级锁 |
| Archive | 只支持插入和查询、高压缩比 | 日志归档、历史数据存储 | 不支持 | 行级锁 |
这事儿我可太有发言权了。 看明白了吗？InnoDB是现在的当红炸子鸡，啥都好，就是占地方。MyISAM是老古董，读起来快，但是写起来就惨了主要原因是它是表锁，一个人写，全表都得等着。Memory引擎就是个暴发户，快是快，但是一断电啥都没了适合做临时中转。Archive引擎是个守财奴，特别省空间，适合存那些不动的老数据。
二、 Buffer Pool与日志：MySQL的“内功心法”
咱们都知道，磁盘IO慢，内存快。MySQL为了解决这个问题，搞了个Buffer Pool。这玩意儿就是InnoDB的一块内存区域，默认大小128MB。你查数据、改数据，其实都是先在这个内存里折腾，我是深有体会。。
整起来。 这里有个细节，MySQL加载数据不是一条条加的，是一页页加的。一页多大？16KB！为啥是16KB？主要原因是操作系统一页是4KB，16KB是4KB的整数倍，这样IO效率高。这就像你去超市买东西，不买一瓶水，而是买一箱，虽然一次拿得重，但是少跑几趟，划算。
那问题来了内存里的数据改了什么时候写到磁盘去？这就是个大学问了。如果你每次改完都立马写磁盘，那随机读写能把磁盘磨冒烟了。所以MySQL用了WAL技术，先写日志，再改内存，再说说慢慢刷盘，一言难尽。。
这时候就要提到日志三宝了：redoLog、undoLog、binLog，离了大谱。。
redoLog是InnoDB特有的，它记录的是“数据被改成了什么样”。它是循环写的，空间固定满了就覆盖。这玩意儿是为了保证持久性，万一机器挂了重启后照着redoLog重做一遍，数据就回来了。
undoLog也是InnoDB的，它记录的是“数据修改前的样子”。这玩意儿是为了回滚用的，事务失败了或者你要撤销操作，就靠它了。MVCC也是靠它来实现的可视化读取。
binLog是服务层的，逻辑日志。它记录的是SQL语句的原始逻辑。这玩意儿主要是用来做主从复制的，从库拿着binLog就能重放一遍主库的操作，原来如此。。
2.1 脏数据什么时候刷盘？这是个玄学
Buffer Pool里被改了还没写回磁盘的数据，叫脏页。啥时候刷盘？这得看心情，哦不看参数。
有个参数叫`innodb_max_dirty_pages_pct`，默认75%。意思是脏页占了Buffer Pool的75%了就得开始刷了不然内存不够用了。还有个`innodb_flush_log_at_trx_commit`参数， 这玩意儿特别重要，面试必问！
- 设为0：事务提交时不写磁盘，只留在内存里。MySQL挂了这一秒的事务就丢了。最快，最不平安。
- 设为1：事务提交时必须把redoLog写到磁盘。最平安，但是性能损耗大。
- 设为2：事务提交时写到操作系统的缓存，不直接落盘。只要操作系统不挂，MySQL挂了也没事。折中方案。
你要是问我怎么选？我一般选1，毕竟数据无价，服务器可以换，数据丢了饭碗就丢了。当然如果你是在做那种统计类的， 完善一下。 丢几条数据没所谓的，那你可以选0或者2，追求极致性能。
三、索引：B+树的魅力
说到索引，那真是让人又爱又恨。爱它是主要原因是查得快，恨它是主要原因是它占地方还影响写入速度。 你我共勉。 MySQL最常用的索引结构就是B+树。为啥是B+树？不是二叉树，不是B树，也不是哈希？
咱们先看看二叉树。如果你按顺序插入1、 2、3、4、5，二叉树就退化成链表了树高5层， 境界没到。 查个数据得读5次磁盘，这谁受得了？
开倒车。 平衡二叉树虽然解决了退化问题，但是每个节点只能存两个子节点，树还是太高了。几千万数据下来树高得吓死人。
他破防了。 B树呢，是多叉树，节点里能存很多数据和指针。但是B树的非叶子节点也存数据，这就导致一页存不了多少索引，树还是降不下来。
再说说是B+树，这可是集大成者。非叶子节点只存索引，不存数据，这样一页16KB能存成千上万个索引。而且叶子节点存了所有数据，并且用双向链表连起来了。这就意味着，树高只有3到4层，查几千万数据也就3、4次磁盘IO，简直爽翻天。而且范围查询特别快，顺着链表一扫就完事了，一针见血。。
3.1 聚集索引 vs 非聚集索引
躺平。 InnoDB的索引分两种：聚集索引和非聚集索引。
聚集索引就是主键索引。它的叶子节点存的是整行数据。一张表只能有一个聚集索引。如果你没设主键， MySQL会帮你找个唯一的非空字段； 踩雷了。 如果还没有，它就自己偷偷搞个隐藏字段叫row_id。反正必须得有一个。
非聚集索引的叶子节点存的是主键的值。这意味着啥？意味着你用辅助索引查数据，查到主键后还得去聚集索引里再查一遍，这叫“回表”。多一次操作，当然慢一点。
所以 咱们建索引的时候，尽量用主键查，或者搞个“覆盖索引”， 划水。 就是你要查的字段都在索引里不用回表，那效率就杠杠的。
3.2 索引失效的坑，你踩过几个？
有时候明明建了索引，MySQL就是不用，气人不？这通常是你写法有问题。
最常见的就是最左前缀原则。你建了个联合索引`name, age, city`， 你查的时候直接`where city = '北京'`，那肯定不走索引啊，索引树是按name排的，你直接跳到city，MySQL怎么找？
我们都... 还有那个`like`查询。`like '张%'`可以走索引， 但是`like '%张'`或者`like '%张%'`就不行了主要原因是不知道前面是啥，没法二分查找。
再说一个，在索引列上做运算、使用函数、或者类型转换， 说真的... 都会导致索引失效。这些坑，踩一次就记住了疼啊。
四、事务：ACID的坚守
MySQL的事务，那可是数据库的基石。ACID四个字母，面试官能跟你聊半天，佛系。。
A原子性：要么全做，要么全不做。这靠的是undoLog。 实锤。 失败了？没事，照着undoLog滚回去。
害... C一致性：前后数据得守恒，钱不能多也不能少。这个是数据库应用层面的目标，靠其他三个特性来保证。
I隔离性：你干你的，我干我的，互不干扰。这靠的是锁和MVCC。MySQL有四个隔离级别：读未提交、 尊嘟假嘟？ 读已提交、可重复读、串行化。级别越高，越平安，但是越慢。
D持久性：一旦提交，就永久保存。这靠的是redoLog。commit成功了就算服务器下一秒爆炸，数据也在，何苦呢？。
五、 那些乱七八糟的数据库产品
说了这么多MySQL，其实市面上数据库产品多如牛毛。咱们做技术的，眼光不能太窄，虽然咱们今天主聊MySQL，但也得知道别人在干啥。下面这个表格列了一些常见的数据库，没事看看，吹牛也能用得上，也是醉了...。
| 数据库名称 | 类型 | 核心特点 | 典型应用场景 |
|---|---|---|---|
| MySQL | 关系型 | 开源、 成本低、社区活跃 | Web业务、电商、博客 |
| Oracle | 关系型 | 强大、稳定、贵、功能全 | 银行、电信、大型国企核心系统 |
| PostgreSQL | 关系型 | 高级特性多、开源、标准支持好 | 地理信息系统、复杂查询场景 |
| MongoDB | 文档型 | Schema自由、高性能、水平 | 游戏、日志、社交数据存储 |
| Redis | 键值型 | 极快、内存存储、支持丰富数据结构 | 缓存、排行榜、消息队列 |
| ClickHouse | 列式存储 | 分析型数据库、查询极快 | 数据分析、报表、用户行为分析 |
六、别被面试官吓倒
我傻了。 说了这么多，其实MySQL架构原理这东西，看着吓人，其实也就那么回事。什么九魂环、十七问，只要你把基础打牢了什么问题都能接招。记住面试是双向选择，他问你问题，也是在考察他的问题水平，别太卑微。
再说说送大家一句话：此心光明，亦复何言？知行合一，以行践言，坚持做难而正确的事。今年生了一场病，目前康复中，这次伤病后最大体悟就是学会了自身内省，致良知。技术这条路，没有捷径，只有不断的学习和积累。2024好事接龙，祝愿所有有缘刷到的同学，好事发生，喜事连连。
对了 如果你觉得这篇文章对你有帮助，别光收藏啊，点个赞，转发一下让我也有动力继续写下去。毕竟写这玩意儿掉头发啊！下次咱们聊聊MySQL的锁机制，或者聊聊JVM调优，看你们想听啥。评论区告诉我！