查詢(xún)優(yōu)化、索引優(yōu)化和表設(shè)計(jì)優(yōu)化是環(huán)環(huán)相扣的。如果你有豐富的編寫(xiě)MySQL查詢(xún)語(yǔ)句的經(jīng)驗(yàn)，你就會(huì)知道如何設(shè)計(jì)表和索引來(lái)支持有效的查詢(xún)。同樣的，知曉表設(shè)計(jì)同樣有助于了解表結(jié)構(gòu)如何對(duì)查詢(xún)語(yǔ)句產(chǎn)生影響。因此，即便表設(shè)計(jì)和索引都設(shè)計(jì)得很好，但如果查詢(xún)語(yǔ)句寫(xiě)得很糟糕，那查詢(xún)的性能也會(huì)很糟糕。

在嘗試編寫(xiě)快速的查詢(xún)語(yǔ)句前，務(wù)必記住快速都是基于響應(yīng)時(shí)間進(jìn)行評(píng)估的。查詢(xún)語(yǔ)句是一組由多個(gè)子任務(wù)組成的大任務(wù)，每一個(gè)子任務(wù)都會(huì)消耗時(shí)間。為了優(yōu)化查詢(xún)，我們需要盡可能地減少子任務(wù)的數(shù)量，或者讓子任務(wù)執(zhí)行得更快。 注：有些時(shí)候我們也需要考慮查詢(xún)對(duì)系統(tǒng)其他查詢(xún)的影響，在這種情況下，還需要盡可能地減少資源消耗。 _ 通常，我們可以認(rèn)為查詢(xún)的生命周期貫穿于客戶(hù)端到服務(wù)端的整個(gè)交互時(shí)序圖中，包括了查詢(xún)語(yǔ)句解析、查詢(xún)計(jì)劃、執(zhí)行過(guò)程和數(shù)據(jù)返回到客戶(hù)端。執(zhí)行是查詢(xún)過(guò)程中最為重要的一環(huán)，包括了從存儲(chǔ)引擎獲取數(shù)據(jù)行而發(fā)起的大量調(diào)用，以及獲取數(shù)據(jù)后的處理，例如分組和排序。

當(dāng)完成所有這些任務(wù)后，查詢(xún)還會(huì)在網(wǎng)絡(luò)傳錯(cuò)、CPU處理、數(shù)據(jù)統(tǒng)計(jì)和策略規(guī)劃、等待鎖、從存儲(chǔ)引擎獲取數(shù)據(jù)行的操作中消耗時(shí)間。這些調(diào)用會(huì)在內(nèi)存操作、CPU操作和I/O操作中消耗時(shí)間。在每一種情況中，如果這些操作被濫用、執(zhí)行次數(shù)過(guò)多、或過(guò)慢，就會(huì)導(dǎo)致額外的時(shí)間開(kāi)銷(xiāo)。查詢(xún)優(yōu)化的目標(biāo)是避免這些情況——通過(guò)消除或減少操作，或者讓操作運(yùn)行更快。

需要注意的是，我們沒(méi)法繪制一個(gè)精確的查詢(xún)生命周期圖，我們的目的是展示理解查詢(xún)生命周期的重要性，并思考這些環(huán)節(jié)的耗時(shí)。有了這個(gè)基礎(chǔ)，就能夠著手去優(yōu)化查詢(xún)語(yǔ)句。

慢查詢(xún)基礎(chǔ)：優(yōu)化數(shù)據(jù)獲取

查詢(xún)性能差的最基礎(chǔ)的原因是處理了太多的數(shù)據(jù)。有些查詢(xún)必須從大量數(shù)據(jù)中進(jìn)行篩選，這種情況就沒(méi)法優(yōu)化。但這是不太正常的情況。大部分糟糕的查詢(xún)可以通過(guò)訪問(wèn)更少的數(shù)據(jù)進(jìn)行優(yōu)化。下面的兩個(gè)步驟對(duì)分析性能差的查詢(xún)十分有用：

1. 找出應(yīng)用是不是獲取了你需要之外的數(shù)據(jù)。通常這意味著應(yīng)用獲取了太多的數(shù)據(jù)行或數(shù)據(jù)列。
2. 找出MySQL服務(wù)器是不是分析了超過(guò)需要的行。
   

檢查是否向數(shù)據(jù)庫(kù)請(qǐng)求了不必要的數(shù)據(jù)

有些查詢(xún)會(huì)向數(shù)據(jù)庫(kù)服務(wù)器請(qǐng)求所需要的數(shù)據(jù)，然后將這些數(shù)據(jù)丟棄。這會(huì)增加MySQL服務(wù)器的工作、加重網(wǎng)絡(luò)負(fù)荷、消耗更多內(nèi)存和應(yīng)用服務(wù)器的CPU資源。下面是一些典型的錯(cuò)誤：

