MySQL存储引擎 - MyISAM与InnoDB的优劣

一、MyISAM 与 InnoDB 的异同

特性对比表格

特点	MyISAM	InnoDB
存储限制	有	64TB
事务安全		支持
锁机制	表锁	行锁
B树索引	支持	支持
哈希索引
全文索引	支持
集群索引		支持
数据缓存		支持
索引缓存	支持	支持
数据可压缩	支持
空间使用	低	高
内存使用	低	高
批量插入速度	高	低
支持外键		支持

二、MyISAM的特性

1. 优势

访问速度快，适用于不需要做事务、以读数据和插数据为主的业务

2.存储方式

• 表结构：.frm
• 数据：.MYD
• 索引：.MYI

默认存储在同一路径下，可在建表时通过DATA DIRECTORY和INDEX DIRECTORY设置数据和索引的存储位置（需要绝对路径)

3.存储格式

• 静态（固定长度表）表
  MyISAM默认存储格式，所有字段为非变长字段，所有记录长度固定
  优点:存储快、易缓存、易恢复
  缺点:占用空间大

  (注：静态表中数据后边的空格会被去掉)

• 动态表
  表中包含变长字段
  优点：占用空间少
  缺点：故障不易恢复、频繁的更新和删除会产生碎片

  需要定期执行OPTIMIZE TABLE语句或myismchk-r命令来改善性能

• 压缩表
  由myisampack工具创建，占用空间非常小，每个记录单独压缩，只有非常小的访问开支

三、InnoDB的特性

1. 优势

支持事务、外键约束、行级锁

2.自增列

自增列必须是索引，如果是联合索引则必须在第一位

MyISAM的自增列可以位于联合索引的任一位置，根据前边的几列完成自增

3.外键约束

InnoDB是目前唯一支持外键约束的MySQL引擎
在创建外键时，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引

在创建索引时可以指定在删除、更新父表时，对子表进行的操作：

• RESTRICT、NO ACTION：在子表存在记录时，父表不能更新
• CASCADE：在父表更新或删除时，同时更新或删除子表对应记录
• SET NULL：父表更新或删除时，子表对应记录被置为NULL

4.存储方式

• 共享表空间存储
  表结构：.frm
  索引+数据：.innodb_data_home_dir和innodb_data_file_path定义的表空间

  为了均衡IO，可以是多个文件（为了均衡负载）

• 多表空间存储
  表结构：.frm
  索引+数据：.ibd

  每个表的数据和索引单独保存在.ibd中
  如果是分区表，则每个分区对应单独的.ibd文件，文件名是表名+分区名，可以在创建分区时指定每个分区的数据文件位置，均衡负载

四、总结

1.MyISAM

• 默认的MySQL插件式存储引擎
• 适合不需要事务以及以读和插入为主的业务
• 在web和数据仓库中常用

2.InnoDB

• 支持事务和外键
• 对事务完整性有要求，在并发条件下要求数据一致性，有更多的更新和删除操作的业务（行级锁优势），适合使用InnoDB
• 在计费、财务系统常用

五、相关话题

• MySQL锁问题 - MyISAM与InnoDB的锁机制
• MySQL的InnoDB索引原理详解[转]