千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

sql如何利用索引消除排序

发表于：2024-11-29 作者：千家信息网编辑
千家信息网最后更新 2024年11月29日，本篇内容介绍了"sql如何利用索引消除排序"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.1.1
千家信息网最后更新 2024年11月29日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数据。

代码：

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

很赞哦！
索引 时间 数据 语句 排序 思路 情况 方法 环境 查询 特殊 内容 参数 基础 实际 更多 知识 处理 有关 不对 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 服务器在开机之前拔掉了内存 中外全文数据库有哪几个 安装卸载工具连接不上数据库 百思图软件开发大连 项目数据库管理 南京软件开发软件公司 方舟服务器崩了 二维码智慧门牌管理软件开发 计算机网络技术课程教学内容 北京专业网络技术服务口碑推荐 怎么看联机侠服务器ip 临床数据库使用 如何登录服务器管理系统 网络技术和计算机专业 创建数据库管理系统的app 网络安全战时准备 检测网络正常无法连接服务器 vf数据库技术五个阶段 svn独立客户端连接服务器失败 电脑网络安全证书存在问题 网络安全是指通过采取 广州个性化软件开发 数据库 保密方法 河北区法院开展网络安全宣传 怎么监控自己的云服务器 在电脑上搭建服务器 文献数据库的使用方法 数据库中成绩变量英文 数据库建库员DBD 网络安全员做什么

相关文章

0