Mysql

潇洒大舅哥

已于 2023-04-24 14:36:49 修改

阅读量460

点赞数

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

于 2023-04-23 16:56:20 首次发布

本文链接：https://blog.csdn.net/weixin_44989660/article/details/130319991

版权

面试专栏收录该内容

3 篇文章 0 订阅

订阅专栏

事务隔离级别？分别解释下含义？

脏读：一个事务读取到了另一个事务未提交的数据
不可重复读：在一个事务中，多次读取同一条数据时，结果出现了不一致的情况
幻读：在一个事务中使用同一个sql查询，第二次读取到了其他事务新插入的行

*幻读侧重于数据的插入；不可重复读注重于数据的修改

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	存在	存在	存在
Read Committed	不存在	存在	存在
Repeatable Read	不存在	不存在	存在
Serializable	不存在	不存在	不存在

Mysql的可重复读是如何实现的？解释下MVCC？

Mysql使用MVCC（多版本控制）来实现数据的可重复读。并且MVCC只作用于RC（Read Committed）和 RR（Repeatable Read）级别，而RU（Read Uncommitted）总是读取最新的数据版本，而不是符合当前事务版本的数据行

InnoDB模式下，在每个行记录后面会有两个隐藏的列，分别保存数据行的事务id和回滚指针。

事务id(DB_TRX_ID)：记录插入或更新改行的事务的事务id

回滚指针(DB_ROLL_PTR)：指针指向undo log记录。每次对某条记录进行修改时，该列会存一个指针，可以通过这个指针找到该记录修改前的信息。当某条记录被多次修改时，该行记录会存在多个版本，通过DB_ROLL_PTR链接形成一个类似版本链的概念。

以RR(可重复读)为例，每开启一个事务时，系统为其分配事务id，在该事物执行第一个select语句的时候，会生成一个当前时间点的事务快照ReadView。主要包含以下属性：

trx_ids：生成ReadView时当前系统中活跃的事务id列表（代表未执行事务提交的事务）
up_limit_id：低水位，取trx_ids中最小的那个，trx_id小于该值都能看到
low_limit_id：高水位，生成ReadView时系统将要分配给下一个事务的id值，trx_id大于等于该值都不能看到
creator_trx_id：生成该ReadView的事务的事务id。

所以有了ReadView，这样访问某条记录时，只需按照下面的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id与ReadView中的creator_trx_id值相同，意味着当前事务在访问自己修改过的记录，所以该版本可以被当前事务访问
如果被访问版本的trx_id小于ReadView中up_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
如果被访问版本的trx_id大于ReadView中low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断trx_id属性值是不是在trx_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问，如果不在，说明创建ReadView时生成该版本的事务已经提交，那么该版本可以被当前事务访问

在进行判断时，首先会拿记录的最新版本来比较，如果该版本无法被当前事务看到，则通过记录的DB_ROLL_PTR找到上一个版本，重新比较，直到找到一个能够被当前事务看到的版本。

以上内容是对于 RR 级别来说，而对于 RC 级别，其实整个过程几乎一样，唯一不同的是生成 ReadView 的时机，RR 级别只在事务开始时生成一次，之后一直使用该 ReadView。而 RC 级别则在每次 select 时，都会生成一个 ReadView

MVCC是否能够解决幻读？

不能。

快照读：生成一个事务快照（ReadView），之后都从这个快照获取数据。普通的select就是快照读。天然解决了幻读
当前读：读取数据的最新版本，常见update、insert、delete、select...for update、select...lock in share mode。需要使用Gap Lock 或Next-Key Lock（Gap Lock + Record Lock）来解决

Mysql默认RR（Repeatable Read）级别，Gap Lock默认开启的

常见的索引类型？

hash：底层就是 hash 表。进行查找时，根据 key 调用hash 函数获得对应的 hashcode，根据 hashcode 找到对应的数据行地址，根据地址拿到对应的数据。
B树：B树是一种多路搜索树，n 路搜索树代表每个节点最多有 n 个子节点。每个节点存储 key + 指向下一层节点的指针+ 指向 key 数据记录的地址。查找时，从根结点向下进行查找，直到找到对应的key。
B+树：B+树是b树的变种，主要区别在于：B+树的非叶子节点只存储 key + 指向下一层节点的指针。另外，B+树的叶子节点之间通过指针来连接，构成一个有序链表，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。

为什么用B+树存储索引？而不用红黑树、Hash、B树？

红黑树：如果在内存中，红黑树的查找效率比B树更高，但是涉及到磁盘操作，B树就更优了。因为红黑树是二叉树，数据量大时树的层数很高，从树的根结点向下寻找的过程，每读1个节点，都相当于一次IO操作，因此红黑树的I/O操作会比B树多的多。
hash 索引：如果只查询单个值的话，hash 索引的效率非常高。但是 hash 索引有几个问题：1）不支持范围查询；2）不支持索引值的排序操作；3）不支持联合索引的最左匹配规则。
B树索引：B树索相比于B+树，在进行范围查询时，需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进行额外的磁盘I/O操作；另外，B树的非叶子节点存放了数据记录的地址，会导致存放的节点更少，树的层数变高。

