前言 

如果把查詢看作是一個任務(wù)，那么它由一些列子任務(wù)組成，每個子任務(wù)都會消耗一定的時間。如果要優(yōu)化查詢，實(shí)際上要優(yōu)化其子任務(wù)，要么消除其中一些子任務(wù)，要么減少子任務(wù)的執(zhí)行次數(shù)。通常來說，查詢的生命周期大致可以按照順序來看：從客戶端到服務(wù)器，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃，執(zhí)行，并返回結(jié)果給客戶端。其中“執(zhí)行”可以認(rèn)為是整個生命周期中最重要的階段，其中包括大量為了檢索數(shù)據(jù)到存儲引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理，包括排序、分組等。上述操作會在網(wǎng)絡(luò)、CPU計(jì)算、生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃、鎖等待（互斥等待）等操作上花費(fèi)時間，尤其是向底層存儲引擎檢索數(shù)據(jù)的調(diào)用操作。根據(jù)存儲引擎的不同，可能還會產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用。

一、是否請求了不需要的數(shù)據(jù)

查詢性能低下最基本的原因是訪問的數(shù)據(jù)太多。大部分性能低下的查詢都可以通過減少訪問的數(shù)據(jù)量的方式進(jìn)行優(yōu)化。對于低效查詢可以通過如下兩個步驟來分析總是有效：

【1】確定應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。意味著訪問了太多的行或者太多的列。

【2】確定 MySQL 服務(wù)器是否在分析大量超過需要的數(shù)據(jù)行。

有些查詢會請求超過實(shí)際需要的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會被應(yīng)用程序丟棄。這會給 MySQL 服務(wù)器帶來額外的負(fù)擔(dān)，并增加網(wǎng)絡(luò)開銷【應(yīng)用服務(wù)器和數(shù)據(jù)庫不再同一臺服務(wù)器上】另外也會消耗應(yīng)用服務(wù)器的 CPU和內(nèi)存資源。通常企業(yè)不允許使用 SELECT * 語句進(jìn)行查詢。

二、是否掃描了額外的記錄

在確定查詢只返回需要的數(shù)據(jù)以后，接下來應(yīng)該查看查詢是否掃描了過多的數(shù)據(jù)。對于 MySQL，最簡單的衡量查詢開銷的三個指標(biāo)是響應(yīng)時間、掃描的行數(shù)、返回的行數(shù)：這三個指標(biāo)都會記錄到慢日志【SHOW VARIABLES LIKE “%slow%”;】中。

【1】響應(yīng)時間： 服務(wù)時間和排隊(duì)時間之和，服務(wù)時間是指數(shù)據(jù)庫處理這個查詢真正花費(fèi)的時間。排隊(duì)時間是指服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時間（等待I/O操作或鎖，等等）。遺憾的是無法將響應(yīng)時間細(xì)分到上面這些部分。

【2】掃描的行數(shù)和返回的行數(shù)： 分析查詢時，查看該查詢掃描的行數(shù)是非常有幫助的。但并不是所有的行的訪問代價都是相同的。較短的行訪問速度快，內(nèi)存中的行也比磁盤中的行的訪問速度要快很多。理想情況下掃描的行數(shù)和返回的行數(shù)應(yīng)該是相同的。但這種情況并不多。例如在做一個關(guān)聯(lián)查詢時，服務(wù)器必須要掃描多行才能生成結(jié)果集中的一行。掃描的行數(shù)對返回的行數(shù)的比率通常很小，以便在1:1和10:1之間，不過有時候這個值也可能非常非常大。

【3】掃描的行數(shù)和訪問類型： 在評估查詢開銷的時候，需要考慮一下從表中找到某一行數(shù)據(jù)的成本。MySQL 有好幾種查詢方式可以查找并返回一行結(jié)果。有些訪問方式可能需要掃描多行才能返回一行結(jié)果，也有些訪問方式可能無需掃描就能返回結(jié)果。在EXPLAN 語句中的 type 列反映了訪問類型。從全表掃描、索引掃描、范圍掃描、唯一索引查詢、常數(shù)引用等。速度從慢到快，掃描的行數(shù)也是從多到少。如果查詢沒有辦法找到合適的訪問類型，那么解決的最好辦法就是添加一個合適的索引。索引讓 MySQL 以最高效、掃描行數(shù)最少的方式找到需要的記錄。

