MySQL知识点整理

1、事务

• 事务四大特性

  • 原子性（Atomicity）
    • 事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
  • 一致性（Consistency）
    • 数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态
  • 隔离性（Isolation）
    • 一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
  • 持久性（Durability）
    • 一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响

• 事务特性的技术实现

  • 原子性
    • 通过事务控制，保证一个事务里的任务要么全部成功，要么全部失败，通过undo log来记录上一次数据更新的状态，如果出现异常，可以通过undo log回滚到上一次的状态
  • 隔离性
    • 通过事务隔离级别来保证相同事务读取数据是一致的, 在Mysql的InnoDB存储引擎中是可以通过MVCC与LBCC来实现隔离性, 通过生成一个类似快照的数据集,保证一个事务只能读取到小于等于当前事务id的数据，以及之后其他事务删除的数据
  • 持久性
    • 通过redo log与double write来确保一个事务中对数据的更新执行成功后能永久的存储在磁盘当中，如果数据在刷盘之前崩溃，重启后可以通过redo log恢复数据，前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲（double write）保证
  • 一致性
    • 数据库通过原子性、隔离性、持久性来保证一致性
    • ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性

• 事务的并发问题

  • 脏读
    • 事务A读取到了其他事务已经修改但未提交的数据，导致前后两次读取数据不一致
  • 不可重复读
    • 事务A读取到了其他事务修改或删除且提交的数据，导致前后两次读取数据不一致
  • 幻读
    • 事务A读取到了其他事务插入的数据，导致前后两次读取数据不一致

• 事务的隔离级别

  • 读未提交（Read UnCommitted）
    • 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
    • 三种并发问题都没解决
  • 读已提交（Read Committed）
    • 可以避免脏读，但不可重复读和幻读的问题仍然可能出现
  • 可重复度（Repeatable Read）
    • 可以避免脏读和不可重复读，但幻读仍然存在
    • InnoDB中RR隔离级别通过间隙锁+MVCC解决了大部分的幻读问题，只有一种特殊的幻读情况无法解决
      • MVCC只能对快照读起作用，而对于加锁的读请求，这种属于当前读，当前读的话是可以查询到其他事务的变更的，所以会产生幻读
  • 串行化（Serializable）
    • 能解决脏读、不可重复读、幻读问题，会锁住所有事务，使每个事务串行执行，一个事务结束另一个事务才能执行
  • 以上隔离级别由上到下，执行效率依次递减

2、锁

• 锁的分类
  • 按粒度区分
    • 行锁
      • 锁定是一行记录
    • 表锁
      • 锁定是整张表
  • 按兼容性区分
    • 共享锁
      • 也叫读锁，多个事务可以共享一把读锁，共享锁会阻塞其他事务的修改
      • 手工在SQL后面加上LOCK IN SHARE MOD 进行加锁
    • 排它锁
      • 也叫写锁，一个事务获取了一行数据的排它锁，其他事务就不能再获取这一行数据的共享锁和排它锁
      • 增删改数据时会默认加上排它锁，也可以手工在SQL后面加上FOR UPDATE 加锁
  • 按锁的模式区分
    • 记录锁（Record Locks）
      • 属于行锁，表示对某一行记录加锁
      • 记录锁总是锁定索引记录（SELECT和UPDATE都会加锁），即使表没有定义索引。对于这种情况， InnoDB有一个隐藏的聚集索引进行记录锁定。但因为可能会扫描全表，那么该锁也就会退化为表锁
    • 间隙锁（Gap Locks）
      • 锁的是索引记录之间的间隙，或者在第一个索引记录之前或最后一个索引记录之后的间隙上锁
      • 对于具有唯一搜索条件的唯一索引，InnoDB只锁定索引记录，而不会锁定间隙
      • 在RC隔离级别下，不会使用间隙锁，在RR级别及以上才会使用它
      • 间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能
    • 临键锁（Next-Key Locks）
      • 临键锁是记录锁+间隙锁的组合，锁的是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合
      • Next-Key 的锁的范围都是左开右闭的
      • 只有在RR隔离级别中才会生效
    • 意向锁（Intention Locks）
      • 意向锁是表级锁，指示事务稍后需要(或想要，表明锁的意向)对表中的行使用哪种类型的锁（共享锁或独占锁），即用来标识该表上面有数据被锁住（或即将被锁）
      • 意向共享锁（IS）：一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁
      • 意向排它锁（IX）：一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁
    • 插入意向锁（Insert Intention Locks）
      • 插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种特别的间隙锁
      • 该锁用以表示插入意向，当多个事务在同一区间插入位置不同的多条数据时，事务之间并不会产生冲突
      • 虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁
    • 自增锁（AUTO-INC Locks）
      • 自增锁是一种特殊的表级锁，主要用于事务中插入自增字段

