常見SQL編寫和優化

来源:https://www.cnblogs.com/AganRun/archive/2019/12/03/11974566.html

常見的SQL優化方式 1. 對查詢進行優化,應儘量避免全表掃描,首先應考慮在 where及order by 涉及的列上建立索引 。 2. 應儘量 避免 在 where 子句中對欄位進行null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如: 可以在num上設置預設值0,確保表中num列是否存 ...


常見的SQL優化方式

  1. 對查詢進行優化,應儘量避免全表掃描,首先應考慮在where及order by 涉及的列上建立索引

  2. 應儘量避免在 where 子句中對欄位進行null 值判斷,否則將導致引擎放棄使用索引而進行全表掃描,如:
select id from t where num is null  

可以在num上設置預設值0,確保表中num列是否存在null值,然後這樣查詢:

select id from t where num = 0  
  1. 應儘量避免在 where 子句中使用 !=或<> 操作符,否則將引擎放棄使用索引而進行全表掃描。

  2. 應儘量避免在 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   

對於union, 優先使用union all, 避免使用union。

UNION 因為會將各查詢子集的記錄做比較,故比起UNION ALL ,通常速度都會慢上許多。

一般來說,如果使用UNION ALL能滿足要求的話,務必使用UNION ALL。

  1. in 和 not in 慎用,否則會導致全表掃描,如:
select id from t where num in (1,2,3)   

對於連續的數值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3  
  1. 慎用模糊查詢,使用 like 兩邊加“%”--造成索引失效。
    左邊沒有%,這個索引不會失效。下麵的查詢將導致全表掃描:
select id from t where name like '%abc%'    
  1. 應儘量避免在 where 子句中對欄位進行表達式操作,這將導致引擎放棄使用索引而進行全表掃描。如:
select id from t where num / 2 = 100    

應改為:

select id from t where num = 100 * 2    
  1. 應儘量避免在where子句中對欄位進行函數操作,這將導致引擎放棄使用索引而進行全表掃描。
    如:
select id from t where substring(name,1,3)='abc'--name以abc開頭的id 

應改為(like 統計第6點):

select id from t where name like 'abc%' 
  1. 不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算,否則系統將可能無法正確使用索引。

  2. 在使用索引欄位作為條件時,如果該索引是複合索引,那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引,
    否則該索引將不會被使用,並且應儘可能的讓欄位順序與索引順序相一致。

  3. 不要寫一些沒有意義的查詢,如需要生成一個空表結構:
select col1,col2 into #t from t where 1=0   

這類代碼不會返回任何結果集,但是會消耗系統資源的,應改成這樣:

create table #t(...)    
  1. in和exist。in 是把外表和內表作hash 連接,而exists是對外表作loop迴圈,每次loop迴圈再對內表進行查詢。因此,in用到的是外表的索引, exists用到的是內表的索引。

如果查詢的兩個表大小相當,那麼用in和exists差別不大,

如果兩個表中一個較小,一個是大表,則子查詢表大的用exists,子查詢表小的用in

select num from a where num in(select num from b)   

用下麵的語句替換:

select num from a where exists(select 1 from b where num=a.num) 
  1. 並不是所有索引對查詢都有效,SQL是根據表中數據來進行查詢優化的,當索引列有大量數據重覆時,SQL查詢可能不會去利用索引,
    如一表中有欄位sex,male、female幾乎各一半,那麼即使在sex上建了索引也對查詢效率起不了作用。

  2. 索引並不是越多越好,索引固然可以提高相應的 select 的效率,但同時也降低了 insert 及 update 的效率,
    因為 insert 或 update 時有可能會重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。
    一個表的索引數最好不要超過6個,若太多則應考慮一些不常使用到的列上建的索引是否有必要。

  3. 儘量使用數字型欄位,若只含數值信息的欄位儘量不要設計為字元型,這會降低查詢和連接的性能,並會增加存儲開銷。
    這是因為引擎在處理查詢和連接時會逐個比較字元串中每一個字元,而對於數字型而言只需要比較一次就夠了。

  4. 儘可能的使用 varchar 代替 char ,因為首先變長欄位存儲空間小,可以節省存儲空間,
    其次對於查詢來說,在一個相對較小的欄位內搜索效率顯然要高些。

  5. 任何地方都不要使用 select * from t ,用具體的欄位列表代替“*”,不要返回用不到的任何欄位。

  6. 避免頻繁創建和刪除臨時表,以減少系統表資源的消耗。
    臨時表並不是不可使用,適當地使用它們可以使某些常式更有效,例如,當需要重覆引用大型表或常用表中的某個數據集時。但是,對於一次性事件,最好使用導出表。

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

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

  9. 儘量避免使用游標,因為游標的效率較差,如果游標操作的數據超過1萬行,那麼就應該考慮改寫。
    使用基於游標的方法或臨時表方法之前,應先尋找基於集的解決方案來解決問題,基於集的方法通常更有效。

  10. 與臨時表一樣,游標並不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法,尤其是在必須引用幾個表才能獲得所需的數據時。
    在結果集中包括“合計”的常式通常要比使用游標執行的速度快。如果開發時間允許,基於游標的方法和基於集的方法都可以嘗試一下,看哪一種方法的效果更好。

  11. 儘量避免大事務操作,提高系統併發能力。

  12. 儘量避免向客戶端返回大數據量,若數據量過大,應該考慮相應需求是否合理

參考:https://blog.csdn.net/jie_liang/article/details/77340905
https://blog.csdn.net/weixin_40792878/article/details/81071584


您的分享是我們最大的動力!

更多相關文章
  • 使用oracle客戶端連接資料庫,從oracle官網下載客戶端instantclient_18_3工具,到目錄下打開cmd命令視窗: 個人網盤客戶端工具:https://pan.baidu.com/s/1lM4Faat7Mq0gr7rzmfjtSQ 1.輸入sqlplus; 2.輸入連接信息:use ...
  • 需求 SQL Server的作業歷史(Job)記錄是保存在msdb庫中的,很方便就查詢相關的Job定義,計劃和歷史記錄,而MySQL的event卻沒有歷史記錄。為方便查看event是否正常執行以及執行結果,通過以下兩個步驟來實現類似的功能。 實現 1. 在mysql庫創建event執行的歷史記錄表 ...
  • 問題描述:情形是當主庫真正出現異常之後,才會執行的操作,那麼我們執行過failover 之後,如何在重新構建DG,這裡我們利用flashback database來重構。模擬前主庫要開啟閃回區,否則要重新搭建DG了 1.由於主庫已經不可訪問,下麵所有的操作都在備庫完成,先取消實時同步 SQL> al ...
  • 有時會發現資料庫system表空間增長很快,使用以下語句查看system表空間使用量。也可以使用toad直接看。 執行以下語句查看是哪個對象占用較大 一般發現都是發現是AUD$審計表占用資源量大。 直接登錄資料庫,清理掉SYS.AUD$表。 如果想關閉資料庫審計,可以參考以下鏈接 https://w ...
  • spark 各個版本的application 調度演算法還是有這明顯的不同之處的。從spark1.3.0 到 spark 1.6.1、spark2.x 到 現在最新的spark 3.x ,調度演算法有了一定的修改。下麵大家一起學習一下,最新的spark 版本spark-3.0的Application 調 ...
  • 說明: 1)該實驗所有過程均是本人親自敲命令完成,所有代碼運行正確 2)安裝過程使用的是suse11 sp3操作系統,後續的實驗過程換成了麒麟中標,因此部分路徑可能存在差異 3)安裝過程使用了命令行安裝,圖形界面簡單,因此本文沒有介紹 4)job部分命令行操作太繁瑣,建議使用圖形界面操作,因此本文也 ...
  • sqlServer2012(936 簡體中文GBK )為例: 例如: varchar(10),只能存儲10個英文字元或數字,也只能存儲5個漢字; char(10),只能存儲10個英文字元或數字,也只能存儲5個漢字; nvarchar(10),即存儲10個英文字元或數字,也能存儲10個漢字; ncha ...
  • 知識點 △用資料庫的原因 1文件操作的複雜度 2同步 3併發處理 4安全 △資料庫系統(DBS) 資料庫(DB) + 資料庫管理系統 (DBS)+ 資料庫應用程式 + 資料庫管理員 (BDA)+ 最終用戶 △資料庫管理系統 DBM 網路應用服務端 我們要使用服務端的數據 需要有一個客戶端 客戶端可以 ...
