MySQL

一、关系型数据库的特点：

1. 传统关系型数据库中的数据以列和行的方式进行存储

1.1.Innodb存储引擎

Innodb存储引擎支持两种常见的索引数据结构：B+树索引、Hash索引，其中B+树索引是目前关系型数据库系统中最常见、最有效的索引。

1.1.1 B+树索引

B+树索引分为聚簇索引与非聚簇索引。聚簇索引就是按照每张表的主键构造一个B+树，B+树的叶子节点中记录着表中一行记录的所有值。非聚簇索引的叶节点不包含行记录的所有值，只包含索引值和主键的值。

B+树特点：

• 1.B+树是一颗平衡树，每个叶子节点到根节点的路径长度相同，查找效率较高。
• 2.B+树的所有关键字都在叶子节点上，因此(支持)范围查询时只需要遍历一遍叶子节点即可。
• 3.B+树的叶子节点都按照关键字大小顺序存放，因此可以快速的支持按照关键字大小进行(支持)排序。
• 4.B+树的非叶子节点不存储实际数据，因此可以存储更多的索引数据。
• 5.B+树的非叶子节点使用指针连接子节点，因此可以快速的支持范围查询和倒叙查询。
• 6.B+树的叶子节点之间通过双向链表连接，方便进行范围查询。

B+树优点 & 缺点：

• 在节点分裂和合并时，IO操作减少。B+树到叶子节点的大小就是固定的，而且节点的大小一般都会设置为一页的大小，这就使得节点分裂和合并时，IO操作很少，只需要读取和写入一次。
• 有利于磁盘预读。由于B+树的节点大小是固定的，因此可以很好的利用磁盘预读的特性，一次性读取多个节点到内存中，可以减少IO操作到次数，提高查询效率。
• 有利于缓存。B+树到非叶子节点只存储指向子节点的指针，而不存数据，这一可以使得缓存能够容纳更多的索引数据，从而提高缓存的命中率，加快查询速度。
• B+树索引的维护成本比较高，插入删除数据时需要调整索引结构，这个过程可能会涉及到页分裂和页合并等操作。

为什么不用B树：

B树（B- Tree）和B+树（B+ Tree）是两种常用的树结构，B是Balanced首字母，平衡的意思，MySQL的InnoDB使用的是B+树，MongoDB使用的就是B树存储。B树属于多叉树，又名平衡多路查找树，B树多设计目的是为了减少磁盘访问次数，提高查询性能。

m阶的B树，满足如下条件：

• 每个节点最多只有m个子节点。
• 每个非叶子节点（除了根）具有至少[m/2]子节点。
• 如果根不是叶节点，则根至少有两个子节点。
• 具有K个子节点的非叶节点包含k-1个键。
• 所有叶子都出现在同一水平，没有任何信息（高度一致）。

特点：

• B树是一种多路搜索树，每个节点可以包含多个子节点。相当于二叉搜索树，B树可以存储更多的关键字和子节点，从而降低了树的高度，减少了磁盘访问次数。
• B树通过在插入和删除操作时进行节点的分裂和合并，保持树的平衡状态。这样可以确保树的高度适中保持在可接受的范围内，保证了较快的查询性能。
• B树的节点中的关键字按照升序排序，使得范围查询操作更加高效。通过遍历树中的叶子姐弟哪，可以顺序获取连续的数据。
• B树的关键字和数据项可以存储在叶子节点和非叶子节点。并且每个关键字出现且只出现在一个节点中。
• B树的搜索可能在非叶子节点上结束，他的搜索性能相当于在关键字全集中做二分查找。

区别：

• 1.数据存储位置：在B树中，数据项存储在叶子节点和非叶子节点上，而B+树中，数据项只存储在叶子节点上。非叶子节点只包含键值信息。
• 2.叶子节点指针：B树的叶子节点之间没有指针连接，每个叶子节点独立存储数据项。而B+树的叶子节点通过指针连接成一个连表，可以方便的进行范围查询。

