字节流的博客

MySQL 存储引擎

1. MyISAM

MyISAM 是 MySQL 数据库默认的存储引擎。不支持事务和外键,其优势是访问速度快,对事务完整性要求不高的或者以 select、insert 为主的应用基本上都可以使用这个引擎来创建表。

MyISAM支持的3种存储格式:

  1. 静态表(固定长度)[默认]:
    • 优点:存储速度快; 方便缓存; 出现故障易回复;
    • 缺点:占用空间大;
  2. 动态表;
  3. 压缩表;
  4. 静态表长度是固定的,当长度不足时自动用空格补齐,获取数据时删掉空格。[注意]当数据中本身存在空格时,获取时也会被删掉。

2. InnoDB

InnoDB 支持事务安全,但存取效率比 MyISAM 低。
InnoDB特点有:

  • 自增长序列:必须为索引,如果是组合索引,也必须是索引的第一列;MyISAM可以为其他列。当插入 0 或 null 时按自增长计算,正常整数按指定的插入;
  • 外键约束:MySQL 中只有 InnoDB 引擎支持外键约束;在创建外键时父表必须有相应的索引,子表创建外键时系统会自动创建对应的索引
  • 在更新父表时,对子表进行的操作:RESTRICTCASCADESET NULLNO ACTION;其中:RESTRICTNO ACTION 相同,是指子表有关联数据时父表不能更新;CASCADE 是级联,指父表更新或删除记录时,子表更新或者删除相应的记录;SET NULL 表示在父表进行更新或删除时,子表相应字段被 SET NULL;
    示例代码:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE school (
    id INT,
    sch VARCHAR (10),
    PRIMARY KEY (id),
    CONSTRAINT fk_school_user FOREIGN KEY (id)
    REFERENCES USER (id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
    ) ENGINE = INNODB DEFAULT charset = utf8;
  • 在导入数据的时候,如果需要暂时关闭外键检查,可以用 mysql>SET foreign_key_checks=0; 来设置,恢复外键验证采用 mysql>SET foreign_key_checks=1; 来设置;

  • 可以用 SHOW TABLE STATUS [LIKE '%user%'] 命令来查看相关表的详细信息;
  • 存储方式:
    • 共享表空间:.frm 文件存储表结构,数据和索引保存在 innodb_data_home 和 innodb_data_file_path 定义的表空间中;
    • 多表空间存储:.frm 存储表结构,但是每个表数据和索引保存在 .ibd 中;如果是分区表,文件以表名+分区名.ibd 命名;在创建分区的时候可以知道每个分区数据文件的位置,以此来将表的io放在多个磁盘上

3. MEMORY

MEMORY 存储引擎在内存中存储数据,每个 MEMORY 表实际只对应一个 .frm 文件;

  • 优点:访问速度快,因为存储在内存中,默认使用 HASH 索引
  • 缺点:一旦服务重启,表中的数据就会丢失[表结构仍存在];
  • 创建索引[可以指定HASH索引还是BTREE索引]CREATE INDEX mem_index USING HASH|BTREE ON table_name(id)
  • 删除索引DROP INDEX mem_INDEX ON table_name
  • 查看索引SHOW INDEX FROM table_name

4. MERGE

  • MERGE 存储引擎是一组 MyISAM 表的组合,这些 MyISAM 表结果完全相同,merge 表本身不存在任何数据;
  • 对 merge 表进行查询、更新和删除操作时实际上是对 MyISAM 的表进行的操作;
  • 对 merge 表进行插入操作时,采用 INSERT_METHOD=FIRST|LAST|NO 选择将数据插入第一个或最后一个表,或者禁止插入数据;示例代码:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    --u1 表
    CREATE TABLE u1(
    id INT,
    name VARCHAR(20)
    )ENGINE=MYISAM;

    --u2 表
    CREATE TABLE u2(
    id INT,
    name VARCHAR(20)
    )ENGINE=MYISAM;

    --u 表
    CREATE TABLE u(
    id INT,
    name VARCHAR(20)
    ) ENGINE=MERGE UNION=(u1,u2) INSERT_METHOD=LAST;
Thanks! 😊