MySQL 如何分析查詢性能

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

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

當(dāng)完成所有這些任務(wù)后，查詢還會(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ù)過多、或過慢，就會(huì)導(dǎo)致額外的時(shí)間開銷。查詢優(yōu)化的目標(biāo)是避免這些情況——通過消除或減少操作，或者讓操作運(yùn)行更快。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

理想情況下，處理的數(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ù)量級。

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

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

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

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

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

這個(gè)查詢會(huì)返回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估計(jì)僅僅需要獲取10行完成查詢。換言之，查詢優(yōu)化器知道如何選擇獲取類型來讓查詢更有效。如果查詢沒有合適的索引會(huì)怎么樣？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估計(jì)需要處理5073行數(shù)據(jù)才能完成查詢。在Extra列中的Using where顯示MySQL服務(wù)器使用了WHERE條件來丟棄存儲(chǔ)引擎讀取的其他不符合條件的數(shù)據(jù)。通常，MySQL會(huì)在下面三種方式中使用WHERE條件，效果依次是從好到差：

1. 通過索引查找操作去除不匹配的數(shù)據(jù)行，這發(fā)生在存儲(chǔ)引擎層；
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;

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

不幸的是，MySQL并不會(huì)告知獲取了多少行來構(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ù)這個(gè)問題：

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

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