優化09-表連接

一、表連接介紹

表連接類型

表連接是關系型數據庫關鍵特性，在關系型數據庫中，表連接分為三類：循環嵌套連接（Nested Loops Join）、哈希連接（Hash Join）、合并排序連接（Merge Sort Join）。假設存在表A和表B,都存在ID列，通過id列連接。

Nested Loops Join：遍歷A中所有id，依次拿id和表B中的id對比。

Hash Join：對A中的id做HASH運算，放入多個HASH Bucket中，根據B表的id列的hash值和HASH Bucket匹配。

Merge Sort Join：對A表和B表的id列進行排序，按照排序結果進行連接。

表連接分析

表的訪問次數，由執行計劃的starts列來表示

  • NL連接驅動表被訪問0次或1次，被驅動表被訪問0次或N次，N的值取決于驅動表的返回行數。
  • HASH連接，驅動表和被驅動表都是被訪問0次或1次，大部分場景是驅動表和被驅動表都訪問1次。
  • MS連接，驅動表和被驅動表都是被訪問0次或1次，大部分場景是驅動表和被驅動表都訪問1次。

驅動順序

  • NL連接的性能與驅動順序有關，一般小表做驅動表，性能更好。
  • HASH連接的性能和驅動順序有關，一般小表做驅動表。
  • MS連接的性能和驅動順序無關。

是否排序

  • NL連接不排序、不多余消耗內存
  • HASH連接不排序，但是hash area多消耗內存
  • MS連接排序，消耗sort area內存

使用限制

  • NL連接支持各種寫法，無限制。
  • HASH連接支持等值連接，不支持>、<、like、<>等。
  • MS連接不支持>、<、like、<>等

適用場景

  • NL連接一般適用于OLTP系統
  • HASH連接、MS連接適用于OLAP系統

二、表連接測試

創建測試數據

--創建表T1和T2
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000));CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000));--插入測試數據
execute dbms_random.seed(0);
INSERT INTO t1
SELECT  rownum,  rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;--查看數據量 
SQL> select count(*) from t1;COUNT(*)
----------100
SQL> select count(*) from t2;COUNT(*)
----------100000

NL連接優化實驗

--  兩個表無索引執行計劃
alter session set statistics_level=all;
select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1016 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.02 |    1016 |  1000K|  1000K|  407K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |       |       |          |
------------------------------------------------------------------------------------------------------------------驅動表創建索引create index t1_n on t1(n);select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.05 |    1008 |
|*  1 |  HASH JOIN                           |      |      1 |      1 |      1 |00:00:00.05 |    1008 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN                  | T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS FULL                  | T2   |      1 |    103K|    100K|00:00:00.02 |    1006 |
-------------------------------------------------------------------------------------------------------
--被驅動表創建索引
CREATE INDEX t2_t1_id ON t2(t1_id);
select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    |Starts| E-Rows|A-Rows|   A-Time   |Buffers|Reads |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     1|       |     1|00:00:00.01 |      7|     4|
|   1 |  NESTED LOOPS                         |         |     1|      1|     1|00:00:00.01 |      7|     4|
|   2 |   NESTED LOOPS                        |         |     1|      1|     1|00:00:00.01 |      6|     4|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1|      1|     1|00:00:00.01 |      3|     0|
|*  4 |     INDEX RANGE SCAN                  | T1_N    |     1|      1|     1|00:00:00.01 |      2|     0|
|*  5 |    INDEX RANGE SCAN                   | T2_T1_ID|     1|      1|     1|00:00:00.01 |      3|     4|
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2      |     1|      1|     1|00:00:00.01 |      1|     0|
-----------------------------------------------------------------------------------------------------------
--大表驅動小表
SELECT /*+ leading(t2) use_nl(t1) */ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4  AND t1.n = 19;
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00.11 |    1013 |
|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.11 |    1013 |
|   2 |   NESTED LOOPS               |      |      1 |    103K|    100K|00:00:00.07 |    1011 |
|   3 |    TABLE ACCESS FULL         | T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |
|*  4 |    INDEX RANGE SCAN          | T1_N |    100K|      1 |    100K|00:00:00.04 |       5 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1   |    100K|      1 |      1 |00:00:00.02 |       2 |
-----------------------------------------------------------------------------------------------
--盡管兩個表都建立了索引，但是如果大表驅動小表，反而消耗更大。
--總結：
--驅動表和被驅動表考慮創建索引
--確保小表驅動大表

HASH連接優化實驗

