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