查看慢sql时发现，一条sql在左连接时，left join ……on……时使用子查询，导致优化器无法将后续where条件带入，导致大量的物理读。

复刻案例

准备测试表。

CREATE TABLE TEST_01 (C1 INT, C2 VARCHAR(10), C3 VARCHAR(10));
CREATE TABLE TEST_02 (C1 INT, C2 VARCHAR(10), C3 VARCHAR(10));
CREATE TABLE TEST_03 (C1 INT, C2 VARCHAR(10), C3 VARCHAR(10));

插入数据

TEST_01：
C1：1……100
C2：a1……a5，b1……b5，……
C3：x、y、z……x、y、z
TEST_02：
C1：101……200
C2：a1……a5，b1……b5，……
C3：p、q、r、s……p、q、r、s
TEST_03：重复组合（每组2条相同C2/C3，不同C1）
C1：1……100
C2：a1、a1、a2、a2……j2、j2
C3：x、x、y、y……y、y、z、z

SQL语句

SELECT
*
FROM TEST_01 A
LEFT JOIN TEST_02 B
ON A.C2=B.C2
LEFT JOIN TEST_03 C
ON C.C1=(SELECT D.C1 FROM TEST_03 D
WHERE D.C3=A.C3
AND D.C2=B.C2
ORDER BY D.C1 DESC
LIMIT 1
);

源执行计划

1 #NSET2: [152, 250->100, 100]
2 #PIPE2: [152, 250->100, 100]
3 #PRJT2: [150, 250->100, 100]; exp_num(9), is_atom(FALSE)
4 #NEST LOOP LEFT JOIN2: [150, 250->100, 100]: col_num(9); partition_keys_num(0); join condition(C.C1 = exp67) ret_null(0)
5 #HASH LEFT JOIN2: [1, 100->100, 100]; key_num(1); col_num(6); partition_keys_num(0); mix(0); MEM_USED(11967KB), DISK_USED(0KB) KEY(A.C2=B.C2)
6 #CSCN2: [1, 100->100, 100]; INDEX33556489(TEST_01); btr_scan(1)
7 #CSCN2: [1, 100->100, 100]; INDEX33556490(TEST_02); btr_scan(1)
8 #CSCN2: [1, 100->100, 100]; INDEX33556491(TEST_03); btr_scan(1)
9 #SPL2: [1, 1->0, 100]; key_num(1), spool_num(0), has_variable(1), sites(-)
10 #PRJT2: [1, 1->5200, 100]; exp_num(1), is_atom(TRUE)
11 #PRJT2: [1, 1->5200, 100]; exp_num(1), is_atom(FALSE)
12 #SORT3: [1, 1->5200, 100]; key_num(1), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(2048KB), DISK_USED(0KB)
13 #PRJT2: [1, 1->6000, 100]; exp_num(2), is_atom(FALSE)
14 #SLCT2: [1, 1->6000, 100]; (D.C3 = var1 AND D.C2 = var2)
15 #CSCN2: [1, 100->1000000, 100]; INDEX33556491(TEST_03); btr_scan(1)

Statistics

0 data pages changed
0 undo pages changed
10025 logical reads
0 physical reads
0 redo size
7427 bytes sent to client
397 bytes received from client
2 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
1829 exec time(ms)

已用时间: 00:00:01.829. 执行号:10814.

优化方法

改写01：使用聚集函数

SELECT
a.,
b.,
d_max.max_c1,
d_max.max_c2,
d_max.max_c3
FROM TEST_01 A
LEFT JOIN TEST_02 B ON A.C2 = B.C2
LEFT JOIN (
SELECT
D.C3,
D.C2,
max(D.C1) AS max_c1,
max(D.C2) AS max_c2,
max(D.C3) AS max_c3
FROM TEST_03 D
GROUP
BY D.C3, D.C2
) d_max ON A.C3 = d_max.C3 AND B.C2 = d_max.C2
order by 1;

改写01：执行计划

1 #NSET2: [3, 1666->100, 200]
2 #PRJT2: [3, 1666->100, 200]; exp_num(9), is_atom(FALSE)
3 #SORT3: [3, 1666->100, 200]; key_num(1), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(2048KB), DISK_USED(0KB)
4 #HASH LEFT JOIN2: [2, 1666->100, 200]; key_num(2); col_num(9); partition_keys_num(0); mix(0); MEM_USED(12479KB), DISK_USED(0KB) KEY(A.C3=D_MIN.C3 AND B.C2=D_MIN.C2)
5 #HASH LEFT JOIN2: [1, 100->100, 100]; key_num(1); col_num(6); partition_keys_num(0); mix(0); MEM_USED(11967KB), DISK_USED(0KB) KEY(A.C2=B.C2)
6 #CSCN2: [1, 100->100, 100]; INDEX33555500(TEST_01); btr_scan(1)
7 #CSCN2: [1, 100->100, 100]; INDEX33555501(TEST_02); btr_scan(1)
8 #PRJT2: [1, 100->80, 100]; exp_num(5), is_atom(FALSE)
9 #HAGR2: [1, 100->80, 100]; grp_num(2), sfun_num(3), MEM_USED(1653KB), DISK_USED(0KB), distinct_flag[0,0,0]; slave_empty(0) keys(D.C3, D.C2)
10 #CSCN2: [1, 100->100, 100]; INDEX33555502(TEST_03); btr_scan(1)

