阿龙的学习笔记---MySQL45讲的总结（一）

学习这个大佬的课，的确超级有经验：https://time.geekbang.org/column/intro/100020801

---

查询语句过程

• 连接器，分析器，优化器，执行器，最后到存储引擎。
  

• MySQL 可以分为 Server 层和存储引擎层两部分。

  • Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能。
  • 存储引擎层负责数据的存储和提取。

• 连接器：

  • 第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

• 查询缓存：

  • 连接完成后，执行 select 语句了。会先查询缓存。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
  • 但是，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。所以静态表比较适合，更新较多的表不适合，反而影响效率。

• 分析器：

  • 分析器先会做“词法分析”。再做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

• 优化器

  • 经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

• 执行器：

  • 优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

---

更新语句过程

• 依然走上面的路：

  • 先连接
  • 查询缓存会清空。
  • 分析器分析出是一条更新语句。
  • 优化器进行索引等优化。
  • 执行器执行。但是除了更新表中数据外，还有redolog和binlog两个日志文件的更新。即物理日志 redo log 和逻辑日志 binlog。

• redo log： InnoDB的物理日志模块:

  • 是为了解决每次都得更新到数据库而导致效率低下的问题。
  • 这个先日志的技术叫：WAL，全称是 Write-Ahead Logging。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。
  • log存储空间是一个环形结构，大小是固定的，如果满了则需要写回数据库中一些。
  • log还解决了数据库如果发生异常宕机的问题，重启后可以恢复。要将持久化至磁盘的参数设置为每次事务都持久化到磁盘。

• bin log： 系统server层的归档日志模块：

  • 前面我们讲过，MySQL 整体来看，一块是 Server 层，它主要做的是 MySQL 功能层面的事情；一块是引擎层，负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志），所有引擎都能用。
  • 除了这个不同之外，redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。redo log 是环形循环写，binlog 是可以追加写到一定大小后会切换到下一个。

• 具体执行流程：

  • 先取到这一行的数据，再调用引擎接口写入这行新数据。
  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，变成 prepare。
  • 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  • 执行器提交事务，rodo log 变成 commit。

• rodelog 的两阶段：prepare 和 commit：

  • 为了 bin 和 redo 的一致性。

---

事务隔离：

• 针对ACID中的隔离性，问题针对多个事务同时进行产生的脏读/不可重复读/幻读，定义了四个隔离级别以解决这些问题。

  未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  可重复读是指，事务在执行期间看到的数据前后必须是一致的。
  串行化，顾名思义是对于同一行记录，所有操作是串行的(是这样吗？)，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
  

• 作者简单的阐述了一下更直观的表述：

  “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
  “读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的；
  可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
  而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
  

• 详细描述”可重复读“的实现：

  • 在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚(undo)操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
  • 不同时刻启动的事务会有不同的 read-view，，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
  • 回滚日志(undo log)的删除，在最早的视图(read-view)之前的会被删除。回滚日志记录的数据量比较大，这也是为什么不应该使用长事务的原因之一，会导致回滚日志的文件很大。

• 事务的启动：实际操作注意点，误操作导致长事务

  • autocommit 选项为1，则是设置在每个语句之后自动commit(我理解为每个语句是一个事务)，但如果一些情况下默认设置为0，那么执行一个语句之后，就开启了事务，但是没有提交，这样会导致你可能并不想开启事务，或者一直没有commit而导致长事务。
  • 所以作者建议，如果要打开事务，每次手动开启。即set autocommit=1, 通过显式语句的方式来启动事务。

---

索引详解：

• 索引的出现是为了提高查询效率，但是实现索引的方式却有很多种：
  • 哈希表：查询很快，但是无序，区间操作时不好使。所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。
  • 有序数组：等值查询或是区间查询，都能二分，效率很高。但是插入/删除操作效率极低。
  • 多叉搜索树：优点快，并且插入也快，需要做平衡, B+树是InnoDB。
  • 跳表、LSM 树等数据结构也被用于引擎设计中。（自己看）
  • 经验：数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。
• InnoDB 的索引：
  • B+树。
  • 索引类型分为 主键索引 和 非主键索引。主键是聚簇索引；非主键索引中存储的是主键的值，也叫二级索引，需要查询两次。
• 索引维护：
  • 自增的主键比较利于维护，不会产生叶子节点的分裂或合并。
