准备好好学习下 MySQL 了，一直以来对其了解只限于粗浅的使用，买了个《MySQL实战45讲》系统了解下MySQL。也不会特别深入，在全局视野知道MySQL都有哪些东西就好了。

这个系列课程感觉还是有很多干货的，每篇下面的思考题，以及评论区的互动都有学习价值。

开篇词 | 这一次，让我们一起来搞懂MySQL

没啥好说的。

01 | 基础架构：一条SQL查询语句是如何执行的？

  • SELECT 语句的执行过程。

0d2070e8f84c4801adbfa03bda1f98d9

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

    1. 连接器：负责跟客户端建立链接、获取权限、维持和管理链接
    2. 查询缓存：查询请求先访问缓存（key 是查询的语句， value 是查询的结果）。命中直接返回。不推荐使用缓存，表中有数据更新就会清除这个表的缓存数据。（MySQL 8.0 以后直接没有缓存了）
      • 关闭缓存： query_cache_type设置成DEMAND
    3. 分析器：对 SQL 语句做解析，判断SQL是否正确
    4. 优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序
    5. 执行期：执行语句，先判断用户有无查询权限，使用表定义的存储引擎执行。

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

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

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

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

    词法分析

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

    执行器

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

    1. 调用 InnoDB 引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存储在结果集中。
    2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取这个表的最后一行。
    3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

02 | 日志系统：一条SQL更新语句是如何执行的？

更新操作首先要把旧的数据查询出来，所以根据WHERE条件走一遍SELECT的过程，

0d2070e8f84c4801adbfa03bda1f98d9

查出来之后更新就好了。本身是一个事物操作。

但是为了保证MySQL的crash-safe，引入了redo log，redo log 和 bin log一起组成两阶段提交机制。

2e5bff4910ec189fe1ee6e2ecc7b4bbe

带着大佬总结的问题记笔记。

  1. redo log的概念是什么？为什么会存在？

    redo log 是重做日志，是属于 InnoDB 的日志。。主要用于MySQL异常重启后的一种数据恢复手段，确保了数据的一致性。归根到底是MySQL为了实现WAL机制的一种手段。因为MySQL进行更新操作，为了能够快速响应，所以采取了异步写磁盘写入内存后就返回的策略。但是会存在crash后内存数据丢失的隐患，redo log 具备了crash-safe能力。

  2. 什么事WAL（write-ahead log）机制，好处是什么？

    WAL机制事写前日志，也就是MySQL更新操作后在真正把数据写入磁盘前就先记录日志。好处是不用每一次操作都实时把数据写盘，就算crash后也可以通过redo log重放恢复，达到快速响应SQL的目的。

  3. redo log 为什么可以保证crash-safe？

    redo log 是在事务提交前先写入的，redo log 的内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log重放就可以恢复数据。（redo log 是环形写入的，如果写满了会真正进行写盘的）

  4. binlog的概念是什么？起到什么作用？可以做crash-safe嘛？

    binlog 是归档日志，属于MySQL Server层的日志。可以起到全量备份的作用。当需要恢复数据时，可以取出某个时间段内的bin log进行重放恢复。但是bin log不可以做crash-safe，因为crash之前，可能还没有写完 binlog MySQL就宕掉了。所以需要配合redo log 才可以进行crash-safe。

  5. bin log 和 redo log的不同点有哪些？

    bin log 是服务层的，追加写不会覆盖，记录了逻辑变化，是逻辑日志。

    rego log 是引擎层的，循环写满了会覆盖，记录了基于页的物理变化，是物理日志，具备crash-safe操作。

  6. 物理一致性和逻辑一致性各应该怎么理解？

    TODO 不是很确定

    物理一致性：在实际物理存储中没有差别。
    逻辑一致性（logical consistency）与矛盾（Contradiction）相对；
    也即逻辑上的一致（logically consistent）＝ 没有逻辑矛盾（no logical contraction）

  7. 执行器和InnoDB在执行update语句时候的流程是什么样的？

    执行器在优化器选择索引后，调用InnoDb的读接口，读取要更新的行到内存中，执行SQL操作后，更新到内存中，然后写redo log，在写bin log，此时InnoDB会在合适的时候把此次操作的结果写入磁盘。

  8. 如果数据库误操作，如何执行数据恢复？

    如果数据库在某一天误操作，就可以找到距离误操作最近时间点前的bin log，重放到临时数据库里，然后选择当天误操作的数据恢复到线上数据库。

  9. *什么是两阶段提交？为什么需要两阶段提交？两阶段提交怎么保证数据库中两份日志间的逻辑一致性？

    先写入redo log 处于prepare阶段，然后写入bin log，最后在redo log中提交变成commit状态。

    如果不用两阶段提交会出现问题（UPDATE SET a=2 table_name WERE a=1）

    1. 先写 redo log 后写 bin log。假设这次更新操作写完了redo log，没写bin log时MySQL就崩溃了，重起恢复时会加载redo log，当前实例数据是正常的a=2，但因为bin log没有这次操作记录，在进行备份的时候数据的状态就不一样了还是a=1。
    2. 先写bin log，后写redo log。假设这次更新操作写完了bin log，没写完redo log时MySQL就崩溃了，重新恢复时加载redo log后a的值还是1。再之后用bin log来恢复的时候a的值又回变成2.

    redo log变成commit状态时bin log一定写完了，两边的逻辑一定是一致的。

  10. 如果不是两阶段， 先写redo log和先写bin log两种情况会遇到什么问题？

    见上题

