四川盐亭网站制作公司

sql如何利用索引消除排序

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


本文标题：sql如何利用索引消除排序
文章源于：http://scyanting.com/article/gdsohe.html

其他资讯