Discussion:
primary key 的資料型態
(时间太久无法回复)
只能靜靜看著了 ...
2007-08-13 07:41:35 UTC
Permalink
最近在看 sql 的書,書上的範例跟說明都沒有提到將 char 設為 primary key 有什麼
問題,但是一直以來都習慣把每個 table 設一個 auto increment 的整數欄位來當
primary key,據說是對 index 或 join 的效率比較好。

請問用 char 或 varchar 當 primary key 真的會影響效能到不能忽視的程度?
用 auto increment 的整數來當 primary key 是一個好習慣嗎?

使用的 DBMS 是 mySql。謝謝 :)
--
夫兵者不祥之器物或惡之故有道者不處君子居則貴左用兵則貴右兵者不祥之器非君子
之器不得已而用之恬淡為上勝而不美而美之者是樂殺人夫樂殺人者則不可得志於天下
矣吉事尚左凶事尚右偏將軍居左上將軍居右言以喪禮處之殺人之眾以哀悲泣之戰勝以
喪禮處之道常無名樸雖小天下莫能臣侯王若能守之萬物將自賓天地相合以降甘露民莫
之令而自均始制有名名亦既有夫亦將知止知 219-81-202-145.dynamic.tfn.net.tw海
Life of Music
2007-08-13 17:14:46 UTC
Permalink
�� �ޭz�mstarjou (�u���R�R�ݵۤF ...)�n���ʨ��G
�аݥ� char �� varchar �� primary key �u���|�v�T�į��줣�੿�����{�סH
�����A��int�H�~���F����PK�O�@�w�|�����C��

���]�pweiyu�j�j�����A�D�j�ƪ����Ƥ�����CPU�Ө��A�u�n�@��instruction�N�n�F

�ܩ��O���O�|�v�T�į��줣�੿�����{�סA���p�ڨä����o

���O���N�ڭ̨ϥήM��DBS�����]�P�ت��ӻ�

�ڻ{���o�˪��į઺�v�T�p�G�O�b10%�H���N�٥i�H����

����DBS�q�`�ä��O�C�b���ئa��


�䦸�A�ϥ�Auto_increment�O���O�Ӧn�ߺD�H
�����ܡA�u���O�v�u�O�ڭӤH�N�DzߤW�M�g���W���[�I
�|�Ҩӻ��A�@�ӷ|�����ƩM�L�̪��U���ݩʡ]�b���Hsn����serial number�^

[member]
sn, id, password, ...
[member_attr]
sn, member_sn, attr, value

�{�b���]�ڭn�̬Y���ݩʨӷj�M�|���ASQL�p�U�G
SELECT m.id FROM member m, member_attr a
WHERE m.sn = a.member_sn
AND a.attr = 'birthday'
AND a.value LIKE '%-01-07'


�i�O�p�G�ڪ����Ʈw�[�c�O�o�ˡG
[member]
id, password, ...
[member_attr]
sn, member_id, attr, value

���ڪ�SQL�N�u�n�GSELECT member_id FROM member_attr WHERE ...
�۹��Ө������u�ADBS�]�i�H�`�٤@�Ǹ�table���B�@


���O�o�˪����k�]�ä��O�S�����I
���軡�ڦA�Y�����Ʈw�[�c..�]�HPK����Primary Key�^

[member] PK: (id)
id, password, ...
[member_attr] PK: (member_id, attr)
member_id, attr, value

���I���@��ӫܩ����A�C��member��attr�u�঳�@�ӭ�
�p�G���O"�g��"�o���ݩʷQ�n��}�C���ܴN�����K�F

���I���G�N�OPK���զ��V�h�A���F��DBS���t���|�ܤj�]index���_�Ӥ��������^���~
���ڤW�n�@�s�ת��ɭԤ]�|�����·�
���]�O�쥻���[�c�A�ڭ̦��@�ӭt�d�R�����ݩʪ�deleteAttr.php
�����쥻�u�ndeleteAttr.php?member_attr=3
�|�ܦ�deleteAttr.php?member_id=&attr=birthday�]�����X:P�^
�۹��Ө��O��������


