Java知识点整理 3 — 数据库

Phoenixxxxxxxxxxxxx

已于 2024-06-18 16:18:09 修改

阅读量1.2k

点赞数 30

分类专栏： Java 文章标签： java 开发语言

于 2024-03-01 16:57:19 首次发布

本文链接：https://blog.csdn.net/konghaoran1/article/details/136388168

版权

Java 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

一. MySQL数据库基础

MySQL是一款开源免费并且比较成熟的关系型数据库。

优点：

成熟稳定，功能完善。
开源免费。
文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。
开箱即用，操作简单，维护成本低。
兼容性好，支持常见的操作系统，支持多种开发语言。
社区活跃，生态完善。
事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。
支持分库分表、读写分离、高可用。

1.MySQL字段类型

MySQL 字段类型可以简单分为三大类：

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

整数类型的UNSIGNED属性有什么用？

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

（1）CHAR和VARCHAR的区别？

CHAR 是定长字符串，VARCHAR 是变长字符串。

（2）VARCHAR(100)和VARCHAR(10)的区别？

它们分别最多能存储100个和10个字符，因此100能满足更大范围的字符存储需求。虽然它们能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的。但VARCHAR(100)会消耗更多内存。

（3）DECIMAL和FLOAT/DOUBLE的区别？

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

（4）为什么 MySQL 不建议使用 NULL 作为列默认值？

空间浪费：当一个列的默认值设置为 NULL 时，每行数据都会占用额外的存储空间用于表示该列的空值。这会导致数据表占用更多的磁盘空间，特别是当表的规模很大时，空间浪费会变得明显。
索引问题：当一个列的默认值设置为 NULL 时，该列的索引会变得更加复杂。在 MySQL 中，B-tree 索引不包含 NULL 值，因此将 NULL 作为默认值会导致索引变得更大、更长，查找和维护索引的性能也会受到影响。
不明确的语义：使用 NULL 作为默认值会模糊列的语义，不清楚 NULL 代表什么含义。例如，一个名称列的默认值为 NULL，那么不清楚 NULL 表示该列缺失值还是未知值，可能会引起混淆和错误的解读。
查询复杂性：在查询时，如果一个列的默认值为 NULL，则需要额外的条件判断以处理 NULL 值的情况。这增加了查询的复杂性和开发的难度。
补充：' ' 空字符串长度为0，不占用空间；在查询时可以使用=、<、>、!=之类的比较运算符，而NULL需要使用IS NULL或者IS NOT NULL来判断。

（5）MySQL中没有专门的布尔类型。

通常会用TINYINT(1)来表示，它可以存储0或1，用来对应false和true。

2.MySQL基础架构

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎

一个 SQL 语句在 MySQL 中的执行流程是什么？

分别经过连接器、查询缓存、分析器解析SQL、优化器优化SQL、执行器执行SQL。

3.MySQL存储引擎

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

（1）MySQL存储引擎架构。

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

（2）MyISAM和InnoDB的区别？

是否支持行级锁。MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。也就是MyISAM只能锁住整张表，而InnoDB可以锁住某行。
是否支持事务。MyISAM不支持事务，InnoDB支持事务，并且实现了 SQL 定义的四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别，可以解决幻读问题的发生（基于 MVCC 和 Next-Key Lock）。
是否支持外键。MyISAM 不支持，而 InnoDB 支持。外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，不建议在实际生产项目中使用外键，在业务代码中进行约束即可。
是否支持数据库异常崩溃后的安全恢复。MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动时会保证数据库恢复到崩溃前的状态。恢复过程依赖于数据库的redo log。
是否支持 MVCC。MyISAM 不支持，而 InnoDB 支持。MVCC是多版本并发控制的缩写，是一种处理并发访问的技术。MVCC 通过在每个数据行中保存多个版本，以及使用时间戳或版本号来标识不同的版本，以实现对数据的并发访问控制，提高了并发性能。
索引实现不一样。它们都采用B+ Tree作为索引结构，但实现方式不一样。InnoDB 引擎中，其数据文件本身就是索引文件。而MyISAM，索引文件和数据文件是分离的。InnoDB表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。
性能差别。InnoDB 的性能比 MyISAM 更好，不管是在读写混合模式还是只读模式，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

