MySQL中InnoDB引擎--特点

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();

insert into classes(name) values("2班"), ("3班"), ("4班");
select last_insert_id();

对于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;

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);

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

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

外键约束可以临时关闭：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文件，文件名为“表名+分区名”。
多表空间的数据文件没有大小限制，不需要设置相关参数。
多表空间存储方式更便于单表的备份和恢复操作。