扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >sql如何利用索引消除排序
  • 305

0
分享到

sql如何利用索引消除排序

2024-04-02 19:04:59 305人浏览 独家记忆
摘要

本篇内容介绍了“sql如何利用索引消除排序”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.1.1&nbs

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

您可能感兴趣的文档:

--结束END--

本文标题: sql如何利用索引消除排序

本文链接: https://lsjlt.com/news/59683.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • sql如何利用索引消除排序
    本篇内容介绍了“sql如何利用索引消除排序”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1.1.1&nbs...
    99+
    2024-04-02
  • SQL如何利用索引排序
    这篇文章主要为大家展示了“SQL如何利用索引排序”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“SQL如何利用索引排序”这篇文章吧。近期在做数据库的慢SQL优化，...
    99+
    2024-04-02
  • Mysql如何使用索引排序
    在mysql中使用索引排序的方法首先，在命令行中启动MySQL服务；service mysql start　　MySQL服务启动后，在命令行中输入mysql的用户名和密码登录到MySQL；mysql -u root -p登录到MySQL后，...
    99+
    2024-04-02
  • 如何利用索引提升PHP与MySQL的排序和分组操作？
    索引是一种在数据库表中创建的数据结构，用来提高查询操作的效率。在PHP和MySQL中，索引可以在排序和分组操作中发挥重要作用，提升数据库查询的性能。本文将介绍如何利用索引来优化PHP与MySQL的排序和分组操作，并提供具体的代码示例。排序操...
    99+
    2023-10-21
    索引（Index） 排序（Sort） 分组（Group）
  • MySQL中如何利用索引
    MySQL中如何利用索引，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。一、前言在MySQL中进行SQL优化的时候，经常会在一些情况下，对M...
    99+
    2024-04-02
  • MySQL利用索引优化ORDER BY排序语句的方法
    创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc)...
    99+
    2022-05-24
    MySQL 优化ORDER BY语句 MySQL 优化排序语句 MySQL 索引优化
  • MySQL中怎么利用索引优化ORDER BY排序语句
    MySQL中怎么利用索引优化ORDER BY排序语句，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 1、ORDER BY的索...
    99+
    2024-04-02
  • 如何高效利用mysql索引
    这篇文章主要介绍了如何高效利用mysql索引，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。前言mysql 相信大部分人都用过，索引肯定也是用...
    99+
    2024-04-02
  • ASP和Django应用程序的Windows索引故障排除指南
    ASP和Django是两种常见的Web应用程序框架，在Windows平台上广泛应用。当这些应用程序出现索引故障时，可能会导致用户无法正常访问网站。本文将介绍如何排除这些故障，并提供一些演示代码。 ASP应用程序的索引故障排除指南 ASP应用...
    99+
    2023-08-24
    django windows 索引
  • 【索引失效如何排查】
    查看查询语句的执行计划：可以通过 SQL 执行计划来确定是否使用了索引，以及是否存在全表扫描等低效的操作。在查询语句前面加上 “EXPLAIN” 命令，可以查看查询语句的执行计划。 检查索引是否存在...
    99+
    2023-09-16
    数据库 mysql
  • 如何利用 Python 中的索引优化程序性能？
    Python 是一种高级编程语言，虽然它非常简单易学，但在处理大规模数据时，程序的性能往往会成为一个瓶颈。因此，我们需要使用一些优化技巧来提高程序性能。其中之一就是利用 Python 中的索引。 索引是一个指向存储在内存中的数据结构的指针。...
    99+
    2023-10-20
    索引 日志 面试
  • oracle如何删除索引
    答案： oracle 中删除索引的步骤包括：标识要删除的索引；使用 drop index 语句删除索引；重新编译受影响的对象。 如何删除 Oracle 索引 Oracle 索...
    99+
    2024-06-12
    oracle
  • sql中不用orderby如何排序
    在SQL中，可以使用ORDER BY子句对结果集进行排序。如果不想使用ORDER BY子句对结果集排序，可以使用其他方法来达到排序的...
    99+
    2024-04-09
    sql
  • Java中的排序数索引怎么利用分治算法实现
    Java中的排序数索引怎么利用分治算法实现？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。具体如下：public class Ono { public static i...
    99+
    2023-05-31
    java 索引 分治算法
  • MySQL 使用索引扫描进行排序
    目录安装sakila索引扫描排序表结构可以使用索引扫描来做排序的情况补足前导列order by 中只包含一种排序无法使用索引扫描的情况查询条件中包含不同排序方向查询条件中引用不在索引中的列无法组合最左前缀时第一列是查...
    99+
    2022-05-27
    MySQL 索引扫描 MySQL 索引排序
  • 使用索引来排序查询结果
    使用索引来排序查询结果在MongoDB中，排序操作可以通过从索引中按照索引顺序获取文档的方式来保证结果的有序性。如果查询计划器(planner)无法从索引中得到排序顺序，那么它将需要在内存中排序结果。相比于...
    99+
    2024-04-02
  • SQL优化之如何使用索引
    这篇文章主要介绍SQL优化之如何使用索引，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！下面 sql  30秒执行出结果，查看 sql ...
    99+
    2024-04-02
  • sql索引如何建立与使用
    在SQL中，索引是一种数据结构，用于提高数据库的查询性能。索引可以加快数据的查找速度，特别是在大型数据库中。下面是索引的建立和使用方...
    99+
    2023-09-05
    sql
  • MySQL 5.5如何删除索引
    这篇文章主要为大家展示了“MySQL 5.5如何删除索引”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL 5.5如何删除索引”这篇文章吧。 ...
    99+
    2024-04-02
  • mysql如何删除index索引
    小编给大家分享一下mysql如何删除index索引，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ m...
    99+
    2024-04-02
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作