資料庫優化方案之SQL腳本優化

• 2019 年 11 月 15 日
• 筆記

隨著資料庫數據越來越大，數據單表存在的數據量也就隨之上去了，那麼怎麼樣讓我們的腳本查詢數據更快呢？

在這個地方我們主要提到兩個資料庫類型：

1.MSSQL（該資料庫我們通過執行計劃來查看資料庫性能在哪個環節消耗最大）

直接在資料庫工具欄上面有一個叫做執行計劃，專業屬於稱為【顯示估計的執行計劃】

例如:

1.1.越粗表示掃描影響的行數愈多。

1.2.Actual Number of Rows 掃描中實際影響的的行數。

1.3.Estimated Number of Rows 預估掃描影響的行數。

1.4.Estimated row size 操作符生成的行的估計大小（位元組）。

1.5.Estimated Data Size 預估影響的數據的大小。

2.MYSQL（該資料庫也可以通過查看查詢執行計劃來看，不過據部落客所知MYSQL裡面是使用關鍵字【EXPLAIN 】來進行查看）

例如：

mysql執行計劃的生成方法和查看

2.1 執行計劃的生成方法:explain select ……………. 生成的方法很簡單在相應的select前面加explain即可

2.2 執行計劃的查看

mysql explain查看sql語句的執行計劃_MySQL

1)、id列數字越大越先執行,如果說數字一樣大,那麼就從上往下依次執行,id列為null的就表是這是一個結果集,不需要使用它來進行查詢。 2)、select_type列常見的有: A:simple:表示不需要union操作或者不包含子查詢的簡單select查詢。有連接查詢時,外層的查詢為simple,且只有一個 B:primary:一個需要union操作或者含有子查詢的select,位於最外層的單位查詢的select_type即為primary。且只有一個 C:union:union連接的兩個select查詢,第一個查詢是dervied派生表,除了第一個表外,第二個以後的表select_type都是union D:dependent union:與union一樣,出現在union 或union all語句中,但是這個查詢要受到外部查詢的影響 E:union result:包含union的結果集,在union和union all語句中,因為它不需要參與查詢,所以id欄位為null F:subquery:除了from字句中包含的子查詢外,其他地方出現的子查詢都可能是subquery G:dependent subquery:與dependent union類似,表示這個subquery的查詢要受到外部表查詢的影響 H:derived:from字句中出現的子查詢,也叫做派生表,其他資料庫中可能叫做內聯視圖或嵌套select 3)、table 顯示的查詢表名,如果查詢使用了別名,那麼這裡顯示的是別名,如果不涉及對數據表的操作,那麼這顯示為null,如果顯示為尖括弧括起來的 就表示這個是臨時表,後邊的N就是執行計劃中的id,表示結果來自於這個查詢產生。如果是尖括弧括起來的 ,與 類似,也是一個臨時表,表示這個結果來自於union查詢的id為M,N的結果集。 4)、type 依次從好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一個索引 A:system:表中只有一行數據或者是空表,且只能用於myisam和memory表。如果是Innodb引擎表,type列在這個情況通常都是all或者index B:const:使用唯一索引或者主鍵,返回記錄一定是1行記錄的等值where條件時,通常type是const。其他資料庫也叫做唯一索引掃描 C:eq_ref:出現在要連接過個表的查詢計劃中,驅動表只返回一行數據,且這行數據是第二個表的主鍵或者唯一索引,且必須為not null,唯一索引和主鍵是多列時,只有所有的列都用作比較時才會出現eq_ref D:ref:不像eq_ref那樣要求連接順序,也沒有主鍵和唯一索引的要求,只要使用相等條件檢索時就可能出現,常見與輔助索引的等值查找。或者多列主鍵、唯一索引中,使用第一個列之外的列作為等值查找也會出現,總之,返回數據不唯一的等值查找就可能出現。 E:fulltext:全文索引檢索,要注意,全文索引的優先順序很高,若全文索引和普通索引同時存在時,mysql不管代價,優先選擇使用全文索引 F:ref_or_null:與ref方法類似,只是增加了null值的比較。實際用的不多。 G:unique_subquery:用於where中的in形式子查詢,子查詢返回不重複值唯一值 H:index_subquery:用於in形式子查詢使用到了輔助索引或者in常數列表,子查詢可能返回重複值,可以使用索引將子查詢去重。 I:range:索引範圍掃描,常見於使用>,<,is null,between ,in ,like等運算符的查詢中。 J:index_merge:表示查詢使用了兩個以上的索引,最後取交集或者並集,常見and ,or的條件使用了不同的索引,官方排序這個在ref_or_null之後,但是實際上由於要讀取所個索引,性能可能大部分時間都不如range K:index:索引全表掃描,把索引從頭到尾掃一遍,常見於使用索引列就可以處理不需要讀取數據文件的查詢、可以使用索引排序或者分組的查詢。 L:all:這個就是全表掃描數據文件,然後再在server層進行過濾返回符合要求的記錄。 5)、possible_keys 查詢可能使用到的索引都會在這裡列出來 6)、key 查詢真正使用到的索引,select_type為index_merge時,這裡可能出現兩個以上的索引,其他的select_type這裡只會出現一個。 7)、key_len 用於處理查詢的索引長度,如果是單列索引,那就整個索引長度算進去,如果是多列索引,那麼查詢不一定都能使用到所有的列,具體使用到了多少個列的索引,這裡就會計算進去,沒有使用到的列,這裡不會計算進去。留意下這個列的值,算一下你的多列索引總長度就知道有沒有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不會計入其中。另外,key_len只計算where條件用到的索引長度,而排序和分組就算用到了索引,也不會計算到key_len中。 8)、ref 如果是使用的常數等值查詢,這裡會顯示const,如果是連接查詢,被驅動表的執行計劃這裡會顯示驅動表的關聯欄位,如果是條件使用了表達式或者函數,或者條件列發生了內部隱式轉換,這裡可能顯示為func 9)、rows 這裡是執行計劃中估算的掃描行數,不是精確值 10)、extra 這個列可以顯示的資訊非常多,有幾十種,常用的有 A:distinct:在select部分使用了distinc關鍵字 B:no tables used:不帶from字句的查詢或者From dual查詢 C:使用not in()形式子查詢或not exists運算符的連接查詢,這種叫做反連接。即,一般連接查詢是先查詢內表,再查詢外表,反連接就是先查詢外表,再查詢內表。 D:using filesort:排序時無法使用到索引時,就會出現這個。常見於order by和group by語句中 E:using index:查詢時不需要回表查詢,直接通過索引就可以獲取查詢的數據。 F:using join buffer(block nested loop),using join buffer(batched key accss):5.6.x之後的版本優化關聯查詢的BNL,BKA特性。主要是減少內表的循環數量以及比較順序地掃描查詢。 G:using sort_union,using_union,using intersect,using sort_intersection: using intersect:表示使用and的各個索引的條件時,該資訊表示是從處理結果獲取交集 using union:表示使用or連接各個使用索引的條件時,該資訊表示從處理結果獲取並集 using sort_union和using sort_intersection:與前面兩個對應的類似,只是他們是出現在用and和or查詢資訊量大時,先查詢主鍵,然後進行排序合併後,才能讀取記錄並返回。 H:using temporary:表示使用了臨時表存儲中間結果。臨時表可以是記憶體臨時表和磁碟臨時表,執行計劃中看不出來,需要查看status變數,used_tmp_table,used_tmp_disk_table才能看出來。 I:using where:表示存儲引擎返回的記錄並不是所有的都滿足查詢條件,需要在server層進行過濾。查詢條件中分為限制條件和檢查條件,5.6之前,存儲引擎只能根據限制條件掃描數據並返回,然後server層根據檢查條件進行過濾再返回真正符合查詢的數據。5.6.x之後支援ICP特性,可以把檢查條件也下推到存儲引擎層,不符合檢查條件和限制條件的數據,直接不讀取,這樣就大大減少了存儲引擎掃描的記錄數量。extra列顯示using index condition J:firstmatch(tb_name):5.6.x開始引入的優化子查詢的新特性之一,常見於where字句含有in()類型的子查詢。如果內表的數據量比較大,就可能出現這個 K:loosescan(m..n):5.6.x之後引入的優化子查詢的新特性之一,在in()類型的子查詢中,子查詢返回的可能有重複記錄時,就可能出現這個 除了這些之外,還有很多查詢數據字典庫,執行計划過程中就發現不可能存在結果的一些提示資訊 11)、filtered 使用explain extended時會出現這個列,5.7之後的版本默認就有這個欄位,不需要使用explain extended了。這個欄位表示存儲引擎返回的數據在server層過濾後,剩下多少滿足查詢的記錄數量的比例,注意是百分比,不是具體記錄數。

