Mysql資料庫規範

標籤：use   程式   單位   資料   rom   mysq   類型   層級   子查詢   

一、基礎規範第一條：必須使用InnoDB儲存引擎 第二條：必須使用utf8mb4字元集utf8mb4是utf8的超集，emoji表情以及部分不常見漢字在utf8下會表現為亂碼，故需要升級至utf8mb4。 第二條：資料表、資料欄位必須加入中文注釋 第三條：禁止使用預存程序、視圖、觸發器、Event 第四條：禁止儲存大檔案或者大照片 二、表和欄位設計規範第一條：禁止使用外鍵，如果有外鍵完整性條件約束，需要應用程式控制 第二條：必須把欄位定義為NOT NULL並且提供預設值

a）null的列使索引/索引統計/值比較都更加複雜，對MySQL來說更難最佳化

b）null 這種類型MySQL內部需要進行特殊處理，增加資料庫處理記錄的複雜性；同等條件下，表中有較多空欄位的時候，資料庫的處理效能會降低很多

c）null值需要更多的儲存空，無論是表還是索引中每行中的null的列都需要額外的空間來標識

d）對null 的處理時候，只能採用is null或is not null，而不能採用=、in、<、<>、!=、not in這些操作符號。如：where name!=’shenjian’，如果存在name為null值的記錄，查詢結果就不會包含name為null值的記錄

 

第三條：禁止使用TEXT、BLOB類型

會浪費更多的磁碟和記憶體空間，非必要的大量的大欄位查詢會淘汰掉熱資料，導致記憶體命中率急劇降低，影響資料庫效能

 第四條：禁止使用小數儲存國幣

曾經踩過這樣的坑，100元分3天攤銷，每天攤銷100/3元，結果得到3個33.33。後來實施對賬系統，始終有幾分錢對不齊，鬱悶了很久（不是幾分錢的事，是業務方質疑的眼神讓研發很不爽），最後發現是除法惹的禍。

解決方案：使用“分”作為單位，這樣資料庫裡就是整數了。

 三、索引設計規範第一條：單表索引建議控制在5個以內 第二條：單索引欄位數不允許超過5個 第三條：禁止在更新十分頻繁、區分度不高的屬性上建立索引

a）更新會變更B+樹，更新頻繁的欄位建立索引會大大降低資料庫效能

b）“性別”這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾資料，效能與全表掃描類似

 

第四條:建立複合式索引，必須把區分度高的欄位放在前面

 

四、SQL使用規範

第一條：禁止使用SELECT *，只擷取必要的欄位，需要顯示說明列屬性

a）讀取不需要的列會增加CPU、IO、NET消耗

b）不能有效利用覆蓋索引

c）使用SELECT *容易在增加或者刪除欄位後出現程式BUG

 

第二條：禁止使用INSERT INTO t_xxx VALUES(xxx)，必須顯示指定插入的列屬性

 

第三條：禁止使用屬性隱式轉換

SELECT uid FROM t_user WHERE phone=13812345678 會導致全表掃描，而不能命中phone索引

這個坑大家沒踩過嗎？

phone是varchar類型，SQL語句帶入的是整形，故不會命中索引，加個引號就好了：

 

SELECT uid FROM t_user WHERE phone=’13812345678’

 

第四條：禁止在WHERE條件的屬性上使用函數或者運算式

SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2017-02-15‘ 會導致全表掃描

正確的寫法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00‘)

 

 第五條：禁止大表使用JOIN查詢，禁止大表使用子查詢大表指的是資料量在1000萬以上的表 第六條：只允許使用內網網域名稱，而不是ip串連資料庫 第七條：禁止使用OR條件，必須改為IN查詢  第八條：禁止使用負向查詢NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，會導致全表掃描

一般來說，WHERE過濾條件不會只帶這麼一個“負向查詢條件”，還會有其他過濾條件，舉個例子：查詢沈劍已完成訂單之外的訂單（好拗口）：

SELECT oid FROM t_order WHERE uid=123 AND status != 1;

 

訂單表5000w資料，但uid=123就會迅速的將資料量過濾到很少的層級（uid建立了索引），此時再接上一個負向的查詢條件就無所謂了，掃描的行數本身就會很少。

 

但如果要查詢所有已完成訂單之外的訂單：

SELECT oid FROM t_order WHERE status != 1;

這就掛了，立馬CPU100%，status索引會失效，負向查詢導致全表掃描。

 

----本文轉自58沈劍老師的，轉載請註明出處

Mysql資料庫規範