03 | 事务隔离：为什么你改了我还看不见？

1. 事物的特性：

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

2. 多事物同时执行的时候，可能会出现的问题：

脏读、不可重复读、幻读。

3. 事物的隔离级别

  • 读未提交（read uncommitted）：一个事物还没提交时，它做的变更就能被其他的事物看到
  • 读提交（read committed）：一个 事物提交后，它做的变更才能被其他的事物看到
  • 可重复读（repeatable read）：一个事物之行过程中看到的数据，总是跟这个食物在启动时看到的是一致的。未提交的更改对其他事物不可见。
  • 串行化（serializable ）：对于同一行距离，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

具体使用哪个隔离级别可以看数据库的transaction_isolation参数配置。

4. 事物并行会产生的问题

脏读：

当一个数据库中一个事物A正在修改一个数据但是还未提交或者回滚，另一个事物B来读取了修改后的数据并使用了。

此情况仅会发生在： 读未提交的的隔离级别.

不可重复读：

在一个事物A中多次操作数据，在事物操作过程中（未提交），事物B也才做了处理，并且该值发生了改变，这时候就会导致A在事物操作的时候发现数据与第一次不一样了。

此情况仅会发生在：读未提交、读提交的隔离级别

幻读：

一个事物按相同的查询条件重新读取以前检索过的数据，却发现其他事物插入了满足其查询条件的新数据。

幻读史之当事物不是独立执行时发生的一种现象，例如一个事物对一个表中的数据进行了修改，比如这种修改设计到表中的全部数据行。同时第二个事物也修改这个表中的数据，这种修改时向表中插入一行新数据。那么就会发生第一个事物的用户发现表中还存在没有修改的数据行，就好像发生了幻觉一样。

一般解决幻读的方法是增加范围锁，锁定检查范围为只读，

此情况会发生在：读未提交、读提交、可重复读的隔离级别.

事务隔离级别  脏读  不可重复读  幻读
读未提交（read-uncommitted）  是  是  是
读已提交（read-committed）  否  是  是
可重复读（repeatable-read）  否  否  是
串行化（serializable）  否  否  否

5. 事物隔离的实现

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

回滚日志

更新操作会在回滚日志记一条回滚操作，众多记录构成不同的read-view。假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。

d9c313809e5ac148fc39feff532f0fee

每个事物查询的时候都会到根据该事物启动的时刻读取当时时刻的read-view。

回滚日志的删除

回滚日志总不能一直保留，在不需要的时候，也就是系统判断宕没有事物在需要用到这些回滚日志是，就会把这些回滚日志删除。

具体实现时会有事物ID。

6. 事物的启动方式

  1. 显式启动事物语句 begin 或者start transaction 提交 commit。回滚rollback

  2. set autocommit=0该命令会把这个线程的自动提交关掉，这样只要之行一个SELECT语句，事物就会启动，并不会自动提交，知道主动commit 或者 rollback或者断开链接。

建议显示启动事物。 如果考虑多一次commit交互问题，可以使用commit work and chain语法，可以提交事物并自动启动下一个事物。

7. 长事物

长事物的问题

长事物存在时系统里面会存在很老的事物视图，在这个食物提交之前，回滚记录都要保留，这回占用大量存储空间。

长事物占用锁和链接资源，增大锁冲突的记录，使连接数占用增加，最终影响服务器性能。

长事物该如何避免

从应用开发段看：

  1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
  2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端看：

  1. 监控 information_schema.innodb_trx表，设置长事物阈值，超过就报警或者kill。
  2. Percona 的 pt-ill 工具
  3. 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题
  4. 如果是MySQL 5.6或者更新的版本，把 innodb_undo_tablespaces设置成2（或者更大的值）。如果真的出现大事物导致的回滚段过大，这样设置后清理起来更方便。

