对线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选哪个了性能
自带系统表使用	Y	N
关注点	节省资源,消耗少,简单业务	高并发,事务,数据量大
默认安装	Y	Y
默认使用	N	Y

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