深入解析MySQL存储引擎特性及使用场景-优快云博客

2019独角兽企业重金招聘Python工程师标准>>>

MySql中的存储引擎（表类型）

1）MyISAM：静态表：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。注意以下系统忽略空格的情况：

create table myisam_char(name char(10)) engine=myisam;(表类型为静态表)

insert into myisam_char values('abcde '),(' abcde');

mysql> select name,length(name) from myisam_char;

	+--------+--------------+

	| name   | length(name) |

	+--------+--------------+

	| abcde  |            5 |

	|  abcde |            6 |

	+--------+--------------+

动态表：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。

压缩表：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

2）InnoDB: 外键约束，自动增长列等特性

	create table country(
		id smallint unsigned not null auto_increment,
		name varchar(50) not null,
		last_update timestamp not null default current_timestamp on update current_timestamp,
		primary key(id)
	);

	create table city(
		id smallint unsigned not null auto_increment,
		name varchar(50) not null,
		country_id smallint unsigned not null,
		last_update timestamp not null default current_timestamp on update current_timestamp,
		primary key(id),
		key index_fk_country_id(country_id)
	);

	ALTER TABLE `city` ADD CONSTRAINT `fk_city_foreign` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

创建子表时使用了ON DELETE RESTRICT ON UPDATE CASCADE;

restricy和no action意思相同，是指限制在子表(city)有关联数据的情况下删除更新父表(country)对应数据 cascade表示父表在更新或者删除数据时，更新或删除子表对应的数据 set null表示父表在更新或者删除数据时，子表对应的数据set null

在导入多个表数据时，如果需要关闭外键约束检查，使用 set foreign_key_checks=0;1为开启

3）Merge：

	create table table1(
		id smallint,
		date datetime,
		key index_fk_1(id)
	)engine=myisam;

	create table table2(
		id smallint,
		date datetime,
		key index_fk_1(id)
	)engine=myisam;
	create table table_all(
		id smallint,
		date datetime,
		index(id)
	)engine=merge union=(table1,table2) insert_method=last;

三张表的结构必须相同，merge表本身没有数据，对merge表进行查询，更新，删除操作实际上是对内部的myisam表进行的。对merge表的插入操作，是通过insert_methid来定义插入的表的，使用first,last指定第一或最后一张表，不定义或者定义为no时，表示不能往表中插入数据

4）Memory：使用存在于内存中的内容来创建表。每个表对应一个磁盘文件（.frm），访问速度很快，因为它的数据放在内存中，并且默认使用hash索引。但是一旦服务关闭，表中的数据就会丢失。可以在启动服务时使用--init-file选项，把insert into语句放入文件中，就可以导入数据。当不使用时应该释放表占用的内存，删除表

5）常用存储引擎的使用环境：

MyISAM：默认的mysql插入式存储引擎。如果应用以读，插操作为主，只有很少的更新和删除操作，对事务的完整性，并发性要求不高，则可以选择这种引擎。在web,数据仓储下常用

InnoDB:用于事务处理应用程序，支持外键。如果应用对事务完整性，并发一致性要求高，对数据出插入，查询外，还包含很多的更新，删除，则可以选择这种引擎。在计费系统和财务系统下常用

Memory：所有数据都保存在RAM中，在需要极速访问数据的情况下使用。缺陷是对表的大小有限制。常用于更新不太频繁的小表，用于快速访问结果。

Merge：用于将一系列等同的MyISAM表以逻辑的方式组合在一起，并作为一个对象引用他们。Merge的有点在于突破单表的大小限制。常用于数据仓储等VLOB环境下。

转载于:https://my.oschina.net/aptx4869/blog/348726