MySQL 重寫查詢語句的三種策略

Posted on by

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

復雜查詢與分步查詢

一個重要的查詢設計課題是將復雜查詢分解為多個簡單查詢是否會更好。在傳統的數據庫設計中強調盡可能地用更少的查詢解決大量工作。在過往，這種方式會更好。這是因為以前的網絡通訊成本更高以及考慮查詢解析器和優化器的負荷。

然而，這種建議並不怎麼適用於 MySQL，這是由於 MySQL 處理建立連接和斷開連接的方式十分高效，並且對簡單查詢的響應很快。當今的網絡速度相比以前也有瞭大幅度的提升。根據不同的服務端版本，MySQL 可以在普通機器上一秒內運行超過10萬次的簡單查詢，並且在千兆網絡上完成每秒2000次的查詢通訊。因此，進行分佈查詢並不是過往說的那麼糟糕。

相比於每秒遍歷的數據行數，連接響應依舊是比較慢的。在內存數據中，這個時間達到瞭毫秒級。當然，使用盡可能的查詢次數依舊是一個不錯的選擇。但是，有時我們可以通過拆分復雜查詢為幾個簡單的查詢來提高性能。接下來我們將展示一些示例。

在程序設計中，使用過多的查詢是一個常犯的錯誤。例如，有些應用執行瞭10個單獨的查詢來獲取10行數據（使用循環一條條獲取），而這本可以通過一條查詢10行數據的查詢來完成。因此，這並不是倡導每次都做查詢的拆分，而是根據實際情況來。

切分查詢語句

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

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

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

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

拆解聯合查詢

很多高性能的應用會拆解聯合查詢。可以通過將聯合查詢拆分為多個單表查詢，然後在應用中再將結果組合起來。例如：

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

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

  • 緩存機制會更有效。很多應用直接使用 ORM 映射數據表。在這個例子中，如果 tag 為 mysql 的對象已經被緩存瞭，第一條查詢就會跳過。如果 posts 中 id 為123，567或9908在緩存中，則可以從 IN 列表中移除這幾個。通過這種策略，查詢緩存會得到相應的受益。如果隻有其中的一個表經常變化，拆解聯合查詢可以減少緩存失效的次數。
  • 單獨執行這些查詢有時候可以減少鎖表的機會。
  • 通過這種方式很容易擴展數據庫，並把數據表放到不同的機器上。
  • 查詢自身可以進行優化。這個例子中，使用 IN 查詢替代聯合查詢後，MySQL 對行 ID 進行排序和獲取數據行有可能會更優。
  • 可以減少冗餘的行訪問。使用這種方式意味著隻做一次數據行獲取，而在聯合查詢中有可能重復獲取相同的數據。基於這種原因，這種拆解方式也可能會減少整個網絡負荷和內存占用。
  • 擴展一下，也可以通過人為進行哈希聯合查詢來替代MySQL聯合查詢的嵌套循環，哈希聯合查詢也可能會更有效。

最終可以看到，通過拆解聯合查詢可以使得緩存復用性更高，多服務器分佈式數據方案更簡單，並可以在大的數據表中使用 IN 查詢替代聯合查詢或同一張表的多次重復查詢。

以上就是MySQL 重寫查詢語句的三種策略的詳細內容，更多關於MySQL 重寫查詢語句的資料請關註WalkonNet其它相關文章！

推薦閱讀：