MySQL存储引擎之MyISAM、InnoDB详细对比

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL存储引擎之MyISAM、InnoDB详细对比

科技小能手 2017-11-12 20:40:00 浏览1003
展开阅读全文

InnoDB概览

是MySQL5.5及以后的默认存储引擎,是MySQL的默认事务性引擎,也是最重要最广泛的存储引擎,它被设计用来处理大量的短期(short-lived)事物,短期事物大部分是正常提交的,很少回滚。InnoDB的性能和自动恢复特性,使得它在非事物型存储需求中也很流行。除非特别原因,否则应该优先考虑InnoDB引擎。InnoDB实现了四个标准的事物隔离级别,默认级别是REPEATABLE READ,采用MVCC支持高并发,并且通过间隙锁策略防止幻读的出现。InnoDB是基于聚簇索引建立的。


MyISAM概览

是MySQL5.1及之前版本的默认存储引擎。MyISAM提供了大量的特性,包含全文索引、压缩、空间函数等,但是MyISAM不支持事物和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据,或者表比较小、可以忍受修复(repair)操作,则依然可以继续使用MyISAM。MyISAM最大的性能问题是表锁问题。

其中MyISAM还有如下一些特性:

1、加锁与并发。MyISAM读加共享锁,写加排他锁,支持并发插入(读取查询的同时往表中插入新的记录);

2、修复。MyISAM可以手工或者自动检查和修复操作,这里说的修复和事物恢复和崩溃修复是不同的概念。

3、索引特性。即使是BLOB和TEXT等长字段,也可以基于前500个字符创建索引。MyISAM还支持全文索引;

4、延迟更新索引。创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立即将修改索引数据写入磁盘,而是会写到内存缓冲区中。这种方式极大的提升了写入性能,但是数据库崩溃会造成索引损坏,需要执行修复操作。


InnoDBMyISAM主要区别

1、InnoDB支持事物、行级锁、主键聚簇索引、存储格式平台独立、支持热备、外键约束;

2、MyISAM支持压缩表、全文索引、空间函数、保存表的具体行数;



总结

对于如何选择存储引擎,可以简单的归纳为一句话:“除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先使用InnoDB引擎”。例如,如果要用到全文索引,建议先考虑InnoDB加上Sphinx的组合,而不是使用MyISAM。当然,如果不需要用到InnoDB的特性,同时其他引擎的特性能够很好的满足,也可以考虑一下其他存储引擎。除非万不得已,否则不要混合使用多种存储引擎,否则可能带来一系列复杂的问题。例如,大部分情况下InnoDB都是正确的选择。如果需要不同的存储引擎,请先考虑一下几个因素:

1、事物。如果需要事物那么InnoDB是目前最好的选择。如果不需要事物,并且主要是SELECT和INSERT操作,那么MyISAM是不错的选择,一般日志型的应用比较符合这一特性。

2、备份。如果可以定期的关闭服务器来执行备份,那么备份的因素可以忽略,反之如果需要热备份,InnoDB就是基本的要求。

3、崩溃恢复。数据比较大的时候,系统崩溃后如何快速的恢复是一个需要考虑的问题。相对而言,MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢。因此即使不需要事物支持,很多人也选择InnoDB。

4、特有的特性。



InnoDBMyISAM的注意事项

1、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
2、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
3、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

另外,InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”



转换表的引擎的命令介绍

最简单的方法: alter table mytable engine=InnoDB;

比较优的方法: 

create table innodb_table like myisam_table;

alter table innodb_table engine=InnoDB;

insert into innodb_table select * from myisam_table;

数据量大的时候可以考虑分批处理

start transaction;

insert into innodb_table select * from myisam_table where id between x and y;

commit;

这样操作完成以后,新表是原表的全量复制,如果有必要,可以再执行的过程中对原表加锁,以确保新表和原表的数据一致。


本文转自 古道卿 51CTO博客,原文链接:http://blog.51cto.com/gudaoqing/1286597

网友评论

登录后评论
0/500
评论