在MySQL 5.1 以及之前的版本中,MySQL的默认存储引擎还是MyISAM,由于MyISAM不支持事务、行级锁,在崩溃无法安全修复,特别是对于大业务数据库的崩溃,无疑是致命的。但是MyISAM也有自己的优势,在作用于只读表并且数据量大时,效果是显而易见的,例如用作与后台的操作日志等。下面我们来对比一下MyISAM和InnoDB具体由哪些差异吧。
差异:
一. 创建表时生成的文件不同
MyISAM: .frm 数据表结构、. myi 索引文件、. myd 数据文件。
InnoDB:.frm 数据表结构、.idb 索引 + 数据文件
二. 事务
MyISAM:不支持
InnoDB:支持
InnoDB默认情况下autocommit的值为ON开启状态,可以用命令show VARIABLES like 'autocommit'查看,即当执行DML(insert、update、delete)操作时,会默认添加start transaction语句,当执行完成后默认COMMIT提交完成事物,当有意外错误时,会执行ROLLBACK回滚。
当有多条连续的DML语句时,可以手动添加start transaction,根据业务完成后提交COMMIT或ROLLBACK操作,以提高DML语句的速度。
三. 外键
MyISAM:不支持
InnoDB:支持
父表与子表必须具有相同的存储引擎,相同的字段类型,其中整数、是否unsigned必须相同,字符的长度可以不同。
外键与关联字段必须创建索引,没有则自动创建。
ps:关于外键约束,会在后面的一篇文章中专门说到。
四. 锁机制
MyISAM:表锁
InnoDB:行锁、表锁
当在查询MyISAM表时,会对整张表上一个读锁,不会影响别的session会话来读这张表,但是在需要执行DML语句时,会阻塞等待释放读锁。
当在对MyISAM执行DML语句时,会给表上一个写锁,其他所有等待读取和DML操作语句需要等待释放,剩下等待释放写锁的sql语句并不一定会按照顺序执行,一个写锁请求可能会被插入到读锁队列前面,但读锁并不能插入到写锁前。
InnoDB默认使用的是行锁,但是InnoDB的行锁是基于索引实现的,在没有索引的情况下,默认还是使用的表锁。
InnoDB在执行DML语句时,会给影响的行上排他锁,查询语句并不会上锁。
在使用ALTER TABLE语句时,MySQL会忽略存储引擎的锁机制,使用表锁。
五. 热备份
MyISAM: 不支持
InnoDB:支持
Oracle公司提供了MySQL Enterprise Backup和Percona开源的XtraBackup可以使InnoDB做到热备份。
六. MVCC
MyISAM: 不支持
InnoDB:支持
MVCC即多版本并发控制,MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。
后续会专门写一篇文章讲述MVCC原理。
七. 全文索引
MyISAM: 支持
InnoDB:不支持
InnoDB不支持FULLTEXT类型的全文索引,如果要使用,可以使用sphinx提供了插件。
八. 主键
MyISAM: 不设置主键则没有
InnoDB:不设置主键会默认设置一个隐藏的主键字段
InnoDB引擎如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)