MySQL的萬(wàn)能嵌套循環(huán)并不是對(duì)每種查詢都是最優(yōu)的。不過(guò)MySQL查詢優(yōu)化器只對(duì)少部分查詢不適用，而且我們往往可以通過(guò)改寫(xiě)查詢讓MySQL高效的完成工作。

1 關(guān)聯(lián)子查詢

MySQL的子查詢實(shí)現(xiàn)的非常糟糕。最糟糕的一類(lèi)查詢時(shí)where條件中包含in()的子查詢語(yǔ)句。因?yàn)?/span>MySQL對(duì)in()列表中的選項(xiàng)有專(zhuān)門(mén)的優(yōu)化策略，一般會(huì)認(rèn)為MySQL會(huì)先執(zhí)行子查詢返回所有in()子句中查詢的值。一般來(lái)說(shuō)，in()列表查詢速度很快，所以我們會(huì)以為sql會(huì)這樣執(zhí)行

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

我們以為這個(gè)sql會(huì)解析成下面的形式

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

實(shí)際上MySQL是這樣解析的

select * from tast_user where exists

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

MySQL會(huì)將相關(guān)的外層表壓縮到子查詢中，它認(rèn)為這樣可以更高效的查找到數(shù)據(jù)行。

這時(shí)候由于子查詢用到了外部表中的id字段所以子查詢無(wú)法先執(zhí)行。通過(guò)explin可以看到，MySQL先選擇對(duì)tast_user表進(jìn)行全表掃描，然后根據(jù)返回的id逐個(gè)執(zhí)行子查詢。如果外層是一個(gè)很大的表，那么這個(gè)查詢的性能會(huì)非常糟糕。當(dāng)然我們可以優(yōu)化這個(gè)表的寫(xiě)法：

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

另一個(gè)優(yōu)化的辦法就是使用group_concat()在in中構(gòu)造一個(gè)由逗號(hào)分隔的列表。有時(shí)這比上面使用關(guān)聯(lián)改寫(xiě)更快。因?yàn)槭褂?/span>in()加子查詢，性能通常會(huì)非常糟糕。所以通常建議使用exists()等效的改寫(xiě)查詢來(lái)獲取更好的效率。

如何書(shū)寫(xiě)更好的子查詢就不在介紹了，因?yàn)楝F(xiàn)在基本都要求拆分成單表查詢了，有興趣的話可以自行去了解下。

2 UNION的限制

有時(shí)，MySQL無(wú)法將限制條件從外層下推導(dǎo)內(nèi)層，這使得原本能夠限制部分返回結(jié)果的條件無(wú)法應(yīng)用到內(nèi)層查詢的優(yōu)化上。

如果希望union的各個(gè)子句能夠根據(jù)limit只取部分結(jié)果集，或者希望能夠先排好序在合并結(jié)果集的話，就需要在union的各個(gè)子句中分別使用這些子句。例如，想將兩個(gè)子查詢結(jié)果聯(lián)合起來(lái)，然后在取前20條，那么MySQL會(huì)將兩個(gè)表都存放到一個(gè)臨時(shí)表中，然后在去除前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;

這條查詢會(huì)將actor中的記錄和customer表中的記錄全部取出來(lái)放在一個(gè)臨時(shí)表中，然后在取前20條，可以通過(guò)在兩個(gè)子查詢中分別加上一個(gè)limit 20來(lái)減少臨時(shí)表中的數(shù)據(jù)。

現(xiàn)在中間的臨時(shí)表只會(huì)包含40條記錄了，處于性能考慮之外，這里還需要注意一點(diǎn)：從臨時(shí)表中取出數(shù)據(jù)的順序并不是一定，所以如果想獲得正確的順序，還需要在加上一個(gè)全局的order by操作

3 索引合并優(yōu)化

前面文章中已經(jīng)提到過(guò)，MySQL能夠訪問(wèn)單個(gè)表的多個(gè)索引以合并和交叉過(guò)濾的方式來(lái)定位需要查找的行。

4 等值傳遞

某些時(shí)候，等值傳遞會(huì)帶來(lái)一些意想不到的額外消耗。例如，有一個(gè)非常大的in()列表，而MySQL優(yōu)化器發(fā)現(xiàn)存在where/on或using的子句，將這個(gè)列表的值和另一個(gè)表的某個(gè)列相關(guān)聯(lián)。

那么優(yōu)化器會(huì)將in()列表都賦值應(yīng)用到關(guān)聯(lián)的各個(gè)表中。通常，因?yàn)楦鱾€(gè)表新增了過(guò)濾條件，優(yōu)化器可以更高效的從存儲(chǔ)引擎過(guò)濾記錄。但是如果這個(gè)列表非常大，則會(huì)導(dǎo)致優(yōu)化和執(zhí)行都會(huì)變慢。

