108-一个SQL, 两种优化方法, 效果都还可以

sql_tigerliu

chengccy2010 发表于 2023-8-15 13:53
拉链表不适合百万级以上的。 如果一个表到了百万级了还是冗余成日频表更好用，分区、索引都方便

专家说的有道理. w表如果是日频表, 就不用那么费劲做优化了, 直接等值关联就好了, 这个从设计的根本上解决了问题.

〇〇

才知道拉链表，https://zhuanlan.zhihu.com/p/618462962?utm_id=0

sql_tigerliu

〇〇 发表于 2023-8-15 11:18
不知下面写法是否等价 select h.*,w.dp_close as w_dp_close from   H LEFT JOIN  W     ON   H.I_CODE = W ...

这个就是业务sql的原始写法, 只是你多加了一个等于号.

newkid

sql_tigerliu 发表于 2023-8-15 09:07
12c的offset  fetch写法, 内部是用row_number分析实现的, 把标量子查询部分单独拿出来执行, 执行计划可以走 ...

我试验了一下OUTER APPLY, 它比标量子查询多了一个排序操作，COUNT STOPKEY进入太迟了，所以速度慢：
select count(w_dp_close)
from
(
select H.*,o.w_dp_close
from T_DEMO_648_QDII_H H
    OUTER APPLY (select dp_close as w_dp_close
        from
         (select w.dp_close,w.beg_date
          from  T_DEMO_648_Wind_W W
          where  H.beg_date<W.end_date and H.i_code=W.i_code
          order by W.end_date  ------- 排序造成了计划中第九行的 SORT ORDER BY
         )x
     where rownum<=1 and H.beg_date>=x.beg_date
    ) o
);

Plan hash value: 2178921771

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                         |     1 |    26 |  8961K  (9)| 00:05:51 |
|   1 |  SORT AGGREGATE                             |                         |     1 |    26 |            |          |
|   2 |   MERGE JOIN OUTER                          |                         |   744K|    18M|  8961K  (9)| 00:05:51 |
|   3 |    TABLE ACCESS FULL                        | T_DEMO_648_QDII_H       |   744K|  9445K|   824   (1)| 00:00:01 |
|   4 |    BUFFER SORT                              |                         |     1 |    13 |  8960K  (9)| 00:05:51 |
|   5 |     VIEW                                    | VW_LAT_FB1FEBC6         |     1 |    13 |    12   (9)| 00:00:01 |
|   6 |      VIEW                                   | VW_LAT_2131DCCF         |     1 |    13 |    12   (9)| 00:00:01 |
|*  7 |       COUNT STOPKEY                         |                         |       |       |            |          |
|*  8 |        VIEW                                 |                         |  1144 | 25168 |    12   (9)| 00:00:01 |
|   9 |         SORT ORDER BY                       |                         |  1144 | 27456 |    12   (9)| 00:00:01 |
|  10 |          TABLE ACCESS BY INDEX ROWID BATCHED| T_DEMO_648_WIND_W       |  1144 | 27456 |    11   (0)| 00:00:01 |
|* 11 |           INDEX RANGE SCAN                  | IDX_T_DEMO_648_WIND_W_1 |  1144 |       |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(ROWNUM<=1)
   8 - filter("H"."BEG_DATE">="X"."BEG_DATE")
  11 - access("H"."I_CODE"="W"."I_CODE" AND "H"."BEG_DATE"<"W"."END_DATE" AND "W"."END_DATE" IS NOT NULL)
  

把那个排序拿掉，变成三秒左右：  
select count(w_dp_close)
from
(
select H.*,o.w_dp_close
from T_DEMO_648_QDII_H H
    OUTER APPLY (select dp_close as w_dp_close
        from
         (select w.dp_close,w.beg_date
          from  T_DEMO_648_Wind_W W
          where  H.beg_date<W.end_date and H.i_code=W.i_code
          --order by W.end_date
         )x
     where rownum<=1 and H.beg_date>=x.beg_date
    ) o
);