Statistics

0 data pages changed
0 undo pages changed
25 logical reads
0 physical reads
0 redo size
7398 bytes sent to client
462 bytes received from client
2 roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
5 exec time(ms)

已用时间: 4.750(毫秒). 执行号:934.

改写02：使用临时表

with CC AS
(SELECT D.C1,
D.C2,
D.C3 ,
ROW_NUMBER() OVER(
partition by C2,
C3
order by c1 desc) as rn
from TEST_03 D
)
select *
from TEST_01 A
LEFT JOIN TEST_02 B
ON A.C2=B.C2
LEFT JOIN CC C
on C.C3=A.C3
and C.C2=B.C2
and rn=1;

改写02：执行计划

1 #NSET2: [1, 100->100, 200]
2 #PRJT2: [1, 100->100, 200]; exp_num(10), is_atom(FALSE)
3 #HASH RIGHT JOIN2: [1, 100->100, 200]; key_num(2); col_num(10); MEM_USED(11967KB), DISK_USED(0KB) KEY(C.C3=A.C3 AND C.C2=B.C2)
4 #SLCT2: [1, 2->80, 100]; C.RN = var1
5 #PRJT2: [1, 100->100, 100]; exp_num(4), is_atom(FALSE)
6 #AFUN: [1, 100->100, 100]; afun_num(1)
7 #SORT3: [1, 100->100, 100]; key_num(3), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(2048KB), DISK_USED(0KB)
8 #CSCN2: [1, 100->100, 100]; INDEX33556491(TEST_03); btr_scan(1)
9 #HASH LEFT JOIN2: [1, 100->100, 100]; key_num(1); col_num(6); partition_keys_num(0); mix(0); MEM_USED(11967KB), DISK_USED(0KB) KEY(A.C2=B.C2)
10 #CSCN2: [1, 100->100, 100]; INDEX33556489(TEST_01); btr_scan(1)
11 #CSCN2: [1, 100->100, 100]; INDEX33556490(TEST_02); btr_scan(1)

Statistics

0 data pages changed
0 undo pages changed
26 logical reads
0 physical reads
0 redo size
8240 bytes sent to client
562 bytes received from client
2 roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
4 exec time(ms)

已用时间: 4.425(毫秒). 执行号:10815.

优化03：创建索引，减少回表。

create index IND_TEST_03 ON TEST_03(C2,C3);

优化03：执行计划

1 #NSET2: [152, 250->100, 100]
2 #PIPE2: [152, 250->100, 100]
3 #PRJT2: [150, 250->100, 100]; exp_num(9), is_atom(FALSE)
4 #NEST LOOP LEFT JOIN2: [150, 250->100, 100]: col_num(9); partition_keys_num(0); join condition(C.C1 = exp67) ret_null(0)
5 #HASH LEFT JOIN2: [1, 100->100, 100]; key_num(1); col_num(6); partition_keys_num(0); mix(0); MEM_USED(11967KB), DISK_USED(0KB) KEY(A.C2=B.C2)
6 #CSCN2: [1, 100->100, 100]; INDEX33556489(TEST_01); btr_scan(1)
7 #CSCN2: [1, 100->100, 100]; INDEX33556490(TEST_02); btr_scan(1)
8 #CSCN2: [1, 100->100, 100]; INDEX33556491(TEST_03); btr_scan(1)
9 #SPL2: [1, 1->0, 100]; key_num(1), spool_num(0), has_variable(1), sites(-)
10 #PRJT2: [1, 1->5200, 100]; exp_num(1), is_atom(TRUE)
11 #PRJT2: [1, 1->5200, 100]; exp_num(1), is_atom(FALSE)
12 #SORT3: [1, 1->5200, 100]; key_num(1), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(2048KB), DISK_USED(0KB)
13 #PRJT2: [1, 1->6000, 100]; exp_num(2), is_atom(FALSE)
14 #BLKUP2: [1, 1->6000, 100]; IND_TEST_03(TEST_03)
15 #SSEK2: [1, 1->6000, 100]; scan_type(ASC), IND_TEST_03(TEST_03), is_global(0), scan_range[(var2,var1),(var2,var1)]

Statistics

0 data pages changed
0 undo pages changed
16026 logical reads
0 physical reads
0 redo size
7427 bytes sent to client
397 bytes received from client
2 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0 io wait time(ms)
1568 exec time(ms)

已用时间: 00:00:01.568. 执行号:10816.

优化总结

ON子句使用子查询时，且使用limit 1。建议使用聚集函数max或min进行改写，或使用临时表ROW_NUMBER() OVER( partition by C2, C3 order by c1 desc)。如果业务无法更改sql，可创建索引，并使用hint绑定索引使用。