MySQL中InnoDB引擎--特点

时间:2024-03-13 15:20:30

InnoDB引擎 特点

innodb存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全;提供外键;提供了多版本并发控制的行级锁。
innodb 在5.5版本以后的MySQL数据库中为默认存储引擎
但是对比MyISAM,InnoDB写的效率差一些,会占用更多的磁盘空间以保留数据和索引。

1 自动增长列

创建表时,不能设置自动增长列的初始值。
create table classes (
id int unsigned auto_increment primary key not null,
name varchar(20) not null
);

可以通过alter table强制设置整个自动增长列的初始值,不设置初始值,默认从1开始。
alter table classes auto_increment=8;

添加数据时,insert into可以强制设置该行自动增长列的值。
insert into classes values (9,"1班");

可以使用last_insert_id()来查询当前线程最后插入记录使用的值,如果是一次插入了多条数据,则返回第一条记录使用的自动增长值。
insert into classes(name) values("1班");
select last_insert_id();
MySQL中InnoDB引擎--特点
insert into classes(name) values("2班"), ("3班"), ("4班");
select last_insert_id();

MySQL中InnoDB引擎--特点

对于InnoDB表,自动增长列必须是索引,如果是组合索引,也必须是组合索引的第一列;
但是对于myisam表,自动增长列可以是组合索引的其他列,自动增长的排序是按照索引列不同内容进行递增,例如:
d2是索引列,自动增长列d1作为组合索引的第二列,对该表插入一些记录后发现,d1是按照d2的内容进行排序后递增的。
create table test(
d1 int unsigned auto_increment not null,
d2 int unsigned not null,
name varchar(20),
index(d2,d1)
) engine=myisam;

MySQL中InnoDB引擎--特点

2 外键约束

MySQL中只有InnoDB引擎支持外键存储,在创建外键的时候,要求父表必须有对应的索引,
子表在创建外键的时候也会自动创建对应的索引。
当某个表被其他表创建了外键参照,那么该表的对应的索引或主键被禁止删除。
外键信息可以通过查看创建表信息得到:show create table
在创建索引的时候,可以指定:
(1)restrict和no action:在删除、更新父表时,限制关联的子表同时进行更新或删除,
如果不设置,子表有数据关联父表某一数据时,父表该数据不允许更新;
(2)cascade:父表在更新或删除时,更新或删除子表对应记录。
(3)set null:父表在更新或删除时,子表的对应字段被set null。
注意:2和3这两种方式选择要谨慎,因为有可能会因为错误的操作而导致数据丢失。

如下:创建表:子表的外键指定on delete restrict on update cascade方式。

– 创建父表
create table classes (
id int unsigned auto_increment primary key not null,
c_name varchar(20) not null
);

– 插入数据
insert into classes(c_name) values("1班"), ("2班"), ("3班");
– 创建子表,并设置外键
create table students(
id int unsigned auto_increment primary key not null,
s_name varchar(20) not null,
cls_id int unsigned not null,
constraint foreign key(cls_id) references classes(id)
on delete restrict on update cascade
);

– 插入数据
insert into students(s_name, cls_id) values
("王一", 1), ("王二", 1), ("王三", 2), ("王四", 2),
("王五", 3);

MySQL中InnoDB引擎--特点

那么主表classes删除的时候,因为子表students因为有对应的记录cls_id,不允许删除;

MySQL中InnoDB引擎--特点

主表classes更新了记录id=5,则子表中也对应更新cls_id=5。

MySQL中InnoDB引擎--特点

外键约束可以临时关闭:set foreign_key_checks=0;
再次开启外键约束:set foreign_key_checks=1;

3 存储特征

innodb存储表和索引有以下两种方式:
(1)共享表空间存储
表结构存储在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。
innodb默认为共享表空间存储。
(2)多表空间存储
表结构存储在.frm文件中,每个表的数据和索引单独保存在.idb文件中。
如果是分区表,则每个分区对应单独是.ibd文件,文件名为“表名+分区名”。
多表空间的数据文件没有大小限制,不需要设置相关参数。
多表空间存储方式更便于单表的备份和恢复操作。