共计 7055 个字符，预计需要花费 18 分钟才能阅读完成。
本篇内容介绍了“sql 如何利用索引消除排序”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
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 如何利用索引消除排序”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
