查詢性能優(yōu)化

在之前的文章中，我們介紹了如何設(shè)計最優(yōu)的庫表結(jié)構(gòu)，如何建立最好的索引，這些對于高性能來說必不可少。但是這些還不夠—你還需要設(shè)計合理的查詢。如果查詢寫的很糟糕，即使庫表結(jié)構(gòu)再合理，索引再合適，也無法實現(xiàn)高性能。

為什么查詢速度會慢

我們在想寫一個快速的查詢之前需要明白一個問題，真正重要的是響應(yīng)時間。如果把查詢看作是一個任務(wù)，那么它是由一系列子任務(wù)組成，每個子任務(wù)都會消耗一定的時間。如果想要優(yōu)化查詢，就需要優(yōu)化其子任務(wù)，要么你就消除其中的一些子任務(wù)，要么就減少子任務(wù)的執(zhí)行次數(shù)，要么就讓子任務(wù)運行的更快。

通常來說呀，查詢的生命周期大致可以按照順序來看：從客戶端，到服務(wù)器，然后在服務(wù)器上解析，生成執(zhí)行計劃，執(zhí)行，并返回結(jié)果給客戶端。

其中 執(zhí)行 可以認為是整個生命周期最重要的階段，其中包括了大量為了檢索數(shù)據(jù)到存儲引擎的調(diào)用以及調(diào)用后的數(shù)據(jù)處理，包括排序，分組等。

在完成這些任務(wù)的時候，查詢需要在不同的地方花費時間，包括網(wǎng)絡(luò)，CPU計算，生成統(tǒng)計信息和執(zhí)行計劃，鎖等待等操作。

尤其是向底層存儲引擎檢索數(shù)據(jù)的調(diào)用操作，這些調(diào)用需要在內(nèi)存操作，CPU操作和內(nèi)存不足時導(dǎo)致IO操作上消耗時間。根據(jù)存儲引擎不同，可能還會產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用。

下面我們就來看看如何優(yōu)化 查詢。

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

查詢性能低下的最基本的原因是訪問的數(shù)據(jù)太多。某些查詢可能不可避免的需要篩選大量數(shù)據(jù)，但這并不常見。

對于一些低效的查詢，我們通常可以使用下面兩個步驟來分析：

1. 確認用用程序是否在檢索大量超過你需要的數(shù)據(jù)。這通常意味著訪問太多行，但有時候也可能是訪問了太多列。
2. 確認MySQL服務(wù)器是否在分析大量超過需要的數(shù)據(jù)行 是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

有些查詢會請求超過實際需要 的數(shù)據(jù)，然后這些多余的數(shù)據(jù)會被應(yīng)用程序丟棄。這就會帶來一些額外的很多負擔(dān)，并增加網(wǎng)絡(luò)開銷。也會消耗應(yīng)用服務(wù)器的cpu和內(nèi)存資源。

這里有一些經(jīng)典案例：

• 查詢不需要的記錄

很多人會以為MySQL只會返回需要的數(shù)據(jù)，實際上MySQL卻是先返回全部結(jié)果集再進行計算。一些開發(fā)者會先使用select語句查詢大量的結(jié)果，然后獲取前面的N行后關(guān)閉結(jié)果集。

你以為mysql只返回了你需要的前幾條信息，實際上MySQL是返回了全部結(jié)果集，然后丟棄了大部分的數(shù)據(jù)。最簡單有效的解決方法就是加limit。

• 多表關(guān)聯(lián)時返回全部列

比如說你想查詢電影FLY 中出現(xiàn)的演員，你可千萬千萬不要像下面這樣寫：

select * FROM actor
inner join  film_actor using(actor_id)
inner join  film using(film_id)
where film.title = 'FLY';

你這樣寫就把三個表的全部數(shù)據(jù)列都返回了，正確的方式是下面這么寫，只取需要的列：

select actor.* FROM actor
inner join  film_actor using(actor_id)
inner join  film using(film_id)
where film.title = 'FLY';

• 總是取出全部列

