在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，表字段的類型選擇至關(guān)重要。它不僅影響數(shù)據(jù)庫(kù)的存儲(chǔ)效率和性能優(yōu)化，還影響數(shù)據(jù)的完整性和擴(kuò)展性。
本文分析了不同應(yīng)用場(chǎng)景下可以使用的 PostgreSQL 數(shù)據(jù)類型，以及它們的存儲(chǔ)需求和優(yōu)缺點(diǎn)。
布爾值
| 數(shù)據(jù)類型 | BOOLEAN（推薦） | SMALLINT | BIT（不推薦） | CHAR(1) |
|---|
| 優(yōu)點(diǎn) | 存儲(chǔ)需求最小（1 字節(jié)）；語(yǔ)義清晰；支持輸入：true, yes, on, 1, false, no, off, 0。 | 支持算術(shù)運(yùn)算；可以存儲(chǔ)真或假之外的數(shù)值。 | 無(wú) | 可以存儲(chǔ)任何字符，輸入靈活。 |
| 缺點(diǎn) | 只能存儲(chǔ)真或者假。 | 語(yǔ)義不夠清晰；需要通過(guò)約束實(shí)現(xiàn)布爾邏輯；需要更多存儲(chǔ)（2 字節(jié)）。 | 需要更多存儲(chǔ)（6 字節(jié)）；只能存儲(chǔ) 1/0；語(yǔ)義不夠清晰；不易查詢。 | 需要通過(guò)約束實(shí)現(xiàn)布爾邏輯，需要更多存儲(chǔ)（2 字節(jié)）。 |
| 百萬(wàn)行存儲(chǔ)空間 | 1 MB | 2 MB | 6 MB | 2 MB |
UUID
| 數(shù)據(jù)類型 | UUID（推薦） | TEXT | CHAR(36) |
|---|
| 優(yōu)點(diǎn) | 存儲(chǔ)優(yōu)化（16 字節(jié)）；校驗(yàn) UUID 格式；索引優(yōu)化。 | 實(shí)現(xiàn)簡(jiǎn)單，格式靈活。 | 固定長(zhǎng)度。 |
| 缺點(diǎn) | 無(wú) | 更多存儲(chǔ)（36 字節(jié) + 1 字節(jié)）；索引低效；需要通過(guò)約束驗(yàn)證格式。 | 更多存儲(chǔ)（36 字節(jié) + 1 字節(jié)）；索引低效；需要通過(guò)約束驗(yàn)證格式。 |
| 百萬(wàn)行存儲(chǔ)空間 | 16 MB | 37 MB | 37 MB |
備注：如果需要使用 UUID 作為主鍵，推薦使用 UUIDv7 這種基于時(shí)間排序的算法。隨機(jī)生成的 UUID 可能導(dǎo)致 B-樹(shù)分裂問(wèn)題。
MD5
| 數(shù)據(jù)類型 | UUID（推薦） | BYTEA | TEXT | CHAR(32) |
|---|
| 優(yōu)點(diǎn) | 存儲(chǔ)優(yōu)化（16 字節(jié)）；索引優(yōu)化。 | 存儲(chǔ)優(yōu)化（16 字節(jié) + 4 字節(jié)）；索引優(yōu)化。 | 實(shí)現(xiàn)簡(jiǎn)單，格式靈活。 | 固定長(zhǎng)度。 |
| 缺點(diǎn) | MD5 顯示成 UUID 格式。 | 二進(jìn)制數(shù)據(jù)不方便處理。 | 更多存儲(chǔ)（32 字節(jié) + 4 字節(jié)）；索引低效。 | 更多存儲(chǔ)（32 字節(jié) + 4 字節(jié)）；索引低效。 |
| 百萬(wàn)行存儲(chǔ)空間 | 16 MB | 20 MB | 36 MB | 36 MB |
MD5 轉(zhuǎn)換成二進(jìn)制類型的方法如下：
SELECT DECODE(MD5('abc'), 'hex');
日期時(shí)間
| 數(shù)據(jù)類型 | DATE（推薦） | TIME | TIMESTAMP | TIMESTAMPTZ（推薦） |
|---|
| 優(yōu)點(diǎn) | 高效存儲(chǔ)日期；語(yǔ)義清晰 | 存儲(chǔ)于日前無(wú)關(guān)的時(shí)間信息 | 同時(shí)存儲(chǔ)日期和時(shí)間 | 存儲(chǔ)包含時(shí)區(qū)的日期和時(shí)間，支持全球化；兼容夏令時(shí) |
| 缺點(diǎn) | 不包含時(shí)間信息 | 不包含日期信息 | 沒(méi)有時(shí)區(qū)信息 | 無(wú) |
| 百萬(wàn)行存儲(chǔ)空間 | 4 MB | 8 MB | 8 MB | 8 MB |
二進(jìn)制數(shù)據(jù)
| 數(shù)據(jù)類型 | BYTEA（推薦） | TEXT |
|---|
| 優(yōu)點(diǎn) | 高效的二進(jìn)制存儲(chǔ)和處理 | 應(yīng)用程序處理簡(jiǎn)單 |
| 缺點(diǎn) | 應(yīng)用程序需要編碼/解碼 | 存儲(chǔ)效率低 |
| 百萬(wàn)行存儲(chǔ)空間 | 2 MB - 2.1 PB | 1 MB - 1.1 PB |
備注：對(duì)于文檔、圖像、音頻、視頻等二進(jìn)制數(shù)據(jù)，不建議使用數(shù)據(jù)庫(kù)存儲(chǔ)，可以使用文件系統(tǒng)存儲(chǔ)這些內(nèi)容，并且在數(shù)據(jù)庫(kù)中記錄文件的訪問(wèn)地址。
貨幣數(shù)字
| 數(shù)據(jù)類型 | MONEY（不推薦） | NUMERIC(15,2)（推薦） | BIGINT（推薦） | FLOAT（不推薦） |
|---|
| 優(yōu)點(diǎn) | 內(nèi)置貨幣符號(hào)；方便存儲(chǔ)財(cái)務(wù)數(shù)據(jù)；高效的存儲(chǔ)和處理 | 高精度存儲(chǔ)；可以靈活設(shè)置存儲(chǔ)精度 | 可以將小數(shù)存儲(chǔ)為整數(shù)；計(jì)算性能好 | 計(jì)算性能最好 |
| 缺點(diǎn) | 只能支持一種貨幣符號(hào)；只能支持小數(shù)點(diǎn)后兩位 | 占用更多存儲(chǔ)；計(jì)算性能不如浮點(diǎn)數(shù) | 應(yīng)用程序需要進(jìn)行小數(shù)轉(zhuǎn)換，例如乘以 100 將小數(shù)點(diǎn)后的分轉(zhuǎn)換為整數(shù) | 非精確數(shù)字；計(jì)算時(shí)存在精度損失 |
| 百萬(wàn)行存儲(chǔ)空間 | 8 MB | 11 MB | 8 MB | 4 MB/8 MB |
枚舉值
| 數(shù)據(jù)類型 | ENUM | TEXT | SMALLINT（推薦） | SMALLINT查找表（推薦） |
|---|
| 優(yōu)點(diǎn) | 提供數(shù)據(jù)庫(kù)級(jí)別校驗(yàn)；存儲(chǔ)高效；可讀性高 | 使用靈活，方便增加新的枚舉值 | 存儲(chǔ)高效；方便增加新的枚舉值 | 存儲(chǔ)高效；方便增加新的枚舉值；通過(guò)查找表可以獲取更多信息 |
| 缺點(diǎn) | 刪除某個(gè)枚舉時(shí)比較復(fù)雜；對(duì)于動(dòng)態(tài)枚舉值不夠靈活 | 沒(méi)有數(shù)據(jù)校驗(yàn)，可能導(dǎo)致數(shù)據(jù)不一致；可能占用更多存儲(chǔ) | 沒(méi)有數(shù)據(jù)校驗(yàn)；含義不明確，應(yīng)用程序需要解釋數(shù)據(jù)含義 | 需要關(guān)聯(lián)查詢獲取枚舉值含義，增加了復(fù)雜度 |
| 百萬(wàn)行存儲(chǔ)空間 | 4 MB | >=2 MB | 2 MB | 2 MB |
文本
| 數(shù)據(jù)類型 | TEXT（推薦） | VARCHAR(N) | CHAR(N) （不推薦） |
|---|
| 優(yōu)點(diǎn) | 幾乎沒(méi)有長(zhǎng)度限制；靈活易用 | 限制了最大長(zhǎng)度 | 固定長(zhǎng)度，占用固定大小 |
| 缺點(diǎn) | 需要通過(guò)檢查約束限制長(zhǎng)度 | 需要提前定義最大長(zhǎng)度；超長(zhǎng)時(shí)出現(xiàn)錯(cuò)誤 | 使用空格填充，可能浪費(fèi)空間；不適用于變長(zhǎng)字符串 |
| 百萬(wàn)行存儲(chǔ)空間 | 2 MB - 1.1 PB | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
PostgreSQL 數(shù)據(jù)庫(kù)中這三種字符串類型沒(méi)有明顯的性能差異，只是 CHAR 類型可能浪費(fèi)一些存儲(chǔ)空間，而指定字段的最大長(zhǎng)度則需要消耗一些 CPU 執(zhí)行長(zhǎng)度校驗(yàn)。
數(shù)字
| 數(shù)據(jù)類型 | NUMERIC | FLOAT |
|---|
| 優(yōu)點(diǎn) | 超大范圍精確數(shù)字；適合金融財(cái)務(wù)數(shù)據(jù) | 占用更少存儲(chǔ)；計(jì)算速度更快，適合科學(xué)計(jì)算 |
| 缺點(diǎn) | 占用更多存儲(chǔ)；計(jì)算更慢 | 不夠精確，可能存在舍入誤差 |
| 百萬(wàn)行存儲(chǔ)空間 | 5 MB - 1 TB | 4 MB/8 MB |
整數(shù)
| 數(shù)據(jù)類型 | SMALLINT | INTEGER | BIGINT |
|---|
| 優(yōu)點(diǎn) | 占用空間最少 | 占用空間較少，支持較大范圍數(shù)字 | 支持超大范圍數(shù)字 |
| 缺點(diǎn) | 支持的數(shù)字范圍小 | 無(wú) | 占用空間最大 |
| 百萬(wàn)行存儲(chǔ)空間 | 2 MB | 4 MB | 8 MB |
SAMLLINT 支持的數(shù)字范圍從 -32768 到 32767，INTEGER 支持的數(shù)字范圍從 -2147483648 到 2147483647，BIGINT 支持的數(shù)字范圍從 -9223372036854775808 到 9223372036854775807。
JSON
| 數(shù)據(jù)類型 | JSON | JSONB（推薦） |
|---|
| 優(yōu)點(diǎn) | 存儲(chǔ)原始文本，保留空白符、順序、重復(fù)鍵 | 二進(jìn)制存儲(chǔ)，優(yōu)化了查詢性能；支持 GIN 索引 |
| 缺點(diǎn) | 讀取速度更慢；不支持高效索引 | 寫(xiě)入時(shí)需要更多解析操作；可能需要更多元數(shù)據(jù)存儲(chǔ) |
| 百萬(wàn)行存儲(chǔ)空間 | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
數(shù)組
| 數(shù)據(jù)類型 | ARRAY | JSONB ARRAY |
|---|
| 優(yōu)點(diǎn) | 單個(gè)字段存儲(chǔ)多個(gè)值，優(yōu)化特定應(yīng)用 | 靈活存儲(chǔ)多個(gè)值；支持索引 |
| 缺點(diǎn) | 查詢和索引復(fù)雜；可能占用更多存儲(chǔ) | 需要額外處理 JSON；可能占用更多存儲(chǔ) |
| 百萬(wàn)行存儲(chǔ)空間 | 12 MB - 1.1 PB | 8 MB - 1.1 PB |
該文章在 2024/10/30 14:35:38 編輯過(guò)
400 186 1886
