上个月在面试时被问到数据库存储引擎的选择问题,不是很清楚。恰巧今日看书时看到了这一块,正好来总结下。如有不足,还望大神斧正。
(1)什么是存储引擎
Mysql中的数据都是用各种不同的技术存储在文件(或内存中的)。这些技术中的每一个技术会使用不同的存储机制、索引技巧等。我们应该根据应用的不同需求,来选择合适的存储引擎,从而改善应用的整体功能。
例如,在研究大量的临时数据,你也许需要使用内存存储引擎,内存存储引擎能够在内存中存储所有的表格数据。
或者,在并发应用中,你需要一个支持事务处理的数据库,以保证事务处理不成功时数据的回退能力。
这些不用的技术及配套的相关功能在Mysql中被称作存储引擎(也叫作表类型)。在mysql中,使用如下命令即可得到一个可用引擎的列表:
<span style="white-space:pre"> </span>show engines;
显示结果为:
(2)指定存储引擎的方法
可以使用很多方法指定一个要使用的存储引擎。最简单的方法是,如果需要一种能满足大多数数据库需求的存储引擎,可以在MySQL设置文件中设置一个默认的引擎类型(使用storage_engine选项),或者在启动数据库服务器时在命令行后面加上--default-storage-engine 或--default-table-type。
最直接的方式就是在创建表时指定存储引擎的类型,在create table 语句最后使用engine属性指定。
例如
CREATE TABLE mytable(id int,title char(20))ENGINE=INNODB
也可以使用alter table命令来更改现有表的存储引擎,例如:
ALTER TABLE mytable ENGINE=MYISAM
如果不指定的话,MySQL默认的存储引擎是MYISAM。
(3)常用存储引擎的优缺点
①ISAM
ISAM在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM的执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的不足在于,它不支持事务处理,也不能容错,如果你的硬盘崩溃了,那么数据文件就无法恢复了。
②MyISAM
MyISAM是MySQL的ISAM扩展格式和默认的数据库引擎。相比于ISAM,MyISAM拥有更好的索引压缩,更好的键码统计分布。 除了提供了ISAM里所没有的索引和字段管理的大量功能,MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作。MyISAM强调了快速读写操作。
③MEMORY
MEMORY只允许驻留在内存里的临时表格中。驻留在内存里的MEMORY要比ISAM和MyISAM都快,但是它所管理的数据都是不稳定的,而且如果在关机之前没有保存,那么所有的数据都会丢失。
④InnoDB和BDB
InnoDB和BDB数据库都支持对事物处理和外来见的支持,但这两者的都比ISAM和MyISAM引擎慢很多。
(4)如何选择引擎
ISAM:执行读取操作的速度很快,而且不占用大量的内存和存储资源,但不支持事物处理,也不能容错
MyISAM:支持索引,能够实现并发的快速读取操作。
MEMORY: 只有内存存储,操作最快,但管理的数据是不稳定的.
InnoDB:支持事务和外来键,速度相对较慢。
(5)MyISAM和InnoDB对比
MyISAM类型不支持事物处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提高事务支持以及外部键等高级数据库功能。
另外,两者都可用来存储表和索引,InnoDB的索引和表存储在同一文件中,MyISAM的索引和表存储在不同文件中。InnoDB占用磁盘空间比MyISAM大,对空闲存储空间的使用不优。因此,MyISAM可大量节省磁盘空间,特别是对索引的存储上,优势巨大。