查詢優(yōu)化、索引優(yōu)化和表設(shè)計(jì)優(yōu)化是環(huán)環(huán)相扣的。如果你有豐富的編寫MySQL查詢語(yǔ)句的經(jīng)驗(yàn)，你就會(huì)知道如何設(shè)計(jì)表和索引來(lái)支持有效的查詢。同樣的，知曉表設(shè)計(jì)同樣有助于了解表結(jié)構(gòu)如何對(duì)查詢語(yǔ)句產(chǎn)生影響。因此，即便表設(shè)計(jì)和索引都設(shè)計(jì)得很好，但如果查詢語(yǔ)句寫得很糟糕，那查詢的性能也會(huì)很糟糕。
在嘗試編寫快速的查詢語(yǔ)句前，務(wù)必記住快速都是基于響應(yīng)時(shí)間進(jìn)行評(píng)估的。查詢語(yǔ)句是一組由多個(gè)子任務(wù)組成的大任務(wù)，每一個(gè)子任務(wù)都會(huì)消耗時(shí)間。為了優(yōu)化查詢，我們需要盡可能地減少子任務(wù)的數(shù)量，或者讓子任務(wù)執(zhí)行得更快。 注：有些時(shí)候我們也需要考慮查詢對(duì)系統(tǒng)其他查詢的影響，在這種情況下，還需要盡可能地減少資源消耗。 _ 通常，我們可以認(rèn)為查詢的生命周期貫穿于客戶端到服務(wù)端的整個(gè)交互時(shí)序圖中，包括了查詢語(yǔ)句解析、查詢計(jì)劃、執(zhí)行過(guò)程和數(shù)據(jù)返回到客戶端。執(zhí)行是查詢過(guò)程中最為重要的一環(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ù)過(guò)多、或過(guò)慢，就會(huì)導(dǎo)致額外的時(shí)間開銷。查詢優(yōu)化的目標(biāo)是避免這些情況——通過(guò)消除或減少操作，或者讓操作運(yùn)行更快。
需要注意的是，我們沒(méi)法繪制一個(gè)精確的查詢生命周期圖，我們的目的是展示理解查詢生命周期的重要性，并思考這些環(huán)節(jié)的耗時(shí)。有了這個(gè)基礎(chǔ)，就能夠著手去優(yōu)化查詢語(yǔ)句。
查詢性能差的最基礎(chǔ)的原因是處理了太多的數(shù)據(jù)。有些查詢必須從大量數(shù)據(jù)中進(jìn)行篩選，這種情況就沒(méi)法優(yōu)化。但這是不太正常的情況。大部分糟糕的查詢可以通過(guò)訪問(wèn)更少的數(shù)據(jù)進(jìn)行優(yōu)化。下面的兩個(gè)步驟對(duì)分析性能差的查詢十分有用：
有些查詢會(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ò)誤：
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';
一旦確定了查詢語(yǔ)句沒(méi)有獲取不必要的數(shù)據(jù)，就可以查找那些在返回結(jié)果前處理過(guò)多數(shù)據(jù)的查詢。在MySQL中，最簡(jiǎn)單的查詢消耗標(biāo)準(zhǔn)是：
這些標(biāo)準(zhǔn)沒(méi)有一個(gè)是完美的查詢性能評(píng)估手段，但它們大致反映了MySQL執(zhí)行查詢語(yǔ)句時(shí)在內(nèi)部處理過(guò)程中獲取的數(shù)據(jù)量和查詢運(yùn)行的速度。這三個(gè)標(biāo)準(zhǔn)都在慢查詢?nèi)罩局杏涗?，因此從慢查詢?nèi)罩局腥グl(fā)現(xiàn)數(shù)據(jù)處理過(guò)多的查詢是查詢優(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ù)端并沒(méi)有真正執(zhí)行查詢的那部分時(shí)間——它在等待某些資源，例如I/O操作的完成、行鎖釋放等等。問(wèn)題在于，你沒(méi)法準(zhǔn)確將響應(yīng)時(shí)間拆分成這兩部分——除非你能夠單獨(dú)測(cè)量這兩部分的時(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è)查詢引起的?？梢酝ㄟ^(guò)計(jì)算查詢的快速上限估計(jì)（QUBE）方法來(lái)評(píng)估其響應(yīng)時(shí)間：通過(guò)檢查查詢計(jì)劃和使用的索引，來(lái)決定需要的順序和隨機(jī)I/O訪問(wèn)操作，然后乘以機(jī)器的硬件執(zhí)行每次操作的時(shí)間來(lái)評(píng)估。通過(guò)將全部的時(shí)間求和可以評(píng)估查詢響應(yīng)慢是因?yàn)椴樵儽旧硪鸬倪€是其他原因。
處理和返回的數(shù)據(jù)行數(shù)量 在分析查詢語(yǔ)句時(shí)，思考處理行的數(shù)量十分有用，因?yàn)檫@樣可以直觀地知道查詢是如何獲取我們所需的數(shù)據(jù)。然而，這對(duì)查找糟糕的查詢并不是完美的測(cè)量工具。并不是所有的行訪問(wèn)都是一致的。更少的行訪問(wèn)速度更快，而從內(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ù)量級(jí)。
當(dāng)思考查詢的代價(jià)時(shí)，可以考慮從數(shù)據(jù)表獲取單獨(dú)一行的代價(jià)。MySQL使用多種獲取方法去查找和返回一行數(shù)據(jù)。有些需要處理多行，而有些則可能不需要檢查直接得到返回結(jié)果。
獲取數(shù)據(jù)的方法在EXPLAIN輸出結(jié)果的type列。包括了全表掃描、索引掃描、范圍掃描、唯一索引查找和常量。由于數(shù)據(jù)讀取量依次減少，因此上述的每一種方法都比它之前的要快。我們不需要記住獲取類型，但需要理解其中的基本概念。
如果沒(méi)有好的獲取類型，最佳解決問(wèn)題的方式是增加一個(gè)合適的索引。索引使得MySQL檢查更少的數(shù)據(jù)，從而更有效地查詢數(shù)據(jù)行。例如，以下面的簡(jiǎn)單查詢?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í)行查詢語(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行完成查詢。換言之，查詢優(yōu)化器知道如何選擇獲取類型來(lái)讓查詢更有效。如果查詢沒(méi)有合適的索引會(huì)怎么樣？MySQL必須使用次優(yōu)的獲取類型，當(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ù)期的那樣，獲取類型變成了全表掃描（ALL），MySQL估計(jì)需要處理5073行數(shù)據(jù)才能完成查詢。在Extra列中的Using where顯示MySQL服務(wù)器使用了WHERE條件來(lái)丟棄存儲(chǔ)引擎讀取的其他不符合條件的數(shù)據(jù)。通常，MySQL會(huì)在下面三種方式中使用WHERE條件，效果依次是從好到差：
下面的例子演示了有好的索引的重要性。好的索引有助于使用好的數(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ù)。這種查詢語(yǔ)句，即便有索引也無(wú)法減少需要的處理的數(shù)據(jù)行數(shù)。
不幸的是，MySQL并不會(huì)告知獲取了多少行來(lái)構(gòu)建返回結(jié)果集，它僅僅告知獲取的總行數(shù)。很多行通過(guò)WHERE條件過(guò)濾掉了，而對(duì)返回結(jié)果集沒(méi)有任何作用。在前面的例子中，移除sakila.film_actor索引后，查詢獲取了數(shù)據(jù)表的全部行，但是只從中取了10條數(shù)據(jù)作為結(jié)果集返回。理解服務(wù)器獲取的數(shù)據(jù)行數(shù)量和返回的數(shù)據(jù)行數(shù)量有助于理解查詢本身。 如果發(fā)現(xiàn)了需要獲取大量數(shù)據(jù)行而只是在結(jié)果使用很少的行，可以通過(guò)下面的方式修復(fù)這個(gè)問(wèn)題：
以上就是MySQL 如何分析查詢性能的詳細(xì)內(nèi)容，更多關(guān)于MySQL 分析查詢性能的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
標(biāo)簽：自貢 迪慶 麗水 西寧 徐州 無(wú)錫 南充 龍巖
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL 如何分析查詢性能》，本文關(guān)鍵詞 MySQL,如何,分析,查詢,性能,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
