Mysql 建庫建表技巧分享
更新時(shí)間：2011年07月20日 21:08:48 作者：
本文中說到的“建”，并非單純的建一個(gè)庫，或是建一張表，而是你建好的庫和表在項(xiàng)目的運(yùn)營(yíng)中，是否能應(yīng)付各種事件，下面我說說幾個(gè)我在項(xiàng)目中遇到的問題以及處理的方法，算是一個(gè)小小的心得，給大家分享下。
一、兩表之間若有關(guān)聯(lián)，你是否還在用主鍵進(jìn)行關(guān)聯(lián)？
比如現(xiàn)在有2張表，一張新聞欄目表，一張新聞表，現(xiàn)在兩張表需要進(jìn)行關(guān)聯(lián)，我想大多數(shù)人的做法肯定是在新聞表里建一個(gè)新聞欄目id，然后把新聞欄目表里的主鍵ID（自增）寫到這個(gè)字段里，通過這樣進(jìn)行兩表關(guān)聯(lián)。
如果你是這樣做的，趕緊改掉這個(gè)習(xí)慣吧。也許你會(huì)問為什么，欄目id是主鍵啊，又是自增的，為什么這樣操作不行？原因其實(shí)很簡(jiǎn)單，欄目我們會(huì)增加，也會(huì)刪除，刪除就會(huì)造成主鍵id之間會(huì)有斷號(hào)的情況，由于主鍵設(shè)置為自增，也就是說你之前刪掉的欄目，再進(jìn)行添加，id是不會(huì)去補(bǔ)上哪個(gè)空缺的，而是一直遞增。這樣就會(huì)造成一種情況，如果那天對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化，把主鍵進(jìn)行了重新排序（暫時(shí)沒有找到mysql優(yōu)化軟件會(huì)優(yōu)化主鍵，但是可以通過代碼刪除主鍵，然后從新建立自增主鍵來實(shí)現(xiàn)主鍵重新排序），那就徹底杯具了，欄目和文章完全對(duì)不上號(hào)了。所以我建議兩表之間關(guān)聯(lián)不用主鍵，而是單獨(dú)建一個(gè)編號(hào)的字段，我們這里可以用mysql的uuid()函數(shù)做為編號(hào)，相關(guān)文獻(xiàn)可以參考《UUID做主鍵好還是不好》，只所以一張表要2個(gè)主鍵，一個(gè)物理主鍵（自增id），一個(gè)邏輯主鍵（UUID），原因是：對(duì)于InnoDB這種聚集主鍵類型的引擎來說，數(shù)據(jù)會(huì)按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會(huì)產(chǎn)生巨大的IO壓力，此時(shí)不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。至于性能，我本地測(cè)了下基本上沒差異，網(wǎng)上也有人做了10W條數(shù)據(jù)的測(cè)試——《實(shí)測(cè)MYSQL UUID性能》。
二、統(tǒng)一把主鍵類型設(shè)為bigint吧
bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數(shù)據(jù)，存儲(chǔ)大小為8個(gè)字節(jié)。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數(shù)據(jù)，存儲(chǔ)大小為4個(gè)字節(jié)。存儲(chǔ)空間擴(kuò)大一倍，而存儲(chǔ)數(shù)據(jù)卻擴(kuò)大N倍，再加上主鍵是一個(gè)自增的字段，我們根本無法控制它會(huì)自增到多少數(shù)值，所以我通常在建表的時(shí)候，主鍵類型都是設(shè)為bigint的，同樣，上面提到的編號(hào)字段類型也是bigint。
三、不要把varchar長(zhǎng)度設(shè)太“死”
這也是我之前經(jīng)常犯得一個(gè)毛病，比如手機(jī)，我就設(shè)置為varchar(11)，郵編設(shè)置成varchar(6)，姓名設(shè)置成varchar(10)等等等等，看似每個(gè)字段都設(shè)置得很嚴(yán)謹(jǐn)，但是在項(xiàng)目實(shí)際進(jìn)行中，這完全就是自找苦吃，比如手機(jī)，用戶偏偏就要在手機(jī)號(hào)前輸個(gè)0，又比如郵編，如果用戶輸入的是全角的數(shù)字呢？姓名就更不用說了，萬一是個(gè)少數(shù)民族的人，名字七八個(gè)字。所以我建議，既然定義為varchar，就代表不會(huì)涉及到計(jì)算，何不干脆定義一個(gè)通用的長(zhǎng)度，比如varchar(50)，如果真要限制長(zhǎng)度，用程序去判斷，不要讓數(shù)據(jù)庫來限制，不然用戶輸了一長(zhǎng)串，結(jié)果mysql就存了前幾個(gè)字符，讓人覺得這程序有問題。
還有就是，如果你是做cms這種通用后臺(tái)，更別把字段限制得太“死”，因?yàn)槟銦o法預(yù)料之后的每個(gè)項(xiàng)目的需求，所以還是把varchar設(shè)大一點(diǎn)，我現(xiàn)在是統(tǒng)一都設(shè)為255，如果很有可能會(huì)超過255的字段，比如URL，我就干脆設(shè)置成text，一勞永逸。
四、為常用的搜索字段建立索引吧
不解釋，但不要盲目建立索引。
五、歡迎您的回復(fù)補(bǔ)充
比如現(xiàn)在有2張表，一張新聞欄目表，一張新聞表，現(xiàn)在兩張表需要進(jìn)行關(guān)聯(lián)，我想大多數(shù)人的做法肯定是在新聞表里建一個(gè)新聞欄目id，然后把新聞欄目表里的主鍵ID（自增）寫到這個(gè)字段里，通過這樣進(jìn)行兩表關(guān)聯(lián)。
如果你是這樣做的，趕緊改掉這個(gè)習(xí)慣吧。也許你會(huì)問為什么，欄目id是主鍵啊，又是自增的，為什么這樣操作不行？原因其實(shí)很簡(jiǎn)單，欄目我們會(huì)增加，也會(huì)刪除，刪除就會(huì)造成主鍵id之間會(huì)有斷號(hào)的情況，由于主鍵設(shè)置為自增，也就是說你之前刪掉的欄目，再進(jìn)行添加，id是不會(huì)去補(bǔ)上哪個(gè)空缺的，而是一直遞增。這樣就會(huì)造成一種情況，如果那天對(duì)數(shù)據(jù)庫進(jìn)行優(yōu)化，把主鍵進(jìn)行了重新排序（暫時(shí)沒有找到mysql優(yōu)化軟件會(huì)優(yōu)化主鍵，但是可以通過代碼刪除主鍵，然后從新建立自增主鍵來實(shí)現(xiàn)主鍵重新排序），那就徹底杯具了，欄目和文章完全對(duì)不上號(hào)了。所以我建議兩表之間關(guān)聯(lián)不用主鍵，而是單獨(dú)建一個(gè)編號(hào)的字段，我們這里可以用mysql的uuid()函數(shù)做為編號(hào)，相關(guān)文獻(xiàn)可以參考《UUID做主鍵好還是不好》，只所以一張表要2個(gè)主鍵，一個(gè)物理主鍵（自增id），一個(gè)邏輯主鍵（UUID），原因是：對(duì)于InnoDB這種聚集主鍵類型的引擎來說，數(shù)據(jù)會(huì)按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會(huì)產(chǎn)生巨大的IO壓力，此時(shí)不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。至于性能，我本地測(cè)了下基本上沒差異，網(wǎng)上也有人做了10W條數(shù)據(jù)的測(cè)試——《實(shí)測(cè)MYSQL UUID性能》。
二、統(tǒng)一把主鍵類型設(shè)為bigint吧
bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數(shù)據(jù)，存儲(chǔ)大小為8個(gè)字節(jié)。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數(shù)據(jù)，存儲(chǔ)大小為4個(gè)字節(jié)。存儲(chǔ)空間擴(kuò)大一倍，而存儲(chǔ)數(shù)據(jù)卻擴(kuò)大N倍，再加上主鍵是一個(gè)自增的字段，我們根本無法控制它會(huì)自增到多少數(shù)值，所以我通常在建表的時(shí)候，主鍵類型都是設(shè)為bigint的，同樣，上面提到的編號(hào)字段類型也是bigint。
三、不要把varchar長(zhǎng)度設(shè)太“死”
這也是我之前經(jīng)常犯得一個(gè)毛病，比如手機(jī)，我就設(shè)置為varchar(11)，郵編設(shè)置成varchar(6)，姓名設(shè)置成varchar(10)等等等等，看似每個(gè)字段都設(shè)置得很嚴(yán)謹(jǐn)，但是在項(xiàng)目實(shí)際進(jìn)行中，這完全就是自找苦吃，比如手機(jī)，用戶偏偏就要在手機(jī)號(hào)前輸個(gè)0，又比如郵編，如果用戶輸入的是全角的數(shù)字呢？姓名就更不用說了，萬一是個(gè)少數(shù)民族的人，名字七八個(gè)字。所以我建議，既然定義為varchar，就代表不會(huì)涉及到計(jì)算，何不干脆定義一個(gè)通用的長(zhǎng)度，比如varchar(50)，如果真要限制長(zhǎng)度，用程序去判斷，不要讓數(shù)據(jù)庫來限制，不然用戶輸了一長(zhǎng)串，結(jié)果mysql就存了前幾個(gè)字符，讓人覺得這程序有問題。
還有就是，如果你是做cms這種通用后臺(tái)，更別把字段限制得太“死”，因?yàn)槟銦o法預(yù)料之后的每個(gè)項(xiàng)目的需求，所以還是把varchar設(shè)大一點(diǎn)，我現(xiàn)在是統(tǒng)一都設(shè)為255，如果很有可能會(huì)超過255的字段，比如URL，我就干脆設(shè)置成text，一勞永逸。
四、為常用的搜索字段建立索引吧
不解釋，但不要盲目建立索引。
五、歡迎您的回復(fù)補(bǔ)充
相關(guān)文章
這篇文章主要介紹了mysql中使用date_add()函數(shù)講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
Mysql8.0輕松實(shí)現(xiàn)主從復(fù)制
這篇文章主要介紹了Mysql8.0輕松實(shí)現(xiàn)主從復(fù)制方法的相關(guān)資料,需要的朋友可以參考下2022-11-11
MySQL連接優(yōu)化主要指客戶端連接數(shù)據(jù)庫以及數(shù)據(jù)庫為響應(yīng)客戶端的請(qǐng)求而打開數(shù)據(jù)表和索引的過程中涉及到的參數(shù)調(diào)整。下面我們來詳細(xì)的探討下2017-03-03
這篇文章主要介紹了Mysql如何查看表及字段信息，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-01-01
這篇文章主要介紹了mysql中的sql_mode模式,結(jié)合實(shí)例形式詳細(xì)分析了mysql sql_mode模式相關(guān)原理、用法及操作注意事項(xiàng),需要的朋友可以參考下2020-01-01
這篇文章主要為大家詳細(xì)介紹了Win10下mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-02-02
mysql 強(qiáng)大的trim() 函數(shù)
這篇文章主要介紹了mysql 強(qiáng)大的trim() 函數(shù)使用方法,需要的朋友可以參考下2014-03-03
在Windows環(huán)境下使用MySQL:實(shí)現(xiàn)自動(dòng)定時(shí)備份
下面小編就為大家分享一篇在Windows環(huán)境下使用MySQL:實(shí)現(xiàn)自動(dòng)定時(shí)備份的方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2017-12-12
本文主要介紹了MySQL如何確定VARCHAR大小問題，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-02-02
本文詳細(xì)介紹了通過MySQL的全局變量檢查日志狀態(tài),監(jiān)控連接數(shù)及啟用和調(diào)整慢查詢記錄,以幫助排查數(shù)據(jù)庫性能問題,感興趣的可以了解一下2025-07-07