Innodb为何能干掉MyISAM

Innodb为何能干掉MyISAM

各款引擎各有千秋，但其中最为常用的就只有MyISAM、InnoDB这两款引擎。

MyISAM引擎是MySQL官方基于早期的ISAM引擎改良而来的，它是一款“苗根正红”的引擎，由于其不错的性能表现，再加上丰富的特性支持（全文索引、压缩机制、空间索引/函数等），在MySQL5.5版本之前，也一直是MySQL默认的存储引擎。

但随着时间慢慢推移，MySQL官方渐渐有了“新欢”，开始主推使用InnoDB作为表的引擎，甚至到了MySQL5.6及以后版本中，直接用InnoDB代替了MyISAM，作为了MySQL默认的存储引擎

一：MyISAM引擎 vs InnoDB引擎

1：磁盘文件的对比

1.1：5.7版本对比

其中使用MyISAM引擎的表：会在本地生成三个磁盘文件：

• zz_myisam_index.frm：该文件中存储表的结构信息。
• zz_myisam_index.MYD：该文件中存储表的行数据。
• zz_myisam_index.MYI：该文件中存储表的索引数据。

所以，MyISAM引擎的表数据和索引数据，会分别放在两个不同的文件中存储。

使用InnoDB引擎的表：在磁盘中仅有两个文件：

• zz_innodb_index.frm：该文件中存储表的结构信息。
• zz_innodb_index.ibd：该文件中存储表的行数据和索引数据。

1.2：8.0版本

myisam的三个文件分别是：(8.0版本)

• zz_myisam_index.MYD：该文件中存储表的行数据。
• zz_myisam_index.MYI：该文件中存储表的索引数据。
• zz_myisam_index_570.sdi：MySQL8将frm文件的信息以及更多信息移动到叫做序列化字典信息(SDI)中

innodb的文件是：(8.0版本)

• zz_innodb_index.ibd：数据库信息、表结构、表数据以及表索引均存储在ibd文件中。

2：索引层面的支持

MyISAM引擎在设计之初，表数据和索引数据是分别放在.MYD、.MYI文件中，所以注定了MyISAM引擎只支持非聚簇索引。[因为是分开放的]

而InnoDB引擎的表数据、索引数据都放在.ibd文件中存储，因此InnoDB是支持聚簇索引的。

为啥分开放置一定不是聚簇索引

因为聚簇索引的要求是：索引键和行数据必须在物理空间上也是连续的，而MyISAM表数据和索引数据，分别位于两个磁盘文件中，注定无法满足要求。

🎉 一种引擎支不支持聚簇索引很重要，这涉及到了后面的很多技术实现，这也是Innodb可以干掉MyISAM的原因之一

❓ 但不支持聚簇索引也有好处，也就是无论走任何索引，都只需要一遍查询即可获得数据，而InnoDB引擎的表中，如果不走聚簇（主键）索引查询数据，走其他索引的情况下，都需要经过两遍（回表）查询才能获得数据。[索引覆盖除外，前面已经介绍过，不再赘述]

3：事务机制的对比

InnoDB引擎中有两个自己专享的日志，即undo-log、redo-log

一条写入类型的SQL语句，在正式执行前都会先记录redo-log、undo-log日志

undo-log中会记录变更前的旧数据，当一个事务提交时，MySQL会正常的将数据落盘，而当一个事务碰到rollback命令需要回滚时，就会找到undo-log中记录的旧数据，接着用来覆盖变更过的新数据，以此做到将数据回滚到变更前的“样貌”。

使用InnoDB存储引擎的表，可以借助undo-log日志实现事务机制，支持多条SQL组成一个事务，可以保证发生异常的情况下，组成这个事务的SQL到底回滚还是提交。

而MyISAM并未设计类似的技术，在启动时不会在内存中构建undo_log_buffer缓冲区，磁盘中也没有相应的日志文件，因此MyISAM并不支持事务机制。

一个引擎是否支持事务，这点尤为重要，因为业务开发过程中，咱们需要关注数据的安全性，这时候就很需要事务的原子性，但是MyISAM没有

