Mysql学习第一讲基础

1、一条sql查询是如何执行的

1、连接器

建立连接，获取权限

1. 长连接：连接建立后，持续有请求发出，使用同一连接 ，长连接积累过多可能会导致内存使用过大，导致OOM ，会被系统强行杀死
2. 短连接：连接建立后，几次查询后，连接自动断开，断开后需要重新建立连接后才能访问，建立连接过程复杂消耗性能
3. 空闲连接：连接建立后，没有后续动作，可以通过wait_timeout控制空闲连接存在时间默认8小时（超过8小时就断开连接接），查看show processlist (sleep表示空闲)

mysql5.7后，一次大查询后，可以通mysql_reset_connection将连接恢复到连接刚建立状态，无需重新建立连接，验证权限

2、查询缓存

之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果
如果能够直接在缓存中找到 key，那么 value 就会被直接返回给客户端

如果直接命中缓存就直接返回（其实返回的时候还要进行权限验证），效率高
但查询缓存的弊大于利： 因为表只要一更行，缓存就会被清空

---

设置query_cache_type 设置为 DEMAND
此时我们可以按需使用 缓存查询

使用 SQL_CACHE 显示指定把语句和结果缓存起来

select SQL_CACHE * from t;

mysql 8.0+版本删除了查询缓存功能，彻底没有了

3、分析器

两点作用：

1. 词法分析
   • 分析字段（列）是否存在
   • 关键字识别出来，如select
   • 字符串“ID”识别成“列 ID”，T 识别为 表T
2. 语法分析
   • 语法分析器会根据语法规则，判断 SQL 语句是否满足 MySQL 语法

如果语句不对，报错误：You have an error in your SQL syntax
注意点：此阶段会做权限验证precheck，而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况

4、优化器

当多索引和多表(join)查询时，优化器会决定连接顺序，优化选择效率高的方案

5、执行器

执行语句

• 判断对这个表是否有权限，如果有进入下一个阶段
• 根据表的引擎定义去使用接口

执行过程：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断是否满足条件，如果满足则将这行存到结果集中；
2. 调用引擎接口取“下一行”，相同操作，直到取到这个表的最后一行。
3. 执行器将所有满足条件的行组成的记录集作为结果集返回给客户端。

慢查询日志中有 rows_examined字段：表示这个语句执行过程中扫描了多少行。

在某些情况下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

练习：

1. MySQL的框架有几个组件, 各是什么作用?

2. Server层和存储引擎层各是什么作用?

3. you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?

分析器阶段

4. 对于表的操作权限验证在哪里进行?

执行器，分析器

5. 执行器的执行查询语句的流程是什么样的?

6. “Unknown column ‘k’ in ‘where clause’ 当报列不存在错误时，这个错是在哪个阶段报的？

分析器阶段

---

2、一条更新语句是如何运行的

一旦更新，和这个表有关的查询缓存都会被清空

更新过程：

1. 连接器 建立连接
2. 分析器 通过词法和语法分析 该更新语句
3. 优化器 决定使用哪个索引
4. 执行器 负责执行，找到对应行，更新数据

这些操作和查询语句很像，除了上面的操作，更新语句还涉及两个重要的日志模块：redo log（重做日志），binlog（归档日志）

redo log（重做日志）

redo log 是引擎层日志

WAL 技术：全称是 Write-Ahead Logging，先写日志，再写磁盘

InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新才算完成了。

InnoDB 引擎会在系统比较空闲的时候，将这个操作记录更新到磁盘里面

redo log 是固定大小的，比如说配置了一组4个文件，一个文件 1G，那么redo log就是一个 4个G的环：

这个环上有两个指针：

1. write pos：写指针
2. check point：擦除指针

当有更新sql语句时write pos就会顺时针移动，当更新语句执行并写入磁盘后，check point就会顺时针移动。

两个指针之间绿色区域就是 用来记录新的操作，当write pos 追上check point 时，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

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

---

binlog（归档日志）

binlog 是Server层日志

binlog 用于归档，没有crash-safe

两种日志有以下三种不同：

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

执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程：

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

---

执行流程：
浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的:

最后三步是：将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再加上binlog，这就是"两阶段提交"。

