一、MySQL存储引用概述

​ 和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

​ 存储引擎就是存储数据、建立索引，更新阐述数据等等技术的实现方式。MySQL存储引擎是基于表的而不是基于数据库的，即在一个数据库中的不同表可以使用不同的存储引擎。

​ Oracle、SqlServer等数据库只支持一种存储引擎。MySQL提供了插件式的引擎结构。所以MySQL有很多存储引擎，并且可以根据需求使用不同的存储引擎，甚至可以自定义编写存储引擎。

​ MySQL5.0支持的存储引擎包括：InnnoDB、MyISAM、MEMORY、BDB、MERGE、EXAMPLR、NDB CLUSTER、ARCHIVE、CSV等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

在mysql命令行，可以使用show engines;命令查看当前数据库支持的存储引擎，如下图所示：

从表中可以看到，我当前使用的数据库版本支持：MEMORY、InnoDB、PERFORMANCE SCHEMA、MyIsAM、MRG_MYISAM、BLACKHOLE、CSV、以及ARCHIVE，其中InnoDB是默认引擎，也即在创建数据表的时候如果没有指定存储引擎，就会默认使用InnoDB作为存储引擎。

当然我们还可以使用命令查看当前MySQL数据库默认的存储引擎：

mysql> show variables like '%storage_engine%';

二、各种存储引擎的特性

下面重点介绍几种常用的存储引擎，并比较各个存储引擎之间的区别，如下表所示

对于我们来说，我们需要种地那关注的存储引擎特性是是否支持事物、锁机制以及是否支持外键。接下来我们详细的聊一下InnoDB、MyISAM，以及MEMORY这三种存储引擎的特性。

1、InnoDB存储引擎的特性

InnoDB是一种通用存储引擎，可兼顾高可靠性和高性能。 在MySQL 5.7中，InnoDB是默认的MySQL存储引擎。 除非你配置了其他存储引擎，否则发出不带ENGINE =子句的CREATE TABLE语句将创建一个InnoDB表。

InnoDB的优势

• （1）支持事物：其DML操作遵循ACID模型，并具有具有提交，回滚和崩溃恢复功能的事务，以保护用户数据。
• （2）支持行级锁，可以支持更大的并发：行级锁定可提高多用户并发性和性能。
• （3）InnoDB表将数据存储在磁盘上，以基于主键优化查询。每个InnoDB表都有一个称为聚集索引的主键索引，该索引组织数据以最小化主键查找的I / O。
• （4）支持外键，保证数据完整性：为了保持数据完整性，InnoDB支持FOREIGN KEY约束。使用外键检查插入，更新和删除，以确保它们不会导致不同表之间的不一致。

InnoDB和ACID模型

ACID模型是一组数据库设计原则，强调对业务数据和任务关键型应用程序非常重要的可靠性方面。MySQL包含了与ACID模型紧密结合的InnoDB存储引擎等组件，这样数据就不会被破坏，结果也不会被软件崩溃和硬件故障等异常情况所扭曲。当依赖于符合ACID的特性时，不需要重新发明一致性检查和崩溃恢复机制。如果有额外的软件保护措施、超可靠的硬件或能够容忍少量数据丢失或不一致的应用程序，则可以调整MySQL设置，以牺牲部分ACID可靠性，从而获得更高的性能或吞吐量。

ACID描述的是一个事务所应该具有的4大特性，即：

• A（Atomicity，原子性）：一个事务就是一个不可在分隔的最小工作单元，事务中的操作要么全部执行，要么全部不执行。
• C（Consistency，一致性）：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• I（Isolation，隔离性）：一个事务在做做的修改提交之前，对于其他事务是不可见的。
• D （Durability，持久性）：一旦事务提交，则器所做的修改就会永久的保存到数据库中。

数据库中隔离性还有其对应的不同等级的隔离级别：

• READ UNCOMMITED 读未提交：在此级别里，事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也就是会产生脏读，在实际应用中一般很少使用。
• READ COMMITED 读以提交：大多数数据库系统的默认隔离级别都是它，但是MySQL不是。它能够避免脏读问题，但是在一个事务里对同一条数据的多次查询可能会得到不同的结果，也就是会产生不可重复读问题。
• REPEATABLE READ 可重复读： MYSQL InnoDB存储引擎的默认隔离级别。它能够防止不可重复读问题，但是在一个事务里对一段数据的多次读取可能会导致不同的结果，也就是会有幻读的问题。
• SERIALIZABLE 可串行化：该隔离级别是级别最高的，它通过锁来强制事务串行执行，避免了前面说的所有问题。在高并发下，可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，因为可重复度级别解决了所有问题。

几种隔离级别分别可以解决的问题：

InnoDB的外键约束 CASCADE、SET NULL、RESTRICT、NO ACTION

首先我们来了解一下几个外键约束属性的作用：

• CASCADE：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• SET NULL：在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)
• NO ACTION：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
• RESTRICT：同no action, 都是立即检查外键约束

外键约束属性： RESTRICT | CASCADE | SET NULL | NO ACTION 外键的使用需要满足下列的条件：

1. 两张表必须都是InnoDB表，并且它们没有临时表。
2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3. 建立外键关系的对应列必须建立了索引。
4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

InnoDB的文件存储方式

InnoDB存储表数据和索引有以下两种方式：

• 使用共享表空间存储，这种方式下表结构存储在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的.ibdata文件中，可以是多个文件。
• 使用多表空间存储，这种方式在表结构仍然存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中。

在Linux系统中，MySQL的这些文件被默认保存在/var/lib/mysql目录下了，如下图所示：

2、MyISAM存储引擎的特性

MyISAM是MySQL5.5版本之前的默认存储引擎，它不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。

• （1）不支持事务：因此不适合数据安全非常重要的场合下使用。
• （2）不支持行级锁，最小的锁粒度是表锁，但是它就有较高的插入和查询速度，比较适合查询比较多的场合下使用。
• （3）每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

MyISAM的文件存储方式

每个MyISAM表在磁盘上都会保存是3个文件，文件名和表名相同，单文件扩展名分别是：

• .frm：用于存储表结构的定义
• .MYD：用于存放数据
• .MYI：用于存放索引，仅保存记录所在页的指针，索引的结构是B+树结构。

同时，MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

（1）静态固定长度表

这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。

（2）动态可变长表

优点是节省空间，但是一旦出错恢复起来比较麻烦。

（3）压缩表

上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

3、MEMORY存储引擎的特性

MEMORY存储引擎相比前面的一些存储引擎，有点不一样，其使用存储在内从中的数据来创建表，而且所有的数据也都存储在内存中。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。

MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用BTree要快，如果希望使用B树型，则在创建的时候可以指定使用。

MEMORY存储引擎文件数据都存储在内存中，如果mysqld进程发生异常，重启或关闭机器这些数据都会消失。所以MEMORY存储引擎中的表的生命周期很短，一般只使用一次。因此MEMORY表可以用于存储临时表数据。

三、InnoDB、MyISAM和MEMORY三种存储引擎比较

• InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

• MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果