MySQL中WHERE後跟著N多個OR條件會怎樣...

来源:https://www.cnblogs.com/greatsql/archive/2022/12/19/16991436.html
-Advertisement-
Play Games

GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。 作者:葉金榮 文章來源:社區原創 可能會執行非常慢,線上生產環境千萬別寫出這種SQL ... 背景交代 用 tpcc-mysql 工具生成 50個倉庫 ...


  • GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。
  • GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。
  • 作者:葉金榮
  • 文章來源:社區原創

可能會執行非常慢,線上生產環境千萬別寫出這種SQL ...

背景交代

tpcc-mysql 工具生成 50個倉庫 的測試數據,表 order_line 共有 37970973 條記錄。

某工具在運行過程中,會產生下麵的SQL進行查詢,WHERE後跟了N多個條件:

mysql> select * from order_line where 
   (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2221' and ol_number = '5') 
or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2225' and ol_number = '1')
or (ol_w_id = '1' and ol_d_id = '1' and ol_o_id = '2155' and ol_number = '2')
...

這裡說的N多個,是指總共有10000個OR條件,這條SQL的長度大概將近800KB。

這條SQL在我的測試伺服器上,運行了約56秒(另一個性能略差的機器上跑了1800秒左右才完成),共掃描75563行記錄,返回8192行結果:

# Query_time: 56.031955  Lock_time: 0.047795 Rows_sent: 8129  Rows_examined: 75563 ... Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 75563 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...
...
#   InnoDB_pages_distinct: 501
...
select * from order_line where ...

相當於只做了1次索引範圍查詢,但總共要掃描7.5萬條數據

問題分析

只需要掃描 7.5萬行記錄,501個page,返回8192行結果,正常情況下不應該需要這麼久才對,肯定是哪裡有問題。

再次手動執行這條SQL,發現的確是這麼慢,並且在最後還有個 warnings 提醒,查看下是啥內容:

mysql> show warnings\G
...
  Level: Warning
   Code: 3170
Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

第一次見到這種告警,先檢查MySQL手冊,看看 range_optimizer_max_mem_size 這個選項是幹嘛用的:

文檔出處:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size

The limit on memory consumption for the range optimizer. A value of 0 means “no limit.” 
If an execution plan considered by the optimizer uses the range access method but 
the optimizer estimates that the amount of memory needed for this method would 
exceed the limit, it abandons the plan and considers other plans. For more 
information, see Limiting Memory Use for Range Optimization.

這個選項是從MySQL 5.7.9開始引入的,用於控制當優化器採用範圍(RANGE)查詢優化方案時使用的記憶體消耗限制

其預設值為8MB(5.7.12及以上版本),當設置為0時,表示不做任何限制。當WHERE查詢條件里有很多OR、AND組成時,優化器判斷超過記憶體消耗限制,則會調整SQL執行計劃,變成其他執行方案,甚至可能是全表掃描

這也就是為什麼執行上面的大SQL後,MySQL會有這樣的告警提示了。

經過幾次簡單嘗試,把 range_optimizer_max_mem_size 選項值調大到 24MB 後,這個SQL就可以正常執行,並且運行速度很快:

# Query_time: 6.721209  Lock_time: 0.044637 Rows_sent: 8129  Rows_examined: 8129 Read_first: 0 Read_last: 0 Read_key: 10000 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 ...
...
#   InnoDB_pages_distinct: 81

註意到幾個變化:

  • 耗時從56秒降到6.7秒;
  • 掃描行數從7.5萬行降到8192行(返回結果數不變);
  • Read_key從1增加到10000;
  • Read_next從75563降到0;
  • 掃描的page數從501降到81。

相當於做了1萬次索引列等值條件查詢

查詢效率提升非常顯著。

進一步優化

線上生產環境中,各式各樣的SQL層出不窮,這次可能是一萬條OR條件,下次可能是其他的,是不能無限度增加資料庫記憶體消耗的。

針對本案中的SQL,更好的優化辦法是找出這些OR條件的範圍規律,並改寫成一條更簡單的SQL,類似下麵這樣:

mysql> select * from order_line where
ol_w_id = 1 and ol_d_id = 1 and (ol_o_id between 2007 and 2997) 
and (ol_number between 1 and 15 );

新的SQL執行代價:

# Query_time: 0.006338  Lock_time: 0.000084 Rows_sent: 9883  Rows_examined: 9883...Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 9883 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0...
...
#   InnoDB_pages_distinct: 81

相當於只做了1次索引範圍查詢,且只需掃描9883條記錄

相比上面調高記憶體上限的優化方案,本次的做法則更為徹底,耗時從6.7秒直接降為6.3毫秒,提升了1000倍;掃描行數、次數和page數也下降了很多。

不過要註意的是,改寫後的SQL查詢結果和原來並不是完全一致的,實際應用中,可能還要再做進一步篩選或者增加 LIMIT N 來控制。

最後再次提醒,WHERE條件後跟著N多個OR/AND條件的寫法非常不可取,尤其是在用一些開發框架構造查詢SQL時,尤其要註意規避這個問題,否則可能造成嚴重性能問題。

延伸閱讀


Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Kotlin + SpringBoot + JPA 服務端開發 本篇主要介紹一下 kotlin + springboot的服務端開發環境搭建 1.概述 Kotlin 是一個基於JVM的編程語言, 是IDEA開發工具 jetbrains 公司開發的語言,也被google選為android開發的首選語言 ...
  • 對於我們.Net程式員,System.Web.Abstractions我們都非常熟悉,主要作用於Web可以實現單元測試,他是在.Net framework 3.5 sp1開始引入的,很好的解決項目表示層不好做單元測試的問題,這個庫所有類都是Wrapper/Decorator模式的。今天給推薦一個IO ...
  • 前言 在前三章中我們的案例大量使用到了Thread這個類,通過其原始API,對其進行創建、啟動、中斷、中斷、終止、取消以及異常處理,這樣的寫法不僅不夠優雅(對接下來這篇,我稱其為.NET現代化並行編程體系中出現的API而言),並且大部分的介面都是極度複雜和危險的。很幸運,如今.NET已經提供,並且普 ...
  • Azure App Service 提供了一個應用內的MySQL,可以供測試、開發使用。 前提條件是需要使用Windows的操作系統。 創建完App Server 之後,只需要在設置下開啟 MySQL In App 即可。 在應用中可以從環境變數中獲取連接字元串。 var mysqlconnstr ...
  • 作者:鄭龍飛 範式定義 百度百科:設計關係資料庫時,遵從不同的規範要求,設計出合理的關係型資料庫,這些不同的規範要求被稱為不同的範式,各種範式呈遞次規範,越高的範式資料庫冗餘越小。 人類語言: 範式可以理解為設計一張數據表的表結構,符合的標準級別、規範和要求。 而通常我們用的最多的就是第一範式(1N ...
  • 摘要:GaussDB(DWS)提供了資源管理功能,用戶可以根據自身業務情況對資源進行劃分,將資源按需劃分成不同的資源池,不同資源池之間資源互相隔離。 本文分享自華為雲社區《GaussDB(DWS)資源管理排隊原理與問題定位》,作者: 門前一棵葡萄樹 。 一、記憶體管控原理 GaussDB(DWS)提供 ...
  • Redis——02 前面瞭解了 Redis 以及在 Linux 的安裝,下麵瞭解一些 Redis 常用的命令。 Redis 常用命令: Redis 是 Key-Value 形式,Key 為字元串類型,而 Value 的取值類型如下: String 字元串 Hash 哈希表 List 列表 Set 集 ...
  • 摘要:以下提供的都是各個資料庫較為官方的jar包獲取方式。 本文分享自華為雲社區《JDBC連接相關jar包獲取及上傳管理中心白名單處理》,作者:HuaWei XYe。 jar包獲取 以下提供的都是各個資料庫較為官方的jar包獲取方式 1、Mysql https://dev.mysql.com/dow ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...