MSSQL查詢性能優化之查詢優化器的局限性與提示

發布于: 2019-01-24    瀏覽: 889    作者:Yang

MySQL的萬能嵌套循環并不是對每種查詢都是最優的。不過MySQL查詢優化器只對少部分查詢不適用,而且我們往往可以通過改寫查詢讓MySQL高效的完成工作。

 

1 關聯子查詢

MySQL的子查詢實現的非常糟糕。最糟糕的一類查詢時where條件中包含in()的子查詢語句。因為MySQLin()列表中的選項有專門的優化策略,一般會認為MySQL會先執行子查詢返回所有in()子句中查詢的值。一般來說,in()列表查詢速度很快,所以我們會以為sql會這樣執行

 

select * from tast_user where id in (select id from user where name like '%');

我們以為這個sql會解析成下面的形式

select * from tast_user where id in (1,2,3,4,5);

實際上MySQL是這樣解析的

select * from tast_user where exists

(select id from user where name like '%' and tast_user.id = user.id);

MySQL會將相關的外層表壓縮到子查詢中,它認為這樣可以更高效的查找到數據行。

 

這時候由于子查詢用到了外部表中的id字段所以子查詢無法先執行。通過explin可以看到,MySQL先選擇對tast_user表進行全表掃描,然后根據返回的id逐個執行子查詢。如果外層是一個很大的表,那么這個查詢的性能會非常糟糕。當然我們可以優化這個表的寫法:

 

select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '%'

另一個優化的辦法就是使用group_concat()in中構造一個由逗號分隔的列表。有時這比上面使用關聯改寫更快。因為使用in()加子查詢,性能通常會非常糟糕。所以通常建議使用exists()等效的改寫查詢來獲取更好的效率。

 

如何書寫更好的子查詢就不在介紹了,因為現在基本都要求拆分成單表查詢了,有興趣的話可以自行去了解下。

2 UNION的限制

有時,MySQL無法將限制條件從外層下推導內層,這使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上。

 

如果希望union的各個子句能夠根據limit只取部分結果集,或者希望能夠先排好序在合并結果集的話,就需要在union的各個子句中分別使用這些子句。例如,想將兩個子查詢結果聯合起來,然后在取前20條,那么MySQL會將兩個表都存放到一個臨時表中,然后在去除前20行。

 

(select first_name,last_name from actor order by last_name) union all

(select first_name,last_name from customer order by  last_name) limit 20;

這條查詢會將actor中的記錄和customer表中的記錄全部取出來放在一個臨時表中,然后在取前20條,可以通過在兩個子查詢中分別加上一個limit 20來減少臨時表中的數據。

 

現在中間的臨時表只會包含40條記錄了,處于性能考慮之外,這里還需要注意一點:從臨時表中取出數據的順序并不是一定,所以如果想獲得正確的順序,還需要在加上一個全局的order by操作

 

3 索引合并優化

前面文章中已經提到過,MySQL能夠訪問單個表的多個索引以合并和交叉過濾的方式來定位需要查找的行。

 

4 等值傳遞

某些時候,等值傳遞會帶來一些意想不到的額外消耗。例如,有一個非常大的in()列表,而MySQL優化器發現存在where/onusing的子句,將這個列表的值和另一個表的某個列相關聯。

 

那么優化器會將in()列表都賦值應用到關聯的各個表中。通常,因為各個表新增了過濾條件,優化器可以更高效的從存儲引擎過濾記錄。但是如果這個列表非常大,則會導致優化和執行都會變慢。

 

5 并行執行

MySQL無法利用多核特性來并行執行查詢。很多其他的關系型數據庫鞥能夠提供這個特性,但MySQL做不到。這里特別指出是想提醒大家不要花時間去嘗試尋找并行執行查詢的方法。

---------------------

6 哈希關聯

2013MySQL并不執行哈希關聯,MySQL的所有關聯都是嵌套循環關聯。不過可以通過建立一個哈希索引來曲線實現哈希關聯如果使用的是Memory引擎,則索引都是哈希索引,所以關聯的時候也類似于哈希關聯。另外MariaDB已經實現了哈希關聯。

 

7 松散索引掃描

由于歷史原因,MySQL并不支持松散索引掃描,也就無法按照不連續的方式掃描一個索引。通常,MySQL的索引掃描需要先定義一個起點和重點,即使需要的數據只是這段索引中很少的幾個,MySQL仍需要掃描這段索引中每個條目。

 

