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
延伸阅读:
https://www.baidu.com/s?wd=MySQL%20存储引擎%20区别
http://www.sogou.com/web?query=MySQL%20存储引擎%20区别
https://www.so.com/s?q=MySQL%20存储引擎%20区别