所以，如果表结构用了MyISAM引擎，想要解决这类问题，就只能在客户端做事务补偿，记录下失败的，然后生成一个反向SQL，保证数据的一致性

4：故障恢复的对比

InnoDB在启动时，会在内存中构建一个redo_log_buffer缓冲区，在磁盘中也会有相应的redo-log日志文件

所以当一条或多条SQL语句执行成功后，不论MySQL在何时宕机，只要这个事务提交了，InnoDB引擎都能确保该事务的数据不会丢失

也就以此保障了事务的持久性。

InnoDB引擎由于redo-log日志的存在，因此只要事务提交，机器断电、程序宕机等各种灾难情况，都可以用redo-log日志来恢复数据。【两阶段】

但MyISAM引擎同样没有redo-log日志，所以并不支持数据的故障恢复

如果表是使用MyISAM引擎创建的，当一条SQL将数据写入到了缓冲区后，SQL还未被写到bin-log日志，此时机器断电、DB宕机了，重启之后由于数据在宕机前还未落盘，所以丢了也就无法找回。

从这一点来说，MyISAM并没有InnoDB引擎可靠

在InnoDB中只要事务提交，它就能确保数据永远不丢失，但MyISAM不行。

这就好比去银行存钱：

• 去InnoDB银行存，你只需要把钱送到它那里，它就能确保你的财产安全【事务提交，它就能确保数据永远不丢失】
• 去MyISAM银行存钱，你必须要把钱送到银行的保险库中才行，否则有可能会因为在送往保险库的过程中“丢失”财产。【写入bin-log才行】

5：锁粒度对比

MySQL的存储引擎中，MyISAM仅支持表锁，而InnoDB同时支持表锁、行锁

MyISAM为啥不能加行锁 <- 因为不支持聚簇索引

假设有一张学生表：

假设使用的是MyISAM引擎，同时对student_id字段建立了主键索引，name字段建立了普通索引，sex、height字段建立了联合索引

此时先不管索引合不合理，以目前情况为例，来推导一下MyISAM表为啥无法实现行锁。

这张表中存在三个索引，那在本地的.MYI索引文件中，肯定存在三颗B+树

同时由于MyISAM不支持聚簇索引，所以这三个索引是平级的，每棵B+树的索引键，都直接指向.MYD数据文件中的行数据地址。

⚠️ 假设MyISAM要实现行锁，当要对一行数据加锁时，可以锁定一棵树中某一个数据，但无法锁定其他树的行数据

select * from students where student_id = 1 for update;

这条SQL必然会走主键索引命中数据，那假设此时对主键索引树上，ID=1的数据加锁，然后接下来再走name字段加锁

select * from students where name = '张三' for update;

此时这条SQL又会走name字段的普通索引查询数据，那此时又对普通索引树上的「张三」数据加锁。

所以MyISAM如果想要实现行锁，就会遇到这个问题，基于不同索引查询数据时，可能会导致一行数据上加多个锁！这样又会导致多条线程同时操作一个数据，所以又会因为多线程并发执行的原因，造成脏读、幻读、不可重复读这系列问题出现。

InnoDB为啥行，也是因为聚簇索引

因为支持聚簇索引，表中就算没有显式定义主键，内部依旧会用一个隐藏列来作为聚簇索引的索引字段

所以InnoDB表中的索引，是有主次之分的，所有的次级索引，其索引值都存储聚簇索引的索引键

因此想要对一行数据加锁时，只需要锁定聚簇索引的数据即可。

-- 通过主键索引查询数据
select * from students where student_id = 1 for update;
-- 通过普通索引查询数据
select * from students where name = "张三" for update;

依旧是前面的这个例子，通过主键索引查询的SQL语句，会直接定位到聚簇索引的数据，然后对ID=1的数据加锁。

而第二条通过普通索引查询数据的SQL语句，经过查询后会得到一个值：ID=1，然后会拿着这个ID=1的值再去回表，在聚簇索引中再次查询ID=1的数据

找到之后发现上面已经有线程加锁了，当前线程就会阻塞等待上一个线程释放锁。

总结

因为MyISAM引擎不支持聚簇索引，所以无法实现行锁，出现多条线程同时读写数据时，只能锁住整张表。

