Mysql存储引擎

文章目录

目录

1、各种存储引擎特性

        1.1 InnoDB

        1.2 MyISAM

        1.3 MEMORY

        1.4 MERGE

总结

---

前言

MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应的引擎。MySQL支持的存储引擎有很多，常用的是：InnoDB，MyISAM。MEMORY，MERGE作为了解，其中InnoDB提供事务安全，其他存储引擎是非事务安全表。

---

1、各种存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE
存储限制	64TB	有	有	没有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁
B树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
支持外键	支持

注：还有很多特性，这些只是我了解到的

可以通过show engines,来查询当前数据库支持的存储引擎：

 查看Mysql数据库的默认引擎指令：

show variables like '%storage_engine%';

 

1.1 InnoDB

InnoDB存储引擎是Mysql的默认引擎。

事务控制

create table my_innodb(
   id int not null primary key auto_increment,
   name varchar(20) not null
 )engine=innodb charset=utf8;

start transaction;

insert into my_innodb(name) values('吵吵');

commit;

 经测试，InnoDB中是存在事务的。

外键约束

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

create table parent_innodb(
    id int not null auto_increment,
    name varchar(20) not null,
    primary key(id)
)engine=innodb charset=utf8;

create table sun_innodb(
    s_id int not null auto_increment,
    s_name varchar(20) not null, 
    id int not null,
    primary key(s_id),
    constraint fk_parent_sun foreign key(id) references parent_innodb(id)
    on delete restrict on update cascade
)engine=innodb charset=utf8;

insert into parent_innodb values(null,'china'),(null,'america'),(null,'japan');
insert into sun_innodb values(null,'BeiJing',1),(null,'NewYork',2),(null,'ShangHai',1);

在添加索引时，可以指定删除、更新父表时，对子表进行的相应操作 。

ON DELETE，ON UPDATE：

RESTRICT和ON ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新。

CASCADE表示父表在更新或删除时，更新或删除子表对应的记录。

SET NULL则表示父表在更新或删除时，子表对应字段值被设置为NULL(不过要求该外键允许为空)

不写ON DELETE ON UPDATE 默认为RESTRICT级别。

表中数据如下： 

外键信息可以通过两种方式查看： 

show create table sun_innodb;

 show index from sun_innodb;

验证级联删除与更新： 

 删除父表id为1的数据

delete from parent_innodb where id=1;

更新父表id

update parent_innodb set id=100 where id=1;

更新后，子表的数据信息为：

 存储方式

innodb在原来版本是将表结构存储在.frm文件中，每个表的数据和索引单独存放在.ibd中。

在8.0版本后没有.frm表结构文件，并入.ibd文件中了。

1.2 MyISAM

MyISAM 不支持事务、也不支持外键，优势就是访问的速度快，以 SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

 不支持事务

create table me_myisam(
    id int not null primary key auto_increment,
    name varchar(20) not null
)engine=myisam charset=utf8;

 

 经测试，MyISAM存储引擎，是没有事务控制的。

存储方式

在原来版本是将表定义存储在.frm文件中，数据存放在.MYD文件中，索引存放在.MYI文件中。在8.0版本后.frm文件就没有了，由.sdi文件存储元数据，并且是以JSON格式存储的，可以格式化一下，就可以方便的看到数据库名、表名以及各个字段。

1.3 MEMORY

Memory存储引擎将表的数据存放在内存中。这样有利于数据的快速处理，提高整个表的效率。MEMORY类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。

1.4 MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

 对于MERGE类型表的插入操作，通过INSERT_METHOD子句定义插入的表，值分别是first(插入操作被作用在第一个表上)，last(插入操作被作用在最后一个表上)，不定义或定义为NO(表示不能对这个MERGE表执行插入操作)。

create table student1(
    tno int,
    name varchar(20)
)engine=myisam charset=utf8; 

create table student2(
    tno int,
    name varchar(20)
)engine=myisam charset=utf8; 

create table student_all(
    tno int,
    name varchar(20)
)engine=merge union=(student1,student2) insert_method=first charset=utf8; 

insert into student1 values(1,'超哥'),(2,'张三');
insert into student2 values(100,'嗯嗯'),(101,'李四');

查询3张表的数据。

student1中的数据：

student2中的数据：

student_all中的数据：

 向student_all表中插入一条数据，由于定义MERGE表时，INSERT_METHOD选择的是first，那么插入的数据会向第一张表中插入。

insert into student_all values(3,'幸运');

---

总结

对于存储引擎的选择，根据业务需求对应各个存储引擎的特征进行选择即可。