常見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 網路應用服務端 我們要使用服務端的數據 需要有一個客戶端 客戶端可以 ...
一周排行
  • HttpReports 簡單介紹 HttpReports 是 .Net Core下的一個Web組件,適用於 WebAPI 項目和 API 網關項目,通過中間件的形式集成到您的項目中, 通過HttpReports,可以讓開發人員快速的搭建出一個 API 性能分析的基礎報表網站。 主要包含 HttpRe ...
  • 大家好,這幾天試著從Github上拉取AspNetCore的源碼,嘗試著通過Visual Studio 打開,但是並不盡人意。我們需要去構建我們拉去的源代碼,這樣才可以通過VisualStudio可還原的項目。畢竟AspNetCore是一個巨型的項目集。 先決條件 在Windows中構建AspNet ...
  • 知識需要不斷積累、總結和沉澱,思考和寫作是成長的催化劑 梯子 一、鎖1、lock2、Interlocked3、Monitor4、SpinLock5、Mutex6、Semaphore7、Events1、AutoResetEvent2、ManualResetEvent3、ManualResetEvent ...
  • 安裝Docker CentOS 7 安裝 Docker 編寫Dockerfile 右鍵項目-》添加-》Docker 支持 選擇Linux 修改為如下: FROM mcr.microsoft.com/dotnet/core/aspnet:2.2-stretch-slim AS base WORKDIR ...
  • 原文:https://blogs.msdn.microsoft.com/mazhou/2017/06/06/c-7-series-part-3-default-literals/ C#的default關鍵字有兩種用法:一種是標記switch…case結構的預設分支(會匹配任意不被所有case條件匹配 ...
  • 相關模塊 1. AbpAspNetCoreModule 2. AbpAspNetCoreMvcModule 3. AbpAspNetCoreMvcContractsModule abp通過這三個模塊載入並配置了 asp.net core。,最主要的就是AbpAspNetCoreMvcModule模塊 ...
  • 安裝 參考文檔:https://docs.docker.com/install/linux/docker-ce/centos/#install-using-the-repository 前提條件 Docker 要求 CentOS 系統的內核版本高於 3.10,在終端輸入以下命令: uname -r ...
  • 目前遇到的問題: 1.路徑區分大小寫及路徑用“/”,而不是常用的"\\"。 windows下路徑為:"xxxx\\yyyy",Linux路徑下為:"xxxx/yyyy" 使用 Path.Combine("xxxx","yyyy") 進行合併即可。 2.有時候就需要在 docker 容器里訪問宿主機提 ...
  • 《.Net 最佳實踐》 [作者] (美) Stephen Ritchie[譯者] (中) 黃燈橋 黃浩宇 李永[出版] 機械工業出版社[版次] 2014年01月 第1版[印次] 2018年01月 第1次 印刷[定價] 69.00元 (P001) 開發人員應該對任何稱之為“最佳實踐”的實踐保持一種懷疑 ...
  • 本文介紹了C#中的屬性,以及C#6和C#7中與屬性相關的新特性。 ...
x