mysql面试题整理

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 本文整理了mysql常见的面试题,知识点偏底层

1、当执行简单的sql的时候,数据库底层发生了什么?

image.png

-> 连上数据库

-> query_cache(查询缓存)

笔记:query cache在5.7版本中不建议使用,在8.0版本被移除

原因:不好用,有其它更好的取代(redis)

不足:一旦有任何(数据、sql)变动都会失效,命中率太低

-> parser(解析器)

词法 语法 解析,拿到一个解析树

-> pre-processor(预处理器)

语法正确,但是语义不对会进行处理

--> optimizer(优化器)

非常重要的模块,根据sql语句基于成本cost寻找最佳执行路径

比如:子/连接查询,先查哪张表(小表驱动大表);

条件优化(and 条件语句理论上走不了索引,实际上可以);

等价位与、嵌套连接查询、语义。。。

--> execution(执行计划)

explain

--> excutor(执行器)

--> storage Engine(存储引擎)

先到Buffer Pool缓存区看有没有,如果没有到DBfile去查然后返回

常用的存储引擎:

InnoDB    2个文件    .frm(表结构)    .idb(数据信息+索引信息)

MyISAM    3个文件    .frm    .myd(数据信息)    .myi(索引信息)

Memory    1个文件    .frm


2、Mysql的架构与内部模块之间的关系及几个关键日志?

image.png

- show engine innodb status;

(1)InnoDB 三大特性:

1、ADAPTIVE HASH INDEX(自适应hash索引)

show variables like '%adaptive%';

查询的时候如果走的二级索引,且用的是等值查询,1s内连续3次,就会在内存里建立hash结构的索引,查询效率为O(1)


2、BUFFER POOL(缓存池):相当于mysql的缓存

changebuffer也在Buffer Pool中

image.png

image.png

OS cache(操作系统)的Buffer Pool采用LRU,会导致问题:预读区失效、缓存污染(一次读很多页,把常用的缓存挤掉)

mysql的Buffer Pool进行了优化,采用了上面分代的思路:分为new_sublist(存储常用的缓存,63%,innodb_new_blocks_pct)和old_sublist(存储新来的页数据,37%,innodb_old_blocks_pct),新来的数据会存放在old_sublist操作,当1s(innodb_old_blocks_time)内再次访问,就会被移到new_list热点区的head。

3、DoubleWrite Buffer(双写缓存)

刷脏部分失败的情况,用双写缓存区确保刷脏成功。

解释:

刷脏:将内存中的数据页保存到磁盘,由于一页数据是16k,操作系统为4k,预读3k,在刷脏的过程中,有可能因为宕机,导致刷脏失败,这个时候就可以用双写缓存区确保刷脏成功。

(2)三大日志文件:

1、binary-log(归档日志)  

bin log是MySQL数据库级别的文件,记录对MySQL数据库执行修改的所有操作,不会记录查询操作,主要用于恢复数据库和主从复制

放在server,没有固定大小,持续追加内容,记录DDL、DML语句

binlog_format:ROW(每行)默认、statement(修改的sql)、mixed(自定义)

作用:

  1. 数据恢复:数据库数据丢失后,我们可以从某个时间节点的数据备份和该时间点之后的BinLog来恢复数据库的数据;
  2. 主从复制:主从复制过程中,主数据库将自身的BinLog发送给从数据库,从数据库通过解析BinLog同步主数据库的数据变更,从而达到主从数据一致;

image.png

2、redo-log(重做日志) 

redo log是innodb引擎级别文件,用于记录innodb存储引擎的事务日志,不管事务是否提交都会被记录下来,用于数据恢复。当数据库发送故障,innodb会使用redo log恢复到发生故障前的时刻,以此来保证数据的完整性,将参数innodb_flush_log_at_tx_commit设置为1,那么执行commit时会将redo log同步写到磁盘。

以页为单位进行存储:存储页的数据快照

image.png

各个部分的详细释义如下:

  • type:该条redo日志的类型。在MySQL 5.7.21这个版本中,InnoDB中的redo日志包含53种不同的类型,稍后会详细介绍不同类型的redo日志。
  • space ID:表空间ID。
  • page number:页号。
  • data:该条redo日志的具体内容。

2个文件 固定大小(48) 环形结构

二阶段提交:记录的时候处于prepare状态,等bin-log修改之后才转commit状态。

目的:避免写binlog时宕机,靠这个文件没法完成主从复制

 

3、undo-log(回滚日志)

保存记录修改前的内容(逻辑日志,存储的是事务相反的SQL,可以通过这个SQL执行进行回滚操作)。通过undo log可以实现事务回滚,并且可以根据undo log回溯到某个特定版本的数据,实现MVCC

