MySQL夯实之路-存储引擎深入浅出

innoDB

Mysql4.1以后的版本将表的数据和索引放在单独的文件中

采用mvcc来支持高并发，实现了四个标准的隔离级别，默认为可重复读，并且通过间隙锁（next-key locking）策略防止幻读（查询的行中的间隙也会锁定）

基于聚簇索引建立，主键要尽可能小（因为二级索引中包含主键列，主键很大，其他索引也会很大）

内部做了很多优化，磁盘读取数据时的可预测预读，自动在内存中创建hash索引加速操作的自适应哈希索引（adaptive hash index），加快插入操作的插入缓冲区

Myisam

mysql5.1版本前默认为myisam存储引擎

myisam不支持事务和行级锁，崩溃后无法安全修复

将表存在两个文件中：数据文件，索引文件，.mdy和.myi

特性：

查询速度快：相比于innoDB，innoDB需要缓存索引和数据，myisam只缓存索引。innoDB要维护mvcc。innoDB寻址先到块，再到行，myisam直接定位到offset

只支持表锁，对整张表加锁，读取时加共享锁，写入时加排他锁。表读取是也可以往表中插入新记录（并发插入），插入速度快。

支持全文索引。

修复慢，安全性不如innoDB。

延迟更新索引键

Myisam压缩表

创建导入数据后不会再修改数据适合使用Myisam压缩表，压缩表不能进行修改，支持索引但也是只读的，表中记录独立压缩的，读取但行时不需要解压整个表（页）。

Myisam性能问题最明显的是表锁问题

其他引擎

Blackhole引擎，不存储数据，会丢弃所有插入的数据。用于复制的场景，如主从复制。

CSV引擎，可以将CSV文件作为MySQL表来处理，可以作为一种数据交换机制。

Memory引擎，数据存在内存中，访问速度快，重启后数据会丢失。

Merge引擎，是MyISAM的变种，由多个MyISAM表合并而来的虚拟表。

选择合适的引擎

Mysql5.5版本时将innoDB作为默认的存储引擎，优先选择innoDB。尽量不要混合使用存储引擎

优先考虑的因素

事务

如果应用需要事务支持，那么InnoDB(或者XtraDB）是目前最稳定并且经过验证的选择。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型的应用比较符合这一特性。

备份

如果需要在线热备份，那么选择InnoDB就是基本的要求。

崩溃恢复
 

MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。

特性

1.innoDB支持行级锁，myisam只支持表级锁。

2.innoDB还支持外键，myisam不支持。

3.innoDB会缓存数据和索引，myisam值缓存索引。

4.myisam查询性能好，innoDB插入，更新性能好，删除从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。myisam适合查多写少，innoDB适合写多读少。

5.innoDB不支持全文索引（5.6开始支持），myisam支持。

日志应用：myisam，archive，开销低，插入速度快

只读或者大部分只读：myisam（如果不介意崩溃，崩溃不是小问题）

订单处理：innoDB，支持事务

电子公告牌和主题讨论区：大部分数据库操作效率不高

CD-ROM应用：myisam表或者myisam压缩表

大数据量：   3-5TB，或者更大，innoDB

10TB：需要建立数据仓库，infobright或tokuDB

转换表的引擎

执行时间长，会加读锁