每次看到**SELECT ***的時候都需要仔細想想，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，會讓優(yōu)化器無法完成索引覆蓋掃描這類優(yōu)化， 還會為服務(wù)器帶來額外的I/O、內(nèi)存和CPU的消耗。因此，一些DBA是嚴格禁止 SELECT *的寫法的，這樣做有時候還能避免某些列被修改帶來的問題。何樂而不為呢？

當(dāng)然，你話不能說死，查詢返回查過需要的數(shù)據(jù)也不總是壞事。因為這種有點浪費數(shù)據(jù)庫資源的方式是可以簡化開發(fā)的，因為它能提高相同代碼片段的復(fù)用性。

• 重復(fù)查詢相同的數(shù)據(jù)

你比如說，一個用戶多次評論的時候，你每次都要查詢它的id，這就很不好，我們呢可以采取的一種方案是，初次查詢的時候就將這個數(shù)據(jù)緩存起來，需要的時候從緩存中取出來，這樣性能顯然會更好。

MySQL是否在掃描額外的記錄

我們上面講的是確定查詢只返回需要的數(shù)據(jù)，那么我們還要關(guān)注什么呢？

我們要去研究為了返回這個結(jié)果，有沒有掃描過多的數(shù)據(jù)這一現(xiàn)象。

對于mysql，最簡單的三個衡量查詢開銷的指標就下面這三哥們：

• 響應(yīng)時間
• 掃描的行數(shù)
• 返回的行數(shù)

當(dāng)然，沒有哪個指標能夠完美的說衡量出查詢的開銷，你只能通過這三指標去權(quán)衡罷了。

這三個指標都會記錄到MySQL的慢日志中去，所以檢查慢日志是找出掃描行數(shù)過多的查詢的好辦法。

1.響應(yīng)時間

響應(yīng)時間是兩個部分之和：服務(wù)時間和排隊時間。服務(wù)時間是指數(shù)據(jù)庫處理這個査詢真正花了多長時間。

排隊時間是指服務(wù)器因為等待某些資源而沒有真正執(zhí)行査詢，在那等資源所消耗的時間——可能是等I/O操作完成，也可能是等待行鎖之類的。

但是上面所說的這些情況在實際情況下是更加復(fù)雜的情況，所以響應(yīng)時間是沒有什么一致的規(guī)律或者公式的。我們只能算個大致的時間然后去判斷是不是一個合理的值。

2.掃描的行數(shù)和返回的行數(shù)

分析査詢時，査看該査詢掃描的行數(shù)是非常有幫助的。這在一定程度上能夠說明該查詢找到需要的數(shù)據(jù)的效率高不高。

當(dāng)然，這個指標可能不夠完美，因為并不是所有的行的訪問代價都是相同的。較短的行的訪問速度更快，內(nèi)存中的行也比磁盤中的行的訪問速度要快得多。

3.掃描的行數(shù)和訪問類型

在評估查詢的開銷的時候，我們還需要考慮一下從表中找到一行數(shù)據(jù)的成本。因為MySQL有好幾種訪問方式可以査找并返回你想要的一行結(jié)果。

有些訪問方式可能需要掃描很多行才能返回一行 結(jié)果，但是也有些訪問方式可能無須掃描就能返回結(jié)果。

在EXPLAIN語句中的type列可以體現(xiàn)出你的訪問類型。訪問類型有很多種，如全表掃描，索引掃描，范圍掃描，唯一索引査詢，常數(shù)引用等。

這些訪問的速度是從慢到快的，掃描的行數(shù)也是從小到大。當(dāng)然我們是不需要記住這些訪問類型。

如果你的查詢沒有辦法找到合適的訪問類型，俺么最好的解決方法就是增加一個合適的索引，這我們之前文章已經(jīng)介紹了。

為什么索引歲查詢性能的優(yōu)化這么重要呢？索引讓MySQL以最高效，掃描行數(shù)最少的方式找到你想要的結(jié)果。

一般MySQL能夠使用如下的三種方式應(yīng)用where條件，也是從好到壞的排序：

