查詢性能優(yōu)化

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

為什么查詢速度會(huì)慢

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

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

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

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

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

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

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

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

對(duì)于一些低效的查詢，我們通?？梢允褂孟旅鎯蓚€(gè)步驟來分析：

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

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

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

• 查詢不需要的記錄

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

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

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

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

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

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

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

• 總是取出全部列

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

當(dāng)然，你話不能說死，查詢返回查過需要的數(shù)據(jù)也不總是壞事。因?yàn)檫@種有點(diǎn)浪費(fèi)數(shù)據(jù)庫資源的方式是可以簡(jiǎn)化開發(fā)的，因?yàn)樗芴岣呦嗤a片段的復(fù)用性。

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

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

MySQL是否在掃描額外的記錄

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

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

對(duì)于mysql，最簡(jiǎn)單的三個(gè)衡量查詢開銷的指標(biāo)就下面這三哥們：

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

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

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

1.響應(yīng)時(shí)間

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

排隊(duì)時(shí)間是指服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行査詢，在那等資源所消耗的時(shí)間——可能是等I/O操作完成，也可能是等待行鎖之類的。

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

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

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

當(dāng)然，這個(gè)指標(biāo)可能不夠完美，因?yàn)椴⒉皇撬械男械脑L問代價(jià)都是相同的。較短的行的訪問速度更快，內(nèi)存中的行也比磁盤中的行的訪問速度要快得多。

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

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

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

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

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

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

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

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

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

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

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

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

就是我們之前提到的一種優(yōu)化方式，我們的SQL查詢太慢，有時(shí)候是因?yàn)槲覀儗懙倪@個(gè)SQL太糟糕了。我們需要換一種方式去寫SQL，但還是要返回一樣的結(jié)果。

一個(gè)復(fù)雜的查詢還是多個(gè)簡(jiǎn)單的查詢

就如標(biāo)題所言，我們?cè)谠O(shè)計(jì)查詢的時(shí)候，需要考慮的一個(gè)重要問題是：是否需要將一個(gè)復(fù)雜的查詢變?yōu)槎鄠€(gè)簡(jiǎn)單的查詢。

切分查詢

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

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

你比如說下面這個(gè)例子：

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

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

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

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

你比如說說下面這個(gè)查詢。

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

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

最新評(píng)論