如何優(yōu)化MySQL查詢

這篇文章給大家介紹如何優(yōu)化MySQL查詢，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

解析器和預(yù)處理器

一開始，MySQL 的解析器將查詢語(yǔ)句拆分成一系列指令并從中構(gòu)建一棵“解析樹”。解析器使用 MySQL 的SQL 語(yǔ)法去翻譯和驗(yàn)證查詢語(yǔ)句。例如，解析器保證了查詢中的指令是有效且次序正確，并且會(huì)檢查那種類似字符串引號(hào)未配對(duì)的錯(cuò)誤。

預(yù)處理器則檢查構(gòu)建好的解析樹中那些解析器無(wú)法處理的語(yǔ)義信息。例如，檢查數(shù)據(jù)表和列是否存在，并且處理字段名稱和別名以保證列引用沒(méi)有歧義。接下來(lái)，預(yù)處理器會(huì)檢查權(quán)限，通常這會(huì)非?？欤ǔ悄愕姆?wù)端有一大堆權(quán)限配置）。

查詢優(yōu)化器

經(jīng)過(guò)解析器和預(yù)處理器后，解析樹就被確定是有效的了，可以被優(yōu)化器進(jìn)行處理并最終轉(zhuǎn)變?yōu)橐粋€(gè)查詢計(jì)劃。一個(gè)具有相同結(jié)果的查詢通常有很多種執(zhí)行方式，而優(yōu)化器的職責(zé)是找出其中最優(yōu)的選項(xiàng)。

MySQL使用基于代價(jià)估計(jì)的優(yōu)化器，這意味著它視圖預(yù)測(cè)眾多執(zhí)行計(jì)劃的代價(jià)，并選擇代價(jià)最低的那個(gè)。最初的單位成本是隨機(jī)的4KB 數(shù)據(jù)頁(yè)讀取，而現(xiàn)在變得更為復(fù)雜，包括了如執(zhí)行 WHERE比較條件的代價(jià)?？梢酝ㄟ^(guò)顯示 Last_query_cost 會(huì)話變量來(lái)查看查詢優(yōu)化器估計(jì)查詢語(yǔ)句的代價(jià)。

SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
SHOW STATUS LIKE 'Last_query_cost';

顯示的 Last_query_cost 意味著優(yōu)化器估計(jì)需要執(zhí)行對(duì)應(yīng)次數(shù)的隨機(jī)數(shù)據(jù)頁(yè)訪問(wèn)才能完成查詢。這是基于如下統(tǒng)計(jì)估算的結(jié)果：

• 數(shù)據(jù)表或索引占據(jù)的數(shù)據(jù)頁(yè)數(shù)；

• 索引的候選值；

• 數(shù)據(jù)行、鍵及鍵值分布對(duì)應(yīng)的數(shù)據(jù)長(zhǎng)度。
  

優(yōu)化器并不會(huì)考慮估計(jì)內(nèi)容的緩存——它假設(shè)每次都從磁盤 I/O 讀取結(jié)果。優(yōu)化器并不是每次都能選擇最優(yōu)的執(zhí)行計(jì)劃，原因如下：

• 統(tǒng)計(jì)本身可能是錯(cuò)誤的。服務(wù)端的統(tǒng)計(jì)結(jié)果依賴于存儲(chǔ)引擎，而存儲(chǔ)引擎可能十分準(zhǔn)確也可能很不準(zhǔn)確。例如，InnoDB 由于其 MVCC 架構(gòu)，并不保留數(shù)據(jù)表的準(zhǔn)確行數(shù)。

• 估計(jì)的代價(jià)和實(shí)際運(yùn)行的代價(jià)并不等價(jià)，因此即便統(tǒng)計(jì)是準(zhǔn)確的，查詢的代價(jià)與 MySQL 的估計(jì)也會(huì)或多或少存在偏差。一個(gè)讀取更多數(shù)據(jù)頁(yè)的查詢計(jì)劃也可能代價(jià)更低，例如如果是有序的磁盤 I/O 訪問(wèn)就會(huì)更快，又或是結(jié)果本身就已經(jīng)在緩存中。因此，優(yōu)化器本身并不知道查詢會(huì)引起多少次 I/O 操作。

• MySQL 人為的優(yōu)化也許與我們期待的不同。我們要的可能是更快的執(zhí)行時(shí)間，而 MySQL 并不是只追求快，它是最求最小化代價(jià)。因此，通過(guò)代價(jià)并不一定科學(xué)。

• MySQL并不考慮并發(fā)中的查詢，而這可能會(huì)影響查詢運(yùn)行的速度。

• MySQL 并不是一直都按代價(jià)估計(jì)做優(yōu)化。有時(shí)候僅僅是遵循一些規(guī)則，例如如果有一個(gè)全文匹配條件（MATCH 方法）則使用全文索引。即便是有一個(gè)更快的的其他索引和非全文條件查詢，MySQL 也不會(huì)按更快的方式執(zhí)行查詢。