• 在索引中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
• 使用索引覆蓋掃描（在Extra列中出現(xiàn)了 Using index）來返回記錄，直接從索引中 過濾不需要的記錄并返回命中的結(jié)果。這是在MySQL服務(wù)器層完成的，但無須再回表查詢記錄。
• 從數(shù)據(jù)表中返回數(shù)據(jù)，然后過濾不滿足條件的記錄（在Extra列中出現(xiàn)Using Where）這在MySQL服務(wù)器層完成，MySQL需要先從數(shù)據(jù)表讀出記錄然后過濾。這就很慢了，很糟糕了。

如果我們發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但是只是返回少數(shù)的行，那么我們通?？梢試L試這些策略技巧去優(yōu)化：

• 使用索引覆蓋掃描，把所有需要用到的列都放到索引中。
• 改變庫表結(jié)構(gòu)。例如使用單獨的匯總表
• 重寫這個復(fù)雜的査詢，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個査詢。

重構(gòu)查詢的方式

就是我們之前提到的一種優(yōu)化方式，我們的SQL查詢太慢，有時候是因為我們寫的這個SQL太糟糕了。我們需要換一種方式去寫SQL，但還是要返回一樣的結(jié)果。

一個復(fù)雜的查詢還是多個簡單的查詢

就如標題所言，我們在設(shè)計查詢的時候，需要考慮的一個重要問題是：是否需要將一個復(fù)雜的查詢變?yōu)槎鄠€簡單的查詢。

切分查詢

有時候啊，我們需要把一個大查詢“分而治之”，將大查詢變?yōu)樾〔樵?，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分的結(jié)果。

你比如說，想要刪除舊的數(shù)據(jù)，，如果說你用一個大的語句一次性完成的話 ，則可能需要一次性鎖住很多數(shù)據(jù)，占滿整個事務(wù)日志，耗盡系統(tǒng)資源，阻塞很多小的，但是重要的查詢。所以將一個大的delete語句切分為多個較小的查詢可以盡可能小地影響MySQL性能，同時還可以減少MySQL復(fù)制的延遲。

你比如說下面這個例子：

DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

我們就可以使用下面同樣的方法來解決：

rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000")	
} while rows_affected > 0

我們通過上面的語句，就實現(xiàn)了一次刪除啊10000行數(shù)據(jù)這樣一個限制，一次刪除10000行數(shù)據(jù)是一個比較高效而且對服務(wù)器影響也是最小的做法。如果，每次刪除數(shù)據(jù)后，都暫停一會再做下一次刪除，這樣也可以將服務(wù)器上原本一次性的壓力分散到一個很長的時間段，也就大大的降低了對服務(wù)器的影響。

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

很多高性能的應(yīng)用都會對關(guān)聯(lián)查詢進行分解。簡而言之就是，可以對每一個表進行一次單表查詢，然后將結(jié)果在應(yīng)用程序中進行關(guān)聯(lián)。

你比如說說下面這個查詢。

SELECT * FROM tag
JOIN tag_post ON tag^post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

我們可以分解成這樣的查詢來代替：

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

返回的結(jié)果一模一樣，那么這樣做有什么好處呢？

• 讓緩存的效率更高。許多應(yīng)用程序可以方便的緩存單表查詢對應(yīng)的結(jié)果對象。你比如說呀，如果第一行的tag已經(jīng)被緩存了，那么應(yīng)用就可以跳過第一個查詢了，
• 我們分解之后，執(zhí)行單個查詢可以減少鎖的競爭
• 在應(yīng)用層做關(guān)聯(lián)，可以更加容易對數(shù)據(jù)庫進行拆分，更加容易做到高性能和可擴展
• 查詢本身的效率也會提升，我們使用in代替關(guān)聯(lián)查詢，這比隨機的關(guān)聯(lián)是要更加高效的。
• 可以減少冗余記錄的查詢。在應(yīng)用層做關(guān)聯(lián)査詢，意味著對于某條記錄應(yīng)用只需要査詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)査詢，則可能需要重復(fù)地訪問一部分數(shù)據(jù)。從這點看，這樣的重構(gòu)還可能會減少網(wǎng)絡(luò)和內(nèi)存的消耗。

到此這篇關(guān)于深入解讀Mysql查詢性能的優(yōu)化的文章就介紹到這了,更多相關(guān)Mysql查詢性能優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論