MySQL之存储引擎

MySQL之存储引擎

一 概念

存储引擎就是存储数据、建立索引、更新数据以及查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
Oracle和SqlServer数据库只有一种存储引擎，MySQL提供了插件式的存储引擎结果。所以MySQL存在多种存储引擎，可以根据需要使用相应的存储引擎。MySQL5.5之后的默认存储引擎是InnoDB，有关MySQL的存储引擎如下图所示：
使用命令show engines;

使用show variables like ‘%storage_engine%’;显示存储引擎的信息

二 存储引擎的特性

对几种常用的存储引擎进行对比，重点掌握InnoDB和MyISAM如下图所示：

三 存储引擎特性详解

1 InnoDB存储引擎
（1）事务控制
InnoDB存储引擎提供了具有事务提交、回滚、崩溃恢复能力的事务安全。但相对于MyISAM而言，InnoDB写的效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

① 创建表
create table goods_innodb(
	id int not null auto_increment,
	name varchar(20) not null,
	primary key(id)
)engine=innodb default charset=utf-8;

② 开启事务并操作数据库，后提交
start transaction;
insert goods_innodb(id,name) values(null,'Meta20');
commit;

未提交事务发现插入失败，提交后则插入成功，测试后发现InnoDB存储引擎是支持事务的。
（2）外键约束
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

① 创建父表，country_id为主键索引
create table country_innodb(
	country_id int not null auto_increment,
	country_name varchar(20) not null,
	primary key(country_id)
)engine=innodb default charset=utf-8;

② 创建子表city_innodb,country_id为外键，对应country_innodb的主键country_id
create table city_innodb(
	city_id int not null auto_increment,
	city_name varchar(20) 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=utf-8;

③ 插入数据
insert into country_innodb values(null,'China'),(null,'Japan'),(null,'America');
insert into city_innodb values(null,'Xian',1),(null,'NewWork',3),(null,'Beijin',1);

在创建索引时，可以指定在删除、更新父表时，对子表进行相应操作，包括restrict,cascade,set null和 no action.
（1）restrict和no action相同，指在子表有关联记录下，父表不能更新
（2）cascade表示父表在更新或删除时，子表也更新或删除对应的记录
（3）set null 表示父表在更新或删除时，子表对应的字段被set null

测试截图如下：
（1）删除

（2）更新

2 MyISAM存储引擎
（1）不支持事务
MyISAM不支持事务，也不支持外键，优势是访问速度快，对事务没有要求的可以设置成该引擎。

① 创建表
create table goods_myisam(
	id int not null auto_increment,
	name varchar(20) not null,
	primary key(id)
)engine=myisam default charset=utf-8;

② 开启事务并操作数据库，不提交
start transaction;
insert goods_innodb(id,name) values(null,'Meta20');

经过测试发现未提交事务也插入成功，所以MyISAM不支持事务。

四 存储引擎的选择

（1）InnoDB：MySQL的默认存储引擎，用于事务处理应用程序，支持外键。如果对事务的完整性有较高的要求，在并发条件下要求数据的一致性，除了插入和查询外还包含很多的更新和删除操作，选择InnoDB比较合适。
（2）MyISAM：应用以读操作和插入操作为主，进行少量的更新和删除操作，并且对事务的完整性和并发性要求不高，选择MyISAM合适。
（3）MEMORY：将所有的数据保存在ARM中，快速定位记录和其他类似数据环境下，它的缺陷是对表的大小有限制，太大的表无法缓存在内存中，它通常用于更新不频繁的小表，拥有快速得到访问结果。