基于阿里數(shù)據(jù)庫設(shè)計規(guī)范擴展而來

一第步、設(shè)計規(guī)范?

1.【推薦】字段允許適當(dāng)冗余虏劲，以提高查詢性能，但必須考慮數(shù)據(jù)一致演闭。冗余字段應(yīng)遵循:

????????不是頻繁修改的字段不跟。

????????不是 varchar 超長字段，更不能是 text 字段米碰。

????????正例:商品類目名稱使用頻率高窝革，字段長度短，名稱基本一成不變吕座，可在相關(guān)聯(lián)的表中冗余存 儲類目名稱虐译，避免關(guān)聯(lián)查詢。

2.【推薦】單表行數(shù)超過 500 萬行或者單表容量超過 2GB吴趴，才推薦進(jìn)行分庫分表漆诽。 說明:如果預(yù)計2年后的數(shù)據(jù)量根本達(dá)不到這個級別，請不要在創(chuàng)建表時就分庫分表锣枝。

3.【推薦】id必須是主鍵厢拭，每個表必須有主鍵，且保持增長趨勢的惊橱， 小型系統(tǒng)可以依賴于 MySQL 的自增主鍵蚪腐，大型系統(tǒng)或者需要分庫分表時才使用內(nèi)置的 ID 生成器

4.【強制】id類型沒有特殊要求箭昵，必須使用bigint unsigned税朴，禁止使用int，即使現(xiàn)在的數(shù)據(jù)量很小。id如果是數(shù)字類型的話正林，必須是8個字節(jié)泡一。參見最后例子

????????方便對接外部系統(tǒng)，還有可能產(chǎn)生很多廢數(shù)據(jù)

????????避免廢棄數(shù)據(jù)對系統(tǒng)id的影響

????????未來分庫分表觅廓，自動生成id鼻忠，一般也是8個字節(jié)

5.【推薦】字段盡量設(shè)置為 NOT NULL， 為字段提供默認(rèn)值杈绸。 如字符型的默認(rèn)值為一個空字符值串’’;數(shù)值型默認(rèn)值為數(shù)值 0;邏輯型的默認(rèn)值為數(shù)值 0;

6.【推薦】每個字段和表必須提供清晰的注釋

7.【推薦】時間統(tǒng)一格式:‘YYYY-MM-DD HH:MM:SS’

8.【強制】更新數(shù)據(jù)表記錄時帖蔓，必須同時更新記錄對應(yīng)的 gmt_modified 字段值為當(dāng)前時間,

二、命名規(guī)范

1.【強制】表達(dá)是與否概念的字段瞳脓，必須使用 is_xxx 的方式命名塑娇，數(shù)據(jù)類型是 unsigned tinyint ( 1表示是，0表示否)劫侧。

? ? ? ?說明：

任何字段如果為非負(fù)數(shù)埋酬，必須是 unsigned。

????????正例:

表達(dá)邏輯刪除的字段名 is_deleted烧栋，1 表示刪除写妥，0 表示未刪除。

2.【強制】表名审姓、字段名必須使用小寫字母或數(shù)字珍特，禁止出現(xiàn)數(shù)字開頭，禁止兩個下劃線中間只 出現(xiàn)數(shù)字邑跪。數(shù)據(jù)庫字段名的修改代價很大次坡，因為無法進(jìn)行預(yù)發(fā)布，所以字段名稱需要慎重考慮画畅。 說明:MySQL 在 Windows 下不區(qū)分大小寫砸琅，但在 Linux 下默認(rèn)是區(qū)分大小寫。因此轴踱，數(shù)據(jù)庫 名症脂、表名、字段名淫僻，都不允許出現(xiàn)任何大寫字母诱篷，避免節(jié)外生枝。 正例:health_user雳灵，rdc_config棕所，level3_name 反例:HealthUser，rdcConfig悯辙，level_3_name

3.【強制】表名不使用復(fù)數(shù)名詞琳省。 說明:表名應(yīng)該僅僅表示表里面的實體內(nèi)容迎吵，不應(yīng)該表示實體數(shù)量，對應(yīng)于 DO 類名也是單數(shù) 形式针贬，符合表達(dá)習(xí)慣击费。

