MySQL数据库管理系统——理论篇

视觉患者leon

已于 2022-07-14 16:11:06 修改

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：计算机文章标签：数据库 mysql

于 2022-06-29 21:04:21 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_39455125/article/details/125529362

一、MySQL数据库管理系统的逻辑架构

1.执行一条SQL语句，MySQL内部发生了什么？

答：
主要了解MySQL 内部的逻辑架构，知道每个步骤中各个模块的功能。

第一步：建立连接

首先，通过连接器建立客户端和服务端之间的TCP连接。其次，对客户端传来的账号密码进行身份认证和权限获取。

	还要对TCP长连接进行管理，以及为每次客户端连接从线程池中分配一个线程。

第二步：查询缓存（可选）

只针对SQL查询语句，查询语句如果命中查询缓存则直接返回结果，否则继续往下执行。

	key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

第三步：解析SQL语句

通过解析器对 SQL 语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

	词法分析：MySQL 会根据你输入的字符串识别出关键字，知道每个字符串代表什么。
	语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

第四步：执行SQL语句

预处理阶段：通过预处理器，检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
优化阶段：通过优化器，确定SQL执行路径，如选择最佳索引方式。
执行阶段：通过执行器，根据执行计划真正执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

	执行器和存储引擎的具体交互过程（省略）

总结：

在这里插入图片描述

MySQL 的架构共分为两层：Server 层和存储引擎层。（以下可以不记）

1.Server 层负责建立连接、分析和执行 SQL：
MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。
另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

2.存储引擎层负责数据的存储和提取：
支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

二、事务

1.什么是事务？

答：
事务就是一个完整的业务逻辑，由多个SQL语句组成一个不可再分的逻辑操作单元，是一个最小的工作单元。
如转账业务，对数据库的所有操作是不可分割的，要么全部执行成功，要么全部失败。

2.事务有哪些特性？

答：

	原子性：针对某个事物的操作，要么全部成功，要么全部失败，不可再分。可以通过回滚日志保证。
	隔离性：针对多个事务并发操作同一数据，需要保证各个事务互不干扰。具有四个隔离级别，通过MCVV和锁机制来保证。
	持久性：事务一旦提交，对数据的改变是永久的。通过重做日志来保证。
	一致性：针对多个事务并发操作同一数据，所有事务完成后，数据是合法的。事务的一致性通过原子性、隔离性、持久性一块来保证。


重做日志redo log：
当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。
这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

在这里插入图片描述

3.并发事务会带来什么问题？

答：
MySQL 服务端是允许多个客户端连接的， MySQL 会同时处理多个事务。并发事务访问相同记录会有以下情况

1）读—读情况：并发事务，读取相同数据。

		读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

2）写—写情况：并发事务，对相同数据做出改动

在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。
所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁 来实现的。

	比如，事务 A 要对这条记录做改动，就需要生成一个 锁结构与之关联。如果此时事务B也想对同一条记录做出改动，也必须生成一个锁。
	但是这条记录已经被事务A加锁成功了，所以事务B只能等待。因此事务A和事务B是排队执行的，是串行的。	

获取锁成功，或者加锁成功：意思就是在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作。
获取锁失败，或者加锁失败，或者没有获取到锁：意思就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务
需要等待，不可以继续执行操作。

3）读----写情况：一个事务进行读取操作，另一个进行改动操作。

	对于并发事务A和B：
	脏读：对于两个并发事务A和B，事务A读取到了事务B还未提交的数据。
	不可重复读：对于两个并发事务A和B，事务A前后读取的数据不一致（因为事务B在此期间改变了数据并提交，事务A前后读取的数据都是对的）
	幻读：对于两个并发事务A和B，事务A前后查询的记录数量不一致（因为事务B在此期间改插入了新的记录）

	幻读是比不可重复读更难处理的一个级别错误，因此需要更高的隔离级别来处理。

不可重复读和幻读的区别：
不可重复读是因为在读的时候没有禁止写操作，两次读取同一条数据的时候结果不一致，主要针对的是update或delete。
幻读是因为在读取多条数据时，虽然此时读操作禁止了写操作，所以update和delete都是不行的，但是由于未知行的不存在，无法加锁，所以insert语句还是可以在读事务中间执行，所以可能出现幻读。
不可重复读和幻读的危害：

不可重复读和幻读的区别：

幻读侧重行数量发生了变化。不可重复读侧重某一行数据发生了变化。
不可重复读，广义上讲，第一次读和第二次读数据不一样，这包含两种情况：
(1) 同一行数据发生了变化；
(2) 行数量发生了变化；
为了区分来这两种情况，就把后者单独叫“幻读”。而不可重复读通常指的就是第一种情况。

4.如何解决并发事务带来的问题？（事务的隔离级别有哪些？）

答：
SQL 标准提出了四种隔离级别来规避这些现象。
在这里插入图片描述

要解决脏读现象，就要升级到「读已提交」以上的隔离级别；
要解决不可重复读现象，就要升级到「可重复读」的隔离级别。

要解决幻读现象不建议将隔离级别升级到「串行化」，因为这样会导致数据库在并发事务时性能很差。

InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。因此InnoDB 引擎可重复读的隔离级别解决了幻读问题。

next-key lock 锁（行锁和间隙锁的组合）会产生死锁现象。

死锁：就是两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
避免死锁有两种方法：
		设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
		开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

总结：

读未提交read uncommitted：该隔离级别下，事务A可以读取到事务B未提交的数据。因此存在脏读、不可重复读、幻读问题。
读已提交read committed：该隔离级别下，事务A只能读取到事务B提交之后的数据。因此不存在脏读问题，但存在不可重复读和幻读问题。
											比如事务B修改数据或者删除、插入新的行数据后，事务A可以读取到修改后的数据，增加或者减少的行数量。
可重复读repeatable read：该隔离级别下，事务A开启之后，即便事务B修改update、删除delete数据并提交，事务A读取的仍然是原来数据，没有发生改变。
											但是，如果并发事务B插入新的数据，事务A会读取到新的行记录。因此只解决了脏读和不可重复读问题，幻读仍在存在。
穿行化s