or+orderby响应很慢
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】：Linux 5.15.0-46-generic #49~20.04.1-Ubuntu SMP Thu Aug 4 19:15:44 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
【CPU】:cpu cores : 16
【问题描述】*：400W数据
表结构为：
CREATE TABLE "HUIYING"."T_TEST_HH"
(
"ID" INT IDENTITY(1, 1) NOT NULL,
"NAME" VARCHAR(50),
"ADDRESS" VARCHAR(100),
"SEX" TINYINT,
"ALIAS" VARCHAR(50),
"AGE" INT,
"CREATE_TIME" TIMESTAMP(0),
"COLUMN_1" VARCHAR(255),
"COLUMN_2" VARCHAR(255),
"COLUMN_3" VARCHAR(255),
"COLUMN_4" VARCHAR(255),
"COLUMN_5" VARCHAR(255),
"COLUMN_6" TEXT,
NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE INDEX "index_name_add" ON "HUIYING"."T_TEST_HH"("NAME" ASC,"ADDRESS" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE INDEX "index_sex_age" ON "HUIYING"."T_TEST_HH"("SEX" ASC,"AGE" ASC,"CREATE_TIME" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE INDEX "index_sex_time" ON "HUIYING"."T_TEST_HH"("SEX" ASC,"CREATE_TIME" DESC) STORAGE(ON "MAIN", CLUSTERBTR) ;
sex 的值范围为 0-4；
select id from HUIYING.T_TEST_HH where sex = 0 order by create_time desc limit 0, 15
耗时 34毫秒
select id from HUIYING.T_TEST_HH where sex = 0 or sex = 1 order by create_time desc limit 0, 15
耗时 36.8秒
select id from HUIYING.T_TEST_HH where sex in(0,1) order by create_time desc limit 0, 15
耗时 32.4秒
select id from HUIYING.T_TEST_HH where sex in(0,1) limit 0, 15
耗时 34毫秒
DROP INDEX "index_sex_age" ;
DROP INDEX "index_sex_time";
CREATE INDEX "index_sex" ON "T_TEST_HH"("SEX") STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE INDEX "index_240102001" ON "T_TEST_HH"("CREATE_TIME" DESC,"SEX" ASC,"ID" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
计划消除了SORT；代价变成了对索引的全扫描，这个较小，索引数据进内存后跑应该不用2秒，对第1个语句是有负面影响的，所以也可以考虑保留那个索引，这里是从总体上处理~根据几个语句访问频率高低来抉择索引
SQL> explain select id from T_TEST_HH where sex in(0,1) order by create_time desc limit 0, 15;
1 #NSET2: [1, 15, 30]
2 #PRJT2: [1, 15, 30]; exp_num(2), is_atom(FALSE)
3 #TOPN2: [1, 15, 30]; top_num(15), top_off(0)
4 #SLCT2: [1, 300, 30]; T_TEST_HH.SEX IN LIST
5 #SSCN: [1, 300, 30]; index_240102001(T_TEST_HH)
used time: 0.553(ms). Execute id is 0.
SQL> explain select id from T_TEST_HH where sex = 0 or sex = 1 order by create_time desc limit 0, 15;
1 #NSET2: [1, 15, 30]
2 #PRJT2: [1, 15, 30]; exp_num(2), is_atom(FALSE)
3 #TOPN2: [1, 15, 30]; top_num(15), top_off(0)
4 #SLCT2: [1, 300, 30]; T_TEST_HH.SEX IN LIST
5 #SSCN: [1, 300, 30]; index_240102001(T_TEST_HH)
used time: 0.757(ms). Execute id is 0.
SQL> explain select id from T_TEST_HH where sex = 0 order by create_time desc limit 0, 15;
1 #NSET2: [1, 1, 30]
2 #PRJT2: [1, 1, 30]; exp_num(2), is_atom(FALSE)
3 #TOPN2: [1, 1, 30]; top_num(15), top_off(0)
4 #SLCT2: [1, 1, 30]; T_TEST_HH.SEX = 0
5 #SSCN: [1, 1, 30]; index_240102001(T_TEST_HH)
used time: 1.099(ms). Execute id is 0.
SQL> explain select id from T_TEST_HH where sex in(0,1) limit 0, 15;
1 #NSET2: [1, 1, 17]
2 #PRJT2: [1, 1, 17]; exp_num(2), is_atom(FALSE)
3 #TOPN2: [1, 1, 17]; top_num(15), top_off(0)
4 #NEST LOOP INDEX JOIN2: [1, 1, 17]
5 #CONST VALUE LIST: [1, 2, 1]; row_num(2), col_num(1),
6 #BLKUP2: [1, 1, 17]; index_sex(T_TEST_HH)
7 #SSEK2: [1, 1, 17]; scan_type(ASC), index_sex(T_TEST_HH), scan_range[DMTEMPVIEW_889193786.colname,DMTEMPVIEW_889193786.colname]
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
我认为写SQL尽量避免用or，可以先把or换成union all试试