MySQL

MySQL
MySQL
发布日期:   2021-03-01
更新日期:   2021-04-29
文章字数:   8.7k

MySQL

1. MySQL 存储概念

关系型数据库。默认端口 3306。

  • 主键：唯一标识一条记录，不能有重复的，不允许为空且只能有一个。
  • 外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值。

1.1 主要存储引擎

  • InnoDB （5.5 后默认引擎），5.7 后只有 InnoDB 支持事务。
  • MyISAM （5.5之前的默认引擎）
1.1.1 InnoDB 与 MyISAM 对比
  • 是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁 (row-level locking)和表级锁，默认为行级锁。
  • 事务支持：只有 InnoDB 支持事务。
    • MyISAM 强调性能，每次查询具有原子性，其执行比 InnoDB 更快，但不支持事务。
  • 崩溃后的安全恢复： InnoDB 能自动进行灾难恢复。
  • 是否支持外键： MyISAM 不支持，InnoDB 支持。
  • 并发度：InnoDB 支持支持 MVCC。
    • 应对高并发事务，MVCC 比单纯的加锁更高效；
    • MVCC 只在 READ COMMITTED 和 REPEATABLE READ两个隔离级别下工作;
    • MVCC 可以使用 乐观锁 和 悲观锁 来实现。

参考：

各种存储引擎之间的对比

InnoDB存储引擎

1.2 基本存储结构

MySQL 的基本存储结构是页，InnoDB 页的默认大小是 16KB，页间组成双向链表，页内的记录组成单向链表。

MySQL基本存储结构

2. MySQL 索引

参考：MySQL 索引机制背后的隐藏之道

2.0 为什么使用索引

索引是为了用一定的形式来组件键的顺序，用于在存储引擎中快速查找记录。

  1. 索引用于减少服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表的创建
  3. 索引可以把随机 I/O 变成顺序 I/O

2.1 磁盘预读

2.1.1 磁盘读取

现代磁盘有多个盘面，磁盘结构从上至下分为：盘面、磁道、扇区。

磁盘中的磁盘控制器是一个固件设备，维护了磁盘的逻辑块号与实际物理扇区之间的映射关系。

磁盘 I/O 的过程：执行磁盘 I/0 时，发送命令给磁盘控制器，读取某个逻辑块号，磁盘控制器把逻辑块号翻译为盘面、磁道、扇区三元组，由这个三元组唯一标识一个物理扇区，然后驱动器移动读写头到指定位置进行数据读取。

  • 数据读取的时间包括寻道时间、旋转时间和传送时间。

2.1.2 局部性原理

局部性原理: CPU 访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中。

  • 时间局部性（Temporal Locality）：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。

  • 空间局部性（Spatial Locality）：在最近的将来将用到的信息很可能与现在正在使用的信息在空间地址上是临近的。

2.1.3 磁盘预读

根据局部性原理，为了减少磁盘 I/O 次数，采用磁盘预读来加快程序的运行效率。即每次读取数据时，会从磁盘的某个位置开始，顺序向后读取一定长度的额外数据。

  • 磁盘预读的长度一般为页 Page 的整数倍。页是计算机管理存储器的逻辑块，主内存和磁盘之间以页为单位来进行数据交换。
    • 当 CPU 需要的数据不在主存中时，会触发一个缺页异常，从而向磁盘发出读盘信号，从所需数据的起始位置向后连续读取一页或几页，然后异常返回，程序继续执行。
    • MySQL 数据库设计为 B- 树的形式，每个结点大小正好一个页，这样每个结点只需要一次磁盘 I/O 就能完全加载。

2.2 MySQL 支持的索引

MySQL 支持的索引包括 B- Tree 索引、哈希索引、空间数据索引、全文索引。

2.2.1 B- Tree 索引

B- Tree 即 B Tree，但内部实现常用 B+ Tree。索引树种的结点是有序排列的。

InnoDB 在内的大多数 MySQL 存储引擎都支持 B+ Tree 索引。

为什么采用 B+ 树？

从 B+ 树的结构中可知，如果树高是 h 的话，访问一个叶子节点需要访问 h 个节点。考虑索引实际上存储在磁盘上，载入索引节点的过程需要经历磁盘 I/O，B+ 树由于出色的高度控制，导致 h 的值不会太大，一般来说百万数量级可以控制在 2 ~ 4 左右，意为访问节点的数量主需要 2 ~ 4 个。