���L�A�ڭ̦^�Y�ݲĤG�ӯ��I�A���u���O���I�ܡH
�b�o�˪��[�c�U�A�����ڭ̤ϦӤ����֯����o���ӵ����ƪ�"�N�q"
�q���}���ڭ̴N�i�H�ܧ֪����D�n�R�����O���ͤ��ݩ�
�Ӥ��ΦA�h�h���@���d��

�o�˪��S�ʡA�b�Y�Ǫ��p�U�O���I�n�B��
���軡�N��deleteAttr.php�����e�Ө��A�쥻���[�c�U�ݭn�g���G
<?php
// ....DB connection, login check or whatever
$sql = "
SELECT id FROM member m, member_attr a
WHERE m.sn = a.member_sn
AND a.sn = %d
";
$attrOwner = $db->getOnw(sprintf($sql, $_GET['member_attr']));
if($_SESSION['userid']!=$attrOwner) exit('Authentication denied!');
else {
$sql = "DELETE FROM member_attr WHERE sn = %d";
$db->query(sprintf($sql, $_GET['member_attr']));
}
// .. other works
?>
���ڤW�R�����y�k���M��²���A���O�o�n�����@���v���T�{

�Ϥ��Y�O�έק蠟�᪺�[�c�G
<?php
// ....DB connection, login check or whatever
if($_SESSION['userid']!=$_GET['member_id'])
exit('Authentication denied!');
else {
$sql = "
DELETE FROM member_attr
WHERE member_id = '%s' AND attr = '%s'
";
$db->query(sprintf($sql, $_GET['member_id'], $_GET['attr']));
}
// .. other works
?>
SQL�O���F�@�I�A���O�o���ڭ̤��Φh���v���ˬd��query


�H�W�O�@�I�p�Ҥl�A�ܩ����Ӥ��k�n�A�ڷQ��������
�D�n���O�ݹ��]�p�̦Ө��A���ˤ������K
�ܩ��t�װ��D�A�ڴN�����򭫵��F
�����u�n�n�D���ܡA�ڭ���Ӧۤv�]�p�w���A�åB�q�զX�y���}�l�ۤv�gDBS
�����M�ڭ̨S���l�O�o�˰��C

