MySQL-浅谈存储引擎

MySQL中的存储引擎

一、前言

数据库作为存储数据的仓库，可能大家会想，关系型数据库把我们的数据一行一行地存储不就完事了吗？哪里又冒出个存储引擎呢？

之所以有多个存储引擎，是因为我们对表的使用场景并不总是相同的，有时候可能我们需要频繁的select 和insert操作，而有的时候可能需要频繁的update 操作。这个时候，不同引擎的数据表table 就有着不同的性能了。

1、引擎之间的区别

1. 引擎engine 不同，数据库把数据data 保存至本地的方式（文件的结构）是不一样的，物理结构影响着数据的操作。
2. 其次，不同的引擎，所拥有的索引也是不同的。所有的引擎都拥有BTree 索引。
3. 还有，不同的引擎，还意味着是否支持事务transaction 操作。目前，只有InnoDB 引擎支持事务操作。

引擎的说明对象

需要说明的是，引擎是针对数据表table 而言的，也就是说我们讨论的是一张数据表以何种引擎的方式来进行数据存储。

---

二、引擎相关操作

1、查看数据库所支持的引擎以及说明

我们可以查看MySQL数据库支持的引擎以及相应的说明。

命令：show engines;

在MySQL里面，我们主要会使用到的引擎有InnoDB （默认引擎）、MyISAM、 MRG_MYISAM (原名MERGE) 、MEMORY 。其他引擎可能非专业DBA 接触不到，所以接下来的存储引擎将围绕上面4个展开。

顺便说一句，我们日常绝大部分的都是InnoDB 和MyISAM 引擎。当然，也有一些非官方的引擎，以其高性能而流行，比如说：TokuDB ，它有多项性能指标吊打InnoDB 。

2、查看数据表table 所使用的引擎

命令：

show create table userinfo \G;  # \G格式化输出

上面的命令，使用查看建表语句，可以查看数据表使用的引擎，当然我们默认的都是InnoDB 。

生成表时指定引擎

命令：

create table tb1(
...
...
)engine=MyISAM default charset=utf8;

修改表的引擎

命令：

alter table t1 engine=InnoDB;

---

三、详细对比各个引擎之间的区别

特点	InnoDB	MyISAM	MRG_MYISAM（原MERGE）	MEMORY
存储大小限制	有	64TB	没有	有
事务安全	支持
锁机制	行锁、表锁	表锁	表锁	表锁
BTree索引	支持	支持	支持	支持
Hash索引				支持
fulltext索引		支持
集群索引	支持
数据可压缩		支持
空间使用	高	低
内存使用	高	低
批量插入的速度		高
支持外键	支持

我们说过，日常中使用最多的是InnoDB 和MyISAM 引擎，从上表可以看出，这2者互有长处与短板。因此，我们要根据业务场景来选择合适的引擎。

---

四、各个引擎的介绍

1、InnoDB 引擎

InnoDB 引擎很重要的2项功能（也是独有）是支持事务 以及支持外键 。

1.1 支持事务

事务的重要性不言而喻，金融上重要的转账、交易等操作，为了应对突发事故如停电，需要对数据的操作具有原子性。==事务的具体内容，放置另一篇章进行讲解==。 这里我们需要知道，对于涉及到事务的数据表，我们只能选择InnoDB 引擎来存储。

1.2 支持外键

外键是一种主从的对应关系。只有InnoDB 支持外键foreign key ，下面来讲解相关的外键操作。

场景假设

现在假设我们开了一个书店bookstore ，我们的书源都是由书籍市场bookmarket 提供，所以我们的书的bookname 应该作为一个外键，参照bookmarket 的主键（bookname）。

模拟

下面我们来生成上面2个表，并设置相应的主键与外键。

drop table if exists bookmarket;
    create table bookmarket(
    bookname varchar(50),
    price double,
    primary key(bookname)
);

drop table if exists bookstore;
    create table bookstore(
    id int primary key auto_increment,
    bookname varchar(50),
    price double,
    constraint `fk_bookname` foreign key(bookname) references bookmarket(bookname)
);

下面我们来复习以下外键相关的知识：

1. 如果我们想在从表中插入数据，那么data 中的外键列的值必须在主表中存在。换句话说，我们想要销售一本新书唐吉坷德，那么在bookmarket中就必须有这本书，我们是不能销售没有货源的书的。

设置主表的DML操作->触发从表操作

这个操作的名称不好取，我们理解还是很好理解的。在对主表的数据进行CURD 中的删除和更新时，我们的从表应该做出哪些相应的操作呢？

我们可以选择的方案有3种：

1. 从表外键数据存在的情况下，主表不允许删除和更新。RESTRICT 或NO ACTION ，这也是未指定时，默认的设置，即on update restrict on delete restrict。
2. 主表删除和更新的时候，从表的数据也进行删除和更新。CASCADE
3. 主表删除和更新的时候，从表的数据对应的列设置为NULL 。SET NULL

模拟

我们现在插入一些相关的数据。

insert bookmarket values('book_1',23.42),('book_2',22.2),('book_3',44.1);

insert bookstore values(null,'book_1',30),(null,'book_2',32.2);

然后，我们以RESTRICT 为例，先修改从表的外键。

alter table bookstore drop foreign key `fk_bookname`;

