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 的表来处理，但是这种表不支持索引。