【4】如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)行： 通常可以使用如下技巧去優(yōu)化它：①、使用索引覆蓋掃描，把所有需要的列都放到索引中，這樣存儲引擎無需回表獲取對應(yīng)行就可以返回結(jié)果了。②、改變表結(jié)構(gòu)。例如使用單獨(dú)的匯總表。③、重寫這個復(fù)雜的查詢，讓 MySQL 優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個查詢。

三、一個復(fù)雜查詢OR多個簡單查詢

有時候，可以將查詢轉(zhuǎn)換一種寫法讓其返回一樣的結(jié)果，但是性能更好。但也可以通過修改應(yīng)用代碼，用另一種方式完成查詢，達(dá)到最后的目的。

設(shè)計(jì)查詢的時候需要考慮一個重要問題，是否需要將一個復(fù)雜的查詢分成多個簡單的查詢。在傳統(tǒng)的實(shí)現(xiàn)中，總是強(qiáng)調(diào)需要數(shù)據(jù)庫層完成盡可能多的工作，這樣做邏輯在于以前總是認(rèn)為網(wǎng)絡(luò)通信、查詢解析和優(yōu)化是一件代價很高的事情。對于MySQL 并不適用，MySQL 從設(shè)計(jì)上讓連接和斷開連接都是輕量級， 在返回一個小的查詢結(jié)果很高效。現(xiàn)在的網(wǎng)絡(luò)速度比以前也快很多，無論是寬帶還是延遲。即使一個通用的服務(wù)器上，也能夠運(yùn)行每秒超過10萬的查詢。

四、切分查詢

有時候?qū)τ谝粋€大查詢我們需要 “分而治之” 將大查詢切分成小查詢，每個查詢功能完全一樣，只是完成一小部分，每次只返回一小部分查詢結(jié)果。刪除舊的數(shù)據(jù)就是一個很好的例子。定期地清除大量數(shù)據(jù)時，如果用一個大的語句一次性完成的話，則可能需要一次性鎖住很多數(shù)據(jù)、占滿整個事務(wù)日志，耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。將一個大的DELETE 切分成多個較小的查詢可以盡可能小地影響 MySQL 性能，同時還可以減少 MySQL 的復(fù)制延遲。一秒刪除一萬行數(shù)據(jù)一般來說是一個比較高效而且對服務(wù)器影響也比較小的做法。如果每次刪除數(shù)據(jù)后，都暫停一會兒再做下一次刪除，這樣也可以將服務(wù)器上原本一次性的壓力分散到一個很長的時間段中，就可以大大降低對服務(wù)器的影響，還可以大大降低刪除時鎖的持有時間。

五、分解關(guān)聯(lián)查詢

很多高性能的應(yīng)用都會對關(guān)聯(lián)查詢進(jìn)行分解?？梢詫γ恳粋€表進(jìn)行一次單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。如下：

SELECT * FROM teacher t
JOIN student s ON t.id = s.t_id
JOIN class c ON t.id = c.t_id
WHERE t.name='Li';
--拆分后
SELECT * FROM teacher t WHERE t.name='Li';
SELECT * FROM student s WHERE s.id = 12;
SELECT * FROM class c WHERE c.id IN (13,45,65);

用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下的優(yōu)勢：

【1】讓緩存的效果更高，許多應(yīng)用程序可以方便地緩存單表查詢對應(yīng)的結(jié)果對象。例如，上面的 teacher 已經(jīng)被緩存了，那么應(yīng)用就跳過了第一個查詢，再例如，應(yīng)用程序中已經(jīng)緩存了 ID 為 12、45 的內(nèi)容，那么第三個查詢的 IN() 中就可以少幾個 ID。另外，對于MySQL 的查詢緩存來說，如果關(guān)聯(lián)中某個表發(fā)生了變化，那么就無法使用查詢緩存了，而拆分后，如果某個表很少改變，那么基于該表的查詢就可以重復(fù)利用查詢緩存結(jié)果了。