• 優(yōu)化器對(duì)于不歸它控制的操作的代價(jià)并不會(huì)考慮，例如執(zhí)行存儲(chǔ)過(guò)程或自定義函數(shù)。

• 優(yōu)化器并不總是能夠估計(jì)每一個(gè)執(zhí)行計(jì)劃，有些時(shí)候它會(huì)忽略一個(gè)更優(yōu)的計(jì)劃。
  

MySQL 查詢優(yōu)化器是其中非常復(fù)雜的一部分，使用了很多優(yōu)化方式將查詢語(yǔ)句轉(zhuǎn)換成為一個(gè)查詢執(zhí)行計(jì)劃。通常有兩種優(yōu)化方式：靜態(tài)優(yōu)化和動(dòng)態(tài)優(yōu)化。靜態(tài)優(yōu)化可以簡(jiǎn)單地通過(guò)檢查解析樹進(jìn)行。例如，優(yōu)化器可以將 WHERE 條件通過(guò)數(shù)學(xué)運(yùn)算規(guī)則轉(zhuǎn)換成一個(gè)等式。靜態(tài)優(yōu)化與具體的值無(wú)關(guān)，例如 WHERE條件的常量值。他們執(zhí)行一次后會(huì)一直有效，即便是查詢語(yǔ)句使用了不同的值再次執(zhí)行?？梢岳斫鉃槭恰熬幾g時(shí)優(yōu)化”。

相反，動(dòng)態(tài)優(yōu)化是基于具體的情景的，并依賴于多種因素。例如，WHERE 條件中的值或索引中對(duì)應(yīng)的數(shù)據(jù)行數(shù)。這個(gè)過(guò)程在每次查詢都需要重新估計(jì)，可以理解為是“運(yùn)行時(shí)優(yōu)化”。以下是一些 MySQL 的典型優(yōu)化方式：

• 聯(lián)合查詢重新排序：數(shù)據(jù)表并不一定需要按照查詢語(yǔ)句的順序聯(lián)合。決定最優(yōu)的聯(lián)合查詢次序是十分重要的優(yōu)化。

• 將外聯(lián)接轉(zhuǎn)換為內(nèi)聯(lián)接：一個(gè)外聯(lián)接并不一定需要按外聯(lián)接查詢。有些因素，例如 WHERE 條件和數(shù)據(jù)表結(jié)構(gòu)可以將外聯(lián)接查詢等價(jià)于內(nèi)聯(lián)接。MySQL 可以識(shí)別這些情況，并重寫聯(lián)合查詢。

• 應(yīng)用數(shù)學(xué)等價(jià)公式：MySQL 應(yīng)用數(shù)學(xué)等價(jià)轉(zhuǎn)換簡(jiǎn)化表達(dá)式?？梢宰龅秸归_和減少常量，排除不可能的情況和常量表達(dá)式。例如，表達(dá)式(5=5 AND a>5)會(huì)精簡(jiǎn)為(a>5)。同樣的，(a 5 AND b=c AND a=5.這些規(guī)則對(duì)帶條件的查詢十分有用。

• COUNT()，MIN()和 MAX()優(yōu)化：索引和空值列通?？梢詭椭?MySQL 優(yōu)化這些函數(shù)。例如，查找二叉樹最左側(cè)一列的最小值時(shí)，MySQL 可以只請(qǐng)求索引的第一行數(shù)據(jù)。甚至可以在查詢優(yōu)化階段完成這個(gè)事情，而對(duì)于剩余的查詢當(dāng)作是常量值。而對(duì)于查詢最大值也是一樣，只需要讀取最后u 一行即可。如果服務(wù)端使用了這種優(yōu)化，可以在 EXPLAIN 中看到“Select tables optimized away”。這意味著優(yōu)化器將數(shù)據(jù)表從查詢計(jì)劃中移除并用常量替代了。類似地，COUNT(*)查詢?cè)跊](méi)有指定 WHERE 條件時(shí)也可以在某些存儲(chǔ)引擎被優(yōu)化（例如 MyISAM，會(huì)一直保存數(shù)據(jù)表的準(zhǔn)確行數(shù)）。

• 評(píng)估和精簡(jiǎn)常量表達(dá)式：一旦 MySQL 檢測(cè)到一個(gè)表達(dá)式可以精簡(jiǎn)為一個(gè)常量，那在優(yōu)化階段就會(huì)完成該操作。例如，一個(gè)用戶定義的變量如果在查詢過(guò)程中沒(méi)有變化，就可以轉(zhuǎn)換為常量。令人驚奇的是，在優(yōu)化階段，有些你認(rèn)為是一個(gè)查詢的語(yǔ)句也會(huì)被轉(zhuǎn)換為常量。一個(gè)例子就是 索引上的MIN()。這種情況也可以擴(kuò)展到對(duì)主鍵或獨(dú)立索引的常量查詢。如果 WHERE 條件對(duì)這樣的索引指定了常量，優(yōu)化器會(huì)知道 MySQL 會(huì)在查詢開始就查找對(duì)應(yīng)的值。然后，就會(huì)在剩余的查詢中把這個(gè)值當(dāng)做常量處理。下面是一個(gè)例子：

