MySQL資料庫規約

標籤：返回   文本   多個   int   需要   extra   har   double   margin   

一、建表規約1、表達是與否概念的欄位，必須使用 is_xxx 的方式命名，資料類型是 unsigned tinyint（1 表示是， 0 表示否） 。 2、表名、欄位名必須使用小寫字母或數字， 禁止出現數字開頭，禁止兩個底線中間只出現數字。資料庫欄位名的修改代價很大，因為無法進行預發布，所以欄位名稱需要謹慎考慮。 3、表名不使用複數名詞。 4、主鍵索引名為 pk_欄位名； 唯一索引名為 uk_欄位名； 普通索引名則為 idx_欄位名。 5、小數類型為 decimal，禁止使用 float 和 double(存在精度損失的問題) 6、如果儲存的字串長度幾乎相等，使用 char 定長字串類型。 7、表必備三欄位： id, gmt_create, gmt_modified。說明： 其中 id 必為主鍵，類型為 unsigned bigint、單表時自增、步長為 1。 gmt_create,gmt_modified 的類型均為 date_time 類型。 8、欄位允許適當冗餘，以提高查詢效能，但必須考慮資料一致。冗餘欄位應遵循：1） 不是頻繁修改的欄位。2） 不是 varchar 超長欄位，更不能是 text 欄位。正例： 商品類目名稱使用頻率高， 欄位長度短，名稱基本一成不變， 可在相關聯的表中冗餘儲存類目名稱，避免關聯查詢 9、單表行數超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表。 二、索引規約 1、業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引。 2、超過三個表禁止 join。需要 join 的欄位，資料類型必須絕對一致； 多表關聯查詢時，保證被關聯的欄位需要有索引。 3、在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文本區分度決定索引長度即可。說明： 索引的長度與區分度是一對矛盾體，一般對字串類型資料，長度為 20 的索引，區分度會高達 90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區分度來確定。 4、頁面搜尋嚴禁左模糊或者全模糊，如果需要請走搜尋引擎來解決。說明： 索引檔案具有 B-Tree 的最左首碼匹配特性，如果左邊的值未確定，那麼無法使用此索引。 5、利用延遲關聯或者子查詢最佳化超多分頁情境。說明： MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後返回放棄前 offset 行，返回N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行 SQL改寫。正例： 先快速定位需要擷取的 id 段，然後再關聯：SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id 6、SQL 效能最佳化的目標：至少要達到 range 層級， 要求是 ref 層級， 如果可以是 consts最好。說明：1） consts 單表中最多隻有一個匹配行（主鍵或者唯一索引） ，在最佳化階段即可讀取到資料。2） ref 指的是使用普通的索引（normal index） 。3） range 對索引進行範圍檢索。 7、建複合式索引的時候，區分度最高的在最左邊。正例： 如果 where a=? and b=? ， a 列的幾乎接近於唯一值，那麼只需要單建 idx_a 索引即可 8、如果有 order by 的情境，請注意利用索引的有序性。 order by 最後的欄位是複合式索引的一部分，並且放在索引組合順序的最後，避免出現 file_sort 的情況，影響查詢效能。正例： where a=? and b=? order by c; 索引： a_b_c反例： 索引中有範圍尋找，那麼索引有序性無法利用，如： WHERE a>10 ORDER BY b; 索引a_b 無法排序。 9、利用覆蓋索引來進行查詢操作， 避免回表。說明： 如果一本書需要知道第 11 章是什麼標題，會翻開第 11 章對應的那一頁嗎？瀏覽目錄一下就好，這個目錄就是起到覆蓋索引的作用。
正例： 能夠建立索引的種類：主鍵索引、唯一索引、普通索引，而覆蓋索引是一種查詢的一種效果，用 explain 的結果， extra 列會出現： using index。 

 三、SQL語句1、不要使用 count(列名)或 count(常量)來替代 count(*)， count(*)是 SQL92 定義的標準統計行數的文法，跟資料庫無關，跟 NULL 和非 NULL 無關 2、count(distinct col) 計算該列除 NULL 之外的不重複行數， 注意 count(distinct col1, col2) 如果其中一列全為 NULL，那麼即使另一列有不同的值，也返回為 0。 3、當某一列的值全是 NULL 時， count(col)的返回結果為 0，但 sum(col)的返回結果為NULL，因此使用 sum()時需注意 NPE 問題。正例： 可以使用如下方式來避免 sum 的 NPE 問題： SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table; 4、在代碼中寫分頁查詢邏輯時，若 count 為 0 應直接返回，避免執行後面的分頁語句。 5、禁止使用預存程序，預存程序難以調試和擴充，更沒有移植性。

MySQL資料庫規約