MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。


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



一、前言背景

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

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

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

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

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

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

3.1 MySQL的存储引擎用过几种?

3.2 MySQL每个页数据大小是多少?

3.3 bufferpool数据清理算法是什么?

3.4 bufferpool内存脏数据什么时候会刷盘?

3.5 事务的原子性主要与哪个日志相关?

3.6 事务的一致性、持久性主要与哪个日志相关?

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

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

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

4.3 什么样的字段不宜建索引?

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

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

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

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


一、前言背景

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


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

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


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

      首先客户端通过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接口返回结果给客户端。存储引擎读取数据这里也有一些细节,包括数据如何加载、索引是否覆盖、是否回表查询、索引如何存储、存储引擎的区别等。后面的问题再深入讨论。


2.2 简单说说一条更新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记录完成,事务提交,就反馈执行接口给客户端。最后存储引擎再不定时将存储引擎的脏数据更新到磁盘。


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

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

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

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

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

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

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

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

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

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

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

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

3.1 MySQL的存储引擎用过几种?

    最常用的是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存储引擎,一个表有3个文件,frm是存储表结构,myd是存储表数据,myi是存储索引文件。如下图:

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

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

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


3.2 MySQL每个页数据大小是多少?

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

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

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

3.3 bufferpool数据清理算法是什么?

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

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

3.4 bufferpool内存脏数据什么时候会刷盘?

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

3.5 事务的原子性主要与哪个日志相关?

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

3.6 事务的一致性、持久性主要与哪个日志相关?

     日志三宝之一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进行数据恢复,确保事务提交的数据的一致性和持久性。


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

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

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

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

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

4.3 什么样的字段不宜建索引?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
3
3
0
95
分享
相关文章
|
12天前
|
《docker基础篇:2.Docker安装》包括前提说明、Docker的基本组成、Docker平台架构图解(架构版)、安装步骤、阿里云镜像加速、永远的HelloWorld、底层原理
《docker基础篇:2.Docker安装》包括前提说明、Docker的基本组成、Docker平台架构图解(架构版)、安装步骤、阿里云镜像加速、永远的HelloWorld、底层原理
251 89
ClickHouse 架构原理及核心特性详解
ClickHouse 是由 Yandex 开发的开源列式数据库,专为 OLAP 场景设计,支持高效的大数据分析。其核心特性包括列式存储、字段压缩、丰富的数据类型、向量化执行和分布式查询。ClickHouse 通过多种表引擎(如 MergeTree、ReplacingMergeTree、SummingMergeTree)优化了数据写入和查询性能,适用于电商数据分析、日志分析等场景。然而,它在事务处理、单条数据更新删除及内存占用方面存在不足。
87 21
Druid 架构原理及核心特性详解
Druid 是一个分布式、支持实时多维OLAP分析的列式存储数据处理系统,适用于高速实时数据读取和灵活的多维数据分析。它通过Segment、Datasource等元数据概念管理数据,并依赖Zookeeper、Hadoop和Kafka等组件实现高可用性和扩展性。Druid采用列式存储、并行计算和预计算等技术优化查询性能,支持离线和实时数据分析。尽管其存储成本较高且查询语言功能有限,但在大数据实时分析领域表现出色。
45 19
|
4天前
|
Doris 架构原理及核心特性详解
Doris 是百度内部孵化的OLAP项目,现已开源并广泛应用。它采用MPP架构、向量化执行引擎和列存储技术,提供高性能、易用性和实时数据处理能力。系统由FE(管理节点)和BE(计算与存储节点)组成,支持水平扩展和高可用性。Doris 适用于海量数据分析,尤其在电商、游戏等行业表现出色,但资源消耗较大,复杂查询优化有局限性,生态集成度有待提高。
37 15
Git进阶笔记系列(01)Git核心架构原理 | 常用命令实战集合
通过本文,读者可以深入了解Git的核心概念和实际操作技巧,提升版本管理能力。
一文彻底讲透GPT架构及推理原理
本篇是作者从开发人员的视角,围绕着大模型正向推理过程,对大模型的原理的系统性总结,希望对初学者有所帮助。
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
56 16
硬核揭秘:线程与进程的底层原理,面试高分必备!
嘿,大家好!我是小米,29岁的技术爱好者。今天来聊聊线程和进程的区别。进程是操作系统中运行的程序实例,有独立内存空间;线程是进程内的最小执行单元,共享内存。创建进程开销大但更安全,线程轻量高效但易引发数据竞争。面试时可强调:进程是资源分配单位,线程是CPU调度单位。根据不同场景选择合适的并发模型,如高并发用线程池。希望这篇文章能帮你更好地理解并回答面试中的相关问题,祝你早日拿下心仪的offer!
26 6
MySQL 面试题
MySQL 的一些基础面试题
MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合
2024年小结:感谢阿里云开发者社区每月的分享交流活动,支持持续学习和进步。过去五个月投稿29篇,其中17篇获高分认可。本文详细介绍了MySQL InnoDB存储引擎的MVCC机制,包括数据版本链、readView视图及解决脏读、不可重复读、幻读问题的demo演示。

相关产品

  • 云数据库 RDS MySQL 版
  • AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等