掌握好這幾個(gè)建表的tips,沒人敢說(shuō)你是數(shù)據(jù)庫(kù)小白

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

本文不從范式角度，也不從數(shù)據(jù)庫(kù)全局庫(kù)表設(shè)計(jì)的角度來(lái)討論。而是基于對(duì)數(shù)據(jù)庫(kù)中創(chuàng)建表最基本的規(guī)范，讓每個(gè)開發(fā)的同事和朋友都能快速的掌握創(chuàng)建表的基本規(guī)范。

下面是作為一個(gè)DBA總結(jié)的針對(duì)創(chuàng)建表的幾個(gè)基本規(guī)范，希望各位開發(fā)的同事能了解和使用這些規(guī)范來(lái)建立表結(jié)構(gòu)。

1.創(chuàng)建表的時(shí)候必須指定主鍵，并且主鍵建立后最好不要再有數(shù)據(jù)修改的需求

MySQL從5.5版本開始默認(rèn)使用InnoDB引擎，InnoDB表是聚簇索引表，也就是說(shuō)數(shù)據(jù)通過(guò)主鍵聚集（主鍵下存儲(chǔ)該行的數(shù)據(jù)，索引指向主鍵值），如下圖所示。

正是由于這種結(jié)構(gòu)，如果后續(xù)對(duì)主鍵對(duì)應(yīng)的值進(jìn)行修改，就會(huì)導(dǎo)致索引節(jié)點(diǎn)的頻繁分裂，性能會(huì)下降非常厲害。因此推薦開發(fā)的同事們使用和業(yè)務(wù)沒有任何關(guān)聯(lián)的自增ID來(lái)做主鍵（切記不要使用UUID來(lái)做主鍵），此外也可以考慮使用其他的方式來(lái)生產(chǎn)自增的ID，比如使用Twitter的Snowflake算法或者zk的Distributed AtomicLong來(lái)間接實(shí)現(xiàn)。使用自增主鍵而不是UUID做主鍵的優(yōu)點(diǎn)主要有如下幾點(diǎn)：

占用的數(shù)據(jù)量更?。?/p>

數(shù)據(jù)順序遞增，不會(huì)導(dǎo)致索引節(jié)點(diǎn)的頻繁分裂；

數(shù)字類型比字符類型效率更高。

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

2.創(chuàng)建表選擇字段的時(shí)候，在符合業(yè)務(wù)需求的情況下盡量小，數(shù)據(jù)類型盡量簡(jiǎn)單

數(shù)據(jù)類型盡量簡(jiǎn)單很好理解，比如，使用數(shù)字類型要比使用字符類型效率更高，因?yàn)椴簧婕暗叫?duì)規(guī)則和字符集。而字段盡量小，說(shuō)的是在滿足業(yè)務(wù)需求的情況下，盡可能小。這么做的好處是：更小的字段類型占用更少的內(nèi)存，占用更少的磁盤空間，占用更少的磁盤IO，以及占用更少的帶寬。舉個(gè)例子，如果一個(gè)varchar(50)的字段，不管你存儲(chǔ)了幾個(gè)字符，在查詢的時(shí)候仍然需要申請(qǐng)50 byte的內(nèi)存。

3、創(chuàng)建表的時(shí)候顯式申明存儲(chǔ)引擎

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

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

4.創(chuàng)建表的時(shí)候顯式申明字符集

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

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

5.創(chuàng)建表的時(shí)候?qū)?jīng)常要查詢的列添加索引或者組合索引

索引直接影響后面的查詢性能，尤其是數(shù)據(jù)量越大的時(shí)候，影響越明顯。作為一個(gè)從事DBA生涯超過(guò)5年的DBA，遇到過(guò)無(wú)數(shù)次由于沒有添加索引，導(dǎo)致的線上故障，請(qǐng)各位開發(fā)和DBA的同事謹(jǐn)記，一定要對(duì)經(jīng)常要查詢的列添加索引或者組合索引，防止線上事故的發(fā)生。

正確招式：建表的時(shí)候就添加對(duì)應(yīng)INDEX。

6、創(chuàng)建表的時(shí)候?qū)ψ侄魏捅硖砑覥OMMENT

這個(gè)主要是方便后續(xù)的維護(hù)，之前在小公司做DBA的時(shí)候，接手?jǐn)?shù)據(jù)庫(kù)時(shí)由于沒有任何的COMMENT，導(dǎo)致完全不知道對(duì)應(yīng)的表和字段是做什么用的，不得不經(jīng)常麻煩開發(fā)的同事解釋。這個(gè)小技巧用一個(gè)經(jīng)典的廣告詞來(lái)解釋就是：你好，我也好。

正確招式：對(duì)列和表都添加COMMENT做詳細(xì)說(shuō)明。

7.創(chuàng)建表的時(shí)候不要添加drop操作

有的開發(fā)人員在創(chuàng)建表之前喜歡添加DROP TABLE IF EXISTS TABLEXXXX，然后再來(lái)個(gè)CREATE TABLE，建議不要這么干，因?yàn)槲抑坝龅竭^(guò)由此帶來(lái)的線上故障。將線上正在使用的表drop掉了。建議大家創(chuàng)建表的時(shí)候修改為：CREATE TABLE IF NOT EXISTS TABLEXXX.......

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

8.創(chuàng)建表的時(shí)候，字段盡量不要為NULL

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

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

（2）進(jìn)行比較的時(shí)候會(huì)更復(fù)雜，還會(huì)導(dǎo)致你select(column)的時(shí)候不準(zhǔn)確；

（3）含有NULL值的列，會(huì)對(duì)SQL優(yōu)化產(chǎn)生影響，尤其是組合索引中。

具體NULL會(huì)帶來(lái)的問(wèn)題大家可以查閱：https://dev.mysql.com/doc/refman/5.7/en/problemswith-null.html。

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

附上一個(gè)簡(jiǎn)單的標(biāo)準(zhǔn)SQL范 例，大家創(chuàng)建表的時(shí)候可以參照下面的范例SQL來(lái)創(chuàng)建，范例如下：

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 '節(jié)假日日期',

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

PRIMARY KEY (`id`),

KEY `holiday_date`(`holiday_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='節(jié)假日數(shù)據(jù)表';