本篇内容介绍了“sql如何利用索引消除排序”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
我们经常碰到这种SQL：
Select * from tab where col1 >‘x’ order by col2 desc limit 0,5;
执行时间很长，需要优化。
这种问题，我们在首页展示时经常碰到。这里我们介绍两个经典的优化思路。
数据库版本：DM Database Server x64 V8.1.0.156-Build(2019.05.05-106384)SEC
环境：个人pc环境
说明：
新建一个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;
说明：这里所有的执行，都不取第一次的执行时间；执行比为【当前方法的执行时间/所有方法的最小执行时间】
说明 | 处理 | 语句 | 计划 | 执行时间（秒） | 执行比 |
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]
| 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]
| 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]
| 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]
| 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]
| 0.324 | 40.5 |
B3 | 我们看看有什么相关的参数 | 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]
| 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]
| 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]
| 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]
| 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]
| 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]
| 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) | 1 #NSET2: [95, 5, 2376]
| 0.343 | 42.875 |
解读分析：
1. 通过A0，和A1，展示的我们通常碰到的场景，一个查询涉及到的表没有索引，于是我们在查询列上建立索引。在这个例子当中，我们可以看到，本来没有走索引的语句，通过我们建立索引后，确实走索引了，但是效率反而下降了4倍多（从原来的0.087秒，变为了0.324秒）。
2. 对于有经验一点的，可能会想到排序列是不是也应该需要放到索引中（这里一般是不需要的，后面有机会再详细介绍；但是特殊情况是需要的，之类就是特殊情况），所以B1和B2的思路是对的，但是实际上，思路需要走到B2-1才是对的。
“sql如何利用索引消除排序”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
