sql如何利用索引消除排序
本篇内容介绍了“sql如何利用索引消除排序”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
1.1.1 现象描述我们经常碰到这种SQL:
Select * from tab where col1 >‘x’ order by col2 desc limit 0,5;
执行时间很长,需要优化。
这种问题,我们在首页展示时经常碰到。这里我们介绍两个经典的优化思路。
1.1.2 处理方法1.1.2.1 使用环境数据库版本:DM Database Server x64 V8.1.0.156-Build(2019.05.05-106384)SEC
环境:个人pc环境
1.1.2.2 构造数据说明:
新建一个50列的表,并插入5w数据。
代码:
drop table t_test_1;
-- 创建一个 50 列的表
declare
tb_s int default 1;
col_s int default 50;
a_sql clob default 'create table t_test_';
--b_sql clob default 'create huge table ht_test_';
begin
for rrs in 1..tb_s loop
a_sql:=a_sql||rrs||'(';
--b_sql=b_sql||rrs||'(';
for rs in 1..col_s-1 loop
a_sql:=a_sql||'col_'||rs||' varchar(50),';
--b_sql=b_sql||'col_'||rs||' varchar(50),';
end loop;
-- a_sql:=a_sql||'col_'||col_s||' int);'; -- 不带主键
a_sql:=a_sql||'col_'||col_s||' int primary key,col_end datetime)';
--b_sql=b_sql||'col_'||col_s||' int primary key)';
execute IMMEDIATE a_sql;
-- dbms_output.put_line(a_sql);
--execute immediate b_sql;
--select cast(b_sql as varchar);
a_sql:='create table t_test_';
--b_sql='create huge table ht_test_';
end loop;
NULL;
end;
-- 生成50000测试数据
--初始化基础数据
declare
tb_s int default 1;
col_s int default 50;
ROW_S INT DEFAULT 50000;
a_sql clob default 'INSERT INTO t_test_';
commit_i int default 10000;
begin
for rrs in 1..tb_s loop
--for rrs in 9..tb_s loop
FOR RRRS IN 1..ROW_S LOOP
a_sql:=a_sql||rrs||' VALUES(';
for rs in 1..col_s-1 loop
a_sql:=a_sql||' dbms_random.string(''x'',50),';
end loop;
a_sql:=a_sql||RRRS||',sysdate-dbms_random.value(1000000))';
EXECUTE immediate a_sql;
-- dbms_output.put_line (a_sql);
--select cast(a_sql as varchar);
a_sql:='INSERT INTO t_test_';
commit_i:=commit_i-1;
if(commit_i =0)then
commit;
commit_i:=10000;
end if;
END LOOP;
end loop;
NULL;
end;
1.1.2.3 查看计划和优化语句1.1.2.4 要查询的SQL、计划和执行时间说明:这里所有的执行,都不取第一次的执行时间;执行比为【当前方法的执行时间/所有方法的最小执行时间】
说明
处理
语句
计划
执行时间(秒)
执行比
A0 【原语句和计划和执行时间】
null
select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [217, 5, 2376]
2 #PRJT2: [217, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [217, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #SLCT2: [38, 2500, 2376]; T_TEST_1.COL_14 > '1'
5 #CSCN2: [38, 50000, 2376]; INDEX33559006(T_TEST_1)
0.078
9.75
A1 【常规优化 1 ,给col_14添加索引】
create index idx_t_test_1_1 on t_test_1(col_14);
select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]
0.324
40.5
B1 【有人会说,这里优化的不对,要把col_end也带上】
create index idx_t_test_1_2 on t_test_1(col_14,col_end);
select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]
0.324
40.5
B2 【col_end desc 呢】
create index idx_t_test_1_3 on t_test_1(col_14,col_end desc);
select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]
0.324
40.5
B2 【在B2的基础上调整顺序】
create index idx_t_test_1_4 on t_test_1(col_end desc,col_14);
select * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]
0.324
40.5
B3
我们看看有什么相关的参数
select * from A8 where para_name like '%TOP%'
我们把这个看起来像是有关系的参数,修改为1,看下计划和执行时间。
select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [0, 5, 2376]
2 #PRJT2: [0, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [0, 5, 2376]; top_num(5), top_off(0)
4 #SLCT2: [0, 100, 2376]; T_TEST_1.COL_14 > '1'
5 #BLKUP2: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1)
6 #SSCN: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1)
0.008
1
B4
select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14>'1' order by col_end desc limit 0,5;
1 #NSET2: [190, 5, 2376]
2 #PRJT2: [190, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [190, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [11, 2500, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [11, 2500, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range('1',max]
0.324
40.5
B5
select/*+TOP_ORDER_OPT_FLAG(1)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;
1 #NSET2: [0, 5, 2376]
2 #PRJT2: [0, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [0, 5, 2376]; top_num(5), top_off(0)
4 #SLCT2: [0, 100, 2376]; T_TEST_1.COL_14 = '1'
5 #BLKUP2: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1)
6 #SSCN: [0, 100, 2376]; IDX_T_TEST_1_4(T_TEST_1)
0.008
1
B6
select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;
1 #NSET2: [5, 5, 2376]
2 #PRJT2: [5, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [5, 5, 2376]; top_num(5), top_off(0)
4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_3(T_TEST_1)
5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max))
0
B7
update t_test_1 set col_14='1' where rownum<=30000 ;commit;
select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;
1 #NSET2: [5, 5, 2376]
2 #PRJT2: [5, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #TOPN2: [5, 5, 2376]; top_num(5), top_off(0)
4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_3(T_TEST_1)
5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_3(T_TEST_1), scan_range[('1',min),('1',max))
0.008
1
B8
select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4) no_index(t_test_1,IDX_T_TEST_1_3)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;
1 #NSET2: [95, 5, 2376]
2 #PRJT2: [95, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [95, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_1(T_TEST_1)
5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_1(T_TEST_1), scan_range['1','1']
0.186
23.25
B9
select/*+TOP_ORDER_OPT_FLAG(1) no_index(t_test_1,IDX_T_TEST_1_4) no_index(t_test_1,IDX_T_TEST_1_3)
no_index(t_test_1,IDX_T_TEST_1_1)*/ * from t_test_1 where T_TEST_1.COL_14='1' order by col_end desc limit 0,5;
1 #NSET2: [95, 5, 2376]
2 #PRJT2: [95, 5, 2376]; exp_num(52), is_atom(FALSE)
3 #SORT3: [95, 5, 2376]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #BLKUP2: [5, 1250, 2376]; IDX_T_TEST_1_2(T_TEST_1)
5 #SSEK2: [5, 1250, 2376]; scan_type(ASC), IDX_T_TEST_1_2(T_TEST_1), scan_range[('1',min),('1',max))
0.343
42.875
解读分析:
1. 通过A0,和A1,展示的我们通常碰到的场景,一个查询涉及到的表没有索引,于是我们在查询列上建立索引。在这个例子当中,我们可以看到,本来没有走索引的语句,通过我们建立索引后,确实走索引了,但是效率反而下降了4倍多(从原来的0.087秒,变为了0.324秒)。
2. 对于有经验一点的,可能会想到排序列是不是也应该需要放到索引中(这里一般是不需要的,后面有机会再详细介绍;但是特殊情况是需要的,之类就是特殊情况),所以B1和B2的思路是对的,但是实际上,思路需要走到B2-1才是对的。
“sql如何利用索引消除排序”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。