一篇文章带你了解数据库存储引擎

查看MySQL提供什么存储引擎：

show engines; show engines \G;

我们也可以查看MySQL中默认的存储引擎：

show variables like '%storage_engine%'; SELECT @@default_storage_engine;

通过上面我可以看到MySQL默认的存储引擎是InnoDB。我们也可以创建表来验证一下效果：

create table emp1(id int); show create table emp1;

当然我们也可以去修改存储引擎：

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

default-storage-engine=MyISAM

CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;

ALTER TABLE 表名 ENGINE = 存储引擎名称;

• MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
• InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
• InnoDB支持外键。
• 除了增加和查询外，还需要更新、删除操作，那么应优先选择InnoDB存储引擎。
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
• 数据文件结构：表名.frm存储表结构（MySQL8.0时，合并在表名.ibd中）表名.ibd存储数据和索引
• 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如：.frm、.par、.trn、.isl、.db.opt等都在 MySQL 8.0 中不存在了。
• 对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
• MyISAM 只缓存索引，不缓存真实数据；InnoDB 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

InnoDB 支持事务的特性演示

首先创建数据表：

create table goods_innodb( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=innodb DEFAULT CHARSET=utf8;

在窗口1开启事务并插入数据：

start transaction; -- 开启事务 insert into goods_innodb(id,name) values(null,'Meta20'); -- 插入数据

不提交窗口1的事务。查看窗口2，发现没有数据新增。但当 commit 窗口1的事务后，窗口2就能查询到窗口1提交的数据。

InnoDB 支持外键的演示

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

父表country_innodb，country_id为主键索引；子表city_innodb表，country_id字段为外键，对应于country_innodb表的主键country_id。

create table country_innodb( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk.city.country` FOREIGN KEY (country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into country_innodb values(null,'China'),(null,'America'), (null,'Japan'); insert into city_innodb values(null,'Xian',1),(null,'New York',2), (null,'Beijing',1);

当我们删除主表的数据，发现无法删除，这就是外键起作用了。

3.2 MyISAM 引擎：主要的非事务处理存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
• 5.5之前默认的存储引擎。
• 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用。
• 针对数据统计有额外的常数存储，故而count(*)的查询效率很高。
• 数据文件结构：表名.frm存储表结构表名.MYD存储数据 (MYData)表名.MYI存储索引 (MYIndex)
• 应用场景：只读应用或者以读为主的业务。

演示 MyISAM 存储引擎不支持事务

创建一张数据表：

create table goods_myisam( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=myisam DEFAULT CHARSET=utf8;

打开窗口1、窗口2并登录到数据库。在窗口1开启事务，插入数据，先不提交，查看窗口2的数据。通过测试发现，在MyISAM存储引擎中，是没有事务控制的。

3.3 InnoDB 和 MyISAM 的对比

4 MySQL 其他的存储引擎介绍

4.1 Archive 引擎

• Archive 是归档的意思，仅支持插入和查询两种功能。在MySQL5.5以后支持索引功能。它的特点是拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时压缩，经常被用来当做仓库使用。
• 创建 Archive 表时，存储引擎会创建名称以表名开头后缀为.archive的文件。
• 根据测试结果来看，同样数据量下，Archive表会比MyISAM表要小约75%，比支持事务处理的InnoDB表要小83%。
• Archive 表存储引擎采用了行级锁，该引擎也支持列的AUTO_INCREMENT属性，列AUTO_INCREMENT可以具有唯一索引或者非唯一索引。
• Archive 表适合支持日志或者数据采集(档案)类应用。适合存储大量的独立的作为历史记录的数据，拥有很高的数据插入速度。但是对查询性能支持较差。

Archive 表的详细特性：

4.2 Blackhole 引擎

特点：丢弃写操作，读操作会返回空内容。

Blackhole 没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。单服务器会记录Blackhole 表的日志，所以可以用于复制数据到备份数据库，或者简单做日志记录，但这种应用方式会碰到很多问题，一般不推荐使用。

4.3 CSV 引擎

特点：存储数据时，以逗号分隔各个数据项。

• CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
• CSV引擎可以是一种数据交换机制，非常实用。
• CSV引擎存储的数据可以直接在操作系统里面使用文本编辑器打开，或者使用Excel文件打开。
• 对于数据的快速导入非常有优势。

具体使用：

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)ENGINE = CSV; INSERT INTO test VALUES(1,'record one'),(2,'record two'); SELECT * FROM test;

如果检查test.CSV(在数据库存储数据可用SHOW VARIABLES LIKE 'datadir';)文件，其内容使用文本编辑器打开，这种格式可以被 Microsoft Excel 等电子表格应用程序读取，甚至写入。

4.4 Memory 引擎

特点：置于内存的表。

概述：Memory采用的逻辑介质是内存，响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

特点：

• Memory同时支持哈希(HASH)索引和B+树索引。
• Memory表至少比MyISAM表要快一个数量级。
• MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
• 数据文件与索引文件分开存储。
• 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

使用Memory存储引擎的场景：

1. 1.目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
2. 2.如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
3. 3.存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

4.5 Federated 引擎

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

4.6 Merge 引擎

管理多个MyISAM表构成的表集合。

4.7 NDB 引擎

MySQL集群专用存储引擎。也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。MySQL中同一个数据库，不同的表可以选择不同的存储引擎。

常用存储引擎对比总表

其实这些东西大家没必要立即就给记住，列出来的目的就是想让大家明白不同的存储引擎支持不同的功能。其实我们最常用的就是InnoDB和MyISAM，有时会提一下Memory。其中InnoDB是MySQL默认的存储引擎。