MySQL主鍵設(shè)計原則
- MySQL主鍵應(yīng)當(dāng)是對用戶沒有意義的。
- MySQL主鍵應(yīng)該是單列的，以便提高連接和篩選操作的效率
- 永遠(yuǎn)也不要更新MySQL主鍵
- MySQL主鍵不應(yīng)包含動態(tài)變化的數(shù)據(jù)，如時間戳、創(chuàng)建時間列、修改時間列等
- MySQL主鍵應(yīng)當(dāng)有計算機(jī)自動生成
主鍵設(shè)計的常用方案
一、自增ID
優(yōu)點(diǎn)：
1、數(shù)據(jù)庫自動編號，速度快，而且是增量增長，聚集型主鍵按順序存放，對于檢索非常有利。
2、 數(shù)字型，占用空間小，易排序，在程序中傳遞方便。
缺點(diǎn)：
1、不支持水平分片架構(gòu)，水平分片的設(shè)計當(dāng)中，這種方法顯然不能保證全局唯一。
2、表鎖
在MySQL5.1.22之前，InnoDB自增值是通過其本身的自增長計數(shù)器來獲取值，該實現(xiàn)方式是通過表鎖機(jī)制來完成的（AUTO-INC LOCKING）。鎖不是在每次事務(wù)完成后釋放，而是在完成對自增長值插入的SQL語句后釋放，要等待其釋放才能進(jìn)行后續(xù)操作。比如說當(dāng)表里有一個auto_increment字段的時候，innoDB會在內(nèi)存里保存一個計數(shù)器用來記錄auto_increment的值，當(dāng)插入一個新行數(shù)據(jù)時，就會用一個表鎖來鎖住這個計數(shù)器，直到插入結(jié)束。如果大量的并發(fā)插入，表鎖會引起SQL堵塞。
在5.1.22之后，InnoDB為了解決自增主鍵鎖表的問題，引入了參數(shù)innodb_autoinc_lock_mode：
0：通過表鎖的方式進(jìn)行，也就是所有類型的insert都用AUTO-inc locking（表鎖機(jī)制）。
1：默認(rèn)值，對于simple insert 自增長值的產(chǎn)生使用互斥量對內(nèi)存中的計數(shù)器進(jìn)行累加操作，對于bulk insert 則還是使用表鎖的方式進(jìn)行。
2：對所有的insert-like 自增長值的產(chǎn)生使用互斥量機(jī)制完成，性能最高，并發(fā)插入可能導(dǎo)致自增值不連續(xù)，可能會導(dǎo)致Statement 的 Replication 出現(xiàn)不一致，使用該模式，需要用 Row Replication的模式。
3、自增主鍵不連續(xù)
Create Table: CREATE TABLE
tmp_auto_inc(
idint(11) NOT NULL AUTO_INCREMENT,
talkidint(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
當(dāng)插入10條記錄的時候，因為AUTO_INCREMENT=16，所以下次再插入的時候，主鍵就會不連續(xù)。
二、UUID
優(yōu)點(diǎn)：
1、全局唯一性、安全性、可移植性。
2、能夠保證獨(dú)立性，程序可以在不同的數(shù)據(jù)庫間遷移，效果不受影響。
3、保證生成的ID不僅是表獨(dú)立的，而且是庫獨(dú)立的，在你切分?jǐn)?shù)據(jù)庫的時候尤為重要
缺點(diǎn)：
1、針對InnoDB引擎會徒增IO壓力，InnoDB為聚集主鍵類型的引擎，數(shù)據(jù)會按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會產(chǎn)生巨大的IO壓力。InnoDB主鍵索引和數(shù)據(jù)存儲位置相關(guān)（簇類索引），uuid 主鍵可能會引起數(shù)據(jù)位置頻繁變動，嚴(yán)重影響性能。
2、UUID長度過長，一個UUID占用128個比特（16個字節(jié)）。主鍵索引KeyLength長度過大，而影響能夠基于內(nèi)存的索引記錄數(shù)量，進(jìn)而影響基于內(nèi)存的索引命中率，而基于硬盤進(jìn)行索引查詢性能很差。嚴(yán)重影響數(shù)據(jù)庫服務(wù)器整體的性能表現(xiàn)。