事务回滚通过这个日志完成


3、一次update的底层执行过程

image.png

update之前的数据:name为"天"

sql:update name="天明"

底层流程:

1、将name为"天"的数据查出来,放到Buffer Pool中(查询的流程可以看前面)

2、name="天"记录到undo log中,以便回滚

3、update name="天明"记录到redo log中,并且置为prepare状态

4、修改结果告诉server

5、记录bin log,并开线程将redo log中的状态置为commit

6、准备commit事务

7、redo log日志状态置为commit

7、刷盘,数据写入磁盘

8、doublewrite双写,确保刷脏(部分)成功


4、bin-log和redo-log的区别?

(1)bin-log会记录所有日志记录,包括innoDB、MyISAM等存储引擎的日志;redo-log值记录innoDB自身的事务日志。

(2)bin-log只在事务提交前写入磁盘,一个事务只写一次;而事务进行过程中,会有redo-log不断写入磁盘。

(3)bin-log是逻辑日志,记录的是SQL语句的原始逻辑;redo-log是物理日志,记录的是在某个数据页上做了什么修改。

(4)bin log日志的顺序是按照提交的顺序来的,而redo log在并发的情况下,不同事务之间的不同版本的记录会穿插写入到RedoLog文件中


5、索引底层数据结构的推导

(1)链表:查询效率是O(n)

(2)二叉树:用二分查找法,效率提高

image.png

问题:如果是递增/递减的值,会退化成斜树

(3)平衡二叉树:通过左右旋转达到平衡

image.png

问题:1、高度太高,io次数会很多;2、新增会打乱树结构,成本高

(4)多路平衡查找树(B-Tree):树高变低,磁盘io减少

image.png

问题:1、根节点存放数据,导致每一页存放的数据不够多
            2、不适合范围查找

(5)B+Tree:设计:1、所有data(行数据)存放在叶子节点

image.png

2、根节点只存放索引和指针

3、所有叶子结点用双向指针连接起来

优势:1、适合范围查找,遍历数据库效率高

  2、树高度更低,减少磁盘io

  3、查询的稳定性更高

(6)hash:查询效率最高,每次都是O(1)

问题:1、不支持范围查询、模糊查询;2、哈希冲突

(7)如果你是引擎设计师,会怎么设计?

聚集索引:索引文件和数据存放在一起,叶子节点存放行数据、非叶子结点存放索引和指针

二级索引:叶子结点存放索引和聚集索引的索引值

6、索引的几个概念

(1)回表:通过二级索引查询的数据项不满足所需的数据,需要再去聚集索引查询数据

(2)覆盖索引:查询的数据项都在二级索引中,不需要进行回表操作

(3)索引下推:二级联合索引abc、遵循最左匹配原则可以用到b或者c索引,这个叫索引下推


7、事务的认识与特性

1、事务定义:是数据库管理系统执行过程中的逻辑单位,序列化的不可分割的操作集合。

2、ACID的特性:

原子性:Atomicity最小的执行单位,要么都成功,要么都失败;通过undolog日志保证隔离性:Isolation两个事务互不影响,事务并发的时候,哪些能读到,哪些不能读到;通过Transaction事务保证持久性:Durability数据一旦落盘,及时宕机恢复,数据也不会丢失;通过redo+binlog+磁盘保证一致性:Consistency事务执行前后数据完整性都保持一致;通过AID最总保证一致性


8、事务并发带来的问题,以及如何解决事务并发的问题?

事务并发的问题

P1脏读(dirtyread):读到了其它事务未提交的数据P2不可重复读(Non-RepeatableRead):读到了其它事务已提交的修改或删除的数据,导致前后两次读不一致P3幻读(PhantomRead):读到了其它事务已提交的新增数据,前后两次读不一致


解决方法:隔离级别

image.png


9、MVCC+LBCC核心思想参考文章

在数据库并发场景中,只有读-读之间的操作才可以并发执行,读-写写-读写-写操作都要阻塞,这样就会导致 MySQL 的并发性能极差。采用了 MVCC 机制后,只有写写之间相互阻塞,其他三种操作都可以并行,这样就可以提高了 MySQL 的并发性能。也就是说 MVCC 具体解决了以下问题:

  • 并发读-写时:可以做到读操作不阻塞写操作,同时写操作也不会阻塞读操作。
  • 解决脏读、幻读、不可重复读等事务隔离问题,但不能解决上面的写-写(需要加锁)问题。

当前读和快照读

当前读:读加锁(selectforupdate),读的是最新的数据快照读:普通查询

MVCC主要包含三部分内容:数据库中的3个隐藏字段、UndoLog日志、ReadView读视图

(1)3个隐藏字段

中文名

名称

备注

行id

