MySQL存储引擎 (包括 InnoDB 和 MyISAM 对比)-蒲公英云

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

常用的是InnoDB和MyISAM，因为在MySQL 5.5版本前，默认的存储引擎为MyISAM。在那之后MySQL的默认存储引擎改为InnoDB，所以目前来说新版不更推荐InnoDB

MySQL存储引擎

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

在这里插入图片描述

存储引擎	描述
InnoDB	具备外键支持功能的事务处理引擎
MRG_MYISAM	相同的MyISAM表的集合
MEMORY	置于内存的表
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
MyISAM	主要的非事务处理存储引擎
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
PERFORMANCE_SCHEMA	性能模式
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。

MyISAM与InnoDB的区别

-	MyISAM	InnoDB
存储结构	每张表被存放在三个文件： .frm文件存储表定义; 数据文件的扩展名为.MYD (MYData); 索引文件的扩展名是.MYI (MYIndex)	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
存储空间	MyISAM 可被压缩，存储空间较小，支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表	InnoDB 的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于 MyISAM 的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了
事务支持	强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持	提供事务支持事务，外部键等高级数据库功能。具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表
AUTO_INCREMENT	以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增	InnoDB 中必须包含只有该字段的索引，引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列
SELECT	MyISAM 更优
INSERT		InnoDB 更优
UPDATE		InnoDB 更优
DELETE		InnoDB 更优它不会重新建立表，而是一行一行的删除
COUNT without WHERE	MyISAM 更优。因为 MyISAM 保存了表的具体行数	InnoDB 没有保存表的具体行数，需要逐行扫描统计，就很慢了
COUNT with WHERE	一样	一样，InnoDB 也会锁表
锁	只支持表锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据	支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
外键	不支持	支持
FULLTEXT 全文索引	支持	不支持（5.6.4 以上支持英文全文索引）可以通过使用 Sphinx 从 InnoDB 中获得全文索引，会慢一点
表的具体行数	保存有表的总行数，如果select count() from table;会直接取出出该值	没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样
外键	不支持	支持

互联网项目中随着硬件成本的降低及缓存、中间件的应用，一般我们选择都以 InnoDB 存储引擎为主，很少再去选择 MyISAM 了。原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，要根据具体情况具体分析。

Reference