关于文章提到的事务、锁、索引、BTree/B+Tree等请看:
MySQL索引详解(结合了其他各大佬的文章)
数据库事务与锁详解
区别
-
InnoDB支持事务,MyISAM不支持事务。对于InnoDB每一条SQL语句都默认封装成了一个事务,自动提交,这样会影响执行速度,所以有些情况下可以把多条SQL语言放在begin和commit之间组成一个事务,当然,事务并不是为了避免这种速度影响而出现的。
-
InnoDB支持外键,而MyISAM不支持外键。因此对一个包含外键的InnoDB表转为MYISAM会失败,所以使用外键的时候也需要考虑这个数据库是否有更换引擎的需求。
-
InnoDB使用的是聚集索引,使用B+Tree作为索引结构,数据和索引存储在一起。MyISAM使用非聚集索引,数据和索引分开存储。
-
MyISAM索引文件中的主键索引和辅助索引在结构上没有区别,只是主键索引要求是主键字段,辅助索引可以是其他字段。InnoDB主键索引的B+Tree中的叶子节点的data域存放的是数据表的数据,而辅助索引的叶子节点存放的是相应记录的主键值。
-
InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。
-
InnoDB支持表锁和行锁(update insert delete 都会默认加上行锁,select不加锁),MyISAM支持表锁。 InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。也就是说如果访问没有命中索引,也无法使用行锁,将要退化为表锁。
//uid是索引,name不是索引
update t_user set age=10 where uid=1; 命中索引,行锁。
update t_user set age=10 where uid != 1; 未命中索引,表锁。
update t_user set age=10 where name=‘shenjian’; 无索引,表锁。 -
InnoDB表必须有主键,因为InnoDB的数据文件本身就是按照主键索引,若没有指定主键,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
-
系统奔溃后,MyISAM恢复起来更困难
-
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM),说明其是有优势的。
MyISAM与InnoDB性能测试
下边两张图是官方提供的MyISAM与InnoDB的压力测试结果:
可以看出,随着CPU核数的增加,InnoDB的吞吐量反而越好,而MyISAM,其吞吐量几乎没有什么变化,显然,MyISAM的表锁定机制降低了读和写的吞吐量。
存储
big _ innodb是一个InnoDB引擎的表,big _ MyISAM是一个MyISAM引擎的表
.frm:结构文件
.ibd:InnoDB数据和索引文件
.MYD:MyISAM数据文件
.MYI:MyISAM索引文件
修改引擎:
alter table tablename ENGINE=MyISAML;
InnoDB为什么推荐使用自增ID作为主键?
自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
InnoDB引擎的4大特性:插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
如何选择
参考 https://blog.youkuaiyun.com/xlgen157387/article/details/68978320
MyISAM适合:
(1)做很多count 的计算;
(2)插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择;
(3)没有事务。
InnoDB适合:
(1)可靠性要求比较高,或者要求事务;
(2)LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
(3)执行大量的INSERT或UPDATE;
(4)DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除;
要注意,创建每个表格的代码是相同的,除了最后的 TYPE参数,这一参数用来指定数据引擎。
总结
InnoDB存储引擎的特点是:行级锁、事务安全(ACID兼容)、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。