MySQL InnoDB 引擎特性

  1. 自增长列
  2. 外键约束
  3. 主键和索引

自增长列

数据自增

当向设置了自增长的列插入数据时如果插入值为 null 或 0 则会插入自增后的值,默认起始值为 1,当有数据时默认为当前列最大索引数+1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table t1
(
i smallint not null auto_increment,
name varchar(10),
primary key (i)
);
insert into t1 (i, name)
values ( null
, 'dudulu');
insert into t1 (i, name)
values ( 0
, 'dudulud');
select * from t1;
mysql> select * from t1;
+---+---------+
| i | name |
+---+---------+
| 1 | dudulu |
| 2 | dudulud |
+---+---------+
2 rows in set (0.01 sec)

手动设置自增初始值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
alter table t1 auto_increment = 100;
insert into t1 (i, name)
values ( 0
, 'dudulud');
select *
from t1;
mysql> select * from t1;
+-----+---------+
| i | name |
+-----+---------+
| 1 | dudulu |
| 2 | dudulud |
| 100 | dudulud |
+-----+---------+
3 rows in set (0.00 sec)

在 MySQL8.0 之前这个值仅会保存在内存中,重启 MySQL 后会失效,插入的值为自增列最大值+1
8.0 的策略将这个值持久化到了 REDO 中。

外键约束

MySQL 目前只有 innoDB 引擎支持外键。在创建外键的时候父表必须有对应的索引、字表在创建外键的时候会自动创建索引

当父表 on update/delete 时对子表进行的操作

约束效果
restrict字表有关联记录时父表不能更新
not action同上
cascade父表更新或删除时字表删除对应关联记录
set null父表更新或删除时字表相关字段被设置成 null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create table country
(
country_id smallint unsigned not null auto_increment,
country varchar(50) not null,
last_update timestamp not null default current_timestamp on update current_timestamp,
primary key (country_id)
);
create table city
(
city_id smallint unsigned not null auto_increment,
city varchar(50) not null,
country_id smallint unsigned not null,
last_update timestamp not null default current_timestamp on update current_timestamp,
primary key (city_id),
KEY idx_country_id (country_id),
CONSTRAINT fk_country_city foreign key (country_id) references country (country_id) on delete restrict on update cascade
)
select c.country_id as id, country, city, c.last_update
from country
right join city c on country.country_id = c.country_id;
delete from country where country_id = 1;
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_country_city` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)
update country
set country_id = 100
where country_id = 1;
select * from city;

主键和索引

所有 InnoDB 表都必须包含主键,如果创建表的时候,没有显示指定主键,那么 InnoDB 存储引擎会自动创建一个长度为 6 个字节的 long 类型隐藏字段做为主键。

主键应该按照以下原则来选择

  • 满足唯一和非空约束
  • 优先考虑使用最经常被当做查询条件的字段或自增字段
  • 字段值基本不会被修改
  • 使用尽可能短的字段

在 InnoDB 表上,除了主键之外的其他索引都叫做辅助索引或者二级索引,二级索引会指向主索引,并通过主索引获取最终数据。因此,主键是否合理的创建,会对所有索引的效率产生影响。