mysql主鍵命中率_mysql主鍵問題

MySQL主鍵

一. MySQL主鍵設計原則

MySQL主鍵應當是對用戶沒有意義的。

MySQL主鍵應該是單列的，以便提高連接和篩選操作的效率(當然復合主鍵是可以的，只是不建議)

永遠也不要更新MySQL主鍵

MySQL主鍵不應包含動態變化的數據，如時間戳、創建時間列、修改時間列等

MySQL主鍵應當有計算機自動生成。

二. 主鍵添加方法

在創建表的時候，直接在字段之后，添加primary key關鍵字

create table my_pri1(

name varchar(20) not null comment ‘姓名‘,

number char(10) primary key comment ‘學號‘

)charset utf8;

在創建表的時候，在所有的字段之后，使用primary key(主鍵字段列表)來創建主鍵，此處可用來添加復合主鍵(不建議)。

create table my_pri2(

number char(10) not null comment ‘學號‘,

course char(10) not null comment ‘課程編號‘,

score tinyint unsigned default 60,

-- 增加主鍵限制，學號和課程編號應該是對應的，具有唯一性

primary key(number) //primary key(number,course)

)charset utf8;

當表創建完之后，額外追加主鍵，可以直接追加主鍵，也可以通過修改表字段的屬性追加主鍵

alter table my_pri3 modify course char(10) primary key comment ‘課程編號‘; -- 不建議使用

alter table my_pri3 add primary key(course); -- 推薦使用

三. 主鍵設計的常用方案

1. 自增ID

優點：

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

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

缺點：

1) 不支持水平分片架構，水平分片的設計當中，這種方法顯然不能保證全局唯一。

2) 表鎖，在MySQL5.1.22之前，InnoDB自增值是通過其本身的自增長計數器來獲取值，該實現方式是通過表鎖機制來完成的(AUTO-INC LOCKING)。鎖不是在每次事務完成后釋放，而是在完成對自增長值插入的SQL語句后釋放，要等待其釋放才能進行后續操作。比如說當表里有一個auto_increment字段的時候，innoDB會在內存里保存一個計數器用來記錄auto_increment的值，當插入一個新行數據時，就會用一個表鎖來鎖住這個計數器，直到插入結束。如果大量的并發插入，表鎖會引起SQL堵塞。在5.1.22之后，InnoDB為了解決自增主鍵鎖表的問題，引入了參數innodb_autoinc_lock_mode：

0：通過表鎖的方式進行，也就是所有類型的insert都用AUTO-inc locking(表鎖機制)。

1：默認值，對于simple insert 自增長值的產生使用互斥量對內存中的計數器進行累加操作，對于bulk insert 則還是使用表鎖的方式進行。

2：對所有的insert-like 自增長值的產生使用互斥量機制完成，性能最高，并發插入可能導致自增值不連續，可能會導致Statement 的 Replication 出現不一致，使用該模式，需要用 Row Replication的模式。

3) 自增主鍵不連續，對表增加屬性AUTO_INCREMENT=16時，主鍵就會從16開始自增起；實際的插入數據操作中，可能存在不連續的主鍵值。

2. UUID

優點：

1) 全局唯一性、安全性、可移植性。

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

3) 保證生成的ID不僅是表獨立的，而且是庫獨立的，在你切分數據庫的時候尤為重要

缺點：

1) 針對InnoDB引擎會徒增IO壓力，InnoDB為聚集主鍵類型的引擎，數據會按照主鍵進行排序，由于UUID的無序性，InnoDB會產生巨大的IO壓力。InnoDB主鍵索引和數據存儲位置相關(簇類索引)，uuid 主鍵可能會引起數據位置頻繁變動，嚴重影響性能。

2) UUID長度過長，一個UUID占用128個比特(16個字節)。主鍵索引KeyLength長度過大，而影響能夠基于內存的索引記錄數量，進而影響基于內存的索引命中率，而基于硬盤進行索引查詢性能很差。嚴重影響數據庫服務器整體的性能表現。

3.自定義序列

自定義序列列表：就是在庫中建一張用于生成序列的表來存儲序列信息，序列生成的策略通過程序層面來實現。如下所示，構建一張序列表：

CREATE TABLE `sequence` (

`name` varchar(50) NOT NULL,

`id` bigint(20) unsigned NOT NULL DEFAULT ‘0‘,

PRIMARY KEY (`name`)

) ENGINE=InnoDB;

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

INSERT INTO `sequence` (`name`) VALUES

(‘users‘), (‘photos‘), (‘albums‘), (‘comments‘);

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

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

SELECT LAST_INSERT_ID();

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

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

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

SELECT LAST_INSERT_ID();

這種方案的問題在于序列生成的邏輯脫離了數據庫層，由應用層負責，增加了開發復雜度。不過一般序列值都是通過Java的框架去自動生成的，具體就不在MySQL系列文章闡述。

四. 如何解決水平分片的需求

UUID

由于UUID出現重復的概率基本可以忽略，所以對分片是天生支持的。

獨立的序列庫

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

復合標識符

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

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

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

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

SELECT LAST_INSERT_ID();

這里的id初始值設定上要求不同的分片取不同的值，且必須連續。同時將每次遞增的步長設定為服務器數目。

比如有3臺機器，那么我們只要將初始值分別設置為1，2，3. 然后執行下面的語句即可：

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

SELECT LAST_INSERT_ID();

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

五. 主鍵的必要性

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

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