iT邦幫忙

0

大家都是怎麼定義自己資料庫裡面的primary key?

最近在思考~如何在資料庫裡設計一個PK。
是數字好呢?還是字串好?
是GUID好呢?還是自行定義的字串+流水號好呢?
GUID是沒有重複性的問題,但拿來做PK好像又太長!
那直接拿流水號或是自行定義的字串外加流水號呢?
因為字串+流水號是一個固定長度,但直接使用流水號長度會比較短!
都不知道該怎麼拿捏才好?
所以請教在大家,你們取PK都是考慮了哪些才決定的呢?
謝謝!

16
wiseguy
iT邦超人 1 級 ‧ 2013-12-18 20:56:28
最佳解答

這個問題可以分成三個層次來回答:

從理論方面,在資料庫的教材中題到,primary key 是該 table 中具有唯一性與識別性的資料,若沒有單一資料具有此特性,也可以是兩個以上的資料合成具有此特性的 key 值,即可做為 primary key。是故,比如身份證號可以是個人資料的 PK,比如時間日期與發生標的物兩個欄位可以合成 Log 的 PK。

從實作方面,牽涉到開發工具、效能、使用的 DB 是否支援以及其最佳化的依據,通常會是以數值欄位、避免複合欄位等等為實作考量,因此很常見的是每個 table 都會開一個 auto increment 的欄位來做為 PK,不過這也違反資料庫理論中的《不在一個 table 中使用兩個具有重覆屬性的欄位》這個原則。比如個人資料 table 中,身份證號已經可以當 PK,但你又加一個 auto increment 的欄位來做為 PK,這就是浪費。

而從我的觀點,怎麼選 PK 只有一個原則:我的經驗。

看更多先前的回應...收起先前的回應...
Ethan Jhuang iT邦研究生 3 級 ‧ 2013-12-19 08:08:57 檢舉

意思就是要請wiseguy到家裡泡茶的意思
灑花灑花灑花

我這邊想提問的PK比較偏向資料庫內部的KEY,也就是此PK對使用者而言是沒有什麼意義的。
若是以會員資料表跟學生資料做比較的話,
會員表的帳號會是唯一值,但我會另外給一個member_key當作PK用來操作DML,避免一些大小寫或是邏輯上的問題。
而學生資料表當然就學號而言就是個很好的PK了,而學號對學生是有意義的。
是想請教類似這種需要自行定義PK的時候,該怎麼去選擇一個好的key值?

wiseguy iT邦超人 1 級 ‧ 2013-12-19 14:32:09 檢舉

看你這個系統要求的是什麼?效率?程式好維護?table資料量大不大?....
所以我才說,到最後決定 PK 的關鍵,通常是開發者自己的經驗。

可否簡單說一下當初你規畫PK的時候,所考量的問題及最後選擇哪一類型的PK?
這只是我最近突然想瞭解的問題,我也沒什麼CASE可以提問。

wiseguy iT邦超人 1 級 ‧ 2013-12-20 10:56:00 檢舉

以下是我的經驗,不是標準,也不是絕對正確,僅供參考:

第一考量:簡單就好。通常會先思考資料本身就能做 PK,那當然就用資料欄位。
第二考量:效率至上。資料量巨大、且有大量 join,那就考慮使用 auto increment 欄位當PK。
第三考量:程式單純且易維護。資料本身需要三個以上的欄位才能做 PK,那也考慮用 auto increment 欄位當PK。
第四考量:資料庫對PK或其它 index 有最佳化,那就看它 prefer 用什麼欄位。

比較好奇的問一下,有人會選擇GUID做為PK的嗎?
在什麼樣的情況下會選擇GUID做為PK呢?
就我看來,GUID長度太長又沒有什麼規則性,
但若是把GUID存成binary(假設是DB internal key),
變成電腦可以讀的語言,這樣子會比較有好嗎?

wiseguy iT邦超人 1 級 ‧ 2013-12-20 14:54:51 檢舉

沒比較好。對DB而言,它就是個字串。除了固定長度是個微不足道的優點之外,其它跟使用一般字串當 PK 幾乎是相同的成本。

4
slime
iT邦大師 1 級 ‧ 2013-12-18 20:44:26

PK 完再決定....(大誤)

個人會考慮:

  1. 將來可能筆數? ( 2 年, 5 年, 10 年, 終生?)
  2. 查詢方便性? (是否需要人工查詢或核對?)
  3. 除了系統外, 是否有使用於其他地方? (例如當成單據號?)

大約這樣去考慮.

