MySQL的存储引擎

在MySQL当中数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、引技巧、锁定水平以及最终提供的不同的功能和和能力。这些就是就是存储引擎。

存储引擎的功能

1、 MySQL将数据存储在文件系统中的一种方式和格式

2、 存储引擎负责执行实际的数据I/O操作。

3、 存储引擎介于数据和文件系统之间。数据会先保存到存储引擎，再按照存储引擎的格式保存到 文件系统(硬盘)。

MySQL的存储引擎分类

1、 INNODB：事务性速记引擎。支持ACID事务，支持所行、锁表。写入和查询性能比较好。5.5版本之后MySQL默认的存储引擎。

2、 MYSAM：插入数据的性能较高，查询速度也很优秀，但是不支持事务。5.5版本之前的MySQL默认存储引擎。

3、 memory：所有数据都保存在内存的存储引擎。一旦服务重启全部丢失。插入数据、跟新数据、查询数据速度比较快。但是占用内存空间比较大。占用和数据量成正比的内存空间，一旦MySQL重启则内容丢失。

4、 csv：由逗号分割的存储引擎。它会在数据库子目录里为每一个数据表建立一个.csv的文件。就是一个普通的文本文件。每个数据行占用一个文本行(一条数据在文本中是一行)。.csv不支持索引。

5、 Archive：适合存储大量的独立的而且是历史数据的引擎。不需要被经常读取。插入的速度很快。但是查询的效率表较低。

6、 blackhole：黑洞引擎。写入的任何数据都会消失。

常见的存储引擎：INNODB、 MYSAM、csv、Archive

MYSAM和INNODB的分析和对比

MYSAM

MYSAM：不支持事务也不支持外键。只支持全文索引。数据文件和索引文件是分开的。访问速度快。

MYSAM的适用场景：查询和插入数据为主的应用。

在磁盘上有三个文件：文件名和表名相同，但是扩展名不同

1、 .frm：存储表结构

2、 .myd：存储数据文件

3、 .MYI：存储索引文件

MYSAM的特点

1、 表级锁定，更新数据时，整个都将锁定。

2、 数据库在读写过程中互相堵塞

支持的存储格式

1、 支持静态表，固定长度表。静态表是MYSAM的默认存储格式。静态表中的字段都是非可变字段。每个记录都是固定长度。存储快，方便缓存。有了故障也容易恢复。缺点是占用的空间比较多。

2、 动态表可以包含可变字段。记录的长度是不固定的。占用空间少，缺点是频繁更新数据、删除记录会产生碎片。需要定期清理。出现故障恢复比较困难。#mvisamchk -r：清理命令

3、 压缩表，myisamchk工具创建的，占据的空间非常小。每条记录都是单独压缩的

INNODB

INNODB：支持事务。支持4个事物的隔离级别。5.5之后是MySQL的默认存储引擎。

读写阻塞和隔离级别相关。如果是未提交读和可提交读，可提交读只有最终提交的数据才会生效，中间的操作没什么影响。

INNODB支持高效的缓存索引以及缓存数据。表与主键以簇的方式存储BETREE

支持外键约束。5.5之后INNODB也可也支持全文索引。

对硬件资源的要求比较高。

支持行锁定。全表扫描也支持表锁定。

1、 如果使用like模糊查询，会进行全表扫描，然后锁定整个表。

2、 如果对没有创建索引的字段进行增、删、改的时候，也会锁定整个表。在这个期间是锁定整个表的无法对表进行任何操作。

3、 如果使用索引进行增、删、改的时候，则是行级锁定。

INNODB的特点

1、 不保存表的行数，统计表的行数，会扫描一遍整个表来计算有多少行。

2、 自增长字段。在INNODB中必须包含只有该字段的索引。

3、 delete删除表，一行一行山，速度比较慢，推荐使用truncate

使用场景

1、 业务需要事务的支持。

2、 论坛、微博、对数据一致性比较高的场景

3、 访问量和并发量比较高的场景。因为INNODB可以支持缓存，可以减少后台服务器的压力。

INNODB存储文件的格式

