高性能SQL查詢最佳化取數速度方案

tags: `SQL`

原文

為什麼要做SQL最佳化

報表的核心是資料，資料集是否合理決定報表的質量。
每張報表都應該有一個主資料集，為了降低維護時的工作量，盡量將所有欄位置於主資料集，除非在某些情況下，不使用多源資料集會導致主資料集異常複雜。
在製作報表之前，盡量考慮到所有需要展示的資料欄位，在資料庫軟體中，合理編寫sql語句，大數據分析情況盡量對sql做最佳化，以及添加索引。
擁有高性能SQL查詢語句，能使查詢速度加快，報表展示速度得到較明顯的提升！

20條Tips方案最佳化你的SQL查詢

SELECT子句中避免使用「*」
- 當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用『』是一個方便的方法。不幸的是，這是一個非常低效的方法。實際上，ORACLE在解析的過程中, 會將「」依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。
刪除重複記錄
- 最高效的刪除重複記錄方法 ( 因為使用了ROWID)
  DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)
用TRUNCATE替代DELETE
- 當刪除表中的記錄時，在通常情況下，回滾段(rollback segments ) 用來存放可以被恢復的信息，如果你沒有COMMIT事務，ORACLE會將資料恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況)，而當運用TRUNCATE時，回滾段不再存放任何可被恢復的信息。當命令運行後，資料不能被恢復。因此很少的資源被調用,執行時間也會很短。
計算記錄條數
- 和一般的觀點相反， count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的。例如COUNT(EMPNO)
用EXISTS替代IN
- 在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行連結。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。
  – 低效
  SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC =』MELB』)
  – 高效
  SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT 『X』 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 『MELB』)
用EXISTS替換DISTINCT
- 當提交一個包含一對多表資料(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換例如: Sql程式碼
  – 低效:
  SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
  – 高效:
  SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 『X』 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)
  EXISTS 使查詢更為迅速
用>=替代>
- 如果DEPTNO上有一個索引
  – 高效：
  SELECT * FROM EMP WHERE DEPTNO >=4
  – 低效：
  SELECT * FROM EMP WHERE DEPTNO >3
  兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
應盡量避免在 where 子句中對欄位判斷!
- 如：
  select id from t where num is null
- 可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：
  select id from t where num=0
應避免在 where 子句中使用!=或<>操作符!
- 將引擎放棄使用索引而進行全表掃描。優化器將無法通過索引來確定將要命中的行數,因此需要搜索該表的所有行。
應避免在 where 子句中使用 or 連結!
- 否則將導致引擎放棄使用索引而進行全表掃描，如：
  select id from t where num=10 or num=20
- 可以這樣查詢：
  select id from t where num=10 union all select id from t where num=20
in 和 not in 也要慎用
- 因為IN會使系統無法使用索引,而只能直接搜索表中的資料。如：
  select id from t where num in(1,2,3)
- 對於連續的數值，能用 between 就不要用 in 了：
  select id from t where num between 1 and 3
應避免在 where 子句中進行表達式操作
- 這將導致引擎放棄使用索引而進行全表掃描。如：
  SELECT * FROM T1 WHERE F1/2=100
  應改為:
  SELECT * FROM T1 WHERE F1=100*2
  
  SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=』5378』
  應改為:
  SELECT * FROM RECORD WHERE CARD_NO LIKE 『5378%』
  
  SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
  應改為:
  SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
  即：任何對列的操作都將導致表掃描，它包括資料庫函數、計算表達式等等，查詢時要儘可能將操作移至等號右邊。
應避免在where子句中進行函數操作
這將導致引擎放棄使用索引而進行全表掃描。如：
select id from t where substring(name,1,3)='abc'
–name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30')=0
–『2005-11-30』生成的id
應改為:
select id from t where name like 'abc%'
select id from t where createdate>=』2005-11-30′ and createdate<'2005-12-1'
不要在 where 子句中的「=」左邊運算
進行函數、算術運算或其他表達式運算，系統將可能無法正確使用索引。
盡量避免向客戶端返回大數據量
若數據量過大，應該考慮相應需求是否合理。
避免使用不兼容的資料類型
例如float和int、char和varchar、binary和varbinary是不兼容的。資料類型的不兼容可能使優化器無法執行一些本來可以進行的最佳化操作。例如:
SELECT name FROM employee WHERE salary > 60000
在這條語句中,如salary欄位是money型的,則優化器很難對其進行最佳化,因為60000是個整型數。我們應當在編程時將整型轉化成為錢幣型,而不要等到運行時轉化。
充分利用連接條件
在某種情況下，兩個表之間可能不只一個的連結條件，這時在 WHERE 子句中將連接條件完整的寫上，有可能大大提高查詢速度。
例：
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO
第二句將比第一句執行快得多。
能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改為：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
能用UNION ALL就不要用UNION！
UNION ALL不執行SELECT DISTINCT函數，這樣就會減少很多不必要的資源
盡量不要用SELECT INTO語句！
SELECT INTO 語句會導致表鎖定，阻止其他使用者訪問該表。

高性能SQL查詢 最佳化取數速度方案

tags: SQL

原文

為什麼要做SQL最佳化

20條Tips方案 最佳化你的SQL查詢

高性能SQL查詢最佳化取數速度方案

tags: `SQL`

20條Tips方案最佳化你的SQL查詢