alter table bookstore add constraint `bookstore_fk_bookname` foreign key(bookname) references 
bookmarket(bookname) on delete restrict on update restrict;

然后，我们试图删除主表的一行数据：

这里提示，不能删除数据，因为数据被外键所引用。顺便一提，默认情况下，外键会采用on delete restrict on update restrict 的方案。

值得一提的是，当我们使用外键的时候，最合理的方式应该是on delete restrict on update cascade ，禁止删除，更新同步。

忽视外键

有一个小技巧，那就是当我们导入多个表的数据时，由于外键的存在，我们导入表的顺序就有先后之分。那么我们可以暂时关闭外键检查，这样就无所谓顺序了。

set forign_key_checks=0;
...
...
set forign_key_checks=1;

1.3 存储方式

该点水平不够。

2、MyISAM 引擎

在5.5版本之前，MySQL的默认引擎就是这个，使用的也很广泛。它的优点在于访问速度快，适合经常进行select和insert 操作的场景。缺点就是不支持事务和外键。

每个MyISAM 表在物理上存储3个文件，分别是：存储表定义、存储数据、存储索引。

适用场景

适合经常需要进行select和insert操作的表。

存储格式

MyISAM 表支持3种存储格式，分别是静态表（默认）、动态表、压缩表。

1. 静态表，优点是存储速度快，缺点是空间耗费大。而且插入的数据中，定长数据格式的数据列，其字符后面的空格会被删除。
2. 动态表，优点是空间小，缺点是会产生垃圾碎片。

模拟

我们创建一个MyISAM 表，有一个列设置为定长数据。（如char），注意varchar是无效的。

drop table if exists isam_t;
    create table isam_t(
    id int,
    name char(12)
)engine=myisam charset=utf8;

接下来，我们插入数据并验证。

insert isam_t values(2,' tom '),(3,'  kim'),(4,' jol   ');
select id,name,length(name) from isam_t;

我们可以看出，name 后面的空格已经被去掉了。

3、MRG_MYISAM 引擎

望文生义，MRG_MYISAM 引擎就是将MyISAM 引擎进行合并。它是一组MyISAM 表的组合。它要求这些表的结构完全相同，MRG_MYISAM 表本身没有数据，它只是一个容器，操作的对象还是内部的MyISAM 表。

很显然，我们可以把它看成是一个分表的工具。前面的引擎对比中，我们知道MyISAM 表的存储大小是有限制的，有时候一张表存储不下所有数据，那么我们分成多张结构完全相同的表进行分表存储。这个时候，就是我们MRG_MYISAM 表的用武之地了。我们可以将所有的分表聚合在一起，这样就能够对所有数据进行操作了。

3.1 表的CURD 操作

因为这张表只提供一个容器的功能，所以MRG_MYISAM 表的CURD 操作和传统意义表的操作是不一样的。

1. create ，定义各个分表的聚合。
2. select 、update 都是操作其内部的分表。
3. delete 只是删除该表的定义，不影响内部的分表。
4. insert ，需要指定数据插入的表，insert_method的可选值只能是first和last 和no ，no 表示不能执行插入操作，默认也是no 。

模拟

我们先生成2张MyISAM 表，然后生成一个MRG_MYISAM 表，包含前面2个分表。这里我们指定insert_method 为last 。

drop table if exists bill_2016;
    create table bill_2016(
    id int primary key,
    time date,
    cost double
)engine=myisam;

drop table if exists bill_2017;
    create table bill_2017(
    id int primary key,
    time date,
    cost double
)engine=myisam;

drop table if exists bill_merge;
    create table bill_merge(
    id int primary key,
    time date,
    cost double
)engine=merge union=(bill_2016,bill_2017) insert_method=last;

然后我们往2张分表中插入数据。

insert bill_2016 values(1,'2016-1-2',22.4),(2,'2016-2-3',44.2);
insert bill_2017 values(100,'2017-4-2',55.2),(102,'2017-6-3',88);

select * from bill_2016;
select * from bill_2017;

我们查看MRG_MYISAM 表的数据。

可以发现，bill_merge 包含了2个分表的数据。

这时候我们向bill_merge 插入数据，按照预期，数据应该被插入了第2张表，也就是bill_2017 中。

3.2 存储结构

MRG_MYISAM 表在物理上生成2个文件，其一是存储表定义，其二是包含组合表的信息和插入依据。

4、MEMORY 引擎

MEMORY 引擎使用内存中的数据来创建表，在物理上只对应一个文件。MEMORY 表的访问速度非常快，因为它的数据是放在内存中的，如果服务一旦关掉，那么表中的数据就会丢失。

适用场景

主要用于存储内容变化不频繁的代码表，或者是作为统计操作的中间结果表。类似于一个中间数据存储站，提供给其他操作进行查询。需要注意的是，该表的数据并不会写入磁盘，所以数据的保存需要注意。

模拟

我们使用bookmarket 表的数据，来生成我们的MEMORY 表。

create table emp_memory engine=memory
select * from bookmarket;

我们在生成索引的时候，可以选择Hash或者BTree 这2种。

create index idx_bookname using hash on emp_memory(bookname);
show index from emp_memory;

当我们不需要MEMORY 表的时候，我们应该删除从而释放内存。

可以是delete from 、truncate table 、drop table 等。