三个文件：

表名.frm：表结构文件

表名.idb：即是数据文件，又是索引文件

dp.opt：表的属性文件

INNODB行锁和索引的关系以及表锁、排他锁、死锁

行锁

终端开启事务到外部测试不提交

外部开启事务发现无法运行。

如果name字段是一个普通索引，会锁住索引行，对饮的主键一并锁住，实际上就是行锁

内部开启一个事务但是不提交

外部再执行一个事务查看一下结果

如果使用的id的字段是主键，innodb会对主键使用聚簇索引。锁定整行的记录

对索引列操作。当你指定了主键或者普通索引时候，前一个事务如果没有提交，其他事务无法对这一行进行操作。但是对其他行不影响，只是锁定索引这一行。

锁定表

锁定表，要对一个非索引键进行操作

当一个事务对非索引列进行操作，因为要全表扫描过滤，所有整张表都会锁定，另一个事务只能查。

排他锁

for update：排他锁

只有一个事务结束之后，其他事务才可以进行操作。

死锁

死循环。两边互相卡住了形成闭环。

事务之间相互等待对方资源，最后形成一个环路造成的。

内部设备开启一个事务但是不提交

外部再开启一个事务

内部任务会卡住，外部事务也卡住。此时发生了死锁

1、 发生死锁的时候，数据库会自动选择一个事务作为受害者，然后先解除思索，再回滚事务。

2、 MySQL的默认的死锁机制，会选则一个事务作为思索的牺牲品。会直接终止其中一个事务，但是不会自动回滚。

锁定总结

行锁：索引只会锁索引行

锁定表：非索引会直接锁表。但是不会自动回滚。

排他锁：只能加一个。一个事务在操作，另一个事务的操作无法执行，只能查。排他锁也可也叫做悲观锁。

乐观锁不会有任何提示，只是数据不能写入。乐观锁不会卡死，数据提交更新时，他会进行校验。如果发生冲突也就是数据不生效而已，没有其他的报错或者卡停。

死锁：都无法操作。会自动选则一个事务作为思索的牺牲品。会直接终止其中一个事务，从而把死锁结束但是不会自动回滚。

如何尽可能避免死锁

1、 业务的逻辑要合理。最好是以固定的顺序访问表和行

2、 如果事务的类型比较复杂，要在业务允许的情况下进行拆分，把大的事务拆小，分次执行。

3、 在同一事务中，尽可能的一次性锁定所有需要的资源，可以减少死锁的概率。

4、 隔离级别。如果要避免死锁可以使用read commit。可以避免死锁。

5、 添加合理的索引。添加了合理索引之后，该锁就会只锁行。如果不添加就会锁表，就有可能出现死锁。合理的使用索引，可以减少索引的概率。

修改存储引擎

show engines\G;
#查看支持的引擎

方法1：alter table 表名 engine=存储引擎名;
#修改表的存储引擎的公式

**alter table test engine=myisam;**
#只修改表的引擎

方法2：vim /etc/my.conf
#修改配置文件进行修改存储引擎

方法3：
create table ky34 (
id int(4),
name char(10)
)engine=myisam;

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

存储引擎的文件的存储目录在/usr/local/mysql/data/kgc

核心内容

索引和行锁之间的关系

非索引的锁表以及死锁

排他锁

INNODB的机制和存储文件的格式

练习题

使用timestamp的间戳的方式。来进行校验查看数据是否冲突。

创建表后插入信息

到内部操作

start TRANSACTION;
#开启事务

update test3 set name = 'newnameC', date='2023-11-03 14:00:00' where id =1 and date='2023-11-03 02:28:31'2:28:31';
#修改表内内容

内部不提交事务到外部开启另一个事务

外部修改内容卡死

回到内部commit提交事务

外部的修改修改成功然后commit查看表内信息

修改成功但是名字没变

这就是乐观锁。乐观锁不会有任何提示，只是数据不能写入。乐观锁不会卡死，数据提交更新时，他会进行校验。如果发生冲突也就是数据不生效而已，没有其他的报错或者卡停。