Mysql知识梳理及常见面试题总结

最新推荐文章于 2024-08-16 18:39:05 发布

小小小航

最新推荐文章于 2024-08-16 18:39:05 发布

阅读量213

点赞数

分类专栏：数据库文章标签： Mysql 数据库 InnoDB 索引事务和锁机制

本文链接：https://blog.csdn.net/u014132710/article/details/100587086

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql架构

Mysql架构主要分为Server层和存储引擎层。

Server层：包括连接器、查询缓存、分析器、优化器、执行器等，所有夸存储引擎的功能都在这层实现，比如存储过程，触发器，视图，函数等。
- 连接器：用于身份认证和权限认证
- 查询缓存：用户的查询语句会首先访问查询缓存，如果命中数据，则返回给客户端。
- 分析器：没有命中缓存的话会经过分析器，分析语句的语句功能及语法问题。
- 优化器：Mysql会按照最优的方案去执行语句
- 执行器：从存储引擎返回数据
存储引擎层：主要负责数据的存储和读取，采用可替换的插件式架构。支持MyISAM、Memory、InnoDB等多个存储引擎，其中InnoDB在Mysql5.5版本之后成为了默认的存储引擎。

下图为Mysql基本架构图，

事务的执行过程

首先要明白为什么有事务这个概念，事务是用来干嘛的。

简单来说，事务是逻辑上的一组操作，比如一条sql语句就是一个事务。

事务的特点（面试常考）

原子性(Atomicity)：事务是最小的执行单位，事务包含的全部操作要么不完成，要么全部完成
一致性(Consistency)：事务只会从一个一致性状态变换为另一个一致状态，而不会处于一个中间态。
隔离性(Isolation)：并发访问数据库时，多个事务间不会互相影响
持久性(Durability)：事务被提交后，对于数据库数据的改变是持久的

如果多个事务并发执行，会带来什么问题？

脏读：假设有A和B两个事务，A事务访问了数据并对进行了修改，但是这种修改还没有提交到数据库中，此时B事务访问到的数据是没有提交到数据库里的数据，也就是“脏数据”。
丢失修改：假设有A和B两个事务，A事务访问了数据并进行了修改，紧接着B事务访问了数据也对其进行了修改，那么A事务在这个数据上做出的修改就会丢失。
不可重复读：假设有A和B两个事务，A事务先访问数据，随后B事务访问了该数据并进行了修改，此时A事务再访问时发现两次读到的数据不一致。成为不可重复读
幻读：幻读的概念和不可重复读的概念很像，只不过不可重复读强调对数据的修改，而幻读强调数据的新增/删除。

那出现这样的问题怎么去解决呢？

Sql标准定义了四个事务隔离级别：

读未提交（READ-UNCOMMITTED）：最低的隔离级别，不能解决上面任何问题。
读已提交（READ-COMMITTED）：只解决了脏读的问题
可重复读（REPEATABLE-READ）：解决了脏读和不可重复读的问题。
串行化（SERIALIZABLE）：所有事务排成队挨个儿执行，挤什么挤，没人抢也就不会有问题了。

在Mysql中，默认的隔离级别是可重复读，看似不能解决幻读的问题，其实不然。InnoDB中提供了一个Next-Key Lock算法，可以解决幻读的问题。所以可以最高级别保证事务的隔离性要求。

那怎么才能实现事务的隔离呢？这里就需要用到锁机制了。

锁的分类

表锁：粒度大，性能开销小，不会出现死锁，出现锁冲突的概率比较高
行锁：粒度小，性能开销大，会出现死锁，出现锁冲突的概率比较低
- Record Lock
- Gap Lock
- Next-key Lock
表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）。
- 共享锁（s）
- 共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。
- 共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
- 排他锁（X）：
  
  排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。
悲观锁和乐观锁
- 悲观锁：指的是事务在执行前会对数据加锁，直到事务完成再解锁，虽然保证了数据的独占性，但是加锁需要很大的开销。通常使用在写比较多的场景中
- 乐观锁：指的是事务执行的时候认为只有它自己在操作数据，所以不加锁。在更新的时候会判断下有没有其他事务修改这个数据，采用MVCC和CAS算法实现。适用于读比较多的场景。

索引

为什么会存在索引？

主要原因是因为现在数据量越来愈多，以往的遍历表好费时间太多，得想个聪明的办法儿，所以索引就诞生了。

索引的概念和字典的目录很像，我们在查单词“apple”的时候，会首先去目录中找到a字母对应的页数，之后在找下一个字母对应的页数...直到查到为止。数据库中的索引也是如此，将随机IO变成了顺序IO。

索引为什么会加快查询速度？

首先说一下数据在Mysql中是怎么存的。

在Mysql中，数据都是存放在磁盘中，我们想要查询数据，就会发生磁盘与内存的交互。在每次的交互过程中，磁盘会将一页记录发送给内存，如果是用户想要查询的数据，则呈递给用户，若不是，则用该页的下一页把内存中的当前页置换出来，直到找到数据为止。记录（每行数据）是按照单链表的方式存储在页中的，每个页都会为它里面的记录生成一个页目录。如图2所示：

在查找记录的时候，如果有索引存在，就可以通过页目录使用二分位定位记录的所在页，再遍历该页中的记录就可快速找到指定的记录；如果没有索引存在，那就得一个个去遍历页，再一个个遍历页中的记录，如果表的数据特别大的话，那不知道得遍历到什么时候。

InnoDB中索引的底层数据结构是B+树，那为什么是B+树呢，因为B+树是一个二叉平衡树，查找仅需要O（logN）的时间。

例子:找到id为8的记录简要步骤：

从图中可以发现，通过折半查找法会很快的找到数据。

索引的类型

B+树索引
- 聚集索引：以主键创建的索引，叶子结点存放点的是实际数据
- 非聚集索引：非主键创建的索引，叶子节点存放的是索引列和主键。
  - 在使用非聚集索引查询数据的时候，如果该数据不是索引列上的字段，则会根据主键再去主键索引上查找，也叫作回表。如果是索引列字段，则直接返回数据，这种特殊的非聚集索引叫做覆盖索引。
  - 例子：现在我创建了索引(username,age)，在查询数据的时候：
```
select username , age from user where username = 'Java3y' and age = 20。
```
    此时就会直接返回数据而不需要进行回表操作
哈希索引
- 采用一定的哈希算法，把字段值换算成新的哈希值，检索的时候只需要一次哈希算法就可定位。
- 缺点：在有大量键值重复的情况下，效率极低。这里涉及到了哈希碰撞问题。