三大日志

重做日志(redo-log)

  1. （用来干嘛的） InnoDB 存储引擎独有，MySQL崩溃时用来恢复数据：重新执行 Redo Log 中尚未持久化到数据文件的修改操作；
  2. （前置知识）MySQL的查询会把数据页读到缓冲池Buffer Pool中，更新数据会直接在Buffer Pool操作；
  3. （如何做的）把“在某个数据页上做了什么操作”记录到重做日志缓存redo log buffer中，再刷盘；
  4. （事务的特殊）事务执行过程的 redo log 会写入 redo log buffer 中，因为可能会被后台线程刷盘，所以没有提交事务的 redo log 记录也可能被写入磁盘；
  5. （存储形式）硬盘上存储的 redo log 是以一个日志文件组的形式出现的，每个的 redo 日志文件大小都是一样的，会进行循环写入；

二进制日志(bin-log)

  1. （做啥的）逻辑日志，顺序记录所有涉及更新数据的逻辑操作；主从|主备依靠它同步；
  2. （记录格式）statement记录SQL原文；row包含操作的数据，例如now()函数的具体时间，是解析后的SQL语句；mixed会判断是否引起不一致，再选择；
  3. （写入机制）先写到binlog cache，事务提交时再一起写到binlog中，每个事务(线程)的binlog cache是独立的；

两阶段提交

  1. （问题所在）binlog和redo log的逻辑可能不一样，即事务中写了redo log，但binlog最终没写入；

  2. （解决方式）把redo log的写入拆分为prepare和commit两个步骤，在提交事务时先写入binlog，再设置redo log的commit；在重启时需要满足既不是commit阶段又不存在对应的binlog，才回滚事务；

两阶段提交

图源：JavaGuide

回滚日志(undo-log)

  1. 回滚日志，用来对事务进行回滚，保证事务的原子性；
  2. 也需要存储到redo log中，但是事务提交后是可以删除的；删除的时机和undo log的种类有关，和MVCC有关，详见MVCC

事务的ACID：

  1. 原子性(Atomic)：修改数据要么全部成功，要么全部失败
  2. 一致性(Consistent)：数据都保持一致状态？
  3. 隔离性(Isolation)：并发事务之间的隔离
  4. 持久性(Duration)：事务完成之后不会被回滚

事务隔离级别

4种级别

  1. READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  2. READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  3. REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  4. SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

RC和RR两种级别都是用MVCC机制，但是他们生成Read View的时机不同，RC会在每次select查询前都生成一个Read View，而RR只在事务开始后第一次select数据前生成Read View，这样RR就实现了可重复读

导致的问题

  1. 脏读(读未提交) => 读已提交

脏读

图源：JavaGuide

  1. 不可重复读(一个事务中读一个数据多次，结果不一样) => 可重复读

不可重复读

图源：JavaGuide

  1. 幻读 => 可重复读

例子：SQL 脚本 1 在第一次查询工资为 500 的记录时只有一条，SQL 脚本 2 插入了一条工资为 500 的记录，提交之后；SQL 脚本 1 在同一个事务中再次使用当前读查询发现出现了两条工资为 500 的记录这种就是幻读。

幻读

图源：JavaGuide

注：图中的select ... for update的for update会将select得到的行（如果没有主键，则全表上锁）上悲观锁，用于select之后update。

解决方案：一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据。

  1. 将事务隔离级别调整为 SERIALIZABLE ；
  2. 在可重复读的事务级别下，给事务操作的这张表添加表锁；
  3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock(Record Lock+Gap Lock)；⚠️待研究

多版本并发控制(MVCC)

MVCC即多版本并发控制，通过维护在每个数据行上的多个版本来实现；这些版本会定期被回收

总述（基于什么逻辑实现的）：

  1. 修改：即写操作，会生成一个新的数据版本，带有当前事务的版本号，从而使得其他事物能够读取相应版本的数据
  2. 读取：使用快照读取，选择符合其事务开始时间的数据版本进行读取
  3. 事务提交后，其版本会成为数据库的最新版本，并对其他事务可见

快照读和当前读

mysql的repeatable-read隔离级别其实一定程度上可以解决幻读问题，这就是用MVCC保证的，关键在于快照读和当前读这两个操作。

  1. 快照读(一致性非锁定读)：读取历史快照的数据；

  2. 锁定读(当前读)：执行select ... lock in share mode、select ... for update、insert, update, delete这些语句的时候，就不能再用快照了，而是会读取最新版本并且对读取到的记录加锁。

共享锁和排它锁

  1. select ... lock in share mode会对数据行添加**共享锁(S锁)，有S锁再添加排他锁(X锁)**会阻塞，因为有人在读不准改；
  2. 如果是insert, update, delete，会添加X锁，此时其他任何锁都不能加，因为我在改

但是即使上S锁或X锁，快照读还是能读的，因为读的是第一次查询时生成的快照

InnoDB对MVCC的实现

前面的MVCC只是一种理论，具体的实现方式要看不同的引擎，比如说快照读，不可能真的存一个快照在本地，太浪费空间了。

当在一个事务中读取某一行时，会根据这个事务的Read View及和该行的隐藏字段DB_TRX_ID(最后一次插入或更新该行的事务id)来判断，该行的最新版本是否对事务可见；如果不可见，则通过行的另一个隐藏字段DB_ROLL_PTR回滚到上一个版本，直到某个版本对该事务可见，或回滚指针不存在，即该版本首次被insert。

