MySQL的存储引擎InnoDB与MyISAM对比

x33g5p2x  于2021-10-13 转载在 Mysql  
字(1.5k)|赞(0)|评价(0)|浏览(371)

1. 区别:

1.1 事务:

InnoDB支持事务,MyISAM不支持事务;对于InnoDB,每一条SQL语句都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语句放在begin和commit之间,组成一个事务;

1.2 外键:

InnoDB支持外键(两个表之间,外键一是一棵索引B+树,键和索引表示同一个意思),MyISAM不支持外键;对一个包含外键的InnoDB表转为MyISAM会失败;

1.3 索引与数据文件:

InnoDB和MyISAM的索引都是使用B+树结构,但是:InnoDB使用的是 “聚集索引(clustered index) + 辅助索引(secondary index)”,其中聚集索引的叶子结点存储的是数据本身,数据文件直接存储在主键索引这棵B+树上,辅助索引的叶子节点存储的是主键的值,需要通过回表到主键B+树上才能找到数据,需要两次索引;MyISAM是非聚集索引,数据文件和索引是完全分离开的,所有索引树的叶子结点存储的都是 数据文件的地址指针。

也正是由于上述原因,InnoDB必须要有唯一索引(有主键索引最好,如果没有主键索引就使用第一个NOT NULL的唯一索引做聚簇索引,如果这个也没有,InnoDB就使用一个隐藏的row_id用来做聚簇索引,以存储数据文件),而MyISAM可以没有索引。

1.4 count(/*):

InnoDB不保存表的具体行数,执行 select count(/*) from table 时需要全表扫描;而MyISAM使用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量的值即可,速度很快。

InnoDB之所以不保存 count(/*) 的值是因为: 由于InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的(快照读),因此count统计会计算对于 当前事务 而言可以统计到的行数,而不是将总行数保存起来。

1.5 全文索引:

InnoDB不支持全文索引,MyISAM支持全文索引。(5.7 版本之后的InnoDB也开始支持全文索引了)

1.6 锁粒度:

InnoDB支持 行级锁(默认)、表级锁;MyISAM只支持表级锁。

注意InnoDB的行级锁是实现在索引上的,而不是锁在物理行记录上,也就是说如果访问没有命中索引,也无法使用行级锁,将使用表级锁。

1.7 存储文件格式:

InnoDB的存储文件有 frm、idb: frm是表定义文件, ibd 是数据文件;

MyISAM的存储文件有 frm、myd、myi: frm是表定义文件, myd 是数据文件, myi 是索引文件。

2. 如何选择:

2.1 是否需要支持事务:

如需要则必须选择InnoDB,如不需要可以考虑MyISAM;

2.2 并发度:

由于InnoDB采用的是行级锁,所以对数据库 “写” 操作能够支持更高的并发度,因此:当表中绝大多数的操作都是 读 操作时,可以考虑使用MyISAM;如果既有读也有写,请使用InnoDB。(例如 )

2.3 崩溃恢复:

InnoDB在系统崩溃后恢复起来更容易(采用redo log + binlog “两阶段提交”的方式,redo log是循环写,当宕机后,redo log中存储的就是还没有来得及刷盘的数据),而MyISAM在系统崩溃后回复起来更困难(只有binlog,binlog是追加写,当宕机恢复后,不知道哪些数据写了磁盘,哪些数据还没写磁盘);

redo log是InnoDB独有的,binlog是MySQL server层的,所有的存储引擎都可以使用;

2.4 MySQL默认的存储引擎:

MySQL 5.5 版本之后,InnoDB取代了MyISAM成为了系统的默认存储引擎,说明其优势有目共睹;如果你不知道用什么,那就用InnoDB,至少不会差。

相关文章