extra extra 列顯示了查詢過程中需要執行的其它操作，有些情況應儘力避免。

using filesort: 查詢時執行了排序操作而無法使用索引排序。雖然名稱為'file'但操作可能是在記憶體中執行的，取決是否有足夠的記憶體進行排序。 應盡量避免這種filesort出現。 using temporary: 使用臨時表存儲中間結果，常見於ORDER BY和GROUP BY語句中。臨時表可能在記憶體中也可能在硬碟中，應盡量避免這種操作出現。 using index: 索引中包含查詢的所有列(覆蓋索引)不需要查詢數據表。可以加快查詢速度。 using index condition: 索引條件推送(MySQL 5.6 新特性)，伺服器層將不能直接使用索引的查詢條件推送給存儲引擎，從而避免在伺服器層進行過濾。 using where: 伺服器層對存儲引擎返回的數據進行了過濾 distinct: 優化distinct操作，查詢到匹配的數據後停止繼續搜索

下面將會從以下點進行展開講解：

1.硬體

2.系統配置

3.數據表結構

4.SQL腳本及索引（本文重點講解）

網路圖片附上：

SQL腳本優化常識：

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

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

3.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num=0

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.下面的查詢也將導致全表掃描：

select id from t where name like '%abc%'

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

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

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 datediff(day,createdate,'2005-11-30')=0–'2005-11-30'生成的id

應改為:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

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.盡量避免大事務操作，提高系統並發能力。

SQL腳本優化常識轉載至部落客：https://www.cnblogs.com/Little-Li/p/8031295.html