数据库系统的设计者又巧妙利用了磁盘预读原理，将一个节点的大小设置成一个页，这样每个节点只需要一次磁盘 I/O 就可以载入主存。这样的话，B+ 树访问一个叶子节点需要 h-1 次磁盘 I/O 就可以，因为其根节点是常驻内存的，极大减少了磁盘 I/O 次数，提高了索引结构的效率。

可以使用 B- Tree 索引的查询类型

  1. 全值匹配：即查询条件与索引中的所有列进行匹配

  2. 范围查找

  3. 前缀查找

    • 匹配最左前缀，即只使用索引的第一列
    • 匹配列前缀，即匹配某一列的值的开头部分

2.2.2 哈希索引

哈希索引方式基于哈希表实现，哈希索引只对精确匹配所有列的查询才有效，因此不能用于范围查询。好处在于查询的时间复杂度为 O(1)。

实现方法为：对于每一行数据，存储引擎都会基于所有的索引列计算出一个哈希码，哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

MySQL 中只有 Memory 引擎显式支持哈希索引。

自适应哈希索引

InnoDB 支持自适应哈希索引。当某些索引使用频繁时，InnoDB 会在内存中基于 B- Tree 索引之上再创建一个哈希索引。

  • 这种创建行为是自动进行的，用户无法进行配置。但可以手动关闭该功能。
哈希索引的缺点
  1. 索引中质保函哈希值和行指针，而不存储字段值。所以不能使用索引来避免对行的读取。
  2. 哈希索引的数据是无序的，因此无法用于排序。
  3. 哈希索引不支持部分匹配，因为每次计算哈希值都需要用到所有的索引列。
  4. 哈希索引只支持等值比较，如 =,IN(),<=>。哈希索引也不支持范围查询，如 >。
  5. 当存在哈希冲突时，存储引擎必须遍历链表中的所有行指针来进行逐行比较。这导致如果哈希冲突很多的话，索引的维护代价也很高。

2.2.3 空间数据索引

空间索引是指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构，其中包含空间对象的概要信息，如对象的标识、外接矩形及指向空间对象实体的指针。

  • 空间索引从所有维度来索引数据，无需类似于 B- Tree 的前缀查询。

  • MySQL 使用 R 树来支持空间索引。

  • MyISAM 支持空间索引，来存储地理数据。

2.2.4 全文索引

全文索引通过匹配关键词来进行查询过滤，而不是直接比较索引中的值。

  • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 引擎均支持全文索引。
  • 只有 char、varchar、text 等字符内容才支持全文索引。
全文索引的语法

全文索引采用 match 和 against 关键字来实现自己的语法。

select * from fulltext_test 
    where match(列1, 列2) against('xxx xxx');

在数据表上创建全文索引：

create fulltext index content_tag_fulltext
    on fulltext_test(列1, 列2);

参考：

MySQL 之全文索引

2.3 InnoDB 的索引

InnoDB 中根据主键来组织聚簇索引表，完整的数据记录储存在主键索引中。

2.3.1 聚簇索引（主键索引）

聚簇索引以主键为键值进行索引，采用B+ 树来作为底层索引结构。

聚簇是指数据行与相邻的键值紧凑存储在一起。相邻的索引对应的数据在磁盘上也是相邻的。

  • 一张表只允许存在一个聚簇索引。
  • 聚簇索引的叶子结点就是数据结点。
    • 叶子结点上包含主键值、事务 ID、用于事务 MVCC 的回滚指针以及所有的剩余列。
  • 非单调的主键会在插入记录时为了维护 B+ 树的特性而导致数据文件频繁进行分裂调整，降低效率，所以最好采用自增的主键来避免数据的频繁移动。
InnoDB 聚簇索引

2.3.2 非聚簇索引

非聚簇索引即辅助索引、二级索引。实质是聚簇索引的二级索引。底层实现是 B+ 树。

  • 与聚簇索引的区别是，非聚簇索引的叶子结点存放主键值。

查询过程：先用辅助索引查询到主键，再根据主键去聚集索引中查找对应的数据记录。该过程叫回表。

  • 叶子中存的是主键值，不是行记录的指针。
  • 优势在于减少了移动数据或数据页分裂时对维护二级索引的开销，因为不需要去更新数据行记录指针。