4.【強制】禁用保留字，如 desc桦他、range蔫巩、match、delayed 等快压，請參考 MySQL 官方保留字圆仔。

5.【強制】主鍵索引名為 pk_字段名;唯一索引名為 uk_字段名;普通索引名則為 idx_字段名。

????????說明:

pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的簡稱蔫劣。

6.【強制】小數(shù)類型為 decimal荧缘，禁止使用 float 和 double。

?????????說明:

float 和 double 在存儲的時候拦宣，存在精度損失的問題截粗，很可能在值的比較時，得到不 正確的結(jié)果鸵隧。如果存儲的數(shù)據(jù)范圍超過? ? ? ? decimal 的范圍绸罗，建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲。

7.【強制】如果存儲的字符串長度幾乎相等豆瘫，使用 char 定長字符串類型珊蟀。

8.【強制】varchar 是可變長字符串，不預(yù)先分配存儲空間外驱，長度不要超過 5000育灸，如果存儲長 度大于此值，定義字段類型為 text昵宇，獨立出來一張表磅崭，用主鍵來對應(yīng)，避免影響其它字段索 引效率瓦哎。

9.【強制】表必備三字段:id, is_delete,gmt_create, gmt_modified砸喻。 說明:其中id必為主鍵，類型為unsigned bigint蒋譬、單表時自增割岛、步長為1。gmt_create, gmt_modified 的類型均為 date_time 類型犯助，前者現(xiàn)在時表示主動創(chuàng)建癣漆，后者過去分詞表示被 動更新。

10.【強制】所有命名必須使用全名剂买，有默認(rèn)約定的除外惠爽，如果超過 30 個字符腰湾，使用縮寫，請盡量名字易懂簡短疆股，如 description --> desc;information --> info;address --> addr 等

11.【推薦】表的命名最好是加上“業(yè)務(wù)名稱_表的作用”。 正例:health_user / trade_config

12.【推薦】庫名與應(yīng)用名稱盡量一致倒槐。如health

13.【推薦】如果修改字段含義或?qū)ψ侄伪硎镜臓顟B(tài)追加時旬痹，需要及時更新字段注釋

14.【推薦】所有時間字段，都以 gmt_開始讨越，后面加上動詞的過去式两残，最后不要加上 time 單詞，例如 gmt_create

三把跨、類型規(guī)范

1.表示狀態(tài)字段(0-255)的使用 TINYINT UNSINGED人弓，禁止使用枚舉 類型，注釋必須清晰地說明每個枚舉的含義着逐，以及是否多選等

2.表示boolean類型的都使用TINYINT(1),因為mysql本身是沒有boolean類型的崔赌，在自動生成代碼的時候，DO對象的字段就是boolean類型耸别，例如 is_delete;其余所有時候都使用TINYINT(4)

TINYINT(4),這個括號里面的數(shù)值并不是表示使用多大空間存儲健芭，而是最大顯示寬度，并且只有字段指定zerofill時有用秀姐，沒有zerofill慈迈，(m)就是無用的,例如id BIGINT ZEROFILL NOT NULL,所以建表時就使用默認(rèn)就好了，不需要加括號了省有，除非有特殊需求痒留，例如TINYINT(1)代表boolean類型。

TINYINT(1)蠢沿，TINYINT(4)都是存儲一個字節(jié)伸头，并不會因為括號里的數(shù)字改變。例如TINYINT(4)存儲22則會顯示0022舷蟀，因為最大寬度為4熊锭，達(dá)不到的情況下用0來補充。

3.【參考】合適的字符存儲長度雪侥，不但節(jié)約數(shù)據(jù)庫表空間碗殷、節(jié)約索引存儲，更重要的是提升檢索速度速缨。

? ??????類型字節(jié)表示范圍：

tinyint1無符號值: 0～255;有符號值： -128~127

smallint2無符號值: 0～65536;有符號值： -32768~32767

mediumint3無符號值: 0～16777215;有符號值： -8388608~8388607

int4無符號值: 0~4294967295;有符號值： -2147483648~2147483647

bigint8無符號值: 0~((232×2)-1);有符號值： -(232×2)/2 ~ (232×2)/2-1

