MySQL基础-基础篇

Aomrsou

已于 2022-02-23 10:06:24 修改

阅读量1.5k

点赞数 1

分类专栏：基础数据库文章标签： mysql 数据库

于 2022-02-06 10:33:49 首次发布

本文链接：https://blog.csdn.net/qq_41079359/article/details/122739952

版权

基础同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

MySQL基础

1.一条查询语句的执行过程：基础架构

在这里插入图片描述
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

不同的存储引擎共用一个 Server 层

连接器

通过mysql -h$ip -P$port -u$user -p输入账号密码连接到MySQL以后，会去权限表查询当前用户的权限，权限也就在此刻确定下来了，如果发生更改，则在下一次登录生效。

mysql > show variables like 'wait_timeout';可以看到最长挂机等待时间，默认是8小时（28800s），超过之后还没动作就会被踢下线。

建立连接的过程比较复杂耗时，所以可以保持几个长连接（用数据库连接池）来节省时间。但是同时有一些数据是绑定在连接对象上的，只有连接断开的时候才会释放，所以一直持有的话内存会OOM，从现象看就是 MySQL 异常重启了。

解决方案1：定期断开连接，或检测到有占内存大的查询后断开，用的时候再重连
解决方案2：5.7及以上版本，执行mysql_reset_connection() 来重新初始化连接资源。该过程无需重连和重新权限验证，会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL拿到一个select请求后会先去缓存看，之前执行过的会以k-v形式存在缓存中，如果命中直接返回。

但条件比较苛刻，需要语句参数完全一致，且表更新后缓存就会失效，虽然可以设置按需开启（将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存），MySQL8直接取消了查询缓存。

分析器

没有命中缓存后，就开始分析。
分析器干两件事：1. 解析语句，生成解析树 2. 检查语句中的关键词，表，字段是否存在。
词法分析：MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。表名和列名在词法分析阶段就确定了。
语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。这些表和列是否存在等。

优化器

此时优化器知道了你要做什么，一条 SQL 语句可能有不同的执行逻辑（或者顺执行顺序），对你的语句（比如两表join）选择一个最优解执行方案（尽可能少的扫描表）去执行。

优化器会改写sql，包括join的连接顺序，匹配索引（可能有多种索引），找到最优sql策略。

执行器

判断有无权限（命中缓存时也会判断）
有权限则打开表，根据引擎定义调用引擎API
- 无索引：
  - 调用引擎API取该表第一行，判断ID是否为10，不是则跳过，是的话存到结果集
  - 调用引擎API取“下一行”，直到该表最后一行（所以全表扫描是执行器做的）
  - 将遍历过程中所有满足条件的行作为结果集返回
- 有索引：
  - 第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

2.一条更新语句的执行过程：日志系统

像查询一样，经过连接器以后，走到分析器，发现这是一条update语句，然后优化，执行。
不同之处是，更新语句需要操作两个日志系统。
redo log（重做日志）和 binlog（归档日志）

redo log（InnoDB引擎特有）

类似文件缓冲的存在。

使用场景：每次更新数据库都要对磁盘进行操作，效率太低。有redo log后，将更新语句先写到日志中，再挑个时间一次性写到磁盘里。这就是WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

大小限制：InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块缓冲总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

它的作用：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog（Server层特有，所有引擎均可使用）

异同点：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行流程图

在这里插入图片描述

两阶段提交

“两阶段提交” 是指上图对 redo log 进行“两阶段提交”：先 prepare，再commit。
数据库 crash-重启后，会对记录对redo log 进行check：

1、如果 redo log 已经commit，则视为有效。
2、如果 redo log prepare 但未commit，则check对应的bin log记录是否记录成功。
- 2.1、bin log记录成功则将该prepare状态的redo log视为有效
- 2.2、bin log记录成功则将该prepare状态的redo log视为无效

这么做之所以可以保证数据一致，是由于两种日志各司其职，redo log负责“事务”，binlog负责归档，binlog记录成功则表明“事务”已经完成只是未曾提交，所以可以认作redo log的“事务”有效。

3.事务的隔离性

在MySQL中，事务是在引擎层实现的。而MyISAM不支持事务，也是被InnoDB替代的原因。
事务具有ACID（Atomicity、Consistency、Isolation、Durability），即原子性、一致性、隔离性、持久性。
MySQL默认的隔离级别是READ-COMMITTED。

mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name 		| Value			 |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

隔离级别

读未提交（Read Uncommitted）：别人改数据的事务尚未提交，我在我的事务中也能读到。
读提交（Read Committed）：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读（Repeatable Read）：别人改数据的事务已经提交，我在我的事务中也读不到（事务在执行期间看到的数据前后必须是一致的）。
串行化（Serializable ）：我的事务尚未提交，别人就别想改数据。

隔离级别的实现是在，数据库执行事务的时候创建一个视图（read-view），访问的时候以视图的逻辑结果为准。
RU：没有视图的概念，直接返回最小行数据。
RC：在每一行SQL语句执行的时候创建。一个事务是可以看到另外一个事务已经提交的内容，因为它在每一次查询之前都会重新给予最新的数据创建一个新的MVCC视图。
RR：在事务启动的时候创建。 MVCC视图实在开始事务的时候就创建好了，这个视图会一直使用，直到该事务结束。
Serial：没有视图的概念，通过锁来实现数据访问。

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
正常操作记录在redo log，回滚操作记录在undo log。

多版本并发控制（MVCC）：同一条数据会存在多个版本，每个事务开启的视图都是一个新的版本，这些视图同时存在时，可以对同一个数据在不同事务中进行修改而互不影响。

undo log何时删除：回滚日志存在的原因是，需要保证事务执行前后数据的一致性，如果早上开启了一个长事务A，之后又进行了若干短事务BCD，BCD进行commit，若一直到下午长事务A仍然没有commit，此时BCD的undo log就不会删除，因为要保证数据完整。所以结论是，就是当系统里没有比这个回滚日志更早的 read-view 的时候。

长事务会带来哪些问题？

占有锁资源，可能拖垮整个库
长事务可能导致暂时需要保存很多undo log，会占用内存空间。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务。

事务的启动方式

显式启动事务语句，begin或者start transaction,提交commit，回滚rollback
set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。

建议使用显式启动，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。
这样省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

4.事务与隔离与锁

前提1：可重复读级别下，每次开一个新的事务都会生成一个全库“快照”，别的修改对它不可见。
前提2：两个事务运行期间，如果update同一行数据，会有行锁的存在，没有拿到锁的一方会处于等待。
问题出现：没有拿到锁的一方，在拿到锁之后，看到的那一行数据是怎样的。

视图的概念

一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view 视图名 as…，这个是有物理结构的。
另外一个是InnovationDB在实现MVCC时用到的一致性视图，即consistent read view，用于支持read committed(RC 读提交)和RR（repeatable read，可重复读）隔离级别的实现。

“快照”在 MVCC 里是怎么工作的？

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

如果有100G数据，需要备份100G吗？其实不然，“快照”只是逻辑上存在，因为undo log记录了每一个事务的更新回滚操作，所以需要拿到旧版的数据时，就通过回滚计算拿到对应旧版的值。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

一个记录被连续更新的样子：
在这里插入图片描述
图中的三个虚线箭头，就是 undo log；
而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。
比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

一致性视图

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位
当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位

判断事务开启条件的一种是begin 并且执行第一个'操作'InnoDB 表的语句，
所以我第一句只执行非select的dml语句就会生成事务id，
但是并不会生成一致性视图，
在我要执行select的时候 其它事物可能已经创建。

所以对于视图数组就会出现比当前事务id还大并且没有提交的事物，
同理也会出现比当前事务还小且没有提交的事物。
所以假如当前事务id为88，活跃数组就可能有[72,79,88,90,91]
不连续的原因也就是因为在生成一致性视图的时候中间短事务早就提交了

事务 A 是以 begin/start transaction 的方式启动，所以启动的时候没有trx_id，
之后事务BCDEF创建，并且获得trx_id，A执行语句获得trx_id，这个值大于bcdef的，
高水位线比A.trx_id大1.

在这里插入图片描述
对于当前事务来说，已提交的事务是可见的，未开始的事务是不可见的。
黄色区间是包含了已经提交的事务的，已经提交的事务的id可以比最低水位大，但小于最高水位。
比如，有5，6，7，8，9这5个事务，9是当前事务，5，6，8是正在执行中的事务，7是已经提交的事务id。那么当前视图数组是[5 6 8 9]，最低水位是5，最高水位是10。事务7落在落在黄色区间，不在数组中，但已提交，所以也可见。