iT邦幫忙

2024 iThome 鐵人賽

DAY 23
0

昨天講的是SQL的好習慣,比大小要注意資料型別、輸出的時候要思考防呆、要給default值。今天要接著講 Performance 。效能好不好的關鍵要先了解CPU執行Oracle命令的順序是: 從上到下 + 從右向左。還有一些語法在使用上也要注意,像是 distinctunion 建議在資料少的時候使用,否則逐筆檢查容易造成 timeout error

效能issue

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運算子只要有一個條件沒索引,則其它條件的索引也失效,建議用INUNION
-- 效能差
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的功能,非必要勿用。

Reference


上一篇
Day 22 進階-撰寫SQL的好習慣
下一篇
Day 24 進階-效能(2)
系列文
不居功的系統隱士 - 30天由淺入深學SQL30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言