MySQL 設計規範（續）

標籤：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許可權。

MySQL 設計規範（續）