mysql---存储引擎-CSDN博客

1、INNODB-5.5之前mysql的默认存储引擎事务性速记引擎。支持CAID事务。支持行锁表锁写入和查询性能比较好
2、MYISAM:.5.5之前的默认存储引擎。插入数据的性能较高查询速度也很优秀。但是不支持事务。
3、memory: 所有数据都保存在内存的存储引擎。插入数据更新查询数据速度比较快。但是占用内存空间比较大。会占用和数据量成正比的内存空间。mysql一旦重启内容就会丢失。
4、csv是由逗号分割的存储引擎。他会在数据库子目录里为每一个数据表创建一个csv的文件。就是一个普通的数据文件每个数据占用一个文本行csv不支持索引。
5、archive非常适合存储大量的独立的历史数据的引擎。插入的速度很快。查询的效率比较低。
6、blackhole黑洞引擎写入的任何数据都会消失。

MYISAM和INNODB做个对比

MYISAM

MYISAM不支持事务也不支持外键只支持全文索引数据文件和索引是分开的
访问速度快
使用场景查询和插入数据为主的应用

在磁盘上有三个文件

编译安装/usr/local/mysql/data/数据库名/下

文件和表名相同但扩展名不同
.frm:存储表结构
.MYD:存储数据文件
.MYI:存储索引文件

vim /etc/my.cnf
--INNODB改为MYISAM
systemctl restart mysqld

MYISAM的特点

1、表级锁定更新数据时整个都将锁定。
2、数据库在读写过程中相互阻塞。

支持的存储格式:

1、静态表固定长度表静态表式mvisam的默认存储格式。静态表中字段都是非可变字段。每个记录都是固定长度。存储快方便缓存有了故障容易恢复缺点是占用空间比较多。
2、动态表动态表可以包含可变字段记录的长度是不固定的。优点是占用空间少。频繁更新数据删除记录会产生碎片。定期清理。myisamchk -r。出现故障恢复比较困难。
3、压缩表myisamchk工具创建占用空间非常小每条记录都是单独压缩。

INNODB

支持事务支持4个事务的隔离级别。5.5之后mysql默认存储引擎。
读写阻塞和隔离级别相关。
支持高校的缓存索引以及缓存数据。
表于主键以族方式存储BTREE。

支持外键约束5.5之后innodb也可以支持全文索引
硬件资源的要求比较高。
行锁定
也可以支持表锁定全表扫描

1、使用like模糊查询会进行全表扫描锁定整个表
2、对没有创建索引的字段进行查询也会纪念性全表扫描。锁定整个表。
3、使用索引进行增删改则是行级锁定。

innodb的特点

1、不保存表的行数统计表的行数扫描一遍整个表来计算多少行。
2、自增长字段innodb中必须包含又该字段的索引
3、delete清空表一行一行删速度比较truncate快

使用场景

1、业务需要事务支持。
2、论坛、微博、对数据一致性比较高的场景
3、访问量和并发量比较高的场景innodb支持缓存就按少后台服务器的压力。

三个文件

编译安装/usr/local/mysql/data/数据库名/下

表名.frm 表结构文件
表名.ibd 即使数据文件又是索引文件
db.opt 表的属性文件

innodb行锁和索引的关系以及表锁排他锁死锁。

行锁

如果name字段是一个普通索引会锁住索引行对应的主键一并锁住实际上就是行锁。
如果使用的字段的是主键innodb对主键使用聚簇索引锁定整行的记录锁行。

对于数据库中的某一行数据进行的锁定以确保在对该行数据进行读取或修改时不会发生数据冲突或数据丢失等问题。可以实现更高的并发性。

表锁

当一个事务对非索引列操作因为要全表扫描整张表都被锁定另一个事务只能查。

在数据库操作中对于一个表进行的锁定以确保在对该表进行修改或读取时不会发生数据冲突或数据丢失等问题。

排他锁 /悲观锁

只能设置排他锁的事务操作其他的事务操作无法执行只能等其commit之后才可。只能加一个

排他锁则只允许一个用户对该表进行修改或读取操作其他用户无法同时进行读写操作。

将访问数据的操作加上锁例如在MySQL中可以使用SELECT ... FOR UPDATE语句来锁定查询结果或者通过LOCK TABLES命令来锁定整个表。当一个事务访问了数据并加锁后其他事务就不能再访问该数据直到当前事务释放锁为止。

select语句 +for update;

缺点是会影响并发性和性能因为它会阻塞其他等待访问该数据的事务。因此通常只在需要确保数据一致性和安全性的场景下使用。

死锁

事务之间相互等待对方资源最后形成一个闭环
情况1、发生死锁时数据库对自动选择一个受害者然后先接触死锁然后再回滚事务
情况2mysql的默认的死锁机制会中选择一个事务作为死锁的牺牲品。直接终止其中一个事务但不会自动回滚。

乐观锁

不会任何提示只是数据不能写入。数据提交更新时进行校验发生冲突数据不生效而已没有报错或者卡顿。

应用场景通常适用于并发量较高的场景

乐观锁的实现方式有多种其中比较常见的是使用版本号或时间戳来判断数据是否被修改。

假设数据是不会被并发访问的因此在修改数据时不加锁而是在更新时比较数据版本号如果版本号一致则表示此间隙内没有其他线程修改该数据可以进行更新操作如果版本号不一致则表示数据已被其他线程修改需要进行回滚或其它处理。

如何尽可能地避免死锁

1、优化业务逻辑业务的逻辑要合理以固定的顺序问表和行
2、尽量使用短事务如果事务的类型比较复杂要进行拆分在业务允许额情况下把大事务拆小。
3、在同一事务中尽可能一次性锁定所有需要的资源可以减少死锁地概率
4、隔离级别read commit可以尽可能的避免死锁
5、建立良好的索引添加合理索引可以减少死锁的概率

6、设置超时时间设置锁的超时时间当超过一定时间限制时强制终止锁的持有者的操作以避免死锁。

7、定期监测对于数据库管理员来说定期监测死锁的发生情况可以及时处理和优化数据库以降低死锁的发生率和影响。

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

标签: mysql