两阶段提交

采用这种 ABA 形式可以防止数据丢失，（AB 或者 BA 都可能丢失数据）

答： Bin log 用于记录了完整的逻辑记录，所有的逻辑记录在 bin log 里都能找到，所以在备份恢复时，是以 bin log 为基础，通过其记录的完整逻辑操作，备份出一个和原库完整的数据。

在两阶段提交时，若 redo log 写入成功，bin log 写入失败，则后续通过 bin log 恢复时，恢复的数据将会缺失一部分。(如 redo log 执行了 update t set status = 1，此时原库的数据 status 已更新为 1，而 bin log 写入失败，没有记录这一操作，后续备份恢复时，其 status = 0，导致数据不一致）。

若先写入 bin log，当 bin log 写入成功，而 redo log 写入失败时，原库中的 status 仍然是 0 ，但是当通过 bin log 恢复时，其记录的操作是 set status = 1，也会导致数据不一致。

其核心就是， redo log 记录的，即使异常重启，都会刷新到磁盘，而 bin log 记录的， 则主要用于备份。

回复 补充: 几乎全对，除了这个“两阶段提交时，若redo log写入成功，但binlog写入失败…”这句话。

实际上，因为是两阶段提交，这时候redolog只是完成了prepare, 而binlog又失败，那么事务本身会回滚，所以这个库里面status的值是0。

如果通过binlog 恢复出一个库，status值也是0。

这样不算丢失，这样是合理的结果。

两阶段就是保证一致性用的。
你不用担心日志写错，那样就是bug了…

---

关键问题：2PC的前两步成功，第三部失败怎么办？
即：
步骤1.InnoDB执行事务prepare操作成功；
步骤2.Server写binlog成功；步骤第三部commit失败的情况。假设MySQL第三步前宕机，重启后MySQL首先扫描binlog文件，收集最近写入的xid【事务ID，redo log记录里也会有该信息】；然后会扫描redo log里有哪些未提交的事务【即prepare状态的事务】，如果事务写入binlog，那么执行commit；如果没有写入binlog【即事务ID在binlog中查找不到】，那么回滚该事务。这样就可以保证两者的一致性了。

3、事务隔离

当数据库有多个事务执行时，可能出现 脏读、不可重复读、幻读

事务一共有四种隔离级别：

1. 读未提交（read uncommitted）：其它事务 修改数据提交前，本事务就能读取到新数据
2. 读已提交（read committed）：其它事务 修改数据提交后，本事务才能读取到新数据
3. 可重复读（repeatable read）：其它事务 修改数据 提交或者没有提交，本事务读取到数据都不会改变，该数据就是事务开始时的值，事务在执行期间看到的数据前后必须是一致的。
4. 串行化（serializable ）：只能存在一个事务，一个事务不提交，其它事务不能开始

我们用下面这张图解释四种隔离级别

读未提交：
v1 = 2 ， v2 = 2 ， v3 = 2

读已提交：
v1 = 1 ， v2 = 2 ，v3 = 2

可重复度：
v1 = 1 ， v2 = 1 ，v3 = 2

串行化：
v1 = 1 ， v2 = 1 ，v3 = 2
若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

Mysql 数据库 默认隔离级别：可重复读
Oracle 数据库 默认隔离级别：读提交

更改隔离级别：
可以把 transaction-isolation 的值设置成 READ-COMMITTED

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

---

事务隔离的实现

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录：
（回滚日志会记录 如何 把当前值改回 1）

即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。
因为 回滚日志会加上： 将 5 改为 4.

长事务存在的风险：

建议尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，这个事务提交之前，回滚记录都必须保留，导致大量占用存储空间

---

事务的启动方式

MySQL 的事务启动方式有以下几种：

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。
其实set autocommit=0 和上面效果一样

如何用正确的方式避免长事务：
因为有时候有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令、一开始时就关闭了自动提交，从而导致了长事务，而 set autocommit=1, 通过显式语句的方式来开启自动提交。

---

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

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

上面的sql 用于查找持续时间超过 60s 的事务

思考

如果你是业务开发负责人同时也是数据库负责人，你会有什么方案来避免出现或者处理这种情况呢？