为什么不用红黑树：

红黑树是一种自平衡的二叉查找树。红黑树适用于关联数组、优先队列等。
优点：

• 保证最坏情况下的性能：红黑树通过维持树的大致平衡，而不是完美平衡。确保了在插入删除和查找操作中最坏情况下的时间复杂度均为O(log n)。这比普通的二叉搜索树(在最坏情况下可能退化为连表，时间复杂度为O(n)好得多)。
• 自平衡：每次插入或删除操作后，红黑树通过旋转和重新着色的方法自动维持平衡，无需额外的操作或维护。
• 数据结构简洁：节点只需要额外存储一个颜色位，因此相比于其他平衡树（如AVL树）来说，内存的额外开销较小。

1.1.2 Hash索引

哈希索引是将索引列的值通过哈希函数计算后得到一个桶的编号，然后将桶内的记录保存在一个链表或者树结构中。

• 哈希索引适用于等值查询，但不适合范围查询和排序操作。
• B+树索引在节点中存储多个键值对，因此可以充分利用磁盘块的空间提高空间利用率，而哈希索引由于需要存储哈希值和指针，因此空间利用率低。

2. 关系型数据库采用结构化查询语言（SQL）对数据进行查询

3. 关系型数据库强调ACID（原子性-Atomicity，一致性-Consistency，隔离性-Isolation，持久性-Durability）

• 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致的状态。
• 隔离性：多个事务兵法执行时，一个事务的执行不应影响其他事务。
• 持久性：一个事务一旦提交，他对数据库的修改应该永久保存在数据库。

3.1. 数据库事务：访问并可能操作各种数据项的一个数据库序列，这些操作要么全部执行要么都不执行，由事务开始于事务结束之间执行的全部数据库操作组成。

• 脏读：读到了其他事务还未提交的数据
• 幻读：事务在做范围查询的过程中，有另外一个事务对范围内新增了记录（INSERT），导致范围查询的结果条数不一致。
• 不可重复读：对某数据进行读取的过程中国，有其他事务对数据进行了修改（UPDATE、DELETE），导致第二次读取1的结果不同

3.2 数据库事务的隔离级别：SQL-92定义了4种隔离级别来解决脏读、幻读、不可重复读。

• 串行化（Serializable）：最高的隔离级别，解决了幻读的问题。
• 可重复读（Repeatable read）：解决了不可重复读的问题，存在幻读的问题。
• 读已提交（Read committed）：在一个事务修改数据的过程中，如果事未提交，其他事务不能读该数据，避免了脏读。会存在不可重复读的问题。
• 未提交读（Read uncommitted）：最低的隔离级别，一个事务可以读到另一个未提交事务的数据。会存在脏读、幻读、不可重复读的问题。

# MySQL5.X事务隔离级别：REPEATABLE-READ
select @@tx_isolation;

# MySQL8.0事务隔离级别：REPEATABLE-READ
select @@transaction_isolation;

# 查看系统当前隔离级别
select @@global.tx_isolation;

# 开始事务
set autocommit=off;
# 或者
start transaction;

3.3 MySQL 默认隔离级别使用：REPEATABLE-READ

• Oracle 默认使用 Read committed。
• MySQL 默认使用 Repeatable read。

3.3.1 原因：兼容历史上statement模式的bin log，导致主从同步数据不一致的问题

• 在主从复制的MySQL集群下，数据的同步是通过bin log进行的，且早期只有statement模式（记录SQL语句原文），如果使用Read committed或Read uncommitted会有问题。
• 问题：在RC的前提下，事务1->新增,事物2->删除 ,如果两个事务写入bin log，事务1先提交，事务2后提交，bin log先记录事务1的SQL语句，后记录事务2的SQL语句，导致主节点，有新增的数据，从节点在则没有新增的数据。
• 解决：Repeatable read 更新，插入数据时会加锁（GAP锁+临键锁），使得事务1会被卡住，待事务2提交回滚后再执行，同时使用mixed或者row格式的bin log日志

