MySQL 常用两种存储引擎选择 (MyISAM 和 InnoDB)
2019-02-27| 程成| 37| 0| MySQL

InnoDB和MyISAM

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。


基本的差别为: MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。


所以从宏观来讲,事务数据库关注细节,而数据仓库关注高层次的聚集,所以,InnoDB更适合作为线上的事务处理,而MyISAM更适合作为ROLAP型数据仓库。



InnoDB引擎适合线上事物型数据库:

1.InnoDB引擎表是基于B+树的索引组织表(IOT);


2.每个表都需要有一个聚集索引(clustered index);


3.所有的行记录都存储在B+树的叶子节点(leaf pages of the tree);


4.基于聚集索引的增、删、改、查的效率相对是最高的;


5.如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择器作为聚集索引;


6.如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引;


7.如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。



MYISAM引擎适用于ROLAP数据仓库:

1.读取效率:数据仓库的高并发上承载的大部分是读, MYISAM强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快。


2. 存储空间:MyISAM: MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。


3. MyISAM可移植性备份及恢复:MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。移植过程中MyISAM不受字典数据的影响。


4.从接触的应用逻辑来说,select count(*) 和order by 是最频繁的,大概能占了整个sql总语句的60%以上的操作,而这种操作Innodb其实也是会锁表的,很多人以为Innodb是行级锁,那个只是where对它主键是有效,非主键的都会锁全表的。但MYISAM对于count操作只需要在元数据中读取,不用扫表。


5.如果和MyISAM比insert写操作的话,Innodb还达不到MyISAM的写性能,如果是针对基于索引的update操作,虽然MyISAM可能会逊色Innodb,但是那么高并发的写,从库能否追的上也是一个问题,且不建议数据仓库中频繁update数据。


6.如果是用MyISAM的话,merge引擎可以大大加快数据仓库开发速度,非常适合大项目总量约几亿的rows某一类型(如日志,调查统计)的业务表。


7.全文索引:MyISAM:支持 FULLTEXT类型的全文索引。InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。


8.表主键:MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。


9.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。


10. MyISAM不支持外键,需通过其他方式弥补。



根据引擎特性的优化

如何对InnoDB引擎的表做最优的优化:


1.使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致,这时候存取效率是最高的


2.该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致



原文地址:http://www.imooc.com/article/257064



下一篇:没有下一篇了
×
作者:程成
QQ:492245711