Plan hash value: 3167939130

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |     1 |    26 |  2977K  (1)| 00:01:57 |
|   1 |  SORT AGGREGATE                           |                         |     1 |    26 |            |          |
|   2 |   MERGE JOIN OUTER                        |                         |   744K|    18M|  2977K  (1)| 00:01:57 |
|   3 |    TABLE ACCESS FULL                      | T_DEMO_648_QDII_H       |   744K|  9445K|   824   (1)| 00:00:01 |
|   4 |    BUFFER SORT                            |                         |     1 |    13 |  2977K  (1)| 00:01:57 |
|   5 |     VIEW                                  | VW_LAT_FB1FEBC6         |     1 |    13 |     4   (0)| 00:00:01 |
|   6 |      VIEW                                 | VW_LAT_2131DCCF         |     1 |    13 |     4   (0)| 00:00:01 |
|*  7 |       COUNT STOPKEY                       |                         |       |       |            |          |
|*  8 |        TABLE ACCESS BY INDEX ROWID BATCHED| T_DEMO_648_WIND_W       |   300 |  7200 |     4   (0)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN                  | IDX_T_DEMO_648_WIND_W_2 |    21 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(ROWNUM<=1)
   8 - filter("H"."BEG_DATE"<"W"."END_DATE")
   9 - access("H"."I_CODE"="W"."I_CODE" AND SYS_OP_DESCEND("H"."BEG_DATE")<=SYS_OP_DESCEND("BEG_DATE") AND
              SYS_OP_DESCEND("BEG_DATE") IS NOT NULL)
       filter("H"."BEG_DATE">=SYS_OP_UNDESCEND(SYS_OP_DESCEND("BEG_DATE")))
      

既然这样就没必要写两层了：
select count(w_dp_close)
from
(
select H.*,o.w_dp_close
from T_DEMO_648_QDII_H H
    OUTER APPLY (select dp_close  as w_dp_close
          from  T_DEMO_648_Wind_W W
          where  H.beg_date<W.end_date and H.i_code=W.i_code
                 and H.beg_date>=w.beg_date
                 and rownum=1
    ) o
);

newkid

〇〇 发表于 2023-8-15 05:54
还是一样，它的执行计划没有用到索引

它的计划不是标量子查询，变成HASH JOIN了，不知道内部是怎么决策的。

〇〇

