mysql開發規範

標籤：member   name   mit   簡單   儲存   char   出現   字母   字元   

1：庫表設計

• 庫名、表名、欄位名採用小寫字母,並採用底線分割;對相關功能的表應該使用相同的首碼，如member_xxx,首碼通常為庫名或依賴主實體物件;
• 所有的表以及欄位都必須有備忘、詳細說明表以及欄位的含義
• 涉及貨幣金額或其他精度敏感的資料必須使用定點數Decimal來替代float和double
• 庫名、表名、欄位名禁止使用mysql保留字,如data、like、desc等
• 控製表欄位數,單表不超過50個純int/20個varchar(10)欄位等同儲存體積的欄位數,上限控制在20~50

2：索引設計

基本規則：索引不是越多越好,能不添加索引盡量不添加索引,過多的索引會嚴重降低插入和更新的效率，並帶來讀寫衝突和死結

• 索引名稱必須使用小寫，普通索引按照idx_欄位名_欄位名[_欄位名]進行命名,唯一索引按照 uniq_欄位名_欄位名[_欄位名]進行名
• 表必須有主鍵，推薦使用獨立業務的AUTO_INCREMENT列或全域ID產生器做主鍵，禁止使用多欄位做聯合主鍵
• 不使用UUID/MD5/HASH等函數產生無規則值做主鍵,效率極差
• 索引數量控制

1. 單張表中索引數量不超過5個
2. 單個索引中的欄位數不超過5個
3. 對字串使用首碼索引,索引長度不超過10個字元

• 索引欄位的順序需要考慮每個欄位去重以後的數量,區分度最大的[個數最多的]放在前面
• 合理建立聯合索引(避免冗餘),符合最左首碼原則：（a,b,c）相對於（a）、(a,b)、(a,b,c)
• 可能需要添加索引的欄位

1. Order by，group by，distinct的字元按需要添加在索引的後面
2. update,delete語句需要根據where條件添加索引
3. 對於join操作,需要join欄位上添加索引

• 索引使用禁忌

1. 不使用%前置的查詢,如like ‘%ab’
2. 不使用負向查詢，如 not in/not lilke/<>
3. 不在區分度的列上建立索引,如性別
4. 不在索引列進行數學運算和函數運算

        樣本：假設在表table中id建立索引

1. select col_A,col_B from table where id+1>100 不會使用索引
2. select col_A,col_B from table where id>100+1 會使用索引

3：sql最佳化

• 線上盡量少使用 大sql,可能一條大sql就把整個資料庫堵死，將複雜sql拆分為多條簡單的sql,化繁為簡

1. 一條sql只能在一個cpu運算,如果sql比較複雜執行效率會非常低
2. 簡單sql快取命中率更高
3. 減少鎖表時間
4. 充分利用多核cpu，提高並發效率

• 減少mysql端的數學運算和邏輯判斷，避免sql語句出現md5()、order by rand()等
• 過濾表記錄合并不去重的情況，改寫union為union all
• 減少使用拼接sql,使用先行編譯語句，降低sql注入的機率
• where 條件中的非等值條件(in between < <= > >=)會導致使用不了聯合索引的後續欄位,注意避免
• where條件比較,欄位類型和傳入值必須保持類型一直,避免隱式轉換

      樣本

         欄位：code varchar(50) not null coment ‘編碼‘ #code 上建立了索引

1. select * from table_name where code=10001；不會使用索引
2. select * from table_name where code=‘10001‘；會使用索引

• Limit 分頁最佳化

1. 傳統分頁

select * from table limit 1000,10

limit原理：

LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的位移量，第二個參數指定返回記錄行的最大數目

limit 1000,10//檢索記錄行1001-1010,位移量越大則越慢

1. 推薦分頁

• 分頁方式1：

Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

id是主鍵查詢的速度比較快

• 分頁方式2：

• 程式取ID: Select id from table limit 10000,10;

• Select * from table WHERE ID in(123,456…);

 

mysql開發規範