關係型資料庫表結構的設計，有下面兩個設計技巧：

物理主鍵作為關聯的外來鍵

關係型資料庫，由多個資料表構成。每一個資料表的結構是相同的，不同表之間可能存在關聯關係。表之間的關聯關係，正是關係型資料庫得名的原因。

一個表由多個欄位構成。其中可能有多個欄位適合作為主鍵。主鍵欄位，就是表中每一行都不會有重複資料的欄位。

主鍵，可以分為兩種：物理主鍵和邏輯主鍵。

每一張資料庫的表，都使用自增長的id欄位作為物理主鍵。

多表之間的外來鍵關聯，都關聯其他表的物理主鍵，也就是關聯其他表的id欄位。

邏輯主鍵，就是除了id欄位外的不重複的欄位。我們設計資料庫的外來鍵關聯時，不使用邏輯主鍵，而是使用物理主鍵。

這是因為，物理主鍵肯定是主鍵，因為它是自增的。（對於不支援自增欄位的關聯式資料庫，可以使用uuid保證物理主鍵的唯一性）

而邏輯主鍵，則可能隨著業務的發展，成為可重複的欄位。一旦這種情況發生，關聯式資料庫的外來鍵關係就被破壞了。如，可能你覺得姓名是邏輯主鍵，但可能後面發現有人重名，那麼這個欄位就不再是主鍵了。而如果之前使用這個欄位作為外來鍵，那麼查詢返回的資料就不是一一對應的了。

這就是為什麼要使用物理主鍵，以及用物理主鍵作為關聯外來鍵的原因。

使用樂觀鎖更新依賴之前狀態的記錄

考慮這樣一種資料庫的應用場景：

任務單的接管：多個操作人員從一個任務池中接管一個任務。只有第一個獲取任務的操作人員才能成功接管該任務，後續操作人員的接管操作都必須失敗。

這時，就需要一種資料庫記錄的鎖定機制。只有第一個事務才能更新記錄。

資料庫可以使用悲觀鎖和樂觀鎖來鎖定資料庫記錄。

悲觀鎖是如下sql語句實現的：

這條語句會在其他修改內容的事務提交後返回最新的資料。

一旦執行這條語句，這些記錄就被鎖住了，不能被其他sql事務修改。直到本事務提交。

樂觀鎖，是應用程式實現的，不是資料庫實現的機制。樂觀鎖，對於資料庫來說，就是沒有上鎖。事務可以select其他事務已經提交的資料。更新資料時，資料庫保證多個事務的更新是原子的。

悲觀鎖，會導致事務等待其他事務完成。樂觀鎖，只會等待其他事務的更新語句的完成，不會等待整個事務完成，因此效率較高。

實現樂觀鎖的方法：

給資料庫表新增一個version欄位。version是一個數字型別的欄位，每次更新都加1。每次更新時都要檢測version欄位是否和當前事務的值相同。如果version欄位不同，那麼就表明在查詢資料之後，有其他事務已經更新了該記錄，就會導致此次更新失敗。應用必須重新載入最新的資料，然後重新更新資料。

如果使用樂觀鎖，那麼如果資料庫中version和應用中version相同，則用version+1的版本值更新version欄位。

SQL語句如下：

   不使用鎖更新獨立狀態的記錄

考慮這樣一種資料庫的應用場景：

需要更新虛擬機器的狀態。多個事務可能會同時更新虛擬機器的狀態為start或者stop。這種狀態的更新和前一個階段的狀態是無關的，因此不需要鎖定記錄。直接更新即可。此時不需要使用悲觀鎖或者樂觀鎖。

如果這個表新增了version欄位，直接忽略對version欄位的比較和更新即可。

SQL語句如下：

總結

設計關係型資料庫的表時，需要給表新增一個ID欄位（自增欄位，或者uuid欄位）和一個version欄位（數值型別）。ID欄位作為物理主鍵，用於保證記錄的不可重複性和用作外來鍵關聯。

version欄位用於實現樂觀鎖，提供比悲觀鎖更好的效能。特別是對於UI顯示並可能出現併發更新的資料，更需要使用樂觀鎖來提升資料庫訪問效能。

對於後臺自動更新的任務，可以使用樂觀鎖實現。但需要在衝突發生時實現自動退讓。也可以使用悲觀鎖在資料庫上對事務進行排隊來解決更新衝突問題。

對於不關心記錄的狀態之間關係的場景，可以直接更新記錄，忽略掉version欄位的檢測和更新。

打賞支援我寫出更多好文章，謝謝！

打賞作者