04 | 深入浅出索引（上）

1. 索引的作用

提高数据查询的效率

2. 常见的索引模型

  1. 哈希表

    key-value形式，把值放到数组里面，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

    哈希冲突的解决方式：开链法，

    只适用等值查询的场景。

  2. 有序数组

    按顺序存储。查询使用二分法，时间复杂度是O(log(N)).

    更新效率低，线性的。

    适用于静态数据存储（不需要更新）场景。

  3. 搜索树

    二叉搜索树

    ​ 每个节点的左儿子小于父节点，父节点又小于右儿子。

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

InnoDB中的索引模型：B+Tree

​ B+Tree 是一个N叉树，N差不多是1200，树高小，读盘次数小。

3. 索引类型

1. 主键索引与非主键索引

主键索引存储的是整行的数据（聚簇索引），非主键索引存储的主键的值（二级索引）。

主键索引只要所搜主键所在的这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，在到主键索引搜索一次（回表）。

2. 索引的维护

当一个数据页满了是，按照B+Tree算法，会申请一个新的数据页，挪动部分数据过去，这个过程叫做页分裂。这种情况下性能会下降，空间利用路降低大概50%。当相邻的数据页利用率很低的时候会做数据页合并，合并过程是分裂过程的逆过程。

从性能和存储空间方面考量，自增主键往往是更合理的选择。 自增主键每新加一行，都会在尾部追加。

05 | 深入浅出索引（下）

1. 索引覆盖

如果查询条件使用的是普通索引（或者联合索引的最左原则字段），查询结果是联合索引的字段或者主键，就不需要回表操作，可以直接返回结果，减少磁盘IO。

2. 最左前缀

联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

3. 联合索引

根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1或age=1 and name='张三'可以使用索引，单以name='张三'不可以使用索引。考虑到存储空间的问题，需要根据业务诉求，将查找频繁的数据进行靠左创建索引。

4. 索引下推

类似like 'hello%' and age > 10的检索，MySQL 5.6 版本之前会对匹配的数据进行回表查询。 5.6版本之后会先过滤掉age<10的数据，在进行回表查询，减少回表率，提升检索速度。

06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

MySQL中的锁大致分成全局锁、表级锁和行锁三类

1. 全局锁

1. 全局锁是什么

全局锁就是对整个数据库实例加锁。当需要让整个库处于只读状态的时候，可以使用Flush tables with read lock (FTWRL)命令。执行这个命令后 数据更新语句（数据的增删改，DML）、数据定义语句（包括建表、修改表结构等，DDL）和更新类事物的提交语句都会被阻塞。

2. 全局锁的场景

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

如果不加全局锁，备份系统备份的时候得到的库不是一个逻辑时间点，这个视图的逻辑不一致。

3. 全局锁的风险

  1. 如果在主库备份，在备份期间不能更新，业务停摆。

  2. 如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟。

    MySQL官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事物，确保拿到一执行视图。由于MVCC的支持，这个过程中的数据是可以正常更新的。

    一致性读是好，但前提是引擎要支持这个隔离级别。

4. 全库只读，为什么不使用set global realonly=true?

  1. 在某些系统中，readonly的值会被用来做其他逻辑，比如判断一个库是主库还是备库。因此修改global变量的影响面更大，不建议使用。
  2. 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到正常可写的状态。将整个库设置成readonly后，客户端异常后，数据库还是会保持readonly状态。会导致整个库长时间不可写，风险高。

2. 表级锁

1. 两类表级锁。

  1. 表锁

    表锁的语法是lock tables ... read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作。

  2. 元数据锁（meta data lock，MDL）

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

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

    当对一个表进行增删改查操作的时候，加MDL读锁；当对表做结构变更的时候，加MDL写锁。

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

2. 给小表加字段

给小表加字段是对表结构变更，会加MDL写锁。

如果之前有事物A搞了MDL读锁，事物没有提交。然后就进行修改表结构，因为加了写锁，所以会被阻塞。如果后面事物A又进行增删改查操作需要申请读锁，就会被锁住，这个表就完全不可读写了。
客户端又重试机制，超时后会仔起session进行请求，这个库的线程很快就爆满了。

那么如何安全地给小表加字段呢？

比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

07 | 行锁功过：怎么减少行锁对性能的影响？

MySQL 的行锁是在引擎层实现的。并不是所有引擎都支持行锁，比如MyISAM就不支持行锁。

不支持行锁的引擎处理并发只能使用表锁，并发度差。

InnoDB实现了行锁，这也是InnoDB代替MyISAM成为MySQL的默认引擎的原因之一。

1. 两阶段锁的概念是什么？对事物使用又什么帮助？

