掌握好這幾個建表的tips,沒人敢說你是數據庫小白

最近在做金融數據庫相關的數據庫SQL審核工作，期間發現非常多不符合基本規范的SQL。和相關的開發溝通后，發現很多的開發，甚至工作了很多年的高工對于數據庫建表的基本規范可能都不太清楚，及時了解基本的規范，也不知道為什么要這么干。有的同事還以為我是在故意為難他，當我把范例的SQL交給他，并做基本解釋后，便讓他豁然開朗，甚至對我表示非常感謝，感謝的理由是之前沒有人對他說過必須這么做以及為什么要這么做。

本文不從范式角度，也不從數據庫全局庫表設計的角度來討論。而是基于對數據庫中創建表最基本的規范，讓每個開發的同事和朋友都能快速的掌握創建表的基本規范。

下面是作為一個DBA總結的針對創建表的幾個基本規范，希望各位開發的同事能了解和使用這些規范來建立表結構。

1.創建表的時候必須指定主鍵，并且主鍵建立后最好不要再有數據修改的需求

MySQL從5.5版本開始默認使用InnoDB引擎，InnoDB表是聚簇索引表，也就是說數據通過主鍵聚集（主鍵下存儲該行的數據，索引指向主鍵值），如下圖所示。

正是由于這種結構，如果后續對主鍵對應的值進行修改，就會導致索引節點的頻繁分裂，性能會下降非常厲害。因此推薦開發的同事們使用和業務沒有任何關聯的自增ID來做主鍵（切記不要使用UUID來做主鍵），此外也可以考慮使用其他的方式來生產自增的ID，比如使用Twitter的Snowflake算法或者zk的Distributed AtomicLong來間接實現。使用自增主鍵而不是UUID做主鍵的優點主要有如下幾點：

占用的數據量更小；

數據順序遞增，不會導致索引節點的頻繁分裂；

數字類型比字符類型效率更高。

正確招式：使用自增ID作為主鍵。

2.創建表選擇字段的時候，在符合業務需求的情況下盡量小，數據類型盡量簡單

數據類型盡量簡單很好理解，比如，使用數字類型要比使用字符類型效率更高，因為不涉及到校對規則和字符集。而字段盡量小，說的是在滿足業務需求的情況下，盡可能小。這么做的好處是：更小的字段類型占用更少的內存，占用更少的磁盤空間，占用更少的磁盤IO，以及占用更少的帶寬。舉個例子，如果一個varchar(50)的字段，不管你存儲了幾個字符，在查詢的時候仍然需要申請50 byte的內存。

3、創建表的時候顯式申明存儲引擎

這個沒什么說的，使用什么引擎就申明什么引擎，防止被默認。有句話說得好：如果你不選擇，那你就被選擇，被選擇的結果不一定是你自己想要的。命運還是要掌握的自己手里。

正確招式：顯式申明ENGINE=xxxx。

4.創建表的時候顯式申明字符集

如果不想被亂碼困擾，就老老實實的申明字符集，還是那句話：如果你不選擇，那你就被選擇，被選擇的結果不一定是你自己想要的。

正確招式：顯式申明DEFAULT CHARSET=xxxx。

5.創建表的時候對經常要查詢的列添加索引或者組合索引

索引直接影響后面的查詢性能，尤其是數據量越大的時候，影響越明顯。作為一個從事DBA生涯超過5年的DBA，遇到過無數次由于沒有添加索引，導致的線上故障，請各位開發和DBA的同事謹記，一定要對經常要查詢的列添加索引或者組合索引，防止線上事故的發生。

正確招式：建表的時候就添加對應INDEX。

6、創建表的時候對字段和表添加COMMENT

這個主要是方便后續的維護，之前在小公司做DBA的時候，接手數據庫時由于沒有任何的COMMENT，導致完全不知道對應的表和字段是做什么用的，不得不經常麻煩開發的同事解釋。這個小技巧用一個經典的廣告詞來解釋就是：你好，我也好。

正確招式：對列和表都添加COMMENT做詳細說明。

7.創建表的時候不要添加drop操作

有的開發人員在創建表之前喜歡添加DROP TABLE IF EXISTS TABLEXXXX，然后再來個CREATE TABLE，建議不要這么干，因為我之前遇到過由此帶來的線上故障。將線上正在使用的表drop掉了。建議大家創建表的時候修改為：CREATE TABLE IF NOT EXISTS TABLEXXX.......

正確招式：CREATE TABLE IF NOT EXISTS TABLEXXX.......

8.創建表的時候，字段盡量不要為NULL

解決辦法就是設置字段為NOT NULL，并設置字段的默認值。字段盡量不要為NULL的原因如下：

（1）NULL需要占用額外的空間存儲；

（2）進行比較的時候會更復雜，還會導致你select(column)的時候不準確；

（3）含有NULL值的列，會對SQL優化產生影響，尤其是組合索引中。

具體NULL會帶來的問題大家可以查閱：https://dev.mysql.com/doc/refman/5.7/en/problemswith-null.html。

正 確 招 式 ：NOT NULL DEFAULT 'xxxxx'。

附上一個簡單的標準SQL范 例，大家創建表的時候可以參照下面的范例SQL來創建，范例如下：

CREATE DATABASE IF NOT EXISTS `dev_ops_db`;

CREATE TABLE IF NOT EXISTS `dev_ops_db`.`monitor_table_holiday` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`holiday_date` date NOT NULL default '1999-01-01' COMMENT '節假日日期',

`holiday_name`varchar(36) NOT NULL default '' COMMENT '節假日名稱',

PRIMARY KEY (`id`),

KEY `holiday_date`(`holiday_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='節假日數據表';