在項目過程中遇到一個看似極為基礎(chǔ)的問題，但是在深入思考后還是引出了不少問題，覺得有必要把這一學(xué)習(xí)過程進行記錄。

MySQL主鍵設(shè)計原則

• MySQL主鍵應(yīng)當(dāng)是對用戶沒有意義的。
• MySQL主鍵應(yīng)該是單列的，以便提高連接和篩選操作的效率
• 永遠也不要更新MySQL主鍵
• MySQL主鍵不應(yīng)包含動態(tài)變化的數(shù)據(jù)，如時間戳、創(chuàng)建時間列、修改時間列等
• MySQL主鍵應(yīng)當(dāng)有計算機自動生成。

主鍵設(shè)計的常用方案

自增ID

優(yōu)點：

1、數(shù)據(jù)庫自動編號，速度快，而且是增量增長，聚集型主鍵按順序存放，對于檢索非常有利。

2、 數(shù)字型，占用空間小，易排序，在程序中傳遞方便。

缺點：
1、不支持水平分片架構(gòu)，水平分片的設(shè)計當(dāng)中，這種方法顯然不能保證全局唯一。
2、表鎖

在MySQL5.1.22之前，InnoDB自增值是通過其本身的自增長計數(shù)器來獲取值，該實現(xiàn)方式是通過表鎖機制來完成的（AUTO-INC LOCKING）。鎖不是在每次事務(wù)完成后釋放，而是在完成對自增長值插入的SQL語句后釋放，要等待其釋放才能進行后續(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：通過表鎖的方式進行，也就是所有類型的insert都用AUTO-inc locking（表鎖機制）。
• 1：默認(rèn)值，對于simple insert 自增長值的產(chǎn)生使用互斥量對內(nèi)存中的計數(shù)器進行累加操作，對于bulk insert 則還是使用表鎖的方式進行。
• 2：對所有的insert-like 自增長值的產(chǎn)生使用互斥量機制完成，性能最高，并發(fā)插入可能導(dǎo)致自增值不連續(xù)，可能會導(dǎo)致Statement 的 Replication 出現(xiàn)不一致，使用該模式，需要用 Row Replication的模式。

3、自增主鍵不連續(xù)

Create Table: CREATE TABLE `tmp_auto_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `talkid` int(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)點
1、全局唯一性、安全性、可移植性。

2、能夠保證獨立性，程序可以在不同的數(shù)據(jù)庫間遷移，效果不受影響。

3、保證生成的ID不僅是表獨立的，而且是庫獨立的，在你切分?jǐn)?shù)據(jù)庫的時候尤為重要

缺點
1、針對InnoDB引擎會徒增IO壓力，InnoDB為聚集主鍵類型的引擎，數(shù)據(jù)會按照主鍵進行排序，由于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ù)量，進而影響基于內(nèi)存的索引命中率，而基于硬盤進行索引查詢性能很差。嚴(yán)重影響數(shù)據(jù)庫服務(wù)器整體的性能表現(xiàn)。

自定義序列表

所謂自定義序列表，就是在庫中建一張用于生成序列的表來存儲序列信息，序列生成的策略通過程序?qū)用鎭韺崿F(xiàn)。如下所示，構(gòu)建一張序列表：

CREATE TABLE `sequence` (
    `name` varchar(50) NOT NULL,
    `id` bigint(20) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`name`)
) ENGINE=InnoDB;

注意區(qū)別，id字段不是自增的，也不是主鍵。在使用前，我們需要先插入一些初始化數(shù)據(jù)：

INSERT INTO `sequence` (`name`) VALUES 
('users'), ('photos'), ('albums'), ('comments');

接下來，我們可以通過執(zhí)行下面的SQL語句來獲得新的照片ID：

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

我們執(zhí)行了一個更新操作，將id字段增加1，并將增加后的值傳遞到LAST_INSERT_ID函數(shù)， 從而指定了LAST_INSERT_ID的返回值。

實際上，我們不一定需要預(yù)先指定序列的名字。如果我們現(xiàn)在需要一種新的序列，我們可以直接執(zhí)行下面的SQL語句：

INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id` + 1);
SELECT LAST_INSERT_ID();

這種方案的問題在于序列生成的邏輯脫離了數(shù)據(jù)庫層，由應(yīng)用層負責(zé)，增加了開發(fā)復(fù)雜度。當(dāng)然，其實可以用spring來解決這一問題，因為在spring JDBC中已經(jīng)對這種序列生成邏輯進行了簡單的封裝。
我們可以看一下spring的相關(guān)源代碼：MySQLMaxValueIncrementer.

	@Override
	protected synchronized long getNextKey() throws DataAccessException {
		if (this.maxId == this.nextId) {
			/*
			* Need to use straight JDBC code because we need to make sure that the insert and select
			* are performed on the same connection (otherwise we can't be sure that last_insert_id()
			* returned the correct value)
			*/
			Connection con = DataSourceUtils.getConnection(getDataSource());
			Statement stmt = null;
			try {
				stmt = con.createStatement();
				DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
				// Increment the sequence column...
				String columnName = getColumnName();
				stmt.executeUpdate("update "+ getIncrementerName() + " set " + columnName +
						" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");
				// Retrieve the new max of the sequence column...
				ResultSet rs = stmt.executeQuery(VALUE_SQL);
				try {
					if (!rs.next()) {
						throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
					}
					this.maxId = rs.getLong(1);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
				}
				this.nextId = this.maxId - getCacheSize() + 1;
			}
			catch (SQLException ex) {
				throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
			}
			finally {
				JdbcUtils.closeStatement(stmt);
				DataSourceUtils.releaseConnection(con, getDataSource());
			}
		}
		else {
			this.nextId++;
		}
		return this.nextId;
	}

