主键,不少人以为自己懂了,却不透彻...

YangSunshine 2019-11-22

主键,不少人以为自己懂了,却不透彻...

MySQL是被广泛应用的数据库,InnoDB又是用得最广的存储引擎,主键又是对InnoDB性能影响最大的因素之一,主键选择对,可以极大提升性能。

InnoDB选择什么列作为主键,有一些最佳实践:

(1)不能为空的列;

(2)不能重复的列;

(3)很少改变的列;

画外音:行是按照聚集索引物理排序的,如果主键频繁改变,物理顺序会改变,性能会急剧降低。

(4)经常被检索(where key=XXX)的列

画外音:被检索的列上要建立索引,如果该索引是聚集索引,能够避免回表,性能提升几乎一倍。

(5)不是太长的列

画外音:普通索引叶子节点会存储主键值,如果主键值太长,会增加普通索引的大小。

聚集索引,普通索引底层结构如何
详见《InnoDB聚集索引,普通索引的索引差异》。
什么是回表,什么是索引覆盖,如何避免回表
详见《如何避免回表查询?什么是索引覆盖?》。
主键太长为啥特别影响性能
详见《数据库,主键为何不宜太长长长?》。
参考上述最佳实践,业务上经常采用这样的一些列作为主键:

  • 用户ID:uid
  • 消息ID:msgid
  • 订单ID:oid

这些列,能够很好的满足非空、唯一、不变、经常被查询、长整型等最佳实践,性能较高。

画外音:这些ID建议业务侧使用snowflake生成,即全局唯一,又趋势递增。

如果没有这样的业务属性,也可以使用自增ID(auto_inc_id)作为主键,自增ID能够满足非空,唯一,不变,长整型等最佳实践,性能也比较高。

画外音:自增ID不宜暴露给上游,否则分库扩展时有大坑。

但是,上面毕竟是理论,落到实操层面,我们真的掌握得这么透彻吗?下面五个小习题,看大家对InnoDB主键到底掌握到什么程度。

练习一:建表时,可不可以不声明主键?

(1) create table user(

name varchar(10)

)engine=innodb;

(2) insert into user values('shenjian');

(3) insert into user values('shenjian');

画外音:建表时,不声明主键,插入两个相同的元素。

提问,连续执行上面的语句,执行结果是:

A 建表语句(1)报错

B 插入语句(2)报错

C 插入语句(3)报错

D 均不报错

练习二:建表时,可不可以不声明主键非空?

(1) create table user(

id int,

name varchar(10),

primary key(id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user(name) values('shenjian');

画外音:建表时,不声明非空,插入两个相同的元素。

提问,连续执行上面的语句,执行结果是:

A 建表语句(1)报错

B 插入语句(2)报错

C 插入语句(3)报错

D 均不报错

练习三:建表时,可不可以选择多个字段做主键?

(1) create table user(

id int not null,

name varchar(10) not null,

primary key(id, name)

)engine=innodb;

(2) insert into user values(1, 'shenjian');

(3) insert into user values(1, 'zhangsan');

(4) insert into user values(2, 'shenjian');

画外音:建表时,声明联合主键(a,b),插入若干元素,有些a重复,有些b重复。

提问,连续执行上面的语句,执行结果是:

A 建表语句(1)报错

B 插入语句(2)报错

C 插入语句(3)报错

D 插入语句(3)报错

E 均不报错

练习四:可不可以主动插入自增主键?

(1) create table user(

id int auto_increment,

name varchar(10) not null,

primary key(id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user(id, name) values(10,'shenjian');

(4) insert into user(name) values('shenjian');

画外音:建表时,自增ID为主键,插入若干元素,有些包含自增ID,有些不包含。

提问,连续执行上面的语句,执行结果是:

A 建表语句(1)报错

B 插入语句(2)报错

C 插入语句(3)报错

D 插入语句(3)报错

E 均不报错

练习五:建表时,可不可以使用联合自增主键?

(1) create table user(

id int auto_increment,

name varchar(10) not null,

primary key(name, id)

)engine=innodb;

(2) insert into user(name) values('shenjian');

(3) insert into user(id, name) values(10,'shenjian');

(4) insert into user(name) values('shenjian');

画外音:建表时,声明联合主键(a,b),并且有一个是自增ID,插入若干元素,包含自增ID,有些不包含。

提问,连续执行上面的语句,执行结果是:

A 建表语句(1)报错

B 插入语句(2)报错

C 插入语句(3)报错

D 插入语句(3)报错

E 均不报错

你的答案是什么,真的掌握透彻了么?
相关文章:
《索引,一文搞定》

相关推荐