前言

主键,能够仅有标识表中的某一行(记载)。合理地设置主键,能够协助咱们精确、快速地找到所需求的数据记载。可是设置出正确的主键好像并没有那么简略,请思考如下几个问题:

  1. 表中的事务字段能够用来做主键吗?
  2. 单机体系下运用自增字段做主键,那在分布式体系下能够吗?
  3. 在分布式体系下怎么确保主键的仅有性呢?

假如对以上问题感到困惑,那么我相信这篇文章能够协助到你。

我坚信,友爱的交流会让互相快速进步!文章不免有遗漏之处,欢迎咱们在谈论区中批评指正。

为什么一定要给表界说主键?

提高查询效率

前言中说到,主键能够仅有标识表中的某一行(记载),合理地设置主键,能够协助咱们精确、快速找到所需求的数据记载。为什么呢?这是由于给表界说了主键,就相当于给表加了一个主键索引。索引能够协助提高数据查询的效率,就像书的目录一样。

安全地更新或删去特定行

假如没有为表增加主键,不只查询效率会变低,更会导致更新或删去表中的特定行很困难,由于没有安全的办法确保只更新或删去相关的行。

假如你运用的图形化管理工具是 Workbench(MySQL 官方图形化管理工具)的话,在履行 UPDATEDELETE 操作时,有必要包含 WHERE 条件。并且,WHERE 条件中,有必要用到主键约束或仅有性约束的字段,不然会报如下过错。

Error Code: 1175.
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

实际案例

现在咱们有一个产品信息表,表中字段和样例数据如下所示。

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

创立产品信息表 sku_info

# 创立产品信息表 sku_info
CREATE TABLE sku_info
(
    pk_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sku_name   VARCHAR(255) COMMENT '产品名',
    price      DECIMAL(10, 2) COMMENT '产品价格',
    gmt_create DATETIME COMMENT '创立时刻',
    gmt_modified DATETIME COMMENT '更新时刻'
);
########################
# 查看表结构
DESCRIBE sku_info;
+--------------+-----------------+------+-----+---------+----------------+
| Field        | Type            | Null | Key | Default | Extra          |
+--------------+-----------------+------+-----+---------+----------------+
| pk_id        | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| sku_name     | varchar(255)    | YES  |     | NULL    |                |
| price        | decimal(10,2)   | YES  |     | NULL    |                |
| gmt_create   | datetime        | YES  |     | NULL    |                |
| gmt_modified | datetime        | YES  |     | NULL    |                |
+--------------+-----------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)

《阿里巴巴 Java 开发手册 1.4.0》的部分建表规约如下:

【强制】 表名、字段名有必要运用小写字母或数字,制止呈现数字最初,制止两个下划线中间只呈现数字。

【强制】 主键索引名为pk_字段名;仅有索引名为uk_字段名;普通索引名则为idx_字段名。

【强制】 小数类型为 decimal,制止运用 float 和 double。

【强制】 表必备三字段:id,create_time,update_time。其间 id 必为主键,类型为 bigint unsigned、单表自增、步长为 1。

依据上面这个强制要求,咱们也知道了数据表中一定要界说主键

想要了解更多,请拜见《阿里巴巴 Java 开发手册 1.4.0》。

刺进样例数据

# 刺进数据
INSERT INTO sku_info
(sku_name, price, gmt_create, gmt_modified) 
VALUES
('书本', 10, '2023-06-01', '2023-06-01'),
('鼠标', 199, '2023-06-01', '2023-06-01');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
########################
# 查看表数据
SELECT * FROM sku_info;
+-------+----------+----------+---------------------+---------------------+
| pk_id | sku_name | price    | gmt_create          | gmt_modified        |
+-------+----------+----------+---------------------+---------------------+
|     1 | 书本     |  10.00 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |
|     2 | 鼠标     | 199.00 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |
+-------+----------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

谨慎运用通配符

除非你明确需求表中一切的列,不然最好别运用 * 通配符。尽管通配符挺省事的,不必明确列出所需求的列,但检索不需求的列通常会下降检索和应用程序的功用。

