設計表時，如何選擇正確的資料型別

菜鸟额發表於2024-03-10
資料型別

前言

假設現在有一個需求，需要建立一張orders表來儲存客戶的訂單資訊。假設表結構如下:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY, -- 訂單ID，主鍵，自增
  customer_id INT NOT NULL,                -- 客戶ID，假設已在其他表中定義
  order_date DATETIME NOT NULL,            -- 訂單日期和時間
  total_amount DECIMAL(10, 2) NOT NULL,    -- 訂單總金額，保留兩位小數
);

這裡需要設計一個status 欄位，用來表示訂單的當前狀態。訂單狀態可以是以下幾種：待支付、已支付、發貨中、已完成、已取消。

這個status欄位在業務邏輯中非常重要，因為它會頻繁地用於查詢，更新等，方便使用者檢視自己處於不同狀態的訂單。

此時我們應該好好設計該欄位，在保證其滿足基本業務需求的同時，效能和擴充套件性這些方面也要充分考慮，避免之後較大的維護成本以及效能開銷。

回到這個欄位的設計來說，現在我們就有幾種不同的資料型別選擇來儲存這個status欄位，而且也能夠基本得滿足業務要求，比如VARCHAR 型別，ENUM 型別，TINYINT 型別，具體設計如下:

  • VARCHAR

我們可以選擇使用字串型別VARCHAR來直接儲存狀態文字（如"待支付"、"已支付"等）。

  • ENUM

我們可以使用列舉型別ENUM('待支付', '已支付', '發貨中', '已完成', '已取消')來儲存這些狀態。

  • TINYINT