3.3.2 bin log 模式

• statement：记录SQL语句的原文。
• row：记录每个数据更改的具体行的细节，不会导致主从不一致，担是需要记录更多的内容，数据恢复时，时间会更长，磁盘IO和网络IO等资源占用也比较高。
• mixed：将row和statement结合，MySQL会根据SQL情况，自动切换一个合适的格式进行记录。

3.4 MySQL InnoDB 的 REPEATABLE-READ 隔离级别有没有解决幻读

• 答案：通过 间隙锁 + MVCC解决了大部分幻读的问题，彻底解决幻读还是需要使用串行化的隔离级别。
• MVCC 机制:MVCC 为每个数据行维护多个版本，每个版本都有一个可见性标志。当事务开始时，它会获得一个读视图，这个视图决定了事务能看到哪些数据版本。事务在其生命周期内始终看到同一份数据版本，即使有其他事务插入了新的行

3.4.1 MVCC（Multiversion Concurrency Control）

• MVCC 翻译为“多版本并发控制”，和数据库锁一样，是一种并发控制的解决方案，主要用于解决 读-写 并发的情况。
• MVCC 是一种读取数据的技术，它允许多个事务同时读取同一份数据的不同版本。在 REPEATABLE-READ 隔离级别下，事务在其生命周期内看到的数据版本是一致的，也就是说，事务在开始时看到的数据版本在整个事务过程中都不会改变。

3.4.1.1 数据库中对数据的主要操作分别是读和写，在并发的场景下则会出现三种情况：

• “读-读并发”不会出现问题。
• “写-写并发”则通过加锁解决数据的一致性。
• “读-写并发”则通过MVCC的机制解决。

3.4.1.2 当前读和快照读

• MySQL中只有 RC 和 RR 这两种事务才会使用当前读和快照读。
• 快照读：读取的是快照数据，即普通SELECT语句在不加锁的情况下就是快照读。
• 当前读：读取的是最新数据，即加锁的SELECT，或对数据进行行增删改都会进行当前读。
• 在RR中，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照。
• 在RC中，每次读取都会重新生成一个快照，总是读取行的最新版本。

3.4.1.3 UndoLog

• undo log是MySQL用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到undo log日志文件中，当事务回滚时或者数据库崩溃时，可以利用undo log来进行回退。
• undo log“更新前的数据”就是快照读，即UndoLog是MVCC实现的重要手段。
• 如果一条记录在同一时刻可能会有多个事务在执行，那么，undo log会有一条记录的多个快照，那么在这一时刻发生select要进行快照读的时候，要读取哪个快照呢？这样就需要用到另外几个信息了。

3.4.1.4 行记录的隐式字段

在数据库中的每行记录中，除了保存我们自定义的一些字段以外，还有一些重要的隐式字段：

• db_row_id:隐藏主键，如果我们没有给表创建主键，那么会以这个字段来创建聚簇索引。
• db_trx_id:对这条记录做了最新一次修改的事务ID。
• db_roll_ptr:回滚指针，指向这条记录的上一个版本，其实她指向的就是UndoLog中的上一个版本的快照的地址。
• 以上字段，只有在聚簇索引的行记录中才会有，而在普通的二级索引记录中是没有的。

3.4.1.5 Read View

Read View主要用来解决可见性的问题，即他会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。
在Mysql中，不同的事务隔离级别，比如我们常见的RR和RC，RR要求在一个事务中，多次读取的结果是保持一致的，而RC则要求每次都要读取到最新的值。

• 在可重复读（Repeatabel Read）级别下，快照读（ReadView）在事务开始时创建一次，并在整个事务期间保持不变。
• 在读已提交（Read Committed）级别下，快照读（ReadView）会在每次查询的时候重新创建，以反映数据库中最新提交更改。
  在Read View中有4个重要的属性：
