常見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 網路應用服務端 我們要使用服務端的數據 需要有一個客戶端 客戶端可以 ...
一周排行
  • 比如要拆分“呵呵呵90909086676喝喝999”,下麵當type=0返回的是中文字元串“呵呵呵,喝喝”,type=1返回的是數字字元串“90909086676,999”, private string GetStrings(string str,int type=0) { IList<strin ...
  • Swagger一個優秀的Api介面文檔生成工具。Swagger可以可以動態生成Api介面文檔,有效的降低前後端人員關於Api介面的溝通成本,促進項目高效開發。 1、使用NuGet安裝最新的包:Swashbuckle.AspNetCore。 2、編輯項目文件(NetCoreTemplate.Web.c ...
  • 2020 年 7 月 30 日, 由.NET基金會和微軟 將舉辦一個線上和為期一天的活動,包括 微軟 .NET 團隊的演講者以及社區的演講者。本次線上大會 專註.NET框架構建微服務,演講者分享構建和部署雲原生應用程式的最佳實踐、模式、提示和技巧。有關更多信息和隨時瞭解情況:https://focu... ...
  • #abp框架Excel導出——基於vue #1.技術棧 ##1.1 前端採用vue,官方提供 UI套件用的是iview ##1.2 後臺是abp——aspnetboilerplate 即abp v1,https://github.com/aspnetboilerplate/aspnetboilerp ...
  • 前言 本文的文字及圖片來源於網路,僅供學習、交流使用,不具有任何商業用途,版權歸原作者所有,如有問題請及時聯繫我們以作處理。 作者:碧茂大數據 PS:如有需要Python學習資料的小伙伴可以加下方的群去找免費管理員領取 input()輸入 Python提供了 input() 內置函數從標準輸入讀入一 ...
  • 從12年到20年,python以肉眼可見的趨勢超過了java,成為了當今It界人人皆知的編程語言。 python為什麼這麼火? 網路編程語言搜索指數 適合初學者 Python具有語法簡單、語句清晰的特點,這就讓初學者在學習階段可以把精力集中在編程對象和思維方法上。 大佬都在用 Google,YouT ...
  • 在社會上存在一種普遍的對培訓機構的學生一種歧視的現象,具體表現在,比如:當你去公司面試的時候,一旦你說了你是培訓機構出來的,那麼基本上你就涼了,那麼你瞞著不說,然後又通過了面試成功入職,但是以後一旦在公司被髮現有培訓經歷,可能會面臨被降薪,甚至被辭退,培訓機構出來的學生,在用人單位眼裡就是能力低下的 ...
  • from typing import List# 這道題看了大佬寫的代碼,經過自己的理解寫出來了。# 從最外圍的四周找有沒有為O的,如果有的話就進入深搜函數,然後深搜遍歷# 判斷上下左右的位置是否為Oclass Solution: def solve(self, board: List[List[s ...
  • import requests; import re; import os; # 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, li ...
  • import requests; import re; import os; import parsel; 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537. ...