Semi-Join Subquery優化策略

来源:https://www.cnblogs.com/gaogao67/archive/2023/05/06/17378214.html
-Advertisement-
Play Games

說明：本文的所有步驟不適用於生產環境，僅用於個人測試環境的快速部署和學習，下述操作過程在Oracle Linux 7.9上安裝Oracle 11.2.0.4單實例為例。 1 安裝環境檢查 安裝環境的檢查可以參考官方文檔Oracle Database Quick Installation Guide ...


Semi-Join Subquery優化策略

Semi-Join Subquery(半連接子查詢)：對應IN或EXISTS子查詢，僅需要檢查"外表記錄"在"子查詢結果集"中是否存在匹配記錄，不需要計算"子查詢結果集"中記錄匹配次數，也不需要返回"子查詢結果集"中匹配記錄內容

在MariaDB(MySQL)中，常用優化Semi-Join(半連接)的策略有：

  • First Match
  • Table Pullout
  • Semi-join Materialization
  • Loose Scan
  • Duplicate Weedout

First Match策略

當迴圈"外部查詢結果集"的每條記錄去"子查詢中"確認"是否匹配"時，只需要找到第一條匹配記錄(First Match)既可跳出子查詢。

如下麵查詢：

SELECT * FROM Country 
WHERE Country.code IN (
    SELECT City.Country 
    FROM City 
    WHERE City.Population > 1*1000*1000
)
AND Country.continent='Europe'

如果不使用First Match策略，當處理到Country表上滿足" Country.continent='Europe' "條件的德國(Deu)記錄時，會掃描City表上滿足" City.Population > 110001000 AND City.Country='DEU' "的所有記錄，再根據匹配記錄總數返回"是否匹配"結果：

如果使用First Match策略，當處理到Country表上滿足" Country.continent='Europe' "條件的德國(Deu)記錄時，會掃描City表上滿足" City.Population > 110001000 AND City.Country='DEU' "的第一條記錄"Berlin"後，立即返回"是否匹配"結果：

在MariaDB上使用First Match策略的查詢的執行計劃為：

MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe';
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
| id | select_type | table   | type | possible_keys      | key       | key_len | ref                | rows | Extra                            |
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
|  1 | PRIMARY     | Country | ref  | PRIMARY,continent  | continent | 17      | const              |   60 | Using index condition            |
|  1 | PRIMARY     | City    | ref  | Population,Country | Country   | 3       | world.Country.Code |   18 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
2 rows in set (0.00 sec)

MariaDB的執行計劃中會有明顯的FirstMatch標識。

在MySQL上使用First Match策略的查詢的執行計劃為：

MySQL [world]> explain select * from Country  where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe';
+----+--------------------+---------+----------------+--------------------+-----------+---------+-------+------+------------------------------------+
| id | select_type        | table   | type           | possible_keys      | key       | key_len | ref   | rows | Extra                              |
+----+--------------------+---------+----------------+--------------------+-----------+---------+-------+------+------------------------------------+
|  1 | PRIMARY            | Country | ref            | continent          | continent | 17      | const |   60 | Using index condition; Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | Population,Country | Country   | 3       | func  |   18 | Using where                        |
+----+--------------------+---------+----------------+--------------------+-----------+---------+-------+------+------------------------------------+
2 rows in set (0.01 sec)

MariaDB的執行計劃中僅顯示為依賴子查詢(DEPENDENT SUBQUERY)

First Match策略和將IN子查詢轉換為EXISTS依賴子查詢很相似，但兩者還是存在明顯差異，並非所有EXISTS操作都能使用First Match策略，如子查詢中使用GROUP BY相關的聚合函數時，需要先完成GROUP BY操作才能確認"是否匹配"。

Table Pullout策略

當子查詢的查詢列表項只有主鍵或唯一索引鍵時，能推算出"子查詢結果集"不存在重覆記錄，因此可以將子查詢改為關聯查詢，即將子查詢中的表上提到關聯查詢。

對於查詢：

SELECT *
FROM City 
WHERE City.Country IN (
	SELECT Country.Code
	FROM Country 
	WHERE Country.Population < 100*1000
);

在MariaDB 5.2 和MySQL 5.6版本及之前版本上，執行計劃為：

