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

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

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

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

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

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

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

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

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

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

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';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這個查詢會返回10行數(shù)據(jù)，然后EXPLAIN指令顯示了MySQL在idx_fk_film_id索引上使用了ref類型執(zhí)行查詢語句。

***********************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估計僅僅需要獲取10行完成查詢。換言之，查詢優(yōu)化器知道如何選擇獲取類型來讓查詢更有效。如果查詢沒有合適的索引會怎么樣？MySQL必須使用次優(yōu)的獲取類型，當(dāng)刪除掉表索引后再來看結(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ù)期的那樣，獲取類型變成了全表掃描（ALL），MySQL估計需要處理5073行數(shù)據(jù)才能完成查詢。在Extra列中的Using where顯示MySQL服務(wù)器使用了WHERE條件來丟棄存儲引擎讀取的其他不符合條件的數(shù)據(jù)。通常，MySQL會在下面三種方式中使用WHERE條件，效果依次是從好到差：

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

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

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

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

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

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

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