数据行隐藏字段

  1. (重要)DB_TRX_ID：表示最后一次插入或更新该行的事务 id

  2. (重要)DB_ROLL_PTR ：回滚指针，指向该行的undo log

  3. DB_ROW_ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

ReadView结合undo-log

Read View创建时会有：

  1. 当前活跃事务列表m_ids：不一定是连续的，因为后创建的可能先完成后，便不活跃了
  2. m_low_limit_id：目前出现过的最大的事务 ID+1
  3. m_up_limit_id：活跃事务列表m_ids中最小的事务 ID，ID比它大的并不一定活跃，也可能完成了，但比他小的是必然完成了

undo-log有两个作用：

  1. 事务回滚时恢复数据
  2. MVCC当读取某个行记录，发现该记录的最新版本对当前事务并不可见，则需要通过undo log读取之前的版本数据，从而实现非锁定读

有两种undo-log：

  1. insert undo log：因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除，所以如果某数据行的DB_TRX_ID表明该数据行不可见，它的DB_ROLL_PTR又是空的话，这就说明他是insert的产生的

  2. update undo log：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除。

    不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

数据表索引

数据结构

哈希表

  1. （怎么建和怎么发挥作用）key为建立索引的字段值，value为该行所在的地址（或index）；

  2. （哈希冲突）拉链法和红黑树or B+Tree

  3. （缺点）不支持顺序和范围查询，无序；例如找id小于500，需要对1和499都定位一次

红黑树

待研究

B树

多叉平衡搜索树，参考https://www.bilibili.com/video/BV1tJ4m1w7yR，下图亦是出自于此

比红黑树好——因为大量数据需要对磁盘进行IO，内存放不下

而B+树每个节点存储的数据多，硬盘读取1次节点耗时是类似的，无论节点的元素有多少

访问节点的操作在硬盘上进行，访问节点内的元素是在内存上进行的

  1. 整体概述：m阶B树的结点，最多m个分支和m-1个元素，最少m/2向上取整个分支，和m/2向上取整个-1个元素

image-20241227164429874

  1. 插入，只可能上溢出，溢出后需要分裂和调整

image-20241227164935458

  1. 删除，只可能下溢出；删除非叶节点的情况，去删除其前驱或后继，转换为删除叶节点

B+树

  1. 分支数目=节点的元素数，每个元素都会出现在叶节点，中间节点的元素是重复的

  2. 叶节点存储数据所在地址，可以针对一张表中多个字段建立B+索引

  3. 是一个多极索引结构，叶节点可理解为表格主键的索引，树中的第i层是对i+1层的索引

image-20241227171213564

索引介绍

  1. 主键索引

    每个表都会有主键并建立索引，会检查是否有唯一索引且不允许null的字段，拿来当作默认主键，否则会自动创建一个自增主键；

    InnoDB默认使用B+树存储主键索引，这时候叶节点就会存储指向相应记录的指针；

    InnoDB使用聚簇索引，MyISAM使用非聚簇索引。前者使得基于主键的查询效率非常高，因为表中数据的物理顺序和主键索引的顺序是一致的，但是更新的代价比较大；后者比前者多一步IO操作，并且可能会导致二次查询，但是更新代价小；

  2. 二级索引

    对应于主键索引，通过二级索引可以定位主键的位置；如果也用B+树，叶节点存储的是索引列的值和对应的主键值，二级索引需要找两次索引树

  1. 覆盖索引：索引包含所需要查询字段的所有值。一般而言，非主键索引查询的时候需要先找到主键值，然后再定位完整的行数据，这叫回表；但是如果需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。即少一次查询索引树

  2. 联合索引：使用表中的多个字段创建索引，其实主要还得看最左的字段，即最左匹配原则。将区分度高的字段放在最左边，可以过滤更多数据。

    假设有一个联合索引(column1, column2, column3)，其从左到右的所有前缀为(column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。只有联合索引的前缀会生效。

    [!NOTE]

    什么时候不走索引呢，即索引失效问题，对于联合索引，只要不包含最左列，就不会走索引，其余情况待深入研究

  1. 前缀索引：只针对字符串类型，对前几个字符创建索引，建立起的索引更小。
  2. 聚簇索引和非聚簇索引：聚簇索引中数据行和索引值是存放在一起的，例如经典例子B+树存主键索引，它会比非聚簇索引少一步根据叶节点的指针去找对应数据行的操作，因为聚簇索引直接把数据行存在叶子结点中，并且叶子结点是可以顺序访问的，每个叶子结点都有指向下一个叶子结点的指针。

执行计划分析

Explain关键字+查询语句

比较重要的字段

type

下面是type字段可能的值，效率依次变差

  1. system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  2. const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  3. eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  4. ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  5. index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  6. range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  7. index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
  8. ALL：全表扫描。

possible_keys & key

possible_keys是可能用到的索引，key是实际用到的索引。InnoDB会选择更优秀的索引来查找数据。通过对比这两个字段，可以分析查询引擎的推断。

Extra

extra包含额外的查询信息，例如：

  1. Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。

  2. Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。

  3. Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。

  4. Using index condition：表示查询优化器选择使用了索引条件下推这个特性。

  5. Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

  6. **Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。