本篇内容介绍了“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如何利用索引消除排序”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注天达云网站，小编将为大家输出更多高质量的实用文章！
|