原文作者： Laurenz Albe
翻譯：多米爸比
原文鏈接：https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/

經(jīng)常有客戶向我咨詢自動(dòng)生成主鍵的最佳實(shí)踐方法。本文我將探討幾種可選項(xiàng)，并給出推薦。

為什么需要自動(dòng)生成主鍵？

每個(gè)表都需要一個(gè)主鍵。因?yàn)樵陉P(guān)系型數(shù)據(jù)庫(kù)中，識(shí)別每個(gè)數(shù)據(jù)表的行是很重要的。關(guān)于這一點(diǎn)如果還存疑，我們?nèi)ゾW(wǎng)上搜索會(huì)發(fā)現(xiàn)有大量的人都在提問(wèn)請(qǐng)求幫助他們解決如何刪除重復(fù)數(shù)據(jù)的問(wèn)題。

好的建議是我們不僅要對(duì)表選擇一個(gè)唯一的主鍵，并且在其生命周期內(nèi)不要改變主鍵值。這是因?yàn)槠渌淼耐怄I通常會(huì)參考這張表的主鍵來(lái)進(jìn)行約束，另外其他相關(guān)引用該主鍵值的地方也會(huì)出現(xiàn)問(wèn)題。

有的時(shí)候，創(chuàng)建表時(shí)，可以從表字段中找到一個(gè)原生主鍵，例如身份證號(hào)碼。但是通常情況下，是沒(méi)有這樣的字段，此時(shí)我們必須構(gòu)造一個(gè)主鍵。也有部分人認(rèn)為表中即使有原生字段可以作為主鍵使用，也應(yīng)該獨(dú)立構(gòu)建，不應(yīng)該依賴業(yè)務(wù)字段，但我不會(huì)加入這樣爭(zhēng)論中。

一、系統(tǒng)提供的自動(dòng)生成主鍵方法

有兩個(gè)基礎(chǔ)的方法：

1.使用sequence序列生成主鍵

sequence是一個(gè)數(shù)據(jù)庫(kù)對(duì)象，它的唯一目的就是生成不重復(fù)的數(shù)字。它是使用內(nèi)部的遞增計(jì)數(shù)器實(shí)現(xiàn)。

sequence極大的優(yōu)化了并發(fā)訪問(wèn)，并且不會(huì)出現(xiàn)重復(fù)的數(shù)字。然而，當(dāng)有很多并發(fā)的SQL語(yǔ)句訪問(wèn)同一個(gè)sequence時(shí)可能會(huì)成為瓶頸，因此，sequence提供了一個(gè)CACHE選項(xiàng)，它會(huì)在數(shù)據(jù)庫(kù)會(huì)話期間一次性分配多個(gè)值。

sequence通常不會(huì)遵循事務(wù)的規(guī)則：如果一個(gè)事務(wù)進(jìn)行回滾，sequence不會(huì)重置它的計(jì)數(shù)器。這也是為了性能所需，并且也不會(huì)構(gòu)成問(wèn)題。如果你需要生成一個(gè)無(wú)縫的數(shù)字序列，sequence不是一個(gè)正確的選擇，此時(shí)你需要對(duì)序列值做低效的排序和更復(fù)雜的技術(shù)處理。

為了從一個(gè)sequence中獲取下一個(gè)值，我們需要用到這樣的函數(shù)：

SELECT nextval('sequence_name');

更多操作sequence的函數(shù)請(qǐng)參考文檔。

2.生成UUIDs

一個(gè)UUID（universally unique identifier）是由算法生成的128-bit的數(shù)字,算法會(huì)保證唯一性。有幾個(gè)標(biāo)準(zhǔn)化的算法。在PostgreSQL中，有兩種方式生成UUIDs函數(shù)。

• uuid-ossp擴(kuò)展提供了生成UUIDs的函數(shù)。注意，由于"-"在名稱中，需要使用雙引號(hào)名稱來(lái)創(chuàng)建擴(kuò)展( CREATE EXTENSION “uuidossp”; )
• 從PostgreSQL v13版本開始，可以使用內(nèi)核函數(shù) gen_random_uuid()來(lái)生成v4版本的UUIDs。

注意：對(duì)于UUIDs我們應(yīng)該始終使用PostgreSQL的uuid數(shù)據(jù)類型。而不應(yīng)該把uuid數(shù)據(jù)類型轉(zhuǎn)換成string或numberic類型，否則我們將會(huì)浪費(fèi)存儲(chǔ)空間以及失去很好的性能。

二、自定義的自動(dòng)生成主鍵方法

有四種方式可以使用自動(dòng)生成值來(lái)定義字段：

1.使用DEFAULT從句

我們可以參考sequences和UUIDs的方法。下面是例子：

CREATE TABLE has_integer_pkey (
 id bigint DEFAULT nextval('integer_id_seq') PRIMARY KEY,
 ...
);

CREATE TABLE has_uuid_pkey (
 id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
 ...
);

