文章目录
1. 概述
MySQL 有多种存储引擎,每种存储引擎有各自的优缺点,可以择优选择使用:
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
MyISAM是MySQL的默认数据库引擎(5.5版之前
),由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良。虽然性能极佳,但却有一个缺点:不支持事务处理
(transaction)。
不过,在这几年的发展下,MySQL也导入了InnoDB(另一种数据库引擎),以强化参考完整性与并发违规处理机制,后来就逐渐取代MyISAM。
InnoDB是MySQL的数据库引擎之一(最新版本5.7已经设置为默认数据库引擎),为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发,2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能,类似于PostgreSQL。
目前InnoDB采用双轨制授权,一是GPL授权,另一是专有软件授权。
2. MyISAM与InnoDB的区别是什么?
2.1、 存储结构
MyISAM:每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。
-
.frm(文件存储表定义)
-
MYD(MYData,存储数据文件)
-
MYI(MYIndex,存储索引文件)
MyISAM例子:
InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件,这个具体要看独立表空间是否默认开启
,如果默认开始(5.7已经默认
),那便是多个文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
InnoDB例子:
2.2、 存储空间
二者的存储区别其实也就是聚簇索引和非聚簇索引的区别,详见:《聚簇索引和非聚簇索引的区别》
MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
MYISAM索引和数据是分开的
,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。
MyISAM缓存在内存的是索引
,不是数据。而InnoDB缓存在内存的是数据,相对来说,服务器内存越大,InnoDB发挥的优势越大。
MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。
能加载更多索引
(试想同样的内存,加载的索引更多了,减少了加载次数),而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB:索引和数据是在一起的,都在ibd文件中
,需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
2.3、 可移植性、备份及恢复
MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
2.4、 事务支持
MyISAM:强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持
。
InnoDB:提供事务支持事务,外键等高级数据库功能
。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
2.5、 联合索引
MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长也必须是组合索引的第一列。
2.6、 表锁差异
MyISAM:只支持表级锁
,用户在操作MyISAM表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。
InnoDB:支持事务和行级锁
,是InnoDB的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在Where的主键是有效的,非主键的WHERE都会锁全表的。
2.7、 全文索引
MyISAM:支持 FULLTEXT类型的全文索引
InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。
2.8、 表主键
MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。
InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键
(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
2.9、 表的具体行数
MyISAM:保存有表的总行数,如果select count(*) from table;
会直接取出出该值。
InnoDB:没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了where条件后,MyISAM和InnoDB处理的方式都一样。
2.10、 CURD操作
MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。
InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。DELETE 从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,在innodb上如果要清空保存有大量数据的表,最好使用truncate table这个命令。
2.11、 外键
MyISAM:不支持
InnoDB:支持
3.总结
通过上述的分析,基本上可以考虑使用InnoDB来替代MyISAM引擎了,原因是InnoDB自身很多良好的特点,比如事务支持、存储过程、视图、行级锁定等等,在并发很多的情况下,相信InnoDB的表现肯定要比MyISAM强很多。另外,任何一种表都不是万能的,只用恰当的针对业务类型来选择合适的表类型,才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用,非关键应用,还是可以继续考虑MyISAM的,这个具体情况可以自己斟酌。
一些关于MyISAM与InnoDB选择使用:
MYISAM和INNODB是Mysql数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。INNODB会支持一些关系数据库的高级功能,如事务功能和行级锁,MYISAM不支持。MYISAM的性能更优,占用的存储空间少。所以,选择何种存储引擎,视具体应用而定。
如果你的应用程序一定要使用事务,毫无疑问你要选择INNODB引擎
。但要注意,INNODB的行级锁是有条件的。在where条件没有使用主键时,照样会锁全表。比如DELETE FROM mytable这样的删除语句。
如果你的应用程序对查询性能要求较高,就要使用MYISAM
了。MYISAM索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能,这可以极大地优化LIKE查询的效率
。
有人说MYISAM只能用于小型应用,其实这只是一种偏见。如果数据量比较大,这是需要通过升级架构来解决,比如分表分库,而不是单纯地依赖存储引擎。
其他一些说法:
现在一般都是选用innodb了,主要是myisam的全表锁,读写串行问题,并发效率锁表,效率低,myisam对于读写密集型应用一般是不会去选用的。