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

一、MyISAM 与 InnoDB 的异同

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

 

二、MyISAM的特性

1. 优势：访问速度快，适用于不需要做事务、以读数据和插数据为主的业务
2. 存储方式：
   
   • .frm 存储表定义
   • .MYD MYDATA存储数据
   • .MYI MYINDEX存储索引
   • 默认存储在同一路径下，可在建表时通过DATA DIRECTORY和INDEX DIRECTORY设置数据和索引的存储位置（需要绝对路径)
3. 存储格式：

• • 静态（固定长度表）表：MyISAM默认存储格式，所有字段为非变长字段，所有记录长度固定；优点是存储快、易缓存、易恢复；缺点是占用空间大 (注：数据后边的空格会被去掉)
  • 动态表：表中包含变长字段；优点：占用空间少；缺点：故障不易恢复、频繁的更新和删除会产生碎片，需要定期执行OPTIMIZE TABLE语句或myismchk-r命令来改善性能；
  • 压缩表：由myisampack工具创建，占用空间非常小，每个记录单独压缩，只有非常小的访问开支；

三、InnoDB的特性

1. 优势：支持事务、外键约束、行级锁
2. 自增列：自增列必须是索引，如果是联合索引则必须在第一位；MyISAM则不同，MyISAM的自增列可以位于联合索引的任一位置，根据前边的几列完成自增；
3. 外键约束：InnoDB是目前唯一支持外键约束的MySQL引擎；在创建外键时，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引；在创建索引时可以指定在删除、更新父表时，对子表进行的操作：
   
   • RESTRICT、NO ACTION：在子表存在记录时，父表不能更新；
   • CASCADE：在父表更新或删除时，同时更新或删除子表对应记录；
   • SET NULL：父表更新或删除时，子表对应记录被置为NULL；
4. 存储方式：

• • 共享表空间存储：
    • .frm：表结构
    • .innodb_data_home_dir和innodb_data_file_path定义的表空间：索引、数据，可以是多个文件（为了均衡负载）
  • 多表空间存储（通过参数innodb_file_per_table设置，重启生效，存量表不变）：
    • .frm：表结构
    • .ibd：每个表的数据和索引单独保存在.ibd中；如果是分区表，则每个分区对应单独的.ibd文件s，文件名是表名+分区名，可以在创建分区时指定每个分区的数据文件位置，均衡负载

四、总结

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

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

五、待发散话题

1. MySQL锁的机制
2. 索引的数据结构
3. 数据、索引缓存