千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

sql如何利用索引消除排序

发表于：2024-10-09 作者：千家信息网编辑
千家信息网最后更新 2024年10月09日，本篇内容介绍了"sql如何利用索引消除排序"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.1.1
千家信息网最后更新 2024年10月09日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安全错误 数据库的锁怎样保障安全 查看数据库服务 应用层软件开发工程师是做什么的 防火墙 家庭网络安全 石家庄学子软件开发有限公司 creo如何设置服务器管理 数据库要求 英雄联盟手游服务器无法登录 服务器阿里云管理地址 金融数据库设计 服务器 端口限速 江阴计算机网络技术价位 数据库原理在线作业答案3 温哥华天气预报软件开发 服务器电脑电源 可能会访问到的服务器有哪些 传统商用开源到云原生数据库 万方全文期刊数据库 灵武app软件开发价格表 计算机网络技术的发展现状及前景 数据库中的数据表如何建立 河南办公系统软件开发要多少钱 数据库经常性维护工作内容 健全网络安全形势研判机制 网络安全短信内容原创 桌面pc 数据库 服务器单路 网络安全法的五个新法 福州软件开发一般多少钱 2012数据库界面 软件开发著作权评估

相关文章

0