搞定PHP面试-MySQL基础知识点整理-存储引擎

MySQL 基础知识点整理 – 存储引擎

0. 查看 MySQL 支持的存储引擎

可以在 mysql 客户端中，使用 show engines; 命令可以查看 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.06 sec)

1. InnoDB 引擎

InnoDB 是 MySQL 默认的存储引擎，也是最重要、使用最广泛的存储引擎。
InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

InnoDB 的特性

• 支持事务。
• 支持外键约束。
• 支持行级锁机制。
• 支持崩溃后的安全恢复。
• 通过一些机制和工具支持真正的热备份。
• 会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引，因此 InnoDB 的表需要更多的内存和存储空间。
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
• InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。

InnoDB 存储表和索引有两种方式

使用共享表空间存储： 表结构保存在 .frm 文件中，数据和索引在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
使用多表空间存储： 表结构保存在 .frm 文件中，每个表的数据和索引单独保存在 .ibd 中。

InnoDB 的使用场景

更新密集的表： InnoDB 存储引擎特别适合处理多重并发的更新请求。
事务： InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
自动灾难恢复： InnoDB 表能够自动从灾难中恢复。
外键约束： MySQL 支持外键的存储引擎只有 InnoDB。
支持自动增加列 AUTO_INCREMENT 属性。

2. MyISAM 引擎

MyISAM 是 MySQL 5.1 及之前版本的默认存储引擎。MySQL 8 即将废弃 MyISAM 引擎。
MyISAM 提供了全文索引、压缩、空间函数（GIS）等特性。
MyISAM 不支持事务和行级锁，崩溃后无法安全恢复。

MyISAM 的特性

• 支持全文索引。
• 支持表级锁，不支持行级锁。
• 不支持事务。
• 拥有较高的插入，查询速度。
• MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• MyISAM 用一个变量保存了整个表的行数，执行 select count(*) from table 时只需要读出该变量即可，速度很快。

MyISAM 的三种存储格式

MyISAM 的三种存储格式分别为静态、动态和压缩。MyISAM 会根据表的定义自动选择存储格式。

静态表： 如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
动态表： 如果数据表中出现 varchar、*text 或 *BLOB 字段时，服务器将自动选择这种表类型。相对于静态 MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用 optimize table 命令或优化工具来进行碎片整理。
压缩表： 以上说到的两种类型的表都可以用 myisamchk 工具压缩。如果表在创建并导入数据后，不在进行修改操作，这样的表适合采用 MyISAM 压缩表。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

MyISAM 表存储

每个 MyISAM 表在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。
.frm 文件存储表结构定义。
.MYD (MYData) 文件存储表的数据。
.MYI (MYIndex) 文件存储表的索引。

MyISAM 使用场景

查询密集型表： MyISAM 存储引擎在筛选大量数据时非常快，是它最突出的优点；
插入密集型表： MyISAM 的并发插入特性允许同时选择、插入数据。例如：MyISAM 存储引擎非常适合管理邮件或 Web 服务器日志数据。

3. MEMORY 引擎

MEMORY 引擎（也叫 HEAP 引擎），将所有数据都保存在内存中，不需要进行磁盘 I /O。因此，MEMORY 表至少比 MyISAM 表快一个数量级。
MySQL 重启或 mysqld 守护进程崩溃时，Memory 表的结构还会保留，但是所有的数据都会丢失。

获得速度的同时也带来一些缺陷。它要求存储在 Memory 数据表里的数据用的是长度不变的格式，这意味着不能用 BLOB 和 TEXT 这样的长度可变的数据类型，VARCHAR 是种长度可变的类型，但因为它在 MySQL 内部当做长度固定不变的 CHAR 类型，所以可以使用。

MEMORY 的特性

• 数据都保存在内存中，不需要进行磁盘 I /O。
• 支持 Hash 索引和 B 树索引。
• 支持表级锁，不支持行级锁。
• 服务重启后，表结构还会保留，但是数据会丢失。
• 不支持 TEXT 和 BLOB 类型的列。

MEMORY 使用场景

• 需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系。
• 用于查找（lookup）或者映射（mapping）表，例如将邮编和地址映射的表。
• 用于保存数据分析中产生的中间数据。
• 用于缓存周期性聚合数据的结果。

4. ARCHIVE 引擎

ARCHIVE 引擎是一个针对插入和压缩做了优化的简单存储引擎。
ARCHIVE 引擎只支持 INSERT 和 SELECT 操作。MySQL 5.1 之前也不支持索引。
ARCHIVE 引擎会利用 zlib 对插入的行进行压缩，所以比 MyISAM 表的磁盘 I/O 更少。

ARCHIVE 的特性

• 只支持 INSERT 和 SELECT 操作。
• MySQL 5.1 之前不支持索引。
• 支持行级锁和专用缓冲区。

ARCHIVE 使用场景

• 适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。
• 需要快速 INSERT 操作的场景。

5. MERGE 引擎

MERGE 存储引擎是 MyISAM 引擎的变种。MERGE 表是由多个 MyISAM 表合并而来的虚拟表，这些 MyISAM 表结构必须完全相同。Merge 表中并没有数据，对 Merge 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行操作。

Merge 存储引擎使用场景对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定时间端相关。例如：可以用 12 个相同的表来存储服务器的日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有 12 个日志表的数据来生成报表时，这意味着需要编写、更新多表查询，以反映这些表中的信息。与其编写这些可能会出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除 Merge 表而不影响原来的数据，删除 Merge 表只会删除掉 Merge 表的定义，对内部表没有任何影响。

但是引入分区功能后，该引擎已经被放弃。

6. CSV 引擎

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

CSV 的特点

• 可以将普通的 CSV 文件作为 MySQL 的表来处理。
• 所有的列必须都是不能为 NULL 的
• 不支持索引（不适合大表，不适合在线处理）
• 可以对数据文件直接编辑（保存文本文件内容）

CSV 使用场景

• 适合做为数据交换的中间表（能够在服务器运行的时候，拷贝和拷出文件，可以将电子表格存储为 CSV 文件再拷贝到 MySQL 数据目录下，就能够在数据库中打开和使用。同样，如果将数据写入到 CSV 文件数据表中，其它 web 程序也可以迅速读取到数据。