資料庫SQL調優的幾種方式

xqhrs232發表於2020-10-23
資料庫SQL

原文地址::https://blog.csdn.net/u010520146/article/details/81161762

 

相關文章

1、大家談談索引在進行select,update,delete,insert操作時對資料庫效能的影響----https://bbs.csdn.net/topics/390515609

2、SQL語句為什麼使用select * 會降低查詢速度?----https://www.zhihu.com/question/37777220

3、MySQL/Oracle資料庫優化總結（非常全面）----https://blog.csdn.net/baidu_37107022/article/details/77460464?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.edu_weight

4、sql優化（面試必問一）----https://blog.csdn.net/tian31233/article/details/52052963?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.edu_weight

5、sql優化的幾種方式----https://blog.csdn.net/qq_38789941/article/details/83744271?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-5.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-5.edu_weight

6、SQL語句優化有哪些方法----https://blog.csdn.net/lizhen1114/article/details/79076447?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-9.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-9.edu_weight

7、8個SQL語句常用優化技巧----https://blog.csdn.net/csdnstudent/article/details/40398245?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-8.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-8.edu_weight

8、MySQL資料庫優化的八種方式(經典必看)----https://blog.csdn.net/orecle_littleboy/article/details/88534160?utm_medium=distribute.pc_relevant.none-task-blog-title-8&spm=1001.2101.3001.4242

 

在專案中,SQL的調優對專案的效能來講至關重要,所有掌握常見的SQL調優方式是必不可少的,下面介紹幾種常見的SQL的調優方式,供借鑑.

一.建立索引

1.要儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引
2.(1)在經常需要進行檢索的欄位上建立索引，比如要按照表欄位username進行檢索，那麼就應該在姓名欄位上建立索引，如果經常要按照員工部門和員工崗位級別進行檢索，那麼就應該在員工部門和員工崗位級別這兩個欄位上建立索引。
(2)建立索引給檢索帶來的效能提升往往是巨大的，因此在發現檢索速度過慢的時候應該首先想到的就是建立索引。
(3)一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

二.避免在索引上使用計算

在where字句中，如果索引列是計算或者函式的一部分，DBMS的優化器將不會使用索引而使用全表查詢,函式
屬於計算的一種,同時在in和exists中通常情況下使用EXISTS，因為in不走索引
效率低：

 select * from user where salary*22>11000(salary是索引列)
  • 1

效率高：

 select * from user where salary>11000/22(salary是索引列)
  • 1

三.使用預編譯查詢

程式中通常是根據使用者的輸入來動態執行SQL，這時應該儘量使用引數化SQL,這樣不僅可以避免SQL隱碼攻擊漏洞
攻擊，最重要資料庫會對這些引數化SQL進行預編譯，這樣第一次執行的時候DBMS會為這個SQL語句進行查詢優化
並且執行預編譯，這樣以後再執行這個SQL的時候就直接使用預編譯的結果，這樣可以大大提高執行的速度。

四.調整Where字句中的連線順序

DBMS一般採用自下而上的順序解析where字句，根據這個原理表連線最好寫在其他where條件之前，那些可以
過濾掉最大數量記錄。

五.儘量將多條SQL語句壓縮到一句SQL中

每次執行SQL的時候都要建立網路連線、進行許可權校驗、進行SQL語句的查詢優化、傳送執行結果，這個過程
是非常耗時的，因此應該儘量避免過多的執行SQL語句，能夠壓縮到一句SQL執行的語句就不要用多條來執行。

六.用where字句替換HAVING字句

避免使用HAVING字句，因為HAVING只會在檢索出所有記錄之後才對結果集進行過濾，而where則是在聚合前
刷選記錄，如果能通過where字句限制記錄的數目，那就能減少這方面的開銷。HAVING中的條件一般用於聚合函式
的過濾，除此之外，應該將條件寫在where字句中。

七.使用表的別名

