MySQL 設計規范（續）

1.1    數據庫字段設計規范

優先選擇符合存儲需要的最小的數據類型：

對于非負數據采用無符號整型進行存儲；

varchar(N)中N代表字符數，不是字節數（Oracle中是字節數，如中文GBK下2字節存一個漢字），即MySQL中varchar(2)可存儲2個漢字，占用6個字節（UTF8）。

避免使用text,blob數據類型：

常見的text類型可存儲64k，一般是使用不到這么大空間；

非要使用，建議把blob或text列分離到單獨的擴展表中，且避免使用select *;

text, blob僅能使用前綴索引，且默認情況下text,blob列均不能有默認值。

避免使用ENUM數據類型：

修改ENUM值需要alter語句；

ENUM類型的ORDER BY操作效率低，需要額外操作；

禁止使用數值作為ENUM的枚舉值。

盡可能把所有列定義為NOTNULL：

索引NULL列需占用額外的空間來保存（是否為空），所以占用更多空間；

比較和計算對NULL做特別處理，可能索引會失效。

使用TIMESTAMP或DATETIME類型存儲時間：

TIMESTAMP 1970-01-01~2038-01-19，占用4字節和INT存儲相同；

其他范圍使用DATETIME類型存儲。

金融財務相關類數據，使用DECIMAL類型：

占用空間有定義的寬度決定；

可用于存儲比bigint更大的整數數據。

1.2    數據庫SQL開發規范

建議使用預編譯語句進行數據庫操作：

只傳參數，比傳遞SQL語句更高效；

相同語句可以一次解析，多次使用，提高處理效率。

避免數據類型的隱式轉換：

隱式轉換可能導致索引失效。

充分利用表上已存在的索引：

避免使用雙%的查詢條件，如’123%’可使用索引；

一個SQL只能使用聯合索引一列進行范圍查詢；

PS：index(a, b, c)，where a = xxand b > low and b < high可使用到b列的索引，

         wherea > low and a < high and b = xx，b這列使用不到索引；

         這個處理和oracle的聯合索引是一致的。

使用left join或not exists來優化not in操作。

禁止跨庫查詢：

程序連接不同數據庫使用不同的賬號；

為數據庫遷移和分庫分表留出余地；

降低業務的耦合度；

避免權限過大產生的安全風險，SQL注入也只能看到一個庫。

禁止使用Select *：

消耗過多的CPU, IO, 網絡資源；

無法使用覆蓋索引；

可減少表結構變更帶來的影響。

禁止使用不含字段列表的INSERT：

INSERT INTO T(A, B) VALUES(…….);

可減少表結構變更帶來的影響。

盡量避免使用子查詢，可以把子查詢優化為join操作：

當然并不是所有的子查詢都能轉換為join，如group by等得子查詢；

通常是IN子查詢這樣的語句。

子查詢結果集無法使用索引；子查詢會產生臨時表操作，如果子查詢數據量大將嚴重影響效率；消耗過多的CPU、IO。

避免使用JOIN關聯太多的表：

每Join一個表多占用一部分內存（join_buffer_size）;

會產生臨時表操作，影響查詢效率；

MySQL最多允許關聯61個表，建議不超過5個。

減少同數據庫的交互次數：

數據庫更適合做批量操作；

合并多個相同的操作到一起；

PS：如alter table t addcolumn c1 int, change column c2 c2 int…

使用in代替or：

in的值不要超過500個；

in操作可以有效的利用索引。

禁止使用order byrand()進行隨機排序：

會把表中所有滿足條件的數據裝載到內存中進行排序；

消耗大量的CPU/IO/MEM；

處理方式：推薦在程序中獲取一個隨機值，然后在數據庫中獲取數據的方式（親認為開發人員愿意去實現多余的算法嗎？都希望一句SQL返回結果集）。

WHERE從句禁止對列進行函數轉換和計算：

where date(createtime) = ‘20171010’ à where createtime >= ‘20171010’ and createtime < ‘20171011’

UNIONALL OR UNION：

這兩種排序方式和Oracle的完全一致，UNION ALL不排序，UNION排序。

拆分復雜SQL為多個小SQL：

MySQL一個SQL只能使用一個CPU進行計算；

SQL拆分后可以通過并行查詢提高處理效率。

1.3    數據庫操作行為規范

超過100W行數據批量DML操作，要分批多次進行操作：

需考慮主從延遲；

binlog日志為row格式產生大量日志；

避免大事務的操作，阻塞并行。

大表表結構修改：

推薦使用pt-online-schema-change修改表結構；

可避免大表修改產生的主動延遲；

避免在對表字段修改時進行鎖表。

禁止為程序賬號賦予super權限：

當達到最大連接數限制時，還允許使用一個帶有super權限的用戶連接；

super權限只能留給DBA處理問題的賬號使用。

對程序賬號，遵循權限最小的原則：

程序在使用數據庫賬號只能在一個DB下使用，不允許跨庫使用；

程序賬號原則上不允許有drop權限。