存储引擎

✏️ 1、存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体性能。

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

🖋️ 1.1、定义

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

🖋️ 1.2、作用

  • 设计并创建数据库以保存系统所需的关系或XML文档。

  • 实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。

  • 为单位或客户部署实现的系统。

  • 提供日常管理支持以优化数据库的性能。

🖋️ 1.3、分类

MySQL存储引擎主要有两大类：

  1. 事务安全表：InnoDB、BDB。

  2. 非事务安全表：MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。

在MySQL 5.5版本之前，MyISAM是MySQL的默认存储引擎，从MySQL 5.5版本之后，MySQL的默认内置存储引擎是InnoDB。配置文件my.ini(windows)或my.cnf(linux)中设置默认存储引擎的参数：default-table-type或default-storage-engine。设置后重启服务，数据库默认的引擎修改生效。也可以在启动数据库服务器时在命令行后面加上--default-storage-engine或--default-table-type选项。

# 1、查询当前数据库支持的存储引擎：
show engines;
show variables like 'have%';
# 2、查看当前的默认存储引擎：
show variables like '%table_type%';
# 3、在建表的时候指定：
create table mytbl(   
    id int primary key,   
    name varchar(50)   
)type=MyISAM; # 或 ENGINE=MyISAM
-- 4、建表后更改：
alter table table_name type = InnoDB;
-- 5、查看修改成功：
show table status from table_name;

✏️ 2、常用存储引擎

🖋️ 2.1、InnoDB

InnoDB是一个事务型的存储引擎，Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选，由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

🐹 2.1.1、存储方式

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

  1. 使用共享表空间存储：这种方式创建的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。

  2. 使用多表空间存储：这种方式创建的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.idb文件中。如果是个分区表，则每个分区对应单独的.idb文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

要使用多表空间的存储方式，需要设置参数innodb_file_per_table并重启服务器后才可以生效，而且只对新建的表生效。多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。即使在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和工作日志放在这个文件中，所以备份使用多表空间特性的表时直接复制.idb文件是不行的，可以通过命令将数据备份恢复到数据库中：

ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;

但是这样只能恢复到表原来所在数据库中，如果需要恢复到其他数据库则需要通过mysqldump和mysqlimport来实现。

🐹 2.1.2、数据文件

InnoDB的数据文件由表的存储方式决定：

  1. 共享表空间文件：由参数innodb_data_home_dir和innodb_data_file_path定义，用于存放数据词典和日志等。

  2. .frm：存放表结构定义。

  3. .idb：使用多表空间存储方式时，用于存放表数据和索引，若使用共享表空间存储则无此文件。

🐹 2.1.3、外键约束

InnoDB是MySQL唯一支持外键约束的引擎。外键约束可以让数据库自己通过外键保证数据的完整性和一致性，但是引入外键会使速度和性能下降。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

外键约束使用示例：

