1. MyISAM
- MyISAM是MySQL5.5之前版本的默认存储引擎,MyISAM既不支持事务,也不支持外键;
- 每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别如下:
- .frm(存储表定义)
- .MYD(MYData,存储数据)
- .MYI(MYIndex,存储索引)
- 数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度;
- 要指定索引文件和数据文件的路径,需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定,索引文件和数据文件可以放置到不同路径下;
- MyISAM类型的表可能被损坏,损坏后不能被访问,MyISAM类型的表提供修复工具,可以用
CHECK TABLE
语句检查MyISAM表的健康,并用REPAIR TABLE
语句修复一个损坏的MyISAM表; - MyISAM的表支持3种不同的存储格式:
- 静态(固定长度)表
- 动态表
- 压缩表
InnoDB
- InnoDB作为MySQL5.5之后的默认存储引擎,提供了具有提交、回滚和崩溃恢复能力的事务安全保障,同时提供了更小的锁粒度和更强的并发能力,拥有自己独立的缓存和日志;
- 对比MyISAM存储引擎,InnoDB会占用更多的磁盘空间以保留数据和索引
1.自动增长列
- InnoDB表的自动增长列可以手工插入,但是插入的值如果是空,则实际插入的将是自动增长后的值;
- 在MySQL8.0之前,对于InnoDB存储引擎来说,这个值只保留在内存中,如果数据库重新启动,那么这个值就会丢失数据库会自动将
AUTO_INCREMENT
重置为自增列当前存储的最大值+1,可能会导致在数据库重启后,自增列记录的值和预期不一致,从而导致数据冲突; - 在MySQL8.0中,这个bug得到了修复,具体实现方式是将自增主键的计数器持久化到REDO LOG中,每次计数器发生改变,都会将其写入到REDO LOG。如果数据库发生重启,InnoDB会根据REDO LOG中的计数器信息来初始化其内存值
- 例子:(MySQL5.7为例)
- 创建测试表,id为自增主键
- 修改
AUTO_INCREMENT=100
- 写入数据,数据生效
- id值为预期的100,再将
AUTO_INCREMENT
改为200 - 重启MySQL实例
- 重启之后,
AUTO_INCREMENT
的值变成了101,是当前自增列的最大值+1,而不是重启前的200,这种情况可能导致某些历史数据归档或复制环境中发生数据冲突;
2.外键约束
- MySQL支持外键的常用存储引擎只有InnoDB,在创建外键时,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引;
-
RESTRICT
、NO ACTION
:指限制在子表有关联记录的情况下父表不能更新; -
CASCADE
:父表在更新或者删除时,更新或者删除子表对应记录; -
SET NULL
:父表在更新或者删除的时候,子表的对应字段被SET NULL; -
ON DELETE RESTRICT ON UPDATE CASCADE
:在主表删除记录时,如果子表有对应记录,则不允许删除;主表在更新记录的时候,如果子表有对应记录,则子表更新; - 在导入多个表的数据时,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查;
- 在执行
LOAD DATA
和ALTER TABLE
操作的时候,可以通过暂时关闭外键约束来加快处理的速度,关闭的命令SET FOREIGN_KEY_CHECKS=0;
,执行完成后,通过执行SET FOREIGN_KEY_CHECKS=1;
,改回原状态; - 外键信息可以通过使用
show create table
或者show table status
命令显示。
3.主键和索引
- InnoDB的数据文件本身以聚簇索引的形式保存的,也称为主索引,并且也是InnoDB表的主键,InnoDB表的每行数据都保存在主索引的叶子节点上;
- InnoDB表必须包含主键,如果创建表时,没有显式指定主键,会自动创建一个长度为6字节的long类型隐藏字段作为主键;
- 主键选择原则:
- 满足唯一和非空约束;
- 优先考虑使用最经常被当作查询条件的字段或者自增字段;
- 字段值基本不会被修改;
- 使用尽可能短的字段。
4.存储方式
- InnoDB存储表和索引有以下两种方式
- 使用共享表空间存储
- 使用多表空间存储