InnoDB
InnoDB是MySQL默认的事务型引擎,也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务,短期事务大部分是正常提交的,很少回滚。InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中,也很流行。除了非常特别的原因需要使用其他引擎,InnoDB也是非常好值得花时间研究的对象。
InnoDB的数据存储在表空间中,表空间是由InnoDB管理的黑盒文件系统,由一系列系统文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间存储介质。
InnoDB通过间隙锁(next-key locking)防止幻读的出现。InnoDB是基于聚簇索引建立,与其他存储引擎有很大的区别,聚簇索引对主键查询有很高的性能,不过它的二级索引(secondary index,非主键索引)必须包含主键列。所以如果主键列很大的话,索引会很大。
MyISAM
在5.1之前,MyISAM是默认的引擎,MyISAM有大量的特心态,包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁,而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务,但是很多人的概念中依然是不支持事务的引擎。
MyISAM并不是无所事处。对于一些只读数据,或者表空间较小,可以忍受恢复操作,可以使用MyISAM。MyISAM会将表存储在两个文件中:数据文件、索引文件。分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表定义选择那种行格式。MyISAM表的行记录数,取决于磁盘空间和操作系统中的单个文件最大尺寸。
在MySQL中,默认配置只能存储256TB的数据。因为指向数据记录的指针长度是6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项。两个相乘是最大的大小。会导致重建索引。
MyISAM是对整个表加锁,而不是行锁,读取的时候对表加共享锁,写入的时候加排他锁。但是在表有读取查询的同时,也可以往表内写入记录。
对于MyISAM,即使是Blob,Text等等长字段,也可以基于前500字符创建索引,MyISAM支持全文索引,这是一个基于分词创建的索引,也可以支持复杂的查询。
MyISAM可以选择延迟更新索引键,在创建表的时候指定delay_key_write选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘,而是写到缓存区,只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能,但是在主机崩溃时会造成索引损坏,需要执行修复操作。
MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改,那么这个表适合MyISAM压缩表。可以使用myisampack对MyISAM表进行打包,压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用,因此可以减少磁盘IO,提升性能,压缩表也支持索引,但是索引也是只读的。
整体来说MyISAM并没有那么不堪,但是由于没有行锁机制,所以在海量写入的时候,会导致所有查询处于Locked状态。