8
sam0407
iT邦高手 1 級 ‧ 2013-12-19 12:05:34

要替Table加上PK,原則很簡單,就是您最常用那個欄位(或那幾個欄位,大部份情形是兩個,最好不要超過3個)作條件來查詢這個Table內的某一筆特定資料。

例如:員工資料表就是用員工編號(或身份證字號),訂單主檔就是訂單編號,訂單明細檔就是用訂單編號+項次。

因為大部份資料庫都會以PK作為資料存取優化的條件,例如在SQL Server中會依PK值作排序來存放每一筆記錄的順序,讓您在用PK作查詢條件時,能以最快的速度將資料取出。

8
zuyan
iT邦好手 1 級 ‧ 2013-12-19 21:19:12

學過的經驗告訴我 . 身份證字號理論上是 唯一性 但是 實務上不是唯一
故以系統的 PK 當作唯一是比較保險的做法
很多事情難保不會有例外

8
一級屠豬士
iT邦高手 1 級 ‧ 2013-12-20 16:43:06

前面幾位已經說明很多,敝人在此不贅述.
樓主似乎是對UUID 與一般自動增加的方式,想做個了解.
在此作個測試.
產生測試資料,先參考敝人之前鐵人賽裡的測試資料的產生.
http://ithelp.ithome.com.tw/question/10133871

<pre class="c" name="code">SET SESSION max_heap_table_size = 1024 * 1024 * 200; 

CALL prc_filler(1000000);
Query OK, 1 row affected (11.30 sec)

select count(1) from filler;
+----------+
| count(1) |
+----------+
|  1000000 |
+----------+

這樣就建立了一個一百萬筆的Table.
接著來建立幾個測試Table