MySQL 中的索引叶子节点存放的是什么？

MyISAM和InnoDB都是采用的B+树作为索引结构，但是叶子节点的存储上有些不同。
MyISAM：主键索引和辅助索引（普通索引）的叶子节点都是存放 key 和 key 对应数据行的地址。在MyISAM 中，主键索引和辅助索引没有任何区别。
InnoDB：主键索引存放的是 key 和 key 对应的数据行。辅助索引存放的是 key 和 key 对应的主键值。因此在使用辅助索引时，通常需要检索两次索引，首先检索辅助索引获得主键值，然后用主键值到主键索引中检索获得记录。

聚簇索引（聚集索引）？

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引将索引和数据行放到了一块，找到索引也就找到了数据。因为无需进行回表操作，所以效率很高。
InnoDB 中必然会有，且只会有一个聚簇索引。通常是主键，如果没有主键，则优先选择非空的唯一索引，如果唯一索引也没有，则会创建一个隐藏的row_id 作为聚簇索引。至于为啥会只有一个聚簇索引，其实很简单，因为我们的数据只会存储一份。
而非聚簇索引则将数据存储和索引分开，找到索引后，需要通过对应的地址找到对应的数据行。MyISAM 的索引方式就是非聚簇索引。

回表查询？

InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。

而对于普通索引，叶子节点存储的是 key + 主键值，因此需要再走一次主键索引，通过主键索引找到行记录，这就是所谓的回表查询，先定位主键值，再定位行记录。

联合索引（复合索引）的底层实现？最佳左前缀原则？

联合索引底层还是使用B+树索引，并且还是只有一棵树，只是此时的排序会：首先按照第一个索引排序，在第一个索引相同的情况下，再按第二个索引排序，依次类推。

这也是为什么有“最佳左前缀原则”的原因，因为右边（后面）的索引都是在左边（前面）的索引排序的基础上进行排序的，如果没有左边的索引，单独看右边的索引，其实是无序的。

还是以字典为例，我们如果要查第2个字母为 k 的，通过目录是无法快速找的，因为首字母 A - Z 里面都可能包含第2个字母为 k 的。

InnoDB 和 MyISAM 的区别？

对比项	InnoDB	MyIsam
事务	支持	不支持
锁类型	行锁、表锁	表锁
缓存	缓存索引和数据	只缓存索引
主键	必须有，用于实现聚簇索引	可以没有
索引	B+树，主键是聚簇索引	B+树，非聚簇索引
select count(*) from table	较慢，扫描全表	贼快，用一个变量保存了表的行数，只需读出该变量即可
hash索引	支持	不支持
记录存储顺序	按主键大小有序插入	按记录插入顺序保存
外键	支持	不支持
全文索引	5.7 支持	支持
关注点	事务	性能

explain 用过吗，有哪些字段分别是啥意思？

explain 字段有：

id：标识符
select_type：查询的类型
table：输出结果集的表
partitions：匹配的分区
type：表的连接类型
possible_keys：查询时，可能使用的索引
key：实际使用的索引
key_len：使用的索引字段的长度
ref：列与索引的比较
rows：估计要检查的行数
filtered：按表条件过滤的行百分比
Extra：附加信息

type 中有哪些常见的值？

按类型排序，从好到坏，常见的有：const > eq_ref > ref > range > index > ALL。

const：通过主键或唯一键查询，并且结果只有1行（也就是用等号查询）。因为仅有一行，所以优化器的其余部分可以将这一行中的列值视为常量。
eq_ref：通常出现于两表关联查询时，使用主键或者非空唯一键关联，并且查询条件不是主键或唯一键的等号查询。
ref：通过普通索引查询，并且使用的等号查询。
range：索引的范围查找（>=、<、in 等）。
index：全索引扫描。
All：全表扫描

如何做慢 SQL 优化？

首先要搞明白慢的原因是什么：是查询条件没有命中索引？还是 load 了不需要的数据列？还是数据量太大？所以优化也是针对这三个方向来的。

首先用 explain 分析语句的执行计划，查看使用索引的情况，是不是查询没走索引，如果可以加索引解决，优先采用加索引解决。
分析语句，看看是否存在一些导致索引失效的用法，是否 load 了额外的数据，是否加载了许多结果中并不需要的列，对语句进行分析以及重写。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分。

MySQL 的主从复制？

MySQL主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个（I/O Thread，SQL Thread）运行在从节点，如下图所示

主从复制默认是异步的模式，具体过程如下。

1）从节点上的I/O 线程连接主节点，并请求从指定日志文件（bin log file）的指定位置（bin log position，或者从最开始的日志）之后的日志内容；
2）主节点接收到来自从节点的 I/O请求后，读取指定文件的指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 bin-log file 以及 bin-log position；从节点的 I/O 进程接收到内容后，将接收到的日志内容更新到 relay log 中，并将读取到的 bin log file（文件名）和position（位置）保存到 master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master “我需要从某个bin-log 的哪个位置开始往后的日志内容”；
3）从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会解析 relay-log 的内容，并在本数据库中执行。