MySQL进阶突击系列(0) MySQL架构原理solo九魂环17连问

MySQL进阶突击系列(0) MySQL架构原理solo九魂环17连问

2024好事接龙，拉丁解牛祝愿所有有缘刷到的同学，好事发生，喜事连连。

开篇，先推荐一篇文章《浅入浅出 JVM 特性》，作者是【JavaSouth南哥】。

  这篇内容非常详细的介绍了JVM内存模型、垃圾回收器、垃圾回收算法，图文并茂，通俗易懂，特此推荐给大家。

-----------------------------正式开启我们正文----------------------

一、前言背景

二、MySQL架构原理相关核心点

2.1 简单说说一条查询SQL的执行过程

2.2 简单说说一条更新SQL的执行过程

2. MySQL更新数据为什么要先记录redoLog、undoLog、最后刷盘？直接刷盘不更快吗？

2.4 总结一下MySQL架构各个组件的作用

三、存储引擎相关知识要点

.1 MySQL的存储引擎用过几种？

.2 MySQL每个页数据大小是多少？

. bufferpool数据清理算法是什么？

.4 bufferpool内存脏数据什么时候会刷盘？

.5 事务的原子性主要与哪个日志相关？

.6 事务的一致性、持久性主要与哪个日志相关？

四、索引相关高频实用考察点

4.1 什么样的字段适合建索引？

4.2 索引字段数量越多越好吗？

4. 什么样的字段不宜建索引？

4.4 什么样的查询会导致索引失效？

4.5 聚集索引和联合索引的区别。

4.6 索引是如何存储数据的？

4.7 说说二叉树、平衡二叉树、B树的区别。

读书心得笔记：此心光明，亦复何言？知行合一，以行践言，坚持做难而正确的事。今年生了一场病，目前康复中，这次伤病后，最大体悟就是学会了自身内省，致良知。

一、前言背景

在技术面试过程中，有的面试官喜欢先聊整体架构，再聊细节；有的喜欢先从一个小点引入，循序渐进逐渐聊到整体架构。不同的考察思路，仁者见仁。而今天想分享的是，为了减少八股面试给候选人的压力与困扰，以及切实掌握候选人的技术经验，针对MySQL架构原理这一块的细节，进行阶段的梳理，进行详细探讨。突出面试官提问要务实，表达思路清晰，此外，也希望候选人抓住机会，简明扼要的回答核心点。

二、MySQL架构原理相关核心点

在涉及MySQL架构原理考察，往往2条SQL，一个查询、一个更新，就可以全面覆盖到MySQL架构的各个组件。之前我们系列1、2文章基于5.7版本的MySQL进行过详细分析，现在开启面试17连问模式。

首先客户端通过MySQL驱动连接到MySQL服务端的SQL接口(也叫连接器)，用的是TCP协议，建立的连接是【长连接】，默认是8小时后超时断开，而服务端默认支持最大连接数是151个，最大可以修改到10w个。SQL接口首先会验证客户端的用户密码是否正确，然后查询是否命中缓存。这个缓存组件在MySQL5.7 默认是关闭的，在8.0版本已经被移除。原因是，缓存组件要求有大量重复查询才有效，而这种场景在redis、或者服务应用层实现会非常高效。对比之下，MySQL层提供这种功能业务价值不高，在8.0已经被移除该组件。

查询SQL用户鉴权通过后，到达SQL解析器。在SQL解析器进行词法分析、语法分析、语义分析，并生成语法树。如果SQL的关键字拼写错误、表名、列名不存在，在SQL解析器就报错返回。

经过SQL解析器后得到了查询SQL具体树形结构语法树，SQL优化器，针对查询SQL的可能执行路径进行分析评估，选择一个最优执行路径。这个阶段会将SQL里写的那种无效条件，或者组合条件进行优化。1=1的会被删掉，以及复合索引创建的时候是name+age,条件写成了age =xx and name =xx的，SQL优化器帮你纠正过来，让索引查询生效。查看优化器分析得到的SQL执行计划，可以用explain formation= json SQL语句来查看。

最后，存储引擎（也有的说有个执行器，实际官方架构图是没有执行器这个组件的）执行具体查询任务。从磁盘加载对应数据到bufferpool，最后通过SQL接口返回结果给客户端。存储引擎读取数据这里也有一些细节，包括数据如何加载、索引是否覆盖、是否回表查询、索引如何存储、存储引擎的区别等。后面的问题再深入讨论。

update SQL和select SQL的前面2.1 说的执行过程一致，唯一区别在于存储引擎部分。

首先更新SQL来到存储引擎(默认说的是InnoDB)后，存储引擎需要将目标数据从磁盘加载到bufferpool。在MySQL InnoDB每次加载数据，只加载一页，每页数据大小是16kb，而操作系统的一页数据是4kb。这是一个小细节。然后存储引擎InnoDB bufferpool的大小是128MB。缓存最多可以放8192个页数据。

存储引擎把目标数据加载到内存后进行修改，需要记录undoLog、redoLog两种日志。其中undoLog为了方便回滚，redoLog用于数据恢复。而作为MySQL服务层的二进制日志文件binLog默认是不开启的，如果开启的话，更新sql执行完成也会进行记录binLog，方便主从数据同步备份。