5 并行執(zhí)行

MySQL無(wú)法利用多核特性來(lái)并行執(zhí)行查詢。很多其他的關(guān)系型數(shù)據(jù)庫(kù)鞥能夠提供這個(gè)特性，但MySQL做不到。這里特別指出是想提醒大家不要花時(shí)間去嘗試尋找并行執(zhí)行查詢的方法。

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

6 哈希關(guān)聯(lián)

在2013年MySQL并不執(zhí)行哈希關(guān)聯(lián)，MySQL的所有關(guān)聯(lián)都是嵌套循環(huán)關(guān)聯(lián)。不過(guò)可以通過(guò)建立一個(gè)哈希索引來(lái)曲線實(shí)現(xiàn)哈希關(guān)聯(lián)如果使用的是Memory引擎，則索引都是哈希索引，所以關(guān)聯(lián)的時(shí)候也類(lèi)似于哈希關(guān)聯(lián)。另外MariaDB已經(jīng)實(shí)現(xiàn)了哈希關(guān)聯(lián)。

7 松散索引掃描

由于歷史原因，MySQL并不支持松散索引掃描，也就無(wú)法按照不連續(xù)的方式掃描一個(gè)索引。通常，MySQL的索引掃描需要先定義一個(gè)起點(diǎn)和重點(diǎn)，即使需要的數(shù)據(jù)只是這段索引中很少的幾個(gè)，MySQL仍需要掃描這段索引中每個(gè)條目。

例：現(xiàn)有索引（a,b）

select * from table where b between 2 and 3;

因?yàn)樗饕那皩?dǎo)字段是a，但是在查詢中只指定了字段b，MySQL無(wú)法使用這個(gè)索引，從而只能通過(guò)全表掃描找到匹配的行。

MySQL全表掃描：

了解索引的物理結(jié)構(gòu)的話，不難發(fā)現(xiàn)還可以有一個(gè)更快的辦法執(zhí)行上面的查詢。索引的物理結(jié)構(gòu)不是存儲(chǔ)引擎的API使得可以先掃描a列第一個(gè)值對(duì)應(yīng)的b列的范圍，然后在跳到a列第二個(gè)不同值掃描對(duì)應(yīng)的b列的范圍

這時(shí)就無(wú)需在使用where子句過(guò)濾，因?yàn)樗缮⑺饕龗呙枰呀?jīng)跳過(guò)了所有不需要的記錄。

上面是一個(gè)簡(jiǎn)單的例子，處理松散索引掃描，新增一個(gè)合適的索引當(dāng)然也可以優(yōu)化上述查詢。但對(duì)于某些場(chǎng)景，增加索引是沒(méi)用的，例如，對(duì)于第一個(gè)索引列是范圍條件，第二個(gè)索引列是等值提交建查詢，靠增加索引就無(wú)法解決問(wèn)題。

MySQL5.6之后，關(guān)于松散索引掃描的一些限制將會(huì)通過(guò)索引條件嚇退的分行是解決。

8 最大值和最小值優(yōu)化

對(duì)于MIN()和MAX()查詢，MySQL的優(yōu)化做的并不好，例：

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

因?yàn)樵?/span>first_name字段上并沒(méi)有索引，因此MySQL將會(huì)進(jìn)行一次全表掃描。如果MySQL能夠進(jìn)行主鍵掃描，那么理論上，當(dāng)MySQL讀到第一個(gè)太滿足條件的記錄的時(shí)候就是我們需要的最小值了，因?yàn)橹麈I是嚴(yán)哥按照actor_id字段的大小排序的。但是MySSQL這時(shí)只會(huì)做全表掃描，我們可以通過(guò)show status的全表掃描計(jì)數(shù)器來(lái)驗(yàn)證這一點(diǎn)。一個(gè)區(qū)縣優(yōu)化辦法就是移除min()函數(shù)，然后使用limit 1來(lái)查詢。

這個(gè)策略可以讓MySQL掃描盡可能少的記錄數(shù)。這個(gè)例子告訴我們有時(shí)候?yàn)榱双@得更高的性能，就得放棄一些原則。

9 在同一個(gè)表上查詢和更新

MySQL不允許對(duì)同一張表同時(shí)進(jìn)行查詢和更新。這并不是優(yōu)化器的限制，如果清楚MySQL是如何執(zhí)行查詢的，就可以避免這種情況。例：

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

這個(gè)sql雖然符合標(biāo)準(zhǔn)單無(wú)法執(zhí)行，我們可以通過(guò)使用生成表的形式繞過(guò)上面的限制，因?yàn)?/span>MySQL只會(huì)把這個(gè)表當(dāng)做一個(gè)臨時(shí)表來(lái)處理。

update table inner join

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

set table.cnt = tb.cnt;

