今天就跟大家聊聊有關(guān)like order by top組合語句的優(yōu)化是怎樣的，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

成都創(chuàng)新互聯(lián)公司基于分布式IDC數(shù)據(jù)中心構(gòu)建的平臺為眾多戶提供成都服務(wù)器托管 四川大帶寬租用 成都機柜租用 成都服務(wù)器租用。

環(huán)境信息

基本信息

操作系統(tǒng)：CentoOS 7.2

數(shù)據(jù)庫版本：DM Database Server x64 V7. 1.5.202-Bu1 1d(2017.07.17-82922 ) ENT

頁大?。?6K

大小寫敏感：是

字符集：GB18030

數(shù)據(jù)庫基本參數(shù)

表結(jié)構(gòu)

CREATE TABLE "XYGX"."XYGX_GS_TYSHXYDM_FR"

(

"UUID" NUMERIC(36,6) NOT NULL,

"QYMC" VARCHAR(200),

"TYSHXYDM" VARCHAR(50),

"SCJYD" VARCHAR(300),

"ZCXS" VARCHAR(2),

"JYZT" VARCHAR(6),

"WZ" VARCHAR(6),

"SCJYDXZQH" VARCHAR(1000),

"ZCH" VARCHAR(50),

"QYLXDM" VARCHAR(4),

"ZHYCNBSJ" DATETIME(6),

"JYFW" VARCHAR(4000),

"ZCZB" NUMERIC(38,8),

"ZCDZSZXXQH" VARCHAR(1000),

"HZRQ" DATETIME(6),

"ZS" VARCHAR(300),

"ZCDYB" VARCHAR(30),

"FZRQ" DATETIME(6),

"CLRQ" DATETIME(6),

"HBZL" VARCHAR(30),

"CYRS" NUMERIC(36,6),

"HYDM" VARCHAR(50),

"YYQXZHI" DATETIME(6),

"YYQXZI" DATETIME(6),

"XXCZLX" VARCHAR(12),

"YWLX" VARCHAR(12),

"DJJG" VARCHAR(200),

"ZHYCNBND" NUMERIC(38,8),

"FDDBR" VARCHAR(300),

"ZTID" NUMERIC(38,8),

NOT CLUSTER PRIMARY KEY("UUID")) STORAGE(ON "XYGX", CLUSTERBTR) ;

CREATE  INDEX "XYGX_GS_TYSHXYDM_FR_INDEX" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("QYMC" ASC,"FDDBR" ASC,"TYSHXYDM" ASC,"ZCH" ASC,"HZRQ" ASC) STORAGE(ON "XYGX", CLUSTERBTR) ;

CREATE  INDEX "IDX_HZRQ_QYMC" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC" DESC) STORAGE(ON "XYGX", CLUSTERBTR) ;

表數(shù)據(jù)量

查詢表的數(shù)據(jù)量

select count(*) from XYGX.XYGX_GS_TYSHXYDM_FR

查詢結(jié)果如下，該表工三百二十多萬條數(shù)據(jù)。

count(*)

3216107

原語句

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,'yyyy-MM-dd')ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,/*TO_CHAR(HZRQ,'yyyy-MM-dd')*/HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,'yyyy-MM-dd')FZRQ,TO_CHAR(CLRQ,'yyyy-MM-dd')CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,'yyyy-MM-dd')YYQXZHI,TO_CHAR(YYQXZI,'yyyy-MM-dd')YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like '星際%' order by HZRQ desc limit 0,50;

原執(zhí)行計劃

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= '星際' AND XTGX_GS_TYSHXYDM. QYMC <'星%')

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

分析過程

基本思路

排序消耗的了大量資源，可以通過索引消除排序的方式進行優(yōu)化。

創(chuàng)建索引

創(chuàng)建相關(guān)索引，把排序列放在前，篩選列放在后。

CREATE  INDEX "IDX_HZRQ_QYMC" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC" DESC) ;

創(chuàng)建索引且清除執(zhí)行計劃緩存后執(zhí)行計劃沒有變化。

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= '星際' AND XTGX_GS_TYSHXYDM. QYMC <'星%')

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

清除執(zhí)行計劃

查出語句相關(guān)的計劃信息，獲取計劃的CACHE_ITEM

select * from v$CACHEPLN where sqlstr like ‘%where QYMC like 星際%’;

根據(jù)CACHE_ITEM 清除執(zhí)行計劃

call sp_clear_plan_cache(139845509285864);

檢查TOP_ORDER_OPT_FLAG

