《MySQL DBA修煉之道》——3.6 ID主鍵

本節書摘來自華章出版社《mysql dba修煉之道》一書中的第3章，第3.6節，作者：陳曉勇，更多章節内容可以通路雲栖社群“華章計算機”公衆号檢視

下面先說明選擇主鍵的注意事項。

1） 建議主鍵是整型。

2） 如果表中包含一列能夠確定唯一、非空（not null），以及能夠用來定位一條記錄的字段，就不要因為傳統而覺得一定要加上一個自增id做主鍵。

3） 主鍵也遵從索引的一些約定，注意聯合主鍵的字段順序。

4） 為主鍵選擇更有意義的名稱，如id這個名稱太過籠統，表達的資訊可能不準确。

1.自增id主鍵

自增列是mysql裡的一種特殊的整型，我們定義一個列的整型的同時，可以設定它是否為自增的，一個表隻能有一個列是自增列，且自增列必然是主鍵列。自增列的預設起始值是1，預設可以按步長為1進行遞增，自增列的增長将受兩個mysql全局參數的影響。

auto_increment_offset：确定auto_increment列值的起點。

auto_increment_increment：控制列值增加的間隔，即步長。

也可以單獨定義某個表的起始值，如：

<code>mysql&gt; alter table tbl auto_increment = 100;</code>

在複制環境中，設定這兩個值可以減少主鍵沖突，關于這一點以後會在複制章節（第12章）中詳述。

使用自增列的原因是唯一辨別資料表的某行記錄。它們也被用來優化表之間的連接配接。連接配接單個列比連接配接多個列更快，連接配接整數列比連接配接其他大多數資料類型也更快。總之，有很多使用它的理由。但也沒有必要濫用自增id，給每個表都設定一個自增id做主鍵，有時可能存在另一個從邏輯上來說更加自然的主鍵。

另外，因為innodb引擎的id主鍵是聚集索引，從前文可以得知，如果簇索引、資料和主鍵索引放在一起且是按主鍵索引進行排序的，那麼基于自增主鍵的單個值查找和小範圍查找将是最高效的。

研發人員有時傾向于使用字元串做主鍵，或者使用多個列的聯合主鍵，但需要清楚一個事實：innodb的其他索引實際上存儲了主鍵的值，這樣做可能會導緻索引空間大大增加。

innodb選擇主鍵建立簇索引。如果沒有主鍵，就會選取一個唯一非空的索引來替代；如果仍然找不到合适的列，那麼将建立一個隐含的主鍵來建立簇索引。選取一個唯一非空的索引做主鍵可能不是我們所期待的，一般的解決辦法是删除我們不期望的主鍵（唯一索引），建立一個非空的自增列，再增加這個唯一索引。

例如，由于未定義主鍵，innodb自動把唯一索引idx_a_b(a,b)定義為主鍵了。我們想增加一個自增id主鍵，并設定唯一索引idx_a_b。idx_a_b表示這個索引是建立在a列和b列的複合索引。

2.自增id可以插入指定的值

自增id還有一個特性，那就是如果插入0值或null值，innodb會認為沒有設定值，然後幫你自增一個值。是以可以利用這個特性生成全局唯一id、序列。如果資料分片到許多執行個體、機器上，那麼就需要一個全局唯一id來辨別記錄了。如下是官方文檔推薦的一個建立唯一序列的方法。

建立一個表，用來控制順序計數器并使其初始化。

<code>mysql&gt; create table sequence (id int not null);</code>

<code>mysql&gt; insert into sequence values (0);</code>

使用該表産生如下的序列數。

<code>mysql&gt; update sequence set id=last_insert_id(id+1);</code>

<code>mysql&gt; select last_insert_id();</code>

高并發下，last_insert_id函數可能會有一定的性能問題，但這種方法很簡單，一般情況下是可以滿足需要的。