EXPLAIN SELECT film.film_id, film_actor.actor_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id = 1;

MySQL 會(huì)將這個(gè)查詢拆分為2步，因此分析結(jié)果會(huì)有兩行。第一步是是在 film 表中查找對(duì)應(yīng)的數(shù)據(jù)行。由于 是按主鍵film_id查詢的，MySQL 知道只有一行數(shù)據(jù)。 因此，此時(shí)的查詢分析結(jié)果的 ref 是常量。在第二步中，MySQL 會(huì)將 film_id 作為已知值，因此對(duì) film_actor 的查詢的 ref 也是常量。其他類似的場(chǎng)景還有 WHERE，USING或 ON 條件中的約束條件是等式。在這個(gè)例子中，MySQL 知道 USING條件的 film_id 在查詢語(yǔ)句中都是相同的值，這個(gè)值必須和 WHERE條件的 film_id 相同。

• 覆蓋索引：MySQL 有時(shí)候會(huì)利用索引數(shù)據(jù)而避免讀數(shù)行數(shù)據(jù)，如果索引包含了查詢所需的全部列的話。

• 子查詢優(yōu)化：MySQL 能夠?qū)⒁恍╊愋偷淖硬樵冝D(zhuǎn)換為更有效的變體形式，從而簡(jiǎn)化它們?yōu)樗饕樵兌皇窍嗷オ?dú)立的查詢。

• 提前中止：MySQL 可以在滿足查詢結(jié)果后提前中止查詢過(guò)程。最明顯的例子是 LIMIT條件。也有一些其他的提前中止的情形。例如，MySQL 檢測(cè)導(dǎo)一個(gè)可能條件后，可以中止整個(gè)查詢，如下面的例子所示：

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

在分析結(jié)果中的 Extra字段會(huì)看到“Impossible WHERE noticed after reading const tables”。在其他情形也會(huì)有提前中止的情況，例如：

SELECT film.film_id
FROM sakila.film
	LEFT OUTER JOIN sakila.film_actor USING(film_id)
WHERE sakila.film_actor.film_id IS NULL;

這個(gè)查詢排除那些有演員的電影。每部電源都可能有多名演員，但是只要找到一名演員后，MySQL 就會(huì)停止處理當(dāng)前的這部電影，而去處理下一部。對(duì)于 DISTINCT，NOT EXISTS 也會(huì)有類似的情況。

• 等效傳遞：MySQL 會(huì)識(shí)別導(dǎo)查詢語(yǔ)句中保持的列是否是等效的。例如，在 JOIN 條件中，WHERE 條件會(huì)影響導(dǎo)相同的列，如下面的查詢：

SELECT film.film_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500;

MySQL 會(huì)知道 WHERE 條件的約束不僅適用于 film 表，同樣也適用于 film_actor 表。但對(duì)于其他數(shù)據(jù)庫(kù)則未必會(huì)有這樣的優(yōu)化效果。

• IN 查詢比較：對(duì)于很多數(shù)據(jù)庫(kù)服務(wù)器，IN 查詢比等價(jià)為多個(gè) OR 條件，在邏輯上二者是等效的。但在 MySQL 中不是這樣，MySQL會(huì)對(duì) IN 查詢的列表值進(jìn)行排序，并使用二分查找法去檢查查詢值是否在列表中。這會(huì)使得算法復(fù)雜度從 O(n)降低導(dǎo) O(log n)。

實(shí)際上，MySQL 使用的優(yōu)化手段比上述列舉的多得多，這里沒(méi)法一一列舉。只是需要記住 MySQL 的優(yōu)化器的復(fù)雜性及其智能化程度。因此，應(yīng)當(dāng)讓優(yōu)化器發(fā)揮其作用，而不是無(wú)限優(yōu)化查詢語(yǔ)句直到 MySQL 的優(yōu)化器沒(méi)有用武之地。當(dāng)然，雖然 MySQL 的優(yōu)化器很聰明，但是它給出的并不一定是最優(yōu)結(jié)果，有些時(shí)候你知道最優(yōu)結(jié)果，而 MySQL 未必知道。這種情況下，你可以對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化從而幫助 MySQL 完成優(yōu)化工作，而有些時(shí)候則需要增加查詢的提示，或是重寫查詢，修改數(shù)據(jù)表設(shè)計(jì)或增加索引。

關(guān)于如何優(yōu)化MySQL查詢就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。