对线HR_MySQL存储引擎,原来是这样啊

MySQL高级篇之存储引擎

特别鸣谢

"康师傅"

文章目录

1. 先入为主

1.1查看MySQL支持的存储引擎

SHOW ENGINES;

1.2默认存储引擎的查看

#方法一
SHOW VARIABLES LIKE '%storage_engine%'

#方法二
SELECT @@SESSION.default_storage_engine;

1.3修改默认存储引擎

#方法一 修改当前会话层的系统变量
SET SESSION default_storage_engine = '存储引擎';

#方法二 修改my.cnf配置文件
[mysqld]
default_storage_engine = 存储引擎;

1.4创建表时指定对应的存储引擎

CREATE TABLE IF NOT EXISTS table_name (
    field_name data_type constraint,
    field_name data_type constraint,
    field_name data_type constraint
)engine = '存储引擎';

#如果没有指明存储引擎则采用默认存储引擎Innodb

1.5修改表的存储引擎

ALTER TABLE table_name engine = '存储引擎';

2.存储引擎的介绍

2.1什么是存储引擎

• 存储引擎可以理解为是一个表的类型,即好像变量有对应的数据类型一样,表也有对应的类型,就是存储引擎
• 存储引擎决定了表如何去处理数据
• 注意:以上两点都是基于存储引擎决定了底层的索引,而索引对应着不同的数据结构

2.2Innodb存储引擎

• 自MySQL5.5之后,默认存储引擎被改为Innodb,在之前的默认存储引擎都是MYISAM- 无特殊情况,优先使用Innodb
• Innodb是支持事务的,即Innodb支持回滚操作\
• Innodb是支持外键约束的
• 常见的DML操作如SELECT UPDATE DELETE INSERT都应该使用Innodb这个存储引擎- 使用该引擎的原因主要的原因是MySQL对Innodb做出了优化,效率较高- 底层索引MYISAM对应的非聚簇索引理论上效率更高 以后阐述
• 文件目录- MySQL8.0- .ibd表结构 + 表数据/索引(数据即索引,索引及数据)- MySQL5.7- .opt数据库信息- .frm:表结构- .ibd:表数据/索引(数据即索引,索引即数据)

2.3MYISAM存储引擎

• MYISAM既不支持外键约束也不支持事务
• MYISAM最大的优势是查询效率高,也是和非聚簇索引有关
• 文件目录 - MYSQL8.0- .MYD存储数据- .MYI存储索引- .sdi表结构- MySQL5.7- .MYD存储数据- .MYI存储索引- .frm表结构- .opt数据库信息

2.4Innodb和MYISAM的区别

对比项

MYISAM

InnoDb

说明外键不支持支持事务不支持支持行表锁表级锁行级锁以Java为例,类似于

synchronized

的作用缓存只缓存索引,不缓存数据数据和索引一起缓存该原因也是和底层是否为聚簇索引导致的,所以,InnoDb对缓存的要求更高,一定程度上应i选哪个了性能自带系统表使用YN关注点节省资源,消耗少,简单业务高并发,事务,数据量大默认安装YY默认使用NY

2.5Archive存储引擎

• Archive叫做归档,顾名思义其主要作用是对数据进行归档处理,即数据存档
• 因为Archive的主要作用是归档,其支持的DML操作只有SELECT & INSERT操作 - 其中SELECT的效率比较低,INSERT的效率极高

2.6Blackhole存储引擎

• 在Blackhole存储引擎中,INSERT操作不被允许,SELECT操作返回的是NULL
• 所以DML最核心的查询和插入操作都是无效的,该存储引擎没有意义

2.7CSV存储引擎

• CSV存储引擎最大的亮点就是其生成如下的文件目录 - .CSV该文件存放的是表中的数据,该文件可以被其他软件打开,如Excel等- .CSM该文件存放的是表的状态
• CSV最大的作用就是让数据库的表可视化,可以用其他软件直接打开,类似于JSON和XML

2.8Memory存储引擎

• Memory存储引擎最大的亮点是,其他存储引擎存储的数据都在磁盘中,而Memory的数据存储在内存当中 - 故memory下的文件目录结构中,在mysq5.7只有.frm文件,在mysql8.0没有任何文件
• 因为数据存储在内存当中 1. 响应速度非常的快,查询效率非常的高底层是哈希索引,查询元素的时间复杂度是O(1),即使是B+Tree对应的聚簇索引也是O(log2N)2. 数据易丢失,生命周期短一旦mysql服务被终止,或者是重启,对应的内存都会消失
• Memory数据文件和索引文件分开存储,因为其底层并不是聚簇索引
• Memory的大小是有限的,主要取决于max_rows和max_heap_table_size- max_rows在创建表时可以指定- max_heap_table_size默认是16mb,可以按需增大- 上面两者类似于自变量和因变量的关系
• Memory的使用场景 - 数据是临时的,而且必须立即使用的,而且这个数据是否被丢弃也无所谓的- 数据量比较小,而且要被频繁的使用 - 其实这句话也不准确,对于底层的哈希索引不适合做一些排序,查找范围值的操作

类似于自变量和因变量的关系

• Memory的使用场景 - 数据是临时的,而且必须立即使用的,而且这个数据是否被丢弃也无所谓的- 数据量比较小,而且要被频繁的使用 - 其实这句话也不准确,对于底层的哈希索引不适合做一些排序,查找范围值的操作