表的主键是指表中的一列或多列，结果必须能够标识表中每一行记录的唯一性。InnoDB表是一个索引组织表，主键既是数据又是索引。

主键的设计原则

1.在空之间占用更少的空间

在上一篇文章中，我们介绍了InnoDB主键的存储方法。pri***ry key 空占用的空间越小，每个索引页中存储的键值就越多，这样就可以一次将更多的数据放入内存。

2.最好有一定的排序属性。

例如，如果使用INT32类型作为主键，并且对值进行严格排序，则可以通过在原始数据页后面添加新记录或者在数据页后添加空页填充记录来完成新记录的插入，这样严格排序的主键的写入速度会非常快。

3.数据类型是塑料。

数据的类型已经提到了。按照前两点的要求，最理想的当然是选择整数类型，比如int32signed。顺序数据增长要么是由数据库本身产生的，要么是由业务自动产生的。

1。与业务无关的属性被用作主键

1.1作为主键的自增字段

这是MySQL最值得推荐的方式。一般用INT32可以满足大部分场景，单个数据库单个表最多可以容纳42亿行记录；新添加的带有自增字段的记录将按顺序添加到当前索引节点的后续位置，直到数据页满，然后写入新的一页。这样会大大减少数据页的随机IO。

使用自增字段作为主键时，可能需要注意两个问题:

第一个问题:MySQL原生自增密钥分裂

如果后期数据增长，有反汇编数据库和表的预期，可以考虑使用INT***；MySQL原生支持数据库反汇编和表反汇编的自增主键，这是由自增步长和初始值决定的。必须至少有两个MySQL节点，每个节点的增量步长为2。假设server_id分别为1和2，增量起始值也可以是1和2。假设下面是第一个MySQL节点。设置好步长和起始值后，表格tmp插入三行，每行严格按照设置的方式插入数据。

mysql> set @@auto_increment_increment=2;Query OK, 0 rows affected (0.00 sec)mysql> set @@auto_increment_offset=1;Query OK, 0 rows affected (0.00 sec)mysql> insert into tmp values(null),(null),(null);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from tmp;+----+| id |+----+| 1 || 3 || 5 |+----+3 rows in set (0.00 sec)

但是，这个MySQL不能保证其他值不会冲突。例如，如果插入节点2值，则可以成功插入。MySQL默认对这一块没有约束，所以数据入库前最好检查一下。

mysql> insert into tmp values(2);Query OK, 1 row affected (0.02 sec)mysql> select * from tmp;+----+| id |+----+| 1 || 2 || 3 || 5 |+----+4 rows in set (0.00 sec)

第二个问题:MySQL自增键合并

这个问题一般涉及旧系统的改造升级。例如，多个部门的旧系统数据必须与新系统合并。在此之前，各分部的自增主键不能简单合并，可能会出现主键冲突。举个例子，假设武汉每个区都有自己的医保数据，而以前每个区都有自己独立设计的数据库。现在医保要升级到全市统一，要按市设计新的数据库模型。

武昌数据如下，对应表n1，

mysql> select * from n1;+----+| id |+----+| 1 || 2 || 3 |+----+3 rows in set (0.00 sec)

汉阳的数据如下，对应表n2，

mysql> select * from n2;+----+| id |+----+| 1 || 2 || 3 |+----+3 rows in set (0.00 sec)

因为两个区的数据库设计者没有考虑未来的合并，每个区的表都有自己独立的自增主键。

考虑设置一个新的自增ID的汇总表n3，设计导入旧系统的ID。

mysql> create table n3 (id int auto_increment pri***ry key, old_id int);Query OK, 0 rows affected (0.07 sec)mysql> insert into n3 (old_id) select * from n1 union all select * from n2;Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from n3;+----+--------+| id | old_id |+----+--------+| 1 | 1 || 2 | 2 || 3 | 3 || 4 | 1 || 5 | 2 || 6 | 3 |+----+--------+6 rows in set (0.00 sec)

