热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
SQL优化案例——利用归并连接优化分页查询
技术分享/
文章详情
/
SQL优化案例——利用归并连接优化分页查询
PYZ
2026/08/07
205
3
0
问题SQL
SELECT *
FROM (SELECT row_.*,
ROWNUM rownum_
FROM (SELECT ci.CLIENT_ID,
ci.CLIENT_NAME,
ci.CLIENT_PROPERTY AS CLIENT_SORT,
ci.CERT_TYPE,
ci.CERT_NO,
ci.ADDR
FROM T_CLIENT ci,
T_CLIENT_MEM cm
WHERE cm.CLIENT_ID = ci.CLIENT_ID
AND cm.STATUS = '2'
AND cm.MEMBER_ID = '0184'
ORDER BY CLIENT_ID ) row_ )
WHERE rownum_ <= 50;
1 #NSET2: [1, 50->50, 432]
2 #PRJT2: [1, 50->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
3 #PRJT2: [1, 50->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
4 #RNSK: [1, 50->50, 432];
5 #PRJT2: [1, 50->50, 432]; exp_num(6), is_atom(FALSE); INFO_BITS(0)
6 #TOPN2: [1, 50->50, 432];
7 #SLCT2: [1, 256->50, 432]; CM.STATUS = '2', slct_pushdown(0)
8 #NEST LOOP INDEX JOIN2: [1, 256->117, 432]
9 #BLKUP2: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); use_clu_addr(0)
10 #SSCN: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); btr_scan(1); is_global(0)
11 #BLKUP2: [1, 1->117, 96]; INDEX33557422(T_CLIENT_MEM); use_clu_addr(0)
12 #SSEK2: [1, 1->117, 96]; scan_type(ASC), INDEX33557422(T_CLIENT_MEM), is_global(0), scan_range[(CI.CLIENT_ID,'0184'),(CI.CLIENT_ID,'0184')]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
16440 logical reads
0 physical reads
0 redo size
5993 bytes sent to client
648 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
470.973 exec time(ms)
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE INFO1
------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- -------------------- -----
DLCK 67 0.02% 13 0 2 0 0 0 0 NULL NULL 0 NULL
SSCN 335 0.1% 12 10 8 0 0 0 0 NULL NULL 0 NULL
PRJT2 1422 0.44% 11 2 102 0 0 0 0 NULL NULL 0 NULL
RNSK 1512 0.47% 10 4 101 0 0 0 0 NULL NULL 0 NULL
PRJT2 1630 0.5% 9 5 100 0 0 0 0 NULL NULL 0 NULL
TOPN2 1687 0.52% 8 6 100 0 0 0 0 NULL NULL 0 NULL
PRJT2 1937 0.6% 7 3 102 0 0 0 0 NULL NULL 0 NULL
NSET2 2294 0.71% 6 1 52 0 0 0 0 NULL NULL 0 NULL
SLCT2 4522 1.39% 5 7 167 0 0 0 0 NULL NULL 0 NULL
BLKUP2 6318 1.95% 4 9 16 0 0 0 0 NULL NULL 0 NULL
IJI2 64252 19.78% 3 8 4244 0 0 0 0 NULL NULL 0 NULL
SSEK2 115245 35.48% 2 12 4119 0 0 0 0 NULL NULL 0 NULL
BLKUP2 123575 38.05% 1 11 8238 0 0 0 0 NULL NULL 0 NULL
--索引信息
SQL> select table_name,index_name,column_name,column_position,descend from dba_ind_columns where table_name in ('T_CLIENT','T_CLIENT_MEM') order by 1,2,4;
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION DESCEND
------------ ------------- ----------- --------------- -------
T_CLIENT INDEX33557420 CLIENT_ID 1 ASC
T_CLIENT_MEM INDEX33557422 CLIENT_ID 1 ASC
T_CLIENT_MEM INDEX33557422 MEMBER_ID 2 ASC
问题分析
通过执行计划看这条SQL似乎没有什么问题，可以利用索引条件做TOPN，单独执行不算慢，但在jmter压测200并发下响应时间要达到几十秒，用户要求并发下响应时间需要达到4秒以内，而且查询涉及到的表是其他业务的核心表，除了查询以外还存在大量高并发的DML操作，为了保证DML业务的效率因此不允许创建额外的索引。
从trace中可见SQL产生的逻辑读为16440，逻辑读越高就意味着从内存中读取数据页的次数越高，CPU需要做的工作就越多，因此在高并发环境下逻辑读高的SQL会消耗更多的CPU资源，并发响应时间也会更高，那么要想在并发压力环境下提升响应时间，关键点在于保证SQL执行效率的同时降低逻辑读，从et结果可见主要代价来自于第11、12步的ssek+blkup，由于T_CLIENT_MEM表的谓词条件并不具备很好的过滤性，因此可以调整表的连接方式进行优化。
优化方案
尝试调整 HASH JOIN
SELECT /*+ enable_index_join(0) */
*
FROM (SELECT row_.*,
ROWNUM rownum_
FROM (SELECT ci.CLIENT_ID,
ci.CLIENT_NAME,
ci.CLIENT_PROPERTY AS CLIENT_SORT,
ci.CERT_TYPE,
ci.CERT_NO,
ci.ADDR
FROM T_CLIENT ci,
T_CLIENT_MEM cm
WHERE cm.CLIENT_ID = ci.CLIENT_ID
AND cm.STATUS = '2'
AND cm.MEMBER_ID = '0184'
ORDER BY CLIENT_ID ) row_ )
WHERE rownum_ <= 50;
1 #NSET2: [73, 50->50, 432]
2 #PRJT2: [73, 50->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
3 #PRJT2: [73, 50->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
4 #RNSK: [73, 50->50, 432];
5 #PRJT2: [73, 50->50, 432]; exp_num(6), is_atom(FALSE); INFO_BITS(0)
6 #SORT3: [73, 50->50, 432]; key_num(1), partition_key_num(0), is_distinct(FALSE), is_adaptive(0), MEM_USED(18432KB), DISK_USED(0KB)
7 #HASH2 INNER JOIN: [70, 16660->16662, 432]; RKEY_UNIQUE KEY_NUM(1), MEM_USED(22656KB), DISK_USED(0KB) KEY(CM.CLIENT_ID=CI.CLIENT_ID) KEY_NULL_EQU(0)
8 #SLCT2: [14, 16660->16662, 144]; (CM.STATUS = '2' AND CM.MEMBER_ID = '0184'), slct_pushdown(0)
9 #CSCN2: [14, 100000->100000, 144]; INDEX33557421(T_CLIENT_MEM); btr_scan(1); need_slct(0)
10 #CSCN2: [32, 200000->200000, 288]; INDEX33557419(T_CLIENT); btr_scan(1); need_slct(0)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
723 logical reads
0 physical reads
0 redo size
5993 bytes sent to client
705 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
577.543 exec time(ms)
调整为 HASH JOIN 后执行时间差不多，逻辑读也大幅降低，这里是因为二级索引回表并非直接访问表里的数据页，而是通过索引定位到的每一行都要单独走一遍聚集索引的B树查找路径，每一层索引节点的访问都会被计入逻辑读，因此BLKUP的逻辑读基本等于索引定位逻辑读+每行数据聚集索引定位的逻辑读，每一行回表即一次从根节点定位到叶子节点的聚集索引B树检索，因此当过滤数据量较多时逐行回表的累计访问次数会远大于全表扫描。虽然逻辑读降低，但是 CSCN + SORT 也带来了更多的 IO 开销，HASH JOIN 所占用的内存空间也有所增加，压测开始出现报错"-524:超出全局hash join空间,适当增加HJ_BUF_GLOBAL_SIZE"。
调整为归并连接
SELECT /*+ stat(ci,5) stat(cm,5) enable_index_join(0) enable_hash_join(0) top_order_opt_flag(1) */
*
FROM (SELECT row_.*,
ROWNUM rownum_
FROM (SELECT ci.CLIENT_ID,
ci.CLIENT_NAME,
ci.CLIENT_PROPERTY AS CLIENT_SORT,
ci.CERT_TYPE,
ci.CERT_NO,
ci.ADDR
FROM T_CLIENT ci,
T_CLIENT_MEM cm
WHERE cm.CLIENT_ID = ci.CLIENT_ID
AND cm.STATUS = '2'
AND cm.MEMBER_ID = '0184'
ORDER BY CLIENT_ID ) row_ )
WHERE rownum_ <= 50;
1 #NSET2: [1, 20->50, 432]
2 #PRJT2: [1, 20->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
3 #PRJT2: [1, 20->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
4 #RNSK: [1, 20->50, 432];
5 #PRJT2: [1, 20->50, 432]; exp_num(6), is_atom(FALSE); INFO_BITS(0)
6 #TOPN2: [1, 20->50, 432];
7 #SLCT2: [1, 20->52, 432]; (CM.STATUS = '2' AND CM.MEMBER_ID = '0184'), slct_pushdown(0)
8 #MERGE INNER JOIN3: [1, 20->413, 432]; KEY(COL_0 = COL_2) KEY_NULL_EQU(0)
9 #BLKUP2: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); use_clu_addr(0)
10 #SSCN: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); btr_scan(1); is_global(0)
11 #BLKUP2: [1, 300->512, 144]; INDEX33557422(T_CLIENT_MEM); use_clu_addr(0)
12 #SSCN: [1, 300->512, 144]; INDEX33557422(T_CLIENT_MEM); btr_scan(1); is_global(0)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
9226 logical reads
0 physical reads
0 redo size
5993 bytes sent to client
747 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
14.829 exec time(ms)
MERGE JOIN 在处理排序分页查询时具有独特的优势，特别是在大数据量和需要全局排序的场景下，通过执行计划可以看到两张表都走了 SSCN 按主键索引的顺序进行合并连接，MERGE JOIN 可以直接利用排序顺序进行有序的输出无需额外的 SORT ORDER BY 操作直接进行TOPN取消了SORT排序操作符，加上分页条件可以提前终止扫描减少 IO 开销，同时内存使用相对于 HASH JOIN 也要更低。而执行计划中 BLKUP 产生的逻辑读也还可以通过参数 PLAN_OP_FLAG 进一步降低。
参数 PLAN_OP_FLAG 取值4、8、12对回表操作具有优化效果
4：仅对 BLKUP 操作符的执行进行优化，即在 B 树上搜索到一行记录后，继续尝试在当前页上搜索下一条记录；
8：在 4 的基础上，生成计划时，在 BLKUP 操作符下方添加一个 SORT 操作符，使传给 BLKUP 的数据更加紧凑，更能发挥 BLKUP 优化的优势，缺点是 SORT 带来的额外开销；
12：在 4 的基础上，对每一批传给 BLKUP 的数据进行 BDTA 排序，不额外使用新的排序操作符；
最终通过hint调整连接方式和回表优化满足了压测条件
SELECT /*+ plan_op_flag(12) stat(ci,5) stat(cm,5) enable_index_join(0) enable_hash_join(0) top_order_opt_flag(1) */
*
FROM (SELECT row_.*,
ROWNUM rownum_
FROM (SELECT ci.CLIENT_ID,
ci.CLIENT_NAME,
ci.CLIENT_PROPERTY AS CLIENT_SORT,
ci.CERT_TYPE,
ci.CERT_NO,
ci.ADDR
FROM T_CLIENT ci,
T_CLIENT_MEM cm
WHERE cm.CLIENT_ID = ci.CLIENT_ID
AND cm.STATUS = '2'
AND cm.MEMBER_ID = '0184'
ORDER BY CLIENT_ID ) row_ )
WHERE rownum_ <= 50;
1 #NSET2: [1, 20->50, 432]
2 #PRJT2: [1, 20->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
3 #PRJT2: [1, 20->50, 432]; exp_num(7), is_atom(FALSE); INFO_BITS(0)
4 #RNSK: [1, 20->50, 432];
5 #PRJT2: [1, 20->50, 432]; exp_num(6), is_atom(FALSE); INFO_BITS(0)
6 #TOPN2: [1, 20->50, 432];
7 #SLCT2: [1, 20->52, 432]; (CM.STATUS = '2' AND CM.MEMBER_ID = '0184'), slct_pushdown(0)
8 #MERGE INNER JOIN3: [1, 20->413, 432]; KEY(COL_0 = COL_2) KEY_NULL_EQU(0)
9 #BLKUP2: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); use_clu_addr(0)
10 #SSCN: [1, 256->4096, 288]; INDEX33557420(T_CLIENT); btr_scan(1); is_global(0)
11 #BLKUP2: [1, 300->512, 144]; INDEX33557422(T_CLIENT_MEM); use_clu_addr(0)
12 #SSCN: [1, 300->512, 144]; INDEX33557422(T_CLIENT_MEM); btr_scan(1); is_global(0)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
98 logical reads
0 physical reads
0 redo size
5993 bytes sent to client
764 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
8.486 exec time(ms)
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1