like order by top组合语句的优化是怎样的
今天就跟大家聊聊有关like order by top组合语句的优化是怎样的，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
环境信息
基本信息
操作系统：CentoOS 7.2
数据库版本：DM Database Server x64 V7. 1.5.202-Bu1 1d(2017.07.17-82922 ) ENT
页大小：16K
大小写敏感：是
字符集：GB18030
数据库基本参数
MAX_OS_MEMORY | 50 |
MEMORY_POOL | 80 |
MEMORY_TARGET | 0 |
MEMORY_MAGIC_CHECK | 0 |
BUFFER | 10000 |
BUFFER_POOLS | 19 |
RECYCLE | 64 |
RECYCLE_POOLS | 1 |
MAX_BUFFER | 10000 |
HJ_BUF_GLOBAL_SIZE | 500 |
HJ_BUF_SIZE | 50 |
DICT_BUF_SIZE | 5 |
VM_POOL_SIZE | 54 |
SESS_POOL_SIZE | 16 |
USE_PLN_POOL | 1 |
VIEU_PULLUP_FLAG | 0 |
OPTIMIZER_MODE | 0 |
OLAP_FLAG | 2 |
TEMP_SIZE | 10 |
CACHE_POOL_SIZE | 10 |
PURGE_DEL_OPT | 0 |
COMPATIBLE_MODE | 0 |
表结构
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) ;
表数据量
查询表的数据量
select count(*) from XYGX.XYGX_GS_TYSHXYDM_FR
查询结果如下，该表工三百二十多万条数据。
count(*)
3216107
原语句
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 '星际%' order by HZRQ desc limit 0,50;
原执行计划
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)
分析过程
基本思路
排序消耗的了大量资源，可以通过索引消除排序的方式进行优化。
创建索引
创建相关索引，把排序列放在前，筛选列放在后。
CREATE INDEX "IDX_HZRQ_QYMC" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC" DESC) ;
创建索引且清除执行计划缓存后执行计划没有变化。
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)
清除执行计划
查出语句相关的计划信息，获取计划的CACHE_ITEM
select * from v$CACHEPLN where sqlstr like ‘%where QYMC like 星际%’;
根据CACHE_ITEM 清除执行计划
call sp_clear_plan_cache(139845509285864);
检查TOP_ORDER_OPT_FLAG
检查TOP_ORDER_OPT_FLAG：
select * from v$dm_ini where para_name like ‘%TOP%’;
查到结果是TOP_ORDER_OPT_FLAG当前只为0，未开启排序优化。
参数说明：
改参数是动态参数，当语句内含有TOP + ORDER，且ORDER BY列属于索引前导列时，如果该值为1，则根据ORDER BY列对应的基本信息，减少估算的行数从而减少代价计算。当该值为0怎不进行优化。
动态调整参数：
sp_set_para_value(1,'TOP_ORDER_OPT_FLAG'1);
调整后执行计划依然不变，另外开窗口并再清除执行计划也不变。
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)
简化语句测试
通过简化发现如果把语句总order by列的to_char函数去掉，执行计划就正常走索引，消除了排序，在order和to_char同时使用一个字段时通过索引消除排序方式不可用
优化思路，通过使用嵌套的方式消除了这种问题，改造后语句：
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 '星际%' order by HZRQ desc limit 0,50)
;
改造后执行计划
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)
二次分析
第一次进行优化后，执行计划得到了改善，执行时间也大大缩短。但因用户需求，对昨天的语句增加了条件，导致语句运行缓慢，需要再次运行。
原语句：
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);
原执行计划：
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)
由于新增了条件字段，另外创建了索引，执行计划未得到改善
简化语句
根据新语句创建索引：
CREATE INDEX "IDX_HZRQ_QYMC_TYSHXYDM" ON "XYGX"."XYGX_GS_TYSHXYDM_FR"("HZRQ" DESC,"QYMC","TYSHXYDM");
执行计划：
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)
较少字段进行覆盖索引消除回表后计划较好，但因使用的字段较多且有函数使用，无法对所有字段进行覆盖索引。
通过ROWID消除回表
通过ROWID，简化和修改语句，尝试消除回表，消除回表后执行计划较好且执行速度提升，改造后语句：
select ROWID from XYGX.XYGX_GS_TYSHXYDM_FR
where QYMC like '%星际联盟%'
or
TYSHXYDM like '%星际联盟%'
order by
HZRQ desc
limit 0,50;
执行计划：
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做子查询进行关联消除回表，外部查询通过聚集索引数据定位,得到了优化；
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 '%星际联盟%'
or
TYSHXYDM like '%星际联盟%'
order by
HZRQ desc
limit 0,50
);
执行计划
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]
看完上述内容，你们对like order by top组合语句的优化是怎样的有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/59.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