而InnoDB由于支持聚簇索引，每个索引最终都会指向聚簇索引中的索引键，因此出现并发事务时，InnoDB只需要锁住聚簇索引的数据即可，而不需要锁住整张表，因此并发性能更高。

同时，InnoDB引擎构建的缓冲区中，会专门申请一块内存作为锁空间(Lock Space)，同时再结合InnoDB支持事务，所以InnoDB是基于事务来生成锁对象，相较于SQL Server的行锁来说，InnoDB的行锁会更节约内存

6：并发性能的对比

MyISAM仅支持表锁，InnoDB同时支持表锁、行锁

由于这点原因，其实InnoDB引擎的并发支持性早已远超MyISAM了，毕竟锁的粒度越小，并发冲突的概率也就越低，因此并发支撑就越高。

同时InnoDB为了提升读-写并存场景下的并发度，又基于undo-log日志的版本链+事务快照，又推出了MVCC多版本并发控制技术，因此对于读-写共存的场景支持并发执行。

但MyISAM只支持表锁，因此当一条SQL在写数据时，其他SQL就算是来读数据的，也需要阻塞等待，因为写数据时需要加排他锁

反过来也是同理，当一条线程在读数据时，另一条线程来写数据，依旧会陷入阻塞等待，毕竟写数据要获取排他锁，也就意味着整张表只允许这一个线程操作。

7：内存利用度对比

InnoDB几乎将内存开发到了极致，虽然InnoDB不像Memory引擎那样完全基于内存运行，但它将所有能够在内存完成的操作，全部都放在了内存中完成

无论是读写数据、维护索引结构也好，记录日志也罢，各类操作全部都在内存完成。

只要你机器的内存够大，为缓冲池分配的内存够多，MySQL在线上运行的时间够久，InnoDB甚至会将磁盘中的所有数据，全部载入内存

所有客户端的读写请求，基本上无需再走磁盘来完成，都采用异步IO的方式完成，即先写内存+后台线程刷写的方式执行

后台线程的刷盘动作，对客户端而言不会有任何感知，在写完内存之后就会直接向客户端返回。

而MyISAM因为MySQL Server的设计影响，对内存的开发度远不足于InnoDB引擎，运行期间大量操作依旧会走磁盘完成

总结下MyISAM的问题

• 存储方式：MyISAM引擎会将表数据和索引数据分成两个文件存储。这也直接导致了其不支持聚簇索引，从而引发一系列的问题
• 索引支持：因为MyISAM引擎的表数据和索引数据是分开的，因此不支持聚簇索引。
• 事务支持：由于MyISAM引擎没有undo-log日志，所以不支持多条SQL组成事务并回滚。
• 故障恢复：MyISAM引擎依靠bin-log日志实现，bin-log中未写入的数据会永久丢失。
• 锁粒度支持：因为MyISAM不支持聚簇索引，因此无法实现行锁，所有并发操作只能加表锁。
• 并发性能：MyISAM引擎仅支持表锁，所以多条线程出现读-写并发场景时会阻塞。
• 内存利用度：MyISAM引擎过于依赖MySQL Server，对缓冲池、异步IO技术开发度不够。

二：MyISAM真的一无是处吗？

1：统计数量的优化

假设要统计一个表中总共有多少条数据的时候，MyISAM很快

因为在MyISAM引擎中会记录表的行数

select count(*) from person;

就是如果一个查询语句是无条件的数量查询，那么MyISAM会非常的快，因为内部记录了数据的行数，直接拿到这个值返回就行了

但是MyISAM的这个特性也仅仅只适用于统计全表数据量，如果后面跟了where条件：

select count(*) from table_name where xxx = "xxx";

如果是这种情况，那InnoDB、MyISAM的工作模式是相同的，先根据where后的条件查询数据，再一行行统计总数。

2：删除数据/表的优化

当使用delete命令清空表数据时

delete from table_name;

MyISAM会直接重新创建表数据文件，而InnoDB则是一行行删除数据

因此对于清空表数据的操作，MyISAM比InnoDB快上无数倍。

同时MyISAM引擎的表，对于delete过的数据不会立即删除，而且先隐藏起来，后续定时删除或手动删除，手动强制清理的命令如下：