我們可以選擇使用較小的整數型別TINYINT，併為每種狀態分配一個數字程式碼(如1=待支付，2=已支付等）。

這種情況下，status 欄位時應該使用VARCHAR，ENUM 還是TINYINT 型別呢?

在平時開發設計時，我們總是不可避免說會遇到類似這種選擇。這種情況下我們應該怎麼抉擇呢? 能從哪些方面考慮呢？

資料型別選擇的原則

在 MySQL 資料表設計時，選擇合適的資料型別對於提高 資料庫 的效能是至關重要且基礎的。下面介紹一些簡單的原則，幫助我們在遇到選擇時，能過做出更好的選擇。

更小的更好

選擇能夠在滿足需求的前提下，佔用最小儲存空間的資料型別。

在執行查詢和其他操作時會將資料載入到記憶體中，使用較小的資料型別可以減少記憶體使用，從而允許更多的資料同時駐留在記憶體中，提高資料的處理速度。同時它們佔用更少的磁碟、CPU快取，並且處理時需要的CPU週期也更少。

同時，資料型別的大小也會對索引的效能產生影響。較小的資料型別也可以提高索引的效率，欄位佔據空間越小，該欄位對應的索引更小，可以提高索引的查詢速度並減少磁碟I/O操作。這對查詢效能有顯著影響，尤其是對於大量資料和高負載的系統來說。

舉例來說，如果你知道一個欄位的值不會超過 255，那麼使用 TINYINT 而不是 INT，這個不管是在儲存空間還是查詢效率上來說，都是TINYINT的效能更好。

但是要確保沒有低估需要儲存的值的範圍，因為擴充套件資料型別的範圍是一個非常耗時和痛苦的操作，也降低了系統的可維護性和擴充套件性，這樣子就得不償失了。

優先使用內建型別

在資料庫設計中，應該優先使用資料庫的內建型別的表示，而不是一些通用型別。

優先使用資料庫內建型別有以下好處:

效能方面，資料庫都會對內建資料型別進行了最佳化，以提供更好的儲存和檢索效能。例如，內建的數值和日期型別通常比通用的字串型別在索引、排序和比較操作中表現得更好。

資料完整性方面，內建資料型別通常包括資料驗證功能，可以在資料插入或更新時自動進行型別檢查。這有助於防止無效資料的輸入，從而維護資料的一致性和準確性。舉例來說，DATE型別的欄位將自動拒絕任何不符合日期格式的資料。

具有更高的儲存效率。因為它們是針對所儲存資料的性質量身定做的。例如，整數型別（如 INT 或 BIGINT）通常比等效的字串表示（如數字的文字形式）佔用更少的儲存空間。

簡化查詢。內建資料型別可以簡化查詢語句的編寫，因為它們不需要額外的轉換或格式化函式。例如，使用 DATE 型別可以直接比較日期，而不需要將字串轉換為日期。

因此，在資料庫設計中，我們應該優先使用系統內建的資料型別，只有在內建欄位不滿足需求的情況下，才考慮一些通用資料型別來表示。

優先使用簡單型別

簡單和複雜資料型別的界定並沒有一個嚴格的標準，這些術語更多地是相對的，並且取決於上下文。

這裡的簡單和複雜是相對於操作和處理這些資料型別所需的資源來說的。

當我們說簡單型別時，我們通常是指這些型別在資料庫中的處理更加直接和高效。而複雜型別則可能需要更多的處理步驟，更多的資源，或者更復雜的內部表示。

當我們在資料庫設計時，某個欄位型別有多種選擇，那麼其中需要更多的儲存空間，查詢排序等操作更為複雜，效能更為低下的，此時該欄位型別在該場景下就被界定為複雜型別。

這裡舉個例子說明一下，假設我們在使用者資訊表中有一個欄位用於儲存性別。性別通常只有幾個固定的選項，例如“男”、“女”、“未指定”等。

在這種情況下，可以使用整數來表示，約定一個小的整數來表示性別，比如，0：未指定，1：男，2：女。

也可以使用字串型別 (VARCHAR) 來儲存性別資訊。這種方式在表達上更直觀，但它比簡單資料型別更復雜、更耗費資源。

在這個場景下，字串型別 相對於 整數型別 來說，其需要佔用更多的儲存空間，查詢排序時也需要耗費更多的cpu和記憶體資源，此時字串型別將被定義為複雜型別，整數型別相對的就是簡單型別了。

在設計資料庫時，考慮使用簡單資料型別而不是複雜資料型別可以在確保足夠表達能力的同時，提高資料庫操作的效率。

考慮資料精度

在資料庫設計時，選擇合適的資料精度是非常重要的。選擇過高或過低的精度都會帶來問題：過高的精度可能會導致不必要的儲存空間浪費和效能下降，而過低的精度則可能導致資料失真，無法滿足業務需求。

經常的一個常見誤區是資料精度越大越好，但是業務場景並不需要那麼精確，此時使用過高的精度意味著需要更多的儲存空間，這在大型資料庫中尤其成問題，可能導致額外的儲存成本。更高的精度可能會導致計算速度變慢，尤其是在進行數學運算、排序和索引操作時。

此時應該是從業務需求出發，選擇最為合適的資料精度，既滿足了業務要求，也避免了儲存空間的浪費和查詢效能的下降。

考慮擴充套件性

資料庫設計時，需要考慮到後續需求的變化，隨著時間的推移，資料庫設計應該能夠很好得適應業務的增長和變化，而不需要進行昂貴的重構。

上面提到了資料型別應該越小越好，但是這個需要考慮後續的擴充套件性。比如，當選擇數值型別時，如果預計記錄數量會非常大，就應該選擇 BIGINT 而不是 INT。對於字串型別，此時應該分配一個足夠容納未來可能增長的內容長度的大小。

再比如字串型別的選擇，如果字串長度在當前是不可預見的，此時使用 VARCHAR 型別 相對於 CHAR 型別就更為靈活，它可以儲存可變長度的字串，節省空間並且可以容納未來長度的變化。

總之，當資料庫設計時，要充分考慮到後續可能的變化，避免在資料庫需要支援更多資料和更復雜查詢時進行成本高昂的重構。

選擇什麼型別呢

回到文章開頭的問題，我們就有幾種不同的資料型別選擇來儲存這個status欄位，如VARCHAR 型別，ENUM 型別，TINYINT 型別，此時我們應該使用哪個欄位型別呢?

這裡我們使用上面幾個簡單原則來幫助我們做出判斷，從而幫助我們做出更好的選擇。

資料型別/考慮因素 VARCHAR TINYINT ENUM
佔用空間 由於狀態字串的長度可能不一致，VARCHAR 可能會使用更多儲存空間。 TINYINT需要非常少的儲存空間（通常是1個位元組）。 ENUM型別相對於VARCHAR型別更節省空間，因為它內部使用數值來表示每個可能的值。
內建型別 不涉及 不涉及 不涉及
簡單型別 字串索引排序效率相對更慢 查詢效能高，儲存效率高,相對 VARCHAR 為簡單型別 底層資料表示上實際上是使用整數索引來儲存的。查詢效能高，儲存效率高，相對VARCHAR 為簡單型別
資料精度 不涉及 不涉及 不涉及
擴充套件性 新增一個列舉簡單，可擴充套件性強 可容納127個狀態，滿足未來可見的變化，可擴充套件性強 可擴充套件性差，每新增一個列舉，都需要重建表

基於以上分析，可以看出來，相比於VARCHAR 和 ENUM 型別，TINYINT 是一個合適的選擇，其提供了最好的儲存效率，資料處理效能，可擴充套件性也比較強，完全能夠適應未來業務需求的變化。

基於此，status 欄位型別最終 選擇 TINYINT 欄位型別。最終的表結構如下:

CREATE TABLE orders (   
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 訂單ID，主鍵，自增
    customer_id INT NOT NULL,                -- 客戶ID，假設已在其他表中定義
    order_date DATETIME NOT NULL,            -- 訂單日期和時間
    total_amount DECIMAL(10, 2) NOT NULL,    -- 訂單總金額，保留兩位小數
    status TINYINT NOT NULL,                 -- 訂單狀態，使用TINYINT型別
);

總結

MySQL 支援的資料型別非常多，在合適的場景下，選擇正確的資料型別對於獲得高效能至關重要。

本文講述了幾個簡單的原則，如選擇最小儲存空間的資料型別，使用內建型別，優先使用簡單型別等。

這幾個原則的核心思想，都是儘量選擇更為簡單的資料型別，減少磁碟空間或者cpu資源的浪費，從而獲得更好的效能。

透過這幾個簡單的原則，希望能夠幫助你在資料庫設計時做出更好的選擇。

相關文章