2.4 MyISAM 的索引

MyISAM 引擎中，采用非聚簇索引方式来进行索引。

  • 索引结构为 B+ 树 ，叶子结点存放数据记录的行指针地址。

  • 索引文件和数据文件是独立的，无论是对于主键索引还是辅助索引。

  • 主键索引和其他列的辅助索引在结构上没区别，主键索引就是一个名为 Primary 的索引，要求唯一且非空。该引擎中可以不设置主键。

查询步骤：在 B+ 树 中按字段查询到叶子结点，如果对应的键存在，则根据行指针的值去读取行记录。

参考：

MYSQL索引：对聚簇索引和非聚簇索引的认识

2.5 联合索引与最左前缀原则

2.5.1 联合索引

联合索引是指由多个字段组成一条索引。

在 MySQL 中创建联合索引：

CREATE INDEX 联合索引名 on 表名 (列1, 列2, 列3);
联合索引的创建规则

首先对联合索引的最左边第一个字段进行排序，然后在第一个字段相等的基础上，对第二个字段进行排序，依次进行…

对一个联合索引 (a, b, c)，实际上是创建了 (a), (a,b), (a,b,c) 三个索引。所以减少了写操作与磁盘空间的开销。

2.5.2 最左前缀原则

最左前缀原则是联合索引的检索规则，即从联合索引的最左边开始匹配。

MySQL 中的查询优化器 explain，会自动纠正字段顺序来使运行效率最高。

  1. 联合索引的查询与 WHERE 条件中字段的顺序无关，只与字段名有关；
  2. 只要 WHERE 条件中包含联合索引的第一个字段即可让查询走联合索引。 
-- 假如说建了 (b, c, d) 联合索引

select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
select * from T1 where b = 12  and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化
select * from T1 where b = 12  and d = 3;-- 应用到一列索引  因为不能跨列使用索引 没有c列 连不上
select * from T1 where c = 14  and d = 3;-- 无法应用索引，违背最左匹配原则

2.5.3 覆盖索引

当要查询的列都在索引树中时，可以直接通过索引树来取得数据，而不需要回表。

  • 联合索引中拥有的内容包括联合索引的列、主键值。

对上面建的表，有联合索引(a, b, c)，比如执行如下的查询语句：

select a, b, c from test where a = 1 and b = 2;

因为 a,b,c 都属于联合索引中的列，所以无需回表。

参考：

最左前缀匹配原则

覆盖索引

2.6 索引和锁

索引可以让查询锁定更少的行。

当索引列有唯一约束时，next-key lock 会退化为行锁，减小粒度。

3. 事务

事务由一条或多条 SQL 语句组成，一个事务是一个不可分割的整体，具有原子性。

事务结束有两种状态：

  1. 提交 commit
  2. 回滚 rollback

3.0 MySQL 事务

MySQL 事务与储存引擎有关：

  • MyISAM：不支持事务，用于只读程序提高性能
  • InnoDB：支持 ACID 事务、行级锁、并发

MySQL 默认自动提交，对 MySQL 的 DML 操作都会被当做事务自动提交；

  • 可以使用 START TRANSACTION 来显式的开始一个事务，这样不会自动提交事务
  • 关闭 MySQL 的自动提交: set autocommit=0

3.0.1 事务的分类

事务分为：

  • 扁平事务：最普通的事务，事务中的所有操作处于同一层次。
    • 主要缺点是不能分段提交或回滚，只能作为整体。
  • 带有保存点的扁平事务：在同一个扁平事务中，使用保存点来保存事务不同阶段的状态。
    • 支持回滚到该事务中的任意一个保存点。
  • 链事务：在 commit 提交一个事务时，会释放不再需要的数据对象，并将必要的处理上下文来隐式地传递给下一个事务。此时上一个事务的提交和下一个事务会合并为一个原子操作，即下一个事务会看到上一个事务的结果。
    • 链事务回滚时只能回滚到最近的一个保存点。
    • 当前事务 commit 后会释放所持有的全部锁。
  • 嵌套事务：父事务中嵌套子事务。
    • 子事务的提交不会立即生效，任何子事务的提交都会在顶层事务被提交后才算真正被提交了。
    • 父事务的回滚会导致其下的所有子事务同样回滚。
  • 分布式事务：指在分布式环境下运行的扁平事务，会根据数据位置来分别访问网络中的不同结点，来执行不同的事务。
    • MySQL 5 开始支持分布式事务。

