MySQL存储引擎的区别

65 阅读 0 评论 43 点赞

我是靠谱客的博主冷傲哈密瓜，最近开发中收集的这篇文章主要介绍MySQL存储引擎的区别，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一.mysql中myisam，innodb和memory三个存储引擎的区别

1、区别：
1） MyISAM管理非事务表。提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，是默认的存储引擎，除非配置MySQL默认使用另外一个引擎。

2）MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。
注释：MEMORY存储引擎正式地被确定为HEAP引擎。

3）InnoDB和存储引擎提供事务安全表，默认被包括在所有MySQL 5.1二进制分发版里，可以按照喜好通过配置MySQL来允许或禁止任一引擎。

2、功能点简介
1）MyISAM存储引擎
MyISAM存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。
myisam类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

（1）静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样mysql就会自动使用静态myisam格式。

使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是有空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

（2）动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生。随着数据变化的怎多，碎片就会增加，数据访问性能就会相应的降低。

（3）压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。

2）MEMORY存储引擎：
（1）memory存储引擎相比前面的一些存储引擎，有点不一样，其使用存储在内从中的数据来创建表，而且所有的数据也都存储在内存中。

（2）每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。

（3）memory存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，如果读者希望使用B树型，则在创建的时候可以引用。

（4）memory存储引擎文件数据都存储在内存中，如果mysqld进程发生异常，重启或关闭机器这些数据都会消失。所以memory存储引擎中的表的生命周期很短，一般只使用一次。

3）innoDB存储引擎：

（1） innodb存储引擎该mysql表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

（2）innodb支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空的话怎会进行自动存现有的值开始增值，如果有但是比现在的还大，则就保存这个值。

（3）innodb存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。

（4）innodb存储引擎最重要的是支持事务，以及事务相关联功能。

（5）innodb存储引擎支持mvcc的行级锁。

1.myisam，innodb和memory的区别如下：

2：InnoDB存储引擎
2.1：InnoDB具有事务，回滚，崩溃修复能力和多版本并发的事务安全

2.2：关于InnoDB的auto_increment列：

2.2.1：InnoDB支持自动增长列，此列不能为空，且值必须唯一

2.2.2：此列必须为主键。插入时，不指定值，默认是自增都的值。指定0或者NULL时，也是自增后的值。指定合法且不存在的值，则此后的自动从该值开始

2.3：InnoDB中的Foreign Key：

InnoDB支持外键。外键所在表通常称为子表。所依赖表称为父表。且父表中，被子表关联的字段必须为父表的主键。
（外键：可以简单的理解为：当对父表的数据进行更新，删除，添加时，子表的数据也会随之变化。例子很多，此处不举例）

2.4：InnoDB的存储引擎及其优缺点：
InnoDB存储引擎的存储格式有三个文件：
.frm表结构文件，此文件存放表创建时的字段定义等信息。
表的数据文件，存放在innodb_data_home_dir目录下，
表的索引文件，存放在innodb_data_file_path目录下
InnoDB存储引擎具有良好的ACID特性。
InnoDB的缺点：读写效率相对MYISAM比较差。占用的磁盘空间比较大。

2.5：InnoDB的理想使用场合：
高并发，更新操作比较多的表。需要使用事务的表。对自动灾难恢复有要求的表。

3：MYISAM存储引擎：

3.1：MYISAM存储引擎表的存储有三个文件：
.frm文件，存储表的结构
.myd文件，存储表的数据
.myi文件，存储表的索引

3.2：MYISAM存储引擎的存储格式

3.2.1：静态型
表的所有列都是静态的（定长的）。这种情况下，维护和访问预定义格式存储的数据需要的开销很低。但是，需要的空间会更多，原因是，数据在存储的时候，使用每个列的最大空间来存储，有浪费的空间。当然，磁盘这个都不是问题。

3.2.2：动态型
指标的列中有动态型的列（不定长的）。优点是：使用空间少。缺点是：更新数据时，需要移动数据，产生内部碎片的同时，也降低了维护和访问的效率。
对于这个问题，我们可以：（1）尽量的使用定长字段（2）使用optimize table语句，整理表中的碎片

3.2.3：压缩型：
针对应用程序的声明周期中，只读的数据表，我们可以通过：myisampack工具转化为MYISAM压缩表，以减少使用的磁盘空间

3.4：MYISAM存储引擎的优缺点：
优点：占用空间小，处理速度快（相对InnoDB来说）
确定：不支持事务的完整性和并发性

4：Memory存储引擎

4.1：Memory存储引擎的文件存储形式
Memory存储引擎也会再磁盘上形成一个 .frm的表结构文件，只是表的数据件并不以文件的形式存放在磁盘上。鉴于其数据存放在内存里，因此，访问速度更快。但需要考虑的是：内存上数据的持久性。

4.2：Memory存储引擎的索引类型
默认的是哈希索引，也支持BTree索引