--
�~ �~ �~ �~���~�� P_LifeOMusic
�x �W ���~�w�� �~�w�� �� �x�x�x�x �� ���~�w�w�W�~�w��
�x �x �x�颤�� �x �x �x �x�����x �x �x���w���x�x
���w���� ���|�w�� ������ �� ��' '�� ���w���w�w�����|�w��
--
kong �b 07/08/14 1:14:46 �q 218.170.121.94 �ק��o�g�峹
只能靜靜看著了 ...
2007-08-14 03:56:53 UTC
Permalink
※ 引述《kong (Life of Music)》之銘言:
※ 引述《starjou (只能靜靜看著了 ...)》之銘言:
Post by 只能靜靜看著了 ...
請問用 char 或 varchar 當 primary key 真的會影響效能到不能忽視的程度?
用 auto increment 的整數來當 primary key 是一個好習慣嗎?
首先,用int以外的東西當PK是一定會比較慢的
原因如weiyu大大說的,非大數的整數比較對CPU而言,只要一個instruction就好了
至於是不是會影響效能到不能忽視的程度,抱歉我並不曉得
但是單就我們使用套裝DBS的原因與目的來說
我認為這樣的效能的影響如果是在10%以內就還可以接受
畢竟DBS通常並不是慢在那種地方
其次,使用Auto_increment是不是個好習慣?
說實話,「不是」只是我個人就學習上和經驗上的觀點
舉例來說,一個會員資料和他們的各種屬性(在此以sn表示serial number)
[member]
sn, id, password, ...
[member_attr]
sn, member_sn, attr, value
現在假設我要依某個屬性來搜尋會員,SQL如下:
SELECT m.id FROM member m, member_attr a
WHERE m.sn = a.member_sn
AND a.attr = 'birthday'
AND a.value LIKE '%-01-07'
可是如果我的資料庫架構是這樣:
[member]
id, password, ...
[member_attr]
sn, member_id, attr, value
那我的SQL就只要:SELECT member_id FROM member_attr WHERE ...
相對而言比較短,DBS也可以節省一些跨table的運作
但是這樣的做法也並不是沒有缺點
比方說我再縮減資料庫架構..(以PK表示Primary Key)
[member] PK: (id)
id, password, ...
[member_attr] PK: (member_id, attr)
member_id, attr, value
缺點之一應該很明顯,每個member的attr只能有一個值
如果像是"經歷"這種屬性想要分開列的話就不方便了
缺點之二就是PK的組成越多,除了對DBS的負擔會變大(index做起來比較複雜)之外
實際上要作編修的時候也會比較麻煩
假設是原本的架構,我們有一個負責刪除該屬性的deleteAttr.php
那麼原本只要deleteAttr.php?member_attr=3
會變成deleteAttr.php?member_id=&attr=birthday(控制碼:P)
相對而言是比較長的
不過,我們回頭看第二個缺點,它真的是缺點嗎?
在這樣的架構下,其實我們反而比較快能夠得知該筆資料的"意義"
從網址中我們就可以很快的知道要刪除的是的生日屬性
而不用再多去做一次查詢
這樣的特性,在某些狀況下是有點好處的
比方說就該deleteAttr.php的內容而言,原本的架構下需要寫成:
<?php
// ....DB connection, login check or whatever
$sql = "
SELECT id FROM member m, member_attr a
WHERE m.sn = a.member_sn
AND a.sn = %d
";
$attrOwner = $db->getOnw(sprintf($sql, $_GET['member_attr']));
if($_SESSION['userid']!=$attrOwner) exit('Authentication denied!');
else {
$sql = "DELETE FROM member_attr WHERE sn = %d";
$db->query(sprintf($sql, $_GET['member_attr']));
}
// .. other works
?>
實際上刪除的語法雖然很簡單,但是卻要先做一次權限確認
反之若是用修改之後的架構:
<?php
// ....DB connection, login check or whatever
if($_SESSION['userid']!=$_GET['member_id'])
exit('Authentication denied!');
else {
$sql = "
DELETE FROM member_attr
WHERE member_id = '%s' AND attr = '%s'
";
$db->query(sprintf($sql, $_GET['member_id'], $_GET['attr']));
}
// .. other works
?>
SQL是長了一點,但是這次我們不用多做權限檢查的query
以上是一點小例子,至於哪個方法好,我想見仁見智
主要仍是看對設計者而言,怎樣比較方便
至於速度問題,我就不那麼重視了
畢竟真要要求的話,我們應該自己設計硬體,並且從組合語言開始自己寫DBS
但顯然我們沒有餘力這樣做。
我也想過以有意義的字串做為 key 會比較方便,不過這個方便性只在於
要只看某個延伸資料表時,而且那個所謂有意義的字串是真的「有意義」。

譬如如果拿身份證字號來當 key,那你得要看到那個身份證字號就知道
他代表誰才行。

即使是 member id,如果你的會員數多,其實一樣是個無意義的字串。

而現在 mySql 都支援 view 了,所以這個操作上的便利性,即使用
整數,一樣可以很方便的達成。

一般來說,database 裡頭最多的動作就是 select,
用 int 來做 join key,或用 char/varchar 來做 join key 在效率上
應該是有所差別,如果 select 是最常做的動作,那這個差別累積起來,
說不定很可觀。

另一個用 auto_increment 的好處是,資料表設計多了一個慣性。
這其實是懶人的好處啦 ... 我接觸過的 mySql 跟 Access 都提供了
這個功能,好像在鼓勵我這樣做一樣 XD

我覺得關鍵還是用 string 當 key 做 join 時的效率影響到底有多大?
如果確定影響可被忽視,其實我也傾向於用 string 當 PK。
--
夫兵者不祥之器物或惡之故有道者不處君子居則貴左用兵則貴右兵者不祥之器非君子
之器不得已而用之恬淡為上勝而不美而美之者是樂殺人夫樂殺人者則不可得志於天下
矣吉事尚左凶事尚右偏將軍居左上將軍居右言以喪禮處之殺人之眾以哀悲泣之戰勝以
喪禮處之道常無名樸雖小天下莫能臣侯王若能守之萬物將自賓天地相合以降甘露民莫
之令而自均始制有名名亦既有夫亦將知止知止219-81-195-65.dynamic.tfn.net.tw海
Loading...