MySQL之InnoDB和MyISAM存储引擎

mysql

存储引擎在MySQL的逻辑架构中位于第三层，负责MySQL中的数据的存储和提取。
MySQL存储引擎有很多，不同的存储引擎保存数据和索引的方式是不同的。
每一种存储引擎都有它的优势和劣势，最常见的两种存储引擎是 InnoDB 和 MyISAM。

一、MySQL支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

二、InnoDB

InnoDB 是 MySQL 中默认的事务型存储引擎，更关注事务实现，一般情况下我会选择使用 InnoDB。

1. 数据存储方式

使用 InnoDB 作为存储引擎时，以表 user 为例，在磁盘上将出现两个文件：user.frm 和 user.ibd，其中：

• user.frm 保存了表的结构性数据
• user.ibd 保存了表的数据和索引

2. 锁

InnoDB 的锁粒度是 行，但 在未使用索引或者索引失效时，将升级为表锁。

3. 事务

Innodb 实现了四个隔离级别，默认的隔离级别是 repeatable-read，并通过 MVCC（多版本控制） 的方式来支持高并发。

4. 聚簇索引

Innodb 的表数据是基于 聚簇索引 存储的，对主键查询有很高的性能，但对非主键索引和二级索引来说必须包含主键索引，索引其他索引的数据消耗会很大。

三、MyISAM

MyISAM 更关注性能，在不包含事务操作的表上，我会选择使用 MyISAM。

1. 数据存储方式

使用 MyISAM 作为存储引擎时，以表 user 为例，在磁盘上将出现三个文件：user.frm、user.MYD 和 user.MYI，其中：

• user.frm 保存了表的结构性数据
• user.MYD 保存了表的数据
• user.MYI 保存了表的索引数据

2. 锁

MyISAM 不支持行锁，只支持表锁。在读取数据时，将对整个表增加 共享锁。在写入数据时，将对整个表增加 排它锁。因此，在并发场景下，MyISAM 的读性能很高，写性能较低。

3. 事务

MyISAM 是不支持事务的。

4. 索引

MyISAM 是基于非聚簇索引存储数据的。

5. 其他

• MyISAM提供了大量的特性，包括全文索引，压缩，空间函数，延迟更新索引键等。
• 进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。
• 全文索引，是一种基于分词创建的索引，可以支持复杂的查询。
• 延迟更新索引键，不会将更新的索引数据立即写入到磁盘，而是会写到内存中的缓冲区中，只有在清除缓冲区时候才会将对应的索引写入磁盘，这种方式大大提升了写入性能。

四、MyISAM 和 InnoDB 如何选择

1. 是否包含事务操作？

如果需要事务操作，则选择 InnoDB。没有事务操作的话，选择 MyISAM。

2. 是否存在并发写入和更新？

MyISAM 在并发写入和更新时的表现不佳，如果存在并发写入，选择 InnoDB。

3. 是否更注重查询效率，而写入和更新操作很少？

如果是，选择 MyISAM。

4. 是否需要全文索引？

如果不考虑第三方索引框架，如：elasticsearch的话，可以考虑使用 MyISAM。