MySQL 有好多存储引擎呢，不同的引擎在不同的情况下用才合适，搞清楚它们的特点对设计数据库可重要啦。

1. MyISAM 引擎

MyISAM 是 MySQL 里 ISAM 的扩展形式，也是默认的数据库引擎之一。它读取速度挺快的，适合那种主要是读取数据的小项目或者做网页开发。

MyISAM 有这些特点：

数据存得挺紧凑的，把表存在两个文件里，一个是数据文件叫.MYD，一个是索引文件叫.MYI。

访问速度快，要是只读数据，或者表比较小、能接受修复操作的情况就很适用。

它不支持事务，也没有行级锁，崩溃了没法安全恢复，也不支持外键。这就说明在需要保证事务完整、并发要求高的情况下，MyISAM 就不合适啦。

它只会缓存索引，能通过 key_buffer_size 这个值来提高索引缓存，这样能大大提高访问性能，减少磁盘读写，不过这个缓存区就只缓存索引，不缓存数据。

它支持全文搜索，在搜索引擎和内容管理系统里特别有用。

用的是表级锁，数据更新的时候会锁住整个表，在高并发写操作的时候可能会让性能变差。虽然读操作能并发进行，但是写操作会把其他读写操作都堵住

2. InnoDB 引擎

InnoDB 是那种能保证事务安全的存储引擎，支持事务处理、外键约束、行级别锁定这些特性，适合对数据完整性要求高、有很多更新和删除操作的应用场景。

InnoDB 有这些特点：

支持事务，能实现 ACID 特性（就是原子性、一致性、隔离性、持久性），能保证数据库里数据完整、一致。支持 4 个事务隔离级别，还支持多版本读。

行级锁定，通过索引实现，要是全表扫描还是会锁住整个表，要注意间隙锁的影响。行级锁定机制让并发性能更好，好几个事务能同时操作表里面不同的行。

读写会不会阻塞和事务隔离级别有关，有好几个级别能选。

缓存特别高效，能缓存索引，也能缓存数据。能让读取数据的性能变好，减少磁盘读写操作。

整个表和主键用 Cluster 方式存，组成一棵平衡树。所有 Secondary Index 都会保存主键的信息。

支持分区、表空间，跟 Oracle 数据库有点像。提供了更灵活的管理数据库的办法。

支持外键约束，能保证关联表之间数据一致。在插入、更新或者删除数据的时候，会检查外键约束。

能自动崩溃恢复，数据库重新启动的时候，会自动把没完成的事务回滚或者提交，保证数据库一致。

能热备份和恢复，支持在线热备份，数据库运行的时候就能备份。同时，也有恢复工具，崩溃以后能很快把数据库恢复到一致的状态。

能灾难恢复，有恢复工具和日志文件，系统崩溃或者出了灾难事件以后能很快恢复数据库。

3. MEMORY 引擎

MEMORY 引擎的数据全放在内存里，处理速度可快啦，但是服务器得有足够的内存，而且数据容易丢，适合在不重要数据的高速查找场景或者临时工作的地方用。

MEMORY 有这些特点：

查询速度快，因为数据直接在内存里存着，不用磁盘读写操作。特别是那种需要频繁读写的情况，优势特别明显。

性能稳定，因为不用磁盘读写操作，所以性能不会被磁盘读写速度影响。

占的空间小，只适合存一些临时数据或者数据量小的表，能省硬件成本。

默认用哈希索引，把键的哈希值和指向数据行的指针存在哈希索引里。速度比用 B 型树索引快，但是也有缺点，比如哈希索引数据不是按索引值顺序存的，没法用来排序；不支持部分索引匹配查找，因为哈希索引是用索引列的全部内容算哈希值的；只支持等值比较，不支持范围查询；要是出现哈希冲突，存储引擎就得把链表里所有的行指针都遍历一遍，一行一行比，直到找到符合条件的行。

数据不是一直存着的，系统重启或者出了异常，所有数据都没啦。这就得咱们多注意备份和恢复。管理大的数据表的时候，效率可能不高。不支持事务和完整性约束这些功能，所以在一些对数据可靠性要求高的场景，就得选别的存储引擎啦。

二、MySQL 数据库引擎的选择

得按照实际用的情况来选合适的数据库引擎。

1. 读操作和插入操作为主的情况

要是主要是读操作和插入操作，MyISAM 引擎就挺合适。MyISAM 引擎不支持事务，对事务完整和并发的要求不高。它的数据存得紧凑，把表存在两个文件里，一个是数据文件叫.MYD，一个是索引文件叫.MYI，访问速度快。在这种情况下，主要就是读数据和插新数据，很少有更新和删除操作，MyISAM 的表级锁对性能影响不大。而且 MyISAM 支持全文索引，要是需要文本搜索的应用就很有用。比如说，在一些只读的数据报表、存静态内容的地方，MyISAM 引擎就能显出它的好处。

