MYSQL 主鍵的那些 “有意思” 故事

任何資料庫在設計之初都有主鍵，沒有主鍵的表是不完整的，尤其在MYSQL中，而MYSQL中的主鍵設計中，總有一些 “奇葩” 的行為，來讓MYSQL 在執行中，因為主鍵的奇葩設計而導致各種各樣的問題，我們今天來總結總結。

1 主鍵輸入時可以是空的

下面的表，中的確id 是主鍵，並且是自增的，但是插入的時候，的確可以在插入的位置寫入 NULL ，但這裡會實際上在上面插入對應的自增的資料。

CREATE TABLE IF NOT EXISTS test_p (

id int AUTO_INCREMENT,

date datetime,

PRIMARY KEY (id)

);

insert into test_p (id,date) values (null,'2022-01-29');

insert into test_p (id,date) values (1,'2022-01-29');

MYSQL 主鍵的那些 “有意思” 故事

實際上這裡的有一個問題，就是為什麼主鍵可以插入NULL，這裡利用了一個在主鍵設計中的沒有寫 NOT NULL 的漏洞，導致寫入的資料可以是NULL 因為NULL 不代表任何，是或否，代表不知道。

所以在編寫程式的時候，不要在對自增的主鍵欄位使用null 作為插入的值使用。

MYSQL 主鍵的那些 “有意思” 故事

2 主鍵看上去可以是空的

MYSQL 主鍵的那些 “有意思” 故事

在欄位是varchar型別的情況下，輸入的值不能為NULL，但可以為‘’，而由於 VARCHAR型別的特性，一個表中如果輸入'' 也只能有一個，在輸入各種空格，則會提示重複主鍵。

CREATE TABLE IF NOT EXISTS test_p (

id varchar(20),

date datetime,

PRIMARY KEY (id)

);

insert into test_p (id,date) values (null,'2022-01-29');

insert into test_p (id,date) values ('','2022-01-29');

insert into test_p (id,date) values (' ','2022-01-29');

而如果我們在往深入的去想，如果ID 採用的是 char型別實際上結果和varchar是一樣的。

MYSQL 主鍵的那些 “有意思” 故事

3 複合主鍵

很多MYSQL設計中表的主鍵被設計成複合主鍵，而複合主鍵的使用中會存在一些問題

問題1 效能問題

在MYSQL 中的資料組織方式是 B+TREE的方式，而主鍵是根節點的組織中的透過排序的方式來存放資料的一種資料儲存組織方式，如果是一個鍵值作為組織方式還好，至少佔用的位元組數要少，而位元組數大的情況下，勢必比位元組數小的主鍵在效能上有一定的差異，具體還需要壓測進行判斷。

同時下面的這個例子就是一個問題，在更新中如果

INSERT INTO test(id,pid,cid,date) VALUES (2,2,3,'2023-01-28') ON DUPLICATE KEY UPDATE cid = 3;

這樣的情況下那麼如果CID 中很多都是3 的情況下，那麼必然這個插入的效能會極低。

問題 2 死鎖問題

因為在MYSQL中不同的隔離級別會對資料庫產生不同的影響，實際就是GAP LOCK ，next-key-looking 的問題，具體參見專業描述 RR RC 在範圍查詢和資料插入，更新中的不同隔離級別的不同影響問題。

這些都還好說，更糟糕的，在開發中對於複合主鍵中的，一個欄位的更新的問題，這樣會導致併發高的情況下，update與insert 產生偶發死鎖的問題。

問題3

mysql 的on duplicate key update 語句失效的問題

這個問題產生在如果是多個欄位做主鍵的情況下，在我們更新多個欄位中的一個欄位後，這個欄位的唯一性會產生問題導致業務邏輯與原先的設定不一致的問題，這也是導致一些 on duplicate key update 在正常工作後，被認為不正常的情況時有發生。

舉例

id pid cid 三個欄位中 id 必須是唯一的情況下，但是建立主鍵是 id pid cid 三個欄位聯合的情況下，在這樣的情況下，如果單獨使用這樣的方式很難保證 id 是唯一的邏輯屬性，尤其在UPDATE 的情況下。

大家注意下圖,這裡的 on duplicate key update 的語句含義是(第二句)

update cid = 3 where id = 2 and pid = 2 and cid = 2 ，最後影響了2行資料，實際上就是 delete + insert （個人認為），尤其在MYSQL中對於效能的影響會較大。

MYSQL 主鍵的那些 “有意思” 故事

綜上所述，複合主鍵使用 on duplicate key update 應該小心注意邏輯上是否符合最初的設計要求，同時在MYSQL 的表設計中應儘量不使用複合主鍵來進行資料表的設計，避免一些未知問題的產生。

這裡也留下一個問題，如果我不使用複合主鍵，而使用複合唯一索引，又會是什麼故事。

MYSQL 主鍵的那些 “有意思” 故事

相關文章