原文作者: 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 | uuid | |
|---|---|---|
| inserts per second | 107090 | 74947 |
| index growth per row | 30.5 bytes | 41.7 bytes |
很明顯bigint性能更好,但是差異也并不是特別懸殊。
結(jié)論
sequence和UUIDs都可以用作自動(dòng)生成主鍵。除非在單個(gè)數(shù)據(jù)庫(kù)之外生成主鍵,否則請(qǐng)使用identity columns,并確保主鍵字段的類型為bigint。
我的相關(guān)文章
保持聯(lián)系
現(xiàn)組建了一個(gè)PG樂(lè)知樂(lè)享交流群,歡迎關(guān)注文章的小伙伴加微信進(jìn)群吹牛嘮嗑,交流技術(shù)。





