Mysql常用引擎

Mysql引擎简介

Mysql支持多种存储引擎，下面几个操作可以直观的实践一下：

1.查询数据库支持哪些引擎

SHOW ENGINES \G

2.创建新表时指定引擎

CREATE TABLE test{
 id NOT NULL AUTO_INCREMENT,
}ENGINE=MYISAM

3.修改表的引擎

ALTER TABLE test engine = innodb;

各种存储引擎的特性

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

MyISAM

MyISAM是一个轻量级引擎，不支持事务，不支持外键，也因此其访问速度快。
应用场景：对事务完整性没有要求，或者以Select、Insert为主的应用
存储方式：

• .frm：存储表的定义；
• .MYD：MYDATA，存储数据；
• .MYI：MYIndex，存储索引；

优化：数据文件与索引文件可以放置在不同目录，以平均分布IO

MyISAM表支持三种不同的存储格式：

• 静态表：字段长度固定，自动以空格补足长度（取出后自动去除），存储速度快，容易缓存，但占用空间较大
• 动态表：可以根据实际需要动态调整字段长度，节省空间，会产生碎片，需定期清理
• 压缩表：占用空间小

InnoDB

InnoDB存储引擎提供了事务安全功能，但也因此导致其写效率低于MyISAM，并且会占用更多的磁盘空间以保存数据和索引。
应用场景：用来出来大量的短期事务。
存储方式：

• 共享表空间存储：
  
  • .frm:存储表的定义
  • innodb_data_home_dir和innodb_data_file_path目录：存储数据与索引，可以包含多个文件
• 多表空间存储：
  
  • .frm:存储表的定义
  • 每个表的数据和索引单独保存在.ibd中，如果是分区表，则每个分区对于单独的.ibd文件。

特点：

1. 自动增长列：

InnoDB表的自动增长列可以手工插入，如果插入值为空或者为0，那么实际插入值为自动增长后的值。

对于InnoDB表，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列。

但是对于MyISAM表，自动增长列可以是组合索引的其他列，但是这样对导致自动增长列会根据前置索引排序结果来进行递增，即先用

前置位的所以排序，在依据排序结果来自增。

例如：

create table test(
d1 smallint not null auto_increment,
d2 smallint not null,
name varchar(10),
index(d2,d1)
)engine=myisam;

insert into test(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');

select * from test;

Which is mean that:如果接下来我们插入一条d2=1的数据，那么这条数据的d1将是会是3，因为d1的增长依据是d2，而目前已经存在

两个d2=1，所以新插入的数据的d1将会是3

So，如果接下来我们再插入一条数据，whose d2 = 5,现在数据库中不存在d2=5的数据，所以该条数据的d1将会是1。

---

2. 外键约束

MySQL中只有InnoDB支持外键，在创建外键时，要求父表必须有对应的索引，字表在创建外键的时候也会自动创建对应的索引。
外键约束例子：

/*创建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;

对于上述两个表，子表city中的country_id是一个外键，对应着country表中的country_id，

且声明了 ON DELETE RESTRICT ON UPDATE CASCADE

对于外键的操作有： RESTRICT、ＣASCADE、SET NULL、NO ACTION。

• RESTRICT、NO ACTION ：子表有关联的情况下，父表不能更新
• SET NULL：父表在更新或删除时，子表对于行的对应字段会变为NULL
• ＣASCADE：父表在更新或删除时，子表对于行会更新或删除

---

MEMORY

MEMORY存储引擎使用存在于内存中的内容来建表。由于数据是放在内存中的，访问速度非常快，默认使用HASH索引，但是一旦服务关

闭，表中的记录就会清空。
存储方式：只有一个.frm文件用于存储表结构。
应用场景：主要用于内容变化不频繁的数据，或者统计操作的中间结果。可以使用-init-file选项在MYSQL启动的时候将数据加载

进表中。

---

MERGE

MERGE存储引擎是一组MyISAM表的组合，组合内的表的结构必须完全相同，MERGE表本身不存储数据，但可以通过MERGE表进行增删改查

操作，这些操作实际是对组合内的MyISAM表进行的。
存储方式：

• .frm文件，用来保存表定义
• .MRG文件，用来保存组合表信息

MERGE表例子：

/*创建2张结构相同的MyISAM表*/
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;

/*创建1张MERGE表，表结构与上面两张一样，注意最后一行，引擎设置不一样,*/
/*末尾的INSERT_METHOD=LAST指的是在插入时从最后一个表插入，*/
/*可选值为FIRST、LAST，不定义或者定义为NO时，不能插入*/
create table payment_all(
country_id smallint,
payment_date datetime,
 amount DECIMAL(15,2),
KEY idx_fk_country_id (country_id)
)engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

/*向MyISAM中插入数据*/
insert into payment_2006 values(1,'2006-01-01',10000),(2,'2006-02-01',20000);
insert into payment_2007 values(1,'2007-03-01',30000),(2,'2007-04-01',40000);

现在分别查看三个表中的数据

select * from payment_2006;

select * from payment_2007;

select * from payment_all;