--刪除索引drop index t1_n;drop index t2_t1_id;select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1016 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.02 |    1016 |  1000K|  1000K|  407K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    103K|    100K|00:00:00.01 |    1006 |       |       |          |
------------------------------------------------------------------------------------------------------------------創建索引
create index idx_t1_n on t1(n);--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      1 |00:00:00.06 |    1008 |       |       |          |
|*  1 |  HASH JOIN                           |          |      1 |      1 |      1 |00:00:00.06 |    1008 |  1000K|  1000K|  402K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   4 |   TABLE ACCESS FULL                  | T2       |      1 |    100K|    100K|00:00:00.02 |    1006 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------總結
--驅動表和被驅動表考慮創建索引
--確保小表驅動大表
--由于HASH操作需要額外內存區域（hash area),如果需要做hash運算的數據太多，則會用的臨時表空間，涉及磁盤IO會大大降低性能，索引盡量保證hash運算在內存中完成。

MS連接優化實驗

--Merge Sort Join兩表限制條件皆無索引SQL> SELECT /*+ leading(t2) use_merge(t1)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4   and t1.n=19;---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      1 |00:00:00.06 |    1007 |       |       |          |
|   1 |  MERGE JOIN                           |          |      1 |      1 |      1 |00:00:00.06 |    1007 |       |       |          |
|   2 |   SORT JOIN                           |          |      1 |    100K|     20 |00:00:00.06 |    1005 |  9762K|  1209K| 8677K (0)|
|   3 |    TABLE ACCESS FULL                  | T2       |      1 |    100K|    100K|00:00:00.01 |    1005 |       |       |          |
|*  4 |   SORT JOIN                           |          |     20 |      1 |      1 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |     INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------限制條件有索引
create index idx_t1_n on t1(n);
create index idx_t2_n on t2(n);
SQL> SELECT /*+ leading(t2) use_merge(t1)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id4  and t1.n=195  and t2.n=12;SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      0 |00:00:00.01 |       5 |       |       |          |
|   1 |  MERGE JOIN                           |          |      1 |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|   2 |   SORT JOIN                           |          |      1 |      1 |      1 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2       |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | IDX_T2_N |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |   SORT JOIN                           |          |      1 |      1 |      0 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |     INDEX RANGE SCAN                  | IDX_T1_N |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------連接條件創建索引
set autotrace traceonly
SQL> SELECT /*+ leading(t1) use_merge(t2)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id;
Statistics
----------------------------------------------------------1  recursive calls0  db block gets1012  consistent gets0  physical reads0  redo size13999  bytes sent via SQL*Net to client673  bytes received via SQL*Net from client8  SQL*Net roundtrips to/from client2  sorts (memory)0  sorts (disk)100  rows processedCREATE INDEX idx_t1_id ON t1(id);
CREATE INDEX idx_t2_t1_id ON t2(t1_id);
set autotrace traceonly
SQL> SELECT /*+ leading(t1) use_merge(t2)*/ *2  FROM t1, t23  WHERE t1.id = t2.t1_id;100 rows selected.Statistics
----------------------------------------------------------1  recursive calls0  db block gets1021  consistent gets0  physical reads0  redo size13999  bytes sent via SQL*Net to client673  bytes received via SQL*Net from client8  SQL*Net roundtrips to/from client1  sorts (memory)0  sorts (disk)100  rows processed

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/83748.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/83748.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/83748.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Dify工作流實踐—根據word需求文檔編寫測試用例到Excel中

Dify工作流實踐—根據word需求文檔編寫測試用例到Excel中

前言 這篇文章依賴到的操作可查閱我之前的文章&#xff1a; dify里的大模型是怎么添加進來的&#xff1a;在Windows本地部署Dify詳細操作 flask 框架app.route()函數的開發和調用&#xff1a;PythonWeb開發框架—Flask工程創建和app.route使用詳解 結構化提示詞的編寫&…
閱讀更多...
AWTK 嵌入式Linux平臺實現多點觸控縮放旋轉以及觸點丟點問題解決

AWTK 嵌入式Linux平臺實現多點觸控縮放旋轉以及觸點丟點問題解決

前言 最近涉及海圖的功能交互&#xff0c;多點觸摸又開始找麻煩。 在PC/Web平臺awtk是通過底層的sdl2庫來實現多點觸摸&#xff0c;但是在嵌入式Linux平臺&#xff0c;可能是考慮到性能原因&#xff0c;awtk并沒有采用sdl庫來做事件處理&#xff0c;而是自己實現一個awtk-lin…
閱讀更多...
Diffusion Planner:擴散模型重塑自動駕駛路徑規劃（ICLR‘25）