optimize table table_name;

这样做有一点好处，就是当你误删一张表的大量数据时，只要你手速够快，手动将本地的.MYD、.MYI文件拷贝出去，就可以直接基于这两个数据文件恢复数据

3：单机CRUD更快

因为MyISAM是非聚簇索引，所以不存在回表问题，只要一次命中任意索引就可以得到完整的行数据

而InnoDB也会定义一个隐式字段ROW_ID来作为聚簇索引字段，这也就意味着：在InnoDB的表中，这个聚簇索引你不要也得要，当查询数据时，如果在基于非聚簇索引查找数据，就算查到了也需要经过一次回表才能得到数据，同时插入数据、修改数据时，都需要维护聚簇索引和非聚簇索引之间的关系

只是单个客户端快

如果是对比单个客户端连接的读写性能，那自然MyISAM远超于InnoDB引擎，毕竟InnoDB需要维护聚簇索引，而MyISAM因为每个索引都是独立的

因此MyISAM插入表数据时都是直接追加在表数据文件的末尾即可，而且修改数据也不需要维护其他索引和聚簇索引的关系。

但把测试的环境换到多个客户端连接的场景时，会出现不同的现象

随着连接数的增加，工作线程会不断增加，CPU使用核数也会不断增加，而InnoDB的性能会逐步上升，但MyISAM引擎基本上没有太大变化，基本上从头到尾一直都很低，原因正是MyISAM只支持表锁，无论有多少个客户端连接到来，对于同一张表永远只能允许一条线程操作，除非多个连接都是RR，才不会相互排斥

反观InnoDB引擎，由于支持行锁，所以并发冲突很小，在高并发、多连接的场景中，性能会更加出色，而MyISAM引擎基本上在并发读写场景中，一张表只允许单线程操作，因此并发冲突很大，吞吐量会因此严重下降。

4：压缩机制

数据库的数据量一多，就很容易出现以下两个问题：

• IO瓶颈：DB数据量过大，导致内存无法载入太多数据，会触发大量磁盘IO，让DB整体性能降低。
• 磁盘空间不足：随着业务的发展，部署数据库的机器磁盘无法存储数据，需要不断扩容硬件。

而MyISAM引擎为了解决这个问题，可以通过myisampack工具对数据表进行压缩

压缩的效果至少能让数据缩小一半，但压缩后的数据只可读，不可写，这点要牢记！

到了MySQL5.7版本中，该特性也被移植到了InnoDB引擎中，相关的压缩参数如下：

• innodb_compression_level：调整压缩的级别，可控范围在1~9，越高压缩效果越好，但压缩速度也越慢。
• innodb_compression_failure_threshold_pct：当压缩失败的数据页超出该比例时，会加入数据填充来减小失败率，为0表示禁止填充。
• innodb_compression_pad_pct_max：一个数据页中最大允许填充多少比例的空白数据。
• innodb_log_compressed_pages：控制是否对redo-log日志的数据也开启压缩机制。
• innodb_cmp_per_index_enabled：是否对索引文件开启压缩机制。

🎉 压缩机制仅需了解即可，毕竟现在分布式技术十分成熟了，因此很少会让单库承载特别大的数据量

一般当数据达到一定级别时，都会采用分库分表的方案来均摊数据，避免单库数据量过大而影响性能。

5：MyISAM应用场景

结合MyISAM引擎的特性而言，它适用于一些不需要事务、并发冲突低、读操作多的表，例如文章表、帖子表、字典表…

有一种场景时，特别适合使用MyISAM引擎，即MySQL利用主从架构，实现读写分离时的场景

一般从库会承载select请求，而主库会承载insert/update/delete请求。

读写分离的场景中，从库的表结构可以改为MyISAM引擎，因为基于MyISAM的索引查询数据，不需要经过回表查询，速度更快！

同时，由于做了读写分离，因此从库上只会有读请求，不会存在任何外部的写请求，所以支持并发读取。

而且从库的数据是由后台线程来从主库复制的，因此从库在写入数据时，只会有少数几条线程执行写入工作，因而造成的冲突不会太大，不会由于表锁引起大量阻塞。

三：存储引擎的相关命令