mysql存储引擎用哪个好用 mysql存储引擎

转载

mob64ca140088a9 2023-08-07 23:02:53

文章标签 mysql存储引擎用哪个好用 mysql 数据库存储引擎数据 文章分类 MySQL 数据库

存储引擎

文章目录

存储引擎

简介

InnoDB

事务控制
外键约束

MyISAM
MERGE
MEMORY

总结

存储引擎对比
存储引擎的选择

简介

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。 所以存储引擎也可被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。
MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

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

mysql存储引擎用哪个好用 mysql存储引擎_mysql

MySQL5.5之前的默认存储引擎是MyISAM，版本5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎

show variables like '%storage_engine%' ；

mysql存储引擎用哪个好用 mysql存储引擎_数据_02

InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。
但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点：

事务控制

start transaction;
insert into goods_innodb(id,name)values(null,'Meta20');
...
commit;

mysql存储引擎用哪个好用 mysql存储引擎_数据_03

外键约束

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

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
	country_name varchar(100) NOT NULL,
	primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
	city_name varchar(50) NOT NULL,
	country_id int NOT NULL,
	primary key(city_id),
	key idx_fk_country_id(country_id),
	CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES
	country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

country_innodb是父表（主表）， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。

有外键的是子表

在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL和 NO ACTION。

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

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

SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

针对上面创建的两个表，子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新。

Restrict 和Cascade限制的是主表的增删改，对子表的增删改无用

删除主表中，有子表关联的信息，报错

mysql存储引擎用哪个好用 mysql存储引擎_数据库_04

更新主表信息，关联的子表字段也更新。

mysql存储引擎用哪个好用 mysql存储引擎_数据库_05

MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快。

对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

每个myISM表存储文件有三个，拓展名如下：

.frm (存储表定义)；
.MYD(MYData , 存储数据)；
.MYI(MYIndex , 存储索引)；

MERGE

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

对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

mysql存储引擎用哪个好用 mysql存储引擎_mysql_06

下面是一个创建和使用MERGE表的示例：

1）. 创建3个测试表 order_1990, order_1991, order_all , 其中order_all是前两个表的MERGE表：

create table order_1990(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_1991(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_all(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

2）. 分别向两张表中插入记录

insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');

3）. 查询3张表中的数据。

order_1990中的数据：

mysql存储引擎用哪个好用 mysql存储引擎_存储引擎_07

order_1991中的数据：

mysql存储引擎用哪个好用 mysql存储引擎_mysql_08

order_all中的数据：

mysql存储引擎用哪个好用 mysql存储引擎_数据库_09

4）. 往order_all中插入一条记录，由于在MERGE表定义时，INSERT_METHOD 选择的是LAST，那么插入的数据会想最后一张表中插入。

insert into order_all values(100,10000.0,'西安');

mysql存储引擎用哪个好用 mysql存储引擎_存储引擎_10

MEMORY

很少使用，一般用NoSQL代替

Memory存储引擎将表的数据存放在内存中。

每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。

MEMORY类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。

总结

存储引擎对比

特点	InnoDB	MyISAM	MEMORY	MeRGE
存储限制	64TB	有	有	没有
事务安全	支持
锁机制	行锁（适合高并发）	表锁	表锁	表锁
B树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持（5.6版本之后）	支持
集群索引	支持
数据索引	支持		支持
索引缓存	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低
内存使用	高	低	中等	低
批量插入速度	低	高	高	高
支持外键	支持

存储引擎的选择

InnoDB : 是Mysql的默认存储引擎，支持外键和事务处理。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作基本包含了CRUD所有操作，那么InnoDB存储引擎是比较合适的选择。InnoDB是唯一可以进行事务处理的存储引擎，对于对数据准确性要求比较高的系统，例如：财务系统，计费系统等。InnoDB是最合适的选择。
MyISAM ：此存储引擎适用于对事务的完整性、并发性要求不是很高，并且数据操作以读操作和插入操作为主的应用。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。
MEMORY：一般用NOSQL代替此引擎，很少使用。