由于更新sql是在内存中进行，效率很高。当undoLog、缓存更新、redoLog记录完成，事务提交，就反馈执行接口给客户端。最后存储引擎再不定时将存储引擎的脏数据更新到磁盘。

这里涉及的是顺序读写和随机读写。redoLog和undoLog都是顺序读写，以及直接修改内存，这两种都是效率非常高的更新。

而如果改成直接更新磁盘ibd文件，这是一个磁盘随机读写，效率很慢。虽然架构设计、执行步骤上更简单明了，但是两种读写方式决定了直接刷盘效率更低。索引MySQL选择了增加redoLog、undoLog、缓存更新，最后不定时刷盘的架构设计进行高效更新操作。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」

MySQL架构主要有2层，一个是服务层、一个是存储引擎层。

服务层有SQL接口、解析器、优化器、此外还有binLog日志。

SQL接口通过连接池管理客户端的连接，默认最大连接只有151个，最多可以改成10w个。客户端的SQL连接、结果返回、用户鉴权、超时断开等都是SQL接口负责。

解析器，主要负责词法解析、语法解析，以及生成树状语法树。这里也会把关键字、表名、字段名、计算符号进行校验判断。

优化器，主要生成执行计划，并选择用索引，生成最优执行计划。

binLog，就是记录增删改，还有授权、新建表、改表结构等操作记录日志。这里附带说一下，binLog不会记录select、show这种操作的日志，以及binLog默认是关闭的。

存储引擎层，主要负责缓存查询和更新数据，并从磁盘加载数据。在存储引擎里，还有redoLog、undoLog的记录，当有数据更新时，会涉及内存脏页数据的刷盘。

三、存储引擎相关知识要点

最常用的是InnoDB、MYISAM。用的比较少但是也很实用的有Memory、Archive存储引擎。

InnoDB，支持行锁、表锁，事务，支持MVCC的多并发事务控制，支持4种事务隔离级别。innoDB 会将表数据分为2个文件进行存储，一个是ifm-存放表结构，一个是ibd-存放聚集索引（数据和主键索引都在这个索引文件里）。当表有主键primary key列，这个主键就是聚集索引。如果没有PK，则选择一个not null 且unique的列作为聚集索引。如果没有PK，也没有not null 且unique的列，MySQL就默认新增一个隐藏的row-id作为聚集索引，强制让每个InnoDB表都是一个B+树的聚集索引。

MYISAM，在MySQL5.5以前是默认的存储引擎，但是不支持行锁，不支持事务，只支持表锁。在读的时候是共享锁，写的时候是排它锁。所以myisam存储引擎的并发效率低。myisam存储引擎，一个表有个文件，frm是存储表结构，myd是存储表数据，myi是存储索引文件。如下图:

此外MyISAM有个特点，支持全文索引，是基于分词创建的索引，但是中文分词不好。myisam这个中文支持不友好的特性，在国内很容易被es、mongoDB替换，所以myisam存储引擎大家用的会比较少。

而archive存储引擎，也叫档案引擎。这个引擎只支持新增和查询。不支持修改和删除。优点是，archive存储引擎对数据进行了压缩，采用该引擎存储数据，比myisam、innodb存储要节省大概80%左右的磁盘存储。所以如果是业务系统日志数据存储、历史数据归档备份存储查询，可以考虑archive存储引擎，将大幅节省存储空间。

最后，memory存储引擎，数据仅存在内存，不会存储到磁盘，如果服务宕机，数据就会丢失。memory存储引擎支持hash索引和B树索引。所有字段长度是varchar(10)或char(10)。适合临时缓存数据查询存储、且容许丢失的场景。

MySQL每个数据页大小是16KB。这么定义，好处是比操作系统的4kb大，而且是4kb的整数倍。在innoDB存储引擎的B+树索引里，每个节点有16kb大，由于非叶子节点只存储索引字段值，不存在整行数据，B+树的每个非叶子节点就可以存储非常多的数据索引值，整体看整个索引树就是一颗树高大概~4的胖乎乎的树，但是实际已经可以构建几千万行数据的索引。

当目标数据查到之后，MySQL也是直接按最小16kb一个数据页的大小去磁盘加载、写入数据，由于是操作系统整数倍数据叶大小，IO效率会很高。

这个16kb的数据页大小，与MySQL一个表能存几千万条数据息息相关。

采用的是优化过的LRU算法-最近最少使用冷热数据分离算法。具体就是，缓存是一个链表存储，且分2段，上半部5/8+下半部/8。上半部会是热数据，下半部分是冷数据。

当数据加载到bufferpool后，先进入冷数据头部，如果1秒钟之后仍被访问，就会被置顶挪到热数据头部。如果1s内被访问，位置不变。最近最少使用的缓存，随着时间推移，不断被挪到链表尾部，直到被淘汰。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」

