根据极客时间 MySQL四十五讲 基础篇知识点汇总

认准 林晓斌老师

一、MySQL 基本架构

• 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接
• 查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
• 分析器：分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL需要识别出里面的字符串分别是什么，代表什么。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒
• 优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
• 执行器：通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句，开始执行的时候，要先判断一下你对这个表T 有没有执行查询的权限，如果没有，就会返回没有权限的错误
• 存储引擎： 存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的数据库管理系统支持多种不同的数据引擎，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。
  
  
  

MySQL 的核心就是存储引擎。
提示：InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

---

二、日志（Redo log 和 Binlog）

2.1 重要的日志模块 ----> Redo log

先把赊账的金额记录在粉板上，等不忙了再记录到账本

• 粉板=“redo log”
• 账本=“磁盘”

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

crash-safe

• 赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目

2.2 重要的日志模块:Binlog

• binlog 会记录所有的逻辑操作
• 两阶段式提交

为了让数据在逻辑上一致

1.写入 redo log Prepare 阶段 – > 2.写入 binlog – > 3.Commit 提交

2.3 Redo log 与 Binlog 不同

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

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。


sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证MySQL 异常重启之后 binlog 不丢失。

---

三、 事务隔离

3.1 事务的特性

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

3.2 隔离级别

当数据库同时执行多个事务的时候 避免出现 脏读（dirty read）、不可重复读（non-repeatable read）、**幻读（phantom read）**的问题。

SQL 标准的事务隔离级别：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

1. 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
2. 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
3. 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

Oracle 数据库的默认隔离级别其实就是“读提交”

3.2 事务隔离的实现

每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

举个例子：

如果这样的话，那么回滚日志肯定会很大，不过不用担心，

回滚日志会删除的！

那好 什么时候它会删除呢 ？？？

系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除

那好 什么又是不需要的时候呢？？？

当系统里么有比这个回滚日志更早的 read-view 的时候

注意：尽量不要使用长事务

那好 这又是 为什么呢？？？

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。

3. 4 事务启动方式

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

举个例子：

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

---

四、深入浅出索引(上)

4.1 索引作用及模型

索引的作用：提高数据查询效率
常见索引模型：哈希表、有序数组、搜索树

4.1.1 哈希表

思路：

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

如何解决冲突：

多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

实用场景：

哈希表这种结构适用于只有等值查询的场景

---

4.1.2 有序数组

思路：

有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))

效率：

有序数组查询效率高，更新效率低

适用场景：

序数组索引只适用于静态存储引擎

---

4.1.3 二叉搜索树

思路：
每个节点的左儿子小于父节点，父节点又小于右儿子

时间复杂度：
查询时间复杂度 O(log(N))，更新时间复杂度 O(log(N))

数据库存储大多不适用二叉树，因为树高过高，会适用 N 叉树

注意：
数据库存储大多不适用二叉树，因为树高过高，会适用 N 叉树

4.2 InnoDB 的索引模型

4.2.1 索引类型

主键索引的叶子节点存的是整行数据 主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值 非主键索引也被称为二级索引（secondary index）。

4.2.2 主键索引和普通索引的区别

主键索引只要搜索 ID 这个 B+Tree 即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)

基于非主键索引的查询需要多扫描一棵索引树

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

注意：

• 一个数据页满了，按照 B+Tree 算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概 50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
• 从性能和存储空间方面考量，自增主键往往是更合理的选择。

---

五、 深入浅出索引（下）

5.1 回表

再搜索的过程中 我们在非主键索引查询时，回到主键的过程 称之为 回表

5.2 覆盖索引

如果查询的数据可以直接提供结果，不需要回表，那么称之为 覆盖索引

 例如：select ID from T where k between 3 and 5

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

注意： 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

---

六、 全局锁和表锁

6.1 全局锁

顾名思义，全局锁就是对整个数据库实例加锁

MySQL 提供了一个加全局读锁的方法，命令是

 Flush tables with read lock (FTWRL)

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句（数据的增删改）、
• 数据定义语句（包括建表、修改表结构等）
• 更新类事务的提交语句

使用场景:
全局锁的使用场景典型是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

6.2 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

6.2.1 表锁

表锁的语法是

lock tables … read/write。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。
而对于 InnoDB 这种支持行锁的引擎 一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

6.2.2 MDL：（元数据锁）

MDL 不需要显式使用，在访问一个表的时候会被自动加上

MDL 的作用是，保证读写的正确性。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

6.3 总结

全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，我建议你选择使用–single-transaction 参数，对应用会更友好。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是：

• 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
• 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成
  begin 和 commit，问题就解决了。

MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

---

七、 行锁

7.1 什么是行锁？

行锁就是针对数据表中行记录的锁

解释：

比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

当数据量大的时候 行锁就会造成锁的冲突，这个时候 我们就需要 来降低这个并发冲突

7.2 死锁和死锁检测

7.2.1 死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

7.2.2 死锁检测

首先 我们需要知道这个 ： 死锁检测也是有额外负担的

那我们来举个例子说明一下：

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

怎么解决由这种热点行更新导致的性能问题呢？

• 一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
• 另一个思路是控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

---

八、事务隔离

既然说到事务隔离 那么我们 首先得知道 MySQL是事务隔离的还是 不隔离的 对吧？？？

在 MySQL 里，有两个“视图”的概念：

• 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read
  Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

作用是事务执行期间用来定义 “我能看到什么数据” 。

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

一致性视图：

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况
   a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

所以这就是 为什么
系统里面随后发生的更新 就跟这个事务看到的内容无关了 而是去看版本

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

1. 一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况： 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。

8.2 更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

8.3 总结：

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。

• 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
• 对于读提交，查询只承认在语句启动前就已经提交完成的数据；

而当前读，总是读取已经提交完成的最新版本。

---

基础篇发布完毕 稍后 会更新 实践篇