4.非負(fù)的數(shù)字類型字段锌妻，都添加上 UNSINGED, 如可以使用 INT UNSINGED 字段存 IPV4

5.時間字段使用時間日期類型，不要使用字符串類型存儲旬牲，日期使用DATE類型仿粹，年使用YEAR類型搁吓，日期時間使用DATETIME

6.字符串VARCHAR(N), 其中 N表示字符個數(shù)，請盡量減少 N 的大小吭历，參考：code VARCHAR(32)堕仔；name VARCHAR(32)；memo VARCHAR(512)晌区；

7.Blob 和 Text 類型所存儲的數(shù)據(jù)量大摩骨，刪除和修改操作容易在數(shù) 據(jù)表里產(chǎn)生大量的碎片，避免使用 Blob 或 Text 類型

四朗若、索引規(guī)范

1.【強制】業(yè)務(wù)上具有唯一特性的字段恼五，即使是多個字段的組合，也必須建成唯一索引哭懈。

不要以為唯一索引影響了 insert 速度灾馒，這個速度損耗可以忽略，但提高查找速度是明 顯的;另外遣总，即使在應(yīng)用層做了非常完善的校驗控制睬罗，只要沒有唯一索引，根據(jù)墨菲定律旭斥，必 然有臟數(shù)據(jù)產(chǎn)生傅物。

2.【強制】超過三個表禁止 join。需要 join 的字段琉预，數(shù)據(jù)類型必須絕對一致;多表關(guān)聯(lián)查詢時董饰， 保證被關(guān)聯(lián)的字段需要有索引。

即使雙表 join 也要注意表索引圆米、SQL 性能卒暂。

3.【強制】在 varchar 字段上建立索引時，必須指定索引長度娄帖，沒必要對全字段建立索引也祠，根據(jù) 實際文本區(qū)分度決定索引長度即可。 說明:索引的長度與區(qū)分度是一對矛盾體近速，一般對字符串類型數(shù)據(jù)诈嘿，長度為 20 的索引，區(qū)分度會高達(dá) 90%以上削葱，可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定奖亚。

4.【強制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決析砸。

????????索引文件具有 B-Tree 的最左前綴匹配特性昔字，如果左邊的值未確定，那么無法使用此索引首繁。

5.【推薦】如果有 order by 的場景作郭，請注意利用索引的有序性陨囊。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后夹攒，避免出現(xiàn) file_sort 的情況蜘醋，影響查詢性能。

? ? ? ? ?正例:where a=? and b=? order by c; 索引:a_b_c 反例:索引中有范圍查找咏尝，那么索引有序性無法利用压语，如:WHERE a>10 ORDER BY b;? 索引 a_b 無法排序。

6.【推薦】利用覆蓋索引來進(jìn)行查詢操作状土，避免回表。

? ??????說明:

如果一本書需要知道第11章是什么標(biāo)題伺糠，會翻開第11章對應(yīng)的那一頁嗎?目錄瀏覽一下就好蒙谓，這個目錄就是起到覆蓋索引的作用。

????????正例:

能夠建立索引的種類:主鍵索引训桶、唯一索引累驮、普通索引，而覆蓋索引是一種查詢的效果舵揭，用explain的結(jié)果谤专，extra列會出現(xiàn):using index。

7.【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景午绳。

? ??????說明:

MySQL并不是跳過 offset 行置侍，而是取 offset+N 行，然后返回放棄前 offset 行拦焚，返回 N 行蜡坊，那當(dāng) offset 特別大的時候，效率就非常的低下赎败，要么控制返回的總頁數(shù)秕衙，要么對超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫。

? ??????正例:

先快速定位需要獲取的 id 段僵刮，然后再關(guān)聯(lián):

SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

8.【推薦】SQL 性能優(yōu)化的目標(biāo):至少要達(dá)到 range 級別，要求是 ref 級別，如果可以是 consts 最好售担。

? ??????說明:

consts 單表中最多只有一個匹配行(主鍵或者唯一索引)蜂科，在優(yōu)化階段即可讀取到數(shù)據(jù)。

ref 指的是使用普通的索引(normal index)窍仰。

