建站
咨询
售后
建站咨询
本篇内容介绍了“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数据。
代码：
droptablet_test_1;
-- 创建一个 50 列的表
declare
tb_s int default1;
col_s int default50;
a_sql clob default'create table t_test_';
--b_sql clob default 'create huge table ht_test_';
begin
forrrs in1..tb_s loop
a_sql:=a_sql||rrs||'(';
--b_sql=b_sql||rrs||'(';
forrs in1..col_s-1 loop
a_sql:=a_sql||'col_'||rs||' varchar(50),';
--b_sql=b_sql||'col_'||rs||' varchar(50),';
endloop;
-- 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)';
executeIMMEDIATEa_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_';
endloop;
NULL;
end;
-- 生成50000测试数据
--初始化基础数据
declare
tb_s int default1;
col_s int default50;
ROW_S INT DEFAULT50000;
a_sql clob default'INSERT INTO t_test_';
commit_i int default10000;
begin
forrrs in1..tb_s loop
--for rrs in 9..tb_s loop
FORRRRS IN1..ROW_S LOOP
a_sql:=a_sql||rrs||' VALUES(';
forrs in1..col_s-1 loop
a_sql:=a_sql||' dbms_random.string(''x'',50),';
endloop;
a_sql:=a_sql||RRRS||',sysdate-dbms_random.value(1000000))';
EXECUTEimmediatea_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;
endif;
ENDLOOP;
endloop;
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]
| 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如何利用索引消除排序”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
文章名称：sql如何利用索引消除排序
URL网址：http://scyingshan.cn/article/gdsohe.html
