5.表类型(存储引擎)的选择

MySQL存储引擎概述

插件式存储引擎是MySQL数据库最重要的特点之一,用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。

MySQL支持的存储引擎有MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BALCKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

创建表不指定存储引擎，则会使用默认的存储引擎，如果要修改默认的存储引擎，可以在参数文件中设置default-storage-engine。看看默认的存储引擎可以使用如下命令：

查看默认存储引擎

 SHOW VARIABLES LIKE 'default_storage_engine%';

修改默认存储引擎

修改默认存储引擎只需修改my.ini文件中default-storage-engine

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

查看MySQL支持的存储引擎

第一种方法

show engines \G;

第二中方法

show variables like 'have%';

其中value显示为disable的记录表示支持改存储引擎，但是数据库启动的时候会被禁用

指定表的存储引擎

在创建表时指定存储引擎

在创建表的时候可以通过ENGINE关键字来指定新建表的存储引擎。

create table e1(id int(11) primary key auto_increment)engine=MyISAM default charset=utf8;

修改表的存储引擎

查看表的存储引擎

法一

show create table table_name \G;

--example
show create table e1;

法二

show table status from database_name where name='table_name' \G;
--example
show table status from test where name='e1' \G;

修改存储引擎

• 语法

alter table table_name engine=table_engine

• 使用

alter table e1 engine = innodb;

各存储引擎的特点

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
外键支持		支持

MyISAM

MyISAM是MySQL默认的存储引擎，不支持事务也不支持外键，其优势是访问速度快，在对事务完整性没有要求或以select、insert为主的应用基本上都可以使用这个引擎来创建表。

每个M有ISAM在磁盘上存储成3个文件，其文件名和表名相同，但扩展名分别是：

• .frm(存储表定义)
• .MYD（MYData，促成农户数据）;
• .MYI（MYIndex，存储索引）；

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

如果需要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件的路径必须是绝对路径，并且是可访问的。

MyISAM类型的表可能会损坏，损坏的表可能不能访问，会提示需要修改或者访问后返回错误的结果。MyISAM存储引擎提供了修复工具，可以使用CHECK TABLE语句来检查MyISAM表的健康，并且用REPAIR TABLE语句修复一个损坏的MYISAM表。

MyISAM表支持3种不同的存储格式，分别是：

• 静态（固定长度）表；
• 动态表；
• 压缩表。

静态表

其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常快速，容易缓存，出现故障容易修复，缺点是占用的空间比动态表多，静态表的数据在存储的时候会按照类定义的宽度来存储数据，数据长度为达到定义列就宽度的部分使用空格来补齐，在访问数据的时候会自动去掉这些空格。

但是如果在存储的时候数据本身末尾含有空格，那么在访问这些数据的时候，数据本身的末尾的空格也会被去掉，从而丢失了源数据末尾空格

一以下是对这种情况的一个示例说明

create table MyISAM_demo(data char(10))engine=myISAM;
insert into MyISAM_demo values('asd'),('sdfdg   '),(' dfsf  ');
select data,length(data) from MyISAM_demo;

可以看出数据末尾的空格被去掉了，而前面的空格得以保留

动态表

动态表包含变长字段，记录不是固定长度的，这种存储方式的优点是占用的存储空间相对较少，但是频繁的更新和删除记录产生数据碎片，需要定期执行OPTIME TABLE语句或myisamchk -r命令来改善性能，兵器而在出现故障恢复时比较困难。

压缩表

压缩表由myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

InnoDB

InnoDB存储引擎具有提交、回滚和崩溃恢复能力的事务安全，但是对比MyISAM存储引擎，InnoDB写入的效率会差一些并且会占用更多的磁盘空间用于保留数据和索引。

自动增长列

InnoDB表的自动增长列可以手动插入，但是如果手动插入的是NULL或者0，则实际插入的将是自动增长后的值。如下示例：

create table autoincre_demo(
    id int(11) primary key auto_increment,
	name varchar(10)
);

insert into autoincre_demo values(1,'a'),(0,'b'),(null,'c');
select * from autoincre_demo;

可以看到如果手动设置的值为0或null最终插入的值是自动增长后的值