二. 索引

索引是一种用于快速排序和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引不一定能带来查询性能的提升。大多数情况下，索引查询比全表扫描要快，但如果数据库的数据量不大，索引带来的性能提升会非常有限。

1.索引底层数据结构

Hash表、二叉查找树(BST)、AVL树、红黑树、B树、B+树等。InnoDB和MyISAM都使用B+树作为索引的数据结构，只是实现方式不同。

MySQL中的索引有哪些？

（1）按数据结构划分：【MySQL支持这四种】

B+树索引：叶子节点存value，非叶子节点存指针和key。MySQL中的默认索引类型。
哈希索引：底层数据结构是用哈希表实现，一次就可以定位。
R树索引：空间索引，主要用于地理空间数据，优势在范围查找，效率低，通常使用搜索引擎如ES代替。
全文索引：是一种通过建立倒排索引，快速匹配文档的方式。类似于ES (对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替)

（2）按应用划分：【MySQL数据库索引的具体类型主要分为以下4类】

主键索引：针对于表中主键创建的索引，只有一个。关键字：PRIMARY
唯一索引：避免同一个表中某数据列中的值重复，可以有多个（可以有 NULL）关键字：UNIQUE
常规索引：用来快速定位某些数据，可以有多个
全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个，关键字：FULLTEXT
覆盖索引：一个索引包含（或说覆盖）要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

（3）按底层存储方式划分：

聚簇索引：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MyISAM 引擎，不管主键还是非主键，都使用非聚簇索引。

B树与B+树的区别：

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率稳定，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

2.主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

3.二级索引

二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

4.聚簇索引与非聚簇索引

（1）聚簇索引

聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

① 优点：

查询速度快，定位到索引的节点，就相当于定位到了数据，相比非聚簇，少了一次读取数据的IO操作。

② 缺点：

依赖于有序的数据。
更新代价大。

（2）非聚簇索引（二级索引、辅助索引）

非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

①优点：

更新代价小(因为非聚簇的叶子节点不存放行数据)。

②缺点：

依赖于有序的数据。
可能会二次查询(回表)：这是非聚簇的最大缺点，当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

补充：

聚集索引叶子节点下面挂的是这一行的数据
二级索引叶子节点下面挂的是该字段值对应的这一行数据的主键值（这里就是id）
对主键字段建的索引叫聚簇索引，对普通字段建的索引叫二级索引，多个普通字段组合在一起建的索引叫联合索引（组合索引）。

5.回表查询

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取行数据的方式，称为回表查询。

6.覆盖索引和联合索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

使用表中的多个字段创建索引，就是 联合索引。

这是一score和name两个字段建立的联合索引。

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左前缀匹配原则：

在使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会用该字段过滤一批数据，直到联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配（范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引）。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，在用联合索引时，可以将区分度高的字段放在最左边，过滤更多数据。

7.通过例子说明

MySQL 的 InnoDB 存储引擎会为每一张数据库表创建一个聚簇索引来保存表的数据，聚簇索引默认使用的是 B+Tree 索引。

假设有一张商品表，表里有这些数据：

聚簇索引的 B+Tree 如图所示：

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键值（id）顺序存放的，每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表，便于范围查询。

假设，执行了 select * from t_products where id = 5 查询语句，该查询语句的条件是找到 id（主键）为 5 的这条记录。因为 B+Tree 是一个有序的数据结构，所以可以通过二分查找算法快速定位到这条记录，这也就是我们常说的索引查询，具体过程如下：