3.1 事务的 ACID 特性

  • 原子性 Atomicity： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么全部失败；
  • 一致性 Consistency ： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  • 隔离性 Isolation： 并发访问数据库时，一个事务所做的修改在最终提交以前对其他事务不可见；
  • 持久性 Durability：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.2 MySQL 事务日志

事务的隔离性是通过锁来实现，事务的原子性、一致性和持久性是通过事务日志来实现。

3.2.1 redo log

redo log 即重做日志。

redo log 包括内存中的日志缓冲 log buffer 和磁盘上的日志文件 redo log file。

  • redo log file 是一块连续的磁盘空间，以记录追加的方式来保存日志，这样的顺序 I/O 可以提高性能。

事务的执行与提交过程：

  1. 事务开启时，事务中的操作，都会先写入内存的日志缓冲 log buffer 中；
  2. 在事务提交之前，执行日志先行，把日志缓冲持久化到磁盘上；
  3. 当事务提交之后，在 Buffer Pool 中的数据才会刷新到磁盘；
    • 此时如果数据库崩溃，那么当系统重启可以根据日志文件来恢复之前的状态。此时未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

3.2.2 undo log

undo log 为事务回滚服务。它会记录数据在每个操作之前的数据状态。

undo log 内部维护了回滚段 rollback segment，每个回滚段包括 1024 个 undo log segment，每个 undo 操作占用一个 undo log segment。

3.3 并发事务的问题

  • 脏读 dirty read： 一个事务读取了另一个事务未提交的数据。（这个数据修改在后面可能会被撤销）
  • 丢失修改 lost to modify：两个事务接连对同一条数据进行修改，第一个事务的修改结果被第二个事务所覆盖。
  • 不可重复读 Unrepeatable read：某事务多次读同一数据，在该事务还没结束时，另一事务对该数据进行了修改，导致第一个事务的两次读数据得到结果不同。
  • 幻影读 Phantom read：与不可重复读类似，区别是数据记录被新插入或删除，导致读取的范围数据与之前不同。

3.4 MySQL 的事务隔离级别

下面的隔离级别依次提升：

  1. NONE ：无事务
  2. READ-UNCOMMITTED (读未提交) ：允许读取尚未提交的数据。可能会导致脏读、幻读或不可重复读。
  3. READ-COMMITTED (读已提交)：仅允许读取已提交的数据。不会发生脏读，但是可能发生不可重读和幻影读。
  4. REPEATABLE-READ (可重复读) ：只允许读取已提交数据，且一个事务的两次读取的间隙不得发生其他事务对该数据的更新，除非数据是被本身事务自己所修改。可以阻止脏读和不可重复读，但幻读仍有可能发生。
    • MySQL 的 InnoDB 存储引擎的默认事务隔离级别是可重复读
  5. SERIALIZABLE (可串行化读) ：多线程访问数据时需要串行化访问，每次只能允许一个线程访问数据。可以防止脏读、不可重复读以及幻读。
隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED √ √ √
READ-COMMITTED × √ √
REPEATABLE-READ × × √
SERIALIZABLE × × ×

在 MySQL 中修改事务隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

InnoDB 默认隔离级别是 REPEATABLE-READ，但由于使用的是 Next-Key Lock 锁算法，间隙也会被锁定，因此在可重复读的基础上避免了幻读。

当 InnoDB 扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

4. 数据库中的锁

4.0 数据库锁分类

4.0.1 Latch 闩锁

Latch 是轻量级的锁，要求锁定时间必须很短。用来保证并发线程操作邻接资源的正确性，通常没有死锁检测机制。

在 InnoDB 中，Latch 分为 互斥量 Mutex 和 读写锁 rwLock。

4.0.2 Lock 事务锁

Lock 锁定的对象是数据库中的对象，如表、页、行。按锁的粒度分别对应表锁、页锁、行锁。

Lock 仅在事务 commit 或 rollback 后进行释放。

4.1 InnoDB 存储引擎中的锁

4.1.1 锁的粒度