當(dāng)我們的INSERT語(yǔ)句中不顯示指定某列時(shí)，PostgreSQL會(huì)使用該列的DEFAULT值。

2.使用serial和bigserial偽類型

這種方法其實(shí)是使用sequence以及設(shè)置DEFAULT默認(rèn)值的一個(gè)快捷方式，使用這種方法，我們可以定義表如下：

CREATE TABLE uses_serial (
 id bigserial PRIMARY KEY,
 ...
);

其實(shí)上面的方式與下面的創(chuàng)建方式是等價(jià)的：

CREATE TABLE uses_serial (
 id bigint PRIMARY KEY,
 ...
);

CREATE SEQUENCE uses_serial_id_seq
 OWNED BY uses_serial.id;

ALTER TABLE uses_serial ALTER id
 SET DEFAULT nextval('uses_serial_id_seq');

"OWNED BY"從句在表字段和序列間增加了一個(gè)依賴，因此在刪除表字段時(shí)會(huì)自動(dòng)刪除序列。

使用serial類型會(huì)創(chuàng)建一個(gè)integer類型的字段，而使用bigserial類型會(huì)創(chuàng)建一個(gè)bigint類型的
字段。（使用smallserial類型會(huì)創(chuàng)建一個(gè)smallint類型的字段）

3.使用identity columns

這是使用sequence的另一種形式，因?yàn)镻ostgreSQL實(shí)際是使用sequence來(lái)實(shí)現(xiàn)identity columns

CREATE TABLE uses_identity (
 id bigint GENERATED ALWAYS AS IDENTITY
 PRIMARY KEY,
 ...
);

還有一種形式是"GENERATED BY DEFAULT AS IDENTITY"，也是一樣的效果，除了當(dāng)我們顯式插入指定列的值時(shí)（類似一個(gè)DEFAULT語(yǔ)句）不會(huì)得到一個(gè)報(bào)錯(cuò)信息。

譯者注：
GENERATED ALWAYS AS IDENTITY聲明的字段，插入語(yǔ)句不能顯式賦值，否則會(huì)報(bào)錯(cuò)。
GENERATED BY DEFAULT AS IDENTITY聲明的字段，插入語(yǔ)句可以顯式賦值，會(huì)使用賦值覆蓋。

我們也可以對(duì)identity columns指定sequence選項(xiàng)：

CREATE TABLE uses_identity (
 id bigint GENERATED ALWAYS AS IDENTITY
 (MINVALUE 0 START WITH 0 CACHE 20)
 PRIMARY KEY,
 ...
);

4.使用BEFORE INSERT觸發(fā)器

這個(gè)和DEFAULT默認(rèn)值類似，但它允許我們強(qiáng)制覆蓋用戶插入的值。當(dāng)然觸發(fā)器最大的缺點(diǎn)是對(duì)性能有影響。

自動(dòng)生成主鍵：integer(serial)和bigint(bigserial)我們?cè)撨x擇使用哪一種？

答案是我們應(yīng)該使用bigint。

integer占4個(gè)字節(jié)，而bigint占8個(gè)字節(jié)。但是：

• 如果我們有一個(gè)小表，integer是足夠的，浪費(fèi)的4字節(jié)也無(wú)關(guān)緊要。然而并不是設(shè)計(jì)的每個(gè)表都是小表。
• 如果我們有一個(gè)大表，可能會(huì)超過(guò)integer的最大值2147483647。注意這也可能發(fā)生在包含較少行的表上：我們可能會(huì)刪除行，sequence值可能會(huì)被回滾的事務(wù)消耗。

如今，在生產(chǎn)數(shù)據(jù)庫(kù)中把一個(gè)大表的主鍵字段從integer轉(zhuǎn)變成bigint類型而不使其消耗額外的停機(jī)時(shí)間是相當(dāng)復(fù)雜的，此種經(jīng)歷的痛苦我們應(yīng)該盡量避免。

使用bigint，可以肯定我們不會(huì)超過(guò)bigint的最大值9223372036854775807：即使我們以每秒10000行的速度不停的插入數(shù)據(jù)，我們至少需要三千萬(wàn)年才會(huì)達(dá)到上限。

自動(dòng)生成主鍵：bigserial和identity column我們?cè)撨x擇使用哪一種？

答案是我們應(yīng)該使用identity column，除非我們?yōu)榱思嫒菖f版本的PostgreSQL。

identity column是從PostgreSQL v10開始引入的（原文寫的是v11,應(yīng)該是作者記錯(cuò)了），它對(duì)比bigserial有兩個(gè)優(yōu)點(diǎn)：

