在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字节的主键(用户不可见)
网友评论