MySql – 浅谈MyISAM与InnoDB的区别及选择

怼烎@ 2023-03-02 10:27 48阅读 0赞

**目录**

1.MyISAM

1.1 MyISAM数据存储形式

1.2 锁的粒度

1.3 事务

2.InnoDB

2.1 InnoDB数据存储形式

2.2 锁的粒度

2.3 事务

2.4 数据的存储特点

3.MyISAM与InnoDB的区别

3.1 MyISAM优点

3.2 MyISAM缺点

3.3 InnoDB优点

3.4 InnoDB缺点

3.5 MyISAM与InnoDB的区别

3.6 表格对比

4.MyISAM与InnoDB适用场景

--------------------

## **1.MyISAM** ##

### **1.1 MyISAM数据存储形式** ###

MyISAM是MySQL关系数据库5.5版本之前的默认储存引擎（5.5之后改为InnoDB）。MyISAM采用的是索引与数据分离的形式（非聚簇索引），将数据保存在尾缀为.frm，.MYD，.MYI的三个文件中。

![20200728224230733.png][]

![20200728224625365.png][]

在查询数据用到索引时，先到.MYI（索引树）文件中进行查找，取到数据所在.MYD（数据文件）的行位置，最终拿到数据。因为MyISAM存储引擎的索引文件和数据文件是独立分开的，所以称之为非聚簇索引。

### **1.2 锁的粒度** ###

MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。

### **1.3 事务** ###

MyISAM不支持事务。

## **2.InnoDB** ##

InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。

### **2.1 InnoDB数据存储形式** ###

使用InnoDB时，会将数据表分为.frm 和 .idb两个文件进行存储。

![20200728232448720.png][]

### **2.2 锁的粒度** ###

InnoDB支持行锁，采用MVCC(多版本并发控制)来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ。

### **2.3 事务** ###

InnoDB是典型的事务型存储引擎

### **2.4 数据的存储特点** ###

InnoDB表是基于聚簇索引建立的，它的数据文件和索引文件是同一个文件。聚簇索引指向主键对数据的引用，非主键索引则指向对主键的引用。聚簇索引对主键的查询有很高的性能，即在查询过程中，找到了索引，便找到了数据文件，不过他的二级索引（非主键索引）必须包含主键列，索引其他的索引会很大。InnoDB中，没有主键索引，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的id来当作主键索引。

## **3.MyISAM与InnoDB的区别** ##

### **3.1 MyISAM优点** ###

1.  高性能的读取速度
2.  保存了表的行数，当使用count统计的时候不会扫描全表

### **3.2 MyISAM缺点** ###

1.  缺点是为表级别锁，粒度大，发生锁冲动概率较高，容纳并发能力低。
2.  不支持事务。

### **3.3 InnoDB优点** ###

1.  支持事务，支持ACID特性
2.  实现了SQL标准的四种隔离级别
3.  支持行级锁和外键约束
4.  可以利用事务日志进行数据恢复

### **3.4 InnoDB缺点** ###

1.  因为没有保存表的行数，当使用COUNT统计时会扫描全表。

### **3.5 MyISAM与InnoDB的区别** ###

1.  InnoDB支持事务，MyISAM不支持事务。InnoDB对于每一条SQL都默认封装成事务，自动提交，这也是为什么比MyISAM查询慢的主要原因。
2.  InnoDB支持外键，MyISAM不支持外键。对于一个包含外键存储引擎为InnoDB的表转换MyISAM会失败。
3.  InnoDB为聚簇索引，MyISAM为非聚簇索引。
4.  InnoDB不保存表的具体行数，count（\*）需要扫描全表，MyISAM使用变量保存了整个表的行数，执行count（\*）只需要读取该变量即可。
5.  InnoDB不支持全文索引，MyISAM支持全文索引。

### **3.6 表格对比** ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;"><strong>对比项</strong></td> 
   <td style="text-align:center;vertical-align:middle;"><strong>MyISAM</strong></td> 
   <td style="text-align:center;vertical-align:middle;"><strong>InnoDB</strong></td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">主外键</td> 
   <td style="text-align:center;vertical-align:middle;">不支持</td> 
   <td style="text-align:center;vertical-align:middle;">支持</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">事务</td> 
   <td style="text-align:center;vertical-align:middle;">不支持</td> 
   <td style="text-align:center;vertical-align:middle;">支持</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">行表锁</td> 
   <td style="text-align:center;vertical-align:middle;">表锁</td> 
   <td style="text-align:center;vertical-align:middle;">行锁</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">缓存</td> 
   <td style="text-align:center;vertical-align:middle;">只缓存索引，不缓存真实数据</td> 
   <td style="text-align:center;vertical-align:middle;">不仅缓存索引，还要缓存真实数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">表空间</td> 
   <td style="text-align:center;vertical-align:middle;">小</td> 
   <td style="text-align:center;vertical-align:middle;">大</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">关注点</td> 
   <td style="text-align:center;vertical-align:middle;">性能</td> 
   <td style="text-align:center;vertical-align:middle;"> <p>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;事务</p> </td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">默认安装</td> 
   <td style="text-align:center;vertical-align:middle;">Y</td> 
   <td style="text-align:center;vertical-align:middle;">Y</td> 
  </tr> 
 </tbody> 
</table>

## **4.MyISAM与InnoDB适用场景** ##

**MyISAM：**

1.  做很多count的计算；
2.  插入不频繁，应用中需要执行大量的SELECT，查询非常频繁；
3.  不需要事务；
4.  系统崩溃后，数据恢复困难，是否可以接受

**InnoDB：**

1.  可靠性要求比较高，或者要求事务；
2.  应用中需要执行大量的INSERT或UPDATE操作，表更新和查询都相当的频繁；

[20200728224230733.png]: /images/20230208/faf5ba97fcef46bc9aedee36ac267b11.png
[20200728224625365.png]: /images/20230208/53190ead92544dd4a5f61964e1cf27d0.png
[20200728232448720.png]: /images/20230208/3b5ac7ee866448e681fd2228c420a6f1.png