我想说：mysql的join真的很弱

newkid · 发表于 2023-11-17 08:39

需求不一样了，当然不能比较了。
Teacher.Tname 的索引建了没有？

〇〇 · 发表于 2023-11-17 08:45

create index idx_teacher_name  on teacher(tname) ;

索引倒是用了，但反而更慢了
┌─────────────┴─────────────┐

│       INDEX_SCAN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       teacher       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          t_id          │
│          tname          │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       EC: 2000000       │
└───────────────────────────┘
┌──────────────┬───────────┬───────┐
│ sname    │ cname │ score │
│ varchar │  varchar  │ int32 │
├──────────────┼───────────┼───────┤
│ name24765018 │ course351 │ 75 │
│ name44955457 │ course444 │ 590 │
│ name11750571 │ course67  │ 91 │
│ name41253250 │ course762 │ 697 │
│ name46**660 │ course237 │ 233 │
│ name40153604 │ course128 │    4 │
│ name47336647 │ course323 │ 441 │
│ name31977356 │ course138 │ 205 │
└──────────────┴───────────┴───────┘
Run Time (s): real 6.900 user 60.029185 sys 9.063658

newkid · 发表于 2023-11-17 08:48

把计划贴出来看看。
严格说起来教师可能重名，
select SC2.t_id,Student.Sname,course.cname,score
  from student
   ,(select *
      from (select sc.*
                  ,rank() over(partition by sc.t_id,sc.c_id order by sc.score desc) rnk
               from sc,teacher
            where sc.t_id=teacher.t_id
                  and Teacher.Tname='tname553'
            )
      where rnk=1
      ) sc2
   ,course
where Student.s_id=SC2.s_id and SC2.c_id=Course.c_id ;

〇〇 · 发表于 2023-11-17 10:37

newkid 发表于 2023-11-17 08:48
把计划贴出来看看。严格说起来教师可能重名，select SC2.t_id,Student.Sname,course.cname,score from st ...

你的这个0.5秒跑出来了

plan.txt (27.35 KB, 下载次数: 1)

rankplan.txt (23.23 KB, 下载次数: 1)

sql_tigerliu · 发表于 2023-11-17 13:33

newkid 发表于 2023-11-16 23:12
如果用SQL BASELINE把计划固定下来不知道怎么样？

baseline还是会硬解析寻找好的执行计划;

newkid · 发表于 2023-11-17 23:07

sql_tigerliu 发表于 2023-11-17 13:33
baseline还是会硬解析寻找好的执行计划;

用这个设置是不是就可以屏蔽掉？

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', 'select xxx .... ', 'FALSE');

newkid · 发表于 2023-11-17 23:09

〇〇发表于 2023-11-17 08:45
create index idx_teacher_name on teacher(tname) ;索引倒是用了，但反而更慢了 ┌───────── ...

它用上了teachers的索引，但是join到sc的时候并没有用 sc.t_id 上面的索引。是不是还要做个统计信息收集之类的操作？

〇〇 · 发表于 2023-11-20 10:18

newkid 发表于 2023-11-17 23:09
它用上了teachers的索引，但是join到sc的时候并没有用 sc.t_id 上面的索引。是不是还要做个统计信息收集之 ...

它好像没有这种命令，SET explain_output='all';可以输出优化前、优化后、实际执行计划

plan3.txt (86.46 KB, 下载次数: 1)

newkid · 发表于 2023-11-20 22:44

把SQL拆小看看能不能用上SC.T_ID上面的索引？

SELECT * FROM SC WHERE T_ID=XXXX

SELECT * FROM SC,TEACHER WHERE SC.T_ID=TEACHER.T_ID AND TEACHER.TNAME='XXXX'

如果还不行，要么这个索引就不存在，或者优化器看不到它。

〇〇 · 发表于 2023-11-21 08:32

newkid 发表于 2023-11-20 22:44
把SQL拆小看看能不能用上SC.T_ID上面的索引？SELECT * FROM SC WHERE T_ID=XXXXSELECT * FROM SC,TEACHER W ...

第1个可以走sc的索引，第2个不能走索引D explain SELECT * FROM SC WHERE T_ID=553;

┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Unoptimized Logical Plan  ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       (t_id = 553)    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       SEQ_SCAN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          sc          │
└───────────────────────────┘

┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       (t_id = 553)    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       INDEX_SCAN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          sc          │
└───────────────────────────┘

┌─────────────────────────────┐
│┌───────────────────────────┐│
││    Physical Plan    ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       (t_id = 553)    │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       EC: 50000000    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       INDEX_SCAN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          sc          │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          t_id          │
│          s_id          │
│          c_id          │
│          score          │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│       EC: 50000000    │
└───────────────────────────┘
Run Time (s): real 0.061 user 0.000000 sys 0.000000
D explain SELECT * FROM SC,TEACHER WHERE SC.T_ID=TEACHER.T_ID AND TEACHER.TNAME='tname553' ;

┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Unoptimized Logical Plan  ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
│          t_id          │
│          tname          │
│          note          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│    (t_id = t_id)    │
│ (tname = 'tname553') │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    CROSS_PRODUCT    ├──────────────┐
└─────────────┬─────────────┘             │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       SEQ_SCAN       ││       SEQ_SCAN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          sc          ││       teacher       │
└───────────────────────────┘└───────────────────────────┘

┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
│          t_id          │
│          tname          │
│          note          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│    COMPARISON_JOIN    │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          INNER          ├──────────────┐
│    (t_id = t_id)    │             │
└─────────────┬─────────────┘             │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       SEQ_SCAN       ││          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│  t_id>=1 AND t_id IS NOT  ││    (t_id <= 9999999)    │
│          NULL          ││ (tname = 'tname553') │
│          sc          ││                         │
└───────────────────────────┘└─────────────┬─────────────┘
                           ┌─────────────┴─────────────┐
                           │       INDEX_SCAN       │
                           │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
                           │       teacher       │
                           └───────────────────────────┘

┌─────────────────────────────┐
│┌───────────────────────────┐│
││    Physical Plan    ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│       PROJECTION       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          │
│          c_id          │
│          t_id          │
│          score          │
│          t_id          │
│          tname          │
│          note          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_JOIN       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          INNER          │
│       t_id = t_id       ├──────────────┐
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │             │
│       EC: 3706718       │             │
└─────────────┬─────────────┘             │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│       SEQ_SCAN       ││          FILTER       │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          sc          ││  ((t_id <= 9999999) AND │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (tname = 'tname553')) │
│          t_id          ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│          s_id          ││       EC: 2000000       │
│          c_id          ││                         │
│          score          ││                         │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││                         │
│ Filters: t_id>=1 AND t_id ││                         │
│       IS NOT NULL    ││                         │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││                         │
│       EC: 50000000    ││                         │
└───────────────────────────┘└─────────────┬─────────────┘
                           ┌─────────────┴─────────────┐
                           │       INDEX_SCAN       │
                           │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
                           │       teacher       │
                           │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
                           │          t_id          │
                           │          tname          │
                           │          note          │
                           │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
                           │       EC: 2000000       │
                           └───────────────────────────┘
Run Time (s): real 0.624 user 0.046800 sys 0.000000

[转载] 我想说：mysql的join真的很弱