• trx_ids: 表示在生成ReadView时当前系统中活跃的读写事务的事务Id列表。
• low_limit_id: 应该分配给下一个事务的Id的值。
• up_limit_id: 未提交的事务中最小的事务Id。
• creator_trx_id: 创建这个Read View 的事务Id。
  trx_ids 中包含了low_limit_id和up_limit_id的信息，其实 trx_ids = [up_limit_id, low_limit_id](但是需要注意，他并不一定连续，只是会包含up_limit_id，并且小于low_limit_id，左闭右开)。
  也就是说，在读已提交（Read Committed）级别下，每次读取数据时都会生成一个ReadView，并在其中记录上trx_ids（包含[up_limit_id, low_limit_id]）和，creator_trx_id。

eg：如何判断一个事务应该看到哪些快照，不应该看到哪些快照？

假如一个ReadView的内容为：

trx_ids = [5,6,8)
low_limit_id = 8
up_limit_id = 5
creator_trx_id = 7

假设当前事务要读取某一个记录行，该记录行的 db_trx_id 为 trx_id，那么就有以下几种情况：

• 1. trx_id < up_limit_id 即小于5的事务，说明这些事务在生成ReadView之前就已经提交了，那么该事务的结果就是可见的。
• 2. trx_id > low_limit_id 即大于8的事务，说明该事务在生成ReadView后才生成，所以该事务的结果就是不可见的。
• 3. up_limit_id < trx_id < low_limit_id 即大于等于5小于8，则会再拿事务Id和ReadView中的trx_ids进行逐一比较。

a. 如果，事务Id在trx_ids列表中,如6，那么表示在当前事务开启时，这个事务还是活跃的，则这个记录对于当前事务来说不可见。
b. 如果，事务Id不在trx_ids列表中,如7，那么表示在当前事务开启之前，其他事务对数据进行修改并提交了，则这个记录对当前事务是可见的。
c. 如果，trx_id = creator_trx_id ，那么表示这个记录对当前事务是可见（都在当前的同一事务中）。

总结：一个事务，能看到的是在他开始之前就已经提交的事务的结果，而未提交的结果都是不可见的。
所以，当读取一条记录的时候，经过上面的判断，发现记录对当前事务可见，那么就直接返回就行了。那么如果不可见怎么办？就需要用到undo log了，即当数据的事务Id不符合Read View规则时，就需要从 undo log 里面获取数据的历史快照，然后数据快照的事务Id再来和Read View 进行可见性比较，如果找到一条快照则返回，找不到则返回空。

3.4.1.6 二级索引在索引覆盖时如何使用MVCC

储备知识：

• 1.行记录的隐式字段
• 2.在MVCC中通过隐藏字段中的db_roll_ptr(回滚指针)来构建版本链，db_trx_id （对这条记录做了最新一次修改的事务ID）也是一个重要的用来判断快照可见性的字段。
• 3.在Innodb的聚簇索引（主键索引）中，叶子节点上保存的是整行的记录。非聚簇索引（二级索引）中，叶子节点上只保存了主键的信息。
• 4.在一条查询语句中，如果能用到索引覆盖，则会直接使用二级索引进行检索，不会回表。

问题：

• 如果某个查询语句的查询字段都包含在二级索引中，即会走覆盖索引，不需要回表去读取聚簇索引的页记录。但是，版本链的头节点在聚簇索引中，不在二级索引中，通过二级索引的记录无法直接找到版本链。在这种情况下如何使用MVCC？
  MySQL官网描述：https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-transaction-management.html（如果发行二级索引页有一个 page_max_trx_id（表示修改该页的最大事务Id） 太新，或者如果二级索引中的记录被删除标记，InnoDB可能需要使用聚簇索引来查找记录），也就是说，索引覆盖并不是用到了联合索引就一定会去走，在括号中的情况下还是会去回表，通过覆盖索引进行查询。

总结：