当然,运用通配符有一个大优点。由于不明确指定列名(通配符 * 检索每个列),所以能检索出名字不知道的列

安全形式下特定删去句子报错

在 Workbench 中履行下面恣意一条指令都会报错:

# 没有增加 WHERE 条件
DELETE FROM sku_info; 
# WHERE 条件中的字段没有主键约束或仅有性约束
DELETE FROM sku_info WHERE price = 10; 

报错状况如下:

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

具体报错信息如下:

Error Code: 1175.
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

第一条句子会删去 sku_info 表中的一切数据记载,第二条句子会删去 sku_info 表中一切价格为 10 的数据记载。这两种操作都会删去许多数据,或许过错地包括不相关的数据,因而被 MySQL 的安全形式予以制止。

当然,假如你在非安全形式下进行上述两种删去操作是没问题的,比方你直接运用终端去履行删去操作是能够经过的,可是请有必要确保不要删去了不相关的数据。

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

运用主键要注意哪些事情?

主键值有必要是的仅有的

表中每行的主键值有必要仅有(主键列不答应 NULL 值)。假如主键运用单个列,值有必要仅有。假如运用多个列,则这些列的组合值有必要仅有。

不要修正主键列中的值

主键值是一条数据记载的仅有标识,假如修正了主键的值,就或许破坏数据的完整性。

假如你需求去修正主键的值,那么很有或许是你的主键设置得不合理。

为什么尽量不要用事务字段做主键?

运用事务字段做主键,由于无法预测项目在整个生命周期中,哪个事务字段会由于事务需求而有重复,或者重用之类的状况,此刻需求变更主键,这往往是不能被承受的。

咱们举一个实际案例,来看看运用事务字段做主键,当需求产生变更时将会产生什么样的事情。

用案例阐明

运用事务字段「会员卡号」做主键

现在有一家零售店,需求进行会员营销,会员信息表字段和样例数据如下所示。

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

在这儿,咱们将事务字段「会员卡号」设置成了主键,「会员卡号」本身不能为空,并且具有仅有性,能够用来仅有标识一名会员。

下面咱们创立出来这张会员信息表,并把上面的数据刺进表中。

创立会员信息表 member_info

CREATE TABLE member_info
(
    pk_card_no CHAR(8) PRIMARY KEY COMMENT '会员卡号', # 会员卡号为主键
    member_name VARCHAR(20) COMMENT '名字',
    gender CHAR(1) COMMENT '性别',
    pid CHAR(18) COMMENT '身份证号',
    gmt_create DATETIME COMMENT '注册时刻',
    gmt_modified DATETIME COMMENT '更新时刻'
);
Query OK, 0 rows affected (0.86 sec)

刺进样例数据

# 刺进样例数据
INSERT INTO member_info
(pk_card_no, member_name, gender, pid, gmt_create, gmt_modified)
VALUES
('11000001', '柿子先生', '男', '123456199902107891', '2023-06-01', '2023-06-01'),
('11000002', '可可小姐', '女', '123456199809077891', '2023-06-01', '2023-06-01');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
########################
# 查看表中数据
SELECT * FROM member_info;
+------------+--------------+--------+---------------------+---------------------+
| pk_card_no | member_name  | gender | gmt_create          | gmt_modified        |
+------------+--------------+--------+---------------------+---------------------+
| 11000001   | 柿子先生      | 男      | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |
| 11000002   | 可可小姐      | 女      | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |
+------------+--------------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

能够看到,「会员卡号」pk_card_no 能够仅有地标识某个会员,体系能够正常运转。

可是,体系上线后产生了一件事,导致「会员卡号」无法再仅有辨认某个会员了。

退卡引发的危机

现在柿子先生搬家了,不再前往该商家消费,所以他退还了会员卡。商家没有丢掉这张会员卡(究竟有制卡本钱),而是把这张卡号为 11000001 的会员卡发给了下一个办理会员卡的用户,六一。