Diffusion Planner:擴散模型重塑自動駕駛路徑規劃（ICLR‘25）

1. 概述 2025年2月14日&#xff0c;清華大學AIR智能產業研究院聯合毫末智行、中科院自動化所和香港中文大學團隊&#xff0c;在ICLR 2025會議上發布了Diffusion Planner——一種創新性的基于Diffusion Transformer的自動駕駛規劃模型架構。該系統聯合建模周車運動預測與自車行…
閱讀更多...
ESP32對接巴法云實現配網

ESP32對接巴法云實現配網

目錄 序言準備工作巴法云注冊與使用Arduino準備 開發開始配網 序言 本文部分內容摘抄原創作者巴法云-做優秀的物聯網平臺 代碼有部分修改并測試運行正常 巴法云支持免費用戶通過開發對接實現各智能音箱設備語音控制智能家居設備&#xff0c;并有自己的App進行配網和控制&…
閱讀更多...
深度學習習題3

深度學習習題3

1.訓練神經網絡過程中&#xff0c;損失函數在一些時期&#xff08;Epoch&#xff09;不再減小, 原因可能是&#xff1a; 1.學習率太低 2.正則參數太大 3.卡在了局部最小值 A1 and 2 B. 2 and 3 C. 1 and 3 D. 都是 2.對于分類任務&#xff0c;我們不是將神經網絡中的隨機權重…
閱讀更多...
【EasyExcel】導出時添加頁眉頁腳

【EasyExcel】導出時添加頁眉頁腳

一、需求 使用 EasyExcel 導出時添加頁眉頁腳 二、添加頁眉頁腳的方法 通過配置WriteSheet或WriteTable對象來添加頁眉和頁腳。以下是具體實現步驟&#xff1a; 1. 創建自定義頁眉頁腳實現類 public class CustomFooterHandler implements SheetWriteHandler {private final…
閱讀更多...
c++ 類型轉換函數

c++ 類型轉換函數

測試代碼&#xff1a; void testTypeTransfer() { // 測試類型轉換函數class Distance {private:int meters;public:// 類型轉換函數&#xff0c;int表示轉化為int類型operator int() {std::cout << "調用了類型轉換函數" << endl;return meters; }Dist…
閱讀更多...
Conda 基本使用命令大全

Conda 基本使用命令大全

Conda 基本使用命令大全 Conda 是一個開源的包管理和環境管理系統&#xff0c;廣泛用于 Python 開發、數據科學和機器學習。以下是 最常用的 Conda 命令&#xff0c;涵蓋環境管理、包安裝、配置等核心操作。 1. 環境管理 創建環境 conda create --name myenv # 創…
閱讀更多...
基于SpringBoot和PostGIS的OSM時空路網數據入庫實踐

基于SpringBoot和PostGIS的OSM時空路網數據入庫實踐

目錄 前言 一、空間表的設計 1、屬性信息 2、空間表結構設計 二、路網數據入庫 1、實體類設計 2、路網數據寫入 3、pgAdmin數據查詢 三、總結 前言 在當今數字化時代&#xff0c;隨著信息技術的飛速發展&#xff0c;地理空間數據的應用范圍越來越廣泛&#xff0c;尤其是…
閱讀更多...
代付入賬是什么意思？怎么操作？

代付入賬是什么意思？怎么操作？

代付入賬就是指商戶委托銀行通過企業銀行賬戶向指定持卡人賬戶劃付款項&#xff0c;款項劃入指定賬戶即為入賬。 具體操作流程如下&#xff1a; 1. 向第三方支付公司指定賬戶充值加款。 2. 通過操作后臺提交代付銀行卡信息。 3. 第三方支付公司受理業務申請。 4. 第三方審…
閱讀更多...
數學復習筆記 27

數學復習筆記 27

前言 太難受了。因為一些事情。和朋友傾訴了一下&#xff0c;也沒啥用&#xff0c;幾年之后不知道自己再想到的時候&#xff0c;會怎么考慮呢。另外&#xff0c;筆記還是有框架一點比較好&#xff0c;這樣比較有邏輯感受。不然太亂了。這篇筆記是關于線代第五章&#xff0c;特…
閱讀更多...
第四十五天打卡

第四十五天打卡

