【面试】Mysql

TaiKuLaHa

已于 2023-07-31 19:36:06 修改

阅读量1.1k

点赞数

分类专栏：数据应用面试文章标签： mysql 面试

于 2023-06-21 17:41:25 首次发布

本文链接：https://blog.csdn.net/JH_Zhai/article/details/131332154

版权

数据应用面试专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1 SQL

关系型代数

TODO

常用SQL

聚合函数和窗口函数的区别
聚合函数输入多条输出一条，窗口函数输入多条输出多条

2 锁

TODO

3 索引

聚集索引和非聚集索引

最左匹配原则

回表索引覆盖索引下推

回表

主键索引的叶子结点存储的是一行完整的数据。
非主键索引的叶子结点存储的则是主键值。

这就是所谓的回表查询，一共搜索了两棵 B+Tree，先搜索非主键索引B+树定位主键值，再搜索主键索引的B+树行记录，它的性能较扫一遍索引树更低。
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
在这里插入图片描述
https://zhuanlan.zhihu.com/p/401198674

索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

索引下推

查询(A=a1, C=c2)->索引(A, B, C) ，根据最左匹配只会用到索引(A), 但是会在用到查询索引中C值过滤，再回表

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

事务

事务的ACID

Atomic 原子性: 事务是一系列操作的结合，原子性就是这一系列操作要么都成功，要么都失败，像个不可分割的原子(虽然后来发现原子还能分为夸克)
Consistent 一致性: (笔者认为最难理解的) ACID中一致性指的是事务操作不会打破数据库的各种约束，比如主键约束等 / CAP中- 的一致性是在业务层面上，以银行转账为例，不管怎么互相转，资金总量是不会变化的。
Isolation 隔离性: 事务之间的操作不会互相影响，这是最常用的一个特性，非常喜欢IBM文献中的理解隔离性是围绕可见性；根据隔离级别分为读未提交、读已提交、可重复读、串行读等四个等级
Durability 持久性: 事务写进去的数据不会再被撤回

推荐参考文献:
『浅入深出』MySQL 中事务的实现

Mysql事务隔离级别

事务隔离是为了解决脏读、不可重复度读和幻读三个问题，依据解决问题能力不同分为了读未提交、读提交、可重复读和串行化四种事务隔离级别

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
不可重复读：不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。
幻读：范围读取场景下，一个事务中前后两次读取的行数不同。

读未提交（READ UNCOMMITTED）
读提交（READ COMMITTED）: 解决脏读
可重复读（REPEATABLE READ）<–Mysql 默认隔离级别：解决脏读&不可重复读
串行化（SERIALIZABLE）：解决脏读&不可重复读&幻读

MVCC

(0) 基础概念
MVCC，即Multi-Version Concurrency Control （多版本并发控制）。它是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
数据库隔离级别读已提交、可重复读 都是基于MVCC实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。
(1) undo log
undo log，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。
可以这样认为，当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。
undo log有什么用途呢？

事务回滚时，保证原子性和一致性。
用于MVCC快照读。
(2) 版本链
多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。

(3) 快照读和当前读
快照读：读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：

select * from core_user where id > 2;

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

select * from core_user where id > 2 for update;
select * from account where id>2 lock in share mode;

(4) Read View
Read View是什么呢？它就是事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。
Read View有什么用呢？它主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据~

Read View是如何保证可见性判断的呢？我们先看看Read view 的几个重要属性

m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
min_limit_id:表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
max_limit_id:表示生成ReadView时，系统中应该分配给下一个事务的id值。
creator_trx_id: 创建当前read view的事务ID

Read view 匹配条件规则如下：

如果数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。
如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
如果 min_limit_id =<trx_id< max_limit_id,需腰分3种情况讨论
- 如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。
- 如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；
- 如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的。

(5) 如何实现的读已提交和可重复读
那又是如何实现读已提交和可重复读呢？其实很简单，就是生成ReadView的时机不同。

读已提交: 事务中的每次读操作都会生成一个新的ReadView，也就是说，如果这期间某个事务提交了，那么它就会从ReadView中移除。这样确保事务每次读操作都能读到相对比较新的数据
可重复读: 事务只有在第一次进行读操作时才会生成一个ReadView，后续的读操作都会重复使用这个ReadView。也就是说，如果在此期间有其他事务提交了，那么对于可重复读来说也是不可见的，因为对它来说，事务活跃状态在第一次进行读操作时就已经确定下来，后面不会修改了。

https://juejin.cn/post/7016165148020703246

Mysql 的binlog relog undolog日志

日志是mysql数据库的重要组成部分，记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志(binlog)和事务日志(包括redo log和undo log)。

1 binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

逻辑日志：可以简单理解为记录的就是sql语句。
物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog使用场景

在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复。

主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
数据恢复：通过使用mysqlbinlog工具来恢复数据。

binlog刷盘时机
对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；
1：每次commit的时候都要将binlog写入磁盘；
N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog日志格式
binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中。
- 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
- 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等。
ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
- 缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨
MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

基于Binlog与Flink实时同步数据仓库实践：CDC (Change Data Capture) + Merge实现
在数据仓库建模中，未经任何加工处理的原始业务层数据，我们称之为ODS(Operational Data Store)数据

实现步骤：

采用Flink负责把Kafka上的Binlog数据拉取到HDFS上。
对每张ODS表，首先需要一次性制作快照（Snapshot），把MySQL里的存量数据读取到Hive上，这一过程底层采用直连MySQL去Select数据的方式，可以使用Sqoop进行一次性全量导入。
对每张ODS表，每天基于存量数据和当天增量产生的Binlog做Merge，从而还原出业务数据。

Binlog是流式产生的，通过对Binlog的实时采集，把部分数据处理需求由每天一次的批处理分摊到实时流上。无论从性能上还是对MySQL的访问压力上，都会有明显地改善。Binlog本身记录了数据变更的类型（Insert/Update/Delete），通过一些语义方面的处理，完全能够做到精准的数据还原。

2 redo log

为什么需要redo log
我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log基本概念
redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：
在这里插入图片描述