range 對索引進(jìn)行范圍檢索萧福。

????????反例:

explain 表的結(jié)果，type=index辈赋，索引物理文件全掃描鲫忍，速度非常慢膏燕，這個 index 級 別比較 range 還低，與全表掃描是小巫見大巫悟民。

9.【推薦】建組合索引的時候坝辫，區(qū)分度最高的在最左邊。

? ??????正例:

如果 where a=? and b=? 射亏，a 列的幾乎接近于唯一值近忙，那么只需要單建 idx_a 索引即 可。

????????說明:

存在非等號和等號混合判斷條件時智润，在建索引時及舍，請把等號條件的列前置。如:where a>? and b=? 那么即使 a 的區(qū)分度更高窟绷，也必須把 b 放在索引的最前列锯玛。

10【推薦】防止因字段類型不同造成的隱式轉(zhuǎn)換，導(dǎo)致索引失效兼蜈。

11.【參考】創(chuàng)建索引時避免有如下極端誤解

寧濫勿缺攘残。認(rèn)為一個查詢就需要建一個索引。

寧缺勿濫为狸。認(rèn)為索引會消耗空間歼郭、嚴(yán)重拖慢更新和新增速度。

抵制惟一索引辐棒。認(rèn)為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決病曾。

12. 總結(jié)

? ? ? 索引占磁盤空間，不要重復(fù)的索引漾根，盡量短?

? ? ? 只給常用的查詢條件加索引?

? ? ? 過濾性高的列建索引知态，取值范圍固定的列不建索引

? ? ? 唯一的記錄添加唯一索引?

? ? ? 頻繁更新的列不要建索引?

? ? ? 不要對索引列運算?

? ? ? 同樣過濾效果下，保持索引長度最小?

? ? ? 合理利用組合索引立叛，注意索引字段先后順序?

? ? ? 多列組合索引负敏，過濾性高的字段最前?

? ? ? order by 字段建立索引，避免 filesort?

? ? ? 組合索引秘蛇，不同的排序順序不能使用索引?

? ? ? <>!=無法使用索引

五其做、SQL規(guī)范

1.【強制】不要使用 count(列名)或 count(常量)來替代 count()，count()是 SQL92 定義的 標(biāo)準(zhǔn)統(tǒng)計行數(shù)的語法赁还，跟數(shù)據(jù)庫無關(guān)妖泄，跟 NULL 和非 NULL 無關(guān)。

count(*)會統(tǒng)計值為 NULL 的行艘策，而 count(列名)不會統(tǒng)計此列為 NULL 值的行蹈胡。

2.【強制】count(distinct col) 計算該列除 NULL 之外的不重復(fù)行數(shù)，

count(distinct col1, col2) 如果其中一列全為NULL，那么即使另一列有不同的值罚渐，也返回為0却汉。

3.【強制】當(dāng)某一列col的值全是 NULL 時，count(col)的返回結(jié)果為 0荷并，但 sum(col)的返回結(jié)果為 NULL合砂，因此使用 sum()時需注意 NPE 問題。

正例:可以使用如下方式來避免sum的NPE問題:SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

4.【強制】使用 ISNULL()來判斷是否為 NULL 值源织。 說明:NULL 與任何值的直接比較都為 NULL翩伪。

NULL<>NULL的返回結(jié)果是NULL，而不是false谈息。

NULL=NULL的返回結(jié)果是NULL缘屹，而不是true。

NULL<>1的返回結(jié)果是NULL侠仇，而不是true轻姿。

5.【強制】 在代碼中寫分頁查詢邏輯時，若 count 為 0 應(yīng)直接返回傅瞻，避免執(zhí)行后面的分頁語句踢代。

6.【強制】不得使用外鍵與級聯(lián)盲憎，一切外鍵概念必須在應(yīng)用層解決嗅骄。 說明:以學(xué)生和成績的關(guān)系為例，學(xué)生表中的 student_id 是主鍵饼疙，那么成績表中的 student_id 則為外鍵溺森。如果更新學(xué)生表中的 student_id，同時觸發(fā)成績表中的 student_id 更新窑眯，即為 級聯(lián)更新屏积。外鍵與級聯(lián)更新適用于單機低并發(fā)，不適合分布式磅甩、高并發(fā)集群;級聯(lián)更新是強阻 塞炊林，存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險;外鍵影響數(shù)據(jù)庫的插入速度。