例:現有索引(a,b

 

select * from table where b between 2 and 3;

 

因為索引的前導字段是a,但是在查詢中只指定了字段bMySQL無法使用這個索引,從而只能通過全表掃描找到匹配的行。

 

MySQL全表掃描:

 

 

 

了解索引的物理結構的話,不難發現還可以有一個更快的辦法執行上面的查詢。索引的物理結構不是存儲引擎的API使得可以先掃描a列第一個值對應的b列的范圍,然后在跳到a列第二個不同值掃描對應的b列的范圍

 

 

 

這時就無需在使用where子句過濾,因為松散索引掃描已經跳過了所有不需要的記錄。

 

上面是一個簡單的例子,處理松散索引掃描,新增一個合適的索引當然也可以優化上述查詢。但對于某些場景,增加索引是沒用的,例如,對于第一個索引列是范圍條件,第二個索引列是等值提交建查詢,靠增加索引就無法解決問題。

 

MySQL5.6之后,關于松散索引掃描的一些限制將會通過索引條件嚇退的分行是解決。

 

8 最大值和最小值優化

對于MIN()MAX()查詢,MySQL的優化做的并不好,例:

 

select min(actor_id) from actor where first_name = 'wang'

因為在first_name字段上并沒有索引,因此MySQL將會進行一次全表掃描。如果MySQL能夠進行主鍵掃描,那么理論上,當MySQL讀到第一個太滿足條件的記錄的時候就是我們需要的最小值了,因為主鍵是嚴哥按照actor_id字段的大小排序的。但是MySSQL這時只會做全表掃描,我們可以通過show status的全表掃描計數器來驗證這一點。一個區縣優化辦法就是移除min()函數,然后使用limit 1來查詢。

 

這個策略可以讓MySQL掃描盡可能少的記錄數。這個例子告訴我們有時候為了獲得更高的性能,就得放棄一些原則。

 

9 在同一個表上查詢和更新

MySQL不允許對同一張表同時進行查詢和更新。這并不是優化器的限制,如果清楚MySQL是如何執行查詢的,就可以避免這種情況。例:

 

update table set cnt = (select count(*) from table as tb where tb.type = table.type);

這個sql雖然符合標準單無法執行,我們可以通過使用生成表的形式繞過上面的限制,因為MySQL只會把這個表當做一個臨時表來處理。

 

update table inner join

(select type,count(*) as cnt from table group by type) as tb using(type)

set table.cnt = tb.cnt;

實際上這執行了兩個查詢:一個是子查詢中的select語句,另一個是奪標關聯update,只是關聯的表時一個臨時表。子查詢會在update語句打開表之前就完成,所以會正常執行。

 

10 查詢優化器的提示(hint

如果對優化器選擇的執行計劃不滿意,可以使用優化器提供的幾個提示(hint)來控制最終的執行計劃。下面將列舉一些常見的提示,并簡單的給出什么時候使用該提示。通過在查詢中加入響應的提示,就可以控制該查詢的執行計劃。

 

HIGH_PRIORITY LOW_PRIORITY

 

這個提示告訴MySQL,當多個語句同時訪問某一表的時候,哪些語句的優先級相對高些,哪些語句優先級相對低些。

 

HIGH_PRIORITY用于select語句的時候,MySQL會將此select語句重新調度到所有正在表鎖以便修改數據的語句之前。實際上MySQL是將其放在表的隊列的最前面,而不是按照常規順序等待。HIGH_PRIORITY還可以用于insert語句,其效果只是簡單的體校了全局LOW_PRIORITY設置對該語句的影響。

 

LOW_PRIORITY則正好相反,它會讓語句一直處于等待狀態,只要在隊列中有對同一表的訪問,就會一直在隊尾等待。在CRUD語句中都可以使用。

 

這兩個提示只對使用表鎖的存儲引擎有效,不能在InnoDB或其他有細粒度所機制和并發控制的引擎中使用。在MyISAM中也要慎用,因為這兩個提示會導致并發插入被禁用,可能會嚴重降低性能。

 

HIGH_PRIORITYLOW_PRIORITY其實只是簡單的控制了MySQL訪問某個數據表的隊列順序。

 

DELAYED

 

這個提示對insertreplace有效。MySSQL會將使用該提示的語句立即返回給客戶端,并將插入的行數據放入緩沖區,然后在表空閑時批量將數據寫入。日志型系統使用這樣的提示非常有效,或者是其他需要寫入大量數據但是客戶端卻不需要等待單條語句完成I/O的應用。這個用法有一些限制。并不是所有的存儲引擎都支持,并且該提示會導致函數last_insert_id()無法正常工作。

 

STRAIGHT_JOIN

 

這個提示可以防止在select語句的select關鍵字之后,也可以防止在任何兩個關聯表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現的順序進行關聯。第二個用法則是固定其前后兩個表的關聯順序。

 

MySQL沒能選擇正確的關聯順序的時候,或者由于可能的順序太多導致MySQL無法評估所有的關聯順序的時候,STRAIGHT_JOIN都會很有用,在MySQL可能會發給大量時間在statistics狀態時,加上這個提示則會大大減少優化器的搜索空間

 

SQL_SMALLRESULTSQL_BIG_RESULT

 

這個兩個提示只對select語句有效。他們告訴優化器對group by或者distinct查詢如何使用臨時表及排序。SQL_SMALL_RESULT告訴優化器結果集會很小,可以將結果集放在內存中的索引臨時表,以避免排序操作。如果是SQL_BIG_RESULT,則會告訴優化器結果集可能會非常大,建議使用磁盤臨時表做排序操作。

 

SQL_BUFFER_RESULT

 

這個提示告訴優化器將查詢結果放入一個臨時表,然后盡可能快速釋放表鎖。這和前面提到的由客戶端緩存結果不同。當你無法使用客戶端緩存的時候,使用服務器端的緩存通常很有效。好處是無需在客戶端上消耗過多內存,還能盡快釋放表鎖。代價是服務器端將需要更多的內存。

 

SQL_CACHESQL_NO_CACHE

 

這個提示告訴MySQL這個結果集是否應該放入查詢緩存中。

 

SQL_CALC_FOUND_ROWS

 

嚴哥來說,這并不是一個優化器提示。它不會告訴優化器任何關于執行計劃的東西。它會讓MySQL返回的結果集包含更多的信息。查詢中加上該提示MySQL會計算limit子句之后這個查詢要返回的結果集總數,而實際上值返回limit要求的結果集。可以通過函數found_row()獲得這個值。慎用,后面會說明為什么。

 

FOR UPDATELOCK IN SHARE MODE

 

這也不是真正的優化器提示。這兩個提示主要控制select語句的鎖機制,但只對實現了行級鎖的存儲引擎有效。使用該提示會對符合查詢條件的數據行加鎖。對于insert/select語句是不需要這兩個提示的因為5.0以后會默認給這些記錄加上讀鎖。

 

唯一內置的支持這兩個提示的引擎就是InnoDB,可以禁用該默認行為。另外需要記住的是,這兩個提示會讓某些優化無法正常使用,例如索引覆蓋掃描。InnoDB不能在不訪問主鍵的情況下排他的鎖定行,因為行的版本信息保存在主鍵中。

 

如果這兩個提示被經常濫用,很容易早晨服務器的鎖爭用問題。

 

USE INDEXIGNORE INDEXFORCE INDEX

 

這幾個提示會告訴優化器使用或者不使用那些索引來查詢記錄。

 

5.0版本以后新增了一些參數來控制優化器的行為:

 

optimizer_search_depth

 

這個參數控制優化器在窮舉執行計劃時的限度。如果查詢長時間處于statistics狀態,那么可以考慮調低此參數。

 

optimizer_prune_level

 

該參數默認是打開的,這讓優化器會根據需要掃描的行數來決定是否跳過某些執行計劃。

 

optimizer_switch

 

這個變量包含了一些開啟/關閉優化器特性的標志位。

 

前面兩個參數時用來控制優化器可以走的一些捷徑。這些捷徑可以讓優化器在處理非常復雜的SQL語句時,可以更高效,但也可能讓優化器錯過一些真正最優的執行計劃,所以慎用。

 

修改優化器提示可能在MySQL更新后讓新版的優化策略失效,所以一定要謹慎。

---------------------

 

 文章內容來自網絡,僅用于學習交流,版權歸原作者所有,如有侵權請聯系管理員刪除。

在線客服

售前咨詢

售后服務

投訴/建議

服務熱線
0731-82880004
18874148081
乌克兰女人xxx动物视频-6a夜趣福利视-www伊人情人综合网com-久久蝌蚪视频在线播放视频下载