1. 獲取不需要的數(shù)據(jù)行：一個(gè)常見(jiàn)的誤區(qū)是假設(shè)MySQL只提供需要的結(jié)果，而不是計(jì)算和返回全部的結(jié)果集。通常這種錯(cuò)誤發(fā)生在熟悉其他數(shù)據(jù)庫(kù)系統(tǒng)的人身上。這些開(kāi)發(fā)者習(xí)慣于使用返回很多行的SELECT語(yǔ)句，然后從中取出前N行，之后不再使用返回的結(jié)果集（例如從一個(gè)資訊網(wǎng)站獲取最近的100篇文章，然后在前端僅僅展示其中的10條）。他們會(huì)認(rèn)為MySQL在拿到10行數(shù)據(jù)后就會(huì)停止查詢(xún)，而實(shí)際MySQL會(huì)獲取完整的數(shù)據(jù)集合。然后，客戶(hù)端或獲取全部的數(shù)據(jù)再將其中的大部分丟棄。最佳的解決方案是在查詢(xún)中加上LIMIT條件。
2. 在一個(gè)多表聯(lián)合查詢(xún)終獲取全部列：如果你需要獲取恐龍時(shí)代這部電影的全部演員，不要像下面那樣寫(xiě)你的SQL語(yǔ)句：

SELECT * FROM sakila.actor
INNER JOIN sakila.file_actor USING(actor_id)
INNER JOIN sakila.file USING (film_id)
WHERE sakila.film.title = 'Academy Dinosaur';

這會(huì)返回參與聯(lián)合查詢(xún)的三張表的全部列。更好的做法是，像下面那樣寫(xiě)：

SELECT sakila.actor.* FROM sakila.actor
INNER JOIN sakila.file_actor USING(actor_id)
INNER JOIN sakila.file USING (film_id)
WHERE sakila.film.title = 'Academy Dinosaur';

1. 獲取全部數(shù)據(jù)列：在你看到SELECT *這樣的查詢(xún)時(shí)，一定要保持懷疑：真的需要全部的列嗎？很可能不是的。獲取全部的數(shù)據(jù)列會(huì)讓覆蓋索引失效、增加I/O負(fù)擔(dān)、內(nèi)存消耗和CPU負(fù)荷。有些DBA直接因?yàn)檫@個(gè)禁用SELECT *，并且可以減少人員修改表的列后引發(fā)的問(wèn)題。當(dāng)然，請(qǐng)求不必要的數(shù)據(jù)并不總是糟糕。在調(diào)查中發(fā)現(xiàn)，這種方式可以簡(jiǎn)化開(kāi)發(fā)工作，因?yàn)檫@樣可以提高代碼的復(fù)用性。只要你知道這會(huì)影響性能，那會(huì)是一個(gè)正當(dāng)?shù)睦碛伞Ｍ瑯拥模绻趹?yīng)用中使用了某些緩存機(jī)制，也會(huì)提高緩存的命中率。獲取和緩存全部對(duì)象可以通過(guò)運(yùn)行多個(gè)獲取部分對(duì)象的獨(dú)立的查詢(xún)來(lái)處理會(huì)更好。
2. 重復(fù)獲取相同數(shù)據(jù)：如果粗心的話，很容易在應(yīng)用中編寫(xiě)獲取相同數(shù)據(jù)的代碼。例如，如果你要在評(píng)論列表中展示用戶(hù)個(gè)人信息中的頭像，你可能再每一條評(píng)論都獲取一次。更有效的方式是第一次獲取后緩存起來(lái)直接在評(píng)論列表使用。
   

檢查MySQL是不是處理了過(guò)多的數(shù)據(jù)

一旦確定了查詢(xún)語(yǔ)句沒(méi)有獲取不必要的數(shù)據(jù)，就可以查找那些在返回結(jié)果前處理過(guò)多數(shù)據(jù)的查詢(xún)。在MySQL中，最簡(jiǎn)單的查詢(xún)消耗標(biāo)準(zhǔn)是：

1. 響應(yīng)時(shí)間
2. 處理的數(shù)據(jù)行數(shù)量
3. 返回的數(shù)據(jù)行數(shù)量
   

