当前位置： 首页 > article >正文

mysql知识点看这一篇就够了！

article 2024/11/18 1:35:00

存储引擎

InnoDB

InnoDB 是 MySQL 默认的事务型存储引擎，只要在需要它不支持的特性时，才考虑使用其他存储引擎。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。

主索引时聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对主键查询有很高的性能。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

InnoDB 支持真正的在线热备份，MySQL 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

InnoDB 和 MyISAM 的比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

索引

看另一篇文章

事务

事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID

事务最基本的莫过于 ACID 四个特性了，这四个特性分别是：

  • Atomicity：原子性
  • Consistency：一致性
  • Isolation：隔离性
  • Durability：持久性

原子性
事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。

一致性
数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。

隔离性
一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

事务的脏读、幻读、不可重复读

（1）脏读：事务1更新了记录，但没有提交，事务2读取了更新后的行，然后事务T1回滚，现在T2读取无效。

（2）不可重复读：事务1读取记录时，事务2更新了记录并提交，事务1再次读取时可以看到事务2修改后的记录和之前不一致；

（3）幻读：事务1读取记录时，事务2增加了记录并提交，事务1再次读取时可以看到事务2新增的记录；

脏读

脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。
它针对读未提交的问题 两个事务均未完成

例如：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
在这里插入图片描述
在这里插入图片描述

不可重复读

不可重复读指的是同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。
针对读已经提交的问题 一个事务已完成，一个未完成

例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

不可重复读和脏读的区别：前者是“读已提交”，后者是“读未提交”。

在这里插入图片描述
在这里插入图片描述

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题是通过 Record Lock 解决的，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

Phantom Proble（幻影读）

幻影读是一种特殊的不可重复读问题。

Phantom Proble 是指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。

例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

幻读是指当事务不独立执行时，插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。举个例子，某事务在检查表中的数据数count时，是10，过一段时间之后再查是11，这就发生了幻读，之前的检测获取到的数据如同幻觉一样。出现幻读和不可重复读的原因很像，都是在多次操作数据的时候发现结果和原来的不一样了，出现了其他事务干扰的现象。但是，幻读的偏重点是添加和删除数据，多次操作数据得到的记录数不一样；不可重复读的偏重点是修改数据，多次读取数据发现数据的值不一样了。

之所以要讲幻读从不可重复读中独立出来，是因为行锁无法解决此类问题。

幻读图示如下：
在这里插入图片描述
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

丢失更新

一个事务的更新操作会被另一个事务的更新操作所覆盖。

例如：

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
在这里插入图片描述
这类型问题可以通过给 SELECT 操作加上排他锁来解决，不过这可能会引入性能问题，具体使用要视业务场景而定。

隔离级别

读未提交（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其他事务也是可见的。
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）
这种隔离级别不会对select默认加锁。

读已提交（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（NonrepeatableRead），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果

这种隔离级别能够有效的避免脏读。对读已提交来说，事务中的每次读操作都会生成一个新的ReadView，也就是说，如果这期间某个事务提交了，那么它就会从ReadView中移除。这样确保事务每次读操作都能读到相对比较新的数据

可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同样数据的结果是一样的。
这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。
不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

对可重复读来说，事务只有在第一次进行读操作时才会生成一个ReadView，后续的读操作都会重复使用这个ReadView。也就是说，如果在此期间有其他事务提交了，那么对于可重复读来说也是不可见的，因为对它来说，事务活跃状态在第一次进行读操作时就已经确定下来，后面不会修改了。即读取的是第一次生成的快照。

总的来说，解决不可重复读的方法是 锁行（但是mysql好像通过上面说的方法不用锁也行？）

注意，如果采取读已提交的隔离级别，除非在查询中显式的加锁，不然，普通的查询是不会加锁的，这样就不能在事务中实现可重复读。
而使用可重复读的级别就不需要了。

可串行化（SERIALIZABLE）

强制事务串行执行。
需要加锁实现，而其它隔离级别通常不需要。这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争
该级别是最高级别的隔离级。它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，SERIALIZABLE是在每个读的数据行上加锁。在这个级别，可能导致大量的超时Timeout和锁竞争Lock Contention现象，实际应用中很少使用到这个级别，但如果用户的应用为了数据的稳定性，需要强制减少并发的话，也可以选择这种隔离级

总的来说，解决幻读的方式是 锁表。

隔离级别对脏读、不可重复读、幻读的影响情况

这四种隔离级别越往后越影响性能，如何选取根据业务需求而定。以下是四种隔离级别中对脏读、不可重复读、幻读的影响情况。

隔离级别脏读不可重复读幻影读
未提交读√√√
提交读×√√
可重复读××√
可串行化×××

通常，除了mysql数据库的隔离级别仅为2 Read Committed ，因而，仍然需要我们进行手动加锁！

解决方案总结