CREATE TABLE `dep` (
    `id` smallint(6) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    `dep_id` smallint(6) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_fk_dep_id` (`dep_id`),
    CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

KEY ：定义索引约束名称。

CONSTRAINT：定义外键约束名称。（在数据库中应是唯一的，若不指定系统会自动生成一个约束名）

ON：指定父表操作对子表的影响（不定义默认采用restrict）。

Restrict和no action：在子表有相关记录的情况下父表不能更新或删除。

Cascade：在父表更新或删除时，同时更新或删除子表对应的记录。

Set null：在父表更新或删除的时候，子表的对应字段被设置为null。

当某个表被其他表创建了外键参照，那么这个表的对应索引或者主键禁止被删除。在导入多个表的数据时，如果需要忽略表的导入顺序，可以暂时关闭外键的检查；在执行load data和alter table操作的时候，也可以通过暂时关闭外键约束来加快处理的速度。

set foreign_key_checks=0;  # 0为关闭，1为打开

🐹 2.1.4、适用场景

  1. 经常更新的表，适合处理多重并发的更新请求。

  2. 支持事务。

  3. 可以从灾难中恢复（通过bin-log日志等）。

  4. 外键约束。只有他支持外键。

  5. 支持自动增加列属性auto_increment。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

🐹 2.1.5、MySQL官方对InnoDB的讲解

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。

  2. InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。

  3. InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

  4. InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

  5. InnoDB被用来在众多需要高性能的大型数据库站点上产生。

🖋️ 2.2、MyISAM

🐹 2.2.1、存储方式

  1. 静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

  2. 动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

  3. 压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）。静态表和动态表是根据正使用的列的类型自动选择的。

🐹 2.2.2、数据文件

MyISAM数据表在磁盘存储成3个文件，其文件名都和表名相同，扩展名分别是：

  1. .frm：存储数据表结构定义。

  2. .MYD：存储表数据。

  3. .MYI：存储表索引。

其中，数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。指定索引文件和数据文件的路径，需要在创建表的时候通过data directory和index directory语句指定。（文件路径需要是绝对路径并且具有访问的权限）

MyISAM类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。可以使用check table语句来检查MyISAM表的健康，并用repair table语句修复已经损坏的MyISAM表。

🐹 2.1.3、适用场景

  • 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyISAM存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。

  • 不支持外键的表设计。

  • 查询速度很快，如果数据库insert和update的操作比较多的话比较适用。

  • 对表经常进行加锁的场景。

  • MyISAM极度强调快速读取操作。

  • MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。

🖋️ 2.3、MEMORY

🐹 2.3.1、数据文件

每个MEMORY表只对应一个.frm磁盘文件，用于存储表的结构定义，表数据存放在内存中。默认使用HASH索引，而不是BTREE索引。和Redis，memcached等思想类似，为了提高数据的访问速度。

🐹 2.3.2、特点

  1. 支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；

  2. 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

  3. 由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；

  4. 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低。

🐹 2.3.3、适用场景

MEMORY存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

🖋️ 2.4、MERGE

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。通过insert_method子句定义merge表的插入操作：使用first或last可以使插入操作被相应地作用在第一或最后一个表上，不定义或定义为No表示不能对这个merge表进行插入操作。对merge表进行drop操作只是删除了merge的定义，对内部的表没有任何影响。

🐹 2.4.1、数据文件

  1. .frm：存储表定义。

  2. .MRG：存储组合表的信息，包括merge表由哪些表组成、插入新数据时的依据。可以通过修改.mrg文件来修改merge表，但是修改后要通过flush tables刷新。

🐹 2.4.2、使用示例

CREATE TABLE `m1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `m2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `m` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

🐹 2.4.3、适用场景

用于将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

✏️ 3、其他引擎

🖋️ 3.1、ARCHIVE

ARCHIVE存储引擎适合的场景有限，由于其支持压缩，故主要是用来做日志，流水等数据的归档，主要特点：

  1. 支持Zlib压缩，数据在插入表之前，会先被压缩；

  2. 仅支持SELECT和INSERT操作，存入的数据就只能查询，不能做修改和删除；

  3. 只支持自增键上的索引，不支持其他索引。

✏️ 4、选择存储引擎

要选择合适的存储引擎时，首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。

🖋️ 4.1、字段和数据类型

虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG(二进制大对象)或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。

这些局限性可能直接影响到你可以存储的数据，同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。

🖋️ 4.2、锁定

数据库引擎中的锁定功能决定了如何管理信息的访问和更新。当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修改这个数据(在某些情况下还不允许读这种数据)。

锁定不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查询。这是因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同一个数据。由于向数据中插入信息和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。

不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持的级别有三种：表锁定、块锁定和行锁定。支持最多的是表锁定，这种锁定是在MyISAM中提供的。在数据更新时，它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。

页级锁定使用Berkeley DB引擎，并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。

行级锁定提供了最佳的并行访问功能，一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不会引起锁定的问题。只有InnoDB存储引擎支持行级锁定。

🖋️ 4.3、建立索引

建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你存储的数据类型。

有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)。

🖋️ 4.4、事务处理

事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的，它允许你更新表中的数据，但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改。例如，在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改，你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成，才接受所做的修改。如果任一项操作失败了，你都可以取消这个事务处理，这些修改就不存在了。如果这个事务处理过程完成了，我们可以通过允许这个修改来确认这个操作。

Previous事务隔离NextMyISAM与InnoDB

Last updated