知識點回顧&#xff1a; tensorboard的發展歷史和原理 tensorboard的常見操作 tensorboard在cifar上的實戰&#xff1a;MLP和CNN模型 效果展示如下&#xff0c;很適合拿去組會匯報撐頁數&#xff1a; 作業&#xff1a;對resnet18在cifar10上采用微調策略下&#xff0c;用tensor…
閱讀更多...
使用高斯樸素貝葉斯算法對鳶尾花數據集進行分類

使用高斯樸素貝葉斯算法對鳶尾花數據集進行分類

高斯樸素貝葉斯算法通常用于特征變量是連續變量&#xff0c;符合高素分布的情況。 使用高斯樸素貝葉斯算法對鳶尾花數據集進行分類 """ 使用高斯貝葉斯堆鳶尾花進行分類 """ #導入需要的庫 from sklearn.datasets import load_iris from skle…
閱讀更多...
【docker】Windows安裝docker

【docker】Windows安裝docker

環境及工具&#xff08;點擊下載&#xff09; Docker Desktop Installer.exe &#xff08;windows 環境下運行docker的一款產品&#xff09; wsl_update_x64 &#xff08;Linux 內核包&#xff09; 前期準備 系統要求2&#xff1a; Windows 11&#xff1a;64 位系統&am…
閱讀更多...
量化Quantization初步之--帶量化(QAT)的XOR異或pyTorch版250501

量化Quantization初步之--帶量化(QAT)的XOR異或pyTorch版250501

量化(Quantization)這詞兒聽著玄&#xff0c;經常和量化交易Quantitative Trading (量化交易)混淆。 其實機器學習(深度學習)領域的量化Quantization是和節約內存、提高運算效率相關的概念&#xff08;因大模型的普及&#xff0c;這個量化問題尤為迫切&#xff09;。 揭秘機器…
閱讀更多...
【Redis】zset 類型

【Redis】zset 類型

zset 一. zset 類型介紹二. zset 命令zaddzcard、zcountzrange、zrevrange、zrangebyscorezpopmax、zpopminzrank、zrevrank、zscorezrem、zremrangebyrank、zremrangebyscorezincrby阻塞版本命令&#xff1a;bzpopmax、bzpopmin集合間操作&#xff1a;zinterstore、zunionstor…
閱讀更多...
Mermaid 繪圖--以企業權限視圖為例

Mermaid 繪圖--以企業權限視圖為例

文章目錄 一、示例代碼二、基礎結構設計2.1 組織架構樹2.2 權限視圖設計 三、銷售數據權限系統四、關鍵語法技巧匯總 一、示例代碼 在企業管理系統開發中&#xff0c;清晰的權限視圖設計至關重要。本文將分享如何使用 Mermaid 繪制直觀的企業權限關系圖&#xff0c;復制以下代…
閱讀更多...
[pdf、epub]300道《軟件方法》強化自測題業務建模需求分析共257頁（202505更新）

[pdf、epub]300道《軟件方法》強化自測題業務建模需求分析共257頁（202505更新）

DDD領域驅動設計批評文集 做強化自測題獲得“軟件方法建模師”稱號 《軟件方法》各章合集 在本賬號CSDN資源下載&#xff0c;或者訪問鏈接&#xff1a; http://www.umlchina.com/url/quizad.html 如果需要提取碼&#xff1a;umlc 文件夾中的“300道軟件方法強化自測題2025…
閱讀更多...
std__map,std__unordered_map,protobuf__map之間的性能比較

std__map,std__unordered_map,protobuf__map之間的性能比較

簡單比較下 std::map、std::unordered_map 和 protobuf::Map 的性能&#xff0c;主要關注在 插入、查找 和 刪除 操作上的效率以及內存管理的差異。 std::map 底層實現&#xff1a;std::map 使用紅黑樹作為底層數據結構&#xff0c;紅黑樹是一種平衡二叉查找樹的變體結構&…
閱讀更多...
文檔處理組件Aspose.Words 25.5全新發布 ：六大新功能與性能深度優化

文檔處理組件Aspose.Words 25.5全新發布 ：六大新功能與性能深度優化

在數字化辦公日益普及的今天&#xff0c;文檔處理的效率與質量直接影響到企業的運營效率。Aspose.Words 作為業界領先的文檔處理控件&#xff0c;其最新發布的 25.5 版本帶來了六大新功能和多項性能優化&#xff0c;旨在為開發者和企業用戶提供更強大、高效的文檔處理能力。 六…
閱讀更多...
最新文章

Copyright @ 2022~2023