db_row_id

隐藏主键

事务id

db_trx_id

最后修改本记录的事务ID

回滚指针

db_roll_ptr

指向这条记录的上一个版本(存储在Rollback Segment)

image.png

(2)ReadView视图:

包含:m_creator_trx_id:创建的事务TRX_IDm_low_limit_id:下一事务的ID(高水位)m_up_limit_id:活跃事务中最小的事务ID(低水位)
m_ids:活跃事务IDm_low_limit_no:回滚到上一个版本的事务id高水位:high_water_markm_low_limit_id低水位:low_water_markm_up_limit_id事务可见性判断规则:1、被访问的记录的版本事务ID<=低水位(活跃事务中最小的事务ID)可见2、被访问的记录的版本事务ID>高水位(分配给下一个事务的ID)不可见3、被访问的记录的版本事务ID在高低水位之间:去看是否在活跃链表(m_ids)中,如果在m_ids中,表明RV创建的时候,事务处于活跃状态,不可见,否则可见4、如果上面还不行,就到undolog回滚链表里面去找(m_low_limit_no)

image.png


(3)几种隔离级别创建Read_View的规则

RU:总是读最新的数据行(和MVCC不兼容)RC:每一次select都会创建一个ReadViewRR:第一次select(不是事务开启的时候)时创建Global_Read_View,后面会一直用这个Read_ViewSerializable:对所有读取的行加锁(和MVCC不兼容)

image.png

(4)undo log结构和ReadView结构的关系:

image.png


8、mysql的锁

行级锁和表级锁SharedandExclusiveLocks:行级锁S:乐观锁/共享锁:查询shareX:悲观锁/排它锁:修改forupdateIntentionLocks:表级锁意向锁补充1:当事务获取某行的S锁,会先去加一个意向表级别的S锁当事务获取某行的X锁,会先去加一个意向表级别的X锁意义:如果要去锁表的时候,先去检查有没有表级别的意向锁----------------------------------------行级锁的三种实现RecordLocks:记录锁,索引精准命中,锁这一行GapLocks:间隙锁,没有命中,锁(key1, key2)
Next-KeyLocks:临键锁,范围查询,锁(key1, key2]
补充2:这三种都是基于索引来说的,查询必须要走索引,否则就会锁整张表

行级锁的三种实现图:

image.png


9、mysql调优

发送死锁怎么办?预调优:顺序访问、走索引、能等值不走范围、大事务改成小事务死锁解决:1、kill死锁线程简单粗暴2、找到拿到锁的事务,看它的业务处理,再进行处理----------------------------------------MySQL优化代码优化多线程数据库连接池分库分表读写分离主从集群缓存消息队列


10、几种并发问题的解决:

脏读:不可重复读:MVCC幻读:Gap锁


11、几种概念

索引覆盖:select的字段都在使用的索引树上,不需要回表索引下推:前提:对于联合索引,如果查询中有某个列的范围查询,则其右边所有列都无法使用索引进行快速定位MySQL5.6之前:当进行索引查询时,首先根据索引来查找记录,然后再在server层根据where条件来过滤记录MySQL5.6开始,数据库在取出索引的同时,会根据where条件在引擎层直接过滤掉不满足条件的记录,减少回表次数。
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
1月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
25天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
19天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
26天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
1月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
1月前
|
SQL 关系型数据库 MySQL
京东面试:什么情况下 mysql RR不能解决幻读? RR隔离mysql如何实现?
老架构师尼恩在其读者交流群中分享了关于MySQL事务隔离级别的深入解析,特别针对RR级隔离如何解决幻读问题进行了详细讨论。文章不仅解释了ACID中的隔离性概念,还列举了四种事务隔离级别(未提交读、提交读、可重复读、串行读)的特点及应用场景。尼恩通过具体的例子和图表,清晰地展示了不同隔离级别下的并发事务问题(脏读、不可重复读、幻读)及其解决方案,特别是RR级隔离下的MVCC机制如何通过快照读和当前读来防止幻读。此外,尼恩还提供了相关面试题的解答技巧和参考资料,帮助读者更好地准备技术面试。更多详细内容和实战案例可在《尼恩Java面试宝典》中找到。
|
1月前
|
SQL 安全 关系型数据库
MySQL 增删操作面试题
MySQL 增删操作面试题
124 1
|
1月前
|
SQL 关系型数据库 MySQL
美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)
本文详细解析了MySQL索引失效的多种场景及解决方法,包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析,帮助读者深入理解索引失效的原因,并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源,助力面试者提升技术水平,顺利通过面试。
|
1月前
|
存储 关系型数据库 MySQL
面试官:MySQL一次到底插入多少条数据合适啊?
本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。
下一篇
无影云桌面