【2】將查詢分解后，執(zhí)行單個查詢就可以減少鎖的競爭。

【3】在應(yīng)用層做關(guān)聯(lián)，可以更容易對數(shù)據(jù)庫進(jìn)行拆分，更容易做到高性能和可擴(kuò)展。

【4】查詢本身效率也可能有所提升。這個例子中，使用 IN() 代替關(guān)聯(lián)查詢，可以讓 MySQL 按照ID 順序進(jìn)行查詢，這可能比隨機(jī)的關(guān)聯(lián)要更高效。

【5】可以減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)查詢，意味著對于某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)。這樣的重構(gòu)還可能會減少網(wǎng)絡(luò)和內(nèi)存的消耗。

【6】這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)，而不是使用 MySQL 的嵌套循環(huán)關(guān)聯(lián)。某些場景哈希關(guān)聯(lián)效率要高很多。

六、UNION的限制

MySQL 無法將外層限制條件延續(xù)到內(nèi)層，這使得原本可以返回部分結(jié)果的條件無法應(yīng)用到內(nèi)部查詢的優(yōu)化上。如果希望 UNION 的各個子句根據(jù) LIMIT 只取部分結(jié)果集，或者希望能夠先排好序再合并結(jié)果集的話，就需要在 UNION 的各個子句中分別使用這些子句。例如，想將兩個子查詢結(jié)果聯(lián)合起來，然后再取前20條記錄，那么MySQL 會將兩個表都存放到同一個臨時表中，然后再取出前20行記錄：

--UNION 操作符選取不同的值。如果允許重復(fù)的值，請使用 UNION ALL
(SELECT first_name,last_name FROM people_A ORDER BY last_name)
UNION ALL
(SELECT first_name,last_name FROM people_B ORDER BY last_name)
LIMIT 20;

這條查詢將會把 people_A 中的所有記錄和 people_B 的所有記錄放在一個臨時表中，然后再從臨時表中取出前20條?？梢酝ㄟ^在 UNION 的兩個子查詢中分別加上一個 LIMIT 20來減少臨時表中的數(shù)據(jù)：

(SELECT first_name,last_name FROM people_A ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name,last_name FROM people_B ORDER BY last_name LIMIT 20)
LIMIT 20;

現(xiàn)在中間的臨時表只會包含40條記錄，除了性能考慮之外，在這里還需要注意一點(diǎn)，從臨時表中取出數(shù)據(jù)的順序并不是一定的，所以如果想獲得正確的順序，還需要加上一個全局的 ORDER BY 和 LIMIT 操作。

MySQL 總是通過創(chuàng)建并填充臨時表的方式來執(zhí)行 UNION 查詢。除非確定需要服務(wù)器消除重復(fù)的行，否則就一定要使用 UNION ALL，如果沒有 ALL 關(guān)鍵字，MySQL 會給臨時表加上 DISTINCT 選項(xiàng)，這會導(dǎo)致給整個臨時表做唯一性檢查。代價非常高。就是有 ALL 關(guān)鍵字，MySQL 仍然會使用臨時表存儲結(jié)果。事實(shí)上，MySQL 總是把結(jié)果放入臨時表，然后再讀出來，再返回給客戶端。

七、優(yōu)化COUNT()查詢

COUNT() 可以統(tǒng)計(jì)某個列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。在統(tǒng)計(jì)列值時要求列值是非空的（不統(tǒng)計(jì)NULL）。如果在COUNT() 的括號中制定了列或者表達(dá)式，則統(tǒng)計(jì)的就是這個表達(dá)式有值的結(jié)果數(shù)。COUNT()的另一個作用是統(tǒng)計(jì)行數(shù)，當(dāng)MySQL確認(rèn)括號內(nèi)的表達(dá)式值不可能為空的時候，實(shí)際上就是在統(tǒng)計(jì)行數(shù)。最簡單的就是當(dāng)我們使用 COUNT(*) 的時候，這種情況它會忽略所有的列直接統(tǒng)計(jì)所有的行數(shù)。