2. 对事务完整性要求高的情况

要是对事务完整性要求高，InnoDB 引擎就是好选择。InnoDB 引擎支持事务处理，能实现 ACID 特性（就是原子性、一致性、隔离性、持久性），能保证数据库里数据完整、一致。它还支持外键约束，能保证关联表之间数据一致。InnoDB 用行级锁定，并发性能好，好几个事务能同时操作表里面不同的行。另外，InnoDB 缓存效果特别好，能缓存索引，也能缓存数据，能让读取数据的性能变好，减少磁盘读写操作。在银行系统、电子商务网站这种对数据完整性要求高、有很多更新和删除操作的地方，InnoDB 引擎是首选。

3. 内容不怎么变的小表或者要快速找到记录的情况

对于内容不怎么变的小表或者需要很快找到记录的情况，MEMORY 引擎能提供特别快的访问。MEMORY 引擎的数据全在内存里，查询速度快，性能稳，占的地方小。但是，因为数据在内存里存着，要是系统重启或者出了异常，所有数据就都没啦。所以用 MEMORY 引擎的时候，得保证数据能恢复，比如定期把数据备份到磁盘啥的。MEMORY 引擎默认用哈希索引，比用 B 型树索引快，不过也有缺点，比如哈希索引数据不是按索引值顺序存的，不能用来排序；不支持部分索引匹配查找，就支持等值比较，不支持范围查询；要是有哈希冲突，存储引擎就得把链表里所有的行指针都过一遍，一行一行比，直到找到符合条件的行。所以，MEMORY 引擎适合临时表、缓存表或者读写速度要求高而且数据丢了也没事的情况。

三、MySQL 行格式介绍

行格式能决定一行数据在磁盘上咋存，不同的行格式对数据读写的效率有影响。

1. InnoDB 行格式的种类

InnoDB 存储引擎现在支持四种行格式，有 REDUNDANT、COMPACT、DYNAMIC 还有 COMPRESSED。

2. REDUNDANT 行格式

这是一种不紧凑的行格式，挺占磁盘空间的，效率也不高。把所有列占的存储空间倒着放在字段长度偏移列表里，记录头信息固定占 6 个字节，用比特位标记来处理 NULL 值。

REDUNDANT 是 InnoDB 最早的行格式，主要在早期版本的 MySQL 里用。这种格式的特点是存了更多的元数据，像额外的系统信息和字段的长度信息，这样每行数据占的空间就更多。就算字段里没内容，也会给它分存储空间。虽说 REDUNDANT 行格式向后兼容挺好，但存东西的效率低。在这种行格式里，用 REDUNDANT 行格式的表会把前 768 个字节的可变长度列值（像 VARCHAR、VARBINARY 还有 BLOB 和 TEXT 类型）存在 B 树节点里的索引记录中，剩下的部分存在溢出页上。大于或者等于 768 字节的固定长度列会被编成可变长度列，能在页外面存。

每个索引记录都有一个 6 字节的标头。标头能把连着的记录连起来，还能用来做行级锁定。聚集索引里的记录有所有用户定义列的字段。另外，还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。要是没给表定主键，那每个聚集索引记录还会有一个 6 字节的行 ID 字段。每个二级索引记录都有为主键定义的、不在二级索引里的所有主键列。记录里有指向记录每个字段的指针。要是一条记录的字段总长度小于 128 字节，那指针就是 1 个字节；要是超过了，就是 2 个字节。这指针的数组叫记录目录。指针指的地方就是记录的数据部分。在里面，像 CHAR (10) 这种固定长度的字符列是按固定长度的格式存的。不会从 VARCHAR 列里截掉后面的空格。大于或者等于 768 字节的固定长度列会被编成可变长度列，能在页外面存。比如说，如果字符集的最大字节长度超过 3，像用 utf8mb4 时，CHAR (255) 列就可能超过 768 个字节。SQL 的 NULL 值在记录目录里会留一或者两个字节。要是 SQL 的 NULL 值在可变长度列里存，那在记录的数据部分就不占空间。对于定长列，这个列的定长就在记录的数据部分留着。给 NULL 值留固定空间能让列从 NULL 直接在原地更新成非 NULL 值，不会让索引页变得零碎。

3. COMPACT 行格式

把记录分成变长字段长度列表、NULL 值列表、记录头信息和列值这四部分。对变长字段倒着存长度，按字符集和实际长度来定存的字节数，用 NULL 值列表省空间，记录头信息里有好几个字段用来管记录。

