MySql数据库存储引擎MyISAM与InnoDB

MySql可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。而每一种存储引擎也使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。常见的存储引擎有MyISAM,InnoDB,Memory,Archive等。

  

本文主要介绍的是:MyISAM与InnoDB。在5.5版本之前,MyISAM是MySQL的默认数据库引擎。虽然性能极佳,但却有一个缺点:不支持事务处理。在之后,MySql的默认数据库引擎就是InnoDB了。与MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能,以及外部键等高级数据库功能。下面就主要聊聊这两者的具体区别。
MyISAM与InnoDB的区别
存储结构

MyISAM:每个MyISAM在磁盘上存储成三个文件。

1
2
3
table_name.frm      存储表结构(定义)。
table_name.MYD 存储数据
table_name.MYI 存储索引文件

InnoDB:把数据存储在表空间中,分为两种方式:
  1)、所有InonoDB表的数据和索引放置于同一个表空间中;
  2)、每个表单独使用一个表空间存储表的数据和索引。
当所有表位于同一表空间中,其数据文件存于数据目录下以ibddata[#]来命名。这种方式不利于管理和备份恢复。所以一般使用第二种方式,每张表创建单独的表空间,用来实现InnoDB所支持的高级特性,比如单表导入导出,单标备份等功能。数据文件为:

1
2
table_name.ibd     存储数据和索引
table_name.frm 存储表结构(定义)

事务支持

MyISAM:强调性能,其执行速度比InnoDB类型更快但不提供事务的支持,也不支持外键,适用于大量select操作的应用场景。

InnoDB:支持事务,并把事务分为四个隔离级别,

1
2
3
4
READ-UNCOMMITTED:   读未提交容易发生脏读;
READ-COMMITTED: 读提交,该级别不可重复读;
REPEATABLE-READ: 可重复读,该级别容易发生幻读;
SERIALIZABLE: 串行化;

默认级别为REPEATABLE-READ。适合对事务要求较高的场景中,较适用于处理大量短期事务。
同时,InnoDB也支持外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
锁粒度

MyISAM:只支持表锁。,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。
InnoDB:支持行锁,极大提升并发能力,并使用间隙锁来防止幻读。当然InnoDB的行级锁也并非绝对,当SQL语句在执行过程中在不能确定扫描范围时也会对整张表进行锁定(只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的)。
索引类型

MyISAM:非聚集索引并且支持全文索引(fulltext index)。允许没有任何索引和主键的表存在,索引都是保存行的地址。
InnoDB:聚集索引,辅助索引,可以使用sphinx插件支持全文索引。如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值的数据列。
AUTO_INCREMENT

MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。
InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。
存储空间

MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认)、动态表、压缩表。
InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
备份及恢复策略

MyISAM:只支持温备与冷备。崩溃后无法安全恢复。
InnoDB:支持热备、温备、冷备。崩溃后可安全恢复。
其他

  1)、MyISAM保存有表的总行数,如果select count() from table;会直接取出出该值。InnoDB没有保存表的总行数,如果使用select count() from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。
  2)、MyISAM不支持外键;而InnoDB支持外键。
  3)、MyISAM不支持hash索引;InnoDB支持自适应的hash索引,使用MVCC来支持高并发。
查看与修改数据库引擎
查看数据库引擎

show create table table_name;

修改表的存储引擎

1、修改mysql的配置文件

default-storage-engine = engine_name

2、通过创建表的命令实现

1
2
3
create table table_name(
......
)engine = engine_name;

3、表单创建完成后,通过修改数据表命令实现

alter table table_name engine = engine_name;

1
alter table table_name engine = engine_name;

MyISAM和InnoDB的选择

  数据库引擎的选择需要我们根据实际情况来选择。默认的InnoDB已经足够使用。
  (1)、当项目中需要事务处理或是外键,那么应该使用InnoDB 。如果你需要全文索引,那么通常来说 MyISAM是好的选择,因为这是系统内建的,但是,我们其实并不会经常地去海量记录。所以,就算是慢一点,我们可以通过使用Sphinx从InnoDB中获得全文索引。
  (2)、数据的大小,同样是一个影响你选择什么样存储引擎的重要因素,大尺寸的数据集趋向于选择InnoDB方式,因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事,InnoDB只需要几分钟。
  (3)、当并发量不大的时候,大批的insert语句或select语句在MyISAM下会快一些,因为MyISAM提供高速存储和检索,以及全文搜索能力。但是当并发量大的时候,在用InnoDB就比MyISAM效率高多了。因为MyISAM只支持表锁,而InnoDB支持效率更高的行锁。

-------------本文结束感谢您的阅读-------------
0%