字节流的博客

MySQL 存储引擎

发表于 | 分类于

1. MyISAM

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

MyISAM支持的3种存储格式：

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

2. InnoDB

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

  • 自增长序列：必须为索引，如果是组合索引，也必须是索引的第一列；MyISAM可以为其他列。当插入 0 或 null 时按自增长计算，正常整数按指定的插入；
  • 外键约束：MySQL 中只有 InnoDB 引擎支持外键约束；在创建外键时父表必须有相应的索引，子表创建外键时系统会自动创建对应的索引；

  • 在更新父表时，对子表进行的操作：RESTRICT，CASCADE，SET NULL 和 NO ACTION；其中：RESTRICT 和 NO 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! 😊