1. MyISAM
MyISAM 是 MySQL 数据库默认的存储引擎。不支持事务和外键,其优势是访问速度快,对事务完整性要求不高的或者以 select、insert 为主的应用基本上都可以使用这个引擎来创建表。
MyISAM支持的3种存储格式:
- 静态表(固定长度)[默认]:
- 优点:存储速度快; 方便缓存; 出现故障易回复;
- 缺点:占用空间大;
- 动态表;
- 压缩表;
- 静态表长度是固定的,当长度不足时自动用空格补齐,获取数据时删掉空格。[注意]当数据中本身存在空格时,获取时也会被删掉。
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
9CREATE 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;