4.3：Memory存储引擎的数据存储周期
数据存放在内存上，一旦服务器关机，数据将不再存在

4.4：Memory存储引擎的优缺点：
Memory存储引擎不支持变长表列
MySQL4.1.0之前，不支持auto_increment列

4.5：Memory存储引擎的使用场合
速度要求快的，临时数据
丢失以后，对项目整体没有或者负面影响不大的时候。

http://zhidao.baidu.com/question/584306809482881565

二.MySQL InnoDB/MYISAM/MERGE/BDB/HEAP的区别

官方准确解释

·MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

·InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

·BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

·Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

·Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

·Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

·Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

·Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

·Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

请记住，对于整个服务器或方案，你并不一定要使用相同的存储引擎，你可以为方案中的每个表使用不同的存储引擎，这点很重要。

http://blog.csdn.net/zuiaituantuan/article/details/5900148

三.mysql外键(FOREIGN KEY)的简单使用
1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

4、外键的使用条件
① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

二、使用方法
1、创建外键的语法：
外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）
② CASCADE（跟随外键改动）
③ SET NULL（设空值）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的）

2、示例
1）创建表1
create table repo_table(
repo_id char(13) not null primary key,
repo_name char(14) not null)
type=innodb;
创建表2
mysql> create table busi_table(
-> busi_id char(13) not null primary key,
-> busi_name char(13) not null,
-> repo_id char(13) not null,
-> foreign key(repo_id) references repo_table(repo_id))
-> type=innodb;
2）插入数据
insert into repo_table values(“12”,“sz”); //success
insert into repo_table values(“13”,“cd”); //success
insert into busi_table values(“1003”,“cd”, “13”); //success
insert into busi_table values(“1002”,“sz”, “12”); //success
insert into busi_table values(“1001”,“gx”, “11”); //failed,提示：[11的值不存在表repo_table的字段busi_id中，所以会报错]
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (smb_man.busi_table, CONSTRAINT busi_table_ibfk_1 FOREIGN KEY (repo_id) REFERENCES repo_table (repo_id))

3）增加级联操作
mysql> alter table busi_table
-> add constraint id_check
-> foreign key(repo_id)
-> references repo_table(repo_id)
-> on delete cascade
-> on update cascade;

ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb;

3、相关操作
外键约束（表2）对父表（表1）的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

关键字

含义

CASCADE

删除包含与已删除键值有参照关系的所有记录

SET NULL

修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)

RESTRICT

拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)

NO ACTION

啥也不做

4、其他
在外键上建立索引:
index repo_id (repo_id),
foreign key(repo_id) references repo_table(repo_id))

http://www.cnblogs.com/mydomain/archive/2011/11/10/2244233.html

constraint的一些用法总结: http://blog.sina.com.cn/s/blog_5eec6f3a0100hxwn.html

四.关于MYSQL Innodb 锁行还是锁表

关于mysql的锁行还是锁表，这个问题，今天算是有了一点头绪，mysql 中 innodb是锁行的，但是项目中居然出现了死锁，锁表的情况。为什么呢？先看一下这篇文章。

做项目时由于业务逻辑的需要，必须对数据表的一行或多行加入行锁，举个最简单的例子，图书借阅系统。假设 id=1 的这本书库存为 1 ，但是有 2 个人同时来借这本书，此处的逻辑为

Select restnum from book where id =1 ;
– 如果 restnum 大于 0 ，执行 update
Update book set restnum=restnum-1 where id=1 ;
Select restnum from book where id =1 ;
– 如果 restnum 大于 0 ，执行 update
Update book set restnum=restnum-1 where id=1;

问题就来了，当 2 个人同时来借的时候，有可能第一个人执行 select 语句的时候，第二个人插了进来，在第一个人没来得及更新 book 表的时候，第二个人查到数据了，其实是脏数据，因为第一个人会把 restnum 值减 1 ，因此第二个人本来应该是查到 id=1 的书 restnum 为 0 了，因此不会执行 update ，而会告诉它 id=1 的书没有库存了，可是数据库哪懂这些，数据库只负责执行一条条 SQL 语句，它才不管中间有没有其他 sql 语句插进来，它也不知道要把一个 session 的 sql 语句执行完再执行另一个 session 的。因此会导致并发的时候 restnum 最后的结果为 -1 ，显然这是不合理的，所以，才出现锁的概念， Mysql 使用 innodb 引擎可以通过索引对数据行加锁。以上借书的语句变为：

Begin;
Select restnum from book where id =1 for update ;
– 给 id=1 的行加上排它锁且 id 有索引
Update book set restnum=restnum-1 where id=1 ;
Commit;

这样，第二个人执行到 select 语句的时候就会处于等待状态直到第一个人执行 commit 。从而保证了第二个人不会读到第一个人修改前的数据。

那这样是不是万无一失了呢，答案是否定的。看下面的例子。