MySQL [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
| id | select_type        | table   | type            | possible_keys      | key     | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | City    | ALL             | NULL               | NULL    | NULL    | NULL | 4079 | Using where |
|  2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY,Population | PRIMARY | 3       | func |    1 | Using where |
+----+--------------------+---------+-----------------+--------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

如果Country.Code是主鍵或唯一索引，則可以將SQL改寫為：

SELECT City.* 
FROM City
INNER JOIN Country 
ON City.Country=Country.Code
WHERE Country.Population < 100*1000;

改為關聯查詢後，可以根據兩張關聯表的統計數據來選擇驅動表和被驅動表，因此在MariaDB 5.3或MySQL 5.7版本，執行計劃為：

MariaDB [world]> explain select * from City where City.Country in (select Country.Code from Country where Country.Population < 100*1000);
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type | table   | type  | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY     | Country | range | PRIMARY,Population | Population | 4       | NULL               |   37 | Using index condition |
|  1 | PRIMARY     | City    | ref   | Country            | Country    | 3       | world.Country.Code |   18 |                       |
+----+-------------+---------+-------+--------------------+------------+---------+--------------------+------+-----------------------+
2 rows in set (0.00 sec)

Materialization策略

在使用Table Pullout策略時，需要能明確推算出"子查詢結果集"不存在重覆記錄時才能將"子查詢"改為"關聯查詢"，如果將"子查詢結果集"通過臨時表去重固化後消除重覆記錄，則可以將子查詢轉換為"關聯查詢"，即Materialization策略。

如對於查詢：

SELECT * FROM Country 
WHERE Country.code IN (
    SELECT City.Country 
    FROM City 
    WHERE City.Population > 1*1000*1000
)
AND Country.continent='Europe'

在轉換為"關聯查詢"後，按照"關聯查詢"中臨時表是否為"驅動表"可以將Semi-join Materialization策略細分為：

  • Materialization/scan 策略，將臨時表作為"驅動表"，遍歷臨時表中每條記錄去另外關聯表中查找匹配記錄。
  • Materialization/lookup 策略，將臨時表作為"被驅動表"，遍歷另外的關聯表在臨時表中查詢匹配記錄。

使用Materialization/scan 策略時，MariaDB 查詢計劃為：

MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where  City.Population > 7*1000*1000);
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY      | <subquery2> | ALL    | distinct_key       | NULL       | NULL    | NULL               |   15 |                       |
|  1 | PRIMARY      | Country     | eq_ref | PRIMARY            | PRIMARY    | 3       | world.City.Country |    1 |                       |
|  2 | MATERIALIZED | City        | range  | Population,Country | Population | 4       | NULL               |   15 | Using index condition |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
3 rows in set (0.01 sec)

使用Materialization/lookup 策略時，MariaDB 查詢計劃為：

MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where  City.Population > 1*1000*1000) ;
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys      | key          | key_len | ref  | rows | Extra                 |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
|  1 | PRIMARY      | Country     | ALL    | PRIMARY            | NULL         | NULL    | NULL |  239 |                       |
|  1 | PRIMARY      | <subquery2> | eq_ref | distinct_key       | distinct_key | 3       | func |    1 |                       |
|  2 | MATERIALIZED | City        | range  | Population,Country | Population   | 4       | NULL |  238 | Using index condition |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
3 rows in set (0.00 sec)

Loose Scan策略

在Materialization/scan 策略時，需要先將"子查詢結果集"移除重覆記錄並固化到臨時表，再作為驅動表進行關聯查詢。MySQL特性Index Loose Scan能在一次掃描中得跳過重覆索引鍵得到"沒有重覆記錄的臨時結果集"，Loose Scan策略基於Index Loose Scan特性保證關聯查詢不會出現"重覆關聯問題"。

如對於查詢：

SELECT * FROM Country  
WHERE Country.code IN (
    SELECT country_code FROM Satellite
)

如果Satellite.country_code 存在索引，基於Index Loose Scan特性則能快速獲得"SELECT DISTINCT country_code FROM Satellite"的效果，如圖所示：

使用Loose Scan 策略時，MariaDB 查詢計劃為：

MariaDB [world]> explain select * from Country where Country.code in (select country_code from Satellite);
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+
| id | select_type | table     | type   | possible_keys | key          | key_len | ref                          | rows | Extra                               |
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+
|  1 | PRIMARY     | Satellite | index  | country_code  | country_code | 9       | NULL                         |  932 | Using where; Using index; LooseScan |
|  1 | PRIMARY     | Country   | eq_ref | PRIMARY       | PRIMARY      | 3       | world.Satellite.country_code |    1 | Using index condition               |
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+

Loose Scan 策略和Materialization/scan 策略區別：

  • Materialization/scan 策略：先將子查詢的查詢結果固化去重後，再作為驅動表與外部表進行關聯查詢，查詢使用到臨時表。
  • Loose Scan 策略：在對子查詢的表進行Index Loose Scan操作過程中，直接將遍歷到的記錄與與外部表進行關聯查詢，查詢未使用到臨時表。

Duplicate Weedout策略

當無法根據表結構信息推算出"子查詢結果集"不存在重覆記錄時，如果將子查詢改寫為關聯查詢，則會導致"外表記錄"被關聯匹配多次而產生重覆記錄，可以通過將關聯結果集插入到"帶有唯一索引的臨時表"的方式來移除重覆記錄，保證最終查詢結果的準確性。

對於查詢：

SELECT * 
FROM Country 
WHERE Country.code IN (
    SELECT City.Country
    FROM City 
    WHERE City.Population > 0.33 * Country.Population 
    AND City.Population > 1*1000*1000
);