从根节点开始，将 5 与根节点的索引数据 (1，10，20) 比较，5 在 1 和 10 之间，根据二分查找算法，找到第二层的索引数据 (1，4，7)；
在第二层的索引数据 (1，4，7)中进行查找，因为 5 在 4 和 7 之间，根据二分查找算法，找到第三层的索引数据（4，5，6）；
在叶子节点的索引数据（4，5，6）中进行查找，然后我们找到了索引值为 5 的这条记录。

聚簇索引只能用于主键字段的快速查询，如果想实现非主键字段的快速查询，我们就要针对非主键字段创建索引，这种索引称作为二级索引。二级索引同样基于 B+Tree 实现的，不过二级索引的叶子节点存放的是主键值，不是实际数据。

这里将前面的商品表中的 product_no （商品编码）字段设置为二级索引，那么二级索引的 B+Tree 如下图，其中非叶子的索引值是 product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。

如果执行如下查询：

select * from product where product_no = '0002';

会先在二级索引的 B+Tree 中快速查找到 product_no 为 0002 的二级索引记录，然后获取主键值，然后利用主键值在主键索引的 B+Tree 中快速查询到对应的叶子节点，然后获取完整的记录。这个过程叫回表，也就是说要查两个 B+Tree 才能查到数据。如下图：

不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引，比如下面这条查询语句：

select id from product where product_no = '0002';

这种在二级索引的 B+Tree 就能查询到结果的过程就叫作覆盖索引，也就是只需要查一个 B+Tree 就能找到数据。

因此，并非所有非聚簇索引都需要回表查询。用上面的查询例子来说，用户想去查询product_no ='0002'的id列，但此二级索引中的叶节点存放就是主键值，即id值，那么可直接查询得到，无需进行下一步的主键索引查询。

此外，如果将商品表中的 product_no 和 name 字段组合成(product_no, name)，那么这种二级索引就被称为联合索引。创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引的非叶子节点用两个字段的值作为 B+Tree 的索引值。

联合索引的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以利用联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

如果联合索引（a,b）:

可以看到，a 是全局有序的（1，2，2，3，4，5，6，7，8），而 b 是全局是无序的（12，7，8，2，3，8，10，5，2）。因此，直接执行 where b = 2 这种查询条件没有办法利用联合索引的，利用索引的前提是索引里的 key 是有序的。

只有在 a 相同的情况才，b 才是有序的，比如 a 等于 2 的时候，b 的值为（7，8），这时就是有序的，这个有序状态是局部的，因此，执行 where a = 2 and b = 7 这种查询条件时， a 和 b 字段能用到联合索引的，也就是联合索引生效了。

8.索引的适用情况

索引的优缺点

优点：

利用索引可以大幅提升数据的检索速度，减少搜索的数据量。
通过创建唯一性索引，可以保证数据库表中每一行记录的唯一性。

缺点：

创建和维护索引需要耗费时间，如果表中存在索引，那么进行增删改查工作时，也需要对索引进行动态修改，会降低数据库执行效率。
索引需要物理文件进行存储，耗费存储空间。

（1）什么情况适合加索引？

字段有唯一性限制的，比如商品编码。
被频繁查询的字段，在经常用于过滤、排序和连接操作的列上添加索引是有益的。
大表的查询优化，数据量庞大，查询涉及多个列和复杂的筛选条件。

（2）什么情况不需要加索引？

频繁更新的字段，索引的修改与维护成本相对较大。
数据量较小的表。
字段中存在大量重复数据。比如性别字段，只有男女，无论是否使用索引搜索出来的结果都接近均匀分布。

（3）索引的创建方式：

创建表时添加索引

CREATE TABLE my_table (  
  id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(100) NOT NULL,  
  PRIMARY KEY (id),  
  INDEX (name)  
);

修改表结构添加索引

ALTER TABLE my_table ADD INDEX (name);

使用CREATE INDEX创建索引

CREATE INDEX idx_name ON my_table (name);

