首页  思维导图  详情

Mysql 面试大纲持续更新

2021-12-29 17:36:24   3  举报





AI智能生成

Mysql 面试大纲

面试

MySQL总结

知识点

Mysql执行过程

作者其他创作

大纲/内容

mysql基础

MYSQL由两部分组成

Mysql Server

连接器

管理连接，权限验证

查询缓存

命中则直接返回结果

分析器

内置函数

词法分析，语法分析

优化器

生成执行计划，选择要使用的索引

explain 查看解释执行计划

执行器

操作引擎返回结果

存储引擎

引擎

MyISAM

不支持行锁

不支持事务

不支持外键

没有崩溃恢复的能力

InnoDB

支持行锁

支持事务

支持外键

有崩溃恢复的能力

使用 InnoDB 独有的redo log 实现

索引

数据页

PPT

索引种类

主键索引（聚集索引）

加速查找+不能为null+一个表中只有一个

普通索引

仅加速查找

唯一索引

加速查询 + 列值唯一（可以有null）

组合索引（覆盖索引）

多列值组合成一个索引，专门用于组合搜索

全文索引

对文本内容进行分词，用于分词搜索

索引类型

FullText索引

Hash索引

BTREE（默认，查找稳定且高效）

为什么使用B+树？和B树的区别？

Mysql 锁机制（自制图）

Mysql 如何做到读写并行？
（多版本控制）

MVCC

简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题。

Mysql 中的死锁

Mysql 针对死锁的两种处理策略(喜马拉雅)

一，可以设置锁等待超时时间

默认超时时间 50s

二，发起死锁检测，主动回滚某一条事务，默认是开启的

缺点：

死锁检测会造成大量CPU消耗。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁

如何解决死锁

可以从业务逻辑上考虑

共享资源拆分为许多个小的共享资源

比如，把要更新的数据放在多条记录上，比如：把余额账户分成多个子账户

Mysql 中的锁

InnoDB 实现了两种类型的行级锁

共享锁（S锁）

排他锁（X锁）

--------------

行级锁优缺点

优点

锁的粒度小，发生锁冲突的概率低；处理并发的能力强

缺点

开销大；加锁慢；会出现死锁

加锁方式：

自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁。

为实现多粒度的锁机制，InnoDB 还有两种表级别的锁

意向共享锁（IS）

事务给数据行添加 S 锁前必须获得该表的 IS 锁

意向排它锁（IX）

事务给数据航添加 X 锁前必须获得该表的 IX 锁

行锁的分为三种算法

行级锁

InnoDB 是针对索引加锁的，不是针对记录加锁。

InnoDB 使用三种行锁算法支持事务隔离级别

Record Locks

该锁为索引记录上的锁，使用索引锁定记录

Gap Locks

该锁会锁定一个范围，但不包括记录本身。

Next-key Locks

该锁是 Record Locks 和 Gap Locks 的组合，即锁定一个范围并且锁定该记录本身。

InnoDB 使用 Next-key Locks 解决幻读问题。

锁优化建议：

1，设计合理的索引

2，减少范围过滤条件

3，缩短事务执行时间

4，尽可能使用低级别的事务隔离

Mysql 隔离级别

四个隔离级别

1.，读未提交（Read UnCommitted）

一个事务读取到另一个事务未提交的数据。（脏读）

大部分业务场景下都不不允许脏读出现。

2，读已提交（Read Committed）

事务A读取事务B中修改的数据，在事务B提交前和提交后读取到的数据不一致的现象。（不可重复度）

一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果。

3，可重复读（Repeatable Read）
Mysql 默认隔离级别

比如：一个事物中，多次对整表进行统计查询，结果不一致。（幻读）

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果

通过快照读

通过 redo log 版本链实现:

因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；
如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

可重复读和读已提交的区别:

1、在可重复读级别下，在事务开始的时候会创建一个readview

2、在读提交级别下，每个语句执行前都会重新生成一个新的readview

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

对于读提交，查询只承认在语句启动前就已经提交完成的数据；

4，序列化（Serizlizable）

所有事务都串行执行（可以避免脏读，不可重复读，幻读）

