提高mysql千萬級大數據SQL查詢優化30條經驗(Mysql索引優化註意)

来源:http://www.cnblogs.com/binbinyouni/archive/2016/11/16/6070715.html
-Advertisement-
Play Games

轉自http://blog.163.com/zhangjie_0303/blog/static/9908270620146951355834/ 1.對查詢進行優化,應儘量避免全表掃描,首先應考慮在 where 及 order by 涉及的列上建立索引。 2.應儘量避免在 where 子句中對欄位進行 ...


 轉自http://blog.163.com/zhangjie_0303/blog/static/9908270620146951355834/

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

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

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

5.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

6.下麵的查詢也將導致全表掃描:

select id from t where name like '李%'

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

7. 如果在 where 子句中使用參數,也會導致全表掃描。因為SQL只有在運行時才會解析局部變數,但優化程式不能將訪問計劃的選擇推遲到運行時;它必須在編譯時進行選擇。然 而,如果在編譯時建立訪問計劃,變數的值還是未知的,因而無法作為索引選擇的輸入項。如下麵語句將進行全表掃描:

select id from t where num=@num

可以改為強制查詢使用索引:

select id from t with(index(索引名)) where num=@num

8.應儘量避免在 where 子句中對欄位進行表達式操作,這將導致引擎放棄使用索引而進行全表掃描。如:

select id from t where num/2=100

應改為:

select id from t where num=100*2

9.應儘量避免在where子句中對欄位進行函數操作,這將導致引擎放棄使用索引而進行全表掃描。如:

select id from t where substring(name,1,3)='abc' 

name以abc開頭的id,應改為:

select id from t where name like 'abc%'

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

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

12.不要寫一些沒有意義的查詢,如需要生成一個空表結構:

select col1,col2 into #t from t where 1=0

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

create table #t(...)

13.很多時候用 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)

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

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

16. 應儘可能的避免更新 clustered 索引數據列,因為 clustered 索引數據列的順序就是表記錄的物理存儲順序,一旦該列值改變將導致整個表記錄的順序的調整,會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列,那麼需要考慮是否應將該索引建為 clustered 索引。

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

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

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

20.儘量使用表變數來代替臨時表。如果表變數包含大量數據,請註意索引非常有限(只有主鍵索引)。

21.避免頻繁創建和刪除臨時表,以減少系統表資源的消耗。

22.臨時表並不是不可使用,適當地使用它們可以使某些常式更有效,例如,當需要重覆引用大型表或常用表中的某個數據集時。但是,對於一次性事件,最好使用導出表。

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

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

25.儘量避免使用游標,因為游標的效率較差,如果游標操作的數據超過1萬行,那麼就應該考慮改寫。

26.使用基於游標的方法或臨時表方法之前,應先尋找基於集的解決方案來解決問題,基於集的方法通常更有效。

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

28.在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ,在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送DONE_IN_PROC 消息。

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

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


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

-Advertisement-
Play Games
更多相關文章
  • ParagraphString - 段落樣式的簡易處理 效果 源碼 https://github.com/YouXianMing/UI-Component-Collection 中的 ParagraphString ...
  • 作為一個MacBook的使用者,無不感受到蘋果對於系統和硬體的完美匹配. 蘋果電腦不適合玩游戲,所以我只用它開發iOS使用.電腦里除了Xcode和常用辦公軟體與通訊軟體以外,我沒有裝其他的任何大應用.更沒有存儲各種片... 但是使用一年以後,更新軟體的時候提示我存儲空間不足... 於是操作 左上角蘋 ...
  • 通常情況下,我們的程式需要從伺服器讀取圖片,但如果需要不止一次讀取某一張圖片的話,就需要做本地緩存了,這樣既為用戶省一點流量,又能顯得你的APP很快。 假如你已經知道了某一張圖片的地址,那麼第一件事就是要把這張圖片下載下來;當然如果是一次性讀取的話,可以直接把圖片地址給Image控制項或者給Bitma ...
  • 前面的話 數據類型是指列、存儲過程參數、表達式和局部變數的數據特征,它決定了數據的存儲格式,代表了不同的信息類型。mysql中的數據類型主要包括整型、浮點型、日期時間型和字元型。下麵來詳細介紹mysql中的數據類型 整型 浮點型 日期時間 字元型 ...
  • [1]登錄退出 [2]語句規範 [3]常用命令 [4]資料庫操作 ...
  • 一、在Linux下重啟Oracle資料庫及監聽器: 方法1: 用root以ssh登錄到linux,打開終端輸入以下命令: cd $ORACLE_HOME #進入到oracle的安裝目錄 dbstart #重啟伺服器 lsnrctl start #重啟監聽器 cd $ORACLE_HOME #進入到o ...
  • 目錄: 1、集群部署介紹 1.1 Hadoop簡介 1.2 環境說明 1.3 環境配置 1.4 所需軟體 2、SSH無密碼驗證配置 2.1 SSH基本原理和用法 2.2 配置Master無密碼登錄所有Salve 3、Java環境安裝 3.1 安裝JDK 3.2 配置環境變數 3.3 驗證安裝成功 3 ...
  • 關係: 如果說關係模型是一個表格模型,那麼關係就是一個表格,並且每個關係都有一個名字,稱為表名 屬性: 屬性這個概念是指表格中的每一列,並且這一列具有相同的數據類型,稱為欄位 元組: 元組指的是關係中的一行數據,稱為記錄 域: 域是指對屬性取值的限制範圍 關鍵字:猶如我們平時書的目錄,能唯一標識一個 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...