MySQL存储引擎

最新推荐文章于 2023-06-19 16:29:31 发布

HanghangzZ

最新推荐文章于 2023-06-19 16:29:31 发布

阅读量142

点赞数

分类专栏：学习数据库笔记文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/HanghangzZ/article/details/109552811

版权

学习数据库笔记专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了MySQL中的两种主要存储引擎MyiSAM和InnoDB。MyiSAM存储引擎适用于简单查询，支持定长、动态和压缩格式，而InnoDB是默认存储引擎，支持事务和ACID属性，提供行级锁和外键。文章还深入解析了ACID的四个特性：原子性、一致性、隔离性和持久性，并通过银行转账案例解释了事务的必要性和数据库管理系统如何确保这些特性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL存储引擎

MyiSAM存储引擎

在MySQL5.5之前，默认的存储引擎就是它，当我们建表的时候如果没有指定存储引擎，就会使用它。

默认MyiSAM的表会在磁盘中产生3个文件C:\ProgramData\MySQL\MySQL Server 8.0\Data\imooc
- .frm：表结构文件(但是我的电脑盘上面并没有这个文件)
- .MYD：数据文件
- .MYI:索引文件
- 可以在创建表的时候指定数据文件和索引文件的存储位置，只有MyiSAM表支持
  
  指定了下面这两个语句之后，还是会产生MYD、MYI的文件，只不过会产生一个软连接，指向产生的绝对路径
  - ```
  DATA DIRECTORY [=] 数据保存的绝对路径
  
  INDEX DIRECTORY [=] 索引文件保存的绝对路径
```
- MyiSAM单表最大支持的数据量2的64次方条记录
- 每个表最多可以建立64个索引
- 如果使用复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
- MyiSAM引擎的存储格式
  - 定长(FIXED 静态)：是指字段中不包含VARCHAR/TEXT/BLOB(这是一个二进制数据类型)，它就是一个定长的格式，存储的是固定的字节数，静态格式也是一种最简单、最安全的一种格式。假如说出现数据或者服务器崩溃，它也是最快恢复的。同时这种储存数据的方式也是最快的
  - 动态(DYNAMIC)：只要字段中包含了VARCHAR/TEXT/BLOB
  - 压缩(COMPRESSED)：myisampack创建
  - 定长和动态不需要我们自己去指定，它会在我们创建表的时候根据字段选择的类型自动的去适配
    
    如果我们的字段都是CHAR类型的，那么它就是一个静态表
    
    如果我们的字段中有VARCHAR或者TEXT类型的话，那么这就是一个动态表
    
    我们还可以通过ROW_FORMAT去强制的使这个表定义成定长的或者动态的
    
    而压缩的，我们就必须通过一个工具MyiSAM来进行创建
    
    静态表较快的原因：它可以很方便的到磁盘当中去定位并查找我们的元素，因为每一行的值长度都是固定的，而且如果有索引的话，我们可以根据索引的行号再乘以长度就可以定位到行所在的位置(不知道是什么东东)，同样的扫描的时候，我们也因为这个长度是确定的，也可以得到指定数量的记录。
    
    当我们在向静态表写入数据的时候，如果我们的MySQL服务器出现了一些异常或者崩溃了，我们的数据也是比较安全的，这个时候我们可以通过MySQL中的一个工具叫MyiSAMCHK这样的一个命令行工具就可以很方便的查找出每一行的起始和终止的位置，这样子我们就可以修复这些记录。
    
    动态表处理数据要复杂一些，因为它在处理数据包括存储数据的时候，它会在每一行有一个行头来标识我们这一行记录的长度，又因为这个长度是不固定的，所以每一次写入，它都会重新的来标定。如果说经常的更新这个表，那么就会产生一些碎片。从性能上来说，动态表远远小于静态表，但是动态表比较节约空间，就像前面所讲的CHAR和VARCHAR一样

MyiSAM测试

-- 测试MyISAM存储引擎 
-- 下面操作后会产生三个文件 
-- MYD是存储数据的文件
-- MYI是索引文件 
CREATE TABLE test_myisam(
	a INT UNSIGNED,
    b VARCHAR(20),
    c CHAR(32)
)ENGINE = MyISAM;


CREATE TABLE IF NOT EXISTS test_myisam1(
	a CHAR(10),
    id INT
)ENGINE = MyISAM;
-- 查看表的状态信息
SHOW TABLE STATUS LIKE 'test_myisam1';

CREATE TABLE test_myisam2(
	a VARCHAR(30),
    id INT
)ENGINE = MyISAM;
SHOW TABLE STATUS LIKE 'test_myisam2';

