昨天講的是SQL的好習慣,比大小要注意資料型別、輸出的時候要思考防呆、要給default值。今天要接著講 Performance
。效能好不好的關鍵要先了解CPU執行Oracle
命令的順序是: 從上到下
+ 從右向左
。還有一些語法在使用上也要注意,像是 distinct
與 union
建議在資料少的時候使用,否則逐筆檢查容易造成 timeout error
。
1. 避免LIKE運算子比對萬用字元(%)開頭
:萬用字元在常數的開頭,則不會使用索引來執行查詢。
--有效索引
SELECT * FROM EMPLOYEE WHERE FirstName LIKE '林%'
--無效索引
SELECT * FROM EMPLOYEE WHERE FirstName LIKE '%林'
2. 避免對欄位運算或函數運算:在WHERE子句用某個欄位的資料運算後再搜尋與比對效能較差,當資料表有大量資料時,在查詢時就呼叫函數的次數就等於資料的筆數,會導致效能變差。
-- 有效索引(先算出700000除以12運算結果後,再用索引搜尋)
SELECT FirstName, LastName, salary
FROM Employee
WHERE salary >= 700000/12
-- 無效索引:指令先做運算(乘以12後再和700000做比較)
SELECT Name, salary
FROM Employee
WHERE salary*12 >= 700000
-- 若有10萬筆員工資料,就會呼叫SUBSTRING函數10萬次
SELECT FirstName, LastName, Title
FROM EMPLOYEE
WHERE SUBSTRING(FirstName,1,1) = '林'
-- 建議改用下方的SQL查詢指令,如下所示:
SELECT FirstName, LastName, Title
FROM EMPLOYEE WHERE FirstName LIKE '林%'
3. 避免使用OR運算子:OR運算子只要有一個條件沒索引,則其它條件的索引也失效,建議用IN
或UNION
-- 效能差
SELECT FirstName, LastName, Title
FROM EMPLOYEE
WHERE LastName = '張'
OR LastName = '王'
OR LastName = '林'
-- 效能好
SELECT FirstName, LastName, Title
FROM EMPLOYEE
WHERE LastName IN ('張','王','林')
4. 適當使用子查詢(Subquery)
子查詢會影響查詢效率,關聯子查詢無法單獨被執行,外層「每一次」查詢都需要讀取內層資料,或內層「每一次」查詢動作都需要讀取外層資料,關連子查詢會隨著資料量的成長,效能變很差。
5. 避免負向查詢:『負向查詢』的判斷會讓效能變差
-- 效能差
SELECT E.ID, E.FirstName, E.LastName
FROM Employee E
WHERE E.ID NOT IN (Select ID From SalaryAdj)
-- 效能好:OUTER JOIN 加上NULL
SELECT E.ID, E.FirstName, E.LastName
FROM Employee E LEFT JOIN SalaryAdj S ON E.ID = S.ID
WHERE S.ID IS NULL
6. 避免大量的排序操作:避免ORDER BY、GROUP BY和HAVING的大量資料排序操作,因為操作排序會讓資料庫做額外的計算,增加處理時間,如果可能盡量避免使用。
7.避免使用count(*):count(*)所有欄位O(n*m),但count(1)效能是O(n)
8.distinct用於小資料集合:不要用於TABLE量大的select條件,否則每筆資料都要比較效能極差
9.使用 union all:union包含distinct的功能,非必要勿用。