在二级索引中，用一个额外的名为page_max_trx_id的变量来记录表示修改过该页的最大事务id。如果当前查询的到的readVidew的up_limit_id > page_max_trx_id ，说明在创建readVidew时，最后一次更新二级索引的事务已经提交了，意味着二级索引里的提交对于当前查询
都是可见的。这时，如果这个二级索引的记录没有被删除，那么就可以直接走索引覆盖查询。否则，就意味着数据可能被修改了，不能直接查询，需要回表，通过聚簇索引进行查询。同时使用聚簇索引时，叶子节点行记录中设计包含了版本链就可以用到MVCC了。

3.4.2 解决的幻读问题：

• RR中，通过MVCC机制，解决了快照读的幻读问题，RR中的快照读只有第一次会进行数据查询，后面都是直接读取快照，所以不会发生幻读。
• RR中，通过间隙锁解决了部分当前读的幻读问题，通过增加间隙锁将记录之间的间隙锁住，避免新的数据插入。

3.4.3 未解决的幻读问题：

• 如果两个事务，事务1先进行select操作（快照读）select -> A,B，然后事务2插入了一条记录并提交事务2add -> C，再在事务1中进行update新插入的这条记录（当前读操作）是可以更新成功的update -> C，这就是发生了幻读。

• 如果两个事务，事务1先进行select操作（快照读）select -> A,B，然后事务2插入了一条记录并提交事务2 add -> C，在事务1中进行了select for update（当前读）之后 select for update -> A,B,C，再进行select（快照读）也会发生幻读 select -> A,B,C。

• 如果两个事务，事务1先进行select操作（快照读）SELECT * FROM users WHERE age = 25; -> A,B，然后事务2插入一条记录并提交事物2（age:唯一索引），再在事务1中进行select操作select -> A,B,C，因为在唯一性索引上插入的新行不会触发间隙锁。

4. 关系型数据库十分强调数据的一致性，并为此降低读写性能付出了巨大的代价

4.1 InnoDB的锁机制

根据不同的锁粒度：

• 行级锁：根据锁粒度区分：记录锁（Record Lock 锁索引记录）、间隙锁（Gap Lock 锁索引记录直接的间隙）、间隙记录锁（Next-Key Lock 同时锁索引记录和间隙，范围左开右闭）、
• 页级锁（InnoDB没有）：
• 表级锁：对表加锁，包含：意向锁、AUTO-INC锁、字典锁、表级排他&共享锁、
• 全局锁：影响整个MySQL实例的锁。FLUSH TABLES WITH READ LOCK这个命令会锁定整个数据库实例所有的表（全局读锁定），主要用于全局备份等操作，执行命令后库实例中的所有表都被锁定为只读。

4.1.1 共享锁和排他锁

Innodb中的锁在锁的级别上一般分为两种，一种是共享锁（S锁），一种是排他锁（X锁）

• 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事物都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁
• 排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁。获得排他锁的事务既能读取数据，又能修改数据。
• 表级排他&共享锁：LOCK TABLES table READ：添加表级共享锁， LOCK TABLES table WRITE：添加表级排他锁。

4.1.2 意向锁（Intention Locks）

当多个事务想要访问一个共享资源时，如果每个事务都直接请求获取锁，那么就可能会导致互相阻塞，甚至导致死锁。
为了解决这个问题，MySQL引入了意向锁机制。所以意向锁上数据库管理系统中用于实现锁协议的一种锁机制，旨在处理不同锁力度（如行锁和表锁）之间的并发性问题。（相同锁粒度（如多个行级锁）之间的并发性问题通过行级互斥锁解决。）

• 意向排他锁（IX锁），表示事务打算在资源上设置排他锁。这表示事务计划修改，并不希望在读取时有其他事务同时设置共享排他锁。
• 意向共享锁（IS锁），表示事务打算在资源上设置共享锁。这通常用于表示事务计划读取资源，并不希望在读取事有其他事务设置排他锁。
• 插入意向锁是一种由插入操作在行插入之前设置的间隙锁。这种锁表明了插入的意图，以这样一种方式，如果多个事务插入到同一索引间隙中但不在间隙内到相同位置插入，则他们不需要互相等待。
  注意：