7.【強制】禁止使用存儲過程卷要，存儲過程難以調(diào)試和擴展渣聚，更沒有移植性。

8.【強制】數(shù)據(jù)訂正時僧叉，刪除和修改記錄時奕枝，要先 select，避免出現(xiàn)誤刪除瓶堕，確認(rèn)無誤才能執(zhí)行更新語句隘道。

9.【推薦】in操作能避免則避免，若實在避免不了，需要仔細(xì)評估 in 后邊的集合元素數(shù)量谭梗，控

制在 1000 個之內(nèi)忘晤。

10.【參考】如果有全球化需要，所有的字符存儲與表示默辨，均以 utf-8 編碼德频，注意字符統(tǒng)計函數(shù) 的區(qū)別。

SELECT LENGTH(“輕松工作”); 返回為12

SELECT CHARACTER_LENGTH(“輕松工作”); 返回為4 如果需要存儲表情缩幸，那么選擇 utfmb4 來進(jìn)行存儲壹置，注意它與 utf-8 編碼的區(qū)別。

11.【參考】TRUNCATE TABLE 比 DELETE 速度快表谊，且使用的系統(tǒng)和事務(wù)日志資源少钞护，但 TRUNCATE 無事務(wù)且不觸發(fā)trigger，有可能造成事故爆办，故不建議在開發(fā)代碼中使用此語句难咕。 說明:TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

12.【推薦】不要寫一個大而全的數(shù)據(jù)更新接口距辆。傳入為 POJO 類余佃，不管是不是自己的目標(biāo)更新字 段，都進(jìn)行 update table set c1=value1,c2=value2,c3=value3; 這是不對的跨算。執(zhí)行 SQL 時爆土，不要更新無改動的字段，一是易出錯;二是效率低;三是增加 binlog 存儲诸蚕。

13.總結(jié)

? 能夠快速縮小結(jié)果集的 WHERE 條件寫在前面步势，如果有恒量條 件，也盡量放在前面 背犯，例如 where1=1? 避免使用 GROUP BY坏瘩、DISTINCT 等語句的使用，避免聯(lián)表查 詢和子查詢 ? 能夠使用索引的字段盡量進(jìn)行有效的合理排列? ? 針對索引字段使用 >, >=, =, <, <=, IF NULL 和 BETWEEN 將會 使用索引漠魏，如果對某個索引字段進(jìn)行 LIKE 查詢倔矾，使用 LIKE? ‘%abc%’ 不能使用索引，使用 LIKE ‘a(chǎn)bc%’ 將能夠使用索引? ? 如果在 SQL 里使用了 MySQL部分自帶函數(shù)柱锹，索引將失效? 避免直接使用 select *,只取需要的字段哪自，增加使用覆蓋索引使用的可能? ? 對于大數(shù)據(jù)量的查詢，盡量避免在 SQL 語句中使用 order by 字句 ? 連表查詢的情況下奕纫，要確保關(guān)聯(lián)條件的數(shù)據(jù)類型一致提陶，避免嵌套子查詢? ? 對于連續(xù)的數(shù)值，使用 between 代替in? where 語句中盡量不要使用 CASE 條件? ? 當(dāng)只要一行數(shù)據(jù)時使用 LIMIT1

????????例子：

CREATE TABLE`health_package`(`id`bigint unsigned NOT NULL AUTO_INCREMENT COMMENT'序號',`package_id`int unsigned NOT NULL COMMENT'套系 id',`module_id`int unsigned NOT NULL COMMENT'模塊 id',`is_delete`tinyint unsigned NOT NULL DEFAULT0COMMENT'是否刪除匹层，0-未刪除隙笆，1-刪除锌蓄，默認(rèn)為0',`gmt_create`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Create time, common column by DB rules',`gmt_modified`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT'Modified time,common column by DB rules ',? PRIMARY KEY (`id`)) COMMENT='This table stores module and package of health for ...';