MyISAM 的 COUNT() 函數(shù)總是非常快，前提是沒有任何 WHERE 條件。因?yàn)闊o需實(shí)際計(jì)算表的行數(shù)。MySQL 可以利用存儲引擎的特性直接獲取這個值。如果 MySQL 知道某個列 col 不可能為 NULL 值，那么內(nèi)部會將 COUNT(col) 轉(zhuǎn)換成COUNT(*)。

【簡單優(yōu)化】 有時候可以使用 MyISAM 在 COUNT(*) 全表非常快的這個特性，來加速一些特定條件的 COUNT() 查詢。比如：

SELECT COUNT(*) FROM city WHERE ID>5;

通過 SHOW STATUS 的結(jié)果可以看到該查詢需要掃描 5000行數(shù)據(jù)。如果將條件反轉(zhuǎn)，先查找ID小于等于5的城市，然后用總城市減就能獲得同樣的結(jié)果，卻可以將掃描數(shù)減少到5行以內(nèi)。

--ID 是索引，所以會去前5行數(shù)據(jù)
SELECT (SELECT COUNT(*) FROM city)-COUNT(*) FROM  city WHERE ID<=5;

通常來說，COUNT() 都需要掃描大量的行才能獲得精準(zhǔn)的結(jié)果，因?yàn)槭呛茈y優(yōu)化的。在MySQL 層面還能做的就只有索引覆蓋掃描了。如果還不夠，就需要考慮修改應(yīng)用的架構(gòu)，可以增加匯總表，或者增加類似 memcached 緩存系統(tǒng)。

八、優(yōu)化LIMIT分頁

在進(jìn)行分頁操作的時候，通常會使用 LIMIT 加上偏移量的辦法實(shí)現(xiàn)，同時加上合適的 ORDER BY 子句。如果有對應(yīng)的索引效率會不錯，否則，MySQL 要做大量的文件排序操作。有一個問題，當(dāng)偏移量非常大的時候，例如 LIMIT 10 000,20 這樣的查詢，這是需要查詢10 020條記錄然后只返回 20條，前面的10 000條記錄都將被拋棄，這樣代價太高。優(yōu)化此類分頁查詢的最簡單辦法就是盡可能地使用覆蓋索引掃描，而不是查詢所有列。對于偏移量大的時候，這樣做的效率會提升非常大。例如：

SELECT id,description FROM tab ORDER BY title LIMIT 10000,20;
--使用覆蓋索引優(yōu)化后的語句如下：
SELECT f.id,f.description FROM tab f 
INNER JOIN (SELECT id FROM tab ORDER BY title LIMIT 10000,20) t
USING(id);

這里的 “延遲關(guān)聯(lián)” 將大大提升查詢效率，它讓 MySQL 掃描盡量少的頁面，獲取需要訪問的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。這個技術(shù)也可以用于優(yōu)化關(guān)聯(lián)查詢中的 LIMIT 子句。

九、排序優(yōu)化

排序是一個成本很高的操作，所以從性能角度考慮，應(yīng)盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進(jìn)行排序。如果數(shù)據(jù)量小于 “排序緩沖區(qū)” 則在內(nèi)存中排序，如果數(shù)據(jù)量大于 “排序緩沖區(qū)” 則使用磁盤進(jìn)行排序 。MySQL 將這一過程統(tǒng)稱為 “文件排序：filesort”（前提沒有使用索引）。 MySQL 使用內(nèi)存進(jìn)行 “快速排序” 操作。如果內(nèi)存不夠排序，那么 MySQL 會先將數(shù)據(jù)分塊，對每個隊(duì)列的塊使用 “快速排序” 進(jìn)行排序，并將各個塊的排序結(jié)果存放在磁盤上，然后將各個排好序的塊進(jìn)行合并（merger），最后返回排序結(jié)果。

【1】兩次傳輸排序（舊版本使用）： 讀取行指針和需要排序的字段，對其進(jìn)行排序，然后再根據(jù)排序結(jié)果讀取所需要的數(shù)據(jù)行。需要進(jìn)行兩次傳輸，既需要從數(shù)據(jù)表中讀取兩次數(shù)據(jù)。第二次讀取數(shù)據(jù)的時候，因?yàn)槭亲x取排序列進(jìn)行排序后的所有記錄，這會產(chǎn)生大量的隨機(jī) I/O，所以兩次數(shù)據(jù)傳輸?shù)某杀痉浅８摺?/p>

