SQL編寫規范36條 · MySQL SQL開發規范

# SQL編寫規范36條以后可能還會增多，也可以考慮做一下減法。 1. 去掉不必要的括號 ``` 如： ((a AND b) AND c OR (((a AND b) AND (c AND d)))) 修改成 (a AND b AND c) OR (a AND b AND c AND d) ``` 2. 去掉重疊常量 ``` 如： (a<b AND b=c) AND a=5 修改成 b>5 AND b=c AND a=5 ``` 3. 去除常量條件(由于常量重疊需要) ``` 如： (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) 修改成 B=5 OR B=6 ``` 4. 去掉無意義的連接用條件 ``` 如：1=1，2>1，1<2等直接從where子句中去掉。 **鄭松華**注解：但是對于1=1 有可能是開發為了動態添加sql 標準化而寫如 select * from emp where 1=1 and emp_no=10001 and …. 這樣后面都是and 開始 ``` 5. 開發過程中不使用拼字符串的方式來完成where子句 6. 多使用等值操作，少使用非等值操作 ``` WHERE條件中的非等值條件（IN、BETWEEN、<、<=、>、>=）會導致后面的條件使用不了索引，因為不能同時用到兩個范圍條件。鄭松華注解：對這條有疑問因為一個sql 的一個表來說只分access 和 fiter 只要使用了相應的選擇率好的索引過濾了大部分數據對后面作為fiter 條件來說沒有大問題 ``` 7. 常數表優先，字典表或小表其次，大表最后 ``` 常數表指：空表或只有1行的表。與在一個PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表。如： SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; 字典表指：小數量的行。如：自定義的自增字段表，而不使用MySQL的AUTO_INCREMENT。鄭松華注解：SQL的執行計劃是基于cost的除非強制使用hint 所以無法控制上面的可以改為表的連接條件最好是查詢結果集最少的為驅動表后續表要有良好的索引 ``` 8. 減少或避免臨時表 ``` 如果有一個ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含聯接隊列中的第一個表之外的其它表的列，則創建一個臨時表。鄭松華注解：這里說的臨時表應該是執行計劃中extra部分的 using temporary 和 using filesort 關鍵字 ``` 9. where子句中的數據掃描別跨越表的30% ``` 比如：where primary_key <> 1或者primary_key not in(…)，這樣跨表的數據肯定超過30%了。 where status=1，其中1值非常少，主要是0值，比如一個表的記錄刪除用了一個狀態位，而刪除的記錄又比較少。鄭松華注解：這個應該講的是索引的選擇率問題，如果是刪除數據的話大量刪除考慮創建一個臨時表改表名的方法或者分段刪除的方法最好不要一次性進行大量刪除 ``` 10. where子句中同一個表的不同的字段組合建議小于等于5組，否則考慮業務邏輯或分表 ``` 鄭松華注解：只要有一個進行好的選擇率的索引條件有幾個都無所謂 ``` 11. 不使用is null或is not null，字段設計時建議not null，若麻煩可折中考慮給一默認值 12. 使用like時，%不要放在首字符位置。 ``` 如果%必須放在首字符位置鄭松華注解：這是基于有索引而言，記住只是字符類型如果是別的數據類型即使把%放在后面一樣不能使用索引 ``` 13. 值域比較多的表字段放在前面 ``` 比如：id，date字段放在前面，而status這樣的字段放在后面，具體的可以通過執行計劃來把握。鄭松華注解：不知道作者想表達意思？如果是SQL中的select 列中的話上面的沒有關系因為只跟是否回表有關系 ``` 14. 表字段組合中出現比較多的表字段放在前面 ``` 方便綜合評估索引，緩解因為索引過多導致的增刪改的一些性能問題。 ``` 15. 表字段不能有表達式或是函數 ``` 如：where abs(列)>3或where 列*10>100 鄭松華注解：這是基于該列有索引如果使用上述方法會導致不能使用索引 ``` 16. 注意表字段的類型，避免表字段的隱示轉換 ``` 比如：列為int，如果where 列=’1’，則會出現轉換。鄭松華注解：這是基于該列有索引如果使用上述方法會導致不能使用索引 ``` 17. 考慮使用union all，少使用union，注意考慮去重 ``` union all不去重，而少了排序操作，速度相對比union要快，如果沒有去重的需求，優先使用union all。鄭松華注解：Mysql5.6和5.7對union all 的執行計劃是不一樣的注意把握 ``` 18. 不同字段的值or或in大于等于3次，考慮用union all替換；同一字段的值or用in替換 ``` Select * from opp WHERE phone=‘12347856' or phone=‘42242233'; 考慮用 Select * from opp WHERE phone in ('12347856' , '42242233'); Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000'; 考慮用 Select * from opp WHERE phone='010-88886666' union all Select * from opp WHERE cellPhone='13800138000'; 鄭松華注解：Mysql5.6 和5.7當中對in 的處理機制有點不一樣注意把握 ``` 19. 用Where子句替換HAVING子句 ``` select id,count(*) from table group by id having age>=30 order by null; 考慮用 select id,count(*) from table where age>=30 group by id order by null; 鄭松華注解：上面的第一個sql 本身就不嚴謹有可能出現執行計劃不同結果不同 ``` 20. 對同一表的order by和group by操作分別小于3組，否則考慮業務邏輯或分表 21. 盡量使用主鍵進行update和delete 22. 小心text/blobs等大字段，如果確實不需要這樣的大字段，則不用放入sql語句中 ``` 鄭松華注解：有可能產生行鏈接問題，還有排序的時候產生大量消耗 ``` 23. 使用INSERT ... ON DUPLICATE KEY update (INSERT IGNORE)來避免不必要的查詢 24. 考慮使用limit N，少用limit M,N，特別是大表，或M比較大的時候 25. 減少或避免排序，如：group by語句中如果不需要排序，可以增加order by null 26. 增刪改語句中不使用不確定值函數和隨機函數，如：RAND()和NOW()等。 27. INSERT語句使用batch提交（INSERT INTO table VALUES(),(),()??），values的個數不超過500。 28. 避免使用存儲過程、觸發器、函數、UDF、events等，容易將業務邏輯和DB耦合在一起，并且MySQL的存儲過程、觸發器、函數、UDF、events中存在一定的bug。 29. 避免使用JOIN。 30. 使用合理的SQL語句減少與數據庫的交互次數。 ``` INSERT ... ON DUPLICATE KEY UPDATE REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),() UPDATE … WHERE ID IN(10,20,50,…) ``` 31. 減少使用視圖，避免復雜的語句。 32. SQL語句中IN包含的值不超過500。 33. UPDATE、DELETE語句不使用LIMIT。有主鍵id的表WHERE條件應結合主鍵。 34. 使用prepared statement，可以提供性能并且避免SQL注入。 35. InnoDB表避免使用COUNT(*)操作，計數統計實時要求較強可以使用memcache或者redis，非實時統計可以使用單獨統計表，定時更新。 36. 禁止在Update語句，將“,”寫成“and”，非常危險。 ``` 正確示例：update Table set uid=uid+1000,gid=gid+1000 where id <=2 ; 錯誤示例：update Table set uid=uid+1000 and gid=gid+1000 where id <=2 ; 此時“uid=uid+1000 and gid=gid+1000”將作為值賦給uid，并且無Warning！！！ ```