达梦数据库sql优化实践1

QXJ项目上线前对系统进行压测，数据库并发响应能力不能满足客户“600并发3秒响应”的要求，达梦数据库服务人员对客户系统中的sql语句进行优化，其中一条sql优化过程如下：
Sql语句如下（sql已经过简化，表名已做模糊化处理）：
select
TAB1.NGRQ as TAB1_NGRQ
from
SCHEMA1.G_TAB2 as TAB2
inner join SCHEMA1.TAB1 as TAB1
on
TAB1.ID = TAB2.INFO_ID
where
TAB1.ROWSTATE = ‘1’
AND TAB2.IS_COLJ=‘1’
AND TAB2.USER_ID=‘51120099’
AND TAB1.STATUS >0
AND TAB1.STATUS <>9
order by
TAB1.CREATE_TIME desc;
该sql是一个非常简单的两表等值关联操作，经过现场检查TAB2表经过“TAB2.IS_COLJ='1’AND TAB2.USER_ID=‘51120099’”两个条件筛选后的结果集为1300行左右，可以作为驱动行源。
相关表结构如下：
CREATE TABLE “SCHEMA1”.“TAB1”
(

“ID” VARCHAR(400) NOT NULL,
“PB” VARCHAR(400),
“ZCZD” VARCHAR(16),
“ZCZD_TEXT” VARCHAR(160),
“REMOTE_ID” VARCHAR(400),
“SOURCE_ID” VARCHAR(400),
“MODULE_ID” VARCHAR(400),
“MODULE_NAME” VARCHAR(400),
“COLJ_ID” VARCHAR(400),
“PID” VARCHAR(400),
“FORM_ID” VARCHAR(400),
“STATUS” DEC(2,0) DEFAULT 0,
“STATUS_TEXT” VARCHAR(400),
“CREATE_USERID” VARCHAR(400),
“CREATE_UNAME” VARCHAR(400),
“CREATE_DEPTID” VARCHAR(400),
“CREATE_UNITID” VARCHAR(400),
“CREATE_TIME” TIMESTAMP(0),
“LASTUPDATE_USERID” VARCHAR(400),
“LASTUPDATE_UNAME” VARCHAR(400),
“LASTUPDATE_DEPTID” VARCHAR(400),
“LASTUPDATE_UNITID” VARCHAR(400),
“LASTUPDATE_TIME” TIMESTAMP(0),
“INFO_VERSION” DEC(9,0) DEFAULT 1,
“ROWSTATE” DEC(2,0) DEFAULT 1,
“HANDLE_USERS_ID” TEXT,
“HANDLE_USERS” TEXT,
“HANDLE_USERS_SCHEMA” TEXT,
“CREATE_DEPTNAME” VARCHAR(400),
“CREATE_UNITNAME” VARCHAR(400),
“LASTUPDATE_DEPTNAME” VARCHAR(400),
“LASTUPDATE_UNITNAME” VARCHAR(400),
“ATTACH_COUNT” DEC(5,0) DEFAULT 0,
“USER_ID” VARCHAR(400),
“USER_NAME” VARCHAR(400),
“FID” VARCHAR(400),
“SHOWORDER” DEC(9,0) DEFAULT 1,
“MD5CODE” VARCHAR(400),
“ORGID_LV1” VARCHAR(400),
“ORGID_LV2” VARCHAR(400),
“ORGID_LV3” VARCHAR(400),
“ORGID_LV4” VARCHAR(400),
“ORGID_LV5” VARCHAR(400),
“HANDLE_NODENAME” TEXT,
“HANDLE_STAGENAME” VARCHAR(400),
“ORGNAME_LV1” VARCHAR(320),
“TEMPSTATUS” DEC(2,0) DEFAULT 1,
“IS_DISTRIBED” DEC(2,0) DEFAULT 0,
“IS_LEADER_SIGN” DEC(2,0) DEFAULT 0,
“IS_LEADER_OPINION” DEC(2,0) DEFAULT 0,
“HANDLE_ORIGINAL_USER_ID” VARCHAR(800),
“HANDLE_ORIGINAL_USER_NAME” VARCHAR(800),
“BT” VARCHAR(8188),
“WH_PERIOD” DEC(9,0),
“WH_RULEID” VARCHAR(400),
“WH_NUM” DEC(9,0),
“WH_TNUM” DEC(9,0),
“WH_RULEORDER” DEC(9,0),
“WH_ORDER” VARCHAR(400),
“WH” VARCHAR(400),
“JJCD_TEXT” VARCHAR(400),
“JJCD” DEC(2,0) DEFAULT 0,
“NGR” VARCHAR(400),
“NGBM” VARCHAR(400),
“NGBM_TEXT” VARCHAR(400),
“NGTEL” VARCHAR(400),
“XXGK” DEC(2,0) DEFAULT 0,
“XXGK_TEXT” VARCHAR(400),
“ARCHIVE_PERIOD” DEC(2,0) DEFAULT 0,
“ARCHIVE_PERIOD_TEXT” VARCHAR(400),
“INFO_CATAGORY” VARCHAR(400),
“INFO_CATAGORY_TEXT” VARCHAR(400),
“BNHQ” TEXT,
“BNHQ_TEXT” TEXT,
“BNHQ_SCHEMA” TEXT,
“ZSDW” TEXT,
“ZSDW_TEXT” TEXT,
“CSDW_TEXT” TEXT,
“CSDW” TEXT,
“HQBM_TEXT” VARCHAR(4000),
“HQBM” VARCHAR(4000),
“HQBM_SCHEMA” VARCHAR(4000),
“COLKR_TEXT” VARCHAR(400),
“COLKR” VARCHAR(400),
“TAB1LX” VARCHAR(400),
“TAB1LX_TEXT” VARCHAR(400),
“HBDW” TEXT,
“HBDW_TEXT” TEXT,
“LHNGR” TEXT,
“LHNGR_TEXT” TEXT,
“LHNGR_SCHEMA” TEXT,
“DWLHTAB1” VARCHAR(4000),
“DWLHTAB1_TEXT” VARCHAR(4000),
“DWLHTAB1_SCHEMA” VARCHAR(4000),
“WDWLHTAB1” VARCHAR(4000),
“WDWLHTAB1_TEXT” VARCHAR(4000),
“NGRQ” TIMESTAMP(0),
“DWGKSX” VARCHAR(240),
“DWGKSX_TEXT” VARCHAR(240),
“COLKSJ” TIMESTAMP(0),
“PBSJ” TIMESTAMP(0),
“IS_AFTER_LEADER” DEC(2,0) DEFAULT 0,
“IS_ROUTINE_DOCUMENT” DEC(2,0) DEFAULT 0,
“QFRQ” TIMESTAMP(0),
“QFR_TEXT” VARCHAR(400),
“QFR” VARCHAR(400),
“SHARE_TYPE” VARCHAR(80) DEFAULT ‘1’,
“IS_HAS_SIGN” DEC(2,0) DEFAULT 0,
“RED_OF_SET” VARCHAR(1600),
“GWZT_TEXT” VARCHAR(400),
“DWZT” VARCHAR(400),
“GWZT” VARCHAR(400),
“UNIONFILES” DEC(2,0) DEFAULT 0,
“UNIONFID” VARCHAR(400) DEFAULT ‘NO’,
“PB_TEXT” VARCHAR(800),
“MAIN_HANDLE_UNIT” VARCHAR(400),
“MAIN_HANDLE_UNIT_TEXT” VARCHAR(400),
“MAIN_HANDLE_UNIT_SCHEMA” VARCHAR(800),
“BJKEC4LGSYKUH8PS2XB” VARCHAR(4000),
“BZ” TEXT,
“END_TIME” TIMESTAMP(0),
“MJ” DEC(2,0) DEFAULT 1,
“MJ_TEXT” VARCHAR(400),
“BMQX” VARCHAR(400),
“BMQX_TEXT” VARCHAR(400),
“NGDW” VARCHAR(400),
“DYFS” DEC(4,0) DEFAULT 1,
“GWWZ_TEXT” VARCHAR(400),
“GWWZ” DEC(2,0),
“BLQX” TIMESTAMP(0),
“HAS_FINALIZATION” DEC(2,0) DEFAULT (-1),
“MASTERID” VARCHAR(256),
“IS_HISTORYFILE” DEC(2,0) DEFAULT (-1),
“IS_HISTORYFILE_TEXT” VARCHAR(160),
“DMZRRQZ” VARCHAR(400),
“DMZRRQZ_ID” VARCHAR(400),
“DMZRRQZ_TIME” TIMESTAMP(0),
“FUZHU_WH” VARCHAR(400),
“FUZHU_WH_RULEID” VARCHAR(400),
“FUZHU_WH_PERIOD” DEC(9,0),
“FUZHU_WH_TNUM” DEC(9,0),
“FUZHU_WH_NUM” DEC(9,0),
“FUZHU_WH_RULEORDER” DEC(9,0),
“FUZHU_WH_ORDER” DEC(9,0),
“IS_REMOTESEND” DEC(2,0) DEFAULT (-1),
“IS_REMOTESEND_TEXT” VARCHAR(160),
“FULL_ELECTRONIC” DEC(2,0) DEFAULT 1,
“FULL_ELECTRONIBJKTEXT” VARCHAR(160),
“START_NUMBER” DEC(4,0),
“CBLD” VARCHAR(4000) DEFAULT ‘1’,
“CBLD_TEXT” VARCHAR(4000),
“CBLD_SCHEMA” VARCHAR(4000),
“NBXXGK” DEC(2,0) DEFAULT 1,
“NBXXGK_TEXT” VARCHAR(160),
“DMYJ” VARCHAR(160),
“DMYJ_TEXT” VARCHAR(400),
“DMYY” TEXT,
“SFGK_VALUE” DEC(2,0),
“SFGK_TEXT” VARCHAR(160),
“FSDW” TEXT,
“FSDW_TEXT” TEXT,
“FCOLY_TEXT” VARCHAR(400),
“FCOLY” VARCHAR(400),
“FCOLY_SCHEMA” VARCHAR(400),
“COLX_NUM” DEC(9,0),
“COLX_TNUM” DEC(9,0),
“COLX_PERIOD” DEC(9,0),
“COLX_RULEID” VARCHAR(400),
“COLX” VARCHAR(400),
“SFLHTAB1” VARCHAR(80) DEFAULT ‘-1’,
“SFLHTAB1_TEXT” VARCHAR(80),
“SFBWHQ” VARCHAR(80) DEFAULT ‘-1’,
“SFBWHQ_TEXT” VARCHAR(80),
“JHDM” VARCHAR(800),
“CNSCOLY” VARCHAR(400),
“CNSCOLY_TEXT” VARCHAR(400),
“CNSCOLY_SCHEMA” VARCHAR(800),
“CNSHDATE” TIMESTAMP(0),
“ZHCSCOLY” VARCHAR(400),
“ZHCSCOLY_TEXT” VARCHAR(400),
“ZHCSCOLY_SCHEMA” VARCHAR(800),
“ZHCSHDATE” TIMESTAMP(0),
“MSCSCOLY” VARCHAR(400),
“MSCSCOLY_TEXT” VARCHAR(400),
“MSCSCOLY_SCHEMA” VARCHAR(800),
“MSCSHDATE” TIMESTAMP(0),
“MSCCOLX_PERIOD” DEC(9,0),
“MSCCOLX” VARCHAR(400),
“MSCCOLX_RULEID” VARCHAR(400),
“MSCCOLX_NUM” DEC(9,0),
“MSCCOLX_TNUM” DEC(9,0),
“MSCCOLX_RULEORDER” VARCHAR(400),
“MSCCOLX_ORDER” VARCHAR(400),
“CBDW” TEXT,
“CBDW_TEXT” TEXT,
“ZBBM” VARCHAR(400),
“ZBBM_TEXT” VARCHAR(400),
“ZBBM_SCHEMA” VARCHAR(1600),
“GWBS” VARCHAR(400),
“WH_TEXT” VARCHAR(400),
“CWRQ” TIMESTAMP(0),
“BJRQ” TIMESTAMP(0),
“GWYS” DEC(5,0),
“SWRQ” TIMESTAMP(0),
“IMPORTANT” DEC(2,0),
“IMPORTANT_TEXT” VARCHAR(400),
“FBCC” DEC(2,0),
“FBCBJKTEXT” VARCHAR(160),
“SUPERIOR_LEADER_OPINION” TEXT,
“LEADER_OPINION” TEXT,
“IS_REMOTE” DEC(2,0) DEFAULT (-1),
“ARCHIVE_STATUS” DEC(2,0) DEFAULT 0,
“ARCHIVE_DATE” TIMESTAMP(0),
“ZXTAB1” TEXT,
“BLQX_DAYS” DEC(5,0) DEFAULT (-1),
“ALLOW_MOBILE” DEC(2,0) DEFAULT 1,
“ALLOW_MOBILE_TEXT” VARCHAR(160),
“NGD_BACKREASON” TEXT,
“NEED_REMOTE_FEEDBACK” DEC(2,0) DEFAULT (-1),
“IS_MAIN_LEADER” DEC(2,0) DEFAULT (-1),
“IS_UPSIDE” DEC(2,0) DEFAULT (-1),
“CSBM_TEXT” VARCHAR(4000),
“CSBM” VARCHAR(4000),
“CSBM_SCHEMA” VARCHAR(4000),
“ZTC” VARCHAR(1600),
“IS_REMOTE_TEXT” VARCHAR(400),
“ARCHIVE_STATUS_TEXT” VARCHAR(400),
“BARCODE_PATH” VARCHAR(1600),
“NGR_RANK” VARCHAR(400),
“MERGE_MAINID” VARCHAR(256),
“NGR_MOBILE” VARCHAR(400),
“REMOTE_SEND_DATE” TIMESTAMP(0),
“XXGK_WJ” TEXT,
“FUZHU1_WH” VARCHAR(400),
“FUZHU11_WH_RULEID” VARCHAR(400),
“FUZHU1_WH_PERIOD” DEC(9,0),
“FUZHU1_WH_NUM” DEC(9,0),
“FUZHU1_WH_TNUM” DEC(9,0),
“FUZHU1_WH_RULEORDER” DEC(9,0),
“FUZHU1_WH_ORDER” DEC(9,0),
“INFOORDER” DEC(9,0) DEFAULT 1,
“MYGKYY” TEXT,
“ASSIST_HANDLE_UNIT” VARCHAR(400),
“ASSIST_HANDLE_UNIT_TEXT” VARCHAR(400),
“ASSIST_HANDLE_UNIT_SCHEMA” VARCHAR(800),
“NEED_REMOTE_FEEDBACK_TEXT” VARCHAR(160),
“IS_MAIN_LEADER_TEXT” VARCHAR(160),
“IS_SEND_FORM” DEC(2,0) DEFAULT (-1),
“IS_SEND_FORM_TEXT” VARCHAR(160),
“IS_TOP” DEC(2,0) DEFAULT (-1),
“IS_TOP_TEXT” VARCHAR(160),
“TOP_ACTION_TIME” TIMESTAMP(0),
“PUBLIBJKBZ” TEXT,
“WCOLXQYJ” TEXT,
“FPZJ” DEC(2,0),
“FPZJ_TEXT” VARCHAR(160),
“YFRQ” TIMESTAMP(0),
“QFNR” TEXT,
“LRJH” VARCHAR(160),
“LRJH_TEXT” VARCHAR(400),
“SFGFXWJ” VARCHAR(160),
“SFGFXWJ_TEXT” VARCHAR(400),
“LHTAB1DW” VARCHAR(4000),
“LHTAB1DW_TEXT” VARCHAR(4000),
“LHTAB1DW_SCHEMA” TEXT,
“YWZZ” DEC(2,0),
“YWZZ_TEXT” VARCHAR(160),
“ZZSFXF” DEC(2,0),
“ZZSFXF_TEXT” VARCHAR(160),
“LXR” VARCHAR(400),
“LXRDH” VARCHAR(400),
“SQFB” TEXT,
“SQFB_TEXT” TEXT,
“TAB1DW_TEXT” VARCHAR(400),
“TAB1DW” VARCHAR(400),
“ZBBMFZR” VARCHAR(400),
“ZBBMHGR” TEXT,
“ZBBMHGR_TEXT” TEXT,
“BGSHGR” TEXT,
“BGSHGR_TEXT” TEXT,
“LHTAB1CWRQ” TIMESTAMP(0),
“ISSIGN” DEC(2,0),
“IS_REG” DEC(2,0),
“SIGN_TIME” TIMESTAMP(0),
“COLXR” TEXT,
“INDEX_STATUS” DEC(2,0) DEFAULT (-1),
“THMB” TEXT,
“THMB_TEXT” TEXT,
“WJCOLX” TEXT,
“TAB1ZL” DEC(2,0),
“FPTAB1LX” DEC(2,0),
“YYQX” TIMESTAMP(0),
“HWH_VALUE” DEC(6,0),
“SFNB” DEC(2,0),
“FH_VALUE” VARCHAR(80),
“ZBCOLX_VALUE” TEXT,
“PBRQ” VARCHAR(80),
“COLKRQ” VARCHAR(80),
“COLKZS” VARCHAR(400),
“COLKRRQ” VARCHAR(80),
“COLKR2” VARCHAR(400),
“COLKR2_TEXT” VARCHAR(400),
“NGRCOLKZS” VARCHAR(400),
“WJCOLKZS” VARCHAR(400),
“PBZS” VARCHAR(400),
“IS_UPSIDE_TETX” VARCHAR(160),
“IS_STAMP” DEC(2,0) DEFAULT 0,
“IS_STAMP_TEXT” VARCHAR(400),
“ZBBMFZR_TEXT” VARCHAR(400),
“ZBBMFZR_SCHEMA” VARCHAR(4000),
“ZBBMFZRDH” VARCHAR(400) DEFAULT ‘根据通讯录自动生成’,
“BGSZR_TEXT” VARCHAR(400),
“BGSZR” VARCHAR(400),
“BGSZRDH” VARCHAR(400) DEFAULT ‘根据通讯录自动生成’,
“WH_RULEOEDER” VARCHAR(400),
“DOBJKTRANSLATE_TYPE” VARCHAR(800),
“DOBJKTYPE” VARCHAR(400),
“SYNBJKID” VARCHAR(400),
“LW_ID” VARCHAR(400),
“GWLB_TEXT” VARCHAR(400),
“GWLB” VARCHAR(400),
“OPEN_SCOPE” VARCHAR(400),
“OPEN_SCOPE_TEXT” VARCHAR(400),
“ACTION_MARK” VARCHAR(400),
“ACTION_MARK_TEXT” VARCHAR(800),
“BGSZR_SCHEMA” VARCHAR(4000),
“ZBBMHGR_SCHEMA” VARCHAR(400),
“MERGE_FID” VARCHAR(400),
“SX_TYPE” VARCHAR(160),
“SX_TYPE_TEXT” VARCHAR(400),
“DMZRR” VARCHAR(400),
“DMZRR_TEXT” VARCHAR(400),
“DMZRR_SCHEMA” VARCHAR(400),
“TAB1JG_TEXT” VARCHAR(4000),
“ISSIGN_TEXT” VARCHAR(400),
“ISSIGN_TEST” VARCHAR(400),
“WQDZJ” VARCHAR(160),
“WQDZJ_TEXT” VARCHAR(400),
“TAB1ZLTEST” VARCHAR(160),
“TAB1ZLTEXT” VARCHAR(160),
“TAB1ZL_TEXT” VARCHAR(400),
“HANDLE_STEPNAME” VARCHAR(400),
“PUBSTATE” VARCHAR(256),
“DSYS” VARCHAR(400),
“CSYS” VARCHAR(400),
“REBTB1_VALUE” TEXT,
“DWLX_VALUE” DOUBLE,
“SFYJCOLYQ” TIMESTAMP(0),
“ISZWB” DEC(1,0),
“SFSGDJ” DEC(2,0) DEFAULT 0,
“IS_UPSIDE_TEXT” VARCHAR(160),
“GWJHTAB1JG” TEXT,
“ZSDW_SCHEMA” TEXT,
“CSDW_SCHEMA” TEXT,
“BJKOBCLGXAU163WA5QI” VARCHAR(4000),
“BJKOWNQQ4C1KCVFRQ3A” VARCHAR(4000),
“BJK210413115016IGGI8IYBPY0KDRF8PRA” VARCHAR(4000),
“ISCSGW” NUMBER(2,0),
“ISCSGW_TEXT” VARCHAR2(50),
“IS_CSGW” DECIMAL(2,0),
“IS_CSGW_TEXT” VARCHAR2(50),
“WHLX” VARCHAR2(400),
“WHLX_TEXT” VARCHAR2(400),
“BJK210301154301RYXAEPGLVNDJT01SDVO” TIMESTAMP(6),
“BJK210301154426SZSBQM5FDQ5P3MITBXB” TIMESTAMP(6),
“BJKIUTA97SHGDGH5J4O” VARCHAR2(4000),
“BJK7OZGIWHMBBAHHLBY” TIMESTAMP(6),
“BJKTCONDLXLWWCOLK0V1N” TIMESTAMP(6),
“BJKDSLTAAKVJ9KWDIQ1” TIMESTAMP(6),
“BJK0CFTFJNTEFDLKDZ4” TIMESTAMP(6),
“BJKJPF7OWO9RXM0DYTC” TIMESTAMP(6),
“BJKBSLT1W2J2NYSYTRBJKTIME” TIMESTAMP(6),
“BJK210716164217PLWDX8MXAHTWUAP7KEN” VARCHAR2(4000),
“BJKPCLVCUUOHYJ7JGQO” TIMESTAMP(6),
“BJK3PZG4GHLC3N1QFIK” TIMESTAMP(6),
“BJKBMSITY5GRMNYMXUU” TIMESTAMP(6),
“BJK2UR0XSVLPZCKWK5F” TIMESTAMP(6),
“BJKCKPEB6EZAXJN8R38” TIMESTAMP(6),
“BJKKVCRXFBFBEF7SCKX” CLOB,
NOT CLUSTER PRIMARY KEY(“ID”)) STORAGE(ON “SCHEMA1”, CLUSTERBTR) ;