Buffer Pool（自制图）

什么是buffer pool?

Buffer Pool 的功能就是缓存 “页”，减少磁盘IO，提高读写效率。

Buffer Pool 的内存结构？

查询

先在Buffer Pool 中查询需要的数据页是否存在，存在就返回，不存在就从磁盘查询，加载到Buffer Pool 中。

更新

update school set name = 'xs' where id = 400；

1. 先查询要更新的数据页是否在Buffer Pool 中，有就直接更新。不存在从磁盘读取到 Buffer Pool 中，再更新。
2. 将更新内容写入redo log（崩溃恢复）。

这种先更新 Buffer Pool 中数据页的值，在合适的时机再刷回磁盘的方法，大大提高了效率，但也带来了“脏页”的问题。

“脏页” 何时写回磁盘

1. 后台线程定时刷新

2. Buffer Pool 内存不足

3. redo log 写满

4. 数据库正常关闭时

Buffer Pool 缓存页的淘汰策略

LRU

Explain 使用

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,

select_type

table

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等.

ALL: 全表扫描

Index: 全索引扫描

eq_ref: 唯一索引扫描

ref: 通常指的是像前缀扫描这些

system,const: 常量级别

NULL: 效率最高,执行时不扫描全表或者索引, 例如通过索引获取最小值

possible_keys

key

key列显示MySQL实际决定使用的键（索引

ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref

rows

估算找到符合条件的记录所扫描的行数, 通常来说,越小越好

Extra

包含MySQL解决查询的详细信息,

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

Mysql 优化

库表结构优化

索引优化

查询语句优化

分库分表

为什么要分库分表?

高并发,大数据

怎么分的

比较常见的包括：cobar、TDDL、atlas、sharding-jdbc、mycat

水平拆分

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。

垂直拆分

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

全局唯一id如何生成？

数据库主键

uuid

时间戳

分布式id生成算法： snowflake算法

分区

表分区与分表的区别

什么是表分区?

分区类型

分布式事务

对比 5 种分布式事务方案，还是宠幸了阿里的 Seata（原理 + 实战）

XA：2PC（两阶段提交）

同步阻塞的刚性事物，所有的事物一起提交

XA：3PC（三阶段提交）

解决 2PC 的事物阻塞不能释放问题

TCC：try-confirm-cancel

pipline 的手动回滚事务

对业务侵入性强，代码量大，还要考虑 cancel 失败，重试，网络等原因

事物消息：最终一致性

利用消息中间件的事物消息特性，采用最终一致性实现分布式事务

阿里 Seata

支持： AT、TCC、SAGA 和 XA 等事务模式，这里重点介绍 AT模式。

面试

普通索引和唯一索引该怎么选择？

查询上基本没影响；
更新尽量使用普通索引

因为可以利用 change buffer

唯一索引因为要判断唯一性，比如把数据页读入内存

为什么MySQL数据库要用B+树存储索引？

1，更少的IO次数

因为非叶子节点可以存储更多数据，减少树的高度

2，更适用于范围查询

B+树种范围查询只需要遍历主键索引，找到对应的叶子节点即可。

3，更稳定的查询效率

B+树的数据都在叶子节点，查询复杂度稳定为树高。

为什么不使用红黑树而使用 B+树？

红黑树本质还是一个二叉查找树，会导致树的高度很高

MySQL主从复制原理的是啥？

binlog

如何实现mysql的读写分离？

一条SQL查询语句是如何执行的？

客户端 -> 连接器 -> 分析器 -> 优化器 -> 执行器 -> 存储引擎

画图

一条SQL更新语句是如何执行的？

更新流程也会走查询的那一套流程

更新流程图

但是还设计两个重要的日志模块

InnoDB存储引擎层：redo log

更新完后，不立即刷回磁盘，先记录一个 redo log 日志。

先写日志，再写磁盘

WAL（Write-Ahead Logging）

redo log 的大小是固定的；由4组文件组成，每个文件1Gb，一共 4 Gb，循环写。

redo log 可以做到崩溃恢复

Mysql Server 层：binlog

binlog 用于归档。

