myisam和innodb的区别和应用场景

概述

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。下面简单说下两者之间的区别。

01

表锁差异

1、MyISAM:

myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

2、InnoDB ：

Innodb支持事务和行级锁，是innodb的最大特色。

事务的ACID属性：atomicity,consistent,isolation,durable。

并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。

事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)

四种隔离级别的比较

查看mysql的默认事务隔离级别“show global variables like 'tx_isolation'; ”

Innodb的行锁模式有以下几种：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁。

注意：当语句没有使用索引，innodb不能确定操作的行，这个时候就使用的意向锁，也就是表锁.

02

数据库文件差异

1、MyISAM

myisam属于堆表

myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。

• .frm 用于存储表的定义
• .MYD 用于存放数据
• .MYI 用于存放表索引

myisam表还支持三种不同的存储格式：

• 静态表(默认，但是注意数据末尾不能有空格，会被去掉)
• 动态表
• 压缩表

2、InnoDB

innodb属于索引组织表

innodb有两种存储方式，共享表空间存储和多表空间存储

两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。

如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。

如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。

03

索引差异

1、关于自动增长

myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。

innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。

2、关于主键

myisam允许没有任何索引和主键的表存在，

myisam的索引都是保存行的地址。

innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)

innodb的数据是主索引的一部分，附加索引保存的是主索引的值。

3、关于count()函数

myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值

innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。

4、全文索引

myisam支持 FULLTEXT类型的全文索引

innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）

5、delete from table

使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)

6、索引保存位置

myisam的索引以表名+.MYI文件分别保存。

innodb的索引和数据一起保存在表空间里。

04

MyISAM和InnoDB两者的应用场景

1) MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

2) InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

但是实际场景中，针对具体问题需要具体分析，一般而言可以遵循以下几个问题：

- 数据库是否有外键？

- 是否需要事务支持？

- 是否需要全文索引？

- 数据库经常使用什么样的查询模式？在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。

- 数据库的数据有多大？ 大尺寸倾向于innodb，因为事务日志，故障恢复。

总结

总结一下，在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也基本稳定，如果是对读取速度要求比较快的应用可以考虑选MyISAM。