CREATE TABLE test_myisam3(
	a VARCHAR(30),
    id INT
)ENGINE = MyISAM, ROW_FORMAT = FIXED;
SHOW TABLE STATUS LIKE 'test_myisam';

在这里插入图片描述

InnoDB存储引擎

从5.5之后默认使用的一个存储引擎。

它的设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Isolation，又称独立性）
- 持久性（Durability）
支持行级锁，可以提升多用户并发时的读写性能
支持外键，保证数据的一致性和完整性
InnoDB具有自己独立的缓冲池，常用的数据和索引都在缓存中。
对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能
创建innodb后会产生的文件
- .frm表结构文件(我的电脑里没有)
- .jbd，数据和索引存储的文件
所有的InnoDB表都需要创建主键，最好是配合上AUTO_INCREMENT，也可以放到经常查询的列作为主键。因为主键的效率是最高的，可以快速定位到记录

ACID详述

ACID，是指在数据库管理系统（DBMS）中，事务(transaction)所具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性限制没有被破坏。
隔离性：当两个或者多个事务并发访问（此处访问指查询和修改的操作）数据库的同一数据时所表现出的相互关系。事务隔离分为不同级别，包括读未提交(Read uncommitted)、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：在事务完成以后，该事务对数据库所作的更改便持久地保存在数据库之中，并且是完全的。

一.事务

定义：所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

准备工作：为了说明事务的ACID原理，我们使用银行账户及资金管理的案例进行分析。

   // 创建数据库
   create table account(
      idint primary key not null,
      namevarchar(40),
      moneydouble
   );

   // 有两个人开户并存钱
   insert into account values(1,'A',1000);
   insert into account values(2,'B',1000); 123456789

二.ACID

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)所应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）.这是可靠数据库所应具备的几个特性.下面针对这几个特性进行逐个讲解.

三.原子性

原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

1.案例

A给B转帐100元钱

   begin transaction
   update account set money= money - 100where name='A';
   update account set money= money +100where name='B';
   if Error then
          rollback
   else
          commit123456

2.分析

在事务中的扣款和加款两条语句，要么都执行，要么就都不执行。否则如果只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷。

3.解决方法

在数据库管理系统（DBMS）中，默认情况下一条SQL就是一个单独事务，事务是自动提交的。只有显式的使用start transaction开启一个事务，才能将一个代码块放在事务中执行。保障事务的原子性是数据库管理系统的责任，为此许多数据源采用日志机制。例如，SQL Server使用一个预写事务日志，在将数据提交到实际数据页面前，先写在事务日志上。

四.一致性

一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

1.案例

对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNT表中aaa和bbb的存款总额为2000元。

2.解决方法

保障事务的一致性，可以从以下两个层面入手

2.1数据库机制层面

数据库层面的一致性是，在一个事务执行之前和之后，数据会符合你设置的约束（唯一约束，外键约束,Check约束等)和触发器设置。这一点是由SQL SERVER进行保证的。比如转账，则可以使用CHECK约束两个账户之和等于2000来达到一致性目的

2.2业务层面

对于业务层面来说，一致性是保持业务的一致性。这个业务一致性需要由开发人员进行保证。当然，很多业务方面的一致性，也可以通过转移到数据库机制层面进行保证。

五.隔离性

多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

在Windows中，如果多个进程对同一个文件进行修改是不允许的，Windows通过这种方式来保证不同进程的隔离性:

企业开发中，事务最复杂问题都是由事务隔离性引起的。当多个事务并发时，SQL Server利用加锁和阻塞来保证事务之间不同等级的隔离性。一般情况下，完全的隔离性是不现实的，完全的隔离性要求数据库同一时间只执行一条事务，这样会严重影响性能。想要理解SQL Server中对于隔离性的保障，首先要了解并发事务之间是如何干扰的.

1.事务之间的相互影响

事务之间的相互影响分为几种，分别为：脏读，不可重复读，幻读，丢失更新

1.1脏读

脏读意味着一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的；如下案例，此时如果事务1回滚，则B账户必将有损失。

1.2不可重复读

不可重复读意味着，在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。如下案例，事务1必然会变得糊涂，不知道发生了什么。

1.3幻读（虚读）

幻读，是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.

1.4丢失更新

两个事务同时读取同一条记录，A先修改记录，B也修改记录（B是不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

六.持久性

持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

七.总结

事务的（ACID）特性是由关系数据库管理系统（RDBMS，数据库系统）来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。
　　数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。