• 优化索引：
  • 覆盖索引技巧：
    • 比如当我们查询非主键索引的时候，取某个字段的值，则需要二次回表再去主键索引中找一遍，这样就要查找两个B+树。这时覆盖索引这个技巧就用得上了。
    • 个人认为这里说的覆盖索引不是一种类型，只是一种技巧，是利用联合索引实现的。比如从非主键查找主键的值，那么只需要一次。那么比如建立<身份证，姓名>的联合索引，那么如果有很多请求都是通过身份证查找名字，那么在这个联合索引中就能遍历一次找到。
  • 最左前缀原则：
    • 两个层面，一是字符串可以最左前缀匹配，因为排序就是按照这个比较方法排序的。
    • 在联合索引中，也是一个技巧和特性。联合索引也支持最左前缀的匹配。比如还是<身份证，姓名>这个联合索引，假如创建了这个联合索引，那么<身份证>这个单独的索引就可以不用建立了，因为联合索引存储时，按照顺序建立索引，身份证在前，前面匹配到即可，所以按身份证查询时，也能用到索引的优势。
  • 索引下推优化：
    • MySQL5.6之后推出的。
    • 比如建立了（name, age）的联合索引。语句是：

      select * from tuser where name like '张%' and age=10 and ismale=1;
      

    • 那么没有索引下推的话，应该在（name，age）中查询到前缀是”张“的人，然后依次回表，查询是否满足条件。
    • 如果有的话，那么命中了name=”张%“之后，还会再判断 age 是否满足。满足了之后，才会回表，这样回表次数更少。
    • 总结： 在满足语句需求的情况下， 尽量少地访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。

---

并发控制——全局锁 & 表锁

• 全局锁:

  • 将整个数据库锁住，处于只读状态，其他更新/定于语句或事务都会阻塞。
  • 典型使用场景是在全库逻辑备份中。如果不加锁，则可能导致数据不一致。
    • 能拿到一致性视图的不用全局锁的方式：在InnoDB中，可重复读的隔离级别，会在事务开始时创建一致性视图，那么这时备份则不用全局锁。
    • 但是只有InnoDB有啊，比如MyISAM就得用全局锁。所以并不是没用。

• 表级锁：

  • 表级锁是锁住整个表，而不是整个数据库。InnoDB中很少用到表级锁。
  • 另一种表级锁是元数据锁MDL（metadata lock)。这个锁主要是针对表结构的。在访问某个表时，这个表的结构不应该发生变化，会加读锁。在修改这个表结构时，加写锁。上述是默认自动加锁的。
    • 不过这个锁又可能会造成问题，比如长事务一直没有释放MDL读锁，你要改变结构会加MDL写锁，然后会等待之前的读锁事务释放。那么之后的所有读操作都不能进行了。
    • 解决方法：首先对于长事务的处理，之前提过。再者更改表结构的语句可以设置一个等待时间，超时则放弃重试。

---

并发控制——行锁

• MySQL 的行锁是在引擎层由各个引擎自己实现的。
• 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
• 所以经验是要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
• 死锁和死锁检测：
  • 两种策略：
    • 一种是超时等待，但时间太长影响性能，时间太短会误操作；
    • 另一种是死锁检测。是有额外负担的，时间复杂度O(n)，会导致性能下降。
  • 解决热点行更新的性能问题：
    • 去掉死锁检测，不太靠谱，除非保证没有死锁。
    • 控制并发度。如果同时只有10个线程更新热点行，那么死锁检测也很快，其他的排队。牛逼的专家可以修改MySQL源码，或者使用中间件排队。再或者将热点行拆分成多行，这个需要考虑业务具体逻辑。

---

事务隔离？查询与更新的差异

• 前面提到可重复读隔离级别，事务 T 启动的时候会创建一个视图 read-view，之后事务 T 执行期间，即使有其他事务修改了数据，事务 T 看到的仍然跟在启动时看到的一样。

• 但是在更新的时候，情况就复制了起来，这时要加行锁，等到锁了之后，修改的是视图中的值，还是最新的值呢？？

• 比如如下操作：表中有id=1,k=1这个数据。
  

  注意： begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

  • 结论：事务A读取到的是1，事务B select 到的是最新的3。先看一下MVCC具体实现，在分析查询与更新。

• MVCC

  • InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id，事务开始时申请，严格递增。
  • 每一行也有多个版本，按顺序，每个版本会记录更新的事务的trx_id。多个版本也不是物理存储多个版本，而是通过**回滚日志（undo_log）**进行回滚。否则每次创建快照都要将数据版本全取出来就太复杂了。
  • 可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。
  • 具体实现：

    • 在实现上， InnoDB 为每个事务构造了一个启动时活跃事务数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
      

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

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

    • 利用这个特性，开始事务创建快照的时候只需要创建这个数组即可。

• 查询与更新的差异：

  • 普通的查询我们可以知道，在开始事务时创建的视图，查询的版本肯定按照上述的规则，所以在上面的例子中，不会查询到B和C的更改。
  • 而更新就不太一样了，只看下图的B和C。B事务先开始，C事务后开始然后提交。所以按理说B是看不到C的更新这个操作的。**但是B需要更新操作，更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。**这时 k 读出是 2， 加 1 是 3。而自己的更新时能被本事务看到的，所以之后select k则结果是3。
    

---