Mysql 建庫建表技巧分享
2024-07-24 13:03:05
供稿：網友
一、兩表之間若有關聯，你是否還在用主鍵進行關聯？
比如現在有2張表，一張新聞欄目表，一張新聞表，現在兩張表需要進行關聯，我想大多數人的做法肯定是在新聞表里建一個新聞欄目id，然后把新聞欄目表里的主鍵ID（自增）寫到這個字段里，通過這樣進行兩表關聯。
如果你是這樣做的，趕緊改掉這個習慣吧。也許你會問為什么，欄目id是主鍵啊，又是自增的，為什么這樣操作不行？原因其實很簡單，欄目我們會增加，也會刪除，刪除就會造成主鍵id之間會有斷號的情況，由于主鍵設置為自增，也就是說你之前刪掉的欄目，再進行添加，id是不會去補上哪個空缺的，而是一直遞增。這樣就會造成一種情況，如果那天對數據庫進行優化，把主鍵進行了重新排序（暫時沒有找到mysql優化軟件會優化主鍵，但是可以通過代碼刪除主鍵，然后從新建立自增主鍵來實現主鍵重新排序），那就徹底杯具了，欄目和文章完全對不上號了。所以我建議兩表之間關聯不用主鍵，而是單獨建一個編號的字段，我們這里可以用mysql的uuid()函數做為編號，相關文獻可以參考《UUID做主鍵好還是不好》，只所以一張表要2個主鍵，一個物理主鍵（自增id），一個邏輯主鍵（UUID），原因是：對于InnoDB這種聚集主鍵類型的引擎來說，數據會按照主鍵進行排序，由于UUID的無序性，InnoDB會產生巨大的IO壓力，此時不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。至于性能，我本地測了下基本上沒差異，網上也有人做了10W條數據的測試——《實測MYSQL UUID性能》。
二、統一把主鍵類型設為bigint吧
bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數據，存儲大小為8個字節。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數據，存儲大小為4個字節。存儲空間擴大一倍，而存儲數據卻擴大N倍，再加上主鍵是一個自增的字段，我們根本無法控制它會自增到多少數值，所以我通常在建表的時候，主鍵類型都是設為bigint的，同樣，上面提到的編號字段類型也是bigint。
三、不要把varchar長度設太“死”
這也是我之前經常犯得一個毛病，比如手機，我就設置為varchar(11)，郵編設置成varchar(6)，姓名設置成varchar(10)等等等等，看似每個字段都設置得很嚴謹，但是在項目實際進行中，這完全就是自找苦吃，比如手機，用戶偏偏就要在手機號前輸個0，又比如郵編，如果用戶輸入的是全角的數字呢？姓名就更不用說了，萬一是個少數民族的人，名字七八個字。所以我建議，既然定義為varchar，就代表不會涉及到計算，何不干脆定義一個通用的長度，比如varchar(50)，如果真要限制長度，用程序去判斷，不要讓數據庫來限制，不然用戶輸了一長串，結果mysql就存了前幾個字符，讓人覺得這程序有問題。
還有就是，如果你是做cms這種通用后臺，更別把字段限制得太“死”，因為你無法預料之后的每個項目的需求，所以還是把varchar設大一點，我現在是統一都設為255，如果很有可能會超過255的字段，比如URL，我就干脆設置成text，一勞永逸。
四、為常用的搜索字段建立索引吧
不解釋，但不要盲目建立索引。
五、歡迎您的回復補充
