【MySQL】存储引擎

发布于 2020-08-03

什么是存储引擎

存储引擎，是数据库存储数据、建立索引、更新数据和查询数据等功能的具体实现。

存储引擎是基于表的，即不同的表可以使用不同的存储引擎。

在MySQL中，若想查看数据库支持的存储引擎，语句为：

1

show engines;

若想查看存储引擎相关配置，语句为：

1

show variables like "%engine%";

`Innodb`

1. 表空间文件模式

InnoDB包含两种表空间文件模式：共享表空间和独立表空间。

共享表空间，是指所有数据保存在一个表空间里面，而这个表空间可以由多个文件组成，一个表可以跨多个文件。该模式下，使用drop table命令删除表时，会删除整个文件。

独立表空间，是指每张表存放在独立的表空间，每个表空间都有多个数据文件。该模式下，使用drop talbe删除表时不会回收空间。

可以通过参数innodb_file_per_table查看表空间的模式，参数为0表示为共享表空间，参数为1表示为独立表空间。

MySQL5.6.6开始，默认使用的是独立表空间，这样便于管理表数据。

2. 文件存储形式

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

使用独立表空间时，每张表都有独立的表空间。表结构存放在.frm文件中，数据和索引存放在.ibd中，每个文件的名字就是表的名字。

注意，MySQL8.0之后，不再使用.frm文件，表结构和数据存放在一起。

删除数据

在独立表空间中，使用delete删除数据时，会造成空洞。

因为删除记录时，InnoDB引擎只会把该记录标记为删除，但磁盘空间不会被回收。等到插入数据时，可以复用该位置。

如果整个数据页的数据被删除，那么该数据页就可以被复用。

如果相邻数据页的利用率很小，系统会将它们合并到一个数据页上，此时另一个数据页也能被复用。

对记录的位置进行复用时，要求插入的数据符合该位置的范围条件，对数据页的复用则没有该限制。

插入数据

在独立表空间中，插入数据时，也可能造成空洞。

如果数据按照索引递增的顺序插入，索引的数据会很紧凑，不会造成分裂。

如果数据是随机插入的，则可能会发生索引数据页的分裂，从而导致空洞。

重建表

在独立表空间中，为了消除空洞，可以对数据表进行重建。重建命令为：

1

alter table 表名 engine=InnoDB;

MySQL5.6之前，重建命令的执行流程为：

server层创建临时表B，B和A的表结构一致。
按主键递增的顺序，将A的数据复制到B。
用B替换A。

流程执行过程中，A不能有更新，即该流程不是Online的。

MySQL5.6开始，重建命令的执行流程为：

InnoDB引擎新建临时文件。
将A的数据页存储到临时文件中。
生成临时文件的过程中，将对A的操作记录到row log。
将row log中的操作应用到临时文件。
用临时文件替换表A的数据文件。

该流程是Online的。alter语句在启动时，会自动获取MDL写锁，然后很快会变成MDL读锁。

重建表还可以使用如下命令：

1

optimize tale 表名;

该命令等同于alter table...和analyze table A一起执行。analyze用于对表的索引信息做重新统计。

`MyISAM`

1. 文件存储形式

对于不同的表，MyISAM会在磁盘上生成三类文件。每个文件的文件名和表名相同，但扩展名分别如下：

.frm：存储表定义，MySQL8.0版本开始，不再生成该类文件。
.MYD：MYDATA，存储数据。
.MYI：MYIndex，存储索引。

`Memory`

Memory存储引擎有如下特性：

每个Memory表对应一个.frm磁盘文件，该文件只存储表结构。
Memory表的数据都是存放在内存中的，因此数据的处理速度非常快。
默认使用HASH索引。
MySQL服务一旦关闭，数据就会丢失。

引擎选择原则

1. `InnoDB`

InnoDB是MySQL的默认存储引擎，支持事务和外健。如果数据的更新、删除操作较多，或者在并发条件下要求数据一致性，或者要求确保事务的完整提交和回滚，InnoDB是比较合适的选择。

2. `MyISAM`

如果读数据和插入数据的操作较多，但是更新和删除数据的操作较少，且对事务以及并发性无要求。则可以选择MyISAM。

3. `Memory`

如果数据量不大，并且数据可以恢复，或者可以容忍数据丢失，则可以使用Memory。

MyISAM与InnoDB的比较

MyISAM不支持主外键；InnoDB支持主外键。
MyISAM不支持事务；InnoDB支持事务。
MyISAM使用表锁，即使操作一条记录也会锁住整个表，不适合高并发操作；InnoDB使用行锁，操作时只锁一行，不对其他行有影响，适合高并发的操作。
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还缓存真实数据，对内存要求较高，并且内存大小对性能有决定性影响。
MyISAM表空间小；InnoDB表空间大。

数据库引擎相关操作

1. 查看支持的引擎

1

SHOW engines;

2. 查看默认使用的引擎

1

SHOW VARIABLES LIKE "default_storage_engin";

3. 手动指定引擎

1
2
3


CREATE TABLE tb(
    ...
)engine=InnoDB DEFAULT charset=utf8mb4;

目录