按锁的粒度分别对应表锁、页锁、行锁。

  • 表锁：加锁的开销小，加锁快，不会发生死锁；但发生锁冲突的概率最高，并发度低。
    • 表锁主要应用在 MyISAM 等非事务引擎。
  • 页锁：加锁的开销介于表锁和行锁之间；会出现死锁，并发度一般。
    • 主要应用在 BerkeleyDB 中。
  • 行锁：并发度最高；但加锁的开销大，加锁慢，且会出现死锁。

表锁更适合以查询为主的应用；行级锁则更适合于并发修改少量数据的场景。

不同引擎的锁粒度支持

4.1.2 锁的类型

  • 共享锁 Shared Lock：读锁，S 锁
  • 排他锁 Exclusive Lock：写锁，X 锁
行级读写锁

InnoDB 支持标准的行级锁，分为行级读锁与写锁。允许事务读写一行数据。

意向锁

行锁与表锁可以共存，意向锁是指 InnoDB 中的表级锁。意向锁同样分为意向共享锁（IS 锁）与意向排他锁（IX 锁）。

意向锁之间的兼容性

4.1.3 一致性锁定读与一致性非锁定读

InnoDB 实现数据隔离的两种方式：

  1. 加读写锁，即一致性锁定读
  2. MVCC， 即一致性非锁定读
一致性锁定读

对于 InnoDB 的读操作，提供两种类型的锁定读来保证并发安全：

  • SELECT ... LOCK IN SHARE MODE：一个事务读时加 S 锁，其他事务也可以继续加 S 锁，但 X 锁会被阻塞。
  • SELECT ... FOR UPDATE：一个事务对其所查询的行及相关的索引记录加 X 锁，其他事务的 S 锁和 X 锁都会阻塞。
    • 只有在自动提交禁用后，SELECT ... FOR UPDATE 才会锁定行；否则不会锁定。

当事务提交后，这两种锁都会被释放。这两种锁都是行锁，会对查询出来的每条记录加行锁。

对于不加这两种语句的查询，都叫做普通查询，因此不会在查询时加锁。也叫一致性非锁定读。

走当前读的模式：

select * from table where ? lock in share mode; 
select * from table where ? for update; 
insert; 
update; 
delete;
一致性非锁定读 (多版本并发控制 MVCC)

普通读是走 MVCC。

select * from xx ...

指 InnoDB 通过多版本并发控制 MVCC 来读取行数据。读数据时不会占用和等待行上的锁，比加锁更有效，并发度更高。

  • 这是 InnoDB 的默认数据读取方式。
  • MVCC 在 Read Committed 和 Repeatable Read 两个隔离级别下起作用。
    • 在 Read Committed 隔离级别下，在同一个事务中，一致性非锁定读总是会读取其他事务最新提交的数据。
    • 在 Repeatable Read 隔离级别下，在同一个事务中，一致性非锁定读总会在第一次读取时生成一份数据快照，之后的读取都是基于这份数据快照。因为该隔离级别要保证多次数据读取的一致性。
    • 对于 Read Uncommitted 则直接可以读取未提交的数据，而 Serializable 则需要加锁。因此这两种都用不到 MVCC 机制。
  • MVCC 可以基于乐观锁和悲观锁来实现。InnoDB 中的 MVCC 机制是通过乐观锁来实现的。

如果一个事务想要读取的行正在被其他事务执行 delete 或 update，则该事务不会去等待锁的释放。而是会读取行的一个快照，从而提高并发度。

InnoDB 中 MVCC 的实现原理

MVCC 通过可读视图 ReadView 来实现不同隔离级别下、事务之间数据版本的可见性。对于不同的隔离级别，ReadView 生成和更新的时间点不同：

在 RR 级别下，在本事务执行第一个 SELECT 语句时，会将当前系统中的所有活跃事务拷贝到一个列表中，生成一个 ReadView，而该事务中的后续所有 SELECT 操作都是基于该 ReadView 来读取。

在 RC 级别下，每当本事务执行一个 SELECT 语句时，都会重新将当前系统中的所有活跃事务拷贝到一个列表中，重新生成 ReadView。

  • 每次新生成 ReadView 时，总会把当前的活跃事务放入其中，如果之前的事务已经提交，则表示其不再活跃，那么该事务的提交就会对当前事务可见。
表中行记录的结构
行记录的结构