所谓脏数据，就是bufferpool里被修改过，还没保存到磁盘的数据。InnoDB有2个参数innodb_max_dirty_pages_pct_lwm+innodb_max_dirty_pages_pc（默认75%）。当脏页比例大于这两个值，就开始进入刷盘模式。默认lwm是零，所以默认内存的脏页数据最多可以达到128M*75%=96Mb，脏数据才开始被刷到磁盘。

事务的原子性，要求要么全部成功、要么全部失败。当事务失败回滚的时候，需要用到undoLog。undoLog之前我们的日志三宝文章有说过，这个日志记录的是更新前数据值的内容，如果事务回滚，可以利用该日志修改前数据值进行回滚。

日志三宝之一redoLog，对MySQL事务的一致性、持久性起到关键作用。redoLog记录的是修改后的值。有自己的更新参数：【innodb_flush_log_at_trx_commit】控制。具体如下：

innodb_flush_log_at_trx_commit=0： 表示每次事务提交时都只是把 redo log 留在 redo log buffer 。

innodb_flush_log_at_trx_commit=1： 表示每次事务提交时都将 redo log 直接持久化到磁盘，

innodb_flush_log_at_trx_commit=2： 表示每次事务提交时都只是把 redo log 写到系统 page cache，这个缓存大概1s左右刷一次到磁盘。

当系统宕机导致缓存脏数据没来得及更新到磁盘，redoLog将支持MySQL进行数据恢复，确保事务提交的数据的一致性和持久性。

四、索引相关高频实用考察点

在查询SQL条件之后的字段适合做索引。比如where、group by、having、order by关键字字段后的列。

当然索引数量不是越多越好。索引也是一把双面剑，除了可以帮助提升查询搜索效率，但是索引也带来了而外的存储空间开销，最重要的是会影响更新写入的效率。如果一个字段变成了索引，更新字段值，还有更新其他的索引树数据。

散列度低的字段。计算公式散列度公式：count(distinct(column_name)) / count(*)。

比如性别，只有男、女两种，一个用户表几百万行数据，性别字段的散列度几乎为0，不能用于建索引。这种索引的搜索效率，比全表扫描还要慢。原因是，散列度低的索引，整颗B+数非叶子节点，有非常多值一样的节点，当查询命中索引时，几乎要一个个去随机加载全部索引，最后还有回表查询，所以比直接全部扫描查询慢很多。

导致索引失效，最常见的是查询条件里有联合索引字段，但不符合最左匹配原则。比如name+city是联合索引，查询的时候，查询条件只有city是不会走索引查询。另外如果查询的时候name是like '%xx%',也不会走索引。

这里有个注意的地方，如果你的sql是where city=‘xx’ and name=‘xx’，理论上是不应该走索引。但是MySQL的查询优化器，自动识别满足符合索引条件，对执行计划进行了优化，最终你的sql是会走索引查询。

innoDB存储引擎的每个表都有聚集索引。聚集索引的每一行数据是存在B+树的叶子节点，并且是有序的。

而联合索引，也是B+树，但是叶子节点存放的是主键id的值。命中联合索引查数据的时候，最后需要在聚集索引里回表查询。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」

InnoDB存储引擎，采用的是B+树索引。如下图，新增id为2、1、4、、6、20的行数据。该数据结构如下：

在非叶子节点上，只存储索引字段值，并且每个节点可以存放很多个索引值。

在叶子节点，如果是聚集索引，将把对应主键的整行数据存放在该节点，而且叶子节点是链表结构，对范围查询非常友好。

在叶子节点，如果是非聚集索引，将存放相关索引值对应的主键值。这样的好处，只有聚集索引有表的全部真实数据，而非聚集索引树的数据大小就变得比较小，避免数据冗余。最终通过回表查询，聚集索引的检索也会很快完成。

二叉树是一种树形结构，每个节点最多有两个子节点，一个左子节点和一个右子节点。

平衡二叉树-AVL树，也是二叉树，但是任意节点的左右子树高度差不超过 1。为了防止二叉查树退化为链表，产生不良的查性能。

B树，属于多叉树，也叫做平衡多路查树（查路径不只两个，这个和传统二叉树不一样）。数据库索引里大量使用者B树和B+树的数据结构。

我们直接举例，写入1、2、4、四个值。

二叉树如下图，树高是4。假如有序写入，二叉树的树高将高的惊人，将变成一个链表。

平衡二叉树，按序写入1、2、4、。树高，只有层，比二叉树4层好多了，右子树比左子树高1层。

最后，B树，同样按顺序写入1、2、4、。树高也只有2层。比二叉树、平衡二叉树都矮，树高也小，搜索效率越高。

推荐阅读拉丁解牛相关专题系列(欢迎交流讨论搜：拉丁解牛)：

1、JVM进阶调优系列(5)CMS回收器通俗演义一文讲透FullGC

2、JVM进阶调优系列(4)年轻代和老年代采用什么GC算法回收?

、JVM进阶调优系列()堆内存的对象什么时候被回收?

4、JVM进阶调优系列(2)字节面试:JVM内存区域怎么划分，分别有什么用？

5、JVM进阶调优系列(1)类加载器原理一文讲透

6、JAVA并发编程系列(1)Future、FutureTask异步小王子