假如只单看这一张表,的确是没有什么影响的,咱们只要把会员信息表中卡号为 11000001 的会员信息修正一下不就能够啦,可是从整个体系的事务层面来看,将会呈现大问题!

下面,咱们来一同看看这个问题是怎么产生的?

现在咱们有一张订单信息表,上面记载了一切的订单信息。2023-06-02,柿子先生购买了一本书,消费了 10 元,体系会记载该订单信息,如下所示:

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

创立订单信息表 order_info

订单信息表 order_info 引入了之前的产品信息表 sku_info产品 id,以及会员信息表 member_info会员卡号

咱们先把订单信息表创立出来,并刺进上面的数据。

# 创立订单信息表
CREATE TABLE order_info
(
    pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    card_no CHAR(8) COMMENT '会员卡号', # 与会员信息表树立联络
    sku_id BIGINT UNSIGNED COMMENT '产品 id', # 与产品信息表树立联络
    sku_price DECIMAL(10,2) COMMENT '产品价格',
    sale_quantity INT UNSIGNED COMMENT '出售数量',
    sale_amount DECIMAL(10,2) COMMENT '出售金额',
    gmt_create DATETIME COMMENT '交易时刻',
    gmt_modified DATETIME COMMENT '更新时刻'
);
Query OK, 0 rows affected (0.06 sec)

刺进样例数据

# 刺进样例数据
INSERT INTO order_info
(card_no, sku_id, sku_price, sale_quantity, sale_amount, gmt_create, gmt_modified)
VALUES
('11000001', 1, 10, 1, 10, '2023-06-02', '2023-06-02');
Query OK, 1 row affected (0.00 sec)
########################
# 查看表中的数据
SELECT * FROM order_info;
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+
| pk_id | card_no  | sku_id | sku_price | sale_quantity | sale_amount | gmt_create          | gmt_modified        |
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+
|     1 | 11000001 |      1 |     10.00 |             1 |       10.00 | 2023-06-02 00:00:00 | 2023-06-02 00:00:00 |
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+
1 row in set (0.01 sec)

查询会员出售记载

现在,咱们要完结这样的需求,依据产品信息表 sku_info、会员信息表 member_info、订单信息表 order_info 查询出 2023-06-02 当天的会员出售记载,具体形式如下:

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

为了完成这个查询,咱们需求运用相关查询,具体履行句子如下:

SELECT m.member_name, s.sku_name, o.sale_quantity, o.sale_amount, o.gmt_create AS trade_time
FROM order_info AS o  
JOIN sku_info AS s 
JOIN member_info AS m
ON (o.card_no = m.pk_card_no AND o.sku_id = s.pk_id);
+--------------+----------+---------------+-------------+---------------------+
| member_name  | sku_name | sale_quantity | sale_amount | trade_time          |
+--------------+----------+---------------+-------------+---------------------+
| 柿子先生      | 书本      |             1 |       10.00 | 2023-06-02 00:00:00 |
+--------------+----------+---------------+-------------+---------------------+
1 row in set (0.02 sec)

咱们查询得到的成果是柿子先生在 2023-06-02 这一天,买了一本书,消费了 10 元

退卡

然后,2023-06-03,柿子先生退还了会员卡,商家又把这张卡发给了六一。那么,咱们是不是要修正会员信息表 member_info 中的会员信息。

UPDATE member_info
SET member_name = '六一',
gender = '男',
pid = '123456202203017891',
gmt_modified = '2023-06-03'
WHERE pk_card_no = '11000001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查询会员出售记载呈现异常

现在咱们再查询一下 2023-06-02 当天的会员出售记载:

