Mysql 建庫建表技巧分享
更新時間:2011年07月20日 21:08:48 作者:
本文中說到的“建”,并非單純的建一個庫,或是建一張表,而是你建好的庫和表在項目的運營中,是否能應付各種事件,下面我說說幾個我在項目中遇到的問題以及處理的方法,算是一個小小的心得,給大家分享下。
一、兩表之間若有關聯(lián),你是否還在用主鍵進行關聯(lián)?
比如現(xiàn)在有2張表,一張新聞欄目表,一張新聞表,現(xiàn)在兩張表需要進行關聯(lián),我想大多數(shù)人的做法肯定是在新聞表里建一個新聞欄目id,然后把新聞欄目表里的主鍵ID(自增)寫到這個字段里,通過這樣進行兩表關聯(lián)。
如果你是這樣做的,趕緊改掉這個習慣吧。也許你會問為什么,欄目id是主鍵啊,又是自增的,為什么這樣操作不行?原因其實很簡單,欄目我們會增加,也會刪除,刪除就會造成主鍵id之間會有斷號的情況,由于主鍵設置為自增,也就是說你之前刪掉的欄目,再進行添加,id是不會去補上哪個空缺的,而是一直遞增。這樣就會造成一種情況,如果那天對數(shù)據(jù)庫進行優(yōu)化,把主鍵進行了重新排序(暫時沒有找到mysql優(yōu)化軟件會優(yōu)化主鍵,但是可以通過代碼刪除主鍵,然后從新建立自增主鍵來實現(xiàn)主鍵重新排序),那就徹底杯具了,欄目和文章完全對不上號了。所以我建議兩表之間關聯(lián)不用主鍵,而是單獨建一個編號的字段,我們這里可以用mysql的uuid()函數(shù)做為編號,相關文獻可以參考《UUID做主鍵好還是不好》,只所以一張表要2個主鍵,一個物理主鍵(自增id),一個邏輯主鍵(UUID),原因是:對于InnoDB這種聚集主鍵類型的引擎來說,數(shù)據(jù)會按照主鍵進行排序,由于UUID的無序性,InnoDB會產(chǎn)生巨大的IO壓力,此時不適合使用UUID做物理主鍵,可以把它作為邏輯主鍵,物理主鍵依然使用自增ID。至于性能,我本地測了下基本上沒差異,網(wǎng)上也有人做了10W條數(shù)據(jù)的測試——《實測MYSQL UUID性能》。
二、統(tǒng)一把主鍵類型設為bigint吧
bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數(shù)據(jù),存儲大小為8個字節(jié)。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數(shù)據(jù),存儲大小為4個字節(jié)。存儲空間擴大一倍,而存儲數(shù)據(jù)卻擴大N倍,再加上主鍵是一個自增的字段,我們根本無法控制它會自增到多少數(shù)值,所以我通常在建表的時候,主鍵類型都是設為bigint的,同樣,上面提到的編號字段類型也是bigint。
三、不要把varchar長度設太“死”
這也是我之前經(jīng)常犯得一個毛病,比如手機,我就設置為varchar(11),郵編設置成varchar(6),姓名設置成varchar(10)等等等等,看似每個字段都設置得很嚴謹,但是在項目實際進行中,這完全就是自找苦吃,比如手機,用戶偏偏就要在手機號前輸個0,又比如郵編,如果用戶輸入的是全角的數(shù)字呢?姓名就更不用說了,萬一是個少數(shù)民族的人,名字七八個字。所以我建議,既然定義為varchar,就代表不會涉及到計算,何不干脆定義一個通用的長度,比如varchar(50),如果真要限制長度,用程序去判斷,不要讓數(shù)據(jù)庫來限制,不然用戶輸了一長串,結果mysql就存了前幾個字符,讓人覺得這程序有問題。
還有就是,如果你是做cms這種通用后臺,更別把字段限制得太“死”,因為你無法預料之后的每個項目的需求,所以還是把varchar設大一點,我現(xiàn)在是統(tǒng)一都設為255,如果很有可能會超過255的字段,比如URL,我就干脆設置成text,一勞永逸。
四、為常用的搜索字段建立索引吧
不解釋,但不要盲目建立索引。
五、歡迎您的回復補充
比如現(xiàn)在有2張表,一張新聞欄目表,一張新聞表,現(xiàn)在兩張表需要進行關聯(lián),我想大多數(shù)人的做法肯定是在新聞表里建一個新聞欄目id,然后把新聞欄目表里的主鍵ID(自增)寫到這個字段里,通過這樣進行兩表關聯(lián)。
如果你是這樣做的,趕緊改掉這個習慣吧。也許你會問為什么,欄目id是主鍵啊,又是自增的,為什么這樣操作不行?原因其實很簡單,欄目我們會增加,也會刪除,刪除就會造成主鍵id之間會有斷號的情況,由于主鍵設置為自增,也就是說你之前刪掉的欄目,再進行添加,id是不會去補上哪個空缺的,而是一直遞增。這樣就會造成一種情況,如果那天對數(shù)據(jù)庫進行優(yōu)化,把主鍵進行了重新排序(暫時沒有找到mysql優(yōu)化軟件會優(yōu)化主鍵,但是可以通過代碼刪除主鍵,然后從新建立自增主鍵來實現(xiàn)主鍵重新排序),那就徹底杯具了,欄目和文章完全對不上號了。所以我建議兩表之間關聯(lián)不用主鍵,而是單獨建一個編號的字段,我們這里可以用mysql的uuid()函數(shù)做為編號,相關文獻可以參考《UUID做主鍵好還是不好》,只所以一張表要2個主鍵,一個物理主鍵(自增id),一個邏輯主鍵(UUID),原因是:對于InnoDB這種聚集主鍵類型的引擎來說,數(shù)據(jù)會按照主鍵進行排序,由于UUID的無序性,InnoDB會產(chǎn)生巨大的IO壓力,此時不適合使用UUID做物理主鍵,可以把它作為邏輯主鍵,物理主鍵依然使用自增ID。至于性能,我本地測了下基本上沒差異,網(wǎng)上也有人做了10W條數(shù)據(jù)的測試——《實測MYSQL UUID性能》。
二、統(tǒng)一把主鍵類型設為bigint吧
bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數(shù)據(jù),存儲大小為8個字節(jié)。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數(shù)據(jù),存儲大小為4個字節(jié)。存儲空間擴大一倍,而存儲數(shù)據(jù)卻擴大N倍,再加上主鍵是一個自增的字段,我們根本無法控制它會自增到多少數(shù)值,所以我通常在建表的時候,主鍵類型都是設為bigint的,同樣,上面提到的編號字段類型也是bigint。
三、不要把varchar長度設太“死”
這也是我之前經(jīng)常犯得一個毛病,比如手機,我就設置為varchar(11),郵編設置成varchar(6),姓名設置成varchar(10)等等等等,看似每個字段都設置得很嚴謹,但是在項目實際進行中,這完全就是自找苦吃,比如手機,用戶偏偏就要在手機號前輸個0,又比如郵編,如果用戶輸入的是全角的數(shù)字呢?姓名就更不用說了,萬一是個少數(shù)民族的人,名字七八個字。所以我建議,既然定義為varchar,就代表不會涉及到計算,何不干脆定義一個通用的長度,比如varchar(50),如果真要限制長度,用程序去判斷,不要讓數(shù)據(jù)庫來限制,不然用戶輸了一長串,結果mysql就存了前幾個字符,讓人覺得這程序有問題。
還有就是,如果你是做cms這種通用后臺,更別把字段限制得太“死”,因為你無法預料之后的每個項目的需求,所以還是把varchar設大一點,我現(xiàn)在是統(tǒng)一都設為255,如果很有可能會超過255的字段,比如URL,我就干脆設置成text,一勞永逸。
四、為常用的搜索字段建立索引吧
不解釋,但不要盲目建立索引。
五、歡迎您的回復補充
相關文章
mysql問題之slow log中出現(xiàn)大量的binlog dump記錄的解決方法
今天在查看mysql中發(fā)現(xiàn)比較慢,然后我使用了slow log,發(fā)現(xiàn)出現(xiàn)了大量的binlog dump記錄,下面我來給大家整理一下這個問題的解決辦法2013-09-09如何在SQL Server中實現(xiàn) Limit m,n 的功能
本篇文章是對在SQL Server中實現(xiàn) Limit m,n功能的方法進行了詳細的分析介紹,需要的朋友參考下2013-06-06