常見SQL編寫和優化

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

常見的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


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

-Advertisement-
Play Games
更多相關文章
  • 使用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 網路應用服務端 我們要使用服務端的數據 需要有一個客戶端 客戶端可以 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...