• 意向锁是一个表级锁（作用范围是针对整个表，而不是针对表中的具体行或页），并且他会在触发意向锁的事务提交或者回滚后释放。
• 意向锁本身不锁定资源，而是未来通知其他事务，以防止它们在资源上设置不兼容的锁。
• 意向锁并不是直接由用户请求的，而是由MySQL管理的。

4.1.3 记录锁（Record Lock）

记录锁是加在索引记录上的锁，是一个典型的行级锁，如果没有索引InnoDB回创建一个隐藏的聚簇索引（隐藏主键row_id），并使用这个索引进行记录锁定。

4.1.4 间隙锁（Gap Lock）

间隙锁是锁索引记录之间的间隙。

4.1.5 间隙记录锁（Next-Key Lock）

是Record Lock和Gap Lock的组合，同时锁索引记录和间隙，他的范围是左开右闭。

注意 MySQL加锁原则为，两个“原则”、两个“优化”和一个“bug”：

• 原则1：加锁的基本单位是next-key lock。是一个前开后闭区间。
• 原则2：查找过程中访问到的对象才会加锁。
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

4.1.6 字典锁（MetaData Locks）

对表结构改变的动作，如ALTER、DROP，他们加锁的过程添加的MDL锁，即字典锁，也叫元数据锁。主要目的是保护数据字典中的元数据不被并发更改。MDL 锁是由MySQL自动管理的，不需要应用程序显式地获取或释放。

4.1.6.1 DML锁的类型：

• MDL_EXCLUSIVE (EX)：排他字典锁，用于写入操作，阻止所有其他类型的 MDL 锁。
• MDL_SHARED (SH)：共享字典锁，用于读取操作，允许其他共享锁，但阻止独占锁。
• MDL_SHARED_UPGRADABLE (SX)：可升级共享锁，用于读取操作，允许其他共享锁，但阻止独占锁。这种锁可以升级为独占锁。
• MDL_INTENTION_EXCLUSIVE (IX)：意向独占锁，表示事务希望在将来获取独占锁。

5. 关系型数据库的数据通常存在硬盘中

5.1. MySQL的数据存储也可以基于内存，即MySQL的内存表技术。它允许将数据和索引存储在内存中，从而提高检索速度和修改数据的效率。创建内存表于创建普通表一样，使用CREATE TABLE语句，但是需要将存储引擎设置为：ENGINE=MEMORY

# 查看mysql当前使用什么存储引擎
show engines;

# 查看mysql当前默认存储引擎
show variables like '%storage_engine%';

# 查看表存储引擎
show create table v_task;

5.2. 数据库存储引擎：数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可以控制访问权限并快速处理事务。

二、注意事项

1.多表join

1.1. join的效率比较低，MySQL使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询，简单来说就是通过两层循环，第一张表做外循环，第二张表做内循环，外循环每一条记录和内循环中的记录作比较符合就输出，eg：2张表join复杂度为O(n^2),3张表join复杂度为O(n^3)

1.1.1 嵌套循环算法（Nested-Loop Join）

• simple nested loop，他的做法简单粗暴，就是全量扫描连接两张表进行数据的两两对比，所以他的复杂度可以认为是N*M
• index nested loop，当Inner Loop的表用到字段有索引的话，可以用到索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是N*logM
• block nested loop，其实是引入了一个Buffer，会提前把外循环的一部分结果提前放到JOIN BUFFER中，然后内循环的每一行都和整个buffer的数据作比较。虽然比较次数还是N*M，但是因为join buffer是基于内存的，所以效率高很多。

1.1.2 不用join如何关联查询

• 在内存中自己做关联，先从数据库吧数据查出来后，代码中再进行二次查询再进行关联。
• 数据冗余，把一些重要的数据在表中做冗余，就可以避免关联查询
• 宽表，就是基于一定的join关系，吧数据库中多张表的数据打平做成一张大的宽表，可以同步到ES或者直接在数据库汇总查询。