跟 REDUNDANT 行格式比，COMPACT 行格式能少大概 20% 的行存储空间，不过代价是有的操作 CPU 使用率会变高。要是工作负载是那种一般被缓存命中率和磁盘速度限制的，那 COMPACT 格式可能会更快。要是工作负载被 CPU 速度限制，紧凑格式可能就更慢。简单说就是 compact 对那种 IO 密集型的更有优势，对计算密集型任务就更差。

每个索引记录都有一个 5 字节的标头，这标头前面可能还有个可变长度的标头。标头能把连着的记录连起来，还能用来做行级锁定。记录头的可变长度部分有个位向量，能指出 NULL 列。要是索引里能为 NULL 的列数是 N，那这个位向量就占 CEILING (N/8) 字节。（比如说，要是有 9 到 16 列能为 NULL，那位向量就用两个字节。）为 NULL 的列除了这个向量里的位，就不占别的空间。标头的可变长度部分还有可变长度列的长度。每个长度要 1 个或者 2 个字节，看列的最大长度。要是索引里所有列都不是 NULL 而且是固定长度，那记录头就没有可变长度部分。对于每个非 NULL 可变长度字段，记录头里有 1 个或者 2 个字节的列长度。只有部分列在溢出页里存或者最大长度超过 255 字节而且实际长度超过 127 字节的时候，才需要 2 个字节。对于在外面存的列，2 字节长度表示里面存的部分的长度加上指向外面存的部分的 20 字节指针。里面的部分是 768 字节，所以长度就是 768 + 20。20 字节的指针存列的真实长度。记录头后面就是非 NULL 列的数据内容。聚集索引里的记录有所有用户定义列的字段。另外，还有一个 6 字节的事务 ID 字段和一个 7 字节的滚动指针字段。要是没给表定主键，那每个聚集索引记录还会有一个 6 字节的行 ID 字段。每个二级索引记录都有为主键定义的、不在二级索引里的所有主键列。要是有任何主键列是可变长度的，那每个二级索引的记录头都有个可变长度部分来记它们的长度，就算二级索引是在固定长度的列上定的。

四、MySQL 行格式的选择

得按照不同的情况来选合适的行格式。

1. 要处理好多可变长度列数据的情况

选 compact 行格式就行，能把可变长度列的数据存好、管好。

处理好多可变长度列数据的时候，比如说有包含 varchar、varbinary、blob 和 text 这些类型的数据，MySQL 的 compact 行格式就挺厉害。这种行格式不光存字段值，还会记记录头信息、记录变长字段长度列表，还能用空值列表存 null 值。对于可变长度列，在真实数据那地方就只存这个列的前 768 字节的数据，多出来的就分散存在别的几个页里，然后在真实数据那地方用 20 个字节存指向溢出页的地址，这样就能找到剩下数据在哪个页了。这种设计让 compact 行格式能好好处理好多可变长度列数据，存东西的效率高了，查数据的性能也好了。跟 redundant 行格式比，compact 行格式能少大概 20% 的行存储空间，不过代价是有的操作 CPU 使用率会变高。要是工作负载是那种一般被缓存命中率和磁盘速度限制的，那 compact 格式可能会更快。要是工作负载被 CPU 速度限制，紧凑格式可能就更慢。简单说就是 compact 对那种 IO 密集型的更有优势，对计算密集型任务就更差。

2. 看重存东西的效率和查数据性能的情况

dynamic 行格式是 compact 的改进版，能少些行里额外的花费，存东西的效率和查数据的性能都高了。

dynamic 行格式是 MySQL 7.5 版本弄出来的，是 compact 格式的改进版，它的结构跟 compact 格式差不多。它跟 compact 格式不一样的地方主要在处理行溢出这块，在真实数据那地方不再额外记一部分数据了，而是用 20 个字节存指向溢出页的地址，所有数据都在溢出页里。这种设计少了行里额外的花费，存东西的效率和查数据的性能都高了。dynamic 行格式特别适合那种有好多可变长度列的表，像有 TEXT、BLOB 这种大字段的表。

3. 对空间要求高但对性能要求不严格的情况

compressed 行格式在 dynamic 基础上做压缩处理，不过性能不太好，一般业务里不推荐用。

compressed 行格式是 MySQL 5.1 里 InnoDB 的新特点之一，它在 dynamic 的基础上做压缩处理，特别是对溢出页的压缩处理。查数据的时候，会自动把数据解压了再返回。不过 compressed 格式其实就是拿时间换空间，性能不太好，一般的业务里不推荐用。这种行格式适合那种对空间要求高但对性能要求不严格的情况，比如说要存好多数据但对查数据的性能要求不高的时候。