美文网首页MySQL
[MySQL 之三] 存储引擎

[MySQL 之三] 存储引擎

作者: 小胡_鸭 | 来源:发表于2021-07-17 13:23 被阅读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 的表在磁盘上存储为三个文件,文件名都与表名相同,扩展名分别是:

  • .frm:存储表定义;
  • .MYD:MYData,存储数据;
  • .MYI:MYIndex,存储索引。

(2)表损坏与修复

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

  • 服务器突然断电导致数据文件损坏;强制关机,没有先关闭 mysql 服务;mysqld 进程在写表时被杀掉;
  • 服务器宕机;
  • 磁盘损坏;
  • mysql 本身的bug

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

check table tablename;

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

repair table tablename;

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

optimize table tablename;

(3)存储格式

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

  • 静态(固定长度)表;
  • 动态表;
  • 压缩表。

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

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

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

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

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

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

2、InnoDB

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

(1)自增列

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

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

(2)外键约束

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

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

  • restrictno action 相同,只指限制在子表有关联记录的情况下父表不能更新;
  • cascade 表示父表更新和删除时,更新和删除子表对应的记录;
  • set null 表示父表在更新和删除时,子表对应的字段被 set null。

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

// 查看开关
select @@foreign_key_check;

// 禁用
set foreign_key_check=0;

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

(3)存储方式

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

  • 共享表空间存储:表结构保存在 .frm 文件中,数据和索引保存在 innodb_data_home_dirinnodb_data_file_path 定义的表空间中,可以是多个文件。

  • 使用多表空间存储:表结构保存在 .frm 文件中,每个表的数据和索引单独保存在 .ibd 文件中。如果是分区表,则对每个分区对应单独的 .ibd 文件,文件名是 "表名+分区名",可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的 IO 均匀地分布在多个磁盘上。

根据参数 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 表的操作,这些表必须满足以下条件:

  • 使用 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)文件组成

  • .frm:存储表的定义。
  • .MRG:存储组合表的组合信息。


三、存储引擎的组合

  • MyISAM:MySQL 5.5.5 之前默认的存储引擎

    【适用场景】
    ① 读操作和插入操作为主,只有很少的删除和更新;
    ② 对事务完整性、并发性要求不高

    【缺点】
    ① 不支持事务
    ② 宕机、服务器断电、磁盘损坏等情况下数据可能被损坏需要修复

  • InnoDB:MySQL 5.5.5 之后默认的存储引擎

    【使用场景】
    ① 需要事务或外键支持;
    ② 数据操作除了查询和插入外,还包含很多更新和删除;
    ③ 希望在不可预期如宕机、服务器断电、磁盘损坏等各种情况下依然鞥保证 crash-safe 能力;
    ④ 对并发要求比较高。

  • MEMORY

    【使用场景】
    ① 将所有数据保存在内容中,方便快速定位记录;
    ② 更新不频繁的小表。

    【缺点】
    对表大小有限制,不能存储大量数据。

  • MERGE

    【使用场景】
    将多个 MyISAM 表以逻辑方式组合在一起,作为一个对象使用,可以突破对单个表的大小限制,并通过不同的表分布在多个磁盘上,可以有效地概述 MERGE 表的访问效率,对于数据仓库的场景十分合适。

相关文章

网友评论

    本文标题:[MySQL 之三] 存储引擎

    本文链接:https://www.haomeiwen.com/subject/orfwpltx.html