关于索引:第17期索引设计主键设计

9次阅读

共计 6456 个字符，预计需要花费 17 分钟才能阅读完成。

表的主键指的针对一张表中的一列或者多列，其后果必须能标识表中每行记录的唯一性。InnoDB 表是索引组织表，主键既是数据也是索引。

主键的设计准则

  1. 对空间占用要小

上一篇咱们介绍过 InnoDB 主键的存储形式，主键占用空间越小，每个索引页里寄存的键值越多，这样一次性放入内存的数据也就越多。

  1. 最好是有肯定的排序属性

如 INT32 类型来做主键，数值有严格的排序，那新记录的插入只有往原先数据页前面增加新记录或者在数据页后新增空页来填充记录即可，这样有严格排序的主键写入速度也会十分快。

  1. 数据类型为整形

数据类型早就曾经讲过，依照前两点的需要，最现实的当然是抉择整数类型，比方 int32 unsigned。数据程序增长，要么是数据库本人生成，要么是业务主动生成。

一、与业务无关的属性做主键

1.1 自增字段做主键

这是 MySQL 最举荐的形式。个别用 INT32 能够满足大部分场景，单库单表能够最大保留 42 亿行记录；含有自增字段的新增记录会程序增加到以后索引节点的后续地位直到数据页写满为止，再写新页。这样会极大水平的缩小数据页的随机 IO。
用自增字段做主键可能须要留神两个问题：
第一个问题：MySQL 原生自增键拆分
如果随着数据前期增长，有拆库拆表预期，能够思考用 INT64；MySQL 原生反对拆库拆表的自增主键，通过自增步长与起始值来确定。起码要有 2 个 MySQL 节点，每个节点自增步长为 2，假如 server_id 别离为 1，2，那自增起始值也能够是 1，2。假如上面是第 1 个 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: 0

mysql> 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)

因为之前两个区数据库设计的人都没有思考当前合并的事件，所以每个区的表都有本人独立的自增主键，
思考这样建设一张汇总表 n3，有新的自增 ID，并且设计导入老零碎的 ID。

mysql> create table n3 (id int auto_increment primary 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: 0

mysql> 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(64),primary 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: 0

mysql> 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 primary key,
old_id int,
old_name varchar(64),
unique key udx_old_id_old_name (old_id,old_name)
);

当然，对于数据汇总迁徙的话题，探讨篇幅太长，不在本节范畴。

1.2 UUID 做主键

UUID 和自增主键一样，能保障主键的唯一性。然而天生无序、随机产生、占用空间大。在 MySQL 里，用 char(36) 来存储 UUID，没有专门的 UUID 数据类型，相似这样的字符串:‘7985847c-7d59-11ea-8add-080027c52750’。因为 InnoDB 表的个性，应该防止用 char(36) 保留原始 UUID 的形式做表主键。
尽管 UUID 无序，且存在空间节约，但天生随机这个长处是否利用上？
MySQL 提供了以下的优化办法来让原始 UUID 能够被用于表主键：
函数 uuid_to_bin
MySQL 提供了函数 uuid_to_bin，把 UUID 字符串变为 16 个字节的二进制串。相似于某些数据库（比方 POSTGRESQL）的 UUID 类型。函数 uuid_to_bin 返回数据类型为 varbinary(16)。
例如表 t_binary，

mysql> create table t_binary(id varbinary(16) primary 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: 0

mysql> 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，后果为 INT64。具体计算形式如下：
(server_id & 255) << 56 + (server_startup_time_in_seconds << 24) + incremented_variable++;

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

如果满足以下条件，那这个值就必然是惟一的

  1. server_id 惟一并且对函数 uuid_short() 的调用次数不超过每秒 16777216 次，也就是 2^24。所以个别状况下，uuid_short 函数能保障后果惟一。
  2. uuid_short 函数生成的 ID 只需一个轻量级的 mutex 来爱护，这点比自增 ID 须要的 auto-inc 表锁更省资源，生成后果必定更加疾速。

上面表 t_uuid_short 演示了如何用这个函数。

mysql> create table t_uuid_short  (id bigint unsigned primary 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: 0

mysql> select * from t_uuid_short;
+----------------------+------+
| id                   | r1   |
+----------------------+------+
| 16743984358464946177 |    1 |
| 16743984358464946178 |    2 |
+----------------------+------+
2 rows in set (0.00 sec)

能够看到 uuid_short 生成的数据是基于 INT64 有序的，所以这块能够看做是自增 ID 的一个补充优化，如果每秒调用次数少于 16777216，举荐用 uuid_short，而非自增 ID。
说了那么多，还是简略验证下下面的论断，做个小试验。
以下试验波及到四张表：

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

正如之前的预期，写性能差别按从最差到最好排列顺次为：t_uuid; t_binary；t_id；t_uuid_short。咱们来试验下是否和预期相符。
新增的两张表构造：

mysql> create table t_uuid(id char(36) primary key, r1 int, key idx_r1(r1));
Query OK, 0 rows affected (0.06 sec)

mysql> create table t_id (id bigint auto_increment primary 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(64),
  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 primary key, 
        userno int unsigned ,
        unique key udx_userno(userno)
        );
Query OK, 0 rows affected (0.08 sec)

用 userno（用户编码）来做主键，如果在业务端数据曾经谬误，比方可能因为老师起因录入谬误数据，或者是业务零碎的 BUG 导致录入数据有误，那不仅要对录入表的主键做更改（这可是聚簇索引），还要更改依赖这张表的所有子表，这其实是一个很大的工程。然而如果有与业务不相干的主键，只须要更改业务字段（二级索引）就能够，不须要更改依赖这张表的子表。
对于 MySQL 主键的设计思路大抵介绍到此，有问题欢送留言，欢送斧正本篇任何不足之处。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
索引
发表至： 索引
2020-12-02
 0
关于索引:索引该怎么创建
关于索引:第17期索引设计主键设计
关于存储:阿里巴巴开源容器镜像加速技术
关于索引:ElasticSearch必知必会Reindex重建索引

站内搜索

-「」