引入

我們?cè)谑褂胢ysql數(shù)據(jù)庫時(shí)，習(xí)慣使用int型作為主鍵，并設(shè)置為自增，這既能夠保證唯一，使用起來又很方便，但int型的長度是有限的，如果超過長度怎么辦呢？

暴露問題

我們先創(chuàng)建一個(gè)測試表，創(chuàng)建語句如下：

CREATE TABLE test1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
)

然后我們插入兩條數(shù)據(jù)：

INSERT INTO test1 VALUES(NULL,'小牛');
INSERT INTO test1 VALUES(NULL,'大牛');

查詢表顯示正常：

int型的有符號(hào)的范圍為231 -1 = 2147483647，我們直接插入一條數(shù)據(jù)id為2147483647，如下：

INSERT INTO test1 VALUES(2147483647 ,'小華')

結(jié)果顯示正常：

此時(shí)自增ID已達(dá)到了int型的上限，如果我再插入數(shù)據(jù)，就會(huì)報(bào)錯(cuò)：

INSERT INTO test1 VALUES(NULL,'母牛');

此時(shí)主鍵已無法自增，插入的id仍然是2147483647，就違反了主鍵唯一的條件，所以報(bào)錯(cuò)。

解決問題

（1）使用更大的數(shù)據(jù)類型bigint

bigint的范圍是263-1，所謂指數(shù)爆炸，此時(shí)的大小達(dá)到了9,223,372,036,854,775,807的可怕量級(jí)，簡單來說就是用bigint 一天100w條數(shù)據(jù)也得存200億年才能自增爆炸，所以在當(dāng)前場景，幾乎不用擔(dān)心bigint會(huì)自增滿

我們修改數(shù)據(jù)類型為bigint，如圖

再執(zhí)行插入語句：

INSERT INTO test1 VALUES(NULL,'母牛');

又能夠正常插入了：

（2）使用UUID作為主鍵

我們都知道，UUID會(huì)根據(jù)當(dāng)前系統(tǒng)性能，時(shí)間戳等一系列參數(shù)經(jīng)過運(yùn)算得到一個(gè)全世界唯一的字符串，并且mysql提供了生成UUID的方法，用它作為主鍵能夠保證數(shù)據(jù)的唯一性。

利用如下代碼可以生成32位的UUID：

-- 生成32位UUID
SELECT REPLACE(UUID(),'-','') AS UUID;

然后咱們?cè)賱?chuàng)建一個(gè)測試表：

CREATE TABLE test2(
  id VARCHAR(50) PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL
)

插入一條數(shù)據(jù)：

-- 插入U(xiǎn)UID
INSERT INTO test2 VALUES(REPLACE(UUID(),'-',''),'老王');

但這樣寫插入語句每次都要手寫UUID函數(shù)，貌似有點(diǎn)太麻煩了，咱們可以寫一個(gè)觸發(fā)器，讓觸發(fā)器自動(dòng)為我們?cè)O(shè)置ID：

-- 創(chuàng)建觸發(fā)器
DELIMITER $$
CREATE
TRIGGER auto_id        -- 名稱
BEFORE INSERT          -- 觸發(fā)時(shí)機(jī)
ON test2 FOR EACH ROW   -- 作用于test2表，對(duì)每行數(shù)據(jù)生效
BEGIN
IF new.id = '' THEN     -- 當(dāng)id為空字符串時(shí)設(shè)置UUID
  SET new.id = REPLACE(UUID(),'-','');
END IF;
END$$

插入一條數(shù)據(jù)：

-- 插入一條數(shù)據(jù)
INSERT INTO test2 VALUES('','小王');

結(jié)果能正常添加

總結(jié)

(1) 用int型和bigInt型增刪改查速度較UUID更快，并且更節(jié)省空間。

(2) 用UUID更方便。

為何要使用自增int作為主鍵

相信大家都知道要使用無符號(hào)自增int作為主鍵的數(shù)據(jù)類型，可你知道為何要使自用增int而不是使用varchar、text、varchar等類型嗎？

大家也能說出一些優(yōu)點(diǎn)：對(duì)上層業(yè)務(wù)透明，插入數(shù)據(jù)時(shí)無需顯示指定；數(shù)據(jù)類型簡單，更便于存儲(chǔ)維護(hù)表結(jié)構(gòu)

其實(shí)，使用自增int作為主鍵好處多多，今天我們就來一起學(xué)習(xí)一下，并強(qiáng)烈建議大家在實(shí)際開發(fā)中使用自增int作為主鍵。

優(yōu)點(diǎn)：

1、int 相比varchar、char、text使用更少的存儲(chǔ)空間，而且數(shù)據(jù)類型簡單，可以節(jié)約CPU的開銷，更便于表結(jié)構(gòu)的維護(hù)

2、默認(rèn)都會(huì)在主鍵上建立主鍵索引，使用整形作為主鍵可以將更多的索引載入內(nèi)存，提高查詢性能

3、對(duì)于InnoDB存儲(chǔ)引擎而言，每個(gè)二級(jí)索引都會(huì)使用主鍵作為索引值的后綴，使用自增主鍵可以減少索引的長度（大?。?，方便更多的索引數(shù)據(jù)載入內(nèi)存

4、可以使索引數(shù)據(jù)更加緊湊，在數(shù)據(jù)插入、刪除、更新時(shí)可以做到索引數(shù)據(jù)盡可能少的移動(dòng)、分裂頁，減少碎片的產(chǎn)生（可以通過optimize table 來重建表），減少維護(hù)開銷

5、在數(shù)據(jù)插入時(shí)，可以保證邏輯相鄰的元素物理也相鄰，便于范圍查找

當(dāng)然，使用自增int作為主鍵也不是百利無一害，在高并發(fā)的情況下也可能會(huì)造成鎖的爭用問題。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論