跟我一步一步来，先建立表

CREATE TABLE book (
id int(11) NOT NULL auto_increment,
num int(11) default NULL,
name varchar(0) default NULL,
PRIMARY KEY (id),
KEY asd (num)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

其中 num 字段加了索引

然后插入数据，运行，

insert into book(num) values(11),(11),(11),(11),(11);
insert into book(num) values(22),(22),(22),(22),(22);

然后打开 2 个 mysql 控制台窗口，其实就是建立 2 个 session 做并发操作

在第一个 session 里运行：

begin;
select * from book where num=11 for update;
出现结果：

±—±----±-----+
| id | num | name |
±—±----±-----+
| 11 | 11 | NULL |
| 12 | 11 | NULL |
| 13 | 11 | NULL |
| 14 | 11 | NULL |
| 15 | 11 | NULL |
±—±----±-----+
5 rows in set

然后在第二个 session 里运行：

begin;
select * from book where num=22 for update;
出现结果：

±—±----±-----+
| id | num | name |
±—±----±-----+
| 16 | 22 | NULL |
| 17 | 22 | NULL |
| 18 | 22 | NULL |
| 19 | 22 | NULL |
| 20 | 22 | NULL |
±—±----±-----+
5 rows in set

好了，到这里什么问题都没有，是吧，可是接下来问题就来了，大家请看：
回到第一个 session ，运行：

update book set name=‘abc’ where num=11;

问题来了， session 竟然处于等待状态，可是 num=11 的行不是被第一个 session 自己锁住的么，为什么不能更新呢？好了，打这里大家也许有自己的答案，先别急，再请看一下操作。

把 2 个 session 都关闭，然后运行：

delete from book where num=11 limit 3;
delete from book where num=22 limit 3;
其实就是把 num=11 和 22 的记录各删去 3 行，
然后重复 “***********************” 之间的操作
竟然发现，运行 update book set name=‘abc’ where num=11; 后，有结果出现了，说明没有被锁住，
这是为什么呢，难道 2 行数据和 5 行数据，对 MySQL 来说，会产生锁行和锁表两种情况吗。经过跟网友讨论和翻阅资料，仔细分析后发现：

在以上实验数据作为测试数据的情况下，由于 num 字段重复率太高，只有 2 个值，分别是 11 和 12. 而数据量相对于这两个值来说却是比较大的，是 10 条， 5 倍的关系。
那么 mysql 在解释 sql 的时候，会忽略索引，因为它的优化器发现：即使使用了索引，还是要做全表扫描，故而放弃了索引，也就没有使用行锁，却使用了表锁。简单的讲，就是 MYSQL 无视了你的索引，它觉得与其行锁，还不如直接表锁，毕竟它觉得表锁所花的代价比行锁来的小。以上问题即便你使用了 force index 强制索引，结果还是一样，永远都是表锁。

所以 mysql 的行锁用起来并不是那么随心所欲的，必须要考虑索引。再看下面的例子。

select id from items where id in (select id from items where id <6) for update;
–id字段加了索引
select id from items where id in (1,2,3,4,5) for update;

大部分会认为结果一样没什么区别，其实差别大了，区别就是第一条 sql 语句会产生表锁，而第二个 sql 语句是行锁，为什么呢？因为第一个 sql 语句用了子查询外围查询故而没使用索引，导致表锁。

好了，回到借书的例子，由于 id 是唯一的，所以没什么问题，但是如果有些表出现了索引有重复值，并且 mysql 会强制使用表锁的情况，那怎么办呢？一般来说只有重新设计表结构和用新的 SQL 语句实现业务逻辑，但是其实上面借书的例子还有一种办法。请看下面代码：

Set sql_mode=
‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Begin;
Select restnum from book where id =1 ; – 取消排它锁 , 设置 restnum 为 unsigned
Update book set restnum=restnum-1 where id=1 ;
If(update 执行成功 ) commit;
Else rollback;

上面是个小技巧，通过把数据库模式临时设置为严格模式，当 restnum 被更新为 -1 的时候，由于 restnum 是 unsigned 类型的，因此 update 会执行失败，无论第二个 session 做了什么数据库操作，都会被回滚，从而确保了数据的正确性，这个目的只是为了防止并发的时候极小概率出现的。2 个 session 的 sql 语句嵌套执行导致数据脏读。当然最好的办法还是修改表结构和 sql 语句，让 MYSQL 通过索引来加行锁, MySQL 测试版本为 5.0.75-log 和 5.1.36-community

所以，可以总结出。Mysql innodb虽是锁行的，但是如果没有索引，或者索引如上，那就要锁表了。

http://www.cnblogs.com/mingxuan/archive/2011/10/11/2207560.html

mysql的行锁与表锁。(select* … FOR UPDATE), http://www.lxway.com/108859524.htm
MySQL中的锁（表锁、行锁）,http://www.lxway.com/989818841.htm