可以通过 ALTER　TABLE　＊＊＊　AUTO_INCREMENT=n;语句来强制设置自动增长列的初始值，默认从1开始，但是改强制指定的初始值保存在内存中，如果该值在使用之前数据库重新启动，那么这个强制的默认初始值就会丢失，需要在数据库启动后重新设置。

可以使用 LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值。通过以下例子来展示：

insert into autoincre_demo values(4,'d');
select LAST_INSERT_ID();

insert into autoincre_demo (name) values('e'),('f'),('g');
select LAST_INSERT_ID();

对于innoDB表，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长里额可以是组合索引的前面激烈进行排序后递增的。例如，创建一个新的MyISAM类型的表autoincre_demo，自动增长列d1作为组合索引的第二列，最该表插入一些记录后，可以发现增长列是按照组合索引的第一列d2进行排序后递增的：

--创建表
create table autoincre_demo2(
	d1 smallint not null auto_increment,
	d2 smallint not null,
	name varchar(10),
	index(d2,d1)
)engine=MyISAM;
--插入数据
insert into autoincre_demo2(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
--查看数据
select * from autoincre_demo2;

外键约束

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

如下，其中country是父表，country_id为主键索引，city表是子表，country_id字段对country表的country_id有外键。

--创建country表
create table country (
	country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	country VARCHAR(50)  NOT NULL,
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
--创建city表
CREATE TABLE city (
	city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	city VARCHAR(50) NOT NULL,
	country_id SMALLINT UNSIGNED NOT NULL,
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (city_id),
	KEY idx_fk_country_id (country_id),
	CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包RESTRICT、CASCADE、SET NULL和 NO ACTION。其中RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，子表对应的字段被 SET NULL.选择后两种方式的时候要谨慎,可能会因为错误的操作导致数据的丢失.

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

select * from contry where country=1;

select * from city where country_id=1;

delete from country where country_id=1;
--错误信息
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country`
ry_id`) ON UPDATE CASCADE)

当某个表被其他表创建了外键参照,那么该表的对应索引或主键禁止被删除.

在导入多个表的数据的时候,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查;同样,在执行LOAD DATA和ALTER TABLE操作的时候,可以通过暂时关闭外键约束来加快处理速度,关闭的命令是　SET FOREIGN_KEY_CKECKS=0;,执行完成后,通过执行 SET FOREIOGN_CHECK= 1;语句来改回原状态.

对于InnoDB类型的表,外键的信息通过使用show create table或者show table status 命令来显示.

show table status like 'city' \G;

存储方式

InnoDB有以下两个存储方式

• 使用共享空间存储

这种方式的创建表的表结构保存在.frm文件中,数据和索引保存下innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件.

• 使用多表空间存储

这种方式出创建的表的表结构任然保存在.frm文件中,但是每个表的数据和索引保存在.ibd中,如果是个分区表,则每个分区对应单独的.ibd问文件,文件名是’表名+分区名’,可以在创建分区的时候知道每个分区的数据文件的位置,以此来将表的IO均匀分布在多个磁盘上.

要使用多表空间的存储方式,需要设置参数innodb_file_per_table,并重新启用服务后才可以生效,对于新建的表按照多表空间的方式创建,已有的表仍然使用共享表空间存储.如果将已有的多表空间方式修改回共享表空间的方式,则新建表会在共享表空间中创建,但已有的多表空间的表仍然保存原来的访问方式.所以多表空间的参数生效后,只对新建的表生效.

多表空间的数据文件大小没有限制,不需要设置初始大小,也不需要文件的最大限制,扩展大小等参数.

对于使用多表空间特性的表,可以比较方便的进行单表备份和恢复操作,但是直接复制.ibd文件时不行的,因为没有共享表空间的数据字典信息,直接复制.ibd文件和.frm文件恢复时是不能被正确识别的,但可以通过以下命令:

ALTER TABLE tb1_name DISCARD TABLESPACE;
ALTER TABLE tb1_name IMPORT TABLESPACE;

将备份恢复到数据库中,但是这样的单表备份,只能恢复到表原来的数据库中,而不能恢复到其他的数据库中,如果要将表恢复到目标数据库,则需要通过mysqldump和mysqlimport来实现.

MEMORY

MEMORY存储引擎使用存在内存中的内容来创建表.每个MEMROY表实际只对应一个磁盘文件,格式是.frm. MEMORY类型的表访问非常快,因为他的数据是放在内存中的,并且默认使用HASH索引,但是一旦服务器关闭,表中的数据就会丢失.

--创建表
CREATE TABLE tab_memory ENGINE=MEMORY
SELECT city_id,city,country_id 
FROM city GROUP BY city_id;
select count(*) from tab_memory;

show table status like 'tab_memory' \G;

给MEMORY表创建索引的时候,可以指定使用HASH索引还是BTREE索引.

create index mem_hash USING HASH on tab_memory (city_id);

show index from tab_memory \G;

DROP index mem_hash on tab_memory;

create index mem_btree USING BTREE on tab_memory(country_id);

show index from tab_memory \G;

在启动MySQL服务的时候使用–init-file选项,把INSERT INTO …SELECT 或 LOAD DATA INFILE这样的语句放入这个文件中,就可以在服务启动时从持久稳固的数据源装载表.

服务器需要足够的内存来维持所有在同一时间使用的MEMORY表,当不再使用MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM 或TRUNCATE TABLE,或者整个地删除表(使用DROP TABLE操作).

每个MEMORY 表中可以放置的数据量大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16MB,可以按照需求加大.另外,在定义MEMORY 表的时候,可以通过MAX_ROWS子句来指定表的最大行数.

MEMORY类型的存储引擎主要用在哪些内容变化不频繁的代码表,或者作为统计操作的中间结果表,便于高效的对中间结果进行分析并得到最终的统计结果.对memory存储引擎的表的更新操作需要谨慎,因为数据并没有实际写入到磁盘中,所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑.

MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MYISAM表的结构必须完全相同,MERGE表本身并没有数据,对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部实际的MyISAM表进行的.对于MERGE类型表的插入操作,是通过INSERT_METHOD字句定义插入的表,可以有三个不同的值,使用FIRST或LAST值是的插入的操作被相应的作用在第一或最后一个表上,不定义这个字句会定义为NO,表示不能对这个MERGE表执行插入操作.

可以对MERGE表进行DROP操作,这个操作只是删除MERGE的定义,对内部的表没有任何的改变.

MERGE在磁盘上保留两个文件,文件名以表的名字开始,一个.frm文件存储表定义,另一个.MRG文件包含组合表的信息,包括MERGE表由哪些表组成、插入新的数据时的依据。可以通过修改。ＭＲＧ文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。

如下示例:

--创建表
create table payment_2006(
	country_id smallint,
	payment_date datetime,
	amount DECIMAL(15,2),
	KEY idx_fk_country_id (country_id)
)engine=myisam;

create table payment_2007(
	country_id smallint,
	payment_date datetime,
	amount DECIMAL(15,2),
	KEY idx_fk_country_id (country_id)
)engine=myisam;

create table payment_all(
	country_id smallint,
    payment_date datetime,
    amount decimal(15,2),
    INDEX(country_id)
)engine=MERGE union = (payment_2006,payment_2007) INSERT_METHOD = LAST;
--插入数据
insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);
insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);
--分别查看表中数据
select * from payment_2006;
select * from payment_2007;
select * from payment_all;

可以发现,payment_all中的数据是payment_2006和payment_2007表的记录合并后的结果集.

接下来,向MERGE表中插入一条数据,由于MERGE表的定义是INSERT_METHOD=LAST,就会向最后一个表中插入记录,所以虽然这里插入的记录是2006年的,但仍然会写到payment_2007表中.

insert into payment_all values(3,'2006-03-31',112200);

select * from payment_all;

select * from payment_2007;

选择合适的存储引擎

在选择存储引擎的时候,应该根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行结合.

• MyISAM

默认的存储引擎,如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎时非常合适的。MyISAM是在web、数据仓库和其他应用环境下最长使用的存储引擎之一。

• InnoDB

用于事务处理程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包括很多的更新、删除操作，那么InnoDB存储引擎时比较合适的选择。InnoDB除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等数据准确性要求比价高的系统，InnoDB 是合适的选择。

• MEMORY

将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

• Merge

整性、并发性要求不是很高，那么选择这个存储引擎时非常合适的。MyISAM是在web、数据仓库和其他应用环境下最常使用的存储引擎之一。