事务的隔离级别实际上都是定义的当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”（需要获取最新的状态）的隔离性，就需要通过加锁来实现了。

在 InnoDB 存储引擎中：

  • SELECT 操作的不可重复读问题通过 MVCC 得到了解决，
  • 而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，
  • INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。
而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。
可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

后面会具体介绍这些概念

锁

看我的另一篇博客

MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。
而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。
可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

版本号

事务相关的版本号：

  • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
  • 事务版本号：事务开始时的系统版本号。

数据行快照版本号：

隐藏的列：MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

  • 创建版本号：指示创建一个数据行的快照时的系统版本号；
  • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效（在当前事务进行的时候还没被删除呢），否则（包括没有的情况？）表示该快照已经被删除了。

Undo 日志

MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

在这里插入图片描述

当事务对数据行进行一次更新操作时，会把旧数据行记录在一个叫做undo log的记录中，在undo log中除了记录数据行，还会记录下该行数据的对应的创建版本号，也就是生成这行数据的事务id嘛~然后将原来数据行中的回滚指针指向undo log记录的这行数据。然后再在原来数据表中进行一次更新操作，如果这次更新操作回滚了，那么就可以根据回滚指针去undo log中查找之前的数据进行复原。如果后续还有更新操作的话，就会在undo log中和之前的数据行形成一条链表，链表头就是最新的数据，这条链表就叫做版本链
在这里插入图片描述
（ps：数据本来是刘备，然后事务id为100的事务先修改成了关羽，再修改成了张飞，后面事务id为200的事务先修改成了赵云，再修改成了诸葛亮）

简单来说 undo log 存的就是“还没有提交的undo的”修改

事务的可见性都是基于这个undo log来实现的

实现过程

以下实现过程针对可重复读隔离级别。

当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

  • INSERT
    会将当前系统版本号作为数据行快照的创建版本号（在哪个事务最新被创建的）

  • DELETE
    将当前系统版本号作为数据行快照的删除版本号。（在哪个事务最新被删除的）

  • UPDATE
    将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

  • SELECT
    多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。（但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。）
    把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于等于 T 的版本号，因为如果大于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。

快照读与当前读

在可重复读级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

select * from table ….;

当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而保证读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

 select * from table where ? lock in share mode;
 select * from table where ? for update;

ReadView 事务快照

刚才说了更新操作，那查询操作呢？这才是实现不同隔离级别的关键地方

当进行查询操作时，事务会生成一个ReadView，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id列表（事务的id值是递增的）

  • trx_list:一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
  • up_limit_id:记录trx_list列表中事务ID最小的ID
  • low_limit_id:Read View生成时刻系统尚未分配的下一个事务ID

其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据。

查询一条数据时，事务会拿到这个ReadView，去到undo log中进行判断。若查询到某一条数据：

  1. 先去查看undo log中的最新数据行，如果undolog数据行的版本号小于ReadView记录的事务id最小值，就说明这条数据对当前数据库是可见的，可以直接作为结果集返回
  2. 若undolog数据行版本号大于ReadView记录最大值，说明这条数据是由一个新的事务修改的，对当前事务不可见，那么就顺着版本链继续往下寻找第一条满足条件的
  3. 若数据行版本号在ReadView最小值和最大值之间，那么就需要进行遍历了整个ReadView了，如果undolog 数据行版本号等于ReadView的某个值，说说明该行数据仍然处于活跃状态，那么对当前事务不可见

http://www.kler.cn/a/7403.html

相关文章：

  • js导入导出
  • 微信小程序 https://thirdwx.qlogo.cn 不在以下 downloadFile 合法域名列表中
  • candence ： 通孔焊盘、插装器件封装绘制
  • VSCode设置
  • SpringMVC数据校验、数据格式化处理、国际化设置
  • 深度学习：transpose_qkv()与transpose_output()
  • 振动监测信号的角度域之阶次分析（1）
  • ChatGPT 存在很大的隐私问题
  • Java分布式事务（七）
  • 前端后端交互系列之原生Ajax的使用
  • C的实用笔记39——结构体占用内存大小(了解)
  • linux信号量及其实例
  • Vue+H5如何适配各个移动端？
  • netty 实现websocket 携带参数建立连接
  • VSCode卸载、重装配置、常用快捷键
  • 【ChatGPT 】国内无需注册 openai 即可访问 ChatGPT：ChatGPT Sidebar 浏览器扩展程序的安装与使用
  • 【软件设计师10】软件工程
  • SpringSecurity实战解析
  • 为什么系统的Swap变高了?
  • 吴恩达机器学习--逻辑回归
  • 分布式id生成方案及springboot进行集成
  • JavaSE基础(26) 接口
  • 面向对象编程（基础）5：类的成员之二：方法(method)
  • 前后端交互系列之跨域问题
  • centos7离线安装docker
  • 算法刷题打卡042 | 动态规划10