每行记录存在两个隐藏列：

  • DATA_TRX_ID：最近更新该行记录的事务 id，占 6 字节。
  • DATA_ROLL_PTR：指向该行记录的回滚段的指针，占 7 字节。通过该指针可以找到旧版本的数据，而旧版本的数据在 undo log 中以链表的形式组织起来。

其他额外的列包括 DB_ROW_ID，是在表没有显式的主键时添加的隐藏主键。这时InnoDB会以该字段建立聚簇索引。

每条行记录的头信息 Record Header 中会有一个 bit 来指示当前的记录是否已经被删除。

  • InnoDB 的 delete 是假删除，删除后的数据被标记为删除。之后再次插入数据时，可以复用该条行记录。
  • 使用 optimize table t 会重新建立这张表，先建立一张临时表，然后从原始表中查询出数据并插入到该临时表中，再用临时表替换原始表，达到数据彻底删除的目的。
Undo Log 链的组织
undo log链的组织

某事务对 DB_ROW_ID = 1 的这行记录进行 update 的操作过程为：

  1. 对 DB_ROW_ID = 1 的这行记录加排他锁；
  2. 把该行原本的值拷贝到 undo log 中，DB_TRX_ID 和 DB_ROLL_PTR 都不动
  3. 该事务修改这条记录的值，产生一个新版本，更新 DATA_TRX_ID 为本事务的 ID，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE，Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁。
  4. 记录 redo log，包括 undo log 中的修改。
ReadView 可读视图

ReadView 是一个当前活跃事务的 id 列表，叫做 m_ids。该列表中最小值为 low_limit_id，最大值为 up_limit_id。列表中的事务 id 是事务开启时 InnoDB 自动分配的，事务 id 的大小决定了事务开启的先后顺序，越大的 id 表示事务开启的时间越晚。利用 ReadView 来判断事务数据的可见性时：

  1. 如果被访问版本的 trx_id 小于 m_ids 中的最小值 low_limit_id，说明生成该版本的事务在 ReadView 生成前就已经提交并结束了，所以该版本可以被当前事务访问。
  2. 如果被访问版本的 trx_id 大于 m_ids 列表中的最大值 up_limit_id，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。当前事务需要根据该数据的 Undo Log 链找到前一个版本，然后根据该版本的 DATA_TRX_ID 重新判断可见性。
  3. 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大值和最小值之间（包含），则判断 trx_id 的值是不是在 m_ids 列表中：
    1. 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链来得到上一个版本，然后根据该版本的 DB_TRX_ID 再重新判断可见性；
    2. 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
  4. 此时经过一系列判断我们已经得到了这条记录相对 ReadView 来说的可见结果。此时，如果这条记录的 delete_flag 为 true，说明这条记录已被删除，不返回。否则说明此记录可以安全返回给客户端。

4.1.4 行锁算法

加锁是对索引加锁，不是对数据加锁。行锁是在走索引的时候才会加行锁。

InnoDB 中的行锁算法有 3 种：

  • Record Lock 行锁：单个行索引记录上加锁。
  • Gap Lock 间隙锁：锁定一个范围，不包括记录本身。针对可重复读及以上事务隔离级别。
    • 为了阻止并发时将记录插入到某一范围内，防止了幻读问题。
    • 向左扫描到第一个比给定参数小的值， 向右扫描到第一个比给定参数大的值， 以此构建一个区间来作为间隙。
  • Next-Key Lock 后码锁：结合了前两种锁，锁定一个范围，该范围包含记录本身，即锁定区间前开后闭。默认情况下， InnoDB 对行的查询就是使用该锁。
    • 当 InnoDB 中更新非唯一索引对应的记录时，会加该锁；
    • 当查询的列是唯一索引的情况下（如主键索引），Next-Key Lock 会降级为 Record Lock；
    • Previous-Key Lock：锁定区间是前闭后开。

InnoDB 扫描索引记录时，会首先对选中的索引行记录加上行锁，再对索引记录两边的间隙加上间隙锁。

锁与mvcc—这个讲的好

4.1.5 自增锁 AUTO_INC LOCKING

自增锁是特殊的表锁。如果表中存在自增字段 AUTO_INCREMENT，则 MySQL 会自动维护自增锁。

  • 当存在自增锁时，所有的其他插入都要等待当前插入操作的完成。

  • 自增锁会在完成带自增长值的插入语句后立即释放锁，而不是等事务完成后才释放锁。

  • 可以通过 innodb_autoinc_lock_mode 来控制自增长的模式。

