MySQL 存储引擎

发布时间:2023年12月25日

目录

定义

常用的存储引擎及区别

常用的存储引擎

MyISAM 与 InnoDB 的区别??

MyISAM 表支持 3 种不同的存储格式

静态(固定长度)表

动态表

压缩表

与存储引擎相关的SQL语句

查看系统支持的存储引擎

?查看表使用的存储引擎

?修改存储引擎

通过 alter table 修改

?通过修改 /etc/my.cnf 配置文件,指定默认存储引擎并重启服务

通过 create table 创建表时指定存储引擎

InnoDB行锁与索引的关系?

?死锁

定义

模拟死锁现象?

如何避免死锁?


定义

存储引擎是MySQL数据库中的组件,负责执行实际的数据I/O操作(数据的存储和提取)。工作在文件系统之上,数据库的数据会先传到存储引擎,再按照存储引擎的存储格式保存到文件系统。

常用的存储引擎及区别

常用的存储引擎

  • InnoDB
  • MyISAM

MyISAM 与 InnoDB 的区别??

MyISAM:不支持事务、外键约束,只支持表级锁定,适合单独的查询和插入的操作,读写会相互阻塞,支持全文索引,硬件资源占用较小,数据文件和索引文件是分开存储的(存储成三个文件:表结构文件.frm、数据文件.MYD、索引文件.MYI)
使用场景:适用于不需要事务支持,单独的查询或插入数据的业务场景

InnoDB:支持事务、外键约束,支持行级锁定(在全表扫描时仍然会表级锁定),读写并发能力较好,支持全文索引(5.5版本之后),缓存能力较好可以减少磁盘IO的压力,数据文件也是索引文件(存储成两个文件:表结构文件.frm、数据文件.ibd)
使用场景:适用于需要事务的支持,一致性要求较高,数据会频繁更新,读写并发高的业务场景

MyISAM 表支持 3 种不同的存储格式

静态(固定长度)表

静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。

动态表

动态表包含可变字段,记录不是固定长度的,这样存储的优点是占用空间较少,但是频繁的更新、删除记录会产生碎片,需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能,并且出现故障的时候恢复相对比较困难。

压缩表

压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。

与存储引擎相关的SQL语句

查看系统支持的存储引擎

show engines;

?查看表使用的存储引擎

方法一:
show table status from 库名 where name='表名'\G

方法二:
use 库名;
show create table 表名;

?

?修改存储引擎

通过 alter table 修改

use 库名;
alter table 表名 engine=MyISAM;

?

?通过修改 /etc/my.cnf 配置文件,指定默认存储引擎并重启服务

vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB

systemctl restart mysql.service

?

?

?注意:此方法只对修改了配置文件并重启mysql服务后新创建的表有效,已经存在的表不会有变更。

通过 create table 创建表时指定存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

InnoDB行锁与索引的关系?

InnoDB行锁是通过给索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。

举例:

delete from t1 where id=1;

如果id字段是主键,innodb对于主键使用了聚簇索引,会直接锁住整行记录。

delete from t1 where name='aaa';

?如果name字段是普通索引,会先锁住索引的两行,接着会锁住相应主键对应的记录。

delete from t1 where age=23;

?如果age字段没有索引,会使用全表扫描过滤,这时表上的各个记录都将加上锁。

?

?死锁

定义

死锁是指两个或多个事务在同一个资源上相互占用,并请求对方的锁定资源,从而导致恶心循环的现象。

模拟死锁现象?

?

?

如何避免死锁?

  1. 设置事务超时等待时间 innodb_lock_wait_timeout
  2. 设置开启死锁检测 innodb_deadlock_detect
  3. 为表添加合理的索引,减少表锁发生的概率
  4. 如果业务允许,可以降低隔离级别,比如采用 提交读 隔离级别
  5. 建议开发人员尽量使用更合理的业务逻辑,多表操作时以固定顺序访问表,尽量避免同时锁定多个资源
  6. 建议开发人员尽量保持事务简短,减少对资源的占用时间和占用范围
  7. 建议开发人员在读多写少的场景下适用乐观锁机制

?

文章来源:https://blog.csdn.net/weixin_72625764/article/details/135208563
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。