如何提高涉及數百萬行的表 JOINS 的性能
我正在處理一個涉及簡單查詢的非常簡單的結構的性能問題。
考慮兩個名為 TBL01 和 TBL02 的表。TBL01 與 TBL02 具有主從關係。TBL01 的主鍵稱為 CONFIGURATION_ID。TBL02 具有 CONFIGURATION_ID 作為 TBL01 的外鍵，它也被索引。TBL01 有 280 萬行，TBL02 可能有 1000 萬行。
TBL01.CONFIGURATION_ID = TBL02.CONFIGURATION_ID 上的簡單 INNER JOIN 有點慢。慢是指從 TBL01 和 TBL02 返回總共 15 列的 10 行需要一秒鐘。如果我開始使用排序等，事情會變得更糟。我過去處理過非常大的桌子，但從來沒有遇到過問題。會不會少了點什麼？
為了提高性能，我的下一步應該是什麼？在 CONFIGURATION_ID 上使用 JOIN 的 BITMAP 索引？那會有幫助嗎？還有其他提示嗎？
**注 1：**我使用的是 ORACLE 12。
**注意 2：**這可能很明顯，但如果我在加入後添加過濾器，情況會更好。
你所描述的是正常行為。
create table t1 as select * from dba_objects; create table t2 as select * from dba_objects; delete from t1 where object_id is null; delete from t2 where object_name = 'T1'; alter table t1 add primary key (object_id); alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id); create index t2_object_id on t2(object_id); exec dbms_stats.gather_table_stats(user, 'T1'); exec dbms_stats.gather_table_stats(user, 'T2'); explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id); select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Plan hash value: 1838229974 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23763 | 5337K| | 516 (1)| 00:00:01 | |* 1 | HASH JOIN | | 23763 | 5337K| 2952K| 516 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 23764 | 2668K| | 115 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| T2 | 23763 | 2668K| | 115 (1)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Note ----- - this is an adaptive plan在這裡，數據庫使用 HASH JOIN 對整個結果集執行連接。預設情況下，優化器會生成一個計劃以從查詢中返回所有行，該查詢是使用
optimizer_mode參數配置的。SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_mode string ALL_ROWS您的查詢說：返回 TBL1 和 TBL2 中與 CONFIGURATION_ID 匹配的所有行。你從來沒有告訴數據庫你只想要前 10 行。如果您希望優化器生成一個更快地返回第一行的計劃，請告訴優化器：更改優化器模式，例如：
alter session set optimizer_mode=first_rows_10; explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id); select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 209411957 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 2300 | 12 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 10 | 2300 | 12 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 10 | 2300 | 12 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T2 | 21604 | 2426K| 2 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | SYS_C006383 | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 115 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")這一次，數據庫執行了 NESTED LOOPS JOIN。
在這種情況下，HASH JOIN 和 NESTED LOOPS JOIN 之間的顯著區別是：HASH JOIN 必須完全讀取其中一個表才能為連接建構雜湊表。NESTED LOOPS JOIN 可以逐行工作並按 ID 進行索引查找，然後在找到 10 行後停止讀取表。因此，即使您僅獲取 10 行，使用 HASH JOIN，數據庫也會從 TBL1 或 TBL2 讀取整個 2.8 或 1000 萬行，具體取決於優化器選擇的順序。
更糟糕的是，如果你想通過指定的順序獲取前 10 行，使用上面的 HASH JOIN，數據庫必須完全讀取兩個表，對所有行執行連接，對大約 1000 萬行進行排序，並且只返回其中的前 10 個。
使用 NESTED LOOPS JOIN，如果您在排序列上有索引，則數據庫甚至可以使用該索引按排序順序讀取行，可以逐行處理，並在找到前 10 行後停止。
優化器模式可以使用上面的參數設置，也可以在語句級別定義，帶有提示，例如：
select /*+ first_rows(10) */ ...