文章详细介绍了MySQL中的两种主要存储引擎InnoDB和MyISAM的特性与区别。InnoDB支持事务处理、行级锁定和崩溃恢复，适合高并发和事务性应用；MyISAM则有较高的读取性能和低存储空间需求，适用于读多写少的场景。此外，文章还提到了其他存储引擎如MEMORY和ARCHIVE等，以及InnoDB和MyISAM在索引实现上的差异。
MySQL中常见的存储引擎
MySQL支持多种存储引擎，每个存储引擎都有其特定的功能和适用场景。以下是MySQL中常见的存储引擎：
-
InnoDB：5.5.5版本之后默认的MySQL存储引擎。它支持事务处理和行级锁定，并具有较好的并发性能和数据完整性。适用于大多数应用程序场景，特别是涉及事务处理的应用。
-
MyISAM：5.5.5版本之前默认的存储引擎，特点是性能较快，但不支持事务和行级锁定。适用于读密集的应用，比如数据仓库或者只读的数据表。
-
MEMORY (HEAP)：将数据存储在内存中，读写速度非常快，但是数据在服务器重启或崩溃时会丢失。适用于临时表或者缓存等临时性需求。
-
NDB (MySQL Cluster)：用于MySQL Cluster分布式数据库，支持高可用性和水平扩展。适用于需要高可用性和大规模数据处理的场景。
-
ARCHIVE：用于归档数据，具有高压缩比，但不支持索引。适用于存档和日志类型的数据表。
-
CSV：将数据存储为CSV格式文件，方便与其他应用程序交换数据。
-
BLACKHOLE：该存储引擎接收但不实际存储数据，可以用于复制数据到其他服务器而无需在本地保留副本。
-
TokuDB：一个第三方的存储引擎，具有出色的压缩性能和查询速度，适用于大规模数据的高效存储和检索。
-
MariaDB ColumnStore：用于大数据分析的列式存储引擎。
官方文档： MySQL 5.7 参考手册 InnoDB 简介
InnoDB 、MyISAM 存储引擎的优缺点
InnoDB
特点：
- 事务支持：InnoDB支持ACID事务，可以执行复杂的事务操作，确保数据的完整性和一致性。
- 行级锁定：InnoDB使用行级锁定，多个事务可以同时访问表中的不同行，提高并发性能。
- 外键约束：InnoDB支持外键约束，保持数据之间的引用完整性。
- 崩溃恢复：InnoDB具有崩溃恢复的能力，通过事务日志将已提交的事务应用于数据，以保持数据的持久性。
- 支持缓存：InnoDB使用缓冲池缓存数据和索引，加快读取操作的速度。
缺点：
- 性能损失：由于支持事务和行级锁定，InnoDB的写入操作性能通常较MyISAM较差，尤其在高并发写入情况下。
- 存储空间占用：InnoDB存储引擎需要更多的存储空间来存储数据和索引。
MyISAM
特点：
- 较高读取性能：MyISAM在读取操作上具有较好的性能，特别是在并发读取的场景中。
- 全文索引：MyISAM支持全文索引功能，适用于文本搜索和全文检索。
- 低存储空间：MyISAM通常需要较少的存储空间，适用于存储大量历史数据。
- 简单和易于维护：MyISAM存储引擎相对简单，易于维护。
缺点：
- 不支持事务：MyISAM不支持事务，无法进行复杂的事务性操作。
- 表级锁定：MyISAM只支持表级锁定，对于并发写入操作可能存在较大的性能瓶颈。
- 数据不安全：MyISAM不支持崩溃恢复，当发生崩溃或意外关闭时，数据可能丢失或损坏。
综合考虑，如果应用场景需要事务支持、高并发性能和数据完整性，通常选择InnoDB存储引擎。如果应用场景是只读或读多写少，并需要全文搜索功能，可以考虑使用MyISAM存储引擎。在新版本的MySQL中，InnoDB成为默认存储引擎，从MySQL 5.6版本开始，InnoDB存储引擎也开始支持全文索引，建议优先考虑使用InnoDB来获得更多的优势和功能。
InnoDB 、MyISAM 存储引擎的区别
-
事务支持：
- InnoDB：InnoDB支持事务，是MySQL中唯一支持ACID（原子性、一致性、隔离性、持久性）事务的存储引擎。它可以执行复杂的事务操作，保证数据的完整性和一致性。
- MyISAM：MyISAM不支持事务，不能进行复杂的事务性操作，只支持基本的CRUD操作。
-
锁定级别：
- InnoDB：InnoDB使用行级锁定（Row-level locking），允许多个事务同时访问同一表的不同行，提高并发性能，降低锁冲突概率。
- MyISAM：MyISAM使用表级锁定（Table-level locking），在写入操作时会锁定整个表，这导致在高并发写入的情况下可能存在较大的性能瓶颈。
-
外键约束：
- InnoDB：InnoDB支持外键约束，可以确保数据之间的引用完整性，也可以进行级联操作（如级联删除和更新）。
- MyISAM：MyISAM不支持外键约束，无法进行外键关联操作。
-
崩溃恢复：
- InnoDB：InnoDB具有崩溃恢复的能力，通过事务日志将已提交的事务应用于数据，保持数据的持久性。
- MyISAM：MyISAM不支持崩溃恢复，当发生崩溃或意外关闭时，数据可能丢失或损坏。
-
全文索引：
- InnoDB：InnoDB从MySQL 5.6版本开始支持全文索引功能，之前的版本不支持。
- MyISAM：MyISAM支持全文索引功能，可以用于文本搜索和全文检索。
-
存储空间占用：
- InnoDB：InnoDB存储引擎通常需要更多的存储空间来存储数据和索引，因为它支持事务和行级锁定等功能。
- MyISAM：MyISAM存储引擎通常需要较少的存储空间，适用于存储大量历史数据。
InnoDB ，MyISAM 索引数据结构方面的区别
InnoDB索引数据结构：
-
B+树索引：InnoDB使用B+树作为其索引数据结构。B+树是一种平衡多路搜索树，具有较好的查询性能和维护效率。B+树的叶子节点包含实际的数据行，因此在查询时可以更快地找到匹配的数据。
-
聚簇索引：InnoDB的主键索引是聚簇索引。聚簇索引将数据行存储在B+树的叶子节点中，并按照主键的顺序进行排序。这意味着具有相邻主键值的数据行在物理上也是相邻存储的，从而提高了范围查询的效率。
-
辅助索引：InnoDB的辅助索引（非主键索引）的叶子节点不包含实际的数据行，而是存储主键的值。在使用辅助索引进行查询时，需要进行“回表”操作，通过主键索引再次查找对应的数据行。
MyISAM索引数据结构：
-
B树索引：MyISAM使用B树作为其索引数据结构。B树与B+树类似，但不同于B+树的是，B树的叶子节点包含实际的数据行。
-
非聚簇索引：在MyISAM中，主键索引与数据行是分开存储的。主键索引存储主键的值和数据行的指针，而数据行则单独存储在磁盘的数据文件中。
-
辅助索引：MyISAM的辅助索引（非主键索引）的叶子节点同样包含实际的数据行指针，从而避免了“回表”操作。
总结：
- InnoDB使用B+树索引，支持聚簇索引，辅助索引的叶子节点存储主键值。
- MyISAM使用B树索引，主键索引和数据行是分开存储的，辅助索引的叶子节点存储数据行指针。
需要注意的是，InnoDB的聚簇索引使得主键值的选择很重要，尽量选择有序的主键值可以提高查询性能，而MyISAM的辅助索引直接存储数据行指针，查询性能受到辅助索引的选择影响较大。在使用不同的存储引擎时，需要考虑到索引的特点来优化数据库的设计和查询性能。
InnoDB ，MyISAM存储引擎索引实现
InnoDB索引实现
InnoDB索引的实现采用了B+树（B+ Tree）数据结构。B+树是一种平衡多路搜索树，被广泛应用于数据库系统中，用于快速定位存储在磁盘上的数据。
在InnoDB中，每个表都有一个主键索引，这个索引是一个聚簇索引（Clustered Index）。聚簇索引的特点是数据行实际存储在索引的叶子节点中，而非另外单独存储。具体实现如下：
-
B+树结构： InnoDB使用B+树来实现索引，B+树是一种平衡多路搜索树，每个节点可以包含多个键和指针。B+树的高度通常较低，因此在查找数据时具有高效的查询性能。
-
叶子节点存储数据行： 在聚簇索引中，B+树的叶子节点包含实际的数据行。这意味着叶子节点按照主键值的顺序存储数据，使得相邻主键值的数据行在物理上也是相邻存储的。这样的有序存储提高了范围查询的性能。
-
主键索引和数据行的关联： InnoDB的主键索引直接与数据行相关联。当执行根据主键进行数据查询时，InnoDB可以直接通过B+树找到相应的数据行，而无需再次查找数据文件。
-
辅助索引（Secondary Index）： InnoDB也支持辅助索引，即非主键索引。辅助索引的叶子节点存储的是主键值，而不是实际的数据行。当使用辅助索引进行查询时，InnoDB需要进行"回表"操作，通过主键索引再次查找对应的数据行。
使用InnoDB引擎创建数据表，将产生2个文件，文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义，数据文件的扩展名为.ibd。
InnoDB采用的聚集索引，聚集索引默认由主键实现（用主键作为B+树的key，并且把数据行绑定在叶子节点）
如果表中没有定义主键，InnoDB会选择一个唯一且非空的列代替，如果没有这样的列，InnoDB会隐式定义一个主键（类似oracle中的RowId）来作为聚集索引。
ibd文件结构：
读写操作：
将内存中的数据刷到磁盘，或者将磁盘中的数据加载到内存，都是以批次为单位，这个批次就是我们常说的数据页。
数据页：
主要是用来存储表中记录的，它在磁盘中是用双向链表相连的，方便查找，能够非常快速得从一个数据页，定位到另一个数据页，数据页主要包含如下几个部分：
-
文件头部
-
页头部
-
最大和最小记录
-
用户记录
-
空闲空间
-
页目录
-
文件尾部
InnoDB数据页结构图：
总结：
多个数据页之间通过页号构成了双向链表。而每一个数据页的行数据之间，又通过下一条记录的位置构成了单项链表。
整体结构图：
由于记录不断增多，一层目录也就不能满足我们的需求，在原来目录页的基础之上我们可以生成再高一级的目录页，生成多层级的目录,这样的结构就是B+树，ibd文件就是这样存储的。
MyISAM索引实现
MyISAM的索引实现采用了B树结构，主键索引和数据行是分开存储的，主键索引包含指向实际数据行的指针。MyISAM支持全文索引功能，适用于读多写少或只读的场景，并且需要全文搜索功能。然而，值得注意的是，MyISAM不支持事务和行级锁定，因此在高并发写入场景下可能存在性能和数据完整性的问题。在现代的MySQL应用中，通常推荐使用InnoDB引擎，特别是对于需要事务支持和更好并发控制的场景。
在MyISAM中，包含以下关键点的索引实现：
-
B树结构： MyISAM使用B树来实现索引，B树是一种平衡多路搜索树，与B+树类似，但不同于B+树的是，B树的叶子节点同样包含实际的数据行。
-
主键索引和数据行分开存储： MyISAM的主键索引和数据行是分开存储的。主键索引包含指向实际数据行的指针，而不是数据行本身。
-
辅助索引（Secondary Index）： MyISAM同样支持辅助索引，与主键索引类似，辅助索引的叶子节点也包含指向实际数据行的指针。
-
全文索引： MyISAM是MySQL中支持全文索引功能的存储引擎。全文索引允许对文本字段进行高效的文本搜索和全文检索。
MyISAM把索引和数据分成了两个文件，这样我们查询的时候需要先去索引文件拿到数据文件的地址，然后根据这个地址在去数据文件里获取具体数据。
使用MyISAM引擎创建数据库，将产生3个文件，文件的名字以表名字开始，扩展名之处文件类型：.frm文件存储表定义，.MYD（MYData）数据文件，.MYI（MYIndex）索引文件。
由于MyISAM是采用的非聚集索引，非聚集索引将数据和索引分开存储的，索引结构的叶子节点存储了这条记录的磁盘的地址，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