當在SQL語句中連線多個表時，請使用表的別名並把別名字首於每個列名上。這樣就可以減少解析的時間並減
少哪些友列名歧義引起的語法錯誤。

八.用union all替換union

當SQL語句需要union兩個查詢結果集合時，即使檢索結果中不會有重複的記錄，如果使用union這兩個結果集
同樣會嘗試進行合併，然後在輸出最終結果前進行排序，因此如果可以判斷檢索結果中不會有重複的記錄時候，應
該用union all，這樣效率就會因此得到提高。

九.考慮使用“臨時表”暫存中間結果

簡化SQL語句的重要方法就是採用臨時表暫存中間結果，但是，臨時表的好處遠遠不止這些，將臨時結果暫存在臨時表，後面的查詢就在tempdb中了，這可以避免程式中多次掃描主表，也大大減少了程式執行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了併發效能。
但是也得避免頻繁建立和刪除臨時表，以減少系統表資源的消耗。

十.只在必要的情況下才使用事務begin translation

SQL Server中一句SQL語句預設就是一個事務，在該語句執行完成後也是預設commit的。其實，這就是begin tran的一個最小化的形式，好比在每句語句開頭隱含了一個begin tran，結束時隱含了一個commit。
有些情況下，我們需要顯式宣告begin tran，比如做“插、刪、改”操作需要同時修改幾個表，要求要麼幾個表都修改成功，要麼都不成功。begin tran 可以起到這樣的作用，它可以把若干SQL語句套在一起執行，最後再一起commit。 好處是保證了資料的一致性，但任何事情都不是完美無缺的。Begin tran付出的代價是在提交之前，所有SQL語句鎖住的資源都不能釋放，直到commit掉。
可見，如果Begin tran套住的SQL語句太多，那資料庫的效能就糟糕了。在該大事務提交之前，必然會阻塞別的語句，造成block很多。
Begin tran使用的原則是，在保證資料一致性的前提下，begin tran 套住的SQL語句越少越好！有些情況下可以採用觸發器同步資料，不一定要用begin tran。

十一.儘量避免使用遊標

儘量避免向客戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。

十二.用varchar/nvarchar 代替 char/nchar

儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。
不要以為 NULL 不需要空間，比如：char(100) 型，在欄位建立時，空間就固定了， 不管是否插入值（NULL也包含在內），都是佔用 100個字元的空間的，如果是varchar這樣的變長欄位， null 不佔用空間。

十三.查詢select語句優化

1.任何地方都不要使用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位
2.應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，
如：

     select id from t where num is null
  • 1

可以在num上設定預設值0，確保表中num列沒有null值，
然後這樣查詢：

      select id from t where num=0
      select id from t where num=10 or num=20
  • 1
  • 2

可以這樣查詢：

      select id from t where num=10
       union all
      select id from t where num=20
  • 1
  • 2
  • 3

4.不能前置百分

select id from t where name like ‘%abc%’
  • 1

若要提高效率，可以考慮全文檢索。

     select id from t where num in(1,2,3)
  • 1

對於連續的數值，能用 between 就不要用 in 了：

    select id from t where num between 1 and 3
  • 1

6.如果查詢的兩個表大小相當，那麼用in和exists差別不大。
in：
例如：表A（小表），表B（大表）

 select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；     
 select * from A where exists(select cc from B where cc=A.cc)   效率高，用到了B表上cc列的索引。
  • 1
  • 2

相反的

 select * from B where cc in (select cc from A)  效率高，用到了B表上cc列的索引；
 select * from B where exists(select cc from A where cc=B.cc)  效率低，用到了A表上cc列的索引。
  • 1
  • 2

十四.更新Update語句優化

1.如果只更改1、2個欄位，不要Update全部欄位，否則頻繁呼叫會引起明顯的效能消耗，同時帶來大量日誌

十五. 刪除Delete語句優化語句

1.最高效的刪除重複記錄方法 ( 因為使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
  • 1

十六.插入Insert語句優化

1.在新建臨時表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。

相關文章