如何利用索引消除排序
1.1 首页排序展示优化（一）——如何利用索引消除排序
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]
|
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 逻辑上不可避免要做的事情很少），但是慢（但是数据库它却做了很多事情个，可能做了一些不必要的的事情）—— 这个才是一般情况，这种参数正经优化。
而，之类讨论的order by , 就是一个预期的扫描行数很多，但是又想很快，这个本身是变态需求，是应用自己需要考虑规避或者减少的—— 只是有些数据库提供了一些协助手段，但是我们一定不要认为这个是应该的，应用、架构、数据模型的优化，才是根本的，而不是饮鸩止渴。
3. 但是在B2-1 的基础上，结果思路是完全正确的了，还要考虑到对应的优化参数才有用，就是设置参数TOP_ORDER_OPT_FLAG 为1 ，上面的例子中，是通过hint 修改（只对改sql 生效），其实在分析可控的情况下，可以直接全局修改（通过参数的返回修改）：sp_set_para_value(1,’ TOP_ORDER_OPT_FLAG’,1); 这个参数默认是0 。
4. 如果是等值条件的话，就是where 的条件是= ，其实前面提到的B2 就是预期逻辑了。这里为什么非等值条件不可以，大家一定要自己思考清楚。
5. 上面的例子，其实还可以有其他很多解读，包括hint 的用法，大家可以自己补充。这里暂不涉及。
1.1.2.5 优化总结
针对Select * from tab where col1 >‘x’ order by col2 desc limit 0,5; 类似语句优化：
1. 需要修改数据库参数：sp_set_para_value(1,’ TOP_ORDER_OPT_FLAG’,1);
2. 需要建立 col2 desc,col1 的索引。
这个例子中间，还可以引申其他一个思路，进行一些扩展介绍，大家可以想到吗？（提示：rowid ）
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69949798/viewspace-2679255/ ，如需转载，请注明出处，否则将追究法律责任。