這些標(biāo)準(zhǔn)沒(méi)有一個(gè)是完美的查詢(xún)性能評(píng)估手段，但它們大致反映了MySQL執(zhí)行查詢(xún)語(yǔ)句時(shí)在內(nèi)部處理過(guò)程中獲取的數(shù)據(jù)量和查詢(xún)運(yùn)行的速度。這三個(gè)標(biāo)準(zhǔn)都在慢查詢(xún)?nèi)罩局杏涗洠虼藦穆樵?xún)?nèi)罩局腥グl(fā)現(xiàn)數(shù)據(jù)處理過(guò)多的查詢(xún)是查詢(xún)優(yōu)化的最佳實(shí)踐方式。

響應(yīng)時(shí)間 首先，注意查詢(xún)響應(yīng)時(shí)間是我們看到的一個(gè)表象。實(shí)際上，響應(yīng)時(shí)間比我們想象的要更為復(fù)雜。響應(yīng)時(shí)間由兩部分組成：服務(wù)時(shí)間和隊(duì)列時(shí)間。服務(wù)時(shí)間是服務(wù)端實(shí)際處理查詢(xún)的時(shí)間。隊(duì)列時(shí)間是服務(wù)端并沒(méi)有真正執(zhí)行查詢(xún)的那部分時(shí)間——它在等待某些資源，例如I/O操作的完成、行鎖釋放等等。問(wèn)題在于，你沒(méi)法準(zhǔn)確將響應(yīng)時(shí)間拆分成這兩部分——除非你能夠單獨(dú)測(cè)量這兩部分的時(shí)間，而這是很難做到的。最常見(jiàn)和最重要的情形是I/O阻塞和等待鎖，但不是百分之百都是這樣。

結(jié)果就是，響應(yīng)時(shí)間在不同負(fù)荷情況下并不是一成不變的。其他的因素，例如存儲(chǔ)引擎鎖、高并發(fā)和硬件都會(huì)影響響應(yīng)時(shí)間。因此，當(dāng)檢查響應(yīng)時(shí)間的時(shí)候，首先要決定這個(gè)響應(yīng)時(shí)間是不是僅僅是這個(gè)查詢(xún)引起的。可以通過(guò)計(jì)算查詢(xún)的快速上限估計(jì)（QUBE）方法來(lái)評(píng)估其響應(yīng)時(shí)間：通過(guò)檢查查詢(xún)計(jì)劃和使用的索引，來(lái)決定需要的順序和隨機(jī)I/O訪問(wèn)操作，然后乘以機(jī)器的硬件執(zhí)行每次操作的時(shí)間來(lái)評(píng)估。通過(guò)將全部的時(shí)間求和可以評(píng)估查詢(xún)響應(yīng)慢是因?yàn)椴樵?xún)本身引起的還是其他原因。

處理和返回的數(shù)據(jù)行數(shù)量 在分析查詢(xún)語(yǔ)句時(shí)，思考處理行的數(shù)量十分有用，因?yàn)檫@樣可以直觀地知道查詢(xún)是如何獲取我們所需的數(shù)據(jù)。然而，這對(duì)查找糟糕的查詢(xún)并不是完美的測(cè)量工具。并不是所有的行訪問(wèn)都是一致的。更少的行訪問(wèn)速度更快，而從內(nèi)存中獲取數(shù)據(jù)行比在磁盤(pán)獲取要快很多。

理想情況下，處理的數(shù)據(jù)行和返回的數(shù)據(jù)行是相等的，但是實(shí)際上很少會(huì)這樣。例如，使用聯(lián)合索引構(gòu)建返回行時(shí)，服務(wù)端必須從多個(gè)行中獲取數(shù)據(jù)以產(chǎn)生返回的行數(shù)據(jù)。處理的數(shù)據(jù)行和返回的數(shù)據(jù)行的比例通常很小，在1:1到10:1之間，但有時(shí)候可能是更大的數(shù)量級(jí)。

數(shù)據(jù)行處理和獲取類(lèi)型

當(dāng)思考查詢(xún)的代價(jià)時(shí)，可以考慮從數(shù)據(jù)表獲取單獨(dú)一行的代價(jià)。MySQL使用多種獲取方法去查找和返回一行數(shù)據(jù)。有些需要處理多行，而有些則可能不需要檢查直接得到返回結(jié)果。

獲取數(shù)據(jù)的方法在EXPLAIN輸出結(jié)果的type列。包括了全表掃描、索引掃描、范圍掃描、唯一索引查找和常量。由于數(shù)據(jù)讀取量依次減少，因此上述的每一種方法都比它之前的要快。我們不需要記住獲取類(lèi)型，但需要理解其中的基本概念。

如果沒(méi)有好的獲取類(lèi)型，最佳解決問(wèn)題的方式是增加一個(gè)合適的索引。索引使得MySQL檢查更少的數(shù)據(jù)，從而更有效地查詢(xún)數(shù)據(jù)行。例如，以下面的簡(jiǎn)單查詢(xún)?yōu)槔?/p>

EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

這個(gè)查詢(xún)會(huì)返回10行數(shù)據(jù)，然后EXPLAIN指令顯示了MySQL在idx_fk_film_id索引上使用了ref類(lèi)型執(zhí)行查詢(xún)語(yǔ)句。

***********************1. row************************
id: 1
select_type: SIMPLE
table:film_actor
type: ref 
possile)keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra: 

EXPLAIN指令顯示MySQL估計(jì)僅僅需要獲取10行完成查詢(xún)。換言之，查詢(xún)優(yōu)化器知道如何選擇獲取類(lèi)型來(lái)讓查詢(xún)更有效。如果查詢(xún)沒(méi)有合適的索引會(huì)怎么樣？MySQL必須使用次優(yōu)的獲取類(lèi)型，當(dāng)刪除掉表索引后再來(lái)看結(jié)果。

ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
ALTER TABLE sakila.film_actor DROP DROP KEY idx_fk_film_id;
EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

***********************1. row************************
id: 1
select_type: SIMPLE
table:film_actor
type: ALL 
possile)keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where

如同預(yù)期的那樣，獲取類(lèi)型變成了全表掃描（ALL），MySQL估計(jì)需要處理5073行數(shù)據(jù)才能完成查詢(xún)。在Extra列中的Using where顯示MySQL服務(wù)器使用了WHERE條件來(lái)丟棄存儲(chǔ)引擎讀取的其他不符合條件的數(shù)據(jù)。通常，MySQL會(huì)在下面三種方式中使用WHERE條件，效果依次是從好到差：

1. 通過(guò)索引查找操作去除不匹配的數(shù)據(jù)行，這發(fā)生在存儲(chǔ)引擎層；
2. 使用覆蓋索引（在Extra列顯示是Using index）去避免數(shù)據(jù)行訪問(wèn)，并在獲取到結(jié)果后將不符合條件的數(shù)據(jù)過(guò)濾掉。這發(fā)生在服務(wù)器層，但不需要從數(shù)據(jù)表讀取數(shù)據(jù)行。
3. 從數(shù)據(jù)表獲取數(shù)據(jù)行，然后在過(guò)濾掉不匹配的數(shù)據(jù)（在Extra列顯示為Using where）。這發(fā)生在服務(wù)器層，并且需要在過(guò)濾數(shù)據(jù)前從數(shù)據(jù)表讀取數(shù)據(jù)行。

下面的例子演示了有好的索引的重要性。好的索引有助于使用好的數(shù)據(jù)獲取類(lèi)型并且只需要處理所需要的數(shù)據(jù)行。然而，添加索引并不總是意味著MySQL獲取和返回的數(shù)據(jù)行是一致的。例如，下面的COUNT()聚合方法。

SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;

這個(gè)查詢(xún)只返回200行，但是在構(gòu)建返回結(jié)果集前需要讀取數(shù)千行數(shù)據(jù)。這種查詢(xún)語(yǔ)句，即便有索引也無(wú)法減少需要的處理的數(shù)據(jù)行數(shù)。

不幸的是，MySQL并不會(huì)告知獲取了多少行來(lái)構(gòu)建返回結(jié)果集，它僅僅告知獲取的總行數(shù)。很多行通過(guò)WHERE條件過(guò)濾掉了，而對(duì)返回結(jié)果集沒(méi)有任何作用。在前面的例子中，移除sakila.film_actor索引后，查詢(xún)獲取了數(shù)據(jù)表的全部行，但是只從中取了10條數(shù)據(jù)作為結(jié)果集返回。理解服務(wù)器獲取的數(shù)據(jù)行數(shù)量和返回的數(shù)據(jù)行數(shù)量有助于理解查詢(xún)本身。 如果發(fā)現(xiàn)了需要獲取大量數(shù)據(jù)行而只是在結(jié)果使用很少的行，可以通過(guò)下面的方式修復(fù)這個(gè)問(wèn)題：

1. 使用覆蓋索引，這使得存儲(chǔ)引擎不需要獲取完整的數(shù)據(jù)行（直接從索引中獲取）。
2. 修改查詢(xún)表，一個(gè)例子是構(gòu)建匯總表來(lái)查詢(xún)統(tǒng)計(jì)數(shù)據(jù)。
3. 重寫(xiě)復(fù)雜的查詢(xún)語(yǔ)句，使得MySQL查詢(xún)優(yōu)化器能夠以更優(yōu)的方式執(zhí)行。

以上就是MySQL 如何分析查詢(xún)性能的詳細(xì)內(nèi)容，更多關(guān)于MySQL 分析查詢(xún)性能的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！