3、MVCC

    MVCC是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，他也是一种并发控制的解决方案

    对数据的操作主要有2中，分别是读和写，而在并发场景下，就可能出现三种情况：`读-读并发`，`读-写并发`，`写-写并发`。

    在没有写的情况下`读-读并发`是不会出现问题的，而`写-写并发`这种情况比较常用的就是通过加锁的方式实现。那么`读-写并发`则可以通过MVCC的机制解决

• 快照读和当前读

  • 快照读就是读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读
  • 当前读就是读取最新数据，所以加锁的 SELECT，或者对数据进行增删改都会进行当前读
  • 快照读是MVCC实现的基础，而当前读是悲观锁实现的基础

• Undo Log

  • undo log是Mysql中比较重要的事务日志之一。是一种用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退
  • 上面提到的存在undo log中的 更新前的数据 就是我们前面提到的快照
  • 行记录的隐式字段
    • 据库中的每行记录中，除了保存了我们自己定义的一些字段，还有重要的隐式字段
      • db_row_id：隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段来创建聚簇索引
      • db_trx_id：对这条记录做了最新一次修改的事务的ID
      • db_roll_ptr：回滚指针，指向这条记录的上一个版本，其实他指向的就是Undo Log中的上一个版本的快照的地址
  • 每一次记录变更之前都会先存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存在undo log中

• Read View

  • Read View 主要来帮我们解决可见性的问题的，他会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照
  • 在 Read View 中有几个重要的属性
    • trx_ids：系统当前未提交的事务 ID 的列表。
    • low_limit_id：未提交的事务中最大的事务 ID。
    • up_limit_id：未提交的事务中最小的事务 ID。
    • creator_trx_id：创建这个 Read View 的事务 ID
  • 每开启一个事务，我们都会从数据库中获得一个事务 ID，这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序，事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能
  • 根据不同的事务隔离级别，Read View的获取时机是不同的，在RC下，一个事务中的每一次SELECT都会重新获取一次Read View，而在RR下，一个事务中只在第一次SELECT的时候会获取一次Read View
  • 所以在RR这种事务隔离级别之下，因为有MVCC机制，就可以解决不可重复读的问题，因为他只有在第一次SELECT的时候才会获取一次Read View，天然不存在重复读的问题了

• MVCC就是通过Read View + Undo Log来实现的，undo log中保存了历史快照，而Read View 用来判断具体哪一个快照是可见的

4、InnoDB 内存结构

• Buffer Pool

  • Buffer Pool 缓存的是页面信息，包括数据页、索引页、锁信息等
  • MySQL表数据是以页为单位，查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中
  • 后续的查询先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时，如果 Buffer Pool 里命中数据，就直接在 Buffer Pool 里更新
  • 缓冲池使用最近最少使用的 （LRU） 算法的变体进行缓存数据页
  • 后续对数据的增删改查都是在Buffer Pool里操作
    • 查询：从磁盘加载到缓存，后续直接查缓存
    • 插入：直接写入缓存
    • 更新删除：缓存中存在直接更新，不存在加载数据页到缓存更新
  • 直接更新数据的缓存页称为脏页，缓存页刷盘后称为干净页

• Change Buffer（写缓冲）

  • 如果数据页不是唯一索引，不存在数据重复的情况，就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）

  • 这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度

  • Change Buffer 记录刷到数据页的操作叫做 merge

    • 访问这个数据页时触发
    • 后台线程定时触发
    • 数据库 shut down时触发
    • redo log 写满时触发

    redo log 写满时触发

• Log Buffer（Redo Log）日志缓存

  • 对表数据进行更新时会把 在某个数据页上做了什么修改 记录到 redo log buffer 里，之后会刷盘到redo log文件中

  • 刷盘时机

    InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种策略

    • 设置为0（延迟写）
      • 事务提交的时不会主动触发刷盘操作
    • 设置为1（默认值，实时写，实时刷）
      • 每次事务提交时都将进行刷盘操作
    • 设置为2（实时写，延迟刷）
      • 表示每次事务提交时都只把redo log buffer内容写入page cache
    • InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存（page cache），然后调用fsync刷盘
    • redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动刷盘