MyISAM:
- 不支持事务,但是每次查询都是原子的;
- 支持表级锁,即每次操作是对整个表加锁;
- 存储表的总行数;
- 一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;
- 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性。
- 支持可压缩
应用场景:访问速度快,对事物完整性无要求或以Seclect/Insert(只有很少的更新和删除操作)为主的应用基础上都可以使用MyISAM来创建表,如web、数据仓库、CHAR(适合数据类型)
InnoDb:
- 支持ACID的事务,支持事务的四种隔离级别;提供了具有提交、回滚、和崩溃恢复能力的事务安全。
- 支持行级锁及外键约束:因此可以支持写并发;
- 不存储总行数;
- 一个InnoDb引擎存储在一个文件空间(共享表空间,表大小不受操作系统控制,一个表可能分布在多个文件里),也有可能为多个(设置为独立表空,表大小受操作系统文件大小限制,一般为2G),受操作系统文件大小的限制;
- 主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。
- 不可压缩
应用场景:计费系统、财务系统、适合字段VARCHAR
MYISAM的主索引结构如下:
辅索引如下:
InnoDb的主索引如下:
辅索引如下:
两种索引数据查找过程如下:
定期使用optimize table
定期使用optimize table table_name优化表
如果一个表数据量比较大,且经常有插入或删除操作,会产生大量的空间碎片(主要由于对表进行delete操作时,服务器并不一定会释放磁盘空间, 或者数据块大小的影响)。如果不定期清理这些碎片,数据库的性能会加速下降。
因此应该有个定期任务,来对表进行optimize操作;一方面可以回收大量的物理空间,另一方面可以大幅度恢复MySQL数据库原有
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
可移植性、备份及恢复
MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
这都是由于B+树和B具有这不同的存储结构所造成的区别,以一个m阶树为例。
关键字的数量不同;B+树中分支结点有m个关键字,其叶子结点也有m个,其关键字只是起到了一个索引的作用,但是B树虽然也有m个子结点,但是其只拥有m-1个关键字。
存储的位置不同;B+树中的数据都存储在叶子结点上,也就是其所有叶子结点的数据组合起来就是完整的数据,但是B树的数据存储在每一个结点中,并不仅仅存储在叶子结点上。
分支结点的构造不同;B+树的分支结点仅仅存储着关键字信息和儿子的指针(这里的指针指的是磁盘块的偏移量),也就是说内部结点仅仅包含着索引信息。
查询不同;B树在找到具体的数值以后,则结束,而B+树则需要通过索引找到叶子结点中的数据才结束,也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。