SELECT m.member_name, s.sku_name, o.sale_quantity, o.sale_amount, o.gmt_create AS trade_time
FROM order_info AS o  
JOIN sku_info AS s 
JOIN member_info AS m
ON (o.card_no = m.pk_card_no AND o.sku_id = s.pk_id);
+-------------+----------+---------------+-------------+---------------------+
| member_name | sku_name | sale_quantity | sale_amount | trade_time          |
+-------------+----------+---------------+-------------+---------------------+
| 六一        | 书本     |             1 |       10.00 | 2023-06-02 00:00:00 |
+-------------+----------+---------------+-------------+---------------------+
1 row in set (0.01 sec)

呈现大问题了!查询得到的成果是 2023-06-02,六一买了一本书,消费 10 元。可是,六一 2023-06-03 才拿到会员卡。

很显着,由于退卡重发,柿子先生的消费行为转移到了六一身上,这肯定是无法承受的。

产生这个问题的原因便是,咱们把会员卡号是 11000001 的会员信息修正了,而会员卡号是主键,会员消费查询经过会员卡号相关到会员信息,得到了彻底过错的成果。

所以,咱们千万不可把会员卡号这种事务字段当做主键

身份证号能够当会员信息表的主键吗

那可不能够运用身份证号来做主键呢?身份证号绝不会重复,并且能够与一个人一一对应起来,看起来很适合做主键呢。

但实际上,这种选择是不合适的。一方面,身份证号归于个人隐私,客户不一定会把身份证号给你,所以咱们在规划会员信息表时,会答应身份证号这个字段为空。另一方面,身份证号来做主键实在是太长了,过长的主键不只会占用过多的空间,还会导致查询功用下降,咱们会在后面打开来说。

小结

在建表的时分尽量不要用事务字段做主键。究竟,作为项目规划的技术人员,咱们谁都无法预测在项目的整个生命周期中,哪个事务字段会由于项目的事务需求而有重复,或者重用之类的状况呈现。

那么,应该运用什么来做主键呢?假如你有仔细观察我界说的产品信息表 sku_info 和订单信息表 order_info的话,你会发现我在这两张表中都界说了自增主键 pk_id在单机体系中,引荐运用自增字段做主键

单机体系引荐运用自增字段做主键

不只是《阿里巴巴 Java 开发手册》中说到建表句子里一定要运用自增主键,在许多建表规范中都有提及。下面,咱们就来看看给表加上自增字段是怎么处理上一节中呈现的问题的。

修正表结构

对于上一末节中呈现的问题,咱们只要在会员信息表 member_info中增加一个自增字段 pk_id 来做主键就能够处理了。

修正会员信息表 member_info

首要,咱们要修正一下会员信息表的结构,增加自增字段 pk_id 做主键。

第一步,先删去会员信息表的主键约束(删去主键约束,并不会删去字段)。

ALTER TABLE member_info
DROP PRIMARY KEY;
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

第二步,重新命名会员卡号,撤销 pk_ 前缀。

ALTER TABLE member_info
CHANGE pk_card_no card_no CHAR(8) NOT NULL COMMENT '会员卡号';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

第三步,增加自增字段 pk_id 为主键。

ALTER TABLE member_info
ADD pk_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

将自增字段界说为无符号,不只能够避免误存负数,还扩大了标明规模。

现在咱们来查看一下会员信息表的表结构。

DESCRIBE member_info;
+--------------+-----------------+------+-----+---------+----------------+
| Field        | Type            | Null | Key | Default | Extra          |
+--------------+-----------------+------+-----+---------+----------------+
| card_no      | char(8)         | NO   |     | NULL    |                |
| member_name  | varchar(20)     | YES  |     | NULL    |                |
| gender       | char(1)         | YES  |     | NULL    |                |
| pid          | char(18)        | YES  |     | NULL    |                |
| gmt_create   | datetime        | YES  |     | NULL    |                |
| gmt_modified | datetime        | YES  |     | NULL    |                |
| pk_id        | bigint unsigned | NO   | PRI | NULL    | auto_increment |
+--------------+-----------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

修正订单信息表 order_info

接下来,咱们要给订单信息表 order_info,增加一个新的字段 member_id,用于对应会员信息表 member_info 中的主键。