为什么要设置自增主键？

当主键自增时，能保证插入的记录主键是连续的。当插入连续的值时，B+ 树每次都是插入到最后面，可以有效减少索引页的分裂和移动。

4.1.6 乐观锁与悲观锁

1. 悲观锁

悲观锁总是假设最坏的情况，每次在拿数据的时候都会上锁，别的线程会阻塞，直到之前拿到锁的线程释放锁。

  • 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
  • 悲观锁适用于多写场景。
2. 乐观锁

乐观锁总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用 版本号机制 和 CAS 算法 实现。

  • 乐观锁适用于多读的应用类型，这样可以提高吞吐量。
版本号机制

在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。

  • 当数据被修改时，version 值会加一。当线程 A 要更新数据值时，在读取数据的同时也会读取 version 值。
  • 在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新；否则认为之前读到的是过期数据，重试直到更新成功。
CAS 算法

CAS 即 compare and swap，比较并交换，是一种有名的无锁算法。

CAS 算法涉及到三个操作数： 需要读写的内存值 V、进行比较的值 A、拟写入的新值 B。当且仅当 V 的值等于 A 时，CAS 通过原子方式用新值 B 来更新 V 的值；否则自旋。

乐观锁的缺点
  1. ABA 问题：A被改为B，然后又被改回A，但 CAS 误认为A没被修改过。
  2. 循环开销大：自旋CAS如果长时间不成功，会给CPU带来非常大的执行开销。
  3. 只能保证一个共享变量的原子操作：CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但可以使用 锁 或者利用 AtomicReference 类把多个共享变量合并成一个对象来进行 CAS 操作。

5. MySQL 缓存

5.1 MySQL 判断缓存命中

缓存是被存放在一个引用表中，通过一个哈希值对其引用，该哈希值包含了查询本身、当前所要查询的数据库、客户端协议的版本等。当判断缓存是否命中时，MySQL 不会去解析、正规化、或参数化该查询语句，而是直接使用 SQL 语句和客户端传来的其他原始信息。

  • 任何字符上的不同，包括空格、注释等，都会导致缓存的不命中。所以要使用统一的编码规则。
  • 在查询缓存前，MySQL 还未解析 SQL 语句，所以只是通过一个大小写不敏感的检查来查看 SQL 语句是否以 SEL 开头。

5.2 缓存添加

MySQL 查询的过程：

  1. 读查询之前，先检查缓存是否命中；
  2. 如果没命中，查数据库。如果这个读查询可以被缓存，且查询缓存中没有该查询，则将查询结果存入查询缓存。

包含不确定函数的查询语句的查询结果，不会被放入缓存中。

5.3 缓存失效

对数据库表的写操作会影响缓存。MySQL 缓存查询系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和该表相关的所有缓存数据都将失效。

当一个事务正在执行对某个表的写操作时，在这个事务被提交之前，对这个表的所有相关查询都无法被缓存，直到该事务被提交后，查询才能被缓存。

当查询缓存的内存用完后，会逐出之前的某些缓存来为新的查询腾出空间。

5.4 缓存内存管理

缓存所占用的内存是由 MySQL 自己管理的，不依赖操作系统的内存管理。

5.5 InnoDB 与查询缓存

Reference

可能是全网最好的MySQL重要知识点

MySQL索引-B+树

【B+树】：MySQL数据库索引是如何实现的？

MySQL 事务

MySQL中的锁

MySQL 索引–讲的很好

文章作者: Yu Yang
文章链接: https://Zgxh.github.io/2021/03/01/mysql/mysql%E5%8E%9F%E7%90%86/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Yu Yang !
MySQL
 上一篇
HTTP 超文本传输协议 HTTP 超文本传输协议
HTTP 整理1. 概念整理1.1 URI 和 URL URI：Uniform Resource Indentifier 统一资源标识符，用于唯一标识一个网络资源 URL：Uniform Resource Locater 统一资源定位符，用
2021-03-01 专业课
计算机网络
下一篇 
HashMap 源码分析 HashMap 源码分析
HashMap 源码分析1. HashMap 概述HashMap 与 HashTable 对比 继承关系：HashTable 继承自 Dictionary 类，HashMap 继承自 AbstractMap 类 线程安全性：HashMap
2021-02-21 开发
Java
  目录