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 均不报错

你的答案是什么，真的掌握透彻了么？

相关文章：
《索引，一文搞定》

ITPub博客

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

最新文章