實(shí)際上這執(zhí)行了兩個(gè)查詢：一個(gè)是子查詢中的select語(yǔ)句，另一個(gè)是奪標(biāo)關(guān)聯(lián)update，只是關(guān)聯(lián)的表時(shí)一個(gè)臨時(shí)表。子查詢會(huì)在update語(yǔ)句打開(kāi)表之前就完成，所以會(huì)正常執(zhí)行。

10 查詢優(yōu)化器的提示（hint）

如果對(duì)優(yōu)化器選擇的執(zhí)行計(jì)劃不滿意，可以使用優(yōu)化器提供的幾個(gè)提示（hint）來(lái)控制最終的執(zhí)行計(jì)劃。下面將列舉一些常見(jiàn)的提示，并簡(jiǎn)單的給出什么時(shí)候使用該提示。通過(guò)在查詢中加入響應(yīng)的提示，就可以控制該查詢的執(zhí)行計(jì)劃。

① HIGH_PRIORITY 和 LOW_PRIORITY

這個(gè)提示告訴MySQL，當(dāng)多個(gè)語(yǔ)句同時(shí)訪問(wèn)某一表的時(shí)候，哪些語(yǔ)句的優(yōu)先級(jí)相對(duì)高些，哪些語(yǔ)句優(yōu)先級(jí)相對(duì)低些。

HIGH_PRIORITY用于select語(yǔ)句的時(shí)候，MySQL會(huì)將此select語(yǔ)句重新調(diào)度到所有正在表鎖以便修改數(shù)據(jù)的語(yǔ)句之前。實(shí)際上MySQL是將其放在表的隊(duì)列的最前面，而不是按照常規(guī)順序等待。HIGH_PRIORITY還可以用于insert語(yǔ)句，其效果只是簡(jiǎn)單的體校了全局LOW_PRIORITY設(shè)置對(duì)該語(yǔ)句的影響。

LOW_PRIORITY則正好相反，它會(huì)讓語(yǔ)句一直處于等待狀態(tài)，只要在隊(duì)列中有對(duì)同一表的訪問(wèn)，就會(huì)一直在隊(duì)尾等待。在CRUD語(yǔ)句中都可以使用。

這兩個(gè)提示只對(duì)使用表鎖的存儲(chǔ)引擎有效，不能在InnoDB或其他有細(xì)粒度所機(jī)制和并發(fā)控制的引擎中使用。在MyISAM中也要慎用，因?yàn)檫@兩個(gè)提示會(huì)導(dǎo)致并發(fā)插入被禁用，可能會(huì)嚴(yán)重降低性能。

HIGH_PRIORITY和LOW_PRIORITY其實(shí)只是簡(jiǎn)單的控制了MySQL訪問(wèn)某個(gè)數(shù)據(jù)表的隊(duì)列順序。

② DELAYED

這個(gè)提示對(duì)insert和replace有效。MySSQL會(huì)將使用該提示的語(yǔ)句立即返回給客戶端，并將插入的行數(shù)據(jù)放入緩沖區(qū)，然后在表空閑時(shí)批量將數(shù)據(jù)寫(xiě)入。日志型系統(tǒng)使用這樣的提示非常有效，或者是其他需要寫(xiě)入大量數(shù)據(jù)但是客戶端卻不需要等待單條語(yǔ)句完成I/O的應(yīng)用。這個(gè)用法有一些限制。并不是所有的存儲(chǔ)引擎都支持，并且該提示會(huì)導(dǎo)致函數(shù)last_insert_id()無(wú)法正常工作。

③ STRAIGHT_JOIN

這個(gè)提示可以防止在select語(yǔ)句的select關(guān)鍵字之后，也可以防止在任何兩個(gè)關(guān)聯(lián)表的名字之間。第一個(gè)用法是讓查詢中所有的表按照在語(yǔ)句中出現(xiàn)的順序進(jìn)行關(guān)聯(lián)。第二個(gè)用法則是固定其前后兩個(gè)表的關(guān)聯(lián)順序。

當(dāng)MySQL沒(méi)能選擇正確的關(guān)聯(lián)順序的時(shí)候，或者由于可能的順序太多導(dǎo)致MySQL無(wú)法評(píng)估所有的關(guān)聯(lián)順序的時(shí)候，STRAIGHT_JOIN都會(huì)很有用，在MySQL可能會(huì)發(fā)給大量時(shí)間在statistics狀態(tài)時(shí)，加上這個(gè)提示則會(huì)大大減少優(yōu)化器的搜索空間

④ SQL_SMALLRESULT和SQL_BIG_RESULT

