Mysql主鍵UUID和自增主鍵的區別及優劣分析

引言

之前有段時間用postgresql 數據庫,在上雲之後,從自增主鍵變為uuid,感覺uuid全球唯一,很方便。

最近用mysql,發現mysql主鍵都是選擇自增主鍵,仔細比較一下,為什麼mysql選擇自增主鍵,有什麼不同。

在mysql5.0之前,如果是多個master復制的環境,無法用自增主鍵,因為可能重復。在5.0以及之後的版本通過配置自增偏移量解決瞭整個問題。

什麼情況下我們希望用uuid

1. 避免重復,便於scale,這就是我們做cloud service的時候選擇uuid的主要原因

2. 入庫之前可以知道id

3.相對安全,不能簡單的從uuid獲取信息,但是如果自增,則容易暴露信息,如果一個客戶id是123456,很容易猜到有客戶id是123456.

UUID有什麼問題

1.uuid有16個字節,比int(4 byte)和bigint(8 byte)占用更多存儲空間

2.由於size和無序性,可能引起性能問題

Mysql的uuid原理

mysql的innodb存儲引擎處理storage的方式是靠聚集索引。

聚集索引是指數據庫表行中數據的物理順序與鍵值的邏輯(索引)順序相同。一個表隻能有一個聚集索引,因為一個表的物理順序隻有一種情況

1.為什麼要使用uuid做主鍵

(1).其實在innodb存儲引擎下,自增長的id做主鍵性能已經達到瞭最佳。不論是存儲和讀取速度都是最快的,而且占的存儲空間也是最小。

(2).但是在我們實際到項目中會碰到問題,歷史數據表的主鍵id會與數據表的id重復,兩張自增id做主鍵的表合並時,id一定會有沖突,但如果各自的id還關聯瞭其他表,這就很不好操作。

(3).如果使用UUID,生成的ID不僅是表獨立的,而且是庫獨立的。對以後的數據操作很有好處,可以說一勞永逸。

2.UUID優缺點

缺點: 1. 影響插入速度, 並且造成硬盤使用率低

2. uuid之間比較大小相對數字慢不少, 影響查詢速度。

3. uuid占空間大, 如果你建的索引越多, 影響越嚴重

優點:出現數據拆分、合並存儲的時候,能達到全局的唯一性

3.最優方案

(1).InnoDB引擎表是基於B+樹的索引組織表。

(2).B+樹:B+樹是為磁盤或其他直接存取輔助設備而設計的一種平衡查找樹,在B+樹中,所有記錄節點都是按鍵值的大小順序存放在同一層的葉節點中,各葉節點指針進行連接。

(3).InnoDB主索引:葉節點包含瞭完整的數據記錄。這種索引叫做聚集索引。InnoDB 的索引能提供一種非常快速的主鍵查找性能。不過,它的輔助索引也會包含主鍵列,所以,如果主鍵定義的比較大,其他索引也將很大。如果想在表上定義 、很多索引,則爭取盡量把主鍵定義得小一些。InnoDB 不會壓縮索引

(4).聚集索引這種實現方式使得按主鍵的搜索十分高效,但是輔助索引搜索需要檢索兩遍索引:首先檢索輔助索引獲得主鍵,然後用主鍵到主索引中檢索獲得記錄。

綜合上述可得:

(1).如果InnoDB表的數據寫入順序能和B+樹索引的葉子節點順序一致的話,這時候存取效率是最高的。為瞭存儲和查詢性能應該使用自增長id做主鍵。

(2).對於InnoDB的主索引,數據會按照主鍵進行排序,由於UUID的無序性,InnoDB會產生巨大的IO壓力,此時不適合使用UUID做物理主鍵,可以把它作為邏輯主鍵,物理主鍵依然使用自增ID。為瞭全局的唯一性,應該用uuid做索引關聯其他表或做外鍵。

4.如果非要使用uuid做主鍵,下面是小建議:

如果是主從即M-S模式,最好是不使用mysql自帶函數uuid來生成唯一主鍵,因為主表生成的uuid要再關聯從表時,需要再去數據庫查出這個uuid,需要多進行一次數據庫交互,而且在這個時間差裡面主表很有可能還有數據生成,這樣就很容易導致關聯的uuid出錯。如果真要使用uuid,可以在Java中生成後,直接存儲到DB裡,這時主從的uuid就是一樣的瞭!

補充:mysql的uuid()主鍵重復

1、mysql的uuid()主鍵重復

mysql使用瞭navicat客戶端,某次執行瞭如下sql

select replace(uuid(), '-', '') as id, u.user_id from t_user u;

結果發現,生成的uuid重復瞭,

經過排查,發現是navicat的問題,需要將該sql語句做如下調整:

select replace(convert(uuid() using utf8mb4), '-', ''), u.user_id from t_user u;

結果如下:

2、使用其他方案:

將uuid再進行一次md5:

select md5(uuid()) as id, u.user_id from t_user u;

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。