Mysql主鍵UUID和自增主鍵的區(qū)別及優(yōu)劣分析

引言

之前有段時間用postgresql 數(shù)據(jù)庫，在上云之后，從自增主鍵變?yōu)閡uid，感覺uuid全球唯一，很方便。

最近用mysql，發(fā)現(xiàn)mysql主鍵都是選擇自增主鍵，仔細(xì)比較一下，為什么mysql選擇自增主鍵，有什么不同。

在mysql5.0之前，如果是多個master復(fù)制的環(huán)境，無法用自增主鍵，因為可能重復(fù)。在5.0以及之后的版本通過配置自增偏移量解決了整個問題。

什么情況下我們希望用uuid

1. 避免重復(fù)，便于scale，這就是我們做cloud service的時候選擇uuid的主要原因

2. 入庫之前可以知道id

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

UUID有什么問題

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

2.由于size和無序性，可能引起性能問題

Mysql的uuid原理

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

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

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

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

(2).但是在我們實際到項目中會碰到問題，歷史數(shù)據(jù)表的主鍵id會與數(shù)據(jù)表的id重復(fù)，兩張自增id做主鍵的表合并時，id一定會有沖突，但如果各自的id還關(guān)聯(lián)了其他表，這就很不好操作。

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

2.UUID優(yōu)缺點

缺點： 1. 影響插入速度， 并且造成硬盤使用率低

2. uuid之間比較大小相對數(shù)字慢不少， 影響查詢速度。

3. uuid占空間大， 如果你建的索引越多， 影響越嚴(yán)重

優(yōu)點：出現(xiàn)數(shù)據(jù)拆分、合并存儲的時候，能達(dá)到全局的唯一性

3.最優(yōu)方案

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

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

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

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

綜合上述可得：

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

(2).對于InnoDB的主索引，數(shù)據(jù)會按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會產(chǎn)生巨大的IO壓力，此時不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。為了全局的唯一性，應(yīng)該用uuid做索引關(guān)聯(lián)其他表或做外鍵。

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

如果是主從即M-S模式，最好是不使用mysql自帶函數(shù)uuid來生成唯一主鍵，因為主表生成的uuid要再關(guān)聯(lián)從表時，需要再去數(shù)據(jù)庫查出這個uuid，需要多進(jìn)行一次數(shù)據(jù)庫交互，而且在這個時間差里面主表很有可能還有數(shù)據(jù)生成，這樣就很容易導(dǎo)致關(guān)聯(lián)的uuid出錯。如果真要使用uuid，可以在Java中生成后，直接存儲到DB里，這時主從的uuid就是一樣的了！

補(bǔ)充：mysql的uuid()主鍵重復(fù)

1、mysql的uuid()主鍵重復(fù)

mysql使用了navicat客戶端，某次執(zhí)行了如下sql

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

結(jié)果發(fā)現(xiàn)，生成的uuid重復(fù)了，

經(jīng)過排查，發(fā)現(xiàn)是navicat的問題，需要將該sql語句做如下調(diào)整:

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

結(jié)果如下：

2、使用其他方案：

將uuid再進(jìn)行一次md5：

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

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。