這個(gè)兩個(gè)提示只對(duì)select語(yǔ)句有效。他們告訴優(yōu)化器對(duì)group by或者distinct查詢?nèi)绾问褂门R時(shí)表及排序。SQL_SMALL_RESULT告訴優(yōu)化器結(jié)果集會(huì)很小，可以將結(jié)果集放在內(nèi)存中的索引臨時(shí)表，以避免排序操作。如果是SQL_BIG_RESULT，則會(huì)告訴優(yōu)化器結(jié)果集可能會(huì)非常大，建議使用磁盤(pán)臨時(shí)表做排序操作。

⑤ SQL_BUFFER_RESULT

這個(gè)提示告訴優(yōu)化器將查詢結(jié)果放入一個(gè)臨時(shí)表，然后盡可能快速釋放表鎖。這和前面提到的由客戶端緩存結(jié)果不同。當(dāng)你無(wú)法使用客戶端緩存的時(shí)候，使用服務(wù)器端的緩存通常很有效。好處是無(wú)需在客戶端上消耗過(guò)多內(nèi)存，還能盡快釋放表鎖。代價(jià)是服務(wù)器端將需要更多的內(nèi)存。

⑥ SQL_CACHE和SQL_NO_CACHE

這個(gè)提示告訴MySQL這個(gè)結(jié)果集是否應(yīng)該放入查詢緩存中。

⑦ SQL_CALC_FOUND_ROWS

嚴(yán)哥來(lái)說(shuō)，這并不是一個(gè)優(yōu)化器提示。它不會(huì)告訴優(yōu)化器任何關(guān)于執(zhí)行計(jì)劃的東西。它會(huì)讓MySQL返回的結(jié)果集包含更多的信息。查詢中加上該提示MySQL會(huì)計(jì)算limit子句之后這個(gè)查詢要返回的結(jié)果集總數(shù)，而實(shí)際上值返回limit要求的結(jié)果集?？梢酝ㄟ^(guò)函數(shù)found_row()獲得這個(gè)值。慎用，后面會(huì)說(shuō)明為什么。

⑧ FOR UPDATE和LOCK IN SHARE MODE

這也不是真正的優(yōu)化器提示。這兩個(gè)提示主要控制select語(yǔ)句的鎖機(jī)制，但只對(duì)實(shí)現(xiàn)了行級(jí)鎖的存儲(chǔ)引擎有效。使用該提示會(huì)對(duì)符合查詢條件的數(shù)據(jù)行加鎖。對(duì)于insert/select語(yǔ)句是不需要這兩個(gè)提示的因?yàn)?/span>5.0以后會(huì)默認(rèn)給這些記錄加上讀鎖。

唯一內(nèi)置的支持這兩個(gè)提示的引擎就是InnoDB，可以禁用該默認(rèn)行為。另外需要記住的是，這兩個(gè)提示會(huì)讓某些優(yōu)化無(wú)法正常使用，例如索引覆蓋掃描。InnoDB不能在不訪問(wèn)主鍵的情況下排他的鎖定行，因?yàn)樾械陌姹拘畔⒈４嬖谥麈I中。

如果這兩個(gè)提示被經(jīng)常濫用，很容易早晨服務(wù)器的鎖爭(zhēng)用問(wèn)題。

⑨ USE INDEX、IGNORE INDEX和FORCE INDEX

這幾個(gè)提示會(huì)告訴優(yōu)化器使用或者不使用那些索引來(lái)查詢記錄。

在5.0版本以后新增了一些參數(shù)來(lái)控制優(yōu)化器的行為：

① optimizer_search_depth

這個(gè)參數(shù)控制優(yōu)化器在窮舉執(zhí)行計(jì)劃時(shí)的限度。如果查詢長(zhǎng)時(shí)間處于statistics狀態(tài)，那么可以考慮調(diào)低此參數(shù)。

② optimizer_prune_level

該參數(shù)默認(rèn)是打開(kāi)的，這讓優(yōu)化器會(huì)根據(jù)需要掃描的行數(shù)來(lái)決定是否跳過(guò)某些執(zhí)行計(jì)劃。

③optimizer_switch

這個(gè)變量包含了一些開(kāi)啟/關(guān)閉優(yōu)化器特性的標(biāo)志位。

前面兩個(gè)參數(shù)時(shí)用來(lái)控制優(yōu)化器可以走的一些捷徑。這些捷徑可以讓優(yōu)化器在處理非常復(fù)雜的SQL語(yǔ)句時(shí)，可以更高效，但也可能讓優(yōu)化器錯(cuò)過(guò)一些真正最優(yōu)的執(zhí)行計(jì)劃，所以慎用。

修改優(yōu)化器提示可能在MySQL更新后讓新版的優(yōu)化策略失效，所以一定要謹(jǐn)慎。

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

 文章內(nèi)容來(lái)自網(wǎng)絡(luò)，僅用于學(xué)習(xí)交流，版權(quán)歸原作者所有，如有侵權(quán)請(qǐng)聯(lián)系管理員刪除。