ALTER TABLE order_info
ADD member_id BIGINT UNSIGNED;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
########################
# 查看修正后的表结构
DESCRIBE order_info;
+---------------+-----------------+------+-----+---------+----------------+
| Field         | Type            | Null | Key | Default | Extra          |
+---------------+-----------------+------+-----+---------+----------------+
| pk_id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| card_no       | char(8)         | YES  |     | NULL    |                |
| sku_id        | bigint unsigned | YES  |     | NULL    |                |
| sku_price     | decimal(10,2)   | YES  |     | NULL    |                |
| sale_quantity | int unsigned    | YES  |     | NULL    |                |
| sale_amount   | decimal(10,2)   | YES  |     | NULL    |                |
| gmt_create    | datetime        | YES  |     | NULL    |                |
| gmt_modified  | datetime        | YES  |     | NULL    |                |
| member_id     | bigint unsigned | YES  |     | NULL    |                |
+---------------+-----------------+------+-----+---------+----------------+
9 rows in set (0.06 sec)

最后,咱们还要再对订单信息表 order_info 进行修正,给新增加的 member_id 字段赋值,让它指向对应的会员信息。

# 给新增加的 member_id 字段赋值,让它指向对应的会员信息
UPDATE order_info AS o, member_info AS m
SET o.member_id = m.pk_id
WHERE o.pk_id > 0
AND o.card_no = m.card_no;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
########
# 查看更新后的订单信息表 `order_info` 内的数据
SELECT * FROM order_info;
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+-----------+
| pk_id | card_no  | sku_id | sku_price | sale_quantity | sale_amount | gmt_create          | gmt_modified        | member_id |
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+-----------+
|     1 | 11000001 |      1 |     10.00 |             1 |       10.00 | 2023-06-02 00:00:00 | 2023-06-02 00:00:00 |         1 |
+-------+----------+--------+-----------+---------------+-------------+---------------------+---------------------+-----------+
1 row in set (0.01 sec)

看到这儿,或许有读者会问,能够先删去订单信息表 order_info,重建表,再刺进数据吗?不建议这样做,尽管这样能够到达同样的目的。

考虑这样的状况,order_info 表中存储了许多重要的数据,此刻是不能去删去表的。最好的办法便是用 WHERE 条件去更新指定的记载。

处理退卡危机

康复会员信息表 member_info 数据

为了复现这种状况,咱们先康复会员信息表中会员卡号 11000001 为柿子先生的个人信息。

# 康复会员信息表中会员卡号 11000001 为柿子先生的个人信息
UPDATE member_info
SET member_name = '柿子先生',
gender = '男',
pid = '123456199902107891',
gmt_create = '2023-06-01',
gmt_modified = '2023-06-01'
WHERE card_no = '11000001';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
########################
# 查看康复后的 member_info 表中的数据
SELECT * FROM member_info;
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
| card_no  | member_name  | gender | pid                | gmt_create          | gmt_modified        | pk_id |
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
| 11000001 | 柿子先生     | 男     | 123456199902107891 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |     1 |
| 11000002 | 可可小姐     | 女     | 123456199809077891 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |     2 |
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
2 rows in set (0.00 sec)

再一次退卡

这次,柿子先生退换会员卡 11000001,商家再把这张卡发给六一,咱们只要在会员信息表 member_info 中增加一条记载就能够了。

# 在会员信息表 member_info 中增加一条记载
INSERT INTO member_info
(card_no, member_name, gender, pid, gmt_create, gmt_modified)
VALUES
('11000001', '六一', '男', '123456202203017891', '2023-06-01', '2023-06-01');
Query OK, 1 row affected (0.03 sec)
########################
# 查看会员信息表中的信息
SELECT * FROM member_info;
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
| card_no  | member_name  | gender | pid                | gmt_create          | gmt_modified        | pk_id |
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
| 11000001 | 柿子先生     | 男     | 123456199902107891 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |     1 |
| 11000002 | 可可小姐     | 女     | 123456199809077891 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |     2 |
| 11000001 | 六一         | 男     | 123456202203017891 | 2023-06-01 00:00:00 | 2023-06-01 00:00:00 |     3 |
+----------+--------------+--------+--------------------+---------------------+---------------------+-------+
3 rows in set (0.04 sec)