【2】單次傳輸排序（新版本使用）： 先讀取排序所需要的列，然后再根據(jù)給定的列進(jìn)行排序，最后直接返回排序結(jié)果。因?yàn)椴恍枰獜臄?shù)據(jù)表中讀取兩次數(shù)據(jù)，對于I/O 密集型的應(yīng)用，這樣做的效率高了很多。另外，相比兩次傳輸排序，這個算法只需要一次順序 I/O 讀取所有的數(shù)據(jù)，而無需任何隨機(jī) I/O。缺點(diǎn)是，如果需要返回的數(shù)據(jù)非常多，非常大，會額外占用大量空間，而這些列對排序本身并沒有任何作用。很難說那個算法效率高，當(dāng)查詢需要所有列的總長度不操作參數(shù) max_length_for_sort_data 時，MySQL 使用單次傳輸排序，可以通過調(diào)整該參數(shù)來影響 MySQL 排序算法的選擇。

MySQL 在進(jìn)行文件排序的時候需要使用的臨時存儲空間可能會比想象的要大得多。在關(guān)聯(lián)查詢需要排序時，會分為兩種情況來處理這樣的文件排序。如果 ORDER BY 子句中的所有列都來自關(guān)聯(lián)的一個表，那么 MySQL 在關(guān)聯(lián)處理第一個表的時候就進(jìn)行了文件排序。使用 EXPLAN 查看時，看到 Extra 字段會有 “Using filesort” 。另一種情況是 MySQL 都會先將結(jié)果存放在一張臨時表中，然后在所有關(guān)聯(lián)都結(jié)束后，再進(jìn)行文件排序。EXPLAN 結(jié)果是 “Using temporary；Using filesort”，如果包含 LIMIT 的話，LIMIT 也會在排序之后應(yīng)用。在 MySQL5.6 之后。當(dāng)使用 LIMIT 子句時，MySQL 不會對所有結(jié)果進(jìn)行排序，而是根據(jù)實(shí)際情況，選擇拋棄不滿足條件的結(jié)果，然后進(jìn)行排序。

十、查詢狀態(tài)

在分析查詢性能的時候，對于一個 MySQL 連接來說，可以通過查看它的狀態(tài)來觀察它正在做什么。最簡單的方式是 SHOW FULL PROCESSLIST 命令，該命令返回結(jié)果中的 Command 列表示當(dāng)前的狀態(tài)。在一個查詢的生命周期中，狀態(tài)會變化多次。MySQL 官方手冊中對這些狀態(tài)值的含義有最權(quán)威的解釋，如下：

【1】Sleep： 線程正在等待客戶端發(fā)送新的請求；

【2】Query： 線程正在執(zhí)行查詢或?qū)⒔Y(jié)果發(fā)送給客戶端；

【3】Locked： 在 MySQL 服務(wù)器層，該線程正在等待表鎖。InnoDB的行鎖并不會體現(xiàn)在線程狀態(tài)中；

【4】Analyzing and statistics： 線程正在收集存儲引擎的統(tǒng)計(jì)信息，并生成查詢的執(zhí)行計(jì)劃。

【5】Copying to tmp table [on disk]： 線程正在執(zhí)行查詢，將結(jié)果都復(fù)制到一個臨時表，這種狀態(tài)一般要么再過 GROUP BY，要么是文件排序操作，或者是 UNION 操作。“on disk” 標(biāo)記，表示 MySQL 正在講一個內(nèi)存臨時表放到磁盤上。

【6】Sorting result： 線程正在對結(jié)果進(jìn)行排序；

【7】Sending data： 表示多種情況，線程可能在多種狀態(tài)之間傳送數(shù)據(jù)。

以上就是MySQL查詢性能優(yōu)化方法匯總講解的詳細(xì)內(nèi)容，更多關(guān)于MySQL查詢性能優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論