两阶段锁：在InnoDB事务中，行锁是在需要时才加上的，但并不是需要了就立刻释放，而是要等食物结束才释放。

帮助：如果事务中需要锁多个行，把最可能造成锁冲突、最可能影响并发的锁尽量往后放。

2. 死锁的概念是什么？举例说明出现死锁的情况。

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

举例：事物A更新行1不提交，行1被事物A锁住，事物B更新行2不提交，行2被事物B锁住。这时事物A再更新行2，因为行2被事物B锁着，事物A被阻塞。同时事物B更新行1，又因为行1倍事物A锁着，事物B又被阻塞了。事物A需要事物B提交才能继续执行，事物B需要事物A提交才能继续执行。就是进入死锁状态了。

3. 死锁处理策略有哪两种？

  1. 等待超时机制：

    通过设置innodb_lock_wait_timeout设置锁等待时间，超过这个时间就可以断开连接。该连接的锁会释放掉。

    局限：

    等待时间不好拿捏。时间过长服务不能接受；时间过短会出现误伤，把正常锁等待的事物所在的连接都给断开了。

  2. 死锁检测处理机制：

    通过设置innodb_deadlock_detect设置为on，开启死锁检测逻辑。当检测到死锁时，会主动回滚死锁事物中一个某一个事物打破死锁，让其他事物可以继续执行。

    局限：

    死锁检测逻辑太耗CPU了，每个新来的线程，都会检测自己会不会导致死锁，需要遍历所有更新这一行的其他线程，总的复杂度是O(N2)O(N^2)的，如果有1000个线程同时更新同一行，需要进行100万次死锁检测操作。

4. 有哪些思路可以解决热点更新导致的并发问题？

  1. 如果确定业务不会出现死锁，可以临时把死锁检测关掉。（一般不建议采用）
  2. 控制并发度，减少对同一行进行操作的线程数量，死锁检测操作的次数就少了。
  3. 将热点数据拆分成逻辑上的多行减少锁冲突。

5. innoDB 行锁是如何实现的？

innoDB行锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

所以对于update语句一般在后面加上limit 1.

这样在处理的时候查到一条数据就会停止遍历，只会对遍历过的行加锁，

08 | 事务到底是隔离的还是不隔离的？

1. MySQL中的两个视图分别应该如何理解？

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

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

事物启动时会拍一个快照，这个快照时基于整个库的。

快照不是物理快照，瞬间复制几百G的库时不现实的。

基于整个库的意思就是说一个事物内整个库的修改对于该事物都是不可见的（对于快照读的情况）

如果在事物内进行SELECT t表，另外执行的事物执行了DDL t表，根据发生时间，要么锁住，要么报错。

每一个事物都有一个事物ID，叫做transaction id。其值是按申请顺序严格递增的。

每行数据在每次事物更新的时候都会生成一个新的数据版本，并把transaction id赋值给这个数据版本的事物ID，记为row trx_id。同时旧的版本保留。每次更新页会在undo log（回滚日志）中记录一条，通过row trx_id和undo log 就可以计算出所有的版本了。

实现上，InnoDB为每个事物构造了一个数组，存储这个事物启动瞬间，正在“活跃”的所有transaction id。“活跃”是指，启动了但还没提交的。

数组里事物ID的最小值记为低水位，当前系统已经创建过的事物ID的最大值+1记为高水位。

这个视图数组和高水位，就组成了当前事物的一致性视图（read-view）

image-20211014005954382

对于一个数据版本的row trx_id

  1. 绿色部分是已提交的事物或者是当前事物生成的，这个是可见的。

  2. 红色部分是将来启动的事物生成的，不可见。

  3. 黄色部分

    1. 如果row trx_id 在数组中表示是还没提交的事物生成的，不可见。

    2. 如果row trx_id 不在数组中表示是已提交的事物生成的，可见。

TODO：这里有点乱。。。

当前事物生成的为啥不是在黄色？

当前读是否破坏了可重复读/一致性读？

可重复度和一致性读是一样的嘛？

3. 当前读、快照读和一致性读分别是什么？

  1. 当前读：更新数据都是先读后写的，这个度只能读当前的值。（除了update语句，select加锁后也是当前读）。
  2. 快照读：一般情况下的SELECT语句读的是快照的值。不是当前最新的数据的值。
  3. 一致性读：MVCC

4. 为什么rr能实现可重复读而rc不能？

  1. 快照读的情况下，rr不能更新事物的高水位，而rc每次会把高水位更新为快照读之前最新已提交事物的transaction id， 所以rc不能可重复度。
  2. 当前读的情况下，rr是利用record lock+gap lock 实现的。而rc没有gap lock 所以rc不能可重复度。