排序性能优化 | 达梦技术社区

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:8
【操作系统】：Windwos Server2016
【CPU】:32核64GB
【问题描述】*：两张表联合查询，每张表在40W条数据左右。关联的字段建立了聚焦索引。不排序分页查询1到20条，显示100毫秒，使用排序分页后需要接近3秒钟，需要如何优化？SQL如下

使用了排序的SQL
总共1个语句正依次执行...

FROM
(
SELECT
"MJZBL"."GUID" "MJZBL"."MZH" "MJZBL"."HZXM" "GHDJ"."HZSFZH" "GHDJ"."HZNL" "MJZBL"."HZYL" "MJZBL"."JZRQSJ" "MJZBL"."KSMC" "MJZBL"."SSDM" "MJZBL"."CZBZDM" "MJZBL"."JZYSXM" "MJZBL"."LGBR" "MJZBL"."CZJH" "MJZBL"."PTCJSJ" "MJZBL"."YLJGDM" "MJZBL"."YLJGMC" "MJZBL"."HZZS" "MJZBL"."HZXBS" "MJZBL"."HZJWS" ROW_NUMBER() OVER(ORDER BY FROM
HISCENTER."MZ_MJZBL" INNER JOIN HISCENTER."MZ_GHDJ" ON "MJZBL"."YLJGDM" WHERE
"MJZBL"."JZRQSJ" )
T
WHERE
RowIndex BETWEEN 81 AND 100
执行成功, 执行耗时2秒 214毫秒. 执行号:5680 AS "GUID" ,
AS "MZH" ,
AS "HZXM" ,
AS "ZJHM" ,
AS "HZNL" ,
AS "HZYL" ,
AS "JZRQSJ",
AS "KSMC" ,
AS "SSDM" ,
AS "CZBZDM",
AS "JZYSXM",
AS "LGBR" ,
AS "CZJH" ,
AS "PTCJSJ",
AS "YLJGDM",
AS "YLJGMC",
AS "ZS" ,
AS "XBS" ,
AS "JWS" ,
"MJZBL"."JZRQSJ" DESC) AS RowIndex
"MJZBL"
"GHDJ"
= "GHDJ"."YLJGDM" AND "MJZBL"."MZH" = "GHDJ"."MZH"
>= '2023-1-1' AND "MJZBL"."JZRQSJ" <= '2023-9-9'

1条语句执行成功

未使用排序的SQL
总共1个语句正依次执行...

FROM
(
SELECT
"MJZBL"."GUID" AS "GUID" ,
"MJZBL"."MZH" AS "MZH" ,
"MJZBL"."HZXM" AS "HZXM" ,
"GHDJ"."HZSFZH" AS "ZJHM" ,
"GHDJ"."HZNL" AS "HZNL" ,
"MJZBL"."HZYL" AS "HZYL" ,
"MJZBL"."JZRQSJ" AS "JZRQSJ",
"MJZBL"."KSMC" AS "KSMC" ,
"MJZBL"."SSDM" AS "SSDM" ,
"MJZBL"."CZBZDM" AS "CZBZDM",
"MJZBL"."JZYSXM" AS "JZYSXM",
"MJZBL"."LGBR" AS "LGBR" ,
"MJZBL"."CZJH" AS "CZJH" ,
"MJZBL"."PTCJSJ" AS "PTCJSJ",
"MJZBL"."YLJGDM" AS "YLJGDM",
"MJZBL"."YLJGMC" AS "YLJGMC",
"MJZBL"."HZZS" AS "ZS" ,
"MJZBL"."HZXBS" AS "XBS" ,
"MJZBL"."HZJWS" AS "JWS" ,
ROWNUM AS RowIndex
FROM
HISCENTER."MZ_MJZBL" "MJZBL"
INNER JOIN HISCENTER."MZ_GHDJ" "GHDJ"
ON "MJZBL"."YLJGDM" = "GHDJ"."YLJGDM" AND "MJZBL"."MZH" = "GHDJ"."MZH"
WHERE
"MJZBL"."JZRQSJ" >= '2023-1-1' AND "MJZBL"."JZRQSJ" <= '2023-9-9'
)
T
WHERE
RowIndex BETWEEN 81 AND 100
执行成功, 执行耗时101毫秒. 执行号:5681

1条语句执行成功

查询计划如下

回答 0

暂无回答