redo log 和 binlog 对比

1.redo log 是 InnoDB 独有的；binlog 是 Mysql Server 层实现的，所有引擎都可以使用。

2. redo log 是物理日志；binlog 是逻辑日志

3. redo log 循环写；binlog 是追加写的，binlog 日志文件满了之后会写入新的文件。

undolog？

update 语句更新流程

流程图

两阶段提交

写完 redo log -> prepare 阶段

写完 binlog -> commit 阶段

需要注意的是，一条update 语句是先写 redolog 日志，再写 binlog 日志的

所以 binlog 日志没有崩溃恢复的能力

一句话解释 redo log 和 binlog：

redo log 记录的，即使异常重启，都会刷新到磁盘；
binlog 记录的，主要用于备份。

mysql的binlog redolog undolog含义？

二进制日志： binlog 日志

作用：

主从复制

归档

特点：

Mysql Server 生成，与具体存储引擎无关

binlog 主要用于主从复制和数据恢复

刷盘时机：在事物提交后写回磁盘，当文件写满后再写入新文件中。

binlog 日志的三种日志格式：

STATMENT

ROW

使用阿里 canal 监听 binlog 日志做数据监听

MIXED

事物日志： redolog 日志

作用：

崩溃恢复

为什么要有 redolog 日志？

redolog 日志是 InnoDB 存储引擎特有的，InnoDB 一个特点就是可以保证事物性，其中事物的特性之一 持久性 ，就是通过 redolog 日志来实现的。

redolog 就是在事物提交前记录的信息，因为 Mysql 是以页为单位从磁盘读取写入文件的，如果每次都修改了页中的数据都写会磁盘，那性能太差了。因为 Mysql 搞了一个缓冲区，叫 BufferPool ，把修改的页先写到 BufferPool，等等待合适时机再写回磁盘。这样减少了与磁盘的交互，会大大提高效率。

但是也带来一个问题，如果事物提交后，Mysql 崩溃，BufferPool 中的数据还未刷回磁盘，那等下次 Mysql 恢复重启，前面提交的事物就会失败。

为解决这个问题， InnoDB 设计了 redolog 日志。

那么问题来了？ redolog 怎会写回磁盘？

图

WAL：

先写日志，再写磁盘的技术就是 MySQL
里经常说到的 WAL(Write-Ahead Logging) 技术。

BufferPool -》 LogPool -》 OSPool -》 fsync() 刷回磁盘

三种刷回时机

实时刷回

每秒刷回

由 OS 选择刷回时机

特点：

InnoDB 崩溃如何恢复？

InnoDB 重启时，会先检查磁盘中的 LSN（逻辑序列号） ，如果小于日志中的 LSN ，就会从 checkpoint 开始恢复数据。

事物日志： undolog 日志

作用：

事物回滚的时候使用

特点：

原子性底层就是通过 undo log 实现的。 undo log 主要记录了数据的逻辑变化，比如一条 ` INSERT
语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的
undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 MVCC `
(多版本并发控制)实现的关键

为什么 InnoDB 使用可重复读作为事务默认隔离级别？

InnoDB 在可重复读（REPEATABLE READ）的级别就解决了幻读的问题

MVCC

简单来说就是通过维护数据历史版本，从而解决并发访问情况下的读一致性问题。

MVCC的意思用简单的话讲就是对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。这样读某一个数据时，事务可以根据隔离级别选择要读取哪个版本的数据。过程中完全不需要加锁。

MVCC 存在的意义：

MVCC 是行级锁的一个变种，读时避免了加锁操作（读取版本链），写的时候只对必要的行加锁。

原理：

每行记录都有两个隐藏列

trx_id

每次修改的时候都会把事物 id 写到这个事物 ID隐藏列

roll_pointer

每次修改会把 undo log 日志日志写到这个回滚隐藏列

这样，对该条记录的修改，就能用链表串起来形成一个 版本链，版本链 的头结点就是当前这条记录最新的值。

ReadView

作用：

在 读已提交 和 可重复读 的隔离级别下，通过判断版本链中的每一条记录对当前事物是否可见，就能实现这两个隔离级别。