檢查TOP_ORDER_OPT_FLAG：

select * from v$dm_ini where para_name like ‘%TOP%’;

查到結(jié)果是TOP_ORDER_OPT_FLAG當(dāng)前只為0，未開啟排序優(yōu)化。

參數(shù)說明：

改參數(shù)是動態(tài)參數(shù)，當(dāng)語句內(nèi)含有TOP + ORDER，且ORDER BY列屬于索引前導(dǎo)列時，如果該值為1，則根據(jù)ORDER BY列對應(yīng)的基本信息，減少估算的行數(shù)從而減少代價計算。當(dāng)該值為0怎不進行優(yōu)化。

動態(tài)調(diào)整參數(shù)：

sp_set_para_value(1,'TOP_ORDER_OPT_FLAG'1);

調(diào)整后執(zhí)行計劃依然不變，另外開窗口并再清除執(zhí)行計劃也不變。

1 #NSET2: [1198, 50, 1142]

2   #RJT2: [1198, 50, 1142]; exp_num(31), is_atom FALSE)

3     #SORT3: [1196， 50, 1142]: key_num(1), is_distinct(FALSE), top_fag(1), is_adaptive(0)

4       #PRJT2: [1198, 482639, 1142]: exp_ num(31), is_aton (FAISE)

5         #SLCT2: [1198, 482639, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= '星際' AND XTGX_GS_TYSHXYDM. QYMC <'星%')

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

簡化語句測試

通過簡化發(fā)現(xiàn)如果把語句總order by列的to_char函數(shù)去掉，執(zhí)行計劃就正常走索引，消除了排序，在order和to_char同時使用一個字段時通過索引消除排序方式不可用

優(yōu)化思路，通過使用嵌套的方式消除了這種問題，改造后語句：

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,'yyyy-MM-dd')HZRQ,ZS,ZCDYB,FZRQ,CLRQ,HBZL,CYRS,HYDM,YYQXZHI,YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from (

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,'yyyy-MM-dd')ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,/*TO_CHAR(HZRQ,'yyyy-MM-dd')*/HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,'yyyy-MM-dd')FZRQ,TO_CHAR(CLRQ,'yyyy-MM-dd')CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,'yyyy-MM-dd')YYQXZHI,TO_CHAR(YYQXZI,'yyyy-MM-dd')YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID

from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like '星際%' order by HZRQ desc limit 0,50)

 ;

改造后執(zhí)行計劃

1 #NSET2: [0, 50, 1142]

2   #RJT2: [0, 50, 1142]; exp_num(31), is_atom FALSE)

3     #PRJT2:[0, 50, 1142]: kexp_num(31), is_atom FALSE)

4       #TOPN2: [0, 50, 1142]; top nun50), top_off(0)

5         #SLCT2: [0, 100, 1142]; (XYGX_GS_TYSHXYDM_FR.QTMC )= '星際' AND XTGX_GS_TYSHXYDM. QYMC <'星%')

6           #CSCN2: [1198, 3216107, 1142]: INDEX33570817 (XYGX GS TYSHXYDM_FR)

二次分析

第一次進行優(yōu)化后，執(zhí)行計劃得到了改善，執(zhí)行時間也大大縮短。但因用戶需求，對昨天的語句增加了條件，導(dǎo)致語句運行緩慢，需要再次運行。

原語句：

select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,'yyyy-MM-dd')HZRQ,ZS,ZCDYB,FZRQ,CLRQ,HBZL,CYRS,HYDM,YYQXZHI,YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID from (

 select UUID,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,'yyyy-MM-dd')ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,HZRQ,ZS,ZCDYB,TO_CHAR(FZRQ,'yyyy-MM-dd')FZRQ,TO_CHAR(CLRQ,'yyyy-MM-dd')CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,'yyyy-MM-dd')YYQXZHI,TO_CHAR(YYQXZI,'yyyy-MM-dd')YYQXZI,XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID 

 from XYGX.XYGX_GS_TYSHXYDM_FR where QYMC like '星際%' or TYSHXYDM like '星際%'

 order by HZRQ desc limit 0,50);

原執(zhí)行計劃：

1   #NSET2: [0, 50, 1142]

2     #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

3       #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

4         #TOPN2: [0, 50, 1142]; top_num(50), top_off(0)

5           #SLCT2: [0, 100, 1142]; (exp11 > 0 OR exp11 > 0)

6             #BLKUP2: [0, 100, 1142]; IDX_HZRQ_QYMC(XYGX_GS_TYSHXYDM_FR)