由于会员卡号 card_no 不再是主键了,能够答应重复,因而,咱们就能够在保留会员「柿子先生」信息的同时,增加运用同一会员卡号的「六一」的信息。

查询会员出售记载正常

现在,咱们再来查询会员的出售记载,就会发现一切正常啦。

SELECT m.member_name, s.sku_name, o.sale_quantity, o.sale_amount, o.gmt_create AS trade_time
FROM order_info AS o
JOIN member_info AS m
JOIN sku_info AS s
ON (o.member_id = m.pk_id AND o.sku_id = s.pk_id);
+--------------+----------+---------------+-------------+---------------------+
| member_name  | sku_name | sale_quantity | sale_amount | trade_time          |
+--------------+----------+---------------+-------------+---------------------+
| 柿子先生     | 书本     |             1 |       10.00 | 2023-06-02 00:00:00 |
+--------------+----------+---------------+-------------+---------------------+
1 row in set (0.01 sec)

能够看到,查询成果是 2023-06-02 柿子先生买了一本书,消费 10 元,是正确的。

新的应战:更多的连锁店

咱们的超市经过一段时刻开展变得越来越好了,接下来咱们要开几家连锁店。

开展新会员的进程一般是在门店进行的,人们总是习气在购物结账时申请会员。因而,连锁店的信息体系需求增加新会员的功用,把新会员信息先存放到本地 MySQL 数据库中,再上传到总部进行汇总。

可是问题来了,咱们的会员信息表的主键都是自增的,那么各个门店新加的会员就会呈现「id」抵触的或许

比方,A 店和 B 店的会员信息表最大的 pk_id 都是 100,各自新增了一个会员,pk_id 都变成了 101。然后 A 店和 B 店把新会员信息都上传到了总部,此刻费事呈现了,两个 pk_id 都是 101,但却是不同的会员。要怎么处理这个问题呢?

分布式体系应运用大局仅有 ID

上一节的最后提出的问题,其实便是在分布式体系中怎么确保数据记载具有仅有标识,这就不得不说到分布式 ID 了。分布式 ID 能够为不同数据节点的数据记载生成大局仅有标识

本末节会先介绍一种依据数据库主键自增的分布式 ID 生成计划,更多的分布式 ID 处理计划的介绍,我将在另一篇文章具体打开。

总部数据库自增生成分布式 ID

咱们能够撤销会员信息表 member_info 的主键 pk_id 的自增特点,改成在增加新会员时对 pk_id 赋值。

然后,在总部数据库体系中,专门预备一张表 sequence_id_generator 用于生成大局仅有 ID。当门店需求增加会员的时分,要先到总部的这张表中,更新 pk_id 的值为最大值 + 1,并将新的值作为新会员的 pk_id 值。

如此一来,各个门店增加会员的时分,都从同一个总部的 sequence_id_generator 表中获取新会员的 pk_id 值,处理了各门店增加会员时会员编号抵触的问题,同时也避免了运用事务字段导致数据过错的问题。

创立生成分布式 ID 的表 sequence_id_generator