9.索引的失效情况

使用select *进行查询不会直接导致索引失效，但会带来一系列的性能问题，比如大量的网络传输开销和内存消耗。
查询条件中使用or，且or的前后条件中有一个列没有索引，涉及的索引都不会被使用到。
在索引列上进行计算、函数、类型转换等操作。
创建了联合索引，但查询条件未遵守最左匹配原则。
发生了隐式转换，比如索引字段是字符串，输入的参数是整型就会失效；但索引字段是整型，输入的参数是字符串，不会失效，这跟mysql的数据类型转换规则有关。
以%开头的LIKE查询，比如LIKE '%abc'。当使用%作为查询模式的前缀时，这表示任意数量的字符，包括零个字符。数据库无法有效匹配以abc结尾的值，因为前面的值难以预测，通常会进行全表扫描。

三. 事务

1.事务的定义与四大特性

事务是逻辑上的一组操作，要么都执行，要么都不执行。

原子性(Atomicity)：事务是最小的执行单位，不允许分割。原子性确保动作要么都做，要么都不做；
一致性(Consistency)：执行事务前后，数据保持一致，如转账，无论事务是否成功，转账人和收款人的总额是不变的；
隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务干扰；
持久性(Durability)：一个事务被提交之后，对数据库数据的改变是持久的。

AID是手段，C是目的。

2.并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。

（1）脏读（Dirty read）

事务A读取数据并对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交，这时事务B读取了这个还未提交的数据，但事务A突然回滚，导致数据没有提交到数据库，那事务B读到的就是脏数据。

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19，事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

（2）丢失修改（Lost to modify）

事务A读取一个数据时，事务B也访问了该数据，在事务A修改了这个数据后，事务B也修改了这个数据。这样事务A的修改结果就被丢失。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

（3）不可重复读（Unrepeatable read）

事务A多次读同一数据。在这个事务还没结束时，事务B也访问该数据。那么，在事务A中的两次读数据之间，由于事务B的修改导致事务A两次读取的数据不一样。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 2 修改 A=A-1，事务 1 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

（4）幻读（Phantom read）

幻读与不可重复读类似。事务A读取了几行数据，接着事务B插入了一些数据，在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样。

例如：事务 1 读取某个范围的数据，事务 2 在这个范围插入了新的数据，事务 1 再次读取这个范围的数据发现多了新的数据。

3.不可重复读和幻读的区别

不可重复读的重点是内容修改或者记录减少，如多次读取到某一记录的值发现其值被修改。
幻读的重点是记录增加，比如多次执行某条查询语句时发现查到的记录数增加。

解决不可重复读可以用记录锁（Record Lock），锁住已存在的记录。为了避免插入新记录，需要用间隙锁（Gap Lock），也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock）进行加锁来保证不出现幻读。

4.并发事务的控制方式

锁和MVCC（多版本并发控制）。

锁控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

undo log : undo log 用于记录某行数据的多个版本的数据。
read view 和隐藏字段 : 用来判断当前版本数据的可见性。

5.MVCC原理

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

MVCC的实现依赖于三个隐藏字段、Read View和undo log。在内部实现中，InnoDB 通过数据行的 trx_id 和 Read View 来判断数据的可见性，如不可见，则通过数据行的roll指针（ROLL_PTR）找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建快照之前已提交的修改和该事务本身做的修改。

（1）隐藏字段

TRX_ID：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头Record header 中的deleted_flag 字段将其标记为已删除。
ROLL_PTR：回滚指针，指向该行的undo log。如果该行未被更新，则为空。
ROW_ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB会使用该 id 来生成聚簇索引。

（2）Read View

low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果m_ids 为空，则 up_limit_id 为 low_limit_id。小于这个 ID 的数据版本均可见。
m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View 时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
creator_trx_id：创建该 Read View 的事务 ID。

（3）undo_logo

主要有两个作用：

