Mysql底层剖析——各存储引擎的区别_mysql四种引擎的区别

---

---

一、InnoDB存储引擎

1、是MySQL的默认事务性引擎，拥有自动崩溃恢复特性。

2、将每个表的数据和索引都放在一起放在.ibd文件中。

3、InnoDB有两个表，一个是.ibd文件，一个是.frm(存储数据结构通用)

4、采用MVCC支持高并发，实现了四个标准的隔离级别，在REPEATED READ中通过实现间隙锁来防止幻读的出现。间隙锁使的InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

5、磁盘读取数据时采用可预测性预读，能够自动在内存中创建hash索引加速读操作的自适应哈希索引等。

二、 MyISAM存储引擎

1、MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复InnoDB却可以。

2、MyISAM有三个文件，InnoDB有两个，因为MyISAM将索引和数据分开存放，.MYD(放数据)、.FRM、MYI（放索引）

3、加锁与并发
MYISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时对表加排它锁，同一个事务读表时，可以插入新的记录。

4、对于MyISAM表，可以手工或者自动执行检查和修复操作，注意和崩溃分开。通过CHECK TABLE mytable检查表错误，有错误可以通过REPAIR TABLE mytable修复，也可以通过myisamchk命令行工具进行检查和修复操作。

5、MyISAM支持全文索引

6、延迟更新索引键：创建表的时候，如果制定了DELAY_KEY_WRITE，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有清理缓冲区或者关闭表的时候才会将对应的索引块携带磁盘

7、MyISAM支持压缩表，在创建表并导入数据后，如果后续不再用可以进行压缩，压缩表是不能修改的，除非解压，压缩表可以极大地减少磁盘的空间占用，减少磁盘IO，提升性能，压缩表也支持索引，但是也只是支持只度的。

8、MyISAM引擎涉及简单，数据紧密格式存储，在某些场景下的性能很好。
MyISAM有一些服务级别的性能扩展限制，比如索引键缓冲区的Mutex锁，MariaDB是基于段的索引键缓冲机制来避免该问题的。但是最大的问题还是表锁问题。

三、InnoDB存储引擎与MyISAM存储引擎的区别

1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败

3.索引的存储结构不同，MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

4、在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

5、如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

6、如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

7、除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

8、主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录、根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

9、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（加where两个都一样）

10、 Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

11、. MyISAM表格可以被压缩后进行查询操作

12、InnoDB支持表、行级锁(默认行级锁），而MyISAM支持表级锁，InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。意思就是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

13、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列ROWID来充当默认主键），而Myisam可以没有

14、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

InnoDB为什么推荐使用自增ID作为主键？
自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

四、如何选择数据库引擎

参考csdn：https://blog.csdn.net/gdnlnsjd/article/details/119713875

四、其他引擎

Archive引擎
        只支持Insert和select操作在MySQL5.1之前也不支持索引。每次SELECT查询都需要执行全表扫描。Archive引擎支持行级锁和专用的缓冲区，可以实现高并发的插入。在一个查询开始知道返回表中的所有行数之前，Archive引擎会阻止其他的SELECT操作，以实现一致性读。

Blackhole引擎
        没有任何的存储机制，它会丢弃所有插入的数据，不做任何保存。

CSV引擎
        可以将普通的CSV文件作为MySQL的表处理，但是这种表不支持索引。

Memory引擎
        如果需要快速地访问数据，并且这些数据不会被修改，那么可以使用Memory表，Memory表比MyISAM表快一个数量级，因为其所有的数据都保存在内存中，不需要进行磁盘I/O，Memory表的结构在重启后还会保留，以防数据丢失。
        Memory表支持Hash索引，所以查找操作非常快。Memory表是表级锁，并发写入的性能较低。
MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表，如果表太大会转变为MyISAM表。
        注意Memory表和临时表的区别：临时表是指使用CREATE TEMPORARY TABLE语句创建的表，它可以使用任何存储引擎，而且临时表只在单个连接中可见，当连接断开时，临时表也将不复存在。

Merge引擎
        Merge引擎是由对个MyISAM表合并而来的虚拟表

五、转换表的引擎的方法

有下列三种方法

1、ALTER TABLE
        将表从一个引擎修改为另一个引擎

        MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表上会加上读锁。如果转换表的存储引擎，将会失去和原引擎相关的所有特性，如果将一张InnoDB转换为MyISAM，然后在转换会InnoDB，原InnoDB表上所有的外检将丢失。

2、导入和导出
        可以使用mysqldump工具将数据导出文件，然后修改文件CREATE TABLE语句的存储引擎选项，注意同时修改表明，同一个数据库中不会存在相同的表明，即使它们是不同的UC农户引擎，但是要注意mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE。

3、创建与查询（CREATE和SELECT）
        这种方法结合了第一种的高效和第二种方法的安全，不需要导出整个表的数据，而是先创建一个新的存储引擎的表，然后利用INSERT….SELECT导数据如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作。这样操作完成以后，新表是原表的一个全量复制，可以避免手工操作可能导致的失误和繁琐。