# 创立表
CREATE TABLE `sequence_id_generator`
(
    `pk_id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `cert` VARCHAR(255) NOT NULL UNIQUE KEY DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
########################
# 查看表结构
DESCRIBE sequence_id_generator;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| pk_id | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| cert  | varchar(255)    | NO   | UNI |         |                |
+-------+-----------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

pk_id 是自增主键,用于指明当时最大的会员编号。cert是事务凭据字段,创立了仅有索引,确保其仅有性,用于不同事务刺进或修正数据。

获取新会员的 id 值

当咱们想要获取新会员的 id 值时,需求履行显式事务(事务中的句子有必要悉数履行,一条失利则悉数回滚)来获取,具体如下所示:

BEGIN;
REPLACE INTO sequence_id_generator (cert) VALUES ('member_info');
SELECT LAST_INSERT_ID(); # 获取表中最大的自增值
COMMIT;

运用 REPLACE INTO 刺进数据的流程如下:

第一步:测验将数据刺进到表中;

第二步:假如主键或仅有索引字段呈现重复数据过错而刺进失利时,先从表中删去含有重复关键字值的抵触行,然后再次测验把数据刺进到表中。

模仿获取新会员的 id 值

下面的动图是运用该处理计划模仿两家门店增加会员时,从总部获取新会员 id 值。

MySQL 避「坑」指南 —— 你能设置出正确的主键吗?

起先,sequence_id_generator 表中 pk_id 的最大值为 5,标明现在一切门店中最大的会员 id 值为 5。

现在 A 门店(左边)要获取新会员的 id 值,开端履行事务 A,与此同时 B 门店(右边)也要获取新会员的 id 值,开端履行事务 B。

由于事务 A 先履行了 REPLACE INTO 的刺进操作,事务 B 只能堵塞等候。

事务 A 履行 SELECT LAST_INSERT_ID(); 成功获取到了新会员的 id 值为 6,然后履行 COMMIT; 提交事务。

然后事务 B 开端履行 REPLACE INTO 操作与 SELECT LAST_INSERT_ID(); 成功获取到了新会员的 id 值为 7,然后履行 COMMIT; 提交事务。

如此一来,A 门店和 B 门店新增加的会员 id 值都是仅有的,成功处理了会员 id 值抵触的问题。

计划的优缺陷

优点

完成简略,会员的 id 值是有序递增的,占用的存储空间少

缺陷

  1. 一个十分显着的缺陷,并发才能很差。一个事务履行时,另一个事务会被堵塞。

  2. 存在安全问题,试想一下假如这个 id 仅有标示的是订单呢?依据 id 的递增规则就能够推算出每天的订单量,会泄露商业秘要!

  3. 每次获取 id 都要拜访一次数据库,增加了对数据库的压力,获取速度也慢。

  4. 存在数据库单点宕机危险。一切门店增加新会员都要拜访总部的数据库,当门店数量许多时,总部数据库很简略就宕机了。

  5. 后期能够采用水平扩展的数据库集群,并经过规定 id 的起始值和自增步长的方法来处理数据库单点压力问题。尽管这种处理计划处理了单点问题,但仍然存在缺陷,不利于后续扩容,直接运用数据库抗流量,无法满意高并发场景。

咱们现已经过一个简略的处理计划开始了解了分布式 ID,下面开端正式介绍它。

总结

今日,和咱们一同探讨了怎么给数据表设置出正确的主键,介绍了给表界说主键的优点,运用主键要注意的事情。

要点强调了尽量不要运用事务字段做主键,由于无法预测未来会不会由于事务需求,而呈现事务字段重复或者重用的状况。

在单机体系中,引荐运用自增字段做主键。可是,假如有多台服务器(分布式体系),各自录入数据,那就无法适用了。由于假如需求兼并每台服务器录入的数据,或许呈现主键重复的问题。

分布式 ID 能够很好地处理这个问题,咱们介绍了一种简略的计划,在总部的数据库中专门有一个负责生成大局仅有 ID 的表,一切门店想要增加新会员的时分要从总部的这张表中获取 ID 值,这样就能够确保一切门店新增加的会员都有大局仅有的 ID 了。

假如以上内容有协助到你,希望点赞收藏加重视,您的鼓励和协助是我更新的动力!以上便是悉数内容,咱们下篇文章再会!

参考资料

  1. 《MySQL 必知必会》 —— Ben Forta
  2. 《MySQL 必知必会》 —— 朱晓峰
  3. 《MySQL 45 讲》 —— 林晓斌
  4. 《阿里巴巴 Java 开发手册 1.4.0》
  5. MySQL 官方文档
  6. JavaGuide
  7. 廖雪峰的官方网站