可以改寫為：

CREATE tmp_Country LIKE Country;

INSERT IGNORE INTO tmp_Country
SELECT Country.* 
FROM Country
INNER JOIN City
ON Country.code = City.Country
WHERE City.Population > 0.33 * Country.Population 
AND City.Population > 1*1000*1000

SELECT * FROM tmp_Country;

如圖所示：

使用Duplicate Weedout 策略時，MariaDB 查詢計劃為：

explain select * from Country where Country.code IN (select City.Country from City where City.Population > 0.33 * Country.Population and City.Population > 1*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
         type: range
possible_keys: Population,Country
          key: Population
      key_len: 4
          ref: NULL
         rows: 238
        Extra: Using index condition; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.City.Country
         rows: 1
        Extra: Using where; End temporary
2 rows in set (0.00 sec)

學習總結

First Match策略通過"找到第一條匹配記錄即返回"的方式來跳過無效子查詢掃描。

除First Match策略外都是子查詢轉換為關聯查詢來優化提升查詢效率，按照不同查詢場景採用不同策略來"避免重覆記錄"：

  • Table Pullout策略，通過唯一索引和主鍵索引邏輯來確認"子查詢結果集"中重覆記錄。
  • Materialization策略，通過臨時表來移除"子查詢結果集"中重覆記錄。
  • Loose Scan策略，通過Index Loose Scan特性來跳過"子查詢結果集"中重覆記錄。
  • Duplicate Weedout策略，通過臨時表來將移除"關聯查詢結果集"中重覆記錄。

參考資料

Semi-join Subquery Optimizations


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

-Advertisement-
Play Games
更多相關文章
  • AutoCAD二次開發系列教程01-如何在AutoCAD中輸出Hello World
    目錄 01項目環境準備 02代碼示例 03輸出示例 04總結 05源碼地址 01項目環境準備 A.開發使用的軟體:AutoCAD2016、VisualStudio2022 B.建立依賴的本地庫(提前從AutoCAD軟體安裝目錄拷貝開發時需要使用的庫，如：accoremgd.dll) C.創建新的類庫 ...
  • .net中最簡單的http請求調用(比如調用chatgpt的openAI介面)
    支持.Net Core(2.0及以上)/.Net Framework(4.5及以上)，可以部署在Docker, Windows, Linux, Mac。 http請求調用是開發中經常會用到的功能，因為，很多第三方功能介面往往是通過http地址的形式提供的，比如：ChatGpt、OpenAI、簡訊服務 ...
  • cryptohack wp day（1）
    就從頭開始吧 第一題 （ASCII) 一道簡單的ASCII碼轉換，直接用題目的提示代碼解就行了 ascii=[99, 114, 121, 112, 116, 111, 123, 65, 83, 67, 73, 73, 95, 112, 114, 49, 110, 116, 52, 98, 108, ...
  • abp(net core)+easyui+efcore實現倉儲管理系統——供應商管理升級之上(六十三)
    最近小編思維發散“Visual Studio可以集成chatgpt嗎？”，這樣不就可以讓chatgpt幫你寫代碼了嗎？尋覓了一圈，還真有這個東西，那就是一個Visual Studio的擴展插件：Visual chatGPT Studio，雖然不是官方的，部分功能也可以值得一用。本文將介紹Visual ...
  • Linux下七種文件類型、文件屬性及其查看方法
    1、七種文件類型 普通文件類型 Linux中最多的一種文件類型, 包括 純文本文件(ASCII)；二進位文件(binary)；數據格式的文件(data);各種壓縮文件.第一個屬性為 [-] 目錄文件 就是目錄， 能用 # cd 命令進入的。第一個屬性為 [d]，例如 [drwxrwxrwx] 塊設備 ...
  • Mac M系列晶元 vue前端node-sass相容問題解決
    0、由於M系列晶元是arm架構，在使用brew安裝node時都是arm的node，但是[email protected]版本中不支持arm架構的出現如下報錯： Error: Node Sass does not yet support your current environment: OS X Un ...
  • 2、etcd單機部署和集群部署
    上一章我們認識了etcd，簡單的介紹了 etcd 的基礎概念，但是理解起來還是比較抽象的。這一章我們就一起來部署下 etcd 。這樣可以讓我們對 etcd 有更加確切的認識。 1、etcd單實例部署 對於平常的學習，其實搭建一個單機節點是夠了的。接下來就講講怎麼搭建單機節點。 本次部署是在 cent ...
  • ☆常用的Sql語句彙總（DDL/DML）
    常用的sql語句彙總 1、獲取所有表名、表信息 裡面有表註釋 | 資料庫種類 | sql | 備註 | | | | | | mysql | -- 獲取所有表名、視圖名show tables-- 獲取 dev_test_data資料庫 所有表、視圖信息select * from information ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類