在優(yōu)化存在問題的查詢時，我們需要改變方式去獲取查詢結(jié)果——但這并不意味著從 MySQL獲取同樣的結(jié)果集。有些時候我們可以將查詢轉(zhuǎn)換為獲取相同結(jié)果，但更好性能的查詢形式。然而，我們也需要考慮重寫查詢?nèi)カ@取不同的結(jié)果，因為這樣可以提高開發(fā)效率。也可以通過修改應(yīng)用程序代碼來取得相同的效果。本篇文章將介紹如何重寫查詢的技巧。

復(fù)雜查詢與分步查詢

一個重要的查詢設(shè)計課題是將復(fù)雜查詢分解為多個簡單查詢是否會更好。在傳統(tǒng)的數(shù)據(jù)庫設(shè)計中強(qiáng)調(diào)盡可能地用更少的查詢解決大量工作。在過往，這種方式會更好。這是因為以前的網(wǎng)絡(luò)通訊成本更高以及考慮查詢解析器和優(yōu)化器的負(fù)荷。

然而，這種建議并不怎么適用于 MySQL，這是由于 MySQL 處理建立連接和斷開連接的方式十分高效，并且對簡單查詢的響應(yīng)很快。當(dāng)今的網(wǎng)絡(luò)速度相比以前也有了大幅度的提升。根據(jù)不同的服務(wù)端版本，MySQL 可以在普通機(jī)器上一秒內(nèi)運行超過10萬次的簡單查詢，并且在千兆網(wǎng)絡(luò)上完成每秒2000次的查詢通訊。因此，進(jìn)行分布查詢并不是過往說的那么糟糕。

相比于每秒遍歷的數(shù)據(jù)行數(shù)，連接響應(yīng)依舊是比較慢的。在內(nèi)存數(shù)據(jù)中，這個時間達(dá)到了毫秒級。當(dāng)然，使用盡可能的查詢次數(shù)依舊是一個不錯的選擇。但是，有時我們可以通過拆分復(fù)雜查詢?yōu)閹讉€簡單的查詢來提高性能。接下來我們將展示一些示例。

在程序設(shè)計中，使用過多的查詢是一個常犯的錯誤。例如，有些應(yīng)用執(zhí)行了10個單獨的查詢來獲取10行數(shù)據(jù)（使用循環(huán)一條條獲?。@本可以通過一條查詢10行數(shù)據(jù)的查詢來完成。因此，這并不是倡導(dǎo)每次都做查詢的拆分，而是根據(jù)實際情況來。

切分查詢語句

另一個方式是拆分查詢后重新再組合。通過在大數(shù)據(jù)量的查詢拆分為更小范圍的查詢以減少每次影響的行數(shù)。

清洗舊數(shù)據(jù)就是一個典型的例子。周期性的清洗數(shù)據(jù)工作需要移除大量數(shù)據(jù)，進(jìn)行這樣的操作會長時間鎖定大量數(shù)據(jù)行。這種操作還會產(chǎn)生事務(wù)日志、消耗大量資源并且會阻塞那些本不應(yīng)該被打斷的小數(shù)據(jù)量的查詢。將DELETE語句切分后，使用中等規(guī)模的查詢可以顯著改善性能，并且在查詢是重復(fù)的時候可以減少重復(fù)查詢產(chǎn)生的額外延遲。例如下面的刪除語句：

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

應(yīng)用的偽代碼的形式如下：

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

一次刪除10000行對于提高每次查詢的效率來說已經(jīng)是一個足夠大的任務(wù)了。一個足夠短的任務(wù)會減少對服務(wù)端的影響（事務(wù)存儲引擎會從中受益）。在 DELETE 語句中插入一些休眠時間也是一個不錯的主意，這樣可以在時間上分散負(fù)荷并且縮短持有鎖的持續(xù)時間。

拆解聯(lián)合查詢

很多高性能的應(yīng)用會拆解聯(lián)合查詢?？梢酝ㄟ^將聯(lián)合查詢拆分為多個單表查詢，然后在應(yīng)用中再將結(jié)果組合起來。例如：

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';

可以將這個聯(lián)合查詢拆分如下是哪個部分。

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);

注：這里的 tag_id=1234和post.id IN (123, 456, 567, 9098, 8904)都是基于前面查詢的結(jié)果得到的值。為什么要這么做？第一眼看過去好像是毫無必要的——增加了查詢的次數(shù)而已。然而，這種重建查詢可以帶來如下優(yōu)勢：

• 緩存機(jī)制會更有效。很多應(yīng)用直接使用 ORM 映射數(shù)據(jù)表。在這個例子中，如果 tag 為 mysql 的對象已經(jīng)被緩存了，第一條查詢就會跳過。如果 posts 中 id 為123，567或9908在緩存中，則可以從 IN 列表中移除這幾個。通過這種策略，查詢緩存會得到相應(yīng)的受益。如果只有其中的一個表經(jīng)常變化，拆解聯(lián)合查詢可以減少緩存失效的次數(shù)。
• 單獨執(zhí)行這些查詢有時候可以減少鎖表的機(jī)會。
• 通過這種方式很容易擴(kuò)展數(shù)據(jù)庫，并把數(shù)據(jù)表放到不同的機(jī)器上。
• 查詢自身可以進(jìn)行優(yōu)化。這個例子中，使用 IN 查詢替代聯(lián)合查詢后，MySQL 對行 ID 進(jìn)行排序和獲取數(shù)據(jù)行有可能會更優(yōu)。
• 可以減少冗余的行訪問。使用這種方式意味著只做一次數(shù)據(jù)行獲取，而在聯(lián)合查詢中有可能重復(fù)獲取相同的數(shù)據(jù)?；谶@種原因，這種拆解方式也可能會減少整個網(wǎng)絡(luò)負(fù)荷和內(nèi)存占用。
• 擴(kuò)展一下，也可以通過人為進(jìn)行哈希聯(lián)合查詢來替代MySQL聯(lián)合查詢的嵌套循環(huán)，哈希聯(lián)合查詢也可能會更有效。
  

最終可以看到，通過拆解聯(lián)合查詢可以使得緩存復(fù)用性更高，多服務(wù)器分布式數(shù)據(jù)方案更簡單，并可以在大的數(shù)據(jù)表中使用 IN 查詢替代聯(lián)合查詢或同一張表的多次重復(fù)查詢。

以上就是MySQL 重寫查詢語句的三種策略的詳細(xì)內(nèi)容，更多關(guān)于MySQL 重寫查詢語句的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論