一周排行
  • FastDBF源代碼地址:https://github.com/SocialExplorer/FastDBF 第一步在解決方案中新建一個類庫的項目:取名為SocialExplorer.FastDBF 第二步:引入FASTDBF的源文件 源代碼可以通過github地址下載引入 源文件:DbfColum ...
  • 目 錄 1. 概述... 2 2. 演示信息... 2 3. 安裝Docker容器... 2 4. 安裝dotnet鏡像... 3 5. 複製iNeuKernel到容器中... 4 6. 進入指定容器... 4 7. 安裝dotnet框架... 4 8. 在Docker容器中運行iNeuKernel ...
  • 本筆記摘抄自:https://www.cnblogs.com/PatrickLiu/p/7699301.html,記錄一下學習過程以備後續查用。 一、引言 今天我們要講結構型設計模式的第二個模式--橋接模式,也有叫橋模式的。橋在我們現實生活中經常是連接著A地和B地,再往後來發展,橋引申為一種紐 帶, ...
  • static void AggregateExceptionsDemo() { var task1 = Task.Factory.StartNew(() => { var child1 = Task.Factory.StartNew(() => { throw new CustomException ...
  • http請求在我們實際工作中天天見,為了不重覆造輪子,現在分享一下最近的一次封裝整理,供大家參考,交流,學習! ...
  • 隨著你的 Python 項目越來越多,你會發現不同的項目會需要 不同的版本的 Python 庫。同一個 Python 庫的不同版本可能不相容。虛擬環境可以為每一個項目安裝獨立的 Python 庫,這樣就可以隔離不同項目之間的 Python 庫,也可以隔離項目與操作系統之間的 Python 庫。 1. ...
  • TypeScript 介紹 TypeScript 是什麼 TypeScript 是 JavaScript 的強類型版本。然後在編譯期去掉類型和特有語法,生成純粹的 JavaScript 代碼。由於最終在瀏覽器中運行的仍然是 JavaScript,所以 TypeScript 並不依賴於瀏覽器的支持,也 ...
  • Hello World 新建 並寫入以下內容: 安裝編譯器: 編譯: 修改 文件中的代碼,為 greeter 函數的參數 person 加上類型聲明 : 重新編譯執行。 讓我們繼續修改: 重新編譯,你將看到如下錯誤: 介面(Interface) 類(Class) 變數聲明 作用域 重覆聲明 塊級作用 ...
  • 解構賦值 數組解構 上面的寫法等價於: 利用解構賦值交換變數: 函數參數解構: 解構剩餘參數: 也可以忽略其它參數: 或者跳過解構: 對象解構 示例一: 就像數組解構,你可以用沒有聲明的賦值: 你可以在對象里使用 語法創建剩餘變數: 屬性解構重命名 你也可以給屬性以不同的名字: 註意,這裡的冒號 不 ...
  • 函數 函數參數 參數及返回值類型 可選參數 預設參數 剩餘參數 箭頭函數 基本示例 for of 迴圈 for 迴圈 forEach 不支持 break for in 會把數組當作對象來遍歷 for of 支持 break 類型推斷(Type Inference) 類型相容性 模塊 概念 模塊通信: ...
x