like order by top組合語(yǔ)句的優(yōu)化是怎樣的

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

環(huán)境信息

基本信息

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

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

頁(yè)大?。?6K

大小寫敏感：是

字符集：GB18030

數(shù)據(jù)庫(kù)基本參數(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é)果如下，該表工三百二十多萬(wàn)條數(shù)據(jù)。

count(*)

3216107

原語(yǔ)句

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í)行計(jì)劃

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)

分析過程

基本思路

排序消耗的了大量資源，可以通過索引消除排序的方式進(jìn)行優(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í)行計(jì)劃緩存后執(zhí)行計(jì)劃沒有變化。

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í)行計(jì)劃

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

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

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

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

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

sp_set_para_value(1,'TOP_ORDER_OPT_FLAG'1);

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

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)

簡(jiǎn)化語(yǔ)句測(cè)試

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

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

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í)行計(jì)劃

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)

二次分析

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

原語(yǔ)句：

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í)行計(jì)劃：

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ì)劃未得到改善

簡(jiǎn)化語(yǔ)句

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

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

執(zhí)行計(jì)劃：

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)

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

通過ROWID消除回表

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

    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í)行計(jì)劃：                                                            

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)

改造語(yǔ)句驗(yàn)證

改造原語(yǔ)句，使用ROWID做子查詢進(jìn)行關(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í)行計(jì)劃

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)容，你們對(duì)like order by top組合語(yǔ)句的優(yōu)化是怎樣的有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。