• identity column遵從SQL標(biāo)準(zhǔn)，bigserial屬性是PostgreSQL里的語(yǔ)法。
• 如果我們使用GENERATED ALWAYS AS IDENTITY，如果我們?cè)诓迦胝Z(yǔ)句里顯式賦值會(huì)得到系統(tǒng)的錯(cuò)誤提示。這能讓我們規(guī)避一個(gè)常見的坑：我們顯式插入的值會(huì)與系統(tǒng)將來(lái)自動(dòng)生成的值產(chǎn)生沖突，導(dǎo)致應(yīng)用程序出現(xiàn)錯(cuò)誤。

所以除非我們?yōu)榱酥С諴ostgreSQL v10以下的版本，否則我們沒(méi)有理由繼續(xù)使用bigserial。

自動(dòng)生成主鍵：bigint和uuid我們?cè)撨x擇使用哪一種？

我的建議是使用sequence bigint，除非我們?cè)贁?shù)據(jù)庫(kù)里使用了sharding分片或者有分布式的場(chǎng)景需求。

實(shí)際的區(qū)別

bigint具有明顯的優(yōu)點(diǎn)：

• bigint使用8字節(jié)存儲(chǔ)，而uuid使用16字節(jié)存儲(chǔ)。
• 從sequence取值的代價(jià)比計(jì)算獲取UUID值要廉價(jià)的多。

使用sequence的一個(gè)缺點(diǎn)是它限制在單個(gè)數(shù)據(jù)庫(kù)的單個(gè)對(duì)象中。所以如果你使用分片，把數(shù)據(jù)分布到多個(gè)數(shù)據(jù)庫(kù)時(shí)不能使用sequence。在分片場(chǎng)景下，UUIDs才是合理的選擇。（雖然我們也可以用sequence來(lái)解決：例如我們可以在定義的sequence時(shí)使用INCREMENT大于1以及不同的START步長(zhǎng)值，但是這樣我們?cè)黾臃謪^(qū)的操作上問(wèn)題會(huì)更多。）

當(dāng)然，如果我們的主鍵不是數(shù)據(jù)庫(kù)自動(dòng)生成的，而是由不同應(yīng)用服務(wù)里的應(yīng)用程序創(chuàng)建時(shí)，我們也可以選擇UUIDs。

臆想的區(qū)別

也有人爭(zhēng)論使用UUIDs會(huì)更好些，因?yàn)榭梢詫⒅麈I索引分散寫入到不同的數(shù)據(jù)塊。這被認(rèn)為會(huì)減少寫入競(jìng)爭(zhēng)、寫入更加均衡以及更少的碎片化索引。第一點(diǎn)是正確的，但這其實(shí)是一個(gè)缺點(diǎn)，實(shí)際我們需要緩存整個(gè)索引來(lái)提高性能。第二點(diǎn)完全是錯(cuò)誤的，眾所周知B-tree是總是保持平衡。同時(shí)PostgreSQL v11有一個(gè)新的變化：使用序列單調(diào)遞增的填充索引比隨機(jī)插入更加高效（但是后續(xù)的刪除操作肯定是會(huì)引起碎片化的）。簡(jiǎn)而言之，上述優(yōu)點(diǎn)要么是微不足道，要么是客戶不存在的，事實(shí)上比均衡更加有影響的因素是uuid使用兩倍的存儲(chǔ)，這會(huì)讓索引變得更大，寫入也會(huì)變大，并且占用更多的緩存。

基準(zhǔn)測(cè)試：bigint VS uuid

我的同事Kaarel不久前做了一個(gè)小的性能測(cè)試發(fā)現(xiàn)：大表join操作時(shí)uuid比bigint會(huì)慢一些。

因此我決定使用如下的兩個(gè)表做少量的insert-only的測(cè)試：

CREATE UNLOGGED TABLE test_bigint (
   id bigint GENERATED ALWAYS AS IDENTITY (CACHE 200) PRIMARY KEY
);
 
CREATE UNLOGGED TABLE test_uuid (
   id uuid DEFAULT gen_random_uuid() PRIMARY KEY
);

我用自己的筆記本（SSD,8核）使用pgbench工具模擬6個(gè)并行的客戶端，每個(gè)客戶端執(zhí)行1000個(gè)事務(wù)，對(duì)下面自定義的腳本持續(xù)運(yùn)行5分鐘。

INSERT INTO test_bigint /* or test_uuid */ DEFAULT VALUES;

自動(dòng)生成主鍵性能對(duì)比 bigint VS uuid

很明顯bigint性能更好，但是差異也并不是特別懸殊。

結(jié)論

sequence和UUIDs都可以用作自動(dòng)生成主鍵。除非在單個(gè)數(shù)據(jù)庫(kù)之外生成主鍵，否則請(qǐng)使用identity columns，并確保主鍵字段的類型為bigint。

我的相關(guān)文章

PostgreSQL新主鍵算法Nano ID介紹

保持聯(lián)系

現(xiàn)組建了一個(gè)PG樂(lè)知樂(lè)享交流群，歡迎關(guān)注文章的小伙伴加微信進(jìn)群吹牛嘮嗑，交流技術(shù)。