Mysql该准备哪些知识？

最新推荐文章于 2023-01-24 17:32:56 发布

伞_

最新推荐文章于 2023-01-24 17:32:56 发布

阅读量137

点赞数

分类专栏： java高级面试梳理

本文链接：https://blog.csdn.net/qq_16773855/article/details/115441351

版权

java高级面试梳理专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Mysql是最常用的关系型数据库。
Mysql的官方文档，和社区资料非常的完备。
此文从个人角度，只做简单的梳理

Mysql该准备哪些知识？

索引、事务、锁、MVCC、sql优化

索引

了解mysql的索引类型： B+索引、hash索引

B+索引

b+索引的核心结构是，B+树。
B+树: 平衡多路查找树+有序链表。

（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
（3）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
（4）非叶子节点的子节点数=关键字数（来源百度百科）（根据各种资料这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1（来源维基百科)，虽然他们数据排列结构不一样，但其原理还是一样的Mysql 的B+树是用第一种方式实现）

为什么选择B+树作为索引结构？
因为mysql，数据存储在磁盘，每一个叶子节点对应一个磁盘页。

磁盘页读取特点：

预读（某个存储未知被访问，其附近的也有很大可能会被访问）
顺序读写快，随机读写慢

因此,b+树作为索引结构有以下优点：

1、B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少，io寻址次数少，所以查询数据更快；
2、B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
3、B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
4、B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

主键索引、非主键索引、联合索引、索引合并

主键索引，每张表都会存在一个主键索引，如果没有声明主键，mysql会生成一个隐藏的主键列并创建主键索引。

主键索引
非叶子结点存储的是索引字段，即主键。
叶子节点，存储的是全部数据

非主键索引
非叶子结点存储的是索引字段。
叶子节点，存储的是主键。
所以，非主键索引需要回表（拿到主键，根据主键索引获取字段）。

联合索引（a,b,c) 主键d
非叶子节点，存储的是a
叶子节点存储的是 a,b,c,d。先以a字段排序，a相同时用b排序，以此类推。

不走索引的几种情况

如果条件中有 or ，即使其中有条件带索引也不会命中(这也是为什么尽量少用or的原因）

like查询是以%开头，如果是int型索引不会命中，字符型的命中 ‘test%’ 百分号只有在右边才可以命中

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

没有查询条件，或者查询条件没有建立索引

查询条件中，在索引列上使用函数（ + , - , * , / ）, 这种情况下需建立函数索引

采用 not in, not exist

联合索引联合索引的命中条件是：左前缀匹配原则。

命中多个索引时，mysql的优化：索引合并
当查询条件命中了多个索引，会合并多个索引。索引的合并是在内存中完成的。

索引合并的范围是单个表，对多个索引的筛选结果，做求交集、求并集的处理。
有篇文章讲解的非常详细，外部链接：索引合并Index Merge优化

事务

清晰的掌握事务中的相关概念。
事务的四个特性：

原子性（atomicity）:强调事务的不可分割.
一致性（consistency）:事务的执行的前后数据的完整性保持一致.
隔离性（isolation）:一个事务执行的过程中,不应该受到其他事务的干扰
持久性（durability） :事务一旦结束,数据就持久到数据库

关于一致性的理解，存在很多的误解，个人认为比较有说服性的一个，强烈推荐阅读

事务读写问题：

读问题

脏读：读到了别的事务修改但是没有提交的数据。
不可重复读：一次事务内，同一数据多次读结果不一致（本事务未对其执行写操作）。
幻读：脏读和不可重复读描述的是某条数据。而幻读是从数量上。一次事务内某范围查询的多次查询，数量不一致。

写问题

丢失更新：自己提交的更新操作丢失了。

事务隔离级别：
针对事务可能产生的读写问题，制定的解决原则。

READ_UNCOMMITED
读未提交，即能够读取到没有被提交的数据，所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种，因此很少使用
READ_COMMITED
读已提交，即能够读到那些已经提交的数据，自然能够防止脏读，但是无法限制不可重复读和幻读
REPEATABLE_READ
可重复读取
SERLALIZABLE
串行化，最高的事务隔离级别，不管多少事务，挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务，这样就解决了脏读、不可重复读和幻读的问题了

最常用的事务隔离级别，也是mysql默认的事务隔离级别，可重复读。
如何实现可重复读呢？mysql 使用的是锁和mvcc

锁

mysql的锁，从锁范围上分为表锁、意向锁、行锁。
从事务角度，对表锁的应用很少。 mysql表锁一般被用作数据恢复。

意向锁，分为意向共享锁IS和意向拍他锁IX。是为了解决表锁和行锁的并存性问题。

事务在对行获取锁时，根据获取的行锁类型，会提前获取对于的意向锁，来检测行锁和表锁的冲突。
获取行X锁之前，获取IX意向排他锁。
获取行S锁之前，获取IS意向共享锁。

表锁的兼容情况：

	S	IS	X	IX
S	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容

其具体原因和应用，没有研究。

行锁

行锁细分为：

record锁，真正意义的行锁，锁一行数据。
gap锁，间隙锁。锁一个区间，左开右开。
next-key锁， record+gap，左开右闭。
插入意向锁，一种特殊的间隙锁。

共享锁是不会产生冲突的，独占锁才会产生冲突。行锁的锁冲突情况如下：

	record	Gap	Insert Intention Record	Next-Key
Gap	兼容	兼容	兼容	兼容
Insert Intention	冲突	兼容	兼容	冲突
Record	兼容	兼容	冲突	冲突
Next-Key	兼容	兼容	冲突	冲突

锁的角度谈谈可重复读

如何实现可重复读呢？即，如何保证一个事务内同一行的多次读取，内容一致（不被其他事务修改）。

在一个事务内，读取某行数据时，对其加一个行共享锁，这样别的事务可以读取，但是不可以修改。
存在的问题：
多个事务并发读，互相持有读锁，都不能够申请到写锁

那么如何解决可重复读中的并发读写问题呢。

由此引入一个概念，快照读。

并发场景中，每个事务的读操作，可以使用快照读。
本事务中的修改操作修改的是自己的快照。事务提交时，将修改的结果放到数据库中。

mysql实现快照读的方式是MVCC

MVCC

全称Multi-Version Concurrency Control,即多版本并发控制。为了解决并发场景的读写冲突。
他的核心是，对数据库表增加了两个隐藏列。

一个是创建版本号。
另一个是修改版本号。
(版本号实际是事务id)

mvcc优化后的可重复读

开启事务，无锁读方式，使快照读，查询数据库对于的修改版本号小于当前事务id的数据。将其写入到undo_log中。
事务内，再次查询时，根据当前的事务版本号，到undo_log中获取到当前事务的数据快照。

由于不加锁，所以多个事务间的读写，并发冲突会极大降低。

比如：
事务A，先开启，查询 id = 5 的数据。此时 num = 1；
事务B，后开启，将 id = 5 的数据，num 修改成 2. 并提交
事务A ，再次查询，查询到的数据，是当钱版本号，在undo_log中的快照数据，此时num仍为1 ,证明实现了可重复读。
事务A，执行 update set num=num+1 where id = 5. 此时修改操作申请行锁，并加锁读num的实际值 2，将num 修改成了 2+1=3.
事务A，再次查询 id = 5，结果 num = 3

在此实验中可以发现，事务AB对 id=5，并发读写。保证了可重复读，并且没有产生锁冲突。
引入mvcc后的，可重复读，极大的降低了事务间的并发读写冲突。

关于锁，推荐一篇非常详细的文章：
insert 死锁问题