事务回滚时，将数据恢复到修改前的样子。
另一个是MVCC，读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，就可以通过 undo log 读之前的版本数据。

6.SQL标准定义的四个隔离级别

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

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

7.RC和RR隔离级别下MVCC的差异

在RC和RR下，InnoDB 引擎使用 MVCC，但它们生成快照的时机不同：

在RC隔离级别下，每次 select 查询前生成一个Read View (m_ids 列表)
在RR隔离级别下只在事务开始后第一次 select查询前生成一个Read View（m_ids 列表）【因此保证了可重复读】

8.MVCC解决不可重复读问题

虽然RC和RR都通过MVCC来读取快照数据，但由于生成快照的时间不同，从而在 RR 级别下实现可重复读。

（1）在RC下 Read View 的生成情况

RC 隔离级别下，事务在每次快照读时，都会生成新的 Read View，所以导致了不可重复读。

（2）在RR下 ReadView 的生成情况

RR隔离级别下，只在事务第一次快照读时生成Read View，后续都是复用这个Read View，Read View一样，Read View的版本链匹配规则也一样，所以最终快照读返回的结果也一样，从而解决了不可重复读问题。

9.MVCC解决幻读问题

InnoDB在 RR 级别下通过 MVCC + Next-key Lock 来解决幻读

分为两种情况：快照读和当前读

（1）执行普通的 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用到事务提交。所以在生成 Read View 之后，其它事务所做的更新、插入记录的版本对当前事务都不可见，因为这种情况下只能看到创建Read View之前的，从而实现了可重复读和防止快照读下的“幻读”

（2）执行 select...for update/lock in share mode、insert、update、delete 等当前读(锁定读)

在当前读下，读的都是最新的数据，如果其它事务插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。执行当前读时，会锁定读取到的记录，同时锁定它们的间隙，防止其它事务在这个查询范围内插入数据。只要不让插入，就不会发生幻读。

10.MySQL的隔离级别都是基于锁实现的吗？

MySQL的隔离级别是基于锁和 MVCC 机制共同实现的。

可串行化隔离级别是通过锁实现的，RC和RR隔离级别是基于MVCC实现的。

不过，可串行化之外的其他级别也可能用到锁，比如RR在当前读情况下，需要加锁来防止出现幻读。

11.假设事务隔离是可重复读的，有事务A和B，A先开启但未提交，B开启后提交，问A读的数据是新的还是旧的？

是旧数据。因为A只能读取到它创建快照之前的数据。

四. MySQL锁

1.表级锁和行级锁

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是对非索引字段加锁，对整张表加锁，发生锁冲突的概率高，并发度低，效率低，不会出现死锁。MyISAM和InnoDB都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是对索引字段加的锁 ，只针对当前操作的行记录加锁。行级锁能减少数据库操作的冲突，并发度高，但加锁的开销大，加锁慢，会出现死锁。

2.InnoDB有哪几类行锁？

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

Next-Key Lock 的加锁范围：当前记录以及当前记录前面的间隙。

3.共享锁和排他锁

不管是表级锁还是行级锁，都有共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）

共享锁：又称读锁，事务在读取记录时获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁：又称写锁，事务在修改记录时获取排他锁，不允许多个事务同时获取。如果一个记录已经加了排他锁，其他事务不能再对这条事务加任何类型的锁。

排他锁与任何锁都不兼容，共享锁只和共享锁兼容。

4.意向锁的作用

用意向锁可以快速判断是否能对某个表使用表锁 (如何判断表中的记录有没有行锁，一行行遍历肯定不行，性能差)

意向锁是表级锁，有两种

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加S锁，加S锁前必须先取得该表的IS锁。由语句select ... lock in share mode添加。与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加X锁，加排他锁之前必须先取得该表的 IX 锁。由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

意向锁是由引擎自己维护的，用户无法手动操作意向锁，意向锁之间是互相兼容的。