暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【干货攻略】SQL优化案例-索引的回表与过滤

【干货攻略】SQL优化案例-索引的回表与过滤

达梦E学 2025-04-28
156
引 言

本期干货主要介绍索引的回表与过滤，主要是索引扫描时是先回表在过滤还是先过滤在回表的问题。
本章内容已在如下环境上测试：
①数据库版本：达梦DM8。
相关关键字：优化 索引。

——正文——

01

 原始表结构和SQL

表定义以及结构如下：
    CREATE TABLE "T_HIST"
    (
    "DBID_" NUMBER(19,0) NOT NULL,
    "CLASS_" VARCHAR2(510) NOT NULL,
    "DBVERSION_" NUMBER(10,0) NOT NULL,
    "HPROCI_" NUMBER(19,0),
    "TYPE_" VARCHAR2(510),
    "EXECUTION_" VARCHAR2(510),
    "ACTIVITY_NAME_" VARCHAR2(510),
    "START_" TIMESTAMP(6),
    "END_" TIMESTAMP(6),
    "DURATION_" NUMBER(19,0),
    "TRANSITION_" VARCHAR2(510),
    "NEXTIDX_" NUMBER(10,0),
    "HTASK_" NUMBER(19,0),
    CONSTRAINT "PK_T_HIST" NOT CLUSTER PRIMARY KEY("DBID_"),
    CONSTRAINT "FK_HTI_HTASK" FOREIGN KEY("HTASK_") REFERENCES "JBPM4_HIST_TASK"("DBID_"),
    CONSTRAINT "FK_HACTI_HPROCI" FOREIGN KEY("HPROCI_") REFERENCES "JBPM4_HIST_PROCINST"("DBID_"));
    CREATE  INDEX "IDX_EXECUTION_start_DBID_" ON "T_HIST"("EXECUTION_" ASC,"START_" ASC,"DBID_" ASC);

    原始慢SQL如下，执行SQL语句的时间达到44S。这里使用了并行hint，不使用的话2min未出结果。

      select 
               /*+ PARALLEL(8) */ 
               ACTIVITY_NAME_, 
               START_, 
               End_, 
               TYPE_, 
               TRANSITION_, 
               DBID_ 
          from T_HIST 
         where EXECUTION_ like'flow_489047332.197122428%' 
           and TYPE_ != 'custom' 
      order by start_
      SQL> /
      行号
           ACTIVITY_NAME_ START_                     End_                       TYPE_ TRANSITION_ DBID_           
      ---------- -------------- -------------------------- -------------------------- ----- ----------- ----------------
      1          step_665427783 2025-03-19 16:58:23.195000 2025-03-19 16:58:23.195000 start NULL        1742374703195936
      2          step_665427800 2025-03-19 16:58:23.366000 NULL                       task  NULL        221223901
      1   #NSET2: [30410, 1123993->2, 254] 
      2     #LOCAL COLLECT: [30410, 1123993->2, 254]; op_id(3) n_grp_by (0) n_cols(0) n_keys(0) for_sync(TRUE)
      3       #PRJT2: [30410, 1123993->2, 254]; exp_num(7), is_atom(FALSE) 
      4         #LOCAL GATHER: [30410, 1123993->2, 254]; op_id(1) n_grp_by (0) n_cols(7) n_keys(1)
      5           #SORT3: [30410, 1123993->(0+0+0+0+2), 254]; key_num(1), is_distinct(FALSE), is_adaptive(0)
      6             #SLCT2: [30303, 1123993->(0+0+0+0+2), 254]; 
      7               #BLKUP2: [30303, 22479869->(2808507+2809052+2812827+2816019+2815528+2810963+2804557+2802606), 254]; IDX_EXECUTION_start_DBID_(T_HIST)
      8                 #SSEK2: [30303, 22479869->(2808507+2809052+2812827+2816019+2815528+2810963+2804557+2802606), 254]; scan_type(ASC), IDX_EXECUTION_start_DBID_(T_HIST)
      已用时间
      : 00:00:44.160. 执行号:945796613.

      1) 通过打印sql语句的trace可以看到，走的是索引IDX_EXECUTION_start_DBID_，根据条件来看是根据EXECUTION_字段进行二级索引定位+回表+过滤数据。

      2) T_HIST表的数据量是两千两百万,回表后的数据有一百多万，回表的数据量还是比较大的有一百多万条 ，回表后的SLCT操作符过滤性剩下了2条。
      3) 此时我们可以使用hint ENABLE_INDEX_FILTER 使用索引先根据过滤条件进行过滤以减少中间结果集。也就是通过Hint调整为先过滤后回表，之前计划是先回表后过滤数据，
      4) 查看SQL语句中EXECUTION_字段提交的过滤性（select count(*) from T_HIST where EXECUTION_ like'flow_489047332.197122428%' ），发现过滤性特别好直接就剩下两条,所以针对这个SQL就不新建新的覆盖索引。

      02

       添加ENABLE_INDEX_FILTER的hint执行

        select 
                 /*+ PARALLEL(8) ENABLE_INDEX_FILTER(1)*/ 
                 ACTIVITY_NAME_, 
                 START_, 
                 End_, 
                 TYPE_, 
                 TRANSITION_, 
                 DBID_ 
            from T_HIST 
           where EXECUTION_ like'flow_489047332.197122428%' 
             and TYPE_ != 'custom' 
        order by start_
        1   #NSET2: [5018, 1124010->2, 254] 
        2     #LOCAL COLLECT: [5018, 1124010->2, 254]; op_id(3) n_grp_by (0) n_cols(0) n_keys(0) for_sync(TRUE)
        3       #PRJT2: [5018, 1124010->2, 254]; exp_num(7), is_atom(FALSE) 
        4         #LOCAL GATHER: [5018, 1124010->2, 254]; op_id(1) n_grp_by (0) n_cols(7) n_keys(1)
        5           #SORT3: [5018, 1124010->(0+0+0+0+2), 254]; key_num(1), is_distinct(FALSE), is_adaptive(0)
        6             #SLCT2: [4911, 1124010->(0+0+0+0+2), 254]; 
        7               #BLKUP2: [4911, 1124020->(0+0+0+0+2), 254]; IDX_EXECUTION_start_DBID_(JBPM4_HIST_ACTINST)
        8                 #SLCT2: [4911, 1124020->(0+0+0+0+2), 254]; 
        9                   #SSEK2: [4911, 1124020->(2808521+2809173+2812572+2816075+2815272+2811003+2804879+2802914), 254]; scan_type(ASC), IDX_EXECUTION_start_DBID_(JBPM4_HIST_ACTINST)
        已用时间
        : 968.404(毫秒). 执行号:945796615.

        从执行结果看，走的是SEEK->SLCT2->BLKUP，也就是在索引扫描阶段即可完成过滤，大幅减少回表次数。

        SQL中使用并行hint的情况下，执行时间也从44S优化到了不到1s，不使用并行的话是2.2s左右。

        03

         分析

        那么SQL中在什么情况下先过滤后回表还是先回表后过滤数据呢？ 
        这个hint ENABLE_INDEX_FILTER类似于MySQL索引下推（Index Condition Pushdown，简称ICP），通过将查询条件从MySQL的服务器层（Service Layer）下推至存储引擎层（Storage Engine）进行处理。MySQL会利用索引定位到符合条件的记录，然后在存储引擎层面直接过滤掉不符合条件的数据行，避免不必要的数据读取和传输。
        索引下推的核心思想是将部分查询条件直接在索引扫描过程中进行过滤，而不是在索引扫描完成后返回主键ID再进行回表操作。优势就是减少回表次数、降低数据传输量、减少CPU和内存开销。
        先过滤再回表（索引过滤后回表）适用场景：
        • 索引覆盖查询：查询字段和过滤字段全部在索引中，无需回表（覆盖索引）。
        • 索引筛选率高：如果索引列的选择性（Selectivity）高，能有效减少返回的行数，先用索引过滤可以大幅减少回表操作的次数，提高查询效率。
        • 回表代价高：如果表数据较大，而索引很小，减少回表次数能显著优化性能。

        先回表再过滤（索引查找后回表再过滤）适用场景：
        • 索引列筛选率低：如果索引列的筛选率较低（如索引列值重复较多），先用索引过滤再回表可能不会有效减少数据量。
        • 过滤字段不在索引中：如果过滤字段不在索引中，必须先回表拿到完整数据后再过滤。
        • 回表代价不高：如果表数据量小，随机 IO 影响较小，回表代价不高。

        总结


        本期关于SQL优化中索引的回表与过滤的内容就介绍到这里，希望能给大家带来帮助。



        END


        想要了解更多往期干货，可访问页面最下方#达梦技术干货攻略#合集或下方相关分享。在此邀请更多学员参与“达梦技术干货投稿活动”，稿件获选后将在达梦“干货分享”专栏进行发布，欢迎来稿！


        往期回顾


        【干货】达梦数据库日志监控与分析（一）

        【干货】达梦数据库日志监控与分析（二）

        【开班】达梦认证专家DCP在线课程招生中

        【开班】达梦数据库迁移海南专场培训招生中



        达梦E学
        达梦数据  学习园地

        作者：GY


        mysql索引索引sqlsql优化达梦数据库
        文章转载自达梦E学，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论