这样，应用程序代码可能不确定如何连接旧数据。该表缺少old_id到原始表名的映射。

然后根据原始表ID和原始表名之间的映射关系构建多值索引。例如，下面的例子:

mysql> create table n4(old_id int, old_name varchar(***),pri***ry key(old_id,old_name));Query OK, 0 rows affected (0.05 sec)mysql> insert into n4 select id ,'n1' from n1 union all select id,'n2' from n2;Query OK, 6 rows affected (0.02 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from n4;+--------+----------+| old_id | old_name |+--------+----------+| 1 | n1 || 1 | n2 || 2 | n1 || 2 | n2 || 3 | n1 || 3 | n2 |+--------+----------+6 rows in set (0.00 sec)

最终的表结构与前面的两个表n3和n4相结合，建立了一个包含新的自增长字段主键、原始表ID和原始表名的新表:

create table n5(id int unsigned auto_increment pri***ry key,old_id int,old_name varchar(***),unique key udx_old_id_old_name (old_id,old_name));

当然，关于数据聚合和迁移的话题，讨论太长了，本节无法涵盖。

1.2 UUID作为主键

UUID和自增主键一样，可以保证主键的唯一性。但它是自然无序的，随机生成的，占据空的大空间。在MySQL中，char(36)用于存储UUID。没有特殊的UUID数据类型，字符串是这样的:“7985847 c-7d 59-11ea-8 add-080027 c 52750”。由于InnoDB表的特点，我们应该避免使用char(36)来保存原来的UUID作为主键。

虽然UUID乱了，有空的浪费，但是我们可以利用随机出生的优势吗？

MySQL提供了以下优化方法，以便原始UUID可以用于表主键:

函数uuid_to_bin

MySQL提供了函数uuid_to_bin，该函数将uuid字符串转换为16字节的二进制字符串。UUID类型类似于某些数据库(如POSTGRESQL)。函数uuid_to_bin返回varbinary(16)的数据类型。

例如表t_binary，

mysql> create table t_binary(id varbinary(16) pri***ry key,r1 int, key idx_r1(r1));Query OK, 0 rows affected (0.07 sec)mysql> insert into t_binary values (uuid_to_bin(uuid()),1),(uuid_to_bin(uuid()),2);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t_binary;+------------------------------------+------+| id | r1 |+------------------------------------+------+| 0x412234A77DEF11EA9AF9080027C52750 | 1 || 0x412236E27DEF11EA9AF9080027C52750 | 2 |+------------------------------------+------+2 rows in set (0.00 sec)

函数uuid_short

Varbinary(16)仍然有问题。为此，MySQL还提供了一个函数uuid_short，用于生成一个类似uuid的全局id。结果是INT***。具体计算方法如下:

(服务器id & amp255)<& lt56 +(服务器启动时间秒数& lt& lt24)+incremented _ variable++；

server_id & 255：占 1 个字节；server_startup_time_in_seconds：占 4 个字节；incremented_variable: 占 3 个字节。

如果满足以下条件，该值必须是唯一的

1.server _ id是惟一的，对函数uuid_short()的调用次数每秒不超过1677216次，即2 ^ 24次。所以一般来说，uuid_short函数可以保证结果的唯一性。

2.uuid _ short函数生成的id只需要一个轻量级的互斥体来保护，比自增ID所需的auto-inc表锁更节省资源，生成的结果肯定更快。

下表t_uuid_short演示了如何使用该函数。

mysql> create table t_uuid_short (id bigint unsigned pri***ry key,r1 int, key idx_r1(r1));Query OK, 0 rows affected (0.06 sec)mysql> insert into t_uuid_short values(uuid_short(),1),(uuid_short(),2)Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t_uuid_short;+----------------------+------+| id | r1 |+----------------------+------+| 167439843584***946177 | 1 || 167439843584***946178 | 2 |+----------------------+------+2 rows in set (0.00 sec)

可以看出，uuid_short生成的数据是基于INT***排序的，所以这一块可以看作是自增id的补充优化。如果每秒调用次数小于1677216，建议使用uuid_short，而不是自增id。

说了这么多，还是简单验证一下上面的结论，做个小实验吧。

以下实验涉及四张桌子:

新建 t_uuid: uuid 为主键表 t_binary：varbinary(16) 为主键表 t_uuid_short：bigint 为主键新建表 t_id：自增 ID 为主键

不出所料，写性能从最差到最好的差别是:t _ uuidt _ binaryt _ idt_uuid_short .让我们试验一下，看看它是否符合预期。

两个新添加的表结构:

mysql> create table t_uuid(id char(36) pri***ry key, r1 int, key idx_r1(r1));Query OK, 0 rows affected (0.06 sec)mysql> create table t_id (id bigint auto_increment pri***ry key, r1 int, key idx_r1(r1));Query OK, 0 rows affected (0.08 sec)

写一个简单的存储过程，分别为这些表做30W条记录。

DELIMITER $$CREATE PROCEDURE `ytt`.`sp_insert_data`( f_tbname VARCHAR(***), f_number INT UNSIGNED ) BEGIN DECLARE i INT UNSIGNED DEFAULT 0; SET @@autocommit=0; IF f_tbname = 't_uuid' THEN SET @stmt = CONCAT('insert into t_uuid values (uuid(),ceil(rand()*100));'); ELSEIF f_tbname = 't_binary' THEN SET @stmt = CONCAT('insert into t_binary values(uuid_to_bin(uuid()),ceil(rand()*100));'); ELSEIF f_tbname = 't_uuid_short' THEN SET @stmt = CONCAT('insert into t_uuid_short values(uuid_short(),ceil(rand()*100));'); ELSEIF f_tbname = 't_id' THEN SET @stmt = CONCAT('insert into t_id(r1) values(ceil(rand()*100));'); END IF; WHILE i < f_number DO PREPARE s1 FROM @stmt; EXECUTE s1; SET i = i + 1; IF MOD(i,50) = 0 THEN COMMIT; END IF; END WHILE; COMMIT; DROP PREPARE s1;SET @@autocommit=1; END$$ DELIMITER ;

接下来，分别调用存储过程，结果与预期一致。T_uuid时间最长，t_uuid_short时间最短。

mysql> call sp_insert_data('t_uuid',300000);Query OK, 0 rows affected (5 min 23.33 sec)mysql> call sp_insert_data('t_binary',300000);Query OK, 0 rows affected (4 min 48.92 sec)mysql> call sp_insert_data('t_id',300000);Query OK, 0 rows affected (3 min 40.38 sec)mysql> call sp_insert_data('t_uuid_short',300000);Query OK, 0 rows affected (3 min 9.94 sec)

第二，业务相关属性作为主键。

主键的设计要求可读性强，如学号(入学年份+院系+专业)、购物订单代码等。实际上，不建议将如此有意义的业务字段用于主键。可以创建一个新的自增主键或uuid_short()函数字段。实际业务字段并没有设计成主键，而是变成了一个通用的唯一索引。例如，表n5:

mysql> create table n5( id int unsigned auto_increment pri***ry key, userno int unsigned , unique key udx_userno(userno) );Query OK, 0 rows affected (0.08 sec)

Userno(用户代码)用作主键。如果业务端的数据出现了错误，例如，老师可能输入了错误的数据或者业务系统中的一个BUG可能导致了错误的数据，那么不仅要更改输入表(这是一个聚集索引)的主键，还要更改依赖于该表的所有子表，这是一个很大的工程。但是，如果存在与业务无关的主键，则只需要更改业务字段(二级索引)，而不需要更改依赖于该表的子表。

这里大致介绍一下MySQL主键的设计思路。如果您有任何问题，请留言并纠正本文中的任何缺点。