談到MySQL數(shù)據(jù)庫(kù)表設(shè)計(jì)和SQL優(yōu)化，都會(huì)了解要有索引，可以說(shuō)MySQL就是索引的代名詞。這話(huà)一點(diǎn)都不夸張。MySQL數(shù)據(jù)庫(kù)底層的innodb引擎就是索引組織表。數(shù)據(jù)行的所有操作都是基本主鍵進(jìn)行的。
數(shù)據(jù)庫(kù)中定義的主鍵具備如下特性：
1、任何兩行都不具有相同的主鍵值，保證唯一性。
2、每個(gè)行都必須具有一個(gè)主鍵值（主鍵列不允許NULL值）
目前來(lái)說(shuō)MySQL的處理邏輯都是跟主鍵綁在一起。主鍵的重要度不言而喻。

官方說(shuō)明

表中每一行都應(yīng)該有可以唯一標(biāo)識(shí)自己的一列，當(dāng)沒(méi)有設(shè)置主鍵的時(shí)候MySQL本身會(huì)生成隱藏的列做為主鍵。

• When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
  當(dāng)表上定義一個(gè)主鍵時(shí)，InnoDB使用它作為聚集索引。如果沒(méi)有邏輯惟一的非空列或列集，則建議添加一個(gè)新的自動(dòng)遞增列，其值將自動(dòng)填充。

• If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
  如果沒(méi)有為表定義一個(gè)主鍵，MySQL定位第一個(gè)UNIQUE索引，所有的鍵列都不是NULL, InnoDB使用它作為聚集索引。

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
  如果該表沒(méi)有PRIMARY KEY或合適的UNIQUE索引，InnoDB內(nèi)部會(huì)在分配給一個(gè)6字節(jié)ID字段，作為隱藏的聚集索引，名為GEN_CLUST_INDEX 。行是按照隱藏ID為主鍵進(jìn)行排序。因此，在插入新行時(shí)單調(diào)增加， 按行ID排序的行在物理上是按插入順序排列的。

備注：聚集索引=主鍵=PRIMAYR KEY

表設(shè)計(jì)和SQL語(yǔ)句中的主鍵

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

MySQL的數(shù)據(jù)結(jié)構(gòu)和普通的查詢(xún)?nèi)缦拢?/p>

#例如創(chuàng)建如下一張表:
mysql> CREATE TABLE users(
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY(id)
);
mysql> INSERT INTO users(id,name,age)VALUES(3,'TOM',18),(5,'Bob',21),(7,'Ana',17),(8,'Rex',55),(9,'Toy',35);

#新建一個(gè)以age字段的二級(jí)索引:
mysql>ALTER TABLE users ADD INDEX index_age(age);
mysql>SELECT * FROM users WHERE age=35;

二級(jí)索引的檢索過(guò)程：

備注:二級(jí)索引是指定字段與主鍵的映射，主鍵長(zhǎng)度越小，普通索引的葉子節(jié)點(diǎn)就越小，二級(jí)索引占用的空間也就越小，所以要避免使用過(guò)長(zhǎng)的字段作為主鍵。

除此之外REPLACE INTO，ON DUPLICATE KEY UPDATE ，第三方mydumper單表邏輯導(dǎo)出并行方式都是依賴(lài)于主鍵或UNIQUE鍵。

高可用復(fù)制模式下主鍵

MySQL復(fù)制是邏輯復(fù)制，雖然有些差異，但等價(jià)于把SQL語(yǔ)句在另一個(gè)節(jié)點(diǎn)執(zhí)行；
1.存在沒(méi)有主鍵的表，導(dǎo)致備庫(kù)應(yīng)用每個(gè)Event 都需要全表掃描，導(dǎo)致大量的延遲。現(xiàn)在了解為什么主從復(fù)制延遲會(huì)經(jīng)常碰到。

2.MGR里主鍵的必要性
要被組復(fù)制的每個(gè)表都必須有一個(gè)定義好的主鍵，或者一個(gè)等效的主鍵，其中等效的主鍵是一個(gè)非空的唯一鍵。這樣的鍵作為表中每一行的唯一標(biāo)識(shí)符是必需的，這樣系統(tǒng)就可以通過(guò)準(zhǔn)確地識(shí)別每個(gè)事務(wù)修改哪些行來(lái)確定哪些事務(wù)發(fā)生了沖突。

