資料庫優化SQL

李德東發表於2018-11-03
資料庫優化SQL

sql優化規則：

1.對於查詢，儘量不要使用全表掃描，儘量在where子句以及order by所對應的欄位建立索引。

2.應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

　　最好不要給資料庫留NULL，儘可能的使用 NOT NULL填充資料庫.備註、描述、評論之類的可以設定為 NULL，其他的，最好不要使用NULL

       一些限制索引的寫法如下：

　　 ①使用不等運算子（<> !=）

　　②使用IS NULL或者is notnull做判斷。比如NVL等

　　③使用函式

select  t.* from eb_bill_app t where substr(t.apply_id,1,6) ='201805'

　　④比較不匹配的資料型別.本來該型別為char，卻使用int比較，oracle自動呼叫to_number函式，導致不使用索引（字元型欄位為數字時在where條件裡不新增引號）。

　　⑤like "%_" 百分號在前。

　　⑥not in ,not exist.

　　⑦單獨引用複合索引裡非第一位置的索引列

　　⑧B-tree索引 is null不會走,is not null會走,點陣圖索引 is null,is not null   都會走。聯合索引 is not null 只要在建立的索引列（不分先後）都會走。

　　⑨應儘量避免在 where 子句中使用 or 來連線條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描

3.select語句中避免使用*，因為oracle在解析過程中會將*轉換為所有的列名。

4.儘量多使用commit，，在事務控制允許的範圍內，commit有利於釋放資源。

5.使用表別名，減少由column引起的歧義。

6.避免使用having

7.對於多張大資料量（這裡幾百條就算大了）的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，效能很差。

8.如果使用到了臨時表，在儲存過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

9.如果資料量過大，優先考慮表分割槽

 

 

改造sql示例：資料庫中有600萬資料左右，僅僅改造一下子查詢。查詢出來的時間相差很大

SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT T.*,
                       GY.F_NAME applyOperatorName,
                       CUST.CUST_NAME,
                       (SELECT COUNT(1)
                          FROM BL_STOCKBILL BILL
                         WHERE BILL.BILL_ID IN
                               (SELECT EXT.BILL_ID
                                  FROM BL_STOCKBILL_EXT EXT
                                 WHERE EXT.PLEDGE_APP_ID = T.PLEDGE_ID
                                   AND EXT.STATUS = '0')) AS BILLS,
                       (SELECT SUM(BILL.BILL_AMT)
                          FROM BL_STOCKBILL BILL
                         WHERE BILL.BILL_ID IN
                               (SELECT EXT.BILL_ID
                                  FROM BL_STOCKBILL_EXT EXT
                                 WHERE EXT.PLEDGE_APP_ID = T.PLEDGE_ID
                                   AND EXT.STATUS = '0')) AS BILLTOTALAMT
                  FROM EB_BL_PLEDGE_APP T, LSGYZD GY, CUST_BASE CUST
                 WHERE T.APP_OPERATOR = GY.F_GYBH(+)
                   AND CUST.CUST_CODE(+) = T.CUST_CODE
                   AND T.SOURCE_MODEL = '1'
                   AND T.STATUS = '00'
                   AND T.APP_OPERATOR = '6660001'
                   AND T.CUST_CODE = '99100001816') ROW_
         WHERE ROWNUM <= 10)
 WHERE ROWNUM_ > 0;

耗時：

 優化後：

SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (SELECT T.*,
                       GY.F_NAME applyOperatorName,
                       CUST.CUST_NAME,
                       TMP.BILLS,
                       TMP.BILLTOTALAMT
                  FROM EB_BL_PLEDGE_APP T,
                       LSGYZD GY,
                       CUST_BASE CUST,
                       (SELECT EXT.PLEDGE_APP_ID,
                               COUNT(1) BILLS,
                               SUM(BILL.BILL_AMT) BILLTOTALAMT
                          FROM BL_STOCKBILL BILL,BL_STOCKBILL_EXT EXT
                         WHERE BILL.BILL_ID =EXT.BILL_ID
                              AND  EXT.STATUS = '0'
                                 GROUP BY EXT.PLEDGE_APP_ID) TMP
                 WHERE T.APP_OPERATOR = GY.F_GYBH(+)
                   AND CUST.CUST_CODE(+) = T.CUST_CODE
                   AND T.SOURCE_MODEL = '1'
                   AND TMP.PLEDGE_APP_ID = T.PLEDGE_ID
                   AND T.STATUS = '00'
                   AND T.APP_OPERATOR = '6660001'
                   AND T.CUST_CODE = '99100001816') ROW_
         WHERE ROWNUM <= 10)
 WHERE ROWNUM_ > 0;

 

 

相關文章