SQL的開發建議（MySQL和Oracle）

MYSQL 開發建議

關於建表

1、儘量使用INNODB儲存引擎。

2、建議使用UNSIGNED儲存非負數值。

3、建議使用INT UNSIGNED儲存IPV4。

4、強烈建議使用TINYINT來代替ENUM型別。

5、使用VARBINARY儲存大小寫敏感的變長字串或二進位制內容。

7、區分使用DATETIME和TIMESTAMP。儲存年使用YEAR型別。儲存日期使用DATE型別。 儲存時間(精確到秒)建議使用TIMESTAMP型別。

8、將大欄位、訪問頻率低的欄位拆分到單獨的表中儲存,分離冷熱資料。

9、禁止在資料庫表中儲存明文密碼。

10.表必須有主鍵,推薦使用UNSIGNED自增列作為主鍵。

11、表字符集使用UTF8,必要時可申請使用UTF8MB4字符集。

a)UTF8字符集儲存漢字佔用3個位元組,儲存英文字元佔用一個位元組。

b)UTF8統一而且通用,不會出現轉碼出現亂碼風險。

c)如果遇到EMOJ等表情符號的儲存需求,可申請使用UTF8MB4字符集。

12、採用合適的分庫分表策略。例如千庫十表、十庫百表等。

 

關於索引

1、禁止冗餘索引。

2、禁止重複索引。

3、不在低基數列上建立索引,例如“性別”。

4、合理使用覆蓋索引減少IO,避免排序。

 

關於SQL

1、 不管資料庫隔離級別是什麼狀態或者事務大小，養成COMMIT習慣，避免事務鎖的長期持有。

2、 更新(update)sql語句儘量使用主鍵條件

3、用IN代替OR。SQL語句中IN包含的值不應過多。

4、用UNION ALL代替UNION。UNION ALL不需要對結果集再進行排序。

5、儘量不使用order by rand()。

6、建議使用合理的分頁方式以提高分頁效率。

7、SELECT只獲取必要的欄位,儘量少使用SELECT *。

8、SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函式。

9、減少與資料庫互動次數,儘量採用批次SQL語句。

使用下面的語句來減少和db的互動次數:

a)INSERT ... ON DUPLICATE KEY UPDATE

b)REPLACE INTO

c)INSERT IGNORE

d)INSERT INTO VALUES()

10、拆分複雜SQL為多個小SQL,避免大事務。

11、對同一個表的多次alter操作必須合併為一次操作。

Oracle 開發建議

使用索引需要注意的地方：

1、避免在索引列上使用NOT，　

2、避免在索引列上使用計算．

低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效:SELECT … FROM DEPT WHERE SAL > 25000/12;

3、避免在索引列上使用IS NULL和IS NOT NULL

低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

4、避免改變索引列的型別.

 

關於SQL

1、用EXISTS替換DISTINCT：

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO

And E.sex =man

(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D

WHERE EXISTS

( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO

And E.sex =man

);

2、用(UNION)UNION ALL替換OR (適用於索引列)

高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

3、用UNION-ALL 替換UNION ( 如果有可能的話)。

4、Order By語句加在索引列，最好是主鍵PK上。

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE（低效）

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE （高效）

5、避免使用耗費資源的操作:

帶有DISTINCT,UNION,MINUS,INTERSECT的SQL語句會啟動SQL引擎 執行耗費資源的排序(SORT)功能.

6、使用Where替代Having（如果可以的話）

低效:

SELECT JOB , AVG(SAL)

FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX

7、通常來說，如果語句能夠避免子查詢的使用，就儘量不用子查詢。因為子查詢的開銷是相當昂貴的。具體的例子在後面的案例“一條SQL的最佳化過程”中。

8、注意WHERE子句中的連線順序。合理選擇驅動表。

9、SELECT子句中避免使用 *。ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是透過查詢資料字典完成的, 這意味著將耗費更多的時間 。