**进的开源数据库 postgresql,第一个根本跑不出来
mydb=# create table h
mydb-# as
mydb-# with h1(i_code ,beg_date ,end_date ,DP_CLOSE )
mydb-# as (select 'c'||i,d,d+interval '1' day ,random()from generate_series(1,151)t(i),
mydb(# generate_series(date'2010-01-01',date'2023-08-02',interval '1' day)q(d))
mydb-# select * from h1;
SELECT 749262
Time: 5776.354 ms (00:05.776)
mydb=# create table w
mydb-# as
mydb-# with h1(i_code ,beg_date ,end_date ,DP_CLOSE )
mydb-# as (select 'c'||i,d,d+interval '1' day ,random()from generate_series(1,151)t(i),
mydb(# generate_series(date'2010-01-01',date'2023-08-02',interval '1' day)q(d))
mydb-# select * from h1;
SELECT 749262
Time: 5868.821 ms (00:05.869)
mydb=# select count(w_dp_close) from
mydb-# (
mydb(# select h.*,w.dp_close as w_dp_close
mydb(# from   H
mydb(# LEFT JOIN  W
mydb(#     ON   H.I_CODE = W.I_CODE
mydb(#      AND W.BEG_DATE <= H.BEG_DATE
mydb(#      AND W.END_DATE >  H.BEG_DATE
mydb(# );
ERROR:  subquery in FROM must have an alias
LINE 2: (
        ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
Time: 1.386 ms
mydb=#
mydb=# select count(w_dp_close) from
mydb-# (
mydb(# select h.*,w.dp_close as w_dp_close
mydb(# from   H
mydb(# LEFT JOIN  W
mydb(#     ON   H.I_CODE = W.I_CODE
mydb(#      AND W.BEG_DATE <= H.BEG_DATE
mydb(#      AND W.END_DATE >  H.BEG_DATE
mydb(# )x;

^CCancel request sent
ERROR:  canceling statement due to user request

mydb=# select count(w_dp_close) from
mydb-# (
mydb(# select h.*,w.dp_close as w_dp_close
mydb(# from   H
mydb(# LEFT JOIN  W
mydb(#     ON   H.I_CODE = W.I_CODE
mydb(#      AND W.BEG_DATE = H.BEG_DATE
mydb(#      AND W.END_DATE =  H.END_DATE
mydb(# )x;
count
--------
749262
(1 row)

Time: 8217.422 ms (00:08.217)

〇〇

3能出结果

mydb=# create index idx on  W(i_code,end_date);
CREATE INDEX
mydb=# select count(w_dp_close)
mydb-# from
mydb-# (
mydb(# select H.*
mydb(#    ,(select dp_close from
mydb(#          (select w.dp_close,w.beg_date
mydb(#           from    W
mydb(#           where  H.beg_date<W.end_date and H.i_code=W.i_code  and H.beg_date>=w.beg_date
mydb(#                     order by W.end_date limit 1)x
mydb(#     ) as w_dp_close
mydb(# from   H
mydb(# )y;
count
--------
749262
(1 row)

Time: 55143.014 ms (00:55.143)

sql_tigerliu

〇〇 发表于 2023-8-17 09:36
3能出结果mydb=# create index idx on  W(i_code,end_date);CREATE INDEXmydb=# select count(w_dp_close)m ...

卢大师, 我用你测试用例,  在我的笔记本电脑虚拟机上, pg版本15.3 , 原sql 跑了90秒,  方法1(你直接改成了等值关联) 890毫秒, 我用generate_series模拟oracle的connect by, 时间也差不多(990毫秒) 说明pg的这个函数比oracle的connect by效率要高; 方法2 标量子查询用时 3.5秒.
方法1的generate_series写法:
select count(w_dp_close) from
(
select h.*,w1.dp_close as w_dp_close
from   H
LEFT JOIN (select i_code,generate_series(1,end_date::date-beg_date::date)+beg_date::date-1 as beg_date,end_date,dp_close from w ) W1
     ON   H.I_CODE   = W1.I_CODE
     AND H.BEG_DATE = W1.BEG_DATE
     --AND W.END_DATE =  H.END_DATE
)x;

〇〇

我也是15.3，你的笔记本电脑比我手机性能高多了，原SQL我这跑了20分钟取消的

〇〇

27楼的执行结果和计划
mydb=# select count(w_dp_close) from
(
select h.*,w1.dp_close as w_dp_close
from   H
LEFT JOIN (select i_code,generate_series(1,end_date::date-beg_date::date)+beg_date::date-1 as beg_date,end_date,dp_close from w ) W1
     ON   H.I_CODE   = W1.I_CODE
     AND H.BEG_DATE = W1.BEG_DATE
     --AND W.END_DATE =  H.END_DATE
)x;
count
--------
749262
(1 row)

Time: 12273.476 ms (00:12.273)
mydb=# explain select count(w_dp_close) from
(
select h.*,w1.dp_close as w_dp_close
from   H
LEFT JOIN (select i_code,generate_series(1,end_date::date-beg_date::date)+beg_date::date-1 as beg_date,end_date,dp_close from w ) W1
     ON   H.I_CODE   = W1.I_CODE
     AND H.BEG_DATE = W1.BEG_DATE
     --AND W.END_DATE =  H.END_DATE
)x;
                                                    QUERY PLAN

------------------------------------------------------------------------------------------------
-------------------
Aggregate  (cost=10450281.59..10450281.60 rows=1 width=8)
   ->  Merge Left Join  (cost=10427808.55..10448408.44 rows=749262 width=8)
         Merge Cond: ((h.i_code = w1.i_code) AND (h.beg_date = w1.beg_date))
         ->  Sort  (cost=98918.77..100791.93 rows=749262 width=12)
               Sort Key: h.i_code, h.beg_date
               ->  Seq Scan on h  (cost=0.00..13002.62 rows=749262 width=12)
         ->  Materialize  (cost=10328889.78..10332636.09 rows=749262 width=16)
               ->  Sort  (cost=10328889.78..10330762.93 rows=749262 width=16)
                     Sort Key: w1.i_code, w1.beg_date
                     ->  Subquery Scan on w1  (cost=1000.00..10242973.62 rows=749262 width=16)
                           ->  Gather  (cost=1000.00..10235481.01 rows=749262 width=24)
                                 Workers Planned: 2
                                 ->  Result  (cost=0.00..**59554.81 rows=312192000 width=24)
                                       ->  ProjectSet  (cost=0.00..1574274.80 rows=312192000 wid
th=24)
                                             ->  Parallel Seq Scan on w  (cost=0.00..8631.92 row
s=312192 width=28)
(15 rows)