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

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

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

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

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

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

切分查詢語句

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

清洗舊數(shù)據(jù)就是一個(gè)典型的例子。周期性的清洗數(shù)據(jù)工作需要移除大量數(shù)據(jù)，進(jìn)行這樣的操作會(huì)長(zhǎng)時(shí)間鎖定大量數(shù)據(jù)行。這種操作還會(huì)產(chǎn)生事務(wù)日志、消耗大量資源并且會(huì)阻塞那些本不應(yīng)該被打斷的小數(shù)據(jù)量的查詢。將DELETE語句切分后，使用中等規(guī)模的查詢可以顯著改善性能，并且在查詢是重復(fù)的時(shí)候可以減少重復(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行對(duì)于提高每次查詢的效率來說已經(jīng)是一個(gè)足夠大的任務(wù)了。一個(gè)足夠短的任務(wù)會(huì)減少對(duì)服務(wù)端的影響（事務(wù)存儲(chǔ)引擎會(huì)從中受益）。在 DELETE 語句中插入一些休眠時(shí)間也是一個(gè)不錯(cuò)的主意，這樣可以在時(shí)間上分散負(fù)荷并且縮短持有鎖的持續(xù)時(shí)間。

拆解聯(lián)合查詢

很多高性能的應(yīng)用會(huì)拆解聯(lián)合查詢?？梢酝ㄟ^將聯(lián)合查詢拆分為多個(gè)單表查詢，然后在應(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';

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

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)勢(shì)：

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

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

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