CREATE INDEX “IX_TAB1_WH_QUERY” ON “SCHEMA1”.“TAB1”(“WH” ASC,“WH_RULEID” ASC,“WH_PERIOD” ASC) STORAGE(ON “SCHEMA1”, CLUSTERBTR) ;
CREATE INDEX “IX_TAB1_FID” ON “SCHEMA1”.“TAB1”(“FID” ASC) STORAGE(ON “SCHEMA1”, CLUSTERBTR) ;
CREATE INDEX “idx_TAB1_formid” ON “SCHEMA1”.“TAB1”(“FORM_ID” ASC,“STATUS” ASC,“CREATE_TIME” DESC) STORAGE(ON “SCHEMA1”, CLUSTERBTR) ;

语句执行计划如下：
1 #NSET2: [1, 106, 258]
2 #PRJT2: [1, 106, 258]; exp_num(1), is_atom(FALSE)
3 #SORT3: [1, 106, 258]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4 #SLCT2: [1, 106, 258]; (TAB1.ROWSTATE = var4 AND TAB1.STATUS > var5 AND TAB1.STATUS <> var6)
5 #HASH2 INNER JOIN: [1, 106, 258]; RKEY_UNIQUE KEY_NUM(1); KEY(TAB2.INFO_ID=TAB1.ID) KEY_NULL_EQU(0)
6 #SLCT2: [1, 106, 258]; (TAB1.ROWSTATE = var1 AND TAB1.STATUS > var2 AND TAB1.STATUS <> var3)
7 #NEST LOOP INDEX JOIN2: [1, 106, 258]
8 #ACTRL: [1, 106, 258];
9 #SLCT2: [1, 106, 126]; TAB2.IS_COLJ = var7
10 #BLKUP2: [1, 106, 126]; IDX_TAB2_INFO_USERID(TAB2)
11 #SSEK2: [1, 106, 126]; scan_type(ASC), IDX_TAB2_INFO_USERID(G_TAB2 as TAB2), scan_range[(‘51120099’,min),(‘51120099’,max))
12 #BLKUP2: [1, 1, 0]; INDEX33556658(TAB1)
13 #SSEK2: [1, 1, 0]; scan_type(ASC), INDEX33556658(TAB1 as TAB1), scan_range[TAB2.INFO_ID,TAB2.INFO_ID]
14 #CSCN2: [40, 306846, 132]; INDEX33555764(TAB1 as TAB1)
其中第8行的“#ACTRL: [1, 106, 258]”表示该sql采用了“自适应”执行路径，添加hint“ADAPTIVE_NPLN_FLAG（0）”后执行计划变为：
1 #NSET2: [70, 1, 390]
2 #PRJT2: [70, 1, 390]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [70, 1, 390]; grp_num(0), sfun_num(1) slave_empty(0)
4 #HASH2 INNER JOIN: [70, 1, 390]; LKEY_UNIQUE KEY_NUM(1); KEY(module.ID=TAB1.MODULE_ID) KEY_NULL_EQU(0)
5 #SSEK2: [1, 8, 96]; scan_type(ASC), ind_module_fid(G_MODULE as module), scan_range[(‘210203171124mfe5gqH8Bt4sKviGhXl’,min),(‘210203171124mfe5gqH8Bt4sKviGhXl’,max))
6 #SLCT2: [69, 90, 294]; (TAB1.ROWSTATE = var1 AND TAB1.STATUS > var2 AND TAB1.STATUS <> var3)
7 #HASH2 INNER JOIN: [69, 90, 294]; RKEY_UNIQUE KEY_NUM(1); KEY(TAB2.INFO_ID=TAB1.ID) KEY_NULL_EQU(0)
8 #SLCT2: [1, 106, 126]; TAB2.IS_COLJ = var4
9 #BLKUP2: [1, 106, 126]; IDX_TAB2_INFO_USERID(TAB2)
10 #SSEK2: [1, 106, 126]; scan_type(ASC), IDX_TAB2_INFO_USERID(G_TAB2 as TAB2), scan_range[(‘51120099’,min),(‘51120099’,max))
11 #CSCN2: [42, 306846, 168]; INDEX33555764(TAB1 as TAB1)
同时执行时间由2秒左右下降至0.05秒左右，达到优化目标。
社区地址：https://eco.dameng.com