應(yīng)該選擇什么樣主鍵

MySQL數(shù)據(jù)結(jié)構(gòu)中理想狀態(tài)下主鍵是什么樣？
1、不更新主鍵列的值
2、不重用主鍵列的值
3、不在主鍵列中使用可能會(huì)更改的值
4、字段長(zhǎng)度需要控制好，避免索引也頻繁的分裂合并

1.自增主鍵

無(wú)特殊需求下Innodb建議使用與業(yè)務(wù)無(wú)關(guān)的自增ID作為主鍵：
1、如果表使用自增主鍵，以利于插入性能的提高。那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫(xiě)滿(mǎn)，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)，近似順序填滿(mǎn)。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)，因此效率很高，也不會(huì)增加很多開(kāi)銷(xiāo)在維護(hù)索引上。

自增型主鍵設(shè)計(jì)(int,bigint)可以降低二級(jí)索引的空間，提升二級(jí)索引的內(nèi)存命中率；
自增型的主鍵可以減小page的碎片，提升空間和內(nèi)存的使用；

2、 如果使用非自增主鍵（如果身份證號(hào)，機(jī)構(gòu)唯一代碼等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置

MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤(pán)上而從緩存中清掉，此時(shí)又要從磁盤(pán)上讀回來(lái)，這增加了很多開(kāi)銷(xiāo)，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。

3.使用自增長(zhǎng)做主鍵的優(yōu)缺點(diǎn)：

• 優(yōu)點(diǎn)
  1、很小的數(shù)據(jù)存儲(chǔ)空間
  2、性能最好
  3、容易緩存

• 缺點(diǎn)
  1、如果存在大量的數(shù)據(jù)，可能會(huì)超出自增長(zhǎng)的取值范圍。
  2、可能不是連續(xù)的。5.7版本bug 重新啟動(dòng)，自增序列丟失問(wèn)題
  3、沒(méi)有業(yè)務(wù)意義
  4、很難處理分布式存儲(chǔ)的數(shù)據(jù)表，尤其是需要合并表的情況下會(huì)存在沖突。
  5、安全性低，因?yàn)槭怯幸?guī)律的，容易被非法獲取數(shù)據(jù)。

2.UUID生成的主鍵

UUID是Universally Unique Identifier的縮寫(xiě)，它是在一定的范圍內(nèi)（從特定的名字空間到全球）唯一的機(jī)器生成的標(biāo)識(shí)符

• 經(jīng)由一定的算法機(jī)器生成為了保證UUID的唯一性，規(guī)范定義了包括網(wǎng)卡MAC地址、時(shí)間戳、名字空間（Namespace）、隨機(jī)或偽隨機(jī)數(shù)、時(shí)序等元素，以及從這些元素生成UUID的算法。
• 非人工指定，非人工識(shí)別
  UUID是不能人工指定的。UUID的復(fù)雜性決定了“一般人“不能直接從一個(gè)UUID知道哪個(gè)對(duì)象和它關(guān)聯(lián)。
• 在特定的范圍內(nèi)重復(fù)的可能性極小
  UUID的生成規(guī)范定義的算法主要目的就是要保證其唯一性。但這個(gè)唯一性是有限的，只在特定的范圍內(nèi)才能得到保證.
• UUID通常以36字節(jié)的字符串表示，示例如下：3F2504E0-4F89-11D3-9A0C-0305E82C3301
• 時(shí)間戳＋UUID版本號(hào)，分三段占16個(gè)字符(60bit+4bit)，
• Clock Sequence號(hào)與保留字段，占4個(gè)字符(13bit＋3bit)，
• 節(jié)點(diǎn)標(biāo)識(shí)占12個(gè)字符(48bit)

UUID 去除“-”: 32字節(jié)，但對(duì)于mysql 來(lái)說(shuō)還是字段太長(zhǎng)，字段基本無(wú)意義。

使用UUID做主鍵的優(yōu)點(diǎn)：
1、出現(xiàn)重復(fù)的機(jī)會(huì)基本無(wú)
2、分布式環(huán)境下適合大量數(shù)據(jù)中的插入和更新操作
3、跨服務(wù)器數(shù)據(jù)合并非常方便
4、安全性較高

