MySQL

[MySQL 之三] 存储引擎

2021-07-17  本文已影响0人  小胡_鸭

一、查看与设置

  旧版本的 MySQL 可使用以下命令查询默认存储引擎：

show variables like 'table_type';

  新版本的 MySQL 使用以下命令查询：

show variables like '%storage_engine%';

  可以通过以下命令查询系统中支持的所有引擎及默认的引擎：

show engines \G

  定义表结构时，如果不指定存储引擎则按照默认引擎设置，也可以显式指定


二、各种存储引擎的特性

特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 有 64TB 有 没有 有
事务安全 支持
锁机制 表锁 行锁 表锁 表锁 表锁
B 树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 低 高 N/A 低 低
内存使用 低 高 中等 低 高
批量插入的速度 高 低 高 高 高
支持外键 支持

1、MyISAM

  MySQL 5.5.5 之前的默认存储引擎，不支持事务和外键，但访问数据块的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用 MyISAM。

（1）文件组成

  使用 MyISAM 的表在磁盘上存储为三个文件，文件名都与表名相同，扩展名分别是：

（2）表损坏与修复

  MyISAM 不具备 crash-safe 能力，所以在以下场景中有可能导致表损坏：

  损坏后的表不能被访问，可以通过以下命令检查表的健康：

check table tablename;

  如果一个表被损坏了，可以通过以下命令修复：

repair table tablename;

  为了减少表损坏的概率，减少文件碎片，在进行大量的更新删除操作之后，可以使用以下命令来优化表：

optimize table tablename;

（3）存储格式

  MyISAM 的表支持三种不同的存储格式，分别为：

静态表是默认的存储格式。表中字段都是非变长字段，每条记录占用的空间大小是固定。

【优点】存储迅速，容易缓存，出现故障容易恢复。
【缺点】占用空间比动态表多。
【实例演示】

  如下面的静态表中，每个字段的长度都是固定的，因此创建出来的表，每条记录占用的存储空间都是 53 个字节。

动态表中包含了变长字段，实际用到多少空间就分配多少，每条记录占用的空间大小是动态的。

【优点】占用的空间相对较少。
【缺点】频繁地更新和删除记录会产生碎片，需要定期执行 optimize table 或 myisamchk-r 命令来改善性能，并且在出现故障时恢复比较困难。

压缩表由 myisampack 工具创建，占据非常小的磁盘空间，适用于基本都是查询的场景。

2、InnoDB

  InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，且支持外键，锁的粒度是行锁，能够获得更高的并发度，但跟 MyISAM 相比需要占用更多的磁盘空间，为了保证事务安全和 crash-safe 能力，插入数据时需要做更多的操作，因此写处理效率低一些。

（1）自增列

  InnoDB 的自增列可以手动插入，但如果是空或者 0 则按照自增值来，若手工插入了合法值，则下一个自增值为该值 +1。

  InnoDB 自动增长列必须是索引，如果是组合索引，自增列也必须是索引中的第一列。

（2）外键约束

  InnoDB 是 MySQL 中唯一支持外键的索引。假设 A 是主表，主键为 a，B 是子表，主键为 b，则 B 中必须有一列 a 作为外键，对应 A 的主键。

  在声明外键时还可以施加约束：

  插入数据时，因为外键的存在和约束，会多做一些检查，也需要更多的存储空间，所以大批量导入数据时，会降低数据导入速度，因此可以通过暂时关闭外键约束来加快处理速度，命令为：

// 查看开关
select @@foreign_key_check;

// 禁用
set foreign_key_check=0;

// 导入完成后，重启约束
set foreign_key_check=1;

（3）存储方式

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

根据参数 innodb_file_per_table 参数的设置，决定使用共享表空间存储还是多表空间存储，默认是打开的，即默认使用的是多表空间存储。如果修改了该参数，则需要重启 MySQL 才能生效，并且原来的表的存储方式不变，新建的表才会按照新的设置进行表空间存储。

使用多表空间特性的表，可以方便地进行单表备份和恢复。

3、MEMORY

（1）内存表的特性

  MEMORY 引擎的数据都在内存中，因此访问速度非常快，每个内存表都只有一个磁盘文件 .frm，但是一旦服务关闭，表中的数据就会丢失掉。

（2）内存表的自动初始化

  如果想在服务启动时，自动加载一些数据到内存表中，则可以使用 --init-file 选项，或者在配置文件中添加 init-file 配置项，步骤如下：

a. 编写一个文件，将要在服务启动时自动执行语句放入其中

b. 在服务初始化配置中加上 init-file 选项


c. 重启服务器

（3）索引

  MEMORY 是 MySQL 唯一一个支持哈希索引的引擎，默认也是哈希索引，当然也可以显式指定使用 HASH 索引还是 BTREE 索引。

（4）大小限制

  定义内存表时，可通过 max_rows 子句指定表的最大行数，若不指定，则默认最大数据量限制为 16M，由 max_heap_table_size 系统变量设定。

（5）使用场景

  用于内容变化不频繁地代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。更新操作时要考虑到服务重启后丢失的问题，做好数据备份。

4、MERGE

  MERGE 引擎是对一组 MyISAM 表的操作，这些表必须满足以下条件：

（1）定义

  MERGE 表不存储数据，对表执行增删查改实际上是对内部的 MyISAM 表进行的操作，定义 MERGE 表的语法如下：

create table merge_tablename (
    ...
)engine=merge union=(myisam_tab1, myisam_tab2, ..., myisam_tabn) insert_method={first|last};

  其中 union 定义了内部都有哪些 MyISAM 表，insert_method 则指定了插入数据时要插入到哪个表中取。

  查询数据时，可以把 MERGE 表当成类似视图一样使用：

  插入数据时，按照表定义决定插入到内部的第一个还是最后一个 MyISAM 表

（2）文件组成


三、存储引擎的组合

上一篇下一篇

猜你喜欢

热点阅读