spring的實現(xiàn)也就是通過update語句對incrementerName表里的columnName 列進行遞增，并通過mysql的last_insert_id()返回最近生成的值。并保證了事務(wù)性及方法的并發(fā)支持。只是這個實現(xiàn)有些過于簡單，比如：一個表對應(yīng)一個序列的做法在實際應(yīng)用開發(fā)中顯得過于零碎，所以在實際應(yīng)用中需要對其實現(xiàn)進行修改，實現(xiàn)一條記錄對應(yīng)一個序列的策略。另外對水平分片的支持并不在這一實現(xiàn)考慮范圍內(nèi)。同時，這種做法依然無法回避表鎖的機制，所以這里通過CacheSize()的做法，實現(xiàn)了一次申請并緩存在內(nèi)存中，以減少表鎖的發(fā)生頻率。

如何解決水平分片的需求

UUID

由于UUID出現(xiàn)重復(fù)的概率基本可以忽略，所以對分片是天生支持的。

獨立的序列庫

單獨建立一個庫用來生成ID，在Shard中的每張表在這個ID庫中都有一個對應(yīng)的表，而這個對應(yīng)的表只有一個字段， 這個字段是自增的。當(dāng)我們需要插入新的數(shù)據(jù)，我們首先在ID庫中的相應(yīng)表中插入一條記錄，以此得到一個新的ID， 然后將這個ID作為插入到Shard中的數(shù)據(jù)的主鍵。這個方法的缺點就是需要額外的插入操作，如果ID庫變的很大， 性能也會隨之降低。所以一定要保證ID庫的數(shù)據(jù)集不要太大，一個辦法是定期清理前面的記錄

復(fù)合標(biāo)識符

這種做法是通過聯(lián)合主鍵的策略，即通過兩個字段來生成一個唯一標(biāo)識，前半部分是分片標(biāo)識符，后半部分是本地生成的標(biāo)識符（比如使用AUTO_INCREMENT生成）

帶分庫策略的自定義序列表

這種做法可以基于上面提到的自定義序列表的方法的基礎(chǔ)上，做一些技巧性的調(diào)整。即如下：

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

這里的id初始值設(shè)定上要求不同的分片取不同的值，且必須連續(xù)。同時將每次遞增的步長設(shè)定為服務(wù)器數(shù)目。
比如有3臺機器，那么我們只要將初始值分別設(shè)置為1，2，3. 然后執(zhí)行下面的語句即可：

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 3) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

這就可以解決主鍵生成沖突的問題。但是如果在運行一段時間后要進行動態(tài)擴充分片數(shù)的時候，需要對序列初始值做一次調(diào)整，以確保其連續(xù)性，否則依然可能存在沖突的可能。當(dāng)然這些邏輯可以封裝在數(shù)據(jù)訪問層的代碼中。

主鍵的必要性

表中每一行都應(yīng)該有可以唯一標(biāo)識自己的一列（或一組列）。雖然并不總是都需要主鍵，但大多數(shù)數(shù)據(jù)庫設(shè)計人員都應(yīng)保證他們創(chuàng)建的每個表有一個主鍵，以便于以后數(shù)據(jù)操縱和管理。其實即使你不建主鍵，MySQL（InnoDB引擎）也會自己建立一個隱藏6字節(jié)的ROWID作為主鍵列，詳細可以參見[這里]

因為，InnoDB引擎使用聚集索引，數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點上。這就要求同一個葉子節(jié)點內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時，MySQL 會根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點和位置，如果頁面達到裝載因子（InnoDB默認(rèn)為15/16），則開辟一個新的頁（節(jié)點）

所以在使用innoDB表時要避免隨機的（不連續(xù)且值的分布范圍非常大）聚簇索引，特別是針對I/O密集型的應(yīng)用。例如：從性能角度考慮，使用UUID的方案就會導(dǎo)致聚簇索引的插入變得完全隨機。

主鍵的數(shù)據(jù)類型選擇

關(guān)于主鍵的類型選擇上最常見的爭論是用整型還是字符型的問題，關(guān)于這個問題《高性能MySQL》一書中有明確論斷：
整數(shù)通常是標(biāo)識列的最好選擇，因為它很快且可以使用AUTO_INCREAMENT,如果可能，應(yīng)該避免使用字符串類型作為標(biāo)識列，因為很消耗空間，且通常比數(shù)字類型慢。

如果是使用MyISAM，則就更不能用字符型，因為MyISAM默認(rèn)會對字符型采用壓縮引擎，從而導(dǎo)致查詢變得非常慢。
參考：
1、http://www.rzrgm.cn/lsx1993/p/4663147.html
2、http://www.rzrgm.cn/zhoujinyi/p/3433823.html
3、http://www.zolazhou.com/posts/primary-key-selection-in-database-partition-design/
4、《高性能MySQL》
5、《高可用MySQL》