使用UUID做主鍵的缺點(diǎn)：
1、存儲(chǔ)空間大（16 byte），因此它將會(huì)占用更多的磁盤(pán)空間
2、會(huì)降低性能
3、無(wú)法緩存大量數(shù)據(jù)
4、由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置，此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤(pán)上而從緩存中清掉，此時(shí)又要從磁盤(pán)上讀回來(lái)，這增加了很多開(kāi)銷(xiāo)，同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。
5、對(duì)于更新UUID操作：UUID的無(wú)序和空間占的大小導(dǎo)致插入時(shí)候要耗費(fèi)更多的時(shí)間去創(chuàng)建和維護(hù)索引

下面介紹下雪花算法：

SnowFlake算法介紹：

SnowFlake算法生成id的結(jié)果是一個(gè)64bit大小的整數(shù)，它的結(jié)構(gòu)如下圖：

通過(guò)多從了解和收集信息： Twitter分布式自增ID算法snowflake算法生成id：Long類(lèi)型占8個(gè)字
snowflake算法的好處：

• 生成的id是一個(gè)數(shù)字的Long類(lèi)型
• 無(wú)需鏈接數(shù)據(jù)庫(kù)或者redis，超高性能

snowflake算法的弊端：

• 每毫秒只能生成4096個(gè)id(內(nèi)理論上最多可以生成1000*(2^12)，也就是409.6萬(wàn)個(gè)ID)。
  隨著cpu不斷的進(jìn)步，每毫秒4096個(gè)id將不能滿(mǎn)足。
  可以不用擔(dān)心，即便cpu性能超過(guò)了這個(gè)值，那么只需等待到下一個(gè)毫秒
• 每毫秒重新計(jì)數(shù)，空閑時(shí)間會(huì)浪費(fèi)很多id空間。
  -系統(tǒng)時(shí)間不可回退，回退將會(huì)導(dǎo)致id重復(fù)。另：系統(tǒng)時(shí)間可以前進(jìn)，不受影響。

為了保證唯一性，業(yè)務(wù)UUID生成方式SnowFlake算法生成id：
SnowFlake算法生成Long類(lèi)型占8個(gè)字節(jié) 可在前面加2個(gè)類(lèi)型的業(yè)務(wù)字段
業(yè)務(wù)UUID=Long類(lèi)型占8個(gè)字節(jié)+ 2個(gè)英文字節(jié)（ 20個(gè)長(zhǎng)度的UUID）
業(yè)務(wù)UUID=UUID去除”-”+ 2個(gè)英文字節(jié)（ 32個(gè)長(zhǎng)度的UUID）

3.業(yè)務(wù)含義的主鍵

通過(guò)架構(gòu)規(guī)劃定義的主鍵，應(yīng)該是最有效的，利用率最高的。
比如訂單方式：
地區(qū)+UNIX_TIMESTAMP+產(chǎn)品編號(hào)+隨機(jī)號(hào)：SH+1637416219+P23+05

綜合考慮建議使用業(yè)務(wù)主鍵，如uuid和自增主鍵使用的建議如下：
1、單實(shí)例下 ，并且數(shù)據(jù)量比較大（百萬(wàn)級(jí)）時(shí)，用自增長(zhǎng)的，此時(shí)最好能考慮下安全性，做些安全措施。
2、單實(shí)例下，并且數(shù)據(jù)量沒(méi)那么大，對(duì)速度和存儲(chǔ)要求不高時(shí)，用UUID。
3、分布式的，那么首選UUID，分布式一般對(duì)速度和存儲(chǔ)要求不高。
4、分布式的，并且數(shù)據(jù)量達(dá)到千萬(wàn)級(jí)別可更高時(shí)，對(duì)速度和存儲(chǔ)有要求時(shí)，可以用自增長(zhǎng)。

總結(jié)

總之主鍵對(duì)于MySQL來(lái)說(shuō)，是非常重要的，每張表的設(shè)計(jì)的時(shí)，都應(yīng)該把主鍵默認(rèn)的加上，不管需不需要它。無(wú)主鍵的時(shí)候 可以選擇自增型的主鍵。一個(gè)表必須要有一個(gè)主鍵，為方便擴(kuò)展、松耦合，高可用的系統(tǒng)做鋪墊。
①可以使用自增加主鍵 ID bigint(20) unsigned NOT NULL AUTO_INCREMENT，
②直接使用uuid
③按照業(yè)務(wù)考慮，自定義主鍵。 （snowflake+業(yè)務(wù)id）
④自定義有業(yè)務(wù)意義的主鍵