CREATE TABLE ithelp1220a(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
pad CHAR(200),
instime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ithelp1220b(
id INT UNSIGNED NOT NULL PRIMARY KEY,
pad CHAR(200),
instime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ithelp1220c(
id CHAR(36) NOT NULL PRIMARY KEY,
pad CHAR(200),
instime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ithelp1220d(
id BINARY(16) NOT NULL PRIMARY KEY,
pad CHAR(200),
instime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

利用filler來產生測試資料.

INSERT INTO ithelp1220a(pad)
SELECT REPEAT('a', 200)
  FROM filler;

Query OK, 1000000 rows affected (37.02 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

INSERT INTO ithelp1220b(id, pad)
SELECT id, REPEAT('a', 200)
  FROM filler;

Query OK, 1000000 rows affected (31.47 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

INSERT INTO ithelp1220c(id, pad)
SELECT UUID(), REPEAT('a', 200)
  FROM filler;

Query OK, 1000000 rows affected (42.51 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

INSERT INTO ithelp1220d(id, pad)
SELECT UNHEX(REPLACE(UUID(),'-','')), REPEAT('a', 200)
  FROM filler;

Query OK, 1000000 rows affected (35.02 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

觀察空間大小

SELECT table_name  
     , data_length / 1024 AS 'data(K)'
     , row_format
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name LIKE 'ithelp1220%'
 ORDER BY table_name;

+-------------+-------------+------------+
| table_name  | data(K)     | row_format |
+-------------+-------------+------------+
| ithelp1220a | 243456.0000 | Compact    |
| ithelp1220b | 243456.0000 | Compact    |
| ithelp1220c | 277504.0000 | Compact    |
| ithelp1220d | 233376.0000 | Compact    |
+-------------+-------------+------------+

接著來觀察 1000萬筆資料的情形.
依樣葫蘆,建立3個空的Table,比較自動增加與兩種UUID的.

CREATE TABLE ithelp1220a1 LIKE ithelp1220a;

CREATE TABLE ithelp1220c1 LIKE ithelp1220c;

CREATE TABLE ithelp1220d1 LIKE ithelp1220d;

INSERT INTO ithelp1220a1(pad)
SELECT REPEAT('a', 200)
  FROM ( SELECT id
           FROM filler) a,
       ( SELECT id
           FROM filler
          LIMIT 10) b;

Query OK, 10000000 rows affected (6 min 9.34 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

INSERT INTO ithelp1220c1(id, pad)
SELECT UUID(), REPEAT('a', 200)
  FROM ( SELECT id
           FROM filler) a,
       ( SELECT id
           FROM filler
          LIMIT 10) b;

Query OK, 10000000 rows affected (10 min 19.08 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

INSERT INTO ithelp1220d1(id, pad)
SELECT UNHEX(REPLACE(UUID(),'-','')), REPEAT('a', 200)
  FROM ( SELECT id
           FROM filler) a,
       ( SELECT id
           FROM filler
          LIMIT 10) b;

Query OK, 10000000 rows affected (10 min 39.95 sec)
Records: 10000000  Duplicates: 0  Warnings: 0


觀察空間大小
SELECT table_name  
     , data_length / 1024 AS 'data(K)'
     , row_format
  FROM INFORMATION_SCHEMA.TABLES  
 WHERE table_name IN ('ithelp1220a1', 'ithelp1220c1', 'ithelp1220d1')
 ORDER BY table_name;

+--------------+--------------+------------+
| table_name   | data(K)      | row_format |
+--------------+--------------+------------+
| ithelp1220a1 | 2311168.0000 | Compact    |
| ithelp1220c1 | 3109888.0000 | Compact    |
| ithelp1220d1 | 2820096.0000 | Compact    |
+--------------+--------------+------------+

使用UUID方式,消耗時間較久,
空間會佔用較多,這是資料庫系統內部實作的關係.
若有想要進一步了解,篇幅較長,改日再探討吧.

看更多先前的回應...收起先前的回應...

我看了產生測試資料那邊文章,我有個疑問想請教你。
我自己測試了兩種產生資料的方法,
第一種是使用你的方法,先產生filler,再去塞資料。
而第二種是我把塞資料的語法直接寫在SP裡透過迴圈塞。
但這兩個方法的效率有著天差地別的結果,想請教一下是什麼原因?

一般來說,執行速度越接近核心會越快.
以這次的情境來說.我只用一道SQL Command就產生了.
用SP,可能就是很多道SQL Command.
用外部程式,也可能是很多道SQL Command,再加上網路傳輸(也許?).
所以這個情境下,我推測是以下順序.
SQL Command > SP > 外部程式.
當然變數會很多,這也不是絕對的,一切以實際為準.
加上我那個filler是用記憶體引擎,相對會更快.
所以我特別寫那篇,可以提供有需要產生測試資料的人參考.
至於執行速度方面,也是有人有不同的看法.
例如: http://ithelp.ithome.com.tw/question/10120801?tab=opinion#ooa\_hash

<pre class="c" name="code">
CREATE PROCEDURE prc_filler2(INT)    
BEGIN    
        DECLARE _cnt INT;    
        SET _cnt = 1;    
        WHILE _cnt <= cnt DO    
                INSERT    
                INTO    ithelp1225(amount, price)    
                SELECT CEILING(RAND(201310) * 1000) + 100,CEILING(RAND(201310) * 1000) + 30.00;    
                SET _cnt = _cnt + 1;    
        END WHILE;    
END;

這是我照你的CODE抄寫,只是把INSERT的地方直接改成ithelp1225而不填入filler。
這差異應該是若是使用SP直接做INSERT,那就是進行了100萬次的insert動作,
而使用filler則只是進行一次的insert的動作。
我想會造成效率有著天差地遠的原因應該就是在這了。

產生 filler的目的,很單純,就是產生一個table,有一定數量.
好方便後續用來join,就可以產生其他資料.
例如產生1萬筆,就可以用來再與自己join加上limit,這樣就可以產生自己想要的數量.
如例子中,並未直接產生1000萬筆,而是利用100萬筆的fille,join產生的.
把這種需要許多次insert,用一個Table先存放,再來join產生,
速度就會變快.
也可以用來產生日曆,這對系統開發來說,就會很方便.

恩恩~我大致上瞭解了FILLER的用意了。
可是最後有提到產生日曆,這邊我就比較不懂了?
另外實作上還有時麼例子是可以使用這種方法會比較方便的?

產生日曆只是一種應用,例如要產生一些報表,假設原本的Table裡的資料只有進貨日期,但是報表需要有每一天,這時候可以利用日曆的Table跟進貨Table 作left join.
很多方面都可以使用,主要就是要來生一些循序的東西.

謝謝 hitomitanaka!!
愛你愛你

6
pantc328
iT邦研究生 1 級 ‧ 2013-12-22 10:13:39

1.系統OO化.別人都用正規化.正規化我不會,我習慣用物件導向方式
2.小系統用Int,流水號,不要用字串,int 是電腦處理基礎單位,所以排序,索引..最快
3.分散式DB用GUID 全世界每一個資料拋過來都不會沖到,而且是Client 端,App端產生
4.PK給App 給 DB 關聯看,所以只要不重複,容易電腦計算,如果給Users識別用,我會再用一組有規律的Key讓Users記

我要發表回答

立即登入回答