7               #SSCN: [0, 100, 1142]; IDX_HZRQ_QYMC(XYGX_GS_TYSHXYDM_FR)

由于新增了條件字段，另外創(chuàng)建了索引，執(zhí)行計劃未得到改善

簡化語句

根據(jù)新語句創(chuàng)建索引：

CREATE  INDEX "IDX_HZRQ_QYMC_TYSHXYDM" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC","TYSHXYDM");

執(zhí)行計劃：

1   #NSET2: [0, 50, 1142]

2     #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

3       #PRJT2: [0, 50, 1142]; exp_num(31), is_atom(FALSE)

4         #TOPN2: [0, 50, 1142]; top_num(50), top_off(0)

5           #SLCT2: [0, 100, 1142]; (exp11 > 0 OR exp11 > 0)

6             #BLKUP2: [0, 100, 1142]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

7               #SSCN: [0, 100, 1142]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

較少字段進行覆蓋索引消除回表后計劃較好，但因使用的字段較多且有函數(shù)使用，無法對所有字段進行覆蓋索引。

通過ROWID消除回表

通過ROWID，簡化和修改語句，嘗試消除回表，消除回表后執(zhí)行計劃較好且執(zhí)行速度提升，改造后語句：

    select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR

                where QYMC like '%星際聯(lián)盟%'

                        or

                      TYSHXYDM like '%星際聯(lián)盟%' 

                       order by

                        HZRQ desc 

                         limit 0,50;

執(zhí)行計劃：                                                            

1   #NSET2: [0, 50, 116]

2     #PRJT2: [0, 50, 116]; exp_num(1), is_atom(FALSE)

3       #TOPN2: [0, 50, 116]; top_num(50), top_off(0)

4         #SLCT2: [0, 100, 116]; (exp11 > 0 OR exp11 > 0)

5           #SSCN: [0, 100, 116]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

改造語句驗證

改造原語句，使用ROWID做子查詢進行關(guān)聯(lián)消除回表，外部查詢通過聚集索引數(shù)據(jù)定位,得到了優(yōu)化；

select

        UUID

,QYMC,TYSHXYDM,SCJYD,ZCXS,JYZT,WZ,SCJYDXZQH,ZCH,QYLXDM,TO_CHAR(ZHYCNBSJ,'yyyy-MM-dd')ZHYCNBSJ,JYFW,ZCZB,ZCDZSZXXQH,TO_CHAR(HZRQ,'yyyy-MM-dd')HZRQ,ZS,ZCDYB,

        TO_CHAR(FZRQ,'yyyy-MM-dd')FZRQ,TO_CHAR(CLRQ,'yyyy-MM-dd')CLRQ,HBZL,CYRS,HYDM,TO_CHAR(YYQXZHI,'yyyy-MM-dd')YYQXZHI,TO_CHAR(YYQXZI,'yyyy-MM-dd')YYQXZI,

        XXCZLX,YWLX,DJJG,ZHYCNBND,FDDBR,ZTID                          

from XYGX.XYGX_GS_TYSHXYDM_FR

WHERE ROWID IN

(

    select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR

                where QYMC like '%星際聯(lián)盟%'

                        or

                      TYSHXYDM like '%星際聯(lián)盟%' 

                       order by

                        HZRQ desc 

                         limit 0,50          

        );

執(zhí)行計劃

1   #NSET2: [457, 50, 1258]

2     #PRJT2: [457, 50, 1258]; exp_num(31), is_atom(FALSE)

3       #NEST LOOP INDEX JOIN2: [457, 50, 1258]

4         #PRJT2: [446, 50, 116]; exp_num(1), is_atom(FALSE)

5           #DISTINCT: [446, 50, 116]

6             #PRJT2: [444, 50, 116]; exp_num(1), is_atom(FALSE)

7               #TOPN2: [444, 50, 116]; top_num(50), top_off(0)

8                 #SLCT2: [444, 160805, 116]; (exp11 > 0 OR exp11 > 0)

9                   #SSCN: [444, 3216107, 116]; IDX_HZRQ_QYMC_TYSHXYDM(XYGX_GS_TYSHXYDM_FR)

10        #CSEK2: [2, 1, 0]; scan_type(ASC), INDEX33570817(XYGX_GS_TYSHXYDM_FR), scan_range[DMTEMPVIEW_19959271.colname,DMTEMPVIEW_19959271.colname]

看完上述內(nèi)容，你們對like order by top組合語句的優(yōu)化是怎樣的有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝大家的支持。